Your Name _______________________________
Homework Six
(4 points total)
1) Consider the following non-normalized table (2 points)
HEALTH_CENTER_TABLE
DRID DrName DrSpecialty PtID PtName PtInsCoID PtInsCoName NextApptDate
1 Dr. Joe General 111 Mia I1 Max Profit June 1
1 Dr. Joe General 222 Pat I1 Max Profit June 2
1 Dr. Joe General 333 Pat I2 Heartless June 3
2 Dr. Sue Ob 333 Pat I2 Heartless June 7
3 Dr. Kim OB 555 Lisa I3 Bottom line June 2
3 Dr. Kim OB 111 Mia I1 Max Profit June 3
4 Dr. Kim Orthopedic 555 Lisa I3 Bottom Line June 9
This table is based on following requirements:
1) Each doctor has only one specialty
2) A patient is insured through one insurance company.
3) A patient can have many appointments scheduled.
4) A patient can have only one appointment scheduled with a particular doctor.
Complete the following tasks:
a) List all the dependencies (desireable, partial and transitive)
b) Determine the primary key for the non-normalized table
c) Complete the normalization process (show 2nd and 3rd normal forms)
d) Create the relational schema for your final set of normalized tables
2) Consider the following non-normalized table (2 points)
JOBS
EID Ename PID Pdescr SID Sname StartYear
MD McDonald 1 Burger Flipper 111 Joe 1998
MD McDonald 2 Manager 222 Mike 1997
BK BurgerKing 1 Manager 333 Sue 1998
BK BurgerKing 2 Burger Flipper 222 Mike 1998
BK BurgerKing 2 Burger Flipper 444 Bob 1999
BO TacoBell 1 Taco Stuffer 111 Joe 1999
EID = Employer Id
Ename = Employer Name
PID = Position ID
Pdescr = Position Description
SID = Student ID
Sname = Student Name
StartYear = Year student began working for employer
