Sistemi e Reti: protocolli di rete e programmazione

Informaitica dalla A ai zombie, protocolli di rete e programmazione java

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

 

Please follow and like us:

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.

Commenti recenti

Working Hours

  • Monday9am - 6pm
  • Tuesday9am - 6pm
  • Wednesday9am - 6pm
  • Thursday9am - 6pm
  • Friday9am - 6pm
  • SaturdayClosed
  • SundayClosed
Latest Posts

Teachers

Facebook
Twitter
LinkedIn
Contatti

Commenti recenti

Wordpress Social Share Plugin powered by Ultimatelysocial
error

Enjoy this blog? Please spread the word :)