SIT103 Database and Information Retrieval

This SIT103 Database and Information Retrieval Assignment are to design and implement a database to support the management and services of the company.

Question 1 (ULO1) Create your own brief description of the following file organisations. You must use several sources for each file organisation to create your description. a) Sequential access b) Random access c) Index sequential access

Question 2 (ULO1) Describe in detail 3 techniques to store and retrieve data using an SQL database. Some techniques are based on SQL statements, and others based on setting the primary key value.

Question 3 (ULO2) Briefly describe the following processes related to the Entity-Relationship data model: a) Gathering business rules b) Normalisation Page

The task consists of the following four parts:

• 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, “Onebranch has manystaff 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.

• 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

• 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 SIT103 Database and Information Retrieval (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:

  • Create all tables in Deakin Oracle DBMS (about nine tables including composite tables) and populate the tables with sample data.
  • 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
  • Increase the annual salary for all managers by 5%.
  • Display the monthlysalary for the staff members who work in a given branch(identified by branch number), showing the staff number, name, position and monthlysalary, sorted by the monthlysalary from the highest to the lowest.
  • For a given branch(identified by branch number), display the number of staff members, minimum, maximum, and average annual salaries.
  • Display all customer members for a given branch(identified by branch number), sorted by the last name.
  • Display all movies with the genre of romance.
  • For a given director first name, display all movies he/she directed.
  • For a given actor first name, display all movies he/she played a role in.
  • 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.