Academia de Studii EconomiceCibernetica, Statistica si Informatica Economica

PROIECT BAZE DE DATEGestiunea bazei de date a unui liceu

Student: Pascu Ionela-StefaniaProf. coordonator: Botha IulianaGrupa 1031, Seria C

SCHEMA CONCEPTUALA

Descriere proiect

Am ales ca tema pentru crearea bazei de date gestiunea unei baze de date a unui liceu.Scopul bazei de date este sa creeze o statistica pentru elevi in functie de bursa pe care o iau, sa tina evidenta burselor in functie de luna in care au fost date si sa faciliteze contactul elevilor si a profesorilor.Am creat intial tabelele ELEVI, PROFESORI, CLASE, PROFILURI, BURSE SI REPARTITIIBURSE.Tabela Elevi are ca si cheie primara atributul id_elev, iar ca si chei externe atributele id_clasa si id_profil. Exista o legatura 1:n intre Elevi si Repartitiiburse ( un elev poate lua o singura bursa intr-o luna).Tabela Profesori are ca si cheie principal atributul id_profesor si mai contine informatii precum nume, prenume, salariul, numarul de telefon precum si materia pe care o preda. Exista o relatie de 1:n intre Profesori si Clase.Tabela Clase contine informatii precum id clasei, anul de studiu, numele clasei, profilul de care apartine si dirigintele acesteia. Are ca si chei externe id_profil si diriginte( id_profesor).Tabela Profiluri contine informatii precum id_profil si numele profilului.Tabela Burse contine informatii precum id_bursa, tipul bursei si suma de bani alocata fiecarui tip de bursa.Tabela Repartitiiburse contine informatii precum id_elev, id_bursa si luna in care a fost alocata.

CREAREA TABELELOR

Tabela Elevi

Create table Elevi ( ID_ELEV VARCHAR2(6) not null,NUME_ELEV VARCHAR2(25),PRENUME_ELEV VARCHAR2(25),ID_BURSA VARCHAR2(6),ID_CLASA VARCHAR2(6),ID_PROFIL VARCHAR2(6),ADRESA VARCHAR2(50),TELEFON VARCHAR(10),OLIMPIC CHAR(2));

Tabela ProfesoriCreate table Profesori( ID_PROFESOR VARCHAR2(6) not null,NUME_PROF VARCHAR2(25),PRENUME_PROF VARCHAR2(25),ADRESA VARCHAR2(50),SALARIUL NUMBER(4),TELEFON VARCHAR2(10) );

Tabela ProfiluriCreate table Profiluri( ID_PROFIL VARCHAR2(6),NUME_PROFIL VARCHAR2(10) NOT NULL);

Tabela BurseCreate table Burse ( ID_BURSA VARCHAR2(6),TIP_BURSA CHAR(10),SUMA_BANI NUMBER(3));

Tabela RepartitiiburseCreate table Repartitiiburse(ID_ELEV VARCHAR2(6), ID_BURSA VARCHAR2(6),LUNA CHAR(15) NOT NULL);

Tabela ClaseCreate table Clase ( ID_CLASA VARCHAR2(6),AN_STUDIU NUMBER(2),NUME_CLASA VARCHAR2(1),ID_PROFIL VARCHAR2(6),DIRIGINTE VARCHAR2(6));

CONDITII DE INTEGRITATE

Tabela Elevi1. Alter table EleviAdd constraint ELEV_ID_ELEV_PK primary key (ID_ELEV);2. Alter table EleviDrop column OLIMPIC;3. Alter table EleviAdd constraint ELEV_TELEFON_UK unique (TELEFON);4. Alter table EleviAdd constraint ELEV_NUME_ELEV_NNCheck (NUME_ELEV IS NOT NULL);5. Alter table EleviAdd constraint ELEV_PRENUME_ELEV_NNCheck (PRENUME_ELEV IS NOT NULL);6. Alter table EleviDrop column ADRESA;

Tabela Profesori1. Alter table ProfesoriAdd constraint PROF_ID_PROFESOR_PK primary key (ID_PROFESOR);2. Alter table ProfesoriAdd constraint PROF_SALARIUL_NNCheck (SALARIUL IS NOT NULL);3. Alter table ProfesoriAdd constraint PROF_TELEFON_UK unique (TELEFON);4. Alter table ProfesoriDrop column ADRESA;5. Alter table ProfesoriAdd (MATERIE VARCHAR2(15));

Tabela Profiluri

1. Alter table ProfiluriAdd constraint ELEV_ID_PROFIL_PK primary key (ID_PROFIL);2. Alter table ProfiluriAdd constraint PRF_NUME_PROFIL_NNCheck (NUME_PROFIL IS NOT NULL)

Tabele Burse1. Alter table BurseAdd constraint BRS_ID_BURSA primary key (ID_BURSA);2. Alter table BurseAdd constraint TIP_BURSA_CK check ( TP_BURSA in (DE MERIT, SOCIALA, NICIUNA));3. Alter table BurseAdd constraint BRS_SUMA_BANI_NNCheck ( SUMA_BANI IS NOT NULL);

Tabela Repartitiiburse1. Alter table RepartitiiburseAdd constraint ELEV_BES_FK foreign key (ID_ELEV)References Elevi (ID_ELEV);2. Alter table RepartitiiburseAdd constraint REP_BRS_FK foreign key ( ID_BURSA)References Burse (ID_BURSA);

Tabela Clase1. Alter table ClaseAdd constraint CLS_ID_CLASA_PK primary key ( ID_CLASA);2. Alter table ClaseAdd constraint CLS_PRF_FK foreign key (ID_PROFIL)References PROFILURI (ID_PROFIL) 3. Alter table Clase Add constraint DRG_PRF_FK foreign key (DIRIGINTE)References PROFESORI (ID_PROFESOR);

ADAUGARE DE INREGISTRARI

Tabela Eleviinsert all into Elevi values ('elv001','Patru','Ionel','brs001,cls001,prf001,'0769634937')into Elevi values ('elv002',Irimia','Alexandra','brs001,cls002,prf002,0765965263)into Elevi values ('elv003','Radu.Ionut,brs002,cls001,prf002,0765239458)into Elevi values ('elv004','Petre,Marius,brs003,cls005,prf005,0756258452)into Elevi values ('elv005','Miron,Claudiu,brs002,cls003,prf004,0756587423)into Elevi values ('elv006','Popescu,Irina,brs001,cls006,prf005,0756525948)into Elevi values ('elv007',Costea,Paula,brs002,cls003,prf001,0759632158)into Elevi values ('elv008','Marin,Claudiu,brs003,cls001,prf002,0756239478)into Elevi values ('elv009','Postolache,Crina,brs002,cls009,prf003,0745862349)into Elevi values ('elv010',Pavel,Andra,brs001,cls002,prf001,0748623595)into Elevi values ('elv011',Mitrea,Ana,brs003,cls002,prf004,0756952364)select *from dual

Tabela Profesoriinsert all into Profesori values ('prf001','Teodorescu','Emil','1300','0754123654','Matematica')into Profesori values ('prf002','Cartas','Elena','2000','0742315856','Lb.Engleza')into Profesori values ('prf003','Vulpe','Matei','1750','0745212365','Lb.Romana')into Profesori values ('prf004','Ilade','Valentin','1400','0741236524','Biologie')into Profesori values ('prf005','Diaconu','Adriana','2000','0745896321','Informatica')into Profesori values ('prf006','Stoleru','Cristian','2500','0748963254','Matematica')into Profesori values ('prf007','Croitoru','Cristina','1300','0763215645','Chimie')into Profesori values ('prf008','Ciobanu','Florina','1400','0786523489','Fizica')into Profesori values ('prf009','Caluian','Andreea','2500','0741236522','Ed.fizica')into Profesori values ('prf010','Mircea','Ioan','1100','0754123624','Lb.Romana')select *from dual

Tabela Profiluriinsert allinto Profiluri values ('prf001','Mate-Info')into Profiluri values ('prf002','Filologie')into Profiluri values ('prf003','MateFizica')into Profiluri values ('prf004','ChimiBio')into Profiluri values ('prf005','StiinteNat')select * from dual

Tabela BurseInsert allInto Burse values (brs001,DE MERIT,200)Into Burse values (brs002,SOCIALA,150)Into Burse values (brs003,NICIUNA,0)Select * from dual

Tabela Repartitiiburseinsert allinto Repartitiiburse values ('elv001','brs001','Decembrie')into Repartitiiburse values ('elv002','brs001','Mai')into Repartitiiburse values ('elv005','brs002','Aprilie')into Repartitiiburse values ('elv007','brs002','Decembrie')into repartitiiburse values ('elv009','brs002','Februarie')into Repartitiiburse values ('elv011','brs003','Mai')select * from dual

Tabela Claseinsert allinto Clase values ('cls001','9','A','prf001','prf002')into Clase values ('cls002','9','C','prf002','prf001')into Clase Values ('cls003','11','D','prf004','prf003')into Clase values ('cls005','12','A','prf005','prf004')into Clase values ('cls009','10','B','prf003','prf006')select * from dual

UPDATE1. Sa se mareasca suma de bani pentru fiecare tip de bursa cu 50%.update Burse set SUMA_BANI=SUMA_BANI*1.5;

2. Sa se mareasca salariul profesorilor care predau matematica cu 30%.update Profesoriset SALARIUL=SALARIUL*1.3WHERE MATERIE LIKE '%Matematica%';

3. Sa se stearga inregistrarile profesorilor al caror nume este Mircea.DELETE FROM Profesoriwhere NUME_PROF like '%Mircea%';

4. Sa se seteze profilul mate-info pentru clasele al caror id este cls001 si cls002.update Eleviset ID_PROFIL='prf001'WHERE ID_CLASA IN ('cls001','cls002');

5. Sa se acorde bursa sociala elevului cu id elv001.update Eleviset id_bursa='brs002'where id_elev='elv001';

15 INREGISTRARI1. Sa se afiseze numele si prenumele elevilor care iau bursa de merit.Select nume_elev, prenume_elev, id_bursaFrom eleviWhere id_bursa=brs001Order by nume_elev;

2. Sa se afiseze numarul profesorilor in functie de materia predata.Select count(id_profesor)From profesoriGroup by materie;

3. Sa se afiseze numele, prenumele si salariul profesorilor care au salariul mai mare de 1700.select nume_prof, prenume_prof, salariulfrom profesoriwhere salariul > 1700;

4. Sa se atribuie un bonus de 50% profesorilor care predau matematica si un bonus de 40% celor care predau lb. romana.select nume_prof, materie, salariulcasewhen upper(materie)='%MATEMATICA%' then 0.5*salariulwhen upper(materie)='%LB.ROMANA%'then 0.4*salariuielse 0end bonusFROM Profesori;

5. select sum(suma_bani)from burse a, repartitiiburse bwhere a.id_bursa=b.id_bursa and upper(b.luna)='%MAI%';

6. Sa se afiseze salariul minim, salariul maxim si salariul mediu.select min(salariul), max(salariul), avg(salariul)from profesori;

7. Sa se afiseze numele si prenumele elevilor al caror prenume incepe cu litera A.select nume_elev, prenume_elevfrom eleviwhere upper(prenume_elev) like 'A%';

8. Sa se afiseze ierarhic toti superiorii elevului cu id elev006.select level, nume_elev, prenume_elevfrom elevistart with id_elev='elv006'connect by prior id_elev='elv009'

9. Sa se afiseze numele si prenume profesorilor care au a doua litera din prenume A.select nume_prof,prenume_proffrom profesoriwhere upper(prenume_prof) like '_A%'

10. Sa se afiseze numele clasei si anul de studiu pentru clasa a carei diriginte are id_prof =prf004.Select an_studiu, nume_clasaFrom claseWhere diriginte=prf004;

11. Sa se afiseze numele si prenumele elevilor a caror numar de telefon se termina cu 937.select nume_elev, prenume_elevfrom eleviwhere telefon like '%937';

12. Sa se afiseze salariul minim, salariul maxim si salariul mediu in functie de materie.select min(salariul), max(salariul), avg(salariul), materiefrom profesorigroup by materie;

13. Sa se afiseze toate informatiile tabelei clase.select id_clasa, an_studiu, nume_clasa, id_profil, dirigintefrom clase;

14. Sa se afiseze numarul elevilor pentru fiecare an de studiu.select count(id_elev)from elevi a, clase bwhere a.id_clasa=b.id_clasagroup by an_studiu;

15. Sa se afiseze numarul elevilor de la profilul mate-info.select count(id_elev)from eleviwhere id_profil='prf001'

16. Sa se afiseze id-ul clasei, numele clasei si anul de studiu pentru clasa la care profesorul cu id-ul prf001 este diriginte.select c.id_clasa,c.nume_clasa, c.an_studiu, p.nume_prof,p.prenume_proffrom clase c, profesori pwhere c.diriginte=p.id_profesorand c.id_clasa='cls001';

17. . Sa se afiseze diferenta dintra tabela PROFESORI si tabela CLASE.SELECT A.ID_PROFESOR FROM PROFESORI AMINUSSELECT B.DIRIGINTE FROM CLASE B;

18. Sa se afiseze intersectia dintre tabela Clase si tabela Elevi.

SELECT Y.ID_CLASA FROM CLASE Y INTERSECT SELECT X.ID_CLASA FROM ELEVI X;

19. Sa se afiseze id-ul clasei, numele concatenate cu prenume concatenate numai pentru elevii al caror nume incepe cu P.SELECT E.iD_CLASA, CONCAT(E.NUME_ELEV,E.PRENUME_ELEV) AS "NUME,PRENUME"FROM ELEVI E,CLASE CWHERE E.ID_CLASA=C.ID_CLASAAND NUME_ELEV LIKE 'P%';

TABELA VIRTUALA

1. Creati o tabela virtuala cu aceiasi structura ca tabela Elevi, dar care sa contina numai acele inregistrari care incep cu litera M.

CREATE VIEW M_ELEVI AS SELECT * FROM ELEVIWHERE UPPER(NUME_ELEV) LIKE M%

SINONIM1. Sa se creeze un sinonim pentru tabela Elevi.Create synonym Elevi_Syn for Elevi;

2. Sa se stearga sinonimul creat.DROP SYNONYM Elevi_SYN;

INDECSI

1. Sa se creeze un index la tabela Elevi pe coloanele nume , care sa faciliteze accesul mai rapid la tabela cu numele si prenumele elevilor.Create index elev_nume_ixOn Elevi (NUME_ELEV,PRENUME_ELEV);

2. Sa se stearga indexul creat.DROP INDEX elev_nume_ix;

SECVENTE

1. Creati o secventa an_studiu_clsid_seg care sa inceapa de la 9, sa fie incrementata cu 3, iar valoarea maxima sa fie 12.

2. Creati o secventa salariul_salid_seg care sa inceapa de la 1400, sa fie incrementata cu 10, iar valoarea maxima sa fie 2000.