Proiect Baze de Date - Pascu - Ionela [PDF]

Zitiervorschau

Academia de Studii Economice Cibernetica, Statistica si Informatica Economica

PROIECT BAZE DE DATE Gestiunea bazei de date a unui liceu

Student: Pascu Ionela-Stefania Prof. coordonator: Botha Iuliana Grupa 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 Profesori

Create 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 Profiluri Create table Profiluri ( ID_PROFIL VARCHAR2(6), NUME_PROFIL VARCHAR2(10) NOT NULL);

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

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

Tabela Clase Create 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 Elevi 1. Alter table Elevi Add constraint ELEV_ID_ELEV_PK primary key (ID_ELEV); 2. Alter table Elevi Drop column OLIMPIC; 3. Alter table Elevi Add constraint ELEV_TELEFON_UK unique (TELEFON); 4. Alter table Elevi Add constraint ELEV_NUME_ELEV_NN Check (“NUME_ELEV” IS NOT NULL); 5. Alter table Elevi Add constraint ELEV_PRENUME_ELEV_NN Check (“PRENUME_ELEV” IS NOT NULL); 6. Alter table Elevi Drop column ADRESA;

Tabela Profesori 1. Alter table Profesori Add constraint PROF_ID_PROFESOR_PK primary key (ID_PROFESOR); 2. Alter table Profesori Add constraint PROF_SALARIUL_NN Check (“SALARIUL” IS NOT NULL); 3. Alter table Profesori Add constraint PROF_TELEFON_UK unique (TELEFON); 4. Alter table Profesori Drop column ADRESA; 5. Alter table Profesori Add (MATERIE VARCHAR2(15));

Tabela Profiluri

1. Alter table Profiluri Add constraint ELEV_ID_PROFIL_PK primary key (ID_PROFIL); 2. Alter table Profiluri Add constraint PRF_NUME_PROFIL_NN Check (“NUME_PROFIL” IS NOT NULL)

Tabele Burse 1. Alter table Burse Add constraint BRS_ID_BURSA primary key (ID_BURSA); 2. Alter table Burse Add constraint TIP_BURSA_CK check ( TP_BURSA in (‘DE MERIT’, ‘SOCIALA’, ‘NICIUNA’)); 3. Alter table Burse Add constraint BRS_SUMA_BANI_NN Check (“ SUMA_BANI” IS NOT NULL);

Tabela Repartitiiburse 1. Alter table Repartitiiburse Add constraint ELEV_BES_FK foreign key (ID_ELEV) References Elevi (ID_ELEV); 2. Alter table Repartitiiburse Add constraint REP_BRS_FK foreign key ( ID_BURSA) References Burse (ID_BURSA);

Tabela Clase 1. Alter table Clase Add constraint CLS_ID_CLASA_PK primary key ( ID_CLASA); 2. Alter table Clase Add 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 Elevi insert 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 Profesori insert 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 Profiluri insert all into 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 Burse Insert all Into Burse values (‘brs001’,’DE MERIT’,’200’) Into Burse values (‘brs002’,’SOCIALA’,’150’) Into Burse values (‘brs003’,’NICIUNA’,’0’) Select * from dual

Tabela Repartitiiburse insert all into 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 Clase insert all into 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

UPDATE 1. 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 Profesori set SALARIUL=SALARIUL*1.3 WHERE MATERIE LIKE '%Matematica%';

3. Sa se stearga inregistrarile profesorilor al caror nume este “Mircea”. DELETE FROM Profesori where NUME_PROF like '%Mircea%';

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

5. Sa se acorde bursa sociala elevului cu id “elv001”. update Elevi set id_bursa='brs002' where id_elev='elv001';

15 INREGISTRARI 1. Sa se afiseze numele si prenumele elevilor care iau bursa de merit. Select nume_elev, prenume_elev, id_bursa From elevi Where id_bursa=’brs001’ Order by nume_elev;

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

3. Sa se afiseze numele, prenumele si salariul profesorilor care au salariul mai mare de 1700. select nume_prof, prenume_prof, salariul from profesori where 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, salariul case when upper(materie)='%MATEMATICA%' then 0.5*salariul when upper(materie)='%LB.ROMANA%' then 0.4*salariui else 0 end bonus FROM Profesori;

5. select sum(suma_bani) from burse a, repartitiiburse b where 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_elev from elevi where upper(prenume_elev) like 'A%';

8. Sa se afiseze ierarhic toti superiorii elevului cu id elev006. select level, nume_elev, prenume_elev from elevi start 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_prof from profesori where 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_clasa From clase Where diriginte=’prf004’;

11.Sa se afiseze numele si prenumele elevilor a caror numar de telefon se termina cu 937. select nume_elev, prenume_elev from elevi where 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), materie from profesori group by materie;

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

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

15.Sa se afiseze numarul elevilor de la profilul mate-info. select count(id_elev) from elevi where 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_prof from clase c, profesori p where c.diriginte=p.id_profesor and c.id_clasa='cls001';

17.. Sa se afiseze diferenta dintra tabela PROFESORI si tabela CLASE. SELECT A.ID_PROFESOR FROM PROFESORI A MINUS SELECT 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 C WHERE E.ID_CLASA=C.ID_CLASA AND 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 ELEVI WHERE UPPER(NUME_ELEV) LIKE ‘M%’

SINONIM 1. 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_ix On 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.