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
Funkcja “gratis”
Funkcja ta przyjmuje jako parametr pesel klienta.
funkcja ta oblicza kwote na jaka klient zrobil zakupy. Nastepnie każdy zwrot czesci do naprawy tego klienta jest dla nas strata
( naprawa jest to rodzaj gwarancji dawanej przez sklep. Jeżeli czesc jest oddana do naprawy to nasza firma ponosi koszt zakupu danej czesci- wiadomo procesorow plyt głównych itd. Się nie naprawia, tylko po prostu wymienia na nowe)
Funkcja zwraca wartość przychodu ze sprzedazy pomniejszonego o koszt zakupu czesci do naprawy, oraz wiadomość w postaci czy opłacało nam się handlowac z klientem o danym peselu czy tez nie. Przyjmujemy ze jeżeli strata nie wynosi wiecej niż 30% calej wartości faktur klienta to warto było prowadzic handel z tym klientem.
ALTER FUNCTION "DBA"."czy_zysk_z_klienta"(in numer decimal(11))
RETURNS double
DETERMINISTIC
BEGIN
DECLARE "suma" double;
DECLARE "przychod" double;
DECLARE "sumakomp" double;
DECLARE "strata" double;
declare warto char;
set warto='T';
set suma=0;
set sumakomp=0;
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_pesel"=numer);
SET sumakomp = (SELECT SUM(cena_zestawu) FROM komputer,pozycja_faktury
WHERE "DBA"."komputer"."id_komputera" = "DBA"."Pozycja_faktury"."id_komputera"
AND "DBA"."Pozycja_faktury"."nr_pesel"=numer);
set strata =(SELECT SUM(cena_zakupu) FROM czesc
join naprawa on "DBA"."czesc"."nr_seryjny" = "DBA"."naprawa"."nr_seryjny"
join pozycja_faktury on "DBA"."Pozycja_faktury"."nr_pesel"=numer
and pozycja_faktury.id_naprawy=naprawa.id_naprawy );
set suma= sumakomp + suma;
set przychod=suma - strata;
if przychod< (0.7*suma) then
set warto='N';
end if;
message 'czy sie oplaca' to client ;
message warto to client;
RETURN "przychod";
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.
Wyszukiwarka