ACADEMIA DE STUDII ECONOMICE BUCUREȘTI
Proiect Baze de Date Gestiunea unei galerii de artă
Clonda Alexandra,CSIE,seria A,grupa 1023 Ianuarie 2013
1. Descrierea bazei de date
Baza de date este formată din patru tabele cu legături între ele, pentru a asigura o implementare cât mai accesibilă și mai potrivită a gestiunii operelor vândute într -o galerie de artă.
Cele patru tabele conțin date d ate despre expoziții, clienți, opere și artiști, astfel:
-
EXPOZIȚIE: codul expoziției, numele expoziției, expoziției, data deschiderii, data închiderii, închiderii,
adresa acesteia; -
ARTIȘTI: CNP artist, nume artist, adresă, telefon, e -mail;
-
CLIENȚI: CNP client, nume client, adresă, telefon, e -mail;
-
OPERE: codul operei, expoziția din care face parte, artistul care a realizat -o , clientul care a cumpărat-o, prețul, data vânzării.
Legăturile între tabele se fac prin intermediul tabelei OPERE, o operă având ca atribute
(cheie străină compusă) cnp-ul artistului, cnp- ul autorului și codul expoziției.
2
1. Descrierea bazei de date
Baza de date este formată din patru tabele cu legături între ele, pentru a asigura o implementare cât mai accesibilă și mai potrivită a gestiunii operelor vândute într -o galerie de artă.
Cele patru tabele conțin date d ate despre expoziții, clienți, opere și artiști, astfel:
-
EXPOZIȚIE: codul expoziției, numele expoziției, expoziției, data deschiderii, data închiderii, închiderii,
adresa acesteia; -
ARTIȘTI: CNP artist, nume artist, adresă, telefon, e -mail;
-
CLIENȚI: CNP client, nume client, adresă, telefon, e -mail;
-
OPERE: codul operei, expoziția din care face parte, artistul care a realizat -o , clientul care a cumpărat-o, prețul, data vânzării.
Legăturile între tabele se fac prin intermediul tabelei OPERE, o operă având ca atribute
(cheie străină compusă) cnp-ul artistului, cnp- ul autorului și codul expoziției.
2
2. Schema conceptuală
CLIENTI
EXPOZITII
Cnp_client Nume Adresa Telefon e-mail
Cod_expozitie Data_deschiderii Data_inchiderii Nume Adresa
1
1 OPERE
n
Cod_opera Nume Cod_expozitie Cnp_artist Cnp_client Data_vanzarii
n n
ARTISTI
1
3
Cnp_artist Nume Adresa Telefon e-mail
3. Operații 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));
4
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);
5
3.2. Actualizarea structurii tabelelor
Schimbarea denumirii tabelei CUSTOMERS cu CLIENTI:
alter table customers rename to clienti;
Adăugarea coloanei cnp_client în tabela OPERE:
alter table opere add (cnp_client varchar2(50));
6
Modificarea tipului coloanei cnp_client din tabela OPERE, făcând-o de tip număr:
alter table opere modify(cnp_client number(13));
Crearea unei legături între tabela OPERE și tabela CLIENȚI prin intermediul atributul cnp_client (adăugarea unei constrângeri de tip foreign key)
alter table opere add(constraint fk_opere foreign key(cnp_client) references clienti(cnp_client));
7
Adăugarea unei constrângeri care să impună ca prețul operelor să fie mai mare decât 10:
alter table opere add(constraint ck_pret check(pret>10));
Setarea constrângerii anterioare (ck_pret) ca DISABLED (modificarea statusului):
alter table opere add(constraint ck_pret check(pret>10));
8
4. Adăugarea de înregistări în tabele Pentru tabela ARTIȘTI: 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', '
[email protected]'); insert into artisti values('2680319445343','Irimia Luminita','Targu Jiu, Bd. Constantin Brancusi, nr. 12', '0733451221', '
[email protected]'); insert into artisti values('1690423444534','Miclea Dragos','Filipestii de padure,Str. Centrala, nr. 45', '0766781234', '
[email protected]'); insert into artisti values('1561123455456','Gurzun Lucian','Bucuresti, Bd. Crangasi, nr. 120', '0213467556', '
[email protected]'); insert into artisti values('2750904589032','Papusoiu Mariana','Cluj-Napoca, Bd. Maracinelui, nr. 76', '0766543321', '
[email protected]'); insert into artisti values('1650323564454','Dan Cristian','Constanta, Str. Popa Tatu, nr. 554', '0732675342', '
[email protected]');
9
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]');
10
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 ',' dd/mm/yy'),to_date('01/07/09 ',' dd/mm/yy'),'Sun Art Studio, Ioan Florescu 13, Bucuresti '); 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 ',' dd/mm/yy'),to_date('15/05/07 ',' dd/mm/yy'),'Sun Art Studio, Ioan Florescu 13, Bucuresti '); 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, I ntrarea Murmurului 2, Bucuresti ');
11
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-mmrrrr'),'2700319445343'); insert into opere values('3', 'Valea Prahovei','1100','expo10',' 1650323564454',to_date('25-082009','dd-mm-rrrr'),' 1750323564454'); insert into opere values('4', 'Ioana','650','expo3',' 2750904589032',to_date('03-04-2009','dd-mmrrrr'),' 1750323564454'); insert into opere values('5', 'Timpul','1500','expo9',' 1561123455456',to_date('21-05-2009','dd-mmrrrr'),' 1890423345654'); insert into opere values('6', 'Intrinsec','1050','expo6',' 1690423444534',to_date('30-10-2008','dd-mmrrrr'),' 1890423345654'); insert into opere values('7', 'Presedintele','590','expo4',' 1780321678955',to_date('10-05-2009','ddmm-rrrr'),' 1901123455456'); insert into opere values('8', 'Apolitic','670','expo4',' 1560321564665',to_date('04-06-2009','dd-mmrrrr'),'2700319445343'); insert into opere values('9', 'Iarna pe malul marii','800','expo10',' 1650323564454',to_date('05-092008','dd-mm-rrrr'),' 1680312543446'); insert into opere values('10', 'Pomi infloriti in Bucuresti','350','expo7',' 2680312879445',to_date('3003-2009','dd-mm-rrrr'),' 1901123455456'); insert into opere values('11', 'Invierea','700','expo8',' 2680312879445',to_date('12-04-2007','dd-mmrrrr'),' 1680312543446'); insert into opere values('12', 'El si ea','900','expo3',' 1560321564665',to_date('16-03-2009','dd-mmrrrr'),' 2890327678955'); insert into opere values('13', 'Ingerii','760','expo6',' 1650323564454',to_date('14-19-2008','dd-mmrrrr'),' 2750904577832'); insert into opere values('14', 'Ceahlaul','490','expo10',' 1780321678955',to_date('15-09-2008','dd-mmrrrr'),' 2750904577832'); insert into opere values('15', 'Pe malul Dunarii','900','expo10',' 2680319445343',to_date('15-082008','dd-mm-rrrr'),' 1700321564665'); insert into opere values('16', 'Inlantuiri','1200','expo5',' 1780321678955',to_date('25-03-2008','ddmm-rrrr'),'2700319445343'); 12
13
5. Modificarea datelor
5.1. Să se mărească prețul operelor cu 100 pentru operele care au prețul mai mic de 500 RON
update opere set pret=pret+100 where pret<500;
14
Înainte de modificare:
După modificare: (4 rows updated)
15
5.2. Să se reducă prețul operelor din expoziția ”Pitoresc in peisajele romanesti” cu 100.
update opere set pret=pret-100 where cod_expo=(select cod_expo from expozitii where lower(nume)='pitoresc in peisajele romanesti');
Înainte de modificare:
16
După modificare:
17
5.3. Pentru toți artiștii care au câmpul email nul, să se adauge adresa de email
[email protected]
update artisti set email='
[email protected]' where email is null;
Înainte de modificare:
18
După modificare:
19
5.4. Să se modifice data vânzării operelor din expoziția cu codul ”expo3”, înlocuindu-se cu data închiderii expoziției respective.
update opere set data_vanzarii=(select data_inchiderii from expozitii where cod_expo='expo3') where cod_expo='expo3';
Înainte de modificare:
20
După modificare:
21
5.5. Să se modifice telefonul clientului cu cnp-ul egal cu 1901123455456. Noul număr de telefon va fi : 0244337491.
update clienti set telefon='0244337491' where cnp_client='1901123455456';
Înainte de modificare:
După modificare:
22
6. Interogări
6.1. Să se afișeze operele care au prețul între 500 și 1000, excluzându -le pe cele cu prețul 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;
23
6.2. Să se afișeze numele, adresa și telefonul artiștilor 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)
24
6.3. Să se selecteze numele clienților care au cumpărat 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);
25
6.4. Să se calculeze discountul pentru clienți în funcție de valoarea totală cumpărată astfel:
- între 1000 și 1500 RON – discountul reprezintă 5% din valoarea totală cumpărată - între 1500 și 2000 RON – discountul reprezintă 10% din valoarea totală cumpărată - între peste 2000 RON – discountul reprezintă 15% din valoarea totală cumpărată
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 union select nume, sum(pret) val_totala, 0.10*sum(pret) discount from clienti, opere where clienti.cnp_client=opere.cnp_client 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 where clienti.cnp_client=opere.cnp_client group by nume having sum(pret)>=2000;
26
Rezolvare:
27
6.5. Să se afișeze artiștii 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;
28
6.6. Să se afișeze numele, data deschiderii, adresa și numărul de expozanți pentru expozițiile din anul 2009 care au 2 sau mai mulți expozanți.
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;
29
6.7. Să se afișeze artiștii 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;
30
6.8. Să se afișeze numele, data deschiderii și durata în luni pentru expozițiile 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')
31
6.9. Să se afișeze din ce fel de rețea de telefonie (fixă sau mobilă) fac parte clienții.
select nume, case substr(telefon,1,1) when '7' then 'retea mobila' else 'retea fixa' end tip_retea_telefonie from clienti
32
6.10. Să se afișeze numele și categoria din care face parte fiecare artist, luând în considerare anul nașterii, astfel:
- dacă este născut după 1980, este considerat amator - dacă este născut între 1970 și 1980 este considerat de nivel mediu - dacă este născut î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
33
6.11. Să se afișeze numele clienților împreună cu apelativul în fața numelui (Domnul sau Doamna).
select decode(substr(cnp_client,1,1),'1','Domnul','2','Doamna','neidentificat') apelativ, nume from clienti
34
7. Gestiunea altor obiecte ale bazei de date: vederi, indecși, sinonime, secvențe
7.1. Tabele virtuale
Să se creeze o tabelă virtuală care să conțină înregistrări din tabela OPERE care au un preț mai mic sau egal cu 500.
create view tablouri_ieftine as select * from opere where pret<=500;
35
Să se afișeze valoarea totală și numărul tablourilor cu prețul 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
36
7.2. Indecși
Să se creeze un index pentru atributul data_deschiderii din tabela EXPOZITII.
create index datad_idx on expozitii(data_deschiderii)
37
Să se șteargă indexul datad_idx creat anterior .
drop index datad_idx
38
7.3. Sinonime
Să se creeze sinonimul AUTORI pentru tabela ARTIȘTI.
create synonym autori for artisti;
39
You're Reading a Preview Unlock full access with a free trial.
Download With Free Trial
