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.
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.
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.
id NUMBER(6, 0) PK nazwa VARCHAR(40) UNIQUE jm VARCHAR(10) klasyf VARCHAR(10)
id — identyfikator składnika.nazwa — unikatowa nazwa składnika.jm — jednostka miary ilości składnika, np.
kg, l lub szt..
klasyf — rola składnika w dostarczonym grafie:
podstawowy oznacza składnik niewytwarzany przez żadną
zapisaną czynność (także produkt kupowany gotowy lub nieużywany wpis
katalogowy); posredni jest wynikiem jednej czynności
i wejściem innej; koncowy jest wynikiem, którego nie zużywa
już żadna zapisana czynność. To klasyfikacja w tym zestawie, a nie
podział na produkty surowe i gotowe do spożycia.
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.
id NUMBER(6, 0) PK nazwa VARCHAR(20) opis VARCHAR(40) NULL dozwolony
id — identyfikator czynności.nazwa — nazwa czynności, np. gotowanie, krojenie lub mieszanie.
Nazwy mogą się powtarzać; czynności rozróżniamy po id.
opis — krótki opis wykonania czynności.
NULL oznacza brak opisu.
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ę.
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)
id_cz — identyfikator czynności.id_skl — identyfikator składnika.il_skl — dodatnia ilość składnika, wyrażona w jego
jednostce miary skladniki.jm. Typ pozwala zapisać
trzy cyfry po przecinku.
tryb — rola składnika: IN oznacza wejście
do czynności, a OUT jej wynik. Schemat dopuszcza tylko
te dwie wartości. Przy pominięciu kolumny wartość domyślna to
IN.
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.
przepisy.id_cz wskazuje czynnosci.id.przepisy.id_skl wskazuje skladniki.id.przepisy łączy czynności i składniki w relacji
wiele do wielu, dopisując rolę oraz ilość składnika.
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.
| Tabela | Liczba wierszy |
|---|---|
skladniki | 197 |
czynnosci | 56 |
przepisy | 202 |
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.
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.