CHAPTER 4 PART C LOGICAL DATABASE DESIGN AND

DATA NORMALIZATION � Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data � The process of decomposing relations with anomalies to produce smaller, well-structured relations Chapter 4 Copyright © 2016 Pearson Education, Inc. 4 -2

FUNCTIONAL DEPENDENCIES Functional Dependency: The value of one set of attributes (the determinant) determines the value of another attribute. X functionally determines Y: X Y, where X is called a determinant. Problem 4 -33 (Fig. 4 -33), p. 195, 196. Chapter 4 Copyright © 2016 Pearson Education, Inc. 4 -33

FIGURE 4 -2 Chapter 4 Copyright © 2016 Pearson Education, Inc. 4 -4 4

FIGURE 4 -23 Chapter 4 Copyright © 2016 Pearson Education, Inc. 4 -5 5

CANDIDATE KEYS � Candidate Key: Attribute or a set of attributes that uniquely identifies a row. � Each non-key field is functionally dependent on every candidate key � One of the candidate keys will become the primary key � E. g. , perhaps there is both credit card number and SS# in a table…in this case both are candidate keys. � Primary Chapter 4 vs. candidate key: Fig. 4 -16, p. 171 Copyright © 2016 Pearson Education, Inc. 4 -66

Figure 4. 22 Steps in normalization 3 rd normal form is generally considered sufficient

FIRST NORMAL FORM A relation is in 1 NF if it contains no multivalued attributes; i. e. , every attribute value is atomic. Fig. 4 -25 is not in 1 st Normal Form (multivalued attributes) it is not a relation. Fig. 4 -26 is in 1 st Normal form. All relations are in 1 st Normal Form. Chapter 4 Copyright © 2016 Pearson Education, Inc. 4 -88

ANOMALIES IN EMPLOYEE 2 (FIG. 4 -2 B) Insertion–can’t enter a new employee without having the employee take a class (or at least empty fields of class information) � Deletion–if we remove employee 140, we lose information about the existence of a Tax Acc class � Modification–giving a salary increase to employee 100 forces us to update multiple records � Why do these anomalies exist? Because there are two themes (entity types) in this one relation. This results in data duplication and an unnecessary dependency between the entities. Chapter 4 Copyright © 2016 Pearson Education, Inc. 4 -99

SECOND NORMAL FORM n A relation is in 2 NF if it is in 1 NF and every non-key attribute is fully functionally dependent on the primary key. No partial functional dependencies (i. e. , every non-key attribute must be defined by the entire key, not by only part of the key). A B C D: A B C, A D. 2 NF? n Is Fig. 4 -23 b (p. 181) in 2 NF? n n Chapter 4 Copyright © 2016 Pearson Education, Inc. 4 -10 10

11 SECOND NORMAL FORM (2 NF) A relation is in 2 NF if Primary key consists of a single attribute; or Primary key consists of all attributes. Fig. 4 -2 b Fig. 4 -1 + Fig. 4 -7 Chapter 4 Copyright © 2016 Pearson Education, Inc. 4 -11

FIGURE 4 -1 Chapter 4 Copyright © 2016 Pearson Education, Inc. 4 -12 12

FIGURE 4 -7 Chapter 4 Copyright © 2016 Pearson Education, Inc. 4 -13 13

14 EMPLOYEE-COURSE RELATIONS IN 2 NF Emp. ID Name Dept. Name Salary Emp. ID

THIRD NORMAL FORM A relation is in 3 NF if it is in 2 NF and no transitive dependencies exists, where a transitive dependency is a functional dependency between non-key attributes. A B C D: A B C, C D. 2 NF? 3 NF? Solution: Non-key determinant with transitive dependencies go into a new table; non-key determinant becomes primary key in the new table and stays as foreign key in the old table. Chapter 4 Copyright © 2016 Pearson Education, Inc. 4 -15 15

16 Relation with transitive dependency (a) SALES relation with simple data Chapter 4 Copyright

17 Relation with transitive dependency Cust. ID Name Cust. ID Salesperson Cust. ID Region Chapter 4 BUT Salesperson Region Transitive dependency (not 3 rd NF) Copyright © 2016 Pearson Education, Inc. 4 -17

18 Removing a transitive dependency (a) Decomposing the SALES relation Chapter 4 Copyright ©

Relations in 3 NF 19 Salesperson Region Cust. ID Name Cust. ID Salesperson Now, there are no transitive dependencies… Both relations are in 3 rd NF Chapter 4 Copyright © 2016 Pearson Education, Inc. 4 -19

Table with multivalued attributes, not in 1 st normal form Note: This is NOT

Table with no multivalued attributes and unique rows, in 1 st normal form Note:

ANOMALIES IN THIS TABLE Insertion–if new product is ordered for order 1007 of existing customer, customer data must be reentered, causing duplication � Deletion–if we delete the Dining Table from Order 1006, we lose information concerning this item’s finish and price � Update–changing the price of product ID 4 requires update in multiple records � Why do these anomalies exist? Because there are multiple themes (entity types) in one relation. This results in duplication and an unnecessary dependency between the entities. Chapter 4 Copyright © 2016 Pearson Education, Inc. 4 -22

Figure 4 -27 Functional dependency diagram for INVOICE Order. ID Order. Date, Customer. ID, Customer. Name, Customer. Address Customer. ID Customer. Name, Customer. Address Product. ID Product. Description, Product. Finish, Product. Standard. Price Order. ID, Product. ID Order. Quantity Therefore, NOT in 2 nd Normal Form Chapter 4 Copyright © 2016 Pearson Education, Inc. 4 -23

Figure 4 -28 Removing partial dependencies Getting it into Second Normal Form Partial dependencies are removed, but there are still transitive dependencies Chapter 4 Copyright © 2016 Pearson Education, Inc. 4 -24