(Employee_Project_Department table) SSN FNAME LNAME PNO Pname DNAME DNUMBER 3334
ID: 3708676 • Letter: #
Question
(Employee_Project_Department table)
SSN
FNAME
LNAME
PNO
Pname
DNAME
DNUMBER
333445555
Franklin
Wong
2
Product Y
Research
5
333445555
Franklin
Wong
3
Product Z
Research
5
333445555
Franklin
Wong
10
Computerization
Research
5
333445555
Franklin
Wong
20
Reorganization
Research
5
987654321
Jennifer
Wallace
30
New Benefits
Administration
4
987654321
Jennifer
Wallace
20
Reorganization
Administration
4
888665555
James
Borg
20
Reorganization
Headquarters
1
[1] Please make an example for an insertion anomaly using above table.
[2] Please make an example for a deletion anomaly using above table.
[3] Please make an example for a modification anomaly using above table.
[4] Please find all the candidate keys.
[5] Choose a primary key for above table.
[6] Find all the functional dependencies. And identify the dependency type (i.e. primary key, partial, or transitive)
[7] Please identify the first normal form of above table.
[8] Please get the second normal form of problem [7].
[9] Please get the third normal form of problem [8].
SSN
FNAME
LNAME
PNO
Pname
DNAME
DNUMBER
333445555
Franklin
Wong
2
Product Y
Research
5
333445555
Franklin
Wong
3
Product Z
Research
5
333445555
Franklin
Wong
10
Computerization
Research
5
333445555
Franklin
Wong
20
Reorganization
Research
5
987654321
Jennifer
Wallace
30
New Benefits
Administration
4
987654321
Jennifer
Wallace
20
Reorganization
Administration
4
888665555
James
Borg
20
Reorganization
Headquarters
1
Explanation / Answer
Question 1
Insertiion Anomaly will occur if an employees is inserted in more than one department
Example
Employee with SSN 333445555 Franklin Wong is already in Reasearcg Department.If the same employee is inserted for the Administration department then there will be an insert anomaly.
Question 2
Deletion Anomaly will occur if an employee is deleted from the department will also delete the details about the project.
Example
Employee with 888665555 James Borg is Headquarters department tagged under Reorganization project.If we delete this employee then the details about the project is also lost.
Question 3
Modification Anomaly will occur when the details about a particular employee is not chagned in all places.
Example
Example Franaklin Wong is tagged under 4 projects.So if there is a change in the name spelling then it must be done in all 4 places or else it will be an modification anomaly.
Question 4
Candidate Key are
PNO and Pname
SSN and FNAME
DNAME and DNUMBER
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.