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. (30 pts)

In what normal form is the table as it is currently presented? Why?

Convert the table into a relation PART_SUP. In what normal form is this relation and why? For the PART_SUP relation, give specific examples of insertion, deletion, and modification anomalies, based on the data.

Create the set of relations needed to represent the data in 3NF.

PartID	PartName	SupName	City	UnitCost
1234	Logic Chip	Intel	Cupertino	10.00
1234	Logic Chip	AMD	Phoenix	8.00
5678	Memory Chip	Intel	Cupertino	3.00
5678	Memory Chip	ChipsRUs	Tucson	2.00
5678	Memory Chip	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. (20 pts)

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. (50 pts) Be sure to list all foreign key constraints.

Patient! D PatFnamePatLna me PatPhone PhysID PhysFname PhysLname)( Department PATIENTo TreatedBy PHYSICIAN