You will begin your project by designing a schema for the database you will be creating and using for the project. Your schema should have at least four tables, but can be much larger. Table size (# of columns...) can vary. There can be foreign keys between columns in different tables. Each column must have a datatype (I mean SQL datatypes here, e.g. VARCHAR) and declared to be either a key or not a key. Choose imaginative and meaningful column and table names.
The following is a description of what the database should store. Certain aspects of the domain will be mandatory. In addition, you are expected to invent a few other attributes of interest to model in your database. The description of the domain will be given informally, and you will need to prepare a database schema that models the domain.
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 that uniquely identifies each package 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.