PROIECT BAZE DE DATE

PROIECT BAZE DE DATE

Sa se realizeze o aplicatie cu baze de date ,in Oracle,pentru evidenta imprumuturilor la o agentie de inchiriere DVD-uri.Dvd-urile sunt imprumutate unor clienti si pentru fiecare dvd exista mai multe copii.

Tabela Clienti contine date despre persoanele care solicita o cerere:cod_cl,BI,Numele prenumele,adresa,telefonul si varsta acestora.

Tabela Cereri contine date despre imprumuturile facute :nr cererii,codul clientului,data realizarii cererii,si codul salariatului care a intocmit cererea.

Tabela Salariati contine inregistrarile legate de salariatii agentiei:codul salariatului,numele,vechimea,functia si varsta.

Tabela Dvd_uri contine date despre codul dvd_ului,titlul,categoria,anul aparitiei,nr de exemplare al fiecarui dvd,starea dvd_ului(disponibil sau nu) si pretul de inchiriere al dvd_ului,calculat in functie de anul aparitiei,si de popularitatea lui.

Tabela Rand_imprumut contine date despre numarul cererii,codul dvd_ului ,si data restituirii acestuia.

1:n

1:n

n:n

n:n

Tabela CLIENTI

Tabela DVD_uri

Tabela SALARIATI

Tabela CERERI

Tabela RAND_IMPRUMUT

Create table CLIENTI

Cod_cl number(4) primary key,

BI number (6),

Nume varchar2(35),

Prenume varchar2(20)

Adresa varchar2(50),

Telefon number(10),

Varsta number(2)

Create table SALARIATI

Cod_sal number(3) primary key,

Nume varchar2(35),

Vechime number(2),

Functie varchar2(10),

Sex varchar2(1)

Create table CERERI

Nr_cerere number(5) primary key,

Cod_cl number(4) references CLIENTI (cod_cl),

Cod_sal number(3) references SALARIATI (cod_sal),

Data_cerere date

Create table DVD-uri

Cod_dvd number(6) primary key,

Titlu varchar2(30),

Categorie varchar2(10),

An_ap number(4),

Nr_ex number(10),

Stare varchar2(12),

Pret number(4)

Create table RAND_IMPRUMUT

Nr_cerere number(5) references CERERI(Cod_cl),

Cod_dvd number(6) references DVD-uri(cod_dvd),

Data-rest date

Insert into CLIENTI values('100','003112','ANDREI ','ANICUTA' ,'str. Capitan Stanculescu,nr.1, Alexandria','0744828171','21');

Insert into CLIENTI values (110,001233,'PETCU','ANDREI','Str. Victoriei,nr. 4,Tr-Magurele',0765703425,'40');

Insert into CLIENTI values ('130','003450','OPRICA', 'IOANA', 'Str.1Mai,nr.7,Constanta',0723459635,'30');

Insert into CLIENTI values ('105','002400','DIMANCEA','LUCIANA', 'Str.Mihai Eminescu, nr.2, Bucuresti' ,'0745601211','26');

Insert into CLIENTI values ('111','00342100','TURCU','SIMONA','Str. Independentei,nr.8,Bucuresti','0724506780','35');

Insert into CLIENTI values ('240','005621','BREZAE','VALENTIN','Str. Pompierilor,nr5,Bucuresti','0721901290','49');

Insert into SALARIATI values('008','Petrescu Camelia','3',casier,'F');

Insert into SALARIATI values('012','Petculescu Ion','1',agent-vanzari,'M');

Insert into SALARIATI values('120','Popescu Alin','2',contabil,'M');

Insert into SALARIATI values ('122','Anghel Mihai','3',agent-vanzari,'M');

Insert into SALARIATI values ('005','Popovici Angela','1',agent-vanzari, 'F');

Insert into SALARIATI values ('027','Tanase Ionut','3',agent-vanzari,'M');

Insert into CERERI values('011', '100', '0045','10-apr-2007','122');

Insert into CERERI values('080','130','4037','05-may-2007','005');

Insert into CERERI values('056','130','2985','11-mar-2007','122');

Insert into CERERI values ('104','110','4040','18-apr-2007','012');

Insert into CERERI values ('090','240','1230','22-apr-2007','005');

Insert into CERERI values ('130','130','1231','23-apr-2007','027');

Insert into RAND_IMPRUMUT values('011', ,0045', to_date('14-apr-2007','dd-mon-yyy'));

Insert into RAND_IMPRUMUT values('104','4040',to_date('20-apr-2007','dd-mon-yyyy'));

Insert into RAND_IMPRUMUT values ('090','1230',to_date('25-apr-2007','dd-mon-yyyy'));

Insert into RAND_IMPRUMUT values ('130','1231',to_date('28-mar-2007','dd-mon-yyyy'));

Insert into RAND_IMPRUMUT values ('056','2985',to_date('15-mar-2007','dd-mon-yyyy'));

Insert into RAND_IMPRUMUT values ('080',4037',to_date('10-may-2007','dd-mon-yyyy'));

Insert into DVD-uri values (1230,'Titanic','romantic',2000,'9', 'disponibil', '5');

Insert into DVD-uri values(1231,'Singur Acasa 2','comedie','2002', '5', 'disponibil', '5');

Insert into DVD-uri values(4040,'Ocean Eleven','actiune',2004,'0', 'indisponibil','6');

Insert into DVD-uri values(4037,'Man on fire','drama',2004, '2', 'disponibil','6');

Insert into DVD-uri values(2985,'Beethoven','comedie',2002,'5', 'disponibil','5');

Insert into DVD-uri values (3409,'The ring 1','horror',2005,'0', 'indisponibil', '7');

B. 1 Sa se adauge in tabela CLIENTI coloana email avand tipul varchar2(30).

Alter table to CLIENTI

Add (email varchar2(30));

2. Sa se modifice proprietatile campului Nr_cerere ,din tabela CERERI astfel incat lungimea acestuia sa fie de 6.

Alter table CERERI

modify(nr_cerere number(6));

3. Adaugati restrictia de integritate Verif_varsta care sa nu permita introducerea in campul Varsta a unor valori mai mici de 18 ani.

Alter table Clienti

Add (Constraint verif_varsta check (varsta>18);

4.Sa se redenumeasca tabela SALARIATI in PERSONAL

Alter table SALARIATI

Rename to PERSONAL;

5.Dezactivati restrictia de integritate Verif_varsta.

Alter table Clienti

Disable Constraint Verif_varsta;

6. Sa se stearga coloana vechime din tabela SALARIATI

Alter table SALARIATI

drop column vechime;

C. 1. Sa se actualizeze starea si nr_ex ale dvd-ului cu codul 110 cu starea si nr_ex ale dvd-ului cu codul 100:

UPDATE DVD-uri

SET (STARE,NR_EX)=(SELECT STARE, NR_EX FROM DVD-URI

WHERE COD_DVD='100')

WHERE COD_DVD='110';

Sa se stearga cererile din anul 2007.

Delete from CERERI

where data_cerere like '%07';

3. Sa se stearga cererile catre clientii cu codul 105sau111.

Delete from CERERI

where cod_cl=105

or cod_cl=111;

Sau

Delete from CERERI

where cod_cl in(105,111);

4. Sa se scada cu 2 dvd-uri numarul de exemplare ale filmului "Titanic":

UPDATE DVD-uri

SET NR_EX=NR_EX-2

WHERE lower(titlu)='Titanic';

5. In tabela DVD-uri sa se modifice starea dvd-ului cu cod-dvd egal cu 130, acesta fiind indisponibil.

Update DVD-uri

Set stare='indisponibil'

where cod_dvd=130;

D.

1. Sa se selecteze cererile solicitate de clientul cu codul = 103:

SELECT * FROM cereri

WHERE cod_cl = '103'

2. Sa se selecteze cererile incheiate de agentul 'Dimancea Luciana' numai in luna aprilie:

SELECT clienti.*, cereri.*

FROM clienti, cereri

WHERE clienti. cod_cl = cereri. cod_cl

AND lower(cereri.data_cerere) like '%aprilie%'

AND upper(clienti.numeclient) = 'DIMANCEA LUCIANA

3. Sa se selecteze titlurile dvd-urilor la care nr_ex a fost maxim.

Select titlu from DVD_uri

where nr_ex=(select max(nr_ex)from DVD_uri);

Sa se selecteze numai clientii din Bucuresti

Select * from CLIENTI

Where upper(adresa) like'%BUCURESTI%';

5 Sa se scrie decodat sexul salariatilor

Select decode(sex,'M','masculin','F','feminin','N','necunoscut')

from SALARIATI;

6 Sa se selecteze CERERILE din luna martie facute pentru 1 DVD:

Select c.nr_cerere from cereri c, RAND_IMPRUMUTr

where c.n_cerere=r.nr_cerere

and to_char(data,'mm')='03'

group by c.nr_cerere

having count(*)=1;

7 Sa se afiseze codul dvd_ului, titlul si data_rest persoanei corespunzatoare:

Select p.cod_dvd, p.titlu, r.data_rest

fromDVD_uri p, RAND_IMPRUMUT r

where p.cod_dvd=r.cod_dvd (+);

8 Sa se afiseze dvd-urile care au numarul de exemplare intre 5 si 7 fara cele care au numarul intre de 8 si 10 bucati.

Select * from DVD_uri

where nr_ex between 5 and 7

minus

select * from DVD_uri

where nr_ex in(8, 10);

9Sa se afiseze numarul total de cereri

Select count(nr_cerere)'nrcereri'from CERERI;

10Sa se selecteze denumirea dvd-urilor si nr de cereri pentru dvd-urile solicitate de maxim 2 ori a dvd-urilor indisponibile.

Select p.titlu, count(r.nr_cerere) nrcereri

from DVD_uri p, RAND_IMPRUMUT r

where p.cod_dvd=r.cod_dvd

group by p.titlu

having count(r.nr_ex)<=3

intersect

select p.titlu,count(r.nr_cerere) nrcereri

fromDVD_uri p, RAND_IMPRUMUT r

where r.cod_dvd=p.cod_dvd

group by p.titlu

where stare= 'indisponibil';

11Sa se afiseze denumirea dvd_ului si categoria lui:

Select DVD_ul : ' || initcap(titlu) ||' este ' || categorie ||'

From DVD_uri;

Sa se calculeze comisionul salariatilor in functie de functia ocupata:

• 0.1% din valoarea cererilor sale daca functia este 'contabil'
• 0.2% din valoarea cererilor sale daca functia este 'agent_vanzari'
• 0.3% din valoarea cererilor sale daca functia este 'director'

Pentru celelalte functii comisionul va fi 0.

Select s.nume, s.functie,

(case when lower(s.functie) ='director' then 0.3

when lower(s.functie)='agent_vanzari' then 0.2

when lower(s.functie)='contabil' then 0.1

else 0 end)*sum(r.nr_ex*r.pret) valoare_comision

from salariati s, cereri c, rand_imprumut r

where s.cod_sal=c.cod_sal

and c.nr_cerere=r.nr_cerere

group by s.nume, s.functie;

Sa se afiseze dvd_urile care au nr cererii cel mai mic:

SELECT p.titlu, r.nr_cerere

FROM dvd_uri p, nr_cerere r

WHERE p.cod_dvd = r.cod_dvd

And r.nr_cerere=(select min(rand_imprumut.nr_cerere) from rand_imprumut);

14. Sa se selecteze cererile care au fost realizate de salariatii cu cod_sal acelasi ca al salariatului 'Tanase Ionut'

SELECT * FROM cereri

WHERE cod_sal=

(SELECT cod_sal FROM salariati WHERE nume= 'TANASE IONUT');

Sa se afiseze numele dvd-ului concatenata cu categoria si lungimea atributului numele dvd_ului , numai pentru dvd_urilor al caror nume incepe cu "T"

SELECT titlu, concat(titlu,categorie), length(titlu)

FROM DVD_uri

WHERE substr(titlu,1,1)='T';

16.Sa se afiseze perioada de timp corespunzatoare (in saptamani) intre data incheierii cererii si data curenta:

SELECT nr_cerere, round((sysdate-data_cerere)/7) saptamani

FROM cereri;

E.

1.Sa se creeze un index pe tabela Clienti pe coloana nume client:

CREATE INDEX CLIENTI_NUMECL_IDX ON CLIENTI(NUME)

2.Sa se creeze o secventa pentru asigurarea unicitatii cheii primare din tabela Cereri.

Create sequence seq_nr_cerere

start with 100 Increment by 1

Maxvalue 500 nocycle

3. Sa se modifice pasul de incrementare pentru secventa anterioara:

Alter sequence seq_nr_cerere increment by 10;

4. Sa se realizeze o tabela virtuala care sa contina numai dvd-urile din categoria comedie.

Create view DVD_COM_V

as select cod-dvd COD_DVD, den TITLU, cat CATEGORIE

from CERERI

where cat='comedie';

5.Sa se creeze un sinonim pentru tabela RAND_IMPRUMUT:

Create synonym detalii_cerere for RAND_IMPRUMUT;