#617 · Subiectul I · Baze de date

Concursuri școlare — model entitate-relație și numărul de premii la fiecare concurs

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

Enunț

Elevii unei școli participă la diferite concursuri și olimpiade școlare, un elev putând participa la mai multe concursuri de-a lungul unui an școlar. Responsabilul pentru proiecte și programe educative dorește să țină evidența acestor participări și a rezultatelor obținute: premii, medalii, participări. Pentru identificarea elevilor participanți se memorează numărul matricol, numele, prenumele, clasa și coordonatorul. Concursurile sunt identificate prin nume concurs, data desfășurării și tipul concursului (științific, artistic, sportiv, etc.).

Cerință: Să se numere câte premii s-au obținut la fiecare concurs la care școala a avut participanți.

Cerința comună: entități și atribute, diagrama entitate-relație, minimum două înregistrări pe entitate, instrucțiunea SQL.

Rezolvare

SQL

SELECT c.nume, COUNT(CASE WHEN p.rezultat = 'premiu' THEN 1 END) AS nr_premii
FROM CONCURS c
JOIN PARTICIPARE p ON p.id_concurs = c.id_concurs
GROUP BY c.id_concurs, c.nume;

Notația e explicată la biroul de traduceri; GROUP BY pe tabela de legătură, la Balul Bobocilor.

1. Entități: ELEV (# nr_matricol, * nume, * prenume, * clasa, * coordonator) · CONCURS (# id_concurs, * nume, * data_concurs, * tip) · PARTICIPARE (# nr_matricol, # id_concurs, * rezultat (premiu / medalie / participare))

Concursul primește un id_concurs artificial: „nume + dată” identifică și el, dar ar face cheia compusă și greu de folosit ca cheie străină.

2. Relații: ELEV–CONCURS e M:N → PARTICIPARE, cu rezultatul ca atribut al legăturii (același elev poate lua premiu la un concurs și doar participare la altul).

3. Înregistrări

ELEV

nr_matricol (Pk)	nume	prenume	clasa	coordonator
501	Ilie	Bogdan	10B	prof. Marin
502	Costea	Ana	11A	prof. Pavel

CONCURS

id_concurs (Pk)	nume	data_concurs	tip
1	Olimpiada de Informatică	15.02.2020	stiintific
2	Cupa Liceelor la Șah	07.03.2020	sportiv

PARTICIPARE

nr_matricol (Pk,Fk1)	id_concurs (Pk,Fk2)	rezultat
501	1	premiu
502	1	premiu
501	2	participare
502	2	medalie

4. SQL

Cum funcționează

Jonctiunea păstrează doar concursurile cu participanți. COUNT(expresie) numără doar valorile ne-NULL: CASE dă 1 la premii și NULL în rest. Rezultat: Olimpiada → 2, Cupa la Șah → 0. Concursul cu zero premii apare, cu 0, pentru că școala a avut participanți acolo.

Greșeli frecvente

WHERE p.rezultat = 'premiu' + COUNT(*): pare natural, dar filtrul elimină rândurile înainte de grupare → Cupa la Șah dispare din rezultat, deși cerința o include.
COUNT(*) fără filtru: numără participările (2 și 2), nu premiile.
Medaliile numărate ca premii: enunțul le enumeră separat („premii, medalii, participări”); dacă profesorul le vrea împreună: p.rezultat IN ('premiu', 'medalie').

Exersează mai departe

Probleme cu aceeași tehnică

#601Baze de dateMagazin plafar — model entitate-relație și cantitatea totală pe produs #615Baze de dateBalul Bobocilor — model entitate-relație și punctajul maxim pe fiecare probă #128Baze de dateBaze de date FIRMA — numărul de angajați pe fiecare departament și telefoanele din Financiar #136Baze de dateBaze de date FOOD_DELIVERY — tipuri de bucătărie distincte și numărul de comenzi pe tip #137Baze de dateBaze de date FOOD_DELIVERY — suma comenzilor livrate, în total și pe restaurant #140Baze de dateBaze de date FOOD_DELIVERY — ștergerea comenzilor anulate și numărul lor pe restaurant