· 8 years ago · Apr 02, 2018, 04:32 PM
1CREATE TABLE IF NOT EXISTS ADMINISTRACIJA
2(
3 BROJ_DOZVOLJENIH_VAGA INTEGER NOT NULL,
4 BROJ_DOZVOLJENIH_RADNIH_TERMOMETARA INTEGER NOT NULL,
5 BROJ_DOZVOLJENIH_PIPETA INTEGER NOT NULL,
6 DATUM_ISTEKA_LICENCE DATE NOT NULL
7);
8
9INSERT INTO ADMINISTRACIJA (BROJ_DOZVOLJENIH_VAGA, BROJ_DOZVOLJENIH_RADNIH_TERMOMETARA, BROJ_DOZVOLJENIH_PIPETA, DATUM_ISTEKA_LICENCE) VALUES (20, 20, 20, date('2019-4-2'));
10
11CREATE TABLE IF NOT EXISTS NALOG
12(
13 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
14 KORISNICKO_IME TEXT NOT NULL,
15 LOZINKA TEXT NOT NULL,
16 TIP INTEGER NOT NULL
17);
18
19INSERT INTO NALOG (KORISNICKO_IME, LOZINKA, TIP) VALUES ('Admin', 'admin1', 0);
20
21CREATE TABLE IF NOT EXISTS VAGA
22(
23 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
24 PROIZVODJAC TEXT,
25 TIP TEXT,
26 SERIJSKI_BROJ TEXT NOT NULL,
27 INVENTARNI_BROJ TEXT,
28 GODINA_PROIZVODNJE INTEGER,
29 LOKACIJA TEXT,
30 ORGANIZACIONA_JEDINICA TEXT,
31 LOKACIJA_SLIKE TEXT
32);
33
34CREATE TABLE IF NOT EXISTS PRISTUP_VAGE
35(
36 NALOG_ID INTEGER NOT NULL,
37 VAGA_ID INTEGER NOT NULL,
38 FOREIGN KEY(NALOG_ID) REFERENCES NALOG(ID) ON DELETE CASCADE,
39 FOREIGN KEY(VAGA_ID) REFERENCES VAGA(ID) ON DELETE CASCADE
40);
41
42CREATE TABLE IF NOT EXISTS OPSEG_VAGE
43(
44 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
45 VAGA_ID INTEGER NOT NULL,
46 GORNJI_OPSEG REAL NOT NULL,
47 DONJI_OPSEG REAL NOT NULL,
48 PODELJAK REAL NOT NULL,
49 FOREIGN KEY(VAGA_ID) REFERENCES VAGA(ID) ON DELETE CASCADE
50);
51
52CREATE TABLE IF NOT EXISTS UVERENJE_VAGE
53(
54 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
55 OPSEG_VAGE_ID INTEGER NOT NULL,
56 BROJ_UVERENJA TEXT,
57 DATUM_ETALONIRANJA DATE,
58 LABORATORIJA_KOJA_JE_ETALONIRALA TEXT,
59 PERIOD_ETALONIRANJA INTEGER,
60 BROJ_ETALONIRANJA INTEGER NOT NULL,
61 BROJ_PONAVLJANJA INTEGER NOT NULL,
62 NOMINALNA_MASA INTEGER NOT NULL,
63 STANDARDNA_DEVIJACIJA REAL NOT NULL,
64 FOREIGN KEY(OPSEG_VAGE_ID) REFERENCES OPSEG_VAGE(ID) ON DELETE CASCADE
65);
66
67CREATE TABLE IF NOT EXISTS TACKA_PROVERE_UVERENJA_VAGE
68(
69 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
70 UVERENJE_VAGE_ID INTEGER NOT NULL,
71 TACKA_PROVERE REAL NOT NULL,
72 ODSTUPANJE REAL NOT NULL,
73 FOREIGN KEY(UVERENJE_VAGE_ID) REFERENCES UVERENJE_VAGE(ID) ON DELETE CASCADE
74);
75
76CREATE TABLE IF NOT EXISTS REFERENTNA_VREDNOST_PONOVLJIVOSTI_VAGE
77(
78 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
79 UVERENJE_VAGE_ID INTEGER NOT NULL,
80 NOMINALNA_MASA REAL NOT NULL,
81 BROJ_MERENJA INTEGER NOT NULL,
82 REFERENTNA_VREDNOST REAL NOT NULL,
83 FOREIGN KEY(UVERENJE_VAGE_ID) REFERENCES UVERENJE_VAGE(ID) ON DELETE CASCADE
84);
85
86CREATE TABLE IF NOT EXISTS REZULTAT_REFERENTNE_VREDNOSTI_PONOVLJIVOSTI_VAGE
87(
88 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
89 REFERENTNA_VREDNOST_PONOVLJIVOSTI_VAGE_ID INTEGER NOT NULL,
90 REZULTAT_MERENJA REAL NOT NULL,
91 FOREIGN KEY(REFERENTNA_VREDNOST_PONOVLJIVOSTI_VAGE_ID) REFERENCES REFERENTNA_VREDNOST_PONOVLJIVOSTI_VAGE(ID) ON DELETE CASCADE
92);
93
94CREATE TABLE IF NOT EXISTS TEG_PONOVLJIVOSTI_VAGE
95(
96 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
97 REFERENTNA_VREDNOST_PONOVLJIVOSTI_VAGE_ID INTEGER NOT NULL,
98 PROIZVODJAC TEXT,
99 KLASA TEXT,
100 UVERENJE TEXT,
101 SERIJSKI_BROJ TEXT,
102 NOMINALNA_MASA REAL NOT NULL,
103 FOREIGN KEY (REFERENTNA_VREDNOST_PONOVLJIVOSTI_VAGE_ID) REFERENCES REFERENTNA_VREDNOST_PONOVLJIVOSTI_VAGE(ID) ON DELETE CASCADE
104);
105
106CREATE TABLE IF NOT EXISTS TEST_PONOVLJIVOSTI_VAGE
107(
108 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
109 REFERENTNA_VREDNOST_PONOVLJIVOSTI_VAGE_ID INTEGER NOT NULL,
110 NALOG_ID INTEGER NOT NULL,
111 REDNI_BROJ_TESTA INTEGER NOT NULL,
112 STATUS INTEGER NOT NULL,
113 DATUM DATE NOT NULL,
114 FOREIGN KEY(NALOG_ID) REFERENCES NALOG(ID) ON DELETE CASCADE,
115 FOREIGN KEY (REFERENTNA_VREDNOST_PONOVLJIVOSTI_VAGE_ID) REFERENCES REFERENTNA_VREDNOST_PONOVLJIVOSTI_VAGE(ID) ON DELETE CASCADE
116);
117
118CREATE TABLE IF NOT EXISTS REZULTAT_MERENJA_TESTA_PONOVLJIVOSTI_VAGE
119(
120 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
121 TEST_PONOVLJIVOSTI_VAGE_ID INTEGER NOT NULL,
122 REZULTAT_MERENJA REAL NOT NULL,
123 FOREIGN KEY(TEST_PONOVLJIVOSTI_VAGE_ID) REFERENCES TEST_PONOVLJIVOSTI_VAGE(ID) ON DELETE CASCADE
124);
125
126CREATE TABLE IF NOT EXISTS REFERENTNA_VREDNOST_TACNOSTI_VAGE
127(
128 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
129 UVERENJE_VAGE_ID INTEGER NOT NULL,
130 PERIODICNOST_TESTA TEXT NOT NULL,
131 FOREIGN KEY(UVERENJE_VAGE_ID) REFERENCES UVERENJE_VAGE(ID) ON DELETE CASCADE
132);
133
134CREATE TABLE IF NOT EXISTS TACKA_PROVERE_REFERENTNE_VREDNOSTI_TACNOSTI_VAGE
135(
136 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
137 REFERENTNA_VREDNOST_TACNOSTI_VAGE_ID INTEGER NOT NULL,
138 NOMINALNA_MASA REAL NOT NULL,
139 REFERENTNA_VREDNOST REAL NOT NULL,
140 FOREIGN KEY(REFERENTNA_VREDNOST_TACNOSTI_VAGE_ID) REFERENCES REFERENTNA_VREDNOST_TACNOSTI_VAGE(ID) ON DELETE CASCADE
141);
142
143CREATE TABLE IF NOT EXISTS REZULTAT_MERENJA_REFERENTNE_VREDNOSTI_TACNOSTI_VAGE
144(
145 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
146 TACKA_PROVERE_REFERENTNE_VREDNOSTI_TACNOSTI_VAGE_ID INTEGER NOT NULL,
147 REZULTAT_MERENJA REAL NOT NULL,
148 FOREIGN KEY(TACKA_PROVERE_REFERENTNE_VREDNOSTI_TACNOSTI_VAGE_ID) REFERENCES TACKA_PROVERE_REFERENTNE_VREDNOSTI_TACNOSTI_VAGE(ID) ON DELETE CASCADE
149);
150
151CREATE TABLE IF NOT EXISTS TEST_TACNOSTI_VAGE
152(
153 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
154 REFERENTNA_VREDNOST_TACNOSTI_VAGE_ID INTEGER NOT NULL,
155 NALOG_ID INTEGER,
156 REDNI_BROJ_TESTA INTEGER NOT NULL,
157 STATUS INTEGER NOT NULL,
158 DATUM DATE,
159 FOREIGN KEY(NALOG_ID) REFERENCES NALOG(ID) ON DELETE CASCADE,
160 FOREIGN KEY (REFERENTNA_VREDNOST_TACNOSTI_VAGE_ID) REFERENCES REFERENTNA_VREDNOST_TACNOSTI_VAGE(ID) ON DELETE CASCADE
161);
162
163CREATE TABLE IF NOT EXISTS MERENJE_TESTA_TACNOSTI_VAGE
164(
165 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
166 TEST_TACNOSTI_VAGE_ID INTEGER NOT NULL,
167 REZULTAT_MERENJA REAL NOT NULL,
168 STATUS INTEGER NOT NULL,
169 FOREIGN KEY (TEST_TACNOSTI_VAGE_ID) REFERENCES TEST_TACNOSTI_VAGE(ID) ON DELETE CASCADE
170);
171
172CREATE TABLE IF NOT EXISTS TEG_TACNOSTI_VAGE
173(
174 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
175 REFERENTNA_VREDNOST_TACNOSTI_VAGE_ID INTEGER NOT NULL,
176 TACKA_PROVERE INTEGER NOT NULL,
177 PROIZVODJAC TEXT,
178 KLASA TEXT,
179 UVERENJE TEXT,
180 SERIJSKI_BROJ TEXT,
181 NOMINALNA_MASA REAL NOT NULL,
182 FOREIGN KEY (REFERENTNA_VREDNOST_TACNOSTI_VAGE_ID) REFERENCES REFERENTNA_VREDNOST_TACNOSTI_VAGE(ID) ON DELETE CASCADE
183);
184
185/* KRAJ VAGA POCETAK RADNOG TERMOMETRA --------------------------------------------------------------------------------------------------------------*/
186
187CREATE TABLE IF NOT EXISTS REFERENTNI_TERMOMETAR
188(
189 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
190 PROIZVODJAC TEXT,
191 TIP TEXT,
192 SERIJSKI_BROJ TEXT NOT NULL,
193 INVENTARNI_BROJ TEXT,
194 LOKACIJA TEXT,
195 GODINA_PROIZVODNJE INTEGER,
196 ORGANIZACIONA_JEDINICA TEXT,
197 LOKACIJA_SLIKE TEXT
198);
199
200CREATE TABLE IF NOT EXISTS MERNA_NESIGURNOST_REFERENTNOG_TERMOMETRA
201(
202 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
203 REFERENTNI_TERMOMETAR_ID INTEGER NOT NULL,
204 TACKA_PROVERE REAL NOT NULL,
205 MERNA_NESIGURNOST REAL NOT NULL,
206 FOREIGN KEY(REFERENTNI_TERMOMETAR_ID) REFERENCES REFERENTNI_TERMOMETAR(ID) ON DELETE CASCADE
207);
208
209CREATE TABLE IF NOT EXISTS UVERENJE_REFERENTNOG_TERMOMETRA
210(
211 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
212 REFERENTNI_TERMOMETAR_ID INTEGER NOT NULL,
213 BROJ_UVERENJA TEXT,
214 DATUM_ETALONIRANJA DATE,
215 LABORATORIJA_KOJA_JE_ETALONIRALA TEXT,
216 PERIOD_ETALONIRANJA INTEGER,
217 BROJ_ETALONIRANJA INTEGER NOT NULL,
218 FOREIGN KEY(REFERENTNI_TERMOMETAR_ID) REFERENCES REFERENTNI_TERMOMETAR(ID) ON DELETE CASCADE
219);
220
221CREATE TABLE IF NOT EXISTS RADNI_TERMOMETAR
222(
223 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
224 PROIZVODJAC TEXT,
225 TIP TEXT,
226 SERIJSKI_BROJ TEXT NOT NULL,
227 INVENTARNI_BROJ TEXT,
228 GODINA_PROIZVODNJE INTEGER,
229 LOKACIJA TEXT,
230 ORGANIZACIONA_JEDINICA TEXT,
231 LOKACIJA_SLIKE TEXT,
232 NAMENA TEXT,
233 GRANICA_GRESKE REAL NOT NULL
234);
235
236CREATE TABLE IF NOT EXISTS OPSEG_RADNOG_TERMOMETRA
237(
238 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
239 RADNI_TERMOMETAR_ID INTEGER NOT NULL,
240 GORNJI_OPSEG REAL NOT NULL,
241 DONJI_OPSEG REAL NOT NULL,
242 PODELJAK REAL NOT NULL,
243 REFERENTNI_TERMOMETAR_ID INTEGER NOT NULL,
244 FOREIGN KEY(RADNI_TERMOMETAR_ID) REFERENCES RADNI_TERMOMETAR(ID) ON DELETE CASCADE,
245 FOREIGN KEY(REFERENTNI_TERMOMETAR_ID) REFERENCES REFERENTNI_TERMOMETAR(ID) ON DELETE CASCADE
246);
247
248CREATE TABLE IF NOT EXISTS TACKA_PROVERE_OPSEGA_RADNOG_TERMOMETRA
249(
250 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
251 OPSEG_RADNOG_TERMOMETRA_ID INTEGER NOT NULL,
252 TACKA_PROVERE REAL NOT NULL,
253 FOREIGN KEY(OPSEG_RADNOG_TERMOMETRA_ID) REFERENCES OPSEG_RADNOG_TERMOMETRA(ID) ON DELETE CASCADE
254);
255
256CREATE TABLE IF NOT EXISTS TEST_RADNOG_TERMOMETRA
257(
258 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
259 OPSEG_RADNOG_TERMOMETRA_ID INTEGER NOT NULL,
260 NALOG_ID INTEGER NOT NULL,
261 REDNI_BROJ_TESTA INTEGER NOT NULL,
262 STATUS INTEGER NOT NULL,
263 DATUM DATE NOT NULL,
264 FOREIGN KEY(OPSEG_RADNOG_TERMOMETRA_ID) REFERENCES OPSEG_RADNOG_TERMOMETRA(ID) ON DELETE CASCADE,
265 FOREIGN KEY(NALOG_ID) REFERENCES NALOG(ID) ON DELETE CASCADE
266);
267
268CREATE TABLE IF NOT EXISTS VREDNOST_TESTA_RADNOG_TERMOMETRA
269(
270 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
271 TEST_RADNOG_TERMOMETRA_ID INTEGER NOT NULL,
272 TACKA_PROVERE REAL NOT NULL,
273 IZMERENA_TEMPERATURA REAL NOT NULL,
274 STATUS INTEGER NOT NULL,
275 FOREIGN KEY(TEST_RADNOG_TERMOMETRA_ID) REFERENCES TEST_RADNOG_TERMOMETRA(ID) ON DELETE CASCADE
276);
277
278/* KRAJ TERMOMETARA POCETAK PIPETA --------------------------------------------------------------------------------------------------------------*/
279
280/*CREATE TABLE IF NOT EXISTS PIPETA
281(
282 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
283 PROIZVODJAC TEXT,
284 TIP TEXT,
285 SERIJSKI_BROJ TEXT NOT NULL,
286 INVENTARNI_BROJ TEXT,
287 GODINA_PROIZVODNJE INTEGER,
288 LOKACIJA TEXT,
289 ORGANIZACIONA_JEDINICA TEXT,
290 LOKACIJA_SLIKE TEXT,
291 SISTEMSKA_GRESKA REAL NOT NULL,
292 SLUCAJNA_GRESKA REAL NOT NULL,
293 MULTIKANALNA INTEGER NOT NULL
294);
295
296CREATE TABLE IF NOT EXISTS TACKE_PROVERE_PIPETE
297(
298 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
299 PIPETA_ID INTEGER NOT NULL,
300 TACKA_PROVERE REAL NOT NULL,
301 FOREIGN KEY(PIPETA_ID) REFERENCES PIPETA(ID) ON DELETE CASCADE
302);
303
304CREATE TABLE IF NOT EXISTS OPSEG_PIPETE
305(
306 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
307 PIPETA_ID INTEGER,
308 OPSEG_MAX REAL NOT NULL,
309 OPSEG_MIN REAL NOT NULL,
310 PODELJAK REAL NOT NULL,
311 FOREIGN KEY(PIPETA_ID) REFERENCES PIPETA(ID) ON DELETE CASCADE
312);
313
314CREATE TABLE IF NOT EXISTS UVERENJE_PIPETE
315(
316 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
317 OPSEG_PIPETE_ID INTEGER NOT NULL,
318 BROJ_UVERENJA TEXT,
319 DATUM_ETALONIRANJA DATE,
320 LABORATORIJA_KOJA_JE_ETALONIRALA TEXT,
321 PERIOD_ETALONIRANJA INTEGER NOT NULL,
322 BROJ_ETALONIRANJA INTEGER NOT NULL,
323 FOREIGN KEY(OPSEG_PIPETE_ID) REFERENCES OPSEG_PIPETE(ID) ON DELETE CASCADE
324);
325
326CREATE TABLE IF NOT EXISTS TEST_PIPETE
327(
328 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
329 UVERENJE_PIPETE_ID INTEGER NOT NULL,
330 REDNI_BROJ_TESTA INTEGER NOT NULL,
331 DATUM DATE,
332 STATUS INTEGER NOT NULL,
333 NALOG_ID INTEGER NOT NULL,
334 ATMOSFERSKI_PRITISAK REAL NOT NULL,
335 RELATIVNA_VLAZNOST REAL NOT NULL,
336 TEMPERATURA_VODE REAL NOT NULL,
337 TEMPERATURA_VAZDUHA REAL NOT NULL,
338 BROJ_KANALA INTEGER NOT NULL,
339 FOREIGN KEY(UVERENJE_PIPETE_ID) REFERENCES UVERENJE_PIPETE(ID),
340 FOREIGN KEY(NALOG_ID) REFERENCES NALOG(ID) ON DELETE CASCADE
341);
342
343CREATE TABLE IF NOT EXISTS REZULTATI_MERENJA_TESTA_PIPETE
344(
345 ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
346 TEST_PIPETE_ID INTEGER NOT NULL,
347 REZULTAT_MERENJA REAL NOT NULL,
348 TACKA_PROVERE INTEGER NOT NULL,
349 FOREIGN KEY(TEST_PIPETE_ID) REFERENCES TEST_PIPETE(ID) ON DELETE CASCADE
350);*/