Project 1

Ans.

Table: Coupon

• coupon_id (primary key)
• coupon_code
• discount_rule (e.g., percentage or fixed amount)
• discount_percentage (e.g., 10% off)
• start_date
• end_date

Table: OrderShipment

• shipment_id (primary key)
• order_id (foreign key to Order table)
• shipment_date
• shipment_status
• shipment_location
• carrier

Table: Invoice

• invoice_id (primary key)
• order_id (foreign key to Order table)
• invoice_date
• total_amount

Table: Wishlist

• wishlist_id (primary key)
• customer_id (foreign key to Customer table)
• product_id (foreign key to Product table)
• added_date

Table: Replacement

• replacement_id (primary key)
• order_id (foreign key to Order table)
• product_id (foreign key to Product table)
• replacement_status (e.g., requested, approved, declined)

Table: Review

• review_id (primary key)
• product_id (foreign key to Product table)
• customer_id (foreign key to Customer table)
• rating
• review_text
• review_date
• review_country

Table: Product

• product_id (primary key)
• product_name
• color
• seller_id (foreign key to Seller table)
• cost
• current_discount
• origin
• is_featured
• popularity_index
• avg_rating
• total_ratings
• description
• is_on_sale

Table: ProductSize

• product_id (foreign key to Product table)
• size

Table: ProductQuantity

• product_id (foreign key to Product table)
• size
• quantity

Table: ProductCategory

• product_id (foreign key to Product table)
• category_id (foreign key to Category table)

Table: ProductBrand

• product_id (foreign key to Product table)
• brand_id (foreign key to Brand table)

Table: ProductTag

• product_id (foreign key to Product table)
• tag_id (foreign key to Tag table)

Table: ProductImage

• product_id (foreign key to Product table)
• image_url

Table: ProductQuestion

• question_id (primary key)
• product_id (foreign key to Product table)
• question_text

Table: ProductAnswer

• answer_id (primary key)
• question_id (foreign key to ProductQuestion table)
• answer_text

Table: Customer

• customer_id (primary key)
• first_name
• last_name
• email
• phone_number
• gender
• dob
• city
• password
• payment_info_saved (e.g., credit card information)

Table: CustomerAddress

• address_id (primary key)
• customer_id (foreign key to Customer table)
• address
• city
• state
• zip_code
• is_default (flag indicating if it is the default address)

Table: CustomerContact

• contact_id (primary key)
• customer_id (foreign key to Customer table)
• phone_number
• email

Table: Order

• order_id (primary key)
• customer_id (foreign key to Customer table)
• order_date
• total_price
• payment_status
• shipping_status
• billing_information
• delivery_expectation
• delivery_date
• order_status

Table: OrderItem

• order_id (foreign key to Order table)
• product_id (foreign key to Product table)
• quantity

Table: Seller

• seller_id (primary key)
• seller_name
• mobile_number
• password

SQL Queries :

1. Creating the Customer table
   

   CREATE TABLE Customer (
       customer_id INT PRIMARY KEY AUTO_INCREMENT,
       first_name VARCHAR(50) NOT NULL,
       last_name VARCHAR(50) NOT NULL,
       email VARCHAR(100) UNIQUE NOT NULL,
       phone_number VARCHAR(20) NOT NULL,
       gender CHAR(1),
       dob DATE,
       city VARCHAR(50),
       password VARCHAR(100) NOT NULL,
       member_type ENUM('normal', 'premium') NOT NULL DEFAULT 'normal'
   );
   ​

2. Customer_Address
   

   CREATE TABLE CustomerAddress (
       address_id PRIMARY KEY INT AUTO-INCREMENT,
       FOREIGN KEY (customer_ID) REFERENCES Customer(Customer_iID),
       FOREIGN KEY (area) REFERENCES CustomerAreaInfo(area)
   );
   ​
   ​

   
   
   
3. Customer_Area_Info
   

   CREATE TABLE CustomerAreaInfo (
       area PRIMARY KEY INT AUTO-INCREMENT,
       isDefault BOOLEAN,
       door_no VARCHAR(4) NOT NULL,
       street VARCHAR(100) NOT NULL,
       city VARCHAR(100) NOT NULL,
       country VARCHAR(100) NOT NULL
   );
   ​
   

4. Customer_contact
   

   CREATE TABLE CustomerContact(
       contact_ID PRIMARY KEY INT AUTO_INCREMENT,
       FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID),
       phone_number INT(11),
       email VARCHAR(255)
   )​

5. Admin
   

   CREATE TABLE Admin(
       admin_ID PRIMARY KEY INT AUTO_INCREMENT,
       name VARCHAR(255),
       password VARCHAR(255)
   )​

6. Admin_Email
   

   CREATE TABLE AdminContact(
       contact_ID PRIMARY KEY INT AUTO_INCREMENT,
       FOREIGN KEY (admin_ID) REFERENCES Admin(admin_ID),
       email VARCHAR(255) NOT NULL,
       phone_number INT(11) NOT NULL
   )​

7. Seller
   

   CREATE TABLE Seller (
       seller_id INT PRIMARY KEY AUTO_INCREMENT,
       name VARCHAR(100) NOT NULL,
   );
   ​

8. Seller_Email
   

   CREATE TABLE Seller (
       FOREIGN KEY (seller_id) REFERENCES Seller(Seller_ID),
       contact_ID PRIMARY KEY INT NOT NULL,
       email VARCHAR(255) NOT NULL,
       phone_number INT(11) NOT NULL
   );
   ​​

   
   
   
9. Seller_location
   

   CREATE TABLE SellerLocation (
       FOREIGN KEY (seller_id) REFERENCES Seller(Seller_ID) ,
       location VARCHAR(255) NOT NULL,
   );
   ​​​

   
   
   
10. Creating Product table
    

    CREATE TABLE Product (
      product_id INT PRIMARY KEY,
      name VARCHAR(255),
      color VARCHAR(255),
      cost DECIMAL(10,2),
      current_discount DECIMAL(4,2),
      product_origin VARCHAR(255),
      is_featured BOOLEAN,
      popularity_index INT,
      avg_ratings DECIMAL(3,2),
      total_ratings INT,
      description VARCHAR(255),
      is_on_sale BOOLEAN,
      category_id INT,
      brand_id INT,
      FOREIGN KEY (seller_id) REFERENCES Sellers (seller_id),
      FOREIGN KEY (category_id) REFERENCES Category (category_id),
      FOREIGN KEY (brand_id) REFERENCES Brand (brand_id)
    );
    

11. Creating Category table
    

    CREATE TABLE Category (
        category_id INT PRIMARY KEY AUTO_INCREMENT,
        category_name VARCHAR(50) NOT NULL,
        parent_id INT,
        FOREIGN KEY (parent_id) REFERENCES Category(category_id)
    );
    ​

12. Product_Category table
    

    CREATE TABLE ProductCategory (
        product_id INT NOT NULL,
        category_id INT NOT NULL,
        PRIMARY KEY (product_id, category_id),
        FOREIGN KEY (product_id) REFERENCES Product(product_id),
        FOREIGN KEY (category_id) REFERENCES Category(category_id)
    );
    ​

13. Brand
    

    CREATE TABLE Brand (
        brand_id INT PRIMARY KEY AUTO_INCREMENT,
        brand_name VARCHAR(50) NOT NULL
    );
    ​

14. Product_Brand
    

    CREATE TABLE ProductBrand (
        product_id INT NOT NULL,
        brand_id INT NOT NULL,
        PRIMARY KEY (product_id, brand_id),
        FOREIGN KEY (product_id) REFERENCES Product(product_id),
        FOREIGN KEY (brand_id) REFERENCES Brand(brand_id)
    );
    ​

15. Tag
    

    CREATE TABLE Tag (
        tag_id INT PRIMARY KEY AUTO_INCREMENT,
        tag_name VARCHAR(50) NOT NULL
    );
    ​

16. Product_Tag
    

    CREATE TABLE ProductTag (
        product_id INT NOT NULL,
        tag_id INT NOT NULL,
        PRIMARY KEY (product_id, tag_id),
        FOREIGN KEY (product_id) REFERENCES Product(product_id),
        FOREIGN KEY (tag_id) REFERENCES Tag(tag_id)
    );
    ​

17. Order
    

    CREATE TABLE Order (
        order_id INT PRIMARY KEY AUTO_INCREMENT,
        customer_id INT NOT NULL,
        order_date DATE NOT NULL,
        total_price DECIMAL(10, 2) NOT NULL,
        payment_status ENUM('pending', 'paid', 'cancelled') NOT NULL DEFAULT 'pending',
        shipping_status ENUM('ordered', 'packed', 'shipped', 'delivered', 'cancelled') NOT NULL DEFAULT 'ordered',
    ​

18. Order_items
    

    CREATE TABLE OrderItems(
        FOREIGN KEY (order_ID) REFERENCES Order(order_ID)
        FOREIGN KEY (PRODUCT_ID) REFERENCES Product(Product_ID),
        item_cost INT (6),
        item_quantity iNT (6),
        item_availability BOOLEAN
    
    )​

19. Wishlist
    

    CREATE TABLE Wishlist (
        wishlist_id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(255),
        description VARCHAR (255),
        added_date DATE
    );
    ​

20. Wishlist_items
    

    CREATE TABLE WishlistItems (
        FOREIGN KEY (wishlist_ID) REFERENCES Wishlist(wishlist_ID),
        FOREIGN KEY (customer_ID) REFERENCES Customers(customer_ID),
        FOREIGN KEY (product_ID) REFERENCES Products(product_ID)
    )​

21. Product_Replacement
    

    CREATE TABLE Product_Replacements (
        replacement_id INT PRIMARY KEY AUTO_INCREMENT,
        order_id INT NOT NULL,
        product_id INT NOT NULL,
        status VARCHAR(20) NOT NULL,
        FOREIGN KEY (order_id) REFERENCES Orders(order_id),
        FOREIGN KEY (product_id) REFERENCES Products(product_id)
    );
    ​

22. Product_Reviews
    

    CREATE TABLE Product_Reviews (
        review_id INT PRIMARY KEY AUTO_INCREMENT,
        product_id INT NOT NULL,
        customer_id INT NOT NULL,
        rating INT NOT NULL,
        review_text VARCHAR(500) NOT NULL,
        review_date DATE NOT NULL,
        FOREIGN KEY (product_id) REFERENCES Products(product_id),
        FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
    );
    ​

23. Coupon
    

    CREATE TABLE Coupon(
        coupon_ID PRIMARY KEY INT NOT NULL,
        discount_rule VARCHAR(255),
        discount_percentage INT(3),
        start_date DATE,
        end_date DATE
    )​

24. Order shipment
    

    CREATE TABLE OrderShipment (
      shipment_id INT PRIMARY KEY,
      order_id INT,
      shipment_date DATE,
      shipment_method VARCHAR(255),
      shipment_tracking_no VARCHAR(255),
      shipment_status VARCHAR(255),
      FOREIGN KEY (order_id) REFERENCES Orders (order_id)
    );
    ​

    
    
    
25. Payment
    

    CREATE TABLE Payment (
        payment_ID PRIMARY KEY NOT NULL INT,
        status VARCHAR(255),
    )​

26. Payment_method
    

    CREATE TABLE PaymentMethod (
        FOREIGN KEY (payment_ID) REFERENCES Payment(payment_ID),
        payment_mode VARCHAR(255),
    )​​

    
    
    
27. Invoice
    

    CREATE TABLE Invoice (
      invoice_id INT PRIMARY KEY,
      order_id INT,
      invoice_date DATE,
      total_amount DECIMAL(10,2),
      payment_method VARCHAR(255),
      FOREIGN KEY (order_id) REFERENCES Orders (order_id)
    );
    ​

28. Replacement
    

    CREATE TABLE Replacement (
      replacement_id INT PRIMARY KEY,
      order_id INT,
      product_id INT,
      quantity INT,
      reason VARCHAR(255),
      status VARCHAR(255),
      FOREIGN KEY (order_id) REFERENCES Orders (order_id),
      FOREIGN KEY (product_id) REFERENCES Products (product_id)
    );
    ​

29. Review
    

    CREATE TABLE Review (
      review_id INT PRIMARY KEY,
      customer_id INT,
      product_id INT,
      rating INT,
      review_text VARCHAR(255),
      review_date DATE,
      FOREIGN KEY (customer_id) REFERENCES Customers (customer_id),
      FOREIGN KEY (product_id) REFERENCES Products (product_id)
    );
    ​

30. Product_Size
    

    CREATE TABLE ProductSize (
      product_id INT,
      size VARCHAR(10),
      PRIMARY KEY (product_id, size),
      FOREIGN KEY (product_id) REFERENCES Product (product_id)
    );
    ​

31. Product_Quantity
    

    CREATE TABLE ProductQuantity (
      product_id INT,
      size VARCHAR(10),
      quantity INT,
      PRIMARY KEY (product_id, size),
      FOREIGN KEY (product_id, size) REFERENCES ProductSize (product_id, size)
    );
    ​

32. Product_Image
    

    CREATE TABLE ProductImage (
      image_url VARCHAR(255),
      PRIMARY KEY (product_id, image_url),
      FOREIGN KEY (product_id) REFERENCES Product (product_id)
    );
    ​

33. Product_Question
    

    CREATE TABLE ProductQuestion (
      question_id INT PRIMARY KEY,
      question_text VARCHAR(255),
      FOREIGN KEY (product_id) REFERENCES Product (product_id)
    );
    ​

34. Product_Answer
    

    CREATE TABLE ProductAnswer (
      answer_id INT PRIMARY KEY,
      answer_text VARCHAR(255),
      FOREIGN KEY (question_id) REFERENCES ProductQuestion (question_id)
    );
    ​

    
    
    
35. Cart
    

    CREATE TABLE Cart (
        cart_ID PRIMARY KEY NOT NULL INT,
        total_cost INT (6),
        total_discount INT (3),
        taxes INT (3)
    )​

36. Cart_items
    

    CREATE TABLE CartItems (
        FOREIGN KEY (cart_ID) REFERENCES Cart(cart_ID),
        item_ID PRIMARY KEY NOT NULL INT,
        item_cost INT (6),
        item_quantity INT (6),
        item_availability BOOLEAN
    )​​

To optimize the database for efficient searching on name, categories, date of product, latest and brand name, we can create appropriate indexes on the relevant columns of the tables.

For example, we can create the following indexes:

1. For the Product table, we can create indexes on the 'name', 'date_added', and 'is_featured' columns.
   

   CREATE INDEX idx_product_name ON Product (name);
   CREATE INDEX idx_product_date_added ON Product (date_added);
   CREATE INDEX idx_product_is_featured ON Product (is_featured);
   ​

2. For the ProductCategory table, we can create indexes on the 'category_id' and 'product_id' columns.
   

   CREATE INDEX idx_product_category_category_id ON ProductCategory (category_id);
   CREATE INDEX idx_product_category_product_id ON ProductCategory (product_id);
   ​

3. For the ProductBrand table, we can create indexes on the 'brand_id' and 'product_id' columns.
   

   CREATE INDEX idx_product_brand_brand_id ON ProductBrand (brand_id);
   CREATE INDEX idx_product_brand_product_id ON ProductBrand (product_id);
   ​

   
   

   By creating these indexes, the database engine can use them to quickly locate the relevant rows for a given search query, thereby improving the performance of the search.

   For example, if we want to search for products with a particular name or brand, we can use the following SQL query:

   SELECT * FROM Product WHERE name LIKE '%keyword%' OR brand LIKE '%keyword%';
   

The database engine can use the 'idx_product_name' and 'idx_product_brand' indexes to quickly locate the relevant rows for the search, rather than having to scan the entire table.

Similarly, if we want to search for products based on their category or date added, we can use the following SQL queries:

SELECT p.* FROM Product p JOIN ProductCategory pc ON p.id = pc.product_id WHERE pc.category_id = category_id;
SELECT * FROM Product WHERE date_added >= 'start_date' AND date_added <= 'end_date';

In both cases, the database engine can use the appropriate indexes to quickly locate the relevant rows for the search, rather than having to scan the entire table.