We have created a Database for a Restaurant as a part of our Database Management System project for the Database Management System. It has been developed using Oracle SQLPLUS software. In our database, we have considered all the major functionalities that are required in a restaurant. An organized system in any restaurant makes the job a lot easier to manage everything properly.
There are lots of different kinds of data to consider here. The various food details have to be stored. We should also have a record of all the customers who come to the restaurant. We need to maintain proper ordering data too to keep track of the various food items ordered by the customer. The feedback given to the customer and the table number where they sat may also be needed for future reference. Finance is one of the most important components here which has to be maintained well too. Then come the people who work in the restaurant and provide service to their customers. All of their details should also be kept properly.
The entire Restaurant Management System project is not only about just storing the data. The process of retrieving the data also should not be troublesome. The users should be able to access it easily and the process should be fast too. So, all the related data should be interlinked which will help in these cases. Many times, the users might also want to insert new data and update or delete them too. Those cases also we have considered. Even after these modifications, the database should work perfectly. Finally, in a nutshell, our work is useful for storing all the data in an organized and efficient manner, saving valuable time, and reducing the huge paperwork.
DATA REQUIREMENTS & CONSTRAINTS
The Restaurant Management System must have the following data requirements and constraints:
Customers who come to the restaurant are identified by their customer IDs and their names, ages, phone numbers, and addresses are recorded. The customer’s name should always be present. Customers give orders. Each Order is identified by order ID and has food ordered, bill no., and date. Bill no. and table no. should be unique. The maximum number of food items that can be ordered is 20. Payment has to be done for the order. Payment is identified by payment ID, it also contains the mode of payment (Cash, Debit card, Credit card, and UPI), total amount, amounts received, amounts returned, and date.
The total amount should be equal to the amount received – the amount returned and should always be greater than zero. There are three kinds of staff members in the Restaurant – Manager, Chef, and Waiter. Every staff member is identified by staff ID and has a name, date of birth, contact number, address, and salary. Staff name and contact should be present for every staff. Waiters’ salaries should be greater than ₹100. The chef’s salary should be greater than ₹500. The manager’s salary should be greater than ₹1000.
The chef cooks the food and the waiter takes and serves the orders. The order consists of varieties of food. Every food is identified by its food ID, it also has a name, description, and price. The price should be greater than ₹100 and lesser than ₹1000. Food names should be present for every food item. Customers are at tables. Tables are identified by table number and have several seats. Several seats should be more than 4. The customer gives feedback. Each feedback is identified by a Feedback ID and contains ratings (1-5) and comments.
FUNCTIONAL REQUIREMENTS
Insertion of Data
1. Details of customers are stored.
2. Details of the orders placed are stored.
3. Details of the dishes are stored.
4. Details of the tables are stored.
5. Details of the payments done are stored.
6. Feedback given by customers is also stored.
7. Details of the staff (manager, chef, waiter) of the restaurant are stored.
8. Details of which cooked food is stored
9. Details of which waiter served which order are stored
10. Details of which dishes are ordered in a particular order are stored
Removal of old data
1. A customer decides to delete his order on a particular date. So by his id and date of order, the staff id, food id, and order id should be deleted.
2. A customer decides to delete a food item from his order. So its food id needs to be deleted.
3. An order has been placed to cook which gets rejected at the last moment. So its food id for cook needs to be deleted.
Modification of data
1. A customer has changed his address. So, his address needs to be updated for delivery of his order by order_id.
2. The amount of payment needs to be updated for an order
3. A customer decides to change his feedback on the services of the restaurant
4. A customer decides to change his mobile number at the time of delivery of an order
Data Retrieval
1. Display the name of the customer who gave the order on the entered date.
2. Display the list of all the bills no for a particular food
3. Display the rating given by a particular customer
4. Display the name of the customer who did give a particular comment and rating
5. Display the table_no where a particular order was served