Książka kucharska — Oracle

Baza opisuje składniki i czynności, które zużywają jedne składniki, a wytwarzają inne. Składnikiem może być surowiec, półprodukt lub gotowa potrawa. Zestaw służy do ćwiczeń SQL. Ilości są przykładowe i dotyczą jednego wykonania danej czynności. Model nie obejmuje wszystkich strat, odpadów ani szczegółów przygotowania potraw.

Ten opis dotyczy wersji Oracle. Dostępny jest również wariant dla PostgreSQL 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 SQL*Plus lub SQLcl:

@kuchnia.create.sql
@kuchnia.insert.sql

W SQL Developer należy użyć Run Script (F5), aby wykonać cały plik. Końcowy ukośnik / w skrypcie danych wykonuje blok PL/SQL.

Dane należy wczytać do pustych tabel, w osobnej sesji bez innych niezatwierdzonych zmian. Skrypt danych jest jednym blokiem PL/SQL: zatwierdza dane po powodzeniu, a po błędzie wycofuje transakcję i ponownie zgłasza wyjątek. Skrypt nie usuwa wcześniejszych rekordów; ponowne wczytanie do wypełnionych tabel spowoduje błąd klucza głównego.

Skrypt kuchnia.drop.sql usuwa tabele w kolejności: przepisy, czynnosci, skladniki. Usuwa także sekwencje seq_skladniki i seq_czynnosci.

Do odtworzenia zestawu można kolejno uruchomić kuchnia.drop.sql, kuchnia.create.sql i kuchnia.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 Oracle. Jedyną kolumną dopuszczającą NULL jest czynnosci.opis. Kolumny wchodzące w skład klucza głównego są obowiązkowe, nawet gdy nie zapisano przy nich osobno NOT NULL.

skladniki

id       NUMBER(6, 0)  PK
nazwa    VARCHAR(40)    UNIQUE
jm       VARCHAR(10)
klasyf   VARCHAR(10)

Ta sama tabela przechowuje zarówno składniki wejściowe, jak i produkty czynności. Nie ma osobnej tabeli potraw. Jeden składnik może uczestniczyć w wielu czynnościach albo nie występować w żadnym wierszu tabeli przepisy.

Wartości jm i klasyf są napisami. Schemat nie ogranicza ich do wskazanych przykładów ani nie sprawdza, czy klasyfikacja odpowiada roli składnika w czynnościach. Aktualne dane są zgodne z powyższymi regułami klasyfikacji, ale schemat sam tej zgodności nie wymusza.

czynnosci

id       NUMBER(6, 0)  PK
nazwa    VARCHAR(20)
opis     VARCHAR(40)    NULL dozwolony

Czynność może mieć wiele składników wejściowych i wiele produktów. Schemat dopuszcza też czynności bez zapisanych wejść lub wyjść, a nawet bez żadnych powiązań ze składnikami. Nie przechowuje się wykonań czynności w określonym dniu ani przez określoną osobę.

przepisy

id_cz    NUMBER(6, 0)  FK → czynnosci.id
id_skl   NUMBER(6, 0)  FK → skladniki.id
il_skl   NUMBER(8, 3)
tryb     VARCHAR(3)    DEFAULT 'IN'
PK (id_cz, id_skl, tryb)

Jeden wiersz tabeli przepisy nie opisuje całego przepisu. Opisuje jedną ilość jednego składnika w jednej roli przy jednej czynności. Komplet wejść i wyjść danej czynności otrzymujemy, wybierając wszystkie wiersze z jej id_cz.

Klucz (id_cz, id_skl, tryb) pozwala zapisać ten sam składnik przy tej samej czynności jednocześnie jako IN i OUT. Nie pozwala natomiast zapisać dwóch osobnych wierszy dla tej samej trójki. W dostarczonych danych nie ma takich podwójnych ról.

Powiązania i interpretacja

Przykładowe łańcuchy czynności

pieczarki są oczyszczane (czynność 54), krojone (53), łączone z roztopionym tłuszczem (45) i smażone (51). Do podsmażonych pieczarek dodaje się jajka (50), następnie całość jest smażona (49) i doprawiana (48). Wynikiem jest składnik 151: jajecznica z pieczarkami i przyprawami. Tłuszcz przygotowuje osobna czynność 52.

Inny łańcuch prowadzi od zmielenia pszenicy (30), przez przygotowanie ciasta naleśnikowego (19) i smażenie naleśników (20), do nadziewania ich serem (21). Do przechodzenia po takich zależnościach można używać kolejnych złączeń lub zapytań rekurencyjnych. W tych danych graf jest acykliczny; sam schemat nie gwarantuje braku cykli.

Kilka czynności ma więcej niż jeden wynik, np. obieranie jajek (6) daje jaja bez skorupek i skorupki. Obierki i skorupki są w tym modelu produktami końcowymi jako odpady: nie są przeznaczone do spożycia. Wykaz odpadów jest przykładowy, nie stanowi pełnego bilansu masy.

Dane początkowe

Tabela Liczba wierszy
skladniki197
czynnosci56
przepisy202

W tabeli przepisy 142 wiersze mają tryb IN, a 60 — OUT. Każda z 56 czynności ma co najmniej jedno wejście i jedno wyjście. Dwie czynności mają NULL w kolumnie opis (identyfikatory 23 i 31).

W katalogu są 74 składniki nieużywane przez żadną czynność. Pozwala to ćwiczyć złączenia zewnętrzne i wyszukiwanie brakujących powiązań. Klasyfikacja obejmuje 137 składników podstawowych, 28 pośrednich i 32 końcowe. Jednostki to kg, l i szt.; ilości w sztukach są całkowite.

Napisy w skrypcie danych są zapisane bez polskich znaków (ASCII), a ten opis jest w UTF-8. W warunkach porównujących nazwy należy zatem używać np. 'maka', a nie 'mąka'. Wersja uporządkowana zachowuje wszystkie dotychczasowe identyfikatory składników (1–197) i czynności (1–56), lecz zmienia część nazw i powiązań. Wyniki dawnych zadań zależnych od konkretnych danych mogą się zmienić; struktura tabel pozostała taka sama.

Identyfikatory i sekwencje

Skrypt danych podaje identyfikatory jawnie. Kolumny id nie mają wartości domyślnej generującej numer.

Skrypt tworzący zakłada sekwencje seq_skladniki i seq_czynnosci. Nie są one automatycznie powiązane z kolumnami. Skrypt danych pobiera z nich wartości aż do osiągnięcia co najmniej 197 i 56, czyli największych wczytanych identyfikatorów. Następne NEXTVAL dadzą więc numery większe od tych identyfikatorów. Skrypt nie zmienia definicji sekwencji. Zużyte wartości sekwencji nie są przywracane przez ROLLBACK.

Liczby rekordów, identyfikatory i konkretne nazwy opisują dostarczony zestaw, a nie ograniczenia schematu. Zapytania powinny działać także po dodaniu innych danych spełniających warunki zadania.

Sprawdź poprawność XHTML 1.0 Strict


Przykładowe schematy baz danych