SEMESTER 1, 2010

Assignment 1 specification

Description  Marks out of  Wtg(%)  Due date 

ASSIGNMENT 1 100.00 12.00 17 May 2010

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.

You must use the official USQ data modelling and normalization methodology. This methodology is based on Clive Finkelstein’s techniques (SR 4.1 and SR 4.2) and all the examples in the lectures, study book and the tutorials use this methodology. Note that the methodology differs slightly from that presented in D’Orazio and the lectures will provide you with detailed information about the differences.

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.

 

Question 1 (65 marks)

Construct a data model for the following specification. 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.

The BURNING VALE town council fire department wishes to develop a system that automates the dispatch of fire engines to attend fires:

The town owns sixteen fire engines. For each fire engine, a unique identified is stored along with details about manufacturer, make, date of purchase, date of manufacture and mileage. The fire department employs around 170 people. There are several types of employee, including general, administrative and crew. For all employees, we store the employee name, address, email and contact number. For administrative employees, we store the name of the highest academic qualification obtained. Each employee of type crew must be assigned to one fire engine and a fire engine has up to ten crew members assigned to it. For employees who are crew members, we also store details about the name and address of the next of kin. Each crew is effectively a team and has a team leader who is also a member of the crew.

Fire engines are rostered (scheduled) for duty. The roster holds details of the fire engine, the day of the week and the start time of the shift.

When a fire is reported, the despatcher  (a general employee) records details about the fire including intensity, address and name of the person reporting. One or more fire engines are then despatched to the fire. Details about the time of despatch and return time are stored. Once a fire has been doused (put out), details are stored about the estimated damage (in dollars) and how much time was taken to douse the fire.

 

Prepare the following:

1 An ER diagram for the system. 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. DO NOT draw up an entity list.

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.

Question 2 (35 marks)

Produce a set of relations in 3NF from the following unnormalised relation. You are not required to show the 1NF and 2NF steps and you are permitted to make assumptions although these may not be accepted. Few (if any) such assumptions are likely to be necessary and they should be noted. You must use the USQ (Finkelstein) methodology as described in your Study Book, the lectures and the tutorials and you should explicitly show all primary and foreign keys

CATALOGUE(item number, name, description, item type, item type description, ((supplier code, supplier name, supplier address, percentage supplied)), warehouse location id, warehouse location descriptor, no of items in catalogue )

Notes:

1. There is only one active catalogue. We do not require a catalogue history.

2. An item may be supplied by many suppliers, in which case we record the percentage of the item supplied by each supplier.

3. An item is of one item type and sourced from one warehouse