Changes between Initial Version and Version 1 of DatabaseCreation


Ignore:
Timestamp:
05/20/26 15:12:42 (6 days ago)
Author:
231198
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseCreation

    v1 v1  
     1= Database Creation =
     2
     3=== Имплементациски детали ===
     4
     5Во овој проект, преку DDL наредби се креирани сите потребни табели за системот за управување со такси апликација, како и нивните меѓусебни релации.
     6
     7Во скриптите се опфатени:
     8 * примарни клучеви (PRIMARY KEY)
     9 * надворешни клучеви (FOREIGN KEY)
     10 * ограничувања за интегритет (CHECK, UNIQUE, NOT NULL)
     11 * правила за ажурирање и бришење (ON DELETE, ON UPDATE)
     12
     13Со овие DDL наредби се обезбедува конзистентност, интегритет и правилна организација на податоците во базата.
     14
     15=== DDL Скрипти ===
     16
     17```
     18CREATE TABLE Status (
     19                        status_id SERIAL PRIMARY KEY,
     20                        status_tip VARCHAR(255) NOT NULL,
     21                        status_text VARCHAR(255) NOT NULL
     22);
     23                                                
     24CREATE TABLE Vehicles_model (
     25                                Vehicles_model_id SERIAL PRIMARY KEY,
     26                                Model VARCHAR(255) NOT NULL
     27);
     28                                                
     29CREATE TABLE Locations (
     30                           location_id SERIAL PRIMARY KEY,
     31                           latitude NUMERIC(9,6) NOT NULL,
     32                           longitude NUMERIC(9,6) NOT NULL,
     33                           grad VARCHAR(255) NOT NULL,
     34                           naselba VARCHAR(255) NOT NULL,
     35                           ulica VARCHAR(255) NOT NULL,
     36                           broj VARCHAR(255) NOT NULL,
     37                           created_at TIMESTAMP NOT NULL
     38); 
     39
     40CREATE TABLE Users (
     41                       User_id SERIAL PRIMARY KEY,
     42                       name VARCHAR(255) NOT NULL,
     43                       username VARCHAR(255) NOT NULL UNIQUE,
     44                       phone VARCHAR(255) NOT NULL UNIQUE,
     45                       email VARCHAR(255) NOT NULL UNIQUE,
     46                       password_hash TEXT NOT NULL,
     47                       created_at TIMESTAMP NOT NULL,
     48                       status_id INT NOT NULL,
     49
     50                       CONSTRAINT chk_users_phone CHECK (phone ~ '^[0-9+]+$'),
     51                       CONSTRAINT chk_users_email CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$'),
     52
     53                       CONSTRAINT FK_Users_Status
     54                           FOREIGN KEY (status_id) REFERENCES Status(status_id)
     55                               ON DELETE RESTRICT
     56                               ON UPDATE CASCADE
     57);
     58
     59CREATE TABLE Drivers (
     60                         driver_id SERIAL PRIMARY KEY,
     61                         name VARCHAR(255) NOT NULL,
     62                         phone VARCHAR(255) NOT NULL UNIQUE,
     63                         license_number VARCHAR(255) NOT NULL UNIQUE,
     64                         registration_date DATE NOT NULL,
     65                         rating NUMERIC(3,2) NOT NULL,
     66                         status_id INT NOT NULL,
     67
     68                         CONSTRAINT chk_drivers_phone CHECK (phone ~ '^[0-9+]+$'),
     69                         CONSTRAINT chk_drivers_rating CHECK (rating BETWEEN 0 AND 5),
     70
     71                         CONSTRAINT FK_Drivers_Status
     72                             FOREIGN KEY (status_id) REFERENCES Status(status_id)
     73                                 ON DELETE RESTRICT
     74                                 ON UPDATE CASCADE
     75);
     76
     77CREATE TABLE Admins (
     78                        admins_id SERIAL PRIMARY KEY,
     79                        name VARCHAR(255) NOT NULL,
     80                        username VARCHAR(255) NOT NULL UNIQUE,
     81                        phone VARCHAR(255) NOT NULL UNIQUE,
     82                        email VARCHAR(255) NOT NULL UNIQUE,
     83                        password_hash TEXT NOT NULL,
     84                        created_at TIMESTAMP NOT NULL,
     85                        status_id INT NOT NULL,
     86
     87                        CONSTRAINT chk_admins_phone CHECK (phone ~ '^[0-9+]+$'),
     88                        CONSTRAINT chk_admins_email CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$'),
     89
     90                        CONSTRAINT FK_Admins_Status
     91                            FOREIGN KEY (status_id) REFERENCES Status(status_id)
     92                                ON DELETE RESTRICT
     93                                ON UPDATE CASCADE
     94);
     95
     96CREATE TABLE Vehicle_types (
     97                               vehicle_type_id SERIAL PRIMARY KEY,
     98                               type_name VARCHAR(255) NOT NULL,
     99                               description VARCHAR(255) NOT NULL,
     100                               Vehicles_model_id INT NOT NULL,
     101
     102                               CONSTRAINT FK_Vehicle_types_Model
     103                                   FOREIGN KEY (Vehicles_model_id)
     104                                       REFERENCES Vehicles_model(Vehicles_model_id)
     105                                       ON DELETE RESTRICT
     106                                       ON UPDATE CASCADE
     107);
     108
     109CREATE TABLE Vehicle_ownership (
     110                                   Vehicle_ownership_id SERIAL PRIMARY KEY,
     111                                   Plate VARCHAR(255) NOT NULL UNIQUE,
     112                                   Traffic_permit VARCHAR(255) NOT NULL UNIQUE,
     113                                   Color VARCHAR(255) NOT NULL,
     114                                   year INT NOT NULL,
     115                                   vehicle_type_id INT NOT NULL,
     116
     117
     118                                   CONSTRAINT chk_vehicle_year CHECK (year >= 1900),
     119
     120                                   CONSTRAINT FK_Vehicle_ownership_Type
     121                                       FOREIGN KEY (vehicle_type_id)
     122                                           REFERENCES Vehicle_types(vehicle_type_id)
     123                                           ON DELETE RESTRICT
     124                                           ON UPDATE CASCADE
     125);
     126
     127CREATE TABLE Pricing_rules (
     128                               tariff_id SERIAL PRIMARY KEY,
     129                               base_price NUMERIC(10,2) NOT NULL,
     130                               price_per_km NUMERIC(10,2) NOT NULL,
     131                               vehicle_type_id INT NOT NULL,
     132
     133                               CONSTRAINT chk_price CHECK (base_price >= 0 AND price_per_km >= 0),
     134
     135                               CONSTRAINT FK_Pricing_rules_Vehicle_types
     136                                   FOREIGN KEY (vehicle_type_id)
     137                                       REFERENCES Vehicle_types(vehicle_type_id)
     138                                       ON DELETE RESTRICT
     139                                       ON UPDATE CASCADE
     140);
     141
     142CREATE TABLE Drivers_Vehicle_ownership (
     143                                           driver_id INT NOT NULL,
     144                                           Vehicle_ownership_id INT NOT NULL,
     145
     146                                           PRIMARY KEY (driver_id, Vehicle_ownership_id),
     147
     148                                           CONSTRAINT FK_DVO_Drivers
     149                                               FOREIGN KEY (driver_id)
     150                                                   REFERENCES Drivers(driver_id)
     151                                                    ON DELETE CASCADE,
     152
     153                                           CONSTRAINT FK_DVO_Vehicles
     154                                               FOREIGN KEY (Vehicle_ownership_id)
     155                                                   REFERENCES Vehicle_ownership(Vehicle_ownership_id)
     156                                                   ON DELETE CASCADE
     157);
     158
     159CREATE TABLE Active_drivers (
     160                                driver_id INT PRIMARY KEY,
     161                                current_location_id INT NOT NULL,
     162                                is_available BOOLEAN NOT NULL,
     163                                working_start TIMESTAMP NOT NULL,
     164                                working_end TIMESTAMP NOT NULL,
     165                                vehicle_ownership_id INT NOT NULL,
     166
     167                                CONSTRAINT FK_Active_drivers_Drivers
     168                                    FOREIGN KEY (driver_id)
     169                                        REFERENCES Drivers(driver_id)
     170                                        ON DELETE CASCADE,
     171
     172                                CONSTRAINT FK_Active_drivers_Locations
     173                                    FOREIGN KEY (current_location_id)
     174                                        REFERENCES Locations(location_id)
     175                                        ON DELETE RESTRICT,
     176
     177                                CONSTRAINT FK_Active_drivers_vehicle_ownership_id
     178                                    FOREIGN KEY (vehicle_ownership_id)
     179                                        REFERENCES Vehicle_ownership(Vehicle_ownership_id)
     180                                        ON DELETE RESTRICT
     181);
     182
     183CREATE TABLE Rides (
     184                       ride_id SERIAL PRIMARY KEY,
     185                       user_id INT NOT NULL,
     186                       driver_id INT NOT NULL,
     187                       vehicle_ownership_id INT NOT NULL,
     188                       pickup_location_id INT NOT NULL,
     189                       dropoff_location_id INT NOT NULL,
     190                       status_id INT NOT NULL,
     191                       request_time TIMESTAMP NOT NULL,
     192                       pickup_time TIMESTAMP NOT NULL,
     193                       start_time TIMESTAMP NOT NULL,
     194                       end_time TIMESTAMP NOT NULL,
     195                       distance_km NUMERIC(6,2) NOT NULL,
     196                       final_price NUMERIC(10,2) NOT NULL,
     197                       discount_percentage NUMERIC(5,2) NOT NULL,
     198
     199
     200                       CONSTRAINT chk_distance CHECK (distance_km >= 0),
     201                       CONSTRAINT chk_discount CHECK (discount_percentage BETWEEN 0 AND 100),
     202
     203                       CONSTRAINT FK_Rides_Users
     204                           FOREIGN KEY (user_id) REFERENCES Users(User_id) ON DELETE RESTRICT,
     205
     206                       CONSTRAINT FK_Rides_vehicle_ownership_id
     207                           FOREIGN KEY (vehicle_ownership_id) REFERENCES Vehicle_ownership(Vehicle_ownership_id) ON DELETE RESTRICT,
     208
     209                       CONSTRAINT FK_Rides_Drivers
     210                           FOREIGN KEY (driver_id) REFERENCES Drivers(driver_id) ON DELETE RESTRICT,
     211
     212                       CONSTRAINT FK_Rides_Pickup_Location
     213                           FOREIGN KEY (pickup_location_id) REFERENCES Locations(location_id) ON DELETE RESTRICT,
     214
     215                       CONSTRAINT FK_Rides_Dropoff_Location
     216                           FOREIGN KEY (dropoff_location_id) REFERENCES Locations(location_id) ON DELETE RESTRICT,
     217
     218                       CONSTRAINT FK_Rides_Status
     219                           FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT
     220);
     221
     222CREATE TABLE Payment_methods(
     223                                payment_method_id SERIAL PRIMARY KEY,
     224                                payment_tip  VARCHAR(255) NOT NULL
     225);
     226
     227CREATE TABLE Payments (
     228                          payment_id SERIAL PRIMARY KEY,
     229                          amount NUMERIC(10,2) NOT NULL,
     230                          payment_method_id INT NOT NULL,
     231                          payment_time TIMESTAMP NOT NULL,
     232                          ride_id INT NOT NULL,
     233                          status_id INT NOT NULL,
     234
     235                          CONSTRAINT chk_amount CHECK (amount >= 0),
     236
     237                          CONSTRAINT FK_Payments_Rides
     238                              FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE,
     239
     240                          CONSTRAINT FK_Payments_Status
     241                              FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT,
     242
     243                          CONSTRAINT FK_Payments_Method
     244                              FOREIGN KEY (payment_method_id) REFERENCES Payment_methods(payment_method_id) ON DELETE RESTRICT
     245);
     246
     247CREATE TABLE Ratings (
     248                         rating_id SERIAL PRIMARY KEY,
     249                         score INT NOT NULL,
     250                         comment VARCHAR(255),
     251                         created_at TIMESTAMP NOT NULL,
     252                         ride_id INT NOT NULL,
     253                         User_id INT NOT NULL,
     254                         driver_id INT NOT NULL,
     255
     256                         CONSTRAINT chk_score CHECK (score BETWEEN 1 AND 5),
     257
     258                         CONSTRAINT FK_Ratings_Rides
     259                             FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE,
     260
     261                         CONSTRAINT FK_Ratings_Users
     262                             FOREIGN KEY (User_id) REFERENCES Users(User_id) ON DELETE CASCADE,
     263
     264                         CONSTRAINT FK_Ratings_Drivers
     265                             FOREIGN KEY (driver_id) REFERENCES Drivers(driver_id) ON DELETE CASCADE
     266);
     267
     268CREATE TABLE Cancellations (
     269                               cancellation_id SERIAL PRIMARY KEY,
     270                               cancelled_by_type VARCHAR(255) NOT NULL,
     271                               cancelled_by_id INT NOT NULL,
     272                               reason VARCHAR(255) NOT NULL,
     273                               cancellation_fee NUMERIC(10,2) NOT NULL,
     274                               created_at TIMESTAMP NOT NULL,
     275                               ride_id INT NOT NULL,
     276
     277                               CONSTRAINT chk_fee CHECK (cancellation_fee >= 0),
     278                               CONSTRAINT chk_cancelled_by_type CHECK (cancelled_by_type IN ('USER', 'DRIVER', 'ADMIN')),
     279
     280                               CONSTRAINT FK_Cancellations_Rides
     281                                   FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE
     282);
     283
     284CREATE TABLE Messages(
     285                         message_id SERIAL PRIMARY KEY,
     286                         message_text VARCHAR(255) NOT NULL
     287
     288);
     289
     290CREATE TABLE Notifications (
     291                               notification_id SERIAL PRIMARY KEY,
     292                               message_id INT NOT NULL,
     293                               sent_time TIMESTAMP NOT NULL,
     294                               User_id INT NOT NULL,
     295                               ride_id INT NOT NULL,
     296                               status_id INT NOT NULL,
     297
     298                               CONSTRAINT FK_Notifications_Users
     299                                   FOREIGN KEY (User_id) REFERENCES Users(User_id) ON DELETE CASCADE,
     300
     301                               CONSTRAINT FK_Notifications_Rides
     302                    
     303                               FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE,
     304
     305                               CONSTRAINT FK_Notifications_Status
     306                                   FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT,
     307
     308                               CONSTRAINT FK_Notifications_Messages
     309                                    FOREIGN KEY (message_id) REFERENCES Messages(message_id) ON DELETE CASCADE
     310);
     311```