Logical and Physical Design – Database Creation: DDL.sql

1   DROP TABLE IF EXISTS delivery_assignment;
2   DROP TABLE IF EXISTS item_ingredient;
3   DROP TABLE IF EXISTS costs;
4   DROP TABLE IF EXISTS earnings;
5   DROP TABLE IF EXISTS menu;
6   DROP TABLE IF EXISTS restaurant_category;
7   DROP TABLE IF EXISTS item_category;
8   DROP TABLE IF EXISTS order_items;
9   DROP TABLE IF EXISTS orders;
10  DROP TABLE IF EXISTS restaurant_owners;
11  DROP TABLE IF EXISTS user_addresses;
12  DROP TABLE IF EXISTS restaurant_owner;
13  DROP TABLE IF EXISTS delivery_man;
14  DROP TABLE IF EXISTS customer;
15  DROP TABLE IF EXISTS restaurant;
16  DROP TABLE IF EXISTS delivery_firm;
17  DROP TABLE IF EXISTS ingredient;
18  DROP TABLE IF EXISTS item;
19  DROP TABLE IF EXISTS category;
20  DROP TABLE IF EXISTS address;
21  DROP TABLE IF EXISTS app_user;
22  DROP TABLE IF EXISTS administrator;
23  DROP TABLE IF EXISTS menu_item;
24  DROP TABLE IF EXISTS shopping_cart;
25  DROP TABLE IF EXISTS cart_items;


 

CREATE TABLE APP_USER (
    user_id SERIAL PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(50)
);

CREATE TABLE ADMINISTRATOR (
    user_id INT PRIMARY KEY,
    authorized_from DATE,
    authorized_to DATE,
    CONSTRAINT fk_admin_user FOREIGN KEY (user_id) REFERENCES APP_USER(user_id)
);

ALTER TABLE APP_USER
ADD COLUMN administrator_id INT,
ADD CONSTRAINT fk_appuser_admin FOREIGN KEY (administrator_id)
    REFERENCES ADMINISTRATOR(user_id);

CREATE TABLE ADDRESS (
    address_id SERIAL PRIMARY KEY,
    city VARCHAR(100),
    street VARCHAR(200),
    postal_code VARCHAR(20)
);

CREATE TABLE CATEGORY (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
);

CREATE TABLE ITEM (
    item_id SERIAL PRIMARY KEY,
    image_url VARCHAR(255),
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    description TEXT
);

CREATE TABLE INGREDIENT (
    ingredient_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE DELIVERY_FIRM (
    deliveryfirm_id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL
);

CREATE TABLE RESTAURANT (
    restaurant_id SERIAL PRIMARY KEY,
    address_id INT,
    name VARCHAR(150) NOT NULL,
    website_url VARCHAR(255),
    opening_time TIME,
    closing_time TIME,
    email VARCHAR(150),
    phone_number VARCHAR(50),
    CONSTRAINT fk_restaurant_address FOREIGN KEY (address_id) REFERENCES ADDRESS(address_id)
);

CREATE TABLE CUSTOMER (
    user_id INT PRIMARY KEY,
    CONSTRAINT fk_customer_user FOREIGN KEY (user_id) REFERENCES APP_USER(user_id)
);

CREATE TABLE DELIVERY_MAN (
    user_id INT PRIMARY KEY,
    CONSTRAINT fk_deliveryman_user FOREIGN KEY (user_id) REFERENCES APP_USER(user_id)
);

CREATE TABLE RESTAURANT_OWNER (
    user_id INT PRIMARY KEY,
    CONSTRAINT fk_restowner_user FOREIGN KEY (user_id) REFERENCES APP_USER(user_id)
);

CREATE TABLE USER_ADDRESSES (
    address_id INT,
    user_id INT,
    PRIMARY KEY (address_id, user_id),
    CONSTRAINT fk_useraddr_address FOREIGN KEY (address_id) REFERENCES ADDRESS(address_id),
    CONSTRAINT fk_useraddr_user FOREIGN KEY (user_id) REFERENCES APP_USER(user_id)
);

CREATE TABLE RESTAURANT_OWNERS (
    user_id INT,
    restaurant_id INT,
    PRIMARY KEY (user_id, restaurant_id),
    CONSTRAINT fk_restowners_user FOREIGN KEY (user_id) REFERENCES RESTAURANT_OWNER(user_id),
    CONSTRAINT fk_restowners_rest FOREIGN KEY (restaurant_id) REFERENCES RESTAURANT(restaurant_id)
);

CREATE TABLE ORDERS (
    order_id SERIAL PRIMARY KEY,
    address_id INT,
    restaurant_id INT,
    deliveryman_id INT,
    user_id INT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    comment TEXT,
    order_status VARCHAR(50),
    total_amount DECIMAL(10,2),
    CONSTRAINT fk_orders_address FOREIGN KEY (address_id) REFERENCES ADDRESS(address_id),
    CONSTRAINT fk_orders_rest FOREIGN KEY (restaurant_id) REFERENCES RESTAURANT(restaurant_id),
    CONSTRAINT fk_orders_delivery FOREIGN KEY (deliveryman_id) REFERENCES DELIVERY_MAN(user_id),
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES CUSTOMER(user_id)
);

CREATE TABLE ORDER_ITEMS (
    order_id INT,
    item_id INT,
    quantity INT NOT NULL,
    total_price DECIMAL(10,2),
    PRIMARY KEY (order_id, item_id),
    CONSTRAINT fk_orderitems_order FOREIGN KEY (order_id) REFERENCES ORDERS(order_id),
    CONSTRAINT fk_orderitems_item FOREIGN KEY (item_id) REFERENCES ITEM(item_id)
);

CREATE TABLE ITEM_CATEGORY (
    category_id INT,
    item_id INT,
    PRIMARY KEY (category_id, item_id),
    CONSTRAINT fk_itemcat_category FOREIGN KEY (category_id) REFERENCES CATEGORY(category_id),
    CONSTRAINT fk_itemcat_item FOREIGN KEY (item_id) REFERENCES ITEM(item_id)
);

CREATE TABLE RESTAURANT_CATEGORY (
    category_id INT,
    restaurant_id INT,
    PRIMARY KEY (category_id, restaurant_id),
    CONSTRAINT fk_restcat_category FOREIGN KEY (category_id) REFERENCES CATEGORY(category_id),
    CONSTRAINT fk_restcat_rest FOREIGN KEY (restaurant_id) REFERENCES RESTAURANT(restaurant_id)
);

CREATE TABLE MENU (
    menu_id SERIAL PRIMARY KEY,
    restaurant_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    name VARCHAR(150),
    CONSTRAINT fk_menu_rest FOREIGN KEY (restaurant_id) REFERENCES RESTAURANT(restaurant_id)
);

CREATE TABLE EARNINGS (
    earning_id SERIAL PRIMARY KEY,
    restaurant_id INT,
    date DATE,
    amount DECIMAL(10,2),
    source VARCHAR(100),
    CONSTRAINT fk_earnings_rest FOREIGN KEY (restaurant_id) REFERENCES RESTAURANT(restaurant_id)
);

CREATE TABLE COSTS (
    cost_id SERIAL PRIMARY KEY,
    restaurant_id INT,
    date DATE,
    amount DECIMAL(10,2),
    type VARCHAR(100),
    CONSTRAINT fk_costs_rest FOREIGN KEY (restaurant_id) REFERENCES RESTAURANT(restaurant_id)
);

CREATE TABLE ITEM_INGREDIENT (
    item_id INT,
    ingredient_id INT,
    quantity VARCHAR(50),
    PRIMARY KEY (item_id, ingredient_id),
    CONSTRAINT fk_iteming_item FOREIGN KEY (item_id) REFERENCES ITEM(item_id),
    CONSTRAINT fk_iteming_ing FOREIGN KEY (ingredient_id) REFERENCES INGREDIENT(ingredient_id)
);

CREATE TABLE DELIVERY_ASSIGNMENT (
    deliveryfirm_id INT,
    user_id INT,
    PRIMARY KEY (deliveryfirm_id, user_id),
    CONSTRAINT fk_deliveryassign_firm FOREIGN KEY (deliveryfirm_id) REFERENCES DELIVERY_FIRM(deliveryfirm_id),
    CONSTRAINT fk_deliveryassign_user FOREIGN KEY (user_id) REFERENCES DELIVERY_MAN(user_id)
);

CREATE TABLE MENU_ITEM (
    menu_id INT,
    item_id INT,
    PRIMARY KEY (menu_id, item_id),
    CONSTRAINT fk_menuitem_menu FOREIGN KEY (menu_id) REFERENCES MENU(menu_id),
    CONSTRAINT fk_menuitem_item FOREIGN KEY (item_id) REFERENCES ITEM(item_id)
);

CREATE TABLE SHOPPING_CART (
    cart_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    CONSTRAINT fk_cart_user FOREIGN KEY (user_id) REFERENCES APP_USER(user_id)
);

CREATE TABLE CART_ITEMS (
    cart_id INT NOT NULL,
    item_id INT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (cart_id, item_id),
    CONSTRAINT fk_cartitems_cart FOREIGN KEY (cart_id) REFERENCES SHOPPING_CART(cart_id),
    CONSTRAINT fk_cartitems_item FOREIGN KEY (item_id) REFERENCES ITEM(item_id)
);