CIS 2002
SEMESTER 3, 2013

Assignment 2 specification

Description: ASSIGNMENT 2
Marks out of: 100.00
Wtg (%): 25%
Due date: 24 Jan 2014
IMPORTANT INFORMATION
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.
If you are unable to complete the whole assignment, submit what you have done rather than nothing.
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.
This assignment covers much of the same ground as the exam and will provide you with a strong indicator of your level of preparedness for the exam. Most of the questions are at examination standard.
SECTION A (Data Modelling) (40 marks)

MARKING CRITERIA

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
5. SQL CREATE TABLE uses an appropriate relation, is syntactically correct and meets the business rules.
6. USQ methodology used throughout.

A wine wholesaler in Adelaide wants to develop a fully customised customer relationship management (CRM) system to track its marketing activities. You are required to develop a data model which will allow following information and activities to be recorded.

The CRM system needs to store the following for a customer: company name, business address, suburb, postcode, state, Australian Business Number (ABN), contact person, contact number, contact email, date of entry and profile. There are three profiles of a customer: wine retailers, hotels and premium individuals. For wine retailers, we store their nominated distributor name, distributor address and distributor contact number. For hotels, we store the name of the hotel chain. For premium individual customers, we store their date of birth and wine preference.

The wholesaler often runs marketing campaigns over multiple channels such as email, social media, direct mail and trade events. Each marketing campaign must be recorded with the following information: campaign name, target customer profile, budget, campaign start date, campaign end date, campaign leader, a number of campaign support staff. Information about staff includes staff code, name, designation and hired date. A staff may work on a number of campaigns simultaneously. We need to store the number of hours each staff spends on each campaign and their role in the campaign. A campaign leader is also a staff member and there has to be one allocated staff member as a leader for a marketing campaign. There are two categories of marketing campaigns: online and face-to-face. In a face-to-face marketing campaign, we must record if free wine tasting was offered or not. For online campaigns, we track the number of clicks on the campaign webpage by customers.

Each campaign captures many new customers as leads. A customer is entered into the CRM system from one and only one campaign or may be entered without a campaign as well.

Prepare the following:

a) An ER diagram for the system. Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities. You must use the Finkelstein methodology as per the study book and tutorials.

b) A list of relations (equivalent to Finkelstein 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. You must use the Finkelstein methodology as per the study book and tutorials.

c) An Oracle SQL table create statement for the relation that you think is most critical in this system. This relation must have a primary key and at least one foreign key.

SECTION B (Normalisation) (20 marks)

MARKING CRITERIA

1. Relations – no missing relations, appropriate names, no redundant relations.
2. All primary keys present and correctly notated.
3. All foreign keys present and correctly notated.
4. All attributes present.
5. All repeating groups resolved.
6. Derived attributes indicated in brackets.
7. All 2NF and transitive dependencies resolved.
8. All relations correctly notated using USQ methodology.

Produce a set of relations (equivalent to the Finkelstein entity list) in third normal form (3NF) from the following un-normalised relation. You must use the Finkelstein methodology as used in the study book and tutorials.

BOOK (isbn, title, paperback, ((author name, author address, author contact)), number of authors, publisher name, publisher address, publisher contact, published date, language, category, price, ((review code, review rating, reviewer comments, date reviewed)) )

1. Each book is identified by an ISBN.
2. A book may have multiple authors and multiple reviews by different customers but only one publisher.
3. The attribute ‘published date’ belongs to the book and it has nothing to do with a publisher.
4. The attributes ‘review code’ and ‘review rating’ are standard review values based on the following table:
review code review rating
1 ?
2 ??
3 ???
4 ????
5 ?????

SECTION C (SQL) (40 marks)
MARKING CRITERIA


1. Four marks awarded for each correct SQL statement and one mark for the correct output.
2. Alternative approaches to the model answer could 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.

For each question, four marks will be awarded for the SQL and one mark for the 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 may query any of these tables but may not make changes.

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 database for you.

The table descriptions appear below, including the column names and data types.

I_CAR

I_CARGROUP

I_MODEL

I_CUSTOMER

I_BOOKING

Write SQL queries to solve the following specifications. Provide the queries and the output.
1. Display full details of all car bookings where (a) car model name starts with ‘P’ or the staff name that completes the reservation starts with ‘R’ and (b) booking has been fully paid. Order the output by car registration in ascending order.

2. Using a set operator, find out all names of the car models that have not had any bookings.

3. Display the booking number, cost of the car, cost of the rental, whether the rental has been paid for and the usual payment method for the customer who booked. Filter your results to show only the records where the customer’s usual payment method is cash or credit card (i.e. pay_method = ‘C’).

4. Display the average cost of the rental for each car model where the average cost is greater than $1,000. Format the output as a currency value (i.e. formatted as $9,999.99).

5. Display the total number of cars that has the most expensive rental charge per day.

6. Display the booking number, car registration, reserved date and rental start date for all bookings that are reserved more than one month in advance.

7. Display all the cars purchased in the year 2010 that cost more than $50,000 and are available for hire (i.e. status = ‘A’).

8. Display the total miles travelled for all the bookings made in July 2013. Exclude all the bookings made by customers from YORKSHIRE county.