Basi di dati Progett o Logico per il Modello Relazionale (E. Baralis, Politecnico di Torino)

1. Basi di datiProgetto Logico per il Modello Relazionale(E. Baralis, Politecnico di Torino)

2. Trasformazione da schema E-R a modello relazionale Risultato: schema relazionale nella forma normale opportuna. SCHEMA E-R passo A: ristrutturazione A Schema E-R ristrutturato passo B: traduzione B Set di relazioni (modello relazionale)

3. Passo A: ristrutturazione dello schema E-R E’ una fase di riorganizzazione dello schema E-R sulla base del carico applicativo previsto. Il carico applicativo consiste di: 1) Volumi dei dati (memoria d’occupazione); 2) Caratteristiche delle operazioni (piu’ rilevanti).

4. Carico applicativo 1) Volumi dei dati: valutati come N*O, dove N è il numero di occorrenze di ogni entità e/o relazione; O è l’occupazione in bytes di ogni occorrenza. 2) Caratteristiche delle operazioni: - tipo (interattivo o batch); - frequenza (numero medio di operazioni in una certa unità di tempo); - volumi di dati coinvolti.

5. Indici di prestazioni • Lo scopo del passo di ristrutturazione è di ottimizzare i seguenti indici di prestazione: • costo di una operazione: valutato come numero di accessi ad occorrenze di entità e/o relazione visitati per rispondere a una operazione; • occupazione di memoria

6. stipendio età impiegato dipartimento (1,n) (0,1) afferenza N_progetti (0,n) data_afferenza telefono (1,n) nome partecipazione data_inizio (1,n) progetto nome budget data_consegna Esempio codice nome

7. Concetto Tipo Volume (n. occ) Impiegato entità 2000 Dipartimento entità 80 entità 500 Progetto Partecipazione relazione 6000 Afferenza relazione 1900 Tavola dei volumi • Dipende dalla cardinalità delle entità e dalla cardinalità media di partecipazione di una occorrenza di entità ad una relazione: • Ipotesi: se un impiegato partecipa in media a 3 progetti, la relazione partecipazione ha in media 2000*3 occorrenze; invece afferenza ha cardinalità di poco inferiore a impiegato

8. Operazione Tipo Frequenza Operazione 1 interattiva 50 volte al giorno Operazione 2 interattiva 100 volte al giorno batch 1 volta alla settimana Operazione 3 Tavola delle operazioni • Operazione 1: assegna un impiegato ad un progetto. • Operazione 2: trova tutti i dati di un impiegato (con i dati del suo dipartimento e l’elenco dei progetti ai quali lavora). • Operazione 3: trova tutti i dati di un dipartimento (con l’elenco dei suoi dipendenti).

9. Costo dell’Operazione 1:“assegna un impiegato ad un progetto” • Schema dell’operazione codice nome stipendio età impiegato N_progetti (0,n) +1 partecipazione data_inizio (1,n) progetto nome budget data_consegna

10. Tavola degli accessi Concetto Tipo N accessi Lettura/Scrittura • Costo:2 operazioni di scrittura * 50 volte al giorno Impiegato entità 1 S Partecipazione relazione 1 S

11. Passo A: ristrutturazione dello schema E-R E’ costituito da una sequenza di passi: 1) Analisi delle ridondanze; 2) Eliminazione delle gerarchie di generalizza-zione; 3) Partizionamento/accorpamento di entità; 4) Scelta degli identificatori.

12. Analisi delle ridondanze • Conviene mantenere l’attributo ridondante N_progetti in impiegato? • Costo in volumi di dati per l’attributo N_progetti: 2000 impiegati * 2 byte= 4Kbyte. • Costo in aggiornamento (Op.1): 1 accesso in scrittura per 50 volte al giorno. • Costo in lettura (Op.2): 1 accesso in lettura per 100 volte al giorno. • Se supponiamo che un’operazione di scrittura costi il doppio rispetto ad una di lettura si hanno 200 accessi al giorno.

13. Eliminazione attributo N_progetti • Alternativamente potrei contare il numero di occorrenze della relazione partecipazione che si riferiscono ad un certo impiegato. • Costo in volumi di dati: nullo. • Costo di mantenimento:nullo. • Costo di lettura:in media 3 operazioni di lettura per 100 volte al giorno: 300 accessi al giorno.

14. Eliminazione delle gerarchie di generalizzazione Vi sono alcune possibilità: 1) Collassamento delle sottoclassi nella superclasse: • gli attributi delle sottoclassi sono uniti a quelli dell’entità superiore • si aggiunge un attributo discriminante 2) Eliminazione della superclasse: • propagazione degli attributi della superclasse in tutte sottoclassi 3) Mantenimento di tutte le entità, correlate da relazioni che rappresentano la generalizzazione.

15. Esempio TUTORE GRADO(a.d.,(0,1)) TITOLO_TESI (0,1) STUDENTE C_FISC NOME CORSO_DI_STUDI STUDENTE C_FISC NOME CORSO_DI_ STUDI (p,e) LAUREANDO UNIVERSITARIO (0,1) TUTORE (1,1) (0,1) (0,1) TITOLO_ TESI SOCIO_ DI SOCIO_ DI HA_ RELATORE HA_ RELATORE (1,n) (1,n) (1,n) (1,n) FACOLTA` ASSOCIAZIONE_ STUDENTESCA FACOLTA` ASSOCIAZIONE_ STUDENTESCA

16. Scelta 1 Svantaggi: Incremento di occupazione di memoria (presenza di valori nulli per gli attributi non significativi). Vantaggi: Conviene quando le operazioni non fanno distinzione rispetto all’appartenenza di una occorrenza a una sottoclasse: numero minore di accessi perché le occorrenze di interesse sono tutte concentrate in una stessa entità.

17. Esempio PAGA_ CONTR C_FISC IMPIEGATO CONTRIBUTI (1,1) (0,1) NOME (0,1) DIRETTORE (t,e) MANAGER SEGRETARIO INGEGNERE (1,n) (0,m) NUM_SOTTOPOSTI SPECIALIZZAZIONE CAPACITA` USA (1,n) (0,n) WORD_PROCESSOR Rappresentazione di gerarchie di generalizzazione mediante sottoclassi

18. (0,1) CONTRIBUTI CONTR_2 (0,1) (0,1) CONTR_1 (0,1) NUM_SOTTOPOSTI (0,1) C_FISC C_FISC CONTR_3 MANAGER INGEGNERE SEGRETARIO C_FISC NOME NOME NOME (0,1) CONOSC (1,n) (0,m) DIRETT_1 SPECIALIZ (1,n) (1,1) DIRETT_3 (0,1) (1,n) USA DIRETT_2 (1,1) (1,n) (0,n) WORD_PROCESSOR

19. Scelta 2 Svantaggi: E’ possibile solo se la generalizzazione è totale, altrimenti le occorrenze della sopraclasse non sarebbero rappresentate. Occorre duplicare il numero di relazioni per ciascuna sottoclasse. Vantaggi: Conviene se le operazioni fanno accesso solo ad occorrenze di una o delle altre sottoclassi. Si risparmia in memoria perché si eliminano i valori nulli degli attributi. Si riducono gli accessi rispetto alla scelta 3 perché per accedere ad un’occorrenza di una sottoclasse non si deve passare per la sopraclasse.

20. Esempio NUM_PROG NOME_PROG HA PROGETTO MEMBRI_ PROGETTO (1,n) (1,m) BUDGET (p,o) SOTTOCONTRATTO PROGETTO_ SW PROGETTO_ HW NUM_ SCHEDE (1,n) CONTRAENTE_ PRINCIPALE MESI_UOMO USA (0,m) COMPONENTI_HW Rappresentazione di gerarchie di generalizzazione tramite relazioni

21. NUM_PROG NOME_PROG HA PROGETTO (1,n) MEMBRI_ PROGETTO (1,m) BUDGET (0,1) (0,1) (0,1) TIPO_HW TIPO_ SOTTOCONTR TIPO_SW (1,1) (1,1) (1,1) SOTTOCONTRATTO PROGETTO_ SW PROGETTO_ HW NUM_ SCHEDE (1,n) CONTRAENTE_ PRINCIPALE MESI_UOMO USA (0,m) COMPONENTI_HW

22. Scelta 3 Svantaggi Si incrementa il numero di accessi per le occorrenze delle sottoclassi. Se la generalizzazione è totale ed esclusiva, occorre aggiungere dei vincoli: una occorrenza della sopraclasse deve partecipare ad una ed una sola relazione con una delle sottoclassi. Vantaggi Conviene quando la generalizzazione non è totale, e le operazioni fanno accesso o ad occorrenze della sopraclasse, o di sottoclassi. Si risparmia memoria e ciò può aumentare il numero di dati che si recuperano con l’accesso ad un singolo buffer di disco.

23. Partizionamento di entità (e relazioni) • Il partizionamento d’entità (relazioni) corrisponde a separare i concetti che vengono acceduti da operazioni diverse. Si può decomporre: • verticalmente selezionando gli attributi (tramite proiezione). Ha il vantaggio che generano entità semplici, con pochi attributi. • orizzontalmente (selezionando le occorrenze sulla base dei valori degli attributi). Ciò può anche essere visto come l’introduzione di una nuova generalizzazione a livello logico. Ha lo svantaggio che si duplicano le relazioni per le due nuove entità.

24. Dati anagrafici Dati lavorativi livello cognome Dati_impiegato (1,1) (1,1) stipendio indirizzo ritenute Data_ nascita Esempio: partizionamento verticale di entità cognome livello impiegato indirizzo stipendio Data_nascita ritenute codice codice

25. Partizionamento di relazione Giocatore Squadra nome cognome composizione (1,n) (1,n) città ruolo Data_ acquisto Data_ cessione (0,1) Giocatore Squadra nome cognome Composizione attuale (1,1) (1,n) città ruolo Data_ acquisto Composizione precedente (1,n) (0,n) Data_acquisto Data_cessione

26. Accorpamento di entità • L’accorpamento d’entità corrisponde a raggruppare i concetti che vengono acceduti insieme dalle stesse operazioni. Ciò ad esempio permette di ridurre il numero di join tra entità. Uno svantaggio però consiste nel fatto che potrebbero essere introdotti attributi con valori nulli. Inoltre si introduce una certa ridondanza, il che è equivalente ad una operazione di de-normalizzazione. Perciò viene utilizzata dove sussistono relazioni 1:1 o 0:1 tra le entità da accorpare, e raramente se le relazioni sono 1:molti.

27. Esempio Persona appartamento indirizzo cognome intestazione (1,1) (0,1) interno indirizzo Data_ nascita CF Persona cognome Indirizzo (0,1) indirizzo Interno (0,1) Data_ nascita CF

28. Eliminazione di attributi composti Due possibilità: a) considerare l’attributo composto come insieme di attributi singoli Problema: si perde la nozione di collegamento tra gli attributi b) eliminare le componenti individuali considerando solo i valori aggregati Problema: il singolo attributo deve poi essere scisso dall’applicazione per individuare i valori separati

29. Esempio PERSONA COGNOME ETA` SESSO VIA INDIRIZZO CITTA` STATO (a) Schema con un attributo composto PERSONA COGNOME PERSONA COGNOME ETA` ETA` SESSO SESSO VIA INDIRIZZO CITTA` STATO (c) Attributo composto considerato come attributo singolo (b) Attributo composto ridotto nelle sue componenti

30. Eliminazione degli attributi multivalore dalle entità • Ogni attributo a molti valori è rappresentato da un'entità, in cui può essere rappresentato come attributo a singolo valore. • La nuova entità contiene l’attributo a molti valori più l’identificatore dell’entità di origine. • L’identificatore della nuova entità è l’insieme di tutti gli attributi.

31. Attributi multivalore di entità A R B A EAM E1 E1 B (1,n) A A E1 EAM B

32. Esempio CODICE_PRODOTTO PRODOTTO CODICE_MATERIALE (1,n) DESCRIZIONE PREZZO CODICE_PRODOTTO PRODOTTO DESCRIZIONE PREZZO PRODOTTO_MATERIALE CODICE_PRODOTTO CODICE_MATERIALE

33. Eliminazione degli attributi multivalore dalle relazioni • Attributo a molti valori appartiene alla relazione R tra le entità E1 e E2 • nuova entità NE che include 1 o 2 attributi presi da E1 o E2 (o entrambi) in funzione del tipo di relazione: • relazione 1 a 1: NE richiede 1 degli identificatori di E1 o E2 • relazione 1 a molti: NE comprende l’identificatore di E2 (E2 nel lato “molti”) • relazione molti a molti: NE comprende gli identificatori provenienti da E1 ed E2

34. Eliminazione degli attributi multivalore dalle relazioni • La chiave primaria di NE è costituita da: • tutti gli attributi di NE provenienti da E1 ed E2 • l’attributo multi valore • Gli attributi non multivalore di R rimangono a R.

35. Attributi a molti valori di relazioni C E1 C E1 R A (0,n) R A B (1,n) D E2 (0,m) D E2 B Attrib B C D

36. Esempio CF_INSEGNANTE INSEGNANTE DIPARTIMENTO TELEFONO (0,m) TIENE MAX_NUM_STUD SEMESTRE (1,n) (1,n) CORSO NUM_CORSO

37. eliminazione di attributo multivalore da una relazione tramite creazione di entità separate CF_INSEGNANTE INSEGNANTE DIPARTIMENTO TELEFONO (0,m) TIENE MAX_NUM_STUD (1,n) CORSO NUM_CORSO CF_INSEGNANTE OFFERTA_ CORSI NUM_CORSO SEMESTRE

38. Scelta degli identificatori • Un attributo che ammette valori nulli non può essere un identificatore. • La scelta tra due identificatori alternativi avviene in base alla semplicità. • Occorre considerare anche la presenza di eventuali identificatori esterni. • Conviene preferire gli identificatori che vengono utilizzati dal maggior numero di operazioni. • In alcuni casi, può convenire creare “ad arte” un codice che serva da identificatore. Questo potrebbe essere creato automaticamente dal DBMS o sarà l’applicazione a dover gestire la creazione e l’unicità del codice.

39. Eliminazione di identificatori esterni Gli identificatori esterni sono trasformati in identificatori interni. ID B E1 A E1 A (1,1) R (1,n) B E2 B E2

40. Esempio CODICE UNIVERSITÀ UNIVERSITÀ NOME CITTÀ ISCRIVE MATRICOLA_STUDENTE STUDENTE COGNOME ETÀ (a) Schema iniziale

41. Esempio CODICE UNIVERSITÀ UNIVERSITÀ NOME CITTÀ + CODICE UNIVERSITÀ STUDENTE MATRICOLA_STUDENTE COGNOME ETÀ (b) Schema finale

42. Eliminazione di identificatori esterni A E1 A E1 R1 ID1 A E2 E2 B B R2 A E3 B E3 C C ID2

43. Passo B:traduzione nel modello relazionale 1) Traduzione di ogni entità in una tabella 2) Traduzione delle relazioni: • le relazioni 1:1, 1:molti, molti:1 sono modellate aggiungendo attributi a tabelle esistenti • le relazioni molti:molti richiedono sempre la creazione di una nuova tabella

44. Traduzione di entità Entità = Tabella Esempio: CF IMPIEGATO NOME COGNOME STIPENDIO IMPIEGATO (CF, NOME, COGNOME, STIPENDIO)

45. E1 E2 R AR Riassunto • Associazione binaria molti a molti A11 • E1(A11,A12) • E2(A21,A22) • R(A11,A21,AR) A12 (X,n) (X,n) A21 A22

46. E1 E2 E3 R AR Riassunto • Associazione ternaria molti a molti A11 • E1(A11,A12) • E2(A21,A22) • E3(A31,A32) • R(A11,A21,A31,AR) A12 (X,n) (X,n) (X,n) A21 A31 A32 A22

47. E1 E2 R AR Riassunto • Associazione uno a molti con partecipazione obbligatoria A11 A12 • E1(A11,A12,A21,AR) • E2(A21,A22) (1,1) (X,n) A21 A22

48. E1 E2 R AR Riassunto • Associazione uno a molti con partecipazione opzionale A11 • E1(A11,A12,A21*,AR*) • E2(A21,A22) • Oppure • E1(A11,A12) • E2(A21,A22) • R(A11,A21,AR) A12 (0,1) (X,n) A21 A22

49. E1 E2 A12 R AR Riassunto • Associazione con identificatore esterno A11 • E1(A11,A21,A12,AR) • E2(A21,A22) (1,1) (X,Y) A21 A22

50. E1(A11,A12,A21,AR) • E2(A21,A22) • Oppure • E1(A11,A12) • E2(A21,A22,A11,AR) E1 E2 R AR Riassunto • Associazione uno a uno con partecipazione obbligatoria per entrambe le entita` A11 A12 (1,1) (1,1) A21 A22