ITEC 3335
Database Development
Fall 2017
Homework #6

Logical Database Design and Normalization Theory

[1] Consider the following table: Shipment.

Shipment(ShipmentId, ProductId, Quantity, SourceCity, DestinationCity, Distance)

ShipmentId is an unique identifier for a shipment of a quantity of a product (identified by ProductId) from a SourceCity to a DestinationCity. The two cities determine the Distance between them. (For example, the distance between Houston and Dallas is 239.1 miles.

(a) Identify the functional dependencies (FD) of the relation.

(b) What are the candidate keys?

(c) What are the non-prime attributes?

(d) What is the highest normal form of the relation?

(e) If it is not in BCNF, convert the relation to relations of BCNF or 3NF.

(2) Consider the following table.

Employee(EmployeeId, SSN, FirstName, LastName, Phone, EMail, DepartmentId, ProjectId)

It stores some information about employees. EmployeeId is a unique id of an employee. The social security number, SSN, of an employee is stored for tax purpose. It should also be unique. One name and phone of every employee is recorded but an employee may have multiple email addresses and all should be recorded. An employee works for one department (identified by DepartmentId) but an employee may work on multiple projects (identified by ProjectId).

(a) Identify the functional dependencies (FD) of the relation.

(b) What are the candidate keys?

(c) What are the non-prime attributes?

(d) What is the highest normal form of the relation?

(e) If it is not in BCNF, convert the relation to relations of BCNF or 3NF.

(3) Consider the relation R(A,B,C,D) with two candidate keys: A and BC. List all superkeys.

(4) Consider an instructor's view of a class roster:

University of Houston-Clear Lake
ITEC 3335 Database Development
Fall Semester, 2017
Ticket number: 11365
Section: 1

Instructor id: 1102345
Instructor Name: Bun Yue
Phone: 281-283-3864

(a) Design a minimal set of tables (and their columns) in at least 3NF to store this information.

(b) List the functional dependencies of each table.

(c) State the highest normal form (up to BCNF) of each table.

Submit the homework through Blackboard.