UCI 204: RELATIONAL DATABASE
MANAGEMENT SYSTEM
NORMALIZATION
DEFINITION
Normalization is the process of organizing data
in a relational database to reduce redundancy and
improve data integrity
It involves decomposing large tables into smaller,
well-structured tables and defining relationships
between them.
OBJECTIVES OF NORMALIZATION
Normalization aims to:
• Eliminate data redundancy – Avoid storing the
same data multiple times.
• Ensure data integrity – Maintain consistency and
correctness of data.
• Avoid update anomalies – Prevent problems
during insert, update, or delete operations.
• Improve database efficiency – Simplify data
management.
PROBLEMS OF POOR DATABASE DESIGN
1. Insertion Anomaly
Occurs when data cannot be inserted because other data is
missing.
Example:
StudentNam
StudentID Course Lecturer
e
101 Alice DBMS Dr. Kamau
PROBLEMS OF POOR DATABASE DESIGN
2. Update Anomaly
Occurs when updating the same information in multiple
rows.
Example:
If Dr. Kamau changes name, every record must be
updated.
PROBLEMS OF POOR DATABASE DESIGN
3. Deletion Anomaly
Deleting a record may remove important information
unintentionally.
Example:
Deleting the last student enrolled in DBMS removes
the lecturer information.
Functional Dependencies (FD)
A Functional Dependency describes a relationship
between attributes in a relation.
If attribute A determines attribute B, then:
A→B
Meaning:
If two rows have the same value of A, they must have the
same value of B.
Functional Dependencies (FD)
Example
Student Table
StudentID StudentName Department
101 Alice Computer Science
102 Brian IT
Functional Dependencies:
StudentID → StudentName
StudentID → Department
Functional Dependencies (FD)
Types of Functional Dependencies
1. Trivial Dependency
A dependency where the right side is part of the left side.
Example:
(A, B) → A
Functional Dependencies (FD)
Types of Functional Dependencies
2. Non-Trivial Dependency
Right side is not a subset of left side.
Example:
StudentID → StudentName
Functional Dependencies (FD)
Types of Functional Dependencies
3. Fully Functional Dependency
Attribute depends on the entire primary key.
Example:
(StudentID, CourseID) → Grade
Functional Dependencies (FD)
Types of Functional Dependencies
4. Partial Dependency
Attribute depends on part of a composite key.
Example:
(StudentID, CourseID) → StudentName
Here StudentName depends only on StudentID,
not the full key.
Non-Loss (Lossless) Decomposition
A decomposition is lossless if the original table can be
reconstructed using natural joins without losing
information.
Example:
Original table
StudentNam
StudentID CourseID CourseName
e
101 C1 Alice DBMS
Non-Loss (Lossless) Decomposition
Functional Dependencies:
StudentID → StudentName
CourseID → CourseName
Decompose into:
……
Non-Loss (Lossless) Decomposition
Student Table
StudentID StudentName
101 Alice
Course Table
CourseID CourseName
C1 DBMS
Enrollment Table
StudentID CourseID
101 C1
Non-Loss (Lossless) Decomposition
Using joins, the original table can be reconstructed.
Therefore the decomposition is lossless.
Non-Loss (Lossless) Decomposition
IMPORTACE OF LOSSLESS DECOMPOSITION
Lossless decomposition ensures:
• No data is lost
• No spurious tuples are generated
• Original relation can be reconstructed
Dependency Preservation
A decomposition is dependency preserving if all
functional dependencies can be enforced without
performing joins.
Example:
Dependency Preservation
Example:
Original relation:
R(A, B, C)
Functional dependencies:
A→B
B→C
Dependency Preservation
Example:
If decomposed into:
R1(A,B)
R2(B,C)
Both dependencies are preserved.
Dependency Preservation
Example:
But if decomposed into:
R1(A,B)
R2(A,C)
Dependency B → C cannot be enforced without joining
tables.
Hence dependency is not preserved.
Dependency Preservation
IMPORTANCE:
• Reduces query complexity
• Improves efficiency
• Ensures integrity constraints are easily enforced
FIRST NORMAL FORM (1NF)
A relation is in First Normal Form (1NF) if:
• Each attribute contains atomic values
• No repeating groups
• Each field contains single values
FIRST NORMAL FORM (1NF)
Example (Not in 1NF)
StudentID Name Courses
101 Alice DBMS, Networks
Courses contain multiple values.
FIRST NORMAL FORM (1NF)
Convert to 1NF
StudentID Name Courses
101 Alice DBMS
101 Alice Networks
Courses contain multiple values.
SECOND NORMAL FORM (2NF)
A table is in Second Normal Form (2NF) if:
• It is already in 1NF
• No partial dependency exists
Meaning:
Non-key attributes depend on the entire primary key.
SECOND NORMAL FORM (2NF)
Example (Not in 2NF)
StudentID CourseID StudentName CourseName
Primary Key:
(StudentID, CourseID)
Functional Dependencies:
StudentID → StudentName
SECOND NORMAL FORM (2NF)
Decomposition
Student Table
| StudentID | StudentName |
Course Table
| CourseID | CourseName |
Enrollment Table
| StudentID | CourseID |
Now the design satisfies 2NF.
THIRD NORMAL FORM (3NF)
A relation is in Third Normal Form (3NF) if:
• It is in 2NF
• No transitive dependency exists
THIRD NORMAL FORM (3NF)
Transitive Dependency
If
A→B
B→C
Then:
A→C
This is a transitive dependency.
THIRD NORMAL FORM (3NF)
Example (NOT in 3NF)
DepartmentNam
StudentID DepartmentID
e
Functional Dependencies:
StudentID → DepartmentID
DepartmentID → DepartmentName
Here:
StudentID → DepartmentName
THIRD NORMAL FORM (3NF)
Decomposition
Student Table
| StudentID | DepartmentID |
Department Table
| DepartmentID | DepartmentName |
Now the table is in 3NF.
BOYCE-CODD NORMAL FORM (BCNF)
Example (Not in BCNF)
Student Course Instructor
Functional Dependencies:
(Student, Course) → Instructor
Instructor → Course
Instructor determines Course but Instructor is not a
BOYCE-CODD NORMAL FORM (BCNF)
Decomposition
Split into:
Instructor Table
| Instructor | Course |
Enrollment Table
| Student | Instructor |
Now the relation satisfies BCNF.
SUMMARY OF NORMAL FORMS
Normal Form Rule
1NF No repeating groups, atomic attributes
2NF No partial dependency
3NF No transitive dependency
BCNF Determinant must be a super key
ADVANTAGES OF NORMALIZATION
• Eliminates data redundancy
• Improves data consistency
• Simplifies database structure
• Improves data integrity
• Reduces storage requirements
DISADVANTAGES OF
NORMALIZATION
• Too many tables may increase complexity
• Queries may require multiple joins
• Performance may decrease in highly normalized systems
STEP-BY-STEP NORMALIZATION
Original Table
| StudentID | StudentName | CourseID | CourseName | Lecturer |
STEP-BY-STEP NORMALIZATION
Original Table
| StudentID | StudentName | CourseID | CourseName | Lecturer |
Step 1: Identify dependencies
StudentID → StudentName
CourseID → CourseName
CourseID → Lecturer
STEP-BY-STEP NORMALIZATION
Step 2: Decompose
Student Table
| StudentID | StudentName |
Course Table
| CourseID | CourseName | Lecturer |
Enrollment Table
| StudentID | CourseID |
STEP-BY-STEP NORMALIZATION
Original Table
| StudentID | StudentName | CourseID | CourseName |
Lecturer |