1 / 31

Database Design 1: Normalization

Database Design 1: Normalization. Concepts – Chapter 5. Learning Objectives. Introduce normalization Introduce anomalies, dependencies, and normal forms Introduce how to normalize a relation in First normal form Second normal form Third normal form. Database Design.

stella
Télécharger la présentation

Database Design 1: Normalization

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. Database Design 1: Normalization Concepts – Chapter 5

  2. Learning Objectives • Introduce normalization • Introduce anomalies, dependencies, and normal forms • Introduce how to normalize a relation in • First normal form • Second normal form • Third normal form

  3. Database Design • Process of determining the content and structure of data in a database • Process: • Conceptual or logical design • E-R Modeling • Normalization • Physical Design

  4. Normalization • Process used to identify the existence of potential problems, known as update anomalies in the design of a relational database • Normal forms are used to correct these problems (or anomalies) and remove them by converting tables into various normal forms

  5. Types of Anomalies • Update • Change one attribute value and either the DBMS must make more than one change to the database or else the database ends up containing inconsistent data • Insertion/Addition • Can’t add a row to a relation because you don’t know the entire primary key value • Deletion • Delete data from a tableand unintentionally loose other critical data

  6. Normal Forms • First Normal Form (1NF) • Second Normal Form (2NF) • Third Normal Form (3NF) • Boyce-Codd Normal Form (BCNF) • Fourth Normal Form (4NF) • Fifth Normal Form (5NF) • Domain Key Normal Form (DKNF)

  7. Normalization • Table in first normal form better than table not in first normal form • Table in second normal form better than table in first normal form, and so on • Goal: new collection of tables that is free of update anomalies Concepts of Database Management

  8. Types of Dependencies • Functional • Partial • Transitive

  9. Functional Dependence • Column B is functionally dependent on column A • Each value for A is associated with exactly one value of B A → B • A functionally determines B Concepts of Database Management

  10. Figures 5.3-5.4: Functional Dependence Example Rep Table Where LastName can determine record Rep Table Where LastName cannot determine record

  11. First Normal Form • Definition: • A relation is in 1NF if it does not contain any repeating groups • If it contains repeating groups, then it is an unnormalized relation • To convert a relation to 1NF: • Need to remove repeating group • Expand primary key to include primary key of repeating group

  12. Figure 5.5: 1NF Example Unnormalized Table

  13. Figure 5.6: 1NF Example (con’t.) Conversion to 1NF

  14. Example 1 Relation (Proj_Num, Proj_Name, (Emp_Num, Emp_Name, Job_Class, Chg_Hour, Hours)) What is the repeating group? What is the primary key of the new relation?

  15. Second Normal Form • A relation is in 2NF if it is in 1NF and it does not contain any partial dependencies. • To convert a relation to 2NF: • Identify functional dependency for every attribute in the relation • If necessary, create new relations and place each attribute in a relation so that the attribute is functionally dependent on the entire primary key

  16. Figure 5.7:Second Normal Form

  17. Second Normal Form (continued) Orders (OrderNum, OrderDate, PartNum, Description, NumOrdered, QuotedPrice) • Functional dependencies: OrderNum→OrderDate PartNum→ Description OrderNum, PartNum→NumOrdered, QuotedPrice Concepts of Database Management

  18. Second Normal Form (continued) FIGURE 5-9: Conversion to second normal form Concepts of Database Management

  19. Dependency Diagram • Dependency diagram: arrows indicate all functional dependencies • Arrows above boxes: normal dependencies • Arrows below boxes: partial dependencies Concepts of Database Management

  20. Second Normal Form (continued) FIGURE 5-8: Dependences in the Orders table Concepts of Database Management

  21. Example 1 cont… • Is it in 2NF? • What are the dependencies? • What is the solution after dependencies are removed?

  22. Third Normal Form • A relation is in 3NF if it is in 2NF and every determinant is a candidate key (or does not have a transitive dependency). • To convert to 3NF: • Remove attributes that depend on the non-candidate-key determinant and place them into a new relation with the determinant as the primary key

  23. Third Normal Form • Customer (CustomerNum, CustomerName, Balance, CreditLimit, RepNum, LastName, FirstName) • Functional dependencies: • CustomerNum→ CustomerName, Balance, CreditLimit • RepNum→LastName, FirstName Concepts of Database Management

  24. Figure 5.10: Sample Customer Data

  25. Figure 5.12: 3NF Example

  26. Figure 5.13: Incorrect Decomposition Example

  27. Example 1 cont… • Is it in 3NF? • Are there transitive dependencies? • What is the solution after transitive dependencies are removed?

  28. Incorrect Decompositions • Decomposition must be done using method described for 3NF • Incorrect decompositions can lead to tables with the same problems as original table Concepts of Database Management

  29. Incorrect Decompositions (continued) FIGURE 5-13: Incorrect decomposition of the Customer table Concepts of Database Management

  30. Incorrect Decompositions (continued) FIGURE 5-13: Incorrect decomposition of the Customer table (continued) Concepts of Database Management

  31. Incorrect Decompositions (continued) FIGURE 5-14: Second incorrect decomposition of the Customer table (continued) Concepts of Database Management

More Related