DDL

1. Вовед

DDL-от беше генериран од страна на Visual Paradigm, испробан локално и дополнет со constraints. Потоа, направивме pg_dump и го импортиравме на нашата remote база за предметот.

DDL:

CREATE TABLE "User" (

user_id SERIAL, name varchar(255), surname varchar(255), email varchar(255), password varchar(255), phone_number varchar(255), registration_date date, PRIMARY KEY (user_id),

CONSTRAINT email_check CHECK (email ~* '^{[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),}

CONSTRAINT password_complexity CHECK (

password ~ '[0-9]' AND password ~ '[^a-zA-Z0-9]'

)

);

CREATE TABLE VehicleType (

vehicle_type_id SERIAL, vehicle_type varchar(255), PRIMARY KEY (vehicle_type_id)

);

CREATE TABLE Vehicle (

vehicle_id SERIAL, registration varchar(255), vehicle_type_id int DEFAULT -1, PRIMARY KEY (vehicle_id), FOREIGN KEY (vehicle_type_id) REFERENCES VehicleType(vehicle_type_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE

);

CREATE TABLE Driver (

driver_id int DEFAULT -1, vehicle_id int DEFAULT -1, status varchar(255), hire_date date, PRIMARY KEY (driver_id), FOREIGN KEY (driver_id) REFERENCES "User"(user_id)

ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (vehicle_id) REFERENCES Vehicle(vehicle_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE

);

CREATE TABLE Customer (

customer_id int DEFAULT -1, PRIMARY KEY (customer_id), FOREIGN KEY (customer_id) REFERENCES "User"(user_id)

ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE LoyalCustomer (

loyalty_id int DEFAULT -1, points int DEFAULT 0 CHECK (points >= 0), joined_day date, birthday date, PRIMARY KEY (loyalty_id), FOREIGN KEY (loyalty_id) REFERENCES Customer(customer_id)

ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE City (

city_id SERIAL, city_name varchar(255), postal_code int, PRIMARY KEY (city_id)

);

CREATE TABLE StoreType (

store_type_id SERIAL, type_name varchar(255), description varchar(255), PRIMARY KEY (store_type_id)

);

CREATE TABLE Store (

store_id SERIAL, store_name varchar(255), city_id int DEFAULT -1, store_type_id int DEFAULT -1, rating double precision CHECK (rating >= 0 AND rating <= 5), PRIMARY KEY (store_id), FOREIGN KEY (city_id) REFERENCES City(city_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE,

FOREIGN KEY (store_type_id) REFERENCES StoreType(store_type_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE

);

CREATE TABLE DeliveryZone (

zone_id SERIAL, city_id int DEFAULT -1, zone_name varchar(255), PRIMARY KEY (zone_id), FOREIGN KEY (city_id) REFERENCES City(city_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE

);

CREATE TABLE Address (

address_id SERIAL, zone_id int DEFAULT -1, street varchar(255), street_number int, door_number int, PRIMARY KEY (address_id), FOREIGN KEY (zone_id) REFERENCES DeliveryZone(zone_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE

);

CREATE TABLE StoreInstance (

store_instance_id SERIAL, store_id int DEFAULT -1, address_id int DEFAULT -1, phone varchar(255), zone_id int DEFAULT -1, PRIMARY KEY (store_instance_id), FOREIGN KEY (store_id) REFERENCES Store(store_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE,

FOREIGN KEY (address_id) REFERENCES Address(address_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE,

FOREIGN KEY (zone_id) REFERENCES DeliveryZone(zone_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE

);

CREATE TABLE Delivery_zone_Store_instance (

zone_id int DEFAULT -1, store_instance_id int DEFAULT -1,

FOREIGN KEY (zone_id) REFERENCES DeliveryZone(zone_id)

ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (store_instance_id) REFERENCES StoreInstance(store_instance_id)

ON DELETE CASCADE ON UPDATE CASCADE,

PRIMARY KEY (zone_id, store_instance_id)

);

CREATE TABLE Product (

product_id SERIAL, store_id int DEFAULT -1, name varchar(255), price int DEFAULT 0 CHECK (price >= 0), PRIMARY KEY (product_id), FOREIGN KEY (store_id) REFERENCES Store(store_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE

);

CREATE TABLE Promotion (

promotion_id SERIAL, store_id int DEFAULT -1, promotion_name varchar(255), discount_percent int DEFAULT 0, start_date date, end_date date, PRIMARY KEY (promotion_id), FOREIGN KEY (store_id) REFERENCES Store(store_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE,

CHECK (discount_percent >= 0 AND discount_percent <= 100), CHECK (start_date <= end_date)

);

CREATE TABLE "Order" (

order_id SERIAL, customer_id int DEFAULT -1, promotion_id int DEFAULT -1, driver_id int DEFAULT -1, store_instance_id int DEFAULT -1, delivery_zone_id int DEFAULT -1, status varchar(255), delivery_fee int DEFAULT 0 CHECK (delivery_fee >= 0), total_price int DEFAULT 0 CHECK (total_price >= 0), PRIMARY KEY (order_id), FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE,

FOREIGN KEY (promotion_id) REFERENCES Promotion(promotion_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE,

FOREIGN KEY (driver_id) REFERENCES Driver(driver_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE,

FOREIGN KEY (store_instance_id) REFERENCES StoreInstance(store_instance_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE,

FOREIGN KEY (delivery_zone_id) REFERENCES DeliveryZone(zone_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE

);

CREATE TABLE OrderItem (

order_item_id SERIAL, order_id int DEFAULT -1, product_id int DEFAULT -1, quantity int DEFAULT 1 CHECK (quantity > 0), PRIMARY KEY (order_item_id), FOREIGN KEY (order_id) REFERENCES "Order"(order_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE,

FOREIGN KEY (product_id) REFERENCES Product(product_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE

);

CREATE TABLE Review (

review_id SERIAL, order_id int DEFAULT -1, customer_id int DEFAULT -1, rating double precision CHECK (rating >= 0 AND rating <= 5), comment varchar(255), review_date date, PRIMARY KEY (review_id), FOREIGN KEY (order_id) REFERENCES "Order"(order_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE,

FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE

);

CREATE TABLE Products_review (

review_id int DEFAULT -1, store_instance_id int DEFAULT -1, FOREIGN KEY (review_id ) REFERENCES Review(review_id)

ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (store_instance_id ) REFERENCES StoreInstance(store_instance_id)

ON DELETE SET default ON UPDATE CASCADE,

PRIMARY KEY(review_id));

CREATE TABLE Delivery_review (

review_id int DEFAULT -1, driver_id int DEFAULT -1, FOREIGN KEY (review_id ) REFERENCES Review(review_id)

ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (driver_id ) REFERENCES Driver(driver_id)

ON DELETE SET default ON UPDATE CASCADE,

PRIMARY KEY(review_id));

CREATE TABLE Payment_method (

payment_method_id SERIAL, payment varchar(255), PRIMARY KEY (payment_method_id)

);

CREATE TABLE Payment (

payment_id SERIAL, status varchar(255), payment_date date, payment_method_id int DEFAULT -1, order_id int DEFAULT -1, PRIMARY KEY (payment_id), FOREIGN KEY (payment_method_id) REFERENCES Payment_method(payment_method_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE,

FOREIGN KEY (order_id) REFERENCES "Order"(order_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE

);

CREATE TABLE Customer_payment_method (

customer_id int DEFAULT -1, payment_method_id int DEFAULT -1, date_added date DEFAULT CURRENT_DATE,

-- Композитен примарен клуч

-- Референца кон Customer FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)

ON DELETE CASCADE ON UPDATE CASCADE,

-- Референца кон Payment_method FOREIGN KEY (payment_method_id) REFERENCES Payment_method(payment_method_id)

ON DELETE CASCADE ON UPDATE CASCADE,

PRIMARY KEY (customer_id, payment_method_id)

);

CREATE TABLE Customer_Address (

customer_id int DEFAULT -1, address_id int DEFAULT -1, address_label varchar(255), -- Опционално: за ознаки како 'Home', 'Work'

FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)

ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (address_id) REFERENCES Address(address_id)

ON DELETE CASCADE ON UPDATE CASCADE,

PRIMARY KEY (customer_id, address_id)

);