23CS45C / 23IT45C / 23AD45C - DATABASE MANAGEMENT SYSTEMS
NATIONAL ENGINEERING COLLEGE, K.R. NAGAR, KOVILPATTI – 628 503
(An Autonomous Institution, Affiliated to Anna University – Chennai)
COMPUTER SCIENCE AND ENGINEERING
DEPARTMENT OF INFORMATION TECHNOLOGY
ARTIFICAL INTELLIGENCE AND DATA SCIENCE
Even Semester - 2024-2025
Laboratory Practice - 05
CO6: DEMONSTRATE DATABASE NORMALIZATION USING APPROPRIATE
TOOL FOR ENHANCING DATA INTEGRITY
Lab Instruction Sheet
Exercise: 05 Apply Normalization theory to the given relation and promote it to highest normal
form.
Concept:
Normalization is a process of organizing database tables to eliminate redundancy and
ensure data integrity. Below are the rules and conditions for each Normal Form (NF):
First Normal Form (1NF)
Rule:
1. Eliminate Repeating Groups: Each column must contain atomic (indivisible) values.
2. Each column should contain a single value (no multivalued or composite attributes).
3. Each row should be uniquely identifiable (using a primary key).
Condition to Achieve 1NF:
If a table has duplicate columns for the same attribute or multiple values in a single
column, it is not in 1NF.
Solution: Convert multivalued attributes into separate rows.
Second Normal Form (2NF)
Rule:
1. Must be in 1NF.
2. Remove Partial Dependencies (Every non-key attribute must depend on the whole
primary key, not just a part of it).
Condition to Achieve 2NF:
If a composite primary key exists, ensure that all non-key attributes depend on the full
primary key, not just a part of it.
Solution: Split the table into separate tables based on functional dependencies.
23CS45C / 23IT45C / 23AD45C - DATABASE MANAGEMENT SYSTEMS
Third Normal Form (3NF)
Rule:
1. Must be in 2NF.
2. Remove Transitive Dependencies (A non-key attribute should not depend on another
non-key attribute).
Condition to Achieve 3NF:
If a non-key attribute depends on another non-key attribute instead of the primary key, it
violates 3NF.
Solution: Split into separate tables.
Boyce-Codd Normal Form (BCNF)
Rule:
1. Must be in 3NF.
2. For every functional dependency X → Y, X should be a super key (a candidate key).
Condition to Achieve BCNF:
If a non-trivial functional dependency exists where a non-key attribute determines part of
the key, it violates BCNF.
Solution: Break the table into smaller tables.
Problem Description:
Problem 01: Student_Course_Instructor Table
Consider the following STUDENT_COURSE table:
Student_I Student_Na Course_I Course_Na Instruct Instructor_Ph Dept_Loc
Dept
D me D me or one ation
S1 Alice C101 DBMS Prof. X 1234567890 CS Block A
S2 Bob C102 Networking Prof. Y 9876543210 IT Block B
S1 Alice C103 OS Prof. Z 4567891230 CS Block A
S3 Charlie C101 DBMS Prof. X 1234567890 CS Block A
S4 David C104 ML Prof. W 3216549870 AI Block C
Functional Dependencies (FDs):
1. Student_ID → Student_Name
2. Course_ID → Course_Name, Instructor, Instructor_Phone
3. Instructor → Instructor_Phone
4. Dept → Dept_Location
5. Course_ID → Dept
Check the above table to promote it to highest Normal Form.
23CS45C / 23IT45C / 23AD45C - DATABASE MANAGEMENT SYSTEMS
Problem 02: Employee_Project_Dept table
Consider the following employee Table:
Emp_I Emp_Nam Project_I Project_Nam Manage Manager_Pho Dep Dept_Locatio
D e D e r ne t n
E1 John P101 AI System M1 1234567890 IT Block A
E2 Alice P102 Web App M2 9876543210 CS Block B
E1 John P103 ML Model M3 4567891230 IT Block A
E3 Bob P101 AI System M1 1234567890 IT Block A
E4 Carol P104 Database M4 3216549870 CS Block B
Functional Dependencies (FDs):
1. Emp_ID → Emp_Name
2. Project_ID → Project_Name, Manager
3. Manager → Manager_Phone
4. Dept → Dept_Location
5. Project_ID → Dept
Task: Normalize this table step by step up to 3NF.
Problem 3: BOOK_AUTHOR_PUBLISHER Table
Book_ID Book_Title Author_ID Author_Name Publisher Publisher_Address Genre
B1 DBMS A1 Smith P1 NY, USA Tech
B2 OS A2 Johnson P2 London, UK Tech
B3 ML A1 Smith P1 NY, USA AI
B4 Python A3 Alice P3 Toronto, CAN CS
Functional Dependencies (FDs):
1. Book_ID → Book_Title, Genre, Publisher
2. Author_ID → Author_Name
3. Publisher → Publisher_Address
Task: Normalize this table step by step up to 3NF.
Problem 4: SUPPLIER_PRODUCT Table
Supplier_ID Supplier_Name Product_ID Product_Name Price Category Category_Desc
S1 ABC Corp P1 Laptop 1000 Electronics Devices
S2 XYZ Ltd P2 Phone 800 Electronics Devices
S3 LMN Inc P3 Sofa 500 Furniture Home Decor
S1 ABC Corp P4 TV 1200 Electronics Devices
23CS45C / 23IT45C / 23AD45C - DATABASE MANAGEMENT SYSTEMS
Functional Dependencies (FDs):
1. Supplier_ID → Supplier_Name
2. Product_ID → Product_Name, Price, Category
3. Category → Category_Desc
Task: Normalize this table step by step up to 3NF.
Problem 5: PATIENT_HOSPITAL Table
Patient_I Patient_Na Diseas Hospital_I Hospital_Na Docto Doctor_De Dept_Locati
D me e D me r pt on
P1 Alice Flu H1 City Hospital Dr. X General Block A
P2 Bob Cancer H2 Metro Care Dr. Y Oncology Block B
Surger
P3 Charlie H1 City Hospital Dr. Z Surgery Block A
y
Functional Dependencies (FDs):
1. Patient_ID → Patient_Name, Disease
2. Hospital_ID → Hospital_Name
3. Doctor → Doctor_Dept
4. Doctor_Dept → Dept_Location
Task: Normalize this table step by step up to 3NF.
Problem 6: ORDER_DETAILS Table
Order_ Customer_ Customer_N Product_ Product_Na Quanti Suppli Supplier_Loca
ID ID ame ID me ty er tion
O1 C1 John P1 Laptop 2 S1 NY, USA
O2 C2 Alice P2 Phone 1 S2 London, UK
O3 C1 John P3 Sofa 1 S3 Toronto, CAN
Functional Dependencies (FDs):
1. Customer_ID → Customer_Name
2. Product_ID → Product_Name, Supplier
3. Supplier → Supplier_Location
Task: Normalize this table step by step up to 3NF.