Advanced Database Management System — Normalization: 1NF, 2NF, 3NF

ADVANCED DATABASE MANAGEMENT SYSTEM

Assignment

Database Normalization
First Normal Form | Second Normal Form | Third Normal Form

Course: Advanced Database Management System (ADBMS)

Department of Computer Science

Page 1
 Advanced Database Management System — Normalization: 1NF, 2NF, 3NF

1. INTRODUCTION TO NORMALIZATION

Database normalization is the process of organizing a relational database to reduce data redundancy and
improve data integrity. It was first proposed by Edgar F. Codd in 1970 as part of his relational model.
Normalization is achieved by applying a series of rules called Normal Forms (NF), each eliminating a
specific type of data anomaly.

The main objectives of normalization are:

- Eliminating redundant (repeated) data across the database
- Ensuring only logically related data is stored in a single table
- Preventing insertion, update, and deletion anomalies
- Producing a flexible and maintainable database structure

This assignment covers the three foundational normal forms — 1NF, 2NF, and 3NF — each building
upon the last, with definitions, examples, and step-by-step conversions.

2. FIRST NORMAL FORM (1NF)

2.1 Definition
A table is in First Normal Form (1NF) if all of the following conditions hold:
1. Each column contains only atomic (indivisible, single) values.
2. Each column contains values of a single data type.
3. Each row is uniquely identifiable — there must be a Primary Key.
4. There are no repeating groups or multi-valued attributes.

Key Rule: Every attribute in a table must hold exactly one atomic value. No sets, lists, or multiple
values are permitted in any single cell.

2.2 Before 1NF — Unnormalized Table

The following Student_Courses table violates 1NF because the Courses and PhoneNumbers columns hold
multiple values in a single cell:

StudentID StudentName Courses (Multi- PhoneNumbers

valued) (Multi-valued)

S101 Ali Hassan Math, Physics, 0300-1234, 0321-5678

Page 2
 Advanced Database Management System — Normalization: 1NF, 2NF, 3NF

StudentID StudentName Courses (Multi- PhoneNumbers

valued) (Multi-valued)

Chemistry

S102 Ayesha Khan Biology, English 0311-9999

S103 Umar Farooq Computer Science, 0333-1122, 0345-3344

Math, Urdu

Problems: The multi-valued Courses and PhoneNumbers columns make it impossible to search, sort, or
update individual values reliably. This is a direct violation of atomicity.

2.3 After 1NF — Normalized Table

To convert to 1NF, each multi-valued entry is separated into its own row, ensuring every cell holds
exactly one atomic value:

StudentID StudentName Course PhoneNumber

S101 Ali Hassan Math 0300-1234

S101 Ali Hassan Physics 0300-1234

S101 Ali Hassan Chemistry 0321-5678

S102 Ayesha Khan Biology 0311-9999

S102 Ayesha Khan English 0311-9999

S103 Umar Farooq Computer Science 0333-1122

S103 Umar Farooq Math 0345-3344

S103 Umar Farooq Urdu 0333-1122

Primary Key: (StudentID, Course) — composite key ensuring each row is unique.

2.4 Summary of 1NF

What 1NF achieves: All attribute values are atomic. Repeating groups and multi-valued attributes are
eliminated. The table now has a well-defined primary key. This is the essential foundation upon which
2NF and 3NF are built.

3. SECOND NORMAL FORM (2NF)

Page 3
 Advanced Database Management System — Normalization: 1NF, 2NF, 3NF

3.1 Definition
A table is in Second Normal Form (2NF) if and only if:
5. It is already in First Normal Form (1NF), AND
6. Every non-key attribute is fully functionally dependent on the entire primary key — not just a
part of it.

Partial Dependency: Occurs when a non-key attribute depends on only a portion of a composite primary
key, rather than the full composite key. 2NF eliminates all such partial dependencies.

Key Rule: 2NF applies only to tables with a composite primary key. A non-key attribute must
depend on ALL columns of the composite key, not merely one column of it.

3.2 Before 2NF — Table with Partial Dependencies

Consider the following Order_Details table, which is in 1NF but contains partial dependencies:

Composite Primary Key: (OrderID, ProductID)

OrderID ProductID ProductNa Quantity Customer Customer

(PK) (PK) me Name City

O001 P10 Laptop 2 Bilal Ahmed Lahore

O001 P20 Mouse 5 Bilal Ahmed Lahore

O002 P10 Laptop 1 Sara Malik Karachi

O003 P30 Keyboard 3 Ahmed Raza Islamabad

Functional Dependency Analysis:

- (OrderID, ProductID) -> Quantity [FULL dependency — correct]
- ProductID -> ProductName [PARTIAL dependency — violation]
- OrderID -> CustomerName, CustomerCity [PARTIAL dependency — violation]

Problems: ProductName repeats for every order containing that product. If a product is renamed, every
row must be updated. Deleting all orders for a customer erases customer data — a deletion anomaly.

3.3 After 2NF — Decomposed Tables

The table is split into three tables, each with full functional dependencies on its own primary key:

Table A: Orders

Page 4
 Advanced Database Management System — Normalization: 1NF, 2NF, 3NF

OrderID (PK) CustomerName CustomerCity

O001 Bilal Ahmed Lahore

O002 Sara Malik Karachi

O003 Ahmed Raza Islamabad

Table B: Products
ProductID (PK) ProductName

P10 Laptop

P20 Mouse

P30 Keyboard

Table C: Order_Details
OrderID (PK, FK) ProductID (PK, FK) Quantity

O001 P10 2

O001 P20 5

O002 P10 1

O003 P30 3

Result: All partial dependencies have been removed. Each non-key attribute now fully depends on the
whole primary key within its own table.

3.4 Summary of 2NF

What 2NF achieves: Elimination of partial dependencies from composite-key tables. Redundancy caused
by attributes that rely on only part of the key is resolved by splitting the table into logically cohesive
smaller tables.

4. THIRD NORMAL FORM (3NF)

4.1 Definition
A table is in Third Normal Form (3NF) if and only if:
7. It is already in Second Normal Form (2NF), AND

Page 5
 Advanced Database Management System — Normalization: 1NF, 2NF, 3NF

8. No non-key attribute is transitively dependent on the primary key — i.e., no non-key attribute
depends on another non-key attribute.

Transitive Dependency: If A -> B and B -> C, where A is the primary key and B, C are non-key
attributes, then C is transitively dependent on A through B. This indirect dependency is what 3NF
eliminates.

Key Rule: Every non-key attribute must depend directly and solely on the primary key — nothing
but the key, and the whole key (Codd's statement of 3NF).

4.2 Before 3NF — Table with Transitive Dependencies

Consider the Employee table below (already in 2NF, single primary key EmpID):

Primary Key: EmpID

EmpID EmpNam DeptID DeptNam DeptHead Salary

(PK) e e

E001 Zara Noor D01 IT Mr. 85,000

Kamran

E002 Fahad Ali D02 HR Ms. Nadia 60,000

E003 Hina Mirza D01 IT Mr. 90,000

Kamran

E004 Tariq Shah D03 Finance Mr. Asad 75,000

E005 Laila Raza D02 HR Ms. Nadia 58,000

Functional Dependency Analysis:

- EmpID -> EmpName, DeptID, Salary [Direct — correct]
- EmpID -> DeptName [TRANSITIVE: EmpID -> DeptID -> DeptName —
violation]
- EmpID -> DeptHead [TRANSITIVE: EmpID -> DeptID -> DeptHead —
violation]

Problems: DeptName and DeptHead are repeated for every employee in the same department. Changing
a department head requires updating multiple rows. Deleting all employees of a department destroys
department information — a deletion anomaly.

4.3 After 3NF — Decomposed Tables

Page 6
 Advanced Database Management System — Normalization: 1NF, 2NF, 3NF

The transitive dependencies are removed by separating department information into its own table:

Table A: Employees
EmpID (PK) EmpName DeptID (FK) Salary

E001 Zara Noor D01 85,000

E002 Fahad Ali D02 60,000

E003 Hina Mirza D01 90,000

E004 Tariq Shah D03 75,000

E005 Laila Raza D02 58,000

Table B: Departments
DeptID (PK) DeptName DeptHead

D01 IT Mr. Kamran

D02 HR Ms. Nadia

D03 Finance Mr. Asad

Result: DeptName and DeptHead now depend directly on DeptID within the Departments table. The
Employees table only references DeptID as a foreign key, removing all transitive chains.

4.4 Summary of 3NF

What 3NF achieves: Elimination of all transitive dependencies. Every non-key attribute now depends
only and directly on the primary key. The database is free of the most common forms of redundancy and
anomaly.

5. COMPARISON OF NORMAL FORMS

Normal Form Prerequisite Dependency Problem Solved

Removed

1NF None Multi-valued / Repeating Non-atomic values in

attributes cells

2NF In 1NF Partial dependency on Redundancy from part-

composite key key attributes

Page 7
 Advanced Database Management System — Normalization: 1NF, 2NF, 3NF

Normal Form Prerequisite Dependency Problem Solved

Removed

3NF In 2NF Transitive dependency Redundancy from

(non-key to non-key) indirect key paths

6. DATA ANOMALIES AND HOW NORMALIZATION RESOLVES THEM

Without normalization, databases are susceptible to three classes of anomaly:

6.1 Insertion Anomaly

It becomes impossible to insert data about one entity without also providing data about an unrelated
entity. For example, in an unnormalized Employee-Department table, a new department cannot be
recorded unless at least one employee already belongs to it.

6.2 Update Anomaly

A single logical change requires updating multiple rows. For example, if a department head changes,
every row belonging to that department must be updated. Missing even one row produces inconsistent
data across the database.

6.3 Deletion Anomaly

Deleting one piece of information inadvertently destroys other important data. For example, removing the
sole employee in a department also deletes all information about that department.

Resolution: Normalization ensures each fact is stored in exactly one place, so insertion, update, and
deletion operations affect only a single row in a single table.

7. CONCLUSION

Database normalization is an indispensable discipline in relational database design. Applying the normal
forms progressively yields the following guarantees:

- 1NF — Atomicity: every cell holds exactly one value; repeating groups are eliminated.
- 2NF — Full dependency: all non-key attributes depend on the complete composite key.

Page 8
 Advanced Database Management System — Normalization: 1NF, 2NF, 3NF

- 3NF — No transitivity: no non-key attribute depends indirectly on the primary key.

Each normal form builds upon the previous, steadily reducing redundancy and enforcing logical
consistency. A well-normalized database is easier to maintain, less error-prone, and more adaptable
to future requirements. In professional database engineering, 3NF represents the widely accepted standard
that balances data integrity with query efficiency.

Mastery of normalization is a core competency for any database designer or administrator. It ensures
that the database faithfully and efficiently represents real-world relationships and business rules.

Page 9