NORMALISATION EXTENDED KNOW WHAT ARE NORMALIZATION AND 1

NORMALISATION EXTENDED KNOW WHAT ARE NORMALIZATION AND 1 NF, 2 NF, 3 NF. DESIGN A SIMPLE RELATIONAL DATABASE TO THIRD NORMAL FORM

PRIMARY KEY A primary is a single column values used to uniquely identify a database record. A primary key cannot be NULL A primary key value must be unique Composite Key: A composite key is a primary key composed of multiple columns used to identify a record uniquely

• It ensures rows in one table have corresponding rows in another • Unlike Primary key they do not have to be unique. Foreign keys can be null even though primary keys can not

Normalization is a database design technique which organizes tables in a manner that reduces redundancy and dependency of data.

• Unnormalized – There are multivalued attributes or repeating groups • • • 1 NF – No repeating groups. 2 NF – 1 NF plus no partial dependencies 3 NF – 2 NF plus no transitive dependencies

FIRST NORMAL FORM – 1 NF • First Normal Form : There should be no repeated groups of attributes. Create a table to store the course attendances seperately for each employee with a foreign key. Table : Staff (Staff. Id, Staff. Name) Staff. Record (Staff. Id, Course. Title, Date, Duration)

SECOND NORMAL FORM – 2 NF The non-key attributes in the table must be dependent on knowing all of the primary key. Table: Staff. Record(Staff. ID, Course. Title, Date, Duration)*This is a composite Key Non-Key Attribute : Duration Which is dependent on course title Create a New Tables Course. Session (Course. Title, Date) Course(Course. Title, Duration)

THIRD NORMAL FORM – 3 NF There must not be dependency in between any non-key attributes.

Course. Title, Duration 1 NF There are no repeated Groups 2 NF It has a single value Primary Key so must be in 2 NF 3 NF It has only one Non-key attribute Course Session Course. Title, Date 1 NF There are no repeated Groups 2 NF There are non-Key Attributes 3 NF There are non-Key Attributes

Staff. Id, Staff. Name 1 NF No Repeated Groups 2 NF One PK so it must be in 2 NF 3 NF Only one Non-key attribute Staff. Record Staff. ID, Course. Title, Date 1 NF No Repeated Groups 2 NF Only one nonkey Attribute 3 NF Only one nonkey Attribute

TASK UNNORMALIZED - UNF Nu m Cust. Name City Country Prod. ID Description 005 Bill Jones London England 1 Table 005 Bill Jones London England 2 Desk 005 Bill Jones London England 3 Chair 008 Amber Arif Lahore Pakistan 2 Desk 008 Amber Arif Lahore Pakistan 7 Cupboard 014 M. Ali Kathmandu Nepal 5 Cabinet 002 Omar Norton Cairo Egypt 7 Cupboard 002 Omar Norton Cairo Egypt 1 Table 002 Omar Norton Cairo Egypt 2 Desk

First Normal Form: A table with no repeating groups is said to be in first normal form. Solution: Need to remove the repeating groups by moving the Prod. ID and Description to a new table with a foreign Key.

Table : Order (1 NF) Num Cust. Name City Country 005 Bill Jones London England 008 Amber Arif Lahore Pakistan 014 M. Ali Kathmandu Nepal 002 Omar Norton Cairo Egypt Table : Order-Products (1 NF) Num Prod. ID Description 005 1 Table 005 2 Desk 005 3 Chair 008 2 Desk 008 7 Cupboard 014 5 Cabinet 002 1 Table 002 2 Desk 002 7 Cupboard

SECOND NORMAL FORM – 2 NF A table is in second normal form if any partial dependencies have been removed. That is, every non-key attribute must be fully dependent on all of the Primary Key. Non-Key : ‘Description’ not dependent on all of the primary key. Description is dependent only on Prod. ID Move the ‘Description’ attribute to a new table and link the new table with foreign Key

Table : Product (2 NF) Description 1 Table 2 Desk 3 Chair 5 Cabinet 7 Table : Order-Products (2 NF) Prod. ID Cupboard Num Prod. ID 005 1 005 2 005 3 008 2 008 7 014 5 002 1 002 2 002 7

Third Normal Form Third Normal form (is like second normal form) is concerned with the non-key attributes. To be in 3 NF, there must be no dependencies between any of the non-key attributes. A table with no or one non-key attribute must be in 3 NF, so the Product and Order-Products are in 3 NF.

Original table ‘Order’ there are two non-key attributes. City & Country- City determines the country. We have two non-key which are dependent i. e. , Order table is not 3 NF Order City Num Cust. Name City Country 1 Bill Jones London England 2 Amber Arif Lahore Pakistan 3 M. Ali Kathmandu Nepal 5 Omar Norton Cairo Egypt

Stage Tables UNF ORDER (Num, Cust. Name, City, Country, (Prod. ID, Description)) 1 NF ORDER (Num, Cust. Name, City, Country) ORDER-PRODUCTS (Num, Prod. ID, Description) 2 NF ORDER (Num, Cust. Name, City, Country) ORDER-PRODUCTS (Num, Prod. ID) PRODUCT (Prod. ID, Description) 3 NF ORDER (Num, Cust. Name, City) CITY-COUNTRIES (City, Country) ORDER-PRODUCTS (Num, Prod. ID) PRODUCT (Prod. ID, Description)

TASK # 2 UNNORMALIZED - CLIENTRENTAL Client. No property. No c. Name p. Address rent. Start rent. Finish rent owner. No o. Name CR 76 PG 4 John Kay 6 lawrence St, Glasgow 1 -Jul-00 31 -Aug-01 350 CO 40 Tina Murphy CR 76 PG 16 John Kay 5 Novar Dr, Glasgow 1 -Sep-02 450 CO 93 Tony Shaw CR 56 PG 4 Aline Stewart 6 lawrence St, Glasgow 1 -Sep-99 10 -Jun-00 350 CO 40 Tina Murphy PG 36 Aline Stewart 2 Manor Rd, Glasgow PG 16 Aline Stewart 5 Novar Dr, Glasgow CR 56 10 -Oct-00 1 -Nov-02 1 -Dec-01 1 -Aug-03 370 450 CO 93 Tony Shaw

CLIENTRENTAL Remove the repeating group Client & Rent 1 NF – FIRST NORMAL FORM Client. No c. Name CR 76 John Kay CR 56 Aline Stewart Rent Client. No property. No CR 76 PG 4 CR 76 PG 16 CR 56 PG 4 CR 56 PG 36 CR 56 PG 16 p. Address 6 lawrence St, Glasgow 5 Novar Dr, Glasgow 6 lawrence St, Glasgow 2 Manor Rd, Glasgow 5 Novar Dr, Glasgow rent. Start rent. Finish rent owner. No o. Name 1 -Jul-00 31 -Aug-01 350 CO 40 Tina Murphy 1 -Sep-02 450 CO 93 Tony Shaw 1 -Sep-99 10 -Jun-00 350 CO 40 Tina Murphy 10 -Oct-00 1 -Dec-01 370 CO 93 Tony Shaw 1 -Nov-02 1 -Aug-03 450 CO 93 Tony Shaw

2 NF CLIENTRENTAL RELATION In 2 NF - Remove the Partial Dependency Client Rental Client. No c. Name Client. No property. No rent. Start rent. Finish CR 76 CR 56 John Kay Aline Stewart CR 76 PG 4 1 -Jul-00 31 -Aug-01 CR 76 CR 56 PG 16 PG 4 PG 36 PG 16 1 -Sep-02 1 -Sep-99 10 -Oct-00 1 -Nov-02 1 -Sep-02 10 -Jun-00 1 -Dec-01 1 -Aug-03 Property. Detail property. No p. Address rent owner. No o. Name PG 4 6 lawrence St, Glasgow 350 CO 40 Tina Murphy PG 16 5 Novar Dr, Glasgow 450 CO 93 Tony Shaw PG 36 2 Manor Rd, Glasgow 370 CO 93 Tony Shaw

3 NF CLIENTRENTAL RELATION Client Rental Client. No c. Name Client. No property. No rent. Start rent. Finish CR 76 CR 56 John Kay Aline Stewart CR 76 PG 4 1 -Jul-00 31 -Aug-01 CR 76 CR 56 PG 16 PG 4 PG 36 PG 16 1 -Sep-02 1 -Sep-99 10 -Oct-00 1 -Nov-02 1 -Sep-02 10 -Jun-00 1 -Dec-01 1 -Aug-03 Remove Non Key Dependency Property. Detail Owner property. No p. Address rent owner. No o. Name PG 4 6 lawrence St, Glasgow 350 CO 40 Tina Murphy PG 16 5 Novar Dr, Glasgow 450 CO 93 Tony Shaw PG 36 2 Manor Rd, Glasgow 370 CO 93

Review 1. Define and explain normalization 2. What are the benefits of normalization 3. Explain tree normal forms