1995 – Notiziario settimanale

Il problema

Il diagramma E/R viene costruito passo passo.

Gli articoli e le lettere

ARTICOLO

• Un articolo può essere pubblicato in un numero della rivista

NUMERO

• Un numero della rivista contiene uno o più articoli

LETTERA

• Una lettera è inviata da un abbonato

ABBONATO

• Un abbonato può inviare una o più lettere

Le lettere pubblicate

Un numero settimanale contiene anche alcune lettere

ARTICOLO

• Un articolo può essere pubblicato in un numero della rivista

NUMERO

• Un numero della rivista contiene uno o più articoli
• Un numero della rivista può contenere una o più lettere

LETTERA

• Una lettera è inviata da un abbonato
• Una lettera può essere pubblicata in un numero

ABBONATO

• Un abbonato può inviare una o più lettere

Autore?

Anche gli articoli hanno un autore (o più di uno?)

AUTORE

• Un autore può scrivere uno o più articoli

ARTICOLO

• Un articolo può essere pubblicato in un numero della rivista
• Un articolo è stato scritto da un autore

NUMERO

• Un numero della rivista contiene uno o più articoli
• Un numero della rivista può contenere una o più lettere

LETTERA

• Una lettera è inviata da un abbonato
• Una lettera può essere pubblicata in un numero

ABBONATO

• Un abbonato può inviare una o più lettere

Ancora?

Il database potrebbe essere arricchito con ulteriori informazioni

• Gli accessi degli abbonati agli articoli
• Il voto, da parte dei lettori, per ogni articolo
• Un abbonato si abbona più volte, in periodi diversi
• Un articolo potrebbe avere più di un autore
• …

Schema relazionale

Considera prima i diversi sottoschemi e poi riuniscili.

Gli articoli e le lettere

Query

Articoli per titolo

SELECT ARTICOLO.*
FROM   ARTICOLO
WHERE  (Titolo LIKE "*" & [Parola chiave?] & "*")

Articoli per settimana

SELECT ARTICOLO.*
FROM   ARTICOLO INNER JOIN NUMERO ON (ARTICOLO.pubblicatoIN = NUMERO.IDnumero)
WHERE  (IDnumero = [Numero rivista?])

Articoli per titolo e settimana

SELECT ARTICOLO.*
FROM   ARTICOLO INNER JOIN NUMERO ON (ARTICOLO.pubblicatoIN = NUMERO.IDnumero)
WHERE  (Titolo LIKE "*" & [Parola chiave] & "*") AND (IDnumero = [Numero rivista?])

Lettere per nome dell’autore

SELECT LETTERA.*
FROM   LETTERA INNER JOIN ABBONATO ON (LETTERA.scrittaDA = ABBONATO.IDabbonato)
WHERE  (Cognome = [Cognome abbonato?]) AND (Nome = [Nome abbonato?])

Lettere per periodo

SELECT LETTERA.*
FROM   LETTERA
WHERE  (DATA BETWEEN [Dal...] AND [Al...])

Lettere per nome e per periodo

SELECT LETTERA.*
FROM   LETTERA INNER JOIN ABBONATO ON (LETTERA.scrittaDA = ABBONATO.IDabbonato)
WHERE  (Cognome = [Cognome abbonato?]) AND
       (Nome = [Nome abbonato?]) AND
       (DATA BETWEEN [Dal...] AND [Al...])

Articoli non pubblicati

SELECT ARTICOLO.*
FROM   ARTICOLO
WHERE  (pubblicatoIN IS NULL)

Le lettere pubblicate

Query

Le lettere di un abbonato, dati nome e cognome

SELECT LETTERA.*
FROM   ABBONATO INNER JOIN LETTERA ON (ABBONATO.IDabbonato = LETTERA.scrittaDA)
WHERE  (Cognome=[Cognome abbonato?]) AND
       (Nome=[Nome abbonato?])

Gli articoli che trattano “arte” nel titolo

SELECT *
FROM   ARTICOLO
WHERE  (Titolo LIKE "*arte*")

Gli articoli che NON trattano “arte” nel titolo

SELECT *
FROM   ARTICOLO
WHERE  (Titolo NOT LIKE "*arte*")

Gli articoli che trattano un certo TESTO nel titolo

SELECT *
FROM   ARTICOLO
WHERE  (Titolo LIKE "*" & [Testo contenuto?] & "*")

Quanti articoli in archivio?

SELECT COUNT(*)
FROM   ARTICOLO

Quanti articoli PUBBLICATI?

SELECT COUNT(pubblicatoIN)
FROM   ARTICOLO

oppure

SELECT COUNT(*)
FROM   ARTICOLO
WHERE  (pubblicatoIN IS NOT NULL)

Quante lettere ricevute in un certo periodo?

SELECT COUNT(*)
FROM   LETTERA
WHERE  (DATA BETWEEN [Dalla DATA?] AND [Alla DATA?])

Gli abbonati che hanno scritto almeno una lettera

SELECT DISTINCT ABBONATO.*
FROM   ABBONATO INNER JOIN LETTERA ON (ABBONATO.IDabbonato=LETTERA.scrittaDA)

oppure

SELECT *
FROM   ABBONATO
WHERE  IDabbonato IN (SELECT scrittaDA
                      FROM LETTERA)

Gli abbonati che hanno scritto almeno X lettere

SELECT IDabbonato, cognome, nome
FROM   ABBONATO
WHERE  IDabbonato IN (SELECT   scrittaDA
                      FROM     LETTERA
                      GROUP BY scrittaDA
                      HAVING   (COUNT(*) >= [Numero minimo lettere?]))

oppure

SELECT   IDabbonato, cognome, nome
FROM     ABBONATO INNER JOIN LETTERA ON (ABBONATO.IDabbonato=LETTERA.scrittaDA)
GROUP BY IDabbonato, cognome, nome
HAVING   (COUNT(*) >= [Numero minimo lettere?])

…, e quante

SELECT   IDabbonato, cognome, nome, COUNT(*) AS [Numero lettere]
FROM     ABBONATO INNER JOIN LETTERA ON (ABBONATO.IDabbonato=LETTERA.scrittaDA)
GROUP BY IDabbonato, cognome, nome
HAVING   (COUNT(*) >= [Numero minimo lettere?])

Classifica per numero di lettere scritte

SELECT   IDabbonato, cognome, nome, COUNT(*) AS [Numero lettere]
FROM     ABBONATO INNER JOIN LETTERA ON (ABBONATO.IDabbonato=LETTERA.scrittaDA)
GROUP BY IDabbonato, cognome, nome
ORDER BY COUNT(*) DESC

Classifica per numero di lettere scritte (i primi 5)

SELECT   TOP 5 IDabbonato, cognome, nome, COUNT(*) AS [Numero lettere]
FROM     ABBONATO INNER JOIN LETTERA ON (ABBONATO.IDabbonato=LETTERA.scrittaDA)
GROUP BY IDabbonato, cognome, nome
ORDER BY COUNT(*) DESC

Classifica per numero di lettere scritte, almeno X

SELECT   IDabbonato, cognome, nome, COUNT(*) AS [Numero lettere]
FROM     ABBONATO INNER JOIN LETTERA ON (ABBONATO.IDabbonato=LETTERA.scrittaDA)
GROUP BY IDabbonato, cognome, nome
HAVING   (COUNT(*) >= [Numero minimo lettere?])
ORDER BY COUNT(*) DESC

Finale

Schema esaustivo delle richieste?