· 6 years ago · Jan 15, 2020, 04:38 PM
1/* ================================================ */
2/* TABELA: DYRYGENT */
3/* ================================================ */
4
5CREATE TABLE [DYRYGENT]
6(
7 PESEL_DYRYGENT CHAR(11) NOT NULL,
8 IMIE_DYRYGENT VARCHAR(32) NOT NULL,
9 NAZWISKO_DYRYGENT VARCHAR(32) NOT NULL,
10 PLEC_DYRYGENT CHAR(1) NULL DEFAULT 'N'
11 CONSTRAINT CHECK_PLEC_DYRYGENT
12 CHECK (PLEC_DYRYGENT IS NULL OR (PLEC_DYRYGENT IN ('N','K','M'))),
13 DATA_URODZENIA_DYRYGENT DATE NULL,
14
15 CONSTRAINT PK_DYRYGENT PRIMARY KEY (PESEL_DYRYGENT)
16);
17
18COMMENT ON TABLE [DYRYGENT] IS
19 'Osoba dyrygujaca dana grupa muzykow';
20
21COMMENT ON COLUMN [DYRYGENT].PLEC_DYRYGENT IS
22 'Plec kompozytora.
23 N - brak danych
24 K - kobieta
25 M - mezczyzna';
26
27COMMENT ON COLUMN [DYRYGENT].PESEL_DYRYGENT IS
28 '11 znakowy unikalny ciag znakow';
29
30COMMENT ON COLUMN [DYRYGENT].IMIE_DYRYGENT IS
31 'Imie jakim posluguje sie dyrygent';
32
33COMMENT ON COLUMN [DYRYGENT].NAZWISKO_DYRYGENT IS
34 'Nazwisko jakim posluguje sie dyrygent';
35
36COMMENT ON COLUMN [DYRYGENT].DATA_URODZENIA_DYRYGENT IS
37 'Data w jakiej przyszedl na swiat dyrygent';
38
39
40/* ================================================ */
41/* TABELA: INSTRUMENT */
42/* ================================================ */
43
44CREATE TABLE [INSTRUMENT]
45(
46 ID_INSTRUMENT INTEGER DEFAULT AUTOINCREMENT,
47 PESEL_MUZYK CHAR(11) NULL,
48 TYP_INSTRUMENT CHAR(1) NOT NULL DEFAULT 'N'
49 CONSTRAINT CHECK_TYP_INSTRUMENT
50 CHECK (TYP_INSTRUMENT IN ('N','A','C','I','M','E')),
51 NAZWA_INSTRUMENT VARCHAR(32) NOT NULL,
52 PRODUCENT VARCHAR(32) NULL,
53 DATA_WYPRODUKOWANIA DATE NULL,
54
55 CONSTRAINT PK_INSTRUMENT PRIMARY KEY (ID_INSTRUMENT)
56);
57
58COMMENT ON TABLE [INSTRUMENT] IS
59 'Dane na temat kazdego egzemplarzu instrumentu';
60
61COMMENT ON COLUMN [INSTRUMENT].PESEL_MUZYK IS
62 '11-znakowy unikalny identyfikator osoby';
63
64COMMENT ON COLUMN [INSTRUMENT].TYP_INSTRUMENT IS
65 'Rodzina do jakiej klasyfikowany jest instrument.
66 N - brak danych
67 A - aerofon - dęty
68 C - chordofon - strunowy
69 I - idiofon - perkusyjny samobrzmiacy
70 M - membranofon - perkusyjny membranowy
71 E - elektrofon - instrument elektroniczny';
72
73COMMENT ON COLUMN [INSTRUMENT].NAZWA_INSTRUMENT IS
74 'Nazwa Instrumentu.';
75
76COMMENT ON COLUMN [INSTRUMENT].PRODUCENT IS
77 'Nazwa tworcy danego egzemplarzu instrumentu.';
78
79COMMENT ON COLUMN [INSTRUMENT].DATA_WYPRODUKOWANIA IS
80 'Data w jakim dniu dany egzemplarz zostal uznany jako gotowy do uzytku.';
81
82/* ================================================ */
83/* TABELA: KOMPOZYTOR */
84/* ================================================ */
85
86CREATE TABLE [KOMPOZYTOR]
87(
88 PESEL_KOMPOZYTOR CHAR(11) NOT NULL,
89 IMIE_KOMPOZYTOR VARCHAR(32) NOT NULL,
90 NAZWISKO_KOMPOZYTOR VARCHAR(32) NOT NULL,
91 DATA_URODZENIA_KOMPOZYTOR DATE NULL,
92
93 CONSTRAINT PK_KOMPOZYTOR PRIMARY KEY (PESEL_KOMPOZYTOR)
94);
95
96COMMENT ON TABLE [KOMPOZYTOR] IS
97 'Osoba tworzaca utwory.';
98
99COMMENT ON COLUMN [KOMPOZYTOR].PESEL_KOMPOZYTOR IS
100 '11 znakowy unikalny ciag znakow';
101
102COMMENT ON COLUMN [KOMPOZYTOR].IMIE_KOMPOZYTOR IS
103 'Imie jakim posluguje sie kompozytor';
104
105COMMENT ON COLUMN [KOMPOZYTOR].NAZWISKO_KOMPOZYTOR IS
106 'Nazwisko jakim posluguje sie kompozytor';
107
108COMMENT ON COLUMN [KOMPOZYTOR].DATA_URODZENIA_KOMPOZYTOR IS
109 'Data w jakiej przyszedl na swiat kompozytor';
110
111/* ================================================ */
112/* TABELA: KONCERT */
113/* ================================================ */
114
115CREATE TABLE [SALA]
116(
117 ID_SALA INTEGER DEFAULT AUTOINCREMENT,
118 NAZWA_SALA VARCHAR(64) NOT NULL,
119 LOKALIZACJA VARCHAR(32) NULL,
120 POJEMNOSC INTEGER NULL
121 CONSTRAINT CHECK_POJEMNOSC
122 CHECK (POJEMNOSC IS NULL OR (POJEMNOSC >=1)),
123
124 CONSTRAINT PK_SALA PRIMARY KEY (ID_SALA)
125);
126
127COMMENT ON TABLE [SALA] IS
128 'Miejsce w ktorym odbywaja sie koncerty.';
129
130COMMENT ON COLUMN [SALA].ID_SALA IS
131 'Unikalny identyfikator sali.';
132
133COMMENT ON COLUMN [SALA].NAZWA_SALA IS
134 'Nazwa nadana przy otwarciu sali.';
135
136COMMENT ON COLUMN [SALA].LOKALIZACJA IS
137 'Mijescowosc w jakiej znajduje sie sala.';
138
139COMMENT ON COLUMN [SALA].POJEMNOSC IS
140 'Ilosc osob jaka moze pomiescic sala';
141
142/* ================================================ */
143/* TABELA: KONCERT */
144/* ================================================ */
145
146CREATE TABLE [KONCERT]
147(
148 ID_KONCERT INTEGER DEFAULT AUTOINCREMENT,
149 ID_SALA INTEGER NOT NULL,
150 NAZWA_KONCERT VARCHAR(32) NOT NULL,
151 DATA_KONCERT DATE NOT NULL,
152
153 CONSTRAINT PK_KONCERT PRIMARY KEY (ID_KONCERT)
154);
155
156COMMENT ON TABLE [KONCERT] IS
157 'Zorganizowane wydarzenie kulturowe na ktorym prezentowane sa pozycje (utwory) instrumentalne.';
158
159COMMENT ON COLUMN [KONCERT].ID_SALA IS
160 'Unikalny identyfikator sali.';
161
162COMMENT ON COLUMN [KONCERT].NAZWA_KONCERT IS
163 'Nazwa nadana wydarzeniu przez organizora eventu.';
164
165COMMENT ON COLUMN [KONCERT].DATA_KONCERT IS
166 'Dzien w jakim ma sie odbyc koncert.';
167
168
169
170/* ================================================ */
171/* TABELA: MUZYK */
172/* ================================================ */
173
174CREATE TABLE [MUZYK]
175(
176 PESEL_MUZYK CHAR(11) NOT NULL,
177 IMIE_MUZYK VARCHAR(32) NOT NULL,
178 NAZWISKO_MUZYK VARCHAR(32) NOT NULL,
179 PLEC_MUZYK CHAR(1) NULL DEFAULT 'N'
180 CONSTRAINT CHECK_PLEC_MUZYK
181 CHECK (PLEC_MUZYK is NULL or (PLEC_MUZYK IN ('N', 'M','K'))),
182 DATA_URODZENIA_MUZYK DATE NULL,
183
184 CONSTRAINT PK_MUZYK PRIMARY KEY (PESEL_MUZYK)
185);
186
187COMMENT ON TABLE [MUZYK] IS
188 'Dane instrumentalistow bioracych udzial w koncertach.';
189
190COMMENT ON COLUMN [MUZYK].PESEL_MUZYK IS
191 '11-znakowy unikalny identyfikator osoby';
192
193COMMENT ON COLUMN [MUZYK].IMIE_MUZYK IS
194 'Imie pod jakim znany jest muzyk';
195
196COMMENT ON COLUMN [MUZYK].NAZWISKO_MUZYK IS
197 'Nazwisko pod jakim znany jest muzyk.';
198
199COMMENT ON COLUMN [MUZYK].PLEC_MUZYK IS
200 'Plec jaka identyfikuje sie muzyk.
201 N - brak danych
202 M - mezczyzna
203 K - kobieta';
204
205COMMENT ON COLUMN [MUZYK].DATA_URODZENIA_MUZYK IS
206 'Dzien w jakim przyszedl na swiat muzyk.';
207
208/* ================================================ */
209/* TABELA: ORKIESTRA */
210/* ================================================ */
211
212CREATE TABLE [ORKIESTRA]
213(
214 ID_ORKIESTRA INTEGER DEFAULT AUTOINCREMENT,
215 PESEL_DYRYGENT CHAR(11) NULL,
216 NAZWA_ORKIESTRA VARCHAR(64) NOT NULL,
217 TYP_ORKIESTRA CHAR(2) NULL DEFAULT 'Zk'
218 CONSTRAINT CHECK_TYP_ORKIESTRA
219 CHECK (TYP_ORKIESTRA IS NULL OR(TYP_ORKIESTRA IN ('Sf', 'Sm','Dt','Bb','Zk'))),
220 DATA_UTWORZENIA DATE NULL,
221
222 CONSTRAINT PK_ORKIESTRA PRIMARY KEY (ID_ORKIESTRA)
223);
224
225COMMENT ON TABLE [ORKIESTRA] IS
226 'Dane o grupach bioracych udzial w koncercach.';
227
228COMMENT ON COLUMN [ORKIESTRA].ID_ORKIESTRA IS
229 'Liczba jednoznacznie pozwalajaca zidentyfikowac grupe osob.';
230
231COMMENT ON COLUMN [ORKIESTRA].NAZWA_ORKIESTRA IS
232 'Nazwa pod jaka wystepuje dana grupa osob.';
233
234COMMENT ON COLUMN [ORKIESTRA].TYP_ORKIESTRA IS
235 'Okresla typy instrumentow wchodzacych w sklad orkiestry.
236 Sf - orkiestra symfoniczna
237 Sm- orkiestra smyczkowa
238 Dt - orkiestra Dęta
239 Bb - orkiestra rozrywkowa (bigband)
240 Zk - zespół kameralny';
241
242COMMENT ON COLUMN [ORKIESTRA].PESEL_DYRYGENT IS
243 'Unikalny 11 znakowy ciag identyfikujacy dyrygenta.';
244
245COMMENT ON COLUMN [ORKIESTRA].DATA_UTWORZENIA IS
246 'Dzien w jakim grupa osob oficalnie zaczela istniec.';
247
248/* ================================================ */
249/* TABELA: UTWOR */
250/* ================================================ */
251
252CREATE TABLE [UTWOR]
253(
254 ID_UTWOR INTEGER DEFAULT AUTOINCREMENT,
255 NAZWA_UTWOR VARCHAR(32) NOT NULL,
256 CZAS_UTWOR INTEGER NULL,
257 DATA_SKOMPONOWANIA INTEGER NULL,
258
259 CONSTRAINT PK_UTWOR PRIMARY KEY (ID_UTWOR)
260);
261
262COMMENT ON TABLE [UTWOR] IS
263'Kompozycja napisana przez danego kompozytora i wykonywana przez muzykow.';
264
265COMMENT ON COLUMN [UTWOR].ID_UTWOR IS
266'Numer katalogowy nadawany po wpisaniu kompozycji do rejestru kompozytora';
267
268COMMENT ON COLUMN [UTWOR].NAZWA_UTWOR IS
269'Tytul nadawany kompozycji przez kompozytora.';
270
271COMMENT ON COLUMN [UTWOR].CZAS_UTWOR IS
272'Czas jaki jest przewidywany na wykonanie kompozycji';
273
274COMMENT ON COLUMN [UTWOR].DATA_SKOMPONOWANIA IS
275'Data wpisania kompozycji do rejestru kompozytora.';
276
277
278
279
280/* ================================================ */
281/* Tabela: CZLONEK */
282/* ================================================ */
283
284CREATE TABLE [CZLONEK]
285(
286 PESEL_MUZYK CHAR(11) NOT NULL,
287 ID_ORKIESTRA INTEGER NOT NULL,
288
289 CONSTRAINT PK_CZLONEK
290 PRIMARY KEY CLUSTERED (PESEL_MUZYK, ID_ORKIESTRA)
291);
292
293COMMENT ON TABLE [CZLONEK] IS
294 'Relacja mowiaca czy muzyk jest w skladzie danej grupy';
295
296COMMENT ON COLUMN [CZLONEK].PESEL_MUZYK IS
297 '11-znakowy unikalny identyfikator osoby';
298
299COMMENT ON COLUMN [CZLONEK].ID_ORKIESTRA IS
300 'Liczba jednoznacznie pozwalajaca zidentyfikowac grupe osob';
301
302/* ================================================ */
303/* Tabela: KATALOG_DZIEL */
304/* ================================================ */
305
306CREATE TABLE [KATALOG_DZIEL]
307(
308 PESEL_KOMPOZYTOR CHAR(11) NOT NULL,
309 ID_UTWOR INTEGER NOT NULL,
310
311 CONSTRAINT PK_KATALOG_DZIEL PRIMARY KEY CLUSTERED (PESEL_KOMPOZYTOR, ID_UTWOR)
312);
313
314COMMENT ON TABLE [KATALOG_DZIEL] IS
315 'Informacje jakie dziala stworzyl dany kompozytor';
316
317COMMENT ON COLUMN [KATALOG_DZIEL].ID_UTWOR IS
318 'Numer katalogowy nadawany po wpisaniu kompozycji do rejestru kompozytora';
319
320COMMENT ON COLUMN [KATALOG_DZIEL].PESEL_KOMPOZYTOR IS
321 'Unikalny 11 znakowy ciag identyfikujacy kompozytora';
322
323/* ================================================ */
324/* Tabela: REPERTUAR */
325/* ================================================ */
326
327CREATE TABLE [REPERTUAR]
328(
329 ID_UTWOR INTEGER NOT NULL,
330 ID_ORKIESTRA INTEGER NOT NULL,
331
332 CONSTRAINT PK_REPERTUAR PRIMARY KEY CLUSTERED (ID_UTWOR, ID_ORKIESTRA)
333);
334
335COMMENT ON TABLE [REPERTUAR] IS
336 'Utwory jakie jest wstanie wykONac orkiestra.';
337
338COMMENT ON COLUMN [REPERTUAR].ID_UTWOR IS
339 'Numer katalogowy nadawany po wpisaniu kompozycji do rejestru kompozytora';
340
341COMMENT ON COLUMN [REPERTUAR].ID_ORKIESTRA IS
342 'Liczba jednoznacnzie pozwalajaca zidentyfikowac grupe osob.';
343
344/* ================================================ */
345/* Tabela: SOLISTA */
346/* ================================================ */
347
348CREATE TABLE [SOLISTA]
349(
350 PESEL_MUZYK CHAR(11) NOT NULL,
351 ID_WYKONANIE INTEGER NOT NULL,
352
353 CONSTRAINT PK_SOLISTA PRIMARY KEY CLUSTERED (PESEL_MUZYK, ID_WYKONANIE)
354);
355
356COMMENT ON TABLE [SOLISTA] IS
357 'Muzyk wystepujacy w pojedynke.';
358
359COMMENT ON COLUMN [SOLISTA].PESEL_MUZYK IS
360 '11-znakowy unikalny identyfikator osoby';
361
362COMMENT ON COLUMN [SOLISTA].ID_WYKONANIE IS
363 'Numer identyfikacyjny wykONania';
364
365
366/* ================================================ */
367/* Tabela: WYKONANIE */
368/* ================================================ */
369
370CREATE TABLE [WYKONANIE]
371(
372 ID_WYKONANIE INTEGER DEFAULT AUTOINCREMENT,
373 ID_UTWOR INTEGER NOT NULL,
374 ID_KONCERT INTEGER NOT NULL,
375 ID_ORKIESTRA INTEGER NULL,
376
377 CONSTRAINT PK_WYKONANIE PRIMARY KEY (ID_WYKONANIE)
378);
379
380COMMENT ON TABLE [WYKONANIE] IS
381'Dane odnosnie wykonania danego utworu.';
382
383COMMENT ON COLUMN [WYKONANIE].ID_WYKONANIE IS
384'Numer identyfikacyjny wykonania.';
385
386COMMENT ON COLUMN [WYKONANIE].ID_UTWOR IS
387'Numer identyfikacyjny utworu.';
388
389COMMENT ON COLUMN [WYKONANIE].ID_KONCERT IS
390'Numer identyfikacyjny koncertu.';
391
392COMMENT ON COLUMN [WYKONANIE].ID_ORKIESTRA IS
393'Numer identyfikacyjny orkiestry.';
394
395
396/* ================================================ */
397/* Tabela: WYPOSAZENIE_SALI */
398/* ================================================ */
399/*
400CREATE TABLE [WYPOSAZENIE_SALI]
401(
402 ID_INSTRUMENT INTEGER NOT NULL,
403 ID_SALA INTEGER NOT NULL,
404
405 CONSTRAINT PK_WYPOSAZENIE_SALI PRIMARY KEY CLUSTERED (ID_INSTRUMENT, ID_SALA)
406);
407
408COMMENT ON TABLE [WYPOSAZENIE_SALI] IS
409 'Duze instrumenty stacjonarne znajdujace sie na stale w salach';
410
411COMMENT ON COLUMN [WYPOSAZENIE_SALI].ID_SALA IS
412 'Unikalny identyfikator sali.';
413*/
414
415
416/* ================================================ */
417/* Tabela: ORKIESTRA_ARCHIWUM */
418/* ================================================ */
419
420CREATE TABLE [ORKIESTRA_ARCHIWUM]
421(
422 ID_ORKIESTRA_ARCHIWUM INTEGER DEFAULT AUTOINCREMENT,
423 ID_ORKIESTRA_A INTEGER NULL,
424 PESEL_MUZYK_A CHAR(11) NOT NULL,
425 NAZWA_ORKIESTRA_A CHAR(32) NULL,
426 OSOBA_A CHAR(64) NOT NULL,
427
428 CONSTRAINT PK_ORKIESTRA_ARCHIWUM PRIMARY KEY CLUSTERED (ID_ORKIESTRA_ARCHIWUM)
429);
430
431 COMMENT ON TABLE [ORKIESTRA_ARCHIWUM] IS
432 'Tabela zawierajaca dane na temat czlonkowstwa muzykow w orkiestrach';
433
434COMMENT ON COLUMN [ORKIESTRA_ARCHIWUM].ID_ORKIESTRA_ARCHIWUM IS
435 'Licznik rekordow w archiwum.';
436
437COMMENT ON COLUMN [ORKIESTRA_ARCHIWUM].ID_ORKIESTRA_A IS
438 'Numer identyfikacyjny orkiestry.';
439
440COMMENT ON COLUMN [ORKIESTRA_ARCHIWUM].PESEL_MUZYK_A IS
441 'Numer identyfikacyjny muzyka.';
442
443COMMENT ON COLUMN [ORKIESTRA_ARCHIWUM].NAZWA_ORKIESTRA_A IS
444 'Nazwa orkiestry.';
445
446COMMENT ON COLUMN [ORKIESTRA_ARCHIWUM].OSOBA_A IS
447 'Imie i nazwisko osoby';
448
449
450/* ================================================ */
451/* Tabela: KONCERT_ARCHIWUM */
452/* ================================================ */
453
454CREATE TABLE [SOLISCI_ARCHIWUM]
455(
456 ID_SOLISCI_ARCHIWUM INTEGER DEFAULT AUTOINCREMENT,
457 UTWOR_A VARCHAR(32) NULL,
458 KONCERT_A VARCHAR(32) NULL,
459 ORKIESTRA_A VARCHAR(64) NULL,
460 SOLISCI_A VARCHAR(256) NULL,
461 DATA_A DATE NOT NULL,
462
463 CONSTRAINT PK_SOLISCI_ARCHIWUM PRIMARY KEY CLUSTERED (ID_SOLISCI_ARCHIWUM)
464);
465
466 COMMENT ON TABLE [SOLISCI_ARCHIWUM] IS
467 'Tabela zawierajaca dane na temat solistow z poszczegolnych wykonan utworow na koncercie';
468
469COMMENT ON COLUMN [SOLISCI_ARCHIWUM].ID_SOLISCI_ARCHIWUM IS
470 'Licznik w archiwum';
471
472COMMENT ON COLUMN [SOLISCI_ARCHIWUM].UTWOR_A IS
473 'Nazwa wykonanego utworu';
474
475COMMENT ON COLUMN [SOLISCI_ARCHIWUM].KONCERT_A IS
476 'Nazwa zagranego koncertu';
477
478COMMENT ON COLUMN [SOLISCI_ARCHIWUM].ORKIESTRA_A IS
479 'Nazwa orkiestry ktora brala udzial';
480
481COMMENT ON COLUMN [SOLISCI_ARCHIWUM].SOLISCI_A IS
482 'Imiona i nazwiska solistow';
483
484COMMENT ON COLUMN [SOLISCI_ARCHIWUM].DATA_A IS
485 'Dzien w ktorym odbyl sie koncert';
486
487
488/* ================================================ */
489/* klucze glowne i indeksy */
490/* ================================================ */
491ALTER TABLE [CZLONEK]
492 ADD CONSTRAINT FK_CZLONEK_MUZYK FOREIGN KEY (PESEL_MUZYK)
493 REFERENCES [MUZYK] (PESEL_MUZYK)
494 ON UPDATE RESTRICT
495 ON
496 DELETE RESTRICT;
497
498 ALTER TABLE [CZLONEK]
499 ADD CONSTRAINT FK_CZLONEK_ORKIESTRA FOREIGN KEY (ID_ORKIESTRA)
500 REFERENCES [ORKIESTRA] (ID_ORKIESTRA)
501 ON UPDATE RESTRICT
502 ON
503 DELETE RESTRICT;
504
505 ALTER TABLE INSTRUMENT
506 ADD CONSTRAINT FK_INSTRUMENT_MUZYK FOREIGN KEY (PESEL_MUZYK)
507 REFERENCES [MUZYK] (PESEL_MUZYK)
508 ON UPDATE RESTRICT
509 ON
510 DELETE RESTRICT;
511
512 ALTER TABLE KONCERT
513 ADD CONSTRAINT FK_KONCERT_SALA FOREIGN KEY (ID_SALA)
514 REFERENCES [SALA] (ID_SALA)
515 ON UPDATE RESTRICT
516 ON
517 DELETE RESTRICT;
518
519 ALTER TABLE KATALOG_DZIEL
520 ADD CONSTRAINT FK_KATALOG_DZIEL_KOMPOZYTOR FOREIGN KEY (PESEL_KOMPOZYTOR)
521 REFERENCES [KOMPOZYTOR] (PESEL_KOMPOZYTOR)
522 ON UPDATE RESTRICT
523 ON
524 DELETE RESTRICT;
525
526 ALTER TABLE KATALOG_DZIEL
527 ADD CONSTRAINT FK_KATALOG_DZIEL_UTWOR FOREIGN KEY (ID_UTWOR)
528 REFERENCES [UTWOR] (ID_UTWOR)
529 ON UPDATE RESTRICT
530 ON
531 DELETE RESTRICT;
532
533 ALTER TABLE ORKIESTRA
534 ADD CONSTRAINT FK_ORKIESTRA_DYRYGENT FOREIGN KEY (PESEL_DYRYGENT)
535 REFERENCES [DYRYGENT] (PESEL_DYRYGENT)
536 ON UPDATE RESTRICT
537 ON
538 DELETE RESTRICT;
539
540 ALTER TABLE REPERTUAR
541 ADD CONSTRAINT FK_REPERTUAR_UTWOR FOREIGN KEY (ID_UTWOR)
542 REFERENCES [UTWOR] (ID_UTWOR)
543 ON UPDATE RESTRICT
544 ON
545 DELETE RESTRICT;
546
547 ALTER TABLE REPERTUAR
548 ADD CONSTRAINT FK_REPERTUAR_ORKIESTRA FOREIGN KEY (ID_ORKIESTRA)
549 REFERENCES [ORKIESTRA] (ID_ORKIESTRA)
550 ON UPDATE RESTRICT
551 ON
552 DELETE RESTRICT;
553
554 ALTER TABLE SOLISTA
555 ADD CONSTRAINT FK_SOLISTA_MUZYK FOREIGN KEY (PESEL_MUZYK)
556 REFERENCES [MUZYK] (PESEL_MUZYK)
557 ON UPDATE RESTRICT
558 ON
559 DELETE RESTRICT;
560
561 ALTER TABLE SOLISTA
562 ADD CONSTRAINT FK_SOLISTA_WYKONANIE FOREIGN KEY (ID_WYKONANIE)
563 REFERENCES [WYKONANIE] (ID_WYKONANIE)
564 ON UPDATE RESTRICT
565 ON
566 DELETE RESTRICT;
567
568 ALTER TABLE WYKONANIE
569 ADD CONSTRAINT FK_WYKONANIE_UTWOR FOREIGN KEY (ID_UTWOR)
570 REFERENCES UTWOR (ID_UTWOR)
571 ON UPDATE RESTRICT
572 ON
573 DELETE RESTRICT;
574
575 ALTER TABLE WYKONANIE
576 ADD CONSTRAINT FK_WYKONANIE_KONCERT FOREIGN KEY (ID_KONCERT)
577 REFERENCES KONCERT (ID_KONCERT)
578 ON UPDATE RESTRICT
579 ON
580 DELETE RESTRICT;
581
582 ALTER TABLE WYKONANIE
583 ADD CONSTRAINT FK_WYKONANIE_ORKIESTRA FOREIGN KEY (ID_ORKIESTRA)
584 REFERENCES [ORKIESTRA] (ID_ORKIESTRA)
585 ON UPDATE RESTRICT
586 ON
587 DELETE RESTRICT;
588
589 /*
590 ALTER TABLE WYPOSAZENIE_SALI
591 ADD CONSTRAINT FK_WYPOSAZENIE_SALI_INSTRUMENT FOREIGN KEY (ID_INSTRUMENT)
592 REFERENCES [INSTRUMENT] (ID_INSTRUMENT
593 )
594 ON UPDATE RESTRICT
595 ON
596 DELETE RESTRICT;
597
598 ALTER TABLE WYPOSAZENIE_SALI
599 ADD CONSTRAINT FK_WYPOSAZENIE_SALI_SALA FOREIGN KEY (ID_SALA)
600 REFERENCES [SALA] (ID_SALA)
601 ON UPDATE RESTRICT
602 ON
603 DELETE RESTRICT;
604 */
605
606
607
608
609 CREATE INDEX CZLONEK_FK ON [CZLONEK]
610 (
611 PESEL_MUZYK ASC
612 );
613
614 CREATE INDEX CZLONEK2_FK ON [CZLONEK]
615 (
616 ID_ORKIESTRA ASC
617 );
618
619 ------------------------------- DYRYGENT
620
621 CREATE UNIQUE INDEX DYRYGENT_PK ON [DYRYGENT]
622 (
623 PESEL_DYRYGENT ASC
624 );
625
626 ------------------------------- INSTRUMENT
627
628 CREATE UNIQUE INDEX INSTRUMENT_PK ON [INSTRUMENT]
629 (
630 ID_INSTRUMENT ASC
631 );
632
633 CREATE UNIQUE INDEX Posiadanie_FK ON [INSTRUMENT]
634 (
635 PESEL_MUZYK ASC
636 );
637
638 ------------------------------- KOMPOZYTOR
639
640 CREATE UNIQUE INDEX KOMPOZYTOR_PK ON [KOMPOZYTOR]
641 (
642 PESEL_KOMPOZYTOR ASC
643 );
644
645 ------------------------------- KONCERT
646
647 CREATE UNIQUE INDEX KONCERT_PK ON [KONCERT]
648 (
649 ID_KONCERT ASC
650 );
651
652 CREATE INDEX Miejsce_koncertu_FK ON [KONCERT]
653 (
654 ID_SALA ASC
655 );
656
657 ------------------------------- KATALOG DZIEL
658 /*
659 CREATE UNIQUE CLUSTERED INDEX KATALOG_DZIEL_PK ON KATALOG_DZIEL
660 (
661 PESEL_KOMPOZYTOR ASC,
662 ID_UTWOR ASC
663 );
664 */
665
666 CREATE INDEX KATALOG_DZIEL_FK ON [KATALOG_DZIEL]
667 (
668 PESEL_KOMPOZYTOR ASC
669 );
670
671 CREATE INDEX KATALOG_DZIEL2_FK ON [KATALOG_DZIEL]
672 (
673 ID_UTWOR ASC
674 );
675
676 ------------------------------- MUZYK
677
678 CREATE UNIQUE INDEX MUZYK_PK ON [MUZYK]
679 (
680 PESEL_MUZYK ASC
681 );
682
683 ------------------------------- ORKIESTRA
684
685 CREATE UNIQUE INDEX ORKIESTRA_PK ON [ORKIESTRA]
686 (
687 ID_ORKIESTRA ASC
688 );
689
690 CREATE INDEX Dyrygowanie_FK ON [ORKIESTRA]
691 (
692 PESEL_DYRYGENT ASC
693 );
694
695 ------------------------------- REPERTUAR
696 /*
697 CREATE UNIQUE CLUSTERED INDEX REPERTUAR_PK ON [REPERTUAR]
698 (
699 ID_UTWOR ASC,
700 ID_ORKIESTRA ASC
701 );
702 */
703 CREATE INDEX REPERTUAR_FK ON [REPERTUAR]
704 (
705 ID_UTWOR ASC
706 );
707
708 CREATE INDEX REPERTUAR2_FK ON [REPERTUAR]
709 (
710 ID_ORKIESTRA ASC
711 );
712
713 ------------------------------- SALA
714
715 CREATE UNIQUE INDEX SALA_PK ON [SALA]
716 (
717 ID_SALA ASC
718 );
719
720 ------------------------------- SOLISTA
721 /*
722 CREATE UNIQUE CLUSTERED INDEX SOLISTA_PK ON [SOLISTA]
723 (
724 PESEL_MUZYK ASC, ID_WYKONANIE ASC
725 );
726 */
727 CREATE INDEX SOLISTA_FK ON [SOLISTA]
728 (
729 PESEL_MUZYK ASC
730 );
731
732 CREATE INDEX SOLISTA2_FK ON [SOLISTA]
733 (
734 ID_WYKONANIE ASC
735 );
736
737 ------------------------------- UTWOR
738
739 CREATE UNIQUE INDEX UTWOR_PK ON [UTWOR]
740 (
741 ID_UTWOR ASC
742 );
743
744 ------------------------------- WYKONANIE
745
746 CREATE UNIQUE INDEX Wykonanie_PK ON [WYKONANIE]
747 (
748 ID_WYKONANIE ASC
749 );
750
751 CREATE INDEX Pozycja_koncertu_FK on [WYKONANIE]
752 (
753 ID_KONCERT ASC
754 );
755
756 CREATE INDEX Pozycja_FK ON [WYKONANIE]
757 (
758 ID_UTWOR ASC
759 );
760
761 CREATE INDEX Wykonanie_orkiestra_FK ON [WYKONANIE]
762 (
763 ID_ORKIESTRA ASC
764 );
765
766
767
768
769 ------------------------------- WYPOSAZENIE_SALI
770 /*
771 CREATE UNIQUE CLUSTERED INDEX WYPOSAZENIE_SALI_PK ON [WYPOSAZENIE_SALI]
772 (
773 ID_INSTRUMENT ASC,
774 ID_SALA ASC
775 );
776 */
777 /*
778 CREATE INDEX WYPOSAZENIE_SALI_FK ON [WYPOSAZENIE_SALI]
779 (
780 ID_INSTRUMENT ASC
781 );
782
783 CREATE INDEX WYPOSAZENIE_SALI2_FK ON [WYPOSAZENIE_SALI]
784 (
785 ID_SALA ASC
786
787 );
788*/
789
790---------------
791--- WIDOKI
792----------------
793
794CREATE MATERIALIZED VIEW KONCERTY AS
795 SELECT DISTINCT
796 [KONCERT].NAZWA_KONCERT AS KONCERT,
797 LIST ([UTWOR].NAZWA_UTWOR ,', ') AS UTWOR,
798 LIST ([ORKIESTRA].NAZWA_ORKIESTRA, ', ') AS ORKIESTRA,
799 LIST ([DYRYGENT].IMIE_DYRYGENT + ' ' + [DYRYGENT].NAZWISKO_DYRYGENT, ', ') AS DYRYGENT,
800 CONVERT(VARCHAR, SUM([UTWOR].CZAS_UTWOR) / 60 ) + 'h ' +
801 CONVERT(VARCHAR, SUM([UTWOR].CZAS_UTWOR) % 60) + 'm' AS CZAS_KONCERTU,
802 [KONCERT].DATA_KONCERT AS DATA
803 FROM
804 [ORKIESTRA] INNER JOIN [DYRYGENT] ON [ORKIESTRA].PESEL_DYRYGENT = [DYRYGENT].PESEL_DYRYGENT,
805 [ORKIESTRA] INNER JOIN [WYKONANIE] ON [ORKIESTRA].ID_ORKIESTRA = [WYKONANIE].ID_ORKIESTRA,
806 [UTWOR] INNER JOIN [WYKONANIE] ON [UTWOR].ID_UTWOR = [WYKONANIE].ID_UTWOR,
807 [KONCERT] INNER JOIN [WYKONANIE] ON [KONCERT].ID_KONCERT = [WYKONANIE].ID_KONCERT
808 GROUP BY KONCERT, DATA
809ORDER BY DATA;
810
811COMMENT ON VIEW [SIKER].KONCERTY IS
812 'Informacje o koncertach';
813
814---
815
816CREATE VIEW WSPOLPRACA AS
817 SELECT
818 ([MUZYK].IMIE_MUZYK + ' ' + [MUZYK].NAZWISKO_MUZYK) AS INSTRUMENTALISTA,
819 [ORKIESTRA].NAZWA_ORKIESTRA AS ORKIESTRY,
820 COUNT ([ORKIESTRA].NAZWA_ORKIESTRA) AS 'WSPOLNE KONCERTY',
821 [INSTRUMENT].NAZWA_INSTRUMENT AS SPECJALNOSC,
822
823 (SELECT TOP 1 [KONCERT].DATA_KONCERT --PIERWSZY
824 FROM
825 [KONCERT] INNER JOIN [WYKONANIE]
826 ON [KONCERT].ID_KONCERT = [WYKONANIE].ID_KONCERT,
827 [WYKONANIE] INNER JOIN [CZLONEK]
828 ON [WYKONANIE].ID_ORKIESTRA = [CZLONEK].ID_ORKIESTRA,
829 [CZLONEK] INNER JOIN [MUZYK]
830 ON [CZLONEK].PESEL_MUZYK = [MUZYK].PESEL_MUZYK
831 WHERE
832 ([MUZYK].IMIE_MUZYK + ' ' + [MUZYK].NAZWISKO_MUZYK) = INSTRUMENTALISTA
833 ORDER BY [KONCERT].DATA_KONCERT ASC )
834 || ' / ' ||
835 (SELECT TOP 1 [KONCERT].DATA_KONCERT -- OSTATNI
836 FROM
837 [KONCERT] INNER JOIN [WYKONANIE]
838 ON [KONCERT].ID_KONCERT = [WYKONANIE].ID_KONCERT,
839 [WYKONANIE] INNER JOIN [CZLONEK]
840 ON [WYKONANIE].ID_ORKIESTRA = [CZLONEK].ID_ORKIESTRA,
841 [CZLONEK] INNER JOIN [MUZYK]
842 ON [CZLONEK].PESEL_MUZYK = [MUZYK].PESEL_MUZYK
843 WHERE
844 ([MUZYK].IMIE_MUZYK + ' ' + [MUZYK].NAZWISKO_MUZYK) = INSTRUMENTALISTA
845 ORDER BY [KONCERT].DATA_KONCERT DESC ) AS 'PIERWSZY/OSTATNI KONCERT'
846
847 FROM
848 [CZLONEK],
849 [MUZYK],
850 [ORKIESTRA] RIGHT JOIN [WYKONANIE]
851 ON [ORKIESTRA].ID_ORKIESTRA = [WYKONANIE].ID_ORKIESTRA,
852 [MUZYK] LEFT JOIN [INSTRUMENT]
853 ON [MUZYK].PESEL_MUZYK = [INSTRUMENT].PESEL_MUZYK
854
855 WHERE
856 [MUZYK].PESEL_MUZYK = [CZLONEK].PESEL_MUZYK
857 AND [ORKIESTRA].ID_ORKIESTRA = [CZLONEK].ID_ORKIESTRA
858
859 GROUP BY INSTRUMENTALISTA, ORKIESTRY, SPECJALNOSC
860ORDER BY INSTRUMENTALISTA ASC;
861
862COMMENT ON VIEW WSPOLPRACA IS
863 'Widok przedstawiajacy z jakimi orkiestrami, ile razy i od jakiego czasu dana osoba wspolpracuje';
864
865---
866
867CREATE VIEW TWORCZOSC AS
868 SELECT
869 [UTWOR].NAZWA_UTWOR AS KOMPOZYCJA,
870 ([KOMPOZYTOR].IMIE_KOMPOZYTOR + ' ' + [KOMPOZYTOR].NAZWISKO_KOMPOZYTOR) AS KOMPOZYTOR,
871 CASE
872 WHEN [UTWOR].DATA_SKOMPONOWANIA >=1400 AND [UTWOR].DATA_SKOMPONOWANIA <1600
873 THEN 'RENESANS'
874 WHEN [UTWOR].DATA_SKOMPONOWANIA >=1600 AND [UTWOR].DATA_SKOMPONOWANIA <1750
875 THEN ' BAROK'
876 WHEN [UTWOR].DATA_SKOMPONOWANIA >=1750 AND [UTWOR].DATA_SKOMPONOWANIA <1800
877 THEN 'KLASYCYZM'
878 WHEN [UTWOR].DATA_SKOMPONOWANIA >=1800 AND [UTWOR].DATA_SKOMPONOWANIA <1900
879 THEN 'ROMANTYZM'
880 WHEN [UTWOR].DATA_SKOMPONOWANIA >=1900 AND [UTWOR].DATA_SKOMPONOWANIA <1975
881 THEN 'XX WIEK'
882 WHEN [UTWOR].DATA_SKOMPONOWANIA >=1975
883 THEN 'WPOLCZESNOSC'
884 ELSE 'SREDNIOWIECZE' END AS EPOKA,
885 [UTWOR].DATA_SKOMPONOWANIA - YEAR([KOMPOZYTOR].DATA_URODZENIA_KOMPOZYTOR) AS WIEK_KOMPOZYTORA
886
887 FROM
888 [UTWOR] RIGHT JOIN [KATALOG_DZIEL]
889 ON [UTWOR].ID_UTWOR = [KATALOG_DZIEL].ID_UTWOR,
890 [KOMPOZYTOR] RIGHT JOIN [KATALOG_DZIEL]
891 ON [KOMPOZYTOR].PESEL_KOMPOZYTOR = [KATALOG_DZIEL].PESEL_KOMPOZYTOR;
892
893COMMENT ON VIEW TWORCZOSC IS
894 'Widok przedstawiajacy spis utworo, w jakich epokach powstaly i ile lat mial kompozytor gdy go stworzyl';
895
896--- LICZNIK
897/*
898CREATE SEQUENCE "ARCHIWUM_S"
899 START WITH 1
900 INCREMENT BY 1
901 MAXVALUE 1000;
902*/
903
904
905---------------
906--- PROCEDURY
907----------------
908
909CREATE PROCEDURE [SIKER].GRYWANIE ( IN PESEL_IN CHAR(11))
910 BEGIN
911 DECLARE PESEL CHAR(11);
912 DECLARE IMIE VARCHAR(32);
913 DECLARE NAZWISKO VARCHAR(32);
914 DECLARE NAZWA VARCHAR(64);
915 DECLARE ORKIESTRY TEXT;
916
917 DECLARE LICZNIK INTEGER;
918
919 DECLARE KURSOR CURSOR FOR (
920 SELECT [ORKIESTRA].NAZWA_ORKIESTRA
921 FROM
922 [ORKIESTRA] INNER JOIN [CZLONEK]
923 ON [ORKIESTRA].ID_ORKIESTRA = [CZLONEK].ID_ORKIESTRA,
924 [MUZYK] INNER JOIN [CZLONEK]
925 ON [MUZYK].PESEL_MUZYK = [CZLONEK].PESEL_MUZYK
926 WHERE [MUZYK].PESEL_MUZYK = PESEL_IN
927 AND [MUZYK].PESEL_MUZYK = [CZLONEK].PESEL_MUZYK
928 AND [CZLONEK].ID_ORKIESTRA = [ORKIESTRA].ID_ORKIESTRA
929 );
930
931 SET IMIE = (SELECT [MUZYK].IMIE_MUZYK
932 FROM [MUZYK]
933 WHERE [MUZYK].PESEL_MUZYK = PESEL_IN);
934 SET NAZWISKO = (SELECT [MUZYK].NAZWISKO_MUZYK
935 FROM [MUZYK]
936 WHERE [MUZYK].PESEL_MUZYK = PESEL_IN);
937
938 MESSAGE 'Osoba ' + IMIE + ' ' + NAZWISKO + ' wystepuje z nastepujacymi orkiestrami:\n' TO CLIENT;
939
940 SET LICZNIK = 0;
941 OPEN KURSOR;
942 petla: LOOP
943 FETCH NEXT KURSOR INTO ORKIESTRY;
944 IF SQLCODE <> 0
945 THEN LEAVE petla;
946 ENDIF;
947 SET LICZNIK = LICZNIK + 1;
948 MESSAGE LICZNIK || ') ' || ORKIESTRY || '\n' TO CLIENT;
949 END LOOP;
950 CLOSE KURSOR
951END;
952
953COMMENT ON PROCEDURE GRYWANIE IS
954 'Procedura zwracajaca w formie komunikatu zespoly z jakimi wystepuje danyh muzyk. Jako argument wejsciowy przyjmuje pesel.';
955---
956
957CREATE PROCEDURE [SIKER].PRZESZLOSC_MUZYKA (IN PESEL_IN CHAR(11))
958 BEGIN
959 DECLARE PESEL CHAR(11);
960 DECLARE DANE_OSOBOWE VARCHAR(64);
961 DECLARE NAZWA VARCHAR(64);
962 DECLARE ORKIESTRY TEXT;
963
964 DECLARE LICZNIK INTEGER;
965
966 DECLARE KURSOR CURSOR FOR(
967 SELECT [ORKIESTRA_ARCHIWUM].NAZWA_ORKIESTRA_A
968 FROM [ORKIESTRA_ARCHIWUM]
969 WHERE [ORKIESTRA_ARCHIWUM].PESEL_MUZYK_A = PESEL_IN
970
971 EXCEPT
972
973 SELECT [ORKIESTRA].NAZWA_ORKIESTRA
974 FROM
975 [ORKIESTRA] INNER JOIN [CZLONEK]
976 ON [ORKIESTRA].ID_ORKIESTRA = [CZLONEK].ID_ORKIESTRA,
977 [MUZYK] INNER JOIN [CZLONEK]
978 ON [MUZYK].PESEL_MUZYK = [CZLONEK].PESEL_MUZYK
979 WHERE [MUZYK].PESEL_MUZYK = PESEL_IN
980 );
981
982 SET DANE_OSOBOWE = (SELECT [ORKIESTRA_ARCHIWUM].OSOBA_A
983 FROM [ORKIESTRA_ARCHIWUM]
984 WHERE [ORKIESTRA_ARCHIWUM].PESEL_MUZYK_A = PESEL_IN
985 GROUP BY [ORKIESTRA_ARCHIWUM].OSOBA_A);
986
987
988 MESSAGE 'OSOBA ' + DANE_OSOBOWE + ' nie wystepuje juz z nastepujacymi orkiestrami:\n' TO CLIENT;
989
990 SET LICZNIK = 0;
991 OPEN KURSOR;
992 PETLA: LOOP
993 FETCH NEXT KURSOR INTO ORKIESTRY;
994 IF SQLCODE <> 0
995 THEN IF LICZNIK = 0
996 THEN MESSAGE 'BRAK' TO CLIENT;
997 ENDIF;
998 LEAVE PETLA;
999 ENDIF;
1000 SET LICZNIK = LICZNIK + 1;
1001 MESSAGE LICZNIK || ') ' || ORKIESTRY TO CLIENT;
1002 END LOOP;
1003 CLOSE KURSOR
1004END;
1005
1006COMMENT ON PROCEDURE PRZESZLOSC_MUZYKA IS
1007 'Procedura zwracajaca w formie komunikatu wykaz zespolow z jakimi nie wpolpracuje juz muzyk. Jako argument wejsciowy przyjmuje pesel.';
1008
1009
1010CREATE PROCEDURE [SIKER].PODZIAL_INSTRUMENTOW (IN ORK INTEGER)
1011 BEGIN
1012 DECLARE ILOSC INTEGER;
1013 DECLARE NAZWA CHAR(32);
1014 DECLARE TYP CHAR(1);
1015 DECLARE TYP_I CHAR(1);
1016 DECLARE LICZNIK INT;
1017
1018 DECLARE KURSOR_TYP CURSOR FOR(
1019 SELECT [INSTRUMENT].TYP_INSTRUMENT
1020 FROM
1021 [INSTRUMENT] INNER JOIN [CZLONEK]
1022 ON [INSTRUMENT].PESEL_MUZYK = [CZLONEK].PESEL_MUZYK,
1023 WHERE ORK = [CZLONEK].ID_ORKIESTRA
1024 GROUP BY [INSTRUMENT].TYP_INSTRUMENT);
1025
1026 DECLARE KURSOR_NAZWA CURSOR FOR(
1027 SELECT [INSTRUMENT].NAZWA_INSTRUMENT, COUNT([INSTRUMENT].ID_INSTRUMENT), [INSTRUMENT].TYP_INSTRUMENT
1028 FROM
1029 [INSTRUMENT] INNER JOIN [CZLONEK]
1030 ON [INSTRUMENT].PESEL_MUZYK = [CZLONEK].PESEL_MUZYK,
1031 WHERE ORK = [CZLONEK].ID_ORKIESTRA
1032 GROUP BY [INSTRUMENT].NAZWA_INSTRUMENT, [INSTRUMENT].TYP_INSTRUMENT);
1033
1034 SET NAZWA = (SELECT [ORKIESTRA].NAZWA_ORKIESTRA FROM [ORKIESTRA] WHERE ORK=ID_ORKIESTRA);
1035 MESSAGE 'INSTRUMENTY ORKIESTRY "' || NAZWA || '" ZAWIERAJA SKLAD:' TO CLIENT;
1036
1037 SET LICZNIK =0;
1038
1039 OPEN KURSOR_TYP;
1040 PETLA_TYP: LOOP
1041 FETCH NEXT KURSOR_TYP INTO TYP;
1042 IF SQLCODE <> 0
1043 THEN LEAVE PETLA_TYP;
1044 ENDIF;
1045 SET ILOSC = ILOSC_INSTRUMENTOW(ORK, TYP);
1046 SET LICZNIK = LICZNIK + 1;
1047 MESSAGE LICZNIK || ') ';
1048 CASE
1049 WHEN TYP = 'A' THEN MESSAGE '\nAEROFONY: ' || ILOSC TO CLIENT;
1050 WHEN TYP = 'C' THEN MESSAGE '\nCHORDOFONY: ' || ILOSC TO CLIENT;
1051 WHEN TYP = 'I' THEN MESSAGE '\nIDIOFONY: ' || ILOSC TO CLIENT;
1052 WHEN TYP = 'M' THEN MESSAGE '\nMEMBRANOFONY: ' || ILOSC TO CLIENT;
1053 WHEN TYP = 'E' THEN MESSAGE '\nELEKTROFONY: ' || ILOSC TO CLIENT;
1054 END;
1055
1056 OPEN KURSOR_NAZWA;
1057 PETLA_NAZWA:LOOP
1058 FETCH NEXT KURSOR_NAZWA INTO NAZWA, ILOSC, TYP_I;
1059 IF SQLCODE <> 0
1060 THEN LEAVE PETLA_NAZWA;
1061 ENDIF;
1062 IF TYP = TYP_I THEN
1063 MESSAGE NAZWA || ' w ilosci: ' || ILOSC TO CLIENT;
1064 ENDIF;
1065 END LOOP;
1066 CLOSE KURSOR_NAZWA;
1067
1068 END LOOP;
1069 CLOSE KURSOR_TYP;
1070
1071 END;
1072
1073COMMENT ON PROCEDURE PODZIAL_INSTRUMENTOW IS
1074 'Procedura zwracajaca w formie komunikatu ilosciowe zestawienie instrumentow z podzialem na typy';
1075
1076CREATE PROCEDURE [SIKER].DODAJ_REPERTUAR (IN KOMPOZYCJA INTEGER, IN ORK INTEGER)
1077 BEGIN
1078 DECLARE KOMUNIKAT TEXT;
1079
1080 IF EXISTS (
1081 SELECT [REPERTUAR].ID_UTWOR, [REPERTUAR].ID_ORKIESTRA
1082 FROM [REPERTUAR]
1083 WHERE
1084 KOMPOZYCJA = [REPERTUAR].ID_UTWOR AND
1085 ORK = [REPERTUAR].ID_ORKIESTRA ) THEN
1086 SET @KOMUNIKAT='ORKIESTRA ZAWIERA TEN UTWOR W SWOIM REPERTUARZE';
1087 MESSAGE KOMUNIKAT TO CLIENT;
1088 ELSE
1089 INSERT INTO [REPERTUAR] (ID_UTWOR, ID_ORKIESTRA) VALUES (KOMPOZYCJA, ORK);
1090 SET KOMUNIKAT='DODANO DO REPERTUARU';
1091 MESSAGE KOMUNIKAT TO CLIENT;
1092 ENDIF;
1093 END;
1094
1095COMMENT ON PROCEDURE DODAJ_REPERTUAR IS
1096 'Procedura dodajaca utwor do repertuaru orkiestry i sprawdzajace czy juz nie istneje';
1097
1098
1099
1100---------------
1101--- TRIGERY
1102----------------
1103
1104CREATE TRIGGER [SIKER].ARCHIWIZACJA_CZLONKOW
1105 AFTER INSERT ,DELETE ,UPDATE ON [CZLONEK]
1106 REFERENCING NEW AS NEWS
1107 FOR EACH ROW
1108
1109 BEGIN
1110 DECLARE PESEL CHAR(11); -- PESEL_MUZYK
1111 DECLARE ZESPOL INT; -- ID_ORKIESTRA
1112 DECLARE NAZWA CHAR(64); -- NAZWA_ORKIESTRA
1113 DECLARE NAME CHAR(64); -- IMIE I NAZWISKO
1114
1115 SET PESEL = [NEWS].PESEL_MUZYK;
1116 SET ZESPOL = [NEWS].ID_ORKIESTRA;
1117
1118 SET NAZWA = (
1119 SELECT [ORKIESTRA].NAZWA_ORKIESTRA
1120 FROM [ORKIESTRA]
1121 WHERE [ORKIESTRA].ID_ORKIESTRA = [NEWS].ID_ORKIESTRA);
1122 SET NAME = (
1123 SELECT [MUZYK].IMIE_MUZYK + ' ' + [MUZYK].NAZWISKO_MUZYK
1124 FROM [MUZYK]
1125 WHERE [NEWS].PESEL_MUZYK = [MUZYK].PESEL_MUZYK);
1126
1127 INSERT INTO [ORKIESTRA_ARCHIWUM] (ID_ORKIESTRA_A, PESEL_MUZYK_A, NAZWA_ORKIESTRA_A, OSOBA_A)
1128 VALUES (ZESPOL, PESEL, NAZWA, NAME);
1129END;
1130
1131COMMENT ON TRIGGER ARCHIWIZACJA_CZLONKOW IS
1132 'TRIGGER dodajacy do archiwizujacy dane o czlonkach orkiestr';
1133
1134---
1135
1136CREATE TRIGGER [SIKER].WYSTEP_GOSCINNY
1137 AFTER INSERT, UPDATE ON [SOLISTA]
1138
1139 REFERENCING NEW AS NEWS
1140 FOR EACH ROW
1141
1142 BEGIN
1143 DECLARE UTWOR_tmp VARCHAR(32);
1144 DECLARE KONCERT_tmp VARCHAR(32);
1145 DECLARE ORKIESTRA_tmp VARCHAR(64);
1146 DECLARE SOLISCI_tmp VARCHAR(256);
1147 DECLARE DATA_tmp DATE;
1148
1149 SET UTWOR_tmp = (
1150 SELECT [UTWOR].NAZWA_UTWOR
1151 FROM
1152 [UTWOR] INNER JOIN [WYKONANIE]
1153 ON [UTWOR].ID_UTWOR = [WYKONANIE].ID_UTWOR
1154 WHERE [NEWS].ID_WYKONANIE = [WYKONANIE].ID_WYKONANIE
1155 );
1156
1157 SET KONCERT_tmp = (
1158 SELECT [KONCERT].NAZWA_KONCERT
1159 FROM
1160 [WYKONANIE] INNER JOIN [KONCERT]
1161 ON [WYKONANIE].ID_KONCERT = [KONCERT].ID_KONCERT
1162 WHERE [NEWS].ID_WYKONANIE = [WYKONANIE].ID_WYKONANIE
1163 );
1164
1165 SET ORKIESTRA_tmp = (
1166 SELECT [ORKIESTRA].NAZWA_ORKIESTRA
1167 FROM
1168 [WYKONANIE] INNER JOIN [ORKIESTRA]
1169 ON [WYKONANIE].ID_ORKIESTRA = [ORKIESTRA].ID_ORKIESTRA
1170 WHERE [NEWS].ID_WYKONANIE = [WYKONANIE].ID_WYKONANIE
1171 );
1172
1173 SET DATA_tmp = (
1174 SELECT [KONCERT].DATA_KONCERT
1175 FROM
1176 [WYKONANIE] INNER JOIN [KONCERT]
1177 ON [WYKONANIE].ID_KONCERT = [KONCERT].ID_KONCERT
1178 WHERE [NEWS].ID_WYKONANIE = [WYKONANIE].ID_WYKONANIE
1179 );
1180
1181 SET SOLISCI_tmp = (
1182 SELECT LIST([MUZYK].IMIE_MUZYK + ' ' + [MUZYK].NAZWISKO_MUZYK, ',')
1183 FROM
1184 [WYKONANIE] INNER JOIN [SOLISTA]
1185 ON [WYKONANIE].ID_WYKONANIE = [SOLISTA].ID_WYKONANIE,
1186 [SOLISTA] INNER JOIN [MUZYK]
1187 ON [SOLISTA].PESEL_MUZYK = [MUZYK].PESEL_MUZYK
1188 WHERE [NEWS].ID_WYKONANIE = [WYKONANIE].ID_WYKONANIE
1189 );
1190
1191 INSERT INTO [SOLISCI_ARCHIWUM] (UTWOR_A, KONCERT_A, ORKIESTRA_A, SOLISCI_A, DATA_A)
1192 VALUES ( UTWOR_tmp, KONCERT_tmp, ORKIESTRA_tmp, SOLISCI_tmp, DATA_tmp);
1193
1194 END;
1195
1196COMMENT ON TRIGGER WYSTEP_GOSCINNY IS
1197 'TRIGGER dodajacy do archiwum dane o solistach wystepujacych na koncertach';
1198
1199---
1200
1201CREATE TRIGGER [SIKER].KONTROLA_KONCERT
1202 BEFORE INSERT ,UPDATE
1203 ORDER 1 ON [WYKONANIE]
1204 REFERENCING NEW AS NEWS
1205 FOR EACH ROW
1206
1207 BEGIN
1208 DECLARE CZAS INTEGER;
1209 DECLARE POBRANE INTEGER;
1210 DECLARE KURSOR CURSOR FOR
1211 (SELECT [UTWOR].CZAS_UTWOR
1212 FROM [UTWOR] INNER JOIN [WYKONANIE]
1213 ON [UTWOR].ID_UTWOR = [WYKONANIE].ID_UTWOR
1214 WHERE
1215 [NEWS].ID_UTWOR = [UTWOR].ID_UTWOR AND
1216 [NEWS].ID_WYKONANIE = [WYKONANIE].ID_WYKONANIE);
1217 SET CZAS = 0;
1218 OPEN KURSOR;
1219 PETLA:LOOP
1220 FETCH KURSOR INTO POBRANE;
1221 IF SQLCODE <> 0 THEN
1222 LEAVE PETLA;
1223 ENDIF;
1224 SET CZAS = CZAS + POBRANE
1225 END LOOP PETLA;
1226 CLOSE KURSOR;
1227
1228 IF CZAS > 300 THEN
1229 MESSAGE 'KONCERT PRZEKRACZA LIMIT CZASOWY' TO CLIENT;
1230 DELETE FROM [KONCERT] WHERE [KONCERT].ID_KONCERT = [NEWS].ID_KONCERT;
1231 ENDIF;
1232
1233 END;
1234
1235COMMENT ON TRIGGER KONTROLA_KONCERT IS
1236 'TRIGGER wysylajacy komunikat do organizatora ze koncert przekracza czas komfortowego sluchania';
1237
1238
1239---------------
1240--- FUNKCJE
1241----------------
1242
1243CREATE FUNCTION [SIKER].NAJAKTYWNIEJSZY_MUZYK (IN MAX INTEGER)
1244 RETURNS CHAR(64)
1245
1246 BEGIN
1247 DECLARE LICZNIK INTEGER;
1248 DECLARE RETURN_NAME CHAR(64);
1249 DECLARE ILOSC INTEGER;
1250
1251 DECLARE LIDER CURSOR FOR(
1252 SELECT [MUZYK].IMIE_MUZYK + ' ' + [MUZYK].NAZWISKO_MUZYK, COUNT([WYKONANIE].ID_WYKONANIE) AS ILOSC
1253 INTO RETURN_NAME, ILOSC
1254 FROM
1255 [MUZYK] INNER JOIN [CZLONEK]
1256 ON [MUZYK].PESEL_MUZYK = [CZLONEK].PESEL_MUZYK,
1257 [CZLONEK] INNER JOIN [ORKIESTRA]
1258 ON [CZLONEK].ID_ORKIESTRA = [ORKIESTRA].ID_ORKIESTRA,
1259 [ORKIESTRA] INNER JOIN [WYKONANIE]
1260 ON [ORKIESTRA].ID_ORKIESTRA = [WYKONANIE].ID_ORKIESTRA
1261 GROUP BY [MUZYK].IMIE_MUZYK, [MUZYK].NAZWISKO_MUZYK
1262 ORDER BY ILOSC DESC
1263 );
1264
1265 SET NAJWIECEJ = 0;
1266 SET LICZNIK = 1;
1267 MESSAGE 'RANKING NAJAKTYWNIEJSZYCH MUZYKOW \n OTO NAJLEPSZYCH ' || MAX || ' MUZYKOW:' TO CLIENT;
1268
1269 OPEN LIDER;
1270 PETLA: LOOP
1271 FETCH NEXT LIDER INTO RETURN_NAME, ILOSC;
1272 IF SQLCODE <> 0 THEN
1273 LEAVE PETLA
1274 ENDIF;
1275
1276 IF LICZNIK < MAX + 1 THEN
1277 MESSAGE LICZNIK || ') ' || RETURN_NAME || ' - ZAGRAL - ' || ILOSC || ' KONCERTOW' TO CLIENT;
1278 ENDIF;
1279 SET LICZNIK = LICZNIK + 1;
1280 END LOOP;
1281 CLOSE LIDER;
1282 RETURN RETURN_NAME; -- osoba z najwieksza iloscia
1283 END;
1284
1285COMMENT ON PROCEDURE NAJAKTYWNIEJSZY_MUZYK IS
1286 'Wysyla w formie komunikatu liste najaktywniejszych muzykow i zwraca osobe w kolejnosci okreslonej przez argument wejsciowy';
1287---
1288
1289CREATE FUNCTION [SIKER].KONCERTOWANIE (IN PESEL_IN CHAR(11), DATE_IN DATE)
1290 RETURNS INT
1291
1292 BEGIN
1293 DECLARE ILOSC INT;
1294
1295 SET ILOSC = (
1296 SELECT COUNT ([KONCERT].DATA_KONCERT)
1297 FROM
1298 [MUZYK] INNER JOIN [CZLONEK]
1299 ON [MUZYK].PESEL_MUZYK = [CZLONEK].PESEL_MUZYK,
1300 [WYKONANIE] INNER JOIN [KONCERT]
1301 ON [WYKONANIE].ID_KONCERT = [KONCERT].ID_KONCERT
1302 WHERE
1303 [MUZYK].PESEL_MUZYK = pesel_IN AND
1304 [CZLONEK].ID_ORKIESTRA = [WYKONANIE].ID_ORKIESTRA AND
1305 [KONCERT].DATA_KONCERT >= DATE_IN
1306 );
1307 RETURN ILOSC;
1308 END ;
1309
1310COMMENT ON PROCEDURE KONCERTOWANIE IS
1311 'Zwraca ile koncertow dana osoba grala po danej dacie';
1312
1313---
1314
1315CREATE FUNCTION [SIKER].CZAS_NA_SCENIE (IN PESEL CHAR(11), IN ORKIESTRA INTEGER)
1316 RETURNS INTEGER
1317 DETERMINISTIC
1318 BEGIN
1319 DECLARE CZAS INTEGER;
1320 DECLARE POBRANE INTEGER;
1321 DECLARE KURSOR CURSOR FOR (
1322 SELECT [UTWOR].CZAS_UTWOR
1323 FROM
1324 [UTWOR] INNER JOIN [WYKONANIE]
1325 ON [UTWOR].ID_UTWOR = [WYKONANIE].ID_UTWOR,
1326 [WYKONANIE] INNER JOIN [ORKIESTRA]
1327 ON [WYKONANIE].ID_ORKIESTRA = [ORKIESTRA].ID_ORKIESTRA,
1328 [ORKIESTRA] INNER JOIN [CZLONEK]
1329 ON [ORKIESTRA].ID_ORKIESTRA = [CZLONEK].ID_ORKIESTRA,
1330 [CZLONEK] INNER JOIN [MUZYK]
1331 ON [CZLONEK].PESEL_MUZYK = [MUZYK].PESEL_MUZYK
1332 WHERE
1333 PESEL = [MUZYK].PESEL_MUZYK AND
1334 ORK = [WYKONANIE].ID_ORKIESTRA
1335
1336 UNION
1337
1338 SELECT [UTWOR].CZAS_UTWOR
1339 FROM
1340 [UTWOR] INNER JOIN [WYKONANIE]
1341 ON [UTWOR].ID_UTWOR = [WYKONANIE].ID_UTWOR,
1342 [WYKONANIE] INNER JOIN [SOLISTA]
1343 ON [WYKONANIE].ID_WYKONANIE = [SOLISTA].ID_WYKONANIE
1344 WHERE
1345 PESEL = [SOLISTA].PESEL_MUZYK AND
1346 ORK = [WYKONANIE].ID_ORKIESTRA);
1347
1348 SET CZAS = 0;
1349 OPEN KURSOR;
1350 PETLA:LOOP
1351 FETCH NEXT KURSOR INTO POBRANE;
1352 IF SQLCODE <> 0 THEN
1353 LEAVE PETLA;
1354 ENDIF;
1355 SET CZAS = CZAS + POBRANE;
1356 END LOOP PETLA;
1357 CLOSE KURSOR;
1358 MESSAGE 'CZAS SPEDZONY NA SCENIE: \N' TO CLIENT;
1359 RETURN CZAS;
1360END;
1361
1362COMMENT ON PROCEDURE CZAS_NA_SCENIE IS
1363 'Funkcja zwraca czas jaki muzyk spedzil z dana orkiestra na scenie';
1364
1365---
1366
1367CREATE FUNCTION [SIKER].ILOSC_INSTRUMENTOW (IN ORK INTEGER, IN TYP CHAR(1))
1368 RETURNS INT
1369 DETERMINISTIC
1370 BEGIN
1371 DECLARE ILOSC INTEGER;
1372 DECLARE POBRANY INTEGER;
1373
1374 DECLARE KURSOR CURSOR FOR(
1375 SELECT COUNT([INSTRUMENT].ID_INSTRUMENT)
1376 FROM
1377 [ORKIESTRA] INNER JOIN [CZLONEK]
1378 ON [ORKIESTRA].ID_ORKIESTRA = [CZLONEK].ID_ORKIESTRA,
1379 [CZLONEK] INNER JOIN [MUZYK]
1380 ON [MUZYK].PESEL_MUZYK=[CZLONEK].PESEL_MUZYK,
1381 [MUZYK] INNER JOIN [INSTRUMENT]
1382 ON [MUZYK].PESEL_MUZYK = [INSTRUMENT].PESEL_MUZYK
1383 WHERE
1384 ORK = [CZLONEK].ID_ORKIESTRA AND
1385 TYP = [INSTRUMENT].TYP_INSTRUMENT);
1386
1387 SET ILOSC = 0;
1388 OPEN KURSOR;
1389 PETLA:LOOP
1390 FETCH NEXT KURSOR INTO POBRANY;
1391 IF SQLCODE <> 0 THEN
1392 LEAVE PETLA;
1393 ENDIF;
1394 SET ILOSC = ILOSC + POBRANY;
1395 END LOOP PETLA;
1396 RETURN ILOSC;
1397 END;
1398
1399COMMENT ON PROCEDURE ILOSC_INSTRUMENTOW IS
1400 'Funkcja zwraca ilosc instrumentow danego typu z danej orkiestry';