PROIECTSGBD - ORACLE

Vlad DragosGrupa 1052

PROIECT SGBD - ORACLE

A.

Profesori

Student

Disciplina

Examene

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,

idexamen

idprofesor nume prenume adresa salariu

idstudent nume prenume adresa taxa

iddisciplina dendisc nrcred

iddisciplina idstudent idprofesori sala nota

nrcredite

Nume varchar2(15) not null,Prenume varchar2(15) not null,Adresa varchar2(30),Taxa varchar2(2) not nullNrcredite 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.

DECLAREv_nume disciplina.dendisc%typeBEGINselect 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.

BEGINupdate exameneset nota=nota+1where 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;BEGINselect taxa into v_taxa from student delete from studentwhere 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%

DECLAREv_credite disciplina.nrcred%type;BEGINSELECT 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.

DECLAREv_credite disciplina.nrcred%type;BEGINSELECT nrcred into v_credite from disciplina where iddisciplina=3002;dbms_output.put_line('Numarul de credite initial este: '||v_credite);CASEwhen 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;

BEGINSELECT nume INTO v_nume FROM studentWHERE taxa=’DA’;dbms_output.put_line(v_nume);

EXCEPTIONWHEN no_data_found THENdbms_output.put_line('Nu exista student la taxa.');

END;

D.2. Sa se afiseze numele profesorilor care au salariul peste 4000 RON.

DECLAREv_nume profesori.nume%type;

BEGINSELECT nume INTO v_nume FROM profesoriWHERE salariu > 4000;dbms_output.put_line(v_nume);

EXCEPTIONWHEN TOO_MANY_ROWS THENdbms_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.

DECLAREexcep1 EXCEPTION;

BEGINUPDATE disciplina SET nrcred=nrcred+2WHERE iddisciplina=8340;

IF sql%notfound THENRAISE excep1;END IF;

EXCEPTION

WHEN excep1 THENdbms_output.put_line('Nu exista disciplina cu acest id.');END;

D.4. Sa se modifice salariul profesorului cu id-ul 1079.

DECLAREexcep2 EXCEPTION;PRAGMA EXCEPTION_INIT(invalid_prod,-20999);BEGINUPDATE profesori SET salariu=salariu+1000WHERE idprofesor=1079;IF sql%notfound THENRAISE_APPLICATION_ERROR (-20999,'Id profesor invalid!');END IF;EXCEPTIONWHEN excep2 THENdbms_output.put_line ('Nu exista profesor cu acest id.');END;

E.1. Sa se afiseze numarul studentilor care au peste 50 de credite acumulate.

DECLAREv_nrstud NUMBER(2);BEGINSelect * 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;

BEGINOPEN c1;LOOPFETCH 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.

DECLARECURSOR c2 (p_credite NUMBER) IS SELECT numeclient FROM clientiWHERE nrcredite > p_credite;

v_nume studenti.nume%type;v_credite number(2);

BEGINv_credite:=45;dbms_output.put_line(‚Studentii care au acumulat cel putin '|| v_sedinte||’ credite:’);

IF NOT c2%ISOPEN THENOPEN c2 (v_credite);END IF;

LOOPFETCH 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 ISv_credite number(3);BEGINSELECT 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 numberISv_credite number(3);BEGINv_credite=credite_maxim - crediteacum;return v_credite;END;

function salariu_profesor(idp profesori.idprofesori%type,salariup OUT profesori.salariu%type)return numberISv_rec profesori%rowtype;BEGINSELECT * INTO v_rec FROM proteine WHERE idprofesor=idp;salariup=v_rec.salariu;

return v_rec.salariu;END;

procedure sterge_student(ids student.idstudent%type)ISDELETE FROM student WHERE idstudent=ids;END;

procedure mareste_credite(idd disciplina.iddisciplina%type)ISUPDATE 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_crediteBEFORE INSERT or UPDATE on nrcrediteFOR EACH ROWBEGIN

IF new.credite>100 THENRAISE_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_secvSTART WITH 1INCREMENT BY 1MAX VALUE 2999NOCYCLE;

CREATE OR REPLACE TRIGGER generare_idstudentBEFORE INSERT ON studentFOR EACH ROWBEGINSELECT student_secv.nextval INTO :new.idstudent FROM dual;END;

VLAD DRAGOSGRUPA 1052