Database Creation

Имплементациски детали

Во овој проект, преку DDL наредби се креирани сите потребни табели за системот за управување со такси апликација, како и нивните меѓусебни релации.

Во скриптите се опфатени:

• примарни клучеви (PRIMARY KEY)
• надворешни клучеви (FOREIGN KEY)
• ограничувања за интегритет (CHECK, UNIQUE, NOT NULL)
• правила за ажурирање и бришење (ON DELETE, ON UPDATE)

Со овие DDL наредби се обезбедува конзистентност, интегритет и правилна организација на податоците во базата.

DDL Скрипти

` CREATE TABLE Status (

status_id SERIAL PRIMARY KEY,

status_tip VARCHAR(255) NOT NULL,

status_text VARCHAR(255) NOT NULL

);                                                  CREATE TABLE Vehicles_model (

Vehicles_model_id SERIAL PRIMARY KEY,

Model VARCHAR(255) NOT NULL

);                                                  CREATE TABLE Locations (

location_id SERIAL PRIMARY KEY,

latitude NUMERIC(9,6) NOT NULL,

longitude NUMERIC(9,6) NOT NULL,

grad VARCHAR(255) NOT NULL,

naselba VARCHAR(255) NOT NULL,

ulica VARCHAR(255) NOT NULL,

broj VARCHAR(255) NOT NULL,

created_at TIMESTAMP NOT NULL

); 

CREATE TABLE Users (

User_id SERIAL PRIMARY KEY,

name VARCHAR(255) NOT NULL,

username VARCHAR(255) NOT NULL UNIQUE,

phone VARCHAR(255) NOT NULL UNIQUE,

email VARCHAR(255) NOT NULL UNIQUE,

password_hash TEXT NOT NULL,

created_at TIMESTAMP NOT NULL,

status_id INT NOT NULL,

CONSTRAINT chk_users_phone CHECK (phone ~ '^[0-9+]+$'),

CONSTRAINT chk_users_email CHECK (email ~ '^[@]+@[^@]+\.[@]+$'),

CONSTRAINT FK_Users_Status

FOREIGN KEY (status_id) REFERENCES Status(status_id)

ON DELETE RESTRICT

ON UPDATE CASCADE

);

CREATE TABLE Drivers (

driver_id SERIAL PRIMARY KEY,

name VARCHAR(255) NOT NULL,

phone VARCHAR(255) NOT NULL UNIQUE,

license_number VARCHAR(255) NOT NULL UNIQUE,

registration_date DATE NOT NULL,

rating NUMERIC(3,2) NOT NULL,

status_id INT NOT NULL,

CONSTRAINT chk_drivers_phone CHECK (phone ~ '^[0-9+]+$'),

CONSTRAINT chk_drivers_rating CHECK (rating BETWEEN 0 AND 5),

CONSTRAINT FK_Drivers_Status

FOREIGN KEY (status_id) REFERENCES Status(status_id)

ON DELETE RESTRICT

ON UPDATE CASCADE

);

CREATE TABLE Admins (

admins_id SERIAL PRIMARY KEY,

name VARCHAR(255) NOT NULL,

username VARCHAR(255) NOT NULL UNIQUE,

phone VARCHAR(255) NOT NULL UNIQUE,

email VARCHAR(255) NOT NULL UNIQUE,

password_hash TEXT NOT NULL,

created_at TIMESTAMP NOT NULL,

status_id INT NOT NULL,

CONSTRAINT chk_admins_phone CHECK (phone ~ '^[0-9+]+$'),

CONSTRAINT chk_admins_email CHECK (email ~ '^[@]+@[^@]+\.[@]+$'),

CONSTRAINT FK_Admins_Status

FOREIGN KEY (status_id) REFERENCES Status(status_id)

ON DELETE RESTRICT

ON UPDATE CASCADE

);

CREATE TABLE Vehicle_types (

vehicle_type_id SERIAL PRIMARY KEY,

type_name VARCHAR(255) NOT NULL,

description VARCHAR(255) NOT NULL,

Vehicles_model_id INT NOT NULL,

CONSTRAINT FK_Vehicle_types_Model

FOREIGN KEY (Vehicles_model_id)

REFERENCES Vehicles_model(Vehicles_model_id)

ON DELETE RESTRICT

ON UPDATE CASCADE

);

CREATE TABLE Vehicle_ownership (

Vehicle_ownership_id SERIAL PRIMARY KEY,

Plate VARCHAR(255) NOT NULL UNIQUE,

Traffic_permit VARCHAR(255) NOT NULL UNIQUE,

Color VARCHAR(255) NOT NULL,

year INT NOT NULL,

vehicle_type_id INT NOT NULL,

CONSTRAINT chk_vehicle_year CHECK (year >= 1900),

CONSTRAINT FK_Vehicle_ownership_Type

FOREIGN KEY (vehicle_type_id)

REFERENCES Vehicle_types(vehicle_type_id)

ON DELETE RESTRICT

ON UPDATE CASCADE

);

CREATE TABLE Pricing_rules (

tariff_id SERIAL PRIMARY KEY,

base_price NUMERIC(10,2) NOT NULL,

price_per_km NUMERIC(10,2) NOT NULL,

vehicle_type_id INT NOT NULL,

CONSTRAINT chk_price CHECK (base_price >= 0 AND price_per_km >= 0),

CONSTRAINT FK_Pricing_rules_Vehicle_types

FOREIGN KEY (vehicle_type_id)

REFERENCES Vehicle_types(vehicle_type_id)

ON DELETE RESTRICT

ON UPDATE CASCADE

);

CREATE TABLE Drivers_Vehicle_ownership (

driver_id INT NOT NULL,

Vehicle_ownership_id INT NOT NULL,

PRIMARY KEY (driver_id, Vehicle_ownership_id),

CONSTRAINT FK_DVO_Drivers

FOREIGN KEY (driver_id)

REFERENCES Drivers(driver_id)

ON DELETE CASCADE,

CONSTRAINT FK_DVO_Vehicles

FOREIGN KEY (Vehicle_ownership_id)

REFERENCES Vehicle_ownership(Vehicle_ownership_id)

ON DELETE CASCADE

);

CREATE TABLE Active_drivers (

driver_id INT PRIMARY KEY,

current_location_id INT NOT NULL,

is_available BOOLEAN NOT NULL,

working_start TIMESTAMP NOT NULL,

working_end TIMESTAMP NOT NULL,

vehicle_ownership_id INT NOT NULL,

CONSTRAINT FK_Active_drivers_Drivers

FOREIGN KEY (driver_id)

REFERENCES Drivers(driver_id)

ON DELETE CASCADE,

CONSTRAINT FK_Active_drivers_Locations

FOREIGN KEY (current_location_id)

REFERENCES Locations(location_id)

ON DELETE RESTRICT,

CONSTRAINT FK_Active_drivers_vehicle_ownership_id

FOREIGN KEY (vehicle_ownership_id)

REFERENCES Vehicle_ownership(Vehicle_ownership_id)

ON DELETE RESTRICT

);

CREATE TABLE Rides (

ride_id SERIAL PRIMARY KEY,

user_id INT NOT NULL,

driver_id INT NOT NULL,

vehicle_ownership_id INT NOT NULL,

pickup_location_id INT NOT NULL,

dropoff_location_id INT NOT NULL,

status_id INT NOT NULL,

request_time TIMESTAMP NOT NULL,

pickup_time TIMESTAMP NOT NULL,

start_time TIMESTAMP NOT NULL,

end_time TIMESTAMP NOT NULL,

distance_km NUMERIC(6,2) NOT NULL,

final_price NUMERIC(10,2) NOT NULL,

discount_percentage NUMERIC(5,2) NOT NULL,

CONSTRAINT chk_distance CHECK (distance_km >= 0),

CONSTRAINT chk_discount CHECK (discount_percentage BETWEEN 0 AND 100),

CONSTRAINT FK_Rides_Users

FOREIGN KEY (user_id) REFERENCES Users(User_id) ON DELETE RESTRICT,

CONSTRAINT FK_Rides_vehicle_ownership_id

FOREIGN KEY (vehicle_ownership_id) REFERENCES Vehicle_ownership(Vehicle_ownership_id) ON DELETE RESTRICT,

CONSTRAINT FK_Rides_Drivers

FOREIGN KEY (driver_id) REFERENCES Drivers(driver_id) ON DELETE RESTRICT,

CONSTRAINT FK_Rides_Pickup_Location

FOREIGN KEY (pickup_location_id) REFERENCES Locations(location_id) ON DELETE RESTRICT,

CONSTRAINT FK_Rides_Dropoff_Location

FOREIGN KEY (dropoff_location_id) REFERENCES Locations(location_id) ON DELETE RESTRICT,

CONSTRAINT FK_Rides_Status

FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT

);

CREATE TABLE Payment_methods(

payment_method_id SERIAL PRIMARY KEY,

payment_tip  VARCHAR(255) NOT NULL

);

CREATE TABLE Payments (

payment_id SERIAL PRIMARY KEY,

amount NUMERIC(10,2) NOT NULL,

payment_method_id INT NOT NULL,

payment_time TIMESTAMP NOT NULL,

ride_id INT NOT NULL,

status_id INT NOT NULL,

CONSTRAINT chk_amount CHECK (amount >= 0),

CONSTRAINT FK_Payments_Rides

FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE,

CONSTRAINT FK_Payments_Status

FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT,

CONSTRAINT FK_Payments_Method

FOREIGN KEY (payment_method_id) REFERENCES Payment_methods(payment_method_id) ON DELETE RESTRICT

);

CREATE TABLE Ratings (

rating_id SERIAL PRIMARY KEY,

score INT NOT NULL,

comment VARCHAR(255),

created_at TIMESTAMP NOT NULL,

ride_id INT NOT NULL,

User_id INT NOT NULL,

driver_id INT NOT NULL,

CONSTRAINT chk_score CHECK (score BETWEEN 1 AND 5),

CONSTRAINT FK_Ratings_Rides

FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE,

CONSTRAINT FK_Ratings_Users

FOREIGN KEY (User_id) REFERENCES Users(User_id) ON DELETE CASCADE,

CONSTRAINT FK_Ratings_Drivers

FOREIGN KEY (driver_id) REFERENCES Drivers(driver_id) ON DELETE CASCADE

);

CREATE TABLE Cancellations (

cancellation_id SERIAL PRIMARY KEY,

cancelled_by_type VARCHAR(255) NOT NULL,

cancelled_by_id INT NOT NULL,

reason VARCHAR(255) NOT NULL,

cancellation_fee NUMERIC(10,2) NOT NULL,

created_at TIMESTAMP NOT NULL,

ride_id INT NOT NULL,

CONSTRAINT chk_fee CHECK (cancellation_fee >= 0),

CONSTRAINT chk_cancelled_by_type CHECK (cancelled_by_type IN ('USER', 'DRIVER', 'ADMIN')),

CONSTRAINT FK_Cancellations_Rides

FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE

);

CREATE TABLE Messages(

message_id SERIAL PRIMARY KEY,

message_text VARCHAR(255) NOT NULL

);

CREATE TABLE Notifications (

notification_id SERIAL PRIMARY KEY,

message_id INT NOT NULL,

sent_time TIMESTAMP NOT NULL,

User_id INT NOT NULL,

ride_id INT NOT NULL,

status_id INT NOT NULL,

CONSTRAINT FK_Notifications_Users

FOREIGN KEY (User_id) REFERENCES Users(User_id) ON DELETE CASCADE,

CONSTRAINT FK_Notifications_Rides

                    

FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE,

CONSTRAINT FK_Notifications_Status

FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT,

CONSTRAINT FK_Notifications_Messages

FOREIGN KEY (message_id) REFERENCES Messages(message_id) ON DELETE CASCADE

); `