[1] The relation R(A,B,C,D) has two candidate keys: A and BCD. What are the superkeys?

[2] Consider the following table: Grade.

Grade(StudentId, StudentFName, StudentLame, ClassId, InstructorId, Grade)

The table stores the grade information of a student (identified by StudentId) taking a class (identified by ClassId). A class is always taught by a single instructor (identified by InstructorId).

(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, decompose the relation to relations of BCNF or 3NF.

[3] Consider R(A,B,C,D) with {A->B, AB->C, AC->D, BC->D, D->BC}

(a) Find A+, B+, C+ and D+.

(b) Find all candidate key(s).

[4] Consider the following part of an invoice of XYZ Company.

XYZ Company 2800 Bay Area Boulevard Houston, TX 77000

Customer Name: Danielle Gump Customers Phone: 281-201-2002 Customers Address: 111, Lane Road, Houston, TX 81010

Purchase Time 8/6/2018 11:05:28am

Purchase Items:

ItemId	Item	Price	Quantity	Total
10112	Fish Bait #1	$2.99	2	$5.98
13111	Fish Line #81	$4.99	1	$4.99
Total				$10.97

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

(b) List the functional dependencies of each table.

(5) Hoffer, et al., Exercise 4-56, p203 (See Blackboard too). The description of the problem in the textbook is a bit ambiguous. To make it more clear and simpler,

replace the sentence:

The video rental service has multiple licenses for the same movie, and the service differentiates the licenses with a movie copy number, which is unique within a single movie but not unique between difference movies.

by:

A movie license number is used to uniquely identify the license of a specific digital copy of a movie.