Bazy danych · PostgreSQL · Autor pierwotnego kolokwium: Krzysztof Stencel.
W których lasach drapieżnik spotka ofiarę przy tym samym paśniku? Dane łączą występowanie gatunków, ich cechy i sieć pokarmową.
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 las.create.sql \i las.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 las.drop.sql usuwa tabele zestawu wraz z danymi.
lasy(nr, nazwa, rodzaj, wielkosc) — wielkosc to powierzchnia w km².pasniki(nr, karma, wielkosc, nr_lasu) — jeden rodzaj karmy w paśniku; wielkosc to jego pojemność w kg.gatunki(nr, nazwa, wielkosc, futro, karma, rozrodczosc, smiertelnosc) — wielkosc to masa osobnika w kg; karma może być nieznana. Rozrodczość to roczna liczba młodych na samicę, śmiertelność to roczny udział zgonów.wystapienia(nr_lasu, nr_gatunku, ilosc) — jedna dodatnia liczebność dla każdej zapisanej pary las–gatunek.pozeranie(kto, kogo, jak_duzo) — gatunek kto poluje na gatunek kogo. Jak_duzo opisuje umowną intensywność, a nie liczbę ofiar.O ile nie wskazano inaczej, wypisanie lasu lub gatunku oznacza podanie numeru i nazwy.
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.
Dla każdego gatunku podać łączną liczbę osobników we wszystkich lasach oraz numer i nazwę lasu, w którym jest ich najwięcej. Remis rozstrzyga najmniejszy numer lasu. Dla gatunku bez wystąpień podać 0 oraz NULL w kolumnach lasu.
Kolumny: nr, nazwa, razem, nr_lasu, las.
Wypisać gatunki na dole sieci pokarmowej, czyli takie, które według tabeli pozeranie nie polują na żaden inny gatunek.
Kolumny: nr, nazwa.
Wypisać pary drapieżnik–ofiara, w których przeciętny osobnik gatunku drapieżnika jest lżejszy od przeciętnego osobnika gatunku ofiary.
Kolumny: nr_drapieznika, drapieznik, nr_ofiary, ofiara.
Wypisać pary las–gatunek, dla których gatunek występuje w lesie, ale nie występuje w nim żaden jego bezpośredni naturalny wróg.
Kolumny: nr_lasu, las, nr_gatunku, gatunek.
Przy których paśnikach drapieżnik może zastać swoją ofiarę? Wypisać bez powtórzeń numery paśników, gdy w tym samym lesie występują oba gatunki, a karma w paśniku odpowiada ofierze. Drapieżnik nie musi jeść tej karmy.
Kolumny: nr.
Dla lasów, w których szacowana masa wszystkich zwierząt przypadająca na km² przekracza 10 kg/km², podać numer, nazwę i tę wartość. Próg sprawdzić przed zaokrągleniem; wynik wyświetlić do dwóch miejsc po przecinku.
Kolumny: nr, nazwa, kg_na_km2.
W prostym modelu bez polowań połowa osobników początkowej populacji to samice. Dla każdej pary las–obecny gatunek przyjąć, że przy n osobnikach spodziewana liczba urodzeń to n × 0,5 × rozrodczosc, a zgonów n × smiertelnosc. Dla zmian o co najmniej 5% w górę lub w dół podać las, gatunek, początkową liczebność i przewidywaną liczebność po roku. Nie zaokrąglać wartości oczekiwanych.
Kolumny: nr_lasu, las, nr_gatunku, gatunek, obecnie, po_roku.
Wypisać lasy, w których występuje przynajmniej jeden gatunek o znanej karmie, dla którego w tym lesie nie ma paśnika z odpowiednią karmą.
Kolumny: nr, nazwa.
Na jeden dzień przyjąć rację karmy równą 2% masy osobnika i założyć, że każdy paśnik jest pełny. Dla każdej pary las–rodzaj karmy, dla której zapotrzebowanie przewyższa podaż, wypisać osobny wiersz: numer i nazwę lasu, rodzaj karmy i brakującą ilość w kg. Sumować tylko osobniki o znanej karmie; brak stosownego paśnika oznacza podaż zerową.
Kolumny: nr, nazwa, karma, niedobor_kg.
Policzyć łańcuchy złożone z dokładnie dwóch gatunków (jedna relacja „zjada”), w których drapieżnik sam nie jest niczyją ofiarą, a ofiara nie poluje na żaden gatunek. Rozpatrywać pełną sieć, bez podziału na lasy.
Kolumny: liczba_lancuchow.
Oba zadania są nieobowiązkowe. Każde jest warte 1 punkt dodatkowy. Proszę użyć WITH RECURSIVE.
Dla wilka (nr 4) wypisać gatunki osiągalne po jednej lub kilku relacjach „zjada”, bez podziału na lasy. Każdy gatunek podać raz wraz z najmniejszą liczbą relacji potrzebnych do dotarcia do niego. Wilka pominąć, także gdyby w danych powstał cykl.
Kolumny: nr, nazwa, odleglosc.
W każdym lesie znaleźć wszystkie najdłuższe łańcuchy „zjada” złożone wyłącznie z gatunków obecnych w tym lesie. Nie powtarzać gatunku na ścieżce. Wypisać las, tablicę numerów gatunków od drapieżnika do ostatniej ofiary oraz liczbę relacji. Gatunek bez połączeń tworzy łańcuch długości zero; las bez gatunków pominąć.
Kolumny: nr, nazwa, droga, liczba_relacji.