WOJSKOWA AKADEMIA TECHNICZNA

WYDZIAŁ CYBERNETYKI

Bazy danych

Sprawozdanie

z projektu

Temat: Baza danych włoskich rodzin mafijnych

Autor projektu: Krzysztof Antczak, IOX3S1

Osoba prowadząca: dr inż. Jarosław Koszela

Data wykonania sprawozdania 14.01.12r.

1. Cel projektu

Celem projektu było przygotowanie bazy danych na wybrany temat. Baza powinna zawierać się z następujących składników:

- minimum 10 tabele

- minimum 3 perspektywy

- minimum 3 wyzwalacze

- minimum 3 wbudowane procedury (w tym jedna z wykorzystaniem kursora i jedna z wykorzystaniem transakcji)

- minimum 3 wbudowane funkcje (w tym jedna z wykorzystaniem kursora i jedna z wykorzystaniem transakcji)

Model konceptualny i fizyczny miał zostać stworzony w programie Power Designer.

System zarządzania bazą danych miał zostać wykonany w komercyjnym programie Sybase Central oraz w bezpłatnym programie (u mnie MySQL).

1. Temat projektu

Przygotowana przeze mnie baza zawiera dane dotyczące funkcjonowania włoskich rodzin mafijnych na terenie Stanów Zjednoczonych.

Zasada działania mafii przypomina zasady działań legalnych firm czy konsorcjów. Niewielka różnica polega na nazewnictwie w strukturze władzy mafii. Wszystkie rodziny mafijne łączy jedna osoba – Capo di Tutti Capi, czyli Ojciec Chrzestny. Jest on wybierany w najsilniejszej rodzinie i ma władzę nad wszystkimi innymi rodzinami, a wszystkie osoby są mu podporządkowane.

Następnymi osobami na szczeblu władzy to Donowie czyli Ojcowie rodzin podporządkowanych głównej rodzinie mafijnej kierowanej przez Ojca Chrzestnego. W każdej z rodzin znajdują się Capo, czyli kapitanowie, którzy mają podwładnych – żołnierzy.

Funkcjonowanie rodziny mafijnej polega na organizowaniu przestępstw i wykonywaniu ich przez żołnierzy. W każdej rodzinie wg przyjętego przeze mnie modelu znajdą się także lekarze i prawnicy, którzy pomagają reszcie członków rodziny w razie problemów zdrowotnych i prawnych.

Struktura bazy ma pozwalać na księgowanie płatności, organizowanie przestępstw, szkoleń i pomocy lekarskiej oraz prawnej dla każdej z rodzin. Ponadto ma zawierać najważniejsze dane o rodzinach (liczba członków, instytucje z którymi są powiązane) i oczywiście strukturę władzy.

1. Model konceptualny

Po przeanalizowaniu założeń jakie ma spełniać baza stworzono w programie Power Designer model konceptualny, który zawierał następujące encje:

Nazwa encji	Komentarz	Atrybuty
Akcja_pomocy	Encja ta zawiera dane dotyczące akcji pomocym które zostały przeprowadzone.	- Id_pomocy - Nazwa_pomocy - Typ_pomocy - Miejsce_udzielania_pomocy
Instytucja	Jest to encja zawierajace informacje o instytucjach powiazanych z rodzinami mafijnymi.	- Nazwa_instytucji - Liczba_członków - Stan - Zasięg_działania(km)
Obiekt_ataku	Obiekt jest celem przeprowadzenia Akcji, np bank, siedziba wrogiej grupy przestępczej.	- Nazwa_obiektu - Odległość - Liczba_członków_ochrony - Liczba_potrzebnych_ludzi - Majątek_obiektu
Osoba	Encja ta zawiera wszystkie osoby, które należą do organizacji przestępczej.	- Id_Osoby - Imie - Nazwisko - Plec - Data urodzenia - Funkcja - Pensja - Liczba_dokonanych_przestępstw
Przestępstwo	Encja ta zawiera w sobie informacje na temat akcji, np atak na bank, sutenerstwo w jakimś mieście, czy zabójstwo.	- Id_przestępstwa - Nazwa_przestępstwa - Data_rozpoczęcia_prz - Data_zakończenia_prz
Płatność	Znajdują się w niej dane dotyczące wszystkich wpłat i wypłat organizacji przestępczej	- Id_płatności - Rodzaj_płatności - Kwota - Data_płatności
Rodzina mafijna	Encja zawierająca informacje na temat rodzin mafijnych oraz stosunków między nimi.	- Nazwa_rodziny - Miejsce_aktywności - Liczba_członków
Sprzęt	Encja posiadająca zbiór wszyskich pojazdów i przedmiotów, które mogą być używane podczas działalności przestępczej.	- Typ_sprzętu - Nazwa_sprzętu - Koszt_użycia
Szkolenie	Encja ta opisuje wydarzenie, które jest szkoleniem, zawiera informacje o jego temacie, liczbie uczestników czy też miejscu spotkania.	- Temat_szkolenia - Maksymalna_ilość_uczestników - Miejsce_szkolenia - Data_ropoczęcia_szk - Data_zakończenia_szk

Powiązania między encjami są następujące:

Nazwa encji	Komentarz	Połączenie
Dochód z przestępstwa	Źródłem dochodów są przestępstwa. Relacja ta pokazuje z jakiego przestępstwa pochodzi wpłata.	Płatność – Przestępstwo n…1
Powiązane instytucje	Każda rodzina mafijna ma wpływy w różnych sferach np instutucja polityczna, policja.	Instytucja - Rodzina_mafijna n…0
Przynależność	Rodzinę mafijną tworzą osoby do niej należące.	Rodzina_mafijna – Osoba 1…n
Relacja wrogości	Relacja ta pokazuje, które rodziny są sobie wrogie..	Rodzina_mafijna - Rodzina_mafijna n…n
Struktura_władzy	Mafia charakteryzuje się zorganizowaną strukturą, każda osoba ma swojego przełożonego.	Osoba – Osoba 0…n
Uczestnik przestępstwa	Relacja pokazująca w jakich przestępstwach uczestniczyła dana osoba.	Osoba – Przestępstwo n…n
Uczestnik szkolenia	Relacja ta określa jaka osoba uczestniczyła w jakim szkoleniu.	Szkolenie – Osoba n…n
Wypłaty	Osobom, które uczesniczyły w przestępstwie przyznawane są wypłaty.	Osoba – Płatność 1…n

5. Generowanie bazy w programie Sybase

Kolejnym krokiem tworzenia bazy było wygenerowanie w programie Power Designer skryptu SQL do programu SQL Anywhere.

Następnie utworzyłem nową – pustą bazę w Sybase.

Parametry bazy ustalone w kreatorze to:

- Rozmiar strony: 4 KB

- Transaction Log: Yes

- Mirror Log: No

- Enable encrytpion: Yes

- Encryption type: Simple

- Login użytkownika: DBA

- Hasło: sql

Po stworzeniu nowej bazy uruchomiłem skrypt w SQL Interactive, który wstawił tabele, atrybuty i rozdzielił klucze, w sposób jaki ustaliłem w Power Designerze.

6. Perspektywy

W mojej bazie danych stworzyłem trzy perspektywy, które dostarczają niebanalnych informacji na podstawie danych z bazy.

1. „Dwudziestu Najbardziej Doświadczonych”

Komentarz:

Widok ukazujący dwudziestu członków rodzin mafijnych, którzy są najbardziej doświadczeni pod względem czasu jaki spędzili na przestępstwach (obliczanego na podstawie tabeli Uczestnik_przestępstwa i Przestępstwo), wypisywana jest także liczba szkoleń w jakich uczestniczyli.

Kod:

ALTER VIEW "DBA"."DwudziestuNajbardziejDoswiadczonych"()

AS

SELECT TOP 20 O.Imie, O.Nazwisko, O.Nazwa_rodziny, ZamienNaDniGodzMin(MAX(CzasNaPrzestepstwach(UP.Id_Osoby)) AS Łączny_czas_przestępstw,

count(Szkolenia.Liczba_szkoleń) AS Liczba_szkoleń

FROM Osoba O, Uczestnik_przestępstwa UP,

(SELECT US.Id_Osoby, COUNT (*) AS Liczba_szkoleń

FROM Uczestnik_szkolenia US, Uczestnik_przestępstwa UP

GROUP BY US.Id_Osoby

UNION

SELECT UP.Id_Osoby, NULL

FROM Uczestnik_przestępstwa UP

GROUP BY UP.Id_Osoby) AS Szkolenia

WHERE UP.Id_Osoby=O.Id_Osoby AND Szkolenia.Id_Osoby=UP.Id_Osoby

GROUP BY UP.Id_Osoby, Szkolenia.Id_Osoby, O.Imie, O.Nazwisko, O.Nazwa_rodziny

ORDER BY Łączny_czas_przestępstw DESC

Przykład działania:

1. „Informacje Dotyczące Rodzin”

Komentarz:

Widok ten pokazuje liczbę wrogów każdej rodziny mafijnej na podstawie tabeli Relacja_wrogości (biorąc pod uwagę że relacje są symetryczne), liczbę przestępstw wykonanych przez członków tej rodziny (na podstawie danych z tabeli Uczestnik_przestępstwa) oraz majątek każdej z rodziny na podstawie danych z tabeli Płatność.

Kod:

ALTER VIEW "DBA"."InformacjeDotyczaceRodzin"()

AS

SELECT Wrogowie.Nazwa_rodziny, Wrogowie.Liczba_wrogów, Przestępstwa.Liczba_przestępstw, Majątki_rodzin.Majątek

FROM (SELECT Intersekcja.Nazwa_rodziny, count(Intersekcja.Nazwa_wrogiej_rodziny) AS Liczba_wrogów

FROM (SELECT R.Nazwa_rodziny, R.Nazwa_wrogiej_rodziny

FROM Relacja_wrogości R

UNION

SELECT R.Nazwa_wrogiej_rodziny, R.Nazwa_rodziny

FROM Relacja_wrogości R

UNION

SELECT R.Nazwa_rodziny,NULL

FROM Rodzina_mafijna R) AS Intersekcja

GROUP BY Intersekcja.Nazwa_rodziny) AS Wrogowie,

(SELECT R.Nazwa_rodziny, MAX(O.Liczba_przestępstw_pomocy) AS Liczba_przestępstw

FROM Rodzina_mafijna R, Osoba O

WHERE O.Nazwa_rodziny=R.Nazwa_rodziny AND O.Funkcja='Żołnierz'

GROUP BY R.Nazwa_rodziny) AS Przestępstwa,

(SELECT MIN.Nazwa_rodziny, (SUM(PLU.DOD)/2-SUM(MIN.ODE)/2) AS Majątek

FROM (SELECT OS.Nazwa_rodziny, SUM(PL.Kwota) AS ODE

FROM Płatność PL, Osoba OS

WHERE (PL.Rodzaj_płatności='Premia' OR PL.Rodzaj_płatności='Wypłata')

AND PL.Id_Osoby=OS.Id_Osoby

GROUP BY OS.Nazwa_rodziny

UNION

SELECT RO.Nazwa_rodziny, 0

FROM Rodzina_mafijna RO) AS MIN,

(SELECT OS.Nazwa_rodziny, SUM(PL.Kwota) AS DOD

FROM Płatność PL, Osoba OS

WHERE (PL.Rodzaj_płatności='Wpłata')

AND PL.Id_Osoby=OS.Id_Osoby

GROUP BY OS.Nazwa_rodziny

UNION

SELECT RO.Nazwa_rodziny, 0

FROM Rodzina_mafijna RO) AS PLU

WHERE MIN.Nazwa_rodziny=PLU.Nazwa_rodziny

GROUP BY MIN.Nazwa_rodziny) AS Majątki_rodzin

WHERE Wrogowie.Nazwa_rodziny=Przestępstwa.Nazwa_rodziny AND Wrogowie.Nazwa_rodziny=Majątki_rodzin.Nazwa_rodziny

Przykład działania:

1. 
   „Dwudziestu Najbardziej Doświadczonych”

Komentarz:

Widok ukazujący dwudziestu członków rodzin mafijnych, którzy są najbardziej doświadczeni pod względem czasu jaki spędzili na przestępstwach (obliczanego na podstawie tabeli Uczestnik_przestępstwa i Przestępstwo), wypisywana jest także liczba szkoleń w jakich uczestniczyli.

Kod:

ALTER VIEW "DBA"."PiecNajlepszychPotencjanychPrzestepstw"()

AS

SELECT BestPlace.Nazwa_obiektu, BestPlace.Zysk AS Majątek_obiektu, BestTeam.Nazwa_rodziny, BestTeam.Drużyna_kapitana,

BestTeam.Łączne_Pensje, BestPlace.Zysk-Srednia.Wart-BestTeam.Łączne_Pensje AS Przewidywany_dochód

FROM (SELECT TOP 5 ROW_NUMBER() OVER (ORDER BY Łączne_Pensje) AS TOP_5, Pod.Nazwa_rodziny,

(O.Imie +' '+ O.Nazwisko) AS Drużyna_kapitana, (Pod.Suma+O.Pensja) AS Łączne_Pensje

FROM Osoba O,

(SELECT O.Nazwa_rodziny, O.Id_przełożonego, SUM(O.Pensja) AS Suma

FROM Osoba O

WHERE O.Funkcja='Żołnierz'

GROUP BY O.Nazwa_rodziny, O.Id_przełożonego

ORDER BY Suma DESC) AS Pod

WHERE O.Id_Osoby=Pod.Id_przełożonego

GROUP BY Pod.Nazwa_rodziny, O.Imie, O.Nazwisko, O.Pensja, Pod.Suma

ORDER BY Łączne_Pensje) AS BestTeam,

(SELECT TOP 5 ROW_NUMBER() OVER(ORDER BY Zysk DESC) AS TOP_5, O.Nazwa_obiektu, O.Zysk

FROM Obiekt_ataku O

WHERE NOT EXISTS (SELECT U.Nazwa_obiektu

FROM Użyty_sprzęt U

WHERE O.Nazwa_obiektu=U.Nazwa_obiektu)

ORDER BY O.Zysk DESC) AS BestPlace,

(SELECT 'Grupa' AS Grupa, SUM(U.Liczba_sprzętu * S.Koszt_użycia) /COUNT(DISTINCT U.Nazwa_obiektu) AS Wart

FROM Użyty_Sprzęt U, Sprzęt S

WHERE U.Nazwa_sprzętu=S.Nazwa_sprzętu

GROUP BY Grupa) AS Srednia

WHERE BestPlace.TOP_5=BestTeam.TOP_5

Przykład działania:

7. Wyzwalacze

1. „Członkowie Rodziny”

Komentarz:

Trigger ten uruchomi się po wstawieniu nowego wiersza, a także edycji lub usunięciu wiersza z tabeli Osoba. Sprawdzi on jakiej rodziny dotyczy zmiana kolumny Nazwa_rodziny i w zależności czy zmieniamy przynależność istniejącej do rodziny dla istniejącej już osoby, czy dodamy nową osobę lub ją usuwamy zwiększy lub zmniejszy liczbę członków tylko rodzin których dotyczą zmiany.

Kod:

ALTER TRIGGER "CzlonkowieRodziny" AFTER INSERT, DELETE, UPDATE

ORDER 1 ON "DBA"."Osoba"

REFERENCING NEW AS PoZmianie OLD AS PrzedZmiana

FOR EACH ROW

BEGIN

DECLARE @Nazwa_rodziny varchar(20);

DECLARE @LC1 int;

DECLARE @LC2 int;

SET @LC1=(SELECT count(*)

FROM Osoba O

WHERE O.Nazwa_rodziny=PoZmianie.Nazwa_rodziny);

SET @LC2=(SELECT count(*)

FROM Osoba O

WHERE O.Nazwa_rodziny=PrzedZmiana.Nazwa_rodziny);

UPDATE Rodzina_mafijna RO SET Liczba_członków = @LC1 WHERE RO.Nazwa_rodziny=PoZmianie.Nazwa_rodziny;

UPDATE Rodzina_mafijna RO SET Liczba_członków = @LC2 WHERE RO.Nazwa_rodziny=PrzedZmiana.Nazwa_rodziny;

END

Przykład działania:

Przed dodaniem osoby do rodziny Zaluchi

Po dodaniem osoby do do rodziny Zaluchi

1. „Liczba Pomocy”

Komentarz:

Trigger sprawdzi jakiej osoby dotyczy usunięcie wiersza, dodanie nowego lub edycja istniejącego i uaktualni w tabeli Osoba liczbę pomocy dla Osoby której ta akcja dotyczy.

Kod:

ALTER TRIGGER "LiczbaPomocy" AFTER INSERT, DELETE, UPDATE

ORDER 1 ON "DBA"."Pomoc"

REFERENCING NEW AS Przed

FOR EACH ROW

BEGIN

DECLARE @LiczbaPomocy INT;

SET @LiczbaPomocy = (SELECT count(P.Id_akcji_pomocy)

FROM Pomoc P,

WHERE P.Id_udzielającego = Przed.Id_udzielającego);

UPDATE Osoba O SET Liczba_przestępstw_pomocy = @LiczbaPomocy

WHERE O.Id_Osoby=Przed.Id_udzielającego

END

Przykład działania:

Przed dodaniem pomocy do tabeli Pomoc:

Po dodaniu pomocy do tabeli Pomoc:

1. „Liczba Przestępstw”

Komentarz:

Trigger LiczbaPrzestepstw na podstawie osoby której dotyczy akcja (edycja, wstawienie lub usunięcie) zmieni dane kolumny Liczba_przestępstw_pomocy wyliczając tę liczbę na podstawie danych z tabeli Uczestnik_przestępstwa tylko tej osobie.

Kod:

ALTER TRIGGER "LiczbaPrzestepstw" AFTER INSERT, DELETE, UPDATE

ORDER 1 ON "DBA"."Uczestnik_przestępstwa"

REFERENCING NEW AS Po

FOR EACH ROW

BEGIN

DECLARE @LiczbaPrzestepstw INT;

SET @LiczbaPrzestepstw = (SELECT count(P.Id_przestępstwa)

FROM Uczestnik_przestępstwa P,

WHERE P.Id_Osoby = Po.Id_Osoby);

UPDATE Osoba O SET Liczba_przestępstw_pomocy = @LiczbaPrzestepstw

WHERE O.Id_Osoby=Po.Id_Osoby

END

Przykład działania:

Przed dodaniem Id osoby do tabeli Uczestnik_przestępstwa:

Po dodaniu Id osoby do tabeli Uczestnik_przestępstwa:

1. „Wstaw Datę Oraz Id”

Komentarz:

Trigger ten uaktywni się przy wstawianiu nowego wiersza do tabeli Płatność i automatycznie uzupełni kolumnę Id_przestępstwa a także wstawi aktualną datę do kolumny Data_płatności.

Kod:

ALTER TRIGGER "WstawDateOrazId" AFTER INSERT

ORDER 1 ON "DBA"."Płatność"

FOR EACH ROW

BEGIN

DECLARE @NajId INT;

DECLARE @Id_płatności VARCHAR(5);

DECLARE @ActualData DATETIME;

SET @NajId=(SELECT TOP 1 CAST(SUBSTR(P.Id_płatności,2,5) as int) as Ide

FROM Płatność P

ORDER BY Ide DESC)+1;

SET @Id_płatności='#';

SET @ActualData=getdate();

UPDATE Płatność SET Id_płatności=(@Id_płatności+CAST(@NajId AS VARCHAR(5)))

WHERE Id_płatności='';

UPDATE Płatność SET Data_płatności=@ActualData

WHERE Data_płatności IS NULL;

END

Przykład działania:

Przed potwierdzeniem płatności:

Po potwierdzeniu płatności:

8. Funkcje

1. „Czas Na Przestępstwach”

Komentarz:

Funkcja CzasNaPrzestępstwach przyjmuje jak argument Id_Osoby, i zwraca czas, jaki spędziła ona na przestępstwach (obliczając te dane na podstawie tabeli Uczestnik_przestępstwa i Przestępstwo). Wynik jest podawany w minutach (aby był czytelniejszy należy użyć funkcji ZamienNaDniGodzMin).

Kod:

ALTER FUNCTION "DBA"."CzasNaPrzestepstwach"(IN @Id_Osoby INT)

RETURNS INT

AS

BEGIN

DECLARE @DataRozp DATETIME

DECLARE @DataZako DATETIME

DECLARE @Dni INT

DECLARE @Godzin INT

DECLARE @Minut INT

SET @Dni=0

SET @Godzin=0

SET @Minut=0

DECLARE @DniB VARCHAR(30)

DECLARE @GodzinB VARCHAR(30)

DECLARE @MinutB VARCHAR(30)

DECLARE @Zwroc INT

DECLARE kursor CURSOR FOR SELECT P.Data_rozpoczęcia_prz, P.Data_zakończenia_prz

FROM Przestępstwo P, Uczestnik_przestępstwa UP

WHERE P.Id_przestępstwa=UP.Id_przestępstwa

AND UP.Id_Osoby=@Id_Osoby

OPEN kursor

FETCH kursor INTO @DataRozp, @DataZako

WHILE @@FETCH_STATUS = 0

BEGIN

SET @GodzinB = HOUR(CONVERT(TIME, @DataZako, 108))-HOUR(CONVERT(TIME, @DataRozp, 108))

SET @DniB = DATE(@DataZako)-DATE(@DataRozp)

SET @MinutB = MINUTE(CONVERT(TIME, @DataZako, 108))-MINUTE(CONVERT(TIME, @DataRozp, 108))

IF @MinutB<0

BEGIN

SET @GodzinB=@GodzinB-1

SET @MinutB=60+@MinutB

END

IF @GodzinB<0

BEGIN

SET @GodzinB=24+@GodzinB

SET @DniB=@DniB-1

END

SET @Dni = @Dni+CAST(@DniB as INT)

SET @Godzin = @Godzin+CAST(@GodzinB as INT)

SET @Minut = @Minut+CAST(@MinutB as INT)

FETCH kursor INTO @DataRozp, @DataZako

END

CLOSE kursor

DEALLOCATE kursor

IF @Godzin>23

BEGIN

SET @Dni=@Dni+FLOOR(@Godzin/24)

SET @Godzin=@Godzin%24

END

IF @Minut>59

BEGIN

SET @Godzin=@Godzin+FLOOR(@Minut/60)

SET @Minut=@Minut%60

END

SET @Zwroc=@Dni*60*24+@Godzin*60+@Minut

RETURN @Zwroc

END

Przykład działania:

Po wykonaniu funkcji dla osoby o Id 2 zwróciła ona następującą wartość:

1. „ZamienNaDniGodzMin”

Komentarz:

Funkcja ta po wprowadzeniu okresu czasu będącego liczbą minut, zwróci ten okres czasu w postaci dni, godzin i minut. Funkcja ta jest wykorzystywana do konwersji danych zwróconych funkcją Czas Na Przestępstwach.

Kod:

ALTER FUNCTION "DBA"."ZamienNaDniGodzMin"(IN @Minuty INT)

RETURNS VARCHAR(50)

DETERMINISTIC

AS

BEGIN

DECLARE @Dni INT

DECLARE @Godziny INT

DECLARE @Zwroc VARCHAR(50)

SET @Godziny=FLOOR(@Minuty/60)

SET @Minuty=@Minuty%60

SET @Dni=FLOOR(@Godziny/24)

SET @Godziny=@Godziny%24

SET @Zwroc=CAST(@Dni as VARCHAR(30))+' dni '+

CAST(@Godziny as VARCHAR(30))+' godzin '+

CAST(@Minuty as VARCHAR(30))+' minut '

RETURN @Zwroc

END

Przykład działania:

Po wykonaniu funkcji dla argumentu o wartości 12345 funkcja zwróciła następującą wartość:

W przypadku użycia tej funkcji z przednią można w czytelniejszy sposób interpretować wynik:

1. „DochodZPrzestepstwa”

Komentarz:

Funkcja DochodZPrzestepstwa przyjmuje jak argument Id_przestępstwa, i zwraca dochód z niego (będący różnicą majątku obiektu którego te przestępstwo dotyczyło i kosztów użycia sprzętów (z tabeli Sprzęt i Użyty_sprzęt) podczas tego przestępstwa).

Kod:

ALTER FUNCTION "DBA"."DochodZPrzestepstwa"(IN @Id_przestepstwa INT)

RETURNS INT

DETERMINISTIC

AS

BEGIN

DECLARE @Dochod INT

DECLARE @Zysk INT

DECLARE @Wydatki INT

SET @Zysk =(SELECT OA.Zysk

FROM Obiekt_ataku OA, Użyty_sprzęt UA

WHERE UA.Nazwa_obiektu = OA.nazwa_obiektu

AND Id_przestępstwa=@Id_przestepstwa

GROUP BY Id_przestępstwa,OA.Zysk)

SET @Wydatki=(SELECT SUM(S.Koszt_użycia*U.Liczba_sprzętu)

FROM Sprzęt S, Użyty_sprzęt U

WHERE S.Nazwa_sprzętu=U.Nazwa_sprzętu

AND Id_przestępstwa=@Id_przestepstwa)

SET @Dochod=@Zysk-@Wydatki

RETURN @Dochod

END

Przykład działania:

Po wykonaniu funkcji dla płatności o Id 2 funkcja zwróciła następującą kwotę:

1. 
   „PremiaZaPrzestepstwa”

Komentarz:

Funkcja PremiaZaPrzestepstwa daje premię osobie, która spędziła najwięcej czasu na przestępstwach. Dane dotyczące tego czasu obliczane są funkcją CzasNaPrzestepstwach (na podstawie tabel Uczestnik_przestępstwa i Przestępstwo). Funkcja ta jest transakcją, która zostanie odrzucona jeśli wprowadzona kwota premii będzie niepoprawna (niedodatnia lub przekraczająca kwotę 2000), a także jeśli wystąpi jakikolwiek błąd podczas wykonywania tej transakcji lub gdy poprzednie transakcje nie zostaną zakończone.

Kod:

ALTER FUNCTION "DBA"."PremiaZaPrzestepstwa"(IN @Premia INT)

RETURNS VARCHAR(50)

AS

BEGIN

DECLARE @Zwroc VARCHAR(50)

DECLARE @Id_Osoby VARCHAR(5)

DECLARE @Minuty INT

DECLARE @Id_płatności VARCHAR(5)

DECLARE Ten CURSOR FOR SELECT TOP 1 UP.Id_Osoby, CzasNaPrzestepstwach(UP.Id_Osoby) as Czas

FROM Przestępstwo P, Uczestnik_przestępstwa UP

WHERE P.Id_przestępstwa=UP.Id_przestępstwa

ORDER BY Czas DESC

SET @Id_płatności='#'+CAST((SELECT TOP 1 CAST(SUBSTR(P.Id_płatności,2,5) as int) as Ide

FROM Płatność P

ORDER BY Ide DESC)+1 AS VARCHAR(5))

OPEN Ten

BEGIN TRANSACTION DodajPlatnosc

FETCH Ten INTO @Id_Osoby, @Minuty

INSERT INTO Płatność (Id_płatności, Id_przestępstwa, Id_Osoby, Rodzaj_płatności, Kwota, Data_płatności)

VALUES(@Id_płatności, NULL, @Id_Osoby, 'Premia', @Premia, GETDATE())

SET @Zwroc='Osoba '+@Id_Osoby+' : '+ZamienNaDniGodzMin(@Minuty)

CLOSE Ten

DEALLOCATE Ten

IF @@TRANCOUNT > 1 OR @@ERROR<>0 OR @Premia>2000 OR @Premia<0

BEGIN

ROLLBACK TRANSACTION DodajPlatnosc

SET @Zwroc=NULL

PRINT 'Transakcja zostala odrzucona, poniewaz poprzednia nie zostala zakonczona, bardz wystapil inny blad.'

END ELSE

BEGIN

COMMIT TRANSACTION DodajPlatnosc

PRINT 'Transakcja zostala przyjeta.'

END

RETURN @Zwroc

END

Przykład działania:

1. „WypiszNajbogatszeRodziny”

Komentarz:

Funkcja WypiszNajbogatszeRodziny przyjmuje jako argument liczbę mówiącą ile najbogatszych rodzin ma wypisać, (majątek każdej z nich zostanie obliczony na podstawie danych z tabeli Płatność) wynik zostanie zwrócony w postaci zmiennej typu TEXT w postaci |NAZWA_RODZINY, MAJĄTEK|.

Kod:

ALTER FUNCTION "DBA"."WypiszNajbogatszeRodziny"(IN @Ile_wypisac INT)

RETURNS TEXT

AS

BEGIN

DECLARE KlMa CURSOR FOR SELECT MIN.Nazwa_rodziny, SUM(PLU.DOD)/2-SUM(MIN.ODE)/2) AS Majątek

FROM (SELECT OS.Nazwa_rodziny, SUM(PL.Kwota) AS ODE

FROM Płatność PL, Osoba OS

WHERE (PL.Rodzaj_płatności='Premia'

OR PL.Rodzaj_płatności='Wypłata')

AND PL.Id_Osoby=OS.Id_Osoby

GROUP BY OS.Nazwa_rodziny

UNION

SELECT RO.Nazwa_rodziny, 0

FROM Rodzina_mafijna RO) AS MIN,

(SELECT OS.Nazwa_rodziny, SUM(PL.Kwota) AS DOD

FROM Płatność PL, Osoba OS

WHERE (PL.Rodzaj_płatności='Wpłata')

AND PL.Id_Osoby=OS.Id_Osoby

GROUP BY OS.Nazwa_rodziny

UNION

SELECT RO.Nazwa_rodziny, 0

FROM Rodzina_mafijna RO) AS PLU

WHERE MIN.Nazwa_rodziny=PLU.Nazwa_rodziny

GROUP BY MIN.Nazwa_rodziny

ORDER BY Majątek DESC

DECLARE @Wypisz TEXT='|'

DECLARE @BuforMajatek VARCHAR(30)

DECLARE @BuforNazwaR VARCHAR(30)

OPEN KlMa

FETCH KlMa INTO @BuforNazwaR, @BuforMajatek

WHILE @@FETCH_STATUS =0 AND @Ile_wypisac>0

BEGIN

SET @Wypisz=@Wypisz+@BuforNazwaR+' '+@BuforMajatek+'$| '

SET @Ile_wypisac=@Ile_wypisac-1

FETCH KlMa INTO @BuforNazwaR, @BuforMajatek

END

CLOSE KlMa

DEALLOCATE kursor

RETURN @Wypisz

END

Przykład działania:

9. Procedury

1. „DodajPodwladnych”

Komentarz:

Procedura ta po podaniu Id_Osoby oraz Id_przestępstwa dodaje do tabeli Uczestnik_przestępstwa Id tej osoby oraz Id jej podwładnych. Dzięki temu o wiele szybciej można dodać umieścić dane o drużynie, która uczestniczyła w przestępstwie o wprowadzonym Id. Procedura ta wykorzystuje kursor, który odwołuje się do Id_podwładnych danej osoby.

Kod:

ALTER PROCEDURE "DBA"."DodajPodwładnych"

(IN @Id_przestepstwa char(20), @Id_przelozonego char(20))

AS

BEGIN

DECLARE @Id_podwladnego varchar(20)

DECLARE kursor CURSOR FOR SELECT OS.Id_Osoby

FROM Osoba OS

WHERE OS.Id_Przełożonego=@Id_przelozonego

INSERT INTO Uczestnik_przestępstwa (Id_przestępstwa, Id_Osoby)

VALUES(@Id_przestępstwa, @Id_przelozonego)

OPEN kursor

FETCH kursor INTO @Id_podwladnego

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO Uczestnik_przestępstwa (Id_przestępstwa, Id_Osoby)

VALUES(@Id_przestępstwa, @Id_podwladnego)

FETCH kursor INTO @Id_podwladnego

END

CLOSE kursor

DEALLOCATE kursor

END

Przykład działania:

Po wykonaniu procedury dla osoby o Id 3 i przestępstwa o Id 6 do tabeli Uczestnik_przestępstwa dodane zostały następujące wiersze:

1. „PodwyzkaZaSzkolenia”

Komentarz:

Procedura PodwyzkaZaSzkolenie przyjmuje jako argument (wartość podwyżki, minimalną liczbę szkoleń, maksymalną sumę podwyżki). Podczas wykonywania procedury na podstawie danych z tabeli Uczestnik_szkolenia zostanie wyliczona liczba szkoleń w których uczestniczyły osoby, których Id występuje w tej tabeli). Następnie jeśli liczba szkoleń danej osoby jest większa niż zadane minimum dostanie ona podwyżkę za każde szkolenie, w którym uczestniczyła. Procedura ta jest transakcją, która zostanie wycofana, jeśli suma wartości wszystkich podwyżek wyniesie określone maksimum, gdy wystąpi błąd podczas transakcji lub gdy poprzednie transakcje nie zostaną zakończone. Jeśli transakcja spełni te wymogi to zostanie zaakceptowana.

Kod:

ALTER PROCEDURE "DBA"."PodwyzkaZaSzkolenie"(@Podwyzka INT, @MinSzkolen INT, @MaxSumaPodw INT)

AS

BEGIN

DECLARE @Id_Osoby VARCHAR(20)

DECLARE @LiSzk INT

DECLARE @SumaPodw INT=0

DECLARE kursor CURSOR FOR SELECT US.Id_Osoby, COUNT (*) AS Liczba_szkoleń

FROM Uczestnik_szkolenia US

GROUP BY US.Id_Osoby

HAVING Liczba_szkoleń>=@MinSzkolen

SET @LiSzk=0

SET @SumaPodw=0

OPEN kursor

BEGIN TRANSACTION PodwyzPensje

FETCH kursor INTO @Id_Osoby,@LiSzk

WHILE @@FETCH_STATUS=0

BEGIN

UPDATE Osoba O SET Pensja = Pensja+@Podwyzka*@LiSzk WHERE O.Id_Osoby=@Id_Osoby

SET @SumaPodw=@SumaPodw+@Podwyzka*@LiSzk

FETCH kursor INTO @Id_Osoby,@LiSzk

END

CLOSE kursor

DEALLOCATE kursor

IF @@TRANCOUNT>1 OR @@ERROR<>0 OR @SumaPodw>@MaxSumaPodw OR @MaxSumaPodw<0 OR @Podwyzka<0

BEGIN

ROLLBACK TRANSACTION PodwyzPensje

PRINT 'Transakcja zostala odrzucona, poniewaz poprzednia nie zostala zakonczona,

wartosc podwyzki przekroczyla maksimum, wartosc podwyzki nie byla poprawna

lub wystapil inny blad.'

END ELSE

BEGIN

COMMIT TRANSACTION PodwyzPensje

PRINT 'Transakcja zostala przyjeta.'

END

//Jesli suma podwyzek bedzie wieksza od ustalonego maksimum to transakcja zostanie wycofana

END

Przykład działania:

Po wykonaniu procedury dla osoby o Id 3 i przestępstwa o Id 6 do tabeli Uczestnik_przestępstwa dodane zostały następujące wiersze:

Wychodzi na to, że Pan Salco uczestniczył w trzech szkoleniach.

1. „PodwyzkaZaSzkolenia”

Komentarz:

Procedura WyplataZaPrzestepstwo przyjmuje jako argument Id_przestępstwa, z którego mają zostać dokonane wypłaty (czyli zaksięgowane w tabeli Płatność). Po odnalezieniu osób które uczestniczyły w przestępstwie o podanym Id, procedura dokona transakcji wypłacenia pensji (na podstawie danych z tabeli Osoba). Jeśli jakakolwiek osoba otrzymała już pensję za te przestępstwo to zostanie ona odrzucona. To samo zajdzie, gdy poprzednie transakcje nie zostaną zakończone, lub gdy zajdzie inny nieoczekiwany błąd. W przeciwnym przypadku transakcja zostanie zaakceptowana.

Kod:

ALTER PROCEDURE "DBA"."WyplataZaPrzestepstwo"(@Id_przestępstwa INT)

AS

BEGIN

DECLARE @Ile INT

DECLARE @NajId INT

DECLARE @Id_Osoby VARCHAR(15)

DECLARE @Id_płatności VARCHAR(5)

DECLARE @Juz_jest INT

DECLARE ZaplacTemu CURSOR FOR SELECT U.Id_Osoby, O.Pensja

FROM Uczestnik_przestępstwa U, Osoba O

WHERE U.Id_przestępstwa=

@Id_przestępstwa

AND O.Id_Osoby=U.Id_Osoby

SET @Id_płatności='#'

SET @NajId=(SELECT TOP 1 CAST(SUBSTR(P.Id_płatności,2,5) as int) as Ide

FROM Płatność P

ORDER BY Ide DESC)

OPEN ZaplacTemu

SET @Juz_jest=0

FETCH ZaplacTemu INTO @Id_Osoby, @Ile

BEGIN TRANSACTION Wyplac

WHILE @@FETCH_STATUS=0

BEGIN

SET @NajId=@NajId+1

IF EXISTS (SELECT P.Id_przestępstwa, P.Id_Osoby, P.Rodzaj_płatności

FROM Płatność P

WHERE P.Id_przestępstwa=@Id_przestępstwa

AND P.Id_Osoby=@Id_Osoby

AND P.Rodzaj_płatności='Wypłata')

SET @Juz_jest=1 ELSE SET @Juz_jest=0

INSERT INTO Płatność (Id_płatności, Id_przestępstwa, Id_Osoby, Rodzaj_płatności, Kwota, Data_płatności)

VALUES(@Id_płatności+CAST(@NajId AS VARCHAR(5)), @Id_przestępstwa, @Id_Osoby, 'Wypłata', @Ile, GETDATE())

FETCH ZaplacTemu INTO @Id_Osoby, @Ile

END

CLOSE ZaplacTemu

DEALLOCATE ZaplacTemu

IF @@TRANCOUNT > 1 OR @@ERROR<>0 OR @Juz_jest=1

BEGIN

ROLLBACK TRANSACTION Wyplac

PRINT 'Transakcja zostala odrzucona, poniewaz poprzednia nie zostala zakonczona lub

juz dokonano wypłaty za to przestępstwo danej osobie lub wystapil inny blad.'

END ELSE

BEGIN

COMMIT TRANSACTION Wyplac

PRINT 'Transakcja zostala przyjeta.'

END

END

Przykład działania:

Po wykonaniu procedury dla przestępstwa o Id 2 do tabeli Płatność dodane zostały następujące wiersze:

1. „WplataZPrzestepstwa”

Komentarz:

Procedura WplataZPrzestepstwa przyjmuje jako argument Id_przestępstwa i oblicza dla przestępstwa o wskazanym Id kwotę jaka powinna zostać zaksięgowana w tabeli Płatność. Kwota ta jest różnicą majątku obiektu jakiego dotyczyło przestępstwo i sumy kosztów użytego podczas tego przestępstwa sprzętu. Jeśli przestępstwo dokonywała więcej niż jedna rodzina mafijna to kwota ta będzie przeznaczona dla rodziny, która ma mniejszą liczbę wpłat za przestępstwa (będzie to najbardziej sprawiedliwe rozwiązanie). W przypadku gdy tylko jedna rodzina dokonała przestępstwa kwotę tę wpłaci kapitan żołnierzy danej rodziny mafijnej.

Kod:

ALTER PROCEDURE "DBA"."WplataZPrzestepstwa"(IN @Id_przestepstwa INT)

AS

BEGIN

DECLARE @Dochod INT

DECLARE @Id_płatności VARCHAR(5)

DECLARE @NajId INT

DECLARE @Id_platnika INT

SET @Id_platnika=(SELECT Kapitan.Id_Osoby

FROM (SELECT UP.Id_Osoby, O.Nazwa_rodziny

FROM Uczestnik_przestępstwa UP,Osoba O,

WHERE O.Id_Osoby=UP.Id_Osoby

AND O.Funkcja='Capo' AND UP.Id_przestępstwa=@Id_przestepstwa) AS Kapitan,

(SELECT TOP 1 O.Nazwa_rodziny, COUNT(P.Id_Platnosci) AS Ile, SUM(P.Kwota) AS Kwota

FROM Płatność P, Osoba O

WHERE O.Id_Osoby=P.Id_Osoby AND P.Rodzaj_płatności='Wpłata'

GROUP BY O.Nazwa_rodziny

ORDER BY Kwota) AS Komu

WHERE Kapitan.Nazwa_rodziny =Komu.Nazwa_rodziny)

IF @Id_platnika IS NULL

SET @Id_platnika=(SELECT UP.Id_Osoby

FROM Uczestnik_przestępstwa UP, Osoba O,

WHERE O.Id_Osoby=UP.Id_Osoby

AND O.Funkcja='Capo' AND UP.Id_przestępstwa=@Id_przestepstwa)

SET @Dochod=DochodZPrzestepstwa(@Id_przestepstwa)

SET @Id_płatności='#'

SET @NajId=(SELECT TOP 1 CAST(SUBSTR(P.Id_płatności,2,5) as int) as Ide

FROM Płatność P

ORDER BY Ide DESC)+1

INSERT INTO Płatność (Id_płatności, Id_przestępstwa, Id_Osoby, Rodzaj_płatności, Kwota, Data_płatności)

VALUES(@Id_płatności+CAST(@NajId AS VARCHAR(5)), @Id_przestepstwa, @Id_platnika, 'Wpłata', @Dochod, GETDATE())

END

Przykład działania:

Po wykonaniu procedury dla przestępstwa o Id 6 do tabeli Płatność dodane zostały następujące wiersze: