Database Normalization Assignment - Module 1 - Semester 1 - University

This report focuses on database normalization, specifically addressing the conversion of a given table into 3rd Normal Form (3NF). The assignment begins with an explanation of why the original table is in 1NF and then proceeds to decompose it into 2NF and finally 3NF. The report details the process of identifying and addressing transitive dependencies, which is crucial for achieving 3NF. The solution includes the creation of new relations, such as VENDORS, to eliminate these dependencies and optimize the database structure. Furthermore, the report outlines the relational schema for each resulting table and illustrates the relationships between these tables using an Entity-Relationship Diagram (ERD). The diagram highlights the connections between PRODUCTS, VENDORS, and INVOICES, showing how products are sold and invoiced, and how vendors supply the products. The report concludes with a list of references used to support the analysis.

1
Running head: DATABASE MANAGEMENT SYSTEMS
DATA BASE MANAGEMENT SYSTEMS

2
DATABASE MANAGEMENT SYSTEMS
Assessment 2
Normalization
The given table is already in First Normal Form since it does not contain any repeating
groups (Czenky, 2017). We therefore create the dependency diagram showing the functional
dependencies in the presented relation.
Decomposing it into a set of 3NF relations, we first move to 2NF from the given 1NF by
having INVOICE, in its optimal form.
We are going to be left with PRODUCTS as shown below:

3
DATABASE MANAGEMENT SYSTEMS
It is clear that the dependency between Vendor_ID and Vendor_Name is a Transitive
dependency. To have our relation fully decomposed to 3NF, we need to eliminate the transitive
dependency (Zaiane, 1998). We will create a relation for the transitive dependency. This will end
up to PRODUCTS which is in 3NF.
The created relation VENDORS is as below:
The Relational Schema for the 3NF relations will be as follows:

4
DATABASE MANAGEMENT SYSTEMS
PRODUCTS (Prod_code, Purchase_date, Qty_sold, Prod_price) will be the first relational
schema.
INVOICE (Inv_No, Inv_Date, Purchase_Date) will be the second relational schema involving
invoices.
VENDORS (Vendor_ID, Vendor_Name) will be the last relational schema drawn to eliminate
the transitive dependency.
The ERD to represent the 3NF relations will be as below
The PRODUCTS entity is related to VENDORS entity. The Vendors sell the products.

5
DATABASE MANAGEMENT SYSTEMS
The PRODUCTS entity is also related to INVOICES. The products are sold and an invoice is
issued.

6
DATABASE MANAGEMENT SYSTEMS
References
Czenky, M. (2017, August 20). Normalization based on dependancy diagrams. Retrieved from
Teaching Mathematics and Computer Science: tmcs.math.unideb.hu
Zaiane, O. (1998, June 9). CMPT 354 (Database Systems 1) lecture notes. Retrieved from
Functional dependencies:
www.cs.sfu.ca/CourseCentral/354/zaiane/material/notes/Chapter6/node10.html