MCS-274 Lab 3: Database Design (Spring 2007)


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?" There are also some optional extra-credit portions of the lab assignment.

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 movies.sql file linked to this assignment, draw an E-R diagram.

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. Your system should not keep any additional information for customers, unless you elect the extra-credit option addressing this issue.

As the next section explains, the combination of a date and time can be stored in SQL as a single timestamp; 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 timestamp. (That is, in E-R terms, the return timestamp attribute has a cardinality indicating that it is optional.)

  2. The past rentals are stored separately from the present rentals. The present ones don't have any place to store a return timestamp, 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.

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 all relevant constraints, including foreign keys with any relevant ON DELETE clauses. 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.

Although Oracle has a built in data type for timestamps, it turns out to be a pain to work with. Therefore, I recommend that you use an an integer. Specifically, this integer would encode the number of seconds since midnight, January 1, 1970 UTC. The application programs using the database would be responsible for converting times into this format and back out. You don't need to worry about it. (The difference between two of these values would show a rental duration in seconds.) For inserting data into your tables, you can just make up some arbitrary integers. Or, if you want realistic values, on a Linux system, you can give the shell command

date +%s

in order to get the current date and time in this format, and you can use a command like

date -d 'Feb 14 4:30 PM CST 2007' +%s
in order to find out the value in this format of 4:30pm on Valentine's Day of this year. (The format of the date and time string in this command is quite flexible.)

A hard 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?

Extra credit option 1: Withdrawing copies

Individual copies of movies may be withdrawn from circulation. You should retain records of prior rentals of the withdrawn copies. However, the withdrawn copies should not be considered available for rental, when a query is made regarding movies with a particular actor. Also, decide whether it should be legal to withdraw a movie that is out on rental at the time. If so, what should happen to the record of that rental? Take these decisions into account in your design and implementation. That is, show the modifications in the E-R diagram and SQL.

Extra credit option 2: Customer accounts

Unlike directors and actors, customers have accounts that can be deactivated and reactivated. (Eventually additional information may also be associated with these accounts, such as addresses and phone numbers.) All rentals need to be to active accounts. Decide exactly what this means and how it should be enforced. Update your E-R diagram and SQL appropriately.

Extra credit option 3: More practice with queries

Write and test SQL queries for as many as you like of the following:

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.