To Prepare a Report

Restaurant Management System Database range Report Submitted by UROOSA RASHID division of Computer Science and Information Technology Jinnah University for Women 5-C Nazimabad, Karachi 74600 1. Introduction 1. 1 Project Overview This paper outlines a project proposal from the Pakistan Food Restaurant to frame a sustainable income stream. This project leave alone help Pakistan Food Restaurant to earn part of the property to support and ensure its future. It is being undertaken by us because it covers various aspects of restaurant management system and makes it easy to access the information about the restaurant management system. . 2 Aims and ObjectivesThe Restaurant Management System project bequeath meet the following objectives 0 It records the information of guests. 1 It records the information and quantity of governs made by customers along with date. 2 It records the price of food. 3 It will observe records of employees of the restaurant 4 It will also keep record of raw materials, supplies and vendors. 1. 3 Project ScopeThis project will keep record of tout ensemble food items of the restaurant. It also records the information of customers along with all the orders made by them. The orders are recorded according to the date.Computerized receipt is given to the customer with the details of the order and bill. Whenever a customer comes first time then he/she must register him/her self at the counter. Next time the customer comes he/she will non need to register at the counter there will be already an existing record of that customer. 1. 4 List of put overs and Description Project consists of following defers * Customers * beau mondes * Orderline * Items * Raw material * Supplies * Vendors * Bills * Employees * Chef * Accountant * WaiterDescription of tables * Customers table consists of ustomer ID, take a shit, Address, Last visit date of customer. It keeps the records of customers. * Orders table keep records of number of orders placed by customers along with OrderID, Order name, Order date, Order time. * Orderline table consists of quantity of orders. * Items table consists of list of food items along with their type, cost, ID. * Raw materials table consists of list of raw materials used to make food items along with Material ID, type, Expiry date and Stock date. * Supplies table consists of Quantity, Supply date, Supply time and cost of raw materials provided by vendors/suppliers. Vendors turn off consists of VendorID, name, impact number, perspective of the vendors of the restaurant. * Bills table consists of Receipt no. , cash received, items purchased and total amount of the customers. * Employees table consist of the complete information of the employees of the restaurant. It consists of employee ID, name, address, postal code, contact number, salary, hire date of the employees. * Chef table consists of the speciality of the chefs of the restaurant. * Accountants table consists of the qualification of the ac countants of the restaurant. * Waiter table consists of the job description of the waiters. 1. 5 Project Features Main features of the project are * Complete Inventory from sales agreement of food items and drinks. * Details of purchases are also maintained. * Reports for all items in stock as well as items to be ordered. * Complete information of employees along with their contact number and ID. * Details of previous orders by a customer. * Computerized receipt generation process. 2. System Design 2. 1 Data Flow draw Context Diagram Amount paid Bills Customer Order sent back Placed order Receipt passed 0 Ordering system Received order Items addressable Placing order 1. 0Choosing from items menu Level 0 Items Customer Order report Availability of orders 2. 0 Processing order Bills details to customers Payment by customer Payment process 3. 0 Payment for the order Payment receipt and order Bills 3. ER-Diagram 4. Snapshots 5. SQL Portion create database Restaurant use Restaurant 5. 1 motion for Table customers create table customers(name n combust(20) non null,customer_id int not null primary primordial,last_visit_date int not null,address varchar(10) null) insert into customers(customer_id,name,address,last_visit_date) values(1,Ahmed Ali,6th Floor,Ambadeep Building,No. 4, K. G. Marg, New Delhi -110001,4/29/2009) 5. 2 Query for Table employees create table employees(employee_id int not null primary fundamental,name nchar(20) not null,hire_date datetime not null,postal_code int not null,employee_address nvarchar(30) null,contact_no int null,salary specie not null,designation char(20) not null) insert into employees(employee_id,name, salary,employee_address, hire_date,postal_code, contact_no) values(1,Abdul Baseer,5,000. 00,AJC Bose Road, Business Tower, 7th Floor,Block AB,Kolkata-700017,8/5/2008,75200,021-35835956) . 3 Query for Table orders create table orders(order_id int not null primary key,number_of_orders int not null,order_date datetime not null ,order_time datetime not null,total_cost money not null,customer_id int not null,employee_id int not null,constraint cust_id external key(customer_id) references customers(customer_id), constraint emp_id foreign key(employee_id) references employees(employee_id)) insert into orders(order_id,number_of_orders,order_date,order_time,total_cost) values(1,1,7/20/2010,60500 PM,250) 5. 4 Query for Table items reate table items(item_id int not null primary key,item_type nchar(10) not null,item_name nchar(20) not null,cost money not null) insert into items(item_id,item_name,item_type,cost) values(1,Chicken Corn soup,Soup,$110. 00) 5. 5 Query for Table bills create table bills(receipt_no int not null,items_purchased int not null,total_amount money not null,cash_received money not null,cash_returned money not null) insert intobills(receipt_no,items_purchased,total_amount, cash_received, cash_returned) values(1,4,$100. 0,$100. 00,$0. 00) 5. 6 Query for Table vendors create table vendors(ven dor_id int not null primary key,name nchar(10) not null,vendor_status nchar(20) not null 5. 7 Query for Table raw_material create table raw_material(material_id int not null primary key,material_name char(25) not null,material_type char(20) not null,quantity int not null,cost money not null,expirydate datetime not null,stock_date datetime not null,item_id int not null, constraint it_id foreign key(item_id) references items(item_id)) nsert into raw_material(material_id,material_name,expiry_date,material_type, stock_date) values(1,Ground beef,5/16/2013,MEAT,7/31/2012) 5. 8 Query for Table supplies create table supplies(supply_date datetime not null,supply_time datetime not null,quantity nchar not null,cost money not null,material_id int not null,vendor_id int not null,constraint mt_id foreign key(material_id) references raw_material(material_id),constraint v_id foreign key(vendor_id) references vendors(vendor_id)) insert into supplies(supply_date,supply_time,quantity,cost) alues(4/1 /2012,110000 AM,40,$10,000. 00) 5. 9 Query for Table order_line create table order_line(quantity int not null,order_id int not null,item_id int not null,constraint od_id foreign key(order_id) references orders(order_id),constraint itm_id foreign key(item_id) references items(item_id)) 6. Ms Access Portion Queries 6. 1 Receipt query SELECT Bills. Receipt number, Bills. Items purchased, Bills. Total Amount, Bills. Cash Received, Bills. Cash Returned, Bills. Customer ID, Bills. A_employee no FROM BillsWHERE (((Bills. Customer ID)=Enter Customer ID)) 6. 2 Employees query SELECT employees. Employee ID, employees. Name, employees. Salary, employees. Address, employees. Hire date, employees. Postal code, employees. contact number FROM employees WHERE (((employees. Salary) Between starting time salary And outcome Salary)) 6. 3 Orders of customers query SELECT customers. Customer ID AS customers_Customer ID, customers. Name, customers. address, customers. Last visit date, orders. Order I D, orders. number of orders, orders. order date, orders. order time, orders. Customer ID AS orders_Customer ID, orders. Employee ID FROM customers INNER JOIN orders ON customers. Customer ID = orders. Customer ID WHERE (((customers. Name)=Enter name)) 6. 4 Items query SELECT Items. Item ID, Items. Name, Items. Type, Items. Cost FROM Items WHERE (((Items. Type)=Enter type of item)) 6. 5 Orders query SELECT orders. Order ID, orders. number of orders, orders. order date, orders. order time, orders. Customer ID, orders. Employee ID FROM orders WHERE (((orders. order date) Between Starting date And Ending date)) 6. 6 Invoice query SELECT Supplies. Cost, Supplies. Quantity(kg), Supplies. Supply date, Supplies. Supply time, Supplies. Vendor ID, Supplies. Material ID, Quantity(kg)*Cost AS total FROM Supplies 7. Data Dictionary 7. 1 Table Employees Attributes Data types particular key outside(prenominal) key positioning Size employee_id int * non null name nchar Not null 20 salary m oney Not null employee_address nvarchar nada 30 hire date datetime Not null ostal_code int Not null contact_no int Null designation nchar Not null 20 7. 2 Table customers Attributes Data types Primary key external key Status Size customer_id int * Not null name nchar Not null 20 last_visit_date int Not null Address varchar Null 10 7. 3 Table orders Attributes Data types Primary key Foreign key Status Size order_id int * Not null number_of_orders int Not null order_date datetime Not null order_time datetime Not null otal_cost money Not null customer_id int * Not null employee_id int * Not null 7. 4 Table bills Attributes Data types Primary key Foreign key Status Size receipt_no int * Not null items_purchased int Not null total_amount money Not null cash_received money Not null cash_returned money Not null Customer_ID int * Not null A_employee no int * Not null 7. 5 Table Items Attributes Data types Primary key Fore ign key Status Size item_id int * Not null tem_type nchar Not null 10 item_name nchar Not null 20 cost money Not null 7. 6 Table raw materials Attributes Data types Primary key Foreign key Status Size material_id Int * Not null material_name char Not null 25 material_type char Not null 20 expirydate datetime Not null stock_date datetime Not null item_id int * Not null 7. 7 Table vendors Attributes Data types Primary key Foreign key Status Size vendor_id int * Not null name nchar Not null 10 endor_status nchar Not null 20 contact_no int null 7. 8 Table supplies Attributes Data types Primary key Foreign key Status Size supply_date datetime Not null supply_time datetime Not null quantity nchar Not null 10 cost money Not null material_id int * Not null vendor_id int * Not null 7. 9 Table orderline Attributes Data types Primary key Foreign key Status Size quantity int Not null order_id int * Not null item_id int * Not null