Ewidencja ludności — nowa wersja PostgreSQL

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.

Pliki i uruchomienie

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.

Tabele i kolumny

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.

osoba

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

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.

dowod

nr              VARCHAR(20)  PK
id_osoby        INTEGER      FK → osoba.id
data_poczatku   DATE
data_konca      DATE         NULL dozwolony

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.

adres

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)

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.

malzenstwo

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)

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.

Daty i okresy

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.

Powiązania i granice modelu

Dane początkowe

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.

Zmiany względem starej wersji PostgreSQL

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.


Przykładowe schematy baz danych

Powrót do zadań