Normalization Solutions for Advanced
Database System Development
Module Code: CC6001NI
Prepared for Exam Preparation
Date: May 28, 2025
(C) London Metropolitan University
Normalization of Employee Allocation Table (Sample4
and Sample Question 1)
The following table represents employee allocation details:
E_id E_name E_dob E_hire_date D_id D_name Salary Loc_id
E1 Raman Singh 1997-May-17 2018-Sep-11 Dep1 Academics 30000 Loc1
E2 Rajiv Shakya 1998-Jan-2 2019-Jan-12 Dep1 Academics 40000 Loc1
E3 Jeeban Raj Bhat 1997-Feb-20 2016-Feb-11 Dep1 Academics 35000 Loc1
E3 Jeeban Raj Bhat 1997-Feb-20 2016-Mar-11 Dep1 Academics 30000 Loc2
Assumptions
• E_id uniquely identifies an employee, but an employee may have multiple job as-
signments (repeating groups for E_hire_date, D_id, D_name, Salary, Loc_id,
Loc_name, job_status).
• D_id uniquely identifies a department, and Loc_id uniquely identifies a location.
• Salary and job_status are tied to specific job assignments.
Unnormalized Form (UNF)
The table is in UNF due to repeating groups for employees with multiple job assignments
(e.g., E3 has two assignments). Using bracket notation:
• Table: Employee (E_id, E_name, E_dob, [E_hire_date, D_id, D_name, Salary,
Loc_id, Loc_name, job_status])
• Primary Key: {E_id}
• Repeating group: [E_hire_date, D_id, D_name, Salary, Loc_id, Loc_name,
job_status]
First Normal Form (1NF)
To achieve 1NF, eliminate repeating groups by moving them into a single column and
including the primary key from the non-repeating group:
• Combine repeating group attributes into a single table, copying E_id as part of the
key.
• Table: Employee_Job (E_id, E_hire_date, D_id, E_name, E_dob, D_name,
Salary, Loc_id, Loc_name, job_status)
• Primary Key: {E_id, E_hire_date, D_id} (E_hire_date and D_id distinguish
multiple assignments for the same E_id).
All attributes are atomic, and there are no repeating groups within rows.
1
Functional Dependencies
• E_id → E_name, E_dob
• D_id → D_name
• Loc_id → Loc_name
• E_id, E_hire_date, D_id → Salary, job_status, Loc_id
Second Normal Form (2NF)
To achieve 2NF, ensure no partial dependencies (non-key attributes must depend on
the entire primary key). The primary key {E_id, E_hire_date, D_id} causes partial
dependencies:
• E_name, E_dob depend only on E_id.
• D_name depends only on D_id.
• Loc_name depends only on Loc_id.
Decompose into:
• Employee (E_id, E_name, E_dob)
• Department (D_id, D_name)
• Location (Loc_id, Loc_name)
• Job (E_id, E_hire_date, D_id, Salary, job_status, Loc_id)
• Primary Keys: Employee: {E_id}, Department: {D_id}, Location: {Loc_id},
Job: {E_id, E_hire_date, D_id}
• Foreign Keys: Job: E_id (references Employee), D_id (references Department),
Loc_id (references Location)
Third Normal Form (3NF)
To achieve 3NF, eliminate transitive dependencies (non-key attributes depending on other
non-key attributes). In the Job table:
• Loc_id → Loc_name (already handled by the Location table).
• No transitive dependencies remain, as Salary and job_status depend on the full
primary key.
The tables are in 3NF:
• Employee (E_id, E_name, E_dob)
2
• Department (D_id, D_name)
• Location (Loc_id, Loc_name)
• Job (E_id, E_hire_date, D_id, Salary, job_status, Loc_id)
• Foreign Keys: Job: E_id, D_id, Loc_id
3
Normalization of Student Information Table (Sam-
ple5)
The following table represents student information:
Student_ID Name Address Age Zip Code City Dept_ID
2 Faiz house 18 Defence Club 21 0 Karachi 4
2 Faiz Street 92 house 2 21 48758 Lahore 4
3 Nouman Street 19 House 20 19 9887 Faisalabad 5
3 Nouman Officer’s Residence New York 19 6556 New York 5
4 Jerry Street 18 House 29 22 265 Dubai 6
4 Jerry Greens building 3 22 5555 Abu Dhabi 6
Assumptions
• Student_ID uniquely identifies a student, but a student may have multiple ad-
dresses (repeating groups for Address, Zip Code, City).
• Dept_ID uniquely identifies a department.
• Age is a student attribute, not tied to address.
• Zip Code and City are associated with a specific address.
Unnormalized Form (UNF)
The table is in UNF due to repeating groups for students with multiple addresses (e.g.,
StudentI D2).U singbracketnotation :
Table: Student (Student_ID, Name, Age, Dept_ID, Department Name, [Address,
Zip Code, City])
Primary Key: {Student_ID}
Repeating group: [Address, Zip Code, City]
First Normal Form (1NF)
To achieve 1NF, eliminate repeating groups by moving them into a single column
and including the primary key:
• Combine repeating group attributes into a single table, copying Student_ID.
• Table: Student_Address (Student_ID, Address, Name, Age, Zip Code, City,
Dept_ID, Department Name)
• Primary Key: {Student_ID, Address} (Address distinguishes multiple ad-
dresses for the same Student_ID).
4
Functional Dependencies
• Student_ID → Name, Age, Dept_ID
• Dept_ID → Department Name
• Address → Zip Code, City
• Student_ID, Address → Zip Code, City, Dept_ID
Second Normal Form (2NF)
To achieve 2NF, ensure no partial dependencies. The primary key {Student_ID,
Address} causes partial dependencies:
• Name, Age, Dept_ID depend only on Student_ID.
• Department Name depends only on Dept_ID.
Decompose into:
• Student (Student_ID, Name, Age, Dept_ID)
• Department (Dept_ID, Department Name)
• Address (Student_ID, Address, Zip Code, City)
• Primary Keys: Student: {Student_ID}, Department: {Dept_ID}, Ad-
dress: {Student_ID, Address}
• Foreign Keys: Student: Dept_ID (references Department), Address: Stu-
dent_ID (references Student)
Third Normal Form (3NF)
To achieve 3NF, eliminate transitive dependencies. In the Address table:
• Address → Zip Code, City (transitive dependency).
Introduce an Address_ID to remove the dependency:
• Address (Address_ID, Address, Zip Code, City)
• Student_Address (Student_ID, Address_ID)
• Primary Keys: Address: {Address_ID}, Student_Address: {Student_ID,
Address_ID}
• Foreign Keys: Student_Address: Student_ID (references Student), Ad-
dress_ID (references Address)
Final 3NF tables:
• Student (Student_ID, Name, Age, Dept_ID)
• Department (Dept_ID, Department Name)
• Address (Address_ID, Address, Zip Code, City)
• Student_Address (Student_ID, Address_ID)
5
• Foreign Keys: Student: Dept_ID; Student_Address: Student_ID, Ad-
dress_ID
