· 9 years ago · Jan 15, 2017, 04:04 PM
1/*
2use master
3go
4
5set language polski
6go
7
8if db_id('Hotel') is not null
9drop database Hotel
10go
11
12if db_id('Hotel') is null
13create database Hotel
14go
15*/
16
17use Hotel
18go
19
20if object_id('dbo.Rezerwacja','U') is not null
21drop table dbo.Rezerwacja
22
23if object_id('dbo.Pracownik','U') is not null
24drop table dbo.Pracownik
25
26if object_id('dbo.Pokoj','U') is not null
27drop table dbo.Pokoj
28
29if object_id('dbo.Pies','U') is not null
30drop table dbo.Pies
31
32if object_id('dbo.Klient','U') is not null
33drop table dbo.Klient
34
35if object_id('dbo.RodzajPokoju','U') is not null
36drop table dbo.RodzajPokoju
37
38if object_id('dbo.Placowka','U') is not null
39drop table dbo.Placowka
40GO
41
42create table Klient(
43PESEL char(11) primary key,
44imie varchar(40) not null,
45nazwisko varchar(40) not null,
46adres varchar(300),
47telefon varchar(11)
48);
49
50create table Placowka(
51idPlacowki int primary key identity(1,1),
52nazwa varchar(50) not null,
53adres varchar(300) not null,
54telefon varchar(11)
55);
56
57create table Pracownik(
58idPracownika int primary key identity (1,1),
59imie varchar(40) not null,
60nazwisko varchar(40) not null,
61adres varchar(300),
62telefon varchar(11),
63stanowisko varchar(15) not null,
64czyAktywny bit default 'true',
65idPlacowki int references Placowka(idPlacowki)
66);
67
68create table Pies(
69idPsa int primary key identity(1,1),
70imie varchar(40) not null,
71rok_urodzenia int,
72rasa varchar(100) not null,
73wielkosc varchar(10),
74PESELWlasciciela char(11) references Klient(PESEL),
75constraint data_urodzenia check (rok_urodzenia > 1990)
76);
77
78create table RodzajPokoju(
79idRodzaju tinyint primary key identity(1,1),
80typ varchar(30) not null,
81cena smallint not null
82);
83
84create table Pokoj(
85idPokoju int primary key identity(1,1),
86opis text,
87idPlacowki int references Placowka(idPlacowki),
88idRodzaju tinyint references RodzajPokoju(idRodzaju),
89);
90
91create table Rezerwacja(
92idRezerwacji int primary key identity(1,1),
93dataRezerwacji datetime,
94dataZameldowania date,
95dataWymeldowania date,
96czyAktywna bit default 'true',
97PESEL char(11) references Klient(PESEL),
98idPsa int references Pies(idPsa),
99idPokoju int references Pokoj(idPokoju),
100idPracownika int references Pracownik(idPracownika),
101constraint checkdate1 check (dataWymeldowania > dataZameldowania),
102constraint checkdate2 check (dataZameldowania > dataRezerwacji),
103constraint checkdate3 check (dataWymeldowania > dataRezerwacji),
104);
105
106GO
107
108CREATE TRIGGER czyAktywny ON Rezerwacja
109INSTEAD OF INSERT
110AS
111
112 IF NOT EXISTS(select distinct p.idPsa from inserted i join Pies p on i.idPsa = p.idPsa and i.PESEL=p.PESELWlasciciela)
113 BEGIN
114 ;THROW 51000, 'Taka osoba nie posiada podanego psa!',1
115 END
116 ELSE IF (select dataWymeldowania from inserted) < GETDATE()
117 INSERT INTO Rezerwacja values ((select dataRezerwacji from inserted),(select dataZameldowania from inserted),(select dataWymeldowania from inserted), 'false' ,(select PESEL from inserted), (select idPsa from inserted), (select idPokoju from inserted), (select idPracownika from inserted))
118 ELSE IF (select dataWymeldowania from inserted) is NULL or (select dataWymeldowania from inserted) > GETDATE()
119 INSERT INTO Rezerwacja values ((select dataRezerwacji from inserted),(select dataZameldowania from inserted),(select dataWymeldowania from inserted), 'true' ,(select PESEL from inserted), (select idPsa from inserted), (select idPokoju from inserted), (select idPracownika from inserted))
120GO
121
122create trigger pobyt on rezerwacja
123after insert
124as
125 declare @idPokoju int,
126 @dataRezerwacji datetime,
127 @dataZameldowania date,
128 @dataWymeldowania date,
129 @zameldowany date,
130 @wymeldowany date
131 set @idPokoju = (select idPokoju from inserted)
132 set @dataRezerwacji = (select dataRezerwacji from inserted)
133 set @dataZameldowania = (select dataZameldowania from inserted)
134 set @dataWymeldowania = (select dataWymeldowania from inserted)
135
136 if exists (select * from Rezerwacja where idPokoju = @idPokoju and czyAktywna = 'true' and dataRezerwacji != @dataRezerwacji)
137 begin
138 select @zameldowany = dataZameldowania, @wymeldowany = dataWymeldowania from Rezerwacja where idPokoju = @idPokoju and czyAktywna = 'true' and dataRezerwacji != @dataRezerwacji
139 if not ((@dataZameldowania < @zameldowany and @dataWymeldowania <= @zameldowany) OR (@dataZameldowania >=@wymeldowany))
140 begin
141 print 'Ten pokój jest zajęty w tym terminie!'
142 rollback
143 end
144 end
145go
146
147insert into Klient(PESEL,imie,nazwisko,adres,telefon) values ('95264785129','Jan','Nowak','Jeziorańskiego 16','541245784')
148insert into Klient(PESEL,imie,nazwisko,adres,telefon) values ('94251368452','Kamila','Kuznowicz','Aleje Niepodległości 34c','514751423')
149insert into Klient(PESEL,imie,nazwisko,adres,telefon) values ('94523654782','Albert','Paderewski','Mickiewicza 14','625412547')
150insert into Klient(PESEL,imie,nazwisko,adres,telefon) values ('85654125412','Marcjanna','Borowiecka','Ul. Kaszatanowa 77','654124785')
151insert into Klient(PESEL,imie,nazwisko,adres,telefon) values ('78121245789','Antonina','Wójcik','Wielkopolańska 84','512645877')
152
153insert into Placowka(nazwa,adres,telefon) values ('Uszko','SÅ‚owackiego 15','222011071')
154insert into Placowka(nazwa,adres,telefon) values ('Åapka','Grunwaldzka 23c','226457784')
155insert into Placowka(nazwa,adres,telefon) values ('Nosek','Sokoła 16','221045587')
156
157insert into Pracownik(imie,nazwisko,telefon,stanowisko,czyAktywny,idPlacowki) values ('Alicja','Jakubczyk','532645785','Kierownik',1,1)
158insert into Pracownik(imie,nazwisko,telefon,stanowisko,czyAktywny,idPlacowki) values ('Jakub','Kowalewski','512458745','Opiekun',0,2)
159insert into Pracownik(imie,nazwisko,adres,telefon,stanowisko,czyAktywny,idPlacowki) values ('Adam','Bartczak','Kraszewskiego 145f','541258745','Opiekun',1,2)
160insert into Pracownik(imie,nazwisko,telefon,stanowisko,czyAktywny,idPlacowki) values ('Marta','Matuszczyk','523658457','Opiekun',1,3)
161
162insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Brzęczyk','1999','Cocker Spaniel','średni','95264785129')
163insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Ludka','2005','Pekińczyk','mały','94251368452')
164insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Bińczyk','2010','Dog niemiecki','duży','94523654782')
165insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Berto','2008','Dog niemiecki','duży','94523654782')
166insert into Pies(imie,rok_urodzenia,rasa,wielkosc,PESELWlasciciela) values ('Makak','2012','Pinczer','mały','78121245789')
167
168insert into RodzajPokoju(typ,cena) values ('standard',100)
169insert into RodzajPokoju(typ,cena) values ('apartament',300)
170
171insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Na pierwszym piętrze, z dużym ogrodem do biegania.',2,2)
172insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Na pierwszym piętrze, z dużym ogrodem do biegania.',3,1)
173insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Dla małych psów, duża ilość zabawek.',1,1)
174insert into Pokoj(opis,idPlacowki,idRodzaju) values ('Standard, wygodne legowisko.',2,1)
175
176insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2010-10-20T10:00:00','2010-10-21','2010-10-24','95264785129',1,1,1)
177insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2011-11-21T09:00:00','2011-11-23','2011-11-26','94251368452',2,1,3)
178insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2013-03-11T11:00:00','2013-04-14','2013-04-26','94523654782',3,2,2)
179insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2015-05-11T12:42:00','2015-05-14','2015-05-26','94523654782',4,3,4)
180insert into Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) values ('2016-12-01T10:42:00','2016-12-06',NULL,'78121245789',5,4,1)
181
182GO
183
184use Hotel
185go
186
187CREATE PROCEDURE dodajKlienta
188 @PESEL char(11),
189 @imie varchar(40),
190 @nazwisko varchar(40),
191 @adres varchar(300),
192 @telefon varchar(11)
193AS
194 IF EXISTS (SELECT * FROM Klient WHERE PESEL = @PESEL)
195 RAISERROR('Taki klient jest już w bazie!', 11, 1)
196 ELSE
197 INSERT INTO Klient VALUES(@PESEL,@imie,@nazwisko,@adres,@telefon)
198GO
199
200CREATE PROCEDURE dodajPsa
201 @imie varchar(40),
202 @rok_urodzenia varchar(4),
203 @rasa varchar(100),
204 @wielkosc varchar(10),
205 @PESELWlasciciela char(11)
206AS
207 IF NOT EXISTS (SELECT * FROM Klient WHERE PESEL = @PESELWlasciciela)
208 RAISERROR('Nie ma takiego klienta w bazie!', 11, 1)
209 ELSE IF EXISTS (SELECT * FROM Pies WHERE imie = @imie AND PESELWlasciciela = @PESELWlasciciela)
210 RAISERROR('Taki pies jest już w bazie!', 11, 1)
211 ELSE
212 INSERT INTO Pies VALUES(@imie,@rok_urodzenia,@rasa,@wielkosc,@PESELWlasciciela)
213GO
214
215CREATE PROCEDURE dodajPracownika
216 @imie varchar(40),
217 @nazwisko varchar(40),
218 @adres varchar(300),
219 @telefon varchar(11),
220 @stanowisko varchar(15),
221 @czyAktywny bit = 'true',
222 @idPlacowki int
223AS
224 IF NOT EXISTS (SELECT * FROM Placowka WHERE idPlacowki = @idPlacowki)
225 RAISERROR('Nie ma takiej placówki w bazie!', 11, 1)
226 ELSE IF EXISTS (SELECT * FROM Pracownik WHERE imie = @imie AND nazwisko = @nazwisko AND adres = @adres)
227 RAISERROR('Taki pracownik jest już w bazie!', 11, 1)
228 ELSE
229 INSERT INTO Pracownik VALUES(@imie,@nazwisko,@adres,@telefon,@stanowisko,@czyAktywny,@idPlacowki)
230GO
231
232CREATE PROCEDURE dodajPlacowke
233 @nazwa varchar(50),
234 @adres varchar(300),
235 @telefon varchar(11)
236AS
237 IF EXISTS (SELECT * FROM Placowka WHERE nazwa = @nazwa)
238 RAISERROR('Taka placówka jest już w bazie!', 11, 1)
239 ELSE
240 INSERT INTO Placowka VALUES(@nazwa,@adres,@telefon)
241GO
242
243CREATE PROCEDURE dodajPokoj
244 @opis text,
245 @idPlacowki int,
246 @idRodzaju tinyint
247AS
248 IF NOT EXISTS (SELECT * FROM Placowka WHERE idPlacowki = @idPlacowki)
249 RAISERROR('Nie ma takiej placówki w bazie!', 11, 1)
250 ELSE IF NOT EXISTS (SELECT * FROM RodzajPokoju WHERE idRodzaju = @idRodzaju)
251 RAISERROR('Nie ma takiego rodzaju w bazie!', 11, 1)
252 ELSE
253 INSERT INTO Pokoj VALUES(@opis,@idPlacowki,@idRodzaju)
254GO
255
256CREATE PROCEDURE utworzRezerwacje
257 @dataRezerwacji datetime,
258 @dataZameldowania date,
259 @dataWymeldowania date,
260 @PESEL char(11),
261 @idPsa int,
262 @idPokoju int,
263 @idPracownika int
264AS
265 IF NOT EXISTS (SELECT * FROM Klient WHERE PESEL = @PESEL)
266 RAISERROR('Nie ma takiego klienta w bazie!', 11, 1)
267 ELSE IF NOT EXISTS (SELECT * FROM Pies WHERE idPsa = @idPsa)
268 RAISERROR('Nie ma takiego psa w bazie!', 11, 1)
269 ELSE IF NOT EXISTS (SELECT * FROM Pokoj WHERE idPokoju = @idPokoju)
270 RAISERROR('Nie ma takiego pokoju w bazie!', 11, 1)
271 ELSE IF NOT EXISTS (SELECT * FROM Pracownik WHERE idPracownika = @idPracownika)
272 RAISERROR('Nie ma takiego pracownika w bazie!', 11, 1)
273 ELSE IF EXISTS (SELECT * FROM Rezerwacja WHERE dataRezerwacji = @dataRezerwacji AND idPokoju = @idPokoju)
274 RAISERROR('Taka rezerwacja już istnieje!', 11, 1)
275 ELSE
276 INSERT INTO Rezerwacja(dataRezerwacji,dataZameldowania,dataWymeldowania,PESEL,idPsa,idPokoju,idPracownika) VALUES(@dataRezerwacji,@dataZameldowania,@dataWymeldowania,@PESEL,@idPsa,@idPokoju,@idPracownika)
277GO
278
279drop procedure utworzRezerwacje
280
281utworzRezerwacje '2016-05-11T13:15:02','2017-06-23','2017-06-24','94523654782',4,3,4
282
283
284select * from Klient
285select * from Placowka
286select * from Pracownik
287select * from Pies
288select * from RodzajPokoju
289select * from Pokoj
290select * from Rezerwacja