SIT103 Database and Information Retrieval Assignment

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.

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.

NEW SOLUTIONS

OZ Assignment Help provides assignments, essays and dissertations writing for guidance and reference purpose only. None of the product is to be submitted directly as the final work. Company will not take any responsibility of any type of academic misconduct done by individual.