· 8 years ago · Dec 24, 2017, 10:42 PM
1# DDL Izrazi za ER dijagramot
2
3DROP TABLE IF EXISTS Vraboten;
4CREATE TABLE Vraboten (
5 MBR VARCHAR(13) PRIMARY KEY,
6 imePrezime VARCHAR(20),
7 adresa VARCHAR(50),
8 email VARCHAR(50)
9);
10
11DROP TABLE IF EXISTS Telefon;
12CREATE TABLE Telefon (
13 MBR VARCHAR(13) REFERENCES Vraboten(MBR),
14 telefon INT(9),
15 PRIMARY KEY (MBR, telefon)
16);
17
18DROP TABLE IF EXISTS Advokat;
19CREATE TABLE Advokat (
20 MBR VARCHAR(13) REFERENCES Vraboten(MBR),
21 oblast VARCHAR(30),
22 PRIMARY KEY(MBR)
23);
24ALTER TABLE Advokat ADD VkAngazman INT NOT NULL;
25
26DROP TABLE IF EXISTS Notar;
27CREATE TABLE Notar (
28 MBR VARCHAR(13) REFERENCES Vraboten(MBR),
29 PRIMARY KEY (MBR)
30);
31
32DROP TABLE IF EXISTS Pripravnik;
33CREATE TABLE Pripravnik (
34 MBR VARCHAR(13) REFERENCES Vraboten(MBR),
35 staz INT(2),
36 MBR_Advokat VARCHAR(13),
37 PRIMARY KEY (MBR),
38 FOREIGN KEY advokat (MBR_Advokat) REFERENCES Advokat (MBR)
39);
40
41DROP TABLE IF EXISTS Klient;
42CREATE TABLE Klient (
43 id INT AUTO_INCREMENT,
44 tip INT,
45 ime VARCHAR(20),
46 adresa VARCHAR(50),
47 PRIMARY KEY(id)
48);
49
50DROP TABLE IF EXISTS Zakon;
51CREATE TABLE Zakon (
52 ime VARCHAR(20) PRIMARY KEY,
53 oblast VARCHAR(40)
54);
55
56DROP TABLE IF EXISTS PravnaAktivnost;
57CREATE TABLE PravnaAktivnost (
58 naziv VARCHAR(20),
59 PRIMARY KEY (naziv)
60);
61
62DROP TABLE IF EXISTS PravenSlucaj;
63CREATE TABLE PravenSlucaj (
64 naziv VARCHAR(15),
65 opis VARCHAR(300),
66 klient_id INT,
67 PRIMARY KEY (naziv),
68 FOREIGN KEY klient (klient_id) REFERENCES Klient(id)
69);
70
71DROP TABLE IF EXISTS PravenSlucaj_Zakon;
72CREATE TABLE PravenSlucaj_Zakon (
73 naziv VARCHAR(15),
74 ime VARCHAR(20),
75 cena INT(6),
76 FOREIGN KEY slucaj (naziv) REFERENCES PravenSlucaj(naziv),
77 FOREIGN KEY zakon (ime) REFERENCES Zakon(ime)
78);
79ALTER TABLE PravenSlucaj_Zakon ADD PRIMARY KEY (naziv, ime);
80
81DROP TABLE IF EXISTS Angazman;
82CREATE TABLE Angazman (
83 MBR_advokat VARCHAR(13) REFERENCES Advokat(MBR),
84 naziv_slucaj VARCHAR(15) REFERENCES PravenSlucaj(naziv),
85 klient_id INT,
86 DatumZapocnuvanje DATETIME CHECK (DatumZapocnuvanje > '2015-01-01 00:00:00'),
87 DatumTraenje DATETIME,
88 FOREIGN KEY klient (klient_id) REFERENCES Klient(id)
89);
90ALTER TABLE Angazman ADD PRIMARY KEY (MBR_advokat, naziv_slucaj, klient_id);
91
92DROP TABLE IF EXISTS Izvrsuvanje;
93CREATE TABLE Izvrsuvanje (
94 MBR_advokat VARCHAR(13),
95 naziv_slucaj VARCHAR(15),
96 naziv_aktivnost VARCHAR(20),
97 datum DATETIME CHECK (datum > '2015-01-01 00:00:00'),
98 FOREIGN KEY advokat (MBR_advokat) REFERENCES Advokat(MBR),
99 FOREIGN KEY slucaj (naziv_slucaj) REFERENCES PravenSlucaj(naziv),
100 FOREIGN KEY aktivnost (naziv_aktivnost) REFERENCES PravnaAktivnost(naziv)
101);
102ALTER TABLE Izvrsuvanje ADD PRIMARY KEY (MBR_advokat, naziv_slucaj, naziv_aktivnost);
103
104DROP TABLE IF EXISTS Podgotovka;
105CREATE TABLE Podgotovka (
106 ID INT AUTO_INCREMENT,
107 MBR_Pripravnik VARCHAR(13) REFERENCES Pripravnik(MBR),
108 naziv_slucaj VARCHAR(15) REFERENCES PravenSlucaj(naziv),
109 naziv_aktivnost VARCHAR(20) REFERENCES PravnaAktivnost(naziv),
110 datum DATETIME CHECK (datum > '2015-01-01 00:00:00'),
111 rab_saati INT,
112 PRIMARY KEY (ID)
113);
114ALTER TABLE Podgotovka ADD FOREIGN KEY (MBR_Pripravnik) REFERENCES Pripravnik(MBR);
115ALTER TABLE Podgotovka ADD FOREIGN KEY (naziv_slucaj) REFERENCES PravenSlucaj(naziv);
116ALTER TABLE Podgotovka ADD FOREIGN KEY (naziv_aktivnost) REFERENCES PravnaAktivnost(naziv);
117
118DROP TABLE IF EXISTS Zadaca;
119CREATE TABLE Zadaca (
120 podgotovka_id INT NOT NULL,
121 zadaca VARCHAR(30),
122 FOREIGN KEY podgotovka (podgotovka_id) REFERENCES Podgotovka (ID)
123);
124
125DROP TABLE IF EXISTS TipDokument;
126CREATE TABLE TipDokument (
127 tip VARCHAR(10) PRIMARY KEY,
128 opis VARCHAR(30),
129 cena INT
130);
131
132DROP TABLE IF EXISTS Dokument;
133CREATE TABLE Dokument (
134 tip VARCHAR(10) REFERENCES TipDokument(tip),
135 naziv VARCHAR(20) NOT NULL,
136 PRIMARY KEY(tip, naziv)
137);
138
139DROP TABLE IF EXISTS Zaverka;
140CREATE TABLE Zaverka (
141 ID INT PRIMARY KEY AUTO_INCREMENT,
142 klient_id INT,
143 notar_mbr VARCHAR(13),
144 doc_tip VARCHAR(10),
145 doc_naziv VARCHAR(20),
146 datum_izvrsuvanje DATETIME CHECK (datum_izvrsuvanje > '2015-01-01 00:00:00'),
147 FOREIGN KEY klient (klient_id) REFERENCES Klient(id),
148 FOREIGN KEY notar (notar_mbr) REFERENCES Notar(MBR),
149 FOREIGN KEY dokument (doc_tip, doc_naziv) REFERENCES Dokument(tip, naziv)
150);
151
152DROP TABLE IF EXISTS Proverka;
153CREATE TABLE Proverka (
154 zaverka_id INT REFERENCES Zaverka(ID),
155 proverka ENUM('LicnaIdentifikacija', 'OriginalenDokument', 'IzjavaOriginalnost'),
156 PRIMARY KEY (zaverka_id, proverka)
157);
158
159# DML Izrazi za ER dijagramot
160
161# a)
162SELECT imePrezime FROM Vraboten
163 INNER JOIN Advokat ON Vraboten.MBR = Advokat.MBR
164 WHERE email LIKE '%gmail.com%';
165
166# b)
167SELECT notar_mbr, doc_tip FROM Zaverka
168 WHERE datum_izvrsuvanje LIKE '2016%';
169
170# v)
171SELECT Klient.* FROM Klient
172 INNER JOIN PravenSlucaj P ON Klient.id = P.klient_id
173 LEFT OUTER JOIN Zaverka Z ON Klient.id = Z.klient_id
174 LEFT OUTER JOIN Angazman A ON Klient.id = A.klient_id
175 WHERE Z.klient_id IS NULL AND A.klient_id IS NULL;
176
177# g)
178CREATE VIEW MaksZaverka AS (
179 SELECT doc_tip, count(*) as c FROM Zaverka
180 WHERE datum_izvrsuvanje LIKE '2016%'
181 GROUP BY doc_tip
182 ORDER BY c DESC
183 LIMIT 1
184);
185
186SELECT doc_tip, count(*) as c FROM Zaverka
187 WHERE datum_izvrsuvanje LIKE '2016%'
188 GROUP BY doc_tip
189 HAVING c = (SELECT c FROM MaksZaverka);
190
191# d)
192CREATE VIEW IstiOblasti AS (
193 SELECT naziv_slucaj FROM SlucaeviOblasti AS SO
194 INNER JOIN AdvokatiOblastiSlucaevi AOS ON SO.naziv = AOS.naziv_slucaj
195 WHERE oblast = oblast_advokat
196);
197
198SELECT naziv FROM PravenSlucaj
199 LEFT OUTER JOIN IstiOblasti ON PravenSlucaj.naziv = IstiOblasti.naziv_slucaj
200 WHERE naziv_slucaj IS NULL;
201
202# gj)
203CREATE VIEW PripravniciPotroseniSaati AS (
204 SELECT MBR_Pripravnik, naziv_aktivnost, SUM(rab_saati) as saati FROM Podgotovka
205 WHERE datum LIKE '2016%'
206 GROUP BY MBR_Pripravnik, naziv_aktivnost
207);
208
209CREATE VIEW MaxSaatiPoPripravnik AS (
210 SELECT MBR_Pripravnik, MAX(saati) AS Max FROM PripravniciPotroseniSaati
211 GROUP BY MBR_Pripravnik
212);
213
214SELECT M.MBR_Pripravnik, naziv_aktivnost FROM PripravniciPotroseniSaati P
215 JOIN MaxSaatiPoPripravnik M ON P.MBR_Pripravnik = M.MBR_Pripravnik AND P.saati = M.Max;
216
217# e)
218CREATE VIEW NaplataZaSlucaevi AS (
219 SELECT K.id as ID, K.ime, SUM(cena) as Naplata FROM PravenSlucaj
220 INNER JOIN Klient K ON PravenSlucaj.klient_id = K.id
221 INNER JOIN PravenSlucaj_Zakon Z ON PravenSlucaj.naziv = Z.naziv
222 GROUP BY klient_id
223);
224
225CREATE VIEW NaplataZaDokumenti AS (
226 SELECT K.id as ID, K.ime, SUM(cena) as Naplata FROM Zaverka Z
227 INNER JOIN Klient K ON Z.klient_id = K.id
228 INNER JOIN TipDokument T ON Z.doc_tip = T.tip
229 GROUP BY K.id
230);
231
232SELECT ID, ime, SUM(Naplata) AS Naplata FROM (
233 SELECT * FROM NaplataZaSlucaevi
234 UNION ALL
235 SELECT * FROM NaplataZaDokumenti
236) A
237GROUP BY ID, ime;
238
239# Trigerot za VkAngazman
240DROP TRIGGER IF EXISTS TR_Izvrsuvanje_VkAngazman;
241CREATE TRIGGER TR_Izvrsuvanje_VkAngazman
242 AFTER UPDATE
243 ON Izvrsuvanje
244 FOR EACH ROW
245 BEGIN
246 CALL update_advokat(NEW.MBR_advokat);
247 END;
248
249DROP TRIGGER IF EXISTS TR_Izvrsuvanje_VkAngazman_Insert;
250CREATE TRIGGER TR_Izvrsuvanje_VkAngazman_Insert
251 AFTER INSERT
252 ON Izvrsuvanje
253 FOR EACH ROW
254 BEGIN
255 CALL update_advokat(NEW.MBR_advokat);
256 END;
257
258DROP TRIGGER IF EXISTS TR_Podgotovka_VkAngazman;
259CREATE TRIGGER TR_Podgotovka_VkAngazman
260 AFTER UPDATE
261 ON Podgotovka
262 FOR EACH ROW
263 BEGIN
264 SET @Advokat_MBR = (SELECT MBR_Advokat FROM Pripravnik WHERE Pripravnik.MBR = NEW.MBR_Pripravnik);
265 CALL update_advokat(@Advokat_MBR);
266 END;
267
268DROP TRIGGER IF EXISTS TR_Podgotovka_VkAngazman_Insert;
269CREATE TRIGGER TR_Podgotovka_VkAngazman_Insert
270 AFTER INSERT
271 ON Podgotovka
272 FOR EACH ROW
273 BEGIN
274 SET @Advokat_MBR = (SELECT MBR_Advokat FROM Pripravnik WHERE Pripravnik.MBR = NEW.MBR_Pripravnik);
275 CALL update_advokat(@Advokat_MBR);
276 END;
277
278DELIMITER //
279DROP PROCEDURE IF EXISTS update_advokat;
280CREATE PROCEDURE update_advokat(IN MBR VARCHAR(13))
281 BEGIN
282 SET @izvrsuvanja = (SELECT COUNT(*) FROM Izvrsuvanje WHERE Izvrsuvanje.MBR_advokat = MBR) * 8;
283 SET @rabSaati = (SELECT SUM(rab_saati) VkupnoSaati FROM Pripravnik
284 LEFT JOIN Podgotovka ON Pripravnik.MBR = Podgotovka.MBR_Pripravnik
285 WHERE Pripravnik.MBR_Advokat = MBR);
286 IF (@rabSaati IS NULL) THEN
287 SET @rabSaati = 0;
288 END IF;
289 UPDATE Advokat SET Advokat.VkAngazman = @izvrsuvanja + @rabSaati WHERE Advokat.MBR = MBR;
290 END//