111 lines
4.8 KiB
SQL
111 lines
4.8 KiB
SQL
CREATE TABLE users (
|
|
id SERIAL PRIMARY KEY,
|
|
username VARCHAR(100) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
email VARCHAR(255)
|
|
);
|
|
|
|
-- =======================================================
|
|
-- MENU ITEM TABLE
|
|
-- =======================================================
|
|
-- Represents available food and drink items.
|
|
-- Requirements:
|
|
-- - Unique identifier
|
|
-- - Name required
|
|
-- - Price positive
|
|
CREATE TABLE menu_items (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(150) NOT NULL,
|
|
description TEXT,
|
|
price DOUBLE PRECISION NOT NULL CHECK (price > 0),
|
|
category VARCHAR(100)
|
|
);
|
|
|
|
-- =======================================================
|
|
-- ORDER TABLE
|
|
-- =======================================================
|
|
-- Represents orders placed by customers.
|
|
-- Requirements:
|
|
-- - Each order belongs to exactly one user
|
|
-- - A user can have multiple orders
|
|
-- - Use non-reserved table name: orders
|
|
CREATE TABLE orders (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
total_price DOUBLE PRECISION NOT NULL DEFAULT 0 CHECK (total_price >= 0),
|
|
CONSTRAINT fk_orders_user
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES users(id)
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
-- =======================================================
|
|
-- ORDER DETAIL TABLE
|
|
-- =======================================================
|
|
-- Represents items inside an order.
|
|
-- Requirements:
|
|
-- - Each detail belongs to one order
|
|
-- - Each detail references one menu item
|
|
-- - Quantity > 0
|
|
-- - Store item price at purchase time
|
|
CREATE TABLE order_details (
|
|
id SERIAL PRIMARY KEY,
|
|
order_id INT NOT NULL,
|
|
menu_item_id INT NOT NULL,
|
|
quantity INT NOT NULL CHECK (quantity > 0),
|
|
price DOUBLE PRECISION NOT NULL CHECK (price > 0),
|
|
CONSTRAINT fk_order_details_order
|
|
FOREIGN KEY (order_id)
|
|
REFERENCES orders(id)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT fk_order_details_menu_item
|
|
FOREIGN KEY (menu_item_id)
|
|
REFERENCES menu_items(id)
|
|
ON DELETE RESTRICT
|
|
);
|
|
|
|
-- =======================================================
|
|
-- INITIAL MENU DATA
|
|
-- =======================================================
|
|
-- Insert at least 3 food/drink items
|
|
INSERT INTO menu_items (name, description, price, category) VALUES
|
|
('Margherita Pizza', 'Classic pizza with tomato sauce, mozzarella, and basil', 8.99, 'Pizza'),
|
|
('Pepperoni Pizza', 'Pizza topped with pepperoni and cheese', 10.99, 'Pizza'),
|
|
('Cheeseburger', 'Beef burger with cheddar cheese and lettuce', 7.50, 'Burger'),
|
|
('Pasta Carbonara', 'Creamy pasta with bacon and parmesan', 11.50, 'Pasta'),
|
|
('Coca Cola', 'Chilled soft drink', 1.50, 'Drink');
|
|
|
|
-- =======================================================
|
|
-- OPTIONAL TEST DATA
|
|
-- =======================================================
|
|
-- Sample user for testing (password is hashed placeholder)
|
|
INSERT INTO users (username, password_hash, email) VALUES
|
|
('testuser', '$2a$10$dummyhashforTestingOnly12345678901234567890', 'testuser@example.com');
|
|
|
|
-- Sample order for testing
|
|
INSERT INTO orders (user_id, created_at, total_price)
|
|
VALUES (1, CURRENT_TIMESTAMP, 0);
|
|
|
|
-- Sample order details for testing
|
|
INSERT INTO order_details (order_id, menu_item_id, quantity, price) VALUES
|
|
(1, 1, 2, 8.99),
|
|
(1, 5, 2, 1.50);
|
|
|
|
-- Update total price for the sample order
|
|
UPDATE orders
|
|
SET total_price = (
|
|
SELECT SUM(quantity * price)
|
|
FROM order_details
|
|
WHERE order_id = 1
|
|
)
|
|
WHERE id = 1;
|
|
|
|
-- =======================================================
|
|
-- VERIFICATION QUERIES
|
|
-- =======================================================
|
|
SELECT * FROM users;
|
|
SELECT * FROM menu_items;
|
|
SELECT * FROM orders;
|
|
SELECT * FROM order_details;
|