Sprawozdanie

Baza danych: „HOTEL”

Karolina Drożdżewska

I6X3S1

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.

3. Model fizyczny

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 <= 0 THEN

SIGNAL zla_cena

end if;

if TypPokoju.iloscLozek <= 0 THEN

SIGNAl nie_ma_pokoju_bez_lozka

END IF;

END

WIDOK:

9. Prawa dostępu

1. Użytkownik: DBA hasło: SQL

Użytkownik DBA ma nadane prawa DBA oraz dostęp, możliwość modyfikacji i usunięcia do wszystkich tabel, widoków, funkcji i procedur. Ma również prawo „ grand with grand option”

Co oznacza i osobowy którym nadaliśmy prawo dostępu również mogą nadawać prawa dostępu innym użytkownikom.

2. Użytkownik: Recepcja hasło: recepcja

Użytkownik recepcja ma nadane odpowiednie prawa do swoich przywilejów: tabele faktura może wyświetlać, uzupełniać i aktualizować, Hotel może wyświetlać, Klienta dodawać i usuwać, Konsumpcje wyświetlać, dodawać i uaktualniać…itp. Może również oglądać wszystkie widoki i korzystać z procedur.

3. Użytkownik: Persnel hasło: personel

Użytkownik persnel ma prawo do oglądania i uaktualniania tabeli Personel oraz do ogladania widoku PersonelOdpowiedzilny.

4. Użytkownik Klient bez hasła.

Użytkownik Klient ma możliwość jedynie oglądania widoku WolnePokoje.

10. Dodatkowe zadania

1. Modyfikacja procedury znajdzPokoj.

Zmiana: Oprócz znalezienia pokoju odpowiedniego zadanym parametrom procedura zwracać ma cenę pobraną z bazy i zwiększać ją przy każdym dodatkowym wyposażeniu pokoju ( TV, wanna, widok na morze…itd. ) o 5%.

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 znizka numeric(10,2);

declare nr integer;

declare pok_kursor cursor for

select NrPokoju

from WolnePokoje;

set znizka=1;

if telewizor='T' then set znizka=znizka+0.05 end if;

if balkon='T' then set znizka=znizka+0.05 end if;

if L='T' then set znizka=znizka+0.05 end if;

if w='T' then set znizka=znizka+0.05 end if;

if telewizor='T' then set znizka=znizka+0.05 end if;

open pok_kursor;

petla2: loop

fetch next pok_kursor into nr;

if sqlcode <> 0 then leave petla2;

end if;

select NrPokoju, (Cena*znizka) as AktualnaCena 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. Modyfikacja funkcji IleDniDoRezerwacji

Tą funkcje przerobiłam na procedurę o nazwie blaa. Procedura ta pokazuje nam Imię, Nazwisko, Nr Pokoju oraz Nazwę hotelu rezerwacji z daty którą podajemy jako parametr wejściowy.

KOD SQL:

ALTER PROCEDURE "DBA"."blaa"( in data_rezerwacji date )

bEGIN

DECLARE dni integer;

declare kursorek cursor for

select DataOd from doTabeli;

set dni=data_rezerwacji - today(*);

open kursorek;

petla3: loop

fetch next kursorek into dni;

if sqlcode <> 0 then leave petla3;

end if;

select Imie, Nazwisko, NrPokoju, Nazwa from doTabeli

where DataOd=data_rezerwacji;

end loop petla3;

close kursorek;

END

WIDOK:

3. Ulepszenie triggera personel.

Trigger Personel ma zliczać liczbę znaków wprowadzonych do PESELU i za pomocą tego sprawdzać jego poprawność.

KOD SQL:

ALTER TRIGGER "personel"

BEFORE INSERT, Update ON Personel

REFERENCING NEW AS Personel

FOR EACH ROW

BEGIN

declare a integer;

DECLARE skontroluj EXCEPTION FOR SQLSTATE '99999';

set a = (char_length(Personel.PESEL));

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 a < 11 THEN

SIGNAL za_krotki_PESEL

end if;

if a > 11 THEN

SIGNAL za_dlugi_PESEL

end if

END

---