Changes between Version 1 and Version 2 of DatabaseCreation


Ignore:
Timestamp:
04/18/26 20:54:05 (2 weeks ago)
Author:
231233
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseCreation

    v1 v2  
    1 fyiukr
     1CREATE 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);
     10CREATE TABLE ROLE
     11(
     12    role_id   SERIAL       NOT NULL,
     13    role_name varchar(255) NOT NULL,
     14    PRIMARY KEY (role_id)
     15);
     16CREATE 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);
     23CREATE TABLE GENRE
     24(
     25    genre_id SERIAL      NOT NULL,
     26    name     varchar(50) NOT NULL,
     27    PRIMARY KEY (genre_id)
     28);
     29CREATE 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);
     36CREATE 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);
     44CREATE 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);
     54CREATE 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);
     61CREATE 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);
     71CREATE 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);
     80CREATE 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);
     87CREATE 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);
     96CREATE 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);
     105CREATE 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);
     116CREATE TABLE CITY
     117(
     118    name    varchar(50) NOT NULL,
     119    city_id SERIAL      NOT NULL,
     120    PRIMARY KEY (city_id)
     121);
     122CREATE 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);
     129CREATE 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);
     139CREATE 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);
     147CREATE 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);
     156CREATE 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);
     164CREATE 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);
     174CREATE 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);
     180
     181CREATE TABLE MOVIE_GENRE
     182(
     183    genre_id int4 NOT NULL,
     184    movie_id int4 NOT NULL,
     185    PRIMARY KEY (genre_id, movie_id)
     186);
     187
     188CREATE 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);
     194
     195CREATE 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);
     201CREATE 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);
     208CREATE TABLE ROLE_PERMISSION
     209(
     210    role_id int4 NOT NULL,
     211    permission_id int4 NOT NULL,
     212    PRIMARY KEY (role_id, permission_id)
     213);
     214ALTER TABLE REVIEW
     215    ADD CONSTRAINT FKREVIEW298251 FOREIGN KEY (user_id) REFERENCES "USER" (user_id);
     216ALTER TABLE REVIEW
     217    ADD CONSTRAINT FKREVIEW927899 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id);
     218ALTER TABLE TICKET
     219    ADD CONSTRAINT FKTICKET285003 FOREIGN KEY (user_id) REFERENCES "USER" (user_id);
     220ALTER TABLE USER_RESERVATION
     221    ADD CONSTRAINT FKUSER_RESER151935 FOREIGN KEY (user_id) REFERENCES "USER" (user_id);
     222ALTER TABLE USER_RESERVATION
     223    ADD CONSTRAINT FKUSER_RESER867618 FOREIGN KEY (reservation_id) REFERENCES RESERVATION (reservation_id);
     224ALTER TABLE PAYMENT
     225    ADD CONSTRAINT FKPAYMENT601419 FOREIGN KEY (reservation_id) REFERENCES RESERVATION (reservation_id);
     226ALTER TABLE PAYMENT_METHOD
     227    ADD CONSTRAINT FKPAYMENT_ME706637 FOREIGN KEY (payment_id) REFERENCES PAYMENT (payment_id);
     228ALTER TABLE TICKET
     229    ADD CONSTRAINT FKTICKET332966 FOREIGN KEY (reservation_id) REFERENCES RESERVATION (reservation_id);
     230ALTER TABLE TICKET
     231    ADD CONSTRAINT FKTICKET680705 FOREIGN KEY (seat_id) REFERENCES SEAT (seat_id);
     232ALTER TABLE SEAT
     233    ADD CONSTRAINT FKSEAT164380 FOREIGN KEY (cinemahall_id) REFERENCES CINEMA_HALL (cinemahall_id);
     234ALTER TABLE SEAT
     235    ADD CONSTRAINT FKSEAT804590 FOREIGN KEY (type_id) REFERENCES SEAT_TYPE (seattype_id);
     236ALTER TABLE SCREENING
     237    ADD CONSTRAINT FKSCREENING975220 FOREIGN KEY (cinemahall_id) REFERENCES CINEMA_HALL (cinemahall_id);
     238ALTER TABLE CINEMA_HALL
     239    ADD CONSTRAINT FKCINEMA_HAL154643 FOREIGN KEY (cinema_id) REFERENCES CINEMA (cinema_id);
     240ALTER TABLE TICKET
     241    ADD CONSTRAINT FKTICKET411280 FOREIGN KEY (screening_id) REFERENCES SCREENING (screening_id);
     242ALTER TABLE PURCHASED_PRODUCT
     243    ADD CONSTRAINT FKPURCHASED_989966 FOREIGN KEY (ticket_id) REFERENCES TICKET (ticket_id);
     244ALTER TABLE SCREENING
     245    ADD CONSTRAINT FKSCREENING176236 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id);
     246ALTER TABLE PROMOTION
     247    ADD CONSTRAINT FKPROMOTION365376 FOREIGN KEY (screening_id) REFERENCES SCREENING (screening_id);
     248ALTER TABLE MOVIE_GENRE
     249    ADD CONSTRAINT FKMOVIE_GENR273664 FOREIGN KEY (genre_id) REFERENCES GENRE (genre_id);
     250ALTER TABLE MOVIE_GENRE
     251    ADD CONSTRAINT FKMOVIE_GENR655579 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id);
     252ALTER TABLE MOVIE_ACTOR
     253    ADD CONSTRAINT FKMOVIE_ACTO60552 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id);
     254ALTER TABLE MOVIE_ACTOR
     255    ADD CONSTRAINT FKMOVIE_ACTO38053 FOREIGN KEY (actor_id) REFERENCES ACTOR (actor_id);
     256ALTER TABLE MOVIE_DIRECTOR
     257    ADD CONSTRAINT FKMOVIE_DIRE300372 FOREIGN KEY (director_id) REFERENCES DIRECTOR (director_id);
     258ALTER TABLE MOVIE_DIRECTOR
     259    ADD CONSTRAINT FKMOVIE_DIRE810181 FOREIGN KEY (movie_id) REFERENCES MOVIE (movie_id);
     260ALTER TABLE "USER"
     261    ADD CONSTRAINT FKUSER65207 FOREIGN KEY (role_id) REFERENCES ROLE (role_id);
     262ALTER TABLE PURCHASED_PRODUCT
     263    ADD CONSTRAINT FKPURCHASED_466460 FOREIGN KEY (product_id) REFERENCES PRODUCT (product_id);
     264ALTER TABLE ROLE_PERMISSION
     265    ADD CONSTRAINT FKROLE_PERMI829363 FOREIGN KEY (role_id) REFERENCES ROLE (role_id);
     266ALTER TABLE ROLE_PERMISSION
     267    ADD CONSTRAINT FKROLE_PERMI727346 FOREIGN KEY (permission_id) REFERENCES PERMISSION (permission_id);
     268ALTER TABLE NOTIFICATION
     269    ADD CONSTRAINT FKNOTIFICATI106294 FOREIGN KEY (user_id) REFERENCES "USER" (user_id);
     270ALTER TABLE CINEMA
     271    ADD CONSTRAINT FKCINEMA56868 FOREIGN KEY (city_id) REFERENCES CITY (city_id);
     272ALTER TABLE "USER"
     273    ADD CONSTRAINT FKUSER561546 FOREIGN KEY (cinema_id) REFERENCES CINEMA (cinema_id);