Microsoft Office Tutorials and References
In Depth Information
Third Normal Form
A table in 2NF is in third normal form (3NF) if every determinant is a candidate key. This
definition for 3NF is referred to as Boyce-Codd normal form (BCNF) and is an improvement
over the original version of 3NF. What are the determinants in the Employee table? The
EmployeeNum and HealthPlan fields are the determinants; however, the EmployeeNum
field is a candidate key because it’s the table’s primary key, and the HealthPlan field is not a
candidate key. Therefore, the Employee table is in second normal form, but it is not in third
normal form.
To convert a table to third normal form, remove the fields that depend on the non-
candidate-key determinant and place them into a new table with the determinant as the
primary key. For the Employee table, the PlanDesc field depends on the HealthPlan field,
which is a non-candidate-key determinant. Thus, you remove the PlanDesc field from the
table, create a new HealthBenefits table, place the PlanDesc field in the HealthBenefits
table, and then make the HealthPlan field the primary key of the HealthBenefits table.
Note that only the PlanDesc field is removed from the Employee table; the HealthPlan
field remains as a foreign key in the Employee table. Figure A-17 shows the database
design for the four 3NF tables.
Figure A-17
After conversion to 3NF
Employee
primary
key
EmployeeNum
FirstName
LastName
HealthPlan
2173
Barbara
Hennessey
B
4519
Lee
Noordsy
A
8005
Pat
Amidon
C
8112
Chris
Wandzell
A
primary
key
HealthBenefits
Position
primary
key
HealthPlan
PlanDesc
PositionID
PositionDesc
PayGrade
A
Managed PPO
1
Director
45
B
Managed HMO
2
Manager
40
C
Health Savings
3
Analyst
30
4
Clerk
20
primary
key
Employment
EmployeeNum
PositionID
StartDate
2173
2
12/14/2008
4519
1
04/23/2010
4519
3
11/11/2004
8005
3
06/05/2009
8005
4
07/02/2007
8112
1
12/15/2009
8112
2
10/04/2008
 
Search JabSto ::




Custom Search