DRAFT: This module has unpublished changes.

Amber Carrier
Database Design Documents

Application Overview

 

                Yankee Courier is a small delivery company ran independently by one driver.  The driver receives work from a dispatching company and is responsible for keeping track of delivery records.  This application is designed for the driver to keep their business efficient and organized.  It is important to keep track of deliveries, clients, expenses, and income.  This application will allow Yankee Courier to easily manage all information relating to their business by enabling the user to add and update records relating to deliveries, clients, income and expenses.  The application will also allow the user to seach relating to the entities.  Furthermore, it report on deliveries, clients, expenses, and income.

 

Mission Statement

                The purpose of the Yankee Courier’s database is to provide a clear organized way to store and display information relating to deliveries, clients, expenses, and income.

 

Mission Objectives

To maintain (add, update) data on deliveries

To maintain (add, update) data on clients

To maintain (add, update) data on expenses

To maintain (add, update) data on income

To perform searches on deliveries

To perform searches on clients

To perform searches on expenses

To perform searches on income

To track the status of deliveries

To track the status of expenses

To track the status of income

To report on deliveries

To report on clients

To report on expenses

To report on income

 

Database Scope

                In the case of Yankee Courier’s Database, the purpose is to maintain data for the driver.  The driver is paid by their dispatcher.  It will not handle operations such as billing clients.

 

Requirements Specification

Deliveries

Deliveries are made to all different types of places.  Data relevant to deliveries includes date, job number, client number, pick up time, destination, drop off time, mileage and status.

Clients

Many different companies, hospitals, and pharmacies use Yankee Courier Services.  Data being stored on clients include client number, company name, street, city, state, zip, phone number, email addresses.

Expenses

Vehicle maintenance and gas are two very importance expenses.  Although any type of expense will be recorded with the expenses.  Information pertaining to expenses includes date, type, description, and amount.

Income

Each job total must be recorded as income.  Income information includes date, job number, and total.

 

Transaction Requirements

Data Entry

Add the details of a new delivery

Add the details of a new client

Add the details of a new expense

Add the details of income

Data Update/Deletion

Update the details of a client

Update the details of a delivery

Update the details of an expense

Update the details of income

Data Queries

List all expenses from a specified date

Look up a specific delivery

Display all jobs relating to a client

Calculate profit

 

Data Dictionary

Entity

Description

Aliases

Occurrence

Delivery

Term used for each job when goods are transported.

Job, run

Each delivery is unique.  Primary key is job number.

Client

Term used to describe people or companies who use Yankee Courier to deliver their package.

Sender

Each client has used Yankee Courier for at least one delivery.

Expenses

Term use to describe any related business expense

 

Each expense occurs on a date relating to a job. Unless the expense is for vehicle maintenance.

Income

the monetary payment received for services

Payment

Each amount of income relates to a specific delivery.

 

 

 

E-R Diagram

*See E-R Diagram Page

 

Relational Model

1.       Derivation of Relations from ERD

Delivery (JobNum text, Date date/time, ClientNum text, PickUpTime date/time, Destination text, DropOffTime date/time, Mileage number)

 

Client (ClientNum text, CompanyName text, Street text, City text, State text, Zip text, Phone number) 

 

Expense (Date date, Description text, Type text, Amount decimal)

 

Income (JobNum text, Date date, Total decimal) 

 

2.       Identification of Functional Dependencies

Delivery Schema:

JobNum --> Date, ClientNum,  PickUpTime, Destination, DropOffTime, Mileage

 

Client Schema:

ClientNum --> CompanyName, Street, City, State, Zip, Phone

CompanyName --> Street, City, State, Zip, Phone

 

Expense Schema:

Date, Description --> Type, Amount

 

Income Schema:

JobNum --> Total

 

 

Discussion of Normalization Process

1.The expense schema does not have a proper primary key.  I will normalize the relation by adding an expense number.

 

2.The income schema should not have the same primary key as the delivery table.  I will fix the issue by adding an assigned income number.

 

4.       Normalized Relations

Delivery (JobNum text, Date date/time, ClientNum text, PickUpTime date/time, Destination text, DropOffTime date/time, Mileage number)

 

Client (ClientNum text, CompanyName text, Street text, City text, State text, Zip text, Phone number) 

 

Expense (ExpenseNum text, Date date, Description text, Type text, Amount decimal)

 

Income (IncomeNum text, JobNum text, Date date, Total decimal) 

 

SQL Queries

Query Name: Invoice

SELECT JobNum, Date, ClientNum, Destination, Mileage, Income.Total
FROM Delivery, Income
WHERE Delivery.JobNum = Income.JobNum
SORT BY Date;

DRAFT: This module has unpublished changes.