Zadanie 20. Hotel Panorama (0-10)
Dane są trzy pliki tekstowe o nazwach: klienci.txt; pokoje.txt; noclegi.txt.
Zawierają one informacje na temat zrealizowanych usług hotelowych hotelu Panorama
w okresie wakacyjnym, tzn. zameldowanie gościa nastąpiło od 1.07.2013 do 31.08.2013.
Pierwszy wiersz każdego z plików jest wierszem nagłówkowym, a dane w wierszach
rozdzielone są znakami tabulacji.
Plik o nazwie klienci.txt zawiera informacje o gościach hotelu. W każdym wierszu
pliku znajdują się następujące dane: numer dowodu osobistego gościa hotelu (nr_dowodu),
nazwisko (nazwisko), imię (imie) i miejsce zamieszkania (miejscowosc).
Przykład:
nr_dowodu nazwisko imie
miejscowosc
SAS253401 Pastuszak Joanna
Szczecin
UNC608098 Siudut
Anna
Jaworzno
NMZ567271 Konopka
Kamil
Tarnowskie Gory
Plik o nazwie pokoje.txt zawiera w każdym wierszu: numer pokoju (nr_pokoju),
maksymalną liczbę osób, które mogą nocować w pokoju (liczba_osob), standard pokoju
(standard), gdzie S - oznacza pokój standardowy, W - pokój o podwyższonym
standardzie oraz cenę wynajęcia pokoju na jedną dobę (cena).
Przykład:
nr_pokoju liczba_osob
standard cena
101
2
S
220
102
2
S
220
103
2
S
220
Plik o nazwie noclegi.txt zawiera w każdym wierszu: identyfikator noclegu (id_noc),
datę przyjazdu gościa hotelu (data_przyjazdu), datę wyjazdu gościa (data_wyjazdu),
numer dowodu osobistego gościa korzystającego z danego noclegu (nr_dowodu), numer
pokoju (nr_pokoju), oraz dodatkowe usługi, z których skorzystał gość podczas noclegu
(uslugi).
Przykład:
id_noc data_przyjazdu
data_wyjazdu
nr_dowodu
nr_pokoju
uslugi
198
2013-07-10
2013-07-12
JAA932190
501
2020
199
2013-07-10
2013-07-11
SIS395155
108
1010
206
2013-07-10
2013-07-13
RMS452742
113
0030
Korzystanie z dodatkowych usług odnotowane jest w postaci liczb jednocyfrowych.
Na pierwszej pozycji od lewej strony odnotowana jest liczba konsumowanych śniadań (cena
jednostkowa 20 zł), na drugiej pozycji - liczba wejść na basen (cena jednostkowa 30 zł),
na trzeciej - parking (cena jednostkowa 15 zł), a na ostatniej pozycji - czyszczenie ubrania
(cena jednostkowa 35 zł).
Na przykład zapis:
2020 - oznacza, że gość 2 razy jadł śniadanie w hotelu (2*20 zł), nie korzystał z basenu,
przez 2 doby parkował samochód na hotelowym parkingu (2*15 zł) oraz nie oddawał ubrania
do czyszczenia. W związku z tym do kosztu noclegu dopisuje się koszt dodatkowych usług
w wysokości 70 zł.
66 Informator o egzaminie maturalnym z informatyki od roku szkolnego 2014/2015
1130 - oznacza, że gość zjadł jeden raz śniadanie w hotelu (1*20 zł), jeden raz skorzystał
z basenu (130 zł) i przez 3 doby parkował samochód na hotelowym parkingu (315 zł) oraz
nie oddawał ubrania do czyszczenia. Gościowi do kosztu noclegu dopisuje się koszt
dodatkowych usług w wysokości 95 zł.
Uwaga: Żadna z usług nie była zamawiana więcej niż 9 razy.
Korzystając z danych zawartych w tych plikach oraz z dostępnych narzędzi informatycznych,
wykonaj poniższe polecenia. Każdą odpowiedź umieść w pliku wyniki.txt, poprzedzając
ją oznaczeniem odpowiedniego podpunktu od a) do e).
a) Podaj imię i nazwisko gościa, który skorzystał z największej liczby noclegów podczas
jednorazowego pobytu w hotelu Panorama. Podaj liczbę tych noclegów.
b) Podaj zestawienie (imię i nazwisko) osób z Krakowa goszczących w hotelu Panorama,
którzy korzystali z parkingu.
c) Utwórz listę miejscowości, z których co najmniej 15 różnych osób nocowało choć raz
w hotelu Panorama. Zestawienie posortuj alfabetycznie.
d) Podaj kwotę uzyskaną z tytułu wynajmu pokoi oraz kwotę uzyskaną z opłat za korzystanie
przez gości z dodatkowych usług w okresie od 1.07.2013 do 31.08.2013.
e) Podaj numery pokoi o podwyższonym standardzie, z których nigdy nie korzystali goście
z Krakowa.
Do oceny oddajesz plik(i) o nazwie(ach) ,
tu wpisz nazwę(y) pliku(ów)
zawierający(e) komputerową(e) realizację(e) Twoich obliczeń oraz plik tekstowy o nazwie
wyniki.txt z odpowiedziami do podpunktów a), b), c), d), e).
Wymagania ogólne
II. Wyszukiwanie, gromadzenie i przetwarzanie
informacji z różnych źródeł; opracowywanie
za pomocą komputera: rysunków, tekstów danych
liczbowych, motywów, animacji, prezentacji
multimedialnych.
Wymagania szczegółowe
przetwarzanie i wykorzystywanie informacji,
korzystanie z różnych źródeł i sposobów zdobywania
informacji.
Zdający:
integralności danych,
\ninformacji w relacyjnej bazie danych; tworzy
aplikację bazodanową, wykorzystującą język zapytań,
kwerendy, raporty; zapewnia integralność danych
na poziomie pól, tabel, relacji.
Przykładowe zadania z rozwiązaniami 67
Schemat punktowania
Podpunkt
Czynność
Liczba
punktów
za
podpunkt
Liczba
punktów
za
zadanie
a
Za poprawną odpowiedź - 2 punkty,
w tym za:
imię i nazwisko - 1 punkt.
liczbę noclegów - 1 punkt.
2
10
b
Za poprawną odpowiedź - 2 punkty.
W przypadku powtórzeń - 1 punkt.
2
c
Za poprawną odpowiedź - 2 punkty,
w tym za:
podanie listy - 1 punkt.
za prawidłowe posortowanie - 1 punkt.
2
d
Za poprawną odpowiedź - 2 punkty,
w tym za:
kwotę uzyskaną z tytułu wynajmu pokoi - 1 punkt.
kwotę uzyskaną z opłat za korzystania przez gości
z dodatkowych usług - 1 punkt.
2
e
Za poprawną odpowiedź - 2 punkty.
W przypadku braku jednego numeru lub wystąpienia
jednego numeru nieprawidłowego - 1 punkt.
2
Zadanie 20. Hotel Panorama (0-10) - rozwiązanie
Plik zawierający rozwiązanie znajdują się w folderze HOTEL.
Komentarz
Zadanie Hotel Panorama należy do grupy zadań, które mogą być rozwiązywane narzędziami
bazodanowymi lub z wykorzystaniem arkusza kalkulacyjnego. Baza składa się z trzech tabel:
klienci (klucz główny - nr_dowodu), pokoje (klucz główny - nr_pokoju) i noclegi (klucz
główny - Id_noclegu). Tabela klienci połączona zostanie z tabelą noclegi relacją typu „jeden
do wielu”, podobnie tabelę pokoje z tabelą noclegi łączy relacja typu „jeden do wielu”.
68 Informator o egzaminie maturalnym z informatyki od roku szkolnego 2014/2015
W Accessie import plików z danymi jest prosty. Ambitni maturzyści mogą utworzyć bazę
i zapytania w języku SQL:
-- utworzenie bazy danych hotel
create database hotel;
-- ustawienie bazy hotel jako domyślnej
use hotel;
-- utworzenie tabeli noclegi
CREATE TABLE noclegi (id_noc int NOT NULL,data_przyjazdu date NOT NULL,data_wyjazdu date NOT NULL,nr_dowodu char(9) NOT NULL,nr_pokoju int NOT NULL,uslugi int(1) NOT NULL,
PRIMARY KEY (id_noc)
);
-- zaladowanie danych do tabeli noclegi
LOAD DATA INFILE
"C:\\\\Dane_hotel\\\\noclegi.csv" INTO TABLE noclegi FIELDS
TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\\\' LINES
TERMINATED BY '\\n' (id_noc,data_przyjazdu,data_wyjazdu,nr_dowodu,nr_pokoju,uslugi);
-- utworzenie tabeli klienci
CREATE TABLE klienci (nr_dowodu char(9) NOT NULL,nazwisko char(20) NOT NULL,imie char(20) NOT NULL,miejscowosc char(20) NOT NULL,
PRIMARY KEY (nr_dowodu)
);
-- zaladowanie danych do tabeli klienci
LOAD DATA INFILE
"C:\\\\Dane_hotel\\\\klienci.csv" INTO TABLE klienci FIELDS
TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\\\' LINES
TERMINATED BY '\\n' (nr_dowodu,nazwisko,imie,miejscowosc);
-- utworzenie tabeli pokoje
CREATE TABLE pokoje (nr_pokoju int(11) NOT NULL,liczba_osob int(11) NOT NULL,standard char(1) NOT NULL,cena int(11) NOT NULL,
PRIMARY KEY (nr_pokoju)
);
Przykładowe zadania z rozwiązaniami 69
-- zaladowanie danych do tabeli pokoje
LOAD DATA INFILE
"C:\\\\Dane_hotel\\\\pokoje.csv" INTO TABLE pokoje FIELDS
TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\\\' LINES
TERMINATED BY '\\n' (nr_pokoju,liczba_osob,standard,cena);
W poleceniu a) należy podać imię i nazwisko gościa, który skorzystał z największej liczby
noclegów podczas jednorazowego pobytu w hotelu Panorama oraz liczbę tych noclegów. Aby
podać odpowiedź na tak postawione pytanie, należy obliczyć liczbę nocy, odejmując od daty
wyjazdu datę przyjazdu, znaleźć maksimum i odpowiadającą temu maksimum osobę.
Zapytanie a:
SELECT Klienci.imie, Klienci.nazwisko, (data_wyjazdu-data_przyjazdu) AS Liczba_nocy
FROM Klienci, Noclegi
WHERE ((Klienci.nr_dowodu)=(Noclegi.nr_dowodu))
ORDER BY (data_wyjazdu-data_przyjazdu) DESC;
W MySQLu do obliczenia liczby noclegów należy użyć funkcji TO_DAYS:
SELECT imie, nazwisko, to_days(data_wyjazdu)-to_days(data_przyjazdu) AS liczba_nocy
FROM Klienci, Noclegi
WHERE ((Klienci.nr_dowodu)=( Noclegi.nr_dowodu))
ORDER BY liczba_nocy DESC;
Aby wykonać zestawienie gości z Krakowa, którzy korzystali z parkingu, należy utworzyć
zapytanie odnoszące się do dwóch tabel: Klienci i Noclegi. Informacja o korzystaniu gościa
z parkingu zapisana została w kolumnie „usługi” na trzeciej pozycji, licząc od strony lewej.
Cyfra oznaczająca liczbę parkowań powinna być różna od zera. Jeżeli kolumna „usługi”
przechowuje dane typu char, to wyodrębnienie informacji o parkowaniu można uzyskać za
pomocą funkcji MID(nazwa_kolumny,pierwszy_znak,liczba_znaków).
Jeżeli ktoś preferuje pracę w arkuszu kalkulacyjnym, może zastosować funkcję
FRAGMENT.TEKSTU(tekst,liczba_początkowa,liczba_znaków). Dodatkowym warunkiem
jest pochodzenie gości z Krakowa (tabela Klienci, kolumna miejscowość).
Zapytanie b:
SELECT klienci.nr_dowodu, imie, nazwisko
FROM Klienci, Noclegi
WHERE Klienci.nr_dowodu = Noclegi.nr_dowodu AND Miejscowosc='Krakow' AND
Mid(uslugi,3,1)<>'0'
GROUP BY klienci.nr_dowodu, imie, nazwisko;
W poleceniu SELECT obok imienia i nazwiska dodano pole nr_dowodu, które jednoznacznie
identyfikuje klienta, aby wykluczyć pominięcie osób o takim samym imieniu i nazwisku.
W celu utworzenia listy miejscowości, z których co najmniej 15 różnych osób nocowało choć
raz w hotelu Panorama, należy utworzyć zapytanie pomocnicze w wyniku którego otrzymamy
zestawienie numerów dowodów osobistych (z tabeli Noclegi) oraz miejscowości (z tabeli
Klienci). Dzięki grupowaniu otrzymamy zestawienie bez powtórzeń (czyli noclegi różnych
osób).
Zapytanie c:
SELECT Noclegi.nr_dowodu, Klienci.miejscowosc
FROM Noclegi, Klienci
WHERE ((Noclegi.nr_dowodu)=(Klienci.nr_dowodu))
GROUP BY Noclegi.nr_dowodu, Klienci.miejscowosc;
70 Informator o egzaminie maturalnym z informatyki od roku szkolnego 2014/2015
Korzystając z wyników pomocniczego zapytania, policzymy miejscowości i wypiszemy tylko
te, które występowały w zestawieniu pomocniczym przynajmniej 15 razy (HAVING
count(*)>=15). Na koniec należy je uporządkować alfabetycznie (ORDER BY miejscowość).
Kwerenda c1:
SELECT miejscowosc,
FROM c
GROUP BY miejscowosc
HAVING count(*)>=15
ORDER BY miejscowosc;
Aby podać kwotę uzyskaną z tytułu wynajmu pokoi, należy w pierwszej kolejności dla każdego
identyfikatora noclegu obliczyć iloczyn liczby nocy i ceny pojedynczego noclegu
w wynajmowanym pokoju.
Kwerenda d:
SELECT id_noc, (to_days(data_wyjazdu)-to_days(data_przyjazdu))*cena AS cena_noclegow
FROM Pokoje, Noclegi
WHERE Pokoje.nr_pokoju=Noclegi.nr_pokoju;
Po zsumowaniu wartości wszystkich noclegów otrzymamy kwotę uzyskaną z tytułu wynajmu
pokoi:
Kwerenda d1:
SELECT Sum(cena_noclegow) AS Za_noclegi
FROM d;
Aby obliczyć kwotę uzyskaną z opłat za korzystanie przez gości z dodatkowych usług, należy
dla każdego identyfikatora noclegu wyodrębnić liczbę wykorzystanych usług i pomnożyć
przez cenę tej usługi. Funkcja VAL zamienia wartość typu char na wartość typu int.
W MySQLu będzie to funkcja CAST.
Kwerenda d2:
SELECT Sum(20*Val(Mid(uslugi,1,1))) AS sniadanie,
Sum(30*Val(Mid(uslugi,2,1))) AS basen,
Sum(15*Val(Mid(uslugi,3,1))) AS parking,
Sum(35*Val(Mid(uslugi,4,1))) AS czyszczenie
FROM Noclegi;
Kwerenda d2:
SELECT sniadanie+basen+parking+czyszczenie AS Za_uslugi
FROM d2;
W ostatnim poleceniu należy podać numery pokoi o podwyższonym standardzie, z których
nigdy nie korzystali goście z Krakowa. W tym celu zostało utworzone podzapytanie dające
w wyniku numery pokoi o podwyższonym standardzie w których byli goście z Krakowa,
a następnie pytanie zewnętrzne: wybierz numery pokoi o podwyższonym standardzie z tabeli
pokoje nie występujące w zestawieniu będącym wynikiem podzapytania.
Kwerenda e:
SELECT Pokoje.nr_pokoju
FROM pokoje
WHERE standard='W' AND pokoje.nr_pokoju NOT IN
(SELECT Noclegi.nr_pokoju
FROM Noclegi,Pokoje, Klienci
Przykładowe zadania z rozwiązaniami 71
WHERE Klienci.nr_dowodu = Noclegi.nr_dowodu AND Noclegi.nr_pokoju =
Pokoje.nr_pokoju
AND standard='W' AND miejscowosc='krakow'
GROUP BY Noclegi.nr_pokoju);