User requirements about the DBMS

• Each customer of the platform is identified by an id and has a unique username and a unique email address. Other information about the customer, including password, full name, address, and phone number, is also maintained.

• A customer may have one or more credit cards registered with the website. The credit card number and other related information of the credit card are kept.

• The platform may host many shops. Each shop has a name and a unique shop id. Each shop sells different projects. Every product has a name, color, size, price and description. It may also have one or several photos. A product belongs to a certain product type identified by a product type id. A product type has a description and all product types form a hierarchy - a product type may have one other product type as its parent. Some shops are restricted to sell a subset of types of products only.

• A customer can place an order. Each order is identified by an order id and the date of the order as well as a status (‘processing’, ‘completed’ or ‘cancelled’) is maintained.

• An order may contain one or more order items (products). For each order item (product), we keep the product unit price, the quantity of the product ordered as well as a status (‘processing’, ‘shipped’ or ‘out of stock’), and some other related information. Each order item may have a sequence number, which is unique within each order. Note that the order price can be different from the product price.

• An invoice is issued for every order. Each invoice carries an invoice number, a date and a status (‘issued’ or ‘paid’).

• Payment can be made for an invoice. The payment id, date and the amount are captured. Partial payment to an invoice can be made.

• Shipment will be made for items in a fully paid order. Items in an order may be shipped separately. The shipment id, items, date, and tracking number are recorded for each shipment.

Assumptions Made

• Multiple quantities of same product must be supplied by the same company
• When customer orders multiple quantity of a particular item, we only ship all of them out when full payment is made. Partial shipment is not allowed
• Multiple shipments may be required when multiple distinct items are involved in the transaction
• Photos are stored in the form of their html link (e.g. img src=”img/product1.jpg” )
• No Cash on Delivery option available for shipping, only payment by CC is allowed

ER Diagram


Shipment Company ERD Diagram

Relational Schema

• CUSTOMER(CustomerID, Name, Address, Email, PhoneNumber, Username, Password)
• CREDIT_CARD(CCNumber, Expiry, CustomerID)
• SHOP(ShopID, Name)
• PRODUCT_TYPE(ProductTypeID, Description, ParentID)
• PRODUCT(ProductID, ProductTypeID, ShopID, Name, Color, Size, Price, Description)
• RESTRICTED_SHOP(ShopID, ProductTypeID)
• PHOTO(PhotoID, PhotoLink, ProductID)
• ORDERS(OrderID, Status, Date, CustomerID)
• INVOICE(InvoiceID, Date, OrderID)
• FULLY_PAID(InvoiceID)
• PARTIALLY_PAID(InvoiceID, PaidAmount)
• SHIPMENT(ShipmentID, TrackingNo, Date, InvoiceNo)
• PAYMENT(PaymentID, Date, Amount, CCNumber, InvoiceNumber)
• ORDERED_PRODUCT(OrderID, SequenceNo, UnitPrice, Status, Quantity, ProductID, ShipmentID)
For full implementation code, please contact me directly.


Back to Projects