Esercitazione sulla modellazione dati
e il linguaggio SQL

Raccolta di informazioni su poeti e poesie

Si devono organizzare in un database le informazioni sulle poesie provenienti da poeti italiani e stranieri, moderni e antichi.
Le poesie sono classificate con riferimento a un periodo storico (Grecia antica, Trecento, Seicento, ecc.) oppure ad una corrente letteraria (Romanticismo, Ermetismo, ecc.), oppure ad entrambe; se una sola informazione è disponibile, l’altra ha valore nullo.
I poeti viventi hanno la data di morte con valore nullo.
Di ciascuna poesia, oltre al titolo e all’anno di edizione, vengono conservati in un campo stringa anche i primi 50 caratteri, e un link al file che contiene il testo completo della poesia. Si suppone che i file dei testi esistano già e che comunque siano registrati esternamente al database.
Dopo aver svolto in modo completo l’analisi e la documentazione del problema, codificare in SQL le seguenti operazioni e interrogazioni sul database:
1. Inserire il record di una nuova poesia di un autore già presente nell’archivio dei poeti.
2. Aggiornare la data di morte di un poeta recentemente scomparso.
3. Elencare i titoli delle poesie (con il cognome del poeta) che contengono una parola prefissata.
4. Elencare cognome, nome, periodo storico dei poeti per i quali ci sono registrate in archivio più di 10 poesie.
5. Elencare le nazioni per le quali non ci sono registrazioni di poeti nel database.
6. Di un poeta di cui viene fornito il codice, elencare tutte le poesie contenute nel database: di ciascuna il titolo e l’età del poeta alla quale è stata scritta.

Analisi del problema

Le entità che possono essere individuate nel problema sono:
- Poesia, per rappresentare le poesie;
- Poeta, per rappresentare i poeti;
- Nazione, per rappresentare la nazionalità dei poeti;
- PeriodoStorico, per rappresentare i periodi storici in cui sono state scritte le poesie;
- CorrenteLetteraria, per rappresentare la corrente letteraria di riferimento per le poesie.

Gli attributi di Poesia sono: id, titolo, annoediz, inizio, link.
Tutti gli attributi sono di tipo carattere ad eccezione di: annoediz di tipo numerico intero.
Gli attributi di Poeta sono: codice, nome, cognome, datanascita, datamorte.
Tutti gli attributi sono di tipo carattere ad eccezione di: datanascita e datamorte di tipo data.
Gli attributi di Nazione sono: codnazione, descrizione, continente.
Tutti gli attributi sono di tipo carattere.
Gli attributi di PeriodoStorico sono: idperiodo, descrizione,
Tutti gli attributi sono di tipo carattere ad eccezione di idperiodo di tipo intero.
Gli attributi di CorrenteLetteraria sono: idcorrente, descrizione,
Tutti gli attributi sono di tipo carattere ad eccezione di idcorrente di tipo intero.

Associazioni
Tra l'entità Poeta e l'entità Poesia esiste un'associazione 1:N, perchè un poeta può scrivere più poesie mentre una poesia deve essere scritta da un solo poeta.
Tra l'entità Nazione e l'entità Poeta esiste un'associazione 1:N, perchè una nazionalità può essere posseduta da più poeti, mentre un poeta deve possedere una sola nazionalità.
Tra l'entità PeriodoStorico e l'entità Poesia  esiste un'associazione 1:N, perchè una poesia deve appartenere a un solo periodo storico, mentre un periodo storico può riferirsi a più poesie.
Tra l'entità CorrenteLetteraria e l'entità Poesia  esiste un'associazione 1:N, perchè una poesia deve appartenere a una sola corrente letteraria prevalente, mentre una corrente letteraria può riferirsi a più poesie.


Modello E/R


Regole di lettura
Ogni poeta può scrivere una o più poesie, ogni poesia deve essere scritta da un solo poeta.
Ogni nazione può raggruppare uno o più poeti, ogni poeta deve essere raggruppato in una sola nazione.
Ogni periodo storico può essere riferito a una o più poesie, ogni poesia deve riferirsi a un solo periodo storico.
Ogni corrente letteraria può classificare una o più poesie, ogni poesia deve essere classificata in una una sola corrente letteraria.

Tabelle
Nazioni (Codnazione, Descrizione,Continente)
Poeti (Codice, Nome, Cognome, Datanascita, Datamorte, Codnazione).
CorrentiLetterarie (Idcorrente, Descrizione).
PeriodiStorici (Idperiodo, Descrizione).
Poesie (Id, Titolo, Annoediz, Inizio, Link, Idperiodo, Idcorrente, Codice).

 

Descrizione dati

Tabella

Attributi

Chiave

Tipo

Obbligatorio

Indicizzato

Nazioni

Codnazione

PK

varchar (2)

Si

Si

 

Continente

 

varchar( 30)

 

 

 

Descrizione

 

varchar( 30)

 

 

CorrentiLetterarie

Idcorrente

PK

integer

Si

Si

 

Descrizione

 

blob

 

 

PeriodiStorici

Idperiodo

PK

integer

Si

Si

 

Descrizione

 

blob

 

 

Poeti

Codice

PK

integer

Si

Si

 

Nome

 

varchar (30)

 

 

 

Cognome

 

varchar (30)

 Si

 

 

Datanascita

 

date

 

 

 

Datamorte

 

date

 

 

 

Codnazione

FK

varchar (2)

 

 

Poesie

Id

PK

integer

Si

Si

 

Titolo

 

varchar (60)

Si

 

 

Annoediz

 

integer

 

 

 

Inizio

 

blob

 

 

 

Link

 

Varchar (40)

 

 

 

Codice

FK

integer

 

 

 

Idperiodo

FK

integer

 

 

 

Idcorrente

FK

integer

 

 

Interrogazioni

1) INSERT INTO Poesie (Titolo ,Annoediz ,Link ,Codice, Idcorrente ,Idperiodo )
VALUES ('La sera', 1895, 'www.testipoesie.it', 2, 1, 3 );

2) UPDATE Poeti
SET Datamorte = [inserisci data di morte]
WHERE Poeti.Codice =[inserisci codice];

3) SELECT Poesie.Titolo, Poeti.Cognome
FROM Poesie,Poeti
WHERE Poesie.Codice=Poeti.Codice AND
Poesie.Titolo LIKE '%[inserisci parola]%';

4) SELECT Poeti.Cognome, Poeti.Nome, PeriodiStorici.Descrizione
FROM Poeti, PeriodiStorici, Poesie
WHERE Poesie.Codice=Poeti.Codice AND
Poesie.Idperiodo=PeriodiStorici.Idperiodo
Group By Poeti.Cognome, Poeti.Nome, PeriodiStorici.Descrizione
Having Count(Poesie.Id) > 10;

5) SELECT Nazioni.Descrizione
FROM Nazioni
WHERE Codnazione NOT IN (SELECT Distinct Poeti.Codnazione
                                FROM Poeti);

6) SELECT Poesie.Titolo,
Poesie.Annoediz – YEAR(Poeti.Datanascita) AS Età
FROM Poesie, Poeti
WHERE Poesie.Codice=Poeti.Codice AND
Poeti.Codice=[inserire codice];