To Prepare a Report

Restaurant Management System Database Project Report Submitted by: UROOSA RASHID Department 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 create a sustainable income stream. This project will help Pakistan Food Restaurant to earn part of the money 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 Objectives:The Restaurant Management System project will meet the following objectives: 0 It records the information of customers. 1 It records the information and quantity of orders made by customers along with date. 2 It records the price of food. 3 It will keep records of employees of the restaurant 4 It will also keep record of raw materials, sup plies and vendors. 1. 3 Project Scope:This project will keep record of all 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 not need to register at the counter; there will be already an existing record of that customer. 1. 4 List of Tables and Description :Project consists of following tables: * Customers * Orders * Orderline * Items * Raw material * Supplies * Vendors * Bills * Employees * Chef * Accountant * WaiterDescription of tables: * Customers table consists of ustomer ID, name, 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, Or der 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 Table consists of VendorID, name, contact number, status 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 accountants of the restaurant. * Waiter table consists o f the job description of the waiters. 1. 5 Project Features: Main features of the project are: * Complete Inventory from sale 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 Diagram Context Diagram Amount paid Bills Customer Order sent back Placed order Receipt passed 0 Ordering system Received order Items Available 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 Query for Table ‘customers’ : create table cus tomers(name nchar(20) not null,customer_id int not null primary key,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 key,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 money 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 A;B,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_d ate 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 foreign 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†²,'6:05:00 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(vendor_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 k ey(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†²,'11:00:00 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 salary†] And [â€Å"Ending 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 ID], 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†] An d [â€Å"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| Primary key| Foreign key| Status| Size| employee_id| int| * | | Not null| | name| nchar| | | Not null| 20| salary| money| | | Not null| | employee_address| nvarchar| | | Null| 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| Foreign 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| | | No t 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| Foreign 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_d ate| 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| |