Proiect Sgbd Oracle

PROIECT SGBD - ORACLE

PROIECT SGBD - ORACLE

A. Profesori idprofesor Student idstudent

nume nume

prenume prenume

Disciplina iddisciplina

dendisc

Examene idexamen

iddisciplina

adresa adresa

salariu taxa

nrcredite

nrcred idstudent

idprofesori

sala

nota

Baza de date administreaza activitatea unei facultati. Prin intermediul tabelelor descrise mai jos, este tinuta evidenta profesorilor, studentilor, obiectelor de studio si a examenelor. Tabela “Profesori” contine informatii despre profesori, nume, prenume, adresa si salariul fiecaruia. Tabela ”Student” contine informatii despre student precum numele, prenumele, adresa si faptul daca este la taxa sau nu. Tabela ”Disciplina” contine informatii referitoare la obiectele de studiu ale studentilor si numarul de credite aferent fiecaruia. Tabela “Examene” ofera informatii despre salile in care se vor tine examenele si notele fiecarui student.

Crearea tabelelor DROP TABLE PROFESORI CASCADE CONSTRAINTS; DROP TABLE STUDENT CASCADE CONSTRAINTS; DROP TABLE DISCIPLINA CASCADE CONSTRAINTS; DROP TABLE EXAMENE CASCADE CONSTRAINTS;

CREATE TABLE PROFESORI (idprofesori number(4) CONSTRAINT pk_profesori primary key, Nume varchar2(15) not null, Prenume varchar2(15) not null, Adresa varchar2(30), Salariu number(4)); CREATE TABLE STUDENT (idstudent number(4) CONSTRAINT pk_studenti primary key, Nume varchar2(15) not null,

Prenume varchar2(15) not null, Adresa varchar2(30), Taxa varchar2(2) not null Nrcredite number(2)); CREATE TABLE DISCIPLINA (iddisciplina number(4) CONSTRAINT pk_disciplina primary key, Dendisc varchar(20) not null, Nrcred number(1) not null; CREATE TABLE EXAMENE (idexamen number(4) CONSTRAINT pk_examene primary key, Iddisciplina number(4) not null, Idstudent number(4) not null, Idprofesor number(4) not null, Data date, Sala varchar2(5) not null, Nota number(2), CONSTRAINT FKprofesori FOREIGN KEY (idprofesori) REFERENCES profesori (idprofesori), CONSTRAINT FKdisciplina FOREIGN KEY (iddisciplina) REFERENCES disciplina (iddisciplina), CONSTRAINT FKstudent FOREIGN KEY (idstudent) REFERENCES student (idstudent));

Insert into profesori values (‘1000’,’Tudor’,’Cristian’,’Str. Dorobantilor nr. 15’,’4000’); Insert into profesori values (‘1001’,’Marian’,’Aurel’,’Str. Barnova nr. 131’,’3250’); Insert into profesori values (‘1002’,’Vlad’,’Andrei’,’Str. Victoriei nr. 23’,’4800’); Insert into profesori values (‘1003’,’Golondac’,’Remes’,’Str. Crizantemelor nr. 22’,’2100’); Insert into profesori values (‘1004’,’Eminescu’,’Mihai’,’Str. Aminitirilor nr. 220’,’7400’); Insert into profesori values (‘1005’,’Arghezi’,’Tudor’,’Str. C.C.Arion nr. 3’,’5600’); Insert into student values (‘2000’,’Jean’,’Maximilian’,’Str. Dinicu Golescu nr. 35 GALATI’,’DA’,’50’); Insert into student values (‘2001’,’Nicolae’,’Filipescu’,’Str. Gheorghe Doja nr. 44 BRAILA’,’NU’ ,’55’); Insert into student values (‘2002’,’Filimon’,’Cristi’,’Str. Constructorilor nr. 21 BUCURESTI’,’NU’ ,’70’); Insert into student values (‘2003’,’Gheorghe’,’Ionut’,’Str. Aviatorilor nr. 33 BUCURESTI’,’DA’ ,’30’);

Insert into student values (‘2004’,’Catalin’,’Cristian’,’Str. 1 Mai nr. 11 GALATI’,’NU’ ,’50’); Insert into student values (‘2005’,’Constantin’,’ Gabriela’,’Str. Vasile Milea nr. 15 BRAILA’,’DA’ ,’60’); Insert into disciplina values (‘3000’,’Bazele tehnologiei informatiei’,’4’); Insert into disciplina values (‘3001’,’Algoritmi in programare’,’5’); Insert into disciplina values (‘3002’,’Baze de date’,’6’); Insert into disciplina values (‘3003’,’Contabilitate’,’5’); Insert into disciplina values (‘3004’,’Microeconomie’,’5’); Insert into disciplina values (‘3005’,’Analiza’,’6’); Insert into examene values (‘4000’,’3000’,’2000’,’1000’,to_date ('ian 12,08','mon dd,yy'),’2011’,’5’); Insert into examene values (‘4001’,’3001’,’2001’,’1001’,to_date ('ian 17,08','mon dd,yy'),’1006’,’6’); Insert into examene values (‘4002’,’3002’,’2002’,’1002’,to_date ('ian 21,08','mon dd,yy'),’3014’,’8’); Insert into examene values (‘4003’,’3003,’,2003’,’1003’,to_date ('ian 24,08','mon dd,yy'),’2001’,’10’); Insert into examene values (‘4004’,’3004’,’2004’,’1004’,to_date ('ian 28,08','mon dd,yy'),’2003’,’4’); Insert into examene values (‘4005’,’3005’,’2005’,’1005’,to_date ('ian 30,08','mon dd,yy'),’3003’,’7’); B.1. Sa se selecteze denumirea disciplinei cu id-ul ‚3002’ din tabela disciplina. DECLARE v_nume disciplina.dendisc%type BEGIN select dendisc into v_nume from disciplina where iddisciplina=’3002’; END; B.2. Studentii ‘2002’ , ‘2003’ si ‘2005’au primit cate un punct in plus la examene in urma contestatiilor. Sa se modifice si in tabela. BEGIN update examene set nota=nota+1 where idstudent IN (‘2002’, ‘2003’, ’2005’); END; B.3. Sa se stearga din baza de date studentii care sunt la taxa. DECLARE v_taxa student.taxa%type; BEGIN

select taxa into v_taxa from student delete from student where taxa := ‘DA’ END;

C.1. Sa se mareasca numarul de credite al disciplinei cu id-ul ‚3000’ astfel: - Daca numarul de credite este mai mic decat 4 cu 200% - Daca numarul de credite este intre 4 si 8 cu 100% - Daca numarul de credite este mai mare de 8 cu 50% DECLARE v_credite disciplina.nrcred%type; BEGIN SELECT nrcred into v_credite from disciplina where iddisciplina=3000; dbms_output.put_line('Numarul de credite initial este: '||v_credite); IF v_credite < 4 THEN v_credite:=4* v_credite; ELSIF v_credite between 4 and 8 THEN v_credite:=2 * v_credite; ELSE v_credite:=1.5* v_credite; END IF; dbms_output.put_line('Numar de credite final este: '||v_credite); END; C.2. Folosind instructiunea CASE sa se mareasca numarul de credite al disciplinei cu id-ul ‚3002’ urmand modelul anterior. DECLARE v_credite disciplina.nrcred%type; BEGIN SELECT nrcred into v_credite from disciplina where iddisciplina=3002; dbms_output.put_line('Numarul de credite initial este: '||v_credite); CASE when v_credite < 4 THEN v_credite:=v_credite*4; when v_credite between 4 and 8 THEN v_credite:=v_credite*2; else v_credite:=v_credite*1.5; END CASE; dbms_output.put_line('Numar de credite final este: '||v_credite); END; D.1. Sa se afiseze numele studentilor care sunt la taxa. DECLARE v_nume studenti.nume%type;

BEGIN SELECT nume INTO v_nume FROM student WHERE taxa=’DA’; dbms_output.put_line(v_nume); EXCEPTION WHEN no_data_found THEN dbms_output.put_line('Nu exista student la taxa.'); END; D.2. Sa se afiseze numele profesorilor care au salariul peste 4000 RON. DECLARE v_nume profesori.nume%type; BEGIN SELECT nume INTO v_nume FROM profesori WHERE salariu > 4000; dbms_output.put_line(v_nume); EXCEPTION WHEN TOO_MANY_ROWS THEN dbms_output.put_line(‚Sunt mai multi profesori cu salariul peste 4000 RON.'); END; D.3. Sa se modifice numarul de credite al disciplinei cu id-ul 8340. DECLARE excep1 EXCEPTION; BEGIN UPDATE disciplina SET nrcred=nrcred+2 WHERE iddisciplina=8340; IF sql%notfound THEN RAISE excep1; END IF; EXCEPTION WHEN excep1 THEN dbms_output.put_line('Nu exista disciplina cu acest id.');

END; D.4. Sa se modifice salariul profesorului cu id-ul 1079. DECLARE excep2 EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_prod,-20999); BEGIN UPDATE profesori SET salariu=salariu+1000 WHERE idprofesor=1079; IF sql%notfound THEN RAISE_APPLICATION_ERROR (-20999,'Id profesor invalid!'); END IF; EXCEPTION WHEN excep2 THEN dbms_output.put_line ('Nu exista profesor cu acest id.'); END; E.1. Sa se afiseze numarul studentilor care au peste 50 de credite acumulate. DECLARE v_nrstud NUMBER(2); BEGIN Select * FROM studenti WHERE nrcredite>50; v_nrstud:=SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE (v_nrstud || ' studenti'); END; E.2. Sa se afiseze primii 5 studenti in functie de numarul de credite. DECLARE v_stud studenti.nume%type; CURSOR c1 IS SELECT nume, FROM studenti ORDER BY nrcredite DESC; BEGIN OPEN c1; LOOP FETCH c1 INTO v_stud; EXIT WHEN c1%ROWCOUNT>5 OR c1%NOTFOUND; dbms_output.put_line(v_stud); END LOOP; CLOSE c1; END;

E.3. Sa se afiseze studentii care au acumulat minim un numar de credite pentru a promova primit ca parametru de cursor. DECLARE CURSOR c2 (p_credite NUMBER) IS SELECT numeclient FROM clienti WHERE nrcredite > p_credite; v_nume studenti.nume%type; v_credite number(2); BEGIN v_credite:=45; dbms_output.put_line(‚Studentii care au acumulat cel putin '|| v_sedinte||’ credite:’); IF NOT c2%ISOPEN THEN OPEN c2 (v_credite); END IF; LOOP FETCH c2 into v_nume; EXIT WHEN c2%notfound; dbms_output.put_line (v_nume); END LOOP; CLOSE c2; END; F. Sa se creeze un pachet care contine 3 functii si 2 proceduri. - O functie care primeste ca parametrii numarul de credite acumulate si daca studentul este sau nu bugetar si returneaza numarul de credite ramase pana la maxim. - O functie care returneaza numar de credite ale studentilor, functia va primi ca parametru numarul de credite aferent anului respectiv. Numarul de credite aferent anului curent este de 100. - O functie care primeste ca parametru id-ul unui profesor si returneaza salariul profesorului printr-un parametru de tip OUT. - O procedura care sterge studentul al carui id il primeste ca parametru. - O procedura care mareste numarul de credite al unei discipline cu 1, pentru un disciplina al carui id il va primi ca parametru. CREATE OR REPLACE PACKAGE pachet IS function credite_ramase( crediteacum studenti.nrcredite%type, taxa student.taxa%type) return number; function nr_credite_acumulate(credite_maxim number)

return number; function salariu_profesor( idp profesori.idprofesor%type, salariup OUT profesori.salariu%type) return number; procedure sterge_student(ids student.idstudent%type); procedure mareste_credite(idc student.idstudent%type); END; CREATE OR REPLACE PACKAGE BODY pachet IS function credite_ramase( crediteacum student.nrcredite%type, taxa student.taxa%type) return number IS v_credite number(3); BEGIN SELECT nrcredite INTO v_credite from student where idstudent=taxa; v_credite=v_credite-crediteacum; return v_credite; END; function nr_credite_acumulate(credite_maxim number) return number IS v_credite number(3); BEGIN v_credite=credite_maxim - crediteacum; return v_credite; END; function salariu_profesor( idp profesori.idprofesori%type, salariup OUT profesori.salariu%type) return number IS v_rec profesori%rowtype; BEGIN SELECT * INTO v_rec FROM proteine WHERE idprofesor=idp; salariup=v_rec.salariu; return v_rec.salariu; END;

procedure sterge_student(ids student.idstudent%type) IS DELETE FROM student WHERE idstudent=ids; END; procedure mareste_credite(idd disciplina.iddisciplina%type) IS UPDATE disciplina SET nrcred=nrcred+1 WHERE iddisciplina=idd; END; G.1. Se creeaza un trigger pentru a nu fi depasit numarul de credite maxim. CREATE OR REPLACE TRIGER restrict_credite BEFORE INSERT or UPDATE on nrcredite FOR EACH ROW BEGIN IF new.credite>100 THEN RAISE_APPLICATION_ERROR (-20202, ‘Numarul de credite nu poate depasi 100.’); END IF; END; G.2. Se creeaza un trigger care asigura unicitatea id-ului studentului folosing valorile generate de o secventa. CREATE SEQUENCE student_secv START WITH 1 INCREMENT BY 1 MAX VALUE 2999 NOCYCLE; CREATE OR REPLACE TRIGGER generare_idstudent BEFORE INSERT ON student FOR EACH ROW BEGIN SELECT student_secv.nextval INTO :new.idstudent FROM dual; END;

VLAD DRAGOS GRUPA 1052