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.
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.
Your new system should satisfy the following requirements:
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.
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.
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.
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:
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.)
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
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.
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' +%sin 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.)
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?
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.
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.
Write and test SQL queries for as many as you like of the following:
Who rented all movies directed by Orson Welles?
Who rented only movies directed by Orson Welles? Be sure to make clear (in English) how you are interpreting this question; there is more than one reasonable interpretation.
What is the average duration of a (completed) rental?
For each customer, what is the average duration of a (completed) rental? Exclude customers who have never rented, because the average would be undefined for them. Organize this table by decreasing duration.
For each customer, how many times has that customer rented a movie? Be sure to include customers who have never rented a movie. Specify whether you have included presently ongoing rentals.
For each customer, how many movies has that customer rented? Be sure to include customers who have never rented a movie. Specify whether you have included presently ongoing rentals.
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.