Casa editrice

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,

1. Uno schema concettuale della realtà di interesse attraverso la produzione del diagramma E/R (scrivendo esplicitamente le conseguenti regole di lettura);
2. lo schema logico della realtà di interesse ottenuto attraverso il mapping relazionale dello schema concettuale (diagramma E/R) ottenuto al punto precedente;
3. la definizione delle relazioni della base dati ottenute al punto precedente in linguaggio SQL.

Ed inoltre

1. 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')