1 / 34

Chapter 5

Chapter 5. Normalization An Normalization example. Learning Objectives. What normalization is and what role it plays in the database design process About the normal forms 1NF, 2NF, 3NF, BCNF, and 4NF How normal forms can be transformed from lower normal forms to higher normal forms

autumn-summers
Download Presentation

Chapter 5

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 5 • Normalization • An Normalization example INSS 651

  2. Learning Objectives • What normalization is and what role it plays in the database design process • About the normal forms 1NF, 2NF, 3NF, BCNF, and 4NF • How normal forms can be transformed from lower normal forms to higher normal forms • That normalization and ER modeling are used concurrently to produce a good database design • That some situations require denormalization to generate information efficiently INSS 651

  3. Normalization A process for evaluating and correcting table structure Minimize data redundancy Eliminate Anomalies INSS 651

  4. Is Normalization Necessary? NO But it is helpful to maintain data integrity and consistency INSS 651

  5. Anomalies • Update—requires update in multiple locations • Deletion—A deletion may lose important information • Insertion—Requires complete definitions, ie does see page 187 (an employee can not be entered unless he is assigned a project INSS 651

  6. Normalization Process • 1st NF • 2nd NF • 3rd NF • Almost for 90-98% application 3rd NF is sufficient INSS 651

  7. Dependency When an attribute value depends on attribute B then B is dependent on A A---B or values of B can be determined by value of A, reverse may or may not be true Ex: ssn--Name SSN, CID--Grade INSS 651

  8. Un-normalized relation Remove REPEATING groups 1st NF Remove PARTIAL dependency 2nd NF Remove TRANSIENT dependency 3rd NF Every determinant is a candidate key Boyce-CODD NF If we can convert a relation into 3NF almost 90-98% of anomalies are removed INSS 651

  9. The Need for Normalization (continued) • Structure of data set in Figure 5.1 does not handle data very well • The table structure appears to work; report is generated with ease • Unfortunately, the report may yield different results, depending on what data anomaly has occurred INSS 651

  10. 1st NF • Remove repeating groups ASSIGNMENT (Proj_num, proj_name(Emp_num,E_name,job_class,chg_hours,Hour)) INSS 651

  11. A Dependency Diagram: First Normal Form (1NF) INSS 651

  12. Second Normal Form (2NF) Conversion Results INSS 651

  13. Third Normal Form (3NF) Conversion Results INSS 651

  14. Un-normalized form A relation is in un-normalized form, if it contains repeating group Typically shown in parentheses Ex: PART NO   DESC.     VENDOR-NAME   ADDRESS  UNIT-COST 1234            LOGIC INTEL                   SAN JOSE    150.00                      chip           LSI LOGIC            SAN JOSE    120.00 5678     MEMORY   INTEL                  SAN JOSE     50.00 chip INSS 651

  15. SUPPLIER (Part_no, Part_DESC, (Vendor_name, Vendor_address, Unit_cost)) INSS 651

  16. Another Way (Part_NO, V_NAME)-> Unit_cost Part_NO->P_Desc (Partial dependency) V-Name->V_DESC (Partial Dependency) Part_NO P-DESC V_NAME V_ADDRESS UNIT_COST INSS 651

  17. 1st NF A relation is in 1st NF if it does NOT contain any repeating groups (Part_no, Part_DESC, (Vendor_name, Vendor_address, Unit_cost)) 1st NF..remove repeating groups Break it into TWO relations One without repeating group and ONE with repeating group AND PK of other relation S1 (Part_no, Part_DESC) S2 (Vendor_name, Part_no, Vendor_address, Unit_cost INSS 651

  18. 2nd NF A relation is in 2nf NF if it is in 1stNF and it does not contain any partial dependency Partial dependency: A partial dependency exists if an attribute is dependent ONLY on PART of the PK and the WHOLE PK We must examine each relation for partial dependency NOTE: A partial dependency can only exist if there are more than ONE attribute as PK INSS 651

  19. S1 (Part_no, Part_DESC) S2 (Vendor_name, Part_no, Vendor_address, Unit_cost Note S1 is already in 2nd NF since there is only attribute as PK In S2: Question is Vendor_address dependent on BOTH vendor_name AND Part_NO? Question is Unit_price dependent on BOTH vendor_name AND Part_NO? INSS 651

  20. Question is Vendor_address dependent on BOTH vendor_name AND Part_NO? Answer: NO Give me vendor_no and I can find vendor_address, we do NOT need Part_No to know vendor_address, ie Vendor_address depends ONLY Vendor_name, hence the partial dependency INSS 651

  21. Question is Unit_price dependent on BOTH vendor_name AND Part_NO? YES if you examine the table, price changes with vendor and part_no, ie price depnds on both Part_no AND which vendor supplies it INSS 651

  22. Remove Partial Dependency VENDOR _ADDRESS VENDOR_name UNIT_PRICE PART# Create TWO tables: One with Partial dependency and other without it S21 (Vendor_name, vendor_address) S22(Vendor_name, Part_no, Unit_price) INSS 651

  23. 3rd NF A relation is in 3rd NF if it is in 2nd NF and it does not contain any transitive dependency Transitive dependency: A transitive dependency exists when some of the non-key attributes are dependent on other non-key attributes INSS 651

  24. So far we have three relations that are in at least 2nd NF S1 (Part_no, Part_DESC) S21 (Vendor_name, vendor_address) S22(Vendor_name, Part_no, Unit_price) S1, S21 & S22 are also in 3rd NF since there is ONLY ONE non_key attribute and transitive dependency can NOT exist INSS 651

  25. ERD VENDOR PART PART-SUPPLIED INSS 651

  26. Q6/p 184 A Using notation from the book • (C1, C3)- C2,C4,C5 (i.e., C2, C4, C5) are functionally dependent on C1 and C3 Above relation is in at least 1stNF, since there are No repeating groups C1C2 there is PARTIAL dependency since C2 depends on PART of the PK and the whole PK C4--C5 (transitive dependency since C5 ( a non-PK attribute) depends on another non-PK attribute (C4) INSS 651

  27. Part b INSS 651

  28. Part c INSS 651

  29. Q8/P187 Table P5.8 Sample ITEM Records INSS 651

  30. Problem 8 Solution BLDG_MANAGER ITEM_ID ITEM_DESCRIPTION BLDG_ROOM BLDG_CODE BLDG_NAME INSS 651

  31. BLDG_MANAGER ITEM_ID ITEM_DESCRIPTION BLDG_ROOM BLDG_CODE BLDG_NAME Problem 9 Solution: All tables in 3NF ITEM_ID ITEM_DESCRIPTION BLDG_ROOM BLDG_CODE BLDG_CODE BLDG_NAME EMP_CODE EMP_CODE EMP_FNAME EMP_INITIAL EMP_LNAME INSS 651

  32. INSS 651

  33. Denormalization • Reversing normalization • i.e from 3rd NF to 2nd NF • Or 2nd to 1st NF INSS 651

  34. Convert into 3NF INVOICE (Inv_num, cust_num,lastname,Firstname,street,city,state,zip,date,(partnum, description,price,numshipped)) INSS 651

More Related