The Shipping Domain

This domain pertains to the shipping and delivery of the items that have been purchased by customers. Customers can choose from many shipping methods (e.g., Next Day, Standard Ground). The options might be priced differently and offered by different shipping agents (e.g., UPS, FedEx). The shipping and delivery database should also keep an accurate record of the status of the orders.

The following are the mandatory aspects of the domain that you need to model

Your schema should model the various shipping options that are available, and enable tracking of the progress of any order.

Package: There has to be a record of each package that has been shipped. Each package will have the number of the invoice that initiated the order. The weight of the package, the number of items in the package, the method of shipping, the tracking number and name of the shipping company need to be recorded. There can be special delivery instructions (e.g., whether the signature is waived, directions to the house). The source warehouse of the package and the delivery name, address, and contact should be recorded. You should also record pick-up and delivery times. You should also record the order status of a package (i.e., either pending, in progress, or completed).

Shipping vendors: There are shipping vendors (or agents) like UPS, FedEX, or DHL. Each agent has a local address. Note that there are multiple warehouses and so there can be different local agents associated with different warehouses. Each vendor also has a url and a toll-free number. The agents might have a scheduled daily pickup time associated with each warehouse.

Shipping Methods: Different vendors provide different shipping options such as Standard, Next Day, or Overnight. Each of these options are priced differently. The unit of pricing can be by weight, by number of items or just fixed rate. The price per unit might also vary substantially. Each shipping method has a guaranteed time to delivery. In addition, of course, there might be restrictions with each method, such as maximum weight or dimensions of package, selected cities of destination, etc. You can assume that the shipping options of a particular vendor are independent of the warehouse of origin.

Optional modeling: In addition to the mandatory aspects of the domain, you should add about 4 attributes of your own. You are welcome to visit any of your favorite online stores for ideas on additional attributes to model in your application.

Queries You Should Support

The interface to the system should be through a web page (see the project support page for details on how to do that). For each of the queries below, you should have a form that implements it. The layout is up to you, i.e., you can have all the forms to be on the same page, or spread out any way you like.

Display all packages in the database (with some details on each).
Given an package ID, display all the information about the package. (Note, you can implement this by adding a link from the answer to the previous query to the package page).
Display packages that are due to arrive today.
Given an integer, find all the packages that weigh at least that number.
For reach shipping vendor, return the number of packages they currently have on route, and those that are already completed.
Given a package weight and number of items in the package, find the shipping options for that package (ignoring the warehouse of origin).
Given a location of a warehouse and a package weight and number of items in the package, find the cheapest shipping method for that package.