Changes between Initial Version and Version 1 of RelationalModel


Ignore:
Timestamp:
04/17/26 17:39:27 (2 weeks ago)
Author:
231198
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • RelationalModel

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