Microsoft Office Tutorials and References
In Depth Information
After conversion to 2NF
Figure A-16
Employee
primary
key
EmployeeNum
FirstName
LastName
HealthPlan
PlanDesc
2173
Barbara
Hennessey
B
Managed HMO
4519
Lee
Noordsy
A
Managed PPO
8005
Pat
Amidon
C
Health Savings
8112
Chris
Wandzell
A
Managed PPO
Position
p rimary
key
PositionID
PositionDesc
PayGrade
1
Director
45
2
Manager
40
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
The alternative way to describe the 2NF tables is:
Employee ( EmployeeNum , FirstName, LastName, HealthPlan, PlanDesc)
Position ( PositionID , PositionDesc, PayGrade)
Employment ( EmployeeNum , PositionID , StartDate)
Foreign key: EmployeeNum to Employee table
Foreign key: PositionID to Position table
All three tables are in second normal form. Do anomalies still exist? The Position and
Employment tables show no anomalies, but the Employee table suffers from anomalies
caused by the transitive dependency between the HealthPlan and PlanDesc fields. (As an
exercise, find examples of the three anomalies caused by the transitive dependency.) That
is, the HealthPlan field is a determinant for the PlanDesc field, and the EmployeeNum
field is a determinant for the HealthPlan and PlanDesc fields. Third normal form addresses
the transitive-dependency problem.
 
Search JabSto ::




Custom Search