Question

I have weekly assignments that consist of interim deliverables for my final project. For the first week, I need to submit a proposal. The proposal should outline how you plan to approach completing your final project and how you plan to overcome obstacles in developing your project.

The project is the following:

The final project is a semester-long exercise that has multiple deliverables throughout the course. Each deliverable is a building block on which you will construct the final project. The approach to building the final project is an abbreviated version of how software projects are typically done in a commercial environment. The general construction steps are as follows:

Customer requirements – Initial project requirements

Systems Analysis – Development of diagrams and concept refinement

Systems Architecture – Constructing the technical framework of the system

System Development – Writing code for the system

System Testing – Testing the functionality of the system to ensure that it meets requirements and is bug-free

System Release – Submission to your instructor

Minimum system requirements:

System design documents

Tables in 3rd normal form

Look up tables

Use of stored procedures for query, insert, and delete

In code commenting

System create scripts

System data insert scripts to populate lookup tables as well as some ‘dummy’ data for the rest of the system so that your instructor may test your system

Note: It can be very easy to read too much into the requirements for any software project, so it important to contact your instructor to get clarification, if needed. If possible, please make contact through the project discussion forum so that the entire class has the benefit of the answer.

The development of the system does not require any knowledge of trucking systems. You will be building your system based on general knowledge that anyone would have about delivering cargo. (Think in terms of what you know about UPS or FexEX).

We are concerned with knowing what cargo was in a truck, who drove the truck, which truck was used, when was it delivered and some information on any truck maintenance that was done during the haul.

The below scenario greatly simplifies what would happen in a real-world scenario. There will only be one driver for the truck, only one truck will be used during a haul and only one trailer will be used for a haul.

You have been hired by Acme Software, Inc. to develop a database for its customer, XYZ Trucking. The proposed database will manage its fleet of trucks. Your task will be to do the initial design of the database that will eventually connect with a web based front end to be developed internally by Acme Software, Inc. You will not be working on the web based front end.

Concept

XYZ Trucking manages several hundred trucks. The proposed system shall manage the daily movements of trucks and drivers as well as track truck downtime due to maintenance or other unforeseen circumstances. Some fleet trucks are a single unit, without trailer, while some are tractor-trailer combination (18-wheel) vehicles that can pull varying types of trailers.

A preliminary business analysis has been done and the analyst has determined that the below data is required for the initial database system.

Minimum Required Data Elements: (Key M = Element must always have a value, L = look up table of default values). Items in ()’s are the only legal values for those fields.

Driver Demographic Information:
Name M
Date of Birth M
Employee Number M
Date of Hire
Commercial Driver License (yes or no) M

Truck Information:
Truck Type (Long or short haul) M
Truck Body Type (Tracker Trailer or Single Unit) M
Truck Number M
Truck License Number
Truck Description
Truck Engine Type
Truck Fuel Type
Truck Current Mileage M

Trailer Information (Tractor-Trailer Only)
Trailer Type (Tanker, Flat Bed, Box, Refrigerated) M, L
Trailer Capacity M
Trailer Mileage M
Trailer Description

Haul Record (Delivery Records)
Truck Used M
Client M
Cargo Type (Hazardous, Liquid, Refrigerated, Standard, Other), L
Date Haul Began M
Date Delivered M
Mileage M
Haul Notes

Haul Manifest (Inventory of items delivered)
Item M
Item Description
Item Weight Per Unit
Quantity M

Truck Maintenance
Truck M
Maintenance Start Date M
Maintenance End Date M
Maintenance Type (Engine, Transmission, Tires, Body, Electrical, Hydraulic, Pneumatic) M, L
Maintenance Code (Routine, Unscheduled) M, L

Using the minimum data elements, and any others that you determine are necessary, build a prototype database system. The user interface being developed by Acme Software shall use stored procedures developed by you to access data in the database. All data manipulation (insert, update, delete, and query) shall be done through your stored procedures.

Business Logic

The basic business logic of the system shall be included in the database’s stored procedures. Design of the logic will require some systems analysis in order to ensure that the logic is correct.

A driver shall always be associated with a haul. (This means that whenever there is data inserted into the haul tables, the driver ID must be included in that data.)

A truck that is a tractor-trailer combination shall always be associated with trailer information.

If a truck has a maintenance date that is between a begin haul date and delivery date, that information must be included in the Haul Notes. (This one is a bit more complex because the stored procedure that inserts a haul record must have logic to check the maintenance tables(s) to see if that truck has scheduled maintenance due between the beginning date and end dates for that haul. If so, the record inserted into the haul table(s) must include a note on that maintenance in the Haul Notes column of the table.) NOTE: This logic is optional for the final project.

Report stored procedures:

Truck Maintenance: The stored procedure shall accept a date range for the report and shall include the truck number, maintenance done, and shall be ordered by long haul, short haul and maintenance date.

Haul Record:The stored procedure shall accept a truck number and date range. The stored procedure shall return the haul record of the truck in chronological order but not include detailed inventory.

Haul Inventory: This stored procedure is similar to Haul Record, except it shall also include the detailed haul inventory.

Custom Report: Develop one other report of your choosing.

Answer 1

To better understand this concept, consider the order_date column. Can it exist independent of theorder_id column? No: an "order date" is meaningless without an order. order_date is said todepend on a key attribute (order_id is the "key attribute" because it is the primary key of the table).

What about customer_name — can it exist on its own, outside of the orders table?

Yes. It is meaningful to talk about a customer name without referring to an order or invoice. The same goes for customer_address, customer_city, and customer_state. These four columns actually rely on customer_id, which is not a key in this table (it is a non-key attribute).

These fields belong in their own table, with customer_id as the primary key (see Figure I).

However, you will notice in Figure I that we have severed the relationship between theorders table and the Customer data that used to inhabit it.

This won't do at all.

Figure I:

We have to restore the relationship by creating something called a foreign key(indicated in our diagramby(FK)) in the orders table. A foreign key is essentially a column that points to the primary key in another table.Figure J describes this relationship:

The relationship that has been established between the ordersand customers table may be expressed in this way:

• each order is made by one, and only one customer;
• each customer can make any number of orders, including zero.

Figure J:

One final refinement...

You will notice that the order_id and item_id columns inorder_items perform a dual purpose: not only do they function as the (concatenated) primary key for order_items, they also individually serve as foreign keys to the order table and items table respectively.

Figure J.1 documents this fact, and shows our completed ERD:

Figure J.1: