Normalization

1. Normalization A technique for identifying table structures that have potential maintenance problems

2. Normalization • Normalization is a set of formal conditions that assure that a database is maintainable. • The results of a well executed normalization process are the same as those of a well planned E-R model

3. PROCESS OF DATA NORMALIZATION • ELIMINATE REPEATING GROUPS Make a separate table for each set of related attributes and give each table a primary key. • ELIMINATE REDUNDANT DATA If an attribute depends on only part of a multivalued key, remove it to a separate table. • ELIMINATE COLUMNS NOT DEPENDENT ON KEY If attributes do not contribute to a description of the key, remove them to a separate table. Database Programming and Design

4. PROCESS OF DATA NORMALIZATION • ISOLATE INDEPENDENT MULTIPLE RELATIONSHIPS No table may contain two or more 1:n or n:m relationships that are not directly related. • ISOLATE SEMANTICALLY RELATED MULTIPLE RELATIONSHIPS There may be practical constraints on information that justify separating logically related many-to-many relationships. Database Programming and Design

5. Anomalies A table anomaly is a structure for which a normal database operation cannot be executed without information loss or full search of the data table Insertion Anomaly • Deletion Anomaly • Update or Modification Anomaly

6. Normal Forms Relational theory defines a number of structure conditions called Normal Forms that assure that certain data anomalies do not occur in a database.

7. Normal Forms 1NFKeys; No repeating groups 2NFNo partial dependencies 3NFNo transitive dependencies BCNFDeterminants are candidate keys 4NFNo multivalued dependencies

8. Premier Products Order Form(Company Order History) Order # 12003 Date Oct 1, 1997 Oklahoma Retail Company 1111 Asp Norman Description Code Qty Price Amount 1. Footballs 21 6 25.00 150 2. Sweat Shirts 44 20 15.00 300 3. Shorts 37 10 12.00 120 Total 570

9. 0nf: Remove titles and derived quantities Order # 12003 Date Oct 1, 1997 Oklahoma Retail Company 1111 Asp Norman Description Code Qty Price Amount 1. Footballs 21 6 25.00 150 2. Sweat Shirts 44 20 15.00 300 3. Shorts 37 10 12.00 120 Total 570

10. 0 Normal Form • Remove titles and derived quantities • Schema notation HISTORY(CustName, CustAddr, CustCity {OrderNum, OrderDate {ProdDescr, ProdCode, QtyOrdered, OrderPrice}}

11. 1st Normal Form Add keys Remove repeating groups

12. 1st Normal Form • Add Keys for embedded entities • Remove Repeating Groups HISTORY(CustID, CustName, CustAddr, CustCity {OrderNum, OrderDate {ProdDescr, ProdCode, QtyOrdered, OrderPrice}}

13. 1st Normal Form • Add Keys for embedded entities • Remove Repeating Groups • Create a table for each embedded entity, from the outside for nested groups • Insert foreign keys and junction tables CUSTOMER(CustID, CustName, CustAddr, CustCity) ORDER(OrderNum, CustID, OrderDate {ProdDescr, ProdCode, QtyOrdered, OrderPrice})

14. 1st Normal Form CUSTOMER(CustID, CustName, CustAddr, CustCity) ORDER(OrderNum, CustID, OrderDate) PRODUCT(ProdDescr, ProdCode,) ORDER-PRODUCT(OrderNum, ProdCode, QtyOrdered, OrderPrice)

15. 1st Normal Form CUSTOMER ORDER PRODUCT

16. 1NF(Keys, No Repeating Groups) Table contains multi-valued attributes. TABLE { ATTRIBUTES} TABLE ?? ATTR-TABLE

17. 2nd Normal Form No partial dependencies (an attribute has a partial dependency if it depends on part of a concatenated key)

18. 2nd Normal Form ROSTER(StuID, ZAPNum, StudentName, CourseTitle, CourseGrade) • Remove partial dependencies STUDENT(StuID, StudentName) SECTION(ZAPNum, CourseTitle) STUDENT-SECTION(StuID, ZAPNum, CourseGrade)

19. 2nd Normal Form ROSTER STUDENT SECTION STUDENT-SECTION

20. 2NFNo partial dependencies Table has data from several connected tables. TABLE TABLE ?? ??

21. 3rd Normal Form No transitive dependencies (a transitive dependency is an attribute that depends on other non-key attributes)

22. 3rd Normal Form Note: a transitive dependency arises when attributes from a second entity appear in a given table. SECTION(ZAPNum, RoomNum, Day, Time, CourseTitle, HoursCredit)

23. 3rd Normal Form SECTION(ZAPNum, RoomNum, Day, Time, CourseID ,CourseTitle, HoursCredit) SECTION(ZAPNum, RoomNum, Day, Time, CourseID) COURSE(CourseID, CourseTitle, HoursCredit)

24. 3NFNo transitive dependencies Table contains data from an embedded entity with non-key attributes. SUB-TABLE TABLE TABLE ?? SUB-TABLE BCNF is the same, but the embedded table may involve key attributes.

25. Boyce Codd Normal Form Every determinant is a candidate key

26. BCNF BCNF dependenceies are like 3nf dependencies but they involve some key attributes Note: BCNF often arises when a 1:m relationship is modeled as a m:n relationship

27. BCNF SALESMAN-CUST(SalesID, CustID, Commission) SALESMAN(SalesID, Commission) CUSTOMER(CustID, SalesID)

28. 4th Normal Form No multi-valued dependencies

29. 4th Normal Form Note: 4th Normal Form violations occur when a triple (or higher) concatenated key represents a pair of double keys

30. 4th Normal Form

31. 4th Normal Form Multuvalued dependencies

32. 4th Normal Form INSTR-BOOK-COURSE(InstrID, Book, CourseID) COURSE-BOOK(CourseID, Book) COURSE-INSTR(CourseID, InstrID)

33. 4NF(No multivalued dependencies) Independent repeating groups have been treated as a complex relationship. TABLE TABLE TABLE TABLE TABLE TABLE