Basi di dati - Appunti lezione ed esercitazioni PDF

Preview

Summary

26 Febbraio 2019 Database SISTEMA INFORMATIVO: si dice sistema informativo un sistema che permette di gestire i dati in modo strutturato. DATABASE: ci permette di strutturare i dati per evitare ridondanza e incongruenze. DBMS: DataBase Management System, serve per la gestione di un database. Lo sche...

Description

26 Febbraio 2019

Database SISTEMA INFORMATIVO: si dice sistema informativo un sistema che permette di gestire i dati in modo strutturato.! DATABASE: ci permette di strutturare i dati per evitare ridondanza e incongruenze.! DBMS: DataBase Management System, serve per la gestione di un database.! Lo schema di progettazione del DataBase è il Modello dei Dati.! Le fasi per quando riguarda la realizzazione di un DataBase sono:! 1. ANALISI - Raccolta dei requisiti! 2. PROGETTAZIONE CONCETTUALE - Modello ER (Entità - Relazione) ! 3. PROGETTAZIONE LOGICA - Si trasforma il Modello ER in una struttura dati (Modello relazionale)! 4. PROGETTAZIONE FISICA - Implementazione del DB tramite linguaggio SQL e scelta del DBMS (es. MySQL…)! 5. DATABASE!

Progettazione Concettuale

ENTITÀ: è un qualsiasi elemento reale e identificabile che possiede delle caratteristiche (dette attributi).!

"

1

26 Febbraio 2019 Gli ATTRIBUTI possono essere di vario tipo:! • ATOMICI - può assumere un solo valore non scindibile (es. nome, cognome…).!

• COMPOSTI - contengono più informazione all’interno dello stesso campo (es. indirizzo). Gli attributi data e ora, pur essendo composti, possono essere considerati atomici perché esiste un tipo che permette di gestirli.!

• DERIVATI - è possibile calcolarli mediante altri attributi (es. età, calcolabile da data di nascita). Si indicano con un cerchio tratteggiato oltre quello normale.!

• MULTIVALORE - che può avere più valori associato allo stesso campo (es. numero di telefono). Si indicano con due cerchi.!

• CHIAVE - attributi che rappresentano univocamente l’entità (es. matricola). Si rappresenta sottolineando il nome dell’attributo.!

In definitiva, considerando gli attributi come funzioni:! A : E -> P(V) U NULL"

• P - rappresenta i possibili vincoli che possono limitare i valori ammissibili del dominio • •

(es. V: numeri interi - P: max 50 min 0).! V - rappresenta tutti i valori associabili al dominio dell’attributo (es. matricola: valori numerici - nome: valori stringa).! NULL - rappresenta un valore non conosciuto, un valore vuoto. È diverso dallo zero.!

GRADO: numero di entità entità collegate con una singola relazione, il grado deve essere sempre pari a 2.! CARDINALITÀ: numero di istanze di ciascuna entità che partecipa alla relazione. Può essere:! • 1 a 1 (uno a uno)!

" " 2

26 Febbraio 2019

• 1 a N (uno a molti)!

• N a N oppure N a M (molti a molti)! !

N.B. La cardinalità può essere espressa anche come coppia di valori numerici che rappresentano il minimo e il massimo numero di istanze.! PARTECIPAZIONE:! • Totale: tutte le istanze sono collegate attraverso relazioni.! • Parziale: non tutte le istanze sono collegate attraverso relazioni.! ! %

X! X!

X!

X!

X!

X!

X

X

PERSONA

STUDENTE

3

26 Febbraio 2019 Ogni STUDENTE è anche PERSONA. Non tutte le PERSONE sono anche STUDENTI.! La partecipazione totale nel diagramma ER viene rappresentata con un tratto in grassetto dal lato della relazione che ha partecipazione totale (in questo caso dal lato di studente).! È possibile che una relazione abbia come chiave univoca le chiavi di altre entità. In questo si rappresenta un arco che collega le relazioni che offrono l’attributo.!

4

27 Febbraio 2019 ENTITÀ RICORSIVA: quando un’entità è collegata a se stessa mediante una relazione.! !

GERARCHIA IS-A: è utilizzate nel caso in cui ci siano delle entità che siano più specifiche di alcune già date, ovvero sono caratterizzazioni più dettagliate che aggiungono altre informazioni all’entità di partenza (entità padre).!

Nel caso della gerarchia IS-A si parla di PARTECIPAZIONE TOTALE nel caso in cui l’entità padre è sempre riconducibile ad un’entità figlia.! ESCLUSIVITÀ DELLA GERARCHIA: si parla di esclusività della gerarchia nel caso in cui l’entità padre può essere ricondotta ad almeno una delle entità figlie. Se non esiste esclusività si parla di GERARCHIA SOVRAPPOSTA.!

5

27 Febbraio 2019 TRACCIA D’ESAME - 2 Settembre 2015! Una base di dati deve essere utilizzata per gestire gli annunci pubblicati su un portale online. Ogni annuncio è identificato da un codice univoco di 10 caratteri, nome, descrizione, categoria (elettronica, immobili, veicoli, altro), data pubblicazione, data scadenza e utente che lo ha pubblicato. Ogni utente è caratterizzato da codice fiscale, nome, cognome e indirizzo e-mail. Per ogni annuncio è possibile inoltre indicare una serie keywords (parole chiave) identificate attraverso codice e nome. Gli annunci si suddividono inoltre in: vendite, si conosce il prezzo indicato e il comune in cui si trova l'oggetto; acquisti, è presente un attributo booleano che indica se l'acquisto dovrà avvenire esclusivamente con consegna di persona o meno. Ad ogni annuncio sono associate delle offerte di cui si conosce data, ora, importo, note eventuali e utente che ha inviato l'offerta. Per ogni offerta occorre verificare che la data sia compresa tra la data di pubblicazione e di scadenza del relativo annuncio e che l'offerta non provenga dallo stesso utente che ha pubblicato l'annuncio. Indicare le cardinalità delle relazioni e un identificatore per ciascuna entità. ! Modello ER!

BUSINESS RULES:! • codice Annuncio max 10 caratteri! • categoria: elettronica, veicoli, immobile, altro! • idPersona di tipo Boolean! Note:! • L’aggiunta di un nuovo attributo (es. in offerta, l’attributo ID come chiave univoca anziché utilizzare CFAnnuncio, data, ora) presenta alcuni problemi:! - spazio,! - vincoli (non può più ripetersi),! - range (valori che può assumere)." 6

27 Febbraio 2019

• L’entità “offerta” è detta ENTITÀ DEBOLE perché:! - i suoi attributi non bastano per definire una chiave univoca,! - non esiste se presa singolarmente, ma solo a seguito di “annuncio”. Quindi la sua esistenza è legata ad un altra entità.!

• Per la gestione della gerarchia (vendita e acquisto) ci sono tre metodi:! ' ' '

1. FIGLIE DEL PADRE L’entità padre eredita tutti gli attributi delle entità figlie, comprese le relazioni. ' ' Spesso si utilizza un ulteriore attributo “tipo” per indicare l’entità figlia specifica.! È preferibile utilizzarlo quando:! • ci sono poche figlie! • pochi attributi delle figlie'! • le figlie hanno poche/non hanno relazioni."

'

PRO! - Risparmio di memoria, dato che evita la creazione di nuove tabelle! - Velocità, essendo tutte le informazioni presenti in un’unica tabella!

'

CONTRO! - Non tutti gli attributi servono contemporaneamente, quindi andranno aggiunte delle Business Rules" BUSINESS RULES:! …"

- TIPO = vendita/acquisto" - (TIPO = vendita AND idPers IS NULL AND prezzo, comune IS NOT NULL) OR (TIPO = acquisto AND idPers IS NOT NULL AND prezzo, comune IS NULL)

7

1 Marzo 2019 ' ' ' '

' ' ' ' ' '

' '

' '

2. PADRE NELLE FIGLIE Tutti gli attributi e relazioni del padre vengono ereditati da ciascuna delle figlie.! Si utilizza soprattutto quando il padre ha poche relazioni.! ' ! CONTRO! - Complessità della realizzazione! - Espansione di n×m, dove n sono le relazioni delle figlie e m sono le relazioni del padre.!

3. FIGLIE DEBOLI RISPETTO AL PADRE Le entità figlie restano identiche nel modello ER. La gerarchia si trasforma in ' ' relazioni 1:1 tra padre e figlie. ! Le figlie diventano deboli rispetto al padre, quindi senza chiavi parziali, che si ' ' rappresentano con una linea con cerchio che taglia la relazione dal lato debole ' ' (figlie).! Si usa quando:! - ci sono molte figlie! - ogni figlia ha tanti attributi! - le figlie hanno tante relazioni! ! PRO! - Facilità di implementazione! - Nessuna Business Rule da aggiungere! ! CONTRO - Più tabelle da inserire! - Bisogna fare un’operazione di JOIN per interagire con le figlie" " " " 8

1 Marzo 2019 " '

!

TRACCIA D’ESAME - 12 Novembre 2015! Una base di dati deve essere utilizzata per gestire un sito di car sharing. Occorre memorizzare i dati riferiti a ciascun utente di cui si conosce codice fiscale, nome, cognome e e-mail. Si conoscono inoltre le informazioni delle auto utilizzate durante i viaggi, identificate da targa, modello, cilindrata, km percorsi e utente proprietario. Per ogni viaggio invece occorre memorizzare data e ora di partenza, auto utilizzata, numero massimo di persone, costo del viaggio, comune di partenza e di arrivo (per ciascun comune si conosce CAP e nome) e lista di utenti passeggeri. Ogni passeggero indicherà anche un voto e una recensione del viaggio. Verificare inoltre che per ciascun viaggio il numero di passeggeri non sia superiore rispetto al numero massimo indicato. Un viaggio può essere di tipo: ! - diretto (senza soste intermedie), di cui si conosce la lunghezza complessiva in km;" - con soste, di cui si conosce la lista dei comuni in cui verrà effettuata una sosta e la relativa durata. Indicare le cardinalità delle relazioni e un identificatore per ciascuna entità." MODELLO ER %

9

1 Marzo 2019 BUSINESS RULES:! • Voto: valori da 1 a 5! • maxPersone >= numero utenti per viaggio! • Tipo: diretto/con soste! • (tipo = diretto AND km IS NOT NULL) OR (tipo = con soste AND km IS NULL)! • La relazione “fa” tra Viaggio e Comune esiste solo per viaggio con soste!

Modello Logico Si parla di RDBMS (DBMS Relazionale).! RELAZIONE: rappresenta una tabella nell’RDBMS! R(A1, A2, … , An) ! INTENSIONE: lista ordinata di attributi della relazione.! Per ciascun attributo è definito un DOMINIO:! D(A1) = valori che può assumere A1 U NULL! ISTANZA: di una generica relazione R è l’insieme non ordinato delle tuple appartenenti alla relazione! r(R) = (t1, t2, … , tn)! TUPLA: insieme ordinato di valori associati a ciascun attributo della relazione (riga della tabella)! tn = ! La chiave univoca va sottolineata anche nel modello logico.! SUPERCHIAVE (SK): l’insieme di attributi che rispettano la relazione ! ti [SK] ≠ tj [SK] ' ∀ i, j! ovvero il valore degli attributi che rappresentano la superchiave deve essere sempre diverso per ogni tupla. ! Es. per la tabella UTENTE! SK1 = (CF, nome) = Chiave Candidata! SK2 = (CF, cognome) = Chiave Candidata! SK3 = (CF) = Chiave Primaria PK! La chiave quindi rappresenta il numero di attributi minimo per rispettare la condizione della superchiave.! La chiave univoca scelta è detta Primary Key (PK).! Tutte le altre possibili combinazioni di chiavi scartate sono dette Chiavi Candidate.! GRADO: di una relazione è il numero di attributi che questa possiede.! CARDINALITÀ: di una relazione è il numero di tuple (righe) che questa possiede.! 10

1 Marzo 2019 Modello logico - Traccia del 12 Novembre 2015! UTENTE(CF, nome, cognome, email)! AUTO(targa, modello, cilindrata, km, CFUtente)! N.B: per le relazioni 1:N (dal lato N) si aggiunge un attributo chiave per collegare le due tabelle.! Questo tipo di attributo è detto Chiave Esterna FK (Foreign Key)! FK = UTENTE(PK) U NULL! La FK deve contenere la PK della tabella a cui è collegata (vincolo di integrità referenziale). È possibile inserire il valore NULL solo quando la partecipazione non è totale.! COMUNE(CAP, nome)! VIAGGIO(data, ora, maxPersone, costo, km, tipo, capPartenza, capArrivo)! N.B: per le relazioni N:N si crea un’ulteriore tabella con le chiavi primarie delle due tabelle collegate e gli attributi della relazione.! UTENTE_VIAGGIO(CFUtente, targaAutoViaggio, dataViaggio, oraViaggio, voto, desc)! COMUNE_VIAGGIO(targaAutoViaggio, dataViaggio, oraViaggio, capSosta, durata)! N.B: per le relazioni 1:1, la chiave primaria della entità con partecipazione parziale si trasporta nella tabella con partecipazione totale.%

11

6 Marzo 2019 TRACCIA D’ESAME - 12 Febbraio 2019! Una base di dati deve memorizzare le informazioni relative allo sviluppo di progetti software. Ogni progetto è identificato attraverso un codice univoco, nome e descrizione. Per ciascuno progetto si conosce inoltre la lista di sviluppatori che ci lavora identificati da un username univoco, password, e-mail e un attributo booleano che indica se lo sviluppatore possiede i permessi di amministratore per il particolare progetto. Ogni progetto prevede differenti versioni caratterizzate da un numero progressivo univoco solo rispetto al progetto e data di rilascio. Ciascuna versione prevede inoltre una serie di attività, aventi un codice univoco solo rispetto alla versione, descrizione, data di creazione e sviluppatore di riferimento. Le attività si dividono in: (i) segnalazione bug, si conosce lo stato (risolto, non risolto) e la priorità (alta, media, bassa); (ii) inserimento di nuove funzionalità, si conosce la data di completamento e la lista di bug risolti. Per ogni attività, verificare che lo sviluppatore indicato si presente tra quelli associati al relativo progetto. Indicare le business rules, le cardinalità delle relazioni e un identificatore per ciascuna entità.! Modello ER!

BUSINESS RULES:! • admin booleano! • stato bug = risolto/non risolto! • priorità = alta/media/bassa! • sviluppatore attività deve essere tra sviluppatore progetto! Per al risoluzione della gerarchia BUG/NUOVA FUNZIONE conviene lasciare deboli le entità figlie avendo una relazione che le collega tra di loro.!

12

6 Marzo 2019 Modello logico! PROGETTO(cod, nome, desc)! SVILUPPATORE(user, psw, email)! PROGETTO_SVILUPPATORE(codProgetto, userSvil, admin)! VERSIONE(codProg, num, dataRilascio)! ATTIVITÀ(codProgetto, numVersione, cod, desc, dataCreazione, userSvil)! BUG(codProgetto, numVersione, codAttivita, stato, priorità)! NUOVAFUNZIONE(codProgetto, numVersione, codAttività, dataCompl)! BUG_FUNZIONE(codProgettoBug, numVerBug, codAttrBug, codProgFun, numVerFun, codAttrFun)!

13

12 Marzo 2019 ALGEBRA RELAZIONALE: sono operazioni che operano su una/due relazioni e producono una nuova relazione (algebra chiusa)." È di tipo procedurale, cioè le operazioni sono eseguite in ordine.! O(R) → R’! O2(O1(R)) → R’! Gli operatori si dividono in UNARI, ovvero che ricevono in input un’unica relazione, e BINARI, ovvero che ricevono due relazioni in input.! OPERATORI UNARI:! • SELEZIONE σ (sigma)" Serve per filtrare le tuple di una relazione di partenza mediante una condizione! σ(R) → R’! σ(STUDENTE) → R’! " grado(R) = grado(R’)! cardinalità(R) ≥ cardinalità(R’)! Gode della proprietà commutativa:" σ(σ(R)) = σ(σ(R))!

• PROIEZIONE π (pi)" Serve per filtrare gli attributi della relazione di partenza mediante una condizione! π (R) → R’! π (STUDENTE) → R’! ! grado(R) ≥ grado(R’)! cardinalità(R) ≥ cardinalità(R’) " perché le tuple non sono prese se hanno NULL all’attributo preso in considerazione.! Non gode della proprietà commutativa:" π (σ (R)) ≠ σ (π (R))!

• RIDENOMINAZIONE ρ (ro)" Serve per ridenominare gli attributi nella relazione di output" ρ (R) → R’! ρ (STUDENTE) → R’! ! grado(R) = grado(R’)! cardinalità(R) = cardinalità(R’)! 14

12 Marzo 2019 OPERATORI BINARI:! • INSIEMISTICI, per utilizzare gli operatori insiemistici è necessario che le relazioni siano UNION-COMPATIBLE, ovvero che abbiano lo stesso schema (stesso numero di attributi, anche con nome diverso, e ogni coppia di attributi delle due relazioni appartiene allo stesso dominio).! • JOIN.! STUDENTE! MATRICOLA

NOME

COGNOME

ABC

Mario

Rossi

DEF

Fabio

Verdi

GHI

Mario

Bianchi

MATRICOLA

NOME

COGNOME

ABC

Mario

Rossi

XYZ

Fabio

Neri

LAUREATO!

OPERATORI INSIEMISTICI:! • UNIONE R U S → R’" Unisce le tuple delle due relazioni, le tuple uguali verranno inserite una sola volta." grado(R) = grado(S) = grado(R’)" cardinalità(R) + cardinalità(S) ≥ cardinalità(R’)" " STUDENTE U LAUREATO" MATRICOLA

NOME

COGNOME

ABC

Mario

Rossi

DEF

Fabio

Verdi

GHI

Mario

Bianchi

XYZ

Fabio

Neri

" " " " " " "

15

12 Marzo 2019

• INTERSEZIONE R ∩ S → R’" Prende solo le tuple comuni alle due relazioni." grado(R) = grado(S) = grado(R’)" cardinalità(R’) ≤ min(cardinalità(R), cardinalità(S))" " STUDENTE ∩ LAUREATO" MATRICOLA

NOME

COGNOME

ABC

Mario

Rossi

"

• SOTTRAZIONE R - S → R’" Toglie le tuple della prima relazione contenute anche nella seconda relazione." grado(R) = grado(S) = grado(R’)" cardinalità(R) ≤ cardinalità(R’)" " STUDENTE - LAUREATO" MATRICOLA

NOME

COGNOME

DEF

Fabio

Verdi

GHI

Mario

Bianchi

XYZ

Fabio

Neri

"

• PRODOTTO CARTESIANO R × S → R’" Le relazioni non devono essere per forza compatibili. Unisce gli attributi delle tabelle di partenza." grado(R’) = grado(R) + grado(S)" cardinalità(R’) = cardinalità(R) × cardinalità(S)" " STUDENTE" MATRICOLA

NOME

COGNOME

ABC

Mario

Rossi

DEF

Fabio

Verdi

GHI

Mario

Bianchi

" " " " " " 16

12 Marzo 2019 " CORSO DI LAUREA" COD

NOME 1 Ing. Inf. 2 Ing. Ges.

" STUDENTE × CDL" MATRICOLA

NOME

COGNOME

COD

NOME

ABC

Mario

Rossi

1 Ing. Inf.

ABC

Mario

Rossi

2 Ing. Ges.

DEF

Fabio

Verdi

1 Ing. Inf.

DEF

Fabio

Verdi

2 Ing. Ges.

GHI

Mario

Bianchi

1 Ing. Inf.

GHI

Mario

Bianchi

2 Ing. Ges.

XYZ

Fabio

Neri

1 Ing. Inf.

XYZ

Fabio

Neri

2 Ing. Ges.

"

• JOIN R ⋈ S → R’" Simile al prodotto, collega le tuple sulla base di una condizione." " Il Join si dice completo se tutte le tuple della relazione di partenza vengono utilizzare per creare almeno una tupla della tabella prodotta dal Join." " EQUI-JOIN: la condizione è un’uguaglianza tra chiave primaria e chiave esterna delle due relazioni" THETA-JOIN: la condizione è una qualsiasi non uguaglianza." " LEFT-JOIN R ⋈ S LEFT → R’" Prima viene applicato il Join normale, poi mostra tutte le tuple della prima relazione che hanno FK pari a NULL. Anche tutti gli altri attributi non collegabili avranno NULL come valore." " RIGHT-JOIN R ⋈ S RIGHT → R’" Prima viene applicato il Join normale, poi mostra tutte le tuple della seconda relazione che hanno FK pari a NULL. Anche tutti gli altri attributi non collegabili avranno NULL come valore." L’unione LEFT-JOIN U RIGHT-JOIN è detta FULL-JOIN."

17

13 Marzo 2019

• NATURAL JOIN: richiede che chiave primaria e chiave esterna abbiano lo stesso nome, in modo tale che non sia necessaria alcuna condizione. Se ci sono altri attributi che hanno lo stesso modo è necessario che abbiano domini diversi per applicare il Natural Join. " Verrà inserita solo una volta la riga contenente chiave primaria/esterna." " STUDENTE" MATRICOLA

NOME

COGNOME

CdL

ABC

Mario

Rossi

1

DEF

Fabio

Verdi

2

GHI