The core idea of this project is creating a well-designed and fully detailed database for a big restaurant franchise, including every detail possible for the database to get close to how it would look like in the real world at a real franchise.
The project includes the following:
- The Entity Relationship Diagram (ERD) of the database.
- The Relational Schema of the database.
- The SQL implementation of the database, including the tables, the relationships, the constraints, the inserts, the queries, and the views.
- The SQL implementation of 18 reports that can be run on the database.
The database includes the following features:
- Tracking the shipments for each branch, including the date, the supplier, the ingredients in the shipment, its quantity and price.
- Tracking which ingredients are used in each dish, the quantity of each ingredient used in each dish, and the stock of each ingredient in each branch and which suppliers provide each ingredient.
- Tracking which menu items are served in each branch, the price of each item, the tools needed to prepare each item, and the ingredients needed to prepare each item.
- Tracking the tools available and their quantity in each branch, the tools needed to prepare each menu item, and which chefs are qualified to use each tool.
- Tracking the branches, the employees working in each branch, the tables in each branch, the reservations for tables, the orders for each table, the menu items ordered in each order, and the chefs who prepared each order.
- Tracking the orders for each branch:
- Delivery: The delivery orders, the driver who delivered each order, the delivery time, and the delivery address.
- Reservations: The reservations for each branch, the tables reserved, the reservation time, and the customer who made the reservation and the number of guests.
- Transactions: The transactions for each order, the payment method, the total amount, and the cashier who processed the transaction.
- Creating offers with various types:
- General for all branches or specific to some branches.
- Discount on all items or specific items.
- Tracking the customers, their orders, their reservations, and their feedback.
- Tracking the employees, their salaries, roles, the branches they work in, and additional data depending on their role. They can be chefs, drivers, cashiers, or managers.
- The chefs can prepare the menu items, the drivers can deliver the orders, the cashiers can process the transactions, and the managers can manage the branches.
For Customers:
- Branch addresses and phone numbers.
- Empty tables in each branch.
- History of orders for the customer.
- Menu items prices after applying the offers.
- Menu items available in a certain branch.
For Chefs:
- All ingredients and tools needed to prepare the items of a certain order.
For Salespersons:
- Most ordered items.
- Items frequently ordered together.
For Branch Managers:
- Average feedback points of each delivery driver.
- Which tools are the most used in each branch.
- Tables with most reservations in each branch.
For the Franchise Owner:
- Revenue of each branch.
- Employees of all branches.
- All the ingredients bought by the branches in a certain period.
- The total profit of the franchise in a certain period.
- Suppliers who provided the ingredients for a certain branch.
The project consists of:
- 18 entities
- 26 relationships
- Total of 35 relations (tables)
Schema statistics:
- 2,000+ lines of SQL commands
- 36 CREATE TABLE
- 35 ALTER TABLE
- 39 INSERT INTO (for a total of 700+ rows)
- 20 SELECT
- 10 VIEWS
Reports and Data Analysis statistics:
- 18 reports, each with a SQL implementation and a screenshot of the output.
The GitHub is divided into 3 main folders:
-
diagrams: the diagrams save data of the tool we use.EER_diagram.drawio: the Entity Relationship Diagram of the database.Relational_model.drawio: the Relational Schema of the database.
-
database: the SQL implementations, divided into:reports: the implementation of the reports and screenshot of the output.schema: the definition of the SQL tables, the alters, inserts, query and views. Each relation is stored in a separate folder with all its related data.
-
out: contains the accumulation of all the SQL commands, to be copied and pasted into the DBMS directly to fully run the project
All the SQL commands for everything have been tested together and can be run without issues.
We created a script that accumulates all the SQL commands into a file for each type, to be copied and pasted into the DBMS directly.
The following command will generate out folder with the accumulation of all the SQL commands.
npm startThere is also a git hook that runs the command before each commit, to ensure that the project is always up-to-date.
To add the git hook, run the following command:
git config core.hooksPath .githooks1- From the out folder, run all commands inside table.sql to create the tables (copy and paste into the DBMS)
2- Run all commands inside insert.sql to insert all the rows into the tables.
3- Run all commands inside alter.sql to set up the foreign key constraints between the tables.
- We separate alters from create tables so that the sequence of creation doesn’t matter.
- We insert before altering so that the sequence of insertions doesn’t matter.
4- With this, the database is ready. You can test the queries inside query.sql, the views inside views.sql, or run reports SQL implementations from database/reports folder.