Case Study

Restaurant Relational Database

A MySQL relational database modeling a full-service restaurant — reservations, orders, menu, inventory, payments — with an ERD, full data dictionary, and 10 managerial SQL queries.

UGA · MIST 4610 (Group Project 1) · with Adriano Onate, Maxwell Shank, Kipras Kairys, Kevin Behlke

MySQLSQLDatabase DesignERD

View GitHub Repository

Project description

This project models and builds a relational database for a full-service restaurant's daily operations. The central entity is Orders, which connects employees, tables, menu items, and payments. The Order_Items table manages the many-to-many relationship between orders / to-go orders and menu items, allowing multiple items per order. The database also tracks suppliers and ingredients to link inventory with restaurant activity. Once populated, it supports SQL queries that provide managerial insights on sales, employee performance, and inventory efficiency.

Data model

The model is based on a full-service restaurant that manages reservations, employees, menu items, and inventory in a single system.

The Customers table stores basic customer information. Each customer can make multiple Reservations (one-to-many). Every reservation links to a specific Table, which stores number, capacity, and style (Booth, High-top, Patio, or Normal); since one table can host many reservations over time, Tables and Reservations are also one-to-many.

The Employees table contains all staff details, including supervisors. Each employee can place many Orders for dine-in tables, and each table can have many orders over time — a many-to-many relationship between Employees and Tables, with Orders as the associative entity. Employees also have a self-referencing relationship, where one employee can supervise many others.

Each Order connects to Menu_Items through the associative entity Order_Items, which records every individual item in an order. This allows multiple items per order and multiple orders per menu item. Order_Items has its own primary key (OrderItemID) and a field for special requests.

The ToGoOrders table manages takeout: each to-go order links to one Customer, one Employee, and one Payment, and connects to Order_Items for its line items — mirroring the dine-in Orders structure for off-site activity. The Payments table records amount and method, with each order or to-go order linking to exactly one payment (one-to-one).

Menu_Items stores each dish's name, calories, price, and category, while Menu_Categories defines those categories (one-to-many). For the kitchen, Ingredients lists everything used in recipes and links to Suppliers (one-to-many), and the many-to-many relationship between Menu_Items and Ingredients is handled through Item_Ingredients, which specifies the quantity of each ingredient per dish.

Entity-relationship data model — Full entity-relationship diagram for the restaurant database.

Data dictionary

Field-level documentation for every table — name, type, keys, and description.

Queries

Ten managerial questions answered with SQL against the populated database. Each is stored as a procedure (CALL TP_Q1(), TP_Q2(), …).

Query 1

Which tables have a capacity of 6 or more people?

A host needs this to quickly seat larger parties of 6+.

SELECT tableNumber, capacity, style
FROM Tables
WHERE capacity >= 6

+------------- +---------- +--------- +
| tableNumber | capacity  | style    |
+------------- +---------- +--------- +
| 1           | 8         | Normal   |
| 2           | 6         | Normal   |
| 4           | 8         | High-top |
| 5           | 7         | High-top |
| 6           | 6         | Booth    |
| 7           | 6         | Patio    |
| 13          | 7         | Patio    |
| 17          | 8         | Patio    |
+------------- +---------- +--------- +
15 rows

Query 2

What are all the appetizers offered?

A manager wants to see current appetizers so staff can upsell them.

SELECT name, categoryName, price, calories
FROM Menu_Items
JOIN Menu_Categories ON Menu_Items.categoryId = Menu_Categories.categoryId
WHERE categoryName = 'Appetizer'

+---------------- +------------- +------- +---------- +
| name           | categoryName | price | calories |
+---------------- +------------- +------- +---------- +
| Cheese Sticks  | Appetizer    | 6     | 150      |
| Potato Skins   | Appetizer    | 7     | 250      |
| Blooming Onion | Appetizer    | 7     | 180      |
| Wings          | Appetizer    | 9     | 225      |
| Sliders        | Appetizer    | 13    | 300      |
+---------------- +------------- +------- +---------- +
5 rows

Query 3

What is the average payment amount across all customers?

Gives the manager insight into the average check size per table.

SELECT AVG(amount)
FROM Payments

+------------ +
| AVG(amount) |
+------------ +
| 23.4400     |
+------------ +
1 row

Query 4

Which employees have more than 3 orders, and what is their position?

Helps managers gauge productivity and see which roles submit the most orders.

SELECT firstName, lastName, title, COUNT(orderId)
FROM Employees
JOIN Orders ON Orders.employeeId = Employees.employeeId
GROUP BY firstName, lastName, title
HAVING COUNT(orderId) >= 3
ORDER BY COUNT(orderId) DESC

+----------- +---------- +------- +---------------- +
| firstName | lastName  | title | COUNT(orderId) |
+----------- +---------- +------- +---------------- +
| Chadwick  | Boseman   | Host  | 4              |
| Bing      | Jellings  | Server| 3              |
| Alvis     | Pressley  | Host  | 3              |
+----------- +---------- +------- +---------------- +
3 rows

Query 5

What are the top-ordered items?

Reveals customer preferences — popular items get prioritized, unpopular ones removed; also informs ingredient inventory.

SELECT Menu_Items.itemId, Menu_Items.name, Menu_Categories.categoryName,
       COUNT(Order_Items.itemId) AS TimesOrdered
FROM Order_Items
JOIN Menu_Items ON Order_Items.itemId = Menu_Items.itemId
JOIN Menu_Categories ON Menu_Items.categoryId = Menu_Categories.categoryId
GROUP BY Menu_Items.itemId, Menu_Items.name, Menu_Categories.categoryName
ORDER BY TimesOrdered DESC

+-------- +-------------- +------------- +-------------- +
| itemId | name         | categoryName | TimesOrdered |
+-------- +-------------- +------------- +-------------- +
| 2      | Cheeseburger | Entree       | 7            |
| 1      | Hamburger    | Entree       | 6            |
| 7      | Clam Chowder | Soup         | 4            |
| 10     | Chips        | Side         | 4            |
| 12     | Water        | Drink        | 4            |
+-------- +-------------- +------------- +-------------- +
13 rows

Query 6

What staff work under which supervisor?

Shows the reporting hierarchy — who reports to whom, top to bottom.

SELECT E.firstName AS EmpFirstName, E.lastName AS EmpLastName, E.title,
       S.firstName AS ManagerFirstName, S.lastName AS ManagerLastName, S.title
FROM Employees AS E
LEFT JOIN Employees AS S ON E.superVisor_ID = S.employeeId

+-------------- +------------- +----------------- +----------------- +
| EmpFirstName | title       | ManagerFirstName | Manager title    |
+-------------- +------------- +----------------- +----------------- +
| Jyoti        | Gen. Manager | (none)           | (none)           |
| Chelsea      | Shift Mgr    | Jyoti            | General Manager  |
| Patrizio     | Kitchen Mgr  | Jyoti            | General Manager  |
| Valery       | Cook         | Marry            | Kitchen Manager  |
| Bing         | Server       | Chelsea          | Shift Manager    |
+-------------- +------------- +----------------- +----------------- +
25 rows

Query 7

What items have never been ordered?

Flags menu items taking up inventory and a menu spot while producing no revenue.

SELECT itemId, name, price
FROM Menu_Items
WHERE NOT EXISTS (
  SELECT * FROM Order_Items WHERE Menu_Items.itemId = Order_Items.itemId
)

+-------- +---------------- +------- +
| itemId | name           | price |
+-------- +---------------- +------- +
| 11     | Shrimp Salad   | 10    |
| 15     | Blooming Onion | 7     |
| 16     | Wings          | 9     |
| 17     | Sliders        | 13    |
| 18     | Lemonade       | 2     |
+-------- +---------------- +------- +
6 rows

Query 8

What ingredients go into each item, and how are they supplied?

Lets a chef check stock for a dish, manage food cost across components, and see every supplier involved. Example dishes: Sliders, Shrimp Salad, Fries, Hamburger, Chicken Salad.

Query 9

How often is each payment method used, and how much revenue has each seen?

Surfaces cash flow and processing-fee exposure — high card revenue can justify renegotiating processor fees, and confirms gift-card redemptions work.

SELECT paymentMethod, COUNT(paymentId) AS NumberOfTransactions,
       SUM(amount) AS TotalRevenue
FROM Payments
GROUP BY paymentMethod
ORDER BY TotalRevenue DESC

+--------------- +-------------------- +------------- +
| paymentMethod | NumberOfTransactions| TotalRevenue |
+--------------- +-------------------- +------------- +
| Card          | 13                  | 337          |
| Cash          | 7                   | 162          |
| Gift Card     | 5                   | 87           |
+--------------- +-------------------- +------------- +
3 rows

Query 10

What is the most expensive item in each category?

Highlights the 'premium' item per category — often the most profitable — so a menu engineer can check whether prices are selling or deterring.

SELECT categoryName, Menu_Items.name AS ItemName, price
FROM Menu_Items
JOIN Menu_Categories ON Menu_Items.categoryId = Menu_Categories.categoryId
WHERE (Menu_Items.categoryId, price) IN (
  SELECT categoryId, MAX(price) FROM Menu_Items GROUP BY categoryId
)

+------------- +-------------- +------- +
| categoryName | ItemName     | price |
+------------- +-------------- +------- +
| Entree       | Seafood Boil | 15    |
| Appetizer    | Sliders      | 13    |
| Salad        | Shrimp Salad | 10    |
| Soup         | Clam Chowder | 8     |
| Side         | Fries        | 6     |
| Drink        | Soda         | 4     |
+------------- +-------------- +------- +
6 rows

Database

Database name: ns_ado51234. Each query above is stored as a procedure and can be run with CALL TP_Q1(), CALL TP_Q2(), and so on.