neon-database/db.sql at main · SOA-2/neon-database · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
-- This script was generated by the ERD tool in pgAdmin 4.
-- Please log an issue at https://github.com/pgadmin-org/pgadmin4/issues/new/choose if you find any bugs, including reproduction steps.
BEGIN;


CREATE TABLE IF NOT EXISTS public.fare
(
    id uuid NOT NULL,
    name character varying(200) COLLATE pg_catalog."default" NOT NULL,
    price double precision NOT NULL,
    CONSTRAINT fare_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.movie
(
    id uuid NOT NULL,
    name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    real_name character varying(200) COLLATE pg_catalog."default",
    clasification character varying(100) COLLATE pg_catalog."default" NOT NULL,
    image character varying(200) COLLATE pg_catalog."default" NOT NULL,
    movie_genre_id uuid NOT NULL,
    release_date date NOT NULL,
    duration numeric NOT NULL,
    context character varying(700) COLLATE pg_catalog."default" NOT NULL,
    trailer character varying(100) COLLATE pg_catalog."default" NOT NULL,
    director character varying(50) COLLATE pg_catalog."default" NOT NULL,


    CONSTRAINT movie_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.movie_genre
(
    id uuid NOT NULL,
    genre_name character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT movie_genre_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.reservation
(
    id uuid NOT NULL,
    show_id uuid NOT NULL,
    user_id uuid NOT NULL,
    reservation_date time with time zone NOT NULL,
    special_code double precision,
    CONSTRAINT reservation_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.screen
(
    id uuid NOT NULL,
    theatre_id uuid NOT NULL,
    fare_id uuid NOT NULL,
    CONSTRAINT screen_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.seat
(
    id uuid NOT NULL,
    letter character varying(50) COLLATE pg_catalog."default" NOT NULL,
    "number" character varying(50) COLLATE pg_catalog."default" NOT NULL,
    screen_id uuid NOT NULL,
    CONSTRAINT seat_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.seat_reserved
(
    id uuid NOT NULL,
    reservation_id uuid NOT NULL,
    seat_id uuid NOT NULL,
    price double precision NOT NULL,
    CONSTRAINT seat_reserved_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.show
(
    id uuid NOT NULL,
    movie_id uuid NOT NULL,
    screen_id uuid NOT NULL,
    date date NOT NULL,
    theatre_id uuid NOT NULL,
    "time" time with time zone NOT NULL,
    CONSTRAINT show_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.theatre
(
    id uuid NOT NULL,
    name character varying(200) COLLATE pg_catalog."default" NOT NULL,
    telephone character varying(20) COLLATE pg_catalog."default" NOT NULL,
    city character varying(30) COLLATE pg_catalog."default",
    address character varying(50) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT theatre_pkey PRIMARY KEY (id)
);

ALTER TABLE IF EXISTS public.movie
    ADD CONSTRAINT fk_movie_genre_id FOREIGN KEY (movie_genre_id)
    REFERENCES public.movie_genre (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.reservation
    ADD CONSTRAINT fk_show_id FOREIGN KEY (show_id)
    REFERENCES public.show (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.screen
    ADD CONSTRAINT fk_fare_id FOREIGN KEY (fare_id)
    REFERENCES public.fare (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.screen
    ADD CONSTRAINT fk_theatre_id FOREIGN KEY (theatre_id)
    REFERENCES public.theatre (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.seat
    ADD CONSTRAINT fk_screen_id FOREIGN KEY (screen_id)
    REFERENCES public.screen (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.seat_reserved
    ADD CONSTRAINT fk_reservation_id FOREIGN KEY (reservation_id)
    REFERENCES public.reservation (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.seat_reserved
    ADD CONSTRAINT fk_seat_id FOREIGN KEY (seat_id)
    REFERENCES public.seat (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.show
    ADD CONSTRAINT fk_movie_id FOREIGN KEY (movie_id)
    REFERENCES public.movie (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.show
    ADD CONSTRAINT fk_screen_id FOREIGN KEY (screen_id)
    REFERENCES public.screen (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.show
    ADD CONSTRAINT fk_theatre_id FOREIGN KEY (theatre_id)
    REFERENCES public.theatre (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;

END;