#603 · Subiectul I · Baze de date

Proiecte cu fonduri nerambursabile — model entitate-relație și angajații cu șefii lor

✓ Rezolvare explicatăBucurești 2020 · nr. 6

Enunț

O companie specializată pe atragere de fonduri nerambursabile desfășoară mai multe proiecte. Pentru fiecare proiect se cunoaște identificatorul proiectului, denumirea acestuia, categoria, valoarea estimativă în Euro, data începerii și eventual data finalizării. La un proiect lucrează mai mulți angajați, coordonați de un manager, iar pentru fiecare dintre aceștia se reține codul angajatului, numele, prenumele, data angajării, funcția în cadrul proiectului și salariul cuvenit pentru munca desfășurată. Pentru buna desfășurare a activității un angajat nu poate lucra în același timp la mai multe proiecte.

Cerință: Să se afișeze pentru angajații care au șefi, numele angajatului și al șefului său.

Cerința comună tuturor subiectelor: (1) deduceți entitățile și atributele lor semnificative; (2) stabiliți relațiile dintre entități și reprezentați-le într-o diagramă entitate-relație; (3) dați minimum două înregistrări fictive pentru fiecare entitate; (4) scrieți instrucțiunea SQL pentru cerința subiectului.

Rezolvare

SQL

SELECT a.nume || ' ' || a.prenume AS angajat,
       s.nume || ' ' || s.prenume AS sef
FROM ANGAJAT a
JOIN ANGAJAT s ON a.cod_sef = s.cod_angajat
ORDER BY sef, angajat;

Notația e explicată la biroul de traduceri; jonctiunea între două tabele, la complexul turistic. Aici apare ceva nou: o relație a unei entități cu ea însăși.

1. Entități și atribute

PROIECT: # id_proiect, * denumire, * categorie, * valoare_estimata, * data_inceput, ° data_finalizare
ANGAJAT: # cod_angajat, * nume, * prenume, * data_angajarii, * functie, * salariu

Managerul nu e o entitate separată: e tot un angajat (are cod, nume, salariu). De aceea relația „coordonează” leagă ANGAJAT de ANGAJAT.

2. Relații (diagrama, în text)

PROIECT are ANGAJAT — 1:N: la un proiect lucrează mai mulți angajați; un angajat lucrează la un singur proiect (enunțul o interzice explicit pe cea de-a doua). → id_proiect (Fk1) în ANGAJAT, fără tabelă de legătură.
ANGAJAT coordonează ANGAJAT — 1:N, recursivă: un șef are mai mulți subordonați, un angajat are cel mult un șef. → cod_sef (Fk2) în ANGAJAT, care trimite tot la cod_angajat. E opțional (°): managerul nu are șef. În diagramă: o buclă care pleacă și se întoarce la ANGAJAT.

3. Înregistrări

PROIECT

id_proiect (Pk)	denumire	categorie	valoare_estimata	data_inceput	data_finalizare
1	Digitalizare școli rurale	educație	250000	01.09.2019	—
2	Pistă de biciclete	infrastructură	400000	15.03.2019	30.06.2020

ANGAJAT

cod_angajat (Pk)	nume	prenume	data_angajarii	functie	salariu	id_proiect (Fk1)	cod_sef (Fk2)
10	Georgescu	Elena	01.02.2015	manager proiect	9000	1	—
11	Matei	Paul	10.05.2018	expert IT	6500	1	10
12	Rusu	Ioana	07.01.2019	asistent	4000	1	10
20	Neagu	Victor	03.11.2014	manager proiect	9500	2	—
21	Lazăr	Dan	20.03.2019	inginer	7000	2	20

4. Instrucțiunea SQL

Cum funcționează

Tabela ANGAJAT se folosește de două ori, cu două aliasuri: a = angajatul, s = șeful. Condiția a.cod_sef = s.cod_angajat caută, pentru fiecare angajat, rândul șefului lui. Rezultat: Matei Paul și Rusu Ioana → Georgescu Elena; Lazăr Dan → Neagu Victor. Cei doi manageri au cod_sef gol, deci jonctiunea (internă) îi lasă pe dinafară — exact „angajații care au șefi”.

Greșeli frecvente

Condiția inversată (a.cod_angajat = s.cod_sef): nu dă eroare, dar coloanele se schimbă între ele — managerii apar ca „angajat”, subordonații ca „șef”.
LEFT JOIN: adaugă managerii cu șef gol, deși cerința îi exclude.
Fără aliasuri (FROM ANGAJAT JOIN ANGAJAT): eroare, coloanele devin ambigue.
Tabelă separată MANAGER: dublează datele unui angajat (nume, salariu) și se strică la prima promovare.
Tabelă de legătură ANGAJAT_PROIECT (M:N): contrazice „un angajat nu poate lucra în același timp la mai multe proiecte”.

Exersează mai departe

Probleme cu aceeași tehnică

#608Baze de dateFundație de cursuri — model entitate-relație și cursanții înscriși în 2019 #611Baze de dateStudio muzical — model entitate-relație și albumele unui artist, descrescător după an #613Baze de dateGrădină botanică — model entitate-relație și plantele din specia erbacee #616Baze de dateSpital județean — model entitate-relație și saloanele ocupate din clădirile 1 și 2 #121Baze de dateBaze de date FIRMA — angajații și primul departament înființat #122Baze de dateBaze de date FIRMA — numărul de angajați și angajații din departamentul Comercial