Delivery in day(s): 3
SIT103 Database and Information Retrieval Assignment
This SIT103 Database and Information Retrieval Assignment are to 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
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
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
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 management.
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).
D. SQL commands
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.