bdsprawko, WAT, semestr III, Bazy danych


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):

0x01 graphic


Model konceptualny systemu(widok z Power Designera 12.0):

0x01 graphic

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:

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