Microsoft Office Tutorials and References
In Depth Information
Figure A-15
After conversion to 1NF
primary key
Employee
EmployeeNum
PositionID
FirstName
LastName
PositionDesc
PayGrade
StartDate
HealthPlan
PlanDesc
2173
2
Barbara
Hennessey
Manager
40
12/14/2008
B
Managed HMO
4519
1
Lee
Noordsy
Director
45
04/23/2010
A
Managed PPO
4519
3
Lee
Noordsy
Analyst
30
11/11/2004
A
Managed PPO
8005
3
Pat
Amidon
Analyst
30
06/05/2009
C
Health Savings
8005
4
Pat
Amidon
Clerk
20
07/02/2007
C
Health Savings
8112
1
Chris
Wandzell
Director
45
12/15/2009
A
Managed PPO
8112
2
Chris
Wandzell
Manager
40
10/04/2008
A
Managed PPO
The alternative way to describe the 1NF table is:
Employee ( EmployeeNum , PositionID , FirstName, LastName, PositionDesc, PayGrade,
StartDate, HealthPlan, PlanDesc)
The Employee table is now a true table and has a composite key. The table, however,
suffers from insertion, deletion, and update anomalies. (As an exercise, find examples of
the three anomalies in the table.) The EmployeeNum field is a determinant for the
FirstName, LastName, HealthPlan, and PlanDesc fields, so partial dependencies exist in
the Employee table. It is these partial dependencies that cause the anomalies in the
Employee table, and second normal form addresses the partial-dependency problem.
Second Normal Form
A table in 1NF is in second normal form (2NF) if it does not contain any partial depen-
dencies. To remove partial dependencies from a table and convert it to second normal
form, you perform two steps. First, identify the functional dependencies for every field in
the table. Second, if necessary, create new tables and place each field in a table, so that
the field is functionally dependent on the entire primary key, not part of the primary key. If
you need to create new tables, restrict them to ones with a primary key that is a subset of
the original composite key. Note that partial dependencies occur only when you have a
composite key; a table in first normal form with a single-field primary key is automatically
in second normal form.
First, identifying the functional dependencies leads to the following determinants for
the Employee table:
EmployeeNum
FirstName, LastName, HealthPlan, PlanDesc
PositionID
PositionDesc, PayGrade
EmployeeNum, PositionID
StartDate
HealthPlan
PlanDesc
The EmployeeNum field is a determinant for the FirstName, LastName, HealthPlan, and
PlanDesc fields. The PositionID field is a determinant for the PositionDesc and PayGrade
fields. The HealthPlan field is a determinant for the PlanDesc field. The composite key
EmployeeNum and PositionID is a determinant for the StartDate field. Performing the sec-
ond step in the conversion from first normal form to second form produces the three 2NF
tables shown in Figure A-16.
Search JabSto ::




Custom Search