Baza opisuje osoby, ich rodziców, dowody osobiste, historię adresów oraz małżeństwa. Służy do ćwiczeń SQL, w szczególności złączeń, samozłączeń, agregacji i podzapytań. Jest uproszczonym modelem ewidencji, a nie projektem rzeczywistego systemu urzędu stanu cywilnego.
Ten opis dotyczy starej wersji schematu PostgreSQL,
z kolumnami takimi jak id_wl, data_pocz
i poczta. Skrypt wczytuje już uporządkowany, powiększony
zestaw danych ze stałymi datami; nie jest to ten sam zestaw co w Oracle.
Pozostałe wersje: Ewidencja ludności — nowa wersja PostgreSQL; Ewidencja ludności — archiwalna wersja Oracle.
Najpierw należy wykonać skrypt tworzący tabele, następnie skrypt wczytujący dane. Skrypty nie tworzą osobnej bazy danych ani konta użytkownika. Wersje używają tych samych nazw tabel, dlatego do ich porównania należy użyć osobnych schematów albo najpierw usunąć poprzedni zestaw.
Po pobraniu plików można je uruchomić w psql.
Poniższy przykład zakłada, że bieżący katalog zawiera pobrane skrypty,
a wybrany schemat nie zawiera jeszcze tabel tego zestawu.
\set ON_ERROR_STOP on \i usc.create.sql \i usc.insert.sql
Skrypt usuwający tabele wykonuje kolejno usunięcie
malzenstwo, dowod, adres
i osoba. Aby odtworzyć dane początkowe, po jego wykonaniu
należy ponownie uruchomić skrypty tworzenia tabel i wczytywania danych.
PK oznacza klucz główny, a FK — klucz obcy.
Wartość NULL jest dozwolona tylko w kolumnach
osoba.id_matki, osoba.id_ojca,
dowod.data_koniec, adres.data_kon,
adres.nr_mieszk oraz malzenstwo.data_kon.
Pozostałe kolumny są obowiązkowe. Kolumny kluczy głównych nie mogą
zawierać NULL, również gdy w skrypcie nie zapisano
przy nich jawnie NOT NULL.
Wszystkie kolumny dat mają typ DATE i przechowują
dzień, miesiąc oraz rok, bez godziny.
id INTEGER PK imie VARCHAR(15) nazwisko VARCHAR(20) data_urodzenia DATE id_matki INTEGER FK → osoba.id, NULL dozwolony id_ojca INTEGER FK → osoba.id, NULL dozwolony
id — dodatni identyfikator osoby. Skrypt nie generuje go automatycznie.
imie, nazwisko — dane osoby. Ani imię, ani nazwisko, ani ich para nie muszą być unikatowe; osoby rozróżniamy po id.
data_urodzenia — data urodzenia osoby. Jeśli ją pominięto przy wstawianiu, domyślnie otrzymuje datę wynikającą z NOW(). Dostarczony skrypt danych podaje wszystkie daty urodzenia jawnie.
id_matki, id_ojca — identyfikatory osób występujących w roli rodziców. NULL oznacza brak informacji o danym rodzicu w bazie, a nie informację, że osoba nie ma dzieci.
Oba klucze obce wskazują tę samą tabelę osoba.
Jedna osoba może być wskazana jako rodzic przez wiele osób.
Relacje rodzic–dziecko są niezależne od tabeli małżeństw:
zapis rodziców nie wymaga, aby ich małżeństwo figurowało w bazie.
Ograniczenia zabraniają wskazania siebie jako własnego rodzica oraz wskazania tej samej osoby jednocześnie jako matki i ojca.
nr INTEGER PK id_wl INTEGER FK → osoba.id data_wydania DATE data_koniec DATE NULL dozwolony
nr — unikatowy numer dowodu. Jest dodatnią liczbą całkowitą; ten model nie przechowuje numerów alfanumerycznych ani zer wiodących.
id_wl — identyfikator właściciela dowodu, wskazujący osoba.id.
data_wydania — data wydania dowodu.
data_koniec — koniec okresu dotyczącego dowodu. NULL oznacza brak zapisanej daty zakończenia.
Dowód należy do dokładnie jednej osoby. Jedna osoba może mieć wiele zapisanych dowodów albo nie mieć żadnego. Identyfikator właściciela nie jest kluczem głównym ani kolumną unikatową tej tabeli.
id_osoby INTEGER FK → osoba.id data_pocz DATE data_kon DATE NULL dozwolony ulica VARCHAR(10) nr_domu VARCHAR(5) nr_mieszk VARCHAR(5) NULL dozwolony kod CHAR(6) poczta VARCHAR(10) PK (id_osoby, data_pocz)
id_osoby — osoba, której dotyczy adres.
data_pocz, data_kon — początek i koniec okresu przypisania adresu do osoby. NULL w końcu okresu oznacza brak zapisanej daty zakończenia.
ulica, nr_domu — ulica i numer domu. Numer domu jest napisem, może więc zawierać np. literę.
nr_mieszk — numer mieszkania jako napis. NULL oznacza, że numeru nie podano; może nie występować w danym adresie.
kod — kod pocztowy, przechowywany w polu CHAR(6). Schemat nie sprawdza wzorca cyfr i łącznika.
poczta — miejscowość pocztowa podana w adresie.
Wiersz opisuje przypisanie adresu do osoby w określonym okresie,
a nie unikatowy lokal. Kilka osób może mieć ten sam adres.
Jedna osoba może mieć wiele wierszy historii adresowej, lecz nie może
mieć dwóch wierszy z taką samą wartością data_pocz.
Składniki adresu nie należą do klucza głównego.
id_zony INTEGER FK → osoba.id id_meza INTEGER FK → osoba.id data_pocz DATE data_kon DATE NULL dozwolony PK (id_zony, id_meza, data_pocz)
id_zony, id_meza — identyfikatory osób w dwóch rolach małżeństwa. Obie wartości wskazują osoba.id i muszą być różne.
data_pocz — początek małżeństwa.
data_kon — koniec małżeństwa. NULL oznacza brak zapisanej daty zakończenia; baza nie przechowuje przyczyny zakończenia.
Ta sama para może występować wielokrotnie z różnymi datami początku.
Osoba może wystąpić w wielu małżeństwach albo w żadnym.
Wyszukując wszystkie małżeństwa osoby, należy uwzględnić obie kolumny
identyfikatorów. W tabeli osoba nie ma kolumny płci,
a schemat nie weryfikuje jej na podstawie imienia.
Nazwy kolumn czasowych nie są ujednolicone:
dowód ma data_wydania i data_koniec,
a adres i małżeństwo mają data_pocz i data_kon.
W adresach i małżeństwach podany koniec musi być późniejszy od początku.
Tabela dowodów nie ma takiego ograniczenia.
Skrypty tej wersji nie określają, czy dzień końcowy należy do okresu.
Przy zadaniach dotyczących stanu na konkretny dzień należy przyjąć
konwencję podaną w treści zadania. Nie należy wyprowadzać jej z samej
nazwy kolumny ani z ograniczenia data_pocz < data_kon.
Brak daty końca nie oznacza automatycznie, że okres obejmuje dowolną
badaną datę — istotny jest również jego początek.
osoba.id_matki i osoba.id_ojca wskazują osoba.id. Do wyświetlenia danych dziecka i rodziców służą samozłączenia z różnymi aliasami tabeli osoba.
dowod.id_wl, adres.id_osoby, malzenstwo.id_zony oraz malzenstwo.id_meza wskazują osoba.id.
| Tabela | Liczba wierszy |
|---|---|
osoba |
167 |
dowod |
182 |
adres |
129 |
malzenstwo |
97 |
Liczby rekordów i konkretne wartości opisują dostarczony zestaw, a nie reguły schematu. Zapytania powinny działać również po dodaniu innych danych spełniających warunki danego zadania.
Archiwalne przykłady zapytań do tej wersji schematu PostgreSQL: część 1 i część 2. Pliki zawierają także warianty zapytań przeznaczone do porównania podczas zajęć.