ITM information technology management

ITM 315 Spring 2018 Assignment 6 29.5 Points possible

Due Friday, April 27, 2018 at 1pm. Submit a word document including the solutions to the problems below via blackboard by the due date/time. This is an individual assignment.

Name your file: Spring18Homework6LastName

1. Transform the diagram below into a relational schema that shows referential integrity constraints. Note that the many to many relationship between vendor and department has not been replaced with an associative entity and two one-to-many relationship, so you will need to take care of that when you convert this to a relational schema.

2. The diagram below shows a relational schema and its functional dependencies. Label each dependency as a full, partial or transitive dependency. Convert the relation to a set of relations in second normal form. Then show the relations in 3rd normal form to illustrate the progression from a single relation to 2nd normal form and then 3rd normal form. For the set of relations in 3rd normal form, show the referential integrity constraints.

Project Number

Employee#

Project Name

Employee name

Job Class

Hourly Charge

Hours

3. Create the relational schema for the table below and draw and label the type of the functional dependencies in the relation. Convert the relation to a set of relations in second normal form. Then show the relations in 3rd normal form to illustrate the progression from a single relation to 2nd normal form and then 3rd normal form. For the set of relations in 3rd normal form, show the referential integrity constraints. There is 1 full dependency, 1 transitive dependency, and one partial dependency. The full dependency is Invoice#, Product#→SaleDate, Quantity Sold

Invoice#	Product#	Sale date	Product Type	Vendor Code	Vendor Name	Quantity Sold	Product Price
211347	AAE3422	1/15/2012	Rotary sander	211	NeverFail, Inc.	1	49.95
211347	QD30093	1/15/2012	¼ in. drill bit	211	NeverFail, Inc.	8	3.45
211347	RU99574	1/15/2012	Band saw	309	BeGood, Inc.	1	39.99
211348	AAE3422	1/15/2012	Rotary sander	211	NeverFail, Inc.	2	49.95
211349	GH77834	1/16/2012	Power drill	157	ToughGo, Inc.	1	87.75