MIS 331 – Homework 2 – Normalization
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