In this project, we shall build a simple database linked to an Online ordering system for a shoe manufacturer. The detailed BOM for the different types of shoes is described below.

The database must keep track of the product and part inventory levels, customer orders, purchase orders, and also the constraints that restrict designs of parts (as described in the BOM description below).

The raw materials for the shoe manufacture are provided by different suppliers. The company keeps records of all potential suppliers, including their company name, address, phone and fax numbers, and the materials they can supply.

A supply is requested by sending out a purchase order, which specifies the amount, the date of request, the expected delivery date, and the materials. We also note the date when the order was actually filled (shipment received date).

Customers can specify their selected shoe by specifying the shoe design over a web interface. They can then place the order for the designed shoe. The system gives quotations once the shoe is designed. Each order includes the quantity requested, and the delivery quantity and schedules (assume that customers are retail shops that may request monthly shipments of different shoe types.)

You must store the cost of each component that you purchase. Also, you must formulate the price for each shoe based on the component costs, and also the order quantity. Use this function to compute the order quotations.

The project involves three sets of tasks:

(a) Design the Database for the product (including constraints that specify which combinations are allowed, and which are disallowed for product design), suppliers, customers, and the operational data (purchase orders, orders).

(b) Design a web interface that allows customers to specify valid shoe designs, and make orders through the web.

(c) Design a web interface for the administration to perform routine checks on the database such as late orders, supplier lists, etc.

The Product BOM Description

We shall assume that each shoe can be made of the following components:

1. The shoe sole. This is made of vulcanized rubber. Rubber is purchased in barrels. It is processes at the plant, including the addition of coloring dyes (five colors are available: Red, Blue, green, White, Black).

After processing, it is molded to form the sole.

2. The upper. The upper is stitched using nylon and/or leather. The upper is composed of five outer pieces: front-piece, top-piece, left-piece, right-piece and back piece; these pieces are stitched together, and then a cotton cushioning cloth is stitched along its inside. The front and back-pieces must be of leather. The rest can be either nylon or leather, but the two side pieces must be of the same material. Further, the nylon comes in two colors: blue or white. The leather also can be one of (red, blue, white, black, brown), and comes in two quality levels (soft and tough).

3. The fastener system. This is one of (Laces, Velcro, or Zipper). Laces can be leather or nylon. If the left- and right-pieces of the upper are made of nylon, then brass holed-rivets are attached to them for shoes with laces.

4. The insert. This can be either plain, or cushioned.