Database Design 1: Normalization

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