E-R model
E-R modeling has been built in a perfect way in the way to create accurate techniques and this has been created by using draw.io. The description for the specific column has been created for understanding all the relationships between the data and entities. This is created for making the development of the restaurant database system and all the perfect analysis has been done and each of the tables is connected to each other with slime-specific criteria that have been portrayed here in this diagram (Kraska et al. 2021).
Figure 1: E-R diagram
(Source: Self-developed)
Relational schema and normalization
Relational schema
The relation schema has been created for understanding all the table’s connections so that the development and the enhancement have been perfectly created and which table is connected to which table that has been perfectly visualized. The scheme has been used here for understanding all the relations of each table (Priebe et al. 2018). Here indexes, as well as the domains as well as the user stored along with the particular enterprise, have been solved.
Figure 2: Relational schema
(Source: Self-developed)
Normalization
There are three types of normalization and by the use of 1NF as well as 2N F along with 3NF all the perfect implications have been processed for understanding all the form of normalization.
Customer table
| Customer_id | name | address | Mail_address | Contact no |
| 1 | JOY | LONDON | joy@gmail.com | 34254356,
4364576567 |
| 2 | MAX | MANCHESTER | max@gmail.com | 567867879 |
1 NF
| Customer_id | name | address | Mail_address | Contact no |
| 1 | JOY | LONDON | joy@gmail.com | 34254356 |
| 1 | JOY | LONDON | joy@gmail.com | 4364576567 |
| 2 | MAX | MANCHESTER | max@gmail.com | 567867879 |
2 NF
| Customer_id | name |
| 1 | JOY |
| 1 | JOY |
| 2 | MAX |
| Customer_id | address | Mail_address | Contact no |
| 1 | LONDON | joy@gmail.com | 34254356 |
| 1 | LONDON | joy@gmail.com | 4364576567 |
| 2 | MANCHESTER | max@gmail.com | 567867879 |
3 NF
| Customer_id | name |
| 1 | JOY |
| 1 | JOY |
| 2 | MAX |
| Customer_id | address |
| 1 | LONDON |
| 1 | LONDON |
| 2 | MANCHESTER |
| Customer_id | Mail_address | Contact no |
| 1 | joy@gmail.com | 34254356 |
| 1 | joy@gmail.com | 4364576567 |
| 2 | max@gmail.com | 567867879 |
Restuarant table
| Restuarant_id | MAIL_ID | CONTACT_NUMBER | ADDRESS | NAME |
| 3 | oven_heaven@gmail.com | 5657564526548, 657381736879 | london | Oven heaven |
| 4 | bong@gmail.com | 6547683672938 | Manchester | Bong |
1 NF
| Restuarant_id | MAIL_ID | CONTACT_NUMBER | ADDRESS | NAME |
| 3 | oven_heaven@gmail.com | 657381736879 | london | Oven heaven |
| 3 | oven_heaven@gmail.com | 5657564526548 | london | Oven heaven |
| 4 | bong@gmail.com | 6547683672938 | Manchester | Bong |
2 NF
| Restuarant_id | MAIL_ID |
| 3 | oven_heaven@gmail.com |
| 3 | oven_heaven@gmail.com |
| 4 | bong@gmail.com |
| Restuarant_id | CONTACT_NUMBER | ADDRESS | NAME |
| 3 | 657381736879 | london | Oven heaven |
| 3 | 5657564526548 | london | Oven heaven |
| 4 | 6547683672938 | Manchester | Bong |
3NF
| Restuarant_id | MAIL_ID |
| 3 | oven_heaven@gmail.com |
| 3 | oven_heaven@gmail.com |
| 4 | bong@gmail.com |
| Restuarant_id | CONTACT_NUMBER |
| 3 | 657381736879 |
| 3 | 5657564526548 |
| 4 | 6547683672938 |
| Restuarant_id | ADDRESS | NAME |
| 3 | london | Oven heaven |
| 3 | london | Oven heaven |
| 4 | Manchester | Bong |
Feedback table
| Feeedback_id | food_quality | suggestions | service | cleanliness |
| 5 | good | It could be better | best | yes |
| 6 | Good | It is fine | best | yes |
2 NF
| Feeedback_id | food_quality |
| 5 | good |
| 6 | Good |
| Feeedback_id | suggestions | service | cleanliness |
| 5 | It could be better | best | yes |
| 6 | It is fine | best | yes |
3 NF
| Feeedback_id | food_quality |
| 5 | good |
| 6 | Good |
| Feeedback_id | suggestions |
| 5 | It could be better |
| 6 | It is fine |
| Feeedback_id | service | cleanliness |
| 5 | best | yes |
| 6 | best | yes |
Item table
| item_id | price | description | quality |
| 7 | 600 | Dinner,
lunch |
good |
| 8 | 800 | breakfast | medium |
1 NF
| item_id | price | description | quality |
| 7 | 600 | lunch | good |
| 7 | 600 | Dinner | good |
| 8 | 800 | breakfast | medium |
2 NF
| item_id | price |
| 7 | 600 |
| 7 | 600 |
| 8 | 800 |
| item_id | description | quality |
| 7 | lunch | good |
| 7 | Dinner | good |
| 8 | breakfast | medium |
3NF
| item_id | price |
| 7 | 600 |
| 7 | 600 |
| 8 | 800 |
| item_id | description |
| 7 | lunch |
| 7 | Dinner |
| 8 | breakfast |
| item_id | quality |
| 7 | good |
| 7 | good |
| 8 | medium |
Logical models
The logical model has been built in the way to create the understanding of the perfect segmentation so that the relation of one-to-many as well as many-to-many and all other relations are perfectly created by the design.
Figure 3: Logical model
(Source: Self-developed)
Physical model
The physical model has been built effectively by the user of the perfect and accurate models which are created by the creation of different tables in restaurants. There are various tables linking restaurant and customer and chef and manager and food and order etc. All these tables have the proper and specific keys in each table which have been used as the primary key of those tables which are used for the identification of the values or data which are required. Here the foreign keys are used as the reference key which is used for making the connection of various tables so that the retrieving, as well as the updating of the data, has been properly created. Here the relational types of the data have been perfectly utilized for storing all the data and using the development of the database (Wang and Siau, 2019). All these tables are created by using various effective technologies and approaches and here all the data has been inserted by using insert queries. Here My SQL Workbench has been used in an accurate way to create the best model in the terms of making the database development.
Customer table
| CREATE TABLE `resturent`.`customer` (
`customer_id` INT NOT NULL, `name` VARCHAR(45) NOT NULL, `address` VARCHAR(45) NOT NULL, `mail_address` VARCHAR(45) NOT NULL, `contact_no` VARCHAR(45) NOT NULL, PRIMARY KEY (`customer_id`)); |
Figure 4: Creating customer table
(Source: Self-developed)
Manager table
| CREATE TABLE `resturent`.`manager` (
`manager_id` INT NOT NULL, `name` VARCHAR(45) NOT NULL, `contact_number` VARCHAR(45) NOT NULL, PRIMARY KEY (`manager_id`)); |
Figure 5: Creating manager table
(Source: Self-developed)
Restaurant table
| CREATE TABLE `resturent`.`resturent` (
`resturent_id` INT NOT NULL, `mail_id` VARCHAR(45) NOT NULL, `contact_number` VARCHAR(45) NOT NULL, `address` VARCHAR(45) NOT NULL, `name` VARCHAR(45) NOT NULL, PRIMARY KEY (`resturent_id`)); |
Figure 6: Creating restaurant table
(Source: Self-developed)
Creating cheff table
| CREATE TABLE `resturent`.`cheff` (
`cheff_id` INT NOT NULL, `name` VARCHAR(45) NOT NULL, PRIMARY KEY (`cheff_id`)); |
Figure 7: Creating cheff table
(Source: Self-developed)
Creating item table
| CREATE TABLE `resturent`.`item` (
`item_id` INT NOT NULL, `price` VARCHAR(45) NOT NULL, `description` VARCHAR(45) NOT NULL, `quality` VARCHAR(45) NOT NULL, PRIMARY KEY (`item_id`)); |
Figure 7: Creating item table
(Source: Self-developed)
Creating cashier table
| CREATE TABLE `resturent`.`cashier` (
`cashier_id` INT NOT NULL, `name` VARCHAR(45) NOT NULL, PRIMARY KEY (`cashier_id`)); |
Figure 8: Creating cashier table
(Source: Self-developed)
Creating order table
| CREATE TABLE `resturent`.`order` (
`order_id` INT NOT NULL, `number_of_order` VARCHAR(45) NOT NULL, PRIMARY KEY (`order_id`)); |
Figure 9: Creating order table
(Source: Self-developed)
Creating bill table
| CREATE TABLE `resturent`.`bill` (
`bill_id` INT NOT NULL, `price` VARCHAR(45) NOT NULL, PRIMARY KEY (`bill_id`)); |
Figure 10: Creating bill table
(Source: Self-developed)
Creating feedback table
| CREATE TABLE `resturent`.`feedback` (
`feedback_id` INT NOT NULL, `food_quality` VARCHAR(45) NOT NULL, `suggestions` VARCHAR(45) NOT NULL, `service` VARCHAR(45) NOT NULL, `cleanliness` VARCHAR(45) NOT NULL, PRIMARY KEY (`feedback_id`)); |
Figure 11: Creating feedback table
(Source: Self-developed)
Data
After the creation of tables each of the tables has been used and the values or the data has been inserted into those tables for securing this data more accurately. The foreign keys of the table connect all the tables so that relation of the data as well as the tables can be understood. The table has been properly saved and all the operation has been properly evaluated by using all these data from the table. The development of this application has been made in order to create the best application where all the data are inserted into the tables (Raghu and Johannes, 2022). The definition and the creation of the table are used as the management so that this can be used in the DBMS for securing the data and the backup of the data aha been properly done by effective techniques which are used in the areas of making the data saved in these areas. The accessing of the data has been properly done by the enhancement of this system perfectly. The DBMS is an effective application that has been accurately produced for the restaurant in a way to create the perfect segmentation for improvement.
Insert bill table
| INSERT INTO `resturent`.`bill` (`bill_id`, `price`) VALUES (’10’, ‘1000’);
INSERT INTO `resturent`.`bill` (`bill_id`, `price`) VALUES (’11’, ‘2000’); INSERT INTO `resturent`.`bill` (`bill_id`, `price`) VALUES (’12’, ‘3000’); |
Figure 12: Creating feedback table
(Source: Self-developed)
Insert cashier table
| INSERT INTO `resturent`.`cashier` (`cashier_id`, `name`) VALUES (’20’, ‘max’);
INSERT INTO `resturent`.`cashier` (`cashier_id`, `name`) VALUES (’21’, ‘jack’); INSERT INTO `resturent`.`cashier` (`cashier_id`, `name`) VALUES (’22’, ‘harry’); |
Figure 13: Creating cashier table
(Source: Self-developed)
Insert cheff table
| INSERT INTO `resturent`.`cheff` (`cheff_id`, `name`) VALUES (’30’, ‘harry’);
INSERT INTO `resturent`.`cheff` (`cheff_id`, `name`) VALUES (’31’, ‘leo’); INSERT INTO `resturent`.`cheff` (`cheff_id`, `name`) VALUES (’32’, ‘olivia’); |
Figure 14: Creating cheff table
(Source: Self-developed)
Insert Customer table
| INSERT INTO `resturent`.`customer` (`customer_id`, `name`, `address`, `mail_address`, `contact_no`) VALUES (’40’, ‘harry’, ‘london’, ‘harry@gmail.com’, ‘78965489’);
INSERT INTO `resturent`.`customer` (`customer_id`, `name`, `address`, `mail_address`, `contact_no`) VALUES (’41’, ‘leo’, ‘manchester’, ‘leo@gmail.com’, ‘45474656’); INSERT INTO `resturent`.`customer` (`customer_id`, `name`, `address`, `mail_address`, `contact_no`) VALUES (’42’, ‘max’, ‘london’, ‘max@gmail.com’, ‘78787099’); |
Figure 15: Inserting customer table
(Source: Self-developed)
Inserting feedback table
| INSERT INTO `resturent`.`feedback` (`feedback_id`, `food_quality`, `suggestions`, `service`, `cleanliness`) VALUES (’50’, ‘good’, ‘it could be better’, ‘good’, ‘yes’);
INSERT INTO `resturent`.`feedback` (`feedback_id`, `food_quality`, `suggestions`, `service`, `cleanliness`) VALUES (’51’, ‘average’, ‘it has to be better’, ‘good’, ‘no’); INSERT INTO `resturent`.`feedback` (`feedback_id`, `food_quality`, `suggestions`, `service`, `cleanliness`) VALUES (’52’, ‘medium’, ‘the quality has been improved’, ‘medium’, ‘no’); |
Figure 16: Inserting feedback table
(Source: Self-developed)
Inserting item table
| INSERT INTO `resturent`.`item` (`item_id`, `price`, `description`, `quality`) VALUES (’60’, ‘1000’, ‘dinner’, ‘good’);
INSERT INTO `resturent`.`item` (`item_id`, `price`, `description`, `quality`) VALUES (’61’, ‘500’, ‘lunch’, ‘best’); INSERT INTO `resturent`.`item` (`item_id`, `price`, `description`, `quality`) VALUES (’62’, ‘800’, ‘breakfast’, ‘average’); |
Figure 17: Inserting item table
(Source: Self-developed)
Inserting manager table
| INSERT INTO `resturent`.`manager` (`manager_id`, `name`, `contact_number`) VALUES (’70’, ‘harry’, ‘4547876’);
INSERT INTO `resturent`.`manager` (`manager_id`, `name`, `contact_number`) VALUES (’71’, ‘max’, ‘55687867’); INSERT INTO `resturent`.`manager` (`manager_id`, `name`, `contact_number`) VALUES (’72’, ‘jones’, ‘56586586’); |
Figure 17: Inserting manager table
(Source: Self-developed)
Inserting manager table
| INSERT INTO `resturent`.`order` (`order_id`, `number_of_order`) VALUES (’80’, ‘3’);
INSERT INTO `resturent`.`order` (`order_id`, `number_of_order`) VALUES (’81’, ‘4’); INSERT INTO `resturent`.`order` (`order_id`, `number_of_order`) VALUES (’82’, ‘2’); |
Figure 18: Inserting order table
(Source: Self-developed)
Inserting restaurant table
| INSERT INTO `resturent`.`resturent` (`resturent_id`, `mail_id`, `contact_number`, `address`, `name`) VALUES (’90’, ‘ax@yahoo.com’, ‘4547657’, ‘london’, ‘max’);
INSERT INTO `resturent`.`resturent` (`resturent_id`, `mail_id`, `contact_number`, `address`, `name`) VALUES (’91’, ‘james@yahoo.com’, ‘4567656’, ‘london’, ‘james’); INSERT INTO `resturent`.`resturent` (`resturent_id`, `mail_id`, `contact_number`, `address`, `name`) VALUES (’92’, ‘lio@yahoo.com’, ‘21341443’, ‘manchester’, ‘lio’); |
Figure 19: Inserting resturent table
(Source: Self-developed)
Constraints order table
| ALTER TABLE `resturent`.`feedback`
ADD INDEX `cccc_idx` (`customer_id` ASC) VISIBLE; ; ALTER TABLE `resturent`.`feedback` ADD CONSTRAINT `cccc` FOREIGN KEY (`customer_id`) REFERENCES `resturent`.`customer` (`customer_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; |
Figure 20: Constraints feedback table
(Source: Self-developed)
Transactions
The transaction of the data has been created in the system so that all the data and all the perfect queries are solved the way to create the best recommendation and all the queries ate performed for answering all the questions of the system and all the perfect implementation has been discovered in an accurate way by running all the SQL queries by using the SQL script of My SQL workbench.
Query 1
Figure 21: Inserting data query
(Source: Self-developed)
Here the insertion of the data has been done by the scripting of queries, and here INSERT statement has been used for adding the data to the customer table.
Query 2
Figure 22: Using update query
(Source: Self-developed)
The update query has been used here and by that the number for the orders for the particular order_id has been updated to 10.
Query 3
Figure 23: Using delete query
(Source: Self-developed)
Here delete query has been used for maintaining all the perfect solutions by giving of proper and accurate row from the table has been deleted by using the DELETE query, where resturent_id has been used.
Query 4
Figure 24: Select manager table
(Source: Self-developed)
The selection of the manager table has been accurately processed by selecting all data from this particular table.
Query 5:
Figure 25: Select query
(Source: Self-developed)
The select query has been used by using the specific column of the table and this has been implemented by using effective software.
Conclusion
The database management has been built here by using MYSQL WORKBENCH and all the data of the restaurant can be properly saved on tables (Warman and Ramdaniansyah, 2018). This has been developed in an accurate way to create the best and the most accurate storing of data so that all the data can be easily retrieved and the queries are performed for maintaining all the requirements. E-R diagram has been perfectly portrayed here and the relational, as well as a logical and physical model, has been built here for the enhancement of the business of restaurant.
References
Warman, I. and Ramdaniansyah, R., 2018. Analisis Perbandingan Kinerja Query Database Management System (Dbms) Antara Mysql 5.7. 16 Dan Mariadb 10.1. Jurnal Teknoif Teknik Informatika Institut Teknologi Padang, 6(1), pp.32-41.
Raghu, R. and Johannes, G., 2022. Database management systems.
Kraska, T., Alizadeh, M., Beutel, A., Chi, E.H., Ding, J., Kristo, A., Leclerc, G., Madden, S., Mao, H. and Nathan, V., 2021. Sagedb: A learned database system.
Priebe, C., Vaswani, K. and Costa, M., 2018, May. EnclaveDB: A secure database using SGX. In 2018 IEEE Symposium on Security and Privacy (SP) (pp. 264-278). IEEE.
Wang, W. and Siau, K., 2019. Artificial intelligence, machine learning, automation, robotics, future of work and future of humanity: A review and research agenda. Journal of Database Management (JDM), 30(1), pp.61-79.