W O J S K O W A A K A D E M I A T E C H N I C Z N A
L A B O R A T O R I U M B A Z D A N Y C H
SPRAWOZDANIE
Z ĆWICZENIA LABORATORYJNEGO
Temat ćwiczenia: Baza danych: Zajezdnia pociągów.
Ćwiczenie wykonał: Tomasz Czarnecki
grupa: I5X5S0
Data wykonania ćwiczenia: 21.06.2007
Procedury
"Czas przejazdu" - procedura wypisująca ile czasu jedzie pociąg na danej trasie pomiędzy dwoma podanymi przystankami
ALTER PROCEDURE "DBA"."Czas przejazdu"(IN numer int, pocz varchar(50), koniec varchar(50) /* [IN | OUT | INOUT] parameter_name parameter_type [DEFAULT default_value], ... */ )
/* RESULT( column_name column_type, ... ) */
BEGIN
declare odj time;
declare przyj time;
SELECT pt.godz_odj INTO odj
FROM przystanek p, przystanki_trasy pt
WHERE pt.id_trasy = numer
AND p.id_przystanku = pt.id_przystanku
AND p.nazwa = pocz;
SELECT pt.godz_przyj INTO przyj
FROM przystanek p, przystanki_trasy pt
WHERE pt.id_trasy = numer
AND p.id_przystanku = pt.id_przystanku
AND p.nazwa = koniec;
MESSAGE DATEDIFF(minute, odj, przyj) TO CLIENT;
END
"Pociag w przedz czasu" - ta procedura wyświetla oznaczenie pociągów, które przejadą danym typem trasy przez dany przystanek, pomiędzy dwoma podanymi godzinami
ALTER PROCEDURE "DBA"."Pociag w przedz czasu"( IN przyst varchar(50), trasa varchar(50), pocz time, koniec time /* [IN | OUT | INOUT] parameter_name parameter_type [DEFAULT default_value], ... */ )
/* RESULT( column_name column_type, ... ) */
BEGIN
declare numer int;
declare ozn varchar(20);
SELECT po.id_pociagu, po.oznaczenie INTO numer, ozn
FROM pociag po, trasa t, przystanki_trasy pt, przystanek p
WHERE trasa = t.nazwa
and po.id_trasy = t.id_trasy
AND t.id_trasy = pt.id_trasy
AND p.id_przystanku = pt.id_przystanku
AND p.nazwa = przyst
AND pt.godz_odj BETWEEN pocz AND koniec;
MESSAGE 'Wymagania spełnia pociąg: ', ozn TO CLIENT;
CALL "DBA"."Ile miejsca"("numer" = numer,"czy" = 1);
END
Funkcje
“Ile miejsca” - funkcja zwracająca ile miejsc siedzących dla obsługi pociągi i dla pasażerów jest w danym składzie wagonów
ALTER FUNCTION "DBA"."Ile miejsca"(IN numer int, czy int /* [IN] parameter_name parameter_type [DEFAULT default_value], ... */ )
RETURNS integer
DETERMINISTIC
BEGIN
DECLARE miejsca integer;
declare obsl int;
SELECT sum(w.ilosc_m_s_obsl), sum(w.ilosc_m_s_pas) INTO obsl, miejsca
FROM wagon w, sklad s, pociag p
WHERE p.id_pociagu = numer
AND p.id_skladu = s.id_skladu
AND s.id_skladu = w.id_skladu;
IF czy = 1 THEN
MESSAGE 'Ilość miejsc dla obsługi to ', obsl, ', a dla pasażerów ', miejsca TO CLIENT;
END IF;
RETURN miejsca;
END
Triggery
"Bledny pracownik" - zwraca błąd jeśli próbujemy dodać zbyt młoego pracownika (poniżej 18 lat), lub jeśli dziwnym zbiegem okoliczności zbyt wcześnie otrzymało n wyższy tytuł (czyli choćby licencjata)
ALTER TRIGGER "Bledny pracownik" AFTER INSERT, UPDATE
ORDER 1 ON "DBA"."PRACOWNIK"
/* REFERENCING OLD AS old_name NEW AS new_name */
FOR EACH ROW /* WHEN( search_condition ) */
BEGIN
declare a date;
declare i int;
declare dzis date;
set dzis = getdate();
select data_ur, count(*) into a, i from pracownik where DATEDIFF(year, data_ur, dzis) < 18 GROUP BY data_ur;
IF i > 0 THEN
MESSAGE 'Ten pracownik jest zbyt mlody. Ma ', DATEDIFF(year, a, dzis), ' lat.' TO CLIENT;
END IF;
set i = 0;
select data_ur, count(*) into a, i
from pracownik
where DATEDIFF(year, data_ur, dzis) < 22 AND id_wyksz = 3
GROUP BY data_ur;
IF i > 0 THEN
MESSAGE 'Ten pracownik nie moze miec tego wykształcenia. Jest zbyt mlody.' TO CLIENT;
END IF;
END
"Przeglad pociagu" - ten trigger zwraca błąd jeśli próbujemy dodać lub edytować pociąg tak, że data jego ostatniego przegląda jest mniejsza od daty produkcji
ALTER TRIGGER "Przeglad pociagu" AFTER INSERT, UPDATE
ORDER 1 ON "DBA"."POCIAG"
/* REFERENCING OLD AS old_name NEW AS new_name */
FOR EACH ROW /* WHEN( search_condition ) */
BEGIN
declare a int;
DECLARE err_user_error EXCEPTION FOR SQLSTATE 'Blad';
set a = (select id_pociagu from pociag where rok_prod > ostatni_przeglad);
IF a > 0 THEN
MESSAGE 'Data przegladu jest mniejsza od daty produkcji' TO CLIENT;
SIGNAL err_user_error;
END IF;
END
Widoki
“Stacje” - widok ten pokazuje wszystkie trasy oraz przystanki na nich, wyświetlając: numer id trasy, nazwę przystanku (w kolejności od początku trasy do końca), oraz datę przyjazdu do i odjazdu z danej stacji
ALTER VIEW "DBA"."Stacje"( /* view_column_name, ... */ )
AS
SELECT "DBA"."PRZYSTANKI_TRASY"."ID_TRASY",
"DBA"."PRZYSTANEK"."NAZWA",
"DBA"."PRZYSTANKI_TRASY"."GODZ_ODJ",
"DBA"."PRZYSTANKI_TRASY"."GODZ_PRZYJ"
FROM "DBA"."PRZYSTANKI_TRASY" CROSS JOIN "DBA"."PRZYSTANEK"
WHERE DBA.PRZYSTANKI_TRASY.ID_PRZYSTANKU = DBA.PRZYSTANEK.ID_PRZYSTANKU
ORDER BY "DBA"."PRZYSTANKI_TRASY"."ID_TRASY" ASC ,
"DBA"."PRZYSTANKI_TRASY"."NUMER_P" ASC
"Obsluga" - widok ten wyświetla listę pracowników (imię oraz nazwisko), wraz z ich stanowiskami, pensjami, oraz wykształceniem, posortowanego według zajmowanego stanowiska
ALTER VIEW "DBA"."Obsluga"( /* view_column_name, ... */ )
AS
SELECT "DBA"."PRACOWNIK"."IMIE",
"DBA"."PRACOWNIK"."NAZWISKO",
"DBA"."WYKSZTALCENIE"."WYKSZTALCENIE",
"DBA"."STANOWISKO"."STANOWISKO",
"DBA"."STANOWISKO"."PENSJA"
FROM ( "DBA"."PRACOWNIK" CROSS JOIN "DBA"."WYKSZTALCENIE" ) CROSS JOIN "DBA"."STANOWISKO"
WHERE DBA.PRACOWNIK.ID_WYKSZ = DBA.WYKSZTALCENIE.ID_WYKSZ AND DBA.PRACOWNIK.ID_STANOWISKO = DBA.STANOWISKO.ID_STANOWISKO
ORDER BY "DBA"."STANOWISKO"."STANOWISKO" ASC