Case 2: Systems Analysis I

Assigned: 20 February 2005
Due: 1 March 2005

UPS Data Model

Drivers (identified by DRIVER-NO, with each driver having a NAME, one HOME ADDRESS, and a DATE-OF-BIRTH) take out vehicles to make deliveries. A vehicle (identified by VEHICLE-NO, with each vehicle being of a particular MAKE and YEAR-OF-MANUFACTURE) may be taken out of a depot whenever available and kept out for any length of time (ranging from one or two hours to a number of days.) It is possible for any vehicle to be taken out more than once on a given day. Any vehicle can be taken out by any driver any number of times each day. There is only one depot. Each time a driver takes out a vehicle, he or she takes out a load made up of any QTY of a number of item types (identified by an ITEM-NO and having a COLOR, WEIGHT, and DESCRIPTION.) Each time a vehicle is taken out, the driver can incur expenses of allowed types (e.g.. fuel cost). Each expense type has CODE-NO. The AMOUNT and CODE-NO are recorded each time an expense is incurred. There may be one or more expenses incurred during the same trip. Any number of stops can be made during the trip. An ADDRESS of the stop is recorded for each stop, together with the QTY-LEFT of each item type left at the stop. A driver will stop at an ADDRESS only once during one trip. However, it is possible for stops to be made at the same address on different trips.

Tasks

The UPS scenario can be divided into the following two rough areas:

The vehicle/driver/depot area
The load/item/trip/stop area

Your group will develop a data model for the UPS scenario by completing the following tasks:

Develop a data model for the vehicle/driver/depot area.
Develop a data model for the load/item/trip/stop area.
Develop a way to combine the data models for the two areas into one large data model.

Suggested sequence for constructing the data model:

Identify the entities.
Draw the relationships between the entities.
Fill in the attributes for the entities.
Create an Entity Relationship Diagram (ERD) of your model.

Submission Requirements

There should be only one final model, the combined model. As your ERDs are stored on the server, there is no need for you to submit this.
Each entity should have at least one attribute, the Identifier Attribute.
All attributes must be fully defined in terms of format, acceptable values, etc.
Clearly label the filename of your ERD to reflect group and case number. For example, Group 5 should label their diagram, Group5_UPS.
Submit a one-page report stating the following:

For each relationship in your model, state the assumption (or fact) in making that relationship. Provide a simple example to illustrate the relationship. For example, if you assume that DRIVER - VEHICLE is a one-to-one relationship, then indicate that you have assumed at UPS that every driver has their own vehicle as an assumption.
Suggest at least one change you would make to improve the data model. Clearly state in what manner the model has improved, and also how the data model has changed to achieve this improvement. Implement this change in your ERD, and save the new file as Group5_UPS_Improved.

Your one-page report should be published on your Class Wiki site as a document, preferably in html. Choose one person in your group as the coordinator, and publish the document to his/her folder. That will serve as the document for the group. Do not publish as a Wiki page. Once you publish it, I will be able to access it.