Proiectarea bazelor de date « Examenul de capacitate »

Universitatea "Dunarea de Jos", Galati

Facultatea de Stiinta Calculatoarelor,

Specializarea Calculatoare si Tehnologia Informatiei

 

Examenul de capacitate

Tema

Crearea unei baze de date date pentru inregistrarea rezultatelor elevilor ce dau examenul de capacitate in judetul Galati.

Cerinte

Evidenta elevilor:

afisarea elevilor inregistrati in baza de date;

adaugarea altor elevi in tabel;

stergerea unor inregistrari;

editarea datelor elevilor inregistrati.

Evidenta unitatilor de invatamant:

afisarea scolilor inregistrate in baza de date;

adaugarea altor scoli in tabel;

stergerea unor inregistrari;

editarea datelor scolilor inregistrate.

Analiza problemei

Examenul de Capacitate are drept scop principal certificarea performantelor absolventilor cursurilor gimnaziale. In acelasi timp, examenul indeplineste si o functie de selectie, avand in vedere faptul ca rezultatele obtinute de candidati la acest examen constituie un criteriu de selectie important pentru accesul la invatamantul liceal.

Examenul de Capacitate este un examen cu caracter national: toti elevii il sustin in acelasi timp si pe baza acelorasi subiecte de examen (care vizeaza testarea cu preponderenta a nivelului de aplicare a cunostintelor si a nivelului de stapanire a capacitatilor esentiale ale absolventilor invatamantului gimnazial), beneficiaza de aceleasi conditii de desfasurare a examenului si de un grad inalt de obiectivitate in procesul de corectare si notare realizat in centre speciale de corectare la nivel judetean si la nivelul municipiului Bucuresti, prin asigurarea unei aplicari uniforme a baremelor analitice de corectare si notare a lucrarilor scrise.

Calculul mediei de admitere in liceu se face astfel:

(TN + ABS) / 2 = MA, unde:

TN = media generala obtinuta la testele nationale;

ABS = media generala de absolvire a claselor a V-a - a VIII-a;

MA = media de admitere.

Proiectare la nivel conceptual

Entitati:

Scoala;

Elev.

Atribute ce apartin entitatilor:

Scoala: cod, nume, adresa, oras, judet;

Elev: cod, nume, nota matematica, nota romana, materie optionala, nota materie optionala, medie generala, medie examen, medie finala (de admitere).

SCOLI (evidenta scolilor pentru care avem campurile):

Cod scoala: cheie primara ce identifica in mod unic fiecare scoala;

Nume scoala: camp ce contine numele scolii respective;

Adresa: strada si numarul;

Oras: orasul in care se afla scoala;

Judet: judetul in care se afla scoala.

ELEVI (inregistrarea elevilor participanti la examen):

Cod: cheie primara ce identifica in mod unic fiecare elev;

Cod scoala: cheie straina ce realizeaza legatura cu tabelul SCOLI si reprezinta scoala de provenienta a elevului;

Nume: camp ce contine numele elevului respectiv;

Nota matematica: nota obtinuta de elev la testul de matematica;

Nota romana: nota obtinuta la testul de limba si literetura romana;

Optional: materia optionala aleasa de elev (geografie sau istorie);

Nota optional: nota obtinuta la testul de geografie/istorie;

Media generala: media generala de absolvire a gimnaziului;

Media examen: media rezultatelor obtinute la testele nationale;

Media finala: media obtinuta dintre media generala si media testelor nationale.

Proiectare la nivel logic

SCOLI [ cod_scoala, nume_scoala, adresa, oras, judet]

ELEVI [ cod, cod_scoala, nume, nota_mate, nota_rom, optional, nota_opt, media_gen, media_examen, media_finala]

Proiectare la nivel fizic

Tabelele aplicatiei:

Elev

Scoala

Comenzile in limbaj SQL folosite pentru a genera tabelele utilizate si inregistrarile initiale:

CREATE TABLE SCOLI(

COD_SCOALA INTEGER NOT NULL

GENERATED ALWAYS AS IDENTITY

CONSTRAINT SCOALA_PK PRIMARY KEY,

NUME_SCOALA VARCHAR(45),

ADRESA VARCHAR(30),

ORAS VARCHAR(20),

JUDET VARCHAR(20)

CREATE TABLE ELEVI(

COD INTEGER NOT NULL

GENERATED ALWAYS AS IDENTITY

CONSTRAINT ELEV_PK PRIMARY KEY,

COD_SCOALA INTEGER NOT NULL,

NUME VARCHAR(40),

NOTA_MATE NUMERIC(4,2),

NOTA_ROM NUMERIC(4,2),

OPTIONAL VARCHAR(20),

NOTA_OPT NUMERIC(4,2),

MEDIA_GEN NUMERIC(4,2),

MEDIA_EXAMEN NUMERIC(4,2),

MEDIA_FINALA NUMERIC(4,2),

CONSTRAINT SCOALA_FK FOREIGN KEY (COD_SCOALA) REFERENCES SCOLI(COD_SCOALA) ON DELETE CASCADE

INSERT INTO SCOLI (NUME_SCOALA, ADRESA, ORAS, JUDET)

VALUES

('SCOALA GIMNAZIALA NR. 16 NICOLAE BALCESCU', 'STR. ARIESULUI NR. 8', 'GALATI', 'GALATI'),

('SCOALA GIMNAZIALA NR. 12 MIRON COSTIN', 'STR. ALBA IULIA NR. 1-2', 'GALATI', 'GALATI'),

('SCOALA GIMNAZIALA NR. 22 DIMITRIE CANTEMIR', 'STR. ZIDARILOR NR. 5', 'GALATI', 'GALATI'),

('SCOALA GIMNAZIALA NR. 20 TRAIAN', 'STR. TRAIAN NR. 318', 'GALATI', 'GALATI'),

('SCOALA GIMNAZIALA NR. 17 NICHITA STANESCU', 'STR. COSTACHE CONACHI NR. 2', 'GALATI', 'GALATI');

INSERT INTO ELEVI (COD_SCOALA, NUME, NOTA_MATE, NOTA_ROM, OPTIONAL, NOTA_OPT, MEDIA_GEN, MEDIA_EXAMEN, MEDIA_FINALA)

VALUES

(1, 'ANGHELESCU I. IULIA MIRUNA',8.50,9.80,'GEOGRAFIE',10.00,8.89,0.00,0.00),

(2, 'MOINEAGU I. MARILENA',9.10,6.80,'ISTORIE',7.00,8.10,0.00,0.00),

(2, 'CORNEA D. ALEXANDRU',5.40,7.10,'ISTORIE',8.40,6.92,0.00,0.00),

(1, 'MIHAI F. CRISTINA',3.25,8.00,'ISTORIE',5.55,7.56,0.00,0.00),

(3, 'ANDONIE A. COSTEL',6.25,5.10,'GEOGRAFIE',9.50,8.34,0.00,0.00),

(5, 'BUHOCI G. TEODOR LIVIU',7.20,4.10,'ISTORIE',4.00,5.80,0.00,0.00),

(5, 'CAULEA I. NICOLAE',10.00,8.70,'ISTORIE',9.45,9.35,0.00,0.00),

(5, 'GRIGORAS M. MARIUS MARIAN',7.00,6.85,'GEOGRAFIE',8.40,9.10,0.00,0.00),

(2, 'CODRESCU G. ALEXANDRA',2.75,8.95,'GEOGRAFIE',7.00,7.50,0.00,0.00),

(4, 'PREDA G. ADRIAN',5.25,9.85,'GEOGRAFIE',10.00,8.83,0.00,0.00),

(3, 'GHEOR9GHIU C. EMMA-CRISTINA',4.00,6.30,'GEOGRAFIE',5.00,6.27,0.00,0.00),

(4, 'CAPATAN T. MARINA-ANDREEA',9.00,3.50,'GEOGRAFIE',7.05,8.12,0.00,0.00),

(4, 'HUZUM A. CORNEL-COSMIN',7.50,6.20,'GEOGRAFIE',4.95,5.90,0.00,0.00),

(4, 'ISAIA D. GABRIEL',6.00,1.80,'ISTORIE',4.15,7.39,0.00,0.00),

(3, 'TUDOR M. MARIAN',10.00,9.90,'GEOGRAFIE',9.25,9.94,0.00,0.00),

(2, 'PURLUCA T. MIHAI',6.35,8.20,'GEOGRAFIE',5.85,9.04,0.00,0.00),

(2, 'RADU I. ANDREI ALEXANDRU',1.70,3.15,'GEOGRAFIE',5.00,6.77,0.00,0.00),

(1, 'OANEA L. CRISTINA',7.00,10.00,'ISTORIE',9.20,8.67,0.00,0.00),

(5, 'CERCHEZ P. ANA MARIA',8.05,8.10,'ISTORIE',8.80,7.91,0.00,0.00),

(5, 'DUMITRU N. ALEXANDRU RAZVAN',4.50,6.85,'GEOGRAFIE',9.00,6.43,0.00,0.00),

(3, 'PARASCHIV G. ANDREI AURELIAN',8.10,7.00,'ISTORIE',5.30,6.68,0.00,0.00),

(1, 'DANILA M. OANA-CRISTINA',7.90,5.00,'GEOGRAFIE',4.90,7.14,0.00,0.00),

(4, 'ANGHEL D. MARIA ADNANA',9.50,9.20,'GEOGRAFIE',10.00,9.87,0.00,0.00),

(2, 'BUBULAC V. VICTOR-EDUARD',4.00,5.80,'GEOGRAFIE',6.00,8.07,0.00,0.00),

(5, 'PETRICA V. IONEL',5.00,4.85,'ISTORIE',5.10,6.02,0.00,0.00);

UPDATE ELEVI

SET MEDIA_EXAMEN = (NOTA_MATE+NOTA_ROM+NOTA_OPT)/3

WHERE NOTA_MATE>5 AND NOTA_ROM>5 AND NOTA_OPT>5;

UPDATE ELEVI

SET MEDIA_FINALA = (MEDIA_EXAMEN+MEDIA_GEN)/2

WHERE MEDIA_EXAMEN>0;

Aplicatia a fost programata in limbaj Java in mediul de programare NetBeans IDE v6.5 si arata in felul urmator: