Restaurant Reservation System Database Implementation via SQL
Find the structured implementation code and inputting of data into SQL here. This database was built from an entity-relation diagram, to a relational schema, normalized, and finally implemented into SQL.
Entity-Relation Diagram and Relational Schema
To the right you can find the entity-relation diagram used to implement this database. Below you will find the relational schema mapping for the database.
Queries and Visualization: Reservations by Table Type
SELECT tabletype.TableType, COUNT(reserves.TableID) AS ReservationCount FROM reserves
LEFT JOIN tables ON reserves.TableID = tables.TableID
LEFT JOIN tabletype ON tables.TypeID = tabletype.TypeID
GROUP BY TableType;
** Visualizations made in tableau
Queries and Visualization: Staff Type Distribution
SELECT typeofemployee as TypeofEmployee, count(typeofemployee) as EmployeeCount,
reservedate as ReserveDate
FROM staff natural JOIN reserves
GROUP BY typeofemployee, reservedate
ORDER BY reservedate;
** Visualizations made in tableau
Other Queries: Customer Reward Claims and Type
SELECT
C.CustomerID,
C.FirstName,
C.LastName,
RT.TypeOfReward,
R.RewardsNumber
FROM CUSTOMER C
JOIN REWARDS R ON C.CustomerID = R.CustomerID
JOIN REWARDTYPE RT ON R.RewardID = RT.RewardID;
​
** Note customer names are randomly generated
Output:
Other Queries: Reservation date, time, table, customerID, and customer name
SELECT
C.CustomerID,
C.FirstName,
C.LastName,
R.ReserveDate,
R.ReserveTime,
T.TableID
FROM CUSTOMER C
JOIN RESERVES R ON C.CustomerID = R.CustomerID
JOIN TABLES T ON R.TableID = T.TableID;
​
** Note customer names are randomly generated
Output:
