MCS-274 Lab 1 (Spring 2005)

Objective

You will extend the movie database example to contain information about the stores in a video rental chain, the copies those stores own of movies, and the rentals of those copies to customers. You will model this extension using an E-R diagram and then create the relevant SQL tables and constraints. Your ultimate goal is to be able to answer queries like "What videos featuring Claude Rains does the St. Peter store have copies of in stock?"

Modeling the legacy system

Although E-R modeling normally precedes the design of specific SQL tables, when you are extending an existing system, the E-R model may not be available to you. Therefore, you may first reverse engineer the existing system before you plan your changes. The movie system is an example of this situation. Starting from the example on the course web site, draw an E-R diagram. To gain comfort with using MySQL, you may also want to play around with the existing system, for example by replicating the example.

Modeling the revised system

Your new system should satisfy the following requirements:

1. You should be able to accommodate any number of stores, each with a name, such as St. Peter or Mankato. It should be possible for a store to change names while still remaining the same store in all other regards. Therefore, it will probably be best for each store to have an identifying number, rather than relying on the name as the identifier.
2. You should be able to accommodate any number of copies of each movie. Each copy is owned by a specific store; a store may own any number of copies. The physical copies have stickers on them containing identifying numbers; the database should contain these as well.
3. The system should be able to retain information about past rentals as well as current ones. All rental records indicate which customer rented which copy at what date and time. Past rentals also include a date and time when the copy was returned.
4. A customer is a person, just like a director or actor is. For simplicity, your system should not keep any additional information for customers. We'll talk about alternatives in class, in the context of is-a relationships.

As the next section explains, the combination of a date and time can be stored in SQL as a single item; your E-R diagram should follow that convention as well.

Draw a revised E-R diagram that reflects the above requirements. You should give some thought to how the past and present rentals will be represented. Choose one of the following three possibilities, and briefly justify why you chose it rather than either of the other two:

1. All rentals are stored in a uniform manner, with the only difference being that the present rentals have a NULL return time.
2. The past rentals are stored separately from the present rentals. The present ones don't have any place to store a return time, not even NULL
3. All rentals are stored in a uniform manner, as in the first option. However, the present rentals (those with NULL return times) are also stored separately, as in the second option. Presumably this redundant storage of the the present rentals in two different forms is intended to provide some benefit of convenience or efficiency.

One factor you should consider is that we haven't yet learned enough of the SQL language to be able to answer the goal query (movies in stock containing an actor) with the first option. Even if that is a decisive factor for you, please indicate what other factors you consider, and how your choice might be different if you knew enough SQL to make all three options viable.

Your E-R diagram should be as specific as possible. If there are important constraints that you cannot indicate using the E-R notation, add them as textual notes.

Mapping your model into SQL

Write a collection of CREATE TABLE statements based on your revised E-R model. Include as many of the constraints as you can. (You can include any CHECK constraints, they simply won't be enforced by MySQL.) Demonstrate your database's operation by putting some data in the tables, then showing the SELECT query needed to find all movies containing Claude Rains for which St. Peter has at least one copy that isn't currently rented.

You can use the type DATETIME to represent a combination of date and time. When inserting data into tables, you can enter each DATETIME as a string; for example, 4:30 pm on Valentine's day this year is '2005-02-14 16:30:00'. The current date and time is available as NOW().

A thought question

Any database that retains personally identifiable information should have an appropriate privacy policy, including in particular a policy regarding how long data is retained. Do you have any thoughts on what a reasonable policy might be in this application domain? Are rental records kept forever, or is there some point at which they should be deleted?

Lab report

Write a report that is concise and assumes an audience familiar with your assignment and the technology, but which addresses all the above topics in an organized fashion.