CIS2002 Assignment 1 2014
|
Description |
Marks out of |
Wtg(%) |
Due date |
|
ASSIGNMENT 1 |
100.00 |
7% ONC / 10% EXT |
25 April 2014 |
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 the 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 ERD’s. 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 of the company wants to develop the system in the head office with web-based access for all store and warehouse managers from their offices. The project proposal has recently been approved and initial requirements gathering and analysis has been undertaken.
As part of the next phase in the project, the project manager has appointed you as the data analyst/ modeler and you are responsible for refining the data requirements for the project and modelling the database for the system. The project manager firstly wants you to research the expectations that database users (data entry/ admins), store managers and warehouse managers may have for the new database-driven inventory system during a stakeholder analysis.
Write a memorandum to the project manager and present your research findings on the expectations of the three different types of users. Your memorandum should be no longer than 500 words.
A template for the memorandum will be placed on the Study Desk in the ASSIGNMENTS folder. You should use this template. Selected Reading 1.3 is a good starting resource to study for this case.
You are expected to present solutions like an IT consultant’s report in a memo format. You are required to extensively research on the relevant topics and present concise and workable solution.
There is no requirement for formal in-text referencing in this question. However, it is recommended to put a list of references at the end of the memo showing the published materials that you researched while answering this question.
SECTION B (SQL) (40 marks)
For each question, three marks will be awarded for the SQL and one mark for the correct output.
The following E-R diagram represents a Car Hire database.
|
|
|
|
|
In this question, you will use the CAR HIRE database. The CAR HIRE database including
appropriate data will be made available on the USQ Oracle server. You must use this data.
If you are using Oracle on your own computer and are unable to access the USQ server, email the course leader for a script file that will create the tables for you. Due to copyright issues, you will need to insert the data yourself but you will be provided with a template.
The table descriptions appear below, including the column names and data types.
I_CAR
|
Column Name |
Type and Size |
Constraints |
Description |
|
Registration |
VARCHAR2(7) |
NOT NULL |
Registration number of the car. This is the Primary key. |
|
Model_name |
VARCHAR2(8) |
FK |
Model for the car. Foreign key into the Models table. |
|
Car_group_name |
VARCHAR2(2) |
FK |
Group code defining type of car and rental cost. Foreign key into the CarGroups table. |
|
Date_bought |
DATE |
|
Date the car was purchased. |
|
Cost |
NUMBER(8,2) |
|
The original cost of the car. |
|
Miles_to_date |
NUMBER(6) |
|
The current mileage of the car as read at the end of the most recent rental. |
|
Miles_last_service |
NUMBER(6) |
|
The mileage of the car when it was last serviced. |
|
Status |
CHAR(1) |
|
The current status of the car. ‘A’ for available, ‘H’ for on hire, ‘S’ for being serviced, ‘X’ for in need of service or repair. |
I_CARGROUP
|
Column Name |
Type and Size |
Constraints |
Description |
|
Car_group_name |
VARCHAR2(2) |
NOT NULL |
The car group code. This will be one of the following values: ‘A1’, ‘A2’, ‘A3’, ‘A4’, ‘B1’, ‘B2’, ‘B3’, or ‘B4’. This column is the primary key for this table. |
|
Rate_per_mile |
NUMBER(3) |
|
The charge per mile for cars in this group in cents. |
|
Rate_per_day |
NUMBER(5,2) |
|
The rental charge per day for cars in this group in dollars and cents. |
I_MODEL
|
Column Name |
Type and Size |
Constraints |
Description |
|
Model_name |
VARCHAR2(8) |
NOT NULL |
The model name, an abbreviation of the full model name. This is the primary key for this table. |
|
Car_group_name |
VARCHAR2(2) |
FK |
The group to which this model of car belongs. |
|
Description |
VARCHAR2(30) |
|
Full description of the model. |
|
Maint_int |
NUMBER(5) |
|
Number of miles between services for this model. |
I_CUSTOMER
|
Column Name |
Type and Size |
Constraints |
Description |
|
Cust_no |
NUMBER(5) |
NOT NULL |
The customer account number. This is the primary key for this table. |
|
Cust_name |
VARCHAR2(20) |
NOT NULL |
The name of the customer. |
|
Address |
VARCHAR2(20) |
|
Street address of the customer. |
|
Town |
VARCHAR2(20) |
|
Town the customer lives in. |
|
County |
VARCHAR2(20) |
|
County the customer lives in. Default is Australia |
|
Post_code |
VARCHAR2(10) |
|
Postcode for the town. |
|
Contact |
VARCHAR2(20) |
|
Name of person to contact. |
|
Pay_method |
CHAR(1) |
|
Code to indicate the usual payment method for this customer. ‘A’ indicates an account, ‘C’ indicates cash or credit card, NULL indicates unknown. |
I_BOOKING
|
Column Name |
Type and Size |
Constraints |
Description |
|
Booking_no |
NUMBER(5) |
NOT NULL |
A serial number used to uniquely identify the booking. This is the primary key for this table. |
|
Cust_no |
NUMBER(5) |
FK |
Customer number of the customer making the booking. |
|
Date_reserved |
DATE |
|
Date on which the booking was made. |
|
Reserved_by |
VARCHAR2(12) |
|
Name of the person who took the reservation. |
|
Date_rent_start |
DATE |
|
Date on which the rental commences. |
|
Rental_period |
NUMBER(3) |
|
Length of rental period in days. |
|
Registration |
VARCHAR2(7) |
FK |
Registration of the car actually rented. |
|
Model_name |
VARCHAR2(8) |
|
Model of the car rented. |
|
Miles_out |
NUMBER(6) |
|
Miles on the odometer at the start of the rental. |
|
Miles_in |
NUMBER(6) |
|
Miles on the odometer at the end of the rental. |
|
Amount_due |
NUMBER(6,2) |
|
Cost of the rental. Calculated when the car is returned. |
|
Paid |
CHAR(1) |
|
Flag to indicate if this rental has been paid for. ‘Y’ if it has been paid and ‘N’ if not. |
Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT. A screen dump of the output is acceptable. Show as many rows as you can. A screen dump is usually done via the ALT + PRNT SCRN command sequence if using Windows.
While the output helps to understand your solution, you should not be over analysing the output of the query. Remember we only have a very small dataset to work from. You can have an output that says NO ROWS FOUND and it could be a perfectly valid output.
1. Display full details of all customers.
2. Display the registration, cost and status for all cars. Order by cost in descending order.
3. Display the car registration, current mileage and purchase date for all cars. Order by current mileage in ascending order.
4. Display all the cars registration, miles to date and status for all the cars that have not had a service but are available for hire. Order the list in descending order of miles to date.
5. Display full details for all the bookings where amount due has not been paid.
6. Display the booking number, date rent started, period of rental and the expected end date of each rental. Label the expected end date of each rental as: ‘Expected Return Date’. Sort the output by the expected end date in ascending order.
7. Display full details for any car that: (a) costs more than $100,000.00 or the name of the car model starts with the letter ‘L’ and (b) belongs to group ‘A4‘ and has a registration whose second digit is 9.
8. Display full details for all bookings where the car has miles out value but the paid field has no value recorded. Both conditions need to be explicitly queried.
9. Display full details for all car models where the car group name is ‘A3’ or ‘B3’ AND the number of miles between services is either 12000 or 15000 miles. Find a way of optimising your code so that the complete SQL statement has only ONE logical operator (AND, OR, NOT).
10. Display the registration and miles traveled since last services and status for all the cars which have traveled more than 1500 miles since last service. Label the miles traveled since last service column “Miles Traveled” and order the query in descending order of miles traveled since lasted service.
SECTION C (Data Modelling) (40 marks)
For the 4 questions below construct a data models for the following specifications. Include an ERD and a list of relations (entity list). Your relations must show all attributes, primary keys and foreign keys. You must use the USQ (Finkelstein) methodology as described in your Study Book, the lectures and the tutorials.
Question 1
An ambulance driver may be assigned to many teams over time. A team comprises of a minimum of one driver but could have up to twelve. For driver, we store the name, address and birth date. For team, we store the team id and the location. We also need to store the date a driver is assigned to a team, the date a driver leaves the team and the rating of the driver within the team.
Question 2
A questionnaire is a research instrument consisting of a series of questions. We need to develop a model to store questionnaires and its underlying questions. A questionnaire records questionnaire name, primary contact name, start date and expected end date. A questionnaire may have many questions. Each question has a question number, question text and a category description for the question.
Question 3
An academic must be assigned to only one area of specialty. An area of specialty could have one or many academics associated with it. For academic, we store the first name, last name and date hired. For specialty, we store an id and a description.
Question 4
A researcher may be working on a number of research projects over time. A research project can have multiple researchers working on it but will only have a single head researcher but we do not need to know who the head researcher is. For researcher, we store the researcher identification number, his/her office number and area of specialty. We also need to store the date a researcher has started working on a research project; and research project title, project manager’s name and project administrator’s name and contact.
Prepare the following for all four questions:
1 An ER diagram: Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities but do not create entities that are not specifically covered by the specification. You must use the Finkelstein methodology as per the study book and tutorials.
2 A list of relations (entity list). Produce complete relations for all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned but do not create any other additional attributes.
MARKING CRITERIA
SECTION A
1. Solution addresses the business problem and provides a workable resolution to the problem.
2. Solution demonstrates appropriate reference to relevant sections of the selected readings, textbook and, if applicable, other reference material.
3. Solution addresses the assumptions of the problem appropriately.
4. Solution uses the correct template.
5. Solution is grammatically and structurally sound.
6. Presentation is neat and professional.
SECTION B
1. Three marks awarded for each correct SQL statement and one mark for the correct output.
2. Alternative approaches to the model answer will often be accepted unless they do not follow the rules / requirements set out in the specification, are poorly optimised or are poorly constructed (SQL).
3. Part marks may be awarded if an answer only has a small problem or an alternative solution is presented that works but is not ideally optimised / constructed.
SECTION C
1. Entities – no missing entities, appropriate names, no redundant entities, etc.
2. Cardinalities and optionalities all shown and correct.
3. Complete list of relations, showing all applicable attributes, primary keys and foreign keys.
4. Sophistication: well presented solution; good layout; innovative approach; correct
diagrams/notation; solution easy to read and understand; solution comprehensive
0 comments