Las — PostgreSQL

Baza opisuje lasy, znajdujące się w nich paśniki, gatunki zwierząt, liczebność gatunków w poszczególnych lasach oraz zależności pokarmowe. Jest uproszczonym modelem do ćwiczeń SQL, a nie modelem biologicznym.

Ten opis dotyczy wersji PostgreSQL. Dostępny jest również wariant dla Oracle z tym samym modelem i danymi początkowymi, ale skryptami właściwymi dla tamtego systemu.

Pliki i uruchomienie

Skrypty działają w bieżącym schemacie; nie tworzą osobnej bazy danych ani konta użytkownika. Najpierw należy utworzyć tabele, a następnie wczytać dane.

Po pobraniu plików, w sesji psql:

\set ON_ERROR_STOP on
\i las.create.sql
\i las.insert.sql

Dane należy wczytać do pustych tabel, w osobnej sesji bez innych niezatwierdzonych zmian. Skrypt danych obejmuje import transakcją BEGIN–COMMIT. Skrypt nie usuwa wcześniejszych rekordów; ponowne wczytanie do wypełnionych tabel spowoduje błąd klucza głównego.

Skrypt las.drop.sql usuwa kolejno tabele pozeranie, wystapienia, gatunki, pasniki i lasy wraz z danymi.

Pliki danych zawierają wyłącznie znaki ASCII, więc nazwy w danych są zapisane bez polskich znaków.

Do odtworzenia zestawu można kolejno uruchomić las.drop.sql, las.create.sql i las.insert.sql; usunie to dotychczasowe dane.

Tabele i kolumny

PK oznacza klucz główny, a FK — klucz obcy. Poniżej podano typy użyte w skrypcie PostgreSQL. Jedyną kolumną dopuszczającą NULL jest gatunki.karma. Kolumny kluczy głównych są obowiązkowe również wtedy, gdy nie zapisano przy nich jawnie NOT NULL.

lasy

nr        INTEGER       PK
nazwa     VARCHAR(20)
rodzaj    VARCHAR(10)
wielkosc  NUMERIC(5,1)

Las może mieć wiele paśników i wiele występujących w nim gatunków. Schemat dopuszcza również las bez paśników albo bez zapisanych wystąpień.

pasniki

nr        INTEGER       PK
karma     VARCHAR(10)
wielkosc  NUMERIC(5,1)
nr_lasu   INTEGER       FK → lasy.nr, ON DELETE CASCADE

Każdy paśnik należy do dokładnie jednego lasu. W tym samym lesie może znajdować się kilka paśników, również z jednakową karmą. Rodzaj karmy jest napisem; nie istnieje osobna tabela karm ani klucz obcy łączący karmę paśnika z karmą gatunku.

gatunki

nr            INTEGER       PK
nazwa         VARCHAR(20)
wielkosc      NUMERIC(5,1)
futro         VARCHAR(20)
karma         VARCHAR(10)    NULL dozwolony
rozrodczosc   NUMERIC(5,2)
smiertelnosc  NUMERIC(3,1)

Gatunek może występować w wielu lasach albo nie mieć żadnego zapisanego wystąpienia. Liczebność populacji nie znajduje się w tej tabeli — jest podawana osobno dla każdego lasu w tabeli wystapienia.

wystapienia

nr_lasu     INTEGER  FK → lasy.nr, ON DELETE CASCADE
nr_gatunku  INTEGER  FK → gatunki.nr, ON DELETE CASCADE
ilosc       INTEGER
PK (nr_lasu, nr_gatunku)

Jeden wiersz określa dodatnią liczbę osobników danego gatunku w danym lesie. Para (nr_lasu, nr_gatunku) jest unikatowa. Tabela realizuje związek wiele do wielu między lasami i gatunkami. Brak wiersza oznacza brak zapisanego wystąpienia; nie zapisuje się wiersza z ilosc = 0, ponieważ zabrania tego ograniczenie. Baza nie przechowuje dat obserwacji ani historii zmian populacji.

pozeranie

kto       INTEGER  FK → gatunki.nr, ON DELETE CASCADE
kogo      INTEGER  FK → gatunki.nr, ON DELETE CASCADE
jak_duzo  INTEGER
PK (kto, kogo)

Wiersz (A, B, n) oznacza, że gatunek A pożera gatunek B, z umowną intensywnością n. Relacja jest skierowana: pożerający → pożerany. Nie oznacza to, że B pożera A. Gatunek może mieć wiele rodzajów ofiar i wiele gatunków drapieżników. Warunek kto <> kogo zabrania relacji gatunku z samym sobą, lecz nie wyklucza cykli prowadzących przez kilka gatunków.

Są to zależności między gatunkami, wspólne dla całej bazy, a nie zapisy konkretnych polowań w konkretnym lesie. Wiersz w pozeranie nie wymaga, aby oba gatunki występowały w tym samym lesie. Jeśli zadanie dotyczy drapieżników i ofiar współwystępujących w lesie, trzeba dodatkowo sprawdzić tabelę wystapienia dla obu gatunków.

Powiązania i usuwanie danych

Identyfikatory nr są podawane jawnie w skryptach; nie są automatycznie generowane. Schemat nie wymaga ich dodatniości. Nazwy i opisy karm nie są słownikami z narzuconą listą wartości. Przy łączeniu paśników z wystąpieniami gatunków należy uważać na zwielokrotnienie wierszy: każdy paśnik lasu może połączyć się z każdym wystąpieniem gatunku w tym lesie.

Dane początkowe

Tabela Liczba wierszy
lasy15
pasniki15
gatunki15
wystapienia45
pozeranie16

Zestaw zawiera przykładowe lasy i gatunki, np. jelenia, sarnę, wilka i lisa. Każdy z 15 lasów ma w danych trzy występujące gatunki. Lasy Gaj Ksiezycowy, Mlody Las i Knieja Poludniowa nie mają paśników. Jedynym gatunkiem bez zapisanej karmy jest puchacz. Przykładowy łańcuch zależności to wilk → lis → wiewiórka.

Jednostki i interpretacje parametrów opisane powyżej przyjęto dla tego zestawu danych. Są to wartości fikcyjne, dobrane do ćwiczeń SQL; nie są wynikami pomiarów ani danymi do prognozowania populacji. Wspólna nazwa kolumny wielkosc nie oznacza wspólnej jednostki we wszystkich tabelach: powierzchni lasu nie należy porównywać bezpośrednio z masą zwierzęcia czy pojemnością paśnika.

Liczby rekordów i konkretne wartości opisują dane początkowe, a nie ograniczenia schematu. Zapytania powinny być poprawne również dla innych danych spełniających warunki zadania. Schemat nadal dopuszcza inne rodzaje lasów, nazwy karm, brak wystąpień gatunku oraz cykle w relacji pożerania; ten zestaw nie zawiera takich cykli.


Przykładowe schematy baz danych

Powrót do zadań