· 6 years ago · Jul 01, 2019, 07:42 PM
1USE master;
2DROP DATABASE IF EXISTS Wypozyczalnia;
3GO
4
5CREATE DATABASE Wypozyczalnia;
6GO
7
8USE Wypozyczalnia;
9GO
10
11DROP TABLE IF EXISTS Klienci;
12DROP TABLE IF EXISTS Miejsca;
13DROP TABLE IF EXISTS Wypozyczenia;
14DROP TABLE IF EXISTS Rezerwacje;
15DROP TABLE IF EXISTS Samochody;
16DROP TABLE IF EXISTS Wyposazenie;
17DROP TABLE IF EXISTS Administratorzy;
18DROP TABLE IF EXISTS Mechanicy;
19DROP TABLE IF EXISTS Naprawy;
20
21--------- CREATE - UTWÓRZ TABELE I POWI¥ZANIA
22
23CREATE TABLE Miejsca
24(
25 id INT PRIMARY KEY IDENTITY,
26 miasto VARCHAR(24) NOT NULL,
27 adres VARCHAR(40) NOT NULL
28);
29
30CREATE TABLE Pracownicy
31(
32 PESEL BIGINT PRIMARY KEY,
33 imie VARCHAR(14) NOT NULL,
34 nazwisko VARCHAR(24) NOT NULL,
35 pensja INT NOT NULL,
36 data_zatr DATE DEFAULT GETDATE() NOT NULL,
37 punkt INT REFERENCES Miejsca(id)
38);
39
40CREATE TABLE Administratorzy
41(
42 PESEL BIGINT REFERENCES Pracownicy(PESEL) PRIMARY KEY,
43 imie VARCHAR(14) NOT NULL,
44 nazwisko VARCHAR(24) NOT NULL,
45 pensja INT NOT NULL,
46 data_zatr DATE DEFAULT GETDATE() NOT NULL,
47 punkt INT REFERENCES Miejsca(id)
48);
49
50CREATE TABLE Mechanicy
51(
52 PESEL BIGINT REFERENCES Pracownicy(PESEL) PRIMARY KEY,
53 imie VARCHAR(14) NOT NULL,
54 nazwisko VARCHAR(24) NOT NULL,
55 pensja INT NOT NULL,
56 data_zatr DATE DEFAULT GETDATE() NOT NULL,
57 punkt INT REFERENCES Miejsca(id)
58);
59
60CREATE TABLE Klienci
61(
62 PESEL BIGINT PRIMARY KEY,
63 imie VARCHAR(14) NOT NULL,
64 nazwisko VARCHAR(24) NOT NULL,
65 rabat DECIMAL(3,2) CHECK (rabat BETWEEN 0 AND 1) DEFAULT 0,
66 opiekun BIGINT REFERENCES Administratorzy(PESEL)
67);
68
69CREATE TABLE Wyposazenie
70(
71 id INT PRIMARY KEY IDENTITY,
72 klimatyzacja VARCHAR(3) CHECK (klimatyzacja IN ('Tak', 'Nie')),
73 nawigacja VARCHAR(3) CHECK (nawigacja IN ('Tak', 'Nie')),
74 podg_fotele VARCHAR(3) CHECK (podg_fotele IN ('Tak', 'Nie')),
75 UNIQUE(klimatyzacja, nawigacja, podg_fotele)
76);
77
78CREATE TABLE Samochody
79(
80 nr_rej VARCHAR(7) PRIMARY KEY,
81 marka VARCHAR(15) NOT NULL,
82 model VARCHAR(15) NOT NULL,
83 rodzaj VARCHAR(10) CHECK (rodzaj IN ('SUV', 'Sedan', 'Hatchback', 'Kombi', 'Coupe')) NOT NULL,
84 wyposazenie INT REFERENCES Wyposazenie(id) NOT NULL,
85 cena INT NOT NULL
86);
87
88CREATE TABLE Wypozyczenia
89(
90 id INT PRIMARY KEY IDENTITY,
91 klient BIGINT REFERENCES Klienci(PESEL) NOT NULL,
92 m_wypozyczenia INT REFERENCES Miejsca(id) NOT NULL,
93 m_zwrotu INT REFERENCES Miejsca(id) NOT NULL,
94 data_wyp DATE NOT NULL,
95 data_zwr DATE NOT NULL,
96 czas_wyp AS (DATEDIFF(day,data_wyp,data_zwr)),
97 samochod VARCHAR(7) REFERENCES Samochody(nr_rej) NOT NULL,
98 koszt DECIMAL(6, 2)
99
100);
101
102CREATE TABLE Rezerwacje
103(
104 id INT PRIMARY KEY IDENTITY,
105 data_od DATE NOT NULL,
106 data_do DATE NOT NULL,
107 czy_odwolana VARCHAR(3) CHECK (czy_odwolana IN ('Tak', 'Nie')) DEFAULT 'Nie' NOT NULL,
108 klient BIGINT REFERENCES Klienci(PESEL) NOT NULL,
109 samochod VARCHAR(7) REFERENCES Samochody(nr_rej) NOT NULL,
110 kaucja DECIMAL(6, 2)
111);
112
113CREATE TABLE Naprawy
114(
115 data_naprawy DATE,
116 mechanik BIGINT REFERENCES Mechanicy(PESEL),
117 samochod VARCHAR(7) REFERENCES Samochody(nr_rej),
118 PRIMARY KEY (data_naprawy, mechanik, samochod)
119);
120
121---------- INSERT - WSTAW DANE
122
123INSERT INTO Miejsca VALUES
124('Poznan', 'Wlodarska 13'),
125('Wloclawek', 'Torunska 222'),
126('Bialystok', 'Szkolna 17');
127
128INSERT INTO Pracownicy VALUES
129(77062284913, 'Jaroslaw', 'Andrzejewski', 2300, '2018-8-21 00:00:00', 3),
130(85031856940, 'Kamil', 'Piotrowicz', 2250, '2018-10-12 00:00:00', 2),
131(93120470322, 'Jan', 'Rodowicz', 2100, '2019-3-9 00:00:00', 1);
132
133INSERT INTO Administratorzy VALUES
134(77062284913, 'Jaroslaw', 'Andrzejewski', 2300, '2018-8-21 00:00:00', 3),
135(85031856940, 'Kamil', 'Piotrowicz', 2250, '2018-10-12 00:00:00', 2);
136
137INSERT INTO Mechanicy VALUES
138(93120470322, 'Jan', 'Rodowicz', 2100, '2019-3-9 00:00:00', 1);
139
140INSERT INTO Klienci VALUES
141(88110283398, 'Piotr', 'Kowalski', 0.4, 77062284913),
142(75042281109, 'Michal', 'Ziolkowski', 0.25, 77062284913),
143(91053074821, 'Robert', 'Wieckiewicz', 0, 85031856940);
144
145INSERT INTO Wyposazenie VALUES
146('Tak', 'Nie', 'Nie'),
147('Nie', 'Nie', 'Nie'),
148('Tak', 'Tak', 'Tak');
149
150INSERT INTO Samochody VALUES
151('CW41111', 'Chevrolet', 'Lacetti', 'Sedan', 1, 150),
152('WE9649A', 'Volvo', 'V40', 'Hatchback', 3, 390),
153('PO81383', 'Audi', 'A4', 'Sedan', 2, 200);
154
155INSERT INTO Wypozyczenia(klient, m_wypozyczenia, m_zwrotu, data_wyp, data_zwr, samochod, koszt) VALUES
156(88110283398, 1, 1, '2019-4-21 21:15:00', '2019-4-24 13:10:00', 'CW41111', 450),
157(75042281109, 2, 3, '2019-5-13 09:53:00', '2019-5-14 11:32:00', 'WE9649A', 390),
158(91053074821, 2, 1, '2019-6-03 15:41:00', '2019-6-20 18:53:00', 'PO81383', 3400),
159(88110283398, 1, 3, '2019-6-04 13:45:00', '2019-6-09 23:44:00', 'CW41111', 750);
160
161INSERT INTO Rezerwacje(data_od, data_do, klient, samochod) VALUES
162('2019-4-21 21:15:00', '2019-4-24 13:10:00', 88110283398, 'CW41111'),
163('2019-5-13 09:53:00', '2019-5-14 11:32:00', 75042281109, 'WE9649A'),
164('2019-6-03 15:41:00', '2019-6-11 18:53:00', 91053074821, 'PO81383');
165
166INSERT INTO Naprawy VALUES
167('2019-6-11 00:00:00', 93120470322, 'CW41111');
168
169GO
170CREATE VIEW Obecne_Wypozyczenia(samochod, do_kiedy, klient)
171AS
172(
173 SELECT samochod, data_zwr, klient
174 FROM Wypozyczenia
175 WHERE GETDATE() BETWEEN data_wyp AND data_zwr
176);
177GO
178
179CREATE VIEW Samochody_Naprawiane_W_Ostatnim_Miesiacu(marka, model, nr_rej)
180AS
181(
182 SELECT DISTINCT S.marka, S.model, S.nr_rej
183 FROM Samochody S
184 JOIN Naprawy N ON N.samochod = S.nr_rej
185 WHERE DATEDIFF(month, N.data_naprawy, GETDATE()) < 1
186);
187GO
188
189CREATE VIEW Powracajacy_klienci(PESEL, imie, nazwisko, liczba_wypozyczen)
190AS
191(
192 SELECT K.PESEL, K.imie, K.nazwisko, COUNT(*)
193 FROM Klienci K
194 RIGHT JOIN Wypozyczenia W ON W.klient = K.PESEL
195 GROUP BY K.PESEL, K.imie, K.nazwisko
196 HAVING COUNT(*) > 1
197);
198GO
199
200CREATE FUNCTION ufn_koszt
201(
202 @data_wyp DATE,
203 @data_zwr DATE,
204 @samochod VARCHAR(7)
205)
206 RETURNS DECIMAL(6,2)
207AS
208BEGIN
209 RETURN (DATEDIFF(day, @data_wyp, @data_zwr)*(SELECT cena FROM Samochody WHERE nr_rej = @samochod));
210END
211GO
212
213CREATE FUNCTION ufn_kaucja
214(
215 @data_wyp DATE,
216 @data_zwr DATE,
217 @samochod VARCHAR(7)
218)
219 RETURNS DECIMAL(6,2)
220AS
221BEGIN
222 RETURN 4*(SELECT cena FROM Samochody WHERE nr_rej = @samochod);
223END
224GO
225
226CREATE FUNCTION ufn_licz_rabat
227(
228)
229 RETURNS TABLE
230AS
231 RETURN SELECT klient, COUNT(*) AS 'liczba wypozyczen' FROM Wypozyczenia
232 GROUP BY klient
233GO
234
235CREATE PROCEDURE usp_licz_rabat
236 @klient BIGINT
237AS
238BEGIN
239 UPDATE Klienci
240 SET rabat = 0.05*(SELECT 'liczba wypozyczen' FROM dbo.ufn_licz_rabat() WHERE klient = @klient)
241 WHERE PESEL = @klient
242END
243GO
244
245CREATE PROCEDURE usp_wypozycz
246 @klient BIGINT,
247 @imie VARCHAR(14),
248 @nazwisko VARCHAR(24),
249 @m_wypozyczenia INT,
250 @m_zwrotu INT,
251 @data_wyp DATE,
252 @data_zwr DATE,
253 @samochod VARCHAR(7)
254AS
255 DECLARE @koszt DECIMAL(6, 2)
256BEGIN
257 IF (@klient NOT IN (SELECT PESEL FROM Klienci))
258 BEGIN
259 INSERT INTO Klienci(PESEL, imie, nazwisko) VALUES
260 (@klient, @imie, @nazwisko)
261 END
262 IF NOT EXISTS (SELECT * FROM Wypozyczenia WHERE samochod = @samochod AND ((@data_wyp BETWEEN data_wyp AND data_zwr) OR (@data_zwr BETWEEN data_wyp AND data_zwr)))
263 BEGIN
264 SET @koszt = (SELECT dbo.ufn_koszt(@data_wyp, @data_zwr, @samochod) )
265 INSERT INTO Wypozyczenia(klient, m_wypozyczenia, m_zwrotu, data_wyp, data_zwr, samochod, koszt) VALUES
266 (@klient, @m_wypozyczenia, @m_zwrotu, @data_wyp, @data_zwr, @samochod, @koszt)
267 END
268 ELSE
269 RAISERROR('Samochod jest zajety', 11, 1)
270END;
271GO
272
273CREATE PROCEDURE usp_rezerwuj
274 @klient BIGINT,
275 @imie VARCHAR(14),
276 @nazwisko VARCHAR(24),
277 @data_od DATE,
278 @data_do DATE,
279 @samochod VARCHAR(7)
280AS
281 DECLARE @kaucja DECIMAL(6, 2)
282BEGIN
283 IF (@klient NOT IN (SELECT PESEL FROM Klienci))
284 BEGIN
285 INSERT INTO Klienci(PESEL, imie, nazwisko) VALUES
286 (@klient, @imie, @nazwisko)
287 END
288 IF NOT EXISTS (SELECT * FROM Wypozyczenia WHERE samochod = @samochod AND ((@data_od BETWEEN data_wyp AND data_zwr) OR (@data_do BETWEEN data_wyp AND data_zwr)))
289 AND NOT EXISTS (SELECT * FROM Rezerwacje WHERE samochod = @samochod AND ((@data_od BETWEEN data_od AND data_do) OR (@data_do BETWEEN data_od AND data_do)))
290 BEGIN
291 SET @kaucja = (SELECT dbo.ufn_kaucja(@data_od, @data_do, @samochod))
292 INSERT INTO Rezerwacje(data_od, data_do, klient, samochod, kaucja) VALUES
293 (@data_od, @data_do, @klient, @samochod, @kaucja)
294 END
295 ELSE
296 RAISERROR('Samochod jest zajety', 11, 1)
297END;
298GO
299
300CREATE PROCEDURE usp_zwolnij_pracownika
301 @pesel BIGINT
302AS
303BEGIN
304 IF EXISTS (SELECT *
305 FROM Pracownicy
306 WHERE pesel = @pesel)
307 BEGIN
308 UPDATE Klienci
309 SET opiekun = NULL
310 WHERE opiekun = @pesel;
311
312 UPDATE Naprawy
313 SET mechanik = NULL
314 WHERE mechanik = @pesel;
315 DELETE FROM Mechanicy
316 WHERE pesel = @pesel;
317 DELETE FROM Administratorzy
318 WHERE pesel = @pesel;
319 DELETE FROM Pracownicy
320 WHERE pesel = @pesel;
321
322 END;
323 ELSE
324 RAISERROR('Nie ma takiego pracownika', 11, 1);
325END;
326GO
327CREATE PROCEDURE usp_odw_rezerwacji
328 @data_od DATE,
329 @data_do DATE,
330 @samochod VARCHAR(7),
331 @klient BIGINT
332
333AS
334BEGIN
335
336 IF EXISTS(SELECT * FROM Rezerwacje
337 WHERE data_od = @data_od
338 AND data_do = @data_do
339 AND samochod = @samochod
340 AND klient = @klient
341 AND czy_odwolana = 'Nie')
342 BEGIN
343 UPDATE Rezerwacje
344 SET czy_odwolana = 'Tak'
345 WHERE data_od = @data_od
346 AND data_do = @data_do
347 AND samochod = @samochod
348 AND klient = @klient;
349
350 END
351 ELSE
352 RAISERROR('Nie ma takiej rezerwacji', 11, 1)
353END;
354GO
355
356CREATE PROCEDURE usp_info
357 @samochod VARCHAR(7)
358
359AS
360BEGIN
361
362 SELECT TOP 3 W2.m_wypozyczenia AS 'Najczesciej wybierane miejsce wyp', COUNT(W2.m_wypozyczenia) AS 'Ile wypozyczen', AVG(czas_wyp) AS 'Sredni czas wypozyczenia'
363 FROM Wypozyczenia W2
364 WHERE W2.samochod = @samochod
365 GROUP BY W2.m_wypozyczenia
366 ORDER BY COUNT(W2.m_wypozyczenia) DESC
367
368END;
369GO
370
371CREATE TRIGGER tr_rabat
372ON Klienci
373AFTER INSERT, UPDATE
374AS
375 IF (SELECT inserted.rabat FROM inserted) > 0.4
376 UPDATE Klienci
377 SET rabat = 0.4 FROM Klienci
378 WHERE PESEL = (SELECT PESEL FROM inserted)
379GO
380
381CREATE TRIGGER tr_odw_rezerwacji
382ON Rezerwacje
383AFTER UPDATE
384AS
385 PRINT 'Uzytkownik ' + USER_NAME() + ' zaktualizowal wiersze';
386GO
387
388CREATE TRIGGER tr_wyp_naprawiany
389ON Wypozyczenia
390INSTEAD OF INSERT
391AS
392 IF (SELECT inserted.data_wyp FROM inserted) = (SELECT data_naprawy FROM Naprawy WHERE samochod = (SELECT samochod FROM inserted))
393 PRINT 'Ze wzgledu na naprawe samochod jest niedostepny';
394GO
395
396CREATE TRIGGER tr_odwolanie
397ON Rezerwacje
398INSTEAD OF UPDATE
399AS
400 IF (SELECT DATEDIFF(day, data_od, GETDATE()) FROM inserted) < 3
401 AND (SELECT czy_odwolana FROM inserted) = 'Tak'
402 PRINT 'Nie mozna odwolac rezerwacji, gdy zostalo mniej niz 3 dni'
403GO
404
405CREATE TRIGGER tr_wypozycz
406ON Wypozyczenia
407AFTER INSERT
408AS
409 PRINT 'Uzytkownik ' + USER_NAME() + ' dodal wypozyczenie'
410GO
411
412CREATE TRIGGER tr_zwolnienie
413ON Pracownicy
414INSTEAD OF DELETE
415AS
416 IF (SELECT DATEDIFF(month, data_zatr, GETDATE()) FROM inserted) < 1
417 PRINT 'Nie mozna zwolnic pracownika podczas okresu probnego'
418GO