COS 597A, Fall 2008 - Problem Set 1

Collaboration Policy

Late Penalties

• 10% of the earned score if submitted after class but by 6:00pm the day due.
• 20% of the earned score if submitted by 5pm on Wednesday 10/1/08.
• 40% of the earned score if submitted by 5pm on Friday 10/3/08.
• No credit if submitted later than the 40% penalty deadline.
  

Chapter, exercise and page numbers refer to the course text Database System Concepts, Fifth Edition, by Silberschatz,  Korth, and Sudarshan

Problem 1: The subclasses in an "IS A" hierarchy can satisfy each of the two constraints "overlapping" and "covering" independently. In class we had examples of "not overlapping and covering (edited books and authored books) and overlapping and not covering (student and employee of PU people).  Give an example for each of the other two possibilities: (overlapping and covering) and (not overlapping and not covering).  Your examples should be easy to understand and should not be taken from the text or lecture.

Problem 3  Consider a database that holds information about movies and their distribution to movie theaters:

• The database keeps track of all movies currently available to be shown in theaters.  Each movie is identified by its name, producer, and release date. Other information recorded in the database about each movie is its rating (G, PG, PG-13, R, X), in which movie theaters, if any, it is currently being shown, and which local distributors are distributing the movie.
• The database keeps information on each movie theater currently operational.  Each theater is identified by its name and location.  The manager of the theater and the number of screens (at least one) in the theater are also recorded.  For each movie showing in the theater, both its identifying information and the local distributor providing the movie are recorded.  Each theater may use several distributors, but only one distributor provides any one movie.  A movie house always has as many movies showing as it has screens. The start date and end date of any movie's showing in the theater are also recorded. 
• The database keeps information on movie reviews.  For each review the following information is kept: the movie reviewed, the reviewer, the date the review was written, the publisher of the review (e.g. NY Times, PBS, MSNBC), and the text of the review.  Assume the text of the review is a prose critique of a specific movie.  (Even if a review appears in several forms, e.g. Web, newspaper, TV,  it has only one publisher.)
• The business name, proprietor, address and telephone number of each local distributor are recorded.  Local distributors are uniquely identified by their business names.  The movies each distributor carries for distribution are recorded.
  

Draw an ER diagram describing the database.  Represent the constraints on data and the constraints on relationships between data. Are there any constraints in the informal specification that you cannot represent in the ER diagram? If so, for each such constraint state why you cannot represent it.

Problem 4. For the ER diagram you developed to solve Problem 2 above, give the set of relations and constraints for a relational database scheme capturing that portion of the ER specification that has to do with shopping.  Do not give the relations capturing the relationships  written_by, published_by, stocks, and the entities author, publisher and warehouse.  If you represented constraints in Problem 2, explain which of these constraints can be captured in the relational model and how, and which of these constraints you cannot capture in the the relational model. 

Problem 5. For your ER diagram solving Problem 3 above, give the set of relations and constraints defining the relational database schema capturing the ER specification. Explain which constraints represented by the entity-relationship model can be captured in the relational model and how, and which constraints you cannot capture in the the relational model.