Database Systems Analysis and Design Report - Semester 1

This report delves into the analysis and design of database systems, specifically focusing on normalization and relational schema creation. The report begins by explaining the concept of First Normal Form (1NF) and progresses to demonstrate the process of normalization to achieve the Third Normal Form (3NF). It includes dependency diagrams to illustrate functional dependencies and the decomposition of relations. The report outlines the steps to transform a given relation into 3NF by addressing transitive dependencies. The solution provides the relational schema for the resulting 3NF relations, including PRODUCTS, INVOICE, and VENDORS. Finally, the report presents an Entity-Relationship Diagram (ERD) to visually represent the relationships between these entities, highlighting how products are sold by vendors and are associated with invoices. The report references relevant academic sources to support the analysis and design choices.

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 (Kent, September 1982).
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 (Date, August 1, 2003).
The created relation VENDORS is as below:
The Relational Schema for the 3NF relations will be as follows:
PRODUCTS (Prod_code, Purchase_date, Qty_sold, Prod_price) will be the first relational
schema.

4
DATABASE MANAGEMENT SYSTEMS
INVOICE (Prod_code, Inv_No, Inv_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.
The PRODUCTS entity is also related to INVOICES. The products are sold and an invoice is
issued.

5
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
Date, C. J. (August 1, 2003). An Introduction to Database Systems (8th Edition). Pearson.
Kent, W. (September 1982). A Simple Guide to Five Normal Forms in Relational Database
Theory. Association for Computing Machinery, Inc.
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