S305/S520 -- Database Management

Videos Unlimited, Inc. (VUI) Case Study

Overview

Videos Unlimited, Inc. (VUI) is a regional video store franchise, serving the Upper Peninsula of Michigan and selected portions of Northern Wisconsin with 50 stores. Currently, VUI is represented by individually owned and operated stores in small-to-medium-sized cities and towns (approximately 1000 - 15000 in population size). Transactions have been conducted on a cash basis and customer records have been kept manually on 3X5 cards contained in small recipe boxes. The VUI stores do not have membership plans nor do they require deposits. The movie and tape inventory of each store is maintained on hand-written journal sheets stored in a three-ring binder and updated on a quarterly basis.

The VUI store managers pride themselves on their ability to maintain a level of comfortable, "hometown" serviceto their customers: being able to greet customers by name, engaging customers in conversation about their favorite movies, special ordering of movies for frequent customers, and "grace periods" for returns of movies from frequent customers. Although the "hometown" flavor to customer service is noted as a strength of these video stores, their profits are slowly being eroded by such national video chains as Blockbuster Video and Captain Video Rentals that can more readily stock the recent releases, can provide a greater variety of movies in a single store, can locate hard-to-find movies in their nationwide database, and are open for longer hours each day.

At a recent day-long meeting of all managers, the topic of how to improve store operations (and thus profits) was debated at great length. The president and CEO of VUI, Joe Warner, challenged the managers to identify how the "hometown" service could be maintained while competing with the national video rental chains. After much discussion, the managers and the president agreed that it was time for VUI to use information technology in support of their business objectives. In particular, VUI wants to develop a region-wide database application for tracking customers, movies, and rentals in all of their stores.

A team of consultants was hired to develop preliminary information requirements based on interviews with VUI store managers and clerks. The preliminary information requirements (i.e., what data is currently captured, what is the current organization of the data, what events need to be supported by the system) have been documented in the textual narrative in the remainder of this document.

Your assignment is to take the next steps in the analysis/design process: (1) refine the initial information requirements; (2) develop the conceptual data model; (3) translate the conceptual data model into the logical design (for a relational model); and (4) use Microsoft Access to produce a prototype of the VUI Video Rental System (see last page for prototype requirements).

VUI Operations

Rental information: The standard time period for a rental is two days after the borrowed tape is issued. However, "new release" tapes are due back within 24 hours after the borrowed tape is issued. From time to time, individual stores run weekend or holiday promotions which permit customers to check-out 4 tapes for 3 days. If the customer fails to return the borrowed tapes within a one-day grace period from the agreed-upon return date noted on the rental receipt, then it is time to contact the customer by phone to request return of the items.

A rental is the borrowing of a tape to a particular person in exchange for cash. A rental has a check-out date, a return date, and a rental charge. If a tape is late, there is a late charge of $2.00 per day. The rental fee is not the same for all movies; some are higher than others (e.g., new releases, special orders). Customers can rent more than one tape at a time.

Movie information: A movie is a cassette of video tape with a pre-recorded movie that can be rented. Selected movies can also be bought by customers at certain stores. Each movie has an identification number, a name, a release date, a type (western, adventure, action, comedy, science fiction, true crime, family, musical, biography, etc.), a purchase price, names of key actors/actresses, and rental rate. There can be more than one copy of each movie.

A movie copy can be rented, it can be sitting on the shelf waiting to be rented, it can be new (not entered into the system yet), it can be overdue from rental period, it can be reserved for a customer, or it can be available for sale (but not rental). Movie copies are purchased from national wholesalers on a monthly basis to replenish each store's inventory. The store price for all movies for sale is calculated based on a 10% markup from the price paid by VUI to the wholesaler.

Customer information: Customers have an identification number, names, address, and phone. VUI would like to track the renting history of its members and reward those members who maintain a frequent renting record (at least 2 tapes per week) or a yearly record (more than 100 tapes a year). Further, in keeping with the "hometown" service aspect of VUI, there is a need to allow for: customer reservation of movies, customer searching for movies by actor/actress, and store tracking of customer movie type preference.

Other details

At a later date, VUI would like to enlarge their base of operations to include outlets at gas stations and 24-hour food marts. Key to this operational expansion will be the database for tracking customers/members, movies, and rentals so that VUI operations can continue outside of the franchise stores. Management anticipates that the prototype database will provide a means of quickly tracking inventory and customers regardless of the rental site.

There are a number of standard events that the Video Rental system must accommodate. The following table provides a sample set of events that the Video Rental system must support:

Sample Events for the Video Rental System

Event 1 / Customer requests tape for rental from VUI store.
Event 2 / Customer makes rental payment.
Event 3 / VUI store clerk adds new movie to store inventory.
Event 4 / VUI store manager retires old movie copies from store inventory.
Event 5 / Customer pays late charge.
Event 6 / VUI store manager submits a rental rate change to a movie.
Event 7 / Customer returns tape to VUI store.
Event 8 / VUI store clerk needs to produce a report of current movie rentals from the store.
Event 9 / VUI store clerk adds a new customer to the store database.
Event 10 / VUI store clerk changes a customer's phone number.
Event 11 / VUI store manager needs to view a list of active customers.
Event 12 / VUI store manager needs to review the store's movie inventory and determine what additional movie copies to order from the wholesaler.
Event 13 / Customer requests a movie to be reserved from a VUI store.
Event 14 / Customer buys a movie from a VUI store.
Event 15 / VUI store manager needs to review current members and their rental history to determine upcoming rental promotions.

You have been provided with a Movies database (Courtesy of Bruce Rollier). The database contains a superset (over 1,200 titles) of the movies titles in your store so you will not need to enter movie title information. The following is the data model for the database.

DATABASE REQUIREMENTS:

  1. Modify the existing Movies database tables as necessary and create the appropriate tables.
  2. Create the relationships between the tables specified in the relational schema provided. Referential integrity should be enforced for all relationships.
  3. Populate the newly created tables. Although there are 50 stores, only include the holdings for three of the locations with 10-20 titles per location.

SYSTEM REQUIREMENTS:

1.Your system should be able to respond the previously listed events. You should also gather additional data on video store operations from other sources.

2.Use the following scenario as a means for validating your system.

An existing customer, Juan Dos, has visited one of your store locations and performs the following queries (note that the first two queries can be completed without any modifications to the Movie database so you may get started on those immediately)

Query Name / Description
Canadian and Mexican Actresses / List (in this order) the Movie name, the star, and the year of all movies featuring stars from Canada or Mexico. The query should sort the result by Movie name in ascending order.
Best Female Directors / List (in this order) the movie title, the director, and the yearof all movies that both received a best picture award and were directed by a woman.
Customer Favorites / List (in this order) the favorite movie, and customer name of each customer. The results should be sorted first by movie title, then by customer name.
PG Comedies at Each Location / List (in this order), the store name and the number of PG comedy movies at that store. Sort the results by number of PG movies (in descending order) then by store name (ascending order).

Later that same visit, Juan rents the following three movies (He also updates his favorite movie to “The Matrix”)

Movie
Toy Story (1995)
Vertigo (1958)
Breakfast at Tiffany's (1961)

He also returns a movie he rented 3 weeks ago and pays the fine.

3.At the end of each transaction (rentals, returns, and sales), the system should be capable of generating a summary report. Create the following rental receipt as an MS Access Report (see rental receipt example below). You should make your report look as much like this sample as possible. Hint: The fastest way to create this report is to use the Report Wizard first, then modify the wizard output to match the sample. The calculated fields can be created in the wizard.

1

(revised 3.6.2000, 8.23.2006) (vuicase.doc)