Project 1
Answers:
PROJECT 1-
Customers can have multiple orders and a shopping cart.
Sellers can add multiple products.
Products can belong to multiple categories, have multiple tags, and be associated with a seller.
Categories can have subcategories.
Orders have items, and each order has shipping details and billing information.
Shipping details and billing information are associated with orders.
TASK 1-
SQL File for Table Structure (create_tables.sql):
-- Create the Product table
CREATE TABLE Product (
ProductID INT PRIMARY KEY,
Name VARCHAR(255),
Color VARCHAR(50),
SellerID INT,
SizesAvailable VARCHAR(255),
QuantityAvailable INT,
Cost DECIMAL(10, 2),
CurrentDiscount DECIMAL(5, 2),
PlaceOfOrigin VARCHAR(100),
IsFeatured BOOLEAN,
PopularityIndex INT,
AvgRatings DECIMAL(3, 2),
TotalRatings INT
);
-- Create the Coupon table
CREATE TABLE Coupon (
CouponID INT PRIMARY KEY,
CouponCode VARCHAR(20),
DiscountRule VARCHAR(255),
Percentage DECIMAL(5, 2)
);
-- Create the User table (Customer and Seller combined)
CREATE TABLE User (
UserID INT PRIMARY KEY,
UserType VARCHAR(10), -- 'Customer' or 'Seller'
Gender VARCHAR(10),
DateOfBirth DATE,
City VARCHAR(50),
Password VARCHAR(255),
Name VARCHAR(100),
Email VARCHAR(100)
);
-- Create the Order table
CREATE TABLE Order (
OrderID INT PRIMARY KEY,
UserID INT,
OrderDate DATE,
Status VARCHAR(20)
);
-- Create the OrderItem table
CREATE TABLE OrderItem (
OrderItemID INT PRIMARY KEY,
OrderID INT,
ProductID INT
);
-- Create the ShippingDetails table
CREATE TABLE ShippingDetails (
ShippingID INT PRIMARY KEY,
OrderID INT,
Address VARCHAR(255),
ShippingStatus VARCHAR(20)
);
-- Create the BillingInformation table
CREATE TABLE BillingInformation (
BillingID INT PRIMARY KEY,
OrderID INT,
TotalAmount DECIMAL(10, 2),
PaymentStatus VARCHAR(20)
);
-- Create the Wishlist table
CREATE TABLE Wishlist (
WishlistID INT PRIMARY KEY,
UserID INT
);
-- Create the Replacement table
CREATE TABLE Replacement (
ReplacementID INT PRIMARY KEY,
OrderID INT,
Status VARCHAR(20)
);
-- Create the ProductReview table
CREATE TABLE ProductReview (
ReviewID INT PRIMARY KEY,
ProductID INT,
UserID INT,
Rating INT,
ReviewText TEXT,
ReviewDate DATE
);
-- Create the ProductCategory table
CREATE TABLE ProductCategory (
ProductID INT,
CategoryID INT
);
-- Create the ProductBrand table
CREATE TABLE ProductBrand (
ProductID INT,
Brand VARCHAR(50)
);
-- Create the ProductTag table
CREATE TABLE ProductTag (
ProductID INT,
Tag VARCHAR(50)
);
-- Create the ProductImage table
CREATE TABLE ProductImage (
ProductID INT,
ImageURL VARCHAR(255)
);
-- Create the ProductQA table
CREATE TABLE ProductQA (
QAID INT PRIMARY KEY,
ProductID INT,
Question TEXT,
Answer TEXT
);
-- Create the RelatedProducts table
CREATE TABLE RelatedProducts (
ProductID INT,
RelatedProductID INT
);
SQL File for Inserting Sample Data (insert_data.sql):
-- Insert sample data into the Product table
INSERT INTO Product (ProductID, Name, Color, SellerID, SizesAvailable, QuantityAvailable, Cost, CurrentDiscount, PlaceOfOrigin, IsFeatured, PopularityIndex, AvgRatings, TotalRatings)
VALUES (1, 'Product1', 'Red', 101, 'S,M,L', 100, 49.99, 'USA', TRUE, 100, 4.5, 50);
-- Insert sample data into the Coupon table
INSERT INTO Coupon (CouponID, CouponCode, DiscountRule, Percentage)
VALUES (1, 'SAVE10', '10% off on all products', 10.00);
-- Insert sample data into the User table
INSERT INTO User (UserID, UserType, Gender, DateOfBirth, City, Password, Name, Email)
VALUES (101, 'Customer', 'Male', '1990-01-01', 'New York', 'password123', 'John Doe', '[email protected]');
-- Insert sample data into the Order table
INSERT INTO Order (OrderID, UserID, OrderDate, Status)
VALUES (1, 101, '2023-01-15', 'Shipped');
-- Insert sample data into the OrderItem table
INSERT INTO OrderItem (OrderItemID, OrderID, ProductID)
VALUES (1, 1, 1);
-- Insert sample data into the ShippingDetails table
INSERT INTO ShippingDetails (ShippingID, OrderID, Address, ShippingStatus)
VALUES (1, 1, '123 Main St, New York', 'Delivered');
-- Insert sample data into the BillingInformation table
INSERT INTO BillingInformation (BillingID, OrderID, TotalAmount, PaymentStatus)
VALUES (1, 1, 49.99, 'Paid');
-- Insert sample data into the Wishlist table
INSERT INTO Wishlist (WishlistID, UserID)
VALUES (1, 101);
-- Insert sample data into the Replacement table
INSERT INTO Replacement (ReplacementID, OrderID, Status)
VALUES (1, 1, 'Approved');
-- Insert sample data into the ProductReview table
INSERT INTO ProductReview (ReviewID, ProductID, UserID, Rating, ReviewText, ReviewDate)
VALUES (1, 1, 101, 5, 'Great product!', '2023-01-20');
-- Insert sample data into the ProductCategory table
INSERT INTO ProductCategory (ProductID, CategoryID)
VALUES (1, 1);
-- Insert sample data into the ProductBrand table
INSERT INTO ProductBrand (ProductID, Brand)
VALUES (1, 'BrandX');
-- Insert sample data into the ProductTag table
INSERT INTO ProductTag (ProductID, Tag)
VALUES (1, 'Sale');
-- Insert sample data into the ProductImage table
INSERT INTO ProductImage (ProductID, ImageURL)
VALUES (1, 'image1.jpg');
-- Insert sample data into the ProductQA table
INSERT INTO ProductQA (QAID, ProductID, Question, Answer)
VALUES (1, 1, 'Can I return this product?', 'Yes, within 30 days.');
-- Insert sample data into the RelatedProducts table
INSERT INTO RelatedProducts (ProductID, RelatedProductID)
VALUES (1, 2);
TASK 3-
Fetch Customer Details in the Order of Their City:
SELECT * FROM Customer
ORDER BY City;
2. Alter the Product Table by Renaming Columns:
-- Assuming the Product table columns are named as in the example:
-- ProductID, Name, Color, SellerID, SizesAvailable, QuantityAvailable, Cost, CurrentDiscount, PlaceOfOrigin, IsFeatured, PopularityIndex, AvgRatings, TotalRatings
-- Rename columns by adding 'prod_' prefix to each column name
ALTER TABLE Product
RENAME COLUMN ProductID TO prod_ProductID,
COLUMN Name TO prod_Name,
COLUMN Color TO prod_Color,
COLUMN SellerID TO prod_SellerID,
COLUMN SizesAvailable TO prod_SizesAvailable,
COLUMN QuantityAvailable TO prod_QuantityAvailable,
COLUMN Cost TO prod_Cost,
COLUMN CurrentDiscount TO prod_CurrentDiscount,
COLUMN PlaceOfOrigin TO prod_PlaceOfOrigin,
COLUMN IsFeatured TO prod_IsFeatured,
COLUMN PopularityIndex TO prod_PopularityIndex,
COLUMN AvgRatings TO prod_AvgRatings,
COLUMN TotalRatings TO prod_TotalRatings;
3. Fetch Customer Details Who Have Purchased At Least One Product:
SELECT * FROM Customer
WHERE UserID IN (SELECT DISTINCT UserID FROM Order);
4. Fetch Customer Details Who Have Names Starting and Ending with 'A' and Purchased More Than One Item:
SELECT * FROM Customer
WHERE Name LIKE 'A%A' AND UserID IN (
SELECT UserID FROM (
SELECT UserID, COUNT(*) AS PurchaseCount
FROM OrderItem
GROUP BY UserID
HAVING PurchaseCount > 1
)
);
5. Fetch Information About Orders That Are Delivered, Cost More Than 10000, and Purchased by More Than 1 Customer:
SELECT O.OrderID, O.OrderDate, O.Status, O.UserID, BI.TotalAmount
FROM Order AS O
INNER JOIN BillingInformation AS BI ON O.OrderID = BI.OrderID
WHERE O.Status = 'Delivered'
AND BI.TotalAmount > 10000
AND O.OrderID IN (
SELECT OrderID
FROM OrderItem
GROUP BY OrderID
HAVING COUNT(DISTINCT UserID) > 1
);
6. Procedure to Run Table Creation, Insertion, and Alteration Statements:
DELIMITER //
CREATE PROCEDURE InitializeECommerceDatabase()
BEGIN
-- Table creation statements
-- Note: Include all the CREATE TABLE statements here.
-- Data insertion statements
-- Note: Include all the INSERT INTO statements here.
-- Table alteration statements
-- Note: Include all the ALTER TABLE statements here.
END;
//
DELIMITER ;