Gestiunea caminelor studentesti

Gestiunea caminelor studentesti

CONTINUTUL LUCRARII:

I. PREZENTAREA PROIECTULUI, REGULI

II. DIAGRAMA ENTITATE / RELATIE

III. ENTITATI, RELATII SI ATRIBUTE

IV. DIAGRAMA CONCEPTUALA

V. SCHEMELE RELATIONALE

VI. TABELELE ASOCIATE

VII. OPERATORII ALGEBREI RELATIONALE

VIII. CERERI ASUPRA BAZEI DE DATE

IX. OPTIMIZAREA UNEI CERERI

I.Prezentarea proiectului, Reguli

Tema acestui proiect este gestiunea unei baze de date care contine evidenta caminelor studentesti de la inceputul unui an universitar. Caminele studentesti sunt repartizate dupa criterii si in functie de mai multe reguli pe care un student trebuie sa le indeplineasca. In primul rand el trebuie sa fie student al unei facultati dintr-o universitate, trebuind sa aiba media din anul anterior (sau de la admitere, daca este anul 1) peste un anumit prag impus de facultatea respectiva.

Un student poate fi repartizat intr-o camera care mai are cel putin un loc liber, cu conditia suplimentara ca daca mai are si alti studenti, acestia au acelasi sex cu respectivul student. Fiecare facultate are diferite medii minime pe care trebuie sa le aiba studentii pentru a fi repartizati la diferite camine.

In acest proiect nu se va face repartizarea studentilor in camine, deoarece aceasta procedura difera de la o universitate la alta.

Reguli :

O facultate are cel putin un camin

Un camin are cel putin o camera

Orice camin este administrat de un singur administrator

Un student primeste o singura camera

O fata nu poate fi repartizata intr-o camera de baieti si nici un baiat intr-o camera de fete

O facultate are cel putin un student

Pot exista mai multe facultati cu acelasi nume, dar nu in aceeasi Universitate.

Nu pot exista 2 universitati cu acelasi nume deoarece in numele universitatii contine si numele orasului in care se afla

Orasul in care se afla caminul coincide cu cel in care se afla facultatea

Nu este neaparata nevoie ca orasul in care este facultatea sa coincida cu cel in care este universitatea (o universitate poate avea facultati in alte orase0

Pentru ca studentul sa aiba dreptul de a fi repartizat la un camin, orasul in care are resedinta trebuie sa fie diferit de cel in care este facultatea

II.Diagrama Entitate - Relatie

III. Descrierea entitatilor, relatiilor si atributelor

1.Entitatea indpendenta UNIVERSITATE - orice Universitate din tara. Cheia primara este atributul cod_universitate.

Atribute :

cod_universitate : sir de 5 caractere care identifica o Universitate inscrisa in baza de date

nume : sir de maxim 20 de caractere care reprezinta numele Universitatii

adresa : sir de maxim 30 de caractere care reprezinta adresa la care se afla rectoratul Universitatii

telefon : sir de maxim 10 caracterecare reprezinta numarul de telefon la care poate fi contactata Universitatea.

2.Entitatea dependenta FACULTATE memoreaza facultatile dintr-o

Universitate. Entitatea FACULTATE este dependenta de entitatea

UNIVERSITATE deoarece pot fi facultati cu acelasi nume, dar la

uiniversitati diferite.

Cheia primara este concatenarea dintre cod_universitate si cod_facultate.

Atribute :

cod _facultate : sir de 5 caractere ce reprezinta codul unei facultati dintr-o Universitate

cod_universitate : sir de 5 caractere care identifica o Universitate inscrisa in baza de date.Concatenarea cu atributul cod_facultate reprezinta o modalitate de identificare a facultatii in baza de date.

nume : sir de maxim 30 de caractere care reprezinta numele facultatii

oras : sir de maxim 20 de caractere care reprezinta orasul in care se afla facultatea

numar_studenti : numeric intreg de lungime maxima 5 care reprezinta numarul de studenti ai facultatii

locuri_camin : numeric intreg de lungime maxima 5 care reprezinta numarul de locuri la camin de care dispune facultatea

3.Entitatea independenta STUDENT memoreaza date despre student.

Atribute :

cod_student : sir de 5 caractere prin care se identifica un student in baza de date

nume : sir de maxim 20 de caractere ce reprezinta numele studentului

prenume : sir de maxim 20 de caractere ce reprezinta prenumele studentului

an_studiu : numeric intreg de lungime maxima 1 si reprezinta anul de studiu in care este studentul

medie : float ce reprezinta media studentului din anul precedent sau media de admitere, daca studentul este anul 1

sex : caracter ce reprezinta sexul studentului si poate fi M sau F

oras : sir de maxim 20 de caractere ce reprezinta orasul in care are studentul domiciliul stabil

4.Entitatea dependenta CAMERA memoreaza date despre tipul camerei si

numarul de locuri care mai sunt libere.Aceasta entitate este dependenta de

entitatea CAMIN deoarece exista mai multe camine care au acelasi cod, dar

sunt in camine diferite.

Cheia primara este concatenarea dintre cod_camin si cod_camera.

Atribute:

cod_camera# : un sir de caractere de lungime 3 care reprezinta codul camerei

cod_camin# : un sir de caractere de lungime 5 si identifica in mod unic caminul in baza de date. Concatenarea cu atributul cod_camera duce la identificarea camerei in baza de date.

tip_camera : caracter ce reprezinta sexul studentilor care sunt deja repartizati in camera respectiva.Poate avea valorile M, F sau L, L in cazul in care camera este libera.

locuri_libere : numeric de lungime 1 ce reprezinta numarul de locuri libere care din camera

5.Entitatea independenta CAMIN memoreaza caminele si locurile

disponibile din fiecare.

Atribute:

cod_camin# : un sir de caractere de lungime 5 ce identifica in mod unic caminul in baza de date.

nume_camin : sir de caractere de lungime maxima 20 care reprezinta numele caminului

numar_camere : numeric intreg ce reprezinta numarul de camere libere din camin

oras : un sir de caractere de lungime maxima 20 care reprezinta orasul in care se afla caminul

telefon : sir de caractere de lungime maxima 10 care reprezinta numarul de telefon de la administratie

6.Entitatea dependenta ADMINISTRATOR memoreaza date despre

administratorii de camine. Entitatea administrator este dependenta de

entitatea camin deoarece administratorul nu ar mai putea fi numit

administrator daca nu ar fi caminul care sa fie administrat.

Atribute:

cod_camin# : un sir de caractere de lungime 5 ce identifica in mod unic caminul in baza de date.

cod_administrator# : un sir de caractere de lungime maxima 5 ce identifica in mod unic administratorul in baza de date.

nume : sir de maxim 20 de caractere ce reprezinta numele administratorului

prenume : sir de maxim 20 de caractere ce reprezinta prenumele administratorului

adresa : un sir de caractere de lungime maxima 30 care reprezinta adresa administratorului

telefon : sir de caractere de lungime maxima 10 care reprezinta numarul de telefon al administratorului

salariu : numeric de dimensiune maxima 7 care reprezinta salariul administratorului

Relatiile care intervin in model sunt :

apartine_de - leaga entitatile FACULTATE si UNIVERSITATE. Intr-o Universitate pot fi mai multe facultati, dar trebuie sa fie cel putin una, si o facultate trebuie sa apartina unei singure universitati.Cardinalitatea minima este 1:1 si maxima este m:1

invata_in - leaga entitatile STUDENT si FACULTATE. Intr-o facultate pot fi mai multi studenti dar cel putin unul, si un student poate invata_in mai multe facultati, dar in cel putin una. Cardinalitatea minima este 1:1 si cea maxima este m : m.

primeste - leaga entitatile STUDENT si CAMERA. Un student poate sa primeasca cel mult un loc intr-o camera, iar o camera trebuie sa aiba cel putin un student.Cardinalitatea minima este 1:1 si cea maxima este m:1.

se_afla_in - leaga entitatile CAMERA si CAMIN. Un camin are mai multe camera, dar cel putin o una, iar o camera se afla intr-un singur camin. Cardinalitatea minima este 1:1 si cea maxima este m:1.

are - leaga entitatile FACULTATE si CAMIN. O facultate poate sa aiba mai multe camine sau poate sa nu aiba nici unul, iar un camin poate sa apartina mai multor facultati, dar cel putin uneia. Cardinalitatea minima este 1:1 si cea maxima este m:1.

administreaza - leaga entitatile ADMINISTRATOR si CAMIN. Un camin este administrat de un singur administrator, iar un administrator administreaza un singur camin. Cardinalitatea minima este 1:1 si cea maxima tot 1:1.

IV.Diagrama conceptuala :

V. SCHEMELE RELATIONALE :

1. UNIVERSITATE(cod_universitate#, nume, adresa, telefon)
2. FACULTATE(cod_facultate#, cod_universitate#, nume, oras, numar_studenti, locuri_camin)
3. INVATA_IN(cod_universitate#, cod_facultate#, cod_student#, forma_invatamant).Forma_invatamant reprezinta forma de invatamant a studentului si este un sir de caractere de dimensiune maxima 4 si are una din valorile "zi", "Id" (invatamant la distanta) sau "taxa"(care inseamna la zi, dar pe locuruile cu taxa)
4. STUDENT(cod_student#, nume, prenume, an_studiu, medie, sex, vrea_camin, oras, cod_camin, cod_camera)
5. CAMERA(cod_camera#, cod_camin#, tip_camera, locuri_libere)
6. CAMIN(cod_camin#, nume_camin, numar_camere, oras, telefon)
7. ARE(cod_universitate#, cod_facultate#, cod_camin#,numar _locuri). Numar_locuri este numeric intreg de lungime maxima 3 si reprezinta numarul de locuri pe care le are o facultate dintr-o universitate intr-un anumit camin
8. ADMINISTRATOR(cod_camin#, cod_administrator#, nume, prenume, adresa, telefon, salariu)

VI.TABELELE ASOCIATE :

UNUVERSITATE

FACULTATE

INVATA_IN

STUDENT

CAMERA

CAMIN

ARE

ADMINISTRATOR

VII. OPERATORII ALGEBREI RELATIONALE

1. PROJECT (proiectie) - extrage atributele specificate;

Ex: Sa se obtina o lista care sa contina : numele, prenumele, anul de studiu si media pentru fiecare student.

Proiectie in algebra relationala

REZULTAT = PROJECT (STUDENT, nume, prenume, an_studiu, media).

Proiectie fara dubluri in SQL

SELECT DISTINCT    nume, prenume, an_studiu, media

FROM student;

STUDENT

REZULTAT

2. SELECT (selectie) - extrage tupluri ce satisfac o conditie specificata;

Ex: Sa se obtina informatii complete despre facultatile care au mai mult de 2000 studenti.

Selectie in algebra relationala :

REZULTAT = SELECT (FACULTATE, numar_studenti>2000

Selectie in SQL

SELECT    *

FROM    facultate

WHERE    numar_studenti>2000;

FACULTATE

REZULTAT

3. DIFFERENCE (diferenta) - extrage tupluri care apar intr-o relatie, dar nu apar in cealalta;

Ex: Sa se obtina o lista cu numele, prenumele, sexul pentru pentru studentii care sunt intr-un an de studiu> 2 si care au media peste 7.50.

Diferenta in algebra relationala

R1 = PROJECT (SELECT (STUDENT, an_studiu>2), nume, prenume, sex );

R2 = PROJECT (SELECT(STUDENT, an_studiu>2 and media<7.50), nume, prenume, sex);

REZULTAT = DIFFERENCE (R1, R2).

Diferenta in SQL

SELECT nume, prenume, sex

FROM student

WHERE an_studiu>2

MINUS

SELECT nume, prenume, sex

FROM student

WHERE an_studiu>2 and media<7.50

R1

R2

REZULTAT

4. PRODUCT (produs cartezian) - genereaza toate perechile posibile de tupluri, primul element al perechii fiind luat din prima relatie, iar cel de-al doilea element din cealalta relatie;

Ex: Sa se obtina lista tuturor posibilitatilor de atasare a unui student de sex masculin la un camin..

Produs cartezian in algebra relationala:

R1 = PROJECT (SELECT (STUDENT, sex='M'), cod_student);

R2 = PROJECT (CAMIN, nume_camin);

REZULTAT = PRODUCT (R1, R2).

Produs cartezian in SQL:

SELECT cod_student, nume_camin

FROM student, camin;

5. UNION (reuniune) - reuneste doua relatii;

Ex: Sa se obtina lista numelor studentilor care au media sub 8 si a studentelor cu media peste 9.

Reuniune in algebra relationala

R1 = PROJECT (SELECT (STUDENT, media<8 and sex='M'), nume, prenume);

R2 = PROJECT (SELECT (STUDENT, media>9 and sex='F'), nume, prenume);

REZULTAT = UNION (R1, R2).

Reuniune in SQL

SELECT nume, prenume

FROM student

WHERE media<8 and sex='M'

UNION

SELECT nume, prenume

FROM student

WHERE media>9 and sex='F'

R1 R2

REZULTAT

6. INTERSECT (intersectie) - extrage tupluri care apar in ambele relatii;

Ex: Sa se obtina o lista cu codul caminului si codul camerei pentru camerele de baieti in care mai sunt cel putin 3 locuri libere

Intersectie in algebra relationala

R1 = PROJECT (SELECT (CAMERA, tip_camera='M'), cod_camin, cod_camera);

R2 = PROJECT (SELECT (CAMERA, locuri_libere>=3), cod_camin, cod_camera);

REZULTAT = INTERSECT (R1, R2).

Intersectie in SQL

SELECT cod_camin, cod_camera

FROM camera

WHERE tip_camera='M'

INTERSECT

SELECT cod_camin, cod_camera

FROM camera

WHERE locuri_libere>=3;

R1 :

R2 :

REZULTAT :

7. DIVISION (diviziune) - extrage valorile atributelor dintr-o relatie, care apar in toate valorile atributelor din cealalta relatie;

Ex: Sa se obtina o lista a tuturor facultatilor din fiecare universitate care au cel putin 2 locuri la camin.

Diviziune in algebra relationala

R1 = PROJECT (ARE, cod_universitate, cod_facultate, cod_camin);

R2 = PROJECT (SELECT (FACULTATE, locuri_camin>=2), cod_camin);

REZULTAT = DIVISION (R1, R2).   

R1 :

R2 :

REZULTAT :

8. NATURAL JOIN (compunere naturala) - combina tupluri din doua relatii, cu conditia ca atributele comune sa aiba valori identice;

Ex: Sa se obtina informatii complete despre camere si caminele de care apartin. (CAMERA are cheia primara cod_camin concatenat cu cod_camera deoarece caminele lucreaza independent unele de celelalte)

Compunere naturala in algebra relationala:

REZULTAT = JOIN (CAMERA, CAMIN).

Compunere naturala in SQL :

SELECT *

FROM camera a, camin b

WHERE a.cod_camin=b.cod_camin

CAMERA

CAMIN

REZULTAT

VIII. CERERI ASUPRA BAZEI DE DATE

Cererea 1 Sa se obtina o lista cu informatii(nume, prenume, cod_facultate) despre studentele care stau la caminul "Grozavesti_D" si au media mai mica de 8.30.

Pasul 1:

Unesc tabelele STUDENT si INVATA_IN

Proiectez rezultatul dupa atributele cod_student, nume, prenume, cod_facultate

Pasul 2 :

Unesc tabelele STUDENT si CAMERA

Selectez tuplurile in care sexul studentului este "F" si media este mai mica de 8.30

Unesc rezultatul cu tabela CAMININ

Selectez tuplurile in care numele caminului este "Grozavesti_D"

Proiectez rezultatul dupa atributul cod_student

Rezultatul va fi unirea rezultatelor celor 2 pasi

Rezolvare prin metoda algebrica (relationala):

R1=JOIN(STUDENT, INVATA_IN)

R2=PROJECT(R1, cod_student, nume, prenume, cod_facultate)

R3=JOIN(STUDENT, CAMERA)

R4=SELECT(R3, sex='F')

R5=SELECT(R4, media<8.30)

R6=JOIN(R5, CAMIN)

R7=SELECT(R6, nume_camin='Grozavesti_D')

R8=PROJECT(R7, cod_student)

REZULTAT=JOIN(R2,R8)

REZULTAT=JOIN( PROJECT ( JOIN (STUDENT, INVATA_IN),cod_student, nume, prenume, cod_facultate ) , PROJECT( SELECT ( SELECT ( SELECT ( JOIN(STUDENT, CAMERA), sex='F'), media<8.30), nume_camin='Grozavesti_D'), cod_student ) )

Cererea 2 Sa se obtina o lista cu informatii(nume, prenume, cod_facultate) despre studentii care stau la caminul "Regie_P2" si au media mai mare de 7.30.

Pasul 1:

Unesc tabelele STUDENT si INVATA_IN

Proiectez rezultatul dupa atributele cod_student, nume, prenume, cod_facultate

Pasul 2 :

Unesc tabelele STUDENT si CAMERA

Selectez tuplurile in care media este mai mare de 7.30

Unesc rezultatul cu tabela CAMININ

Selectez tuplurile in care numele caminului este "Regie_P2"

Proiectez rezultatul dupa atributele cod_student

Rezultatul va fi unirea rezultatelor celor 2 pasi

Rezolvare prin metoda matematica:

cod_student nume_student prenume_student cod_facultate

REZULTAT= (STUDENT INVATA_IN (

Media>7.30

nume_ camin= 'Regie_P2'

cod_student

(σ (CAMIN    ( STUDENT CAMERA)

Cererea 3 Sa se obtina o lista cu numele, prenumele si codul studentilor din anul 2 care sunt la universitatea'U.P.B.', 'Facultatea de calculatoare'.

Pasul 1:

Proiectez dupa cod_student, nume, prenume, an_studiu

Selectez tabela STUDENT dupa an_studiu=2

Pasul 2:

Unesc tabelele FACULTATE si UNIVERSITATE

Selectez rezultatul dupa nume_universitate='U.P.B'

Selectez rezultatul dupa nume_facultate='Facultatea de calculatoare'

Unesc rezultatul cu tabela INVATA

Proiectez dupa cod_student

Unesc cele 2 rezultate

_REZULTAT

		UNIVERSITATE		FACULTATE

IX. OPTIMIZAREA UNEI CERERI

In cele ce urmeaza vom efectua optimizare pentru cererea 3 (in varianta grafica):

_{Aplicand compunerea proiectiei cu JOIN-ul, rezulta cererea optimizata :}

_REZULTAT