Esercizio - Normalizzazione di libri, autori e case editrici (esercizio di normalizzazione)

Testo (esercizio "biblioteca/libreria" del corso di Basi di Dati, Ing. Informatica UniPD). Sono date due relazioni.

ScriveLibro(ISBN, CF, nome, cognome, anno_inizio, anno_fine):

Libro(ISBN, titolo, generi, casa_editrice_PIVA, nome_casa_editrice, address_casa_editrice), dove generi contiene una lista:

Si chiede di portare lo schema in 1NF, 2NF e 3NF e di confrontare il risultato con la traduzione dallo schema ER. Teoria: Dipendenze funzionali e normalizzazione - BCNF e terza forma normaleLa qualità di uno schema relazionale si misura con quattro linee guida informali: semantica chiara degli attributi (una relazione per un'entità o associazione), niente ridondanza che causi anomalie di inserimento, cancellazione e modifica, pochi NULL sistematici, nessuna tupla spuria dopo un join (si unisce solo su chiavi primarie ed esterne). La normalizzazione verifica queste proprietà con le dipendenze funzionali X -> Y (se due tuple coincidono su X coincidono su Y), usando le regole di Armstrong (riflessiva, aumento, transitiva) e la chiusura X+ per trovare chiavi. 1NF: attributi atomici (si tolgono multivalore e relazioni annidate). 2NF: ogni attributo non primo dipende completamente da ogni chiave (niente dipendenze parziali). 3NF: nessun attributo non primo dipende transitivamente da una chiave; equivalentemente per ogni X -> A, X è superchiave oppure A è primo. BCNF: per ogni X -> A non banale X è superchiave. Si ottiene scomponendo senza perdere informazione (join senza perdita se l'intersezione determina una delle parti); la 3NF si ottiene sempre mantenendo le dipendenze, la BCNF a volte le perde. Un buon schema ER tradotto è già normalizzato.Dipendenze funzionali e normalizzazione - BCNF e terza forma normale →.

1. Dipendenze funzionali e chiavi

Dalla semantica (e dalle tabelle):

Per ScriveLibro la chiave è {ISBN, CF} (verificato con la chiusura: $\{\mathrm{ISBN},\mathrm{CF}\}^+$ contiene tutti gli attributi, e né ISBN né CF da soli bastano: $\mathrm{CF}^+=\{\mathrm{CF},\mathrm{nome},\mathrm{cognome}\}$). Per Libro la chiave è {ISBN}; ISBN determina anche PIVA e, per transitività, nome e indirizzo della casa.

Ridondanza visibile nelle tabelle. Nome e cognome di Luca Rossi sono scritti 3 volte (3 libri), in ScriveLibro; l'indirizzo di Edizioni Aurora 4 volte in Libro (4 libri). Modificare l'indirizzo della casa editrice richiede 4 aggiornamenti: anomalia di modifica. Eliminare l'unico libro di una casa editrice fa perdere la casa: anomalia di cancellazione.

2. Prima forma normale

Libro.generi contiene una lista: attributo multivalore, quindi Libro non è in 1NF. Strategia scelta dal corso (la migliore): tolgo l'attributo e creo due relazioni.

Libro(*ISBN, titolo, PIVA, nome_casa, address_casa)
HaGenere(*ISBN, *nome_genere)         FK ISBN -> Libro(ISBN); FK nome_genere -> Genere(nome_genere)
Genere(*nome_genere)

(Alternative scartate: chiave {ISBN, genere} su Libro, che ripete titolo e casa per ogni genere, aumenta la ridondanza; colonne Genere1, Genere2, ... che introducono NULL e rendono le interrogazioni difficili.) Con i dati dell'esempio: 15 generi distinti e 18 righe in HaGenere (2+2+1+2+2+2+1+2+2+2).

Perché HaGenere ha chiave composta ({ISBN, nome_genere}): ogni coppia libro-genere compare una volta, ed è una relazione molti-a-molti. ScriveLibro è già in 1NF (attributi atomici).

3. Seconda forma normale

Per le relazioni con chiave semplice la 2NF è automatica (Libro, Genere). Si controlla ScriveLibro, con chiave composta {ISBN, CF}:

• $\{\mathrm{ISBN},\mathrm{CF}\}\to\mathrm{anno\_inizio}$ è completa (non basta né ISBN né CF);
• $\{\mathrm{ISBN},\mathrm{CF}\}\to\{\mathrm{nome},\mathrm{cognome}\}$ è parziale, perché già $\mathrm{CF}\to\{\mathrm{nome},\mathrm{cognome}\}$.

Gli attributi non primi nome e cognome dipendono da una parte della chiave: ScriveLibro non è in 2NF. Si scompone: una relazione per la chiave parziale CF con i suoi dipendenti, e si tiene la relazione con la chiave originale e gli attributi completamente dipendenti.

Autore(*CF, nome, cognome)
ScriveLibro(*ISBN, *CF, anno_inizio, anno_fine)      FK ISBN -> Libro(ISBN); FK CF -> Autore(CF)

La scomposizione è senza perdita: l'intersezione {CF} è chiave di Autore. Controllato sui dati: ScriveLibro JOIN Autore ON CF restituisce esattamente le 10 righe originali (SQLite). Autore ha 7 righe: Luca Rossi non compare più 3 volte ma una.

4. Terza forma normale

Libro(ISBN, titolo, PIVA, nome_casa, address_casa) è in 2NF (la chiave è un solo attributo), ma c'è una dipendenza transitiva: $\mathrm{ISBN}\to\mathrm{PIVA}$ e $\mathrm{PIVA}\to\{\mathrm{nome\_casa},\mathrm{address\_casa}\}$, con PIVA che non è né chiave né parte di una chiave. Quindi $\mathrm{ISBN}\to\mathrm{nome\_casa}$ è transitiva e Libro non è in 3NF. Si scompone:

CasaEditrice(*PIVA, nome, address)
Libro(*ISBN, titolo, PIVA)           FK PIVA -> CasaEditrice(PIVA)

Anche qui il join è senza perdita (PIVA è chiave di CasaEditrice) e verificato: Libro JOIN CasaEditrice restituisce le 10 righe originali; CasaEditrice ha 3 righe invece di 10 copie.

5. Schema finale e controllo con BCNF

Autore(*CF, nome, cognome)
CasaEditrice(*PIVA, nome, address)
Libro(*ISBN, titolo, PIVA)                       FK PIVA -> CasaEditrice(PIVA)
ScriveLibro(*ISBN, *CF, anno_inizio, anno_fine)   FK ISBN -> Libro(ISBN); FK CF -> Autore(CF)
Genere(*nome_genere)
HaGenere(*ISBN, *nome_genere)                    FK ISBN -> Libro(ISBN); FK nome_genere -> Genere(nome_genere)

Controllo BCNF: in ogni relazione l'unica FD non banale ha come lato sinistro la chiave (CF in Autore, PIVA in CasaEditrice, ISBN in Libro, {ISBN, CF} in ScriveLibro): le sinistre sono tutte superchiavi, quindi lo schema è anche in BCNF. Tutte le FD originali sono conservate, ciascuna su una singola relazione.

6. Confronto con lo schema ER

Il corso mostra anche lo schema ER di partenza: Autore (CF, nome, cognome) $(0,N)$ — Scrive (anno inizio, anno fine) — $(1,N)$ Libro (ISBN, titolo, numero pagine); Libro $(0,N)$ — HaGenere — $(0,N)$ Genere (genere); Libro $(1,1)$ — Pubblica — $(0,N)$ CasaEditrice (P.IVA, nome, address). La traduzione (Ristrutturazione dello schema ER e traduzione nel modello relazionaleLa progettazione logica ha due passi. 1) Ristrutturazione dello schema ER, per togliere i costrutti che il modello relazionale non esprime: analisi delle ridondanze (attributi derivati e cicli di relazioni, decisa con volume dei dati e carico, tabelle di frequenza e accessi), eliminazione degli attributi multivalore (nuova entità), composti (appiattiti o nuova entità), delle IS-A (relazione con partecipazione (1,1) dalla sottoclasse) e delle generalizzazioni (opzione 1 relazioni, 2 accorpamento nel padre con attributo discriminante, 3 accorpamento nei figli, solo se completa e disgiunta), scelta degli identificatori principali (niente cicli di identificazione esterna), riscrittura dei vincoli esterni, partizionamento e accorpamento di entità. 2) Traduzione quasi automatica nel modello relazionale: ogni entità diventa una relazione con la chiave primaria data dall'identificatore principale (anche esterno); una relazione molti-a-molti diventa una relazione con le chiavi delle entità; una uno-a-molti con partecipazione obbligatoria si fonde con l'entità dal lato max 1 con una chiave esterna; con partecipazione opzionale si fonde (NULL) o si crea una relazione a parte; le uno-a-uno si fondono sul lato con partecipazione obbligatoria; le ternarie seguono la stessa logica con le chiavi di tutte le entità.Ristrutturazione dello schema ER e traduzione nel modello relazionale →) dà direttamente le stesse sei relazioni: Scrive e HaGenere sono molti-a-molti, Pubblica (1,1) fusa in Libro come chiave esterna casa_ed. Conclusione del corso: uno schema ER progettato bene produce relazioni già normalizzate; la normalizzazione serve soprattutto per controllare schemi scritti senza passare dall'ER. (Nota: l'attributo "numero pagine" compare nell'ER ma non nelle tabelle di partenza.)

Domande d'esame

Perché ScriveLibro non è in 2NF? Traccia. La chiave è {ISBN, CF} e nome e cognome dipendono dal solo CF (dipendenza parziale): ripetuti per ogni libro dell'autore. Si scompone in Autore(CF, nome, cognome) e ScriveLibro(ISBN, CF, anni).

Come si porta in 3NF la relazione Libro con PIVA, nome e indirizzo della casa? Traccia. ISBN → PIVA → (nome, indirizzo) è transitiva: nuova relazione CasaEditrice(PIVA, nome, indirizzo), Libro con PIVA come chiave esterna. Join senza perdita perché PIVA è chiave di CasaEditrice.