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 tracking number that uniquely identifies each package, 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, 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 expected delivery time (when the package arrives its destination). You should also record the order status of a package (i.e., either pending, in progress (shipped), or completed).
Shipping vendors: There are shipping vendors (or agents)
like UPS,
FedEX, or DHL. Each vendor is associated with several of your
warehouses (see inventory) Each vendor also
has a url and a
toll-free number.
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 number of items per package, selected cities of
destination, etc. You can assume that the shipping options of a
particular vendor are independent of the warehouse of origin.
Note: A Shipping option is different from a shipping method. Here a
shipping option simply means "standard, next day, overnight,
teleportation, warp portal, missile" etc. A shipping method here is an
entity composed of shipping option, shipping vendor providing it,
pricing option, restrictions... etc. e.g. There may several different
"next day" shipping methods with different pricing options and
restrictions provided by different vendors.
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.