Question

For each relation:

a) List Functional Dependencies.

b) Based on the given primary key, is the relation in 1NF, 2NF, or 3NF? Why or why not?

c) Normalize the relation successively into 3NF, if it is not already in 3NF.

1. FACULTY(FacNo, FacName, DeptNo, DeptName, DeptLoc, InsurPlanNo, InsurPlanDesc)
2. ORDER(OrdNo, OrdDate, CustNo, PartNo, Qty, UnitPrice)

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

Normalization :

• Normalization in database helps to decompose large and complex tables into simple and smaller form.
• Due to normalization duplicate data will be removed and consistent data will be stored.

Example :Consider relation given in the question.

1. FACULTY(FacNo, FacName, DeptNo, DeptName, DeptLoc, InsurPlanNo, InsurPlanDesc)

First Normal Form (1NF) :

• First Normal Form (1NF) says all the columns in the table should be automic in nature,.
• Duplicate columns and multivalued columns are not allowed in 1NF.

Given relation is in the first normal form because all columns are automic in nature and no duplicate columns exists.

Second Normal Form (2NF) :

• 2NF says table should be in the 1NF.
• All non key columns in the table should depends upon primary key column.
• In 2NF partial dependencies are not allowed.

a) List Functional Dependencies :

FacNo==> FacName

DeptNo==>DeptName, DeptLoc

InsurPlanNo==>InsurPlanDesc

Here need to identify new tables like

• Faculty :This table stores faculty details that is FacNo, FacName
• Department :This table stores department details such as DeptNo,DeptName, DeptLoc
• Insurance :This table stores insurance details such as InsurPlanNo,InsurPlanDesc

Below is the table details

1.Table Name :Faculty

Schema :Faculty(FacNo, FacName)

FD :FacNo,==>FacName

2.Table Name :Department

Schema :Department(DeptNo,DeptName, DeptLoc)

FD :DeptNo==>DeptName, DeptLoc

3.Table Name :Insurance

Schema :Insurance (InsurPlanNo, InsurPlanDesc)

FD :InsurPlanNo==> InsurPlanDesc

Third Normal Form (3NF):

• 3NF says table should be in the 2NF.
• All non key columns should depends upon non key column in the table.
• In 3NF transitive dependency is not allowed.

Here above table need to normalize into 3NF to remove transitive dependency.Below are tables in 3NF.

1.Table Name :Faculty

Schema :Faculty(FacNo, FacName)

FD :FacNo,==>FacName

2.Table Name :Department

Schema :Department(DeptNo,DeptName, DeptLoc)

FD :DeptNo==>DeptName, DeptLoc

3.Table Name :Insurance

Schema :Insurance (InsurPlanNo, InsurPlanDesc)

FD :InsurPlanNo==> InsurPlanDesc

4.Table Name :FacDeptInsurance

Schema :FacDeptInsurance(FacNo,DeptNo,InsurPlanNo)

*****************************

Relation II :ORDER(OrdNo, OrdDate, CustNo, PartNo, Qty, UnitPrice)

Given relation is in the 2NF because some of the columns contains transitive dependency that needs to be removed.

Below are tables in 3NF.

1.Table Name :Order

Schema :Order (OrdNo,OrdDate,CustNo)

FD :OrdNo==>OrdDate,CustNo

Here ordNo is primary key.Each order can have only one date but order can have more then one product and hence need to store product details with these order in another table called orderDetails.

2.Table Name :OrderDetails

Schema :OrderDetails(OrdNo,PartNo,Qty,UnitPrice)

FD:OrdNo,PartNo==>Qty,UnitPrice

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.