Proiect Ivascu Claudia

ACADEMIA DE STUDII ECONOMICE BUCURESTIFACULTATEA DE CIBERNETICA,STATISTICA SI INFORMATICA ECONOMICA

PROIECT - BAZE DE DATE -Student: Ivascu Claudia-Anitta

An.II Seria.C Grupa : 1034

BUCURESTI,2010Initial 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:

NRCOMCODC

DATA_I

DATA_LVAL

RAND_COM

NRCOM

CODP

CANTSe 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 ANGAJATICODANUMELOCSTRDATA_ANGCODSFUNCTIESALCODDEP

PRODUSE

CODPDENPPRETSTOCCODDEP

DEPARTAMENTCODDEPDEN_DEP

RAND_COM

NRCOMCODPCANT

COMENZI

NRCOMCODCDATA_IDATA_LVAL

CLIENTI

CODCDENCLOC

CREAREA SI GESTIUNEA TABELELOR

1. 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 numbe(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 numbe(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 client(codc));

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

codp varchar2(3) references produse(codp),

cant number(5));

2. 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 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;

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

alter table Produse

add constraint NN_codp check ("codp" IS NOT NULL);

4. 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);

5. Sa se marcheze atributul str din tabela Angajati ca atribut neutilizat. alter table Angajati set unused(str); 6. Sa se stearga atributul marcat ca neutilizat. alter table Angajati drop unused columns;ACTUALIZAREA DATELOR1. 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-2000', 'dd-mm-yyyy'), 1, Functionar Depozit, 800,009);

Insert into Angajati values ( 3, Demea Monica, Iasi, to_date ('21-07-2000', '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-2001', '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 )








































Delete from Rand_com;

Insert into Rand_com(1444, CT48, 670); Insert into Rand_com(1920, L45, 125);

Insert into Rand_com(2014, M46, 3600);

Insert into Rand_com(2280, CT48, 497); Insert into Rand_com(2443, CT48, 180); Insert into Rand_com(2458, MG50, 11334); Insert into Rand_com(2561, M46, 1866); Insert into Rand_com(2617, V44, 12400); Insert into Rand_com(2741, JV51, 16910); Insert into Rand_com(3011, B49, 2500);Comenzile DML1. 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;

2. 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.

create table istorie_sal(coda number(3) constraint PK_ang primary key , nume varchar2(30), sal number(5));create table istorie_functie(coda number(3) constraint PK_ang primary key , nume varchar2(30), functie varchar2(30)) ;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;3. Sa se utilizeze o singura comanda insert pentru a incarca in tabela Istorie_functie codul angajatiilor care au functia Functionar Vanzari, in tabela Istorie_sal codul numele si salariul angajatiilor care au salariu egal cu 900 iar in tabela Rest codul celorlalti angajati.Create table Rest(coda number(3) constraint PK_res primary key);

Delete from Istorie_functie; Delete from Istorie_Sal;Insert first

When functie= Functionar Vanzari then

into istorie_functie values(coda, nume, functie)

when sal=900 then

into istorie_sal values (coda , nume , sal)

else

into rest values(coda)

select coda,nume,functie,sal from Angajati;

Comanda Update :1. Sa se diminueze stocul cu 45 de buc pentru produsul Lanseta.Update Produse

Set stoc=stoc-45

Where denp= Lanseta;

2. Sa se modifice data de livrare si valoarea comenzii 1000 comanda 1000 sa aiba acelasi termen de livrare si valoare cu comanda 1444)Update Comenzi

Set data_l=(select data_l from comenzi

Where nrcom=1444),

Val=(select val from comenzi

Where nrcom=1444)

Where nrcom=1000;

3. Sa se modifice salariul angajatiilor cu functia Functionar Depozit, la 750.

Update Angajati

Set sal=750

Where functia=Functionar Depozit;

Comanda Delete :4. Sa se stearga toti clientii din Bucuresti.

Delete from Clienti

where unitcap(loc)= Bucuresti;

Sa se anuleze comanda anterioara-

Rollback;

Exemple de interogari variateComanda Select :1. Sa se afiseze toate produsele , ordonate crescator dupa denumire.Select * from produse

Order by denp desc;

2. Sa se simuleze o proiectie pe tabela Clienti.

Select distinct loc from client;

3. Sa se simuleze o selectie pe tabela Angajati.

Select * from Angajati

Where coddep= 009 ;

4. Sa se simuleze un produs cartezian intre tabelele Clienti si Comenzi.

Select denc,nrcom from client , comenzi;5. Sa se afiseze comenzile a caror valoare este intre 500000 si 900000.Select nrcom from comenzi

Where val between 500000 and 900000

6. Sa se afiseze codul persoanei , numele concatenate cu codul si lungimea atrubutului nume , numai pentru persoanele a caror nume are a doua litera e.Select coda,concat(coda,nume), length (nume)

From Angajati

Where nume like e%;7. Sa se afiseze numele angajatiilor la care ultima litera din nume este litera n.

Select nume from Angajati

Where substr(nume,-1,1)= n;

8. Sa se afiseze comanda a carei valoare este 620000 si perioada de timp corespunzatoare in saptamani intre data incheierii comenzii si data curenta.

Select nrcom, (sysdate-data_i)/7 sapt from comenzi

Where val= 620000;

9. Sa se afiseze comenzile cu termenul de livrare in luna septembrie.

Select nrcom from comenzi

Where extract (month from data_l)=9;

10. Sa se afiseze numele angajatilor care au o vechime mai mare de 5 ani.


Where extract(year from sysdate)-extract(year from data_ang)>5;

11. Sa se afiseze pentru toate comenzile , data incheierii comenzii in format MM/YY

Select nrcom, to_char (data_i, MM/YY) data from comenzi;

12. Daca produsul este :

Vobler se va afisa stocul impartit la 10 Huse se va afisa stocul inmultit cu 1.7

Mulineta se va afisa stocul crescut cu 2%. Pentru celelalte produse stocul ramane acelasi

Select codp , stoc,

Case

When denp = Vobler then stoc=stoc/10

When denp = Huse then stoc=stoc*1.7

When denp = Mulinete then stoc=stoc+soc*2%

Else stoc=stoc

End

From Produse;

13. Pentru toate comenzile se adauga 7 luni de la data incheierii comenzii si se afiseaza urmatoarea zi de marti dupa acea data.Select to_char(next_day(add_months(data_i,7), TUESDAY);14. Sa se afiseze pentru fiecare produs , denumirea si valoarea totala a vanzarilor .Select p.denp denumire , sum(p.pret*r.cant) vanzari

From produse p , rand_com r

Where p.codp=r.codp

Grup by p.denp;

15. Pentru toate produsele din nomenclatorul de produse , sa se afiseze denumirea si cantitatea totala vanduta.

Select p.denp denumire , sum (r.cant) cantitate


Where p.codp=r.codp(+)

Grup by p.denp;

16. Sa e afiseze produsele care nu s-au vandut.

Select p.denp denumire


Where p.codp=r.codp(+) and cant is null

17. Sa se afiseze numele fiecarui angajat si numele sefului direct superior .Select muncitori.nume || lucreaza pentru || sefi.nume

From Angajati muncitori , angajati sefi

Where muncitori.cods=sefi.coda;

18. Sa se afiseze valoarea maxima , valoarea medie , valoarea minima si valoarea totala pentru comenzile cu termen de livrare in septembrie. Select avg(val) , max(val) , min(val) , sum(val)

From comenzi

Where data_l like %09%;

Where extract(month from data_l)=9;

19. Sa se calculeze cantitatea medie vanduta.Select avg(sum(r.cant)) cantitatea medie


Where p.codp=r.codp(+) grup by p.denp;20. Sa se afiseze termenul de livrare al ultimei comenzi si termenul de livrare al celei mai vechi comenzi.

Select min(data_l) ultima , max(data_l) veche From comenzi ;

21. Sa se afiseze numarul de produse vandute.Select count(distinct(codp)) from rand_com;

22. Sa se afiseze cantitatea medie vanduta , numai pentru acele produse a caror cantitate medie vanduta este mai mare decat 1000

Select p.denp denumire , avg(r.cant) cant_med

From produse p , rand_com r Where r.codp=p.codp

Group by p.denp Having avg(r.cant)>1000;23. Sa se afiseze persoanele angajate la acelasi department cu Marin Ion.


Where coddep=(select coddep from Angajati where nume= Marin Ion)And nume < > Marin Ion ;

24. Sa se afiseze produsele care au cantitatea minima vanduta mai mare decat cea corespunzatoare produsului Cort

Select p.denp denumire , min(r.cant) cant_min

From rand_com r , produse p

Where r.codp=p.codp

Group by p.denp

Having min(r.cant)>(select min(cant) from rand_com

where copdp=(select codp from produse where denp= Cort));

25. Sa se afiseze detalii despre angajatii care au acelasi sef si aceeiasi functie cu angajatul care are codul de angajat 2 si 5.

Select coda, functie , cods from Angajati

Where (functie , cods ) in (select functie, cods from angajati where coda in (2,5))

And coda not in (2,5);

26. Sa se afiseze numele , valoarea totala comandata si numarul de comenzi pentru toate produsele care au fost comandate de cel putin 2 ori si a caror valoare totala este diferita de 20000.

Select p.denp , sum(r.cant*p.pret) valoare , count(r.nrcom)From produse p, rand_com r

Where p.codp=r.codp

Having count(r.nrcom)>=2

INTERSECT

Select p.denp , sum(r.cant*p.pret) valoare , count(r.nrcom)

From produse p, rand_com r

Where p.codp=r.codp

Having sum(r.cant*p.pret) < > 20000;

27. Afisati comenzile care au nr comenzii intre 1000 si 2500 eliminandu-le pe cele care au valoarea 640000 sau 760000.

Select * from comenzi

Where nrcom between 1000 and 2500

MINUS

Select * from comenzi

Where val in (640000,760000);

Gestiunea altor obiecte ale bazei de date

Tabele virtuale :1. Sa se creeze o tabela virtuala Ang_2000 care va contine informatii despre persoanele angajate in 2000.Create view Ang_2000 as select * from Angajati

Where extract(year from data_ang)=2000;

2. Sa se strearga angajatul Negrea din aceasta tabela. Delete from Ang_2000 where coda=1;

-sa se anuleze stergerea-

Rollback;

3. Sa se stearga tabela virtual create anterior.

drop view Ang_2000;

Secvente:

4. Sa se creeze o secventa Com_nr care incepe cu valoarea 1000 , pasul de incrementare este 10 , iar valoarea maxima 3300. Secventa se va folosi pentru a asigura unicitatea atributului nrcom din tabela Comenzi.Create sequence Com_nr

Increment by 10 start with 1000

Maxvalue 3300 noncycle

5. Sa se stearga secventa.

Drop sequence Com_nr

Indecsi:

6. Sa se creeze un index pe atributul denc din tabela Clienti.

Create index Cl_den_index on Clienti(denc);7. Sa se stearga indexul.

Drop index Cl_den_index ; Sinonime:

8. Sa se creeze un sinonim public pentru tabela Produse a utilizatorului Student.Create public synonym Prod for Student.Produse;

9. Sa se stearga sinonimul.

Drop synonym Prod;

Proiect Ivascu Claudia

Documents

Transcript of Proiect Ivascu Claudia