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;