Fashion Bags Website Database Design

Fashion Bags Website Database Design
Student’s Name:
Institutional Affiliation:
Course Name and Number:
Instructor’s Name:
Due:
Index Data Requirements. 3 ERD Diagram.. 4 Data Tables. 4 Customer Table. 4 Product Table. 5 Order Table. 6 CartItems Table. 7 Sales Table. 8 Shipping Table. 9 Returns Table. 10 Cart Table. 11 SQL Commands. 12 Customer Table. 12 Products Table. 12 Sales Table. 12 Cart Table. 13 CartItems Table. 13 Order Table. 14 Returns Table. 14 Shipping Table. 14  
Fashion Bags Website Database Design
Data Requirements
The design of the database for the ecommerce website should fulfill the following requirements:
* Customers: The database should include a table for customers that includes information such as their name, address, email address, phone number, and any other relevant contact information.
* Products: The database should include a table for products that includes information such as the product name, description, image, price, and any other relevant details.
* Orders: The database should include a table for orders that includes information such as the order number, customer name, order date, and any other relevant information related to the order.
* Sales: The database should include a table for sales that includes information such as the sale date, customer name, product name, and any other relevant information related to sales tracking.
* Shipping: The database should include a table for shipping that includes information such as the shipping address, shipping method, and any other relevant information related to shipping orders.
* Returns: The database should include a table for returns that includes information such as the return date, customer name, product name, and any other relevant information related to returns processing.
* Cart: The database should have a cart that contains information about the session that is related to ecommerce. The table will also include information to check out whether the cart was checked out or abandoned.
* CartItems: The table will contain the individual products in a cart, including their prices and quantity. The information will help to calculate the total price as well as match the correct customer with their items.
ERD Diagram
Data Tables
Customer Table
Attribute
Description
Constraints
Data Type & Size
Data Description
Customer_ID
Primary Key
Unique identifier for a customer
NOT NULL, UNIQUE
INT
Integer value that auto-increments with each new customer record
Customer_Name
Full name of the customer
NOT NULL
VARCHAR(50)
String of maximum 50 characters representing the customer's full name
Customer_Address
Full address of the customer
NOT NULL
VARCHAR(100)
String of maximum 100 characters representing the customer's full address
Customer_Email
Email address of the customer
NOT NULL, UNIQUE
VARCHAR(50)
String of maximum 50 characters representing the customer's email address
Customer_Phone
Phone number of the customer
NOT NULL
VARCHAR(20)
String of maximum 20 characters representing the customer's phone number
Product Table
Attribute
Description
Constraints
Data Type & Size
Data Description
Product_ID
Primary Key
Unique identifier for a product
NOT NULL, UNIQUE
INT
Integer value that auto-increments with each new product record
Product_Name
Name of the product
NOT NULL
VARCHAR(50)
String of maximum 50 characters representing the name of the product
Product_Description
Detailed description of the product
TEXT
Textual description of the product
Product_Image
Image of the product
BLOB
Binary Large Object representing the product image
Product_Price
Price of the product
NOT NULL, CHECK (Product_Price >= 0)
DECIMAL(10,2)
Decimal value with 2 decimal places representing the price of the product in a specific currency (assumed to be USD)
Product_Category
Category to which the product belongs
NOT NULL
VARCHAR(50)
String of maximum 50 characters representing the category to which the product belongs
Order Table
Attribute
Description
Constraints
Data Type & Size
Data Description
Order_ID
Primary Key
Unique identifier for an order
NOT NULL, UNIQUE, AUTO_INCREMENT
INT
Integer value that auto-increments with each new order record
Customer_ID
Foreign Key
Identifier for the customer who placed the order
NOT NULL
INT
Integer value referencing the primary key of the corresponding Customer record
Order_Date
Date and time when the order was placed
NOT NULL
DATETIME
Date and time in the format YYYY-MM-DD HH:MM:SS
Order_TotalAmount
Total amount charged for the order
NOT NULL, CHECK (Order_TotalAmount >= 0)
DECIMAL(10,2)
Decimal value with 2 decimal places representing the total amount charged for the order in a specific currency
CartItems Table
Attribute
Description
Constraints
Data Type & Size
Data Description
CartItems_ID
Primary Key
Unique identifier for a cart item
NOT NULL, UNIQUE, AUTO_INCREMENT
INT
Integer value that auto-increments with each new cart item record
Cart_ID
Foreign Key
Identifier for the cart that the item belongs to
NOT NULL
INT
Integer value referencing the primary key of the corresponding Cart record
Product_ID
Foreign Key Identifier for the product that the cart item represents
NOT NULL
INT