Wojskowa Akademia Techniczna
Wydział Cybernetyki
Instytut Systemów Informatycznych
Przedmiot: Bazy Danych
Rodzaj pracy: Sprawozdanie z projektu
Projekt: Baza danych sklepu
Prowadzący: mgr inż. Justyna Pancerow
Student: Mirosław Krasowski
Grupa szkoleniowa: I6X3S1
Termin ukończenia projektu: 25czerwca 2008
Godz. 3:30
Model fizyczny systemu(widok z Power Designera 12.0):
Model konceptualny systemu(widok z Power Designera 12.0):
Postać normalna - postać relacji w bazie danych, w której nie występuje redundancja (nadmiarowość) czyli powtarzanie się tych samych informacji.
Relacja jest w pierwszej postaci normalnej, jeśli wartości atrybutów są elementarne (atomowe, niepodzielne): są to pojedyncze wartości określonego typu, a nie zbiory wartości. Tabela reprezentująca tę relację nie zawiera powtarzających się grup informacji. Każda kolumna jest wartością skalarną (atomową), a nie macierzą lub listą czy też czymkolwiek, co posiada własną strukturę.
Relacja jest w drugiej postaci normalnej wtedy i tylko wtedy gdy jest w pierwszej postaci normalnej i każda kolumna zależy funkcyjnie od całego klucza głównego (a nie np. od części klucza).
Relacja jest w trzeciej postaci normalnej wtedy i tylko wtedy gdy jest w drugiej postaci normalnej i kolumny są w bezpośredniej zależności funkcyjnej jedynie od klucza głównego. Nie ma takiej zależności między innymi kolumnami. Oznacza to, że nie istnieją żadne zależności przechodnie .
Relacja jest w czwartej postaci normalnej, jeżeli zawsze wtedy kiedy zbiór atrybutów X określa wartościowo Y, to zachodzi jeden z następujących warunków:
Y jest puste lub zawiera się w X,
suma zbiorów X i Y jest pełnym zbiorem atrybutów,
X zawiera klucz.
Czwarta i piąta postać normalna są w zasadzie używane wyłącznie przy okazji rozważań teoretycznych.
Moja baza jest w 3 postaci normalnej.
Temat: sieć sklepów komputerowych
UŻYTKOWNICY: wraz z prawami dostępu:
dba,--- ----DBA:Yes, Resource:Yes, Remote:Yes, Backup:Yes, Validate:Yes)
misiek ---- DBA:yes, Resource:yes, Remote:yess, Backup:yes, Validate:no)
inni --------DBA:No, Resource:Yes, Remote:No, Backup:No, Validate:No)
Widoki :
Czesci—wypisuje tylko te czesci ktorych producentem jest firma AMD
ALTER VIEW "DBA"."czesci"( /* view_column_name, ... */ )
AS
SELECT "DBA"."Czesc"."Nazwa_czesci",
"DBA"."Czesc"."Typ",
"DBA"."Czesc"."Cena_sprzedazy",
"DBA"."Czesc"."Opis"
FROM "DBA"."Czesc"
WHERE nazwa_producenta ='AMD'
ORDER BY "DBA"."Czesc"."Typ" ASC ,
"DBA"."Czesc"."Cena_sprzedazy" ASC
Faktury: wypisuje faktury ktore zostały wystawione od czerwca 2008 i sortuje malejąco po dacie
ALTER VIEW "DBA"."faktury"( /* view_column_name, ... */ )
AS
SELECT *
FROM "DBA"."Faktura"
WHERE data>'2008-06-01'
ORDER BY "DBA"."Faktura"."Data" DESC
Prac: wybiera tych pracuwnikow ktorzy sa monterami I ktorzy przebywaja na sklepie1
ALTER VIEW "DBA"."prac"( /* view_column_name, ... */ )
AS
SELECT "DBA"."Pracownik"."Imie",
"DBA"."Pracownik"."Nazwisko",
"DBA"."Pracownik"."Pesel"
FROM "DBA"."Pracownik"
WHERE typ_pracownika='monter'
AND nazwa='sklep1'
ORDER BY "DBA"."Pracownik"."Nazwisko" ASC ,
"DBA"."Pracownik"."Imie" ASC
TRIGERY
Tel- trigger do tabeli klient
Sprawdza czy jeśli jest wpisywany bądź zmieniany numer telefonu klienta to czy nie istnieje juz w bazie jako telefon innego klienta
ALTER TRIGGER "tel" BEFORE INSERT, update
ORDER 1 ON "DBA"."klient"
REFERENCING NEW AS new_ins
FOR EACH ROW
BEGIN
declare znajdz integer;
if (new_ins.telefon is not null) then
begin
set znajdz = 0;
select count(*)
into znajdz
from klient
where telefon = new_ins.telefon;
if znajdz >= 1 then
signal Błąd_podany_telefon_jest_zarezerwowany
end if;
end
end if;
end
kasuj- triger do tabeli pracownik
jeżeli chcemy skasować pracownika z bazy to sprawdzamy jeżeli w typie pracownika jest inna wartość niż NULL ( np. monter) to dany pracownik uznawany jest za czynnego i nie można go usunąć
ALTER TRIGGER "kasuj"
BEFORE DELETE
ON pracownik
REFERENCING OLD AS pr
FOR EACH ROW /* WHEN( search_condition ) */
BEGIN
IF pr.typ_pracownika IS not NULL THEN
RAISERROR 30001 'ten pracownik jest czynny i nie mozna go usunąć';
END IF;
END
Cena- trigger do tabeli czesc.
Sprawdza czy cena sprzedaży nie jest niższa od ceny zakupu jeśli tak to wyświetla stosowny komunikat i czeka Az wprowadzimy cenę sprzedaży minimum równą cenie zakupu
ALTER TRIGGER "cena"
BEFORE insert,UPDATE
ON czesc
REFERENCING OLD AS before_update
NEW AS after_update
FOR EACH ROW
BEGIN
//IF after_update.cena_sprzedazy < before_update.cena_sprzedazy THEN
if after_update.cena_sprzedazy < after_update.cena_zakupu then
RAISERROR 30002 'nie mozna zmniejszyc ceny sprzedazy ponizej ceny zakupu';
//end if
END IF;
END
Funkcje:
Suma_fak- sumuje wartosc calej faktury o numerze podanym jako parametr
ALTER FUNCTION "DBA"."suma_fak"(in numer char(10))
RETURNS double
DETERMINISTIC
BEGIN
DECLARE "suma" double;
SET suma = (SELECT SUM(cena_sprzedazy) FROM czesc,pozycja_faktury
WHERE "DBA"."czesc"."Nr_seryjny" = "DBA"."Pozycja_faktury"."Nr_seryjny"
AND "DBA"."Pozycja_faktury"."Nr_faktury"=numer);
RETURN "suma";
END
Sprawdz_prac: funkcja z kursorem. Sprawdza kolejno rekordy. Jeżeli podany PESEL jako parametr zostanie znaleziony w tabeli pracownicy to system wyświetla komunikat „T”
Jeżeli nie znaleziono to „N”
ALTER FUNCTION "DBA"."sprawdz_prac"(in pes decimal(11))
RETURNS varchar
DETERMINISTIC
BEGIN
DECLARE czy varchar;
declare kursor dynamic scroll cursor for select pesel from pracownik;
declare @zmienna numeric(20);
declare i int;
set i=0;
set czy='N';
set @zmienna=0;
open kursor;
while i < (select count(pesel) from pracownik)
loop
set i=i+1;
fetch next kursor into @zmienna;
if(@zmienna=pes) then set czy='T';
end if;
end loop;
close kursor;
message 'sprawdzono baze w poszukiwaniu wskazanego peselu' to client ;
message czy to client;
RETURN czy;
END
Sprawdz pesel: sprawdza poprawność peselu co do długości oraz oblicza poprawność sprawdzając cyfrę kontrolna.
ALTER FUNCTION "DBA"."sprawdz_pesel"( in PES decimal(11) /* [IN] parameter_name parameter_type [DEFAULT default_value], ... */ )
RETURNS varchar
DETERMINISTIC
BEGIN
DECLARE sum integer;
IF LENGTH(PES) <> 11 THEN
message'to nie jest pesel , zla ilosc cyfr' to client;
RETURN 'N';
END IF;
set sum = MOD(10 -
MOD(SUBSTR(PES, 1, 1) * 1
+SUBSTR(PES, 2, 1) * 3
+SUBSTR(PES, 3, 1) * 7
+SUBSTR(PES, 4, 1) * 9
+SUBSTR(PES, 5, 1) * 1
+SUBSTR(PES, 6, 1) * 3
+SUBSTR(PES, 7, 1) * 7
+SUBSTR(PES, 8, 1) * 9
+SUBSTR(PES, 9, 1) * 1
+SUBSTR(PES, 10, 1) * 3
,10)
,10);
if sum = SUBSTR(PES, 11, 1) THEN
message'PESEL jest ok' to client;
RETURN 'T';
ELSE
message'To nie jest pesel' to client;
RETURN 'N';
END IF;
END
PROCEDURY:
Dodaj_pracownika:- dodaje nowego pracownika poprzez podanie wszystkich atrybutow jako parametry pod warunkiem ze wporwadzony zostanie poprawny numer pesel.
ALTER PROCEDURE "DBA"."dodaj_pracownika"(IN i varchar(20) , IN naz varchar(20) , in nr_pes decimal(11),in nazw varchar(20),in typ varchar(20), tel decimal(15),in miejsc varchar(20),in kod varchar(6), in ul varchar(20), in nr_miesz tinyint)
/* [IN | OUT | INOUT] parameter_name parameter_type [DEFAULT default_value], ... */
BEGIN
//procedura dodajaca pracownika
declare czy char;
set czy = (select Sprawdz_pesel(nr_pes));
if(czy = 'T')
then
insert into "DBA"."pracownik" values(i, naz,nr_pes,nazw,typ,tel,miejsc,kod,ul, nr_miesz);
else
message 'zly pesel!!'
end if;
END
Dodaj_klienta: -dodaje nowego klienta pod warunkiem ze podany zostal poprawny numer PESEL
ALTER PROCEDURE "DBA"."dodaj_klienta"(IN i varchar(20) , IN naz varchar(20) , in nr_pes varchar(11),in tel decimal(15),in miejsc varchar(20),in kod varchar(6), in ul varchar(20), in nr_miesz tinyint)
/* [IN | OUT | INOUT] parameter_name parameter_type [DEFAULT default_value], ... */
BEGIN
//procedura dodajaca klienta
declare czy char;
set czy = (select Sprawdz_pesel(nr_pes));
if(czy = 'T')
then
insert into "DBA"."Klient" values(i, naz,nr_pes,tel,miejsc,kod,ul, nr_miesz);
else
message 'zly numer pesel!!';
end if;
END
Policz- liczy sumy wszystkich faktur
ALTER PROCEDURE "DBA"."policz"()
BEGIN
declare kursor dynamic scroll cursor for select faktura.suma from Faktura;
declare @zmienna float;
declare @ile float;
declare i int;
set i=0;
set @zmienna=0;
set @ile=0;
open kursor;
while i < (select count(suma) from Faktura)
loop
set i=i+1;
fetch next kursor into @zmienna;
set @ile=@ile + @zmienna;
end loop;
close kursor;
message 'wartosc wszsytkich faktur' to client ;
message @ile to client;
END
Wywołania uzyte dla sprawdzenia poprawności wykonywania sie procedur I funkcji:
select suma_fak(1);
select sprawdz_pesel(86052809897);
call dodaj_pracownika(`jan','kowalski',123123,'sklep1','monter',1234,'warszawa','21-666','prosta',22);
.
.
.
Podsumowanie:
Użyte przeze mnie tabele są jedynie zalążkiem prawdziwej bazy danych. Model Bazy zawiera podstawową funkcjonalność, która miała za zadanie pokazać zasadę działania przykładowej bazy danych, oraz mnogości zastosowań takich jak: funkcje, trigery, widoki, prawa dostępu użytkowników itd.
Wszystkie założenia co do wymogów zostały spełnione.