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.
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
Wzorzec uruchamia się poleceniem \i rozwiazanie.sql. Po zakończeniu ćwiczenia \i kuchnia.drop.sql usuwa tabele zestawu wraz z danymi.
skladniki(id, nazwa, jm, klasyf) — słownik składników. Każda nazwa jest unikatowa; jm podaje jednostkę miary, a klasyf rolę: podstawowy, posredni lub koncowy.czynnosci(id, nazwa, opis) — czynności kuchenne; identyfikuje je id, nie nazwa.przepisy(id_cz, id_skl, il_skl, tryb) — ilość składnika na jedno wykonanie czynności; 'IN' oznacza wejście, 'OUT' produkt. Kluczem jest trójka (id_cz, id_skl, tryb).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.
Wypisać bez powtórzeń nazwy składników wejściowych czynności o nazwie 'mieszanie salatki'.
Kolumny: nazwa.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Oba zadania są nieobowiązkowe. Każde jest warte 1 punkt dodatkowy. Proszę użyć WITH RECURSIVE.
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.
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.
Pierwotna strona i oryginalne pliki (ZIP). Numeracja, dialekt SQL i zestaw danych w archiwum mogą różnić się od obecnej wersji.