SQL Project - Restaurant Order Management Database

SQL
Author

Boon Chin Look

Published

November 20, 2023

SQL Code File

This project was developed as part of an 8-week introduction to the structured query language (SQL) course hosted by Girls Code First. Throughout the course, I built foundational SQL skills through hands-on learning and completed an individual assignment that covered topics such as:

  • SQL coding and database management techniques.

  • Data analysis and manipulation

  • Working with complex data structures

  • Database design and data visualization

  • Project presentation and reporting

Final Project: SQL ERD – Restaurant Order Management

For my final project, I designed a relational database centred around restaurant order management. This involved implementing key database concepts such as primary and foreign keys, and developing an Enhanced Entity-Relationship (EER) diagram to visually represent the system structure.

The project gave me the opportunity to apply theoretical concepts in a real-world scenario, significantly enhancing both my technical proficiency and confidence in structured data management. Below, you can find code snippets used to create the database tables and their relationships.

-- Creates the restaurant Database
Create Database restaurant;
-- Creates the Customer table
CREATE TABLE Customer (
    custid INT PRIMARY KEY,
    cust_firstname VARCHAR(50) NOT NULL,
    cust_lastname VARCHAR(50) NOT NULL
);
-- Selects all values from the Customers table within the restaurant database
SELECT * FROM restaurant.customer;
-- Insert values into the Customer table
INSERT INTO Customer (custid, cust_firstname, cust_lastname)
VALUES
    (1, 'John', 'Soda'),
    (2, 'Jane', 'Smith'),
    (3, 'Michael', 'Johnson'),
    (4, 'Emily', 'Brown'),
    (5, 'Robert', 'Wilson');
-- Create the Cashier table
CREATE TABLE Cashier (
    cashier_id INT PRIMARY KEY,
    cashier_name VARCHAR(50) NOT NULL,
    hourly_wage DECIMAL(10, 2) NOT NULL
);
-- Create the Cashier table
CREATE TABLE Cashier (
    cashier_id INT PRIMARY KEY,
    cashier_name VARCHAR(50) NOT NULL,
    hourly_wage DECIMAL(10, 2) NOT NULL
);
-- Selects all values from the Cashier table within the restaurant database
SELECT * FROM restaurant.cashier; 
-- Insert values into the Cashier table
INSERT INTO Cashier (cashier_id, cashier_name, hourly_wage)
VALUES
    (1, 'Jay', 10),
    (2, 'Samuel', 10.25),
    (3, 'Noel', 11),
    (4, 'Jill', 10.25),
    (5, 'Philip', 12);
-- Create the Address table
CREATE TABLE Address (
    address_id INT PRIMARY KEY,
    delivery_address1 VARCHAR(200) NOT NULL,
    delivery_address2 VARCHAR(200),
    delivery_city VARCHAR(50) NOT NULL,
    delivery_eircode VARCHAR(10) NOT NULL
);
-- Selects all values from the Address table within the restaurant database
SELECT * FROM restaurant.address;
-- Insert values into the Address table
INSERT INTO Address (address_id, delivery_address1, delivery_address2, delivery_city, delivery_eircode)
VALUES
    (1, '123 Main Street', NULL, 'City A', 'EIR123'),
    (2, '456 Elm Street', 'Apt 101', 'City B', 'EIR456'),
    (3, '789 Oak Street', NULL, 'City C', 'EIR789'),
    (4, '321 Pine Street', 'Suite 5', 'City D', 'EIR321'),
    (5, '654 Birch Street', 'Apt 202', 'City E', 'EIR654');
-- Create the Item table
CREATE TABLE Item (
    itemID INT PRIMARY KEY,
    cashier_id INT NOT NULL,
    custid INT NOT NULL,
    address_id INT NOT NULL,
    totalcost decimal(5, 2) NOT NULL,
    FOREIGN KEY (cashier_id) REFERENCES Cashier(cashier_id),
    FOREIGN KEY (custid) REFERENCES Customer(custid),
    FOREIGN KEY (address_id) REFERENCES Address(address_id)
);
-- Selects all values from the Item table within the restaurant database
SELECT * FROM restaurant.item;
-- Insert values into the Item table
INSERT INTO Item (itemID, cashier_id, custid, address_id, totalcost)
VALUES
    (1, 3, 1, 1, 25.50),
    (2, 1, 2, 2, 30.75),
    (3, 1, 3, 3, 18.90),
    (4, 2, 4, 4, 42.25),
    (5, 1, 5, 5, 22.60);
-- Create the Mealone table
CREATE TABLE Mealone (
    itemID INT PRIMARY KEY,
    Fruit_Cocktail VARCHAR(50),
    FOREIGN KEY (itemID) REFERENCES Item(itemID)
);
-- Selects all values from the Mealone table within the restaurant database
SELECT * FROM restaurant.mealone;
-- Insert values into the Mealone table
INSERT INTO Mealone (itemID, Fruit_Cocktail)
VALUES
    (1, 'Tropical Fruit Cocktail'),
    (2, 'Mixed Berry Cocktail'),
    (3, 'Pineapple and Mango Cocktail'),
    (4, 'Exotic Fruit Delight'),
    (5, 'Watermelon and Cantaloupe');
-- Create the Mealtwo table
CREATE TABLE Mealtwo (
    itemID INT PRIMARY KEY,
    Fruits VARCHAR(50),
    FOREIGN KEY (itemID) REFERENCES Item(itemID)
);
-- Selects all values from the Mealtwo table within the restaurant database
SELECT * FROM restaurant.mealtwo;
-- Insert values into the Mealtwo table
INSERT INTO Mealtwo (itemID, Fruits)
VALUES
    (1, 'Tropical Fruit Cocktail'),
    (2, 'Strawberries and Kiwi'),
    (3, 'Bananas and Grapes'),
    (4, 'Watermelon and Cantaloupe'),
    (5, 'Exotic Fruit Delight');
-- Create the Mealthree table
CREATE TABLE Mealthree (
    itemID INT PRIMARY KEY,
    Fruitsalad VARCHAR(50),
    FOREIGN KEY (itemID) REFERENCES Item(itemID)
);
-- Selects all values from the Mealthree table within the restaurant database
SELECT * FROM restaurant.mealthree;
-- Insert values into the Mealthree table
INSERT INTO Mealthree (itemID, Fruitsalad)
VALUES
    (1, 'Tropical Fruit Cocktail'),
    (2, 'Tropical Fruitsalad'),
    (3, 'Citrus Fruitsalad'),
    (4, 'Citrus Fruit Medley'),
    (5, 'Berry Fruitsalad');
Back to top