Assignment 1 – Database Design
B561 - Fall 2011
Due: 10pm Sep. 30
Total score: 40 points + 10 bonus points
Consider the following scenario:
You are asked to design a movie database which manages information about movies and major
participants of the movies.
For each movie, the database needs to store the title, the year it was released, the language it used,
the rating (such as PG-13), the producer(s) and the director(s). The following information should be
stored if they are available: the plot, rating by critics, budget, box office, the movie it preceded by
and/or succeeded by if the movie is part of a sequel, the book which the movie is based upon if any,
the actors/actresses in the movie and the characters they performed, etc.
For the personnel in the movie industry, the database keeps track of his/her names (and stage names,
if different), date of birth (and death) and gender, as well as the movies he/she acted in, directed and
produced. In addition, the database keeps track of the awards they won (hint: using Oscar as
example, think about how many different ways one can win an Oscar based on his/her contribution in
a move. Ideally, your design should be able to keep track of as many scenarios as possible).
Please complete the following tasks:
1. Design an ER model for the movie database. Please write a short paragraph explain the
scenarios you take in to consideration and the non-trivial decision you make in the design. (20
points)
2. Design the relational model based on your ER model. Please discuss the integrity constraints you
intend to specify on the relations and explain the non-trivial decisions you make. (10 points)
3. Please identify the functional dependencies in the relations in your design. (10 points)
4. In your design, can one actor perform more than one role in the same movie? Why? (10 bonus
points)