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.

---