Super Shop Management System

Super Shop Management System 

Program Name - Computer Engineering Program Code – CO-3-I Course Name – Database Management System Course Code – 22319 Project Title “Super Shop Management System” Academic Year 2018-19

“Super Shop Management System”

Program Name – Computer Engineering                                           Program Code – CO-I

Course Name – Database Management System                                 Course Code – 22319

Project Title - “Super Shop Management System”

Course Outcomes (CO), Practical Outcomes (PRO’s) and Unit Outcomes (UOs) Mapping

COs	PRO’s and PSO Mapping	UOs
CO304.1 – Design4 normalized data on given data. CO304.2 – Create6& Manage database using SQL command CO304.3 – Write1 PL/SQL code for given database. CO304.4 – Apply3 trigger on database also create procedure and function according to condition.	PRO’s Sr. No. 3,4,5,6,7,8,10, 11,13,14,15,16	1. D-Draw the ER diagram of the given database and identify relationship between the entities. 2. B -Design normalized database structure in the given problem. 2. C -Design SQL queries to create relational database and apply in the given constraints. 3-A -Write the given queries using relevant functions. 3-C -Design SQL queries to implement VIEWS. 4-A -Write simple PLSQL code using control structure and handle exceptions in given situation. 4-B -Create cursor for retrieving multiple records. 4-D -Create and apply database triggers using PLSQL in the given situation.
	PSO Mapping PSO1: Software Domain

OBJECTIVES:-

·         To produce software which manage the activities done in a Super-Market.

·         To maintain the stock details.

·         To reduce time in calculation of Sales activities.

·         To store large amount of data in the database which will reduce clumsiness.

·         To reduce paper work; so that users can spend more time on  monitoring the Super-Market.

Introduction

·         This project deals with Super-Market automation.

·         A Super-Market is a self-service store offering a wide variety of items related to food,household or daily use.

·         Includes both purchase and sale of products.

·         Designed to make the existing system more informative, reliable, fast and easy for all the stake-holders.

Work Flow

Work in the Supermarket will be done in the following way:

1. The product will come in the store.

2. Data entry operator will enter the information of the product in

database.

3. The Administrator will enter the taxes and commissions for each

product.

4. The customer will come and take the basket with him/her and choose

the product and took it to the counter.

5. The bill calculating operator will check the products with the bar code

detecting machine then it will match with product-id then it will show

its information and price and the bill will be calculated and total

payment will shown.

6. Customer will pay for the products.

7. All the products will be packed and delivered to the customer.

Modules

We will use 5 modules in this project. These are as follows:

Module 1: Login–Id

This module is made for the login of users. We know that we have 3 users so

login-id is for:

1. Administrator

2. Data Entry Operator

3. Bill Calculating Operator

Module 2: Apply taxes and commissions

This module is for administrator who will

1. Set the taxes for the products.

2. Set the commissions for the products.

Module 3: Check the Report

This module is also for the administrator who can generate or check the report

of the product and how many products are sold on particular date or in a period

of time.

Module 4: Enter the information about products

This module is for data entry operator who will

1. Enter which products come in the store.

2. Prices and expiry date of the product.

Module-5: Calculate the bill

This module is for bill calculating operator who will

1. Calculate the bill.

2. Print it.

Advantages of the proposed system

ü  Reduced processing cost.

ü  Error reduction.

ü  Automatic updation of product details.

ü  Improved report generation and analysis.

ü  Better equipped to meet user requirements.

ü  Reduction in use of paper.

ü  Reduction in man power.

ü  Faster response time

Pre-Normalization

List of Attributes :

• Customer – Customer Name , Customer ID , Address, Phone no

• Ordered Product – Product Name , Product Type , Product  ID , Product Price, Quantity

• Billing Counter – Bill No, Customer ID details , Bill Amount, Bill Status

• Product Shelves – Product Category , Product Type , Product Model ID

• Employee – Employee NameEmployeeDesignation ,Employee ID , Salary

Relationships between Tables

1.    Customer Purchase Products

2.    Customer Pays Bill

3.    Customer Buys From Shop Details

4.    Employee Has Shop Details

5.    Distributor Distribute To Shop Details

6.    Distributor Supplies Products

Primary Keys

·         CUSTOMER – CUSTOMER_ID

·         BILL - BILL_NO.

·         EMPLOYEE – EMP_ID

·         DISTRIBUTER- DISTRIBUTER_ID

·         PRODUCT- PRODUCT_ID

Relationships

·         Customer – Customer ID – Billing Counter

·          

ER Diagram

SQL Queries

1>  CREATE TABLE SHOP_DETAILS( SHOP_NAME VARCHAR(30),ADDRESS VARCHAR(50),EMAIL_ID VARCHAR(50),CONTACT_NO BIGINT);

INSERT INTO SHOP_DETAILS VALUES('SBS SUPER SHOPEE','KHOL GALI DHULE','sbsdhule@gmail.com',7447521020);

 2>CREATE TABLE EMPLOYEE (EMP_ID INT PRIMARY KEY,F_NAME VARCHAR(20),L_NAME VARCHAR(20),DESIGNATION VARCHAR(20),SALARY INT );

INSERT INTO employee VALUES(1001,'Paresh','Patil','Administrator',40000);

INSERT INTO employee VALUES(1002,'Suresh','More','Manager',25000);

INSERT INTO employee VALUES(1003,'Lalit','Bhavsar','Data Entry oparetor',15000);

INSERT INTO employee VALUES(1004,'Rajesh','Koranne','Bill Accountant',10000);

INSERT INTO employee VALUES(1005,'Mahesh','Suryawanshi','Grain cleaner',9000);

INSERT INTO employee VALUES(1006,'Suraj','Agrawal','Grain cleaner',8500);

INSERT INTO employee VALUES(1007,'Rakesh','Mahale','Sweeper',7000);

3>CREATE TABLE CUSTOMER(CUSTOMER_ID INT PRIMARY KEY,C_NAME VARCHAR(50),PHONE_NO BIGINT,ADDRESS VARCHAR(50));

INSERT INTO customer VALUES('Anil','Mali',2001,9884063210,'Khol galli, Dhule');

INSERT INTO customer VALUES('Akshay','Bramhe',2002,8567432180,'Walwadi, Dhule');

INSERT INTO customer VALUES('Jayesh','Bafna',2003,9856321473,'vadibhokar, Dhule');

INSERT INTO customer VALUES('Ashish','More',2004,9764312586,'Panchavati, Dhule');

INSERT INTO customer VALUES('Bhavesh','Patil',2005,9864676123,'railway_station,Dhule');

INSERT INTO customer VALUES('Swarnim','Jain',2006,9685743212,'ram_wadi,Dhule');

INSERT INTO customer VALUES('Sushant','Shinde',2007,9999666633,'agrawal_nagar,Dhule');

4>CREATE TABLE PRODUCT(PRODUCT_ID INT PRIMARY KEY,TYPE VARCHAR(50),PRICE INT,QUANTITY INT);

INSERT INTO PRODUCT VALUES(

5001

,’Bath and whasing soap’

,30,

50);

INSERT INTO PRODUCT VALUES(

5002

,’Snacks’

,10,

50);

INSERT INTO PRODUCT VALUES(

5003

,’Masala and papads’

,70,

50);

INSERT INTO PRODUCT VALUES(

5004

,’Mens and womens grooming’

,100,

50);

INSERT INTO PRODUCT VALUES(

5005

,’Dals,Sabudana and grains’

,30,

50);

INSERT INTO PRODUCT VALUES(

5006

,’Choclates’

,30,

50);

5>CREATE TABLE BILL(BILL_NO INT PRIMARY KEY,CUSTOMER_ID INT REFERENCES customer(CUSTOMER_ID),  AMOUNT INT,BILL_STATUS VARCHAR(10));

INSERT INTO bill VALUES(3001,2001,2000,'Paid');

INSERT INTO bill VALUES(3002,2002,2400,'Paid');

INSERT INTO bill VALUES(3003,2003,1500,'Paid');

INSERT INTO bill VALUES(3004,2004,1700,'Paid');

INSERT INTO bill VALUES(3005,2005,1100,'Paid');

INSERT INTO bill VALUES(3006,2006,1000,'Due');

INSERT INTO bill VALUES(3007,2007,3500,'Paid');

6>CREATE TABLE DISTRIBUTER(DISTRIBUTER_ID INT PRIMARY KEY,D_NAME VARCHAR(50),PRODUCT_TYPE VARCHAR(20), P_OF_SINGLE INT,TOTAL_PRICE INT);

INSERT INTO distributer VALUES('Raja Distributer',4001,'Bath Soap',20,2000);

INSERT INTO distributer VALUES('Ganesh Distributer',4002,'Namkeens',15,1500);

INSERT INTO distributer VALUES('Radhika Distributer',4003,'Masala & Papads',20,2000);

INSERT INTO distributer VALUES('Varai Distributer',4004,'Pickles',60,3000);

INSERT INTO distributer VALUES('Ravalgaon Choclates',4005,'Choclates',200,2000);

INSERT INTO distributer VALUES('ShivaDistributer',4006,'Dals,Sabudana,Grains',70,70000);

INSERT INTO distributer VALUES('Manish Distributer',4007,'Mens and womens Gromming',20,2000);

Conclusion

With business opportunities increasing as never before, companies are in dire need of

efficient management. One such key area is to maintain a methodical way of managing largedatabases, especially in the Retail sector.

DBMS is a vital tool for future growth of business organizations.

It offers a simple, efficient and reliable way of storing, managing and accessing data.

The features offered by DBMS are : Query ability, Backup, Security and Computation which are the needs of a fast-paced corporate system.

 To churn profits, companies need to have a good plan along with affective DBMS.

• Tweet
• Share
• Share
• Share

Related Post