Database and information

 

 

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,
Page 2 of 4
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.
As a database expert, you are approached by MovieLover 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.

 

 

Design and implement a database to support the management and services of the company.

 

The task consists of the following four parts.
A. A list of business rules (15 marks)
A business rule should be able to identify the entities and the multiplicity of the relationship between
the entities. For example, “One branch has many staff members”. The list should cover all business
rules applied to the scenario above.
Instruction: Use a dotted item for each business rule. Assume that a movie has only one director.
B. Entity-Relationship (E-R) diagram (30 marks)
ER diagram is based on the business rules, and should clearly label all entities, entity attributes,
primary and foreign keys, relationship and connectivity. The cardinality is optional.
Instruction: Use professional software (e.g., MS Office Visio) to draw the ER diagram. Crow’s Foot
notation is preferable.
C. Data dictionary (15 marks)
Data dictionary is based on the ER diagram, and contains all table names, table attribute definitions,
as well as their characteristics and relationships that are to be implemented in the database.
Instruction: Use data dictionary template (e.g., Table 3.6 on page 92, Table 7.3 on page 253 of the
textbook, or the example in lecture notes).
Minimum Standards for Unit Learning Outcomes (ULO2 and ULO2)
As this assignment assesses ULO2 and ULO3, it is compulsory to complete and pass Part B
(ERD) and Part D-Question 1 to demonstrate your achievement of minimum standards for
ULO2 and ULO3. Pass means = 50%.
Page 3 of 4
D. SQL commands (40 marks)
Based on the ER diagram and data dictionary, you are required to use SQL to create a database
and implement the database functions as indicated below:
1. Create all tables in Deakin Oracle DBMS (about nine tables including composite tables) and
populate the tables with sample data.
2. Display all staff members whose annual salary is between $20,000 and $50,000 (inclusive),
sorted by the annual salary from the highest to the lowest.
3. Increase the annual salary for all managers by 5%.
4. Display the monthly salary for the staff members who work in a given branch (identified by branch
number), showing the staff number, name, position and monthly salary, sorted by the monthly
salary from the highest to the lowest.
5. For a given branch (identified by branch number), display the number of staff members, minimum,
maximum, and average annual salaries.
6. Display all customer members for a given branch (identified by branch number), sorted by the
last name.
7. Display all movies with the genre of romance.
8. For a given director first name, display all movies he/she directed.
9. For a given actor first name, display all movies he/she played a role in.
10. Display the rental history of a given customer (identified by member ID number), showing
customer name, phone number, movie copy number, movie title, branch number, renting out
date, and returning date.