Database Design Practical

This practical assignment demonstrates the design of a relational database. The solution includes a dependency diagram illustrating functional dependencies, a decomposition into the third normal form (3NF), a relational schema with referential integrity constraints, and an entity-relationship (ER) diagram. The assignment addresses the design of tables for products, invoices, and vendors, considering relationships and constraints. The solution explains the rationale behind the design choices, including primary and foreign keys, and the normalization process to achieve 3NF. The document also includes a list of references used during the assignment.

Student ID:
Student Name:
Subject code

Table of Contents
Dependency Diagram..................................................................................................................................3
Third (3rd) Normal Form..............................................................................................................................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 first functional dependency, Prod_Name, Prod_price, Purchase_date, Vendor_ID and
Vendor_Name are depend upon Prod_code
Second Functional Dependency:
Prod_code, Inv_No Qty_sold, Inv_Date
In second functional dependency, Qty_sold and Inv_Date are dependent upon Prod_code,
and Inv_No
Third Functional Dependency:
Vendor_ID Vendor_Name
In third functional dependency, Vendor_Name is dependent upon on Vendor_ID

Third (3rd) Normal Form
Decomposing of relation into third normal form
As per above scenarios:
1. Product table is used to store the product’s information such as Prod_code, Prod_name,
Prod_price, Purchase_date and reference of vendor table. In this table there are non-
repeated attributes. Prod_Name, Prod_price, Purchase_date are non-repeated attributed
dependent upon the primary key Prod_code. Foreign key present here is Vendor_ID
referencing to Vendor table.
2. Invoice table is used to store the details of invoice such as Inv_No, Qty_sold, Inv_Date
and reference of product table. In this table there are non-repeated attributes. Qty_sold,
Inv_Date are non-repeated attributed dependent upon the primary key Inv_No. Foreign
key present here is Prod_code referencing to Product table.

3. Vendor table is used to store the details of vendor such as Vendor_ID, Vendor_Name. In
this table there are non-repeated attributes. Vendor_Name is non-repeated attributed
dependent upon the primary key Vendor_ID.
Relational Schema with referential integrity constraints
As per the above diagram:
1. Product table contains Prod_code as primary key and Vendor_ID as foreign key
referencing to Vendor table.
2. Invoice table contains Inv_No as primary key and Prod_code as foreign key referencing
to Product table.

3. Vendor table contains Vendor_ID as primary key
Entity Relation Diagram
Assumptions
1. A vendor may or may not sell the product.
2. Purchase_date stores the date when the product has been purchased from the vendor.
3. Inv_Date is used to store the date on which invoice is generated.
4. One invoice may be generated for the each product.
5. One invoice can be generated for multiple products.

References
Al-Saggaf, Y. (n.d.). Retrieved from youtube: https://www.youtube.com/watch?v=vkUyuLLgmwQ
Converting an E-R diagram to a relational schema. (n.d.). Retrieved from https://piazza-
resources.s3.amazonaws.com/hkpkwe3n2896eo/hny225c7ni56ov/
Converting_an_ER_to_RDB.pdf?
AWSAccessKeyId=AKIAIEDNRLJ4AZKBW6HA&Expires=1504707594&Signature=jkCQIBM5yd1Rab
EBt3xp0L8VS%2BI%3D
Database Third Normal Form Explained in Simple English. (n.d.). Retrieved from Essential SQL:
https://www.essentialsql.com/get-ready-to-learn-sql-11-database-third-normal-form-explained-
in-simple-english/
Functional Dependency Diagram and Examples. (n.d.). Retrieved from Tutorialink:
http://tutorialink.com/dbms/functional-dependency-diagram%C2%A0and-examples.dbms
Relational Schema Design. (n.d.). Retrieved from
http://imada.sdu.dk/~petersk/DM505/F09/slides/slides5.pdf
Relational Schema Design. (n.d.). Retrieved from
http://cir.dcs.uni-pannon.hu/cikkek/Relational_Schema_Design.pdf
THAKUR, D. (n.d.). Type of Functional Dependence. Retrieved from ComputerNotes:
http://ecomputernotes.com/fundamental/what-is-a-database/functional-dependence
Third Normal Form in DBMS with Examples. (n.d.). Retrieved from Study.com:
http://study.com/academy/lesson/third-normal-form-in-dbms-with-examples.html