CHAPTER 5 Normalization Of Database Tables Part II

CHAPTER 5 Normalization Of Database Tables (Part II: The Process)

Chapter 5: Normalization Of Database Tables Part 2: Normalization Process Objectives • How to undertake the process of normalization. Data Redundancies 0 NF/UNF Normalization 1 NF 2 NF Denormalization 3 NF 2

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process UNF (1 Table) 1)Repeat Group 2)PK is not defined 1 NF -Have repeating group -PK not defined 1)Remove Repeat Group (1 or 2 Tables) 2)Defined PK composite PK consist of attributes -No repeating group Test for partial dependency If (exist) (a b …. TD) 1 (a ……. TD) 2 (b …. … TD) 3 (a, b x, y) (a c, d) (b z) (c d) Relation/table Format 2 NF (2 or 3 Tables) (more then 1 table) Test for transitive dependency If (exist) 3 NF (3 or 4 Tables) -PK defined -Test partial dependency -No repeating group -PK defined -No partial dependency -Test transitive dependency -No repeating group -PK defined -No partial dependency -No transitive dependency

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: First Normal Form (1 NF) First Normal Form 1 NF = A relation in which the intersection of each row and column contains one and only one value. A relation is in 1 NF if every attribute for every tuple have a value and domain for each attribute can not be simplified anymore. 4

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: UNF 1 NF • • • Nominate an attribute or group of attributes to act as the key for the unnormalized table. Identify the repeating group(s) in the unnormalized table which repeats for the key attribute(s). Remove the repeating group: – Entering appropriate data into the empty columns of rows containing the repeating data • Fill the blanks by duplicating the non repeating data, where required. • This approach is commonly referred as ”flattening table”. • This approach will produce redundancy in a relationship, but it can be eliminated in higher normalization process. 5

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: UNF 1 NF • Example: Dream. Home Case Study • A collection of Dream. Home leases (rent) form is shown in Figure 2. The lease on top is for a client called Rannia who is leasing a property in Skudai, Johor, which is owned by Dollah. For this worked example, we assume that a client rents a given property only once and cannot rent more than one property at any one time. • Sample data is taken from two leases for two different clients called Rannia and Amat and is transformed into table format with rows and columns, as shown in Figure 3. This is an example of unnormalized table. 6

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: UNF 1 NF Figure 2 : Collection of Dream Home leases (rent) form Page 2 DREAMHOUSE LEASE Client Rental Information Date : 28/02/2007 Client Name : Ahmad House No Client Number : CR 56 Page 1 Alamat Rumah Mula Sewa Tamat Sewa DREAMHOUSE LEASE No Client. Harga Rental Information Sewa Date : 28/02/2007 Nama Pemilik Client Name : Rannia PG 04 Subang Jaya, Selangor. House Client Number : CR 76 1/7/93 House PG 16 Address No Address 1/9/00 Pasir Gudang, Johor. 31/8/00 750 C 040 Karim Fendi Rent Start 1/9/01 Rent Finish 850 Monthly Rent C 093 Owner No Kasim Selamat Name PG 04 Skudai, Johor 1/7/93 31/8/00 750 C 040 Dolah PG 16 Ampang, Selangor 1/9/00 1/9/01 850 C 093 Abdullah 7

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: UNF 1 NF Figure 3 : CLIENTRENTAL UNF client. NO c. NAME house. NO house. ADD rent. STA RT rent. FINIS H rent CR 76 Rannia PG 04 Skudai, Johor. 1/7/93 31/8/00 750 C 040 Dolah PG 16 Ampang, Selangor 1/9/00 1/9/01 850 C 093 Abdullah PG 04 Skudai, Johor. 20/3/90 19/6/93 750 C 040 Dolah PG 36 Kuantan, Pahang. 21/6/93 3/1/00 1000 C 093 Abdullah PG 16 Ampang, Selangor. 25/1/00 30/8/00 850 C 093 Abdullah CR 56 Ahmad owner. NO o. NAME Key attribute: client. NO, house. NO Repeating group in the unnormalized table as the property rented details, which repeat for each client: Repeating Group: ( house. NO, house. ADD, rent. START, rent. FINISH, rent, owner. NO, o. NAME) 8

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: UNF 1 NF Key attribute: client. No, house. NO Repeating group in the unnormalized table as the property rented details, which repeat for each client: Repeating Group: (house. NO, house. ADD, rent. START, rent. FINISH, rent, owner. NO, o. NAME) • There are multiple value at the intersection of certain rows and columns. • e. g. there are two value for house. No (PG 4 and PG 16) for the client Rannia. • To transform an unnormalized table into 1 NF, we ensure that there is a single value at the intersection of each row and column. This is achieved by removing the repeating group. • With this approach, remove the repeating group (house rented details) by entering the appropriate client data into each row. • The resulting First Normalize Form (1 NF) CLIENTRENTAL relation is shown in Figure 4. 9

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: UNF 1 NF Figure 4 : 1 NF CLIENTRENTAL relation client. NO house. NO c. NAME house. ADD rent. STA RT rent. FINIS H rent CR 76 PG 04 Rannia Skudai, Johor. 1/7/93 31/8/00 750 C 040 Dolah CR 76 PG 16 Rannia Ampang, Selangor 1/9/00 1/9/01 850 C 093 Abdullah CR 56 PG 04 Ahmab Skudai, Johor. 20/3/90 19/6/93 750 C 040 Dolah CR 56 PG 36 Ahmad Kuantan, Pahang. 21/6/93 3/1/00 1000 C 093 Abdullah CR 56 PG 16 Ahmad Ampang, Selangor. 25/1/00 30/8/00 850 C 093 Abdullah owner. NO o. NAME CLIENTRENTAL (client. NO, house. NO, c. NAME, house. ADD, rent. Start, rent. Finish, rent, owner. NO, o. NAME) Primary Key for the Client. Rent relation is a composite key that are client. NO and house. NO 10 Client. Rent relation is in 1 NF as there is a single value at the intersection of each row and columns.

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: UNF 1 NF • Relationship in Figure 4 contains data that describes client, house for rent and owner of the house, which is repeated for several times. • As a consequences, the CLIENTRENTAL relation contains significant data redundancy. • If implemented, the 1 NF relation would be subject to the update anomalies. • To remove some of these, transform 1 NF 2 NF 11

Chapter 5: Normalization Of Database Tables The decomposition of the Client. Rental UNF 2 NF Unnormalized Form (UNF) CLIENTRENTAL (client. NO, house. NO, c. NAME, house. Add, rent. START, rent. FINISH, rent, owner. NO, o. NAME) First Normal Form (1 NF) CLIENTRENTAL (client. NO, house. NO, c. NAME, house. Add, rent. START, rent. FINISH, rent, 1 NF owner. NO, o. NAME) House. Owner Client Rental House. For. Rent Owner 12

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: Second Normal Form (2 NF) Second Normal Form 2 NF = A relation that is in 1 NF and every non-PK attribute is fully functionally depends on the PK. • Based on the concept of partial dependency. – Dependencies based on only a part of composite primary key • 2 NF applies to relations with composite keys, that is, relations with PK composed of two or more attributes. • A relation with a single-attribute PK is automatically in at least 2 NF. 13

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: 1 NF 2 NF 1. Identify the primary key for the 1 NF relation. 2. Identify the functional dependencies in the relation. a. Draw functional dependencies diagram b. Write functional dependencies in relational schema 3. If partial dependencies exist on the primary key remove them by placing them in a new relation along with a copy of their determinant. 14

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: 1 NF 2 NF Figure 5 : Functional dependencies diagram for the CLIENTRENTAL relation client. NO house. NO c. NAME house. ADD rent. START rent. FINISH rent owner. NO o. NAME (Primary Key) fd 1 (Partial dependency) fd 2 fd 3 (Partial dependency) fd 4 (Transitive dependency) 15

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: 1 NF 2 NF • As shown in Figure 5, CLIENTRENTAL relation has the following functional dependencies: • Relational Schema CLIENTRENTAL(client. NO, house. NO, c. NAME, house. ADD, rent. START, rent. FINISH, rent, owner. NO, o. NAME) • Functional dependencies – fd 1 client. NO, house. NO rent. START, rent. FINISH – fd 2 client. NO c. NAME – fd 3 house. NO house. ADD , rent, owner. NO, o. NAME (PK) (PD) Write functional dependencies in relational schema for the CLIENTRENTAL relation 16

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: 1 NF 2 NF • There are partial dependencies on the Primary Key (PK) in the CLIENTRENTAL relation, so the relation are not in 2 NF: – fd 2 client. NO c. NAME (Partial Dependency) • The client attribute (c. Name) is partially depend on the PK • The relation have a single-attribute PK -- automatically in 2 NF – fd 3 house. NO house. ADD, rent, owner. NO, o. NAME (Partial Dependency) • The house rented attributes (house. Add, rent, owner. No, o. Name) are partially depend on the PK • The relation have a PK composed of two or more attributes -- not in 2 NF • The house rented attributes: – fd 1 client. NO, house. NO rent. START, rent. FINISH (Primary Key) • The house rented attributes are fully depend on the whole PK 17

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: 1 NF 2 NF • To transform the CLIENTRENTAL relation into 2 NF, requires the creation of new relations, so that, the non-primary-key attributes are removed along with a copy of the part of the PK on which they are fully functionally depend. • This results in creation of there new relations called CLIENT, RENTAL and HOUSEOWNER as shown in Figure 6. 18

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: 1 NF 2 NF Figure 6 : 2 NF RENTAL house. N 0 CR 76 PG 04 rent. STA RT 1/7/93 CR 76 PG 16 1/9/00 1/9/01 CR 56 PG 04 20/3/90 19/6/93 CR 56 PG 36 21/6/93 3/1/00 CR 56 PG 16 25/1/00 30/8/00 client. N 0 rent. FINIS H 31/8/00 CLIENT client. NO c. NAME CR 76 Rannia CR 56 Ahmad HOUSEOWNER house. NO house. ADD rent owner. NO o. NAME PG 04 Skudai, Johor. 750 C 040 Dolah PG 36 Kuantan, Pahang. 1000 C 093 Abdullah PG 16 Ampang, Selangor. 850 C 093 Abdullah CLIENT(client. NO, c. NAME) RENTAL(client. NO, house. NO, rent. START, rent. FINISH) HOUSEOWNER(house. NO, house. ADD, rent, owner. NO, o. NAME) These relation are in 2 NF as every non-primary-key attribute is fully functional dependent on 19 the PK of the relation.

Chapter 5: Normalization Of Database Tables The decomposition of the Client. Rental UNF 2 NF Unnormalized Form (UNF) CLIENTRENTAL (client. NO, house. NO, c. NAME, house. Add, rent. START, rent. FINISH, rent, owner. NO, o. NAME) First Normal Form (1 NF) CLIENTRENTAL (client. NO, house. NO, c. NAME, house. Add, rent. START, rent. FINISH, rent, 1 NF owner. NO, o. NAME) Second Normal Form (2 NF) CLIENT(client. NO, c. NAME) RENTAL(client. NO, house. NO, rent. START, rent. FINISH) HOUSEOWNER(house. NO, house. Add, rent, owner. NO, o. NAME) House. Owner Client Rental House. For. Rent Owner 20

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: Third Normal Form (3 NF) = A relation that is in 1 NF and 2 NF and which no non-primary-key attribute is transitively dependent on the primary key. • Based on the concept of transitive dependency. • Transitive Dependency is a condition where – A, B and C are attributes of a relation such that if A B and B C – then C is transitively dependent on A through B. (Provided that A is not functionally dependent on B or C). 3 NF applies to relations with transitive dependency. A relation that have no transitive dependency are already in 3 NF. • • 21

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: 2 NF 3 NF • Identify the primary key in the 2 NF relation. • Identify functional dependencies in the relation. • If transitive dependencies exist on the primary key, remove them by placing them in a new relation along with a copy of their determinant. 22

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: 2 NF 3 NF • Guidelines: – Identify transitive dependencies and identify determinant that is not primary key (PK). – Group attributes that have transitive dependencies with its determinant to a new relation, R 3. Group also the left over attributes, to a new relation, R 4. – For a relationship, R, with attributes, A, B and C, which A B and B C, simplified the R relationship to R 1 and R 2, with schema: • R 1 (A, B) R (A, B, C) with transitive dependencies: A B • R 2 (B, C) B C Simplified R to: R 1 (A, B*) R 2 (B, C) 23

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: 2 NF 3 NF • The functional dependencies for the CLIENT, RENTAL HOUSEOWNER relations, derived from Dream. Home, are as follows: and – RENTAL fd 1 client. NO, house. NO rent. START, rent. FINISH (Primary Key) – CLIENT fd 2 client. NO Cname (Primary Key) – HOUESOWNER fd 3 house. NO house. ADD, rent, owner. NO, o. NAME fd 4 owner. NO o. NAME (Primary key) (Transitive dependency) 24

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: 2 NF 3 NF • All the non-primary-key attributes within the CLIENT and RENTAL relations are functionally dependent on their PK. • The CLIENT and RENTAL relations have no transitive dependencies and are therefore already in 3 NF. • • All the non-primary-key attributes within the HOUSEOWNER relation are functionally dependent on the PK, – with the exception of o. NAME, which is transitively dependent on owner. NO. To transform the HOUSEOWNER relation into 3 NF, – Remove transitive dependency by creating 2 new relations called HOUSE and OWNER as shown in Figure 7. 25

Chapter 5: Normalization Of Database Tables 5. 3 The Normalization Process: 2 NF 3 NF Figure 7 : 3 NF relations derived from the House. Owner relation. HOUSE house. NO house. ADD rent owner. NO PG 04 Skudai, Johor. 750 C 040 PG 36 Kuantan, Pahang. 1000 C 093 C 040 Dolah PG 16 Ampang, Selangor. 850 C 093 Abdullah OWNER owner. NO o. NAME HOUSE(house. NO, house. ADD, rent, owner. NO*) OWNER(owner. NO, o. NAME) 26

Chapter 5: Normalization Of Database Tables The decomposition of the Client. Rental UNF 3 NF Unnormalized Form (UNF) CLIENTRENTAL (client. NO, house. NO, c. NAME, house. Add, rent. START, rent. FINISH, rent, owner. NO, o. NAME) First Normal Form (1 NF) CLIENTRENTAL (client. NO, house. NO, c. NAME, house. Add, rent. START, rent. FINISH, rent, 1 NF owner. NO, o. NAME) Second Normal Form (2 NF) CLIENT(client. NO, c. NAME) RENTAL(client. NO, house. NO, rent. START, rent. FINISH) HOUSEOWNER(house. NO, house. Add, rent, owner. NO, o. NAME) House. Owner Third Normal Form (3 NF) CLIENT(client. NO, c. NAME) RENTAL(client. NO, house. NO, rent. START, rent. FINISH) HOUSE(house. NO, house. Add, rent, owner. NO*) OWNER(owner. NO, o. NAME) Client Rental House. For. Rent Owner 27

Chapter 5: Normalization Of Database Tables The decomposition of the Client. Rental 1 NF 3 NF Figure 8 : The decomposition of the Client. Rental 1 NF relation into 3 NF relations. CLIENTRENTAL 1 NF HOUSEOWNER House. Owner CLIENT Client RENTAL Rental HOUSE House. For. Rent OWNER Owner 2 NF 3 NF 28

Chapter 5: Normalization Of Database Tables A summary of the 3 NF relation derived from Client. Rental relation Figure 9 : A summary of the 3 NF relation derived from the Client. Rental relation. RENTAL CLIENT house. NO CR 76 Rannia CR 76 PG 04 rent. STA RT 1/7/93 CR 56 Ahmad CR 76 PG 16 1/9/00 1/9/01 CR 56 PG 04 20/3/90 19/6/93 CR 56 PG 36 21/6/93 3/1/00 CR 56 PG 16 25/1/00 30/8/00 client. NO c. Name HOUSE rent. FINIS H 31/8/00 house. NO house. ADD rent owner. NO PG 04 Skudai, Johor. 750 C 040 PG 36 Kuantan, Pahang. 1000 C 093 C 040 Dolah PG 16 Ampang, Selangor. 850 C 093 Abdullah CLIENT RENTAL HOUSE OWNER owner. NO o. NAME (client. NO, c. NAME) (client. NO, house. NO, rent. START, rent. FINISH) (house. NO, house. ADD, rent, owner. NO*) (owner. NO, o. NAME) These relation are in 3 NF as every non-primary-key attribute is fully functional dependent on the PK of the relation and have no transitive dependencies.

Chapter 5: Normalization Of Database Tables A summary of the 3 NF relation derived from Client. Rental relation Figure 10 : ERD for the 3 NF relation derived from the Client. Rental relation. house. No CLIENT 1 M RENTAL 1 M HOUSE M client. NO rent. START rent house. NO rent. FINISH c. NAME have house. ADD owner. NO* 1 CLIENT RENTAL HOUSE OWNER (client. NO, c. NAME) (client. NO, house. NO, rent. START, rent. FINISH) (house. NO, house. ADD, rent, owner. NO*) (owner. NO, o. NAME) OWNER owner. NO o. NAME 30

Chapter 5: Normalization Of Database Tables General Definations of UNF, 1 NF, 2 NF & 3 NF • Unnormalized Form (UNF) – A table/relation that contains one or more repeating groups. • Second Normal Form (1 NF) – A relation that is in 1 NF and every non-primary-key attribute is fully functionally dependent on the primary key. • Second normal form (2 NF) – A relation that is in first normal form and every non-primary-key attribute is fully functionally dependent on any candidate key. • Third normal form (3 NF) – A relation that is in first and second normal form and in which no non -primary-key attribute is transitively dependent on any candidate key. 31