Core Concepts in Normalization

Before diving into the specific database management systems, it's essential to understand the
fundamental concepts of normalization.
What is Normalization?
Normalization is the process of organizing the columns (attributes) and tables (relations) of a
relational database to minimize data redundancy. The primary goal is to divide larger tables into
smaller, well-structured tables and define relationships between them.
Why is Normalization Important?
●​ Minimizes Data Redundancy: Reduces the amount of duplicate data in a database.
●​ Prevents Data Anomalies: Helps in avoiding issues with inserting, updating, and
deleting data.
●​ Improves Data Integrity: Ensures that the data is reliable and consistent.
●​ Simplifies Queries: Well-normalized tables are often easier to understand and query.
Types of Anomalies:
●​ Insertion Anomaly: The inability to add new data to the database without the presence
of other, unrelated data.
●​ Update Anomaly: The need to update the same piece of information in multiple places,
which can lead to inconsistencies if not all instances are updated.
●​ Deletion Anomaly: The unintentional loss of data when a record is deleted.

1. School DBMS
Unnormalized Form (UNF)
Let's start with an unnormalized table for a school. This table contains multiple values in a single
cell.
Table: School_Details
Student_ID Student_Name Student_Address Courses_Enrolled Teacher_Name Teacher_Subject

101 Anne Smith 123 Maple St Math, Science Mr. Davis, Mrs. Mathematics,
White Physics

102 Ben Green 456 Oak Ave History, English Ms. Jones History, English Lit

103 Chloe Brown 789 Pine Ln Math, Art Mr. Davis, Ms. Mathematics, Fine
Taylor Arts
Anomalies and Dependencies in UNF:
●​ Insertion Anomaly: We cannot add a new course until a student enrolls in it. Similarly,
we cannot add a new teacher until they are assigned to a student.
●​ Update Anomaly: If Mr. Davis changes his subject, we would need to update this
information in multiple rows (for students 101 and 103). Missing an update would lead to
inconsistency.
●​ Deletion Anomaly: If student 102 drops out and we delete their record, we lose the
information that Ms. Jones teaches History and English Literature.
●​ Dependencies: The Teacher_Name and Teacher_Subject are dependent on the
Courses_Enrolled.
 First Normal Form (1NF)
What and Why: The first step is to ensure that the table is in 1NF. This means that each cell
should contain only a single value, and each record needs to be unique. We achieve this by
breaking down the multivalued attributes into separate rows.
Table: School_1NF
Student_ID Student_Name Student_Address Course Teacher_Name Teacher_Subject

101 Anne Smith 123 Maple St Math Mr. Davis Mathematics

101 Anne Smith 123 Maple St Science Mrs. White Physics

102 Ben Green 456 Oak Ave History Ms. Jones History

102 Ben Green 456 Oak Ave English Ms. Jones English Lit

103 Chloe Brown 789 Pine Ln Math Mr. Davis Mathematics

103 Chloe Brown 789 Pine Ln Art Ms. Taylor Fine Arts
Anomalies and Dependencies in 1NF:
●​ Insertion Anomaly: We are still unable to add a new student without them enrolling in a
course.
●​ Update Anomaly: The Student_Name and Student_Address are repeated for each course
a student takes. If Anne Smith's address changes, it needs to be updated in multiple rows.
●​ Deletion Anomaly: If a student drops a single course, we might accidentally delete the
student's entire record if they are only enrolled in that one course.
●​ Dependencies: We now have a composite primary key (Student_ID, Course). However,
Student_Name and Student_Address are only dependent on Student_ID (a partial
dependency). Teacher_Name and Teacher_Subject rely on the Course.

Second Normal Form (2NF)

What and Why: To achieve 2NF, we must remove all partial dependencies. A partial
dependency occurs when a non-key attribute is dependent on only a part of the composite
primary key. We do this by splitting the table into multiple tables.
Tables:
Student Table:
Student_ID Student_Name Student_Address

101 Anne Smith 123 Maple St

102 Ben Green 456 Oak Ave

103 Chloe Brown 789 Pine Ln

Course_Enrollment Table:
Enrollment_ID Student_ID Course_ID

1 101 C01

2 101 C02

3 102 C03

4 102 C04

5 103 C01

6 103 C05
Course_Teacher Table:
Course_ID Course_Name Teacher_Name Teacher_Subject

C01 Math Mr. Davis Mathematics

C02 Science Mrs. White Physics

C03 History Ms. Jones History

C04 English Ms. Jones English Lit

C05 Art Ms. Taylor Fine Arts

Anomalies and Dependencies in 2NF:

●​ Insertion Anomaly: In the Course_Teacher table, we cannot add a new teacher without
assigning them a course.
●​ Update Anomaly: If we need to update a teacher's subject specialty, we might still have
to do it in multiple places if they teach multiple courses in that subject area.
●​ Deletion Anomaly: If we cancel a course, say 'Art' (C05), we lose the information about
Ms. Taylor.
●​ Dependencies: In the Course_Teacher table, Teacher_Name determines the
Teacher_Subject. This is a transitive dependency because Course_ID determines
Teacher_Name, and Teacher_Name determines Teacher_Subject.

Third Normal Form (3NF)

What and Why: To reach 3NF, we must remove all transitive dependencies. A transitive
dependency is when a non-key attribute is dependent on another non-key attribute. We create
separate tables for these relationships.
Final 3NF Tables:
Student Table:
Student_ID Student_Name Student_Address

101 Anne Smith 123 Maple St

102 Ben Green 456 Oak Ave

103 Chloe Brown 789 Pine Ln

Enrollment Table:
Enrollment_ID Student_ID Course_ID

1 101 C01

2 101 C02

3 102 C03

4 102 C04

5 103 C01

6 103 C05

Course Table:
Course_ID Course_Name Teacher_ID

C01 Math T1

C02 Science T2

C03 History T3

C04 English T3

C05 Art T4
 Teacher Table:
Teacher_ID Teacher_Name Teacher_Subject

T1 Mr. Davis Mathematics

T2 Mrs. White Physics

T3 Ms. Jones History, English Lit

T4 Ms. Taylor Fine Arts

Reasoning for 3NF:

●​ No Redundancy: Each piece of information is stored only once.
●​ No Anomalies:
○​ Insertion: We can add a new student without enrolling them in a course. We can
add a new teacher without assigning them a course. We can add a new course
without any students enrolled.
○​ Update: Changing a student's address or a teacher's name is done in a single
record in their respective tables.
○​ Deletion: Deleting a student's enrollment in a course does not delete the student
or the course. Deleting a course does not delete the teacher.

2. College DBMS
The normalization process for a College DBMS is very similar to a School DBMS. We will use a
slightly different initial table to highlight different aspects.

Unnormalized Form (UNF)

Table: College_Data
Student_ID Student_Name Major Courses_Taken Professor_Name Professor_Office

S201 David Lee Computer CS101, CS202 Dr. Evans, Dr. A101, B203
Science Clark

S202 Emily Wang Biology BIO101, Dr. Garcia, Dr. C301, C305
CHEM101 Patel

S203 Frank Kim Computer CS101, Dr. Evans, Dr. A101, D401
Science MATH201 Moore
Anomalies and Dependencies in UNF:
●​ Insertion Anomaly: Cannot add a new professor until they are assigned to a course taken
by a student.
●​ Update Anomaly: If Dr. Evans moves to a new office, this must be updated for every
student taking his course.
●​ Deletion Anomaly: If Emily Wang is the only student in Biology and she drops out, we
lose the information about Dr. Garcia and his office.
●​ Dependencies: Professor_Name and Professor_Office are dependent on Courses_Taken.

First Normal Form (1NF)

Table: College_1NF
Student_ID Student_Name Major Course Professor_Name Professor_Office

S201 David Lee Computer CS101 Dr. Evans A101

Science

S201 David Lee Computer CS202 Dr. Clark B203

Science

S202 Emily Wang Biology BIO101 Dr. Garcia C301

S202 Emily Wang Biology CHEM10 Dr. Patel C305

1

S203 Frank Kim Computer CS101 Dr. Evans A101

Science

S203 Frank Kim Computer MATH20 Dr. Moore D401

Science 1

Anomalies and Dependencies in 1NF:

●​ Update Anomaly: The Student_Name and Major for a student are repeated.
●​ Partial Dependencies: Student_Name and Major depend only on Student_ID, which is
part of the composite primary key (Student_ID, Course).
Second Normal Form (2NF)
Tables:
Student Table:
Student_ID Student_Name Major

S201 David Lee Computer

Science

S202 Emily Wang Biology

S203 Frank Kim Computer

Science

Enrollment_Course_Professor Table:
Enrollment_ID Student_ID Course Professor_Name Professor_Office

1 S201 CS101 Dr. Evans A101

2 S201 CS202 Dr. Clark B203

3 S202 BIO101 Dr. Garcia C301

4 S202 CHEM101 Dr. Patel C305

5 S203 CS101 Dr. Evans A101

6 S203 MATH201 Dr. Moore D401

Anomalies and Dependencies in 2NF:
●​ Transitive Dependency: In the Enrollment_Course_Professor table, Course determines
Professor_Name, and Professor_Name determines Professor_Office.

Third Normal Form (3NF)

Final 3NF Tables:
Student Table:
Student_ID Student_Name Major

S201 David Lee Computer

Science

S202 Emily Wang Biology

S203 Frank Kim Computer

Science
Enrollment Table:
Enrollment_ID Student_ID Course_ID

1 S201 C1

2 S201 C2

3 S202 C3

4 S202 C4

5 S203 C1

6 S203 C5

Course Table:
Course_ID Course_Name Professor_ID

C1 CS101 P1

C2 CS202 P2

C3 BIO101 P3

C4 CHEM101 P4

C5 MATH201 P5

Professor Table:
Professor_ID Professor_Name Professor_Office

P1 Dr. Evans A101

P2 Dr. Clark B203

P3 Dr. Garcia C301

P4 Dr. Patel C305

P5 Dr. Moore D401

Reasoning for 3NF:
●​ Data is stored logically and without redundancy.
●​ Anomalies are eliminated, ensuring data integrity.
 3. University DBMS
A University DBMS can be more complex, often including departments and research groups.
Unnormalized Form (UNF)
Table: University_Records
Professor_ID Professor_Name Department_Na Department_ Student Student_Name Student_Thesis_Topics
me Head _ID

P301 Dr. Allen Physics Dr. Brown U401 Nina Chen Quantum Entanglement,
String Theory

P302 Dr. Davis History Dr. Green U402 Omar Khan Renaissance Art,
Roman Empire

P301 Dr. Allen Physics Dr. Brown U403 Peter Jones General Relativity

Anomalies and Dependencies in UNF:

●​ Update Anomaly: If Dr. Brown steps down as the Head of the Physics Department, this
information needs to be updated in multiple records.
●​ Deletion Anomaly: If Omar Khan graduates and his record is deleted, we lose the
information that Dr. Davis is in the History department.
●​ Insertion Anomaly: We cannot add a new department until a professor from that
department advises a student.
●​ Dependencies: Department_Head is dependent on Department_Name. Student_Name
and Student_Thesis_Topics are dependent on Student_ID.

First Normal Form (1NF)

Table: University_1NF
Professor_ID Professor_Name Department Department Student Student_Name Thesis_Topic
_Name _Head _ID

P301 Dr. Allen Physics Dr. Brown U401 Nina Chen Quantum
Entanglement

P301 Dr. Allen Physics Dr. Brown U401 Nina Chen String Theory

P302 Dr. Davis History Dr. Green U402 Omar Khan Renaissance Art

P302 Dr. Davis History Dr. Green U402 Omar Khan Roman Empire

P301 Dr. Allen Physics Dr. Brown U403 Peter Jones General
Relativity
Anomalies and Dependencies in 1NF:
●​ Partial Dependencies: Professor_Name, Department_Name, and Department_Head are
dependent only on Professor_ID. Student_Name is dependent only on Student_ID.

Second Normal Form (2NF)

Tables:
Professor_Department Table:
Professor_ID Professor_Name Department_Name Department_Head

P301 Dr. Allen Physics Dr. Brown

P302 Dr. Davis History Dr. Green

Student_Advisor_Thesis Table:
Record_ID Professor_ID Student_ID Student_Name Thesis_Topic

1 P301 U401 Nina Chen Quantum

Entanglement

2 P301 U401 Nina Chen String Theory

3 P302 U402 Omar Khan Renaissance Art

4 P302 U402 Omar Khan Roman Empire

5 P301 U403 Peter Jones General Relativity

Anomalies and Dependencies in 2NF:

●​ Transitive Dependency: In Professor_Department, Department_Name determines
Department_Head.
●​ Partial Dependency: In Student_Advisor_Thesis, Student_Name depends on
Student_ID.

Third Normal Form (3NF)

Final 3NF Tables:
Professor Table:
Professor_ID Professor_Name Department_ID

P301 Dr. Allen D1

P302 Dr. Davis D2

Department Table:
Department_ID Department_Name Department_Head_ID

D1 Physics P_Head1

D2 History P_Head2
(A further table for Department Heads would also be ideal to link back to the Professor table)
Student Table:
Student_ID Student_Name

U401 Nina Chen

U402 Omar Khan

U403 Peter Jones

Advisory_Thesis Table:
Record_ID Professor_ID Student_ID Thesis_Topic

1 P301 U401 Quantum Entanglement

2 P301 U401 String Theory

3 P302 U402 Renaissance Art

4 P302 U402 Roman Empire

5 P301 U403 General Relativity

Reasoning for 3NF:
●​ All transitive and partial dependencies have been removed.
●​ Each table represents a single entity (Professor, Department, Student) or a relationship
between them.

4. Bank DBMS from the beginning to the final 3NF structure, explaining each step in detail.
Unnormalized Form (UNF)
We begin with a single, unnormalized table that contains all the bank's customer information.
This table includes repeating groups and multivalued attributes, which is the starting point before
any normalization occurs.
 Table: Bank_Customer_Info
Customer_ID Customer_Name Customer_Add Account_Num Account_T Branch_C Branch_Ad
ress bers ypes ode dress

C501 John Doe 789 River Rd A1001, L2001 Savings, B01 1 Main St
Personal
Loan

C502 Jane Roe 123 Lakeview A1002 Checking B02 2 Broad St

C501 John Doe 789 River Rd A1003 Checking B01 1 Main St

Dependencies and Anomalies in UNF:
●​ Multivalued Attributes: The Account_Numbers and Account_Types columns hold
multiple values in a single cell, violating the principle of atomicity.
●​ Update Anomaly: If John Doe moves, his Customer_Address needs to be updated in two
separate rows. If the address of branch B01 changes, it also has to be updated in two
rows. Missing an update leads to inconsistent data.
●​ Insertion Anomaly: We cannot add a new branch to our system until at least one
customer opens an account there.
●​ Deletion Anomaly: If Jane Roe (customer C502) closes her only account and we delete
her record, we completely lose all information about branch B02, including its address.

First Normal Form (1NF)

What and Why: The first step is to ensure every cell contains a single, atomic value. We
achieve this by breaking down the multivalued attributes (Account_Numbers and
Account_Types) into separate rows, so each row represents a single account.
Table: Bank_1NF
Customer_ID Customer_Name Customer_Addr Account_Nu Account_T Branch_C Branch_Add
ess mber ype ode ress

C501 John Doe 789 River Rd A1001 Savings B01 1 Main St

C501 John Doe 789 River Rd L2001 Personal B01 1 Main St

Loan

C502 Jane Roe 123 Lakeview A1002 Checking B02 2 Broad St

C501 John Doe 789 River Rd A1003 Checking B01 1 Main St

Dependencies and Anomalies in 1NF:
While we've solved the multivalue problem, significant redundancy remains, leading to new
issues. We can consider Account_Number as our primary key here.
●​ Partial Dependencies: This is not a primary issue here since Account_Number is a
singlecolumn key, but the data structure reveals functional dependencies that cause
problems.
 ●​ Data Redundancy: The details for Customer C501 (John Doe, 789 River Rd) are
repeated for every account he owns. The details for Branch B01 (1 Main St) are also
repeated for every account at that branch.
●​ Transitive Dependencies: Branch_Address is dependent on Branch_Code, which is not
the primary key. This is a classic transitive dependency: Account_Number →
Branch_Code → Branch_Address.

Second Normal Form (2NF)

What and Why: The goal of 2NF is to remove partial dependencies. In a broader sense, we
separate data into tables based on what they describe. We can see that some columns describe the
customer, while others describe the account and branch. We will create a separate Customer table
to hold customerspecific data, removing that redundant information from our main table.
Tables:
1. Customer Table​
This table isolates customerspecific information. Now, John Doe's details exist in only one place.
Customer_ID Customer_Name Customer_Address

C501 John Doe 789 River Rd

C502 Jane Roe 123 Lakeview

2. Account_Branch Table​
This table contains the remaining information about accounts, linking them back to the customer
via the Customer_ID foreign key.
Account_Number Account_Type Customer_ID (FK) Branch_Code Branch_Address

A1001 Savings C501 B01 1 Main St

L2001 Personal Loan C501 B01 1 Main St

A1002 Checking C502 B02 2 Broad St

A1003 Checking C501 B01 1 Main St

Anomalies and Dependencies in 2NF:
We have resolved the redundancy of customer data. However, the Account_Branch table still has
a problem:
●​ Transitive Dependency: As identified, Branch_Address depends on Branch_Code, not
on the table's primary key (Account_Number). The redundancy of branch information
still exists. If the address for B01 changes, we still have to update it in three places.

Third Normal Form (3NF)

What and Why: To achieve 3NF, we must eliminate the transitive dependency identified in the
2NF table. We do this by splitting the Account_Branch table. We create a dedicated Branch table
to store branch information, ensuring all attributes in that table are dependent only on the
Branch_Code.
Final 3NF Tables
This is the final, optimal structure for this database.
1. Customer Table​
(Unchanged from 2NF)
Customer_ID Customer_Name Customer_Address

C501 John Doe 789 River Rd

C502 Jane Roe 123 Lakeview

2. Branch Table​
(New table created to remove transitive dependency)
Branch_Code Branch_Address

B01 1 Main St

B02 2 Broad St

3. Account Table​
(Final version of the account table, now linking to both Customer and Branch tables with foreign
keys)
Account_Number Account_Type Customer_ID (FK) Branch_Code (FK)

A1001 Savings C501 B01

L2001 Personal Loan C501 B01

A1002 Checking C502 B02

A1003 Checking C501 B01

Reasoning and Why the 3NF Structure is Superior
This final 3NF design is robust and efficient because it has successfully eliminated the data
anomalies:
●​ No Redundancy: Each piece of information is stored only once. The address for branch
B01 ("1 Main St") now exists in a single row in the Branch table.
●​ No Update Anomaly: If branch B01 relocates, you only need to update one record in
the Branch table. This change is instantly reflected for all associated accounts.
●​ No Insertion Anomaly: A new branch can be added to the Branch table at any time,
even if it has no accounts yet.
●​ No Deletion Anomaly: Deleting customer C502 and their account A1002 will not affect
the Branch table. The record for branch B02 remains intact.