Edi 104 - Chapter 6

This chapter learns the most important
technique for designing tables in a

database. The technique is called
normalization, which is a process of
CHAPTER placing attributes into tables as to avoid
redundancy and anomalies in database.
6
The normalization is done, based on the
data model, which is the representation of
tables before they are created in a
database.
The latter part of the chapter describes
the step-by-step approach, as how to
normalize a table into several tables.
Eventually, the chapter also focuses on
more examples to normalize tables.
[Please insert any

relevant photo Normalization
around this box]
Subtopics:-
6.1 Relational Data Model ............................................1
6.1.1 What is a Relation? .........................................1
6.1.2 A notation of Relation ....................................2 - 4
6.1.3 Problems in Relational Data Model...............5 - 10
6.2 Normalization ...........................................................10
6.2.1 Definition of Normalization.............................10
6.2.2 The purpose of Normalization: ......................10 - 14
6.2.3 Level of Normalization....................................14 - 15
6.2.4 Steps in Normalization ....................................15 - 44
6.3 Summary....................................................................45 - 46
6.4 Exercises.....................................................................47
6.1 Relational Data Model
6.1.1 What is a Relation?

In a relational database, all data are stored in tables. Each table is assigned a name. Each row in the table
holds data that pertains something or a portion of something. Each column of the table contains data
regarding an attribute. Sometimes, rows are called tuples and the columns in a relation are called attributes.
• A two-dimensional table with columns and rows is called a RELATION.
Example :
1|P a g e C h a p t e r 6 : D a t a b a s e F u n d a m e n t a l
6.1.2 A notation of Relation
Relation Schema
A relation scheme is a list of attributes and their corresponding domains.
Format:
Relation_name (Primary_key, Non_ Key_Attribute)
From the example, we can identify:
· Relation name : PUBLISHER
· Primary Key : Publisher_Code
· Non Primary Key : Name, City
The relation schema will be like this:
PUBLISHER (Publisher_Code, Name, City)
Below is the example relational schema of E-Book database.
The relation schema are :
PUBLISHER (Publisher_Code, Name, City)
AUTHOR (Author_No, Author_Name)
BOOK_AUTHOR (Book_Code, Author_No)
BOOK(Book_Code, Book_Title , Type, Price, Book_Sample, Book_Cover, Publisher_Code)
6.1.3 Problems in Relational Data Model
There are 2 problems in relational data model:
· Anomalies
· Information Redundancy
6.1.3.1 Anomalies
Anomalies - Errors or inconsistencies that may result when a user attempts to update a table
that contains redundant data.
Types of anomalies:
(i) Modification Anomalies
(ii) Insert Anomalies
(iii) Delete Anomalies
(i) Modification Anomalies
The Problem :
Due to the database is not normalized, changing the data of a record may subject to the
problem of changing the data of a few records instead.
For example, when we want to change the Course_Name’s data from “Computer Power 200 (Graphic)” to
“Computer Power 200 (Graphic & Multimedia)” in the STUDENT_COURSE
table, we have to change two rows of students who take that course. (As shown above)
The Solution :
Therefore, to avoid this problem, we have to separate the STUDENT_COURSE table into
two tables as follows:
The Problem :
When data is not normalized, the confusing error in adding a new record with null primary
key may happen.
For example, when we want to insert a new Course_Code (i.e. CGMP200) and Course_Name (i.e. Diploma
in Information Technology) onto the STUDENT_COURSE table, a null data of Student_ID appears in the
record and it will cause an error because the Student_ID is the primary key and can not be null. On the other
hand, no student is being admitted to study the new course yet.
The Solution :
Therefore, to avoid this problem, we have to separate the STUDENT_COURSE table as
follows:
The Problem :
When data is not normalized, a deletion of one record can cause the table to provide
insufficient information.
For example, the deletion of the Student_Code equals to S0159 may cause the course with Course_Code
equals to DIPCGR totally vanish from the database, as if the course is not available at all. Therefore, the
table can no longer provide the information for all the courses available for the student to study any more.
The Solution :
Therefore, to avoid this problem, we have to separate the STUDENT_COURSE table as
follows:
6.1.3.2 Information Redundancy
Duplication is due the storing of the same data for multiple times.
The Problem :
In the STUDENT_SUBJECT table there is redundant information. It shows that the details
of subject ADM101 (Internet Publishing) are repeated for every member of Student_ID
enrolled for that subject.
The Solution :
Therefore, to avoid this problem, we have to separate the STUDENT_SUBJECT table into
three tables as follows:
6.2 Normalization
6.2.1 Definition of Normalization

A process for assigning attributes into table and reduces data redundancy and helps eliminate the data
anomalies that associated with poor database design (unnormalized).
6.2.2 The purpose of Normalization:
The two goals of the normalization process are:
1. to eliminate the redundant data (such as, storing the same data in more than one
tables).
2. to ensure the correctness in data dependencies. Both of these are the worthy goals as they reduce the
amount of space a database consumed and ensure the data is logically stored.
Unnormalized Form (UNF) - A table contains one or more repeating group.
Example 1
10 | P a g e C h a p t e r 6 : D a t a b a s e F u n d a m e n t a l
The table below shows that the Subject_Name, Subject_Code, Credit_Hour and Grade fields
contain multiple or more repeating values.
Unnormalised Form (UNF)
[Please
insert
any
relevant
photo
around
this
box]
Example 2
The table below shows that the Item_code, Item_desc, Qty, Price, Supplier &
Supplier_Address fields contain multiple or more repeating values.
Unnormalised Form (UNF)
6.2.3 Level of Normalization
There are 3 levels of normalization:
· First Normal Form (1NF)

It is a relation, where data stored in a two-dimensional table that does not have any
repeating groups, which are many groups of attributes appearing in multiple places in
a table. A group of attributes is the attributes that are in relation and the attributes
create the duplicate values for the table, where an attribute is a column in a relation.
· Second Normal Form (2NF)
It is a relation that is in 1NF and every non-primary key attribute is fully functionally
dependent on the primary key.
A functional dependency is a one-way relationship between two attributes such that at
any given time, for each unique value of attribute A, only one value of B is associated
with it through the relation.
· Third Normal Form (3NF)
It is a relation that has removed transitive dependency.
A transitive dependency exists when you have the following functional dependency
pattern for the relation: A->B, B->C therefore A->C. For a removed transitive
dependency means that A should relates directly to B and to C, i.e. A->B and A->C.
6.2.4 Steps in Normalization

Example 1:
Steps in Normalization
STUDENT (Student_ID, Student_Name, Subject_Name , Subject_Code, Credit_Hour,
Grade, Address, Course, Coordinator)
The table above shows that the Subject_Name, Subject_Code, Credit_Hour and Grade fields contain
multiple repetitive values.
Note 1.1
Problems that occur in tables with repeating groups :
· It’s hard to know exactly which Credit_Hour belongs to which Subject_Name.
· To find a record in the table is very difficult.
How to handle Repeating Groups?
· The repeating group can be handled by separating Unnormalised Form (UNF) into
First Normal Form (1NF) as shown later.
Note 1.2
What is Functional Dependencies?
Functional Dependencies describe relationships between attributes in a relation.
A relationship between attributes, of which an attribute or a group of attributes determines the value of
another in the same table.
Example :
Consider microcomputers in a COSMOPOINT computer lab. Each has only one size
of main memory. So MemorySize is functionally dependent on ComputerSerialNumber.
How to write?
The attributes on the left side of the arrow are called determinants.
How to read?
“ComputerSerialNumber functionaly determines MemorySize”
or
“ComputerSerialNumber determines MemorySize”
or
“MemorySize is functionally dependent on ComputerSerialNumber”
Based on these 3 relations schema, there will be 3 tables (relations) in 2NF
Note 1.3
What is Transitive Dependencies?
· A functional dependency between 2 or more non-key attributes. A condition in which an attribute is
dependent on another attributes that is not part of the primary key.
· A relation is in Third Normal Form (3NF) if it is in 2NF and has no transitive
dependency.
· Non-primary key attribute is functionally dependent on another non-primary key
attribute.
Solution : In order to build 3NF table (relation), you have to remove the transitive
Dependencies
The table above shows that the Item_Code, Item_Desc, Qty, Price, Supplier &
Supplier_Address fields contain multiple repetitive values.
Assumption : One supplier supplies many products.
Note 2.1
Problems that occur in table with repeating groups :
· It’s hard to know exactly which Item_Code belongs to which Item_Desc.
· To find a record in the table is very difficult.
· The repeating group can be handled by separating Unnormalised Form (UNF) into
First Normal Form (1NF) as shown later.
The table above shows that each row contains a single value.
Note 2.2 : Identify Functional Dependencies.
A relationship between attributes, of which an attribute or a group of attribute determines the value of
Based on these 3 functional dependencies, there will be 3 tables (relations) in 2NF.
Note 2.3
Transitive Dependency
· A functional dependency between 2 or more non-key attributes. A condition in which
an attribute is dependent on another attributes that is not part of the primary key.
dependency.
attribute.
Solution: In order to build 3NF table (relation), you have to remove the transitive
dependencies.
The above table shows that the fields (Staff_No, Staff_Name, Job_Classification,
Hourly_Rate & Hours) have Repeating Values.
Note 3.1:
Problems that occur in those tables with repeating groups are :
· It’s hard to know exactly which Staff_No belongs to which Staff_Name.
· It’s hard to find a record in the table.
The repeating groups can be handled by separating Unnormalised Form (UNF) into First
Normal Form (1NF) and it will be shown later on.
The above table shows that each row has its own value.
Note 3.2:
A relationship between attributes, of which one attribute or group of attribute determines the value of
Identify Functional Dependencies
Based on these 3 functional dependencies, there will be 3 tables (relations) in 2NF.
Note 3.3:
dependent on another attribute that is not part of the primary key.
dependency.
attribute.
Identify Transitive Dependencies

Solution: In order to build 3NF table (relation), you have to remove the transitive
dependencies.
Table in 1NF is the situation where the intersection of each row and column contains no
repeating values (only one value).
Table in 1NF must be transform to 2NF by identify the functional dependencies.
A relationship between two attributes, of which is an attribute or a group of attributes
determines the value of another in the same table.
Table in 1NF must be transform to 2NF by identify the transitive dependency.
dependent on another attributes that is not part of the primary key.
dependency.
attribute.
Example 5:
Normalize the following form to Third Normal Form (3NF). You are required to show the
Normalization steps taken. Provide your answer in a relation scheme.
Solution:
The Steps in Normalization:
· Name the appropriate relation name.
· List down all attributes involved.
Relation Scheme Format:
a) Unnormalised Form (UNF)

Relation scheme in an Unnormalised Form :
RENTAL (Tenant_Code, Tenant_Name, House_Code, Address, Start_Date,
End_Date, Rental, Landlord_Code, Landlord_Name)
b) First Normal Form (1NF)
· Make sure there is no redundant data in relation.
· Underline Primary Key.
Relation Scheme in 1NF:
CUSTOMER(Tenant_Code, Tenant_Name)
RENTAL (Tenant_Code, House_Code, Address, Start_Date, End_Date, Rental,
Landlord_Code, Landlord_Name)
c) Second Normal Form (2NF)
Identify the Functional Dependencies (relationship among attributes in a relation).
RENTAL (Tenant_Code, House_Code, Start_Date, End_Date)
HOUSE (House_Code, Address, Rental, Landlord_Code, Landlord_Name)
d) Third Normal Form (3NF)

HOUSE (House_Code, Address, Rental, Landlord_Code)
LANDLORD (Landlord_Code, Landlord_Name)
Solution:

STUDENT (Name, Student_No, Address, Major, Status, Subject_Code,
Subject_Name, Professor, Prof_Dept, Grade, Semester)

STUDENT (Student_No, Name, Address, Major, Status)
STUDENTSUBJECT (Student_No, Subject_Code, Subject_Name, Professor,
Prof_Dept, Grade, Semester)

GRADEREPORT (Student_No, Subject_Code, Grade, Semester)
SUBJECT (Subject_Code, Subject_Name, Professor, Prof_Dept)
SUBJECT (Subject_Code, Subject_Name, Professor)
PROF (Professor, Prof_Dept)
Solution:

INVOICE (Invoice_No, Date, Customer_No, Name, Address, Tel_No, Product_Code,
Product_Name, Unit_Price, Quantity, Discount, Subtotal)

INVOICE (Invoice_No, Date, Customer_No, Name, Address, Tel_No)
PRODUCT (Invoice_No , Product_Code, Product_Name, Unit_Price, Quantity,
Discount, Subtotal)

INVOICE (Invoice_No, Date, Customer_No, Name, Address, Tel_No)
PRODUCT (Product_Code, Product_Name, Unit_Price )
ORDER (Invoice_No, Product_Code, Quantity, Discount, Subtotal )
PRODUCT
PRODUCT (Product_Code, Product_Name, Unit_Price )
INVOICE (Invoice_No, Date, Customer_No)
CUSTOMER (Customer_No, Name, Address, Tel_No)
6.3 Summary
· A two dimensional table with columns and rows is called a RELATION.

· A relation scheme is a list of attributes and their corresponding domains.
· Format for relation scheme :
Format : Relation Name ( Primary Key, Non Key Attribute)

· Problems in relational data model :
o Anomalies
o Information Redundancy
· Anomalies is an errors or inconsistencies that may result when a user attempts to update a table that
contains redundant data.
· Normalization is a process for assigning attributes into table and reduces data
redundancy and helps eliminate the anomalies problems.
· Level of normalization :
1 NF
2 NF
3 NF
· 1 NF is a relation where the intersection of each row and column contains no repeating values.
· NF is a relation that is in 1 NF and every non-primary key attribute is fully functionally
depending on the primary key.
· NF is a relation that is in 2 NF and is based on the concept of transitive dependency
· Steps in normalization :
· Functional dependency is a relationship between attributes in which one attributes or a group of

attributes determines the value of another in the same table.
· Transitive dependencies are a condition where an attribute is dependent on another attribute that is
not part of the primary key.
6.4 Exercises
Fill in the blank

1. _______________________ is a 2 dimensional table with columns and row.
2. ______________________ is a list of attribute and their corresponding domains in
relation.
3. 2 problems in relational data model are _______________________________ and
____________________________________.
4. 3 types of anomalies are ___________________________,
__________________________ and ______________________________ .
5. ____________________________ is errors or inconsistencies that may result when a
user attempts to update a table that contains redundant data.
6. ___________________________ is a process for assigning into a table and reduces
data redundancy and helps eliminate the data anomalies.
7. _________________________ is a table that contains one or more repeating
groups.
8. 3 level of normalization are ________________________,
_______________________ and ______________________________.
9. ________________________ is a relation in which based on the concept of transitive
dependency.
10. ________________________ is a relation in 1 NF and every non-primary key
attribute is fully dependent on the primary key.
True or False
1. 1 NF is errors or inconsistencies that may result when a user attempts to update a table
that contains redundant data. (True / False)
2. Functional dependency is a relationship between attributes in which one or a group of
attribute determines the value of another in the same table . (True / False)
3. Transitive dependency is a situation where non-primary key attribute is functionally
dependent on another non-primary key attribute. (True / False)
4. 1 NF is a relation where each attribute do not contains repeating values. (True / False)
Answer the following question
1) List down the purpose of normalization
___________________________________________________________________
___________________________________________________________________
___________________________________________________________________
___________________________________________________________________
2) Change the table below to the relation scheme.
a) Explain the anomalies that exist when the name for Product_No is going to
change to Black Pen?
b) Explain the anomalies that exist when we are going to add new product but we
don’t receive any order yet?
c) Explain the anomalies that exist when we are going to delete Order_No 123?
a) Convert the following table to 3 NF. This is a table concerning information about
patients of a dentist. Each patient belongs to a household. The head of the household
is designated as HH in the table. The following dependencies exist in Patient.
Patient (HouseholdNumber, HouseholdName, HouseholdStreet, HouseholdCity,
HouseholdState, HouseholdZip, Balance, PatientName, ServiceCode, ServiceName,
ServiceDescription, ServiceFee)
3) Using your knowledge of a college environment, determine the functional
dependencies that exist in the following table. After these have been determined,
convert this table to 3 NF.
Student (StudentNumber, StudentName, NumberCredits, AdvisorNumber,
AdvisorName, DeptNumber, DeptName, CourseNumber, CourseDescription,
CourseTerm, Grade)
4) Consider the following relation and assume that the values of SiblingName are the
names of all of a given students brothers and sisters, and also assume that students
have at most one major.
STUDENT (StudentNum, StudentName, SiblingName, Major)
a) Show an example of this relation for 2 students, one of whom has 3 siblings
and the other has only 2 siblings
b) State the functional dependencies in this relation.
c) Show the relation in 3 NF.
Answer the following question
1) Based on the relation below, normalize to 3NF
Employee (EmpCode, LastName, FirstName, Education, DeptCode, Department,
DepartmentManager, JobClass, Title, Dependents, BirthDate, HireDate, Training,
BaseSalary)
2) To keep track of the office furniture, computers, printers, and so on, the ABX
Company uses the following table structure. Normalize it to 3 NF.
3) Normalize the table below to 3 NF.
4) Consider the table below, which contains sample data for parts and for vendors who
supply those parts. (Part Number uniquely identifies parts and vendor names uniquely
identify vendors. Normalize the table to 3 NF.

Edi 104 - Chapter 6

Uploaded by

Copyright:

Available Formats

Edi 104 - Chapter 6

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Edi 104 - Chapter 6

Uploaded by

Copyright:

Available Formats

This chapter learns the most important

technique for designing tables in a

[Please insert any

6.1 Relational Data Model

6.1.1 What is a Relation?

Below is the example relational schema of E-Book database.

BOOK(Book_Code, Book_Title , Type, Price, Book_Sample, Book_Cover, Publisher_Code)

6.2.1 Definition of Normalization

· First Normal Form (1NF)

6.2.4 Steps in Normalization

Based on these 3 relations schema, there will be 3 tables (relations) in 2NF

Based on these 3 functional dependencies, there will be 3 tables (relations) in 2NF.

Identify Transitive Dependencies

a) Unnormalised Form (UNF)

Relation Scheme in 3NF:

a) Unnormalised Form (UNF)

Relation Scheme in 1NF:

Relation Scheme in 2NF:

a) Unnormalised Form (UNF)

Relation Scheme in 1NF:

Relation Scheme in 2NF:

· A two dimensional table with columns and rows is called a RELATION.

Format : Relation Name ( Primary Key, Non Key Attribute)

· Functional dependency is a relationship between attributes in which one attributes or a group of

Fill in the blank

You might also like