TABLE OF CONTENTS
S.no Chapter Page No.
1 Abstract 3
2 Problem Statement 4
2 Introduction 5
3 Analysis And Design 6
3.1) Objectives 6
3.2) Requirement And Specification 6
4 List Of Figures 7
4.1) ER Diagram 7
4.2) Schema 8
5 Queries(DDL,DML,etc) 9-11
6 Project Functions 12
7 Front End Features 13
8 Table Description 14-15
9 SQL Concepts Used 16-17
10 Data Normalization 18
11 DataBase Guidelines 19
12 Conclusion 2
Lg Shopping Marketer is an Relational Database System made with SQL subsystem MYSQL which is capable of handling customer records and transcations independantly of any changes to an application program connected to this system.
This Database comprises of two modules The back End which is MYSQL and the front
end is implemented in an programming Language Java.
The back end MYSQL is capable of storing and quering hundered’s of customer records which comprises of their billing information,purchases,Personal information such as phone,email and also contains the entire blueprint of the shopping mall which enables dynamic and fast manipulation of products and employees if any changes are needed.
The front end is an application Program implemented in Java which provides an GUI(Graphical User Interface) for an user friendly environment for both customer’s and employees.Every customer and employee has an account coordinated by the Program to ensure data security and provides restricted but sufficient access to data.
As Data grows exponentially in size and exceeds tera bytes of storage space an new system is needed to organize and refine this information to allow faster and efficient access to this data while keeping the data in an constient access when multiple users access this data concurrently.
SQL(Structured Query Language) Solves this problem.SQL is an new script like programming language that organizes data into new tables and tabulates,indexed them in an orderly and effeciently format.
The most powerfull feature about SQL is that data can be queried using human vocabulary and multiple advanced techniques can be performed on the search results such as if/else conditions,joints,nested queries etc.
The problem stated in this project is managing an large shopping database which stores multiple products whilest keeping track of customer,employee records with multiple users concurrently accessing this data and how SQL helps us to solve this problem.
Lg Shopping Marketer is an offline Customer Shopping DataBase which handles customer queries regarding product purchases,billing requirments and also stores employee information regarding which department they are working in.
Each Floor has an department and multiple Subdepartments which holds info on what product they are selling which employees work there and the attributes of every product available for shopping experience.
Every product has an associated coupon which is unique for each product even with the same brand. If the customer has to make an purchase he/she has to specify what coupons and in what quantity would they like to purchase in the checkout cart.
Once the cart is full the customer proceeds to payment by providing payment info such as email,phone,name etc.Then that customer record is permantly stored in the database and the receipt is given to the customer. Every customer has an unique ID assigned to him.her so if they visit again special bonuses can be given to them and payment can be made quickly.
For an employee his/her info is seperated into two components The Work_Info and the Person_Info. The Work_Info stores the employess id their designation and their salary and the Person_Info stores that employee’s name,phone,email etc. This allows more efficient record access and makes info updation easier
ANALYSIS AND DESIGN
As Data grows More complex And execeeds tera bytes of storage and integrated system is required to store,index and query this data to enable faster and concurrent access to the data. An need emerges to develop an separate structured language dedicated to this task.
This is where SQL(Structured Query Language) comes to play. SQL indexes and tabulates data in the form of relations and provides procedures and methods to manipulate,delete and retrive this data via advanced techniques such as Joints, Nested ; Correlated Queries, If/Else Statements etc.
This Project is meant to demonstrate some of these concepts. It illustrates concepts such as Keys(Foreign.Primary),Triggers,Tables,Constraint’s etc. It demonstrates SQL command types such as DDL(Data Definition Langguage),DML(Data Manipulation Language) etc.
Besides illustrating these concepts The database created is an offline shopping database that records customer/employee information such as names,phones,emails etc. Stores customers cart as an independent table,Manages the manager of every department and Keeps track of customer purchases to make future sales predictions etc.
REQUIRMENT AND SPEIFICATION
S.NO REQUIRMENT SPECIFICATION
1. DataBase MYSQL(5.1)
2. Front End Java(jdk1.8.0_171)
3. Operating System Windows/Linux
4. CPU Intel(I5)
5. RAM (300-400)MB