The given relation PROJECT_DETAILS has to be normalized step-by-step. For each n
ID: 3825614 • Letter: T
Question
The given relation PROJECT_DETAILS has to be normalized step-by-step. For each normal form, the FD diagram has to be drawn. The dependencies and anomalies have to be briefed.
Project Employee Project Employee Department Department Hourly Project Title Name Code No Name No Rate Budget A Smith S10001 L004 22.00 Pensions L Jones L023 Pensions 18.50 24500 PC010 S10030 System P Lewis S21010 L004 21.00 B Jones 21.75 S 10010 L004 IT PC045 S 10001 Salaries A Smith L004 18.00 17400 S3 1002 System TGilbert L028 Database 25.50 S13210 W Richards L008 Salary 17.00 T Gilbert L028 Database 23.25 S3 1002 PC064 S21010 HR System P Lewis L004 IT 17.50 12250 S1 0034 16.50 B James L009 HRExplanation / Answer
Normalization:
It is a process which is used to organize data in such to avoid data redundancy, insertion update & deletion anomalies.
1st Normal Form:
As per the rule of first normal form, an attribute of a table cannot hold multiple values.
But in the given information, there are multiple values. By avoiding them the resulting table as follows:
Project Code
Employee No.
Project Title
Employee Name
Department No.
Department Name
Hourly rate
Project Budjet
PC010
S10001
Pensions System
A Smith
L004
IT
22.00
24500
PC010
S10030
Pensions System
L Jones
L023
Pensions
18.50
24500
PC010
S21010
Pensions System
P Lewis
L004
IT
21.00
24500
PC045
S10010
Salaries System
B Jones
L004
IT
21.75
17400
PC045
S10001
Salaries System
A Smith
L004
IT
18.00
17400
PC045
S31002
Salaries System
T Gilbert
L028
Database
25.50
17400
PC045
S13210
Salaries System
W Richerds
L008
Salary
17.00
17400
PC064
S31002
HR Systems
T Gilbert
L028
Database
23.25
12250
PC064
S21010
HR Systems
P Lewis
L004
IT
17.50
12250
PC064
S10034
HR Systems
B James
L009
HR
16.50
12250
2nd Normal form:
It should be in 1st NF.
As per the Second Normal Form there must not be any partial dependency of any column on primary key.
In the above resulting table there are some partial dependencies. We have toremodel database as follows:
Employee No.
Employee Name
Department No.
Hourly rate
S10001
A Smith
L004
22.00
S10030
L Jones
L023
18.50
S21010
P Lewis
L004
21.00
S10010
B Jones
L004
21.75
S10001
A Smith
L004
18.00
S31002
T Gilbert
L028
25.50
S13210
W Richerds
L008
17.00
S31002
T Gilbert
L028
23.25
S21010
P Lewis
L004
17.50
S10034
B James
L009
16.50
Department No.
Department Name
Project Code
L004
IT
PC010
L004
IT
PC045
L004
IT
PC064
L008
Salary
PC045
L009
HR
PC064
L023
Pensions
PC010
L028
Database
PC045
L028
Database
PC054
Project Code
Project Title
Project Budjet
PC010
Pensions System
24500
PC045
Salaries System
17400
PC064
HR System
12250
3NF:
As per Third Normal form applies that every non-prime attribute of table must be dependent on primary key.
The above relation satisfies 3NF. Because there are no trivial dependencies.
BCNF.
It should be in 3NF.
A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF.
The above table also satisfies BCNF.
Project Code
Employee No.
Project Title
Employee Name
Department No.
Department Name
Hourly rate
Project Budjet
PC010
S10001
Pensions System
A Smith
L004
IT
22.00
24500
PC010
S10030
Pensions System
L Jones
L023
Pensions
18.50
24500
PC010
S21010
Pensions System
P Lewis
L004
IT
21.00
24500
PC045
S10010
Salaries System
B Jones
L004
IT
21.75
17400
PC045
S10001
Salaries System
A Smith
L004
IT
18.00
17400
PC045
S31002
Salaries System
T Gilbert
L028
Database
25.50
17400
PC045
S13210
Salaries System
W Richerds
L008
Salary
17.00
17400
PC064
S31002
HR Systems
T Gilbert
L028
Database
23.25
12250
PC064
S21010
HR Systems
P Lewis
L004
IT
17.50
12250
PC064
S10034
HR Systems
B James
L009
HR
16.50
12250
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.