Changes between Version 49 and Version 50 of DatabaseCreation


Ignore:
Timestamp:
06/29/26 21:31:34 (6 days ago)
Author:
231027
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseCreation

    v49 v50  
    44== DDL скрипти
    55
    6 === `Event`, `Concert`, `Play`
     6=== `User`, `Admin`, `Regular_User`
     7
     8{{{
     9
     10CREATE TABLE "User" (
     11    user_id BIGSERIAL PRIMARY KEY,
     12    username VARCHAR(255) NOT NULL UNIQUE,
     13    email VARCHAR(255) NOT NULL UNIQUE,
     14    password VARCHAR(255) NOT NULL,
     15    registration_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     16
     17    CONSTRAINT user_email_check CHECK (
     18        email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
     19    ),
     20    CONSTRAINT user_password_check CHECK (
     21        length(password) >= 8 AND password ~ '[A-Z]' AND password ~ '[a-z]' AND password ~ '[0-9]' AND password ~ '[^a-zA-Z0-9]'
     22    )
     23);
     24
     25}}}
     26
     27{{{
     28
     29CREATE TABLE "Admin" (
     30    user_id BIGINT PRIMARY KEY,
     31
     32    CONSTRAINT fk_admin_user FOREIGN KEY (user_id) REFERENCES "User" (user_id)
     33        ON DELETE CASCADE
     34        ON UPDATE CASCADE
     35);
     36
     37}}}
     38
     39{{{
     40
     41CREATE TABLE "Regular_User" (
     42    user_id BIGINT PRIMARY KEY,
     43    first_name VARCHAR(255) NOT NULL,
     44    last_name VARCHAR(255) NOT NULL,
     45    date_of_birth DATE NOT NULL,
     46
     47    CONSTRAINT fk_regular_user FOREIGN KEY (user_id) REFERENCES "User" (user_id)
     48        ON DELETE CASCADE
     49        ON UPDATE CASCADE
     50);
     51
     52}}}
     53
     54
     55=== `Performer`
     56
     57{{{
     58
     59CREATE TABLE "Performer" (
     60    performer_id BIGSERIAL PRIMARY KEY,
     61    name VARCHAR(255) NOT NULL
     62);
     63
     64}}}
     65
     66
     67=== `Event_Type`, 'Event'
     68
     69{{{
     70
     71CREATE TABLE "Event_Type" (
     72    type_id BIGSERIAL PRIMARY KEY,
     73    name VARCHAR(255) NOT NULL
     74);
     75
     76}}}
    777
    878{{{
    … …  
    1181    event_id BIGSERIAL PRIMARY KEY,
    1282    name VARCHAR(255) NOT NULL,
     83    type_id BIGINT NOT NULL,
    1384    description TEXT NOT NULL,
    14     min_age INT NOT NULL
    15 );
    16 
    17 }}}
    18 
    19 {{{
    20 
    21 CREATE TABLE "Concert" (
    22     event_id BIGINT PRIMARY KEY,
    23     concert_type VARCHAR(50) NOT NULL,
    24     setlist TEXT NOT NULL,
    25 
    26     CONSTRAINT fk_concert_event FOREIGN KEY (event_id) REFERENCES "Event" (event_id)
    27         ON DELETE CASCADE
    28         ON UPDATE CASCADE
    29 );
    30 
    31 }}}
    32 
    33 {{{
    34 
    35 CREATE TABLE "Play" (
    36     event_id BIGINT PRIMARY KEY,
    37     genre VARCHAR(50) NOT NULL,
    38     number_of_acts INT NOT NULL,
    39     language VARCHAR(50) NOT NULL,
    40     director VARCHAR(255) NOT NULL,
    41 
    42     CONSTRAINT fk_play_event FOREIGN KEY (event_id) REFERENCES "Event" (event_id)
    43         ON DELETE CASCADE
    44         ON UPDATE CASCADE
    45 );
    46 
    47 }}}
    48 
    49 === `Performer`, `Musical_Performer`, `Acting_Performer`
    50 
    51 {{{
    52 
    53 CREATE TABLE "Performer" (
    54     performer_id BIGSERIAL PRIMARY KEY,
    55     name VARCHAR(255) NOT NULL,
    56     number_of_members INT NOT NULL,
    57     contact_email VARCHAR(255) NOT NULL UNIQUE,
    58     technical_requirements TEXT NOT NULL,
    59 
    60     CONSTRAINT performer_contact_email_check CHECK (contact_email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
    61 );
    62 
    63 }}}
    64 
    65 {{{
    66 
    67 CREATE TABLE "Musical_Performer" (
    68     performer_id BIGINT PRIMARY KEY,
    69     musician_type VARCHAR(50) NOT NULL,
    70     genre VARCHAR(50) NOT NULL,
    71     record_label VARCHAR(255) NOT NULL,
    72 
    73     CONSTRAINT fk_musical_performer_performer FOREIGN KEY (performer_id) REFERENCES "Performer" (performer_id)
    74         ON DELETE CASCADE
    75         ON UPDATE CASCADE
    76 );
    77 
    78 }}}
    79 
    80 {{{
    81 
    82 CREATE TABLE "Acting_Performer" (
    83     performer_id BIGINT PRIMARY KEY,
    84     role_type VARCHAR(50) NOT NULL,
    85     acting_style VARCHAR(100) NOT NULL,
    86     agency VARCHAR(255) NOT NULL,
    87 
    88     CONSTRAINT fk_acting_performer_performer FOREIGN KEY (performer_id) REFERENCES "Performer" (performer_id)
    89         ON DELETE CASCADE
    90         ON UPDATE CASCADE
    91 );
    92 
    93 }}}
     85    min_age INTEGER NOT NULL,
     86    created_by BIGINT NOT NULL,
     87
     88    CONSTRAINT fk_event_type FOREIGN KEY (type_id) REFERENCES "Event_Type" (type_id)
     89        ON DELETE RESTRICT
     90        ON UPDATE RESTRICT,
     91    CONSTRAINT fk_event_admin FOREIGN KEY (created_by) REFERENCES "Admin" (user_id)
     92        ON DELETE RESTRICT
     93        ON UPDATE RESTRICT
     94);
     95
     96}}}
     97
    9498
    9599=== `Venue`, `Section`, `Seat`
    … …  
    100104    venue_id BIGSERIAL PRIMARY KEY,
    101105    name VARCHAR(255) NOT NULL,
    102     number_of_sections INT NOT NULL,
    103     number_of_seats INT NOT NULL,
     106    number_of_sections INTEGER NOT NULL,
     107    number_of_seats INTEGER NOT NULL,
    104108    address_street VARCHAR(255) NOT NULL,
    105     address_city VARCHAR(100) NOT NULL,
    106     address_country VARCHAR(100) NOT NULL
     109    address_city VARCHAR(255) NOT NULL,
     110    address_country VARCHAR(255) NOT NULL
    107111);
    108112
    … …  
    113117CREATE TABLE "Section" (
    114118    section_id BIGSERIAL PRIMARY KEY,
     119    name VARCHAR(255) NOT NULL,
     120    number_of_seats INTEGER NOT NULL,
    115121    venue_id BIGINT NOT NULL,
    116     name VARCHAR(255) NOT NULL,
    117     number_of_seats INT NOT NULL,
    118 
     122
     123    CONSTRAINT uq_section_venue_name UNIQUE (venue_id, name),
    119124    CONSTRAINT fk_section_venue FOREIGN KEY (venue_id) REFERENCES "Venue" (venue_id)
    120125        ON DELETE CASCADE
    121         ON UPDATE CASCADE,
    122     CONSTRAINT uq_section_venue_name UNIQUE (venue_id, name)
     126        ON UPDATE CASCADE
    123127);
    124128
    … …  
    129133CREATE TABLE "Seat" (
    130134    seat_id BIGSERIAL PRIMARY KEY,
     135    seat_number INTEGER NOT NULL,
     136    row_number INTEGER NOT NULL,
    131137    section_id BIGINT NOT NULL,
    132     seat_number INT NOT NULL,
    133 
     138
     139    CONSTRAINT uq_seat_section_number UNIQUE (section_id, seat_number),
    134140    CONSTRAINT fk_seat_section FOREIGN KEY (section_id) REFERENCES "Section" (section_id)
    135141        ON DELETE CASCADE
    136         ON UPDATE CASCADE,
    137     CONSTRAINT uq_seat_section_number UNIQUE (section_id, seat_number)
    138 );
    139 
    140 }}}
    141 
    142 === `Organizer`, `Sponsor`
    143 
    144 {{{
    145 
    146 CREATE TABLE "Organizer" (
    147     organizer_id BIGSERIAL PRIMARY KEY,
    148     name VARCHAR(255) NOT NULL,
    149     contact_email VARCHAR(255) NOT NULL UNIQUE,
    150 
    151     CONSTRAINT organizer_contact_email_check CHECK (contact_email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
    152 );
    153 
    154 }}}
    155 
    156 {{{
    157 
    158 CREATE TABLE "Sponsor" (
    159     sponsor_id BIGSERIAL PRIMARY KEY,
    160     name VARCHAR(255) NOT NULL,
    161     contact_email VARCHAR(255) NOT NULL UNIQUE,
    162     sponsor_type VARCHAR(50) NOT NULL,
    163 
    164     CONSTRAINT sponsor_contact_email_check CHECK (contact_email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
     142        ON UPDATE CASCADE
    165143);
    166144
    … …  
    176154    event_time TIMESTAMP NOT NULL,
    177155    venue_id BIGINT NOT NULL,
    178     duration INT NOT NULL,
    179 
    180     CONSTRAINT fk_event_happening_event FOREIGN KEY (event_id) REFERENCES "Event" (event_id)
     156    duration_minutes INTEGER NOT NULL,
     157    organizers TEXT NOT NULL,
     158    sponsors TEXT,
     159
     160    CONSTRAINT uq_happening_time_venue UNIQUE (event_time, venue_id),
     161    CONSTRAINT fk_happening_event FOREIGN KEY (event_id) REFERENCES "Event" (event_id)
    181162        ON DELETE RESTRICT
    182163        ON UPDATE CASCADE,
    183     CONSTRAINT fk_event_happening_venue FOREIGN KEY (venue_id) REFERENCES "Venue" (venue_id)
    184         ON DELETE RESTRICT
    185         ON UPDATE CASCADE,
    186     CONSTRAINT uq_happening_time_venue UNIQUE (event_time, venue_id)
     164    CONSTRAINT fk_happening_venue FOREIGN KEY (venue_id) REFERENCES "Venue" (venue_id)
     165        ON DELETE RESTRICT
     166        ON UPDATE CASCADE
    187167);
    188168
    … …  
    193173CREATE TABLE "Event_Period" (
    194174    period_id BIGSERIAL PRIMARY KEY,
    195     event_happening_id BIGINT NOT NULL,
    196175    name VARCHAR(255) NOT NULL,
    197176    start_date DATE NOT NULL,
    198177    end_date DATE NOT NULL,
    199     price_change_percent INT NOT NULL,
    200     increase_decrease BOOLEAN NOT NULL,
    201 
    202     CONSTRAINT fk_event_period_event_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
    203         ON DELETE CASCADE
    204         ON UPDATE CASCADE,
     178    price_discount_percent INTEGER NOT NULL,
     179    event_happening_id BIGINT NOT NULL,
     180
    205181    CONSTRAINT uq_period_happening_name UNIQUE (event_happening_id, name),
    206     CONSTRAINT event_period_dates_check CHECK (start_date <= end_date)
    207 );
    208 
    209 }}}
    210 
    211 === '''M:N''' релации: `Event_Happening_Performer`, `Event_Happening_Organizer`, `Event_Happening_Sponsor`
     182    CONSTRAINT event_period_dates_check CHECK (start_date <= end_date),
     183    CONSTRAINT fk_period_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
     184        ON DELETE CASCADE
     185        ON UPDATE CASCADE
     186);
     187
     188}}}
     189
     190=== `Event_Happening_Performer`
    212191
    213192{{{
    … …  
    217196    performer_id BIGINT NOT NULL,
    218197
    219     CONSTRAINT pk_event_happening_performer PRIMARY KEY (event_happening_id, performer_id),
    220     CONSTRAINT fk_ehp_event_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
     198    CONSTRAINT pk_ehp PRIMARY KEY (event_happening_id, performer_id),
     199    CONSTRAINT fk_ehp_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
    221200        ON DELETE CASCADE
    222201        ON UPDATE CASCADE,
    223202    CONSTRAINT fk_ehp_performer FOREIGN KEY (performer_id) REFERENCES "Performer" (performer_id)
    224203        ON DELETE CASCADE
    225         ON UPDATE CASCADE,
    226     CONSTRAINT uq_performer_at_time UNIQUE (performer_id, event_happening_id)
    227 );
    228 
    229 }}}
    230 
    231 {{{
    232 
    233 CREATE TABLE "Event_Happening_Organizer" (
    234     event_happening_id BIGINT NOT NULL,
    235     organizer_id BIGINT NOT NULL,
    236 
    237     CONSTRAINT event_happening_organizer PRIMARY KEY (event_happening_id, organizer_id),
    238     CONSTRAINT fk_eho_event_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
    239         ON DELETE CASCADE
    240         ON UPDATE CASCADE,
    241     CONSTRAINT fk_eho_organizer FOREIGN KEY (organizer_id) REFERENCES "Organizer" (organizer_id)
    242         ON DELETE CASCADE
    243         ON UPDATE CASCADE
    244 );
    245 
    246 }}}
    247 
    248 {{{
    249 
    250 CREATE TABLE "Event_Happening_Sponsor" (
    251     event_happening_id BIGINT NOT NULL,
    252     sponsor_id BIGINT NOT NULL,
    253 
    254     CONSTRAINT event_happening_sponsor PRIMARY KEY (event_happening_id, sponsor_id),
    255     CONSTRAINT fk_ehs_event_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
    256         ON DELETE CASCADE
    257         ON UPDATE CASCADE,
    258     CONSTRAINT fk_ehs_sponsor FOREIGN KEY (sponsor_id) REFERENCES "Sponsor" (sponsor_id)
    259         ON DELETE CASCADE
    260         ON UPDATE CASCADE
    261 );
    262 
    263 }}}
    264 
    265 === `User`, `Event_Happening_Rating`
    266 
    267 {{{
    268 
    269 CREATE TABLE "User" (
    270     user_id BIGSERIAL PRIMARY KEY,
    271     username VARCHAR(50) NOT NULL UNIQUE,
    272     first_name VARCHAR(100) NOT NULL,
    273     last_name VARCHAR(100) NOT NULL,
    274     date_of_birth DATE NOT NULL,
    275     contact_email VARCHAR(255) NOT NULL UNIQUE,
    276     password VARCHAR(100) NOT NULL,
    277     registration_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    278 
    279     CONSTRAINT user_date_of_birth_check CHECK (date_of_birth < CURRENT_DATE),
    280     CONSTRAINT user_contact_email_check CHECK (contact_email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    281     CONSTRAINT user_password_check CHECK (
    282         length(password) >= 8 AND
    283         password ~ '[A-Z]' AND
    284         password ~ '[a-z]' AND
    285         password ~ '[0-9]' AND
    286         password ~ '[^a-zA-Z0-9]'
    287     )
    288 );
    289 
    290 }}}
     204        ON UPDATE CASCADE
     205);
     206
     207}}}
     208
     209
     210=== `Event_Happening_Rating`
    291211
    292212{{{
    … …  
    294214CREATE TABLE "Event_Happening_Rating" (
    295215    rating_id BIGSERIAL PRIMARY KEY,
    296     rating INT NOT NULL CHECK (rating BETWEEN 1 AND 10),
     216    rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 10),
    297217    comment TEXT,
     218    timestamp TIMESTAMP NOT NULL,
    298219    event_happening_id BIGINT NOT NULL,
    299220    user_id BIGINT NOT NULL DEFAULT 0,
    300221
    301     CONSTRAINT fk_event_happening_rating_event_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
     222    CONSTRAINT uq_rating_happening_user UNIQUE (event_happening_id, user_id),
     223    CONSTRAINT fk_rating_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
    302224        ON DELETE CASCADE
    303225        ON UPDATE CASCADE,
    304     CONSTRAINT fk_event_happening_rating_user FOREIGN KEY (user_id) REFERENCES "User" (user_id)
     226    CONSTRAINT fk_rating_regular_user FOREIGN KEY (user_id) REFERENCES "Regular_User" (user_id)
    305227        ON DELETE SET DEFAULT
    306         ON UPDATE CASCADE,
    307     CONSTRAINT uq_rating_happening_user UNIQUE (event_happening_id, user_id)
    308 );
    309 
    310 }}}
    311 
    312 === `Ticket`, `Ticket_Purchase`, `Ticket_Refund`
     228        ON UPDATE CASCADE
     229);
     230
     231}}}
     232
     233
     234=== `Ticket`
    313235
    314236{{{
    … …  
    316238CREATE TABLE "Ticket" (
    317239    ticket_id BIGSERIAL PRIMARY KEY,
    318     ticket_type VARCHAR(50) NOT NULL,
    319     base_price FLOAT4 NOT NULL,
     240    base_price DECIMAL(10,2) NOT NULL,
    320241    is_available BOOLEAN NOT NULL DEFAULT TRUE,
    321242    event_happening_id BIGINT NOT NULL,
    322243    seat_id BIGINT NOT NULL,
    323244
    324     CONSTRAINT fk_ticket_event_happening FOREIGN KEY (event_happening_id)
    325         REFERENCES "Event_Happening" (event_happening_id)
     245    CONSTRAINT uq_ticket_happening_seat UNIQUE (event_happening_id, seat_id),
     246    CONSTRAINT fk_ticket_seat FOREIGN KEY (seat_id) REFERENCES "Seat" (seat_id)
    326247        ON DELETE RESTRICT
    327248        ON UPDATE CASCADE,
    328     CONSTRAINT fk_ticket_seat FOREIGN KEY (seat_id)
    329         REFERENCES "Seat" (seat_id)
     249    CONSTRAINT fk_ticket_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
     250        ON DELETE RESTRICT
     251        ON UPDATE CASCADE
     252);
     253
     254}}}
     255
     256
     257=== `Ticket_Order`, `Ticket_Order_Item`
     258
     259{{{
     260
     261CREATE TABLE "Ticket_Order" (
     262    order_id BIGSERIAL PRIMARY KEY,
     263    order_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     264    order_amount DECIMAL(10,2) NOT NULL DEFAULT 0.0,
     265    user_id BIGINT NOT NULL,
     266
     267    CONSTRAINT fk_order_regular_user FOREIGN KEY (user_id) REFERENCES "Regular_User" (user_id)
     268        ON DELETE RESTRICT
     269        ON UPDATE CASCADE
     270);
     271
     272}}}
     273
     274{{{
     275
     276CREATE TABLE "Ticket_Order_Item" (
     277    order_item_id BIGSERIAL PRIMARY KEY,
     278    qr_code VARCHAR(255),
     279    item_price DECIMAL(10,2) NOT NULL,
     280    order_id BIGINT NOT NULL,
     281    ticket_id BIGINT NOT NULL,
     282
     283    CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES "Ticket_Order" (order_id)
    330284        ON DELETE CASCADE
    331285        ON UPDATE CASCADE,
    332     CONSTRAINT uq_ticket_event_happening_seat UNIQUE (event_happening_id, seat_id)
    333 );
    334 
    335 }}}
    336 
    337 {{{
    338 
    339 CREATE TABLE "Ticket_Purchase" (
    340     purchase_id BIGSERIAL PRIMARY KEY,
    341     ticket_id BIGINT NOT NULL,
    342     user_id BIGINT NOT NULL DEFAULT 0,
    343     qr_code VARCHAR(255) NOT NULL UNIQUE,
    344     purchase_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    345     purchase_amount FLOAT4 NOT NULL,
    346 
    347     CONSTRAINT fk_purchase_ticket FOREIGN KEY (ticket_id) REFERENCES "Ticket" (ticket_id)
    348         ON DELETE RESTRICT
    349         ON UPDATE CASCADE,
    350     CONSTRAINT fk_purchase_user FOREIGN KEY (user_id) REFERENCES "User" (user_id)
    351         ON DELETE SET DEFAULT
    352         ON UPDATE CASCADE
    353 );
    354 
    355 }}}
     286    CONSTRAINT fk_item_ticket FOREIGN KEY (ticket_id) REFERENCES "Ticket" (ticket_id)
     287        ON DELETE RESTRICT
     288        ON UPDATE CASCADE
     289);
     290
     291}}}
     292
     293
     294=== 'Ticket_Refund', `Ticket_Refund_Item`
    356295
    357296{{{
    … …  
    359298CREATE TABLE "Ticket_Refund" (
    360299    refund_id BIGSERIAL PRIMARY KEY,
    361     purchase_id BIGINT NOT NULL UNIQUE,
    362300    refund_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    363     refund_amount FLOAT4 NOT NULL,
    364     reason TEXT,
    365 
    366     CONSTRAINT fk_refund_purchase FOREIGN KEY (purchase_id) REFERENCES "Ticket_Purchase" (purchase_id)
    367         ON DELETE CASCADE
     301    refund_amount DECIMAL(10,2) NOT NULL DEFAULT 0.0,
     302    reasons TEXT,
     303    order_id BIGINT NOT NULL,
     304
     305    CONSTRAINT fk_refund_order FOREIGN KEY (order_id) REFERENCES "Ticket_Order" (order_id)
     306        ON DELETE RESTRICT
     307        ON UPDATE CASCADE
     308);
     309
     310}}}
     311
     312{{{
     313
     314CREATE TABLE "Ticket_Refund_Item" (
     315    refund_item_id BIGSERIAL PRIMARY KEY,
     316    item_price DECIMAL(10,2) NOT NULL,
     317    refund_id BIGINT NOT NULL,
     318    order_item_id BIGINT NOT NULL UNIQUE,
     319
     320    CONSTRAINT fk_ri_refund FOREIGN KEY (refund_id) REFERENCES "Ticket_Refund" (refund_id)
     321        ON DELETE CASCADE
     322        ON UPDATE CASCADE,
     323    CONSTRAINT fk_ri_order_item FOREIGN KEY (order_item_id) REFERENCES "Ticket_Order_Item" (order_item_id)
     324        ON DELETE RESTRICT
    368325        ON UPDATE CASCADE
    369326);