Skład zespołu:

Systemy Baz Danych Projekt

Grupa: Ocena: ………………

Sieć hoteli

Opis problemu projektowego

Tematem projektu jest baza danych sieci hotelowej. Zaprojektowana ona do przechowywanie danych o rezerwacjach. W jej skład będzie wchodziła lista hoteli, klientów i rezerwacji których dokonali oni w tych hotelach. Główną grupą docelową która dla której została stworzona ta baza są recepcje poszczególnych hoteli. Osoby tam pracujące będą mogły rejestrować nowe rezerwacje, modyfikować je, anulować. Menadżerowie hoteli będą mogli sprawdzić aktualny stan obłożenia hoteli, a dział marketingu wygenerować statystyki potrzebne do promocji.

Lista tabel w bazie danych

1. Hotele - zawiera listę hoteli wraz z ich parametrami.

2. Pokoje - zawiera listę pokoi w hotelach wraz z ich parametrami.

3. Klienci - zawiera listę gości dokonujących rezerwację.

4. Rezerwacje - zawiera listę zamówień wraz z ich statusem i innymi danymi.

5. Statusy - zawiera listę statusów jakie może przyjąć rezerwacja

6. Kraje - zawiera listę krajów z których pochodzą klienci, oraz w których są hotele.

Struktura poszczególnych tabel

Hotele

• Id - klucz główny

• Nazwa

• Adres

• KodPocztowy

• Miasto

• KrajId - klucz obcy z tabeli Kraje

• Telefon

• FAX

• IloscGwiazdek

Pokoje

• Id - klucz główny

• HotelId - klucz obcy z tabeli Hotele

• Numer

• Pietro

• IloscOsob

Klienci

• Id - klucz główny

• Imie

• Nazwisko

• Plec

• Adres

• KodPocztowy

• Miasto

• KrajId - klucz obcy z tabeli Kraje

• Telefon

Rezerwacje

• Id - klucz główny

• PokojId - klucz obcy z tabeli Pokoje

• KlientId - klucz obcy z tabeli Klienci

• DataPrzyjazdu

• DataWyjazdu

• StatusId - klucz obcy z tabeli Statusy

Statusy

• Id - klucz główny

• Nazwa

Kraje

• Id - klucz główny

• Nazwa

Implementacja modelu w języku Sybase SQL

CREATE TABLE Hotele

(

Id INTEGER PRIMARY KEY DEFAULT AUTOINCREMENT,

Nazwa VARCHAR(50) NOT NULL,

Adres VARCHAR(100) NOT NULL,

KodPocztowy VARCHAR(6) NOT NULL,

Miasto VARCHAR(20) NOT NULL,

KrajId INTEGER NOT NULL,

Telefon VARCHAR(15) NOT NULL,

FAX VARCHAR(15) NOT NULL,

IloscGwiazdek SMALLINT NOT NULL

);

CREATE TABLE Pokoje

(

Id INTEGER DEFAULT AUTOINCREMENT,

HotelId INTEGER NOT NULL,

Numer INTEGER NOT NULL,

Pietro SMALLINT NOT NULL,

IloscOsob SMALLINT NOT NULL,

PRIMARY KEY (Id),

FOREIGN KEY (HotelId) REFERENCES Hotele (Id)

);

CREATE TABLE Klienci

(

Id INTEGER DEFAULT AUTOINCREMENT,

Imie VARCHAR(50) NOT NULL,

Nazwisko VARCHAR(50) NOT NULL,

Plec CHAR NOT NULL,

CHECK (UPPER(Plec) = 'M' or UPPER(Plec) = 'K'),

Adres VARCHAR(50) NOT NULL,

KodPocztowy VARCHAR(6) NOT NULL,

Miasto VARCHAR(20) NOT NULL,

KrajId INTEGER NOT NULL,

Telefon VARCHAR(10) NOT NULL,

PRIMARY KEY (Id),

FOREIGN KEY (KrajId) REFERENCES Kraje (Id)

);

CREATE TABLE Rezerwacje

(

Id INTEGER DEFAULT AUTOINCREMENT,

PokojId INT NOT NULL,

KlientId INT NOT NULL,

DataPrzyjazdu DATE NOT NULL,

DataWyjazdu DATE NOT NULL,

StatusId SMALLINT NOT NULL,

PRIMARY KEY (Id),

FOREIGN KEY (PokojId) REFERENCES Pokoje (Id),

FOREIGN KEY (KlientId) REFERENCES Klienci (Id),

FOREIGN KEY (StatusId) REFERENCES Statusy (Id)

);

CREATE TABLE Statusy

(

Id SMALLINT PRIMARY KEY DEFAULT AUTOINCREMENT,

Nazwa VARCHAR(50) NOT NULL

);

CREATE TABLE Kraje

(

Id INTEGER PRIMARY KEY DEFAULT AUTOINCREMENT,

Nazwa VARCHAR(50) NOT NULL

);

Opis przykładowych funkcji realizowanych przez system

Dodanie nowego kraju do bazy danych:

INSERT INTO Kraje (Nazwa) VALUES ('Polska')

Alternatywnym sposobem jest import państw z pliku:

LOAD INTO TABLE Kraje FROM 'D:\Dokumenty\hotele\kraje.txt' DEFAULTS ON

Dodanie nowego hotelu do bazy danych:

INSERT INTO Hotele (Nazwa, Adres, KodPocztowy, Miasto, KrajId, Telefon, FAX, IloscGwiazdek) VALUES ('Hilton', 'ul. Plaska 1', '02-526', 'Kielce', '1', '[phone]', '[phone]', 5)

Dodanie nowego pokoju jednoosobowego na 3 piętrze o numerze 14 do hotelu o Id=5:

INSERT INTO Pokoje (HotelId, Numer, Pietro, IloscOsob) VALUES (5, 14, 3, 1);

Dodanie nowego klienta do bazy danych:

INSERT INTO Klienci (Imie, Nazwisko, Plec, Adres, KodPocztowy, Miasto, KrajId, Telefon) VALUES ('Grzegorz', 'Trochimiuk', 'M', 'Rakowiecka 1', '00-700', 'Malbork', 1, '12345')

Funkcja dodania rezerwacji do systemu dla klienta o określonym Id:

INSERT INTO Rezerwacje (PokojId, KlientId, DataPrzyjazdu, DataWyjazdu, StatusId) VALUES (1, 987, '2006-01-01', '2006-01-01', 1)

Funkcja anulowania już istniejącej rezerwacji:

UPDATE Rezerwacje SET Statusie=1 WHERE id=39

Funkcja wyczyszczenia wszystkich rezerwacji

TRUNCATE TABLE Rezerwacje

Funkcja usuwająca rekordy z klientami którzy nie złożyli żądnej rezerwacji:

DELETE FROM Klienci WHERE id NOT IN (SELECT DISTINCT KlientId FROM Rezerwacje)

Lista wszystkich hoteli posortowana po kraju i mieście

SELECT k.Nazwa, h.Nazwa, h.Miasto, h.Adres, h.KodPocztowy FROM Hotele h LEFT JOIN Kraje k ON k.Id=h.KrajId ORDER By k.Nazwa, h.Miasto

Lista 10 hoteli z największa ilością pokoi:

SELECT TOP 10 h.Nazwa, h.Miasto, COUNT(p.Id) AS LiczbaPokoi

FROM Pokoje p LEFT JOIN Hotele h On h.Id=p.HotelId

GROUP BY h.Nazwa, h.Miasto, p.HotelId ORDER BY LiczbaPokoi DESC

Lista wszystkich rezerwacji anulowanych gdzie data przyjazdu jest pomiędzy dniem 4, a 8 luty 2007 roku:

SELECT k.Imie, k.Nazwisko, kk.Nazwa, r.DataPrzyjazdu, r.DataWyjazdu, h.Nazwa, h.Miasto

FROM Rezerwacje r

LEFT JOIN Pokoje p ON p.Id = r.PokojId

LEFT JOIN Hotele h ON h.Id = p.HotelId

LEFT JOIN Kraje hk ON hk.Id = h.KrajId

LEFT JOIN Statusy s ON s.Id = r.StatusId

LEFT JOIN Klienci k ON k.Id = r.KlientId

LEFT JOIN Kraje kk ON kk.Id = k.KrajId

WHERE r.DataPrzyjazdu BETWEEN '2007-02-04' AND '2007-02-08' AND s.Nazwa='Anulacja'

Lista wszystkich rezerwacji klientów mieszkających w wybranych miastach:

SELECT k.Imie, k.Nazwisko, kk.Nazwa, k.Miasto, r.DataPrzyjazdu, r.DataWyjazdu, h.Nazwa, h.Miasto

FROM Rezerwacje r

LEFT JOIN Pokoje p ON p.Id = r.PokojId

LEFT JOIN Hotele h ON h.Id = p.HotelId

LEFT JOIN Kraje hk ON hk.Id = h.KrajId

LEFT JOIN Statusy s ON s.Id = r.StatusId

LEFT JOIN Klienci k ON k.Id = r.KlientId

LEFT JOIN Kraje kk ON kk.Id = k.KrajId

WHERE k.Miasto IN ('Brooklyn', 'Ventura')

Lista wszystkich klientów, których nazwisko rozpoczyna się na `Th':

SELECT Imie, Nazwisko, Adres, KodPocztowy, Miasto FROM Klienci WHERE Nazwisko LIKE 'Th%' ORDER BY Nazwisko

Lista rezerwacji klientów pochodzących z krajów kończących się na `s'

SELECT Imie+' '+Nazwisko+' z '+Miasto AS Klient, kk.Nazwa AS Kraj

FROM Klienci k LEFT JOIN Kraje kk ON kk.Id=k.KrajId

WHERE kk.Nazwa LIKE '%s'

Lista 5 najpóźniej wyjeżdżających klientów z wybranych hoteli:

SELECT TOP 5 k.Imie, k.Nazwisko, kk.Nazwa, k.Miasto, r.DataPrzyjazdu AS [Data Przyjzdu], r.DataWyjazdu AS [Data Wyjazdu], h.Nazwa, h.Miasto

FROM Rezerwacje r

LEFT JOIN Pokoje p ON p.Id = r.PokojId

LEFT JOIN Hotele h ON h.Id = p.HotelId

LEFT JOIN Kraje hk ON hk.Id = h.KrajId

LEFT JOIN Statusy s ON s.Id = r.StatusId

LEFT JOIN Klienci k ON k.Id = r.KlientId

LEFT JOIN Kraje kk ON kk.Id = k.KrajId

WHERE h.Id=20 OR h.Id=21 ORDER By 6 DESC

Lista krajów z których było od 21 do 23 rezerwacji:

SELECT UPPER(kk.Nazwa) AS Kraj, COUNT(r.Id) AS [Liczba Rezerwacji]

FROM Rezerwacje r

LEFT JOIN Klienci k ON k.Id = r.KlientId

LEFT JOIN Kraje kk ON kk.Id = k.KrajId

GROUP BY kk.Nazwa

HAVING COUNT(r.Id) BETWEEN 21 AND 23

ORDER BY 2 ASC

Perspektywa zawierająca listę nie anulowanych rezerwacji:

CREATE VIEW RezerwacjeNieanulowane AS

SELECT k.Imie, k.Nazwisko, kk.Nazwa AS MiastoKlienta, r.DataPrzyjazdu, r.DataWyjazdu, h.Nazwa, h.Miasto

FROM Rezerwacje r

LEFT JOIN Pokoje p ON p.Id = r.PokojId

LEFT JOIN Hotele h ON h.Id = p.HotelId

LEFT JOIN Kraje hk ON hk.Id = h.KrajId

LEFT JOIN Statusy s ON s.Id = r.StatusId

LEFT JOIN Klienci k ON k.Id = r.KlientId

LEFT JOIN Kraje kk ON kk.Id = k.KrajId

WHERE s.Id=2 ORDER BY hk.Nazwa DESC, h.Miasto, r.DataPrzyjazdu

Prawa dostępu użytkowników:

W naszej bazie danych proponujemy 3 grupy użytkowników:

• Administratorzy - mają pełne uprawnienia do całej razy danych

GRANT ALL PRIVILEGES ON Kraje TO adminKowalski

GRANT ALL PRIVILEGES ON Kraje TO adminNowak

GRANT ALL PRIVILEGES ON Kraje TO adminJasinski

GRANT ALL PRIVILEGES ON Kraje TO adminAdamski

…

• Rezerwacje - osoby odpowiedzialne za tworzenie i modyfikacje z pewnymi ograniczeniami już istniejących klientów i ich rezerwacji

GRANT SELECT, INSERT, UPDATE(StatusId) ON Rezerwacje TO rezerwacje1

GRANT SELECT, INSERT, ON Klienci TO rezerwacje1

GRANT SELECT ON Kraje TO rezerwacje1

GRANT SELECT ON Statusy TO rezerwacje1

GRANT SELECT ON Pokoje TO rezerwacje1

…

• Marketing - osoby generujące statystyki z bazy danych, a więc nie muszą mieć uprawnień do ich modyfikacji

GRANT SELECT ON Rezerwacje TO marketing1

GRANT SELECT ON Pokoje TO marketing1

GRANT SELECT ON Hotele TO marketing1

GRANT SELECT ON Kraje TO marketing1

GRANT SELECT ON Statusy TO marketing1

Projekt bazy danych powstał w oparciu o silnik bazy danych Sybase.

---