Normalization of Database

Database Normalization is a technique of organizing the data in the database. Normalization is a

systematic approach of decomposing tables to eliminate data redundancy and undesirable
characteristics like Insertion, Update and Deletion Anomalies. It is a multi-step process that puts
data into tabular form by removing duplicated data from the relation tables.

Normalization is used for mainly two purpose,

 Eliminating redundant (useless) data.

 Ensuring data dependencies make sense i.e. data is logically stored.

Normalization is a technique for producing relational schema with the following properties:

 No Information Redundancy
 No Update Anomalies

Anomalies
Anomalies are inconvenient or error-prone situation arising when we process the tables. There
are three types of anomalies:

1. Update Anomalies
2. Delete Anomalies
3. Insert Anomalies

Update Anomalies
An Update Anomaly exists when one or more instances of duplicated data is updated, but not
all. For example, consider Jones moving address - you need to update all instances of Jones's
address.

StudentNum CourseNum Student Name Address Course

S21 9201 Jones Edinburgh Accounts
S21 9267 Jones Edinburgh Accounts
S24 9267 Smith Glasgow physics
S30 9201 Richards Manchester Computing
S30 9322 Richards Manchester MathS

Page 1 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

Delete Anomalies
A Delete Anomaly exists when certain attributes are lost because of the deletion of other
attributes.

For example, consider what happens if Student S30 is the last student to leave the course - All
information about the course is lost.

StudentNum CourseNum Student Name Address Course

S21 9201 Jones Edinburgh Accounts
S21 9267 Jones Edinburgh Accounts
S24 9267 Smith Glasgow physics
S30 9201 Richards Manchester Computing
S30 9322 Richards Manchester Maths

Insert Anomalies
An Insert Anomaly occurs when certain attributes cannot be inserted into the database without
the presence of other attributes.

For example this is the converse (opposite) of delete anomaly - we can't add a new course unless
we have at least one student enrolled on the course.

StudentNum CourseNum Student Name Address Course

S21 9201 Jones Edinburgh Accounts
S21 9267 Jones Edinburgh Accounts
S24 9267 Smith Glasgow physics
S30 9201 Richards Manchester Computing
S30 9322 Richards Manchester Maths

Page 2 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

Problem without Normalization

Without Normalization, it becomes difficult to handle and update the database, without facing
data loss. Insertion, Updating and Deletion Anomalies are very frequent if Database is not
normalized. To understand these anomalies let us take an example of Student table.

S_id S_Name S_Address Subject_opted

401 Adam Noida Bio
402 Alex Panipat Maths
403 Stuart Jammu Maths
404 Adam Noida Physics

 Updation Anamoly : To update address of a student who occurs twice or more than
twice in a table, we will have to update S_Address column in all the rows, else data will
become inconsistent.
 Insertion Anamoly : Suppose for a new admission, we have a Student id(S_id), name
and address of a student but if student has not opted for any subjects yet then we have to
insert NULL there, leading to Insertion Anamoly.
 Deletion Anamoly : If (S_id) 401 has only one subject and temporarily he drops it, when
we delete that row, entire student record will be deleted along with it.

Page 3 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

Normalization Rules
Normalization rule are divided into following normal form.

1. First Normal Form

2. Second Normal Form
3. Third Normal Form
4. BCNF

Normalization Stages
Process involves applying a series of tests on a relation to determine whether it satisfies or
violates the requirements of a given normal form.
 When a test fails, the relation is decomposed into simpler relations that individually meet
the normalization tests.

 The higher the normal form the less vulnerable to update anomalies the relations become.

 Three Normal forms: 1NF, 2NF and 3NF were initially proposed by Codd.

 All these normal forms are based on the functional dependencies among the attributes of
a relation.

Normalisation follows a staged process that obeys a set of rules. The steps of normalisation are:

Step 1: Select the data source and convert into an unnormalised table (UNF)

Step 2: Transform the unnormalised data into first normal form (1NF)

Step 3: Transform data in first normal form (1NF) into second normal form (2NF)

Step 4: Transform data in second normal form (2NF) into third normal form (3NF)

Occasionally, the data may still be subject to anomalies in third normal form. In this case, we
may have to perform further transformations.

 Transform third normal form to Boyce-Codd normal form (BCNF)

 Transform Boyce-Codd normal form to fourth normal form (4NF)

 Transform fourth normal form to fifth normal form (5NF)

Page 4 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

First Normal Form (1NF)

As per First Normal Form,

1. No two Rows of data must contain repeating group of information i.e. each set of column
must have a unique value, such that multiple columns cannot be used to fetch the same
row.
2. Each table should be organized into rows, and each row should have a primary key that
distinguishes it as unique. The Primary key is usually a single column, but sometimes
more than one column can be combined to create a single primary key.

For example consider a table which is not in First normal form

Student Table:

Student Age Subject

Adam 15 Biology, Maths
Alex 14 Maths
Stuart 17 Maths

In First Normal Form, any row must not have a column in which more than one value is saved,
like separated with commas. Rather than that, we must separate such data into multiple rows.

Student Table following 1NF will be:

Student Age Subject

Adam 15 Biology
Adam 15 Maths
Alex 14 Maths
Stuart 17 Maths

Using the First Normal Form, data redundancy increases, as there will be many columns with
same data in multiple rows but each row as a whole will be unique.

Page 5 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

Definition: A table (relation) is in 1NF if

1. There are no duplicated rows in the table.

2. Each cell is single-valued (i.e., there are no repeating groups or arrays).

3. Entries in a column (attribute, field) are of the same kind.

Page 6 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

Second Normal Form (2NF)

As per the Second Normal Form

1. There must not be any partial dependency of any column on primary key. It means that
for a table that has concatenated primary key, each column in the table that is not part of
the primary key must depend upon the entire concatenated key for its existence. If any
column depends only on one part of the concatenated key, then the table fails Second
normal form.

In example of First Normal Form there are two rows for Adam, to include multiple subjects that
he has opted for. While this is searchable, and follows First normal form, it is an inefficient use
of space. Also in the above Table in First Normal Form, while the candidate key is {Student,
Subject}, Age of Student only depends on Student column, which is incorrect as per Second
Normal Form. To achieve second normal form, it would be helpful to split out the subjects into
an independent table, and match them up using the student names as foreign keys.

New Student Table following 2NF will be:

Student Age
Adam 15
Alex 14
Stuart 17

In Student Table the candidate key will be Student column, because all other column i.e. Age is
dependent on it.

Page 7 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

New Subject Table introduced for 2NF will be:

Student Subject
Adam Biology
Adam Maths
Alex Maths
Stuart Maths

In Subject Table the candidate key will be {Student, Subject} column. Now, both the above
tables qualifies for Second Normal Form and will never suffer from Update Anomalies.
Although there are a few complex cases in which table in Second Normal Form suffers Update
Anomalies, and to handle those scenarios Third Normal Form is there.

Definition: A table is in 2NF if it is in 1NF and if all non-key attributes are dependent on all
of the key.

Note: Since a partial dependency occurs when a non-key attribute is dependent on only a part of
the (composite) key, the definition of 2NF is sometimes phrased as, "A table is in 2NF if it is in
1NF and if it has no partial dependencies."

Page 8 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

Third Normal Form (3NF)

Third Normal form applies that every non-prime attribute of table must be dependent on
primary key, or we can say that, there should not be the case that a non-prime attribute is
determined by another non-prime attribute.

Definition: A table is in 3NF if it is in 2NF and if it has no transitive dependencies.

So this transitive functional dependency should be removed from the table and also the table
must be in Second Normal form. For example, consider a table with following fields.

Student_Detail Table:

Student_id Student_name DOB Street city State Zip

In this table Student_id is Primary key, but street, city and state depends upon Zip. The
dependency between zip and other fields is called transitive dependency. Hence to apply 3NF,
we need to move the street, city and state to new table, with Zip as primary key.

New Student_Detail Table:

Student_id Student_name DOB Zip

Address Table:

Zip Street city state

The advantage of removing transitive dependency is,

 Amount of data duplication is reduced.

 Data integrity achieved.

Page 9 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

Boyce and Codd Normal Form (BCNF)

Boyce and Codd Normal Form is a higher version of the Third Normal form. This form deals
with certain type of anomaly that is not handled by 3NF. A 3NF table which does not have
multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF, following
conditions must be satisfied:

 R must be in 3rd Normal Form

 And, for each functional dependency (X -> Y), X should be a super Key.

Page 10 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

Normalisation Example
We will demonstrate the process of normalisation (to 3NF) by use of an example. Normalisation
is a bottom-up technique for database design, normally based on an existing system (which may
be paper-based).

We start by analysing the documentation, eg reports, screen layouts from that system. We will
begin with the Project Management Report, which describes projects being worked upon by
employees. This report is to be 'normalised'. Each of the first four normalisation steps is
explained.

Page 11 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

Step 1
Select the data source (ie the report from the previous page) and convert into an unnormalised
table (UNF). The process is as follows:

 Create column headings for the table for each data item on the report (ignoring any
calculated fields). A calculated field is one that can be derived from other information on
the form. In this case total staff and average hourly rate.

 Enter sample data into table. (This data is not simply the data on the report but a
representative sample. In this example it shows several employees working on several
projects. In this company the same employee can work on different projects and at a
different hourly rate.)

 Identify a key for the table (and underline it).

 Remove duplicate data. (In this example, for the chosen key of Project Code, the values
for Project Code, Project Title, Project Manager and Project Budget are duplicated if
there are two or more employees working on the same project. Project Code chosen for
the key and duplicate data, associated with each project code, is removed. Do not confuse
duplicate data with repeating attributes which is described in the next step

Page 12 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

Page 13 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]
Step 2
Transform a table of unnormalised data into first normal form (1NF). Any repeating attributes to
a new table. A repeating attribute is a data field within the UNF relation that may occur with
multiple values for a single value of the key. The process is as follows:

 Identify repeating attributes.

 Remove these repeating attributes to a new table together with a copy of the key from the
UNF table.

 Assign a key to the new table (and underline it). The key from the original unnormalised
table always becomes part of the key of the new table. A compound key is created. The
value for this key must be unique for each entity occurrence.

Notes:

 After removing the duplicate data the repeating attributes are easily identified.

 In the previous table the Employee No, Employee Name, Department No, Department
Name and Hourly Rate attributes are repeating. That is, there is potential for more than
one occurrence of these attributes for each project code. These are the repeating attributes
and have been to a new table together with a copy of the original key (ie: Project Code).

 A key of Project Code and Employee No has been defined for this new table. This
combination is unique for each row in the table.

Page 14 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

1NF Tables: Repeating Attributes Removed

Page 15 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

Step 3
Transform 1NF data into second normal form (2NF). Remove any -key attributes (partial
Dependencies) that only depend on part of the table key to a new table.

What has to be determined "is field A dependent upon field B or vice versa?" This means:
"Given a value for A, do we then have only one possible value for B, and vice versa?" If the
answer is yes, A and B should be put into a new relation with A becoming the primary key. A
should be left in the original relation and marked as a foreign key.

Ignore tables with (a) a simple key or (b) with no non-key attributes (these go straight to 2NF
with no conversion).

The process is as follows:

Take each non-key attribute in turn and ask the question: is this attribute dependent on one part
of the key?

 If yes, remove the attribute to a new table with a copy of the part of the key it is
dependent upon. The key it is dependent upon becomes the key in the new table.
Underline the key in this new table.

 If no, check against other part of the key and repeat above process

 If still no, i.e.: not dependent on either part of the key, keep attribute in current table.

Notes:

 The first table went straight to 2NF as it has a simple key (Project Code).

 Employee name, Department No and Department Name are dependent upon Employee
No only. Therefore, they were moved to a new table with Employee No being the key.

 However, Hourly Rate is dependent upon both Project Code and Employee No as an
employee may have a different hourly rate depending upon which project they are
working on. Therefore it remained in the original table.

Page 16 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

 2NF Tables: Partial Key Dependencies Removed

Step 4

Data in second normal form (2NF) into third normal form (3NF).

Remove to a new table any non-key attributes that are more dependent on other non-key attributes than
the table key.

What has to be determined is "is field A dependent upon field B or vice versa?" This means: "Given a
value for A, do we then have only one possible value for B, and vice versa?" If the answer is yes, then
A and B should be put into a new relation, with A becoming the primary key. A should be left in the
original relation and marked as a foreign key.

Ignore tables with zero or only one non-key attribute (these go straight to 3NF with no conversion).

Page 17 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

The process is as follows: If a non-key attribute is more dependent on another non-key attribute than
the table key:

 Move the dependent attribute, together with a copy of the non-key attribute upon which
it is dependent, to a new table.

 Make the non-key attribute, upon which it is dependent, the key in the new table.
Underline the key in this new table.

 Leave the non-key attribute, upon which it is dependent, in the original table and mark it
a foreign key (*).

Notes:

 The project team table went straight from 2NF to 3NF as it only has one non-key
attribute.

 Department Name is more dependent upon Department No than Employee No and

therefore was moved to a new table. Department No is the key in this new table and a
foreign key in the Employee table.

Page 18 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

 3NF Tables: Non-Key Dependencies Removed

Summary of Normalisation Rules

That is the complete process. Having started off with an unnormalised table we finished with four
normalised tables in 3NF. You will notice that duplication has been removed (apart from the keys
needed to establish the links between those tables).

The process may look complicated. However, if you follow the rules completely, and do not miss
out any steps, then you should arrive at the correct solution. If you omit a rule there is a high
probability that you will end up with too few tables or incorrect keys.

Page 19 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]

 The following normal forms were discussed in this section:

1. First normal form: A table is in the first normal form if it contains no repeating columns.

2. Second normal form: A table is in the second normal form if it is in the first normal form and
contains only columns that are dependent on the whole (primary) key.

3. Third normal form: A table is in the third normal form if it is in the second normal form and
all the non-key columns are dependent only on the primary key. If the value of a non-key
column is dependent on the value of another non-key column we have a situation known as
transitive dependency. This can be resolved by removing the columns dependent on non-key
items to another table.

Page 20 of 20 DIT 1201 DATABASE SYSTEM P Mackenzie petnjau@[Link]