BAZE DE DATE - Repartitia Studentilor in Caminele A.S.E.-ului pe anul universitar 2006-2007

 

BAZE DE DATE

Repartitia Studentilor in Caminele A.S.E.-ului

pe anul universitar

si plata regiei pana la data de 15 februarie

CERINTE

VARIANTA 1

1. Definirea schemei bazei de date - tabele (minim 4) cu legaturi si alte restrictii de integritate (CREATE TABLE), populate (INSERT).

2. 5 exemple variate cu ALTER TABLE, pe coloane si pe restrictii.

3. 5 exemple variate cu operatiile de actualizare a datelor (comenzile DML - UPDATE, DELETE, [MERGE] pentru inregistrari).

4. Minim 15 exemple cu interogari cat mai variate (SELECT):

Utilizarea operatorilor de comparatie

Utilizarea functiilor la nivel de rand (upper, substr, decode, case, nvl, to_date, to_char)

Utilizarea functiilor de grup si conditii asupra acestora

GROUP BY, HAVING

Join-uri (INNER, OUTER)

Utilizarea operatorilor UNION, MINUS, INTERSECT

Subcereri (cereri imbricate)

[Cereri ierarhice]

Se va puncta complexitatea si originalitatea interogarilor.

5. 5 exemple cu gestiunea altor obiecte ale bazei de date: vederi (se pot realiza view-uri cu cerintele de la punctul D), partitii, indecsi , sinonime, secvente.

VARIANTA 2

A.     Definirea schemei bazei de date - tabele (minim 4), legaturi, alte restrictii de integritate (CREATE TABLE).

B.     Aplicatie cu baze de date avand interfata vizuala (.NET, PHP, JAVA, Oracle Developer). Baza de date va fi obligatoriu ORACLE. Se vor utiliza minim 10 interogari variate (jonctiuni, GROUP BY, HAVING).

CERINTE PRIVIND REDACTAREA SI SUSTINEREA PROIECTULUI

1. Proiectele se vor prezenta la seminar in ultimele 2 saptamani dupa programul discutat in prealabil.

2. Schema bazei de date va fi dupa modelul urmator:

Cu linie continua se va sublinia cheia primara a fiecarei tabele si cu linie intrerupta cheia/cheile externa/externe.

3. Fiecare secventa de comenzi va fi insotita de enuntul problemei. Se va realiza separat un document word unde alaturi de schema bazei de date se va mentiona enuntul si rezolvarea secventelor de comenzi.

De exemplu:

-- Sa se sterga produsele comandate pt care cant<200;

DELETE FROM rindcom

WHERE cant<200;

--Sa se sterga toate inregistrarile din tabela stoc_min:

DELETE FROM stoc_min;

4. Proiectele vor fi aduse pe suport magnetic (CD) si pe hartie si vor contine schema bazei de date precum si scripturile cu cerintele de mai sus. Va fi prezentat un singur CD/grupa continand toate proiectele studentilor.

REzolvare:

--A. Definirea schemei bazei de date - tabele (minim 4) cu legaturi si alte restrictii de integritate (CREATE TABLE), populate (INSERT).

drop table plata_regie cascade constraints;

drop table repartitie_stud cascade constraints;

drop table camin_stud cascade constraints;

drop table cereri_stud cascade constraints;

drop table facultati cascade constraints;

create table facultati(

cod_fac number(10),

nume_fac varchar2(12),

total_cer_stud number(8),

nrloc_alocate number(6),

constraint facultati_pk primary key(cod_fac)

);

create table cereri_stud(

cod_cerere number(6),

cod_stud number(6) ,

nume_stud varchar2(20),

datan date default sysdate,

an_studiu number(2) not null,

cod_fac number(10),

medie number(6,2),

constraint cereri_stud_pk primary key (cod_stud),

constraint cereri_stud_fk1 foreign key (cod_fac) references facultati

);

create table camin_stud(

ncamin varchar2(12) primary key,

nr_locuri number(6) not null,

nr_loc_cam number(1),

administrator varchar2(20),

constraint camin_stud_r1 check (nr_loc_cam<6)

);

create table repartitie_stud(

cod_stud number(6) primary key,

ncamin varchar2(12),

cam number(4),

constraint repartitie_stud_fk1 foreign key (ncamin) references camin_stud,

constraint repartitie_stud_fk2 foreign key (cod_stud) references cereri_stud,

constraint repartitie_stud_r1 check (cam > 0 )

);

create table plata_regie(

cod_chitanta number(10),

data_chit date default sysdate,

luna_plat number(2),

cod_stud number(6),

constraint plata_regie_pk primary key (cod_chitanta),

constraint plata_regie_fk1 foreign key (cod_stud) references repartitie_stud

);

DELETE from facultati;

insert into facultati values ('01','CSIE','403','80');

insert into facultati values ('02','FABBV','230','165');

insert into facultati values ('03','CIG','478','95');

insert into facultati values ('04','REI','312','150');

insert into facultati values ('05','SELS','309','110');

insert into facultati values ('06','COM','460','100');

select * from facultati;

DELETE from cereri_stud;

insert into cereri_stud values ('1000','101','Talmaciu Cristina',to_date('08 19, 1986','mm dd,yyyy'),'2','1','8,9');

insert into cereri_stud values ('1001','232','Razvan Daniel',to_date('02 7, 1984','mm dd,yyyy'),'3','2','9,7');

insert into cereri_stud values ('1002','603','Diaconu Marina',to_date('05 23, 1985','mm dd,yyyy'),'1','6','9,05');

insert into cereri_stud values ('1003','404','Dinu Gabriel ',to_date('06 25, 1985','mm dd,yyyy'),'3','4','9,9');

insert into cereri_stud values ('1004','305','Georgescu Luoana',to_date('10 03, 1986','mm dd,yyyy'),'2','3','8,7');

insert into cereri_stud values ('1005','106','Enache Tudor',to_date('07 12, 1987','mm dd,yyyy'),'1','1','8,2');

insert into cereri_stud values ('1006','507','Stere Marta',to_date('04 29, 1986','mm dd,yyyy'),'2','5','9,30');

insert into cereri_stud values ('1007','408','Martin Alexandru',to_date('02 7, 1986','mm dd,yyyy'),'3','4','7,10');

insert into cereri_stud values ('1008','209','Cernat Alexandru',to_date('01 19, 1987','mm dd,yyyy'),'1','2','8,55');

insert into cereri_stud values ('1009','410','Duica Ramona',to_date('09 20, 1987','mm dd,yyyy'),'4','4','9,10');

insert into cereri_stud values ('1010','311','Craciun Teodora',to_date('09 19, 1986','mm dd,yyyy'),'2','3','7,80');

insert into cereri_stud values ('1011','112','Florescu Dan',to_date('04 1, 1985','mm dd,yyyy'),'3','1','9,2');

insert into cereri_stud values ('1012','513','Stan Diana',to_date('11 14, 1987','mm dd,yyyy'),'1','5','8,9');

insert into cereri_stud values ('1013','114','Lisandru Madalina',to_date('12 19, 1985','mm dd,yyyy'),'3','1','8,87');

insert into cereri_stud values ('1014','415','Manea Elena',to_date('03 21,86','mm dd,yy'),'2','4','9,30');

insert into cereri_stud values ('1015','116','Tudor Tatiana',to_date('12 12, 1983','mm dd,yyyy'),'4','1','9,70');

insert into cereri_stud values ('1016','617','Grigore Dacian',to_date('10 13, 1986','mm dd,yyyy'),'2','1','7,20');

insert into cereri_stud values ('1017','418','Lefteriu Bogdan',to_date('06 11, 1986','mm dd,yyyy'),'1','4','8,75');

select * from cereri_stud;

DELETE from camin_stud;

insert into camin_stud values('Moxa','10','2','Marcu Iulia');

insert into camin_stud values('Belvedere','9','3','Nicolae Marian');

insert into camin_stud values('Agronomie','10','2','Neamtu Ioana');

insert into camin_stud values('Vitan','6','2','Simionescu Sofia');

select * from camin_stud;

DELETE from repartitie_stud;

insert into repartitie_stud values ('101', 'Agronomie','301');

insert into repartitie_stud values ('232' ,'Moxa','102');

insert into repartitie_stud values ('603', 'Belvedere','203');

insert into repartitie_stud values ('404', 'Moxa','102');

insert into repartitie_stud values ('305','Agronomie','302');

insert into repartitie_stud values ('106','Vitan','403');

insert into repartitie_stud values ('507','Belvedere','203');

insert into repartitie_stud values ('209', 'Vitan','403');

insert into repartitie_stud values ('410', 'Vitan','403');

insert into repartitie_stud values ('112', 'Belvedere','202');

insert into repartitie_stud values ('513', 'Vitan','401');

insert into repartitie_stud values ('114', 'Agronomie','301');

insert into repartitie_stud values ('415','Moxa','101');

insert into repartitie_stud values ('116', 'Moxa','101');

insert into repartitie_stud values ('418','Belvedere','202');

select * from repartitie_stud;

DELETE from plata_regie;

insert into plata_regie values ('1001',to_date('01 08,07','mm dd,yyyy'), '10','404');

insert into plata_regie values ('1002',to_date('01 08,07','mm dd,yyyy'), '11','404');

insert into plata_regie values ('1003',to_date('01 08,07','mm dd,yyyy'), '12','404');

insert into plata_regie values ('1004',to_date('11 12,06','mm dd,yyyy'), '10','101');

insert into plata_regie values ('1005',to_date('11 26,06','mm dd,yyyy'), '10','232');

insert into plata_regie values ('1006',to_date('11 30,06','mm dd,yyyy'), '10','603');

insert into plata_regie values ('1007',to_date('11 30,06','mm dd,yyyy'), '10','305');

insert into plata_regie values ('1008',to_date('12 07,06','mm dd,yyyy'), '11','305');

insert into plata_regie values ('1009',to_date('12 11,06','mm dd,yyyy'), '10','507');

insert into plata_regie values ('1010',to_date('12 11,06','mm dd,yyyy'), '11','507');

insert into plata_regie values ('1011',to_date('12 11,06','mm dd,yyyy'), '10','418');

insert into plata_regie values ('1012',to_date('12 15,06','mm dd,yyyy'), '10','209');

insert into plata_regie values ('1013',to_date('12 16,06','mm dd,yyyy'), '11','209');

insert into plata_regie values ('1014',to_date('12 16,06','mm dd,yyyy'), '10','114');

insert into plata_regie values ('1015',to_date('01 08,07','mm dd,yyyy'), '10','415');

insert into plata_regie values ('1016',to_date('12 08,06','mm dd,yyyy'), '11','415');

insert into plata_regie values ('1017',to_date('01 08,07','mm dd,yyyy'), '12','415');

insert into plata_regie values ('1018',to_date('01 08,07','mm dd,yyyy'), '01','415');

SELECT * FROM plata_regie;

-- B. exemple variate cu ALTER TABLE, pe coloane si pe restrictii

-- B1.sa se elimine restrictia in ceea ce priveste numarul de locuri in camera:

Alter table camin_stud drop constraint camin_stud_r1;

--B2.restrictionati valorile pe care poate sa le ia data_n (nu mai mult de 12 avand in vedere ca desemneaza o luna!)

Alter table plata_regie add constraint check_luna check (luna_plat < 13);

--B3.adaugati o noua coloana "adresa" in tabela camin_stud;

Alter table camin_stud add adresa varchar2(20);

--B4.modificati coloana adresa astfel incat sa fie de tipul varchar2(40)

Alter table camin_stud modify (adresa varchar2(40));

--B5.stergeti coloana adresa;

Alter table camin_stud drop column adresa;

--C. 5 exemple variate cu comenzile DML

--C1.triplati numarul de locuri alocate fiecarei facultati care are prima litera din denumire C ;

update facultati set nrloc_alocate=nrloc_alocate*3 where upper(nume_fac) like 'C%';

--C2.mutati toti studentii cu media cuprinsa intre 8 si 9,16 in caminele din Agronomie;

update repartitie_stud set ncamin='Agronomie' where cod_stud in (select cod_stud from cereri_stud where medie between '8,9' and '9,15');

--C3. stergeti chitantele care au fost emise in luna pentru care se face plata;

delete from plata_regie where to_char(data_chit,'mm')=luna_plat;

--C4.stergeti chitanta studentului cu codul 209 din luna noiembrie;

delete from plata_regie where cod_stud='209' and luna_plat=11;

--C5.modificati media si data de nastere a studentului cu codul '617' cu media si data de nastere a stundentului cod 408:

update cereri_stud set (datan, medie)=(select datan, medie from cereri_stud where cod_stud=408) where cod_stud=617;

--D.Interogari(select)

--D1.sa se afiseze facultatile care au studenti cu medii mai mici de 8 , precum si numarul acestora;

select f.nume_fac, count(*) from facultati f, cereri_stud c

where f.cod_fac=c.cod_fac and c.medie<'8,75' group by f.nume_fac order by count(*);

--D2.sa se afiseze media studentilor care au primit camin pe anii de studiu;

Select c.an_studiu, avg(medie) from cereri_stud c, repartitie_stud r where c.cod_stud=r.cod_stud group by c.an_studiu order by avg(medie);

--D3.sa se afiseze studentii care au platit cel putin pe 2 luni regia

select c.nume_stud, count(p.cod_stud) from cereri_stud c, plata_regie p where c.cod_stud=p.cod_stud group by c.nume_stud having count(p.cod_stud)>1 order by count(p.cod_stud);

--D4.sa se afiseze facultatile care au elevi cazati in moxa precum si numarul acestora

select f.nume_fac, count(r.ncamin) from facultati f, cereri_stud c, repartitie_stud r where f.cod_fac=c.cod_fac and c.cod_stud=r.cod_stud and upper(r.ncamin)='MOXA'

group by f.nume_fac;

--D5.sa se afiseze studentii care locuiesc in belvedere si sunt nascuti in anul 85';

select c.nume_stud, r.ncamin from cereri_stud c, repartitie_stud r where initcap(r.ncamin)='Belvedere'

intersect

select c.nume_stud, r.ncamin from cereri_stud c, repartitie_stud r where to_char(c.datan,'yy')='85';

--D6.sa se afiseze cate luni mai are de plata fiecare student

select c.nume_stud,

(case when count(p.cod_stud)=4 then 0

when count(p.cod_stud)=3 then 1

when count(p.cod_stud)=2 then 2

when count(p.cod_stud)=1 then 3

else 4 end) luni_de_plata from cereri_stud c, plata_regie p where c.cod_stud=p.cod_stud(+)

group by c.nume_stud order by count(p.cod_stud) desc;

--D7.sa se afiseze codul studentilor care nu se afla in tabelul plata_regie ca restantieri

select r.cod_stud restantieri, count(p.cod_chitanta) from repartitie_stud r, plata_regie p

where r.cod_stud=p.cod_stud(+) group by r.cod_stud having count(p.cod_chitanta)=0;

--D 8.se se afiseze codul cererii si numele studentilor care nu au primit cazare

select cod_cerere, nume_stud from cereri_stud where cod_stud not in

(select cod_stud from repartitie_stud );

-- D9.sa se afiseze administratorii caminelor pt care au fost platite chitante in luna decembrie:

select distinct cs.administrator admin from camin_stud cs, repartitie_stud r, plata_regie p where p.cod_stud=r.cod_stud and r.ncamin=cs.ncamin and

p.luna_plat=12;

--D10.Afisarea tutoror studentilor care au facut cerere in ordinea descrescatoare a mediilor

select nume_stud, medie from cereri_stud order by medie desc;

--D11.Sa se afiseza codul celei mai recente chitante;

select max(data_chit) from plata_regie;

--D12.afisati pentru fiecare facultate denumirea exacta!

Select cod_fac, decode(lower(nume_fac ),

'csie','Cibernetica, Statistica si Informatica Economica',

'fabbv','Finante, Asigurari, Banci si Burse de Valori',

'cig', 'Contabilitate si Informatica de Gestiune',

'rei','Relatii Economice Internationale',

'sels','Studii Europene in Limbi Straine',

'com','Comert') denumire_exacta from facultati;

--D13.Sa se afiseze media cea mai mica pentru studentii care au primit camin

Select min(c.medie) from repartitie_stud r, cereri_stud c

where c.cod_stud=r.cod_stud;

--D14.sa se afiseze numele caminului concatenat cu numele studentului pentru toti studentii din anul 1 sau 4

select concat(r.ncamin,c.nume_stud) from cereri_stud c, repartitie_stud r where r.cod_stud=c.cod_stud and c.an_studiu in ('1','4');

--D15.sa se afiseze media rotunjita a fiecarui student

Select nume_stud, round(medie,0) from cereri_stud;

--E. Dati 5 exemple cu gestiunea altor obiecte ale bazei de date:

--E1. creati o tabela virtuala numai cu cererile studentilor facultatii cibernetica formata din cod_cerere, numestudent, an_studiu, medie;

create view cereri_cibe as select cod_cerere, nume_stud, an_studiu, medie from cereri_stud where cod_fac=1;

--E2. sa se creeze un sinonim pt tabela camin_stud;

Create synonym caminASE_admini for camin_stud;

--E3. sa se stearga sinonimul creat anterior:

Drop synonym caminASE_admini;

--E4.sa se creeze un index pe tabela facultati pe coloana nume_fac;

Create index facultati_numefac_idx on facultati(nume_fac);

--E5.sa se stearga indexul creat anterior:

Drop index facultati_numefac_idx;