PROIECT BAZA DE DATE( PL SQL)

5/25/2018 PROIECT BAZA DE DATE( PL SQL)

1/41

ACADEMIA DE STUDII ECONOMICE BUCURETI

Proiect Baze de Date

Gestiunea unei galerii de art

Clonda Alexandra,CSIE,seria A,grupa 1023

Ianuarie 2013


2/41

2

1. Descrierea bazei de date

Baza de date este format din patru tabele cu legturi ntre ele, pentru a asigura o

implementare ct mai accesibil i mai potrivit a gestiunii operelor vndute ntr -o galerie deart.

Cele patru tabele conin date despre expoziii, clieni, opere i artiti, astfel:

- EXPOZIIE: codul expoziiei, numele expoziiei, data deschiderii, data nchiderii,adresa acesteia;

- ARTITI: CNP artist, nume artist, adres, telefon, e-mail;- CLIENI: CNP client, nume client, adres, telefon, e-mail;- OPERE: codul operei, expoziia din care face parte, artistul care a realizat-o ,

clientul care a cumprat-o, preul, data vnzrii.

Legturile ntre tabele se fac prin intermediul tabelei OPERE, o oper avnd ca atribute

(cheie strin compus) cnp-ul artistului, cnp-ul autorului i codul expoziiei.


3/41

3

2. Schema conceptual

CLIENTI

Cnp_client

Nume

Adresa

Telefon

e-mail

EXPOZITII

Cod_expozitie

Data_deschiderii

Data_inchiderii

Nume

Adresa

OPERE

Cod_opera

Nume

Cod_expozitieCnp_artist

Cnp_client

Data_vanzarii

ARTISTI

Cnp_artist

Nume

Adresa

Telefon

e-mail

n

nn

11

1


4/41

4

3. Operaii DDL

3.1. Crearea tabelelor

create table customers(cnp_client number(13) primary key,nume varchar2(40),adresa

varchar2(50),telefon number(15),email varchar2(50));

create table expozitii(cod_expo varchar2(20) primary key,nume varchar2(50),data_deschiderii date,data_inchiderii date,adresa varchar2(50));


5/41

5

create table artisti(cnp_artist number(13) primary key, nume varchar2(50), adresa varchar2(50),

telefon number(15), email varchar2(50));

create table opere(cod_opera number(15) primary key, denumire varchar2(50), pret number(7),

cod_expo references expozitii(cod_expo), cnp_artist references artisti(cnp_artist), data_vanzarii date);


6/41

6

3.2. Actualizarea structurii tabelelor

Schimbarea denumirii tabelei CUSTOMERS cu CLIENTI:alter table customersrename to clienti;

Adugarea coloanei cnp_client n tabela OPERE:alter table opere add (cnp_client varchar2(50));


7/41

7

Modificarea tipului coloanei cnp_client din tabela OPERE, fcnd-o de tip numr:

alter table opere modify(cnp_client number(13));

Crearea unei legturi ntre tabela OPERE i tabela CLIENI prin intermediul atributulcnp_client (adugarea unei constrngeri de tip foreign key)

alter table opere add(constraint fk_opere foreign key(cnp_client) references

clienti(cnp_client));


8/41

8

Adugarea unei constrngeri care s impun ca preul operelor s fie mai mare dect 10: alter table opere add(constraint ck_pret check(pret>10));

Setarea constrngerii anterioare (ck_pret) ca DISABLED (modificarea statusului):alter table opere add(constraint ck_pret check(pret>10));


9/41

9

4. Adugarea de nregistri n tabele

Pentru tabela ARTITI:

insert into artisti values('2890423295890','Maria Ionescu','Bucuresti, Bd. Magheru, nr. 50', '0213456798',

'[email protected]');

insert into artisti values('1780321678955', 'Ciobotea Andrei', 'Bucuresti, Bd. Regina Elisabeta, nr. 17',

'0213456889', '[email protected]');

insert into artisti values('2680312879445','Ionescu Paraschiva','Campina, Str. Alexandru Odobescu, nr. 23',

'0244336754',null);

insert into artisti values('1560321564665','Ghiteanu Petre','Ploiesti, Bd. Republicii, nr. 150', '0244556721',

null);

insert into artisti values('1600312543446','Vivian Dan','Braila, Str. Pacii, nr. 11', '0733453221',


insert into artisti values('2680319445343','Irimia Luminita','Targu Jiu, Bd. Constantin Brancusi, nr. 12',


insert into artisti values('1690423444534','Miclea Dragos','Filipestii de padure,Str. Centrala, nr. 45',


insert into artisti values('1561123455456','Gurzun Lucian','Bucuresti, Bd. Crangasi, nr. 120', '0213467556',


insert into artisti values('2750904589032','Papusoiu Mariana','Cluj-Napoca, Bd. Maracinelui, nr. 76',


insert into artisti values('1650323564454','Dan Cristian','Constanta, Str. Popa Tatu, nr. 554', '0732675342',



10/41

10

Pentru tabela CLIENTI:

insert into clienti values('1890423345654','Puricelu Ciprian','Bucuresti, Bd. Magheru, nr.

540','0216756798',null);

insert into clienti values('2890327678955','Banu Loredana','Ploiesti, Bd. Republicii, nr.

172','0244765493','[email protected]');

insert into clienti values('2870312879445','Ionescu Ioana','Campina, Str. Alexandru Odobescu, nr.

29','0745221123',[email protected]);

insert into clienti values('1700321564665','Bucurei Petru','Breaza, Str. Mare, nr. 150',null,null);

insert into clienti values('1680312543446','Marinescu Dan','Braila, Str. Alexandru Ioan Cuza, nr.

134','0733953221','[email protected]');

insert into clienti values('2700319445343','Pioara Diana','Targu Mures, Bd. Constantin cel mare, nr.

132','0722451221','[email protected]');insert into clienti values('1790423444534','Bucur Marian','Galati ,Str. Toamnei, nr.

35','0765781234',null);

insert into clienti values('1901123455456','Badiceanu Lucian','Campina, Str. B.P. Hasdeu, nr.

10','0722467556','[email protected]');

insert into clienti values('27509045778032','Popescu Mihaela','Crivina, Bd. Maracinilor, nr.

796','0723543321',null);

insert into clienti values('1750323564454','Popovici Cristian','Bucuresti, Str. Ion Agarbiceanu , nr.

345','0733675342','[email protected]');


11/41

11

Pentru tabela EXPOZITII:

insert into expozitii values('expo1','Naturi Statice', to_date('29/01/09 ',' dd/mm/yy'),to_date('25/02/09

',' dd/mm/yy'),'Sun Art Studio, Ioan Florescu 13, Bucuresti ');

insert into expozitii values('expo2','Flori', to_date('12/01/07 ',' dd/mm/yy'),to_date('25/02/07 ','

dd/mm/yy'),'Sun Art Studio, Ioan Florescu 13, Bucuresti ');

insert into expozitii values('expo3','Portretul modern', to_date('15/02/09 ','

dd/mm/yy'),to_date('15/04/09 ',' dd/mm/yy'),'Sun Art Studio, Ioan Florescu 13, Bucuresti ');

insert into expozitii values('expo4','Parodii si caricaturi', to_date('01/01/09 ','


insert into expozitii values('expo5','Feminin', to_date('01/03/08 ',' dd/mm/yy'),to_date('01/04/08 ','

dd/mm/yy'),'Muzeul national de arta contemporana, Izvor, Bucuresti ');

insert into expozitii values('expo6','Inaripari', to_date('20/09/08 ',' dd/mm/yy'),to_date('25/11/08 ','dd/mm/yy'), 'Muzeul national de arta contemporana, Izvor, Bucuresti ');

insert into expozitii values('expo7','Miresme de primavara', to_date('10/03/09 ',' dd/mm/yy'),

to_date('10/05/09 ',' dd/mm/yy'),'Galeria Veroniki Art, Intrarea Murmurului 2, Bucuresti ');

insert into expozitii values('expo8','Pastele la romani', to_date('14/03/07 ','


insert into expozitii values('expo9','Relativitati', to_date('17/01/09 ',' dd/mm/yy'),to_date('25/08/09 ','

dd/mm/yy'), 'Muzeul national de arta contemporana, Izvor, Bucuresti ');

insert into expozitii values('expo10','Pitoresc in peisajele romanesti', to_date('20/07/08 ',' dd/mm/yy'),

to_date('27/10/08 ',' dd/mm/yy'), 'Galeria Veroniki Art, Intrarea Murmurului 2, Bucuresti ');


12/41

12

Pentru tabela OPERE:

insert into opere values ('1', 'Trandafiri',450,'expo1', '2890423295890', to_date ('23/02/09',

'dd/mm/yy'), '1790423444534');

insert into opere values ('123', 'Flori de camp',350,'expo1', '2890423295890', to_date ('23/02/09',

'dd/mm/yy'), '1790423444534');

insert into opere values ('2', 'Prietenii','500','expo3','2890423295890',to_date('15-03-2009','dd-mm-

rrrr'),'2700319445343');

insert into opere values('3', 'Valea Prahovei','1100','expo10',' 1650323564454',to_date('25-08-

2009','dd-mm-rrrr'),' 1750323564454');

insert into opere values('4', 'Ioana','650','expo3',' 2750904589032',to_date('03-04-2009','dd-mm-

rrrr'),' 1750323564454');

insert into opere values('5', 'Timpul','1500','expo9',' 1561123455456',to_date('21-05-2009','dd-mm-rrrr'),' 1890423345654');

insert into opere values('6', 'Intrinsec','1050','expo6',' 1690423444534',to_date('30-10-2008','dd-mm-

rrrr'),' 1890423345654');

insert into opere values('7', 'Presedintele','590','expo4',' 1780321678955',to_date('10-05-2009','dd-

mm-rrrr'),' 1901123455456');

insert into opere values('8', 'Apolitic','670','expo4',' 1560321564665',to_date('04-06-2009','dd-mm-

rrrr'),'2700319445343');

insert into opere values('9', 'Iarna pe malul marii','800','expo10',' 1650323564454',to_date('05-09-

2008','dd-mm-rrrr'),' 1680312543446');

insert into opere values('10', 'Pomi infloriti in Bucuresti','350','expo7',' 2680312879445',to_date('30-

03-2009','dd-mm-rrrr'),' 1901123455456');

insert into opere values('11', 'Invierea','700','expo8',' 2680312879445',to_date('12-04-2007','dd-mm-

rrrr'),' 1680312543446');

insert into opere values('12', 'El si ea','900','expo3',' 1560321564665',to_date('16-03-2009','dd-mm-

rrrr'),' 2890327678955');

insert into opere values('13', 'Ingerii','760','expo6',' 1650323564454',to_date('14-19-2008','dd-mm-

rrrr'),' 2750904577832');

insert into opere values('14', 'Ceahlaul','490','expo10',' 1780321678955',to_date('15-09-2008','dd-mm-

rrrr'),' 2750904577832');

insert into opere values('15', 'Pe malul Dunarii','900','expo10',' 2680319445343',to_date('15-08-

2008','dd-mm-rrrr'),' 1700321564665');

insert into opere values('16', 'Inlantuiri','1200','expo5',' 1780321678955',to_date('25-03-2008','dd-

mm-rrrr'),'2700319445343');


13/41

13


14/41

14

5. Modificarea datelor

5.1. S se mreasc preul operelor cu 100 pentru operele care au preul mai mic

de 500 RON

update opere set pret=pret+100 where pret


15/41

15

nainte de modificare:

Dup modificare: (4 rows updated)


16/41

16

5.2. S se reducpreul operelor din expoziia Pitoresc in peisajele romanesticu

100.

update opere

set pret=pret-100

where cod_expo=(select cod_expo from expozitii where lower(nume)='pitoresc in peisajele

romanesti');



17/41

17

Dup modificare:


18/41

18

5.3. Pentru toi artitii care au cmpul email nul, s se adauge adresa de email

[email protected]

update artisti

set email='[email protected]'

where email is null;



19/41

19

Dup modificare:


20/41

20

5.4. S se modifice data vnzrii operelor din expoziia cu codul expo3,

nlocuindu-se cu data nchiderii expoziiei respective.

update opere

set data_vanzarii=(select data_inchiderii from expozitii where cod_expo='expo3')

where cod_expo='expo3';



21/41

21

Dup modificare:


22/41

22

5.5. S se modifice telefonul clientului cu cnp-ul egal cu 1901123455456. Noul

numr de telefon va fi : 0244337491.

update clienti

set telefon='0244337491'

where cnp_client='1901123455456';


Dup modificare:


23/41

23

6. Interogri

6.1. S se afieze operele care au preul ntre 500 i 1000, excluzndu-le pe cele cu preul

de 700.

select cod_opera, denumire, pret from opere

where pret between 500 and 1000

minus

select cod_opera, denumire, pret from opere

where pret=700;


24/41

24

6.2. S se afieze numele, adresa i telefonul artitilor care au 2 sau mai multe

opere n galerie.

select nume,adresa,telefon from artisti

where cnp_artist in

(select cnp_artist from opere

group by cnp_artist

having count(cnp_artist)>=2)


25/41

25

6.3. S se selecteze numele clienilor care au cumprat opere cu o valoare total

mai mare de 1200.

select nume from clienti

where clienti.cnp_client in

(select cnp_client

from opere

group by cnp_client

having sum(pret)>=1200);


26/41

26

6.4. S se calculeze discountul pentru clieni n funcie de valoarea total cumprat astfel:

-ntre 1000 i 1500 RON discountul reprezint 5% din valoarea total cumprat

-ntre 1500 i 2000 RON discountul reprezint 10% din valoarea total cumprat

- ntre peste 2000 RONdiscountul reprezint 15% din valoarea total cumprat

select nume, sum(pret) val_totala, 0.05*sum(pret) discount

from clienti, opere

where clienti.cnp_client=opere.cnp_client

group by nume

having sum(pret) between 1000 and 1500

unionselect nume, sum(pret) val_totala, 0.10*sum(pret) discount

from clienti, opere


group by nume

having sum(pret) between 1500 and 2000

union

select nume, sum(pret) val_totala, 0.15*sum(pret) discount

from clienti, opere


group by nume

having sum(pret)>=2000;


27/41

27

Rezolvare:


28/41

28

6.5. S se afieze artitii care nu au nici o oper momentan n gestiunea galeriei.

select nume,artisti.cnp_artist

from opere, artisti

where opere.cnp_artist(+)=artisti.cnp_artist

minus

select nume, artisti.cnp_artist

from opere, artisti

where opere.cnp_artist=artisti.cnp_artist;


29/41

29

6.6. S se afieze numele, data deschiderii, adresa i numrul de expozani pentru

expoziiile din anul 2009 care au 2 sau mai muli expozani.

select nume, data_deschiderii, adresa , count(opere.cod_expo) numar_expozanti

from expozitii,opere

where expozitii.cod_expo = opere.cod_expo and extract (year from data_deschiderii) =

'2009'

group by nume,data_deschiderii,adresa

having count(opere.cod_expo)>=2;


30/41

30

6.7. S se afieze artitii care au expuse 3 sau mai multe opere, dar i pe cei care au expuse opere in

valoare de cel putin 1000 Ron.

select nume,sum(pret) valoare_opere_expuse, count(cod_opera) numar_opere

from artisti,opere

where artisti.cnp_artist=opere.cnp_artist

group by nume

having sum(pret)>=1000

union

select nume,sum(pret) valoare_opere_expuse, count(cod_opera) numar_opere

from artisti,opere where artisti.cnp_artist=opere.cnp_artist

group by nume

having count(cod_opera)>=3;


31/41

31

6.8. S se afieze numele, data deschiderii i durata n luni pentru expoziiile la care expune i

artistul cu cnp-ul egal cu '2890423295890'.

select nume, data_deschiderii, round(months_between(data_inchiderii,data_deschiderii))

durata_in_luni

from expozitii

where cod_expo in (select cod_expo from opere where cnp_artist='2890423295890')


32/41

32

6.9. S se afieze din ce fel de reea de telefonie (fix saumobil) fac parte clienii.

select nume,

case substr(telefon,1,1)

when '7' then 'retea mobila'

else 'retea fixa'

end tip_retea_telefoniefrom clienti


33/41

33

6.10. S se afieze numele i categoria din care face parte fiecare artist, lund n considerare

anul naterii, astfel:

- dac este nscut dup 1980, este considerat amator

- dac este nscut ntre 1970 i 1980 este considerat de nivel mediu

- dac este nscut nainte de 1970 este considerat profesionist

select nume,

case

when to_number(substr(cnp_artist,2,2))>80 then 'artist amator'

when to_number(substr(cnp_artist,2,2)) between 70 and 80 then 'artist de nivel mediu'

else 'artist profesionist'

end categorie_artist

from artisti


34/41

34

6.11. S se afieze numele clienilor mpreun cu apelativul nfaa numelui (Domnul sau

Doamna).

select

decode(substr(cnp_client,1,1),'1','Domnul','2','Doamna','neidentificat') apelativ,

numefrom clienti


35/41

35

7. Gestiunea altor obiecte ale bazei de date: vederi, indeci, sinonime, secvene

7.1. Tabele virtuale

S se creeze o tabel virtual care s conin nregistrri din tabela OPERE care au un pre mai

mic sau egal cu 500.

create view tablouri_ieftine

as select * from opere where pret


36/41

36

S se afieze valoarea total i numrul tablourilor cu preul mai mic sau egal cu 500 RON ,

folosindu-se tabela virtual creat anterior.

select sum(pret) val_tot_tablouri_ieftine,count(cod_opera) numar_tablouri_ieftine

from tablouri_ieftine


37/41

37

7.2. Indeci

S se creeze un index pentru atributul data_deschiderii din tabela EXPOZITII.

create index datad_idx

on expozitii(data_deschiderii)


38/41

38

S se tearg indexul datad_idx creat anterior.

drop index datad_idx


39/41

39

7.3. Sinonime

S se creeze sinonimul AUTORI pentru tabela ARTITI.

create synonym autori

for artisti;


40/41

40

S se selecteze datele din tabela ARTISTI folosind sinonimul AUTORI.

select * from autori


41/41

41

7.4. Secvene

S se creeze o secven care s permit introducerea de date n tabela expozitii astfel nct codurile

expozitiilor s ia valori n intervalul (1,1000), consecutiv.

create sequence secv_expo

start with 1

increment by 1

max value 1000

no cycle

PROIECT BAZA DE DATE( PL SQL)

Documents

Transcript of PROIECT BAZA DE DATE( PL SQL)