Database Solutions: 3NF Normalization Assignment - [Institution Name]

This assignment solution focuses on database normalization, specifically addressing the 3rd Normal Form (3NF). The solution begins with a dependency diagram illustrating functional dependencies within a sample relation. It then decomposes the relation into three 3NF relations: PRODUCTS, SALES, and VENDORS. Each relation is explained in detail, demonstrating compliance with 3NF principles, including the absence of repeating attributes and dependencies on the primary key. The solution also presents the relational schema, including functional dependencies and referential integrity constraints, ensuring data validity through primary and foreign keys. Furthermore, the assignment includes an Entity-Relationship Diagram (ERD) representing the 3NF relations, providing a visual representation of the database structure. The solution references key database concepts and includes a list of relevant sources.

Running Head: DATABASE RELATION NORMALIZATION 1
Database Solutions 3NF Normalization
[Student Names]
[Institution Name]

DATABASE RELATION NORMALIZATION 2
Dependency diagram showing the functional dependencies in the
relation.
Prod_code
Prod_Name
Purchase_date
Qty_sold
Prod_price
Inv_No
Inv_Date
Vendor_ID
Vendor_Name

DATABASE RELATION NORMALIZATION 3
Decomposed relation into a set of 3NF relations.
The above dependency diagram can be split into three relations in 3NF which includes
PRODUCTS,SALES and VENDORS relations as below:
PRODUCTS
Prod_code Prod_Name Purchase_date
The above PRODUCTS relations from the above dependency diagram is in 3NF due to
following :
i. There is no repeating attribute in the relations.
ii. The other attributes in the relations depends on the primary key Prod_code.
iii. The only dependencies are functional and transitive and therefore this relation in 3NF.
SALES
Inv_No Qty_sold Prod_price Inv_Date Prod_code
The above SALES relations from the above dependency diagram is in 3NF due to
following :
i. There is no repeating attribute in the relations.
ii. The other attributes in the relations depends on the primary key Inv_No and a foreign
key prod_code that references PRODUCTS table.
iii. The only dependencies are functional and transitive and therefore this relation in 3NF.
VENDORS
Vendor_ID Vendor_Name Prod_code
The above SALES relations from the above dependency diagram is in 3NF due to
following :
i. There is no repeating attribute in the relations.
ii. The other attributes in the relations depends on the primary key Vendor_ID and
a foreign key prod_code that references PRODUCTS table..

DATABASE RELATION NORMALIZATION 4
iii. The only dependencies are functional and transitive and therefore this relation in
3NF.
Relational schema and the referential integrity constraints for 3NF
relations.
The below is the relational schema of the resulted 3NF relations where they indicates the
functional dependencies and references integrity constraints ,in the functional dependencies there
are two attributes in the tables of relational databases that relates and they acts as the constraints
of the relation as shown below (Coronel,2016).
However the referential integrity will ensure the data that is feed in a database table is
always valid which is enabled through use of both primary and foreign keys that enables data
synchronization (Chris,2015)..
PRODUCTS
Prod_code Prod_Name Purchase_date
SALES
Inv_No Prod_code Qty_sold Prod_price Inv_Date
VENDORS
Vendor_ID Prod_code Vendor_Name

DATABASE RELATION NORMALIZATION 5
Below is the description of the respective relations in the above schema describing their
attributes and keys and their functional dependencies.
PRODUCTS RELATIONS.
i. The Prod_code , Prod_Name ,Purchase_date forms the set of attributes in the
PRODUCTS relations (Mannino,2016).
ii. Then the primary key for the PRODUCTS relations is the Prod_code .
iii. The below is the functional dependencies in the PRODUCTS relation.
Prod_code Prod_Name ,Purchase_date.
Therefore the Prod_Name ,Purchase_date can be said to be depending functionary
on Prod_code.
SALES RELATIONS
i. The Inv_No,Prod_code ,Qty_sold ,Prod_price ,Inv_Date forms the set of
attributes in the SALES relations.
ii. Then the primary key for the SALES relations is the Inv_No.
iii. The Prod_code is an attribute that is a foreign key in SALES table that
references the PRODUCTS table above (Codd , 2014).
iv. The below is the functional dependencies in the SALES relation.
Inv_NoProd_code ,Qty_sold ,Prod_price ,Inv_Date.
Therefore the Prod_code ,Qty_sold ,Prod_price ,Inv_Date can be said to be
depending functionary on Inv_No (Date,2014).

PRODUCTS
Prod_code
Prod_Name
Purchase_date
SALES
Vendor_ID
Prod_code
Vendor_Name
VENDORS
Inv_No
Prod_code
Qty_sold
Prod_price
Inv_Date
DATABASE RELATION NORMALIZATION 6
VENDORS RELATION
i. The Vendor_ID ,Prod_code,Vendor_Name forms the set of attributes in the
VENDORS relations.
ii. Then the primary key for the SALES relations is the Vendor_ID.
iii. The Prod_code is an attribute that is a foreign key in VENDORS table that
references the PRODUCTS table above.
iv. The below is the functional dependencies in the VENDORS relation.
Vendor_ID Prod_code,Vendor_Name.
Therefore the Prod_code and Vendor_Name can be said to be depending
functionary on Vendor_ID.
Entity relational diagram representing 3NF relations.
The ERD is a major technique that is used to model databases, however it is done in the
phase of conceptual modeling of the database and it is achieved through the normalization
process.
However the below is the ERD that is designed from the resultant relations which is in
3NF .
(Thalheim, 2015).

DATABASE RELATION NORMALIZATION 7
References
Thalheim, B. (2015). Fundamentals of Entity-Relationship Modeling. U.S.A:
Springer-Verlag.
Date, C. (2014). Introduction to Database Systems,U.S.A:
Addison-Wesley.
Mannino, M.(2016).Database Design, Application Development,
and Administration, , U.S:McGraw-Hill, NJ.
Coronel,C.(2016).Database Systems Design Implementation & Management,
ENGLAND:Course Technology.
Chris,D.(2015).Introduction to Database Systems,U.S.A:
Addison-Wesley,
Codd ,E (2014).Further normalization of the data base relational model,
U.S.A: In Proc.