Una casa editrice desidera archiviare in un database le informazioni riguardanti gli abbonamenti alle riviste ed ai giornali pubblicati tra il 1995 ed il 2006.
Per ogni abbonato si richiede di memorizzare i dati anagrafici, per ogni abbonamento la data ed il periodo di validità (trimestrale, semestrale, annuale). Bisogna considerare che gli abbonati possono avere abbonamenti anche per più pubblicazioni.
Per ogni giornale o rivista occorre archiviare il titolo, la periodicità (quotidiano, settimanale, mensile, il prezzo dell’abbonamento e gli argomenti trattati. Inoltre deve essere mantenuto un indice con i titoli dei principali articoli pubblicati ed a ciascun articolo deve essere associata la pubblicazione in cui è comparso.
Si realizzino, fatte le ipotesi aggiuntive del caso,
- Uno schema concettuale della realtà di interesse attraverso la produzione del diagramma E/R (scrivendo esplicitamente le conseguenti regole di lettura);
- lo schema logico della realtà di interesse ottenuto attraverso il mapping relazionale dello schema concettuale (diagramma E/R) ottenuto al punto precedente;
- la definizione delle relazioni della base dati ottenute al punto precedente in linguaggio SQL.
Ed inoltre
- si implementino, , le seguenti interrogazioni:
Q1: Dato il titolo di una pubblicazione, ricercare gli articoli pubblicati in un determinato anno;
Q2: Dato il titolo di una pubblicazione, ricercare gli abbonati annuali;
Q3: Dato il nominativo di un abbonato, stabilire a quante riviste è abbonato; Q4: Dato un argomento, elencare le pubblicazioni in cui è trattato;
Q5: Riportare per ogni pubblicazione il numero di abbonamenti;
Q6: Visualizzare i giornali con almeno 5000 abbonati annuali;
Q7: Dati i titoli di due pubblicazioni, visualizzarne gli abbonati comuni;
Q8: Dato il titolo di una pubblicazione, elencare le pubblicazioni che trattano i suoi stessi argomenti.
Analisi
Dobbiamo gestire la banca dati in cui memorizzare gli abbonati con i relativi abbonamenti a delle riviste e/o giornali pubblicati da una casa editrice in un determinato periodo. Delle pubblicazione ci interessano anche gli articoli pubblicati e gli argomenti trattati da ciascun tipo di pubblicazione.gli argomenti trattati.
Modelle Concettuale
Le entità coinvolte con i relativi attributi sono:.
Pubblicazione (ISA totale con entità figlie), possiamo accorparle e inserire un attributo tipo.
Passiamo ora ad esaminare quali sono le associazioni tra le entità:
Tra le entità Pubblicazione ed Argomento esiste un’associazione “Tratta” di molteplicità N:N, totale in entrambi i versi, in quanto l’argomento trattato deve riguardare almeno una pubblicazione e viceversa una pubblicazione deve trattare almeno un argomento. Se supponiamo di avere solo riviste che trattano un solo genere di argomenti e di non interessarci agli argomenti dei giornali allora l’associazione diventa 1 a N tra argomento e pubblicazione.
Tra le entità Pubblicazione ed Articolo esiste un’associazione “Pubblica”(con attributo numero di pubblicazione) di molteplicità 1:N, totale in entrambi i versi, in quanto “una pubblicazione” deve pubblicare almeno un’articolo e un articolo è pubblicato solo in una pubblicazione.
Tra abbonato e Abbonamento l’associazione Stipula è 1:N totale, in quando un abbonato deve stipulare almeno un abbonamento e un abbonamento è stipulato da un solo abbonato.
Tra Pubblicazione è Abbonamento l’associazione riguarda è 1 a N perché una Pubblicazione riguarda almeno un abbonamento e un abbonamento riguarda una sola pubblicazione.
La ristrutturazione del modello non è necessaria in quando l’unica problematica ( gerarchia l’abbiamo eliminata).
Regole di Lettura
Tra le entità Pubblicazione ed Argomento
Ogni pubblicazione deve trattare uno o più argomenti
Ogni argomento deve essere trattato in una o più pubblicazioni.
Tra le entità Pubblicazione ed Articolo
Ogni pubblicazione deve pubblicare uno o più articoli
Ogni articolo deve essere pubblicato in una pubblicazione.
Tra Pubblicazione è Abbonamento
Ogni abbonato deve essere riguardare una pubblicazione
Ogni pubblicazione deve riguardare uno o più abbonamenti.
Tra Abbonato è Abbonamento
Ogni abbonato deve Stipulare uno o più abbonamenti.
Ogni Abbonamento deve essere stipulato da un abbonato.
Modello logico
Ogni Entità diventa una relazione con chiave primaria l’attributo identificatore unico, e se partecipa a un associazione uno a N con molteplicità N prende come chiave esterna la chiave primaria della relazione che partecipa con molteplicità uno. Le associazioni N:M diventano delle relazioni con chiave primaria le chiavi primarie delle relazioni coinvolte.
Pubblicazioni (Codice(PK), Titolo, Periodicità, Tipo,PrezzoM,PressoS,PrezzoA)
Il campo periodicità può assumere solo determinati valori, se nella nostra implementazione è possibile usare un campo di tipo ENUM possiamo lasciare la tabella cosi altrimenti lo sostituiamo con le chiave esterna di una nuova relazione Periodicità(idPeriodicità(PK),descrizione) lo stesso ragionamento per il campo tipo.
Abbonati (CodFisc(PK), Cognome, Nome,Via, Civico, Cap, Città,Telefono)
Abbonamenti (NAbbonamento(PK),Abbonato(FK), Pubblicazione(FK), Data, PeriodoValidità)
Per il campo PeriodoValidità vale lo stesso discoro fatto in precedenza per periodicità.
Argomenti (idAcromento(PK),Descrizione)
Tratta (Pubblicazione(PK), Argomento(PK))
Articoli (Codice(PK), Titolo, Testo, NumPubbl, Data, Pubblicazione(FK))
La relazione Articoli non è in terza forma normale numero pubblicazione e data dipendono da pubblicazione e non dalla chiave primaria.
Articoli (Codice(PK), Titolo, Testo, NumPubbl(fk))
NumeroPubb(idPub(pk),NumPubbl, Data, Pubblicazione(FK))
Relazione | Attributo | Chiave | Tipo | Dimensione | Vincoli |
Abbonati | codiceFiscale | PK | char | 16 | Chiave |
cognome | varchar | 20 | NOT NULL | ||
nome | varchar | 20 | NOT NULL | ||
telefono | varchar | 15 | NOT NULL | ||
via | varchar | 15 | NOT NULL | ||
civico | varchar | 6 | |||
città | varchar | 15 | NOT NULL | ||
cap | char | 5 | NOT NULL | ||
Pubblicazioni | codice | PK | int | auto incremento | |
titolo | varchar | 15 | UNICO, NOT NULL | ||
periodicità | Enum(‘quotidiano’, ‘Settimanale’, ‘Mensile’) | NOT NULL | |||
tipo | ENUM(‘GIORNALE’,’RIVISTA’) | NOT NUL | |||
prezzoT | DECIMAL | 5,2 | NOT NULL | ||
prezzoS | Decimal | 5,2 | NOT NUL | ||
prezzoA | Decimal | 5,2 | NOT NULL | ||
Abbonamenti | idAbbonamento | PK | int | Autoincrement | |
data | datatime | NOT NULL default data corrente compresa tra il 1995 e il 2006 | |||
validità | ENUM(‘trimestrale’,’semestrale’,’annuale’) | NOT NULL | |||
abbonato | FK | char | 16 | Abbonato(CodiceFiscale) | |
pubblicazioni | FK | int | Pubblicazioni(codice) NOT NULL | ||
Argomenti | idArgomenti | PK | int | Autoincrement | |
descrizione | varchar | 255 | NOT NULL | ||
Tratta | pubblicazione | PK | int | Pubblicazione(codice) NOT NULL | |
argomento | PK | int | Argomenti(idArgomento) NOT NULL | ||
NumeroPubb | idNum | pk | int | auto incremento | |
numero | int | NOT NULL | |||
Data | Date | data corrente compresa tra il 1995 e il 2006 | |||
pubblicazione | FK | int | Pubblicazione(codice) | ||
Articoli | idArticolo | PK | int | Auto incremento | |
titolo | varchar | 50 | NOT NULL | ||
testo | text | NOT NULL | |||
numeroPubb | FK | int | NumeroPubb(idNum) |
Modello Fisico Msql MariaDB
CREATE TABLE Abbonati ( codiceFiscale CHAR(16) NOT NULL, cognome VARCHAR(20) NOT NULL, nome VARCHAR(20) NOT NULL, telefono VARCHAR(15) NULL, via VARCHAR(15) NULL, città VARCHAR(15) NULL, civico VARCHAR(6) NULL, cap CHAR(5) NULL, PRIMARY KEY (`codicefiscale`)); CREATE TABLE Pubblicazioni ( codice INT AUTO_INCREMENT PRIMARY KEY, titolo VARCHAR(15) NOT NULL, periodicita ENUM('quotidiano', 'Settimanale', 'Mensile') NOT NULL, tipo ENUM('GIORNALE','RIVISTA') NOT NULL, UNIQUE INDEX Titolo_UNIQUE (titolo ASC)); CREATE TABLE Abbonamenti ( idAbbonamenti INT AUTO_INCREMENT PRIMARY KEY, data DATETIME NOT NULL DEFAULT current_timestamp, validità ENUM('trimestrale','semestrale','annuale') NOT NULL, abbonato CHAR(16), pubblicazione INT NOT NULL, FOREIGN KEY (abbonato) REFERENCES Abbonati(codiceFiscale) ON DELETE Set NULL ON UPDATE CASCADE, FOREIGN KEY (pubblicazione) REFERENCES Pubblicazioni(codice) ON DELETE CASCADE ON UPDATE CASCADE, CHECK ( YEAR(data) BETWEEN 1995 And 2006) ); CREATE TABLE Argomenti( idArgomento INT AUTO_INCREMENT PRIMARY KEY, descrizione varchar(255) NOT NULL ); CREATE TABLE Tratta( argomento INT NOT NULL, pubblicazione INT NOT NULL, PRIMARY KEY(argomento, pubblicazione), FOREIGN KEY(argomento) REFERENCES Argomenti(idArgomento) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(pubblicazione) REFERENCES Pubblicazioni(codice) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE NumeroPubb ( idNum int auto_increment PRIMARY KEY, numero int NOT NULL, data DateTime DEFAULT CURRENT_TIMESTAMP , pubblicazione int, FOREIGN KEY (publicazione) REFERENCES Pubblicazioni(codice), CHECK ( YEAR(data) BETWEEN 1995 And 2006) ); CREATE TABLE Articoli( idArticolo int AUTO_INCREMENT PRIMARY KEY, titolo varchar(50) NOT Null, contenuto text NOT NULL, numeroPubb int, FOREIGN KEY (numeroPubb)REFERENCES NumeroPubb(idNum));

Interrogazioni
Q1: Dato il titolo di una pubblicazione, ricercare gli articoli pubblicati in un determinato anno;
SELECT Articoli.titolo, Articoli.contenuto, NumeroPubb.numero FROM Pubblicazioni, NumeroPubb, Articoli WHERE Pubblicazioni.codice = NumeroPubb.pubblicazione AND NumeroPubb.idNum=Articoli.numeroPubb AND (year(NumeroPubb.data) = 2020) AND (Pubblicazioni.titolo = 'java script'); OPPURE SELECT Articoli.titolo, Articoli.contenuto, NumeroPubb.numero FROM Pubblicazioni JOIN NumeroPubb on Pubblicazioni.codice = NumeroPubb.pubblicazione JOIN Articoli ON NumeroPubb.idNum=Articoli.numeroPubb WHERE (YEAR(NumeroPubb.data) = 2020) AND Pubblicazioni.titolo = 'java script';
Q2: Dato il titolo di una pubblicazione, ricercare gli abbonati annuali
SELECT Cognome, Nome FROM Abbonati, Abbonamenti, Pubblicazioni WHERE (codiceFiscale = abbonato) AND (pubblicazione = codice) AND (Titolo = 'java script') AND validità='Annuale'; oppure SELECT Cognome, Nome FROM Abbonati JOIN Abbonamenti ON codiceFiscale = abbonato JOIN Pubblicazioni ON pubblicazione = codice WHERE (Titolo = 'java script') AND validità='Annuale';
Q3: Dato il nominativo di un abbonato, stabilire a quante riviste è abbonato
SELECT COUNT(*) AS 'Numero Riviste' FROM Abbonati JOIN Abbonamenti ON codiceFiscale = abbonato WHERE (cognome = 'Filini') AND nome='Giorgio';
Q4: Dato un argomento, elencare le pubblicazioni in cui è trattato
SELECT titolo FROM Pubblicazioni INNER JOIN(Tratta INNER JOIN Argomenti ON argomento = idArgomento) ON codice = pubblicazione WHERE Descrizione = '[InserisciDescrizione]'
Q5: Riportare per ogni pubblicazione il numero di abbonamenti
SELECT codice, titolo, COUNT(*) AS NumAbbonati FROM Abbonamenti INNER JOIN Pubblicazioni ON codice = pubblicazione GROUP BY codice, titolo;
Q6: Visualizzare i giornali con almeno 5000 abbonati annuali
SELECT codice, titolo FROM Abbonamenti INNER JOIN Pubblicazioni ON codice = pubblicazione WHERE (tipo = 'Giornale') AND (validità = 'Annuale') GROUP BY codice, titolo HAVING COUNT(*) >= 5000;
Q7: Dati i titoli di due pubblicazioni, visualizzarne gli abbonati comuni
SELECT Cognome, Nome FROM Abbonati, Abbonamenti, Pubblicazioni WHERE (Abbonati.codiceFiscale = abbonato) AND (pubblicazione = codice) AND (titolo = 'java') AND codiceFiscale in (SELECT codiceFiscale FROM Abbonati, Abbonamenti, Pubblicazioni WHERE Abbonati.codiceFiscale = abbonato AND pubblicazione = codice AND (titolo = 'sql') ) oppure SELECT distinct cognome, Nome,titolo FROM Abbonati, Abbonamenti, Pubblicazioni WHERE (Abbonati.codiceFiscale = abbonato) AND (pubblicazione = codice) AND (titolo = 'Java' OR titolo = 'Java Script') group by cognome,nome having count(*)=2
Q8: Dato il titolo di una pubblicazione, elencare le pubblicazioni che trattano i suoi stessi argomenti
SELECT distinct titolo FROM Pubblicazioni join Tratta on codice=pubblicazione and argomento in (select argomento From Tratta join Pubblicazioni on codice=pubblicazione and titolo='java')
Lascia un commento