Database Normalization Assignment: Solutions and Examples

This document presents a comprehensive solution to a database normalization assignment, demonstrating the application of 1st, 2nd, and 3rd normal forms. The assignment covers the normalization of various relational database tables, including supplier, part, invoice, customer, and product data. The solution meticulously outlines the steps involved in achieving 3rd normal form, identifying functional dependencies and transitive dependencies, and restructuring tables to eliminate redundancy and ensure data integrity. The document also addresses the selection of appropriate solutions and identifies the normal form of given database structures. Overall, the assignment provides a detailed guide to database design principles and normalization techniques, offering practical examples and a clear understanding of the normalization process. Finally, the assignment includes a bibliography of relevant sources.

Running head: DATABASE NORMALIZATION
Database Normalization
Name of the Student
Name of the University

1DATABASE NORMALIZATION
Table of Contents
Answer to Question 1:.....................................................................................................................2
1.a Normalise the following relation to 3rd normal form, showing all steps and indicate any
assumption you have made..........................................................................................................2
1.b Normalize the following table to 3rd Normal form...............................................................3
Answer to Question 2:.....................................................................................................................5
Answer to Question 3:.....................................................................................................................8
3.a Selecting the Most Appropriate Solution:..............................................................................8
3.b Identifying the Normal Form:................................................................................................8
Answer to Question 4:.....................................................................................................................9
Bibliography:.................................................................................................................................11

2DATABASE NORMALIZATION
Answer to Question 1:
1.a Normalise the following relation to 3rd normal form, showing all steps and
indicate any assumption you have made
1st Normal Form:
List of attributes are SuppNO, Supp_Name, Supp_Addr, Part_Num, Part_Desc,
Unit_Price, Transfer_Price, CategoryNO, CatName. All the attributes are atomic and unique
identifier is present in the table.
2nd Normal Form:
The non-key attributes have to be dependent on the key-attribute.
Functional Dependencies
SuppNO → (Supp_Name, Supp_Addr)
Part_Num → (Part_Desc, Unit_Price, Transfer_Price)
CategoryNO → (CatName)
Tables:
So the normalized tables up to 2nd Normal Form are as following.
Supplier (SuppNO, Supp_Name, Supp_Addr)
Part (Part_Num, Part_Desc, Unit_Price, Transfer_Price, CategoryNO)

3DATABASE NORMALIZATION
Supplier_Part (SuppNO, Part_Num)
Category (CategoryNO, CatName)
3rd Normal Form:
There must not be any transitive dependency in the table.
All the tables are in 3rd Normal form as there is no transitive dependency among the
attributes.
Final Solution:
Supplier (SuppNO, Supp_Name, Supp_Addr)
Part (Part_Num, Part_Desc, Unit_Price, Transfer_Price, CategoryNO)
Supplier_Part (SuppNO, Part_Num)
Category (CategoryNO, CatName)
1.b Normalize the following table to 3rd Normal form
1st Normal Form:
List of attributes are INVOICE (Invoice_No, Cust_No, Cust_Name, Cust_Add,
Cust_Phone, Inv_Date, (Product_No, Product_Name, CategoryNO, CategoryName, Unit_Price,
Sale_Price Qty_Sold )). All the attributes are atomic and unique identifier is present in the table.
2nd Normal Form:
The non-key attributes have to be dependent on the key-attribute.
Functional Dependencies

4DATABASE NORMALIZATION
Invoice_No → (Inv_Date, Product_ No, Sale_Price, Qty_Sold)
Cust_No → (Cust_Name, Cust_Add, Cust_Phone)
Product_No → (Product_Name, Unit_Price)
CategoryNO → (CategoryName)
Tables:
So the normalized tables up to 2nd Normal Form are as following.
Invoice (Invoice_No, Product_No, Inv_Date, Sale_Price, Qty_Sold, Cust_No)
Customer (Cust_No, Cust_Name, Cust_Add, Cust_Phone)
Product (Product_No, CategoryNO, Product_Name, Unit_Price)
Category (CategoryNO, CategoryName)
3rd Normal Form:
There must not be any transitive dependency in the table.
Transitive Dependency
Product_No → Invoice_No (Sale_Price, Qty_Sold)

5DATABASE NORMALIZATION
Invoice_No does not → Product_No
Invoice_No → (Sale_Price, Qty_Sold)
Tables:
ProductSold (Invoice_No, Product_No, Sale_Price, Qty_Sold)
Invoice (Invoice_No, Inv_Date, Product_ No, Cust_No)
Product (Product_No, CategoryNO, Product_Name, Unit_Price)
All the tables are in 3rd Normal form as there is no transitive dependency among the
attributes.
Final Solutions:
ProductSold (Invoice_No, Product_No, Sale_Price, Qty_Sold)
Invoice (Invoice_No, Inv_Date, Product_ No, Cust_No)
Customer (Cust_No, Cust_Name, Cust_Add, Cust_Phone)
Product (Product_No, CategoryNO, Product_Name, Unit_Price)
Category (CategoryNO, CategoryName)
Answer to Question 2:
1st Normal Form:

6DATABASE NORMALIZATION
List of attributes are INVOICE (Invoice_No, Cust_No, Cust_Name, Cust_Add,
Inv_Date, (SuppNO, Supp_Name, Supp_Addr), (Product_No, Product_Description, Unit_Price,
Sale_Price Qty_Sold, Line_Total)). All the attributes are atomic and unique identifier is present
in the table.
2nd Normal Form:
The non-key attributes have to be dependent on the key-attribute.
Functional Dependencies
Invoice_No → (Inv_Date, Product_ No, Sale_Price, Qty_Sold, Line_Total)
SuppNO → (Supp_Name, Supp_Addr)
Cust_No → (Cust_Name, Cust_Add)
Product_No → (Product_ Description, Unit_Price)
Tables:
So the normalized tables up to 2nd Normal Form are as following.
Invoice (Invoice_No, Product_No, Inv_Date, Sale_Price, Qty_Sold, Cust_No)
Customer (Cust_No, Cust_Name, Cust_Add)
Product (Product_No, SuppNO, Product_Name, Unit_Price)

7DATABASE NORMALIZATION
Supplier (SuppNO, Supp_Name, Supp_Addr)
3rd Normal Form:
There must not be any transitive dependency in the table.
Transitive Dependencies
Product_No → Invoice_No (Sale_Price, Qty_Sold)
Invoice_No does not → Product_No
Invoice_No → (Sale_Price, Qty_Sold)
Tables:
ProductSold (Invoice_No, Product_No, Sale_Price, Qty_Sold)
Invoice (Invoice_No, Inv_Date, Product_ No, Cust_No)
Product (Product_No, SuppNO, Product_Name, Unit_Price)
All the tables are in 3rd Normal form as there is no transitive dependency among the
attributes.
Final Solutions:
ProductSold (Invoice_No, Product_No, Sale_Price, Qty_Sold)
Invoice (Invoice_No, Inv_Date, Product_ No, Cust_No)

8DATABASE NORMALIZATION
Customer (Cust_No, Cust_Name, Cust_Add, Cust_Phone)
Product (Product_No, SuppNO, Product_Name, Unit_Price)
Supplier (SuppNO, Supp_Name, Supp_Addr)
Answer to Question 3:
3.a Selecting the Most Appropriate Solution:
(StudentNo, FirstName, Surname, CourseNo)
(StudenNo, SubjectNo, Result )
(SubjectNo, SubjectDescription )
(CourseNo, Course_Name)
All the data are atomic and each of the table can be identified by unique identifier.
The non-attributes are depended only on the key attributes.
There are no transitive dependency among the attributes.
3.b Identifying the Normal Form:
(StockNO, WarehouseNO, WarehouseName, StockName QtyonHand, SelfLocation,
Unit_Price)
The table is in the first normal form. The WarehouseName, StockName, Unit_Price does
not depended on the composite primary key. This table indicates the update anomaly. In case the
warehouse name is updated in one row then for the each row where WarehouseNO is same has
to be updated.

9DATABASE NORMALIZATION
(StudentNO, SubjectNO, Semester, Year, Grade)
The table is in the third normal form as the non-key attribute is completely depended on
the composite primary key.
Answer to Question 4:
1st Normal Form:
List of attributes are project_id, project_description, company, commencement_date,
Task_No, task_name, strat_date, end_date, skill_no, skill_name, start_date, end_date, hours. All
the attributes are atomic and unique identifier is present in the table.
2nd Normal Form:
The non-key attributes have to be dependent on the key-attribute.
Functional Dependencies:
project_id → (project_description, company, commencement_date)
Task_No → (task_name, strat_date, end_date)
skill_no → (skill_name, start_date, end_date, hours)
Tables:
So the normalized tables up to 2nd Normal Form are as following.
Project (project_id, project_description, company, commencement_date, Task_No)

10DATABASE NORMALIZATION
Task (Task_No, task_name, strat_date, end_date)
Task_Skill (Task_No, skill_no, hours)
Skill (skill_no, skill_name, start_date, end_date)
3rd Normal Form:
There must not be any transitive dependency in the table.
All the tables are in 3rd Normal form as there is no transitive dependency among the
attributes.
Final Solution:
Project (project_id, project_description, company, commencement_date, Task_No)
Task (Task_No, task_name, strat_date, end_date)
Task_Skill (Task_No, skill_no, hours)
Skill (skill_no, skill_name, start_date, end_date)

11DATABASE NORMALIZATION
Bibliography:
Albarak, M. and Bahsoon, R., 2018. Prioritizing Technical Debt in Database Normalization
Using Portfolio Theory and Data Quality Metrics. arXiv preprint arXiv:1801.06989.
Alotaibi, Y. and Ramadan, B., 2017. A Novel Normalization Forms for Relational Database
Design throughout Matching Related Data Attribute.
Mühlfellner, P., Furgale, P., Derendarz, W. and Philippsen, R., 2015. Designing a relational
database for long-term visual mapping.
Reimer, Y., 2017. CSCI 340. Y1: Database Design.