CSCI 343 Assignment 4 Example Solution
List of normalized tables:
Customers(customer#, name, address, city, state, zip)
Locations(location#, name/office#, address, city, state, zip, zone)
Packages(packageID, barCode, order#, orderItem#,
destinationLocation#, description, value, COD, wt, cost, size,
cubicFt)
Order(order#, customer#, date)
Plane(plane#, type, cargoSize, maxContainer, CargoWt)
Shipping(Shipping#, zone, mgrFax, date, time, plane#)
ShippingItem(Shipping#, packageID, loadTime, comments)
Employee(employeeID, lastName, firstName, hireDate, releaseDate, phone,
office, mgr, equipmentID)
Equipment(equipmentID, description, datePurchased)
EmployeeHistory(employeeID, position, date, salary, location#)
Delivery(date, time, packageID, driverEmployeeID, customerSig,
location#, route#)
Assumptions
- Pickup locations and distination locations can be the same. The
pickup locations in the order form are locations of the
shipping company branch offices and pickup sites.
- BoxID/code, bar code and package ID are all identifiers assigned
to each package.
- Cargo size, max container and cargo wt are properties of a plane.
- In the plane loading form, size, wt, distination zone, and load
time are properties of a package.
- A package can be both shipped and delivered more than once, but it
can only be placed on an order once.
- Each employee is assigned only one piece of equipment.