Baze de date

Introducere în conceptele fundamentale ale bazelor de date relaționale și ale limbajului SQL.

Concepte generale și modelul relațional

O bază de date este o colecție organizată de date, structurată astfel încât informația să poată fi stocată, căutată și modificată eficient. Programul care o administrează se numește SGBD — Sistem de Gestiune a Bazelor de Date (exemple: MySQL, PostgreSQL, SQLite, Oracle). Distincția cerută la examen: baza de date este colecția de date, SGBD-ul este software-ul care o gestionează — nu sunt sinonime.

În modelul relațional, datele stau în tabele:

• o coloană (câmp, atribut) descrie o proprietate — de exemplu nume, data_nasterii — și are un tip de date fix (număr, text, dată);
• o linie (înregistrare, tuplu) descrie o entitate concretă — un elev anume;
• fiecare tabel are un nume unic în baza de date.

Regula de aur a modelului: fiecare celulă conține o singură valoare atomică — nu liste de valori. Dacă un elev are mai multe numere de telefon, ele nu se înghesuie într-o celulă, ci se mută într-un tabel separat.

Avantajele bazelor de date față de fișierele obișnuite: eliminarea redundanței (aceeași informație stocată o singură dată), integritatea datelor impusă prin reguli, accesul concurent al mai multor utilizatori și interogarea printr-un limbaj standard — SQL (Structured Query Language).

Chei primare și chei externe

Cheia primară (primary key) este coloana — sau combinația de coloane — care identifică unic fiecare înregistrare dintr-un tabel. Proprietăți obligatorii: valorile ei sunt unice și nu pot fi NULL. Exemple bune: CNP-ul, un id numeric generat automat. Exemplu prost: numele — două persoane pot avea același nume.

Cheia externă (foreign key) este o coloană dintr-un tabel care face referire la cheia primară a altui tabel, creând legătura dintre ele. În tabelul note, coloana id_elev este cheie externă către tabelul elevi: fiecare notă „știe” al cui e.

Cheia externă impune integritatea referențială: nu poți insera o notă pentru un id_elev care nu există în tabelul elevi și nu poți șterge un elev care are încă note asociate (decât dacă regula de ștergere prevede altceva). Aceasta este plasa de siguranță care împiedică apariția înregistrărilor „orfane”.

Tipurile de relații între tabele:

• 1:1 — unei înregistrări îi corespunde cel mult una în celălalt tabel (elev — dosar medical);
• 1:n — cea mai des întâlnită: un elev are mai multe note, dar fiecare notă aparține unui singur elev; cheia externă stă în tabelul de pe partea „n”;
• m:n — un elev urmează mai multe cursuri, un curs are mai mulți elevi; se implementează obligatoriu printr-un tabel de legătură cu două chei externe.

Confuzia clasică: cheia externă NU trebuie să fie unică — același id_elev apare la toate notele elevului respectiv; unicitatea e proprietatea cheii primare.

Interogarea datelor: SELECT, WHERE, ORDER BY

Instrucțiunea SELECT extrage date dintr-un tabel. Forma de bază:

• `SELECT nume, prenume FROM elevi;` — alege doar coloanele enumerate;
• `SELECT * FROM elevi;` — steaua înseamnă toate coloanele;
• `SELECT DISTINCT oras FROM elevi;` — elimină duplicatele din rezultat.

WHERE filtrează liniile care intră în rezultat: `SELECT * FROM elevi WHERE media >= 9;`. În condiții se folosesc operatorii de comparație (=, <, >, <=, >=, <> pentru diferit), operatorii logici AND, OR, NOT, plus:

• BETWEEN a AND b — interval închis, echivalent cu >= a AND <= b;
• IN (v1, v2, ...) — apartenența la o listă de valori;
• LIKE — potrivire de șabloane text: % înlocuiește orice secvență de caractere (inclusiv vidă), _ exact un caracter; `nume LIKE 'Po%'` găsește Popescu și Pop;
• IS NULL / IS NOT NULL — testul valorilor lipsă. Capcană sigură de examen: comparația `camp = NULL` nu funcționează — NULL nu este egal cu nimic, nici măcar cu el însuși; singura formă corectă este IS NULL.

ORDER BY sortează rezultatul: `ORDER BY media DESC, nume ASC` — descrescător după medie, iar la egalitate alfabetic după nume. Fără DESC, sortarea este implicit ASC (crescătoare).

Ordinea clauzelor este fixă și se cere ca atare: SELECT ... FROM ... WHERE ... ORDER BY ... — un ORDER BY pus înaintea lui WHERE este eroare de sintaxă.

Modificarea datelor: INSERT, UPDATE, DELETE

Cele trei instrucțiuni care schimbă conținutul tabelelor:

• INSERT INTO elevi (nume, media) VALUES ('Ionescu', 9.50); — adaugă o înregistrare nouă. Dacă se omite lista de coloane, trebuie furnizate valori pentru toate coloanele, în ordinea din definiția tabelului;
• UPDATE elevi SET media = 10 WHERE id = 3; — modifică valorile înregistrărilor care satisfac condiția;
• DELETE FROM elevi WHERE id = 3; — șterge înregistrările care satisfac condiția.

Avertismentul care apare în toate manualele și în viața reală: UPDATE sau DELETE fără clauza WHERE afectează TOATE înregistrările tabelului. `DELETE FROM elevi;` golește tot tabelul — fără confirmare, fără avertisment. Reflexul corect: scrii întâi un SELECT cu aceeași condiție WHERE, verifici ce linii returnează, abia apoi transformi interogarea în UPDATE sau DELETE.

Distincții de vocabular cerute la examen:

• DELETE șterge înregistrări (date), dar tabelul continuă să existe, chiar gol;
• DROP TABLE șterge tabelul însuși, cu structură cu tot — este comandă DDL (limbaj de definire a datelor), nu DML;
• instrucțiunile SELECT, INSERT, UPDATE, DELETE formează DML — limbajul de manipulare a datelor; CREATE, ALTER, DROP formează DDL — limbajul de definire.

La INSERT, valorile de tip text și dată se scriu între apostrofuri, iar încălcarea unei constrângeri (cheie primară duplicată, cheie externă inexistentă) face ca instrucțiunea să fie respinsă de SGBD.

Funcții agregate și gruparea datelor

Funcțiile agregate calculează o singură valoare dintr-o mulțime de linii:

• *COUNT() — numărul de linii; COUNT(camp) — numărul de valori nenule** din coloană — diferența dintre ele este o grilă clasică;
• SUM(camp) — suma valorilor; AVG(camp) — media aritmetică;
• MIN(camp) și MAX(camp) — cea mai mică, respectiv cea mai mare valoare.

Toate ignoră valorile NULL (cu excepția lui COUNT(*), care numără liniile indiferent de conținut). De aceea AVG(media) pe o coloană cu valori lipsă împarte doar la numărul valorilor existente.

GROUP BY împarte liniile în grupuri și aplică funcțiile agregate pe fiecare grup: `SELECT clasa, AVG(media) FROM elevi GROUP BY clasa;` produce câte o linie pentru fiecare clasă. Regula strictă: în SELECT pot apărea doar coloanele din GROUP BY și funcții agregate — o coloană negrupată și neagregată este o eroare (ce nume ai afișa pentru un grup întreg?).

HAVING filtrează grupurile, după agregare, pe când WHERE filtrează liniile, înainte de grupare — distincția cea mai testată din tot capitolul:

• `WHERE media >= 5` — elimină elevii corigenți înainte de orice calcul;
• `HAVING AVG(media) > 8` — păstrează doar clasele cu media generală peste 8.

O funcție agregată nu poate apărea în WHERE (la momentul filtrării liniilor, agregatul nu e încă calculat); locul ei de filtrare este exclusiv HAVING. Ordinea completă a clauzelor: SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY.

Interogări pe mai multe tabele: JOIN

Informația reală e împrăștiată în mai multe tabele — elevii într-unul, notele în altul — iar JOIN le recombină pe baza legăturii dintre cheia externă și cheia primară:

`SELECT e.nume, n.nota FROM elevi e JOIN note n ON e.id = n.id_elev;`

Clauza ON spune cum se potrivesc liniile: fiecare notă se lipește de elevul cu id-ul corespunzător. Aliasurile (e, n) scurtează scrierea și devin obligatorii când coloane din tabele diferite poartă același nume.

Tipurile de join cerute la examen:

• INNER JOIN (sau simplu JOIN) — păstrează doar perechile care au corespondent în ambele tabele; un elev fără nicio notă dispare din rezultat;
• LEFT JOIN — păstrează toate liniile tabelului din stânga, iar acolo unde nu există corespondent în dreapta, coloanele acestuia se completează cu NULL; exact instrumentul pentru întrebarea „care elevi NU au nicio notă?” — LEFT JOIN plus condiția `n.id_elev IS NULL`;
• RIGHT JOIN — simetricul, păstrează toate liniile tabelului din dreapta.

Capcana fundamentală: un JOIN fără condiția ON (sau cu FROM elevi, note fără WHERE de legătură) produce produsul cartezian — fiecare linie din primul tabel combinată cu fiecare linie din al doilea: 30 de elevi × 200 de note = 6000 de linii fără sens. Dacă rezultatul unei interogări are suspect de multe linii, prima verificare este condiția de join.

Join-urile se pot înlănțui pe mai mult de două tabele, iar la relațiile m:n trecerea se face prin tabelul de legătură: elevi JOIN inscrieri JOIN cursuri.

Test — 6 întrebări ca la examen