· 4 years ago · May 01, 2021, 06:28 PM
1DROP TABLE Collectors cascade constraints;
2DROP TABLE Orders cascade constraints;
3DROP TABLE Magazin cascade constraints;
4DROP TABLE MangaVolume cascade constraints;
5DROP TABLE Episode cascade constraints;
6DROP TABLE Manga cascade constraints;
7DROP TABLE IndividualPages cascade constraints;
8DROP TABLE CharacterInManga cascade constraints;
9DROP TABLE Author cascade constraints;
10DROP TABLE Publisher cascade constraints;
11DROP TABLE Genre cascade constraints;
12
13DROP TABLE OrderMagazin cascade constraints;
14DROP TABLE OrderMangaVolume cascade constraints;
15DROP TABLE MagazinEpisode cascade constraints;
16DROP TABLE CharacterInEpisode cascade constraints;
17DROP TABLE CollectorCharacter cascade constraints;
18DROP TABLE MangaCharacter cascade constraints;
19DROP TABLE CollectorManga cascade constraints;
20DROP TABLE MangaAuthor cascade constraints;
21DROP TABLE AuthorGenre cascade constraints;
22DROP TABLE MangaGenre cascade constraints;
23
24
25DROP SEQUENCE SEQ_Magazin;
26
27CREATE TABLE Collectors
28(
29 CollectorId NUMBER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1),
30 UserName VARCHAR(50) NOT NULL,
31 UserAddress VARCHAR(50) NOT NULL,
32 Email VARCHAR(50) NOT NULL CHECK(REGEXP_LIKE(Email,'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$')),
33 Phone VARCHAR(20) UNIQUE NOT NULL CHECK(Phone != ''),
34 PRIMARY KEY(CollectorId)
35);
36
37CREATE TABLE Orders
38(
39 OrderId NUMBER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1),
40 OrderDate DATE NOT NULL,
41 OrderType VARCHAR(50) NOT NULL,
42 OrderStatus VARCHAR(50) NOT NULL,
43 CollectorsId NUMBER NOT NULL,
44 PRIMARY KEY (OrderId)
45);
46
47CREATE TABLE Magazin
48(
49 MagazinId NUMBER NOT NULL,
50 MagazinName VARCHAR(50) NOT NULL,
51 PublicationDate DATE NOT NULL,
52 MagazinPrice NUMBER NOT NULL,
53 PRIMARY KEY (MagazinId)
54);
55
56CREATE TABLE MangaVolume
57(
58 MangaVolumeId NUMBER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1),
59 VolumeName VARCHAR(50) NOT NULL,
60 PublicationDate DATE NOT NULL,
61 VolumePrice NUMBER NOT NULL,
62 PRIMARY KEY (MangaVolumeId)
63);
64
65CREATE TABLE Episode
66(
67 EpisodeId NUMBER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1),
68 EpisodeName VARCHAR(50) NOT NULL,
69 EpisodeNumber NUMBER NOT NULL,
70 MangaVolId NUMBER NOT NULL,
71 InManga NUMBER NOT NULL,
72 PRIMARY KEY (EpisodeId)
73);
74
75CREATE TABLE Manga
76(
77 MangaId NUMBER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1),
78 MangaName VARCHAR(50) NOT NULL,
79 PublicationDateStart DATE NOT NULL,
80 PublicationDateEnd DATE NULL,
81 PublisherName VARCHAR(50) NOT NULL,
82 PRIMARY KEY (MangaId)
83);
84
85CREATE TABLE IndividualPages
86(
87 IndividualPagesId NUMBER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1),
88 EpisodeId NUMBER NOT NULL,
89 PageNumber NUMBER NOT NULL,
90 Content BLOB NULL,
91 PRIMARY KEY (IndividualPagesId)
92);
93
94CREATE TABLE CharacterInManga
95(
96 CharacterInMangaId NUMBER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1),
97 CharacterName VARCHAR(50) NOT NULL,
98 CharacterAge NUMBER,
99 Information VARCHAR(500) NOT NULL,
100 FirstOccurrence NUMBER NOT NULL,
101 LastOccurrence NUMBER NOT NULL,
102 Passed NUMBER NULL,
103 PRIMARY KEY (CharacterInMangaId)
104);
105
106CREATE TABLE Author
107(
108 AuthorId NUMBER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1),
109 AuthorName VARCHAR(50) NOT NULL,
110 AuthorAge NUMBER NULL,
111 AuthorAddress VARCHAR(100) NULL,
112 PRIMARY KEY (AuthorId)
113);
114
115CREATE TABLE Publisher
116(
117 PublisherName VARCHAR(50) NOT NULL,
118 PublisherAddress VARCHAR(100) NOT NULL,
119 Email VARCHAR(50) UNIQUE NOT NULL CHECK(Email != ''),
120 Phone VARCHAR(20) UNIQUE NOT NULL CHECK(Phone != ''),
121 PRIMARY KEY (PublisherName)
122);
123
124CREATE TABLE Genre
125(
126 GenreName VARCHAR(50) NOT NULL,
127 Information VARCHAR(1000) NOT NULL,
128 PRIMARY KEY (GenreName)
129);
130
131
132CREATE TABLE OrderMagazin
133(
134 OrderId NUMBER NOT NULL,
135 MagazinId NUMBER NOT NULL
136);
137
138CREATE TABLE OrderMangaVolume
139(
140 OrderId NUMBER NOT NULL,
141 MangaVolumeId NUMBER NOT NULL
142);
143
144CREATE TABLE MagazinEpisode
145(
146 MagazinId NUMBER NOT NULL,
147 EpisodeId NUMBER NOT NULL
148);
149
150CREATE TABLE CharacterInEpisode
151(
152 EpisodeId NUMBER NOT NULL,
153 MangaCharacterId NUMBER NOT NULL
154);
155
156CREATE TABLE CollectorCharacter
157(
158 CollectorId NUMBER NOT NULL,
159 MangaCharacterId NUMBER NOT NULL
160);
161
162CREATE TABLE MangaCharacter
163(
164 Manga NUMBER NOT NULL,
165 MangaCharacter NUMBER NOT NULL
166);
167
168CREATE TABLE CollectorManga
169(
170 CollectorId NUMBER NOT NULL,
171 MangaId NUMBER NOT NULL
172);
173
174CREATE TABLE MangaAuthor
175(
176 MangaId NUMBER NOT NULL,
177 AuthorId NUMBER NOT NULL
178);
179
180CREATE TABLE AuthorGenre
181(
182 AuthorId NUMBER NOT NULL,
183 GenreName VARCHAR(50) NOT NULL
184);
185
186CREATE TABLE MangaGenre
187(
188 MangaId NUMBER NOT NULL,
189 GenreName VARCHAR(50) NOT NULL
190);
191
192CREATE SEQUENCE SEQ_Magazin INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 0;
193
194-- trigger for Id(PK) autocomplete
195CREATE OR REPLACE TRIGGER TR_Magazin BEFORE INSERT ON Magazin FOR EACH ROW
196begin
197 if :NEW.MagazinId is null then
198 SELECT SEQ_Magazin.nextval INTO :NEW.MagazinId FROM DUAL;
199 end if;
200end TR_Magazin;
201/
202
203-- UPDATE system_info
204-- SET field_value = 'NewValue'
205-- WHERE field_desc IN (SELECT role_type
206-- FROM system_users
207-- WHERE user_name = 'uname')
208
209-- define for how many episodes every character was in manga
210
211
212ALTER TABLE Orders ADD CONSTRAINT FK_Orders FOREIGN KEY (CollectorsId) REFERENCES Collectors (CollectorId);
213ALTER TABLE Episode ADD CONSTRAINT FK_Episode_1 FOREIGN KEY (MangaVolId) REFERENCES MangaVolume (MangaVolumeId);
214ALTER TABLE Episode ADD CONSTRAINT FK_Episode_2 FOREIGN KEY (InManga) REFERENCES Manga (MangaId);
215ALTER TABLE IndividualPages ADD CONSTRAINT FK_IndividualPages FOREIGN KEY (EpisodeId) REFERENCES Episode (EpisodeId);
216ALTER TABLE CharacterInManga ADD CONSTRAINT FK_CharacterInManga_1 FOREIGN KEY (FirstOccurrence) REFERENCES IndividualPages (IndividualPagesId);
217ALTER TABLE CharacterInManga ADD CONSTRAINT FK_CharacterInManga_2 FOREIGN KEY (LastOccurrence) REFERENCES IndividualPages (IndividualPagesId);
218ALTER TABLE Manga ADD CONSTRAINT FK_Publisher FOREIGN KEY (PublisherName) REFERENCES Publisher (PublisherName);
219ALTER TABLE OrderMagazin ADD CONSTRAINT FK_OrderMagazin_1 FOREIGN KEY (OrderId) REFERENCES Orders (OrderId);
220ALTER TABLE OrderMagazin ADD CONSTRAINT FK_OrderMagazin_2 FOREIGN KEY (MagazinId) REFERENCES Magazin (MagazinId);
221ALTER TABLE OrderMangaVolume ADD CONSTRAINT FK_OrderMangaVolume_1 FOREIGN KEY (OrderId) REFERENCES Orders (OrderId);
222ALTER TABLE OrderMangaVolume ADD CONSTRAINT FK_OrderMangaVolume_2 FOREIGN KEY (MangaVolumeId) REFERENCES MangaVolume (MangaVolumeId);
223ALTER TABLE MagazinEpisode ADD CONSTRAINT FK_MagazinEpisode_1 FOREIGN KEY (MagazinId) REFERENCES Magazin (MagazinId);
224ALTER TABLE MagazinEpisode ADD CONSTRAINT FK_MagazinEpisode_2 FOREIGN KEY (EpisodeId) REFERENCES Episode (EpisodeId);
225ALTER TABLE CharacterInEpisode ADD CONSTRAINT FK_CharacterInEpisode_1 FOREIGN KEY (EpisodeId) REFERENCES Episode (EpisodeId);
226ALTER TABLE CharacterInEpisode ADD CONSTRAINT FK_CharacterInEpisode_2 FOREIGN KEY (MangaCharacterId) REFERENCES CharacterInManga (CharacterInMangaId);
227ALTER TABLE CollectorCharacter ADD CONSTRAINT FK_CollectorCharacter_1 FOREIGN KEY (CollectorId) REFERENCES Collectors (CollectorId);
228ALTER TABLE CollectorCharacter ADD CONSTRAINT FK_CollectorCharacter_2 FOREIGN KEY (MangaCharacterId) REFERENCES CharacterInManga (CharacterInMangaId);
229ALTER TABLE MangaCharacter ADD CONSTRAINT FK_MangaCharacter_1 FOREIGN KEY (Manga) REFERENCES Manga (MangaId);
230ALTER TABLE MangaCharacter ADD CONSTRAINT FK_MangaCharacter_2 FOREIGN KEY (MangaCharacter) REFERENCES CharacterInManga (CharacterInMangaId);
231ALTER TABLE CollectorManga ADD CONSTRAINT FK_CollectorManga_1 FOREIGN KEY (CollectorId) REFERENCES Collectors (CollectorId);
232ALTER TABLE CollectorManga ADD CONSTRAINT FK_CollectorManga_2 FOREIGN KEY (MangaId) REFERENCES Manga (MangaId);
233ALTER TABLE MangaAuthor ADD CONSTRAINT FK_MangaAuthor_1 FOREIGN KEY (MangaId) REFERENCES Manga (MangaId);
234ALTER TABLE MangaAuthor ADD CONSTRAINT FK_MangaAuthor_2 FOREIGN KEY (AuthorId) REFERENCES Author (AuthorId);
235ALTER TABLE AuthorGenre ADD CONSTRAINT FK_AuthorGenre_1 FOREIGN KEY (AuthorId) REFERENCES Author (AuthorId);
236ALTER TABLE AuthorGenre ADD CONSTRAINT FK_AuthorGenre_2 FOREIGN KEY (GenreName) REFERENCES Genre (GenreName);
237ALTER TABLE MangaGenre ADD CONSTRAINT FK_MangaGenre_1 FOREIGN KEY (MangaId) REFERENCES Manga (MangaId);
238ALTER TABLE MangaGenre ADD CONSTRAINT FK_MangaGenre_2 FOREIGN KEY (GenreName) REFERENCES Genre (GenreName);
239
240INSERT INTO Author (AuthorName, AuthorAge, AuthorAddress) VALUES ('Naoshi Arakawa', NULL, 'Japan');
241INSERT INTO Author (AuthorName, AuthorAge, AuthorAddress) VALUES ('Hadzime Isaiama', '34', 'Japan');
242
243INSERT INTO Genre (GenreName, Information) VALUES ('Drama', 'Drama is the specific mode of fiction represented in performance: a play, opera, mime, ballet, etc., performed in a theatre, or on radio or television. Considered as a genre of poetry in general, the dramatic mode has been contrasted with the epic and the lyrical modes ever since Aristotles Poetics —the earliest work of dramatic theory. The term drama comes from a Greek word meaning action (Classical Greek: δρᾶμα, drama), which is derived from I do (Classical Greek: δράω, drao). The two masks associated with drama represent the traditional generic division between comedy and tragedy.');
244INSERT INTO Genre (GenreName, Information) VALUES ('Anti-utopia', 'A dystopia is a fictional community or society that is undesirable or frightening. It is often treated as an antonym of utopia, a term that was coined by Sir Thomas More and figures as the title of his best known work, published in 1516, which created a blueprint for an ideal society with minimal crime, violence and poverty. But the relationship between utopia and dystopia is more complex than this, as there exist utopian elements in many dystopias, and vice-versa.');
245
246INSERT INTO Collectors (UserName, UserAddress, Email, Phone) VALUES ('Ivan', 'Jilkova, 61500, Brno', 'xbobro01@fit.vutbr.cz', '+4200000000000');
247INSERT INTO Collectors (UserName, UserAddress, Email, Phone) VALUES ('Dmitrii', 'Kastanova, 62000, Brno', 'xkozhe00@fit.vutbr.cz', '+420111111111');
248INSERT INTO Collectors (UserName, UserAddress, Email, Phone) VALUES ('Snezhana', 'Kastanova, 62000, Brno', 'xhunter01@fit.vutbr.cz', '+420111111222');
249
250INSERT INTO Publisher (PublisherName, PublisherAddress, Email, Phone) VALUES ('Kodansha', 'Japan, Tokio', 'kodansha.co.jp', '+894256899865');
251INSERT INTO Publisher (PublisherName, PublisherAddress, Email, Phone) VALUES ('Lupus', 'Russia, Tokio', 'lupus.su', '+2552462466');
252
253INSERT INTO Manga (MangaName, PublicationDateStart, PublicationDateEnd, PublisherName) VALUES ('Shigatsu wa Kimi no Uso (Your Lie in April)', DATE '2017-01-02', DATE '2020-01-22', 'Kodansha');
254INSERT INTO Manga (MangaName, PublicationDateStart, PublicationDateEnd, PublisherName) VALUES ('Shingeki no Kyojin (Attack on titan)', DATE '2017-01-02', DATE '2020-01-22', 'Kodansha');
255INSERT INTO Manga (MangaName, PublicationDateStart, PublicationDateEnd, PublisherName) VALUES ('Naruto', DATE '2010-01-02', DATE '2019-01-22', 'Kodansha');
256INSERT INTO Manga (MangaName, PublicationDateStart, PublicationDateEnd, PublisherName) VALUES ('Boruto', DATE '2017-01-02', DATE '2021-01-22', 'Kodansha');
257
258INSERT INTO MangaVolume (VolumeName, PublicationDate, VolumePrice) VALUES ('Volume 1', DATE '2018-01-02', 10);
259INSERT INTO MangaVolume (VolumeName, PublicationDate, VolumePrice) VALUES ('Volume 2', DATE '2018-01-10', 15);
260
261INSERT INTO Episode (EpisodeName, EpisodeNumber, MangaVolId, InManga) VALUES ('Monotone/Colorful', 1, 1, 1);
262INSERT INTO Episode (EpisodeName, EpisodeNumber, MangaVolId, InManga) VALUES ('Friend A', 2, 1, 1);
263
264INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 1', DATE '2018-01-02', 10);
265INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 2', DATE '2018-01-05', 45);
266INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 3', DATE '2018-01-02', 60);
267INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 4', DATE '2018-01-05', 56);
268INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 4', DATE '2018-01-15', 42);
269INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 4', DATE '2018-01-16', 43);
270INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 5', DATE '2018-01-17', 29);
271INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 5', DATE '2018-01-18', 48);
272INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 6', DATE '2018-01-19', 71);
273INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 7', DATE '2018-01-20', 34);
274INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 1', DATE '2018-01-21', 52);
275INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 8', DATE '2018-01-22', 19);
276INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 8', DATE '2018-01-23', 61);
277
278
279INSERT INTO IndividualPages (EpisodeId, PageNumber) VALUES (1, 12);
280INSERT INTO IndividualPages (EpisodeId, PageNumber) VALUES (1, 13);
281
282INSERT INTO CharacterInManga (CharacterName, CharacterAge, Information, FirstOccurrence, LastOccurrence, Passed) VALUES ('Eren Yeger', 14, 'Information about Eren', 1, 2, NULL);
283INSERT INTO CharacterInManga (CharacterName, CharacterAge, Information, FirstOccurrence, LastOccurrence, Passed) VALUES ('Mikasa Akkerman', 14, 'Information about Mikasa', 1, 1, NULL);
284INSERT INTO CharacterInManga (CharacterName, CharacterAge, Information, FirstOccurrence, LastOccurrence, Passed) VALUES ('Naruto Uzumaki', 16, 'Jsem stanu HOKAGE', 1, 1, NULL);
285INSERT INTO CharacterInManga (CharacterName, CharacterAge, Information, FirstOccurrence, LastOccurrence, Passed) VALUES ('Naruto Uzumaki', 28, 'Jsem HOKAGE', 1, 1, NULL);
286INSERT INTO CharacterInManga (CharacterName, Information, FirstOccurrence, LastOccurrence, Passed) VALUES ('Ococuke Kaguja', 'Not Information', 1, 1, NULL);
287
288INSERT INTO Orders (OrderDate, OrderType, OrderStatus, CollectorsId) VALUES (DATE '2018-01-11', 'On-line', 'Done', 1);
289INSERT INTO Orders (OrderDate, OrderType, OrderStatus, CollectorsId) VALUES (DATE '2018-03-12', 'On-line + book', 'In process', 2);
290INSERT INTO Orders (OrderDate, OrderType, OrderStatus, CollectorsId) VALUES (DATE '2018-03-13', 'On-line + book', 'In process', 2);
291INSERT INTO Orders (OrderDate, OrderType, OrderStatus, CollectorsId) VALUES (DATE '2018-03-14', 'On-line + book', 'Canceled', 2);
292INSERT INTO Orders (OrderDate, OrderType, OrderStatus, CollectorsId) VALUES (DATE '2018-03-15', 'On-line + book', 'In process', 3);
293INSERT INTO Orders (OrderDate, OrderType, OrderStatus, CollectorsId) VALUES (DATE '2018-03-15', 'On-line + book', 'Done', 2);
294INSERT INTO Orders (OrderDate, OrderType, OrderStatus, CollectorsId) VALUES (DATE '2018-03-15', 'On-line + book', 'In process', 1);
295
296
297-- SQL skript obsahující dotazy SELECT musí obsahovat konkrétně alespoň dva dotazy
298-- využívající spojení dvou tabulek
299
300-- information to send notification about order status
301SELECT Collectors.UserName, Collectors.Phone, Collectors.Email, Orders.OrderStatus
302FROM Orders JOIN Collectors ON Orders.OrderId = Collectors.CollectorID
303ORDER BY Orders.OrderStatus;
304
305-- which publisher publish manga from certain author
306SELECT Manga.MangaName, Manga.PublisherName, AuthorName
307FROM Author JOIN Manga ON Author.AuthorId = Manga.MangaId
308ORDER BY Author.AuthorName;
309
310-- jeden využívající spojení tří tabulek,
311
312-- find information about charecter in certain episode and certain manga
313SELECT CharacterInManga.CharacterName, CharacterInManga.CharacterAge, CharacterInManga.Information, Manga.MangaName, Episode.EpisodeNumber
314FROM CharacterInManga
315JOIN Episode ON CharacterInManga.CharacterInMangaId = Episode.EpisodeId
316JOIN Manga ON CharacterInManga.CharacterInMangaId = Manga.MangaId
317ORDER BY CharacterInManga.CharacterName;
318
319-- dva dotazy s klauzulí GROUP BY a agregační funkcí
320
321-- return an oldest version of characters
322SELECT CharacterName, MAX( CharacterAge )
323FROM CharacterInManga GROUP BY CharacterName
324ORDER BY CharacterName;
325
326-- returns the minimum price for each magazine in the given price range.
327SELECT MagazinName, MIN(MagazinPrice)
328FROM Magazin
329--WHERE MagazinPrice =(
330 --SELECT MAX(MagazinPrice)
331 --FROM Magazin
332--)
333GROUP BY MagazinName
334HAVING MIN(MagazinPrice) BETWEEN 40 AND 70
335ORDER BY MagazinName;
336
337--jeden dotaz obsahující predikát EXISTS
338
339-- search for chars if they're age is present
340
341SELECT CharacterName, CharacterAge, Information
342FROM CharacterInManga
343WHERE EXISTS (
344 SELECT *
345 FROM Manga
346 WHERE CharacterInManga.CharacterInMangaId = Manga.MangaId
347 );
348
349
350
351--jeden dotaz s predikátem IN s vnořeným selectem (nikoliv IN s množinou konstantních dat).
352
353-- search for order status, where status is 'in progress'
354
355SELECT OrderId, UserName, CollectorId, OrderStatus
356FROM Orders FULL OUTER JOIN Collectors ON Orders.CollectorsId = Collectors.CollectorID
357WHERE OrderStatus IN (
358 'In process'
359)
360ORDER BY OrderId;
361
362select * from ORDERS;
363select * from COLLECTORS;
364select * from MAGAZIN;
365select * from CHARACTERINMANGA;
366select * from Episode;
367
368--SQL skript pro vytvoření pokročilých objektů schématu databáze – SQL skript, který nejprve vytvoří základní objekty schéma databáze a naplní tabulky ukázkovými daty (stejně jako skript v bodě 2),
369-- a poté zadefinuje či vytvoří pokročilá omezení či objekty databáze dle upřesňujících požadavků zadání.
370--Dále skript bude obsahovat ukázkové příkazy manipulace dat a dotazy demonstrující použití výše zmiňovaných omezení a objektů tohoto skriptu (např. pro demonstraci použití indexů zavolá nejprve skript EXPLAIN PLAN na dotaz bez indexu, poté vytvoří index, a nakonec zavolá EXPLAIN PLAN na dotaz s indexem; pro demostranci databázového triggeru se provede manipulace s daty, která vyvolá daný trigger; atp.).
371--Dokumentace popisující finální schéma databáze – Dokumentace popisující řešení ze skriptu v bodě 4 vč. jejich zdůvodnění (např. popisuje výstup příkazu EXPLAIN PLAN bez indexu, důvod vytvoření zvoleného indexu, a výstup EXPLAIN PLAN s indexem, atd.).
372
373-- index and explain plan
374
375EXPLAIN PLAN SET STATEMENT_ID = 'performanceAnalysisBefore' FOR
376 SELECT MagazinId, MagazinName, Magazin.MagazinPrice, COUNT(*)
377 FROM Magazin WHERE MagazinPrice < 50
378 GROUP BY (MagazinId, MagazinName, Magazin.MagazinPrice);
379SELECT * FROM TABLE(DBMS_XPLAN.display);
380
381CREATE INDEX performanceIndex ON Magazin (MagazinPrice);
382
383EXPLAIN PLAN SET STATEMENT_ID = 'performanceAnalysisAfter' FOR
384 SELECT MagazinId, MagazinName, MagazinPrice, COUNT(*)
385 FROM Magazin WHERE MagazinPrice < 50
386 GROUP BY (MagazinId, MagazinName, MagazinPrice);
387SELECT * FROM TABLE(DBMS_XPLAN.display);
388
389DROP INDEX performanceIndex;
390
391
392GRANT ALL PRIVILEGES ON Collectors TO xkozhe00;
393GRANT ALL PRIVILEGES ON Orders TO xkozhe00;
394GRANT ALL PRIVILEGES ON Magazin TO xkozhe00;
395GRANT ALL PRIVILEGES ON MangaVolume TO xkozhe00;
396GRANT ALL PRIVILEGES ON Episode TO xkozhe00;
397GRANT ALL PRIVILEGES ON Manga TO xkozhe00;
398GRANT ALL PRIVILEGES ON IndividualPages TO xkozhe00;
399GRANT ALL PRIVILEGES ON CharacterInManga TO xkozhe00;
400GRANT ALL PRIVILEGES ON Author TO xkozhe00;
401GRANT ALL PRIVILEGES ON Publisher TO xkozhe00;
402GRANT ALL PRIVILEGES ON Genre TO xkozhe00;
403GRANT ALL PRIVILEGES ON OrderMagazin TO xkozhe00;
404GRANT ALL PRIVILEGES ON OrderMangaVolume TO xkozhe00;
405GRANT ALL PRIVILEGES ON MagazinEpisode TO xkozhe00;
406GRANT ALL PRIVILEGES ON CharacterInEpisode TO xkozhe00;
407GRANT ALL PRIVILEGES ON CollectorCharacter TO xkozhe00;
408GRANT ALL PRIVILEGES ON MangaCharacter TO xkozhe00;
409GRANT ALL PRIVILEGES ON CollectorManga TO xkozhe00;
410GRANT ALL PRIVILEGES ON MangaAuthor TO xkozhe00;
411GRANT ALL PRIVILEGES ON AuthorGenre TO xkozhe00;
412GRANT ALL PRIVILEGES ON MangaGenre TO xkozhe00;
413
414
415DROP MATERIALIZED VIEW MV_notify;
416-- DROP MATERIALIZED VIEW LOG ON Collectors;
417
418CREATE MATERIALIZED VIEW LOG ON Collectors
419 WITH PRIMARY KEY
420 INCLUDING NEW VALUES;
421
422CREATE MATERIALIZED VIEW MV_notify
423 BUILD IMMEDIATE
424 REFRESH FAST ON COMMIT
425 AS SELECT CollectorId, Email FROM Collectors;
426
427SELECT * from MV_notify;
428GRANT SELECT ON MV_notify to xkozhe00;
429
430
431CREATE OR REPLACE Procedure UpdateMagazin
432 ( name_in IN VARCHAR )
433 IS
434 MPrice number;
435
436 cursor c1 is
437 SELECT MagazinPrice
438 FROM Magazin
439 WHERE MagazinName = name_in;
440
441BEGIN
442
443 open c1;
444 fetch c1 into MPrice;
445
446 if c1%notfound then
447 MPrice := -1;
448 else MPrice := 50;
449 end if;
450
451 MagazinName := 'LALALA';
452 MPrice := 999;
453 INSERT INTO Magazin
454 ( MagazinName,
455 MagazinPrice )
456 VALUES
457 ( name_in,
458 MPrice );
459
460 commit;
461
462 close c1;
463
464EXCEPTION
465WHEN OTHERS THEN
466 raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
467END;
468/
469
470SELECT * FROM MAGAZIN;
471begin
472 UpdateMagazin('Magazin 6');
473end;
474/
475SELECT * FROM MAGAZIN;