Changes between Version 3 and Version 4 of DatabaseCreation

Timestamp:

05/26/26 14:43:16 (5 hours ago)

Author:

232026

Comment:

--

DatabaseCreation

@@ -4 +4 @@
 
 == DDL: ==
-teo
+
+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)
+);