PROGETTO DI

LABORATORIO DI BASI DI DATI

A.A. 2005-2006

Prof.ssa: De Marsico

Jacopo Busso	Luca Di Filippo
Simona Sforza	David Taboada

IMPLEMENTAZIONE DI UNA BASI DI DATI

DI UNA CASA EDITRICE

La prima fase di sviluppo del progetto consiste nella raccolta dei requisiti, che nel nostro caso sono dati dalle specifiche assegnate. Si procede quindi all'analisi scrupolosa del materiale a disposizione e nel caso si presentino delle ambiguità è necessario eliminarle facendo luce sui punti poco chiari, argomentando le scelte effettuate.

Dopo una prima lettura delle specifiche risulta che la base dati richiesta può essere fondamentalmente suddivisa in due tipologie di dati, in modo tale di poter trattare ogni singola suddivisione come un'entità a se stante.

I due nodi distinti sono rappresentati da:

Informazioni sui periodici e articoli

Collaboratori (Redazione)

Cerchiamo dunque di isolare gli aspetti delle specifiche che riguardano ciascuno dei due blocchi evidenziati, raccogliendo le informazioni che nel testo sono espresse piuttosto alla rinfusa.

Informazioni sui periodici e articoli

Il testo afferma che la casa editrice deve pubblicare diversi giornali, tale affermazione e' piuttosto ambigua, giacché non sappiamo se il database debba contenere solo le informazioni relative ai quotidiani, oppure in questo caso 'giornali' sia inteso come sinonimo di periodico. Non conoscendo l'uso che il nostro database deve avere è conveniente mantenere un sufficiente grado di generalità, consentendo l'inserimento di dati di diversi periodici che si distinguono dalla loro frequenza di pubblicazione (quotidiano, settimanale, quindicinale e mensile).

Si vuole inoltre tenere traccia delle informazioni sugli articoli pubblicati, l'autore, il supervisore e un editoriale scritto da un membro del comitato. Per ora possiamo ignorare gli aspetti riguardanti autore e supervisore che trattano problematiche legate alla redazione, tenendole comunque sempre presenti.

Le specifiche affermano: 'Occorre inoltre registrare la composizione e la data di uscita dei diversi numeri pubblicati di ogni rivista', da ciò deduciamo che gli articoli devono essere raccolti in base all'uscita di ciascuna rivista e anche qui si presenta un'ambiguità dovuta all'uso improprio di sinonimi, il termine 'rivista' potrebbe indicare solo i periodici settimanali e mensili oppure anche gli articoli dei quotidiani, questi potrebbero essere catalogati allo stesso modo?

Decidiamo che anche per i quotidiani vale lo stesso e proseguiamo con l'analisi.

Tra le altre, la seguente frase: 'il prezzo degli spazi pubblicitari varia a seconda del numero di ripetizioni nel periodo di pubblicazione', presente nelle specifiche ci è sembrata piuttosto ambigua.

Non si capisce se si parli di periodi nel senso di 'stagioni'. In alcuni periodi dell'anno, una pubblicità potrebbe costare più che in altri, per motivi di tiratura più elevata, oppure, se in un certo periodo, (settimana, mese, anno) un maggior numero di ripetizioni, comporti uno sconto sul pagamento totale. Abbiamo deciso di lasciare il prezzo con una tariffa base, alla quale possono essere applicate delle variazioni da parte dell'operatore.

Informazioni sui Collaboratori

Per quanto riguarda questa parte, dalle specifiche riscontriamo delle ambiguità sui componenti del Comitato della Redazione:

La frase: 'Si vogliono memorizzare informazioni sul comitato di redazione (direttore e supervisore)' è in contrasto con 'Ogni numero prevede un editoriale scritto da un membro del Comitato di Redazione o dal Direttore della rivista' in quanto dalla seconda sembrerebbe esserci una distinzione tra il Comitato e il Direttore a differenza della prima.

Abbiamo deciso di considerare il direttore facente parte della redazione insieme ai supervisori, come descritto nella prima parte, perché ci sembrava opportuno far coincidere le due cariche all'interno di un organismo superiore in una gerarchia di lavoro.

'Gli stipendi dei Collaboratori Fissi sono soggetti a variazioni contrattuali'.

Ciò non ci fa intendere se si vuole registrare uno storico dei contratti effettuati, oppure se si voglia poter cambiare i dati dei contratti, in special modo per quanto riguarda la retribuzione dei Collaboratori. Alcuni casi possono essere: promozioni a supervisori oppure a direttore, bonus riguardanti articoli di successo, oppure in caso di maggior numero di articoli pubblicati, da quelli previsti dal contratto.

Per questo abbiamo deciso di Conservare nel database unicamente lo stato attuale dei contratti stipulati. In questo modo un cambiamento di contratto implica la sostituzione e non una semplice modifica o registrazione di un nuovo contratto, evitando così accidentali inconsistenze nel database.

'il compenso dei Collaboratori Occasionali viene stabilito in base alle singole prestazioni'

Non è chiaramente spiegato, cosa si intenda con il termine 'prestazioni', se si parli solo della stesura di articoli, oppure di ricerche approfondite, oppure ancora di servizi fotografici.

È stato deciso che le prestazioni riguardassero soltanto un lavoro finito, quindi l'intera stesura di un articolo.

'Il comitato di redazione può anche partecipare alla stesura di articoli'

Tale frase può essere interpretata in più modi, ad esempio il comitato può scrivere interamente articoli oppure cooperare alla stesura di un articolo come coautore, ricordiamo che i membri del comitato, partecipano già alla stesura degli articoli, in quanto supervisori. Abbiamo ritenuto che la prima ipotesi fosse la più plausibile.

Dizionario delle Entità

Dizionario delle Associazioni

Vincoli di integrità sui dati

Un articolo può essere scritto da un qualsiasi collaboratore, (avendo deciso di memorizzare i dati delle prestazioni dei collaboratori occasionali nell'associazione Scritto_da_1) nel caso in cui questo venga scritto da un collaboratore fisso, l'attributo pagato dell'associazione viene settato a 0.00, giacché il reddito del collaboratore fisso dipende soltanto dal contratto che egli ha stipulato.

Un articolo deve essere supervisionato, soltanto da un supervisore della stessa area.

Un Supervisore può essere scelto soltanto tra i Collaboratori fissi.

Dal momento che un collaboratore non può essere allo stesso momento sia fisso che occasionale, non possiamo avere uno stesso Codice Fiscale nelle due tabelle.

PROCEDIMENTO DI LAVORO SULLO

SCHEMA CONCETTUALE

Nel creare lo schema concettuale, abbiamo deciso inizialmente di creare quattro entità principali, le quali sono poi state suddivise in altre sotto-entità.

Articolo

Collaboratore

Redazione

Periodico

A queste quattro entità, si è poi deciso di aggiungere le entità:

Uscita

Pubblicità

Ditta

Contratto

La prima utile soprattutto a tenere lo storico dei dati di ogni pubblicazione di un periodico.

Pubblicità e Ditta sono state inserite per poter gestire attraverso il data-base le pubblicità che vengono pubblicate sui periodici.

Contratto infine è stata inserita per agevolare la gestione della retribuzione dei collaboratori.

Per quanto riguarda gli attributi delle entità, inizialmente sono stati inseriti quelli ritenuti necessari e più logici, come i dati anagrafici di un collaboratore, il nome dei periodici, titolo e testo di un articolo, ragione sociale delle ditte.

Gli altri sono poi stati inseriti seguendo le necessità dovute alle operazioni possibili da effettuare sulla base di dati.

Le entità sono poi state così suddivise:

Periodico

- Quotidiano

- Settimanale

- Quindicinale

- Mensile

Questa generalizzazione è stata inserita per suddividere ulteriormente i quattro diversi tipi di periodici stampati.

E' di tipo totale-esclusivo

Collaboratore

- Occasionale

- Fisso

- Supervisore

- Direttore

In questa duplice generalizzazione, la più esterna si è resa necessaria, in quanto il collaboratore fisso e quello occasionale, vengono spesso trattati in modo differente. Più precisamente, i collaboratori fissi stipulano un contratto con la casa editrice, e possono far parte del comitato di redazione, sia in veste di supervisore, sia in veste di direttore (generalizzazione più interna). I collaboratori occasionali invece, vengono pagati per ogni singolo lavoro svolto per la casa editrice.

La generalizzazione più esterna è di tipo totale-esclusiva, mentre quella più interna è di tipo parziale-sovrapposta, in quanto il direttore di un comitato è prima di tutto un supervisore.

Articolo

- Editoriale

- Generico

Questa generalizzazione è stata inserita sia per differire i due tipi di articoli, che sono pubblicati sulle uscite con frequenza molto diversa e che non possono essere sempre scritti dalle stesse persone, ma soprattutto in previsione della creazione dello schema logico, dove le due specializzazioni di Articolo, verrano poi trattate come due entità separate.

E' di tipo totale-esclusivo

Redazione

- Comitato

Con questa generalizzazione, viene resa esplicita la differenza tra il comitato di redazione, relativo soprattuto ai collaboratori, e la redazione stessa, intesa come entità che lavora su un periodico.

Una volta definite le entità, le associazioni che le legano sono venute quasi in modo banale.

Gli unici dubbi sono sorti su come gestire gli spazi pubblicitari, e su come esplicitare che a differenza di tutti gli articoli generici, ogni uscita di un periodico deve contenere uno e non più di un editoriale. Per quanto riguarda il primo problema, alla fine siamo giunti alla conclusione di utilizzare l'associazione “Pubblicato su”, solo per indicare il periodico su quale viene pubblicato un determinato spazio pubblicitario, mentre poi l'associazione “Compra”, viene utilizzata per tenere lo storico di tutti gli acquisti di spazi pubblicitari sui periodici da parte delle ditte. Per quanto riguarda il problema della pubblicazione degli editoriali, abbiamo preferito creare due associazioni diverse, che legano l'editoriale e gli articoli generici con un uscita. Precisamente l'associazione “Appare su” per gli editoriali, e l'associazione “Appare su supervisionato da” per gli articolo generici. Quest'ultima è un associazione ternaria, e oltre ad articoli e uscite, lega anche l'entità supervisore, in quanto gli articoli generici devono essere supervisionati prima di essere stampati.

Una volta ben definito lo schema, si è passati ad inserire le cardinalità, mentre alcune di esse sono risultate più evidenti, altre non sono state subito inserite correttamente, ma procedendo col progetto, quando siamo andati a creare lo schema relazionale, ci siamo degli errori e li abbiamo corretti.

Tavola dei Volumi

Tavola delle Operazioni

Tavole degli accessi

Per effettuare l'operazione bisogna accedere una volta in scrittura Articolo, per poter registrare tutti i dati di un articolo (nuova occorrenza).

In seguito dovremo accedere a Scritto Da in scrittura per in registrare una nuova occorrenza, accedendo una volta in lettura anche a Collaboratore per cercare il Nome dell'autore coinvolto nell'associazione.

Avendo una frequenza di 10 volte al giorno, otteniamo un totale di 20 accessi in scrittura e di 10 accessi in lettura.

Otteniamo quindi un totale di 30 accessi al giorno. Supponendo invece che gli accessi in scrittura abbiano un costo doppio otteniamo un totale di 50 accessi al giorno.

Per la seconda operazione iniziamo accedendo una volta in scrittura su Uscita, dove poter registrare la nuova occorrenza. Dobbiamo accedere una volta in scrittura anche a Viene Stampato dove inseriremo una nuova occorrenza. Accediamo una volta in lettura all'occorrenza di Periodico coinvolta nell'associazione.

Di seguito accederemo una volta in scrittura per ogni nuova occorrenza di Appare su – Supervisionato da,(in media dovrebbero essere 29, giacché abbiamo deciso a priori che la media di articoli è 30, incluso l'editoriale) nelle quali coinvolgeremo la nuova occorrenza di Uscita scritta in precedenza insieme alle occorrenze di Articolo le quali accederemo in lettura.

Dobbiamo accedere una volta in scrittura ad Appare_su per poter inserire la nuova occorrenza, accedendo quindi in lettura una volta anche all'occorrenza di Editoriale, che è coinvolta nell'associazione.

Con una frequenza di 3 volte alla settimana abbiamo un totale di 189 accessi in una settimana. Nel caso in cui gli accessi in scrittura abbiano un doppio costo otteniamo in totale è di 285 accessi in una settimana.

Per la terza operazione dobbiamo effettuare un accesso in lettura per ogni occorrenza di Uscita in media su un totale di 37 occorrenze (30 Quotidiani, 4 settimanali, 2 quindicinali, 1 mensile).

Di seguito accederemo in lettura, per ognuna delle precedenti occorrenze di Uscita, in media su 29 occorrenze di Appare su – Supervisionato da,per un totale di 1073 accessi quindi in lettura per lo stesso numero di occorrenze di Articolo.

Accederemo una in lettura, sempre in media su 37 occorrenze di Viene Stampato e in lettura allo stesso numero di occorrenze di Periodico.

Avendo bisogno anche delle informazioni sugli editoriali dobbiamo accedere una volta per ogni numero all'occorrenza di Appare_sui (37 volte) e infine lo stesso numero di volte in lettura,uno per ogni occorrenza di Editoriale coinvolta.

Otteniamo un totale di 2331 accessi al mese.

In questa quarta operazione dobbiamo iniziare accedendo in lettura su tutte le occorrenze di Articolo, da questa richiesta otterremo un numero di tuple variabile.

Ed è da questa variabile che dipende il numero di accessi alle varie occorrenze di Scritto da e all'occorrenza di Collaboratore coinvolta in ogni associazione.

Quindi il numero di accessi totale avrebbe la formula (13050+2X)*20.

Nell'operazione numero 5, accediamo una volta in lettura per ogni occorrenza di Uscita, quindi un totale di accessi intorno ai 225(essendo la media intorno a fine giugno (6*37).

Quindi per ognuna di queste 225 occorrenze dobbiamo accedere in lettura all'occorrenza di Viene Stampato a cui è annessa e quindi di conseguenza anche all'occorrenza di Periodico coinvolta.

Dovremo accedere in lettura alle varie occorrenze di Appare su – Supervisionato da, calcolato come (225*29) e conseguentemente in lettura allo stesso numero di occorrenze di Articolo collegate.

Per ogni occorrenza di Articolo dobbiamo accedere all'occorrenza di Scritto da e lo stesso numero di volte dovremo accedere all'occorrenza di Collaboratore.

Quindi avremo un totale di 267750 accessi al mese.

In questo caso dobbiamo accedere in lettura ad ogni occorrenza di Collaboratore, quindi 100 accessi (numero che noi abbiamo deciso a priori di Collaboratori fissi) lo stesso numero di accessi in lettura all'occorrenza di Stipulato e di conseguenza all'occorrenza di Contratto dove ci sono i dati necessari a svolgere il calcolo.

Avremo come risultato un totale di 300 accessi al mese.

Anche in questo caso il numero totale di accessi dipende dal risultato che avremo dopo aver effettuato un accesso su un'entità.

In questo caso accederemo inizialmente in lettura su ogni occorrenza di Collaboratore,(50 accessi, numero dei collaboratori fissi deciso a priori) dal numero di righe ottenuto da questa tabella, avremo il numero di accessi da effettuare in lettura all'occorrenza di Scritto da coinvolta in ogni associazione.

Per ottenere il risultato dobbiamo effettuare un'accesso in lettura a tutte le occorrenze di Pubblicato_su per ottenere la lista di tutte le pubblicità pubblicate

Quindi dovremo effettuare 740 (37 uscite * 20 spazi/uscita) accessi in lettura all'occorrenza di Spazio Pubblicitario.

In questo caso è stato deciso di calcolare il numero massimo, e non la media, che sarebbe stata calcolata a metà del mese, perché essendo una richiesta che viene effettuata solo una volta al mese ci sembra opportuno, prevedere che la stessa venga effettuata alla fine del mese, per avere un resoconto generale.

Riscontriamo un totale di 820 accessi al mese.

In questo caso cominciamo accedendo in lettura una volta ad ogni occorrenza di Uscita in media 5. Dopo quest'interrogazione dovremo accedere volte in lettura alle occorrenze di Appare su - Supervisionato da necessarie (5*29), quindi lo stesso numero di volte in lettura all'occorrenza di Articolo.

Quindi procediamo accedendo in lettura a 145 occorrenze di Scritto da e alle 145 occorrenze di Collaboratore a cui vi accederemo sempre in lettura.

Avremo in totale 2925 accessi al giorno.

Per quest'ultima operazione avremo bisogno di accedere una volta in lettura su tutte le occorrenze di Periodico, che avevamo deciso essere 4, successivamente in lettura a tutte le occorrenze di Viene Stampato, lo stesso numero di accessi necessario per l'entità Uscita. Successivamente devo effettuare per ogni uscita un accesso in lettura alle 29 occorrenze di Appare su – Supervisionato da cui farà parte, e un accesso in lettura allo stesso numero di occorrenze di Articolo.

Avendo come risultato un totale di 27908 accessi al giorno.

La quale operazione ci è sembrata piuttosto superflua nel nostro database, giacché avevamo deciso a priori una media di articoli per ogni pubblicazione. Un'altra banalità dell'operazione è che venga effettuata più volte al giorno, in effetti difficilmente la media degli articoli cambierà dalle 8 del mattino alle 8 di sera, dal momento che il calcolo viene effettuato sull'intero Database.

PROCEDIMENTO DI LAVORO SULLO

SCHEMA LOGICO

Per creare lo scema logico, quindi affrontare il processo ristrutturazione dello schema, abbiamo deciso di agire per gradi, seguendo le quattro fasi proposte nelle dispense:

Analisi delle ridondanze

Eliminazione delle generalizzazioni

Partizionamento/Accorpamento di entità e relazioni

Scelta degli identificatori

Non nascondiamo il fatto che anche seguendo questo schema, in certe occasioni è stato poi necessario ripetere un secondo processo di ristrutturazione, ripetendo qualcuno dei quattro punti sopra elencati.

Analisi delle ridondanze

Analizzando gli attributi, abbiamo notato di non avere ridondanze nello schema, e quindi si è scelto di lasciare gli stessi attributi dello schema concettuale. Un unico dubbio è sorto in quanto una delle prime stesure dello schema concettuale, conteneva l'attributo “n_componenti” dell'entità “Comitato”, attributo che poi nella realizzazione dello schema logico, sarebbe stato eliminato, in quanto derivabile da una queri che conta il numero dei supervisori di una redazione. Nella stesura finale dello schema concettuale però, quell'attributo è stato eliminato, in quanto ritenuto superfluo anche in quel frangente.

E' stato poi sciolto l'attributo composto “Indirizzo” dell'associazione “Ditta”, suddiviso in quattro attributi: “Città”, “CAP”, “Via”, “N_civico”.

Eliminazione delle generalizzazioni

Il nostro schema concettuale presenta cinque generalizzazioni, che sono state sciolte in modo differente, nello schema logico.

Collaboratore

- Occasionale

- Fisso

La generalizzazione è stata eliminata, creando due entità figlie “Occasionale” e “Fisso” legate all'entità padre “Collaboratore”, attraverso due associazioni con cardinalità massima 1: “IS A 1”, e “IS A 2”.

Si è scelta questa soluzione, in quanto i collaboratori sono a loro volta un entità padre di altre due entità, e sono legati, al contrartio di quelli occasionali, all'associazione “Stipulato”. Per queste differenze, è stato ritenuto più utile trattare le due entità figlie come due entità completamente a se stanti.

Fisso

- Direttore

- Supervisore

La generalizzazione è stata eliminata, creando due entità figlie “Direttore” e “Supervisore” legate all'entità padre “Fisso”, attraverso due associazioni con cardinalità massima 1: “IS A 3”, e “IS A 4”.

Il motivo che ci ha portati a questa soluzione, è lo stesso della gerarchia “Collaboratore-Occasionale-Fisso”, in quanto visto che il direttore è a sua volta un supervisore, è solo quest'ultimo che partecipa alle associazioni “Scritto da 2” con “Editoriale” e “Appare su supervisionato da” con articolo generico e uscita. Quindi, per quanto appena detto, anche in questo caso è stato ritenuto più utile trattare le due entità figlie come due entità completamente a se stanti.

Redazione

- Comitato

Anche in questo caso la generalizzazione è stata eliminata creando un associazione che lega l'entità padre con l'entità figlia, per la precisione con l'entità “Fa parte”.

Il motivo che ci ha fatto scegliere questa soluzione è dovuto al fatto che il comitato partecipa alle associazioni relative al direttore e al supervisore, quindi come è stato scritto prima trattando dello schema concettuale, il comitato è direttamente collegato con i collaboratori fissi, mentre la redazione è collegata attraverso l'associazione “Lavora al” ai periodici.

Per questo anche in questo caso abbiamo preferito trattare le due entità come entità separate e indipendenti.

Periodico

- Quotidiano

- Settimanale

- Quindicinale

- Mensile

In questo caso la generalizzazione è stata sciolta aggiungendo un semplice attributo “Tipo” all'entità padre. Questo perché i vari tipi di periodici pubblicati, partecipano allo stesso modo a tutte le stesse associazioni, quindi ci è sembrato inutile mantenere quattro entità superflue.

Articolo

- Generico

- Editoriale

L'eliminazione di questa gerarchia viene trattata nel paragrafo seguente, in quanto nella sua versione finale, vengono create due entità separate, che sostituiscono l'entità padre di partenza.

Partizionamento/Accorpamento di entità e relazioni

Nel passare dallo schema concettuale a quello logico, solo in un caso abbiamo avuto la necessità di dover creare due nuove entità, questo è avvenuto nell'eliminazione della gerarchia:

Articolo

- Generico

- Editoriale

Nello schema logico, il tutto viene trasformato in due entità separate e indipendenti, anche se simili tra loro:

“Art_generico” collegata alle associazioni “Scritto da 1” e “Appare su supervisionato da”

“Editoriale” collegata alle associazioni “Scritto da 2” e “Appare su”

Questa soluzione, è dovuta al fatto che le due entità anche se simili fra loro, presentano delle diversità di fondo, che le rendono impossibili da essere trattate insieme.

Nello specifico, anche se entrambe le entità sono considerate come articoli, gli articoli generici possono essere scritti da qualsiasi collaboratore, mentre gli editoriali possono essere scritti solo dai supervisori.

Da qui la necessita delle due associazioni “Scritto da 1” e “Scritto da 2”.

Poi c'è il duplice problema che ogni uscita deve avere almeno e non più di un editoriale, mentre può avere qualsiasi numero di articoli, e che l'editoriale essendo scritto già da un supervisore, non ha bisogno di essere supervisionato prima di andare in stampa, cosa non valida per un articolo generico.

Da qui il bisogno di inserire le due associazioni “Appare su” e “Appare su pubblicato da”.

Scelta degli identificatori primari

Controllando gli identificatori primari, abbiamo deciso di lasciare, quando possibile, gli stessi dello schema concettuale, è stato però necessario calcolare quelli nuovi nelle parti dello schema che hanno subito una ristrutturazione.

Analizzando le nuove entità derivate dall'eliminazione delle gerarchie, sono stati presi come identificatori delle chiavi esterne.

Nel caso delle creazione delle due nuove entità “Art_generico” e “Editoriale”, è stata lasciata la chiave esterna presente nella vecchia entità padre “Articolo”, solo è stata ripetuta per entrambe le due nuove entità.

NORMALIZZAZIONE

ENTITÀ

REDAZIONE (Cod, N_Ufficio)

CONTRATTO (Tipo, Retribuzione, Bonus, Ferie, Requisiti)

DITTA (Cod, Nome, Tel, CAP, Città, Via, N_Civico)

SPAZIO PUBBLICITARIO (Cod, Tipo, Prezzo, N/B)

PERIODICO (Nome, Genere, Tipo)

USCITA ( Numero, Nome_P, Tiratura, Data, N_Pagine, Prezzo)

COMITATO (Cod_R, N_Componenti)

COLLABORATORE (Cod_Fiscale, Nome, Cognome, Data_Nascita,

Telefono)

COLLABORATORE_OCCASIONALE (Cod_Fiscale,

Data_Ultimo_Lavoro)

COLLABORATORE_FISSO (Cod_Fiscale)

SUPERVISORE (Cod_Fiscale, Area)

DIRETTORE (Cod_Fiscale)

ART_GENERICO (Titolo, CF_Autore, Area, Lunghezza_Testo)

EDITORIALE (Titolo, CF_Autore, Lunghezza_Testo)

ASSOCIAZIONI

LAVORA PER:

COLLABORATORE (Cod_Fiscale, Nome, Cognome, Data_Nascita, Telefono)

REDAZIONE (Cod, N_Ufficio)

LAVORA PER (Cf_Collaboratore, Cod_Redazione, Dalla_Data)

PUBBLICATO SU:

SPAZIO_PUBBLICITARIO (CodD, Tipo, Prezzo, B/N, Nome_Periodico)

PERIODICO (Nome, Genere, Tipo)

COMPRA:

SPAZIO_PUBBLICITARIO (Cod, Tipo, Prezzo, B/N)

DITTA (Cod, Nome, Tel, CAP, Città, Via, N_Civico)

COMPRA (Sp_Cod, D_Cod, N_Ripetizioni, Dalla_Data, Alla_Data)

LAVORA AL:

PERIODICO (Nome, Genere, Tipo, Cod_Redazione)

REDAZIONE (Cod, N_Ufficio)

FA PARTE:

COMITATO (Cod_Redazione)

REDAZIONE (Cod, N_Ufficio)

DEL 1:

DIRETTORE (Cf_Direttore, Cod_Redazione, Dalla_Data)

COMITATO (Cod_Redazione)

DEL 2:

SUPERVISORE (Cf_Supervisore, Area, Cod_Redazione, Dalla_Data)

COMITATO (Cod_Redazione)

STIPULATO:

FISSO (Cod_Fiscale, Tipo, Data_Stipulazione)

CONTRATTO (Tipo, Retribuzione, Bonus, Ferie, Requisiti)

IS A 1:

COLLABORATORE (Cod_Fiscale, Nome, Cognome, Età, Data_Nascita, Telefono)

COLLABORATORE_OCCASIONALE (Cod_Fiscale, Data_Ultimo_Lavoro)

IS A 2:

COLLABORATORE (Cod_Fiscale, Nome, Cognome, Età, Data_Nascita, Telefono)

COLLABORATORE_FISSO(Cod_Fiscale)

IS A 3:

FISSO (Cod_Fiscale)

DIRETTORE (Cod_Fiscale)

IS A 4:

FISSO (Cod_Fiscale)

SUPERVISORE (Cod_Fiscale, Area)

VIENE STAMPATO:

USCITA ( Numero, Nome_P, Tiratura, Data, N_Pagine, Prezzo)

PERIODICO (Nome, Genere, Tipo)

SCRITTO DA 1:

ART_GENERICO (Cf_Autore, Titolo, Area, Lunghezza, Testo)

COLLABORATORE (Cod_Fiscale, Nome, Cognome, Età, Data_Nascita, Telefono)

SCRITTO DA 2:

EDITORIALE (CF_Autore, Titolo, Lunghezza, Testo)

SUPERVISORE (Cod_Fiscale, Area)

APPARE SU SUPERVISIONATO DA:

ART_GENERICO(CF_Autore, Titolo, Area, Lunghezza, Testo)

SUPERVISORE (Cod_Fiscale, Area)

USCITA ( Numero, Nome_P, Tiratura, Data, N_Pagine, Prezzo)

APPARE_SU_SUPERVISIONATO_DA(Titolo_Articolo, CF_Autore, Numero_Uscita,

Nome_Periodico, CF_Supervisore, Alla_Pagina)

APPARE_SU:

EDITORIALE (CF_Autore, Titolo, Lunghezza, Testo)

USCITA ( Numero, Nome_P, Tiratura, Data, N_Pagine, Prezzo,

CF_Autore_Editoriale, Titolo_Editoriale, Alla_Pagina)

SCHEMA RELAZIONALE

COLLABORATORE (Cod_Fiscale, Cognome, Area_di_Interesse, Data_di_Nascita,

Telefono)

REDAZIONE (Cod, N_Ufficio)

LAVORA_PER_REDAZIONE (CF_Collaboratore, Cod_Redazione, Dalla_Data)

SPAZIO_PUBBLICITARIO (SP_Cod, Tipo, B/N, Prezzo, Nome_Periodico)

il prezzo di uno spazio pubblicitario è inteso come costo di una singola uscita

il tipo di uno spazio pubblicitario mi indica grandezza e posizione nella pagina

B/N e’ un valore booleano per indicarmi se la pubblicità è in bianco e nero o a colori

PERIODICO (Nome, Genere, Tipo, Cod_Redazione)

il tipo di periodico indica la frequenza di uscita

il genere di un periodico indica il tema di cui trattano la gran parte degli articoli

DITTA (D_Cod, Nome, Telefono, CAP, Città, Via, N_Civico)

ACQUISTO_PUBBLICITÀ (SP_Cod, D_Cod, N_Ripetizioni, Dalla_Data, Alla_Data)

sp_cod = Spazio_Pubblicitario.SP_COD

d_cod = Ditta.D_COD

DIRETTORE (Cod_Fiscale, Cod_Redazione, Dalla_Data)

SUPERVISORE (Cod_Fiscale, Area, Cod_Redazione, Dalla_Data)

COLL_FISSO (Cod_Fiscale, Tipo_Contratto, Data_Stipulazione)

COLL_OCCASIONALE (Cod_Fiscale, Data_Ultimo_Lavoro)

CONTRATTO (Tipo, Retribuzione, Bonus, Ferie, Requisiti)

i requisiti di un contratto sono il numero minimo di anni di lavoro nel campo del giornalismo per poter stipulare questo tipo di contratto

i bonus sono i premi monetari per un determinato lavoro svolto

USCITA (Numero, Nome_Periodico, Prezzo, N_Pagine, Data, Tiratura,

CF_Autore_Editoriale, Titolo_Editoriale, Alla_Pagina)

ART_GENERICO (Titolo, CF_Autore, Tipo, Area, Lunghezza, Testo)

EDITORIALE (CF_Autore, Titolo, Lunghezza_Testo)

APPARE_SU_SUPERVISIONATO_DA (Titolo, CF_Autore, Numero_Uscita,

Nome_Periodico, CF_Supervisore, Alla_Pagina)

CODICI PER LA CREAZIONE DI TABELLE

Table: acquisto_pubblicita

CREATE TABLE acquisto_pubblicita

(

sp_cod int2 NOT NULL,

d_cod int2 NOT NULL,

n_ripetizioni int2 NOT NULL,

dalla_data date NOT NULL,

alla_data date NOT NULL,

CONSTRAINT acquisto_pubblicita_pkey PRIMARY KEY (sp_cod, d_cod),

CONSTRAINT acquisto_pubblicita_d_cod_fkey FOREIGN KEY (d_cod) REFERENCES ditta (d_cod),

CONSTRAINT acquisto_pubblicita_sp_cod_fkey FOREIGN KEY (sp_cod) REFERENCES spazio_pubblicitario (sp_cod))

Table: appare_su_supervisionato

CREATE TABLE appare_su_supervisionato

(

titolo char(20) NOT NULL,

cf_autore char(16) NOT NULL,

numero_uscita int2 NOT NULL,

nome_periodico char(20) NOT NULL,

cf_supervisore char(16) NOT NULL,

alla_pagina int2 NOT NULL,

CONSTRAINT appare_su_supervisionato_pkey PRIMARY KEY (titolo, cf_autore, numero_uscita, nome_periodico, cf_supervisore),

CONSTRAINT appare_su_supervisionato_cf_supervisore_fkey FOREIGN KEY (cf_supervisore) REFERENCES supervisore (cod_fiscale) ,

CONSTRAINT appare_su_supervisionato_nome_periodico_fkey FOREIGN KEY (nome_periodico, numero_uscita) REFERENCES uscita (nome_periodico, numero)

CONSTRAINT appare_su_supervisionato_titolo_fkey FOREIGN KEY (titolo, cf_autore) REFERENCES art_generico (titolo, cf_autore))

Table: art_generico

CREATE TABLE art_generico

(

titolo char(20) NOT NULL,

cf_autore char(16) NOT NULL,

lunghezza int2,

testo text NOT NULL,

area char(20) NOT NULL,

data date NOT NULL,

prezzo int2,

CONSTRAINT art_generico_pkey PRIMARY KEY (titolo, cf_autore),

CONSTRAINT art_generico_cf_autore_fkey FOREIGN KEY (cf_autore) REFERENCES collaboratore (cod_fiscale))

Table: coll_fisso

CREATE TABLE coll_fisso

(

cod_fiscale char(16) NOT NULL,

tipo_contratto char(20) NOT NULL,

data_stipulazione date NOT NULL,

CONSTRAINT coll_fisso_pkey PRIMARY KEY (cod_fiscale),

CONSTRAINT coll_fisso_cod_fiscale_fkey FOREIGN KEY (cod_fiscale) REFERENCES collaboratore (cod_fiscale) ,

CONSTRAINT coll_fisso_tipo_contratto_fkey FOREIGN KEY (tipo_contratto) REFERENCES contratto (tipo) )

Table: coll_occasionale

CREATE TABLE coll_occasionale

(

cod_fiscale char(16) NOT NULL,

data_ultimo_lavoro date,

CONSTRAINT coll_occasionale_pkey PRIMARY KEY (cod_fiscale),

CONSTRAINT coll_occasionale_cod_fiscale_fkey FOREIGN KEY (cod_fiscale) REFERENCES collaboratore (cod_fiscale))

Table: collaboratore

CREATE TABLE collaboratore

(

cod_fiscale char(20) NOT NULL,

nome char(20) NOT NULL,

cognome char(20) NOT NULL,

"data_di nascita" date NOT NULL,

tel int4,

CONSTRAINT collaboratore_pkey PRIMARY KEY (cod_fiscale))

Table: contratto

CREATE TABLE contratto

(

tipo char(20) NOT NULL,

bonus int2,

retribuzione int2 NOT NULL,

ferie int2,

requisiti text,

CONSTRAINT contratto_pkey PRIMARY KEY (tipo))

Table: direttore

CREATE TABLE direttore

(

cod_fiscale char(16) NOT NULL,

cod_redazione int2 NOT NULL,

"dalla data" date NOT NULL,

CONSTRAINT direttore_pkey PRIMARY KEY (cod_fiscale),

CONSTRAINT direttore_cod_fiscale_fkey1 FOREIGN KEY (cod_fiscale) REFERENCES coll_fisso (cod_fiscale)

CONSTRAINT direttore_cod_redazione_fkey FOREIGN KEY (cod_redazione) REFERENCES redazione (cod))

Table: ditta

CREATE TABLE ditta

(

d_cod int2 NOT NULL,

nome char(20),

cap int2,

citta char(20),

via char(20),

n_civico int2,

tel int4,

CONSTRAINT ditta_pkey PRIMARY KEY (d_cod))

Table: editoriale

CREATE TABLE editoriale

(

cf_autore char(16) NOT NULL,

titolo char(20) NOT NULL,

lunghezza int2,

testo text NOT NULL,

CONSTRAINT editoriale_pkey PRIMARY KEY (cf_autore, titolo),

CONSTRAINT editoriale_cf_autore_fkey FOREIGN KEY (cf_autore) REFERENCES supervisore (cod_fiscale))

Table: lavora_per_redazione

CREATE TABLE lavora_per_redazione

(

cf_collaboratore char(16) NOT NULL,

cod_redazione int2 NOT NULL,

dalla_data date NOT NULL,

CONSTRAINT lavora_per_redazione_pkey PRIMARY KEY (cf_collaboratore, cod_redazione),

CONSTRAINT lavora_per_redazione_cf_collaboratore_fkey FOREIGN KEY (cf_collaboratore) REFERENCES collaboratore (cod_fiscale)

CONSTRAINT lavora_per_redazione_cod_redazione_fkey FOREIGN KEY (cod_redazione) REFERENCES redazione (cod))

Table: periodico

CREATE TABLE periodico

(

nome char(20) NOT NULL,

genere char(20),

tipo char(20),

cod_redazione int2 NOT NULL,

CONSTRAINT periodico_pkey PRIMARY KEY (nome),

CONSTRAINT periodico_cod_redazione_fkey FOREIGN KEY (cod_redazione) REFERENCES redazione (cod))

Table: redazione

CREATE TABLE redazione

(

cod int2 NOT NULL,

n_ufficio int2,

CONSTRAINT redazione_pkey PRIMARY KEY (cod))

Table: spazio_pubblicitario

CREATE TABLE spazio_pubblicitario

(

sp_cod int2 NOT NULL,

tipo char(20),

"b/n" bool,

prezzo int2,

nome_periodico char(20) NOT NULL,

CONSTRAINT spazio_pubblicitario_pkey PRIMARY KEY (sp_cod),

CONSTRAINT spazio_pubblicitario_nome_periodico_fkey FOREIGN KEY (nome_periodico) REFERENCES periodico (nome))

Table: supervisore

CREATE TABLE supervisore

(

cod_fiscale char(16) NOT NULL,

area char(20) NOT NULL,

cod_redazione int2 NOT NULL,

dalla_data char(20) NOT NULL,

CONSTRAINT supervisore_pkey PRIMARY KEY (cod_fiscale),

CONSTRAINT supervisore_cod_fiscale_fkey FOREIGN KEY (cod_fiscale) REFERENCES coll_fisso (cod_fiscale)

CONSTRAINT supervisore_cod_redazione_fkey FOREIGN KEY (cod_redazione) REFERENCES redazione (cod) )

Table: uscita

CREATE TABLE uscita

(

numero int2 NOT NULL,

nome_periodico char(20) NOT NULL,

prezzo int2 NOT NULL,

n_pagine int2,

data date NOT NULL,

tiratura int2,

cf_autore_editoriale char(16) NOT NULL,

alla_pagina int2 NOT NULL,

titolo_editoriale char(25) NOT NULL,

CONSTRAINT uscita_pkey PRIMARY KEY (numero, nome_periodico),

CONSTRAINT uscita_cf_autore_editoriale_fkey FOREIGN KEY (cf_autore_editoriale, titolo_editoriale) REFERENCES editoriale (cf_autore, titolo)

CONSTRAINT uscita_nome_periodico_fkey FOREIGN KEY (nome_periodico) REFERENCES periodico (nome))

Codice SQL per implementare le Operazioni richieste

1. Inserire dati completi di un articolo

INSERT INTO art_generico (titolo,cf_autore,lunghezza,data,testo,area,prezzo)

VALUES ([titolo],[cf_autore],[lunghezza],[data],[testo],[area],[prezzo]);

Tale query consente l'inserimento dei dati di un articolo, creando una nuova tupla nello schema 'art_generico'. I campi delimitati da parentesi quadre (es. [titolo]) devono essere sostituiti con gli opportuni valori che si vogliono inserire nel database.

2. Inserire dati completi di un numero di un periodico

INSERT INTO uscita(numero,nome_periodico,prezzo,n_pagine,data,tiratura,cf_autore_editoriale,titolo_editoriale,alla_pagina)

VALUES

([numero],[nome_periodico],[prezzo],[n_pagine],[data],[tiratura],[cf_autore_editoriale],[titolo_editoriale],[alla_pagina]);

INSERT INTO art_generico (titolo,cf_autore,lunghezza,testo,area,prezzo)

VALUES ([titolo],[cf_autore],[lunghezza],[testo],[area],[prezzo]);

INSERT INTO appare_su_supervisionato (titolo, cf_autore, numero_uscita, nome_periodico, cf_supervisore, alla_pagina)

VALUES ([titolo], [cf_autore], [numero_uscita], [nome_periodico], [cf_supervisore], [alla_pagina])

INSERT INTO editoriale (cf_autore,titolo,lunghezza,testo)

VALUES ([cf_autore],[titolo],[lunghezza],[testo])

La query richiesta coinvolge l'inserimento di tuple nelle tabelle 'uscita', 'art_generico', 'appare_su_supervisionato' e 'editoriale'.

In uscita viene inserita una nuovo numero di un periodico,in editoriale l'editoriale , mentre in art_generico aggiungiamo gli articoli che intendiamo pubblicare (una INSERT per articolo ). Nella relazione 'appare_su_supervisionato' è necessario specificare 'titolo' e 'autore' degli articoli che si intendono pubblicare (tra quelli inseriti in art_generico) e l'uscita del periodico creato in precedenza oltre al supervisore che visiona ciascun articolo.

3.Data e indice dei periodici usciti nel novembre 2005

SELECT T1.nome_periodico AS periodico, T1.data AS data, T3.alla_pagina AS pag_gen, T2.titolo AS tit_gen

FROM uscita AS T1, art_generico AS T2, appare_su_supervisionato AS T3

WHERE

T3.titolo = T2.titolo AND T3.cf_autore = T2.cf_autore AND T1.nome_periodico = T3.nome_periodico

AND T1.numero = T3.numero_uscita AND T1.data between

'2005-11-01' and '2005-11-30';

La query restituisce l'indice degli articoli generici presenti nei periodici usciti nel novembre 2005. Gli editoriali non vengono considerati.

4.Autori degli articoli i cui titoli contengono una particolare parola(ad es "costituzione")

SELECT collaboratore.nome, collaboratore.cognome, art_generico. titolo

FROM art_generico, collaboratore

WHERE collaboratore.cod_fiscale = art_generico.cf_autore AND art_generico.titolo like '%costituzione%'

Tale query controlla i titoli degli articoli restituendo nome e cogome dell'autore e titolo completo dell'articolo che soddisfano la condizione di ricerca.

5.Numero degli articoli per ogni rivista che nell'anno corrente hanno un collaboratore occasionale

SELECT uscita.nome_periodico,uscita.numero, COUNT(art_generico.titolo)

FROM uscita, coll_occasionale, collaboratore, art_generico, appare_su_supervisionato

WHERE art_generico.cf_autore = collaboratore.cod_fiscale AND coll_occasionale.cod_fiscale = collaboratore.cod_fiscale AND art_generico.titolo = appare_su_supervisionato.titolo AND art_generico.cf_autore = appare_su_supervisionato.cf_autore AND uscita.numero = appare_su_supervisionato.numero_uscita AND uscita.nome_periodico =appare_su_supervisionato.nome_periodico AND uscita.data BETWEEN current_date-365 and current_date

GROUP BY uscita.nome_periodico,uscita.numero;

La query trova le uscite dei periodici e conta il numero di articoli per ogni pubblicazione scritti da collaboratori occasionali

6. Somma degli stipendi per i collaboratori fissi nel mese corrente

SELECT SUM( retribuzione + bonus)

FROM contratto, coll_fisso

WHERE coll_fisso.tipo_contratto = contratto.tipo;

Per ricavare la somma degli stipendi fissi del mese corrente vengono sommate le retribuzioni ( e gli eventuali bonus) di tutti i collaboratori fissi in quanto cio' che viene memorizzato nella basedati rappresenta l'attuale stato dei contratti stipulati.

7. Somma degli stipendi per i collaboratori occasionali nel mese corrente

SELECT SUM(art_generico.prezzo)

FROM coll_occasionale, collaboratore, art_generico

WHERE coll_occasionale.cod_fiscale = collaboratore. cod_fiscale

AND art_generico.cf_autore = collaboratore. cod_fiscale AND art_generico.data BETWEEN (current_date-30) AND current_date;

La funzione effettua una stima degli stipendi per i collaboratori occasionali sulla base dei precedenti trenta giorni.

8. Somma delle entrate pubblicitarie nel mese corrente

SELECT SUM(spazio_pubblicitario.prezzo * n_ripetizioni)

FROM spazio_pubblicitario, acquisto_pubblicita

WHERE spazio_pubblicitario.sp_cod = acquisto_pubblicita.sp_cod AND alla_data < current_date AND dalla_data > (current_date -30) ;

9.Elenco degli autori degli articoli pubblicati nella settimana corrente che sono collaboratori fissi.

SELECT collaboratore.*

FROM appare_su_supervisionato,coll_fisso, collaboratore, art_generico, uscita

WHERE art_generico.cf_autore = collaboratore.cod_fiscale AND coll_fisso.cod_fiscale = collaboratore.cod_fiscale AND art_generico.titolo = appare_su_supervisionato.titolo AND art_generico.cf_autore = appare_su_supervisionato.cf_autore AND uscita.numero = appare_su_supervisionato.numero_uscita AND uscita.nome_periodico =appare_su_supervisionato.nome_periodico

AND uscita.data BETWEEN (current_date - 7) AND current_date;

La query estrapola dai collaboratori fissi coloro che hanno scritto articoli che sono apparsi su un periodico nei sette giorni precedenti alla data attuale.

10.Numero medio di articoli per ogni tipo di rivista.

CREATE VIEW n_articoli_per_tipo

SELECT periodico.tipo, count(*)as n_articoli

FROM periodico, uscita

WHERE periodico.nome = uscita.nome_periodico

GROUP BY periodico.tipo;

SELECT avg(n_articoli)

FROM n_articoli_per_tipo:

La vista estrapola per ogni tipologia di periodico il numero di articoli presenti. La query calcola la media dei valori prodotti.

jacopo

2006-07-10T18:31:54