Project 1

Task 1;

CREATE DATABASE Project1;
USE Project1;

CREATE TABLE Customer(
customer_id INT,
firstName VARCHAR(255),
lastName VARCHAR(255),
email VARCHAR(255),
PASSWORD VARCHAR(255),
dateOfBirth DATE,
shippingAddress VARCHAR(255),
billingAddress VARCHAR(255),
TYPE VARCHAR(255),
PRIMARY KEY (customer_id)
);

INSERT INTO Customer(customer_id,customer_firstname,customer_lastname,customer_email,customer_password,customer_dateOfBirth,customer_shippingAddress,customer_billingAddress,customer_Type) VALUES (101,'James','Stewart','[email protected]','James',01-01-1990,'Banglore','Banglore','Ordinary');

INSERT INTO Customer(customer_id,customer_firstname,customer_lastname,customer_email,customer_password,customer_dateOfBirth,customer_shippingAddress,customer_billingAddress,customer_Type) VALUES (102,'Mary','Dsouza','[email protected]','Mary',04-07-1985,'Pune','Pune','VIP');

CREATE TABLE Seller(
seller_id INT,
firstName VARCHAR(255),
lastName VARCHAR(255),
email VARCHAR(255),
PASSWORD VARCHAR(255),
dateOfBirth DATE,
shippingAddress VARCHAR(255),
billingAddress VARCHAR(255),
PRIMARY KEY(seller_id)
);

INSERT INTO Seller(seller_id,seller_firstname,seller_lastname,seller_email,seller_password,seller_dateOfBirth,seller_shippingAddress,seller_billingAddress) VALUES (201,'Vijay','Andrew','[email protected]','vijay',25-10-1987,'Chennai','Chennai');

INSERT INTO Seller(seller_id,seller_firstname,seller_lastname,seller_email,seller_password,seller_dateOfBirth,seller_shippingAddress,seller_billingAddress) VALUES (202,'Pooja','Modi','[email protected]','Pooja',26-11-1985,'Guwahati','Guwahati');

CREATE TABLE Product(
product_id INT,
NAME VARCHAR(255),
price DECIMAL,
description TEXT,
seller_id INT,
PRIMARY KEY(product_id),
FOREIGN KEY (seller_id) REFERENCES Seller(seller_id)
);

CREATE TABLE Category(
category_id INT,
NAME VARCHAR(255),
description TEXT,
PRIMARY KEY(category_id)
);

CREATE TABLE Subcategory(
subcategory_id INT PRIMARY KEY,
category_id INT,
NAME VARCHAR(255),
description TEXT,
FOREIGN KEY(category_id) REFERENCES Category(category_id)
);

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

CREATE TABLE ProductSubcategory(
product_id INT,
subcategory_id INT,
FOREIGN KEY (product_id) REFERENCES Product(product_id),
FOREIGN KEY (subcategory_id) REFERENCES Subcategory(subcategory_id),
PRIMARY KEY (product_id, subcategory_id)
);

CREATE TABLE Brand(
brand_id INT,
NAME VARCHAR(255),
description TEXT,
PRIMARY KEY(brand_id)
);

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

CREATE TABLE Segment(
segment_id INT,
NAME VARCHAR(255),
description TEXT,
PRIMARY KEY(segment_id)
);

CREATE TABLE ProductSegment(
product_id INT,
segment_id INT,
FOREIGN KEY (product_id) REFERENCES Product(product_id),
FOREIGN KEY (segment_id) REFERENCES Segment(segment_id),
PRIMARY KEY (product_id, segment_id)
);

CREATE TABLE Tag(
tag_id INT,
NAME VARCHAR(255),
PRIMARY KEY(tag_id)
);

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

CREATE TABLE Cart(
cart_id INT,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
PRIMARY KEY (cart_id)
);

CREATE TABLE CartItem(
cartItem_id INT,
cart_id INT,
product_id INT,
quantity INT,
FOREIGN KEY (cart_id) REFERENCES Cart(cart_id),
FOREIGN KEY (product_id) REFERENCES Product(product_id),
PRIMARY KEY (cartItem_id)
);

CREATE TABLE Orders(
order_id INT,
customer_id INT,
shippingAddress VARCHAR(255),
billingAddress VARCHAR(255),
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
PRIMARY KEY (order_id)
);

CREATE TABLE OrderItem(
orderItem_id INT,
order_id INT,
product_id INT,
quantity INT,
FOREIGN KEY (order_id) REFERENCES Orders(order_id),
FOREIGN KEY (product_id) REFERENCES Product(product_id),
PRIMARY KEY (orderItem_id)
);

INSERT INTO OrderItem(orderItem_id,order_id,product_id,quantity) VALUES (1401,301,801,5);

INSERT INTO OrderItem(orderItem_id,order_id,product_id,quantity) VALUES (1402,302,802,7);