Ogród zoologiczny

Baza opisuje zwierzęta, pomieszczenia i sektory ogrodu zoologicznego, opiekunów oraz wypłaty za opiekę. Jeden opiekun może zajmować się wieloma sektorami, a jeden sektor może mieć kilku opiekunów. Zestaw służy do ćwiczeń SQL w Oracle. Dane osób, adresy, zwierzęta i wypłaty są fikcyjne.

Nazwę tabeli klatka należy rozumieć szeroko: jej wiersz może oznaczać wybieg, pawilon, basen, akwarium lub inny sektor. To uproszczony model dydaktyczny, nie dokumentacja rzeczywistego zoo.

Pliki i uruchomienie

Skrypty tworzą i wypełniają tabele w schemacie użytkownika. Nie tworzą osobnego konta. Po przejściu do katalogu z plikami w SQL*Plus lub SQLcl można wykonać:

@zoo.create.sql
@zoo.insert.sql

W SQL Developer pliki należy uruchamiać jako skrypty (F5). Plik insert kończy blok PL/SQL ukośnikiem w osobnym wierszu. Pliki danych i opisu są zapisane w UTF-8; klient Oracle powinien odczytywać plik danych w tym kodowaniu.

Skrypt danych zawiera pełny zestaw i wymaga pustych tabel. Należy uruchamiać go w osobnej sesji, bez innych niezatwierdzonych zmian. Udane wstawianie kończy się COMMIT, a błąd powoduje ROLLBACK i ponowne zgłoszenie wyjątku. Aby odtworzyć zestaw, można wykonać kolejno zoo.drop.sql, zoo.create.sql i zoo.insert.sql. DROP TABLE usuwa istniejące dane i nie można go cofnąć poleceniem ROLLBACK w Oracle.

Tabele i kolumny

PK oznacza klucz główny, FK — klucz obcy, a UQ — ograniczenie unikatowości. Identyfikatory są nadawane jawnie; schemat nie zawiera sekwencji ani kolumn z automatyczną numeracją. Poniższe typy odpowiadają niezmienionemu skryptowi create.

opiekun

nr        INTEGER      PK
nazwisko  VARCHAR(20)
adres     VARCHAR(30)

Wszystkie kolumny są obowiązkowe. Nazwisko nie zastępuje identyfikatora: w danych występują na przykład dwie osoby o nazwisku Nowak. Opiekun może istnieć bez przydzielonych sektorów.

klatka

nr        INTEGER      PK
rodzaj    VARCHAR(20)
wielkosc  INTEGER      CHECK (wielkosc > 0)

rodzaj i wielkosc dopuszczają NULL. Ograniczenie CHECK wymaga dodatniej wielkości, gdy została ona podana; samo nie zabrania wartości NULL. Sektor może istnieć bez zwierząt oraz bez opiekunów.

zwierze

nr        INTEGER      PK
imie      VARCHAR(10)   UQ
gatunek   VARCHAR(20)
nrklatki  INTEGER      FK → klatka.nr
kolor     VARCHAR(10)
wymaga    NUMBER(38)

Obowiązkowe są nr, nrklatki i wymaga. imie, gatunek i kolor dopuszczają NULL. Każde zwierzę należy do dokładnie jednego sektora. UNIQUE przy imieniu nie zastępuje NOT NULL: Oracle dopuszcza wiele wierszy z niepodanym imieniem.

Znaczenie liczb wielkosc i wymaga jest w tym zestawie umowne. Przyjmujemy wspólną jednostkę przestrzeni, aby można było sumować wymagania zwierząt i porównywać wynik z wielkością sektora. Schemat nie zapisuje jednostek; wartości nie są metrami kwadratowymi ani normami utrzymywania zwierząt. Przygotowane dane spełniają warunek, że suma wymagań zwierząt w sektorze nie przekracza jego wielkości. Baza nie wymusza tego warunku automatycznie.

opieka

nrklatki   INTEGER  FK → klatka.nr
nropiekuna  INTEGER  FK → opiekun.nr
PK (nrklatki, nropiekuna)

Wiersz oznacza przydzielenie opiekuna do sektora. Obie kolumny są obowiązkowe, ponieważ należą do klucza głównego. Dana para sektor–opiekun może wystąpić najwyżej raz. Tabela realizuje powiązanie wiele do wielu.

Opieka dotyczy sektora, a przez niego wszystkich przypisanych do niego zwierząt. Nie ma osobnej tabeli przydzielającej opiekuna bezpośrednio do zwierzęcia ani dat rozpoczęcia i zakończenia opieki.

wynagrodzenie

nrklatki   INTEGER
nropiekuna  INTEGER
data       DATE         DEFAULT SYSDATE
kwota      NUMBER(6,2)
PK (nrklatki, nropiekuna, data)
FK (nrklatki, nropiekuna) → opieka(nrklatki, nropiekuna)

Klucz obcy obejmuje parę kolumn. Samo istnienie sektora i opiekuna nie wystarcza: musi istnieć ich wspólny wiersz w tabeli opieka. Jeden przydział może mieć wiele wypłat w różnych terminach albo nie mieć żadnej.

Trzy kolumny klucza głównego są obowiązkowe; tylko kwota dopuszcza NULL. Oracle DATE przechowuje także godzinę, minutę i sekundę. Klucz zabrania dwóch wypłat dla tej samej pary w dokładnie tym samym momencie, ale nie zabrania dwóch wypłat w różnych godzinach tego samego dnia. Pominięta data otrzymuje SYSDATE. W skrypcie wszystkie daty podano jawnie, więc ponowne utworzenie zestawu daje takie same dane.

Powiązania i ograniczenia modelu

Dane początkowe

TabelaLiczba wierszy
opiekun14
klatka16
zwierze74
opieka17
wynagrodzenie30

Wypłaty pochodzą z 10 września i 10 października 2026 roku. Piętnaście przydziałów opieki ma po dwie wypłaty, a dwa przydziały nie mają żadnej. Trzech opiekunów nie ma przydziału, a wybieg rezerwowy o numerze 8 nie ma ani zwierząt, ani opiekunów.

Zestaw zawiera wiele zwierząt jednego gatunku, opiekunów o tych samych nazwiskach, sektory z kilkoma opiekunami i opiekunów obsługujących kilka sektorów. Pozwala to ćwiczyć złączenia, złączenia zewnętrzne, grupowanie, agregacje, samozłączenia oraz podzapytania, w tym skorelowane. Podane liczby i przyjęte znaczenie jednostek opisują dane dydaktyczne, a nie dodatkowe ograniczenia zapisane w schemacie.

Sprawdź poprawność XHTML 1.0 Strict


Przykładowe schematy baz danych