2) Classify each of the following relations as 1NF, 2NF, or 3NF (state any assumptions you make). Identify the key of each relation and Normalize relations into 3NF if they are in 1NF or 2NF. (30 points)
A) PATIENT(PATIENT#, PAT_NAME, PAT_ADR, DOCTOR_NUMBER, DOCTOR_NAME,
MED_EXAM#, MED_EXAM_DESCRIPTION, DATE_TEST, TEST_RESULT)
Normal Form:
Key:
3NF relations:
E_R diagram:
B) BILL(BILL#, DATE, CUST#, CUST_NAME, CUST_ADR, BILL_TOTAL, PRODUCT#,
PRODUCT_DESCRIPTION, UNIT_ORDER/PRODUCT)
Normal Form:
Key:
3NF relations:
E_R diagram:
C) PAYROLL(CHECK#, EMPID, PAYDATE, GROSSPAY, EMPLOYEE_NAME,
EMPLOYEE_ADDRESS, DEPARTMENT_NAME, DEPARTMENT_MANAGER)
Normal Form:
Key:
3NF relations:
E_R diagram:
3) The set of functional dependencies F of relation R(ABCDEFGHIJKLMNOP) is
(20 points)
A à B, C, D
C à B
B à C
E à F, G, H, I, J
J à F, G
EF à G,H
K à L, M, N, O
O à M
a) Find a minimal cover of the set F.
b) Find a candidate key for the relation R.
c) Find a lossless join decomposition of R into 3NF (also give a proof of lossless join).
d) What are the benefits of finding minimal cover of a set of FDs?
4. Use the following set of rules of inference FDs (15 points)
IR1 (reflexive rule) if X Ê Y, then X ® Y
IR2 (augmentation rule) if X ® Y then XZ ® YZ
IR3 (transitive rule) if {X ® Y, Y ® Z} then X ® Z
To prove the following rules for functional dependencies
1. {Xà Y, Yà Z} |== X à YZ
2. {X à Y, X à W, WY ® Z} |== X ® Z
3. {X à W, Y à Z} |== XY® Z
4. Use the following set of rules of inference FDs (20 points)
IR1 (reflexive rule) if X Ê Y, then X ® Y
IR2 (augmentation rule) if X ® Y then XZ ® YZ
IR3 (transitive rule) if {X ® Y, Y ® Z} then X ® Z
To prove the following rules for functional dependencies
1. X à Y, X à W, WY à Z |== X ® Z
2. Xà Y and Y Ê WZ |== X à Z