Indian Institute of Technology, Indore
CS 257
DBIS Project
Restaurant Management (Khazana) Database for IIT Indore
Xomato of IIT Indore
Submitted by- Shaikh Ubaid and Ruchir Mehta
Introduction:
Restaurants are the places where they have to deal with huge databases handling the menu and the order placed by multiple customers simultaneously. We as customers face a lot of problems while choosing a place and food items to dine. We want that we eat our desired food items which also has good rating points and great reviews and would we great if it is among the trending items of that restaurant. To rectify this issue we came up with a platform that will take care of all your needs and provide you with a facility to order food from home after a proper procedure of logging in into your account.
Project Description :
We as students face many problems and even outsiders many times face some difficulties while choosing a restaurant at mealtime on our campus. Out institute offers various degrees viz. BTech, MTech, PhD, MSc and there are different prices for students pursuing different degrees. While choosing a restaurant, we do not know whether it is open or close and even if we know the menu and prices are unknown. Secondly, many items of a restaurant do not become popular since they remain hidden from customers due to unawareness about them among the customers.
A pocket-friendly person wants that he gets his desired food item in his budget which has nice ratings and reviews and is currently a trending item of that restaurant. There are about 10 restaurants which generally makes the customer confused in choosing a particular place to eat. Hence, we came up with an idea to integrate this information about restaurants on our campus which is very useful for all students, faculties, visitors and the shop owner themselves.
For Newcomers and Visitors:
For newcomers, visitors and all the students@ IITI, it will be a great boon which will help them decide. 1. What to eat?
2 . Where to eat?
3. When to eat?
For shop owners:
They can:
1)Edit the menu anytime:
a)add food items
b)delete items
c)modify prices
2)Change restaurant timing
a)they can switch their restaurant open/close through a manual process also in our software 3)Manage/maintain a proper information (type/job)about the workers, chef, waiter, etc(their salary, their name, hired date)
4)Manage the amount of food constituents available in their inventory. Here manage involves the amount present and constituents present. Moreover as soon as the quantity of any item reduces below a pre-set limit, it shows warning and gets added to the shopping list of Due Items.
- We will keep a managerial section in this project which keeps a track of all out clients (shopowners) where the upcoming shop owners can join the system through a login channel for which we maintain a login system.
- Customers will get a bill based on the food items ordered. Bill will be printed. They can order food from one restaurant only.
Restaurants:
ER Analysis: Identifying Entity Sets :
- Employee(entity set)
- Restaurant(entity set)
- Orders(entity set)
- Order_Details(partial entity)
- Customer(entity set)
- Food(entity set)
- Account(entity set)
Relationship Sets:
- Menu – Between Restaurant and Food Item (one to many)
- Controls – Recursive Relation on Employee Entity Set. A Manager controls its Workers (one to many)
- Works – Between Employee and Restaurant (many to one)
- Belongs – Between Bill and Restaurant (many to one)
- Contains – Between Bill and Food Items (many to many)
- Consists – Between Order and Order Details (one to many)
- Employee_Login – Between Manager and Account (one to one)
- Customer_Login – Between Customer and Account (one to one)
- Paid_By – Between Bill and Customer (many to one)
Entity and Relationship Sets:
Transformation of ER diagrams into set of Tables:
create database rs;
- Employee
CREATE TABLE employee (
employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name VARCHAR (255),
last_name VARCHAR (255),
salary INTEGER,
incentive DECIMAL,
contact_no VARCHAR,
hire_date DATE,
experience INTEGER,
manager_id INTEGER
);
- Restaurant
CREATE TABLE restaurant (
name VARCHAR (255),
rating DECIMAL,
rating_count INTEGER,
status BOOLEAN,
open_time TIME,
close_open TIME,
contact_no VARCHAR,
trending VARCHAR,
least_ordered VARCHAR,
located_near VARCHAR,
street_name VARCHAR,
rest_id INTEGER PRIMARY KEY AUTOINCREMENT,
city VARCHAR
);
- Food
CREATE TABLE food (
name VARCHAR (255),
price INTEGER,
rating INTEGER,
rating_count INTEGER,
food_id INTEGER PRIMARY KEY AUTOINCREMENT
);
- Orders
CREATE TABLE Orders (
order_id INTEGER PRIMARY KEY AUTOINCREMENT,
order_date DATE DEFAULT (date('now') ),
sum INTEGER,
rest_comment VARCHAR,
rest_rating INTEGER,
order_time TIME DEFAULT (time('now') )
);
- Customers
CREATE TABLE customers (
username VARCHAR PRIMARY KEY,
first_name VARCHAR,
last_name VARCHAR,
phone INTEGER
);
- Menu
CREATE TABLE Menu (
Rest_id INTEGER,
Food_id INTEGER PRIMARY KEY AUTOINCREMENT
);
- Works
CREATE TABLE works (
rest_id INTEGER,
employee_id INTEGER PRIMARY KEY AUTOINCREMENT
);
- Order_Details
CREATE TABLE Order_Details (
order_id INTEGER REFERENCES Orders (order_id),
food_id INTEGER REFERENCES food (food_id),
food_rating INTEGER,
comments VARCHAR (255),
quantity INTEGER,
amount INTEGER
);
- Account
CREATE TABLE account (
username VARCHAR (255),
password VARCHAR (255)
);
- Belongs
CREATE TABLE belongs (
rest_id INTEGER,
order_id INTEGER PRIMARY KEY AUTOINCREMENT
);
- Employee_login
CREATE TABLE employee_login (
employee_id INTEGER,
username VARCHAR (255)
);
- Paid_By
CREATE TABLE paid_by (
order_id INTEGER PRIMARY KEY AUTOINCREMENT,
username VARCHAR (255)
);
Triggers
CREATE TRIGGER cal
AFTER INSERT
ON Order_Details
FOR EACH ROW
BEGIN
UPDATE order_details
SET amount = new.quantity * (
SELECT price
FROM food
WHERE food.food_id = new.food_id
)
WHERE food_id = new.food_id AND
order_id = new.order_id;
END;
;
SQL Queries (as implemented in code):
- HomePage:
select * from restaurant;
- MenuPage:
- select * from restaurant;
- select * from menu inner join food on menu.food_id=food.food_id where menu.rest_id=rest_id;
- SELECT name FROM restaurant WHERE rest_id =rest_id;
- select * from restaurant where name=rest_name;
- select * from food where food_id in ("+food_id+");
- insert into orders(sum) values(sum);
- SELECT * FROM orders ORDER BY order_id DESC LIMIT 1;
- Insert into belongs(rest_id,order_id) values(restaurant.rest_id,order_detail.order_id);
- insert into paid_by(order_id,username) values(order_detail.order_id,req.session.username);
- for(let index=0;index<food_item.length; index+ +)
{ db.run("insert into order_details(order_id,food_id,quantity) values(?,?,?)",[order_detail.order_id,food_item [index].food_id,m.get(food_item[index].food_id)],function(err){}); }
- select * from customers where username = req.session.username;
- select * from paid_by inner join orders on paid_by.order_id=orders.order_id inner join belongs on orders.order_id=belongs.order_id inner join restaurant on restaurant.rest_id=belongs.rest_id where username = req.session.username;
- select * from order_details inner join food on food.food_id=order_details.food_id where order_details.order_id=order_id
- SELECT * FROM account WHERE username = username AND password = password;
- insert into account values(username,password)
- insert into customers values(?,?,?,?)",[username,firstname,lastname,phone]
- select * from customers inner join account on customers.username=account.username where customers.username = ?",[req.session.username]
- update customers set first_name=?,last_name=?,phone=? where username=?",[firstname,lastname,phone,username]
- "update account set password=? where username=?",[password,username]
Implementation Of the Above Database
Web Pages
- Homepage
- Profile Page
- Invoice:
- Contact:
- Menu:
- My Orders Page:
--Thank You--