Changes between Version 2 and Version 3 of DatabaseCreation


Ignore:
Timestamp:
05/20/26 12:14:02 (6 days ago)
Author:
231233
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseCreation

    v2 v3  
    1 CREATE TABLE MOVIE
    2 (
    3     movie_id     SERIAL       NOT NULL,
    4     title        varchar(255) NOT NULL,
    5     release_date date         NOT NULL,
    6     description  varchar(255) NOT NULL,
    7     duration     int4         NOT NULL,
    8     PRIMARY KEY (movie_id)
    9 );
    10 CREATE TABLE ROLE
    11 (
    12     role_id   SERIAL       NOT NULL,
    13     role_name varchar(255) NOT NULL,
    14     PRIMARY KEY (role_id)
    15 );
    16 CREATE TABLE DIRECTOR
    17 (
    18     director_id varchar(13) NOT NULL,
    19     name        varchar(50) NOT NULL,
    20     surname     varchar(50) NOT NULL,
    21     PRIMARY KEY (director_id)
    22 );
    23 CREATE TABLE GENRE
    24 (
    25     genre_id SERIAL      NOT NULL,
    26     name     varchar(50) NOT NULL,
    27     PRIMARY KEY (genre_id)
    28 );
    29 CREATE TABLE RESERVATION
    30 (
    31     reservation_id SERIAL       NOT NULL,
    32     status         varchar(255) NOT NULL,
    33     "date"         date         NOT NULL,
    34     PRIMARY KEY (reservation_id)
    35 );
    36 CREATE TABLE PAYMENT
    37 (
    38     payment_id     SERIAL       NOT NULL,
    39     amount         float4       NOT NULL,
    40     status         varchar(255) NOT NULL,
    41     reservation_id int4         NOT NULL,
    42     PRIMARY KEY (payment_id)
    43 );
    44 CREATE TABLE TICKET
    45 (
    46     ticket_id      SERIAL      NOT NULL,
    47     price          float4      NOT NULL,
    48     user_id        varchar(13) NOT NULL,
    49     reservation_id int4        NOT NULL,
    50     seat_id        int4        NOT NULL,
    51     screening_id   int4        NOT NULL,
    52     PRIMARY KEY (ticket_id)
    53 );
    54 CREATE TABLE PAYMENT_METHOD
    55 (
    56     payment_method_id SERIAL      NOT NULL,
    57     name              varchar(20) NOT NULL,
    58     payment_id        int4        NOT NULL,
    59     PRIMARY KEY (payment_method_id)
    60 );
    61 CREATE TABLE REVIEW
    62 (
    63     review_id SERIAL       NOT NULL,
    64     comment   varchar(255) NOT NULL,
    65     "date"    date         NOT NULL,
    66     user_id   varchar(13)  NOT NULL,
    67     movie_id  int4         NOT NULL,
    68     rating    int4         NOT NULL,
    69     PRIMARY KEY (review_id)
    70 );
    71 CREATE TABLE SEAT
    72 (
    73     seat_id       SERIAL NOT NULL,
    74     row_number    int4   NOT NULL,
    75     seat_number   int4   NOT NULL,
    76     type_id       int4   NOT NULL,
    77     cinemahall_id int4   NOT NULL,
    78     PRIMARY KEY (seat_id)
    79 );
    80 CREATE TABLE SEAT_TYPE
    81 (
    82     seattype_id SERIAL      NOT NULL,
    83     name        varchar(20) NOT NULL,
    84     price       float4      NOT NULL,
    85     PRIMARY KEY (seattype_id)
    86 );
    87 CREATE TABLE SCREENING
    88 (
    89     screening_id  SERIAL  NOT NULL,
    90     "date"        date    NOT NULL,
    91     time          time(7) NOT NULL,
    92     cinemahall_id int4    NOT NULL,
    93     movie_id      int4    NOT NULL,
    94     PRIMARY KEY (screening_id)
    95 );
    96 CREATE TABLE CINEMA
    97 (
    98     cinema_id BIGSERIAL    NOT NULL,
    99     name      varchar(255) NOT NULL,
    100     address   varchar(255) NOT NULL,
    101     contact   varchar(255) NOT NULL,
    102     city_id   int4         NOT NULL,
    103     PRIMARY KEY (cinema_id)
    104 );
    105 CREATE TABLE CINEMA_HALL
    106 (
    107     cinemahall_id SERIAL       NOT NULL,
    108     capacity      int4         NOT NULL,
    109     cinema_id     int8         NOT NULL,
    110     number_rows   int4         NOT NULL,
    111     number_seats  int4         NOT NULL,
    112     description   varchar(255) NOT NULL,
    113     status        varchar(255) NOT NULL,
    114     PRIMARY KEY (cinemahall_id)
    115 );
    116 CREATE TABLE CITY
    117 (
    118     name    varchar(50) NOT NULL,
    119     city_id SERIAL      NOT NULL,
    120     PRIMARY KEY (city_id)
    121 );
    122 CREATE TABLE ACTOR
    123 (
    124     actor_id varchar(13) NOT NULL,
    125     name     varchar(50) NOT NULL,
    126     surname  varchar(50) NOT NULL,
    127     PRIMARY KEY (actor_id)
    128 );
    129 CREATE TABLE PROMOTION
    130 (
    131     promotion_id SERIAL      NOT NULL,
    132     name         varchar(50) NOT NULL,
    133     discount     float4      NOT NULL,
    134     "start"      date        NOT NULL,
    135     "end"        date        NOT NULL,
    136     screening_id int4        NOT NULL,
    137     PRIMARY KEY (promotion_id)
    138 );
    139 CREATE TABLE PURCHASED_PRODUCT
    140 (
    141     purchased_product_id SERIAL NOT NULL,
    142     numbers              int4   NOT NULL,
    143     ticket_id            int4   NOT NULL,
    144     product_id           int4   NOT NULL,
    145     PRIMARY KEY (purchased_product_id)
    146 );
    147 CREATE TABLE PRODUCT
    148 (
    149     product_id SERIAL      NOT NULL,
    150     name       varchar(50) NOT NULL,
    151     unit       varchar(20) NOT NULL,
    152     quantity   int4        NOT NULL,
    153     price      float4      NOT NULL,
    154     PRIMARY KEY (product_id)
    155 );
    156 CREATE TABLE NOTIFICATION
    157 (
    158     notification_id SERIAL       NOT NULL,
    159     message         varchar(255) NOT NULL,
    160     "date"          date         NOT NULL,
    161     user_id         varchar(13)  NOT NULL,
    162     PRIMARY KEY (notification_id)
    163 );
    164 CREATE TABLE "USER"
    165 (
    166     user_id   varchar(13)  NOT NULL,
    167     password  varchar(255) NOT NULL,
    168     name      varchar(50)  NOT NULL,
    169     surname   varchar(255) NOT NULL,
    170     role_id   int4         NOT NULL,
    171     cinema_id int8,
    172     PRIMARY KEY (user_id)
    173 );
    174 CREATE TABLE USER_RESERVATION
    175 (
    176     user_id varchar(13) NOT NULL,
    177     reservation_id int4 NOT NULL,
    178     PRIMARY KEY (user_id, reservation_id)
    179 );
     1= Креирање и пополнување на базата
    1802
    181 CREATE TABLE MOVIE_GENRE
    182 (
    183     genre_id int4 NOT NULL,
    184     movie_id int4 NOT NULL,
    185     PRIMARY KEY (genre_id, movie_id)
    186 );
     3=== DDL скрипта
     4[[html(<a href="">ddl.sql</a>)]]
    1875
    188 CREATE TABLE MOVIE_ACTOR
    189 (
    190     movie_id int4 NOT NULL,
    191     actor_id varchar(13) NOT NULL,
    192     PRIMARY KEY (movie_id, actor_id)
    193 );
    1946
    195 CREATE TABLE MOVIE_DIRECTOR
    196 (
    197     director_id varchar(13) NOT NULL,
    198     movie_id int4 NOT NULL,
    199     PRIMARY KEY (director_id, movie_id)
    200 );
    201 CREATE TABLE PERMISSION
    202 (
    203     permission_id SERIAL NOT NULL,
    204     status bool NOT NULL,
    205     name varchar(255) NOT NULL,
    206     PRIMARY KEY (permission_id)
    207 );
    208 CREATE TABLE ROLE_PERMISSION
    209 (
    210     role_id int4 NOT NULL,
    211     permission_id int4 NOT NULL,
    212     PRIMARY KEY (role_id, permission_id)
    213 );
    214 ALTER TABLE REVIEW
    215     ADD CONSTRAINT FKREVIEW298251 FOREIGN KEY (user_id) REFERENCES "USER" (user_id);
    216 ALTER TABLE REVIEW
    217     ADD CONSTRAINT FKREVIEW927899 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id);
    218 ALTER TABLE TICKET
    219     ADD CONSTRAINT FKTICKET285003 FOREIGN KEY (user_id) REFERENCES "USER" (user_id);
    220 ALTER TABLE USER_RESERVATION
    221     ADD CONSTRAINT FKUSER_RESER151935 FOREIGN KEY (user_id) REFERENCES "USER" (user_id);
    222 ALTER TABLE USER_RESERVATION
    223     ADD CONSTRAINT FKUSER_RESER867618 FOREIGN KEY (reservation_id) REFERENCES RESERVATION (reservation_id);
    224 ALTER TABLE PAYMENT
    225     ADD CONSTRAINT FKPAYMENT601419 FOREIGN KEY (reservation_id) REFERENCES RESERVATION (reservation_id);
    226 ALTER TABLE PAYMENT_METHOD
    227     ADD CONSTRAINT FKPAYMENT_ME706637 FOREIGN KEY (payment_id) REFERENCES PAYMENT (payment_id);
    228 ALTER TABLE TICKET
    229     ADD CONSTRAINT FKTICKET332966 FOREIGN KEY (reservation_id) REFERENCES RESERVATION (reservation_id);
    230 ALTER TABLE TICKET
    231     ADD CONSTRAINT FKTICKET680705 FOREIGN KEY (seat_id) REFERENCES SEAT (seat_id);
    232 ALTER TABLE SEAT
    233     ADD CONSTRAINT FKSEAT164380 FOREIGN KEY (cinemahall_id) REFERENCES CINEMA_HALL (cinemahall_id);
    234 ALTER TABLE SEAT
    235     ADD CONSTRAINT FKSEAT804590 FOREIGN KEY (type_id) REFERENCES SEAT_TYPE (seattype_id);
    236 ALTER TABLE SCREENING
    237     ADD CONSTRAINT FKSCREENING975220 FOREIGN KEY (cinemahall_id) REFERENCES CINEMA_HALL (cinemahall_id);
    238 ALTER TABLE CINEMA_HALL
    239     ADD CONSTRAINT FKCINEMA_HAL154643 FOREIGN KEY (cinema_id) REFERENCES CINEMA (cinema_id);
    240 ALTER TABLE TICKET
    241     ADD CONSTRAINT FKTICKET411280 FOREIGN KEY (screening_id) REFERENCES SCREENING (screening_id);
    242 ALTER TABLE PURCHASED_PRODUCT
    243     ADD CONSTRAINT FKPURCHASED_989966 FOREIGN KEY (ticket_id) REFERENCES TICKET (ticket_id);
    244 ALTER TABLE SCREENING
    245     ADD CONSTRAINT FKSCREENING176236 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id);
    246 ALTER TABLE PROMOTION
    247     ADD CONSTRAINT FKPROMOTION365376 FOREIGN KEY (screening_id) REFERENCES SCREENING (screening_id);
    248 ALTER TABLE MOVIE_GENRE
    249     ADD CONSTRAINT FKMOVIE_GENR273664 FOREIGN KEY (genre_id) REFERENCES GENRE (genre_id);
    250 ALTER TABLE MOVIE_GENRE
    251     ADD CONSTRAINT FKMOVIE_GENR655579 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id);
    252 ALTER TABLE MOVIE_ACTOR
    253     ADD CONSTRAINT FKMOVIE_ACTO60552 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id);
    254 ALTER TABLE MOVIE_ACTOR
    255     ADD CONSTRAINT FKMOVIE_ACTO38053 FOREIGN KEY (actor_id) REFERENCES ACTOR (actor_id);
    256 ALTER TABLE MOVIE_DIRECTOR
    257     ADD CONSTRAINT FKMOVIE_DIRE300372 FOREIGN KEY (director_id) REFERENCES DIRECTOR (director_id);
    258 ALTER TABLE MOVIE_DIRECTOR
    259     ADD CONSTRAINT FKMOVIE_DIRE810181 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id);
    260 ALTER TABLE "USER"
    261     ADD CONSTRAINT FKUSER65207 FOREIGN KEY (role_id) REFERENCES ROLE (role_id);
    262 ALTER TABLE PURCHASED_PRODUCT
    263     ADD CONSTRAINT FKPURCHASED_466460 FOREIGN KEY (product_id) REFERENCES PRODUCT (product_id);
    264 ALTER TABLE ROLE_PERMISSION
    265     ADD CONSTRAINT FKROLE_PERMI829363 FOREIGN KEY (role_id) REFERENCES ROLE (role_id);
    266 ALTER TABLE ROLE_PERMISSION
    267     ADD CONSTRAINT FKROLE_PERMI727346 FOREIGN KEY (permission_id) REFERENCES PERMISSION (permission_id);
    268 ALTER TABLE NOTIFICATION
    269     ADD CONSTRAINT FKNOTIFICATI106294 FOREIGN KEY (user_id) REFERENCES "USER" (user_id);
    270 ALTER TABLE CINEMA
    271     ADD CONSTRAINT FKCINEMA56868 FOREIGN KEY (city_id) REFERENCES CITY (city_id);
    272 ALTER TABLE "USER"
    273     ADD CONSTRAINT FKUSER561546 FOREIGN KEY (cinema_id) REFERENCES CINEMA (cinema_id);
     7
     8=== Views
     9[[html(<a href="">views.sql</a>)]]