Gestiunea studentilor intr-o universitate

Scenariu

In cadrul unei universitati a fost nevoie de crearea unei baze de date pentru a centraliza datele legate de studenti. Baza de date ne ofera posibilitatea de adauga, modifica sau sterge datele legate de studenti respectand anumite norme de integritate. Fiecare student va avea inregistrate date precum nume,prenume,cnp,data nasterii,gupa din care face parte,anul s.a. . Baza de date ne va oferi posibilitatea de a stoca notele fiecarui student si facultatea din care face parte.
Baza de date contine urmatoarele tabele : student, facultate, note si materii.
Tabela student contine urmatoarele informatii: id-ul studentului, id-ul facultatii din care face parte,numele, prenumele,cnp-ul, data nasterii, anul si grupa din care face parte.
Tabela facultate contine : id-ul facultatii, numele facultatii,adresa si numele decanului.
Tabela note contine urmatoarele informatii: id-ul notei, id-ul materiei,id-ul studentului, nota si data in care a fost primita.
Tabela materii cotine urmatoarele informatii: id-ul materiei, denumire, an, profersor.

Diagrama ERD

Crearea tabelelor:

Creare tabela studenti: create table studenti( id_stud number(4) not null primary key, - numar din maxim 3 cifre, nenul, cheie primara id_fac number(2) not null, -numar din maxim 2 cifre, nenul nume varchar(20) not null, -maxim 20 de caractere,nenul prenume varchar(20) not null, - maxim 20 de caractere,nenul cnp number(13) not null, -numar din maxim 13 cifre, nenul data_nasterii date not null, -data,nenula an number(1) not null, -numar din maxim o cifra, nenul grupa varchar(6) not null, -maxim 6 caractere constraint vfan check(an>0)
);
Creare tabela facultate: create table facultate( id_fac number(2) not null primary key, - numar din maxim 2 cifre, nenul, cheie primara nume_fac varchar(60) not null, -maxim 60 de caractere, nenul adresa_fac varchar(60) not null, -maxim 60 de caractere, nenul nume_decan varchar(25) not null -maxim 25 de caractere, nenul
);

Creare tabela note: create table note( id_nota number(5) not null primary key, - numar din maxim 5 cifre, nenul, cheie primara id_materie number(2) not null, - numar din maxim 2 cifre, nenul, id_stud number(4) not null, -numar din maxim 4 cifre, nenul nota number(2) not null - numar din maxim 2 cifre, nenul
);

Creare tabela materii:

create table materii( id_materie number(2) not null primary key, - numar din maxim 2 cifre, nenul, cheie primara denumire varchar(20) not null, -maxim 20 de caractere, nenul an number(1) not null, -numar din maxim 1 cifrea, nenul profesor varchar(40) not null - maxim 40 de caractere, nenul
);

Crearea legaturilor intre tabele

facultate - studenti : alter table studenti add constraint fk_1 foreign key (id_fac) references facultate(id_fac) on delete cascade; id_fac e cheie primara in tabela facultate cheie straina in tabela studenti => legatura de tipul 1-N (intr-o facultate poti fi unul sau mai multi student)

studenti - note : alter table note add constraint fk_2 foreign key (id_stud) references studenti(id_stud) on delete cascade; id_stud e cheie primara in tabela studenti cheie straina in tabela note => legatura de tipul 1-N (fiecare student poate sa aiba una sau mai multe note)

materii - note : alter table note add constraint fk_3 foreign key (id_materie) references materii(id_materie) on delete cascade; id_materie e cheie primara in tabela materii cheie straina in tabela note => legatura de tipul 1-N (la fiecare materie pot fi una sau mai multe note)

facultate - materii : alter table materii add constraint fk_4 foreign key (id_fac) references facultate(id_fac) on delete cascade; id_fac e cheie primara in tabela facultate cheie straina in tabela materii => legatura de tipul 1-N (fiecare facultate poate avea una sau mai multe materii)

Adaugarea constrangerilor la anumite campuri

Notele sa fie cuprinse intre 1 si 10 : alter table note add constraint cons_nota check(nota between 1 and 10);
CNP-ul din tabela studenti sa fie pozitiv: alter table studenti add constraint cons_cnp check(cnp>0);
Anul din tabela student sa fie cuprins intre 1 si 4: alter table studenti add constraint cons_a check(an between 1 and 4);

Popularea datelor in tabele

Tabela facultate: insert into facultate values(1, 'Facultatea de Matematica şi Informatica', 'Str. Mihail Kogalniceanu nr. 1', 'Adrian Petrusel'); insert into facultate values(2, 'Facultatea de Geografie', 'Str. Castanilor nr 40’, 'Adrian Petrusel'); insert into facultate values(3, ' Facultatea de Litere', 'Str. Horea nr. 20', 'Vasile Pop'); insert into facultate values(4, ' Facultatea de Educatie Fizica si Sport', 'Str. Plopilor nr. 23', 'Mircea Alexei'); insert into facultate values(5, 'Facultatea de Istorie ', 'Str Emil Racovita nr. 12', 'Marius Comsa');

Tabela materii:
Insert into materii values(13, 1, 'Baze de date', 1, 'Victor Stanciu');
Insert into materii values(27, 2, 'Geografia Romaniei', 2, 'Gheorghe Pic');
Insert into materii values(23, 2, 'Hidrologie', 2, 'Vasile Campean');
Insert into materii values(42, 4, 'Handbal', 1, 'Suciu Tudor');
Insert into materii values(44, 4, 'Tennis', 1, 'Chis Matei');
Insert into materii values(15, 1, 'Algoritmi si Structuri de date', 1, 'Baciu Cornelia’);
Insert into materii values(16, 1, 'Cibernetica', 1, 'Tise Florin');
Insert into materii values(29, 2, 'Geografia Spaniei', 2, 'Fartan Alexandru');
Insert into materii values(31, 3, 'Gramatica limbii japoneze', 3, 'Dascal Emanuela');
Insert into materii values(34, 3, 'Literatura franceza', 3, 'Petrisor Cornel');
Insert into materii values(39, 3, 'Literatura rusa', 3, 'Radu Vasile');
Insert into materii values(51, 5, 'Istoria Angliei', 2, 'Roland Adriana’);
Insert into materii values(53, 5, 'Istoria Americii', 2, 'Emeric Imre');
Insert into materii values(57, 5, 'Istoria Romaniei', 2, 'Goldis Vasile');
Insert into materii values(49, 4, 'Atletism', 1, 'Chis Matei');
Tabela Studenti:
Insert into studenti values(1, 1, 'Muresan’, 'Luca’, 1730610155203, '22-12-1991’, 1, 'i3’);
Insert into studenti values(2, 1, 'Stanciu’, 'Vasile’, 1930410157202, '12-11-1991’, 1,’ i2’);
Insert into studenti values(3, 2, 'Demeter’, 'Ioana’, 1830614125203, '07-01-1990’, 2,’ g2’);
Insert into studenti values(4, 2, 'Sabrin’, 'Sburlea’, 1700640155403, '13-03-1991’, 2, 'g2’);
Insert into studenti values(5, 3, 'Radulescu’, 'Elena’, 1800640155403, '15-07-1989’, 3,’ l1’);
Insert into studenti values(6, 3, 'Marinescu, 'Alexandru’, 1900740155403, '01-10-1990’, 3, 'l2’);
Insert into studenti values(7, 4, 'Ionescu’, 'Eugen’, 1910640155403, '23-12-1990’, 1, 'f2’);
Insert into studenti values(8, 4, 'Balan’, 'Gabriela’, 1800640455403, '02-06-1991’, 1, 'f2’);
Insert into studenti values(9, 5, 'Petrila’, 'Valentin’, 1703640455403, '10-12-1991’, 2,’ s2’);
Insert into studenti values(10, 5, 'Pop’, 'Vlad’, 1910240155403, '13-11-1991’, 2, 's2’);
Tabela Note:
Insert into note values(1, 13, 1, 7, '13-01-2012’);
Insert into note values(2, 15, 1, 8, '01-02-2012’);
Insert into note values(3, 16, 1, 10, '14-11-2012’);
Insert into note values(4, 13, 2, 9, '13-11-2012’);
Insert into note values(5, 15, 2, 7, '22-01-2012’);
Insert into note values(6, 16, 2, 7, '24-11-2012’);
Insert into note values(7, 27, 3, 5, '30-04-2012’);
Insert into note values(8, 23, 3, 6, '18-01-2012’);
Insert into note values(9, 29, 3, 8, '19-03-2012’);
Insert into note values(10, 27, 4, 7, '13-03-2012’);
Insert into note values(11, 23, 4, 7, '13-11-2012’);
Insert into note values(12, 29, 4, 6, '14-05-2012’);
Insert into note values(13, 31, 5, 9, '13-01-2012’);
Insert into note values(14, 34, 5, 7, '01-02-2012’);
Insert into note values(15, 39, 5, 5, '06-12-2012’);
Insert into note values(16, 31, 6, 10, '26-04-2012’);
Insert into note values(17, 34, 6, 7, '15-03-2012’);
Insert into note values(18, 39, 6, 9, '07-09-2012’);
Insert into note values(19, 42, 7, 8, '23-12-2012’);
Insert into note values(20, 44, 7, 7, '18-10-2012’);
Insert into note values(21, 49, 7, 10, '17-02-2012’);
Insert into note values(22, 42, 8, 7, '04-09-2012’);
Insert into note values(23, 44, 8, 7, '13-11-2012’);
Insert into note values(24, 49, 8, 10, '13-02-2012’);
Insert into note values(25, 51, 9, 10, '14-05-2012’);
Insert into note values(26, 53, 9, 9, '27-02-2012’);
Insert into note values(27, 57, 9, 8, '30-11-2012’);
Insert into note values(28, 51, 10, 8, '17-12-2012’);
Insert into note values(29, 53, 10, 7, '10-11-2012’);
Insert into note values(30, 57, 10, 9, '13-11-2012’);
Modificari de structura a tabelelor

Adugam in tabela preturi un camp nou numit unde se poate adauga o scurta descriere a facultatii: alter table facultate add descriere varchar(80);

Adugam in tabela studenti un camp nou numit adresa unde se poate adauga adresa studentului: alter table studenti add adresa varchar(50);

Actualizare de continut a tabelelor

* In tabela facultate am actualizat coloana descriere pentru facultateacu id-ul egal cu 5: update facultate set descriere = 'Cea mai buna facultate de istorie din Romania' where id_fac= 5; * In tabela studenti am actualizat coloana grupa astefel incat sa se adauge textul ‘gr’ la inceputul fiecarei grupe: update studenti set grupa= concat('gr’,grupa);

Creare index

Cream un index ordonare_note care ordoneaza notele crescator in functie de notaobtinuta si data: create index "ordonare_note" on note(nota,data);

Creare view

Pentru a vedea notele de 10 a studentilor din cadrul universitatii cream urmatorul view: select p.id_student, p.nume, p.prenume, c.nota, f.id_nota from studenti p, note c where c.nota = 10;

Sa se afiseze toate materiile care au note mai mici decat 7 si sa se ordoneze crescator in functie de note:

select m.id_materie, m.denumire, p.id_nota,p.nota from materii m, note p where m.id_materie = p.id_materie and nota < 7 order by nota;

Sa se afiseze toti studentii nascuti intre 1 Martie 1989 si 1 Martie 1991, facultatea din care fac parte si sa se ordoneze dupa data nasterii :

select m.nume, m.prenume, m.data_nasterii, p.id_fac, p.nume_fac from studenti m, facultate p where m.id_fac = p.id_fac and m.data_nasterii between '01-mar-1989' and '01-mar-1991' order by data_nasterii;

Sa se afiseze tabela “Facultate” cu o singura coloana care sa contina toate informatiile celorlalte coloane: select concat(nume,concat('cu id-ul', concat(id_fac, concat('are adresa', concat(adresa, concat(‘iar decanul facultatii este’, nume_decan))))) date_facultate from facultate;

Sa se afiseze tabela “Materii” cu o singura coloana care sa contina toate informatiile celorlalte coloane: select concat('Materia ',concat(denumire, concat('cu id-ul ', concat(id_materie, concat('este predata in anul ', concat(an,concat(' de catre profesorul ', profesor))))))) date_materii from materii;

Sa se afiseze toti studentii de la Facultatea de Educatie Fizica si Sport nascuti dupa 10 iunie 1990.

select v.id_stud, v.nume, v.prenume, p.nume_fac from studenti v, facultate p where v.id_fac=4 and v.id_fac = p.id_fac and v.data_nasterii > '10-jun-1990';

Sa se ordoneze campurile tabelei Facultate in functie de id, adresa, nume decan, acolo unde nu exista descriere adaugata : select * from facultate where descriere is null order by id_fac, nume_fac, adresa_fac, nume_decan;