160 likes | 291 Views
BACS 485. Normalization. Data Anomalies. WORKER ( WORKER-ID , NAME, SKILL-TYPE, SUPV-ID, BLD-ID). Normalization Process. 0NF. ORDERS ( ORDER-NUM , ORDER-DATE, {PART-NUM, PART-DESC, QTY-ORD, PRICE}). 1NF. ORDERS ( ORDER-NUM , ORDER-DATE, PART-NUM , PART-DESC, QTY-ORD, PRICE). 1NF.
E N D
BACS 485 Normalization
Data Anomalies WORKER (WORKER-ID, NAME, SKILL-TYPE, SUPV-ID, BLD-ID)
0NF ORDERS (ORDER-NUM, ORDER-DATE, {PART-NUM, PART-DESC, QTY-ORD, PRICE})
1NF ORDERS (ORDER-NUM, ORDER-DATE, PART-NUM, PART-DESC, QTY-ORD, PRICE)
1NF ORDERS (ORDER-NUM, ORDER-DATE, PART-NUM, PART-DESC, QTY-ORD, PRICE) ORDER-NUM, PART-NUM ---> QTY-ORD ORDER-NUM ---> ORDER-DATE PART-NUM ---> PART-DESC, PRICE
2NF ORDERS (ORDER-NUM, ORDER-DATE) PART (PART-NUM, PART-DESC, PRICE) ORDER-LINE (ORDER-NUM, PART-NUM, QTY-ORD) Part Orders Order_Line
3NF Problems CUSTOMER (CUST-NUM, CUST-NAME, CUST-ADDR, SALES-NUM, SALES‑NAME) Customer CUST-NUM ---> CUST-NAME, CUST-ADDR, SALES-NUM, SALES-NAME SALES-NUM ---> SALES-NAME
3NF Problems CUST-NUM ---> CUST-NAME, CUST-ADDR, SALES-NUM, SALES-NAME SALES-NUM ---> SALES-NAME
3NF CUSTOMER (CUST-NUM, CUST-NAME, CUST-ADDR, SALES-NUM) SALES-REP (SALES-NUM, SALES-NAME)
BC/NF Problem FACULTY (NAME, DEPT, OFFICE, RANK, DATE-HIRED) OFFICE ---> DEPT NAME, DEPT ---> OFFICE, RANK, DATE-HIRED NAME, OFFICE ---> DEPT, RANK, DATE-HIRED
BC/NF Problem OFFICE ---> DEPT NAME, DEPT ---> OFFICE, RANK, DATE-HIRED NAME, OFFICE ---> DEPT, RANK, DATE-HIRED
BC/NF Solution FAC-LOC (OFFICE, DEPT) FACULTY (NAME, OFFICE, RANK, DATE-HIRED) • The NAME,OFFICE candidate key was chosen as the primary key because the NAME,DEPT candidate key would not be in BCNF. In fact, it would not even be in 2NF since there would be a partial dependency between OFFICE and DEPT.
0NF Relation FACULTY (FAC-ID, {STU-ID}, {COMMITTEE-CODE})
Normalized? FACULTY (FAC-ID, STU-ID, COMMITTEE-CODE) FAC-NAME --->> STU-ID FAC-NAME --->> COMMITTEE-CODE
5NF FAC-STU (FAC-ID, STU-ID) FAC-COMM (FAC-ID, COMMITTEE-CODE)