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 nowej wersji PostgreSQL, z kolumnami
data_poczatku i data_konca.
Dane odpowiadają staremu zestawowi PostgreSQL po zmianie nazw kolumn
i przekształceniu numeru dowodu na napis.
Pozostałe wersje: Ewidencja ludności — stara 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.nowa.create.sql \i usc.nowa.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_konca, adres.data_konca,
adres.nr_mieszkania oraz malzenstwo.data_konca.
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(50) nazwisko VARCHAR(100) 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.
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.
Data urodzenia nie ma wartości domyślnej — trzeba ją podać. Ograniczenia zabraniają wskazania siebie jako własnego rodzica oraz wskazania tej samej osoby jednocześnie jako matki i ojca.
nr VARCHAR(20) PK id_osoby INTEGER FK → osoba.id data_poczatku DATE data_konca DATE NULL dozwolony
nr — unikatowy numer dowodu. Jest napisem, może więc zawierać litery i zera wiodące. Ograniczenie odrzuca pusty napis i napis złożony wyłącznie ze zwykłych spacji, ale nie narzuca urzędowego formatu numeru.
id_osoby — identyfikator właściciela dowodu, wskazujący osoba.id.
data_poczatku — początek okresu dotyczącego dowodu; w starej wersji kolumna nazywała się data_wydania.
data_konca — 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_poczatku DATE data_konca DATE NULL dozwolony ulica VARCHAR(120) nr_domu VARCHAR(20) nr_mieszkania VARCHAR(20) NULL dozwolony kod_pocztowy CHAR(6) miejscowosc VARCHAR(100) PK (id_osoby, data_poczatku)
id_osoby — osoba, której dotyczy adres.
data_poczatku, data_konca — 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_mieszkania — numer mieszkania jako napis. NULL oznacza, że numeru nie podano; może nie występować w danym adresie.
kod_pocztowy — kod pocztowy, przechowywany w polu CHAR(6). Schemat nie sprawdza wzorca cyfr i łącznika.
miejscowosc — miejscowość 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_poczatku.
Składniki adresu nie należą do klucza głównego.
id_zony INTEGER FK → osoba.id id_meza INTEGER FK → osoba.id data_poczatku DATE data_konca DATE NULL dozwolony PK (id_zony, id_meza, data_poczatku)
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_poczatku — początek małżeństwa.
data_konca — 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.
We wszystkich trzech tabelach historii — dowod,
adres i malzenstwo — przyjęto okres
[data_poczatku, data_konca):
początek należy do okresu, a koniec już do niego nie należy.
NULL w data_konca oznacza okres otwarty.
Ograniczenia wymagają, aby podany koniec był późniejszy od początku.
Data odniesienia podana w skrypcie danych to 1 października 2026 r. Przykładowo adresy obowiązujące tego dnia można wybrać następująco:
SELECT * FROM adres WHERE data_poczatku <= DATE '2026-10-01' AND (data_konca IS NULL OR DATE '2026-10-01' < data_konca);
Samo data_konca IS NULL nie wystarcza do sprawdzenia,
czy okres obejmuje wybrany dzień: trzeba także sprawdzić początek.
Skończony okres również może obejmować ten dzień, jeśli jego koniec
przypada później. Podana data odniesienia nie zmienia się z dniem
uruchomienia skryptu.
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_osoby, 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.
| Stara wersja | Nowa wersja |
|---|---|
dowod.id_wl |
dowod.id_osoby |
dowod.data_wydania |
dowod.data_poczatku |
dowod.data_koniec |
dowod.data_konca |
adres.data_pocz, malzenstwo.data_pocz |
adres.data_poczatku, malzenstwo.data_poczatku |
adres.data_kon, malzenstwo.data_kon |
adres.data_konca, malzenstwo.data_konca |
adres.nr_mieszk |
adres.nr_mieszkania |
adres.kod |
adres.kod_pocztowy |
adres.poczta |
adres.miejscowosc |
dowod.nr INTEGER |
dowod.nr VARCHAR(20) |
Ponadto zwiększono długości pól VARCHAR, usunięto domyślną
datę urodzenia, dodano kontrolę kolejności dat dowodu oraz nazwano
wszystkie ograniczenia poza NOT NULL.
Przenosząc zadania i rozwiązania, należy dostosować nazwy kolumn
oraz traktować numer dowodu jako napis.