Online Movie Rentals

CSC 4480: Principles of Database Systems

Andrew Keenan & Kamel Branch-Powers

Online Video Rental Store

Business Rules

  1. Two types of Users, Employees and Users
  2. Users must provide their email, phone, billing information and home address. All users are assigned a unique ID
  3. Employees provide their name, email, phone, home address. All employees are assigned a unique ID. Employees work at a specific shipping hub.
  4. There are multiple shipping hubs. Each shipping hub has its own inventory of movies.
  5. When a user rents a movie, the shipping hub closest to the user will send the movie to the user.
  6. A user may have items from multiple shipping hubs.
  7. The user will return all items to the nearest shipping hub. Items are then sent from that hub back to their native hub.
  8. Items are all assigned a unique ID.
  9. Items can be a movie or a video game.
  10. All movies are recorded by their name, genre, release date, director.
  11. All games are recorded by their name, genre, and release date.
  12. Every time an item is shipped out, the issue date and the expected return date are recorded
  13. A user can optionally rate an item from 1-5 stars and write a comment about it. The date is recorded when the user gave their rating.
  14. A user may rent up to 5 items at a time. They may return and rent new items as long as their total does not exceed 5.
  15. A user may place a reservation for an item that is currently out on loan.

Exceptions

  1. A user may not place a reservation for an item they already have.
  2. If an item is returned late, the user is charged a recurring $1 late fee for every day the movie is not returned.
  3. A user may not rent multiple copies of the same item.
  4. A user will be charged if their item is returned damaged.

Questions

  1. How many items does the company have as a whole?
  2. Which movies does a given user currently have out on loan?
  3. When will an out of stock movie be next available?
  4. What are the most popular items?
  5. What are the highest rated items?.