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
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)
• 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