Restaurant needs to manage table reservations, orders, menus, inventory,revenue and customer reviews. Our application will store data about customer reservations, orders, transactions, customer ratings, employee information, inventory and supplier information. Our application will be used daily to record transactions, revenue, keep track of employees, and allow the restaurant to coordinate suppliers to fill the necessary inventory to keep the restaurant running.
Following are our database design process:
- Conceptual Design
- Create Entity Relationship Diagrams (ERD) for each user type
- Build a Conceptual Data Model (CDM)
- Define Business Rules, restrictions and requirements for database
- Logical Design
- Transform CDM to a set of Third Normal Form (3NF) relations indicating all primary and foreign keys
- Physical Design
- Create Procrocess Versis Entity Matrix
- Create Composite Usage Map
- Analyze key transactions
- Implementation
- Write DDL statements to create database in SQL language
- Implement indexing and clustering using the outcome of Physical Design
- Calculate daily revenue using histograms
- Calculate daily cost using histograms
- Calculate total sales per item
This database model we structured covers the daily internal operation of most restaurants. It helps them to organize personnels to better serve customers in order to receive a satisfactory customer review; It also helps restaurant managers to coordinate with suppliers to keep track of the inventory cost in order to generate more profits.