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(250) 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),
  price DECIMAL(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 INTEGER NOT NULL,
    orderDate TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    total_price DECIMAL(10,2) NOT NULL,

    CONSTRAINT fk_orders
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- =======================================================
-- 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 INTEGER NOT NULL,
    menu_items_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity>0),
    price_at_purchase DECIMAL(10,2) NOT NULL,

    CONSTRAINT fk_order_detail_order
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_menu_items_id
        FOREIGN KEY (menu_items_id) REFERENCES menu_items(id)
        ON DELETE RESTRICT
);



-- =======================================================
-- INITIAL MENU DATA
-- =======================================================
--
-- Insert at least 3 food or drink items.
--
-- Example categories:
-- - Pizza
-- - Burger
-- - Pasta
-- - Drink
--
INSERT INTO menu_items (name, price, category) VALUES
    ('Margherita', 9.99, 'Pizza'),
    ('Cheeseburger', 8.50, 'Burger'),
    ('Alfredo pasta', 12.00, 'Pasta'),
    ('Coca Cola', 2.50, 'Drink');



-- =======================================================
-- OPTIONAL TEST DATA
-- =======================================================
--
-- You may insert sample users and orders for testing.
-- This section is optional.
--
INSERT INTO users (username, password, email) VALUES
    ('aga armin', MD5('123456'), 'ar@example.com'),
    ('mewsoume', MD5('mypass'), 'm@example.com');

INSERT INTO orders (user_id, total_price) VALUES
    (1, 22.49);

INSERT INTO order_details (order_id, menu_items_id, quantity, price_at_purchase) VALUES
    (1, 1, 2, 9.99),
    (1, 3, 1, 12.00);

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