Kolokwium SQL — Lasy — równowaga ekosystemu

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.

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 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.

Schemat w skrócie

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.

Zadania podstawowe 1–10

1. Populacja i najliczniejsze stanowisko

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.

2. Gatunki u podstawy sieci

Wypisać gatunki na dole sieci pokarmowej, czyli takie, które według tabeli pozeranie nie polują na żaden inny gatunek.

Kolumny: nr, nazwa.

3. Drapieżnik lżejszy od ofiary

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.

4. Lasy bez naturalnego wroga

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.

5. Spotkania przy paśniku

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.

6. Masa zwierząt na kilometr kwadratowy

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.

7. Zmiana populacji po roku

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.

8. Gatunki bez odpowiedniego paśnika

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.

9. Czy wystarczy karmy?

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.

10. Łańcuchy z jedną relacją

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.

Zadania dodatkowe 11–12: rekurencja

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

11. Od wilka do dalszych ofiar

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.

12. Najdłuższe łańcuchy w każdym lesie

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.