Laboratorium 4
==============
_"Quidquid latine dictum, altum videtur"_
-----
Z tego laboratorium przygotowujesz sprawozdanie. Przygotowujesz je na zajęciach, a przy
ich zakończeniu wysyłasz na adres podany na końcu tej instrukcji. Instrukcja dla wszystkich
jest taka sama.
Pracę wykonujesz w grupach dwuosobowych. Dobierz sobie współpracownika. Na liście obecności
zaznacz numer grupy (dwie osoby - jeden numer grupy).
Rzeczy oznaczone tak, jak poniżej, dotyczą tego, co masz zawrzeć w sprawozdaniu. Na przykład:
Zapisz swoje imię, nazwisko, adres e-mail, kierunek i rok studiów
oraz grupę laboratoryjną i numer albumu.
Wykonujesz ten wariant zadania, jaki jest numer grupy. Na przykład wariant,
który obecnie czytasz, ma numer $lp$.
Podaj również numer zajęć laboratoryjnych (nr 4).
Mogą być to też pytania, na które w sprawozdaniu udzielisz odpowiedzi. Możesz pomagać
sobie wyszukiwarką internetową, oraz zabrać głos w dyskusji, jeśli się jakaś wywiąże.
# Bazy relacyjne
Relacyjne bazy danych (ang. _relational database management systems_, RDBMS, może bardziej poprawnie
_system zarządzania relacyjną bazą danych_), to pewien pomysł na przechowywanie danych. Pomysł ten polega
na rozbiciu naszego świata w szereg takich _obiektów_, _bytów_ (w żargonie bazodanowców **encji**,
od ang. _entity_ czyli w zasadzie _byt_), które będzie można wygodnie umieścić w _tabelach_.
Obiekt taki charakteryzuje się tym, że ma swoją pewną _tożsamość_, po której to tożsamości będziemy
go potem identyfikować i po tejże się do niego odwoływać.
Na przykład: tworząc bazę danych do celów poboru podatków w Jaśnie Oświeconej III Rzplitej, potrzebujemy
listy podatników. Żeby uprościć nieco sprawę, załóżmy że rzeczona baza dotyczyć będzie tylko osób fizycznych
(słowem będzie bazą PIT, od ang. _personal income tax_, podatek dochodowy od osób fizycznych), czyli
_obywateli_.
Jaka cecha obywatela stanowi jego dobrą tożsamość? Imię i nazwisko niezbyt - Janów Kowalskich mamy wszak
ilość znaczną. Może imię, nazwisko i data urodzenia? Też niedobrze, wszak możemy wyobrazić sobie sytuację,
w której dwóch Janów Kowalskich urodziło się w tym samym dniu. Jest to
[dużo bardziej prawdopodobne, niż się wam wydaje](https://pl.wikipedia.org/wiki/Paradoks_dnia_urodzin).
Takie _tożsamości_, lub lepiej **klucze podstawowe** (ang.
_primary keys_) mogą również składać się z kilku cech (lub lepiej **pól**, ang. _fields_).
Wydaje się po dłuższych deliberacjach że dobrym kluczem podstawowym będzie tu PESEL obywatela. Ryzyko pomyłki
z innym obywatelem jest minimalne (chyba że aktualnie jesteśmy komornikiem). Dzięki takiej _tożsamości_
możemy potem kazać bazie chociażby _zmień nazwisko osobie legitymującej się numerem PESEL 95101810106_
bez ryzyka pomyłki, w razie gdyby zmieniła nazwisko z racji wyjścia za mąż. Klucz podstawowy przywiązuje
nasz wiersz w bazie do konkretnego obiektu istniejącego w rzeczywistości.
Takie rozwiązanie od kilku lat
już zastosowano, dzięki czemu ilość druków NIP-7 w obrocie znacznie spadła, a Panowie zapewne do swojego
rocznego rozliczenia podatkowego nie wpisywali żadnych NIP-ów, bo wystarczył PESEL.
Pominę tu pytanie jaki klucz podstawowy należy zastosować do konkretnego, rocznego rozliczenia podatkowego,
gdyż ze względu na
[mnogość możliwych sposobów rozliczeń przewidzianych stosowną ustawą](http://isap.sejm.gov.pl/Download;jsessionid=DEE94711477213D50C7A580724D3176C?id=WDU19910800350&type=3)
stanowi to Bardzo Dobre Pytanie. **Zwalniam z zaliczenia jak ktoś ma dobry i wyczerpujący pomysł**.
Tak czy inaczej, wypiszmy kilka sensownych _pól_ w takiej tabeli od obywateli:
* Imię
* Nazwisko
* Adres zameldowania: miasto
* Adres zameldowania: kod pocztowy
* Adres zameldowania: ulica i numer budynku oraz lokalu
* Adres korespondencyjny: miasto
* Adres korespondencyjny: kod pocztowy
* Adres korespondencyjny: ulica i numer budynku oraz lokalu
* PESEL
* Data urodzin
* Numer telefonu
I teraz parę kwestii nazewniczych. Odnosząc się do tego konkretnego przykładu:
* Nazwę pola, wraz z jego typem i przeznaczeniem w konkretnej tabeli nazywamy **kolumną**
* Reprezentację obywatela w tej tabeli nazywamy **wierszem**
* Konkretną cechę konkretnego obywatela nazywamy **polem**
Numeru telefonu obywatel posiadać nie musi. Każdy bieszczadzki zakapior ma prawo do
Od razu zauważyć możemy, że trzymanie dwóch _adresów_. nie jest takim dobrym pomysłem. Analityczny umysł
zauważyć może, że adres jest pewnym _bytem samym w sobie_. Możemy więc stworzyć osobną tabelę do przechowywania adresów.
Powstaje z kolei bardzo dobre pytanie, co stanowi _tożsamość_ danego adresu. Czasem, gdy nie mamy dobrego
pomysłu na tożsamość możemy zażyczyć sobie, aby baza danych stworzyła osobną tożsamość dla danego wiersza.
Później, gdy będziemy dodawać rekord, baza poinformuje nas o tym identyfikatorze.
Musimy jednak uważać! Dla potrzeb bazy dwa identycznie wpisane adresy będą _różnymi_, ponieważ bedą miały
różne identyfikatory (chyba że nie poinformujemy o tym bazy). Czasem jednak możemy to przecierpieć.
Spróbujmy więc odseparować obywatela od adresu. Otrzymamy zapewne coś takiego:
**Obywatel**:
* Imię
* Nazwisko
* Adres zameldowania - ID adresu
* Adres korespondencyjny - ID adresu
* PESEL (**klucz podstawowy**)
* Data urodzin
* Numer telefonu (**może być pusty**)
**Adres**:
* Identyfikator adresu (**klucz podstawowy**, **generowany automatycznie**)
* Miasto
* Kod pocztowy
* Ulica i numer lokalu/budynku
Można sprzeczać się, że taki podział nie miał najmniejszego sensu. To możliwe. W każdym razie to co zrobiliśmy
teraz nazywa się [normalizacją](https://pl.wikipedia.org/wiki/Normalizacja_bazy_danych). W epoce baz danych
[NoSQL](https://msdn.microsoft.com/pl-pl/dn912483.aspx) staje się ona coraz bardziej zagadnieniem jedynie akademickim,
jednak mam wrażenie że będzie na egzaminie.
Identyfikatory generowane przez bazę najczęściej są rosnącymi liczbami dodatnimi i **nie powtarzają się**,
nawet gdybyśmy skasowali wiersz. Ma to sens. W końcu gdybyśmy prowadzili policyjną kartotekę dowodów rzeczowych,
a ktoś w piśmie wspomniał o dowodzie o określonym identyfikatorze, a w międzyczasie ten dowód został zniszczony ze względu na koniec sprawy,
to teraz jego papier mógłby się odwoływać do *istniejącego dowodu w innej sprawie*, a nie po prostu do *niczego*.
Byłoby to katastrofalne w skutkach, a błąd programisty mógłby kosztować kogoś pozbawienie wolności.
Istotne w kluczu podstawowy jest to, że w danej tabeli istnieć może tylko jeden rekord o tym kluczu. Nie
jest możliwe wstawienie dwóch - mamy więc podstawowy system zapobiegający sytuacjom niemożliwym, na przykład
istnieniu dwóch _różnych_ osób o identycznym PESEL-u.
Kolumnę, która stanowi klucz podstawowy w innej tabeli - czyli odnosi się do innej tabeli - nazywamy **kluczem obcym**
(ang. _foreign key_). W naszej tabeli _Obywatel_ mamy dwa klucze obce - oba odnoszą się do tabeli
_Adres. Ponieważ wielu obywatelom przypisać można jeden adres, istnieje więc relacja **wiele-do-jednego**
między tabelami *Obywatel* i *Adres*.
Po co stosuje się takie wyróżnienia? Otóż możemy bazę poinformować o istnieniu takiej relacji. Wtedy pilnować
ona będzie spójności, czyli jeśli spróbujemy skasować adres, pod którym ktoś jednak zamieszkuje, baza
odmówi wykonania takiego polecenia (i dobrze).
Opisując kolumny w tabeli musimy również podać typ tego, co będziemy tam przechowywać. Typy zależą już
od konkretnego oprogramowania RDBMS z którego korzystamy. Na WEiI PRz istnieje parcie na
[Oracle Database](https://docs.oracle.com/cd/E11882_01/server.112/e41085/sqlqr06002.htm#SQLQR959), tak
więc tej typologii rekomendowałbym się nauczyć na egzamin.
Na przyszłych laboratoriach będziemy korzystać z [PostgreSQL](https://pl.wikipedia.org/wiki/PostgreSQL)
oraz jego typów. Ponieważ istnieje [tabela konwersji typów](http://www.sqlines.com/oracle-to-postgresql) między tymi bazami, radzę
myśleć w typach Oracle, a w locie dopasowywać sobie to do PostgreSQL. O typach PostgreSQL można zapomnieć
po odesłaniu ostatniej instrukcji.
Projektując bazę danych, dobrze jest ją sobie narysować. Służy do tego [diagram związków encji](https://pl.wikipedia.org/wiki/Diagram_zwi%C4%85zk%C3%B3w_encji)
lub inaczej ERD. Na egzaminie najpewniej będzie (jeśli będzie) jej wariant w postaci [notacji kruczej stopki](https://pl.wikipedia.org/wiki/Diagram_zwi%C4%85zk%C3%B3w_encji).
Użyj Google i naucz się rysować diagramy ERD w maksymalnie 15 minut.
Narysuj diagram ERD dla tabel Obywatel i Adres.
Jeśli chcemy zamodelować relację "jedna encja posiada inne" najlepiej to zrobić właśnie na dwóch tabelach.
Gdybyśmy chcieli stworzyć tabelę pt. "czy obywatel rozliczył podatek", możemy zrobić tabelę
_CzyRozliczyłPodatek_, która wyglądać będzie mniej więcej tak:
* PESEL obywatela
* Rok fiskalny
* Czy rozliczone?
Przy czym pola PESEL i rok fiskalny stanowić będą klucz podstawowy, zaś sam PESEL - klucz obcy. Jest to
dobry wybór na tożsamość tego pola, bo przecież jedna osoba rozliczyć za dany rok może się tylko raz (
albo tak myślimy). Jak tu widzimy, klucz podstawowy może się również składać z klucza obcego - bo czemu nie.
Wystąpi również relacja **jeden-do-wielu** w stosunku Obywatel - CzyRozliczyłPodatek, bo jedna osoba
może (a nawet musi) się rozliczać wielokrotnie - raz z każdego roku.
# Zadanie laboratoryjne
Na dzisiejszym laboratorium przygotujemy schemat bazy danych do pewnego zastosowania.
Każda grupa ma inne zadanie. Twoje zadanie to przygotować schemat $description$.
Minimalnie użyjesz $min_tables$ tabel, choć jeśli jesteś w stanie znormalizować ten schemat bardziej,
to będzie to mile widziane. Każda z tabel ma zawierać minimalnie $min_fields$ pól. Nie wolno tworzyć
identyfikatorów automatycznie generowanych, jeśli użyć można istniejącego w rzeczywistości atrybutu
(np. [EAN](https://pl.wikipedia.org/wiki/EAN)).
Jeśli w tabeli chciałbyś zawrzeć więcej informacji, niż to wynika z zadania, to bardzo dobrze! W razie
wątpliwości pytaj prowadzącego.
1. Zastanów się, co w zasadzie śledzisz. Gdybyś prowadził kartotekę papierową z tymi informacjami, to jak poukładałbyś szufladki?
2. Jakie _byty_ śledzisz w swojej bazie danych?
Krótki opis zagadnienia bazy oraz odpowiedzi na powyższe pytania zapisz w sprawozdaniu.
3. Rozplanuj bazę danych, każdą tabelę z osobna i opisz je słowami.
4. Narysuj jej diagram ERD
Plan bazy i tabel umieść w sprawozdaniu wraz z diagramem ERD.
Diagram ERD koniecznie w postaci zdjęcia narysowanego odręcznie na papierze schematu.
Pamiętaj, aby schematem tabeli uniemożliwić sytacje niemożliwe (np. dwie osoby mają rezerwację na
identyczne miejsca w tym samym seansie kinowym).
**Zanim zakończysz pracę, skonsultuj swoje wyniki z prowadzącym!**
# Wyślij sprawozdanie
_Fast fertig_! Zapisz i wyślij swoje sprawozdanie. Jeśli masz problem z wklejeniem zdjęcia zrobionego
z telefonu to wyślij sprawozdanie później. Jeśli nie masz takiego telefonu, wykorzystaj kolegę.
Wyślij je na adres sprawozdania@henrietta.com.pl.
W tytule umieść imię, nazwisko i numer zadania.
Załącz sprawozdanie, lub wklej je w treść maila.
Pamiętaj, uczestniczysz w zajęciach LABORATORYJNYCH,
więc nie pisz "Projekt Adam Nowak".
Podejście takie będzie aktywnie penalizowane.
Teraz idź korzystać z dobrej pogody, bo na weekendzie ma kropić.
_"Uczmy się kochać papier, tak szybko odchodzi"_