Scenario A company named MovieLover provides the service of renting out movie videos to its members. Currently the company has about 200 staff working in 25 branches. Each staff member works at one branch. Each branch is managed by a manager who is also a staff member. When a staff member joins the company, the staff member’s personal information is recorded, which includes staff number, name, position (e.g. manager, assistant), annual salary, email address and branch number.
MovieLover stocks about 10,000 movie titles. Each movie title is uniquely identified by a catalog number, and has its genre (e.g. romance, action, science-fiction etc.) and daily rental rate (i.e., rental price). In most cases, each movie title has many copies at each branch, and each copy is identified by a movie copy number. If a copy is rented out, it is not available for renting until it is returned.
In order to rent movies, a customer is required to be registered as a member of MovieLover by providing his/her personal information, which includes name, postal address, email address, phone number, password, branch number and registered date. Email address and password are required for a member to access his/her account at MovieLover website.
To assist members in selecting movies, MovieLover website provides a movie search function supported by the database system. With the search function, a member can search for preferred movies based on the movie title, main actor, director, genre, or release date.
A member rents movie copies from a branch. The company keeps the rental history for each member. The history records keep the member’s ID number, rented movies, renting out dates, returning dates, overdue or not, and fined or not.
a database expert, you are approached by MovieLoverr CEO to analyze current management and business processes of the company, design and implement a database to enable the staff members and customers to easily obtain required information for better management and services.