Project 1
Q. Submit your updated ER and tables diagrams. You can use dbdiagram.io for this. Submit all SQL queries used to define your database and insert information in it.
==> ER diagram and tables are submitted with file attachment below.
All queries for define/creation ecommerce database and insertion information in it.
and below all database tables are attached in sql file format.
Customer Table :
CREATE TABLE `customer` (
`Customer_id` varchar(10) NOT NULL,
`Name` varchar(200) NOT NULL,
`email` varchar(100) NOT NULL,
`gender` varchar(50) NOT NULL,
`dob` datetime NOT NULL,
`city` varchar(100) NOT NULL,
`delivery_Address` varchar(500) NOT NULL,
`contact` int(11) NOT NULL,
`Current_order` varchar(100) NOT NULL,
`Prev_Order` varchar(100) NOT NULL,
`Cart_id` varchar(100) DEFAULT NULL
)
ALTER TABLE `customer`
ADD PRIMARY KEY (`Customer_id`),
ADD KEY `Cart_id` (`Cart_id`);
ALTER TABLE `customer`
ADD CONSTRAINT `customer_ibfk_1` FOREIGN KEY (`Cart_id`) REFERENCES `cart` (`Cart_id`);
INSERT INTO `customer` (`Customer_id`, `Name`, `email`, `gender`, `dob`, `city`, `delivery_Address`, `contact`, `Current_order`, `Prev_Order`, `Cart_id`) VALUES
('0023pkr', 'Aishwarya', '[email protected]', 'female', '2003-11-14 15:23:33', 'mumbai', 'dadar', 888859944, 'Dress', 'lehanga', 'c_105'),
('B12kr', 'Abhi', '[email protected]', 'male', '1995-11-02 15:19:36', 'Bangalore', 'bangalore', 988849445, 'Headphone', 'Shirts', 'c_103'),
('G_235', 'Ganesh', '[email protected]', 'male', '2022-11-05 08:11:53', 'Pune', 'Hadapsar Pune', 456777653, 'T_Shirt', 'Cap', 'c_102'),
('Tj402', 'Vankatesh', 'vanki@[email protected]', 'male', '2002-11-21 15:19:36', 'Chennai', 'chennai', 78906483, 'Homethreater', 'sounds', 'c_104'),
('V_Patil', 'Vaibhav', '[email protected]', 'Male', '2000-11-12 12:41:53', 'Nanded', 'Latur', 845905668, 'Macbook', 'HP Laptop', 'c_101');
Cart Table :
CREATE TABLE `cart` (
`Cart_id` varchar(100) NOT NULL,
`Item_quantity` int(11) NOT NULL,
`Total_cost` int(11) NOT NULL
);
ALTER TABLE `cart`
ADD PRIMARY KEY (`Cart_id`);
INSERT INTO `cart` (`Cart_id`, `Item_quantity`, `Total_cost`) VALUES
('c_101', 20, 3000),
('c_102', 10, 1200),
('c_103', 2, 200),
('c_104', 5, 2000),
('c_105', 10, 70000);
Cart_Item Table :
CREATE TABLE `cart_item` (
`Cart_id` varchar(100) NOT NULL,
`Product_id` varchar(100) NOT NULL,
`Date_Added` date NOT NULL,
`purchased` varchar(3) DEFAULT 'NO',
`item_quantity` int(11) DEFAULT NULL
);
ALTER TABLE `cart_item`
ADD PRIMARY KEY (`Cart_id`,`Product_id`),
ADD KEY `Product_id` (`Product_id`);
ALTER TABLE `cart_item`
ADD CONSTRAINT `cart_item_ibfk_1` FOREIGN KEY (`Cart_id`) REFERENCES `cart` (`Cart_id`),
ADD CONSTRAINT `cart_item_ibfk_2` FOREIGN KEY (`Product_id`) REFERENCES `product` (`Product_id`),
ADD CONSTRAINT `cart_item_ibfk_3` FOREIGN KEY (`Cart_id`) REFERENCES `cart` (`Cart_id`);
INSERT INTO `cart_item` (`Cart_id`, `Product_id`, `Date_Added`, `purchased`, `item_quantity`) VALUES
('c_101', 'P_3', '2022-11-07', 'NO', 7),
('c_102', 'P_1', '2022-11-22', 'y', 3),
('c_103', 'P_1', '2022-10-19', 'Y', 5),
('c_104', 'P_3', '2022-11-03', 'Y', 6),
('c_105', 'P_1', '2022-11-01', 'NO', 3);
Category Table :
CREATE TABLE `category` (
`category_id` varchar(100) NOT NULL,
`category_name` varchar(100) NOT NULL,
`Product_id` varchar(100) NOT NULL,
`subCategory` varchar(100) NOT NULL
);
ALTER TABLE `category`
ADD PRIMARY KEY (`category_id`),
ADD KEY `Product_id` (`Product_id`);
ALTER TABLE `category`
ADD CONSTRAINT `category_ibfk_1` FOREIGN KEY (`Product_id`) REFERENCES `product` (`Product_id`);
INSERT INTO `category` (`category_id`, `category_name`, `Product_id`, `subCategory`) VALUES
('cat_285', 'Accessories', 'P_1', 'Headphones'),
('cat_288', 'Laptops', 'P_1', 'Apple'),
('cat_291', 'Accessories', 'P_3', 'Phones'),
('cat_299', 'Phones', 'P_3', 'Vivo'),
('cat_301', 'Fashion', 'P_3', 'Mens');
Orders Table :
CREATE TABLE `orders` (
`order_id` varchar(100) NOT NULL,
`status` varchar(100) NOT NULL,
`order_date` date NOT NULL,
`delivery_date` date NOT NULL,
`customer_id` varchar(100) NOT NULL
);
ALTER TABLE `orders`
ADD PRIMARY KEY (`order_id`),
ADD KEY `customer_id` (`customer_id`);
ALTER TABLE `orders`
ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`Customer_id`);
INSERT INTO `orders` (`order_id`, `status`, `order_date`, `delivery_date`, `customer_id`) VALUES
('02341mrk', 'shipping', '2022-11-05', '2022-11-08', 'V_Patil'),
('09hjwe3', 'delivered', '2022-11-01', '2022-11-05', '0023pkr'),
('1567o3d', 'delivered', '2022-11-02', '2022-11-04', 'G_235'),
('klr892', 'dileverd soon', '2022-11-04', '2022-11-06', 'B12kr'),
('ormh26', 'shiiping', '2022-11-02', '2022-11-07', 'Tj402');
Payment Table :
CREATE TABLE `payment` (
`payment_id` varchar(100) NOT NULL,
`payment_date` date NOT NULL,
`Payment_type` varchar(200) NOT NULL,
`Customer_id` varchar(100) NOT NULL,
`Cart_id` varchar(100) NOT NULL
);
ALTER TABLE `payment`
ADD PRIMARY KEY (`payment_id`),
ADD KEY `Customer_id` (`Customer_id`),
ADD KEY `Cart_id` (`Cart_id`);
ALTER TABLE `payment`
ADD CONSTRAINT `payment_ibfk_1` FOREIGN KEY (`Customer_id`) REFERENCES `customer` (`Customer_id`),
ADD CONSTRAINT `payment_ibfk_2` FOREIGN KEY (`Cart_id`) REFERENCES `cart` (`Cart_id`);
INSERT INTO `payment` (`payment_id`, `payment_date`, `Payment_type`, `Customer_id`, `Cart_id`) VALUES
('0023449', '2022-11-03', 'reference-0394j34', 'B12kr', 'c_104'),
('0294738484', '2022-11-01', 'UPI', '0023pkr', 'c_102'),
('488443565', '2022-11-02', 'phonepay', 'G_235', 'c_103'),
('4930980', '2022-11-04', 'bharatPay', 'V_Patil', 'c_101'),
('9849344', '2022-11-04', 'upi', 'Tj402', 'c_104');
Product Table :
CREATE TABLE `product` (
`Product_id` varchar(50) NOT NULL,
`Name` varchar(200) NOT NULL,
`color` varchar(100) NOT NULL,
`seller_id` varchar(100) NOT NULL,
`size` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
`cost` int(11) NOT NULL,
`discount` int(11) NOT NULL,
`place_for_delivery` varchar(200) NOT NULL,
`isFeatured` varchar(10) NOT NULL,
`rating` int(11) NOT NULL,
`reviews` int(11) NOT NULL,
`isOnScale` varchar(100) NOT NULL,
`Category_id` varchar(100) NOT NULL,
`brand` varchar(100) NOT NULL,
`description` varchar(300) NOT NULL,
`tags` varchar(100) NOT NULL,
`related_product` varchar(300) NOT NULL
);
ALTER TABLE `product`
ADD PRIMARY KEY (`Product_id`),
ADD KEY `seller_id` (`seller_id`);
ALTER TABLE `product`
ADD CONSTRAINT `product_ibfk_1` FOREIGN KEY (`seller_id`) REFERENCES `seller` (`seller_id`);
INSERT INTO `product` (`Product_id`, `Name`, `color`, `seller_id`, `size`, `quantity`, `cost`, `discount`, `place_for_delivery`, `isFeatured`, `rating`, `reviews`, `isOnScale`, `Category_id`, `brand`, `description`, `tags`, `related_product`) VALUES
('Hag3943r', 'Rebion Goggals', 'red-black', 'S123_KR', 0, 1, 2000, 300, 'Latur', 'yes', 5, 4, 'yes', 'cat_291', 'Rebion', 'sunlight glasses', 'goggles', 'yellow glasses'),
('krt455', 'T_Shirt', 'Yellow', 'S111_MH', 42, 2, 3000, 200, 'Nanded', '', 4, 8, 'yes', 'Fashion', 'being human', 'T_shirt with cap', '', 'being humans jeans'),
('P_1', 'Macbook', 'black', 'S123_KR', 14, 1, 120000, 7000, 'pune', 'yes', 5, 40, 'very good product', 'C_301', 'Apple', 'Product is very branded and good', 'Laptop', 'IPad'),
('P_3', 'Vivo_30', 'silver', 'S111_MH', 20, 2, 40000, 2000, 'Latur', 'Welll feat', 7, 20, 'well developed', 'Phones', 'Vivo', 'Very good phone camera 64 ram 8 gb', 'vivo', 'vivo-28'),
('P_490g', 'headphone', 'white', 'S111_MH', 0, 3, 700, 110, 'pune', 'yes', 5, 5, 'yes', 'cat_285', 'realme', 'white headphones', 'realme', 'realme ipods');
Seller Table :
CREATE TABLE `seller` (
`seller_id` varchar(100) NOT NULL,
`Name` varchar(200) NOT NULL,
`Mobile_no` int(11) NOT NULL,
`password` varchar(200) NOT NULL,
`Product_Category` varchar(100) NOT NULL
);
ALTER TABLE `seller`
ADD PRIMARY KEY (`seller_id`);
INSERT INTO `seller` (`seller_id`, `Name`, `Mobile_no`, `password`, `Product_Category`) VALUES
('er455k', 'Jully', 59545554, 'li9345n', 'Accessories'),
('kjhkj54', 'Danni', 345665656, 'ggj54t02', 'Laptop'),
('S-66jd', 'Javed', 483595847, 'ghd3435j', 'fashion'),
('S111_MH', 'Abhi', 8549505, 'V4590nd', 'Accessories'),
('S123_KR', 'Vinay', 84564865, '90hf3e', 'Phones');
Some Queries :
If customer wants to see details of present in cart.
select * from product where product_id in( select product_id from Cart_item where (Cart_id in ( select Cart_id from Customer where Customer_id='cid100' )) and purchased='NO');
If customer want to see history of orders.
select product_id,item_quantity from Cart_item where (purchased='Y' and Cart_id in (select Cart_id from customer where Customer_id='V_Patil'));
If customer want to see total price fir product in cart.
select sum(item_quantity * cost) total_payable from product p join cart_item c on p.product_id=c.product_id where c.product_id in (select product_id from cart_item
where cart_id in(select Cart_id from customer where customer_id='V_Patil') );