pasiinviata · web viewbaza de date contine urmatoarele tabele : angajati ( coda number(3) primary...

ACADEMIA DE STUDII ECONOMICE BUCURESTI

FACULTATEA DE CIBERNETICA,STATISTICA SI INFORMATICA ECONOMICA

PROIECT - SGBD -

Student: Ivascu Claudia-Anitta

An.II Seria.B Grupa : 1034

BUCURESTI,2010

Initial baza de date avea urmatoarele caracteristici:

ANGAJATI:

CODA

NUME

ADRESA : LOC

STR structura de grup

DATA_ANG

CODS

FUNCTIE

SAL

CODDEP

DEPARTAMENTE :

CODDEP

DEN_DEP

PRODUSE:

CODP

DENP

PRET

STOC

CLIENTI:

CODC

DENC

LOC

COMENZI:

NRCOM

CODC

DATA_I

DATA_L

VAL

RAND_COM

NRCOM

CODP

CANT

Se elimina structurile de grup din tabela ANGAJAT.

ANGAJATI:

CODA

NUME

LOC

STR

DATA_ANG

CODS

FUNCTIE

SAL

CODDEP

Datorita faptului ca intre campurile tabelelor nu mai exista dependente de nici un alt fel (functionale, incomplete sau tranzitive) putem considera tabelele ca fiind in FN3.

Baza de date contine urmatoarele tabele :

ANGAJATI ( coda number(3) primary key, nume varchar2(30) , loc varchar2(20), str varchar2(10), data_ang date, cods number(3): Angajati , functie varchar2(30), sal number(5),

coddep number(3) : Departamente)

DEPARTAMENTE (coddep numbe(3) primary key, den_dep varchar2(30))

PRODUSE (codp varchar2(3) primary key, denp varchar2(30), pret number(5), stoc number(5), coddep numbe(3):Departamente)

CLIENTI (codc number(3) primary key, denc varchar2(50), loc varchar2(20))

COMENZI(nrcom number(5) primary key, data_i date, data_l date, val number (10), codc number(3):Clienti)

RAND_COM (nrcom number(5):Comenzi, codp varchar2(3): Produse, cant number(5))

Definirea legaturilor :

Legatura dintre Departamente si Angajati este de tipul 1: m (un angajat apartine unui singur department, un departament are mai multi angajati)

Legatura dintre Departamente si Produse este de tipul 1:m ( un produs este oferit de un singur department , un department contine mai multe produse)

Legatura dintre Produse si Comenzi este de tipul m:m , relatie despartita in relatie 1:1( intre Produse si Rand_com) si 1:m (intre Rand_com si Comenzi)

Legatura dintre Clienti si Comenzi este de tipul 1:m ( un client poate face mai multe comenzi insa o comanda este data de un singur client)

*Alte rectrictii le vom defini ulterior in cerinte.

SCHEMA CONCEPTUALA

ANGAJATI

CODA

NUME

LOC

STR

DATA_ANG

CODS

FUNCTIE

SAL

CODDEP

PRODUSE

CODP

DENP

PRET

STOC

CODDEP

DEPARTAMENT

CODDEP

DEN_DEP

RAND_COM

NRCOM

CODP

CANT

COMENZI

NRCOM

CODC

DATA_I

DATA_L

VAL

CLIENTI

CODC

DENC

LOC

CREAREA SI GESTIUNEA TABELELOR

Sa se creeze tabelele cu structurile de mai sus.

create table angajati (coda number(3) constraint PK_ang primary key,

nume varchar2(30),

loc varchar2(20),

str varchar2(10),

data_ang date,

cods number(3) references angajati(coda) ,

functie varchar2(30),

sal number(5),

coddep number(3) references departamente(coddep));

create table departamente (coddep number(3) constraint PK_dep primary key,

den_dep varchar2(30))

create table produse(codp varchar2(3) constraint PK_prod primary key,

denp varchar2(30),

pret number(5),

stoc number(5),

coddep number(3) references departamente(coddep));

create table clienti(codc number(3) constraint PK_cl primary key,

denc varchar2(50),

loc varchar2(20));

create table comenzi(nrcom number(5) constraint PK_com primary key,

data_i date,

data_l date,

val number (10),

codc number(3) references clienti(codc));

create table rand_com(nrcom number(5) references comenzi(nrcom),

codp varchar2(3) references produse(codp),

cant number(5));

Sa se defineasca restrictiile de cheie primara pentru tabela Rand_com (codp si nrcom)

alter table Rand_com

add constraint PK_pc primary key (nrcom,codp);

alter table Rand_com

add constraint FK_pc_prod foreign key (codp) references Produse(codp);

alter table Rand_com on delete set null

add constraint FK_pc_com foreign key (nrcom)

references Comenzi (nrcom) on delete cascade;

Sa se adauge restrictia entitatii (NOT NULL) pentru tabela Produse , atributul denp.

alter table Produseadd constraint NN_codpcheck (‘codp’ IS NOT NULL);

Sa se adauge restrictia de unicitate pentru atributele codc si coda din tabelele Clienti, respective Angajati.

alter table Clienti

add constraint Un_cc unique(codc);

alter table Angajati

add constraint Un_ang unique(coda);

Sa se marcheze atributul str din tabela Angajati ca atribut neutilizat.

alter table Angajati set unused(str);

Sa se stearga atributul marcat ca neutilizat.

alter table Angajati drop unused columns;

ACTUALIZAREA DATELOR

Scriptul de adaugare de inregistrari :

Delete from Angajati;

Insert into Angajati values ( ‘1’, ‘Negrea Dorin’, ‘Hunedoara’, to_date (‘01-03-2000’, ‘dd-mm-yyyy’), null , ‘Sef Depozit’, ‘1000’,’009’);

Insert into Angajati values ( ‘2’, ‘Barbu Mihai’, ‘Cluj’, to_date (‘17-06-2008’, ‘dd-mm-yyyy’), ‘1’, ‘Functionar Depozit’, ‘800’,’009’);

Insert into Angajati values ( ‘3’, ‘Demea Monica’, ‘Iasi’, to_date (‘21-07-2006’, ‘dd-mm-yyyy’), null , ‘Sef Vanzari’, ‘1000’,’006’);

Insert into Angajati values ( ‘4’, ‘Vantu Catalin’, ‘Deva’, to_date (‘14-01-2001’, ‘dd-mm-yyyy’), ‘3’, ‘Functionar Vanzari’, ‘800’,’006’);

Insert into Angajati values ( ‘5’, ‘Marin Ion’, ‘Galati’, to_date (‘02-11-2000’, ‘dd-mm-yyyy’), ‘1’ , ‘Functionar Depozit’, ‘800’,’009’);

Insert into Angajati values ( ‘6’, ‘Perijoc Daniel’, ‘Hunedoara’, to_date (‘13-09-2009’, ‘dd-mm-yyyy’), null , ‘Sef Contabilitate’, ‘1000’,’003’);

Insert into Angajati values ( ‘7’, ‘Flore Andreea’, ‘Bucuresti’, to_date (‘17-11-2001’, ‘dd-mm-yyyy’), ‘6’ , ‘Economist’, ‘900’,’003’);

Insert into Angajati values ( ‘8’, ‘Popescu Ion’, ‘Arad’, to_date (‘01-09-2002’, ‘dd-mm-yyyy’), ‘1’ , ‘Functionar Depozit’, ‘800’,’009’);

Insert into Angajati values ( ‘9’, ‘Lungu Lucian’, ‘Alexandria’, to_date (‘03-12-2004’, ‘dd-mm-yyyy’), ‘3’ , ‘Functionar Depozit’, ‘800’,’009’);

Insert into Angajati values ( ‘10’, ‘Munteanu Irina’, ‘Bucuresti’, to_date (‘09-04-2006’, ‘dd-mm-yyyy’), ‘1’, ‘Functionar Vanzari’, ‘800’,’006’);

Delete from Departamente;

Insert into Departamente values (‘003’, ‘Contabilitate’);

Insert into Departamente values (‘006’, ‘Vanzari’);

Insert into Departamente values (‘009’, ‘Depozit’);

Delete from Produse ;

Insert into Produse values (‘V44’, ‘Vobler’, ‘25’ , ‘300’ , ‘009’);

Insert into Produse values (‘L45’, ‘Lansete’, ‘400’ , ‘100’ , ‘009’);

Insert into Produse values (‘M46’, ‘Mulinete’, ‘150’ , ‘233’ , ‘009’);

Insert into Produse values (‘H47’, ‘Huse’, ‘175’ , ‘200’ , ‘009’);

Insert into Produse values (‘CT48’, ‘Cort’, ‘1000’ , ‘44’ , ‘006’);

Insert into Produse values (‘B49’, ‘Barca’, ‘850’ , ‘15’ , '006');

Insert into Produse values (‘MG50’, ‘Minciog’, ‘30’ , ‘400’ , ‘009’);

Insert into Produse values (‘JV51’, ‘Juvelnic’, ‘17’ , ‘300’ , ‘009’);

Delete from Clienti;

Insert into Clienti values ( 011, ‘Gipo’ , ‘Bucuresti’);

Insert into Clienti values ( 012, ‘Rapala’ , ‘Arad’);

Insert into Clienti values ( 013, ‘BibanFish’ , ‘Cluj’);

Insert into Clienti values ( 014, ‘TotalFishing’ , ‘Bucuresti’);

Insert into Clienti values ( 015, ‘CrazyFish’ , ‘Timisoara’);

Insert into Clienti values ( 016, ‘Pescarul’ , ‘Craiova’);

Insert into Clienti values ( 017, ‘Pescarusi’ , ‘Iasi’);

Insert into Clienti values ( 018, ‘Campionii’ , ‘Tulcea’);

Insert into Clienti values ( 019, ‘Schimano’ , ‘Braila’);

Delete from Comenzi;

Insert into Comenzi (nrcom, data_i , data_l, val , codc ) values (1000, to_date (‘18-05-2000’, ‘dd-mm-yyyy’) , to_date (‘20-05-2000’, ‘dd-mm-yyyy’) ‘450000’, ‘015’);

Insert into Comenzi (nrcom, data_i , data_l, val , codc ) values (1444, to_date (‘23-05-2001’, ‘dd-mm-yyyy’) , to_date (‘20-06-2001’, ‘dd-mm-yyyy’) , ‘670000’, ‘011’);

Insert into Comenzi (nrcom, data_i , data_l, val , codc )values (1892, to_date (‘12-09-2001’, ‘dd-mm-yyyy’) , to_date (‘14-09-2001’, ‘dd-mm-yyyy’), ‘20000’, ‘014’);



Insert into Comenzi (nrcom, data_i , data_l, val , codc ) values (2000, to_date (‘03-03-2002’, ‘dd-mm-yyyy’) , to_date (‘09-03-2002’, ‘dd-mm-yyyy’), ‘640000’, ‘018’);








Insert into Comenzi (nrcom, data_i , data_l, val , codc ) values (2517, to_date (‘15-10-2004’, ‘dd-mm-yyyy’) , to_date (‘18-10-2004’, ‘dd-mm-yyyy’) ,‘280000’, ‘013’);









Delete from Rand_com;

Insert into Rand_com VALUES (1444, ‘CT8’, ‘670’);

Insert into Rand_com VALUES (1920, ‘L45’, ‘125’);

Insert into Rand_com VALUES (2014, ‘M46’, ‘3600’);

Insert into Rand_com VALUES (2280, ‘CT8’, ‘497’);

Insert into Rand_com VALUES (2443,’CT8’, ‘180’);

Insert into Rand_com VALUES (2458, ‘MG5’, ‘11334’);

Insert into Rand_com VALUES (2561, ‘M46’, ‘1866’);

Insert into Rand_com VALUES (2617, ‘V44’, ‘12400’);

Insert into Rand_com VALUES (2741, ‘JV51’, ‘16910’);

Insert into Rand_com VALUES (3011, ‘B49’, ‘2500’);

Comenzile DML

Sa se adauge in tabela Clienti_buc informatii despre clientii din Bucuresti. Tabela Clienti_buc are aceeiasi structura ca tabela Clienti.

create table clienti_buc(codc number(3) constraint PK_cl primary key,

denc varchar2(50),

loc varchar2(20));

insert into client_buc select * from Clienti

where unitcap(loc)= ‘Bucuresti’;

Sa se utilizeze o singura comanda INSERT pentru a incarca in tabela Istorie_sal codul numele si salariul angajatiilor care au un salariu > 900 , iar in tabela Istorie_functie codul numele si functia angajatiilor care au functia Functionar Depozit, folosind un cursor sa se afiseze continutul tabelei istorie_sal.

Dialog

create table istorie_sal(coda number(3) constraint PK_ang1 primary key , nume varchar2(30), sal number(5));

create table istorie_functie(coda number(3) constraint PK_ang2 primary key , nume varchar2(30), functie varchar2(30)) ;

Command line

Set serveroutput on

Declare

Cursor x is select * from istorie_sal;

begin

insert all

when sal>900 then

into istorie_sal values (coda , nume , sal)

when functie= 'Functionar Depozit' then

into istorie_functie values(coda, nume, functie)

select coda , nume, sal, functie from Angajati;

dbms_output.put_line('Nume || Salariu || Cod Angajat ');

for v in x loop

dbms_output.put_line(v.nume||' '||v.sal||' '||v.coda);

end loop;

end;

/

Comanda Update :

Sa se diminueze stocul cu x buc pentru un produs introdus de la tastatura. Unde x este o valoare introdusa de utilizator. In cazul in care stocul ajunge pe minus se va anula tranzactia. In cazul in care nu exista produsul se va invoca o exceptie.

Set serveroutput on

Accept y prompt 'Introduceti denumirea produsului';

Accept x prompt 'Introduceti nr de bucati cu care sa se diminueze stocul';

Declare

a produse.stoc%type:=&x;

b produse.denp%type:='&y';

x1 exception;

x2 exception;

c produse.denp%type;

begin

select stoc into c from produse where denp=initcap(b);

dbms_output.put_line('Stocul produsului '||b||' este: '||c);

Update Produse

Set stoc=stoc-a

Where denp=initcap(b);

If SQL%NOTFOUND then RAISE x1;

End if;

Select stoc into a from produse where denp=initcap(b);

If a<0 then RAISE x2;

End if;

select stoc into c from produse where denp=initcap(b);

dbms_output.put_line('Noul stoc al produsului '||b|| ' este de: '||c);

Exception

When x1 then dbms_output.put_line('Nu s-a gasit produsul dorit!');

When x2 then dbms_output.put_line('Stoc negativ!?') ; ROLLBACK;

when NO_DATA_FOUND then dbms_output.put_line('Nu s-a gasit produsul: '||b||'!');

End;

/

Sa se modifice data de livrare si valoarea comenzii x – comanda x sa aiba acelasi termen de livrare si valoare cu comanda 1444). X introdus de la tastatura. Tratati exceptiile.

Set serveroutput on

Accept x prompt 'Introduceti Codul Comenzii: ';

Declare

x comenzi.nrcom%type:=&x;

v_data comenzi.data_l%type;

v_val comenzi.val%type;

begin

select data_l, val into v_data, v_val from comenzi where nrcom=x;

dbms_output.put_line('Data initiala: '||v_data);

dbms_output.put_line('Valoare initiala: '||v_val);

Update Comenzi

Set data_l=(select data_l from comenzi

Where nrcom=1444),

Val=(select val from comenzi

Where nrcom=1444)

Where nrcom=x;

select data_l, val into v_data, v_val from comenzi where nrcom=x;

dbms_output.put_line('Data actualizata: '||v_data);

dbms_output.put_line('Valoare actualizata: '||v_val);

Exception

when NO_DATA_FOUND then dbms_output.put_line('Nu exista comanda cu acel numar!');

End;

/

Creati o procedura care sa modifice salariul pe tabela angajati. Procentul de modificare este in functie de vechimea angajatilor dupa cum urmeaza:

10 ani vechime – 7%

5-10 ani vechime – 4%

1-5 ani vechime – 2%

Sub 1 an vechime – nu se modifica

set serveroutput on

Create or replace procedure modif_sal

Is

Cursor k is select round((sysdate-data_ang)/365) vech, coda, nume

from angajati

order by vech desc;

v_sal angajati.sal%type;

BEGIN

For v in k

loop

If v.vech>=10 then

select sal into v_sal from angajati where coda=v.coda;

dbms_output.put_line('Salariul Initial al angajatului '||v.nume||' este: '||v_sal);

Update angajati

Set sal=sal+sal*0.07

Where coda=v.coda;


dbms_output.put_line('Salariul Modificat al angajatului '||v.nume||' este: '||v_sal);

dbms_output.put_line('--------------------------------------');

End if;

If v.vech between 5 and 9 then



Update angajati


Where coda=v.coda;




End if;

If v.vech between 1 and 4 then



Update angajati


Where coda=v.coda;




End if;

end loop;

end;

/

show errors;

begin

modif_sal();

end;

/

Comanda Delete :

Sa se stearga toti clientii dintr-o localitate introdusa de la tastatura.Tratati exceptiile.

set serveroutput on

accept x prompt 'Introduceti localitatea: ';

declare

x clienti.loc%type:='&x';

x1 exception;

begin

delete denc

from clienti

where loc=initcap(x);

IF SQL%FOUND THEN

dbms_output.put_line('S-au sters '|| SQL%ROWCOUNT||' clienti!');

rollback;

dbms_output.put_line('S-au anulat stergerile');

ELSE RAISE x1;

END IF;

exception

when x1 then dbms_output.put_line('Nu exista clienti din localitatea: '||x);

end;

/

Sa se creeze o procedura afis_prod care afiseza informatii despre produse , ordonate crescator pe departamente si dupa denumire.

set serveroutput on

create or replace procedure afis_prod

is

cursor c is select den_dep, coddep from departamente ;

cursor k(v_coddep departamente.coddep%type) is select * from produse

where coddep=v_coddep

order by denp;

v_coddep departamente.coddep%type;

begin

for v in c

loop

dbms_output.put_line('----'||v.den_dep);

v_coddep:=v.coddep;

for v1 in k(v_coddep)

loop

dbms_output.put_line(v1.denp||' cu pretul de '||v1.pret||' ');

end loop;

end loop;

end;

/

show errors;

begin

afis_prod();

end;

/

Creati un pachet de subprograme care sa contina urmatoarele:

-o procedura pentru afisarea sefilor si subalternilor corespunzatori acestora

-o functie pentru calcularea salariului mediu pe un departament introdus de la tastatura

-o procedura pentru marirea salariului cu 15% pentru angajatii care au un salariu mai mic decat o valoarea introdusa de la tastatura cu afisarea numarului de inregistrari modificate

-o procedura care afiseaza angajatul dintr-un an introdus de la tastatura, se vor trata exceptiile predefinite.

set serveroutput on

create or replace package pachet_angajati

is

procedure big_boss ;

function medie_sal (v_dep angajati.coddep%type)

return number;

procedure update_sal (v_sal angajati.sal%type);

procedure list_ang (v_data number);

end pachet_angajati;

/

create or replace package body pachet_angajati

is

procedure big_boss is

cursor c is select coda,nume from angajati

where cods IS NULL;

cursor k(v_cods angajati.cods%type) is select nume from angajati

where cods=v_cods;

v_cods angajati.cods%type;

begin

for v in c loop

dbms_output.put_line('---'||v.nume||' are subalternii');

v_cods:=v.coda;

for x in k(v_cods) loop

dbms_output.put_line (x.nume);

end loop;

end loop;

end;

function medie_sal(v_dep angajati.coddep%type)

return number

is

v_avg number;

x number:=0;

suma number:=0;

cursor c is select sal from angajati where coddep=v_dep;

begin

for v in c loop

x:=x+1;

suma:=suma+v.sal;

end loop;

v_avg:=round(suma/x,2);

return v_avg;

end;

procedure update_sal (v_sal angajati.sal%type)

is

x exception;

begin

update angajati

set sal=sal*1.5

where sal

if SQL%NOTFOUND then raise x;

else dbms_output.put_line('S-a efectuat marirea de salariu.'); dbms_output.put_line('S-au marit '||SQL%ROWCOUNT||' salarii');

end if;

exception

when x then dbms_output.put_line('Nu s-a gasit niciun angajat cu salariul sub '||v_sal);

end;

procedure list_ang (v_data number)

is

v_nume angajati.nume%type;

begin

select nume into v_nume from angajati

where extract(year from data_ang)=v_data;

dbms_output.put_line('In anul '||v_data||' a fost angajat '||v_nume);

Exception

when NO_DATA_FOUND then dbms_output.put_line('Nu exista angajat!');

when TOO_MANY_ROWS then dbms_output.put_line('Prea multi angajati!');

end;

END;

/

show errors

--Apelurile subprogramelor incluse in pachet!!!--

accept x prompt 'Introduceti Departamentul: ';

accept x1 prompt 'Introduceti Salariul: ';

accept x2 prompt 'Introduceti Anul Angajarii: ';

declare

x angajati.coddep%type:=&x;

x1 angajati.sal%type:=&x1;

x2 number:=&x2;

begin

pachet_angajati.big_boss();

dbms_output.put_line('-----------------------------------------------');

dbms_output.put_line('Media pe departamentul '||x||' este: '||pachet_angajati.medie_sal(x));


pachet_angajati.update_sal(x1);


pachet_angajati.list_ang(x2);

end;

/

Sa se creeze o procedura care calculeaza si afiseaza comenzile cu cel mai mare termen de livrare respective cel mai mic si valoarile acestora in RON si USD unde cursul de schimb RON/USD este preluat de la tastatura.

set serveroutput on

create or replace procedure money (cs number)

is

x number:=0;

x1 number:=999999;

y number;

y1 number;

cursor c is select round(data_l-data_i) termen, val from comenzi;

begin

for v in c

loop

if x

end if;

if x1>v.termen then x1:=v.termen; y1:=v.val;

end if;

end loop;

dbms_output.put_line('Comanda cu termenul de livrare '||x||' are valoarea: '||y||' RON');

dbms_output.put_line('Comanda cu termenul de livrare '||x1||' are valoarea: '||y1||' RON');

y:=round(y/cs,2);

y1:=round(y1/cs,2);

dbms_output.put_line('Comanda cu termenul de livrare '||x||' are valoarea: '||y||' $');

dbms_output.put_line('Comanda cu termenul de livrare '||x1||' are valoarea: '||y1||' $');

end;

/

show errors;

accept cs prompt 'Introduceti cursul de schimb RON/USD: ';

declare

cs number:=&cs;

begin

money(cs);

end;

/

Creati un trigger care sa nu permita diminuarea stocului sub 10 unitati. In cazul in care se incearca se va apela o procedura care va mari automay stocul cu 100.

set serveroutput on

create or replace procedure marire_stoc(v_codp produse.codp%type)

is

begin

update produse

set stoc=stoc+100

where codp=v_codp;

end;

/

create or replace trigger zanu_stoc

after update on produse

for each row

declare

v_stoc_min number;

begin

if :new.stoc<10 then raise_application_error(-20202, 'Nu se poate diminua stocul sub 10 unitati!!! LICHIDARE DE STOC?!?! SOS');

marire_stoc(:new.codp);

end if;

end;

/

show errors

begin

update produse

set stoc=5

where codp='L45';

end;

/

CREATE OR REPLACE TRIGGER produse_trig

BEFORE INSERT ON produse

BEGIN

dbms_output.put_line('triggerul s-a executat');

END;

/

insert into produse (codp , denp) values ('SC7', 'Scaun');

pasiinviata · web viewbaza de date contine urmatoarele tabele : angajati ( coda number(3) primary...

Documents