AdvancedApplicationDesign: V1__init_db.sql

File V1__init_db.sql, 10.5 KB (added by 216049, 14 months ago)
Line 
1-- set search_path = project_new;
2
3drop table if exists Se_simnuva_na;
4--drop table if exists Se_validira;
5drop table if exists KaznaZaRegistriran;
6drop table if exists KaznaZaNeregistriran;
7drop table if exists Kazna;
8drop table if exists Kontroli;
9drop table if exists Vozenje;
10drop table if exists Bilet;
11drop table if exists TipBilet;
12drop table if exists PostojkaNaLinija;
13drop table if exists Postojka;
14drop table if exists Mesto;
15drop table if exists InstancaNaLinija;
16drop table if exists Linija;
17drop table if exists Avtobus;
18--drop table if exists Dokument;
19drop table if exists Kondukter;
20drop table if exists Vozac;
21drop table if exists Vraboten;
22drop table if exists Patnik;
23drop table if exists KorisnikRole;
24drop table if exists Korisnik;
25drop table if exists Role;
26
27
28--drop domain if exists string_dolg;
29--drop domain if exists string_kratok;
30
31--drop schema if exists project_new;
32
33--create schema project_new;
34
35--set search_path = project_new;
36
37--create domain string_dolg AS character varying(4000);
38--create domain string_kratok AS character varying(500);
39
40
41
42create table Korisnik(
43 is_admin boolean,
44 id bigserial primary key,
45 adresa character varying(255),
46 email character varying(255),
47 ime character varying(255),
48 lozinka character varying(255),
49 telefon character varying(255)
50);
51
52create table Role (
53 id bigserial primary key,
54 description character varying(255),
55 name character varying(255)
56);
57
58create table Korisnik_Role (
59 id bigserial primary key,
60 korisnik_id bigint,
61 role_id bigint,
62 foreign key (role_id) references role (id),
63 foreign key (korisnik_id) references korisnik (id)
64);
65
66create table Patnik(
67 id bigserial primary key,
68 korisnik_id bigint,
69 foreign key (korisnik_id) references korisnik (id)
70);
71-- *k_id referencira od Korisnik(k_id)
72
73
74create table Vraboten(
75 datum_na_vrabotuvanje date,
76 datum_prekin_vrabotuvanje date,
77 plata double precision,
78 id bigserial primary key,
79 korisnik_id bigint,
80 foreign key (korisnik_id) references korisnik (id)
81);
82-- *k_id referencira od Korisnik(k_id)
83
84
85create table Vozac(
86 id bigserial primary key,
87 vraboten_id bigint,
88 foreign key (vraboten_id) references vraboten (id)
89);
90-- *k_id referencira od Korisnik(k_id)
91
92create table Kondukter(
93 id bigserial primary key,
94 vraboten_id bigint,
95 foreign key (vraboten_id) references vraboten (id)
96);
97-- *k_id referencira od Korisnik(k_id)
98
99--create table Dokument(
100-- d_broj_na_dokumnet string_kratok primary key,
101-- d_datum_na_izdavanje date not null,
102-- d_datum_ist date not null,
103-- d_koj_go_izdal string_kratok not null,
104-- k_id bigint not null,
105-- constraint dokument_za_covek foreign key (k_id) references Korisnik(k_id)
106--);
107
108create table Avtobus(
109 broj_sedishta smallint,
110 id bigserial primary key,
111 registracija character varying(255),
112 seriski_broj character varying(255)
113);
114
115create table Linija(
116 id serial primary key,
117 ime character varying(255)
118);
119
120create table Pravec (
121 id bigserial primary key,
122 pravec character varying(255),
123 opis character varying(255)
124);
125
126create table linija_pravec (
127 id bigserial primary key,
128 pravec_id bigint,
129 linija_id integer,
130 foreign key (pravec_id) references Pravec (id),
131 foreign key (linija_id) references Linija (id)
132);
133
134create table Instanca_Na_Linija (
135 linija_id integer,
136 pravec_id bigint,
137 avtobus_id bigint,
138 end_date timestamp(6) without time zone,
139 id bigserial primary key,
140 start_date timestamp(6) without time zone,
141 vozac_id bigint,
142 foreign key (avtobus_id) references avtobus (id),
143 foreign key (linija_id) references linija (id),
144 foreign key (vozac_id) references vozac (id)
145);
146-- *k_id referencira od Korisnik(k_id)
147-- ^a_registracija referncira od Avtobus(a_registracija)
148-- #li_id referencira od Linija(li_id)
149
150--create table Mesto(
151-- m_id serial primary key,
152-- m_grad string_kratok not null,
153-- m_opstina string_kratok not null,
154-- m_ulica string_kratok not null
155--);
156
157create table Postojka (
158 id serial primary key,
159 lat double precision,
160 lon double precision,
161 ime character varying(255),
162 opis character varying(255)
163);
164--*m_id referncira od Mesto(m_id)
165
166
167create table Postojka_Na_Linija (
168 linija_id integer,
169 postojka_id integer,
170 pravec_id bigint,
171 reden_broj smallint,
172 id bigserial primary key,
173 foreign key (postojka_id) references postojka (id),
174 foreign key (linija_id) references linija (id)
175);
176-- *li_id referencira od Linija(li_id)
177-- ^p_id referencira od Postojka(p_id)
178
179create table instanca_na_linija_postojka_na_linija (
180 id bigserial primary key,
181 instanca_na_linija_id bigint not null,
182 postojka_na_linija_id bigint not null,
183 timestamp timestamp not null,
184 foreign key (instanca_na_linija_id) references instanca_na_linija(id),
185 foreign key (postojka_na_linija_id) references postojka_na_linija(id)
186);
187
188create table TipBilet (
189 cena real,
190 id bigserial primary key,
191 trajnost bigint,
192 ime character varying(255)
193);
194
195
196
197create table Bilet (
198 datum_kupuvanje timestamp(6) without time zone,
199 datum_aktivacija timestamp(6) without time zone,
200 id bigserial primary key,
201 patnik_id bigint,
202 tip_id bigint,
203 status character varying(255),
204 foreign key (patnik_id) references patnik (id),
205 foreign key (tip_id) references tipbilet (id)
206);
207
208
209create table Vozenje (
210 bilet_id bigint,
211 end_date timestamp(6) without time zone,
212 id bigserial primary key,
213 instanca_na_linija_id bigint,
214 patnik_id bigint,
215 postojka_na_linija_start_id bigint,
216 start_date timestamp(6) without time zone,
217 status character varying(255),
218 foreign key (patnik_id) references patnik (id),
219 foreign key (bilet_id) references bilet (id),
220 foreign key (instanca_na_linija_id) references instanca_na_linija (id),
221 foreign key (postojka_na_linija_start_id) references postojka_na_linija (id)
222);
223
224
225create table Kontrola (
226 date_created timestamp(6) without time zone,
227 id bigserial primary key,
228 instanca_na_linija_id bigint,
229 kondukter_id bigint,
230 foreign key (instanca_na_linija_id) references instanca_na_linija (id),
231 foreign key (kondukter_id) references kondukter (id)
232);
233
234create table Kazna (
235 iznos double precision,
236 plateno boolean,
237 date_created timestamp(6) without time zone,
238 date_payed timestamp(6) without time zone,
239 id bigserial primary key,
240 kondukter_id bigint,
241 kontrola_id bigint,
242 dokument character varying(255),
243 foreign key (kondukter_id) references Kondukter (id),
244 foreign key (kontrola_id) references kontrola (id)
245);
246
247
248create table Kazna_Za_Registriran(
249 id bigserial primary key,
250 kazna_id bigint,
251 patnik_id bigint,
252 foreign key (patnik_id) references patnik (id),
253 foreign key (kazna_id) references kazna (id)
254);
255
256create table kazna_za_neregistriran(
257 id bigserial primary key,
258 kazna_id bigint,
259 adresa character varying(255),
260 ime character varying(255),
261 telefon character varying(255),
262 foreign key (kazna_id) references kazna (id)
263);
264
265
266
267--create table Se_validira (
268-- vozenje_id bigint not null,
269-- b_id bigint not null,
270-- primary key (vozenje_id, b_id),
271-- foreign key (vozenje_id) references Vozenje(vozenje_id),
272-- foreign key (b_id) references Bilet(b_id)
273--);
274---- * vozenje_id referencira od Vozenje(vozenje_id)
275---- ^ b_id referencira od Bilet(b_id)
276
277
278create table se_simnuva_na (
279 id bigserial primary key,
280 postojka_na_linija_id bigint,
281 vozenje_id bigint,
282 foreign key (vozenje_id) references vozenje (id),
283 foreign key (postojka_na_linija_id) references postojka_na_linija (id)
284);