82 lines
2.4 KiB
SQL
82 lines
2.4 KiB
SQL
-- =======================================================
|
|
-- USER TABLE
|
|
-- =======================================================
|
|
|
|
CREATE TABLE users(
|
|
id SERIAL PRIMARY KEY,
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
email VARCHAR(250)
|
|
);
|
|
|
|
|
|
-- =======================================================
|
|
-- MENU ITEM TABLE
|
|
-- =======================================================
|
|
|
|
|
|
CREATE TABLE menu_items(
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(250) NOT NULL,
|
|
description TEXT,
|
|
price NUMERIC NOT NULL CHECK (price > 0),
|
|
category VARCHAR(50)
|
|
);
|
|
|
|
-- =======================================================
|
|
-- ORDER TABLE
|
|
-- =======================================================
|
|
|
|
CREATE TABLE user_orders (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL,
|
|
total_price NUMERIC NOT NULL,
|
|
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
|
|
-- =======================================================
|
|
-- ORDER DETAIL TABLE
|
|
-- =======================================================
|
|
|
|
|
|
CREATE TABLE order_details(
|
|
id SERIAL PRIMARY KEY,
|
|
order_id INT NOT NULL,
|
|
menu_id INT NOT NULL,
|
|
quantity NUMERIC NOT NULL CHECK (quantity > 0),
|
|
item_price NUMERIC NOT NULL CHECK (item_price > 0),
|
|
|
|
FOREIGN KEY (order_id) REFERENCES user_orders(id),
|
|
FOREIGN KEY (menu_id) REFERENCES menu_items(id)
|
|
);
|
|
|
|
-- =======================================================
|
|
-- INITIAL MENU DATA
|
|
-- =======================================================
|
|
|
|
|
|
INSERT INTO menu_items (name, description, price, category) VALUES
|
|
('Margherita Pizza', 'Classic pizza with tomato sauce, mozzarella, and basil.', 12.99, 'Pizza'),
|
|
('Pepperoni Pizza', NULL, 14.49, 'Pizza'),
|
|
('Cheeseburger', 'Beef patty with cheddar cheese, lettuce, and tomato.', 10.99, 'Burger'),
|
|
('Chicken Burger', NULL, 11.49, 'Burger'),
|
|
('Caesar Salad', 'Romaine lettuce, croutons, parmesan, and Caesar dressing.', 8.99, 'Salad'),
|
|
('Greek Salad', NULL, 9.49, 'Salad'),
|
|
('French Fries', 'Crispy golden potato fries.', 4.99, 'Side'),
|
|
('Onion Rings', NULL, 5.49, 'Side'),
|
|
('Espresso', NULL, 2.99, 'Drink'),
|
|
('Cappuccino', 'Espresso with steamed milk and milk foam.', 4.49, 'Drink');
|
|
|
|
|
|
|
|
-- =======================================================
|
|
-- VERIFICATION QUERIES
|
|
-- =======================================================
|
|
|
|
SELECT * FROM users;
|
|
SELECT * FROM menu_items;
|
|
SELECT * FROM user_orders;
|
|
SELECT * FROM order_details; |