(DBMS PROJECT REPORT)
END SEMESTER EVALUATION
SUBMITTED TO:
`DR.RANJEET KUMAR RANJAN
SUBMITTED BY:
SURYA RATHEE (102203232)
SOURABH (102203241)
BARTINDER SAINI (102203243)
VIKAS KUMAR(102203233)
GROUP N0. 2CO6
THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY
TABLE OF CONTENTS
• Introduction and Project Overview
• ER Diagram
• ER to table
• Normalization
• SQL/PL-SQL Code
• PL-SQL Queries
• Front End Website
INTRODUCTION AND PROJECT OVERVIEW
The purpose of this project is to provide an online food delivery system.
Online Food ordering system is a process in which one can order various foods and beverages
from some local restaurant and hotels through the use of internet, just by sitting at home or
any place. And the order is delivered to the told location. Nowadays everyone is having busy
schedule whether it is urban area or rural. But talking specifically about the urban areas and
deeply about the big cities, people out there are so busy in their life that they don’t get
enough of time to have their meals properly. As these days women are no less than men, in
any field. So in big cities even wives are working women, therefore mostly the small families
manage to have their food ordered from somewhere, as they lack time. Not only this is the
case, if we talk about the children in the modern era they like only fast food or something
from the outside. But they ignore eating homemade meals.
So food ordering system these days has one of the fastest growing market, though being a
new idea. In this project we have developed something like the same to earn from and serve
the nation in a much better way possible. Nowadays, people are more regular to dine-in at
restaurant for their meals. The online food ordering system provides convenience for the
customers that are nothing special but the general busy people of the society. It overcomes
the demerits of the manual hotel or mess system and the old fashioned queuing system. This
system enhances the readymade of foods than people.
Therefore, this system enhances the speed of getting food in person’s plate and quality and
manner of taking the order from the customer. It provides a better communication platform.
The user’s details are stored using the electronic media. The online food ordering system
provides the menu online and the customers can easily place the order by just clicking the
mouse or by touching a button on their smart phones. Also with the food ordering system
online, people can easily track their orders, and admin can maintain customer’s database and
advance the food delivery system.
This food ordering system allows the user to select the desired food items from a list of
available menu items provided by the local hotel or restaurant. The user can place orders for
the food items of their like from the list. The payment can be made online or pay-on-delivery
system. The user’s details are maintained confidential because it maintains a separate
account for each user. An id and password is provided for each user. And several encryption
techniques have also been used on the server side to protect the card details. Therefore it
provides a more secured and safe ordering system.
ER DIAGRAM
ER TO TABLE
NORMALIZATION
• Customer(Customer_id (KEY), first_name, last_name, address, password,
email, phone_no)
Customer_id = >first_name
Customer_id => last_name
Customer_id => address
Customer_id => password
Customer_id => email
Customer_id => phone_no
The above table is in :-
o 1NF as it does not contain any multivalued attribute.
o 2NF as it is in 1NF and every non-primary key attribute is fully
dependent on the primary key
o 3NF as it is in 2NF and every non-key attribute is non-transitively
dependent on the primary key
o BCNF as it is in 3NF and every determinant is a primary key.
• Restaurant(Restaurant_id (KEY), Restaurant_name, Restaurant_address,
Restaurant_password , Restaurant_phone _no)
Restaurant_id => Restaurant_name
Restaurant_id => Restaurant_address
Restaurant_id => Restaurant_password
Restaurant_id => Restaurant_phone _no
The above table is in :-
o 1NF as it does not contain any multivalued attribute.
o 2NF as it is in 1NF and every non-primary key attribute is fully
dependent on the primary key.
o 3NF as it is in 2NF and every non-key attribute is non-transitively
dependent on the primary key.
o BCNF as it is in 3NF and every determinant is a primary key.
• Ratings((customer_id (KEY), restaurant_id,rating)
customer_id,restaurant_id => rating
The above table is in :-
o 1NF as it does not contain any multivalued attribute.
o 2NF as it is in 1NF and every non-primary key attribute is fully
dependent on the primary key.
o 3NF as it is in 2NF and every non-key attribute is non-transitively dependent on
the primary key.
o BCNF as it is in 3NF and every determinant is a primary key.
• Admin(Admin_id (KEY),admin_name,admin_password)
Admin_id=>admin_name
Admin_id=> Admin_password
The above table is in :-
o 1NF as it does not contain any multivalued attribute.
o 2NF as it is in 1NF and every non-primary key attribute is fully
dependent on the primary key
o 3NF as it is in 2NF and every non-key attribute is non-transitively
dependent on the primary key.
o BCNF as it is in 3NF and every determinant is a primary key.
• Category(Category_id (KEY), Category_name)
Category_id => Category_name
The above table is in :-
o 1NF as it does not contain any multivalued attribute.
o 2NF as it is in 1NF and every non-primary key attribute is fully
dependent on the primary key
o 3NF as it is in 2NF and every non-key attribute is non-transitively
dependent on the primary key.
o BCNF as it is in 3NF and every determinant is a primary key
Category_id is the key.
• Menu_item(item_code (KEY), item_name, price)
Item_code=>item_name
Item_code=>price
The above table is in :-
o 1NF as it does not contain any multivalued attribute.
o 2NF as it is in 1NF and every non-primary key attribute is fully
dependent on the primary key
o 3NF as it is in 2NF and every non-key attribute is non-transitively dependent on
the primary key
o BCNF as it is in 3NF and every determinant is a
primary key.
• Order_details(Order_id,Order_time,Order_amount,
Order_status)
Order_id => Order_time
Order_id => Order_amount
Order_id => Order_amount
The above table is in :-
o 1NF as it does not contain any multivalued attribute.
o 2NF as it is in 1NF and every non-primary key attribute is fully
dependent on the primary key
o 3NF as it is in 2NF and every non-key attribute is non-transitively
dependent on the primary key.
o BCNF as it is in 3NF and every determinant is a primary key.
• Order(order_id,item_code,quantity)
{Order_id,item_code}=>quantity
The above table is in :-
o 1NF as it does not contain any multivalued attribute.
o 2NF as it is in 1NF and every non-primary key attribute is fully
dependent on the primary key
o 3NF as it is in 2NF and every non-key attribute is non-transitively
dependent on the primary key.
o BCNF as it is in 3NF and every determinant is a primary key.
Order_id,item_code is the composite primary key.
• Payment_details(payment_id (KEY), payment_mode, payment_time)
payment_id=>payment_mode
payment_id=>payment_time
The above table is in :-
o 1NF as it does not contain any multivalued attribute.
o 2NF as it is in 1NF and every non-primary key attribute is fully
dependent on the primary key
o 3NF as it is in 2NF and every non-key attribute is non-transitively
dependent on the primary key.
o BCNF as it is in 3NF and every determinant is a primary key.
• Delivery_details(delivery_id (KEY), delivery_address, delivery_status)
delivery_id=>delivery_address
delivery_id=>delivery_status
The above table is in :-
o 1NF as it does not contain any multivalued attribute.
o 2NF as it is in 1NF and every non-primary key attribute is fully
dependent on the primary key
o 3NF as it is in 2NF and every non-key attribute is non-transitively
dependent on the primary key.
o BCNF as it is in 3NF and every determinant is a primary key.
SQL/PLSQL CODES
CREATE TABLE Admin(
Admin_id number(8) primary key,
Admin_name varchar(15) NOT NULL,
Admin_password varchar(16) NOT NULL);
INSERT INTO Admin VALUES (01,'Team-GBH','GBH-@123');
CREATE TABLE customer(
customer_id number(8) primary key,
customer_firstname varchar(30) NOT NULL,
customer_lastname varchar(30) NOT NULL,
customer_password varchar(16) NOT NULL,
customer_phoneno varchar(10) NOT NULL,
customer_address varchar(50) NOT NULL,
customer_email varchar(20) NOT NULL,
Admin_id int references Admin(Admin_id));
INSERT INTO customer VALUES
(01,'Harsh','Mehta','harsh#212',7435068222,'1st Cross,Rammurthy
nagar,Bangalore' , '
[email protected]',01);
INSERT INTO customer VALUES
(02,'Jainam','Shah','Jainu$820',9924567892,'117, Examiner Road,
Fort,Mumbai, Maharashtra','
[email protected]',01);
INSERT INTO customer VALUES
(03,'Ishaan','Dawra','bmw@765',9874578428,'1723,Opp. Clock Tower
Ludhiana,Punjab','
[email protected]',01);
INSERT INTO customer VALUES
(04,'James','Heckins','Dodge@985',8902345789,'Bee H-138,
Kodambakkam Road,Chennai, Karnataka','
[email protected]',01);
INSERT INTO customer VALUES
(05,'Muskanpreet','Kaur','lovi@123',9934654319,'854,Guru Nanak Nagar
Amritsar, Punjab','
[email protected]',01);
INSERT INTO customer VALUES
(06,'Karanpreet','Singh','rocket@123',7734688001,'Opp. Satsang
Bhawan,Beas,Punjab','
[email protected]',01);
INSERT INTO customer VALUES
(07,'Divyanka','Kapoor','Cat@123',9867445890,'A/3, Alpha
City,Mumbai,Maharashtra','
[email protected]',01);
INSERT INTO customer VALUES
(08,'Rustom','Pavri','Rustom&993',8839950081,'134,14,Nagarathpet
Main Road,Banglore,Karnataka','
[email protected]',01);
INSERT INTO customer VALUES
(09,'Lovish','Singla','love@123',9891444976,' 754 Arjan Dev
Nagar,Amritsar,Punjab','
[email protected]',01);
INSERT INTO customer VALUES
(10,'Jogesh','Das','Jogesh@231',8870080010,'854 Gali No.1 Mujessar,Ram
Colony,Haryana','
[email protected]',01);
CREATE TABLE restaurant(
Admin_id number(8) references Admin(Admin_id),
restaurant_id number(8) primary key,
restaurant_name varchar(20) NOT NULL,
restaurant_address varchar(50) NOT NULL,
restaurant_password varchar(16) NOT NULL,
restaurant_phoneno varchar(10) NOT NULL
);
INSERT INTO restaurant VALUES(01,111,'China
Town','66,Residency,Bengaluru,Karnataka','ct@111',8066606969);
INSERT INTO restaurant VALUES(01,112,'Leaf Hatch','F-
30,Sampige,Chennai,Tamil Nadu','LHat@112',8762204545);
INSERT INTO restaurant VALUES(01,113,'Spicy Terrace','24,Ashok
nagar,Ludhiana,Punjab','SpiT@113',8067189999);
INSERT INTO restaurant VALUES(01,114,'Yay Mumbaar','Raheja Tower,
Mumbai,Maharashtra','YaM@l14',9458262800);
INSERT INTO restaurant VALUES(01,115,'Boombox','Sector-17
Chandigarh,Punjab','Boom#115',9773818001);
INSERT INTO restaurant VALUES(01,116,'Sowento','Level-8, Connaught
Place, New Delhi, Delhi','Sow@l16',1141191040);
INSERT INTO restaurant VALUES(01,117,'Taj Hotel','154 Khan Market,
India Gate, New Delhi','tajr@117',1166566162);
INSERT INTO restaurant VALUES(01,118,'Haveli','B-Block,Ranjit
Avenue,Anritsar,Punjab','haveli@118',4428368333);
INSERT INTO restaurant VALUES(01,119,'Chauki Ghani','Jandiala
Rd,Amritsar,Punjab','Cg@119',8054343839);
INSERT INTO restaurant VALUES(01,120,'Kobe sizzler','Main City
centre,Gurgaon,Haryana','Kobe@120',8889323457);
CREATE TABLE category(
category_id number(8) primary key,
category_name varchar(15) NOT NULL,
restaurant_id number(8) references restaurant(restaurant_id)
);
INSERT INTO category VALUES (201,'SOUTH-INDIAN','112');
INSERT INTO category VALUES (202,'CHINESE','111');
INSERT INTO category VALUES (203,'ALL','115');
INSERT INTO category VALUES (204,'INDONESIOAN','113');
INSERT INTO category VALUES (205,'KATHIAWADI','114');
INSERT INTO category VALUES (206,'PUNJABI','118');
INSERT INTO category VALUES (207,'RAJASTHANI','119');
INSERT INTO category VALUES (208,'DESSERTS','120');
INSERT INTO category VALUES (209,'NORTH-INDIAN ','116');
INSERT INTO category VALUES (210,'CONTINENTAL','117');
CREATE TABLE Menu_items(
item_code number(8) primary key,
item_name varchar(20) NOT NULL,
Price number(8) NOT NULL,
category_id number(8) references category(category_id)
);
INSERT INTO Menu_items VALUES(301,'White Pasta',230,203);
INSERT INTO Menu_items VALUES(302,'Alfredo Pasta',200,203);
INSERT INTO Menu_items VALUES(303,'Tandoori pizza',400,203);
INSERT INTO Menu_items VALUES(304,'Masala dosa',300,201);
INSERT INTO Menu_items VALUES(305,'uttampam',115,201);
INSERT INTO Menu_items VALUES(306,'idli-vada-sambhar',120,201);
INSERT INTO Menu_items VALUES(307,'Amritsari Kulcha',120,206);
INSERT INTO Menu_items VALUES(308,'Paneer parantha',250,206);
INSERT INTO Menu_items VALUES(309,'Bhature Chole',100,209);
INSERT INTO Menu_items VALUES(310,'Dry manchrian',200,202);
INSERT INTO Menu_items VALUES(311,'CHINESE BHEL',150,202);
INSERT INTO Menu_items VALUES(312,'Fried rice',210,202);
INSERT INTO Menu_items VALUES(313,'lasaniya bataka',250,210);
INSERT INTO Menu_items VALUES(314,'rigan no olo',300,210);
INSERT INTO Menu_items VALUES(315,'sev tameta nu shaak',220,205);
INSERT INTO Menu_items VALUES(316,'waffle',220,208);
INSERT INTO Menu_items VALUES(317,'chocolate brownie',115,208);
INSERT INTO Menu_items VALUES(318,'pancakes',120,208);
INSERT INTO Menu_items VALUES(319,'burger',55,203);
INSERT INTO Menu_items VALUES(320,'french fries',100,203);
INSERT INTO Menu_items VALUES(321,'muskabun',25,209);
INSERT INTO Menu_items VALUES(322,'peach mojito ',180,207);
INSERT INTO Menu_items VALUES(323,'hazlenut coffee',135,207);
INSERT INTO Menu_items VALUES(324,'kewi juice',90,207);
INSERT INTO Menu_items VALUES(325,'French toast',100,209);
INSERT INTO Menu_items VALUES(326,'Pancakes ',180,208);
INSERT INTO Menu_items VALUES(327,'Steaks',335,210);
CREATE TABLE Payment_details(
payment_id number(8) primary key,
payment_mode varchar(15) NOT NULL,
payment_timestamp timestamp NOT NULL
);
INSERT INTO Payment_details VALUES (501,'PayTM','15-NOV-2022
11:12:33');
INSERT INTO Payment_details VALUES (502,'COD','11-NOV-2022
9:06:30');
INSERT INTO Payment_details VALUES (503,'CARD','22-NOV-2022
1:10:22');
INSERT INTO Payment_details VALUES (504,'COD','02-NOV-2021
5:12:03');
INSERT INTO Payment_details VALUES (506,'GPay','27-OCT-2021
01:13:13');
INSERT INTO Payment_details VALUES (507,'CARD','09-NOV-2022
09:08:48');
INSERT INTO Payment_details VALUES (508,'COD','28-OCT-2021
10:07:53');
INSERT INTO Payment_details VALUES (509,'PhonePe','05-NOV-2022
7:17:37');
INSERT INTO Payment_details VALUES(510,'Mobkwik','16-NOV-2022
9:19:22');
CREATE TABLE delivery_details(
delivery_id number(8) primary key,
delivery_address varchar(50) NOT NULL,
delivery_status varchar(30) NOT NULL
);
INSERT INTO delivery_details VALUES (401,'1st Rammurthy
nagar,Bangalore','Delivered');
INSERT INTO delivery_details VALUES (402,'Examiner
Road,Mumbai','Delivered');
INSERT INTO delivery_details VALUES (403,'1723,Opp. Clock
Tower,Ludhiana','Preparing');
INSERT INTO delivery_details VALUES (404,'H-138, bakkam
Road,Chennai','Pickedup');
INSERT INTO delivery_details VALUES (405,'854,Guru Nanak Nagar,
Amritsar','Delivered');
INSERT INTO delivery_details VALUES (406,'Kaziranga,
Meghalaya','preparing');
INSERT INTO delivery_details VALUES (407,'A/3, Alpha
City,Mumbai','Preparing');
INSERT INTO delivery_details VALUES (408,'134/135
Nagarathpet,Banglore,Karnataka','Delivered');
INSERT INTO delivery_details VALUES (409,'754 Arjan Dev
Nagar,Amritsar','Picked-up');
INSERT INTO delivery_details VALUES (410,'Main City
centre,Gurgaon,Haryana','Pickedup');
CREATE TABLE Rating(
ratings number(8) NOT NULL,
customer_id number(8) references customer(customer_id),
restaurant_id number(8) references restaurant(restaurant_id)
);
INSERT INTO Rating VALUES (3,01,111);
INSERT INTO Rating VALUES (4,02,114);
INSERT INTO Rating VALUES (2,03,117);
INSERT INTO Rating VALUES (4,04,120);
INSERT INTO Rating VALUES (3,06,115);
INSERT INTO Rating VALUES (4,08,113);
INSERT INTO Rating VALUES (3,09,112);
INSERT INTO Rating VALUES (4,01,119);
INSERT INTO Rating VALUES (3,09,116);
select * from Rating
CREATE TABLE Order_details(
Order_id number(8) primary key,
Order_time timestamp,
Order_amount number(8) NOT NULL,
Order_status varchar(20) NOT NULL,
customer_id number(8) references customer(customer_id),
delivery_id number(8) references delivery_details(delivery_id),
payment_id number(8) references Payment_details(payment_id),
restaurant_id number(8) references restaurant(restaurant_id)
);
INSERT INTO Order_details VALUES (601,'11-FEB-2022
9:06:30','240','Preparing',01,401,501,111);
INSERT INTO Order_details VALUES (603,'02-APR-2021 5:12:03','345',
'delivered',03,409,503,117);
INSERT INTO Order_details VALUES (604,'27-JUL-2021 01:13:13','400',
'preparing', 04,404,504,112);
INSERT INTO Order_details VALUES (605,'09-MAY-2022 09:08:48','250',
'delivered',04,404,509,119);
INSERT INTO Order_details VALUES (606,'28-DEC-2021 10:07:53','230',
'preparing', 06,406,506,114);
INSERT INTO Order_details VALUES (607,'05-MAR-2022 7:17:37','300',
'delivered',08 ,408 ,507, 113);
INSERT INTO Order_details VALUES (608,'16-JAN-2022 9:19:22','50',
'preparing',09,410,508,120);
Select * from Orders
CREATE TABLE Orders(
quantity number(8) NOT NULL,
Order_id number(8) references Order_details(Order_id),
item_code number(8) references Menu_items(item_code)
);
INSERT INTO Orders VALUES (2,601,307);
INSERT INTO Orders VALUES (3,603,305);
INSERT INTO Orders VALUES(2,604,310);
INSERT INTO Orders VALUES (1,605,313);
INSERT INTO Orders VALUES(1,606,301);
INSERT INTO Orders VALUES (3,607,306);
INSERT INTO Orders VALUES(2,608,321);
PL-SQL QUERIES
➢ Retrieve data for restaurant id 117
➢ Retrieve the item name, price, quantity, order status and total amount of
order when menu item matches the order details
➢ Alter table
➢ Order by
➢ Count and like
➢ Group by and having
➢ Trigger
➢ To display details of customer
➢ To display details of customer with order amount equal to 300
➢ To display details of customer with rating more than 3
➢ Exception handling
Frontend Website
