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.