setup.sql

CREATE DATABASE specialty_coffee_db;

USE specialty_coffee_db;



-- coffee table
CREATE TABLE coffee (
                        coffee_id INT PRIMARY KEY,
                        name VARCHAR(100) NOT NULL,
                        origin_country VARCHAR(50) NOT NULL,
                        process VARCHAR(50) NOT NULL,
                        roast_level VARCHAR(20) NOT NULL
);

-- roaster table
CREATE TABLE roaster (
                         roaster_id INT PRIMARY KEY,
                         name VARCHAR(100) NOT NULL,
                         city VARCHAR(50) NOT NULL,
                         country VARCHAR(50) NOT NULL
);

-- cafe table
CREATE TABLE cafe (
                      cafe_id INT PRIMARY KEY,
                      name VARCHAR(100) NOT NULL,
                      city VARCHAR(50) NOT NULL,
                      country VARCHAR(50) NOT NULL
);

-- menu table (junction table between cafe, roaster, and coffee)
CREATE TABLE menu (
                      menu_id INT PRIMARY KEY,
                      cafe_id INT NOT NULL,
                      coffee_id INT NOT NULL,
                      roaster_id INT NOT NULL,
                      price DECIMAL(5,2) NOT NULL,
                      brew_method VARCHAR(50) NOT NULL,
                      FOREIGN KEY (cafe_id) REFERENCES cafe(cafe_id),
                      FOREIGN KEY (coffee_id) REFERENCES coffee(coffee_id),
                      FOREIGN KEY (roaster_id) REFERENCES roaster(roaster_id)
);


-- Coffee table
INSERT INTO coffee (coffee_id, name, origin_country, process, roast_level) VALUES
                                                                               (1, 'Ethiopia Guji Natural', 'Ethiopia', 'Natural', 'Light'),
                                                                               (2, 'Colombia Huila Washed', 'Colombia', 'Washed', 'Medium'),
                                                                               (3, 'Kenya Nyeri AA', 'Kenya', 'Washed', 'Light'),
                                                                               (4, 'Brazil Cerrado Pulped Natural', 'Brazil', 'Honey', 'Medium'),
                                                                               (5, 'Panama Geisha', 'Panama', 'Washed', 'Light');

-- Roaster table
INSERT INTO roaster (roaster_id, name, city, country) VALUES
                                                          (1, 'Tim Wendelboe', 'Oslo', 'Norway'),
                                                          (2, 'Square Mile Coffee', 'London', 'UK'),
                                                          (3, 'La Cabra', 'Aarhus', 'Denmark'),
                                                          (4, 'Nomad Coffee', 'Barcelona', 'Spain');

-- Cafe table
INSERT INTO cafe (cafe_id, name, city, country) VALUES
                                                    (1, 'Café de Lisa', 'Madrid', 'Spain'),
                                                    (2, 'Brew Bros', 'London', 'UK'),
                                                    (3, 'Oslo Espresso Bar', 'Oslo', 'Norway'),
                                                    (4, 'Third Wave Spot', 'Barcelona', 'Spain');
INSERT INTO cafe (cafe_id, name, city, country) VALUES
                                                    (5, 'Coffee Collective', 'Copenhagen', 'Denmark');

-- Menu table
INSERT INTO menu (menu_id, cafe_id, coffee_id, roaster_id, price, brew_method) VALUES
                                                                                   (1, 1, 1, 4, 4.50, 'V60'),
                                                                                   (2, 1, 2, 2, 4.00, 'Aeropress'),
                                                                                   (3, 1, 5, 3, 7.50, 'Kalita'),
                                                                                   (4, 2, 2, 2, 3.80, 'Espresso'),
                                                                                   (5, 2, 3, 2, 4.20, 'Chemex'),
                                                                                   (6, 3, 1, 1, 5.00, 'V60'),
                                                                                   (7, 3, 4, 1, 4.50, 'Espresso'),
                                                                                   (8, 4, 3, 3, 4.20, 'Aeropress'),
                                                                                   (9, 4, 5, 3, 8.00, 'V60'),
                                                                                   (10, 4, 2, 4, 3.90, 'Espresso');