Baza danych Hotel PDF

Preview

Summary

Baza danych Hotel...

Description

Sprawozdanie Baza danych: „HOTEL”

1.Opis hotelu Baza danych przystosowana jest dla sieci hoteli. Każdy hotel posiada pokoje. Każdy pokój posiada swój numer i swój stan: czy jest zajęty czy wolny. Stan pokoju zmieniany jest na bieżąco w recepcji, w momencie, kiedy osoba melduje się w hotelu. Każdy pokój ma określony typ i wyposażenie. W typie określamy ilość łóżek w pokoju, cenę i typ pokoju. W wyposażeniu podajemy czy pokój ma telewizor, balkon, łazienkę, wannę oraz widok na morze. Pokój może mieć jeden typ i jeden rodzaj wyposażenia. Dany rodzaj wyposażenia i typ może być przyporządkowany do wielu pokoi. Podczas rezerwacji pokoju przypisujemy danej „rezerwacji” osobę i wybrany pokój. Zaznaczamy również daty od kiedy i do kiedy dany pokój nie będzie dostępny. Konsumpcja oznacza co dana osoba zamawiała, kupowała – czyli za co musi zaplacić. Uwzględniamy tutaj rodzaj i cenę danej usługi / rzeczy. W wierszu faktury uwzględniamy ilość zakupionej rzeczy bądź ile razy korzystaliśmy z usługi. Wszystko przypisujemy do jednej faktury i zapisujemy ilość kopii faktur. Od każdego klienta pobieramy dane: imię, nazwisko, adres i telefon. Klient może dokonać wiele rezerwacji, co za tym idzie może mieć wystawionych wiele faktur. Hotel zatrudnia personel. Zapisujemy dane każdego z pracownika: imie, nazwisko, PESEL, nr konta. Istotny jest tez stan, który wskazuje czy dany pracownik w danej chwili jest zajęty czy wolny. Na podstawie tego możemy odszukać osobę, która posprząta przed kolejną rezerwacje.

2. Model konceptualny.

TypPokoju

Hotel Nazwa Characters (10) Characters (20) Ulica Characters (5) NrUlicy Characters (6) Kod Characters (15) Miasto Integer REGON

idTypPokoju Integer

iloscLozek Integer Cena Float typ Characters (10)

Pokoj

posiada

NrPokoju Integer

Stan Characters (1)

jest

NrPokoju

IdTypPokoju

Identifier_1 wyposazony

idWyposazenia Integer Boolean TV Boolean Balkon Boolean Łazienka Boolean Wanna Boolean WidokNaMorze

zatrudnia zostanie Personel

IdPersonelu Integer Characters (10) Imie Characters (20) Nazwisko Characters (20) Ulica Characters (5) NrDomu Characters (5) NrMieszkania Characters (6) Kod Characters (15) Miasto Number (27,0) NrKonta Characters (11) PESEL Characters (1) Stan

Wyposazenie

IdWyposazenia Rezerwacja IdRezerwacji Integer DataOd Date DataDo Date DataRezerwacji Date

laczy Faktura

IdRezerwacji

IdFaktury Integer ilosc Integer

IdPersonelu

ma

IdFaktury

dokonal zawiera

IdWierszaFaktury

dostanie Klient idKlienta Integer

Imie Characters (10) Nazwisko Characters (20) Ulica Characters (20) NrDomu Characters (5) NrMieszkania Characters (5) Kod Characters (6) Miasto Characters (15) Tel Characters (15) IdKlienta

WierszFaktury IdWierszaFaktury Integer ilosc Integer

przechodzi Konsumpcja

idKonsumpcji Integer Characters (15) Rodzaj Float Cena IdKonsumpcji

3. Model fizyczny TypPokoju

Hotel Nazwa Ulica NrUlicy Kod Miasto REGON

char(10) char(20) char(5) char(6) _POSIADA_HOTEL char(15) integer

Pokoj NrPokoju idTypPokoju Nazwa Stan

integer integer char(10) char(1)

idTypPokoju iloscLozek FK_POKOJ_JEST_TY Cena typ

integer integer float char(10)

FK_WYPOSAZO_WYPOSAZON_POKOJ FK_PERSONEL_ZATRUDNIA_HOTEL

Wyposazenie

wyposazony FK_REZERWAC_ZOSTANIE_POK

NrPokoju integer idWyposazenia integer

SAZO_WYPOSAZON_W

Personel IdPersonelu Nazwa Imie Nazwisko Ulica NrDomu NrMieszkania Kod Miasto NrKonta PESEL Stan

integer

char(10)

char(10) char(20) char(20) char(5) char(5) char(6) char(15) numeric(27,0) char(11) char(1)

Rezerwacja IdRezerwacji NrPokoju idKlienta DataOd DataDo DataRezerwacji

FK_REZERWAC_DOKONAL_KLIENT

integer char char char char char

integer ACZY_REZERWAC integer integer date int Faktura date WierszFaktury IdFaktury integer idKlienta integer FK_WIERSZFA_MA_FAKTURA IdWierszaFaktury integer integer ilosc IdRezerwacji integer IdFaktury integer FK_KONSUMPC_ZAWIERA_REZERWAC ilosc integer

FK_KONSUMPC_PRZECHODZ_WIERSZFA

Klient idKlienta Imie Nazwisko Ulica NrDomu NrMieszkania Kod Miasto Tel

idWyposazenia TV Balkon Łazienka Wanna WidokNaMorze

integer A_DOSTANIE_KLIENT char(10) char(20) char(20) char(5) char(5) char(6) char(15) char(15)

Konsumpcja idKonsumpcji IdWierszaFaktury IdRezerwacji Rodzaj Cena

integer integer integer char(15) float

4. Co z postaciami normalnymi?

I postać normalna – tabela spełnia założenia pierwszej postaci normalnej, jeżeli posiada tylko pola zawierające wartości skalarne. II postać normalna – tabela odpowiada drugiej postaci normalnej, jeśli spełnia dwa założenia: - Tabela jest I POSTACI NORMALNEJ - Kolumny nie wchodzące w skład kluczy są zależne od całego klucza ( nie od jego części) III postać normalna – trzecia postać normalna wymaga, aby wszystkie kolumny nie należące do klucza, zależały funkcyjnie od klucza kandydującego. Moja baza danych jest normalizowana do trzeciej postaci normalnej. Dlaczego zazwyczaj właśnie takiej normalizacji dokonujemy? Normalizacja taka zapewnia integralność danych, zwiększa precyzyjność oraz wydajność bazy oraz ułatwia aktualizację, wprowadzanie i usuwanie danych.

5. Widoki 1. PersonelOdpowiedzialny Widok ten pokazuje nam na kiedy dany pokój ma być przygotowany, i który z pracowników w tej chwili jest wolny i może przygotować odpowiedni pokój. KOD SQL:

ALTER VIEW "DBA"."PersonelOdpowiedzialny" AS SELECt Personel.Nazwisko, Personel.Imie, Pokoj.NrPokoju, Rezerwacja.DataOd as OdKiedy From Personel, Pokoj, Hotel, Rezerwacja where Personel.Nazwa=Hotel.Nazwa and Hotel.Nazwa=Pokoj.Nazwa and Pokoj.NrPokoju=Rezerwacja.NrPokoju and Personel.stan='w' order by Rezerwacja.DataOd

WIDOK:

2. WolnePokoje Widok pokazuje nam które pokoje są wolne, a dokładnie ich numer, typ, ilość łóżek oraz wyposażenie ( TV, balkon, łazienka, wanna, widok na morze ).

KOD SQL: ALTER VIEW "DBA"."WolnePokoje" AS select Pokoj.NrPokoju, TypPokoju.typ, TypPokoju.iloscLozek, TypPokoju.Cena, Wyposazenie.TV, Wyposazenie.Balkon, Wyposazenie.Łazienka, Wyposazenie.Wanna, Wyposazenie.WidokNaMorze from Pokoj, TypPokoju, wyposazony, Wyposazenie where Pokoj.idTypPokoju=TypPokoju.idTypPokoju and Pokoj.NrPokoju=wyposazony.NrPokoju and wyposazony.idWyposazenia=Wyposazenie.idWyposazenia and Pokoj.Stan='wolny' group by TypPokoju.typ, TypPokoju.iloscLozek, TypPokoju.Cena, Wyposazenie.TV, Wyposazenie.Balkon, Wyposazenie.Łazienka, Wyposazenie.Wanna, Wyposazenie.WidokNaMorze, Pokoj.NrPokoju order by TypPokoju.Cena

WIDOK:

3. Zapłata Widok ten pokazuje jakie pozycje ( zakupy, usługi ) dokonał klient oraz pokazuje obliczoną sumę do zapłaty za dana pozycje ( zakupy, usługę ). KOD SQL:

ALTER VIEW "DBA"."Zaplata" AS SELECT Faktura.IdFaktury, Rezerwacja.IdRezerwacji, Klient.Nazwisko, Klient.Imie, Konsumpcja.Rodzaj, sum(Konsumpcja.Cena*WierszFaktury.ilosc) as Suma From Faktura, WierszFaktury, Konsumpcja, Rezerwacja, Klient where Faktura.idKlienta=Klient.idKlienta and Faktura.IdFaktury=WierszFaktury.IdFaktury and WierszFaktury.IdWierszaFaktury=Konsumpcja.IdWierszaFaktury and Rezerwacja.IdRezerwacji=Konsumpcja.IdRezerwacji and Rezerwacja.idKlienta=Klient.idKlienta group by Faktura.IdFaktury, Rezerwacja.IdRezerwacji, Klient.Nazwisko, Klient.Imie, Konsumpcja.Rodzaj, WierszFaktury.ilosc, Konsumpcja.Cena order by Faktura.IdFaktury

WIDOK:

6. PROCEDURY 1. ZnajdźPokoj – procedura z kursorem Procedura ta wyszukuje nam odpowiedniego pokoju przy zadanych kryteriach ( parametry: telewizor oznacza czy pokój ma posiadać TV, balkon oznacza czy pokój ma posiadać na posiadać Balkon, L oznacza czy pokój ma posiadać łazienkę, w oznacza czy pokój ma posiadać wannę oraz t oznacza typ pokoju: pokój bądź apartament ). KOD SQL: ALTER PROCEDURE "DBA"."ZnajdzPokoj"( in telewizor char(1), in balkon char(1), in L char(1), IN w char(1), in morze char(1), in t char(10)) BEGIN declare nr integer; declare pok_kursor cursor for select NrPokoju from WolnePokoje; open pok_kursor; petla2: loop fetch next pok_kursor into nr; if sqlcode 0 then leave petla2; end if; select NrPokoju from WolnePokoje where TV=telewizor and typ=t and Balkon=balkon and Łazienka=L and Wanna=w and WidokNaMorze=morze; end loop petla2; close pok_kursor; end WIDOK:

2. Zarezerwuj – procedura z kursorem Procedura ta dokonuje rezerwacji. Wprowadza dane do tabelki, oblicza nam kolejny nr IdRezerwacji oraz wprowadza obecną datę do kolumny DataRezerwcji. Parametry jakie wprowadzamy: Nr – nr pokoju, klient – id klienta, d Data od kiedy ma być rezerwacja oraz dd data do kiedy ma być rezerwacja. Procedura zwraca Id ostatniej rezerwacji. KOD SQL: ALTER PROCEDURE "DBA"."Zarezerwuj"( in Nr integer, in klient integer, in d date, in dd date ) BEGIN DECLARE idRezerwacji integer; declare numer integer; declare rez_kursor cursor for select IdRezerwacji from Rezerwacja; open rez_kursor; fetch next rez_kursor into numer; select max(IdRezerwacji) from Rezerwacja; set numer=(select max(IdRezerwacji) from Rezerwacja); insert Rezerwacja values (numer+1, Nr, klient, D, DD, today(*)); fetch next rez_kursor into numer; close rez_kursor; END

WIDOK:

3. PokazStanPokoi Pokazuje nam, które pokoje są wolne lub które pokoje są zajęte ( w zależności od wprowadzonego parametru ‘w’ – wolne, ‘z’ – zajęte ). KOD SQL: ALTER PROCEDURE "DBA"."PokazStanPokoji"( IN s char(1) ) BEGIN IF s = 'w' then select NrPokoju, Stan from Pokoj where Stan='wolny' else if s = 'z' then select NrPokoju, Stan from Pokoj where Stan='zajety' end if end if END

WIDOK:

Lub

4. dodajKlienta

Procedura, która dodaje nowego klienta do tabelki. Parametry ( id – id klienta, I – imie, Naz –nazwisko, Ul – ulica, NrD – nr domu, NrM – nr mieszkania, K – kod pocztowy, M – miasto, T – telefon ). KOD SQL: ALTER PROCEDURE "DBA"."dodajKlienta"( IN id integer, in I Char(10), in Naz char(20), in Ul char(20), in NrD char(5), in NrM char(5), in K char(6), in M char(15), in T char(15)) BEGIN insert into Klient ( idKlienta, Imie, Nazwisko, Ulica, NrDomu, NrMieszkania, Kod, Miasto, Tel) values ( id, I, Naz, Ul, NrD, NrM, K, M, T ) END WIDOK:

7. Funkcje 1. SumaFaktury – funkcja z kursorem Oblicza sumę faktury zadanej jako parametr ( numer – id faktury ) KOD SQL: ALTER FUNCTION "DBA"."SumaFaktury"( numer integer ) RETURNS double DETERMINISTIC BEGIN DECLARE SUMA double; declare tmp double; declare kursor dynamic scroll cursor for select Suma from Zaplata where IdFaktury=numer; set SUMA=0; open kursor; petla: loop fetch next kursor into tmp; if sqlcode 0 then leave petla; end if; set SUMA=SUMA + tmp; end loop petla; close kursor; RETURN SUMA; END WIDOK:

2. Zniżka – funkcja z kursorem Funkcja oblicza sumę zadanej faktury ze zniżka. Parametry ( numer – id faktury, wartZnizki – jaki procent ma zapłacić klient ). KOD SQL: ALTER FUNCTION "DBA"."znizka"( numer integer, wartZnizki double ) RETURNS double DETERMINISTIC BEGIN DECLARE SUMA double; declare tmp double; declare kursor dynamic scroll cursor for select Suma from Zaplata where IdFaktury=numer; set SUMA=0; open kursor; petla: loop fetch next kursor into tmp; if sqlcode 0 then leave petla; end if; set SUMA=SUMA + tmp; end loop petla; close kursor; set SUMA=SUMA*wartZnizki; RETURN SUMA; END WIDOK:

3. IleDniDoRezerwacji Funkcja oblicza ile dni zostało do wprowadzonej daty. KOD SQL:

ALTER FUNCTION "DBA"."IleDniDoRezerwacji"( data_rezerwacji date ) RETURNS integer DETERMINISTIC BEGIN DECLARE dni integer; set dni=data_rezerwacji - today(*); RETURN dni; END

WIDOK:

8. Triggery 1. Data-Rezerwacja Trigger sprawdza czy data rezerwacji nie jest wcześniejsza niż dzisiejsza data, gdy data jest błędna wypisuje komentarz „ Zła_Data”. Trigger ten sprawdza również czy data do ktorej ma być rezerwacja nie jest wcześniejsza niż data od której ma być rezerwacja, gdy data jest błędna wypisuje komentarz „Data_Do_Nie_Moze_Byc_wczesniejsza_niz_Data_Od ”. KOD SQL: ALTER TRIGGER "Data" BEFORE INSERT, Update ON Rezerwacja REFERENCING NEW AS Rezerwacja FOR EACH ROW BEGIN DECLARE skontroluj_datę EXCEPTION FOR SQLSTATE '99999'; IF Rezerwacja.DataOd < today(*) THEN SIGNAL Zła_Data; end if; if Rezerwacja.DataOd > Rezerwacja.DataDo THEN SIGNAL Data_Do_Nie_Moze_Byc_wczesniejsza_niz_Data_Od; END IF; END WIDOK:

2. personel – Personel Trigger sprawdza czy Nr Konta podany przez personel ma 26 cyfr, jeśli jest za krotki otrzymujemy komentarz „za_krotki_nr_konta”, jeśli jest za długi otrzymujemy „za_dlugi_nr_konta”. Trigger sprawdza również czy nr PESEL ma 11 cyfr. Dostajemy odpowiednie informacje „za_krotki_PESEL” oraz “za_dlugi_PESEL”. KOD SQL: ALTER TRIGGER "personel" BEFORE INSERT, Update ON Personel REFERENCING NEW AS Personel FOR EACH ROW BEGIN DECLARE skontroluj EXCEPTION FOR SQLSTATE '99999'; IF Personel.NrKonta < 10000000000000000000000000 THEN SIGNAL za_krotki_nr_konta end if; if Personel.NrKonta > 99999999999999999999999999 THEN SIGNAL za_dlugi_nr_konta END IF; IF Personel.PESEL < 10000000000 THEN SIGNAL za_krotki_PESEL end if; if Personel.PESEL > 99999999999 THEN SIGNAL za_dlugi_PESEL end if END WIDOK:

3. TypPokoju Trigger sprawdza czy cena pokoju jest większa niż 0 oraz czy liczba łóżek jest większa niż 0. Gdy występuje błąd system wysyła odpowiednio komunikaty: „zla_cena” oraz “ nie_ma_pokoju_bez_lozka”.

KOD SQL:

ALTER TRIGGER "TypPokoju" BEFORE INSERT, Update ON TypPokoju REFERENCING NEW AS TypPokoju FOR EACH ROW BEGIN DECLARE skontroluj EXCEPTION FOR SQLSTATE '99999'; IF TypPokoju.Cena 11 THEN SIGNAL za_dlugi_PESEL end if END...