Książka kucharska — PostgreSQL

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 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 kuchnia.create.sql
\i kuchnia.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 kuchnia.drop.sql usuwa tabele w kolejności: przepisy, czynnosci, skladniki.

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 PostgreSQL. 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       NUMERIC(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       NUMERIC(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    NUMERIC(6, 0)  FK → czynnosci.id
id_skl   NUMERIC(6, 0)  FK → skladniki.id
il_skl   NUMERIC(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

Skrypt danych podaje identyfikatory jawnie. Kolumny id nie mają wartości domyślnej generującej numer. Skrypt tworzący nie zakłada żadnych sekwencji.

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