Birla Vishvakarma
Mahavidyalaya
A
Project Report
On
ELECTRONIC STORE MANAGEMENT
19CPO31: MEET KERASIYA
19CPO35: MITESH KATELIYA
2CP01: DATABSE MANAGEMENT
SYSTEM
Functional requirements:
➢Our project is about electronic store
management system
➢In which we store the data about
products, customers, payment details,
product stock and supliers
➢In the customer table, there is a details of
items which they purchase are stored
➢In the suppliers details who have supplies
particular electronic items will be store
➢Payment details will be help us in
customers pay the bill via cheque or cash
or credit/debit card
➢In the product table details we easily
maintains product details such as product
id, model id, model no.
➢Category and stock details stored
categorized items in store and whether
the stock is new or old respectively
• ER diagram:
• Relational model:
• FD’S and Normalization:
(1) PRODUCTS:
P_ID → P_NAME
P_ID → PRICCE
P_ID → MODEL
Here L.H.S. of all fd’s are candidate key,
therefore this relation is in BCNF. And also in
3NF, 2NF, 1NF.
P_ID P_NAME PRICE MODEL
(2) CUSTOMER:
CUST_ID → CUST_NAME
CUST_ID →CONTACT_NO
Here L.H.S. of all fd’s are candidate key,
therefore this relation is in BCNF. And also in
3NF, 2NF, 1NF.
CUST_ID CUST_NAME CONTACT_NO
(3) SUPPLIERS:
SUPPLIER_ID → SUPPLIER_NAME
SUPPLIER_ID→ CONTACT_NO
SUPPLIER_ID→ EMAIL_ID
Here L.H.S. of all fd’s are candidate key,
therefore this relation is in BCNF. And also in
3NF, 2NF, 1NF.
SUPPLIER_ID SUPPLIER_NAME CONTACT_NO EMAIL_ID
(4) CATEGORY:
CATEGORY_ID → CATEGORY _NAME
Here L.H.S. of all fd’s are candidate key,
therefore this relation is in BCNF. And also in
3NF, 2NF, 1NF.
CATEGORY_ID CATEGORY_NAME
(5) STOCK:
STOCK_ID→ STOCK_NAME
STOCK_ID→ QUANTITY
Here L.H.S. of all fd’s are candidate key,
therefore this relation is in BCNF. And also in
3NF, 2NF, 1NF.
STOCK_ID STOCK_NAME QUANTITY
(6) PAYMENT :
PAYMENT_ID → PAYMENT_TYPE
PAYMENT_ID → PAYMENT_DATE
Here L.H.S. of all fd’s are candidate key,
therefore this relation is in BCNF. And also in
3NF, 2NF, 1NF.
CUSTOMER_ID PAYMENT_ID PAYMENT_TYPE PAYMENT_DATE
Some queries to retrieve specific data from database
are following :
1)Retrieve the total no of customers who purchase
mobile
select count (pro_name) from product where
(pro_name='mobile');
2)Count how many old stock items has purchased by
customers
select count(product.stock_id) from product join stock
on product.stock_id=stock.stock_id where
stock.s_name='old';
3) find the suppliers who supplies refrigerators
select supplier.sup_name ,product.pro_name from
supplier join product on
product.sup_id=supplier.sup_id where
product.pro_name='headphone';
4)for each product ,retrive the product id, model name
and model number which is in new stock
select
product.pro_id,product.model_name,product.model_i
d from product join stock on
product.stock_id=stock.stock_id where s_name='new';
5)find the product which price is between 1000 and
35000.
select count(pro_name) from product where price
between 1000 and 35000;
6)retrieve average price for old sold stock.
select avg(price) from product join stock on
product.stock_id=stock.stock_id where
stock.s_name='old';
7)retrieve all customer name and catagory names
select cust_name,cat_name from ( product join
customer on product.cust_id=customer.cust_id ) join
catagory on product.cat_id=catagory.cat_id ;
8)retrieve all supplier whose price of product is below
10000
select supplier.sup_name from product join supplier on
product.sup_id=supplier.sup_id where price<10000;
9)retrieve all cash payments in 2019.
select * from payment where payment_type='cash'
and '1-1-2019'<payment_date and payment_date<'31-
12-2019' ;
10)retrieve all customer who purchased new stock .
select customer.cust_name from (product join
customer on product.cust_id=customer.cust_id ) join
stock on product.stock_id=stock.stock_id where
stock.s_name='new';
11)Retrieve all the payments where customer pay
through credit card
select customer.cust_name from customer join
payment on customer.cust_id=payment.cust_id where
payment_type='credit card';
