REMEMBER: The goal of normalization is to end up with tables in which the primar

Question

REMEMBER: The goal of normalization is to end up with tables in which the primary key determines all non-key columns. For each relation: a) List Functional Dependencies. b) Based on the given primary key, is the relation in INF, 2NF, or NF? Why or why not? c) Normalize the relation successively into 3NF, if i is not already in 3NF You may present the normalized relations using the kind of notation seen in the FACULTY or ORDER relations below, or you could use the kind of notation used when drawing relational schemas (e.g., with table headers). 1. FACULTY(FacNo, FacName, DeptNo, DeptName, DeptLoc, InsurPlanNo, InsurPlanDesc) ORDEROrdNo, OrdDate, CustNo, PartNo, Qty, UnitPrice) 3 Transform the following relation into 3NF: EMPLOYEE (Emp ID, Name, Dept Name, Salary, Course_Title, Date Completed) The functional dependencies are Emp_ID à Name, Dept Name, Salary (Emp_ID, Course_Title) à Date Completed

Explanation / Answer

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

The current table is not in 2nf as there are non prime attribute which
are not just dependent on the primary key specified but other attributes like
DeptName, DeptLoc is dependent on DeptNo, same goes for InsurPlanDesc. So
following are the tables in 3nf:

FacNo FacName DeptNo InsurPlanNo (Table 1)
DeptNo DeptName DeptLoc (Table 2)
InsurPlabNo InsurPlanDesc (Table 3)

2.ORDER(OrdNo, OrdDate, CustNo, PartNo,Qty, UnitPrice)

Tables in 3nf are as follows:

OrdNo OrdDate PartNo Qty (Table1)
PartNo UnitPrice(Table2)

3. Employee (Emp_ID, Name, Dept_Name, Salary, Course_Title, Date_Completed)

   Tables in 3nf are as follows:

   Emp_Id Name Dept_Name Salary (Table1)

   Emp_Id Course_Title Date_Completed (Table 2)

4. SALESPERSON( SalespersonNo, ProductNo, SalesPersonName,Commision,DeptNo,ManagerName,ProductName,UnitPrice, Quantity)

It is not in 2nf because there are non-prime attributes like ProductName which
are dependent on ProductNo only which is a proper subset of primary key set {SalespersonNo, ProductNo]. So the tables in 3nf are as follows:

   SalespersonNo SalesPersonName Commission DeptNo
   SalespersonNo ProductNo Quantity
   DeptNo Manager
   ProductNo ProductName UnitPrice
  

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.