1. The table below contains sample data for parts and for suppliers who provide those parts. In discussing this data with users, we find that part ID, but not names, uniquely identify parts and that supplier names uniquely identify suppliers.
1-In what normal form is the table as it is currently presented? Why?
2-Convert the table into a relation PART_SUP. In what normal form is this relation and why?
3-For the PART_SUP relation, give specific examples of insertion, deletion, and modification anomalies, based on the data.
4-Create the set of relations needed to represent the data in 3NF.
|
PartID
|
PartName
|
SupName
|
City
|
UnitCost
|
|
1234
|
Logic Chip
|
Intel
|
Cupertino
|
10.00
|
|
|
|
AMD
|
Phoenix
|
8.00
|
|
5678
|
Memory Chip
|
Intel
|
Cupertino
|
3.00
|
|
|
|
ChipsRUs
|
Tucson
|
2.00
|
|
|
|
AMD
|
Phoenix
|
5.00
|
2. For the following, (1) determine what normal form it is currently in, (2) explain why it's in that normal form (and not others), and (3) convert it into 3NF if it's not already there.
CLASS(CourseID, InstID, CourseName, ClassTime, InstName)
FD: CourseID -->CourseName
FD: InstID -->InstName
JOBS(EmployeeID, DeptNum, JobID, JobTitle, EmpName)
FD: JobID -->JobTitle
3. Transform the following E-R Diagram into a set of relations in 3NF. Be sure to list all foreign key constraints.
