WS-10-Database/database.sql

-- Restaurant Database Management System
--
-- Instructions:
-- 1. Create all required tables.
-- 2. Design appropriate PRIMARY KEY and FOREIGN KEY relationships.
-- 3. Add suitable constraints based on the requirements.
-- 4. Insert initial mock data.
-- 5. Insert at least 3 menu items.
-- 6. The script should be executable from start to finish without errors.

DROP TABLE IF EXISTS order_details CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS menu_items CASCADE;
DROP TABLE IF EXISTS users CASCADE;

-- =======================================================
-- USER TABLE
-- =======================================================
--
-- Represents customers using the system.
--
-- Required information:
-- - Unique identifier
-- - Username
-- - Password
-- - Email (optional)
--
-- Requirements:
-- - Each user must have a unique identifier.
-- - Usernames must be unique.
-- - Username and password are required.
-- - Passwords should not be stored in plain text.
--
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100)
);


-- =======================================================
-- MENU ITEM TABLE
-- =======================================================
--
-- Represents available food and drink items.
--
-- Required information:
-- - Unique identifier
-- - Name
-- - Description (optional)
-- - Price
-- - Category (optional)
--
-- Requirements:
-- - Each menu item must have a unique identifier.
-- - Name is required.
-- - Price must always be positive.
--
CREATE TABLE menu_items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL CHECK (price > 0),
    category VARCHAR(50)
);


-- =======================================================
-- ORDER TABLE
-- =======================================================
--
-- Represents orders placed by customers.
--
-- Required information:
-- - Unique identifier
-- - Reference to customer
-- - Creation date and time
-- - Total price
--
-- Requirements:
-- - Each order must belong to exactly one user.
-- - A user can have multiple orders.
-- - The relationship between User and Order must be implemented.
--
-- Note:
-- Avoid using reserved SQL keywords as table names.
-- Consider using a name such as "orders" or "customer_orders".
--
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP NOT NULL,
    total_price NUMERIC(10, 2) NOT NULL DEFAULT 0.00
);


-- =======================================================
-- ORDER DETAIL TABLE
-- =======================================================
--
-- Represents items inside an order.
--
-- Required information:
-- - Unique identifier
-- - Reference to an order
-- - Reference to a menu item
-- - Quantity
-- - Item price at purchase time
--
-- Requirements:
-- - Each detail record must belong to one order.
-- - Each detail record must reference one menu item.
-- - Quantity must always be greater than zero.
-- - Store the item's price at the moment of purchase.
--
CREATE TABLE order_details (
    id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    menu_item_id INT NOT NULL REFERENCES menu_items(id),
    quantity INT NOT NULL CHECK (quantity > 0),
    price NUMERIC(10, 2) NOT NULL
);


-- =======================================================
-- INITIAL MENU DATA
-- =======================================================
--
-- Insert at least 3 food or drink items.
--
-- Example categories:
-- - Pizza
-- - Burger
-- - Pasta
-- - Drink
--
INSERT INTO menu_items (name, description, price, category) VALUES
('Pizza', 'Delicious cheese pizza with tomato sauce', 10.00, 'Pizza'),
('Burger', 'Juicy beef burger with lettuce and tomato', 8.00, 'Burger'),
('Pasta', 'Rich creamy Alfredo pasta', 12.00, 'Pasta'),
('Soda', 'Cold carbonated drink', 2.50, 'Drink');


-- =======================================================
-- OPTIONAL TEST DATA
-- =======================================================
--
-- You may insert sample users and orders for testing.
-- This section is optional.
--


-- =======================================================
-- VERIFICATION QUERIES
-- =======================================================
--
-- Uncomment these queries to verify your database.
--
SELECT * FROM users;
SELECT * FROM menu_items;
SELECT * FROM orders;
SELECT * FROM order_details;