Chapter 5 Normalization of Database Tables

1. Chapter 5Normalization of Database Tables Database Systems: Design, Implementation, and Management Peter Rob & Carlos Coronel

2. In this chapter, you will learn: • What normalization is and what role it plays in database design • 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 E-R modeling are used concurrently to produce a good database design • That some situations require denormalization to generate information efficiently

3. Database Tables and Normalization • Normalization is a process for assigning attributes to entities. It • reduces data redundancies • helps eliminate data anomalies. • produces controlled redundancies to link tables • Normalization works through a series of stages called normal forms: • First normal form (1NF) • Second normal form (2NF) • Third normal form (3NF) • Fourth normal form (4NF) • The highest level of normalization is not always desirable.

4. Database Tables and Normalization • The Need for Normalization • Case of a Construction Company • Building project -- Project number, Name, Employees assigned to the project. • Employee -- Employee number, Name, Job classification • The company charges its clients by billing the hours spent on each project. The hourly billing rate is dependent on the employee’s position. • Periodically, a report is generated. Table 5.1 • The easiest way to generate the required report might seem to be a table whose contents correspond to the reporting requirements. Figure 5.1

7. Database Tables and Normalization • Need for Normalization:Problems with the Figure 5.1 • The project number is intended to be a primary key, but it contains nulls. • The table displays data redundancies. • The table entries invite data inconsistencies. • The data redundancies yield the following anomalies: • Update anomalies. (modify JOB_CLASS for Employee 105) • Insertion anomalies. (a new Employee not yet assigned) • Deletion anomalies. ( Employee 103 quits)

8. Database Tables and Normalization • Conversion to 1NF • Repeating groups – a group of multiple entries can exist for any single key attribute occurrence. • Repeating groups must be eliminatedAny project number (PROJ_NUM) can have a group of several data entries. • A relational table must not contain repeating groups.

9. Step 1: Eliminate the Repeating Groups – Repeating groups can be eliminated by adding the appropriate entry in at least the primary key column(s). • Step 2: Identify the Primary Key • Uniquely identifies attribute values (rows) • Combination of PROJ_NUM and EMP_NUM

10. Database Tables and Normalization • Step 3: Identify all Dependencies • Dependency Diagram • The primary key components are bold, underlined, and shaded in a different color. • The arrows above entities indicate all desirable dependencies ( dependencies based on PK ) • The arrows below the dependency diagram indicate less desirable dependencies – • partial dependencies ( dependencies based on only a part of PK ) • transitive dependencies ( nonprime attribute → nonprime attribute ) • Prime attribute = Key attribute • Nonprime attribute = Nonkey attribute

11. Database Tables and Normalization • EMP_NUM → EMP_NAME, JOB_CLASS_, CHG_HOUR • PROJ_NUM → PROJ_NAME • JOB_CLASS → CHG_HOUR

12. Database Tables and Normalization • 1NF Definition • The term first normal form (1NF) describes the tabular format in which: • All the key attributes are defined. • There are no repeating groups in the table. Each row/col intersection can contain one and only one value, not set of values. • All attributes are dependent on the primary key. • All relational tables satisfy the 1NF requirements. • 1NF Drawback • Partial dependencies(EMP_NUM → EMP_NAME, JOB_CLASS_, CHG_HOUR)→→ data redundancies→→ data anomalies

13. Database Tables and Normalization • Conversion to 2NF • Step 1: Identify All Key Components • Writing each key component on a separate line, and then • writing the original key on the last line and PROJ_NUM EMP_NUM PROJ_NUM, EMP_NUM • Step 2: Identify the Dependent Attributes • Writing the dependent attributes after each new key. PROJECT ( PROJ_NUM,PROJ_NAME) EMPLOYEE ( EMP_NUM, EMP_NAME, JOB_CLASS, CHG_HOUR) ASSIGN ( PROJ_NUM, EMP_NUM, HOURS)

15. Database Tables and Normalization • 2NF Definition • A table is in 2NF if: • It is in 1NF and • It includes nopartial dependencies; that is, no attribute is dependent on only a portion of the primary key. • A table whose primary key is notcompositemust automatically be in 2NF.BECAUSE a partial dependency can exist only if a table has a composite primary key • It is still possible for a table in 2NF to exhibit transitive dependency; that is, one or more attributes may be functionally dependent on nonkey attributes. • 2NF Drawback • Transitive dependencies(JOB_CLASS → CHG_HOUR)→→ data redundancies→→ data anomalies

16. Database Tables and Normalization • Conversion to 3NF • Create a separate table with attributes in a transitive functional dependence relationship. • Step 1: Identify Each New Determinant JOB_CLASS • Step 2: Identify the Dependent Attributes JOB_CLASS → CHG_HOUR • Step 3: Remove the Dependent Attributes from Transitive Dependencies EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS) JOB (JOB_CLASS, CHG_HOUR) PROJECT (PROJ_NUM, PROJ_NAME) ASSIGN (PROJ_NUM, EMP_NUM, HOURS)

18. Database Tables and Normalization • 3NF Definition • A table is in 3NF if: • It is in 2NF and • It contains notransitive dependencies.

19. Improving the Design • Table structures are cleaned up to eliminate the troublesome initial partial and transitive dependencies • Normalizationcannot, by itself, be relied on to make good designs • It is valuable because its use helps eliminate data redundancies

20. Improving the Design (continued) • The following changes were made: • PK assignment • Naming conventions • Attribute atomicity • Adding attributes • Adding relationships • Refining PKs • Maintaining historical accuracy • Using derived attributes

21. Improving the Design • Adding relationships • Project’s manager • EMP_NUM as a FK in PROJECT. Project manager 3NF

22. Improving the Design • PK assignment • JOB_CODE • Naming conventions • JOB_CHG_HOUR • JOB_CLASS >> JOB_DESCRIPTION 2NF

23. Improving the Design 3NF • Attribute atomicity • EMP_NAME >> EMP_LNAME,EMP_FNAME,EMP_INITIAL • Adding attributes • EMP_HIREDATE JOB_CLASS

24. Improving the Design 3NF • Refining PKs • (EMP_NUM+PROJ_NUM) >> ASSIGN_NUM • Maintaining historical accuracy • ASSIGN_CHG_HOUR <<>> JOB_CHG_HOUR • Using derived attributes • ASSIGN_CHARGE = ASSIGN_HOURS × ASSIGN_CHG_HOUR

25. The Completed Database

26. The Completed Database (continued)

27. Database Tables and Normalization • Boyce-Codd Normal Form (BCNF) • A table is in Boyce-Codd normal form (BCNF) if every determinant in the table is a candidatekey. (A determinant is any attribute whose value determines other values with a row.) • If a table contains only one candidate key, the 3NF and the BCNF are equivalent. • BCNF can be violatedonly if the table contains more than one candidate key • BCNF is a special case of 3NF. • Figure 5.7 illustrates a table that is in 3NF but not in BCNF. • Figure 5.8 shows how the table can be decomposed to conform to the BCNF form.

28. A Table That Is In 3NF But Not In BCNF • A + B → C, D • C → B : Nottransitive dependencies (A nonkey attribute is the determinant of a key attribute) → → 3NF • C : Notcandidatekey → → Not In BCNF Figure 5.7

29. The Decomposition of a Table Structure to meet BCNF Requirements • A + B → C, D C → B Change the PK to A+C • A + C → B, D C → B 1NF Figure 5.8 • A + C → D • C → B

30. Sample Data for a BCNF Conversion

31. Decomposition into BCNF Figure 5.9

32. Normalization and Database Design • Normalization should be part of the design process • E-R Diagram provides macro view • Normalization provides micro view of entities • Focuses on characteristics of specific entities • A micro view of the entities within the ER diagram • Difficult to separate normalization from E-R diagramming • Two techniques should be used concurrently

33. Normalization and Database Design • Database Design and Normalization Example:(Construction Company) • Summary of Operations: • The company manages many projects. • Each project requires the services of many employees. • An employee may be assigned to several different projects. • Some employees are not assigned to a project and perform duties not specifically related to a project. • Some employees are part of a labor pool, to be shared by allproject teams. • Each employee has a (single) primary job classification. This job classification determines the hourly billing rate. • Many employees can have the same job classification.

34. Normalization and Database Design • Two Initial Entities: • PROJECT (PROJ_NUM, PROJ_NAME) [3NF ] • EMPLOYEE ( EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, JOB_DESCRIPTION, JOB_CHG_HOUR) No partial dep. Transitive dep. JOB_DESCRIPTION → JOB_CHG_HOUR [2NF ]

35. Normalization and Database Design • Three Entities After Transitive DependencyRemoved PROJECT (PROJ_NUM, PROJ_NAME) EMPLOYEE ( EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, JOB_CODE) JOB ( JOB_CODE, JOB_DESCRIPTION, JOB_CHG_HOUR) EMPLOYEE

36. The Modified ERD For A Contracting Company Because the normalization process yields an additional entity (JOB), we modify the initial ERD. M Is held by

37. Normalization and Database Design • Creation of the Composite EntityASSIGNMENT ASSIGNMENT Is held by The Final ( Implementable) ERD for the Contracting Company

38. Normalization and Database Design • Attribute ASSIGN_HOUR is assigned to the composite entity ASSIGN. • “Manages” relationship is created between EMPLOYEE and PROJECT. PROJECT (PROJ_NUM, PROJ_NAME, EMP_NUM) ASSIGNMENT(ASSIGN_NUM, ASSIGN_DATE , ASSIGN_HOURS, ASSIGN_CHG_HOUR , ASSIGN_CHARGE,EMP_NUM, PROJ_NUM) EMPLOYEE (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE) JOB (JOB_CODE, JOB_DESCRIPTION, JOB_CHG_HOUR) Manages

40. Higher-Level Normal Forms • In some databases, multiple multivalued attributes exist • 4NF Definition • A table is in 4NFif it is in 3NF and has no multiple sets of multivalued dependencies.

41. Higher-Level Normal Forms • An employee can have multiple assignments and can also be involved in multiple service organization. • EMP_SERVICE (volunteer work) and EMP_ASSIGN (assigned project)each may have many different values. • The table contain two sets of multivalued dependencies. Independent 1

42. A Set of Tables in 4NF • The solution is to eliminate the problems caused by independentmultivalued dependencies.

44. Denormalization • Creation of normalized relations is important database design goal • Processing requirements should also be a goal • If tables decomposed to conform to normalization requirements • Number of database tables expands • Joininglarger number of tablestakes additional disk input/output (I/O) operations and processing logic • Reduces system speed

45. Denormalization transitive dep. • Normalization is only one of many database design goals. • Normalized (decomposed) tables require additional processing, ( Join : additional I/O operations )reduce system speed. • CUSTOMER( CUS_NUM, CUS_NAME, … ,ZIP_CODE, CITY) Is it really practical? ZIP(ZIP_CODE, CITY) • Some degree of denormalization →→ increase processing speed

46. Denormalization • Normalization purity is often difficult to sustain in the modern database environment. • The conflict between design efficiency, information requirements, and processing speed are often resolved through compromises that include denormalization. • In fact, we used a 2NF structure in the JOB table (Fig.5.6) to decrease the likelihood of referential integrity violations. • JOB table • Original PK: JOB_CLASS • New PK: JOB_CODE • EMPLOYEE table • Original FK: JOB_CLASS • New FK: JOB_CODE • Use denormalizationcautiously.

47. The Initial 1NF Structure

48. Identifying the Possible PK Attributes

49. Table Structures Based On The Selected PKs Foreign key