Piątek, 16 listopada 2007
Procedury
1. Wykonaj następujące zadania:
a) Napisz procedurę WstawMiejscowosc podając jako parametr wejściowy nazwę miejscowości. Id_Miejscowości wylicz na podstawie istniejących wpisów (ostatni jaki był + 1).
b) Napisz procedurę WstawPraprzodka podając jako parametry wejściowe pesel, imię, nazwisko, płeć oraz datę i miejsce urodzenia. Zanim wstawisz dane, sprawdź, czy nie ma pracownika o zadanym peselu.
c) Napisz procedurę WstawPotomka podając jako parametry wejściowe pesel, imię, nazwisko, płeć, datę i miejsce urodzenia oraz pesel matki i ojca. Zanim wstawisz dane, sprawdź, czy nie ma pracownika o zadanym peselu oraz, czy są w bazie pesele rodziców.
d) Napisz procedurę RejestrujZgon podając jako parametr wejściowy pesel i datę zgonu. Ta procedura ma rzecz jasna aktualizować rekord o danym peselu wstawiając zadaną na wejściu datę zgonu.
e) Utwórz procedurę PoliczOsoby zwracającą ilość rekordów w tabeli Osoba. Przećwicz 3 sposoby zwracania danych przez procedurę (parametr typu OUTPUT, RETURN i resultset).
f) Napisz procedurę PodajRodziców podając jako parametr wejściowy pesel osoby, której rodziców chcemy znać. Procedura ma wypisywać na ekran imiona i nazwiska rodziców osoby o zadanym peselu.
g) Napisz procedurę PodajRodzeństwo podając jako parametr wejściowy pesel osoby, której rodzeństwo chcemy znać. Procedura ma wypisywać na ekran imiona i nazwiska sióstr i braci osoby o zadanym peselu. Napisz tę procedurę nie używając kursora, a następnie napisz ją, używając kursora. W drugiej wersji (z kursorem) wypisz na ekran informację o tym, czy to siostra, czy brat.
h) Napisz procedurę UzupelnijPlec uzupełniającą pole płeć. Jeśli pole Imie kończy się na literę "a" w pole płeć należy wpisać "kobieta", w przeciwnym przypadku – "mężczyzna". Pamiętaj o konieczności stworzenia kursora z klauzulą "FOR UPDATE".
Zaproponuj schemat bazy danych dla USC, w której będą przechowywane podstawowe dane o obywatelach:
M dane personalne,
M informacje o miejscu i dacie urodzenia,
M informacje o pokrewieństwie,
M informacje o małżeństwach.
M Każdy obywatel może zawrzeć wiele związków małżeńskich, z tym że kolejne małżeństwo można zawrzeć dopiero po ustaniu poprzedniego. W bazie danych powinny być przechowywane także informacje o osobach zmarłych.
Z powyższej specyfikacji udaje nam się ustalić, że potrzebne są encje Obywatel, Zwiazek_Malzenski oraz ewentualnie Miejscowosc (jako encja słownikowa):
Widzimy, że w bazie opartej na takich encjach da się przechować prawie wszystkie żądane informacje. Prawie, ponieważ brakuje jednej istotnej – mianowicie informacji o pokrewieństwie. Gdzie zatem zapamiętywać, kto dla kogo jest ojcem, matką, siostrą, bratem, dziadkiem, babcią, itd.? Tworzyć osobną encję Pokrewienstwo? Spójrzmy:
Ale, czy tu nie ma informacji nadmiarowych? Czy aby na pewno musimy dla każdych dwóch osób połączonych więzami krwi opisywać, jakie to więzy. A jak jest w życiu? Czy aby dowiedzieć się, kto jest kim dla kogo w jakiejś rodzinie, musimy zapytać jaki rodzaj pokrewieństwa łączy każde dwie osoby? A może po uzyskaniu pewnych informacji, potrafimy resztę wywnioskować? Jeśli tak, to zastanówmy się jaka minimalna wiedza jest nam potrzebna. Co wystarczy wiedzieć, aby móc stwierdzić, że pan X jest dziadkiem pani Y, a pan Z bratem pana X? Po krótkim rozważeniu sprawy dochodzimy do wniosku, że musimy tylko i wyłącznie znać matkę i ojca każdej z rozpatrywanych osób (poza tymi rozpoczynającymi hierarchię rodzinną). Wówczas całe drzewo genealogiczne wygenerujemy sobie sami. A zatem sprawę pokrewieństwa można rozwiązać tak:
Na koniec rzućmy okiem na pełny diagram, który po powyższych rozważaniach nie jest już dla nikogo zaskoczeniem.
P:\FTP(Public)\honzik\KBD\ProceduryTSQL.doc
yoquero