CIS2002 Assignment 1 Specification

Cis2002 Assignment 1 2014assignment 1 Specification

There are 3 sections to this assignment. You are required to attempt all sections and all questions. You must submit the assignment electronically by the due date via the EASE link on the study desk. Instructions will be provided on the course study desk. You must use the official USQ data modelling and normalization methodology.

This methodology is based on Clive Finkelstein’s techniques (SR 2.1 and SR 2.2) and all the examples in the lectures, study book, and tutorials use this methodology. If you do not use the USQ methodology, you will probably be awarded a mark of zero. It is perfectly acceptable if you submit neat hand-drawn ERDs. Alternatively, you might wish to use Word. If you use a CASE or drawing tool, you must adapt the drawing to conform to the USQ methodology.

SECTION A (APPLIED DATABASE THEORY) (20 marks)

FABILOUS FASHION is a ladies apparel business with over 60 retail outlets in NSW, VIC, and QLD. The company wishes to develop a system that controls and manages their inventory to better coordinate operations between their outlets and three warehouses in the three states. The company head office in Melbourne has initiated development of a database-driven inventory management system. The general manager wants the system to be developed at the head office with web-based access for all store and warehouse managers from their offices. The project proposal has been approved, and initial requirements gathering and analysis have been undertaken.

As part of the next phase, you are tasked with refining data requirements and modeling the database. The project manager wants you to research the expectations that database users (data entry/admins), store managers, and warehouse managers may have for the new inventory system during a stakeholder analysis. Write a memorandum to the project manager presenting your research findings on the expectations of these three user groups. The memorandum should be no longer than 500 words. Use the provided template and focus on concise, workable solutions. Extensive research on relevant topics is expected. Formal references are not required within the memo, but a list of sources researched should be included at the end.

SECTION B (SQL) (40 marks)

For each question, three marks will be awarded for the correct SQL and one mark for the correct output. The CAR HIRE database is available on the USQ Oracle server, including necessary data. Use the provided table descriptions and constraints.

Write SQL queries to solve the following specifications, include the query and the output (a screen dump or sample data). The dataset may be small or even yield no rows, which is acceptable.

1. Display full details of all customers.
2. Display registration, cost, and status for all cars, ordered by cost descending.
3. Display registration, current mileage, and purchase date for all cars, ordered by mileage ascending.
4. Display registration, miles to date, and status for available cars with no service history, ordered by miles to date descending.
5. Display full details of bookings where amount due has not been paid.
6. Display booking number, start date, rental period, and expected return date, sorted by expected return date ascending.
7. Display details for cars costing over $100,000 or models starting with “L”, belong to group ‘A4’, and registration whose second digit is 9.
8. Display details for bookings where miles out value exists but payment has no recorded value.
9. Display model details for groups ‘A3’ or ‘B3’, with service miles 12000 or 15000, optimizing the query to use only one logical operator.
10. Display registration and miles traveled since last service, status for cars with over 1500 miles since last service, ordered by miles traveled descending.

SECTION C (Data Modelling) (40 marks)

For each of the four questions, construct an ER diagram using the USQ methodology, showing entities, relationships, cardinalities, and optionalities. Include all attributes, primary keys, and foreign keys. Do not create extra entities beyond those specified.

1. Model ambulance drivers assigned to many teams over time. Store driver name, address, birth date, team id, location, assignment start/end dates, and driver rating within the team.
2. Model questionnaires with multiple questions. Store questionnaire name, primary contact, start/end dates; questions include question number, text, and category.
3. Model academic staff with one area of specialty. Store staff first name, last name, hire date; specialty includes an id and description.
4. Model researchers working on research projects. Store researcher ID, office number, area of specialty, project start date, project title, project manager and administrator names, and contacts. Each researcher can work on multiple projects, but each project has only one head researcher, though this detail is not stored.

For each question, produce:

• An ER diagram with all entities, relationships, and their attributes, including primary and foreign keys.
• A relation list for all entities, indicating primary keys and foreign keys, following Finkelstein methodology.

Ensure clarity, completeness, and adherence to the specified methodology in your models.