Either design a relational database for the following situation, or choose a situation to design a relational database for. If you choose your own situation, check your idea with me to make sure it is suitable before you proceed. More detailed specifications for designing a relational database appear below.

A Hollywood studio wants to use a relational database system to keep track of information about movie stars and the movies they have starred in. The information stored must include the star's name, fee for starring in a movie, name of the star's agent, address(es), and the name of movies starred in and the year the movie was released. Each star has exactly one agent, but a star will usually have more than one home, and so more than one address. You may assume that no two movie stars have the same name. You may store more information than the minimum specified here if you wish.

For this situation (or a situation you choose), do the following:

1. (3 points) List all of the attributes that will need to be stored in the database. Your answer here should be the scheme of the database if it had only one relation.
2. (10 points) Give D, the set of functional and multivalued dependencies that this database should satisfy. Use your best judgement about the meaning of the data here, and document any assumptions that you make.
3. (20 points) Find a decomposition of your answer from part one that represents a ``good'' database design. Explain why your design is ``good'', using your set of dependencies D and your knowledge of normalization and other relational database design principles. In particular, you should specify whether or not your decomposition is a lossless join decomposition with respect to D, whether or not your decomposition is dependency preserving with respect to any functional dependencies in D, and which (if any) normal form(s) your design is in. (You do not need to worry about preserving multivalued dependencies.) Be sure to provide justification for any claims you make about your design.
4. (20 points) Implement your design using your favorite SQL-based relational database package (or just MS Access). You do not need to load any tuples into your relation(s) - just give the SQL commands needed to implement the design as one or more relations. You should specify all relevant domain constraints, primary keys, and referential integrity constraints when implementing your design. For this part of the assignment, turn in a printout of all of your SQL commands. Feel free to turn in additional supporting printouts/documentation for this part.

If you choose a situation other than the one specified here, we may need to adjust the point distribution or substitute for one or more of the parts of the assignment. You should discuss such issues with me after you have at least sketched out parts one - three above.

• About this document ...