Database Design Project: E-commerce Platform Database Schema

Past papers and solved assignments for students. This project details an e-commerce database design.

1. Customer Table Query
Figure 1: Query for the customer table
In this diagram, a table is created for Customer details in which there are attributes created to
store the details. The values can’t be null for the attributes which are customer_id which
stores unique number/ id given to each customer so that they can be differentiated,
customer_name consists of the name of customer which can be same, customer_address
contains the address of the customer, customer_email is unique and the mail will be sent to
this email-id, customer_username contains the credentials for login purpose and Password
which would be entered to log in. In this table, customer_id and customer_email are primary
keys.
Execution of query
Figure 2: Screenshot of executing query 1
2. Category Table Query
Figure 3: Query for Category table
In this diagram, a table is created for Category details in which there are attributes created to
store the details of the type of product which are categories in two parts: Rent and Sales. The
values can’t be null for the attributes which are category_id and product_type. In this
category_id is the primary key and has unique values. The data types for both the tables are
varchar which accepts the length of characters till 45.
Execution of query
Figure 4: Screenshot of executing query 2

3. Product Table Query
Figure 5: Query for the Product table
In this diagram, a table is created for Product details in which there are attributes created to
store the details of the product. The values can’t be null for the attributes which are
product_id which contains the unique value & every product is assigned with different id,
product_name contains the name of the product & accepts the length of 45 characters & the
data type is varchar, product_quantity consist of a number of products to add at a time to cart
& the user can enter the value of 2 digits in it and this is of int data type, product_price
contains the price of the product which has the data type of int & accepts the value until 11
characters and the last attribute is category_id consist of type such as rent and sales.
Execution of query
Figure 6: Screenshot of executing query 3
4. Shopping Basket Table Query
Figure 7: Query for Shopping Basket table
In this diagram, a table is created for Shopping Basket details in which there are attributes
created to store the details for the shopping cart. There are attributes such as basket_id which
has unique values for every cart. The cart takes costumer_id and total_price to produce the
desired result. Total price is stored in total_price attribute.
Execution of query
Figure 8: Screenshot of executing query 4

5. Payment Table Query
Figure 9: Query for Payment table
In this diagram, a table is created for Payment details in which there are attributes created to
store the details for payment. There are attributes such as checkout_id which is used to
uniquely identify the order and car for a particular customer. When the verification is done
than a mail is sent to the customer through customer_email attribute. There is another
attribute which takes payment which is stored in payment_mode.
Execution of query
Figure 10: Screenshot of executing query 5
6. Shipping Details Table Query
Figure 11: Query for a shipping table
In this diagram, a table is created for shipping details in which there are attributes created to
store the details for shipment. In this attributes are shipping_id which is unique and is not
null, payment_id is used to accept the payment, shipping_address is used to add the address
details to which the shipment is to be done and shipping_date contains the expected delivery
date.
Execution of query
Figure 12: Screenshot of executing query 6