Case scenario
A movie and game rental company, Blockbuster Plus, has been operating in the various Melbourne suburbs and serving the communities for the past fifteen years. You have been asked to analyse the current front-end business processes and design a data model, which could then be implemented into a new rental management system.
Currently the company employs 85 staff members working across 17 branches. Each branch, identified by its branch number and suburb, is run by a team of staff members who worked under the supervision of a manager, who is also a staff member(urnary). When a staff member joins the team, s/he is required to provide the following information: name, gender, hours worked per week, e-mail and a mobile contact number. Additional information will be provided and recorded at the time a staff is hired: staff ID, position (manager or team member), wage per hour, branch number.
Blockbuster Plus stocks over 15,000 movie as well as game titles. Each movie or game record contains a media ID, movie or game title, type (M – movie, or G – game), media player (DVD, Blu-ray or 4K, or game console, which includes x-box, PS or PC), media released date, category (G – general, PG – parental guidance, M – mature, MA15+ – mature accompanied, R18+ – restricted, X18+ – restricted to adult), rating, copies available, and genre. The movie genre includes action, adventure, comedy, crime, drama, fantasy, historical, horror, mystery, romance, science fiction, thriller and western. The game genre comprises action, action-adventure, role-playing, simulation, strategy, sports and puzzle. The genre lists are meant to be non-exhaustive and can grow with the collection.
A store carries many copies of the same movie or game. The number of copies stocked in a branch is dependent on the title rating. For the least popular item (with a rating of 1.0 or less), the store stocks one copy. A rating of 2.0 or less warrants two copies; a rating of 3.0 or less warrants three copies; a rating of 4.0 or less warrants five copies; a rating of less than 5.0 warrants seven copies; and finally, for popular titles with a rating between 5.0 and 5.5, the store can stock up to twelve copies.(give type) Each copy of a movie or game is identified by a catalogue number, along with the movie or game ID, rental rate, state (STD – standard, N – new release, OS – on special, UFS – used copy for sale, NFS – new copy for sale), and availability (A – available, or R – rented).
The standard rental rate is dependent on the duration of a rental: AUD1.50 for one day; AUD2.75 for two days; and AUD3.75 for three days, AUD5.50 for 5 days and up to AUD9.00 for a week. A late penalty of AUD2.50 is also applied for each day late. A new release can only be rented out for one day at a rental rate of AUD3.50 each time; and a late penalty of AUD5.00 per day is applied. The on-special items are given a 30% discount off the standard rental rates; but the same late penalty applies. A used copy (movie or game) is sold for AUD5.00; a new copy (movie or game) is sold for AUD12.00.
A customer who rents from a Blockbuster Plus branch must register their member ID, first name, last name, address, e-mail, phone number, password, branch number, date at time of registration, date of closing the account (NULL by default), and the last active date. The e-mail and password are used as a login credential on the Blockbuster Plus website to access to various site services. One such service is the ability to search a movie or game by its title, actor, director or game console, genre and media released date. To facilitate the search functionality of a movie, additional data will need to be stored regarding each of following: the actor and director. An actor can play many roles in one or more movies; and a movie have one or more roles. A director can direct one or more movies; and a movie is directed by only one director.
A Blockbuster branch keeps a record of each customer’s rental as well as purchase history. To track the rental history, the store keeps track of the following: the serving staff ID, member ID, catalogue ID of a movie or game, date out, rental duration, date returned, amount due, penalty paid (Yes or No – only when late, otherwise NULL to indicate no penalty applied). The status of rental (late or on time) can be determined by calculating the difference between date returned and date out. To track the purchase history, the store keeps track of the following: the serving staff ID, member ID, catalogue ID of a movie or game purchased, date purchased.