RelationalDesign: V1__init_db.sql

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