MIS 331 - Homework 2 – Normalization
Due October 1, 2014

1. Transform the following E-R Diagram into a set of relations in at least 3NF.  (50 pts)  Be sure to list all foreign key constraints.




2. For the following, (1) determine what normal form it is currently in, (2) tell me why it's in that normal form (and not others), and (3) do what is necessary to get it into 3NF if it's not already there. (20 pts)

	Class (Course#, Inst#, CourseName, ClassTime, InstructorName)
		FD: Course# --> CourseName
		FD: Inst# --> InstructorName



	


Jobs (Employee#, Dept#, JobCode, JobDescr, EmployeeName)
		FD: JobCode --> JobDescr
		
 
3. The table below contains sample data for parts and for vendors who supply those parts.  In discussing this data with users, we find that part numbers, but not descriptions, uniquely identify parts and that vendor names uniquely identify vendors. (30 points)

a.	In what normal form is the table as it is currently presented?  Why?
b.	Convert the table into a relation (named PartVendor).  In what normal form is this relation and why?
c.	For the PartVendor relation, give specific examples of insertion, deletion, and modification anomalies, based on the data.
d.	Create the set of relations needed to represent the data in 3NF.

Part NO.	Description	Vendor Name	Address	Unit Cost
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