· 7 years ago · Jan 30, 2019, 07:16 PM
1CREATE DATABASE MultiKino;
2USE MultiKino;
3
4DROP TABLE film
5DROP TABLE seans
6DROP TABLE kategoria
7DROP TABLE sala
8DROP TABLE autorzy
9DROP TABLE cennik
10DROP TABLE rezerwacje
11DROP TABLE produkcja
12
13
14CREATE TABLE film(
15 id_filmu INT PRIMARY KEY,
16 nazwa_filmu VARCHAR(20) UNIQUE NOT NULL,
17 kategoria_filmu INT FOREIGN KEY REFERENCES kategoria (id_kategorii) ON DELETE CASCADE ON UPDATE CASCADE
18)
19
20CREATE TABLE kategoria(
21 id_kategorii INT PRIMARY KEY,
22 nazwa_kategorii VARCHAR(20) UNIQUE NOT NULL
23)
24
25CREATE TABLE seans(
26 id_seansu INT PRIMARY KEY,
27 id_filmu INT FOREIGN KEY REFERENCES film(id_filmu) ON DELETE CASCADE ON UPDATE CASCADE,
28 sala_nr INT FOREIGN KEY REFERENCES sala(nr_sali) ON DELETE CASCADE ON UPDATE CASCADE,
29 data DATE NOT NULL,
30 czas_rozpoczenia TIME NOT NULL,
31 czas_zakonczenia TIME NOT NULL,
32)
33
34CREATE TABLE sala(
35 nr_sali INT PRIMARY KEY,
36 liczba_miejsc INT NOT NULL
37)
38
39CREATE TABLE autorzy(
40 rezyser VARCHAR(40) NOT NULL,
41 scenariusz VARCHAR(40) NOT NULL,
42 nazwa_filmu VARCHAR (20) UNIQUE FOREIGN KEY REFERENCES film(nazwa_filmu) ON DELETE CASCADE ON UPDATE CASCADE
43)
44
45CREATE TABLE cennik(
46 cena INT NOT NULL,
47 nazwa_filmu VARCHAR (20) UNIQUE FOREIGN KEY REFERENCES film(nazwa_filmu) ON DELETE CASCADE ON UPDATE CASCADE
48)
49
50CREATE TABLE produkcja(
51 nazwa_filmu VARCHAR (20) UNIQUE FOREIGN KEY REFERENCES film(nazwa_filmu) ON DELETE CASCADE ON UPDATE CASCADE,
52 nazwa_producenta VARCHAR(20)
53)
54
55
56CREATE TABLE rezerwacje(
57 id_rezerwacji INT PRIMARY KEY,
58 id_seansu INT FOREIGN KEY REFERENCES seans(id_seansu) ON DELETE CASCADE ON UPDATE CASCADE,
59 liczba_zarezerwowanych_miejsc INT
60)
61
62drop table rezerwacje;
63
64
65use MultiKino
66
67
68select * from sala
69
70delete from sala
71
72insert into film values (1,'planeta singli 2','1');
73insert into kategoria values (1,'romantyczny');
74insert into sala values (1,20);
75insert into seans values (1,1,1,'2019-01-30','14:00','16:00');
76
77insert into film values (2,'listy do m', 1);
78insert into seans values (2,1,1,'2019-01-30','17:00','18:00');
79
80insert into seans values (3,2,1,'2019-01-30','20:00','22:00');
81
82select * from seans;
83
84delete from seans
85select * from seans;
86
87-- filmy wyswietlone w danym dniu
88
89drop procedure dbo.filmyWdniu
90
91--1 procedura
92CREATE PROCEDURE dbo.filmyWdniu (@data DATE)
93AS
94 SELECT f.nazwa_filmu from seans s join film f on s.id_filmu=f.id_filmu
95 where s.data=@data
96 GROUP BY f.nazwa_filmu
97GO
98
99
100--2 procedura
101CREATE PROCEDURE dbo.filmyWsaliNR (@numer INT)
102AS
103 SELECT f.nazwa_filmu from seans s join film f on s.id_filmu=f.id_filmu
104 where s.sala_nr=@numer
105 GROUP BY f.nazwa_filmu
106GO
107
108drop procedure usunRezerwacje
109--3 procedura
110CREATE PROCEDURE dbo.usunRezerwacje(@numer INT)
111AS
112 IF EXISTS( select * from rezerwacje where id_rezerwacji=@numer )
113 delete from rezerwacje where id_rezerwacji=@numer
114GO
115
116
117-- 4 procedura
118CREATE PROCEDURE dbo.AutorzyFilmowZkategorii(@nazwa VARCHAR(20))
119AS
120 IF EXISTS (SELECT * FROM kategoria where nazwa_kategorii = @nazwa )
121 SELECT k.nazwa_kategorii, a.rezyser, a.scenariusz from kategoria k join film f on k.id_kategorii = f.kategoria_filmu join autorzy a on a.nazwa_filmu = a.nazwa_filmu
122 ORDER BY nazwa_kategorii
123GO
124
125--5 procedura
126CREATE PROCEDURE dbo.JakieFilmyOgladanowDanymDniu (@data DATE)
127AS
128 IF EXISTS (SELECT * FROM rezerwacje r join seans s on r.id_seansu = s.id_seansu where s.data = @data)
129 SELECT f.nazwa_filmu FROM rezerwacje r join seans s on r.id_seansu = s.id_seansu join film f on f.id_filmu = s.id_filmu where s.data = @data
130 GROUP BY nazwa_filmu
131 ORDER BY nazwa_filmu
132GO
133
134--5 zrobie se to jako funkcje
135CREATE PROCEDURE dbo.IleBiletowZostaloSprzedanych(@nazwaFilmu VARCHAR(20))
136AS
137 SELECT SUM(liczba_zarezerwowanych_miejsc) FROM rezerwacje r join seans s on r.id_seansu = s.id_seansu join film f on s.id_filmu = f.id_filmu
138 where f.nazwa_filmu = @nazwaFilmu
139GO
140
141--6 zwroc ile biletow zostalo zakupionych miedzy danymi dniami
142
143
144EXEC dbo.filmyWdniu @data = '2019-01-30';
145
146DROP TRIGGER Sprawdz_miejsca
147--1 trigger
148CREATE TRIGGER Sprawdz_miejsca ON rezerwacje --sprawdza czy sa jeszce miejsca na sali
149INSTEAD OF INSERT
150AS
151 IF NOT EXISTS (select * from seans s where s.id_seansu = (select id_seansu from inserted))
152 BEGIN
153 ROLLBACK
154 RAISERROR('NIE ISTNIEJE TAKI SEANS!',16,1)
155 RETURN;
156 END
157
158 DECLARE @max_osob INT
159 DECLARE @dane INT
160 DECLARE @aktualnie INT = 0
161 SET @aktualnie = (SELECT SUM(liczba_zarezerwowanych_miejsc) from rezerwacje R
162 where (SELECT id_seansu from inserted) = R.id_seansu )
163 SET @max_osob = (SELECT A.liczba_miejsc from sala A
164 join seans E on A.nr_sali = E.sala_nr
165 where (SELECT id_seansu from inserted) = E.id_seansu)
166IF ( (@aktualnie + (SELECT liczba_zarezerwowanych_miejsc from inserted)) > @max_osob )
167BEGIN
168 ROLLBACK
169 RAISERROR('Brak miejsc!',16,1)
170END
171ELSE
172BEGIN
173INSERT INTO rezerwacje select id_rezerwacji, id_seansu, liczba_zarezerwowanych_miejsc from inserted
174END
175GO
176
177
178drop trigger sprawdz_seans
179
180--2 trigger
181CREATE TRIGGER Sprawdz_seans ON seans --sprawdza czy mozna dopisac seans
182INSTEAD OF INSERT
183AS
184IF ( (SELECT czas_rozpoczenia from inserted) > (SELECT czas_zakonczenia from inserted ))
185BEGIN
186 ROLLBACK
187 RAISERROR('Niepoprawnie wpisany czas!',16,1)
188END
189ELSE
190BEGIN
191 IF EXISTS (SELECT sala_nr from seans S where ((S.sala_nr) = (SELECT sala_nr from inserted) AND (S.data = (SELECT data from inserted))))
192 BEGIN
193 DECLARE @blad BIT = 0
194 Declare @sala INT = NULL
195 SET @sala = (SELECT sala_nr from seans S where ((S.sala_nr) = (SELECT sala_nr from inserted)) GROUP BY sala_nr)
196 DECLARE kurs CURSOR FOR
197 SELECT s.czas_rozpoczenia, s.czas_zakonczenia from seans AS s where @sala = s.sala_nr;
198 DECLARE @rozpoczecie TIME, @zakonczenie TIME
199 OPEN kurs
200 FETCH NEXT FROM kurs INTO @rozpoczecie, @zakonczenie
201 WHILE @@FETCH_STATUS = 0
202 BEGIN
203 IF EXISTS (SELECT * from inserted I where sala_nr = @Sala and (I.czas_rozpoczenia BETWEEN @rozpoczecie AND @zakonczenie))
204 BEGIN
205 ROLLBACK
206 RAISERROR('Sala jest zajeta!',16,1)
207 SET @blad = 1
208 BREAK
209 END
210 IF EXISTS (SELECT * from inserted I where sala_nr = @Sala and (I.czas_zakonczenia BETWEEN @rozpoczecie AND @zakonczenie))
211 BEGIN
212 ROLLBACK
213 RAISERROR('Sala jest zajeta!',16,1)
214 SET @blad = 1
215 BREAK
216 END
217 FETCH NEXT FROM kurs INTO @rozpoczecie, @zakonczenie
218 END
219 CLOSE kurs
220 DEALLOCATE kurs
221 IF (@blad = 0 )INSERT INTO seans select id_seansu,id_filmu,sala_nr,data,czas_rozpoczenia,czas_zakonczenia from inserted
222 END
223 ELSE
224 INSERT INTO seans select id_seansu,id_filmu,sala_nr,data,czas_rozpoczenia,czas_zakonczenia from inserted
225 END
226GO
227
228
229
230insert into rezerwacje values (4,1,1)
231
232drop trigger Sprawdz_miejsca;
233select * from rezerwacje;
234
235insert into rezerwacje values (3,5,1);
236
237delete from seans where id_seansu=4
238select * from seans
239insert into seans values (4,2,1,'2019-01-30','18:01','19:59');
240
241
242select * from rezerwacje
243
244EXEC usunRezerwacje @numer=3
245GO
246
247EXEC JakieFilmyOgladanowDanymDniu @data='2019-01-30'