Assignment 3: Movie critics

Due: 5pm, Tuesday, March 5. Value: 40 pts. You may work with one other student on this assignment if you wish. Submit your solutions to Moodle, written in ASCII, OpenOffice.org, or PDF format (no Word files!).

Suppose our workgroup were charged with designing a database to store critics' movie ratings. One of your coworkers — who clearly lacks your expertise gained from CSCI 340 — proposes the following schema.

reviews(title, year, length, critic, site, rating)

Moreover, the coworker proposes that the following FDs apply to the schema, abbreviating the attributes as T, Y, L, C, S, and R. (You may use these abbreviations in your answers, too.)

T Y → L
C → S
C T → R Y
C L Y → R T

Below is an example of a partially filled relation. (Ratings are taken from The Movie Review Query Engine.)

title	year	length	critic	site	rating
Hamlet	1996	242	Ebert	rogerebert.suntimes.com	4
Hamlet	2000	112	Berardinelli	www.reelviews.net	2
King Kong	1933	100	Berardinelli	www.reelviews.net	3.5
King Kong	1976	134	Ebert	rogerebert.suntimes.com	3
King Kong	2005	187	Travers	www.rollingstone.com/reviews	4
Lord of the Rings	1978	132	Ebert	rogerebert.suntimes.com	2.5
Lord of the Rings	2001	178	Berardinelli	www.reelviews.net	4
Lord of the Rings	2001	178	Travers	www.rollingstone.com/reviews	2

For your assignment, write a short answer to each of the following questions. Please submit a typed copy, in ASCII text, OpenOffice.org, PDF, or PostScript format, to Moodle. Please don't attempt to write a paper that coherently flows from one question to the next — instead, clearly label your answer to each question.

1. In your own words, explain what the FD C T → R Y means semantically. (As an example of what I'm looking for, consider the hall → sex FD for the students example in class: For that example, the FD specifies that all dorms on campus must be single-sex residence halls.)

2. Do the same for the FD C L Y → T.

3. Give an example of a new row that could be inserted into the above example relation that would break one or more of the FDs, and explain.

4. Give an example of a modification of one cell in the above example relation that would break one or more of the FDs, and explain.

5. The relation has two keys. Identify each, and explain why each qualifies as a key.

6. Identify each FD violating BCNF, and explain why.

7. Decompose the relation into BCNF relations. Describe your intermediate steps.

8. Identify any FDs that were lost in your decomposition above.

9. For each of the BCNF-violating FDs from (6.), explain why it does or does not violate 3NF.