Skip to content

basi di dati

Un meta esercizio: descrivere il modello ER tramite uno schema ER

Il modello ER mette a disposizione dei costrutti logici con cui rappresentare una realtà d’interesse. Per procedere alla descrizione del modello ER è necessario innanzitutto determinare quali sono questi costrutti: essi saranno poi rappresentati nel nostro schema come delle entità.


Lo studio del modello ER ci porta a concludere che i costrutti impiegati sono:

entità, relazioni, attributi e generalizzazioni.

Procedendo con l’analisi, bisogna stabilire quali sono gli attributi che caratterizzano tali costrutti e quali sono le relazioni (anche dette associazioni) che legano fra loro i costrutti appena definiti.
Può esserci d’aiuto stilare un breve elenco (una descrizione esaustiva coprirebbe un capitolo di un libro), concentrandoci solo su gli aspetti fondamentali dei costrutti:

  • Entità: un’entità ha un nome che la identifica, può possedere attributi, può essere semplice o debole, può partecipare in alcune relazioni.
  • Relazione: una relazione ha un nome che la identifica, può avere degli attributi, le entità partecipano ad una relazione, può essere parziale o totale.
  • Attributo: un attributo ha un nome, ma questo non lo identifica univocamente, potendo esistere attributi con lo stesso nome su entità differenti. Un attributo appartiene ad un’entità o ad una relazione. Può essere composto da altri attributi. Può essere normale o multivalore.
  • Generalizzazione: una generalizzazione lega due o più entità che partecipano ad una relazione di tipo padre/figlio (“classe / classe derivata” diremmo nella terminologia ad oggetti). Non ha un nome identificativo, ma è identificata dalle entità che vi partecipano. Può essere totale o parziale, disgiunta o sovrapposta.

A questo punto bisogna tradurre il modello appena descritto in uno schema ER. Per far ciò si dovranno utilizzare costrutti ER per descrivere i costrutti ER appena definiti. Questo significa che si userà un linguaggio per descrivere se stesso (cioè linguaggio e metalinguaggio verranno a coincidere) bisognerà fare molta attenzione a non confondere i concetti e a non creare contraddizioni. Per ridurre le ambiguità, raccoglieremo tra doppi apici “” gli elementi che sono oggetto di descrizione.

Dall’analisi appena fatta, nel nostro schema del modello ER (ed approfondendo un po’ l’analisi) ci accorgiamo che il nostro schema dovrà rappresentare le seguenti relazioni:

  • Proprietà: è la relazione che lega un entità “attributo” ad un’entità “entità” o “relazione”.
  • Partecipazione: è la relazione che lega un’entità “entità” ad un’entità “relazione”. Ha un attributo cardinalità esprimibile come una coppia di numeri interi (min,max).
  • Composizione: è la relazione per cui un “attributo” può essere formato da altri “attributi”.

“Entità” e “Relazione”

Possiamo notare che le entità “entità” e “relazione”, hanno alcune caratteristiche in comune: entrambe sono identificate dall’attributo “nome” ed entrambe partecipano alla relazione “proprietà” con l’entità “attributo”. Questo suggerisce l’idea che “entità” e “relazione” siano concetti più dettagliati di un concetto più generale che raccoglie le caratteristiche in comune e che potremmo definire “costrutto semplice”.
Quindi “costrutto semplice” è identificato dal suo nome e partecipa alla relazione “proprietà” insieme ad “attributo”. Le entità “entità” e “relazione” costituiscono una specializzazione totale e disgiunta di “costrutto semplice” e ne ereditano le caratteristiche.
Le entità “entità” e “relazione” si specializzano poiché partecipano alla relazione “partecipazione” con ruoli differenti. Un’ “entità” può partecipare un numero arbitrario di volte in “partecipazione”. Stesso vale per “relazione”, con la differenza che ogni istanza di “relazione” collega almeno 2 “entità” (una relazione è al meno binaria).
Possiamo considerare che in una relazione, la cardinalità non è una proprietà che appartiene alle entità che vi partecipano, ma è una caratteristica della relazione. Quindi la relazione “partecipazione” avrà un attributo “cardinalità” il cui valore potrà essere derivato dalla partecipazione delle entità nella relazione.

“Attributo”

Come visto nella descrizione, “attributo” non ha attributi chiave, ma può essere identificato da un attributo parziale “nome” e dalla relazione “proprietà” che lo lega alla sua entità proprietaria “costrutto semplice”.
Quindi “attributo” è un entità debole e “proprietà” è la sua relazione identificante.
Un “attributo” può specializzarsi nelle entità “attributo scalare” ed “attributo multivalore” la differenza tra queste due specializzazioni sta nel fatto che uno “scalare” è associato ad uno ed un solo dato, mentre uno “multivalore” può avere nessuno o più dati.
Salta fuori da questa osservazione una nuova entità del modello ER, sfuggita in prima analisi, che è il “dato”. Il “dato” non è un costrutto, cioè non è un concetto che viene disegnato negli schemi ER, ma è il concetto fondamentale su cui si costruiscono le basi di dati.
Per mancanza di fantasia, ho denotato le associazioni tra “dato” e “scalare” e tra “dato” e “multivalore” rispettivamente “a” e “b”.

“Attributo composto”: un’entità definita ricorsivamente

Un’entità “attributo” può essere composta da altre entità “attributo”. Ciò è rappresentabile tramite una relazione ricorsiva, “composizione”, dell’entità “attributo” su se stessa.
Dato che non tutte le occorrenze di “attributo” sono del tipo “attributo composto” (ma ogni occorrenza di “attributo composto” è anche un occorrenza di “attributo”), è corretto rappresentare “attributo composto” come una specializzazione di “attributo”, dotata della proprietà aggiuntiva di partecipare nella relazione “composizione” con un ruolo specifico: essere composto da altri attributi.

“Generalizzazione”

Una generalizzazione, dato che collega due entità tramite una relazione padre/figlio, potrebbe sembrare un caso particolare (una specializzazione) di “relazione”.
Ma non è così. Una generalizzazione non può ereditare un nome, ma è identificata dalle entità che ne sono collegate.
Ne consegue logicamente una rappresentazione dell’entità “generalizzazione” come un entità debole legata a due relazioni identificanti “padre” e “figlio”.

Mettendo tutto insieme

Si può disegnare uno schema ER che rappresenta il modello ER:

meta-esercizio-ER

Esercizi sulle query in SQL

Alcune premesse:
1) Ho svolto questi esercizi come applicazione di quanto ho potuto appendere dalla visione
delle video lezioni (anche qui) del corso di basi di dati tenuto dal Prof. Paolo Atzeni e dal Prof. Riccardo Torlone.
2) Ho usato SQLite 3.7 come dbms per svolgere e testare questi esercizi. Un’istanza del database a scopo di esercizio è qui
3) Sono riportate le soluzioni degli esercizi così come li ho svolti,
ciò non implica che gli esercizi non possano essere svolti trovando soluzioni alternative.
4) Per semplicità si assume che l’attributo “nome” nella tabella “persone” sia sufficiente ad
identificare univocamente una persona

Dato il seguente schema di database,

CREATE TABLE maternita(
madre text references persone(nome),
figlio text unique references persone(nome)
);
CREATE TABLE paternita(
padre text references persone(nome),
figlio text unique references persone(nome)
);
CREATE TABLE persone(
nome text primary key,
eta numeric,
reddito numeric
);

scrivere in SQL le query necessarie a:

 

  1. Trovare nome e reddito della madre di Filippo.SELECT nome, reddito
    FROM persone, maternita WHERE nome = madre AND figlio = ‘Filippo’;oppure tramite l’uso di query annidateSELECT nome, reddito
    FROM persone
    WHERE nome = (SELECT madre FROM maternita WHERE figlio = ‘Filippo’);
  2. Trovare i padri delle persone che guadagnano più di 20.SELECT DISTINCT padre
    FROM paternita, personeWHERE figlio = nome AND reddito > 20;Osservazione: questo esercizio mostra l’uso della parola chiave
    DISTINCT e mette in evidenza le differenze che esistono tra ciò che in
    pratica è una tabella e quello che invece nella teoria dei database
    relazionali viene considerata una relazione.Una relazione è un insieme di tuple, mentre una tabella è una lista
    di ennuple. Capito qual’è la differenza?oppure tramite query annidate

    SELECT DISTINCT padre
    FROM paternita
    WHERE figlio IN (SELECT nome FROM persone WHERE reddito > 20);

  3. Ricavare la relazione genitore-figlio.
    Questa relazione è già presente nel database, ma è divisa nelle
    relazioni di maternità e paternità. Per ottenere la relazione richiesta
    è sufficiente unire maternità e paternità insieme facendo attenzione a
    rinominare opportunamente i loro attributi.SELECT padre AS genitore, figlio FROM paternita
    UNION
    SELECT madre AS genitore, figlio FROM maternita;
  4. Trovare nome, padre e madre delle persone per cui sia noto il padre e la  madre.
    Si ricercano i figli che sono comuni alle relazioni di paternità e maternità. E’necessario un equi-join sull’attributo figlio.SELECT P.figlio, padre, madre
    FROM paternita P, maternita M
    WHERE P.figlio=M.figlio;oppureSELECT figlio, padre, madre
    FROM paternita NATURAL JOIN maternita;
  5. Per ogni persona trovare padre e madre.Rispetto al caso precedente sono da considerarsi informazioni da riportare nel risultato anche i casi in cui nella base di dati NON siano presenti padre e/o madre di un individuo.Nota: se nel database fossero presenti padre e madre di ogni persona, allora questo dovrebbe contenere l’intero albero genealogico fino ad Adamo ed Eva!SELECT nome,padre,madre
    FROM (persone LEFT JOIN paternita ON nome = figlio) AS P LEFT JOIN maternita AS M on nome = M.figlio;Per risolvere questa interrogazione è necessario l’uso dell’operatore
    di join esterno.

    SQLite ha a disposizione l’operatore di join a sinistra.

  6. Quanti figli ha avuto ciascuna madre?

    SELECT madre, count(*) AS numFigli FROM maternita GROUP BY madre;Questa interrogazione mostra l’uso dell’operatore di aggregazione GROUP
    BY e delle funzione che operano sugli aggregati (nel nostro caso la funzione count). L’operatore di aggregazione non è un operatore dell’algebra relazionale ma è specifico del linguaggio SQL.
  7. Qual’è lo stipendio medio dei figli di ciascun padre?

    SELECT padre, AVG(reddito) AS redditoMedioFigli
    FROM paternita JOIN persone
    WHERE figlio = nome
    GROUP BY padre;
  8. Trovare le persone che guadagnano più dei rispettivi padri; mostrando nome della persona, il suo reddito ed il reddito del padre.SELECT bimbo.nome AS nome, bimbo.reddito AS reddito, papa.reddito AS padrereddito
    FROM persone papa, paternita relazione, persone bimbo
    WHERE papa.nome = relazione.padre AND bimbo.nome = relazione.figlio AND bimbo.reddito > papa.reddito
  9. Trovare tutte le persone che hanno un figlio.SELECT DISTINCT madre AS genitore FROM maternita
    UNION
    SELECT DISTINCT padre AS genitore FROM paternita;Oppure si può usare il quantificatore esistenziale EXISTSSELECT nome AS genitore FROM persone
    WHERE EXISTS (SELECT * FROM paternita WHERE nome = padre)
    OR EXISTS (SELECT * FROM maternita WHERE nome = madre);
  10. Trovare i padri che hanno figli che guadagnano TUTTI più di 20.Risolvere questa interrogazione richiederebbe l’uso di un quantificatore universale, che però non è presente in SQL ma può essere facilmente simulato con l’uso del quantificatore esistenziale EXISTS.Come noto dalla logica, l’interrogazione richiesta è equivalente a
    “trovare i padri che NON hanno ALCUN figlio che guadagna meno di 20”.SELECT DISTINCT padre FROM paternita AS A
    WHERE NOT EXISTS (SELECT * FROM paternita B, persone
    WHERE A.padre = B.padre AND B.figlio = nome AND reddito <= 20);

    Si può anche risolvere osservando che l’insieme dei padri che hanno figli che guadagnano più di 20 può essere ottenuto sottraendo all’insieme di tutti i padri quelli  che hanno almeno un figlio che ha un reddito minore o uguale a 20.

    SELECT DISTINCT padre FROM paternita
    EXCEPT
    SELECT DISTINCT padre FROM paternita, persone
    WHERE figlio = nome AND reddito <= 20;

    quest’ultimo esempio mostra che pensare in termini d’insiemi può essere altrettanto efficace.

.

Concetto di sistema informativo e sistema informatico

Un sistema informativo è quel sottosistema di un’organizzazione che si occupa di gestire (cioè acquisire, elaborare, conservare, rendere disponibili, etc) le informazioni per perseguire gli scopi dell’organizzazione stessa.

Il sistema informativo può fare uso (ma non è detto che lo faccia sempre) di strumenti automatici per gestire le informazioni. La parte automatizzata del sistema informativo viene definita sistema informatico.