COS 597A, Fall 2011: Problem Set 1

COS 597A, Fall 2011 - Problem Set 1

Due at 3:00pm, Monday October 3, 2011.

Collaboration Policy

You may discuss problems with other students in the class. However, each student must write up his or her own solution to each problem independently. That is, while you may formulate the solutions to problems in collaboration with classmates, you must be able to articulate the solutions on your own.

Late Penalties

5% of the earned score if submitted after class but by 5:45pm the day due.
20% of the earned score if submitted by 5:45pm on Wednesday 10/5/11.
40% of the earned score if submitted by 5:45pm on Friday 10/7/11.
No credit if submitted later than the 40% penalty deadline.

Chapter, exercise and page numbers refer to the course text Database Management Systems, 3rd edition, by Ramakrishnan and Gehrke

Problem 1 Chapter 2, exercise 2.6, pg. 53-54.

Problem 2 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 3 For your ER diagram solving Problem 1 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. You may use SQL or the specification style used in lecture.

Problem 4 For your ER diagram solving Problem 2 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. Again, you may use SQL or the specification style used in lecture.