1 NORMALIZATIO N Part 1 The Concept Objectives

1 NORMALIZATIO N Part 1: The Concept

Objectives 2 How to undertake the normalization process. How normalization uses functional dependencies to group attributes into relations that are in a known normal form. How to identify the most commonly used normal forms: � � � First Normal Form (1 NF) Second Normal Form (2 NF) Third Normal Form (3 NF) The problems associated with relations that break the rules of 1 NF, 2 NF, or 3 NF. How to represent attributes shown on a form as 3 NF relations using normalization.

Introduction 3 2 strategies in creating data model: We have adopted a top-down approach to database design that begins by identifying the entities and relationship. Normalization is a bottom-up approach to database design that begins by examining the relationships between attributes.

Database Design Strategies 4 Two classical approaches/strategies to d. B design: Top-down Bottom-up Conceptual Model { Normalization } Entity Attribute

Table & Relational Schema 5 staff branch staff. NO s. Name position S 21 Johan Manager S 37 Ana S 14 salary branch. NO b. Address 3000 B 005 123, Kepong Assistant 1200 B 003 B 007 456, Nilai Daud Supervisor 1800 B 003 789, PTP S 9 Mary Assistant 900 B 007 S 5 Siti Manager 2400 B 003 S 41 Jani Assistant 900 B 005 branch (branch. NO, b. Address) staff (staff. NO, s. Name, position, salary, *branch. NO)

Normalization 6 When we design a database, the main objective is to create an accurate representation of data, relationship between the data, and constraints on the data that is relevant. To achieve this objective, we have to identify suitable set of relations (table) by creating good table structure/process of assigning attributes to entities. The process is known as Normalization. � Process for evaluating and correcting table structures to minimize/control data redundancies {reduces data anomalies}. � Works through a series of stages called normal forms.

Normalization 7 The most commonly used normal forms: § First Normal Form (1 NF) § Second Normal Form (2 NF) § Third Normal Form (3 NF) 1 NF < 2 NF < 3 NF Highest normalization is not always desirable § More JOINS are required § Affect data retrieval performance/high response time For most business database design purposes, 3 NF is as high as we need to go in normalization process

Normalization 8 Every normal form is based on functional dependencies between attributes in a relationship. Each relationship can be normalized into a specific form to avoid anomalies. � Anomalies? Anomaly = abnormality Ideally a field value change, should be made only in a single place. Data redundancy, promotes an abnormal condition by forcing field value changes in many different locations. Insertion anomalies Deletion anomalies Modification/Update anomalies

Functional Dependencies 9 An important concept associated with normalization is functional dependency which describes the relationship between attributes. In this section, you will learn about functional dependency and then focus on the particular characteristics of functional dependency that are useful for normalization.

Functional Dependencies 10 Functional dependency can be divided into two types: � Full functional dependency/Partial dependency (PD) Will be used to transform 1 NF 2 NF � Transitive Will dependency (TD) be used to transform 2 NF 3 NF

Functional Dependencies 11 Multivalued Attributes (or repeating groups): non-key attributes or groups of non-key attributes the values of which are not uniquely identified by (directly or indirectly) (not functionally dependent on) the value of the Primary Key (or its part). 1 st row 2 nd row Relational Schema STUDENT(Stud_ID, Name, (Course_ID, Units))

Functional Dependencies 12 Partial Dependency – when an non-key attribute is determined by a part, but not the whole, of a COMPOSITE primary key (The Primary Key must be a Composite Key). Cust_ID → Name

Functional Dependencies 13 Transitive Dependency – when a non-key attribute determines another non-key attribute. Dept_ID → Dept_Name

Functional Dependencies 14 Consider a relation with attributes A and B, where attribute B is functionally depends on attribute A. Let say an A is a PK of R. R(A, B) A B A B is functionally depends on A B To describe the relationship between attributes A and B is to say that “A functionally determines B”.

Functional Dependencies 15 When a functional dependency exist, the attribute or group of attributes on the left-handed side of the arrow is called determinant. Determinant: Refers to the attributes, or a group of attributes, on the left handed side of the arrow of a functional dependency. A A functionally determines B B

Functional Dependencies 16 staff. NO s. Name position salary branch. No S 21 Johan Manager 3000 B 005 S 37 Ana Assistant 1200 B 003 S 14 Daud Supervisor 1800 B 003 S 9 Mary Assistant 900 B 007 S 5 Siti Manager 2400 B 003 S 41 Jani Assistant 900 B 005 branch Determinant branch. NO b. Address B 005 123, Kepong B 007 456, Nilai B 003 789, PTP

Functional Dependencies 17 Consider the attributes staff. NO and position of the staff relation. For a specific staff. NO (S 21), we can determine the position of that member of staff as Manager. staff. NO functionally determines position is functionally staff. NO Staff number (S 21) depends on staff. NO position Position (manager)

Functional Dependencies 18 However the next figure illustrate that the opposite is not true, as position does not functionally determines staff. NO. A member of staff holds one position; however, they maybe several members of staff with the same position. staff. NO does not functionally position depends on position staff. NO staff number (S 21) Position(manager) staff number (S 5)

Functional Dependencies Partial Dependencies: Full functional dependency indicates that if A and B are attributes of a relation, B is fully functionally dependent on A, if B is functionally dependent on A, but not on any proper subset of A. staff(staff. NO, s. Name, position, salary, branch. NO) staff. NO, staff. Name branch. NO True!!! each value of (staff. NO, s. Name) is associated with a single value of branch. NO. however, branch. NO is also functionally dependent on staff. NO. 19

Functional Dependencies Transitive Dependencies: staff(staff. NO, s. Name, position, salary, *branch. NO) branch(branch. NO, b. Address) staff. NO s. Name, position, salary, branch. NO, b. Address branch. NO b. Address True for transitive dependency!!! branch. NO → b. Address exists on staff. NO via branch. NO 20

Normalization Process 21 Formal technique for analyzing relations based on their Data Redundancies 0 NF/UNF 1 NF 2 NF 3 NF

Normalization Process 22 Relationship between Normalize Form Denormalization Figure 1: Diagrammatic illustration of the relationship between the normal forms Normalization

Normalization Process 23 Data Sources Users’ requirements specification Forms/reports that Sources describing the are used or generated enterprise such as data by the enterprise dictionary and corporate data model Transfer attributes into table format Unnormalized Form (UNF) Remove repeating group First Normal Form (1 NF) Remove partial dependencies Second Normal Form (2 NF) Remove transitive dependencies Third Normal Form (3 NF)

Normalization Process 24 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

Normalization Process 25 Remember this!!! Unnormalized (UNF): There are multivalued attributes or repeating groups, not CK (or PKs) 1 NF: NO multivalued attributes or repeating groups, has CK (or PKs) 2 NF: 1 NF with NO Partial Dependencies (PD) 3 NF: 2 NF with NO Transitive Dependencies (TD)

Unnormalized Form (UNF) 26 Unnormalized Form UNF/0 NF = A table that contains one or more repeating groups To create an unnormalized table � Transform the data from the information source (e. g. form) into table format with columns and rows.

First Normal Form (1 NF) 27 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.

Transforming UNF to 1 NF 28 1 2 UNF To-do list: 1. Remove repeating groups 2. Identify Composite Key 1 2 3 1 NF

Example 1: Determine UNF 29

Example 1: Determine UNF 30

Example 1: Determine UNF 31

Example 1: Determine UNF 32

Example 1: Determine UNF 33

Example 2: Determine UNF 34

Example 2: Determine UNF 35

Example 2: Determine UNF 36

Example 2: Determine UNF 37

Example 3: Determine UNF 38

Example 3: Determine UNF 39

UNF to 1 NF 40 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). Step 1: Eliminate the Repeating Groups � Eliminate nulls: each repeating group attribute contains an appropriate data value Step 2: Identify the Primary Key � Must uniquely identify attribute value � New key must be composed Step 3: Identify All Dependencies � Dependencies are depicted with a diagram

UNF to 1 NF 41 Relational Schema STUDENT(Stud_ID, Name, (Course_ID, Units))

UNF to 1 NF 42 Key Attribute: Stud_ID, Course_ID Repeating Group: (Course_ID, Units)

UNF to 1 NF 43 § § Ensure a single value at the intersection of each row and column Enter appropriate student data in each row Relational Schema STUDENT(Stud_ID, Name, Course_ID, Units)

UNF to 1 NF 44 Key Attribute: Stud_ID, Course_ID Repeating Group: (Course_ID, Units)

UNF to 1 NF 45

UNF to 1 NF 46 Dependency diagram: � Depicts all dependencies found within given table structure � Helpful in getting bird’s-eye view of all relationships among table’s attributes � Makes it less likely that you will overlook an important dependency

UNF to 1 NF 47 Remove the repeating group by � 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 to as ”flattening table”. This approach will produce redundancy in a relationship, but it can be eliminated in higher normalization process.

UNF to 1 NF 48 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 Ahmad and is transformed into table format with rows and columns, as shown in Figure 3. This is an example of unnormalized table.

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

UNF to 1 NF 50 Figure 3 : Client. Rental UNF client. No c. Name house. No house. Add rent. Start rent. Finish rent owner. No o. Name 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 CR 56 Ahmad PG 04 PG 36 Skudai, Johor. 20/3/90 21/6/93 19/6/93 3/1/00 750 1000 C 040 C 093 Dolah PG 16 Ampang, Selangor. 25/1/00 30/8/00 850 C 093 Abdullah Kuantan, Pahang. Abdullah Key attribute: client. 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)

UNF to 1 NF 51 Key attribute: client. 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) As a consequences, there are multiple value at the intersection of certain rows and columns. For examples, 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) Client. Rental relation is shown in Figure 4.

UNF to 1 NF 52 Figure 4 : 1 NF Client. Rental relation client. No house. No c. Name house. Add rent. Start rent. Finish rent owner. No o. Name 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 PG 36 Ahmad Skudai, Johor. Kuantan, Pahang. 19/6/93 3/1/00 750 1000 C 040 C 093 Dolah Ahmad 20/3/90 21/6/93 PG 16 Ahmad Ampang, Selangor. 25/1/00 30/8/00 850 C 093 Abdullah CR 56 Abdullah Client. Rental (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 Client. Rent relation is in 1 NF as there is a single value at the intersection of each row and columns.

UNF to 1 NF 53 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 Client. Rental 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

54 Borrow(b. ID, b. DATE, m. ID, m. NAME, m. ADD, ISBN, title, r. DATE) Functional dependencies: b. ID, ISBN b. DATE (PK) b. ID m. ID, m. NAME, m. ADD, r. DATE (PD) ISBN title (PD) m. ID m. NAME, m. ADD (TD)

55 INSURANCE(c. NO, c. NAME, c. ADD, p. NO, p. TYPE, p. DATE, purchase. DATE, p. PRICE, a. NO, a. NAME, a. TEL) Functional Dependencies: c. NO, p. NO, a. NO purcahse. DATE (PK) c. NO c. NAME, c. ADD (PD) p. NO p. TYPE, p. DATE, p. PRICE (PD) a. NO a. NAME, a. TEL (PD)

56 RECEIPT(r. NO, date, t. NO, s. ID, s. NAME, i. NO, m. DES, qty, price, amt, total, pa y, balance) Functional Dependencies: r. NO, m. NO qty, i. NO, amt (PK) r. NO date, t. NO, s. ID, s. NAME, total, pay, balance m. NO m. DES, price s. ID s. NAME (PD) (TD)

Second Normal Form (2 NF) 57 Second Normal Form = A relation that is in 1 NF and every non-PK 2 NF 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.

58 CONTINUE TO NEXT PRESENTATION TRANSFORMING UNF TO 2 NF