Database Design Project: Database Normalization and Diagramming

This database design project presents a comprehensive solution involving database normalization, entity-relationship diagrams (ERDs), and relational schema. The project begins with a dependency diagram illustrating functional dependencies, including scenarios for product codes, invoice numbers, and vendor IDs. The solution then decomposes the relations into the third normal form (3NF), detailing the structure of Product, Invoice, and Vendor tables. The relational schema is provided with referential integrity constraints to ensure data consistency. An ERD visually represents the relationships between entities, clarifying assumptions about vendors, products, and invoices. The project also includes a list of references to support the design and concepts presented. This assignment is designed to help students understand and apply database design principles, covering essential topics such as normalization, relational schema, and ER modeling.

Student ID
Name
Subject code

Table of Contents
Dependency Diagram..................................................................................................................................3
Third (3rd) Normal Form Relations..............................................................................................................4
Relational Schema with referential integrity constraints..............................................................................5
Entity Relation Diagram..............................................................................................................................6
References...................................................................................................................................................7

Dependency Diagram
Following is the dependency diagram. It depicts or represents the functional dependencies:
First Functional Dependency:
Prod_code Prod_Name, Prod_price, Purchase_date, Vendor_ID,
Vendor_Name
In the above mentioned scenario, Prod_Name, Prod_price, Purchase_date, Vendor_ID
and Vendor_Name are dependent (functionally) on Prod_code
Second Functional Dependency:
Prod_code, Inv_No Qty_sold, Inv_Date
In the above mentioned scenario, Qty_sold and Inv_Date are dependent (functionally) on
Prod_code, and Inv_No
Third Functional Dependency:
Vendor_ID Vendor_Name
In the above mentioned scenario, Vendor_Name is dependent (functionally) on
Vendor_ID

Third (3rd) Normal Form Relations
Decomposing of relation into third normal form
According to above scenario:
1. Product table stores the information or data of product(s) like product code, name, price,
product purchase date and reference of the vendor. In this table attributes are not
repeated. Non key attributes like Prod_Name, Prod_price, Purchase_date are dependent
upon primary key Prod_code. Vendor_ID is the foreign key.
2. Invoice table stores invoice number, product reference, quantity sold and invoice date.
Non key attributes like Qty_sold, Inv_Date are dependent upon primary key Inv_No.
Prod_code is the foreign key.
3. Vendor table includes vendor id and vendor’s name. . Non key attributes like
Vendor_Name are dependent upon primary key Vendor_ID.

Relational Schema with referential integrity constraints
In the above scenario following are the scenarios:
1. In Product table, Prod_code is the primary key and Vendor_ID is the foreign key which
refers to Vendor table.
2. In Invoice table, Inv_No is the primary key and Prod_code is the foreign key which refers
to Product table.
3. In Vendor table, Vendor_ID is the primary key.

Entity Relation Diagram
Assumptions
1. A vendor cannot or can sell the product.
2. Purchase_date means when the product is purchased from the vendor.
3. Single or more invoices can or cannot be generated for the product.
4. Invoice can or cannot be generated for the single or more product.

References
Al-Saggaf, Y. (n.d.). Retrieved from youtube: https://www.youtube.com/watch?v=vkUyuLLgmwQ
ARADHYAYANA. (2014, May 6). ER DIAGRAM TO RELATIONAL SCHEMA MAPPING. Retrieved from
https://www.slideshare.net/ARADHYAYANA/er-diagram-to-relational-schema-mapping
DATABASE TECHNOLOGY. (n.d.). Retrieved from studytonight:
http://www.studytonight.com/dbms/database-normalization.php
Functional Dependencies. (n.d.). Retrieved from
http://jcsites.juniata.edu/faculty/rhodes/dbms/funcdep.htm
Functional dependency in DBMS. (n.d.). Retrieved from beginnersbook:
https://beginnersbook.com/2015/04/functional-dependency-in-dbms/
Hoffer, J. A., Topi, H., & Venkataraman, R. (n.d.). Modern Database Management.
SINGH, C. (n.d.). Transitive dependency in DBMS. Retrieved from beginnersbook:
https://beginnersbook.com/2015/04/transitive-dependency-in-dbms/
Teaching Mathematics and Computer Science. (n.d.). Retrieved from
http://tmcs.math.unideb.hu/load_doc.php?p=188&t=doc
Understanding Relational Databases: Referential Integrity. (n.d.). Retrieved from
http://www.miswebdesign.com/resources/articles/wrox-beginning-php-4-chapter-3-5.html
Watt, A. (n.d.). Chapter 11 Functional Dependencies. Retrieved from opentextbc.ca:
https://opentextbc.ca/dbdesign01/chapter/chapter-11-functional-dependencies/