Project 1
PROJECT-1
CREATE ENTITY RELATIONSHIP DIAGRAM FOR THE E-COMMERCE WEBSITE BASED ON THE REQUIRMENTS SPECIFICATIONS;
STRUCTURE OF TABLES ALONG WITH THE DATA INSERTED INTO IT:
( BASIC FLOW FOR SHORTCUTS: DROP TABLE, SELECT * FROM, DESCRIBE )
CREATE DATABASE project_1;
USE project_1;
Register table;
CREATE TABLE Register(
RegisterId INT,
NAME CHAR(20),
PhoneNumber INT,
city CHAR(10),
country CHAR(10)Pincodede INT,
Email VARCHAR(50),
N0n_premium CHAR(5),
premium CHAR(5),
PRIMARY KEY (premium, N0n_premium,Email,PhoneNumber,RegisterId,city,country,pincoode)
);
SELECT * FROM Register;
INSERT INTO Register VALUES(1,'bala',85749758,'sklm','IND',53241,'[email protected]','YES','NO'),
(2,'khan',254725541,'VI','Srilanka',538451,'[email protected]','NO','YES'),
(3,'ram',1475955,'KM','AU',535241,'[email protected]','NO','YES'),
(4,'raju',24872535,'MD','ENG',532841,'[email protected]','NO','YES'),
(5,'sham',54614631,'CH','USSR',532541,'[email protected]','NO','YES'),
(6,'gana',36146316,'KL','USA',263241,'[email protected]','YES','NO');
customer table;
CREATE TABLE customer(
RegisterId INT REFERENCES Register(RegisterId),
NAME CHAR(20) REFERENCES Register(NAME),
PhoneNumber INT REFERENCES Register(PhoneNumber),
city CHAR(10) REFERENCES Register(city),
country CHAR(10) REFERENCES Register(country),
pincoode INT REFERENCES Register(pincoode),
Email VARCHAR(50) REFERENCES Register(Email),
N0n_premium CHAR(5) REFERENCES Register(N0n_premium),
premium CHAR(5) REFERENCES Register(premium)
);
SELECT * FROM customer;
INSERT INTO customer VALUES(1,'bala',85749758,'sklm','IND',53241,'[email protected]','YES','NO'),
(2,'khan',254725541,'VI','Srilanka',538451,'[email protected]','NO','YES'),
(3,'ram',1475955,'KM','AU',535241,'[email protected]','NO','YES'),
(4,'raju',24872535,'MD','ENG',532841,'[email protected]','NO','Yes'),
(5,'sham',54614631,'CH','USSR',532541,'[email protected]','NO','YES'),
(6,'gana',36146316,'KL','USA',263241,'[email protected]','YES','NO');
discount table;
CREATE TABLE discount(
id INT REFERENCES Register(RegisterId),
N0n_premium CHAR(5) REFERENCES Register(N0n_premium),
premium CHAR(5) REFERENCES Register(premium),
discountPremium INT,
discountNon_premium INT
);
SELECT * FROM discount;
INSERT INTO discount VALUES(1,'YES','NO',0,0),
(2,'NO','YES',500,0),
(3,'NO','YES',500,0),
(4,'NO','YES',500,0),
(5,'NO','YES',500,0),
(6,'YES','NO',0,0);
products table;
CREATE TABLE products(
NAME CHAR(50) REFERENCES seller(NAME),
product_id INT PRIMARY KEY,
collor CHAR(50) REFERENCES seller(collor),
seller_id INT REFERENCES seller(seller_id),
sizesAvailable CHAR(20)REFERENCES seller(sizesAvailable) ,
quantityAvailable CHAR(20) REFERENCES seller(quantityAvailable),
cost INT REFERENCES seller(cost),
currentdiscount DOUBLE
);
SELECT * FROM products;
INSERT INTO products VALUES('shirt',11,'black',12,'S','Good',1500,0.15),
( 'shoes',12,'white',123,'L','Good',2500,0.1),
( 'pens',13,'black',125,'Ball','Good',150,0),
( 'laptop',14,'i5core',1214,'i5','Good',15000,0.25),
( 'keyboard',15,'black',1245,'S','Good',1500,0.15),
( 'mouse',16,'black',124,'S','Good',500,0.1),
( 'pendrive',17,'sandisk',129,'S','Good',500,0.15);
Administrator table;
CREATE TABLE Administrator(
Admin_id INT,
Admin_Name CHAR(20),
seller_id INT REFERENCES seller(seller_id)
);
SELECT * FROM Administrator;
INSERT INTO Administrator VALUES(121,'Ganesh',12),
(122,'manu',123),
(123,'Akash',125),
(124,'Bala',1214),
(125,'Gana',1245),
(126,'Gireesh',124),
(127,'Gowthami',129);
Seller table;
CREATE TABLE seller(
NAME CHAR(50),
collor CHAR(50),
seller_id INT ,
sizesAvailable CHAR(20),
quantityAvailable CHAR(20),
cost INT,
PRIMARY KEY(NAME,collor,seller_id,quantityAvailable,cost,sizesAvailable)
);
SELECT * FROM seller ;
INSERT INTO seller VALUES('shirt','black',12,'S','Good',1500),
( 'shoes','white',123,'L','Good',2500),
( 'pens','black',125,'Ball','Good',150),
( 'laptop','i5core',1214,'i5','Good',15000),
( 'keyboard','black',1245,'S','Good',1500),
( 'mouse','black',124,'S','Good',500),
( 'pendrive','sandisk',129,'S','Good',500);
categories_1 table;
CREATE TABLE categories_1(
product_id INT REFERENCES products(product_id),
NAME CHAR(50) REFERENCES seller(NAME),
collor CHAR(50) REFERENCES seller(collor),
Brand CHAR(20),
Gender CHAR(20),
Age INT
);
SELECT * FROM categories_1;
INSERT INTO categories_1 VALUES(11,'shirt','black','Gucci','male',25),
(12,'shoes','white','Nike','male',22),
(13,'pens','Red','Elcos','male',30),
(14,'laptop','i5core','HP','male',18),
(15,'keyboard','black','Dell','male',19);
categories_2 table;
CREATE TABLE categories_2(
product_id INT REFERENCES products(product_id),
NAME CHAR(50) REFERENCES seller(NAME),
color CHAR(50) REFERENCES seller(color),
Brand CHAR(20),
Gender CHAR(20),
Age INT
);
SELECT * FROM categories_2;
INSERT INTO categories_2 VALUES(11,'shirt','black','Gucci','Female',25),
(12,'shoes','white','Nike','Female',22),
(13,'pens','Red','Elcos','Female',30),
(14,'laptop','i5core','HP','Female',18),
(15,'keyboard','black','Dell','Female',19);
cart table;
CREATE TABLE cart(
product_id INT REFERENCES products(product_id),
NAME CHAR(50) REFERENCES seller(NAME),
color CHAR(50) REFERENCES seller(color),
Brand CHAR(20)
);
SELECT * FROM cart;
INSERT INTO cart VALUES(11,'shirt','black','Gucci'),
(12,'shoes','white','Nike'),
(13,'pens','Red','Elcos'),
(14,'laptop','i5core','HP'),
(15,'keyboard','black','Dell');
wishlist table;
CREATE TABLE wishlist(
product_id INT REFERENCES products(product_id),
NAME CHAR(50) REFERENCES seller(NAME),
color CHAR(50) REFERENCES seller(color),
Brand CHAR(20)
);
SELECT * FROM wishlist;
INSERT INTO wishlist VALUES(11,'shirt','black','Gucci'),
(12,'shoes','white','Nike'),
(13,'pens','Red','Elcos'),
(14,'laptop','i5core','HP'),
(15,'keyboard','black','Dell');
orders table;
CREATE TABLE orders(
product_id INT REFERENCES products(product_id),
NAME CHAR(50) REFERENCES seller(NAME),
color CHAR(50) REFERENCES seller(color),
Brand CHAR(20),
cost INT REFERENCES seller(cost),
payment CHAR(20)
);
SELECT * FROM orders;
INSERT INTO orders VALUES(11,'shirt','black','Gucci',1500,'Yes'),
(11,'shirt','black','Gucci',1500,'Yes'),
(12,'shoes','white','Nike',2500,'Yes'),
(13,'pens','Red','Elcos',150,'Yes'),
(14,'laptop','i5core','HP',15000,'Yes'),
(14,'laptop','i5core','HP',15000,'Yes'),
(15,'keyboard','black','Dell',1500,'Yes'),
(15,'keyboard','black','Dell',1500,'Yes');
reviews table;
CREATE TABLE reviews(
product_revid CHAR(20),
NAME CHAR(20) REFERENCES Register(NAME),
product_stars INT
);
SELECT * FROM reviews;
INSERT INTO reviwers VALUES(301,'bala',5),
(302,'khan',4),
(303,'ram',3),
(304,'raju',0),
(305,'sham',5),
(306,'gana',3);
>>TASK 3: WAQ SECTION :
1. SELECT * FROM customers ORDER BY city ASC;
2. ALTER TABLE products ADD prod_ CHAR (20);
3.SELECT COUNT(product_id),NAME,collor,Brand,cost,payment FROM Orders
GROUP BY product_id HAVING COUNT(product_id) >= 1;
4. SELECT * FROM orders WHERE NAME LIKE 'k%' AND NAME LIKE'%d' ;
SELECT COUNT(product_id),NAME,collor,Brand,cost,payment
FROM Orders GROUP BY product_id HAVING product_id> 1;
5. SELECT COUNT(product_id),NAME,collor,Brand,cost,payment
FROM Orders GROUP BY product_id HAVING cost>14000 AND product_id>1;
6. Simple procedure for brief explanation and running the above structures of tables;
In this project, Firstly we have created Entity Relation (ER) diagram and given relations to it as per the instructions given in the project .
In SQLyog we have created a main database and then created the required tables like Register, customer, seller, discounts, products,administartion,categories_1, categories_2, orders, wishtlist, reviews and cart which impacts the most complete structure of the E-commerce website.
For tables which already created as per our code requirement for few attributes we have considered the primary key and foreign key where ever it is required.
After that we have inserted the values for all the tables as per the fields input.
And finally, we have write down the queries for given inputs.
The table structures can be runned in the Sqlyog and they are some tables are related to every other neighboring table with a foreign key.
NOTE: ER Diagram (PNG) is uploaded in the form of a Zip file.
* T H A N K Y O U *