ASSIGNMENT

Subitted to : Dr Saima Noreen Khosa

Submitted By : Syeda Rutab Aziz

Registration no : COSC232101044

Class : BSCS 4A

Course : Data Base Management System

Department : Computer Science

Topic : Normalization

Date submitted : 12 - 21 - 24

Normalization
A large database defined as a single relation may result in data duplication. This repetition of data may
result in:

Making relations very large.

It isn't easy to maintain and update data as it would involve searching many records in relation.
Wastage and poor utilization of disk space and resources.
The likelihood of errors and inconsistencies increases.

So to handle these problems, we should analyze and decompose the relations with redundant data into
smaller, simpler, and well-structured relations that are satisfy desirable properties. Normalization is a
process of decomposing the relations into relations with fewer attributes.

What is Normalization?
Normalization is the process of organizing the data in the database.
Normalization is used to minimize the redundancy from a relation or set of relations. It is also
used to eliminate undesirable characteristics like Insertion, Update, and Deletion Anomalies.
Normalization divides the larger table into smaller and links them using relationships.
The normal form is used to reduce redundancy from the database table.

Why do we need Normalization?

The main reason for normalizing the relations is removing these anomalies. Failure to eliminate anomalies
leads to data redundancy and can cause data integrity and other problems as the database grows.
Normalization consists of a series of guidelines that helps to guide you in creating a good database
structure.

Data modification anomalies can be categorized into three types:

Insertion Anomaly: Insertion Anomaly refers to when one cannot insert a new tuple into a
relationship due to lack of data.
Deletion Anomaly: The delete anomaly refers to the situation where the deletion of data results
in the unintended loss of some other important data.
Updatation Anomaly: The update anomaly is when an update of a single data value requires
multiple rows of data to be updated.

Types of Normal Forms:

Normalization works through a series of stages called Normal forms. The normal forms apply to individual
relations. The relation is said to be in particular normal form if it satisfies constraints.

Following are the various types of Normal forms:

Normal Form Description

1NF A relation is in 1NF if it contains an atomic

value.

2NF A relation will be in 2NF if it is in 1NF and all

non-key attributes are fully functional
dependent on the primary key.

3NF A relation will be in 3NF if it is in 2NF and no

transition dependency exists.

BCNF A stronger definition of 3NF is known as Boyce

Codd's normal form.

4NF A relation will be in 4NF if it is in Boyce Codd's

normal form and has no multi-valued
dependency.

5NF A relation is in 5NF. If it is in 4NF and does not

contain any join dependency, joining should be
lossless.
Advantages of Normalization
Normalization helps to minimize data redundancy.
Greater overall database organization.
Data consistency within the database.
Much more flexible database design.
Enforces the concept of relational integrity.

Disadvantages of Normalization
You cannot start building the database before knowing what the user needs.
The performance degrades when normalizing the relations to higher normal forms, i.e., 4NF, 5NF.
It is very time-consuming and difficult to normalize relations of a higher degree.
Careless decomposition may lead to a bad database design, leading to serious problems.

Example :
Imagine the following unnormalized table containing students, their courses, the instructor teaching
the course, and the grade received:

Unnormalized Table: Student_Course_Enrollment

Student_ID Student_Nam Course_ID Course_Nam Instructor_Na Grade

e e me

1 Alice C101 Math Dr. Smith A

1 Alice C102 English Dr. Johnson B

2 Bob C101 Math Dr. Smith A

2 Bob C103 History Dr. Lee C

3 Charlie C102 English Dr. Johnson A

First Normal Form (1NF)

1NF requires that the table have atomic values (no repeating groups), and each record should be
unique.

Here, the table already has atomic values, but there are repeating groups for students taking multiple
courses. We need to remove this redundancy by separating the information into multiple rows, with
each row representing a single enrollment.
After applying 1NF:

We simply keep the structure but ensure each row contains a unique combination of student-course
pairs:

Student_ID Student_Nam Course_ID Course_Nam Instructor_Na Grade

e e me

1 Alice C101 Math Dr. Smith A

1 Alice C102 English Dr. Johnson B

2 Bob C101 Math Dr. Smith A

2 Bob C103 History Dr. Lee C

3 Charlie C102 English Dr. Johnson A

Second Normal Form (2NF)

2NF requires the table to be in 1NF and that all non-key attributes are fully dependent on the entire
primary key. In our case, the primary key is a composite key: (Student_ID, Course_ID).

Student_Name depends only on Student_ID, and Instructor_Name depends only on Course_ID,

not on the whole primary key.

To fix this, we separate the table into two tables to eliminate partial dependencies:

After applying 2NF:

1. Students Table: Stores student information.

2. Courses Table: Stores course and instructor information.
3. Enrollments Table: Stores student enrollments and grades.

Students Table:
Student_ID Student_Name

1 Alice

2 Bob

3 Charlie

Courses Table:

Course_ID Course_Name Instructor_Name

C101 Math Dr. Smith

C102 English Dr. Johnson

C103 History Dr. Lee

Enrollments Table:

Student_ID Course_ID Grade

1 C101 A

1 C102 B

2 C101 A

2 C103 C

3 C102 A

Third Normal Form (3NF)

3NF requires that the table be in 2NF and that there are no transitive dependencies. In our case,
Instructor_Name depends on Course_ID, and Course_ID depends on Course_Name. To satisfy 3NF,
we need to remove the transitive dependency by splitting the Courses table into two tables.

After applying 3NF:

1. Courses Table: Stores only course information.

2. Instructors Table: Stores instructor information.
3. Enrollments Table: Remains the same.

Courses Table:

Course_ID Course_Name

C101 Math

C102 English

C103 History

Instructors Table:

Instructor_ID Instructor_Name

1 Dr. Smith

2 Dr. Johnson

3 Dr. Lee

Enrollments Table:
Student_ID Course_ID Grade

1 C101 A

1 C102 B

2 C101 A

2 C103 C

3 C102 A

Boyce-Codd Normal Form (BCNF)

BCNF requires that every determinant be a candidate key. In this case, Instructor_Name is
determined by Course_ID, but Instructor_Name is not a candidate key.

To satisfy BCNF, we need to decompose the Courses table to eliminate this issue. In the new design,
we separate the instructor information into its own table and link it to the Courses table.

After applying BCNF:

1. Courses Table: Stores course information.

2. Instructors Table: Stores instructor information.
3. Course_Instructors Table: Links courses to instructors.
4. Enrollments Table: Remains the same.

Courses Table:

Course_ID Course_Name

C101 Math

C102 English

C103 History

Instructors Table:
Instructor_ID Instructor_Name

1 Dr. Smith

2 Dr. Johnson

3 Dr. Lee

Course_Instructors Table:

Course_ID Instructor_ID

C101 1

C102 2

C103 3

Enrollments Table:

Student_ID Course_ID Grade

1 C101 A

1 C102 B

2 C101 A

2 C103 C

3 C102 A

Fourth Normal Form (4NF)

4NF requires the table to be in BCNF and should not contain any multi-valued dependencies. For
example, if a student could take multiple courses, and each course could have multiple instructors,
we would need to decompose the Enrollments table further to separate multi-valued dependencies.
However, this example does not contain any multi-valued dependencies, so the design remains
unchanged.

After applying 4NF:

The structure remains the same as BCNF because there are no multi-valued dependencies.