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,, 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.


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.)

King Kong1933100Berardinelliwww.reelviews.net3.5
King Kong1976134Ebertrogerebert.suntimes.com3
Lord of the Rings1978132Ebertrogerebert.suntimes.com2.5
Lord of the Rings2001178Berardinelliwww.reelviews.net4
Lord of the

For your assignment, write a short answer to each of the following questions. Please submit a typed copy, in ASCII text,, 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.