Invatare Atomica

Proiect integrat: OOP + baza de date relationala SQL

Progres lectie:
0%
🎯

Obiectivul lectiei

Vei construi o aplicatie completa de catalog scolar care combina clasele Python (OOP) cu o baza de date relationala SQLite. Vei folosi toate cele patru operatii fundamentale SQL — SELECT, INSERT, UPDATE, DELETE — plus JOIN intre doua tabele, intr-un proiect real reutilizabil.

Dupa aceasta lectie vei putea:

  • Sa proiectezi schema unei baze de date relationale pentru un catalog scolar
  • Sa scrii o clasa Python care incapsuleaza conexiunea si operatiile SQL
  • Sa executi SELECT cu WHERE, ORDER BY si functii agregate (AVG, COUNT)
  • Sa modifici date cu UPDATE si sa stergi selectiv cu DELETE
  • Sa interogezi doua tabele legate prin cheie straina cu JOIN
  • Sa normalizezi datele evitand redundanta (1NF, 2NF) EXCLUSIV INTENSIV

Incearca singur!

Provocare:

Inainte sa citesti lectia, gandeste-te: un catalog scolar are studenti si note la mai multe materii. Cum ai proiecta tabelele? Ce coloane ar fi in fiecare? Ce ar lega cele doua tabele?

💡 Ai nevoie de un indiciu?

Gandeste-te asa: studenti (id, nume, clasa) si note (id, student_id, materie, nota). Campul student_id din tabelul note este cheia straina care refera id din studenti. Asa eviti sa reperti numele studentului in fiecare rand de nota.

1

1. Schema bazei de date — doua tabele relationale

Un catalog scolar bine proiectat separa entitatile: studentii exista independent de note. Relatia se exprima printr-o cheie straina (FOREIGN KEY).
  • studenti — id, nume, clasa (date despre persoana)
  • note — id, student_id, materie, nota (date despre evaluare)
  • note.student_id refera studenti.id — evita redundanta numelui
Analogie: fisier Excel cu doua foi legate

Foaia Studenti are un numar unic per rand (ID). Foaia Note contine acel numar in loc sa repete numele. Daca studentul isi schimba numele, il schimbi o singura data in foaia Studenti — notele raman corecte automat. Aceasta este esenta normalizarii.

import sqlite3

conn = sqlite3.connect('catalog.db')
cur = conn.cursor()

cur.execute('''CREATE TABLE IF NOT EXISTS studenti (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    nume TEXT    NOT NULL,
    clasa TEXT   NOT NULL
)''')

cur.execute('''CREATE TABLE IF NOT EXISTS note (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    materie    TEXT    NOT NULL,
    nota       REAL    NOT NULL,
    FOREIGN KEY(student_id) REFERENCES studenti(id)
)''')
conn.commit()
AUTOINCREMENT — SQLite asigneaza automat un ID unic la fiecare INSERT. NOT NULL — campul este obligatoriu; un INSERT fara aceasta coloana va esua cu eroare.
2

2. INSERT si SELECT — adaugare si interogare

INSERT adauga randuri noi. SELECT le recupereaza cu filtre si ordonare. Folosim parametri legati (?) ca sa prevenim SQL injection.
# INSERT cu executemany — eficient pentru date in lot
studenti = [
    ('Ana Ionescu', 'XII-A'),
    ('Mihai Pop',   'XII-A'),
    ('Elena Radu',  'XII-B'),
    ('Bogdan Stan', 'XII-B'),
]
cur.executemany(
    'INSERT INTO studenti (nume, clasa) VALUES (?, ?)',
    studenti
)
conn.commit()

# SELECT cu WHERE si ORDER BY
print('--- Studenti din XII-A ---')
for row in cur.execute(
        'SELECT id, nume FROM studenti WHERE clasa = ? ORDER BY nume',
        ('XII-A',)):
    print(row)
Output real (rulat cu python):
--- Studenti din XII-A ---
(1, 'Ana Ionescu')
(2, 'Mihai Pop')
SQL injection: Niciodata nu concatena string-uri in interogari ("WHERE nume = '" + x + "'"). Foloseste intotdeauna parametri ? — SQLite le escapeaza automat.
3

3. UPDATE si DELETE — modificare si stergere selectiva

UPDATE modifica valori existente. DELETE sterge randuri. Ambele fara WHERE afecteaza INTREGUL tabel — cel mai comun accident SQL.
# INSERT note pentru studenti
note = [
    (1, 'Informatica', 9.5),
    (2, 'Informatica', 4.8),
    (3, 'Informatica', 7.2),
    (4, 'Informatica', 8.0),
]
cur.executemany(
    'INSERT INTO note (student_id, materie, nota) VALUES (?, ?, ?)',
    note
)
conn.commit()

# UPDATE — corecteaza nota unui student
cur.execute(
    'UPDATE note SET nota = ? WHERE student_id = ? AND materie = ?',
    (5.5, 2, 'Informatica')
)
conn.commit()
row = cur.execute(
    'SELECT nota FROM note WHERE student_id = 2'
).fetchone()
print(f'Dupa UPDATE: nota Mihai Pop = {row[0]}')

# DELETE — sterge notele sub 5
cur.execute('DELETE FROM note WHERE nota < 5')
conn.commit()
cnt = cur.execute('SELECT COUNT(*) FROM note').fetchone()[0]
print(f'Note ramase dupa DELETE(nota<5): {cnt}')
Output real (rulat cu python):
Dupa UPDATE: nota Mihai Pop = 5.5
Note ramase dupa DELETE(nota<5): 3
4

4. JOIN — interogarea datelor din doua tabele

JOIN combina randuri din doua tabele pe baza unei conditii de legatura. INNER JOIN (sau simplu JOIN) returneaza doar randurile cu corespondent in ambele tabele.
# INNER JOIN — studenti cu notele lor la Informatica
print('--- Raport note Informatica ---')
for row in cur.execute('''
    SELECT s.nume, s.clasa, n.nota
    FROM   studenti s
    JOIN   note n ON s.id = n.student_id
    WHERE  n.materie = 'Informatica'
    ORDER  BY n.nota DESC
'''):
    print(row)

# Functii agregate: AVG si COUNT
avg, cnt = cur.execute('''
    SELECT AVG(n.nota), COUNT(*)
    FROM   studenti s
    JOIN   note n ON s.id = n.student_id
    WHERE  n.materie = 'Informatica'
''').fetchone()
print(f'Media Informatica: {avg:.2f}  (din {cnt} note)')
Output real (rulat cu python):
--- Raport note Informatica ---
('Ana Ionescu', 'XII-A', 9.5)
('Elena Radu', 'XII-B', 7.2)
('Mihai Pop', 'XII-A', 5.5)
Media Informatica: 7.40  (din 3 note)
Alias (s, n) — prescurtari pentru nume de tabele in interogari lungi. AVG() si COUNT() sunt functii agregate: calculeaza o singura valoare din mai multe randuri.
5

5. Aplicatia completa — clasa CatalogDB

Incapsulam toate operatiile intr-o clasa Python. Metodele ascund detaliile SQL — codul apelant lucreaza cu obiecte si date, nu cu interogari brute.
import sqlite3, os

class CatalogDB:
    def __init__(self, db_path):
        self.conn = sqlite3.connect(db_path)
        self.conn.row_factory = sqlite3.Row  # acces prin nume coloana
        self._creare_tabele()

    def _creare_tabele(self):
        self.conn.executescript('''
            CREATE TABLE IF NOT EXISTS studenti (
                id    INTEGER PRIMARY KEY AUTOINCREMENT,
                nume  TEXT NOT NULL,
                clasa TEXT NOT NULL
            );
            CREATE TABLE IF NOT EXISTS note (
                id         INTEGER PRIMARY KEY AUTOINCREMENT,
                student_id INTEGER NOT NULL,
                materie    TEXT    NOT NULL,
                nota       REAL    NOT NULL,
                FOREIGN KEY(student_id) REFERENCES studenti(id)
            );
        ''')

    def adauga_student(self, nume, clasa):
        cur = self.conn.execute(
            'INSERT INTO studenti (nume, clasa) VALUES (?, ?)', (nume, clasa))
        self.conn.commit()
        return cur.lastrowid  # ID-ul generat

    def adauga_nota(self, student_id, materie, nota):
        self.conn.execute(
            'INSERT INTO note (student_id, materie, nota) VALUES (?, ?, ?)',
            (student_id, materie, nota))
        self.conn.commit()

    def raport(self, materie):
        return self.conn.execute('''
            SELECT s.nume, s.clasa, n.nota
            FROM   studenti s JOIN note n ON s.id = n.student_id
            WHERE  n.materie = ?
            ORDER  BY n.nota DESC
        ''', (materie,)).fetchall()

    def media(self, materie):
        row = self.conn.execute(
            'SELECT AVG(nota), COUNT(*) FROM note WHERE materie = ?',
            (materie,)).fetchone()
        return row[0], row[1]

    def inchide(self):
        self.conn.close()

# === MAIN ===
DB = 'catalog.db'
cat = CatalogDB(DB)

id_ana    = cat.adauga_student('Ana Ionescu',  'XII-A')
id_mihai  = cat.adauga_student('Mihai Pop',    'XII-A')
id_elena  = cat.adauga_student('Elena Radu',   'XII-B')
id_bogdan = cat.adauga_student('Bogdan Stan',  'XII-B')

for sid, nota in [(id_ana, 9.5), (id_mihai, 5.5),
                    (id_elena, 7.2), (id_bogdan, 8.0)]:
    cat.adauga_nota(sid, 'Informatica', nota)

print('=== RAPORT INFORMATICA ===')
for r in cat.raport('Informatica'):
    status = 'PROMOVAT' if r[2] >= 5 else 'RESPINS'
    print(f'  {r[0]:<20} {r[1]:<6} nota={r[2]:.1f}  [{status}]')

avg, cnt = cat.media('Informatica')
print(f'Media: {avg:.2f}  (din {cnt} note)')
cat.inchide()
os.remove(DB)
Output real (rulat cu python):
=== RAPORT INFORMATICA ===
  Ana Ionescu          XII-A  nota=9.5  [PROMOVAT]
  Bogdan Stan          XII-B  nota=8.0  [PROMOVAT]
  Elena Radu           XII-B  nota=7.2  [PROMOVAT]
  Mihai Pop            XII-A  nota=5.5  [PROMOVAT]
Media: 7.55  (din 4 note)
6

6. Normalizare si principii de proiectare EXCLUSIV INTENSIV

⚡ Sectiune pentru intensiv informatica

Normalizarea reduce redundanta si anomaliile de actualizare. Primele doua forme normale (1NF, 2NF) sunt suficiente pentru proiecte scolare.

Anti-pattern vs. schema normalizata
Anti-pattern (un singur tabel)Normalizat (doua tabele)
Ana Ionescu, XII-A, Informatica, 9.5studenti(1, Ana Ionescu, XII-A)
Ana Ionescu, XII-A, Matematica, 8.0note(1, 1, Informatica, 9.5)
Ana Ionescu, XII-A, Romana, 7.5note(2, 1, Matematica, 8.0)
Numele si clasa repetate de 3 ori!note(3, 1, Romana, 7.5)
Probleme rezolvate prin normalizare:
  • Anomalie de actualizare: in anti-pattern, schimbarea clasei lui Ana necesita UPDATE pe toate randurile ei
  • Anomalie de stergere: stergand toate notele unui student, pierzi si datele despre student
  • 1NF: fiecare coloana contine o singura valoare atomica (nu liste)
  • 2NF: fiecare coloana non-cheie depinde de INTREAGA cheie primara (nu partial)
Principii retinute din acest proiect:
  • Separarea datelor: entitati distincte = tabele distincte
  • Integritate referentiala: FOREIGN KEY garanteaza ca student_id exista intotdeauna in studenti
  • Parametri legati (?): previn SQL injection, intotdeauna
  • Incapsulare OOP: clasa CatalogDB ascunde SQL-ul — codul client nu stie cum sunt stocate datele

Exercitii practice

Exercitiul 1 (Nivel minim) — Creare si interogare

Creeaza o baza de date SQLite biblioteca.db cu un singur tabel carti (id, titlu, autor, an). Insereaza 3 carti si scrie un SELECT care returneaza cartile ordonate descrescator dupa an.

Rezultat asteptat: 3 randuri, cel mai recent an primul.

Exercitiul 2 (Nivel standard) — UPDATE si DELETE

Pornind de la baza de date biblioteca.db creata anterior:

  • Actualizeaza anul uneia dintre carti cu UPDATE
  • Sterge toate cartile publicate inainte de 2000 cu DELETE WHERE
  • Afiseaza numarul de carti ramase cu COUNT(*)

Verifica ca UPDATE si DELETE au clauza WHERE corecta inainte de executie.

Exercitiul 3 (Nivel performanta) INTENSIV — Schema cu doua tabele si JOIN

Extinde biblioteca.db cu un tabel imprumuturi (id, carte_id, cititor, data_imprumut). Adauga 4 imprumuturi pentru 3 carti diferite. Scrie un JOIN care afiseaza titlul cartii si cititorul pentru fiecare imprumut, ordonat dupa data. Identifica care carte a fost imprumutata de mai multe ori folosind GROUP BY si COUNT.

Ce ai invatat astazi

  • Schema relationala: doua tabele legate prin cheie straina (studenti ↔ note)
  • INSERT cu parametri legati (?) — prevenire SQL injection
  • SELECT cu WHERE, ORDER BY si functii agregate AVG, COUNT
  • UPDATE si DELETE selectiv — mereu cu WHERE
  • INNER JOIN: combina randuri din doua tabele pe baza cheii straine
  • Clasa CatalogDB: incapsulare OOP peste operatii SQL — separarea responsabilitatilor
  • Normalizare 1NF/2NF: eliminarea redundantei si a anomaliilor INTENSIV

Felicitari! Ai finalizat Modulul M1.

Ai parcurs baze de date relationale, SQL complet (SELECT/INSERT/UPDATE/DELETE/JOIN), normalizare si integrarea OOP cu SQLite. Recapituleaza prin exercitiile de mai sus sau continua cu modulul urmator.

Inapoi la modul →