Ewidencja ludności — stara 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 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.

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.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.

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_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.

osoba

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

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.

dowod

nr              INTEGER  PK
id_wl           INTEGER      FK → osoba.id
data_wydania    DATE
data_koniec     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_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)

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.

malzenstwo

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)

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

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.

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.

Dodatkowe materiały

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ęć.

Sprawdź poprawność XHTML 1.0 Strict


Przykładowe schematy baz danych