-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathids2.sql
296 lines (230 loc) · 10 KB
/
ids2.sql
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
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
-- Authors: Sabina Gulcikova, xgulci00@stud.fit.vutbr.cz
-- Martin Zatovic, xzatov00@stud.fit.vutbr.cz
DROP TABLE magia CASCADE CONSTRAINTS;
DROP TABLE element CASCADE CONSTRAINTS;
DROP TABLE zvitok CASCADE CONSTRAINTS;
DROP TABLE kuzlo CASCADE CONSTRAINTS;
DROP TABLE kuzelnik CASCADE CONSTRAINTS;
DROP TABLE grimoar CASCADE CONSTRAINTS;
DROP TABLE magicke_miesto CASCADE CONSTRAINTS;
DROP TABLE kuzelnik_vlastni_grimoar CASCADE CONSTRAINTS;
DROP TABLE vedlajsi_element_kuzla CASCADE CONSTRAINTS;
DROP TABLE grimoar_zoskupuje_kuzla CASCADE CONSTRAINTS;
DROP TABLE kuzelnikova_synergia_s_elementom CASCADE CONSTRAINTS;
DROP SEQUENCE Magia_seq;
DROP SEQUENCE Zvitok_seq;
DROP SEQUENCE Kuzlo_seq;
DROP SEQUENCE Kuzelnik_seq;
-------------------------------
CREATE SEQUENCE Magia_seq
START WITH 10000
INCREMENT BY 1;
CREATE SEQUENCE Zvitok_seq
START WITH 10000
INCREMENT BY 1;
CREATE SEQUENCE Kuzlo_seq
START WITH 10000
INCREMENT BY 1;
CREATE SEQUENCE Kuzelnik_seq
START WITH 10000
INCREMENT BY 1;
-------------------------------
CREATE TABLE element
(
-- magicka znacka je ekvivalent chemickej znacky
magicka_znacka VARCHAR(2) PRIMARY KEY,
typ VARCHAR(15) NOT NULL,
specializacia VARCHAR(254) NOT NULL,
uroven_vzacnosti VARCHAR(31) NOT NULL,
CHECK ( uroven_vzacnosti IN ('veľmi vzácny', 'vzácny', 'štandardný','frekventovaný') ),
forma_vyskytu VARCHAR(63)
);
CREATE TABLE magia
(
id INT DEFAULT Magia_seq.NEXTVAL PRIMARY KEY,
farba VARCHAR(63) NOT NULL,
charakter VARCHAR(63) NOT NULL,
min_uroven_kuzlenia VARCHAR(2) NOT NULL
CHECK ( min_uroven_kuzlenia IN ('E', 'D', 'C', 'B', 'A', 'S', 'SS')),
dlzka_posobenia VARCHAR(63) NOT NULL,
element VARCHAR(2),
CONSTRAINT magia_fk FOREIGN KEY (element) REFERENCES element (magicka_znacka) ON DELETE CASCADE
);
CREATE TABLE kuzlo
(
ev_cislo INT DEFAULT Kuzlo_seq.NEXTVAL PRIMARY KEY,
uroven_zlozitosti_zoslania VARCHAR(2) NOT NULL
CHECK ( uroven_zlozitosti_zoslania IN ('E', 'D', 'C', 'B', 'A', 'S', 'SS')),
typ VARCHAR(127) NOT NULL
CHECK ( typ IN ('verbálne', 'materiálne') ),
sila INT NOT NULL
CHECK ( sila >= 0 AND sila <= 10),
meno VARCHAR(127) NOT NULL,
formula_vyvolania VARCHAR(254) NOT NULL,
hlasitost_vyvolania VARCHAR(63) DEFAULT NULL,
carovny_nastroj VARCHAR(127) DEFAULT NULL,
gesto VARCHAR(254) DEFAULT NULL
);
CREATE TABLE zvitok
(
id INT DEFAULT Zvitok_seq.NEXTVAL PRIMARY KEY,
stav VARCHAR(15) NOT NULL CHECK ( stav IN ('plný', 'prázdny') ),
kuzlo INT,
CONSTRAINT zvitok_fk FOREIGN KEY (kuzlo) REFERENCES kuzlo (ev_cislo)
);
CREATE TABLE grimoar
(
-- msgn - magical standard grimoire number, ekvivalent ISBN
-- uvazujeme format MSGN-10 (ekviv. ISBN-10), pricom kontrolujeme len lexikalnu spravnost
-- delitelnost 11 bude overena vhodnym triggerom v dalsej casti projektu
-- MSGN 80-204-0105-9
msgn VARCHAR(18)
CHECK ( LENGTH(msgn) = 18 )
CHECK ( REGEXP_LIKE(msgn, '^MSGN ([0-9]|X|x){1,5}[- ]([0-9]|X|x){1,7}[- ]([0-9]|X|x){1,6}[- ]([0-9]|X|x)$', 'i') ),
historia_vlastnictva INT NOT NULL,
mnozstvo_magie VARCHAR(4) NOT NULL,
datum_konca DATE,
stav VARCHAR(254) NOT NULL,
CONSTRAINT grimoar_pk PRIMARY KEY (msgn)
);
CREATE TABLE kuzelnik
(
id INT DEFAULT Kuzelnik_seq.NEXTVAL PRIMARY KEY,
pseudonym VARCHAR(255) NOT NULL,
datum_zrodenia VARCHAR(63),
uroven_kuzlenia VARCHAR(2) NOT NULL
CHECK ( uroven_kuzlenia IN ('E', 'D', 'C', 'B', 'A', 'S', 'SS')),
velkost_many NUMBER(7,2) NOT NULL
);
CREATE TABLE magicke_miesto
(
gps_suradnice varchar(32)
CHECK ( REGEXP_LIKE(
gps_suradnice, '^[-+]?([1-8]?\d(\.\d+)?|90(\.0+)?),\s*[-+]?(180(\.0+)?|((1[0-7]\d)|([1-9]?\d))(\.\d+)?)$', 'i') ),
miera_presakovania NUMBER(5,2) NOT NULL,
oblast VARCHAR(254) NOT NULL,
ulica VARCHAR(254) NOT NULL,
poznavacie_znamenie VARCHAR(254) NOT NULL,
presakujuca_magia INT,
CONSTRAINT magicke_miesto_pk PRIMARY KEY (gps_suradnice),
CONSTRAINT magicke_miesto_fk FOREIGN KEY (presakujuca_magia) REFERENCES magia (id)
);
CREATE TABLE kuzelnik_vlastni_grimoar
(
msgn VARCHAR(18),
id_kuzelnika INT,
od VARCHAR(63),
do VARCHAR(63),
CONSTRAINT kuzelnik_vlastni_grimoar_pk PRIMARY KEY (msgn, id_kuzelnika),
CONSTRAINT kuzelnik_vlastni_grimoar_fk_grimoar FOREIGN KEY (msgn) REFERENCES grimoar (msgn),
CONSTRAINT kuzelnik_vlastni_grimoar_fk_kuzelnik FOREIGN KEY (id_kuzelnika) REFERENCES kuzelnik (id)
);
CREATE TABLE vedlajsi_element_kuzla
(
magicka_znacka VARCHAR(2),
ev_cislo INT,
CONSTRAINT vedlajsi_element_kuzla_pk PRIMARY KEY (magicka_znacka, ev_cislo),
CONSTRAINT vedlajsi_element_kuzla_fk_element FOREIGN KEY (magicka_znacka) REFERENCES element (magicka_znacka),
CONSTRAINT vedlajsi_element_kuzla_fk_kuzlo FOREIGN KEY (ev_cislo) REFERENCES kuzlo (ev_cislo)
);
CREATE TABLE grimoar_zoskupuje_kuzla
(
msgn VARCHAR(18),
ev_cislo INT,
CONSTRAINT grimoar_zoskupuje_kuzla_pk PRIMARY KEY (msgn, ev_cislo),
CONSTRAINT grimoar_zoskupuje_kuzla_fk_msgn FOREIGN KEY (msgn) REFERENCES grimoar (msgn),
CONSTRAINT grimoar_zoskupuje_kuzla_fk_ev_cislo FOREIGN KEY (ev_cislo) REFERENCES kuzlo (ev_cislo)
);
CREATE TABLE kuzelnikova_synergia_s_elementom
(
id_kuzelnika INT,
magicka_znacka_elementu VARCHAR(2),
CONSTRAINT kuzelnikova_synergia_s_elementom_pk PRIMARY KEY (id_kuzelnika, magicka_znacka_elementu),
CONSTRAINT kuzelnikova_synergia_s_elementom_fk_kuzelnik FOREIGN KEY (id_kuzelnika) REFERENCES kuzelnik (id),
CONSTRAINT kuzelnikova_synergia_s_elementom_fk_element FOREIGN KEY (magicka_znacka_elementu) REFERENCES element (magicka_znacka)
);
--=======-NAPLNENIE DATAMI-=======--
INSERT INTO element
VALUES ('Ob', 'obranné', 'ľudské bytosti', 'vzácny', 'tuhá');
INSERT INTO element
VALUES ('Om', 'revitalizačné', 'ľudské bytosti', 'veľmi vzácny', 'tekutá');
INSERT INTO element
VALUES ('Sl', 'útočné', 'škriatkovia', 'štandardný', 'tekutá');
INSERT INTO element
VALUES ('Ur', 'obranné', 'zvieratá', 'vzácny', 'plynná');
INSERT INTO magia
VALUES (DEFAULT, 'žtlá', 'kladný', 'C', '2 roky', 'Ur');
INSERT INTO magia
VALUES (DEFAULT, 'zelená', 'kladný', 'E', '1 mesiac', 'Ob');
INSERT INTO magia
VALUES (239, 'oranžová', 'negatívny', 'A', '1 hodina', 'Sl');
INSERT INTO magia
VALUES (1781, 'fialová', 'negatívny', 'E', '10 minút', 'Sl');
INSERT INTO kuzlo
VALUES (DEFAULT, 'E', 'verbálne', 4, 'Abrakando', 'Abraka Dabra', 'šepot - 30 dB', DEFAULT, DEFAULT);
INSERT INTO kuzlo
VALUES (784, 'D', 'verbálne', 5, 'Simsalando', 'Simsala bimsala sim bim', 'krik - 100 dB', DEFAULT, DEFAULT);
INSERT INTO kuzlo
VALUES (1212, 'A', 'materiálne', 7, 'Karavaggio', 'Kryptonium ecce', DEFAULT, 'čarovný prútik', 'štyri otočenia zápastím pravej ruky');
INSERT INTO zvitok
VALUES (DEFAULT, 'prázdny', NULL);
INSERT INTO zvitok
VALUES (DEFAULT, 'plný', 784);
INSERT INTO zvitok
VALUES (6543, 'plný', 1212);
INSERT INTO grimoar
VALUES ('MSGN 80-204-0105-9', 8, '50%', '12-December-4020', 'použitý');
INSERT INTO grimoar
VALUES ('MSGN 3-16-148410-X', 1, '84%', '10-December-8021', 'nový');
INSERT INTO grimoar
VALUES ('MSGN 9971-5-0210-0', 3721, '12%', '1-November-2030', 'zachovalý');
INSERT INTO kuzelnik
VALUES (DEFAULT, 'Bilbian Hop', '30-November-1807', 'B', 127.02);
INSERT INTO kuzelnik
VALUES (172, 'Jakobian Hugger', '12-September-2000', 'E', 1200.00);
INSERT INTO kuzelnik
VALUES (823, 'Halabala Vincentala', '13-December-1321', 'SS', 1500.89);
INSERT INTO magicke_miesto
VALUES ('33.961973, 108.248309', 98.04, 'Tarpotove výšiny', 'Spodná', 'Tri topole v jednom kmeni', 239);
INSERT INTO magicke_miesto
VALUES ('23.441948, 120.987931', 12.01, 'Medvedia hora', 'Krútivá', 'Nehasnúce ohnisko', 10000);
INSERT INTO magicke_miesto
VALUES ('-15.417006, 166.934858', 100.00, 'Oskarie jaskyne', 'Kalabodova', 'Trhlina krútivého tvaru v stene', 1781);
INSERT INTO kuzelnik_vlastni_grimoar
VALUES ('MSGN 80-204-0105-9', 10000, '24-03-1981', '15-07-2029');
INSERT INTO kuzelnik_vlastni_grimoar
VALUES ('MSGN 3-16-148410-X', 172, '13-07-2012', '17-04-7054');
INSERT INTO kuzelnik_vlastni_grimoar
VALUES ('MSGN 9971-5-0210-0', 823, '31-12-1902', '15-04-2021');
INSERT INTO vedlajsi_element_kuzla
VALUES ('Ob', 784);
INSERT INTO vedlajsi_element_kuzla
VALUES ('Ur', 784);
INSERT INTO vedlajsi_element_kuzla
VALUES ('Ur', 1212);
INSERT INTO grimoar_zoskupuje_kuzla
VALUES ('MSGN 9971-5-0210-0', 784);
INSERT INTO grimoar_zoskupuje_kuzla
VALUES ('MSGN 9971-5-0210-0', 1212);
INSERT INTO grimoar_zoskupuje_kuzla
VALUES ('MSGN 80-204-0105-9', 784);
INSERT INTO kuzelnikova_synergia_s_elementom
VALUES (172, 'Sl');
INSERT INTO kuzelnikova_synergia_s_elementom
VALUES (823, 'Ob');
INSERT INTO kuzelnikova_synergia_s_elementom
VALUES (823, 'Om');
INSERT INTO kuzelnikova_synergia_s_elementom
VALUES (10000, 'Ob');
--=======-ZOBRAZENIE DAT-=======--
-- SELECT * FROM magia;
-- SELECT * FROM element;
-- SELECT * FROM zvitok;
-- SELECT * FROM kuzlo;
-- SELECT * FROM kuzelnik;
-- SELECT * FROM grimoar;
-- SELECT * FROM magicke_miesto;
-- SELECT * FROM kuzelnik_vlastni_grimoar;
-- SELECT * FROM vedlajsi_element_kuzla;
-- SELECT * FROM grimoar_zoskupuje_kuzla;
-- SELECT * FROM kuzelnikova_synergia_s_elementom;