Kolokwium SQL — Przepisy kulinarne

Bazy danych · PostgreSQL · Autor pierwotnego kolokwium: Krzysztof Stencel.

Kolokwium z 6 listopada 2023 r. Jak rozłożyć przepis na czynności, półprodukty i surowce? Jedna czynność może zużywać kilka składników i wytwarzać więcej niż jeden produkt.

Zadania podstawowe 1–10 są warte po 1 punkcie. Zadania 11–12 z rekurencji są dla chętnych — po 1 punkcie dodatkowym.

Pliki do pobrania

Uruchomienie

W jednym katalogu proszę zapisać skrypty lub rozpakować paczkę studencką. Po uruchomieniu w tym katalogu programu psql i połączeniu z własną bazą proszę sprawdzić schemat i wczytać dane:

SELECT current_user, current_schema();
\i kuchnia.create.sql
\i kuchnia.insert.sql

W nazwie rozwiazania.imie.nazwisko.sql proszę wstawić swoje imię i nazwisko, np. rozwiazania.Jan.Kowalski.sql. Wewnątrz pliku proszę zastąpić kolejne SELECT n AS zadanie; własnymi zapytaniami.

\i rozwiazania.Jan.Kowalski.sql

Po zakończeniu ćwiczenia \i kuchnia.drop.sql usuwa tabele zestawu wraz z danymi.

Schemat w skrócie

Wartości tekstowe w danych zapisano bez polskich znaków. Nazwy w apostrofach w poleceniach odpowiadają dokładnym wartościom w tabelach.

Każde zadanie należy rozwiązać jednym niezależnym zapytaniem. Powinno ono działać także dla innych danych zgodnych ze schematem i założeniami zadania. Jeśli nie wskazano porządku wyników, kolejność wierszy jest dowolna.

Zadania podstawowe 1–10

1. Składniki sałatki

Wypisać bez powtórzeń nazwy składników wejściowych czynności o nazwie 'mieszanie salatki'.

Kolumny: nazwa.

2. Lista zakupów

Wypisać nazwy składników klasy 'podstawowy', które są wejściem co najmniej jednej czynności, lecz nie są wynikiem żadnej. Jest to lista surowców do kupienia.

Kolumny: nazwa.

3. Półprodukty bez przepisu lub odbiorcy

Dla każdego składnika klasy 'posredni' podać nazwę, liczbę czynności wytwarzających go i liczbę czynności zużywających go. Nie pomijać zer. Raport ma pozwolić dostrzec przerwany przepis: składnik pośredni bez producenta lub bez odbiorcy.

Kolumny: nazwa, producenci, odbiorcy.

4. Najwięcej składników wejściowych

Znaleźć czynności o największej liczbie różnych składników wejściowych. Wypisać identyfikator, nazwę i tę liczbę, uwzględnić wszystkie remisy. Czynność bez wejść ma wynik 0.

Kolumny: id, nazwa, liczba_wejsc.

5. Klasy składników każdej czynności

Dla każdej czynności i każdej klasy składników występującej w słowniku skladniki wypisać identyfikator i nazwę czynności, klasę oraz liczbę wejść tej klasy. Dla brakujących kombinacji wypisać zero.

Kolumny: id, nazwa, klasyf, ile.

6. Wspólne składniki trzech zup

Wypisać składniki będące wejściem każdej czynności, która produkuje jedną z trzech zup: 'zupa dyniowa', 'zupa pomidorowa', 'zupa z makaronem'. Szukać tylko bezpośrednich wejść tych czynności. Jeżeli żadna z zup nie ma przepisu, wynik ma być pusty.

Kolumny: nazwa.

7. Dwa kroki od zupy dyniowej

Wypisać bez powtórzeń nazwy składników klasy 'podstawowy' potrzebnych do przygotowania produktu 'zupa dyniowa' bezpośrednio albo za pośrednictwem jednego półproduktu. Uwzględnić wszystkie dostępne przepisy. Nie rozwijać zależności dalej niż o dwa kroki.

Kolumny: nazwa.

8. Najczęściej używana jednostka

Wypisać jednostkę miary występującą w największej liczbie różnych czynności, obok liczbę tych czynności. Jednostka liczy się raz na czynność, niezależnie od tego, czy dotyczy wejść, wyjść, czy obu. Uwzględnić remisy i tylko jednostki faktycznie użyte w przepisach.

Kolumny: jm, czynnosci.

9. Przepisy różniące się jednym składnikiem

Znaleźć pary czynności o podobnej liście surowców: obie mają co najmniej jeden składnik wejściowy, a wejścia pierwszej są dokładnie wejściami drugiej powiększonymi o jeden składnik. Wypisać identyfikatory i nazwy obu czynności (bogatszą najpierw) oraz nazwę dodatkowego składnika. Nie porównywać ilości.

Kolumny: id_bogatszej, bogatsza, id_ubozszej, ubozsza, dodatkowy.

10. Ubytek masy

Znaleźć czynności, dla których wszystkie wejścia i wyjścia mierzy się w kg, a łączna masa produktów jest mniejsza od łącznej masy wejść. Wymagać co najmniej jednego wejścia i jednego wyjścia. Wypisać identyfikator, nazwę i ubytek masy w kg, od największego ubytku.

Kolumny: id, nazwa, ubytek_kg.

Zadania dodatkowe 11–12: rekurencja

Oba zadania są nieobowiązkowe. Każde jest warte 1 punkt dodatkowy. Proszę użyć WITH RECURSIVE.

11. Zupa dyniowa od podstaw

Rozwiązać zadanie o składnikach produktu 'zupa dyniowa' bez ograniczenia głębokości. Idąc od produktu do wejść, uwzględnić wszystkie możliwe przepisy. Każdy osiągalny składnik klasy 'podstawowy' wypisać raz. Zapytanie musi się zakończyć także w razie cyklu.

Kolumny: nazwa.

12. Co zależy od ziarna pszenicy?

Restauracja nie może kupić składnika 'ziarno pszenicy'. Idąc od wejść ku produktom, znaleźć składniki klasy 'koncowy', których przynajmniej jeden łańcuch przygotowania korzysta z tego ziarna. Dla każdego podać nazwę i najmniejszą liczbę kroków od ziarna. Nie powtarzać składnika na jednej ścieżce; inne warianty przepisu mogą nadal być dostępne.

Kolumny: nazwa, kroki.