1. SPECIFICHE PROGETTUALI · Tesina Basi di Dati – Anno Accademico 1999/2000 GRUPPO 2: Baraldi...

Tesina Basi di Dati – Anno Accademico 1999/2000

GRUPPO 2: Baraldi F., Cassan W., Zanella F.

1

1. SPECIFICHE PROGETTUALI

Una nota agenzia giornalistica, con sede principale a Roma e sedi sussidiarie in molte città del mondo, intende ristrutturare i flussi d’informazione e la gestione dell’archivio delle notizie, in modo da permettere la creazione di una centrale informativa che, da un lato, sia a-limentata in tempo reale dai vari redattori sparsi per il mondo e dall’altro sia disponibile on-line per la consultazione da parte dei potenziali acquirenti dei servizi giornalistici dell’agenzia. ◊ GIORNALISTI, FOTOGRAFI E CAPIREDATTORI

Nel sistema informativo piatto che l’agenzia giornalistica vuole implementare, ogni persona riconosciuta come giornalista accreditato può contribuire al popolamento della base di dati centrale. Quindi in base al ruolo del giornalista accreditato (giornalista, fotografo, …) chiunque può contribuire sottoponendo materiale correlato (per esempio un fotografo può in-serire fotografie a sé stanti oppure fotografie correlate ad articoli), direttamente da ogni parte del mondo ove si trovi. E’ compito della redazione centrale, ove operano capo redattori per ogni categoria di interesse (politica, società, sport, cultura,...), vagliare il materiale proposto (inserito nel sistema dai giornalisti), eventualmente apportare correzioni, e renderlo pubblicabile per gli acquirenti dei servizi giornalistici. Ogni giornalista accreditato (giornalista, fotografo, …) afferisce ad un’unica sede d’agenzia. I giornalisti , i fotografi e i redattori sono descritti dagli attributi: cognome, nome, recapito, ruolo, indirizzo E-mail, numero di telefono e cellulare. ◊ NOTIZIE DI AGENZIA

Le notizie di agenzia vengono raccolte nel sistema centrale e sono redatte da un qual-siasi giornalista accreditato che le sottopone alla redazione centrale. I file contenenti le notizie non vengono memorizzati nella base di dati, ma vengono memorizzati i loro URL. La reda-zione centrale, eventualmente apportando correzioni, accetta il dispaccio e lo pubblica oppure lo archivia. Ogni notizia appartiene ad una certa categoria predefinita (politica, società, sport, cultura,...). Ai fini di individuare gli inviati più produttivi, viene tenuto il conto del numero di notizie proposte e del numero di notizie pubblicate di ciascun giornalista accreditato. Le notizie di agenzia hanno anche gli attributi (luogo, data ed autore). ◊ ARTICOLI

Gli articoli sono testi corredati da fotografie. I file contenenti gli articoli non vengono memorizzati nella base di dati, ma vengono memorizzati i loro URL. Vengono raccolti nel si-stema centrale e sono redatti da qualsiasi giornalista che li sottopone alla redazione centrale. La redazione centrale, eventualmente apportando correzioni, accetta l’articolo e lo pubblica, oppure lo conserva per utilizzi futuri.


GRUPPO 2: Baraldi F., Cassan W., Zanella F. 2

Ogni articolo appartiene ad una certa categoria predefinita (politica, società, sport, cultu-ra,...). Ai fini di identificare i giornalisti più produttivi, viene tenuto il conto del numero di ar-ticoli proposti e del numero di articoli pubblicati di ciascun giornalista accreditato. Un articolo può essere correlato, cioè può far riferimento ad altri articoli già esistenti e può avere associate una o più fotografie, ogni articolo è caratterizzato da una serie di parole chia-ve. Gli articoli hanno anche gli attributi (luogo, data ed autore). ◊ FOTOGRAFIE

Le fotografie sono immagini in formato digitale; i file contenenti le fotografie non vengono memorizzati nella base di dati, ma vengono memorizzati i loro URL. Possono essere associate ad uno o più articoli, oppure essere parte di un progetto fotografico, oppure ancora essere foto a sé stante. Ogni fotografia è caratterizzata anche da una serie di parole chiave ed è corredata da una didascalia. Le fotografie raccolte dalla redazione centrale vengono pubblicate oppure vengono archiviate per utilizzi futuri, a giudizio della redazione centrale. Ogni fotografia appartiene ad una certa categoria predefinita (politica, società, sport, cultu-ra,...). Ai fini di identificare i fotografi più produttivi, viene tenuto il conto del numero delle fotografie proposte e del numero di fotografie pubblicate di ciascun fotografo accreditato. Le fotografie hanno anche gli attributi (luogo, data ed autore). ◊ AREE GEOGRAFICHE

Esiste una classificazione in aree geografiche dei luoghi. Ogni notizia, articolo, foto-grafia quindi è associata ad una determinata area geografica (es. Nord America, Sud America, Europa, Italia, Nord Africa, Africa Centrale, Sud Africa, Medio oriente, Estremo Oriente, O-ceania, Regioni Artiche,...). ◊ ACQUISTI DEI SERVIZI GIORNALISTICI L’acquisizione dei servizi (articoli o notizie) giornalistici dell’agenzia da parte di un qualsiasi utente, pubblico o privato, avviene tramite la sottoscrizione di un abbonamento. Si hanno due tipi di abbonamento: PerCategoria e Cumulativi. Tramite un abbonamento PerCategoria è possibile ricevere, per l’intera durata dell’abbonamento, tutto il materiale pubblicato dall’agenzia appartenenti ad una certa catego-ria. L’Agenzia mantiene traccia dei servizi inviati per un certo abbonamento; inoltre, nel caso in cui l’utente sia un mezzo di comunicazione, si memorizza anche se il servizio è successi-vamente pubblicato o meno. Tramite un abbonamento Cumulativo è possibile consultare tutti i titoli delle notizie e degli articoli pubblicati dall’agenzia appartenenti ad una certa categoria e prelevare tra questi quelli di interesse; nell’abbonamento è specificato il numero totale di servizi (notizie ed arti-coli) che si possono prelevare. Con questo tipo di abbonamento si possono prelevare giornal-mente sino ad un massimo di 10 articoli 20 notizie. Uno stesso utente non può sottoscrivere due abbonamenti per la stessa categoria.



3

◊ SICUREZZA Il prototipo effettua verifica di autorizzazione di accesso degli utenti (giornalisti, utenti,...). ◊ VOLUME DEI DATI Nel seguito sono riportate alcune indicazioni che possono essere seguite per la scelta del volume dei dati; in alternativa, si possono fissare e considerare altri valori. D’altra parte il volume dei dati riportato riguarda solo i concetti principali riportati nelle specifiche. In base allo schema E/R risultante dalla interpretazione della descrizione si possono fissare opportuni valori per il volume dei dati degli altri concetti. Lo stesso discorso vale per le operazioni e per la loro frequenza. Volume dei dati delle principali entità

ENTITA VOLUME Sedi 55 Categorie di Interesse 25 Aree Geografiche 20 Giornalisti 1’000 Fotografi 300 Capi Redattori 200 Notizie 10’000’000 Articoli 1’500’000 Progetti Fotografici 200 Fotografie 4’000’000 Parole Chiave 300 Abbonamenti PerCategoria 500 Abbonamenti Cumulativi 400

◊ OPERAZIONI Le operazioni previste per il sistema sono: 1. Visualizzare le ultime N notizie pubblicate. (Frequenza: 1’000’000 di accessi giornalieri) 2. Ricerca dei titoli degli ultimi N servizi giornalistici pubblicati dall’agenzia che riguardano

una certa categoria di interesse per una determinata area geografica o di una determinata sede dell’agenzia.

(Frequenza: 10’000 accessi giornalieri).



3. Ricerca e prelievo degli articoli pubblicati dall’agenzia che fanno riferimento ad una de-terminata parola chiave. Il riferimento può essere diretto (cioè l’articolo descritto dalla parola chiave oppure associato ad una fotografia con quella parola chiave) o indiretto (cioè l’articolo correlato ricorsivamente ad un altro articolo riferito a quella parola chia-ve).

(Frequenza: 1’000 accessi giornalieri). 4. Inserimento di notizie di agenzia. (Frequenza:2’000 volte al giorno). 5. Inserimento di articoli con parole chiave, articoli correlati e fotografie correlate. (Frequenza:300 volte al giorno). 6. Visualizzazione dei giornalisti che hanno pubblicato il maggior numero di notizie ed arti-

coli. (Frequenza: 1 volta al mese).



5



3. PROGETTO CONCETTUALE

Riportiamo di seguito la descrizione delle entità e delle associazioni introdotte nello schema Entity_Relationship: ◊ GIORNALISTI, FOTOGRAFI E CAPI REDATTORI

L’entità UTENTI, identificata dallo username e caratterizzata dagli attributi semplici password, nome, telefono, cellulare, e-mail e dall’attributo composto indirizzo, è stata intro-dotta al fine di poter gestire la sicurezza negli accessi alla base di dati e per poter visualizzare maschere d’ingresso diversificate a seconda dell’utente. UTENTI ha come sotto entità PER-SONALE e CLIENTI determinando così una gerarchia a copertura totale ed esclusiva. PER-SONALE, identificata dal codice fiscale e caratterizzata dall’attributo semplice cognome, ha come sottoentità GIORNALISTIACCREDITATI, CAPIREDATTORI e GESTORICLIENTI che determinano una gerarchia a copertura totale ed esclusiva. L’entità PERSONALE contie-ne le informazioni relative ai dipendenti dell’agenzia. I GESTORICLIENTI hanno la facoltà di inserire nuovi clienti e di sottoscrivere o rinnovare gli abbonamenti dei clienti che ne fanno richiesta. I CAPIREDATTORI, identificati dal codice fiscale ereditato da PERSONALE, appartengono ad un’unica categoria. Questo legame è espresso tramite l’associazione many to one DELLA determinando così la categoria di materiale che ogni capo redattore può archiviare o pubblica-re. L’entità GIORNALISTIACCREDITATI è in relazione con l’entità SEDIAGENZIA (identifi-cata dal nome e dalla città in cui si trova), tramite l’associazione many to one FARIFERI-MENTO che indica la sede dell’agenzia a cui il singolo giornalista accreditato afferisce, men-tre i capi redattori afferiscono tutti alla sede principale di Roma. I GIORNALISTIACCRE-DITATI, identificati dal codice fiscale ereditato dall’entità padre e dallo username ereditato dall’entità UTENTE, si suddividono a loro volta nelle sotto entità: GIORNALISTI e FOTO-GRAFI generando così una copertura parziale ed esclusiva. I GIORNALISTI sono in relazio-ne con le entità NOTIZIE ed ARTICOLI tramite le associazioni one to many REDIGEN e REDIGEA, mentre i FOTOGRAFI sono in relazione con l’entità FOTOGRAFIE tramite l’associazione one to many SCATTA. Tali associazioni permettono di individuare l’autore. Per completare la descrizione delle entità sopra citate sono stati utilizzati anche i seguenti at-tributi: - GIORNALISTI: #not.proposte, #not.pubblicate, #art. proposti e #art. pubblicati; - FOTOGRAFI: #foto. proposte e #foto. pubblicate; - GIORNALISTIACCREDITATI: ruolo del giornalista accreditato ( giornalista, fotografo, ecc...) - SEDIAGENZIA: l’attributo composto indirizzo; - CATEGORIA: l’attributo semplice categoria. ◊ NOTIZIE, ARTICOLI e FOTOGRAFIE

Si è creata l’entità MATERIALE identificata dal titolo e caratterizzata dagli attributi semplici Url, datacreazione e tipomateriale che raccoglie tutto il materiale (notizie, articoli, fotografie,...) che i giornalisti accreditati hanno inviato alla sede principale dell’agenzia da ogni parte del mondo; il luogo e l’area geografica di provenienza sono determinati



7

dall’associazione many to one PROVIENE che lega MATERIALE all’entità AREEGEO-GRAFICHE, mentre la categoria di appartenenza e determinata dalla relazione many to one DELLA che lega MATERIALE con CATEGORIA. MATERIALE ha come sotto entità FOTOGRAFIE, PROGETTIFOTOGRAFICI, NOTIZIE ed ARTICOLI determinando così una gerarchia a copertura totale ed esclusiva. Nell’entità ARTICOLI è presente un’associazione anello many to many che esprime il riferimento che un articolo può fare nei confronti di altri articoli già esistenti nella base di dati. FOTOGRAFIE è in relazione con ARTICOLI tramite l’associazione molti a molti CONTIENE, che esprime le fotografie contenute in un determinato articolo, e con PROGETTIFOTOGRAFICI con l’associazione many to many IN che esprime la composizione di ogni singolo progetto foto-grafico. MATERIALE dà inoltre origine ad un’ulteriore gerarchia a copertura totale ed esclusiva ca-ratterizzata dalle sotto entità MATERIALEPUBBLICATO, MATERIALEARCHIVIATO e MATERIALEDAESAMINARE. MATERIALEPUBBLICATO e MATERIALEARCHIVIA-TO sono in relazione con l’entità CAPIREDATTORI rispettivamente con le associazioni many to one PUBBLICA ed ARCHIVIA che esprimono così il legame fra il materiale ed il capo redattore che si è occupato della pubblicazione/archiviazione. Per completare la descrizione delle entità sopra citate sono stati utilizzati anche i seguenti at-tributi: -ARTICOLI: l’attributo multiplo parolechiave; -FOTOGRAFIE: l’attributo multiplo parolechiave e l’attributo semplice didascalia; -PROGETTIFOTOGRAFICI: l’attributo semplice descrizione; -MATERIALEPUBBLICATO: l’attributo semplice datapubblicazione; -PUBBLICA ed ARCHIVIA: l’attributo semplice corretto; -PROVIENE: l’attributo semplice luogo; -AREEGEOGRAFICHE: gli attributi semplici nome e descrizione; ◊ CLIENTI ED ABBONAMENTI

Si è creata l’entità CLIENTI, identificata da cf_piva cioè dal codice fiscale o dalla par-tita iva a seconda che il cliente sia un privato o un mezzo di comunicazione, in cui sono me-morizzati tutti i clienti che sottoscrivono un abbonamento. CLIENTI ha come sotto entità PRIVATI, con attributo semplice cognome, e MEZZIDICOMUNICAZIONE, generando così una gerarchia a copertura totale ed esclusiva. CLIENTI è collegata con ABBONAMENTI tramite l’associazione one to many SOTTOSCRIVE che esprime il legame tra il singolo cliente e l’abbonamento da egli sottoscritto. L’entità ABBONAMENTI è identificata esternamente da cf_piva e categoria (chiavi rispetti-vamente delle entità CLIENTI e CATEGORIA); in questo modo un cliente può sottoscrivere un solo abbonamento per categoria. Questa entità ha come sotto entità PERCATEGORIA, con attributo semplice #giornidurata che indica la durata in giorni dell’abbonamento, e CU-MULATIVI, con attributi semplici #servizi che indica il numero di servizi totali dell’abbonamento e #serviziprelevati che indica il numero dei servizi prelevati, generando co-sì una gerarchia a copertura totale ed esclusiva. Gli abbonamenti PERCATEGORIA sono in associazione con MATERIALEPUBBLICATO tramite la relazione many to many SERVI-ZIINVIATI, con attributo semplice pubblicato(memorizza se il sevizio prelevato è stato in seguito pubblicato dal mezzo di comunicazione), in cui possiamo recuperare per ogni abbo-namento i servizi richiesti dal cliente. Gli abbonamenti CUMULATIVI sono in associazione con l’entità DISPONIBILITAGIORNALIERE tramite la relazione one to many HA. L’entità DISPONIBILITAGIORNALIERE identificata da cf_piva, categoria e giorno serve per verifi-



care se il prelievo giornaliero di articoli e notizie da parte di un cliente eccede il limite dato dai vincoli . DISPONIBILITAGIORNALIERE è in associazione con le entità ARTICOLI-PUBBLICATI e NOTIZIEPUBBLICATE rispettivamente tramite le relazioni DISPONIBI-LITADIARTICOLI e DISPONIBILITADINOTIZIE. Per limitare il prelievo di articoli e noti-zie è stata imposta la cardinalità fra DISPONIBILITAGIORNALIERE - DISPONIBILITA-DIARTICOLI e DISPONIBILITAGIORNALIERE - DISPONIBILITADINOTIZIE rispetti-vamente di (0,10) e (0,20). ARTICOLIPUBBLICATI è un subset delle entità MATERIALEPUBBLICATO ed ARTI-COLI rappresentando così un caso di ereditarietà di un entità da due entità (la cui traduzione in schema relazionale è spiegata successivamente). La medesima considerazione vale anche per NOTIZIEPUBBLICATE. Essendo le schema Entity Relationship un modello di rappresentazione statica di dati, per po-ter conservare le informazioni riguardanti gli abbonamenti sottoscritti in passato da ogni cliente, si è presentata la necessità di introdurre l’entità ABBONAMENTISCADUTI, identi-ficata da cf_piva, categoria e datainizio, che non è in associazione con nessun’altra entità pre-sente nello schema ma viene utilizzata solo come archivio storico a beneficio dell’agenzia. Per completare la descrizione delle entità sopra citate sono stati utilizzati anche i seguenti at-tributi: - ABBONAMENTI: attributo semplice datainizio



9

4. PROGETTO LOGICO 4.1 RIELABORAZIONE DELLO SCHEMA ENTITY RELATIONSHIP

Di seguito è riportata la rielaborazione delle gerarchie e dei subset introdotti nello

schema Entity Relationship: • Traduzione della gerarchia totale ed esclusiva UTENTI:

Non essendo l’entità UTENTI in relazione con nessun’entita, ed essendo la gerarchia a coper-tura totale ed esclusiva, si è optato per un ‘collasso verso il basso’.

U T E N T I

P E R S O N A L E C L I E N T I

U S E R N A M EP A S S W O R D

C F _ P I V A

D O P O I L C O L L A S S O V E R S O I L

P E R S O N A L E

C L I E N T IC F _ P I V A



( t , e )

I N D I R I Z Z O V I A

C I T T A

P R O V I N C I A C A P

S T A T O

N _ C I V I C O

N O M ET E LE - M A I L


C I T T A


S T A T O

N _ C I V I C O


C I T T A


S T A T O

N _ C I V I C O

N O M E

E _ M A I LT E L

N O M E

T E L

E - M A I L

C F

C F



• Traduzione della gerarchia totale ed esclusiva PERSONALE:

Non essendo l’entità PERSONALE in relazione con nessun’altra entità, ed essendo la gerar-chia a copertura totale ed esclusiva, si è effettuato un ‘collasso verso il basso’ senza introdur-re ridondanza di dati. • Traduzione della gerarchia parziale ed esclusiva GIORNALISTIACCREDITATI

Essendo la gerarchia a copertura parziale non è possibile effettuare il ‘collasso verso il bas-so’; anche la soluzione ‘collasso verso l’alto’ è da scartare in quanto se si riportassero tutti gli attributi sull’entità padre si otterrebbero un gran numero di valori NULL; inoltre tutte e tre le

P E R S O N A L E

G IO R N A L I S T I A C C R E D IT A T I

C A P I R E D A T T O R I

E - M A ILT E L . C E L L .

IN D IR IZ Z O

V IA

N _ C IV IC O

C IT T AP R O V IN C IA C A P

S T A T O

E F F E T T U A N D O I L C O L L A S S O V E R S O IL B A S S O

N O M E C O G N O M E C F

G IO R N A L IS T I A C C R E D IT A T I


IN D I R IZ Z ON _ C IV IC O


S T A T O


C A P I R E D A T T O R I


IN D IR IZ Z ON _ C IV IC O


S T A T O


V IA

V IA


U S E R N A M E

U S E R N A M E

P A S S W O R D

P A S S W O R D

G E S T O R I C L IE N T I

( t , e )

G E S T O R I C L IE N T I


IN D IR IZ Z ON _ C IV IC O


S T A T O


V IAU S E R N A M EP A S S W O R D

F O T O G R A F IG I O R N A L I S T I

# N O T P R O P O S T E# N O T P U B B L I C A T E

# A R T P R O P O S T I# A R T P U B B L I C A T I

# F O T O P R O P O S T E# F O T O P U B B L I C A T E

G I O R N A L I S T I A C C R E D I T A T I

T E L .C E L L .

I N D I R I Z Z ON _ C I V I C O

C I T T AP R O V I N C I A C A P

S T A T O


E - M A I L

V I A

( p , e )

C O N I L M A N T E N I M E N T O D E L L E E N T I T A

G I O R N A L I S T I A C C R E D I T A T I

T E L .C E L L .

I N D I R I Z Z ON _ C I V I C O


S T A T O

E - M A I L

C FC O G N O M E N O M E

G I O R N A L I S T I F O T O G R A F I# F O T O P R O P O S T E

# F O T O P U B B L I C A T E

# N O T P R O P O S T E# N O T P U B B L I C A T E

# A R T P R O P O S T I# A R T P U B B L I C A T I

( 0 , 1 ) ( 0 , 1 )

( 1 , 1 ) ( 1 , 1 )

R U O L OV I A

R U O L O

U S E R N A M E

P A S S W O R D

U S E R N A M E

P A S S W O R D



11

entità sono in associazione con altre entità. Quindi in questo caso si è optato per un ‘mante-nimento delle entità’. • Traduzione della gerarchia totale ed esclusiva MATERIALE1

In questo caso

si è proceduto con il ‘mantenimento delle entità’ essendo ARTICOLI in relazione con FO-TOGRAFIE tramite l’associazione CONTIENE e FOTOGRAFIE in relazione con PROGET-TIFOTOGRAFICI tramite l’associazione IN per cui non si poteva utilizzare il ‘collasso verso l’alto’. Inoltre non è stato possibile considerare il ‘collasso verso il basso’ essendo materiale l’entità padre di un’altra gerarchia. • Traduzione della gerarchia totale ed esclusiva MATERIALE2

Anche in questo caso, analogamente a quanto visto sopra, si è proceduti con un ‘mantenimen-to delle entità’ essendo MATERIALE entità padre anche della precedente gerarchia. E’ scon-

D A T A _ C R E A Z IO N E

T IT O L O

M A T E R I A L E

U R L

N O T I Z E F O T O G R A F I EA R T I C O L IP R O G E T T I

F O T O G R A F I C I

P A R O L E C H IA V E P A R O L E C H IA V E

( 0 , N ) ( 0 , N )

D E S C R IZ IO N E

D ID A S C A L IA


D A T A _ C R E A Z IO N E

T IT O L O

M A T E R I A L E

U R L

N O T I Z E A R T I C O L I

P A R O L E C H IA V E

( 0 , N )

F O T O G R A F I E

( 0 , N )

D ID A S C A L IA

P R O G E T T I F O T O G R A F I C I

D E S C R IZ IO N E

( t , e )

( 0 , 1 )

( 0 , 1 )( 0 , 1 )

( 0 , 1 )

( 1 , 1 ) ( 1 , 1 ) ( 1 , 1 ) ( 1 , 1 )

T IP O _ M A T E R IA L E


T IT O L O

M A T E R I A L E

M A T E R I A L E P U B B L I C A T O

M A T E R I A L E D A E S A M I N A R E

M A T E R I A L E A R C H I V I A T O ( t , e )

D A T A _ P U B B L IC A Z IO N E

U R L D A T A _ C R E A Z IO N E

C O N I L M A N T E N I M E N T O D E L L E E N T I T A '

M A T E R I A L E

M A T E R I A L E A R C H I V I A T O

M A T E R I A L E P U B B L I C A T O

( 1 , 1 )

( 0 , 1 )

( 1 , 1 ) T IT O L O

U R L D A T A _ C R E A Z IO N E

D A T A _ P U B B L IC A Z IO N E





veniente effettuare un ‘collasso verso l’alto’ essendo MATERIALEPUBBLICATO entità pa-dre di ARTICOLIPUBBLICATI e NOTIZIEPUBBLICATE. • Traduzione della gerarchia totale ed esclusiva CLIENTI

In questo caso si è ritenuto di poter procedere con ‘collasso verso l’alto’ non essendo PRI-VATO e MEZZODICOMUNICAZIONE in relazione con nessun’altra entità e visto che co-gnome è l’unico attributo che può dare origine a valori NULL. • Traduzione della gerarchia totale ed esclusiva ABBONAMENTI

N _ C I V I C O

C L IE N T I

P R IV A T I M E Z Z I D I C O M U N IC A Z IO N E

( t ,e )

E - M A I L

C O G N O M E

IN D IR IZ Z O


S T A T O

C F / P I V AN O M E

T E LV I A

D O P O IL C O L L A S S O V E R S O L 'A L T O

N _ C I V I C O

C L IE N T I

E - M A I L

IN D IR IZ Z O


S T A T O

C F / P I V AN O M E

T E LV I A

P R I V A T O _ M E Z C O M U N I C A Z I O N E



( 0 , 1 ) C O G N O M E

A B B O N A M E N T I

C U M U L A T I V IP E R C A T E G O R I A

C A T E G O R IA

D A T A _ IN IZ IO

# G IO R N I D U R A T A

# S E R V IZ I

# S E R V IZ I P R E L E V A T I

C F _ P IV A


A B B O N A M E N T I

C A T E G O R IA

D A T A _ IN IZ IO

C F _ P IV A

P E R C A T E G O R I A

# G IO R N I D U R A T A

C U M U L A T I V I

# S E R V IZ I

# S E R V IZ I P R E L E V A T I

( 0 , 1 ) ( 0 , 1 )

( 1 , 1 ) ( 1 , 1 )

( t , e )



13

Nella presente gerarchia si è ritenuto di poter procedere con un ‘mantenimento delle entità’ essendo ABBONAMENTI in relazione con CLIENTI mediante l’associazione SOTTO-SCRIVE; inoltre con la soluzione ‘collasso verso l’alto’ avremmo ottenuto molti valori NULL negli attributi #giornidurata, #serviziprelevati, #servizi. 4.2 PROGETTAZIONE LOGICA RELAZIONALE

Questa seconda fase del progetto logico è legata al modello relazionale e consiste nell’eliminazione degli attributi composti e ripetuti perché entrambi violano la prima forma normale. • Traduzione dell’attributo composto INDIRIZZO

L’attributo composto indirizzo, che caratterizza le entità UTENTI e SEDIAGENZIA, è stato tradotto nei suoi componenti come attributi semplici preferendo così il mantenimento dell’articolazione dei vari componenti a scapito di una semplificazione dello schema E-R qua-lora si fosse tradotto indirizzo come attributo semplice. • Traduzione dell’attributo multiplo PAROLE CHIAVE

E N T IT A

E N T IT A

N_ CIV ICO IN D IR IZ Z O

CITTAPRO V INCIA CA P

S TA TO

V IAV IA

S TA TOCA PPRO V INCIA

CITTAV IA

N_ CIV ICO

D O P O L A T R A D U Z IO N E D E L L 'A T T R IB U T O C O M P O S T O

E N T IT A

PA RO L E_ CHIA V E

(1 ,N)

D O PO L A T R AD U Z IO N E D E L L 'AT T R IB U T O M U LT IPL O

EN T IT A PA R O L E C H IA V E

PA RO L A

(1 ,N) (1 ,1 )



Le entità ARTICOLI e FOTOGRAFIE sono caratterizzate dall’attributo multiplo parolechia-ve il quale viene tradotto con una entità, che porta lo stesso nome, ed identificata dalla parola e dal titolo dell’articolo e della fotografia rispettivamente. 4.3 SCHEMA RELAZIONALE Riportiamo di seguito la traduzione delle associazioni ed entità in relazioni: GIORNALISTI_ACCREDITATI(Cf, Nome, Cognome, Via, N_Civico, Città, Provincia, Cap, Stato, Ruolo, E-Mail, Tel, Cell, Nome_Sede, Citta_Sede, Username, Password) AK: Username FK: Nome_Sede, Citta_Sede REFERENCES SEDI_AGENZIA GESTORI_CLIENTI(Cf, Nome, Cognome, Via, N_Civico, Città, Provincia, Cap, Stato, E-Mail, Tel, Cell, Username, Password) AK: Username SEDI_AGENZIA(Nome, Citta, Via, N_Civico, Città, Provincia, Cap, Stato) GIORNALISTI(Cf_Giornalista, N_NotProp, N_NotPubbl, N_ArtProp, N_ArtPubbl) FK: Cf_Giornalista REFERENCES GIORNALISTI_ACCREDITATI FOTOGRAFI(Cf_Fotografo, N_FotoProp, N_FotoPubbl) FK: Cf_Fotografo REFERENCES GIORNALISTI_ACCREDITATI CATEGORIE(Categoria) MATERIALE(Titolo, Url, Data, Categoria, Luogo, Nome_AreaGeo) AK: Url FK: Nome_AreaGeo REFERENCES AREE_GEOGRAFICHE FK: Categoria REFERENCES CATEGORIE AREE_GEOGRAFICHE(Nome, Descrizione) NOTIZIE(Titolo_Notizia, Cf_Giornalista) FK: Titolo_Notizia REFERENCES MATERIALE FK:Cf_Giornalista REFERENCES GIORNALISTI ARTICOLI(Titolo_Articolo, Cf_Giornalista) FK:Titolo_Articolo REFERENCES MATERIALE FK: Cf_Giornalista REFERENCES GIORNALISTI RIFERIMENTI_ARTICOLI (Articolo_From, Articol_To) FK: Articolo_From REFERENCES ARTICOLI FK: Articolo_To REFERENCES ARTICOLI FOTOGRAFIE(Titolo_Foto, Cf_Fotografo, Didascalia) FK: Titolo_Foto REFERENNCES MATERIALE



15

FK: Cf_Fotografo REFERENCES FOTOGRAFI CONTIENE(Titolo_Articolo, Titolo_Foto) FK: Titolo_Articolo REFERENCES ARTICOLI FK: Titolo_Foto REFERENCES FOTOGRAFIE PAROLE_CHIAVE_ARTICOLI(Parola_Chiave, Titolo_Articolo) FK: Titolo_Articolo REFERENCES ARTICOLI PAROLE_CHIAVE_FOTO(Parola, Titolo_Foto) FK: Titolo_Foto REFERENCES FOTOGRAFIE PROGETTI_FOTOGRAFICI(Titolo_Progetto, Descrizione) FK: Titolo_Progetto REFERENCES MATERIALE FOTO_IN_PROGETTO(Titolo_Foto, Titolo_Progetto) FK: Titolo_Foto REFERENCES FOTOGRAFIE FK: Titolo_Progetto REFERENCES PROGETTI_FOTOGRAFICI MATERIALE_PUBBLICATO(Titolo_Materiale_Pubblicato, Data_Pubblicazione, Corretto, Cf_Capo_Redattore) FK: Titolo_Mat_Pubblicato REFERENCES MATERIALE FK: Cf_Capo_Redattore REFERENCES CAPI_REDATTORI MATERIALE_ARCHIVIATO(Titolo_Mat_Archiviato, Cf_Capo_Redattore, Corretto) FK: Titolo_Mat_Archiviato REFERENCES MATERIALE FK: Cf_Capo_Redattore REFERENCES CAPI_REDATTORI CAPI_REDATTORI(Cf_Capo_Redattore, Nome, Cognome, Via, N_Civico, Città, Provincia, Cap, Stato, E-Mail, Tel, Cell, Categoria, Username, Password) AK: Username FK: Categoria REFERENCES CATEGORIE CLIENTI(Cf_Piva, Privato_MezzoComunicazione Nome, Cognome, Via, N_Civico, Città, Provincia, Cap, Stato, E-Mail, Tel, Username, Password) AK: Username ABBONAMENTI(Cf_Piva, Categoria, Data_Inizio_Abbonamento) FK: Cf_Piva, REFERENCES CLIENTI FK: Categoria REFERENCES CATEGORIE ABBONAMENTI_PER_CATEGORIA(Cf_Piva, Categoria, Durata_Abbonamento) FK: Cf_Piva, Categoria REFERENCES ABBONAMENTI ABBONAMENTI_CUMULATIVI(Cf_Piva, Categoria, N_Servizi_Abbonamento, N_Servizi_Prelevati) FK: Cf_Piva, Categoria REFERENCES ABBONAMENTI



SERVIZI_INVIATI(Cf_Piva, Categoria, Titolo_Mat_Inviato, Pubblicato) FK: Cf_Piva, Categoria REFERENCES ABBONAMENTI_PER_CATEGORIA FK:Titolo_Mat_Inviato REFERENCES MATERIALE_PUBBLICATO Traduzione del subset a due entità

DISPONIBILITA GIORNALIERE

CATEGORIACF_PIVAGIORNO

ARTICOLI PUBBLICATI

MATERIALE PUBBLICATO ARTICOLI

DI(0,10) (0,N)

Nello schema relazionale non è stata tradotta l’entità ARTICOLIPUBBLICATI mentre l’associazione DISPONIBITA_DI_ARTICOLI presenta due foreign key alle due entità padre. Analoghe considerazioni per NOTIZIEPUBBLICATE. DISPONIBILITA_GIORNALIERE(Cf_Piva, Categoria, Giorno) FK: Cf_Piva, Categoria REFERENCES ABBONAMENTI_CUMULATIVI DISPONIBITA_DI_ARTICOLI(Cf_Piva, Titolo_Articolo_Pubblicato, Categoria, Giorno) FK: Cf_Piva, Categoria, Giorno REFERENCES DISPONIBILITA_GIORNALIERE FK: Titolo_Articolo_Pubblicato REFERENCES MATERIALE_PUBBLICATO FK: Titolo_Articolo_Pubblicato REFERENCES ARTICOLI DISPONIBILITA_DI_NOTIZIE(Cf_Piva, Titolo_Notizia_Pubblicata, Categoria, Giorno) FK: Cf_Piva, Categoria, Giorno REFERENCES DISPONIBILITA_GIORNALIERE FK: Titolo_Notizia_Pubblicata REFERENCES MATERIALE_PUBBLICATO FK: Titolo_Notizia_Pubblicata REFERENCES NOTIZIE ABBONAMENTI_SCADUTI(Cf_Piva, Categoria, Data_Inizio, Cat_Cum)



17

4.4 DATI DERIVATI

Di seguito si vuole verficare, in base al volume dei dati e di operazioni, se sia conve-niente mantenere nello schema E-R gli attributi derivati #ArticoliProposti (che riporta il nu-mero degli articoli proposti da un giornalista all’agenzia), #NotiziePubblicate (che riporta il numero di notizie pubblicate fra quelle proposte da ogni giornalista all’agenzia) e #Servizi-Prelevati (che riporta il numero di servizi attualmente prelevati da un cliente che ha sottoscrit-to un abbonamento cumulativo). Analoghe considerazioni possono essere fatte per gli attributi derivati #ArticoliPubblicati, #NotizieProposte, #FotoProposte, #FotoPubblicate. • Dato #ArticoliProposti

Le operazioni da eseguire sono le seguenti: Operazione1: Iserimento di un articolo. Operazione2: Ricerca del giornalista che ha prodotto più articoli.

Con il dato derivato

Operazione1 3 accessi in scrittura 1 accesso in lettura 7 * 300 = 2’100/giorno Operazione2 1 accesso in lettura 1 * 1/30 = (1/30)/giorno

REDIGEAGIORNALISTI ARTICOLI

(0,N) (1,1)

(1)(1500)

#ART_PROPOSTI

tabella delle operazioni OPERAZIONI TIPO FREQUENZA Operazione1 I 300/giorno Operazione2 I 1/30 al giorno

tabella dei volumi CONCETTO TIPO VOL. Giornalisti E 1’000 Articoli E 1’500’000 RedigeA R 1’500’000

CONCETTO ACC. TIPO Articoli 1 S RedigeA 1 S Giornalisti 1 L Giornalisti 1 S Giornalisti 1 L



Senza il dato derivato

Operazione1 2 accessi in scrittura 4 * 300 = 1’200/giorno Operazione2 1'501 accessi in lettura 1'501 * (1/30) * 1'000 = 50'033 Dal confronto degli accessi nei due casi si evince che conviene mantenere il dato derivato. • Dato #NoiziePubblicate

Le operazioni da eseguire sono le seguenti: Operazione1: Ricerca numero notizie pubblicate da ogni giornalista. Operazione2: Pubblicazione di una notizia. tabella dei volumi

N.B.: Si è ipotizzato che delle notizie proposte ne saranno pubblicate circa il 70%.

CONCETTO ACC. TIPO Articoli 1 S RedigeA 1 S Giornalisti 1 L RedigeA 1500 L

G IO R N A L IS T I N O T IZ IE

N O T IZ IE P U B B L IC A T E

R E D IG E N( 0 ,N ) ( 1 ,1 )

( 1 )( 1 0 '0 0 0 )

# N O T IZ IE_ PU B B L IC A T E

tabella delle operazioni OPERAZIONI TIPO FREQUENZA Operazione1 I 1/30 al giorno Operazione2 I 1400/giorno

CONCETTO TIPO VOL. Giornalista E 1’000 Notizie E 10'000’000 RedigeN R 10'000’000 Notizie Pubblicate E 7'000’000



19


Operazione1 1'000 accessi in lettura 1'000 * (1/30) = 33/giorno Operazione2 3 accessi in scrittura 6 * 1’400 = 8’400/giorno Senza il dato derivato

Operazione1 10'710'000 accessi in lettura 10'710'000 * (1/30) = 356'700/giorno Operazione2 1 accesso in scrittura 2 * 1'400 = 2’800/giorno Dal confronto degli accessi nei due casi si evince che conviene mantenere il dato derivato. • Dato #ServiziPrelevati

CUMULATIVI HA

#SERVIZI #SERVIZI PRELEVATI


DI

ARTICOLI PUBBLICATI

DI

NOTIZIE PUBBLICATE

(0,N) (0,N)

(0,10)

(1,1)

GIORNO

(0,20)

(1,N)(34) (1)

(5) (10)

CONCETTO ACC. TIPO Giornalista 1’000 L Notizie Pubblicate 1 S RedigeN 1 S Giornalista 1 S

CONCETTO ACC. TIPO Giornalista 1’000 L RedigeN 10'000’000 L Notizie Pubblicate 7'000’000 L Notizie Pubblicate 1 S



Le operazioni da eseguire sono le seguenti: Operazione1: invio di una notizia. Operazione2: verifica se l’abbonamento e scaduto (eseguito ogni volta che viene inviato un

articolo o una notizia). Si fanno le seguenti ipotesi per la valutazione del dato derivato: - In media un abbonamento cumulativo ha un massimo di 500 servizi prelevabili. - Al giorno il prelievo medio è di 15 servizi (10 notizie e 5 articoli).

Quindi l’utente in media accede al database per 500/15 = 34 giorni. Per ogni accesso giornaliero si ha una probabilità pari a 1/15 di scivere in HA e DISPO-NIBILITAGIORNALIERE.

- L’associazione HA (e quindi anche l’entità DISPONIBILITAGIORNALIERE) ha un vo-lume medio di: nº abbonamenti * nº di accessi medio per abbonamento = 400 * 34 =13'600

- L’associazione DISPONIBILITADINOTIZIE ha un volume di: volume DISPONIBILI-TAGIORNALIERE * prelievo medio di notizie al giorno 13'600 * 10 = 136'000.

- L’associazione DISPONIBILITADIARTICOLI ha un volume di: volume DISPONIBILI-TAGIORNALIERE * prelievo medio di articoli al giorno 13'600 * 5 = 68'000

- Si è ipotizzato che delle notizie proposte ne saranno pubblicate circa il 70%. - Si è ipotizzato che degli articoli proposti ne saranno pubblicati circa il 70%.


Schema navigazionale operazione 1 operazione 2

CUMULATIVI HA



DI

NOTIZIE PUBBLICATE

GIORNO

tabella delle operazioni OPERAZIONI TIPO VOLUME Operazione1 I 1’000/giornoOperazione2 I 1’000/giorno

tabella dei volumi CONCETTO TIPO VOLUME Cumulativi E 400 Ha R 13’600 Disponibilita Giornaliere E 13’600 Disponibilta di Notizie R 136’000 Disponibilita di Articoli R 68’000 Articoli Pubblicati E 1’050’000 Notizie Pubblicate E 7’000’000

CUMULATIVI




21

Operazione1 2,13 acccessi in scrittura 1 accesso in lettura 5,26 * 1'000 = 5’260 Operazione2 1 accesso in lettura 1 * 1'000 = 1'000 Senza il dato derivato

Schema navigazionale operazione 1 operazione 2

HA DISPONIBILITA GIORNALIERE

DI

ARTICOLI PUBBLICATI

DI

NOTIZIE PUBBLICATE

GIORNO

Operazione1 1,13 accessi in scrittura 2,26 * 1'000 = 2’260/giorno Operazione2 578 accessi in lettura 578 * 1'000 = 578’000/giorno Dal confronto degli accessi nei due casi si evince che conviene mantenere il dato derivato.

CONCETTO ACC. TIPO Ha 1*1/15 S Disponibilita Giornaliere 1*1/15 S Disponibilita di Notizie 1 S Cumulativi 1 L Cumulativi 1 S Cumulativi 1 L

CONCETTO ACC. TIPO Ha 1*1/15 S Disponibilita Giornaliere 1*1/15 S Disponibilita di Notizie 1 S Ha 34 L Disponibilita Giornaliere 34 L Disponibilita Di Noitzie 34*10 L Disponibilita di Articoli 34*5 L

HA DISPONIBILITA GIORNALIERE

DI

NOTIZIE PUBBLICATE

GIORNO



5. IMPLEMENTAZIONE DEL DATABASE 5.1 CREAZIONE DELLE TABELLE create table CATEGORIE (CATEGORIA varchar(20) not null, constraint PK_CATEGORIE primary key (CATEGORIA)); create table SEDI_AGENZIA (NOME varchar(40) not null, CITTA varchar(40) not null, VIA varchar(40) not null, N_CIVICO int, PROVINCIA char(2), CAP_ZIP int not null, STATO varchar(40) not null, constraint PK_SEDI_AGENZIA primary key(NOME,CITTA)); create table GESTORI_CLIENTI (CF char(16) not null, NOME varchar(40) not null, COGNOME varchar(40) not null, VIA varchar(40) not null, N_CIVICO int, CITTA varchar(40) not null, PROVINCIA char(2), CAP_ZIP int not null, STATO varchar(40) not null, EMAIL varchar(40), TEL varchar(11) not null, CELL char(11), USERNAME varchar(20) not null unique, PASSWORD varchar(10) not null, constraint PK_GESTORE_CLIENTI primary key(CF)); create table GIORNALISTI_ACCREDITATI (CF char(16) not null, NOME varchar(40) not null, COGNOME varchar(40) not null, VIA varchar(40) not null, N_CIVICO int, CITTA varchar(40) not null, PROVINCIA char(2), CAP_ZIP int not null, STATO varchar(40) not null, RUOLO varchar(40) not null, EMAIL varchar(40),



23

TEL varchar(11) not null, CELL char(11), NOME_SEDE varchar(40) not null, CITTA_SEDE varchar(40) not null, USERNAME varchar(20) not null unique, PASSWORD varchar(10) not null, constraint PK_GIORNALISTI_ACCREDITATI primary key(CF), constraint FK_GIORNALISTI_ACCREDITATI_NOME_SEDE_CITTA_SEDE

foreign key(NOME_SEDE,CITTA_SEDE) references SEDI_AGENZIA); create table GIORNALISTI (CF_GIORNALISTA char(16) not null, N_NOTPROP smallint not null default (0), N_NOTPUBBL smallint not null default (0), N_ARTPROP smallint not null default (0), N_ARTPUBBL smallint not null default (0), constraint PK_GIORNALISTI primary key(CF_GIORNALISTA), constraint FK_GIORNALISTI_CF_GIORNALISTA foreign key(CF_GIORNALISTA)

references GIORNALISTI_ACCREDITATI); create table FOTOGRAFI (CF_FOTOGRAFO char(16) not null, N_FOTOPROP smallint not null default (0), N_FOTOPUBBL smallint not null default (0), constraint PK_FOTOGRAFI primary key(CF_FOTOGRAFO), constraint FK_FOTOGRAFI_CF_FOTOGRAFO foreign key(CF_FOTOGRAFO)

references GIORNALISTI_ACCREDITATI); create table AREE_GEOGRAFICHE (NOME varchar(40) not null, DESCRIZIONE text, constraint PK_AREE_GEOGRAFICHE primary key(NOME)); create table MATERIALE (TITOLO varchar(100) not null, URL varchar(100)not null unique, /*creo la chiave alternativa*/ DATA_CREAZIONE datetime not null, CATEGORIA varchar(20) not null, TIPO_MATERIALE varchar(20) not null, LUOGO varchar(40) not null, NOME_AREAGEO varchar(40) not null, constraint PK_MATERIALE primary key(TITOLO), constraint FK_MATERIALE_CATEGORIA foreign key (CATEGORIA)

references CATEGORIE, constraint FK_MATERIALE_NOME_AREAGEO foreign key(NOME_AREAGEO)

references AREE_GEOGRAFICHE);



create table NOTIZIE (TITOLO_NOTIZIA varchar(100) not null, CF_GIORNALISTA char(16) not null, constraint PK_NOTIZIE primary key(TITOLO_NOTIZIA), constraint FK1_NOTIZIE_TITOLO_NOTIZIA foreign key(TITOLO_NOTIZIA)

references MATERIALE, constraint FK2_NOTIZIE_CF_GIORNALISTA foreign key(CF_GIORNALISTA)

references GIORNALISTI); create table ARTICOLI (TITOLO_ARTICOLO varchar(100) not null, CF_GIORNALISTA char(16) not null, constraint PK_ARTICOLI primary key(TITOLO_ARTICOLO), constraint FK1_ARTICOLI_TITOLO_ARTICOLO foreign key (TITO-LO_ARTICOLO)

references MATERIALE, constraint FK2_ARTICOLI_CF_GIORNALISTA foreign key (CF_GIORNALISTA) references GIORNALISTI); create table RIFERIMENTI_ARTICOLI (ARTICOLO_FROM varchar(100) not null, ARTICOLO_TO varchar(100) not null, constraint PK_RIFERIMENTI_ARTICOLI

primary key(ARTICOLO_FROM,ARTICOLO_TO), constraint FK1_RIFERIMENTI_ARTICOLI_ARTICOLO_FROM

foreign key(ARTICOLO_FROM) references ARTICOLI, constraint FK2_RIFERIMENTI_ARTICOLI_ARTICOLO_TO foreign key(ARTICOLO_TO) references ARTICOLI); create table FOTOGRAFIE (TITOLO_FOTO varchar(100) not null, CF_FOTOGRAFO char(16) not null, DIDASCALIA text, constraint PK_FOTOGRAFIE primary key(TITOLO_FOTO), constraint FK1_FOTOGRAFIE_TITOLO_FOTO foreign key (TITOLO_FOTO)

references MATERIALE, constraint FK2_FOTOGRAFIE_CF_FOTOGRAFO foreign key (CF_FOTOGRAFO) references FOTOGRAFI); create table CONTIENE (TITOLO_ARTICOLO varchar(100) not null, TITOLO_FOTO varchar(100) not null, constraint PK_CONTIENE primary key (TITOLO_ARTICOLO,TITOLO_FOTO), constraint FK1_CONTIENE_TITOLO_ARTICOLO foreign key (TITOLO_ARTICOLO) references ARTICOLI, constraint FK2_CONTIENE_TITOLO_FOTO foreign key (TITOLO_FOTO)

references FOTOGRAFIE);



25

create table PAROLE_CHIAVE_ARTICOLI (PAROLA_CHIAVE varchar(20) not null, TITOLO_ARTICOLO varchar(100) not null, constraint PK_PAROLE_CHIAVE_ARTICOLI

primary key (PAROLA_CHIAVE,TITOLO_ARTICOLO), constraint FK_PAROLE_CHIAVE_ARTICOLI_TITOLO_ARTICOLO foreign key (TITOLO_ARTICOLO) references ARTICOLI); create table PAROLE_CHIAVE_FOTO (PAROLA_CHIAVE varchar(20) not null, TITOLO_FOTO varchar(100) not null, constraint PK_PAROLE_CHIAVE_FOTO

primary key (PAROLA_CHIAVE,TITOLO_FOTO), constraint FK_PAROLE_CHIAVE_FOTO_TITOLO_FOTO

foreign key (TITOLO_FOTO) references FOTOGRAFIE); create table PROGETTI_FOTOGRAFICI (TITOLO_PROGETTO varchar(100) not null, DESCRIZIONE text, constraint PK_PROGETTI_FOTOGRAFICI primary key (TITOLO_PROGETTO), constraint FK_PROGETTI_FOTOGRAFICI_TITOLO_PROGETTO

foreign key (TITOLO_PROGETTO) references MATERIALE); create table FOTO_IN_PROGETTO (TITOLO_FOTO varchar(100) not null, TITOLO_PROGETTO varchar(100) not null, constraint PK_FOTO_IN_PROGETTO

primary key (TITOLO_FOTO,TITOLO_PROGETTO), constraint FK1_FOTO_IN_PROGETTO_TITOLO_FOTO

foreign key (TITOLO_FOTO) references FOTOGRAFIE, constraint FK2_FOTO_IN_PROGETTO_TITOLO_PROGETTO

foreign key (TITOLO_PROGETTO) references PROGETTI_FOTOGRAFICI); create table CAPI_REDATTORI (CF_CAPO_REDATTORE char(16) not null, NOME varchar(40) not null, COGNOME varchar(40) not null, VIA varchar(40) not null, N_CIVICO int, CITTA varchar(40) not null, PROVINCIA char(2), CAP_ZIP int not null, STATO varchar(40) not null, EMAIL varchar(40), TEL varchar(11) not null, CELL char(11), CATEGORIA varchar(20) not null, USERNAME varchar(20) not null unique, PASSWORD varchar(10) not null, constraint PK_CAPI_REDATTORI primary key(CF_CAPO_REDATTORE),



constraint FK_CAPI_REDATTORI_CATEGORIA foreign key (CATEGORIA) references CATEGORIE); create table MATERIALE_PUBBLICATO (TITOLO_MAT_PUBBLICATO varchar(100) not null, DATA_PUBBLICAZIONE datetime not null, CF_CAPO_REDATTORE char(16) not null, CORRETTO varchar(2) not null, check (CORRETTO in ('si','s','no','n')), constraint PK_MATERIALE_PUBBLICATO

primary key (TITOLO_MAT_PUBBLICATO), constraint FK1_MATERIALE_PUBBLICATO_TITOLO_MAT_PUBBLICATO

foreign key (TITOLO_MAT_PUBBLICATO) references MATERIALE(TITOLO), constraint FK2_MATERIALE_PUBBLICATO_CF_CAPO_REDATTORE

foreign key (CF_CAPO_REDATTORE) references CAPI_REDATTORI(CF_CAPO_REDATTORE));

create index INDEX_MATERIALE_PUBBLICATO on MATERIALE_PUBBLICATO (DATA_PUBBLICAZIONE) create table MATERIALE_ARCHIVIATO (TITOLO_MAT_ARCHIVIATO varchar(100) not null, CF_CAPO_REDATTORE char(16) not null, CORRETTO varchar(2) not null, check (CORRETTO in ('si','s','no','n')), constraint PK_MATERIALE_ARCHIVIATO

primary key (TITOLO_MAT_ARCHIVIATO), constraint FK1_MATERIALE_ARCHIVIATO_TITOLO_MAT_ARCHIVIATO

foreign key (TITOLO_MAT_ARCHIVIATO) references MATERIALE(TITOLO), constraint FK2_MATERIALE_ARCHIVIATO_CF_CAPO_REDATTORE

foreign key (CF_CAPO_REDATTORE) references CAPI_REDATTORI(CF_CAPO_REDATTORE));

create table CLIENTI (CF_PIVA varchar(20) not null, NOME varchar(40) not null, COGNOME varchar(40), VIA varchar(40) not null, N_CIVICO int, CITTA varchar(40) not null, PROVINCIA char(2), CAP_ZIP int not null, STATO varchar(40) not null, EMAIL varchar(40), TEL varchar(11) not null, CELL char(11), PRIVATO_MEZZODICOMUNICAZIONE varchar(2) not null, USERNAME varchar(20) not null unique, PASSWORD varchar(10) not null, check (PRIVATO_MEZZODICOMUNICAZIONE in ('P','MC')),



27

constraint PK_CLIENTI primary key (CF_PIVA)); create table ABBONAMENTI (CF_PIVA varchar(20) not null, CATEGORIA varchar(20) not null, DATA_INIZIO_ABBONAMENTO datetime not null default (getdate()), constraint PK_ABBONAMENTI primary key (CF_PIVA,CATEGORIA), constraint FK_ABBONAMENTI_CATEGORIA foreign key (CATEGORIA) references CATEGORIE, constraint FK_ABBONAMENTI_CF_PIVA foreign key (CF_PIVA)

references CLIENTI); create table ABBONAMENTI_PER_CATEGORIA (CF_PIVA varchar(20) not null, CATEGORIA varchar(20) not null, DURATA_ABBONAMENTO int not null, constraint PK_ABBONAMENTI_PER_CATEGORIA primary key (CF_PIVA,CATEGORIA), constraint FK_ABBONAMENTI_PER_CATEGORIA_CF_PIVA_CATEGORIA

foreign key (CF_PIVA,CATEGORIA) references ABBONAMENTI); create table ABBONAMENTI_CUMULATIVI (CF_PIVA varchar(20) not null, CATEGORIA varchar(20) not null, N_SERVIZI_ABBONAMENTO int not null, N_SERVIZI_PRELEVATI int not null default (0), constraint PK_ABBONAMENTI_CUMULATIVI primary key (CF_PIVA,CATEGORIA), constraint FK_ABBONAMENTI_CUMULATIVI_CF_PIVA_CATEGORIA

foreign key (CF_PIVA,CATEGORIA) references ABBONAMENTI); create table SERVIZI_INVIATI (CF_PIVA varchar(20) not null, CATEGORIA varchar(20) not null, TITOLO_MAT_INVIATO varchar(100) not null, PUBBLICATO varchar(2), check (PUBBLICATO in ('si','s','no','n')), constraint PK_SERVIZI_INVIATI

primary key (CF_PIVA,CATEGORIA,TITOLO_MAT_INVIATO), constraint FK1_SERVIZI_INVIATI_CF_PIVA_CATEGORIA

foreign key (CF_PIVA,CATEGORIA) references ABBONAMEN-TI_PER_CATEGORIA, constraint FK2_SERVIZI_INVIATI_TITOLO_MAT_INVIATO

foreign key (TITOLO_MAT_INVIATO) references MATERIALE_PUBBLICATO); create table DISPONIBILITA_GIORNALIERE (CF_PIVA varchar(20) not null, CATEGORIA varchar(20) not null, GIORNO datetime not null, constraint PK_DISPONIBILITA_GIORNALIERE



primary key (CF_PIVA,CATEGORIA,GIORNO), constraint FK_DISPONIBILITA_GIORNALIERE_CF_PIVA_CATEGORIA

foreign key (CF_PIVA,CATEGORIA) references ABBONAMEN-TI_CUMULATIVI); create table DISPONIBILITA_DI_ARTICOLI (CF_PIVA varchar(20) not null, CATEGORIA varchar(20) not null, GIORNO datetime not null, TITOLO_ARTICOLO_PUBBLICATO varchar(100) not null, constraint PK_DISPONIBILITA_DI_ARTICOLI

primary key(CF_PIVA,CATEGORIA,GIORNO,TITOLO_ARTICOLO_PUBBLICATO), constraint FK1_DISPONIBILITA_DI_ARTICOLI_CF_PIVA_CATEGORIA_GIORNO foreign key(CF_PIVA,CATEGORIA,GIORNO)

references DISPONIBILITA_GIORNALIERE, constraint FK2_DISPONIBILITA_DI_ARTICOLI_TITOLO_ARTICOLO_PUBBLICATO

foreign key (TITOLO_ARTICOLO_PUBBLICATO) references MATERIALE_PUBBLICATO, constraint FK3_DISPONIBILITA_DI_ARTICOLI_TITOLO_ARTICOLO_PUBBLICATO foreign key (TITOLO_ARTICOLO_PUBBLICATO) references ARTICOLI);

create table DISPONIBILITA_DI_NOTIZIE (CF_PIVA varchar(20) not null, CATEGORIA varchar(20) not null, GIORNO datetime not null, TITOLO_NOTIZIA_PUBBLICATA varchar(100) not null, constraint PK_DISPONIBILITA_DI_NOTIZIE

primary key(CF_PIVA,CATEGORIA,GIORNO,TITOLO_NOTIZIA_PUBBLICATA), constraint FK1_DISPONIBILITA_DI_NOTIZIE_CF_PIVA_CATEGORIA_GIORNO foreign key (CF_PIVA,CATEGORIA,GIORNO)

references DISPONIBILITA_GIORNALIERE, constraint FK2_DISPONIBILITA_DI_NOTIZIE_TITOLO_NOTIZIA_PUBBLICATA foreign key (TITOLO_NOTIZIA_PUBBLICATA)

references MATERIALE_PUBBLICATO, constraint FK3_DISPONIBILITA_DI_NOTIZIE_TITOLO_NOTIZIA_PUBBLICATA

foreign key (TITOLO_NOTIZIA_PUBBLICATA) references NOTIZIE);



29

Parte non collegata allo schema E/R /* La seguente tabella serve per memorizzare gli abbonamenti scaduti */ create table ABBONAMENTI_SCADUTI (CF_PIVA varchar(20) not null, CATEGORIA varchar(20) not null, DATA_INIZIO_ABBONAMENTO datetime not null, TIPO_ABBONAMENTO char(3) not null, check (TIPO_ABBONAMENTO in ('CAT','CUM','cat','cum')), constraint PK_ABBONAMENTI_SCADUTI

primary key (CF_PIVA,CATEGORIA,DATA_INIZIO_ABBONAMENTO));



5.2 CREAZIONE VISTE • Vista NOTART

Si è creata la vista NOTART per ricavare il codice fiscale dei giornalisti che hanno redat-to notizie e/o articoli e viene utilizzata nella seconda parte dell'operazione 2: ricerca degli ul-timi N servizi pubblicati per categoria e sede di agenzia. Grazie al codice fiscale del giornali-sta si può risalire così alla sede di agenzia selezionata dall’utente essendo l’entità SEDIA-GENZIA in associazione con GIORNALISTIACCREDITATI. create view NOTART (CF, TITOLO) as select CF_GIORNALISTA, TITOLO_NOTIZIA from NOTIZIE union select CF_GIORNALISTA, TITOLO_ARTICOLO from ARTICOLI • Viste CATART e CATFOTO

Si sono create le viste CATART e CATFOTO per ricavare la categoria di appartenenza degli articoli e delle fotografie e sono utilizzate nel trigger INSERIMENTO_IN_CONTIENE il quale inserisce nella tabella CONTIENE il titolo dell’articolo e della foto da esso contenuta. Tramite le viste CATART e CATFOTO il trigger può verificare se la categoria della fotogra-fia è uguale alla categoria dell’articolo e quindi se si può effettuare l’inserimento. create view CATART (TITOLO, CATEGORIA) as select A.TITOLO_ARTICOLO, MT.CATEGORIA from ARTICOLI A, MATERIALE MT where A.TITOLO_ARTICOLO = MT.TITOLO create view CATFOTO (TITOLO, CATEGORIA) as select F.TITOLO_FOTO, MT.CATEGORIA from FOTOGRAFIE F, MATERIALE MT where F.TITOLO_FOTO = MT.TITOLO



31

5.3 CREAZIONE DELLE STORED PROCEDURE • Stored procedure per inserimento Notizie

La procedura inserisce nel database la notizia redatta da un giornalista. L’ inserimento av-viene in cascata nelle tabelle MATERIALE e NOTIZIE. Viene inoltre aggiornato il dato derivato N_NOTPROP contenente il numero di notizie proposte dal giornalista. I parametri passati sono: codice fiscale del giornalista, titolo della notizia, categoria alla quale appartiene la notizia, url in cui è memorizzata la notizia, data creazione della noti-zia, luogo e area geografica ai quali si riferisce la notizia.

if exists (select NAME from sysobjects where NAME = 'insert_notizia' and type = 'P') drop procedure insert_notizia go /*creazione della procedura*/ create proc insert_notizia @vCF_GIORNALISTA char(16), @vTITOLO_NOTIZIA varchar(100), @vCATEGORIA varchar(20), @vURL varchar(100), @vDATA_CREAZIONE datetime, @vLUOGO varchar(40), @vNOME_AREAGEO varchar(40) as begin transaction insert into MATERIALE (TITOLO, URL, DATA_CREAZIONE, CATEGORIA,

TIPO_MATERIALE, LUOGO, NOME_AREAGEO) values (@vTITOLO_NOTIZIA, @vURL, @vDATA_CREAZIONE,

@vCATEGORIA, 'NOTIZIA', @vLUOGO, @vNOME_AREAGEO)

if @@error <> 0 begin raiserror ('errore nell’ inserimento in materiale !!! ',16,1) rollback tran return end insert into NOTIZIE (TITOLO_NOTIZIA, CF_GIORNALISTA)



values (@vTITOLO_NOTIZIA, @vCF_GIORNALISTA) if @@error <> 0 begin raiserror ('errore nell’ inserimento in notizie !!! ',16,1) rollback tran return end /* aggiorno il dato derivato: incremento il numero di notizie proposte*/ update GIORNALISTI set N_NOTPROP = N_NOTPROP + 1 where CF_GIORNALISTA = @vCF_GIORNALISTA commit transaction go • Stored procedure per inserimento fotografie

La procedura inserisce nel database la fotografia scattata da un fotografo. L’ inserimento avviene in cascata nelle tabelle MATERIALE e FOTOGRAFIE. Viene inoltre aggiornato il dato derivato N_FOTOPROP contenente il numero di foto proposte dal fotografo. I parametri passati sono: codice fiscale del fotografo, titolo della fotografia, didascalia re-lativa alla fotografia, categoria alla quale appartiene la fotografia, url in cui è memorizza-ta la fotografia, data creazione della fotografia, luogo e area geografica ai quali si riferi-sce la fotografia.

if exists (select NAME from sysobjects where NAME = 'insert_fotografia' and type = 'P') drop procedure insert_fotografia go /*creazione della procedura*/ create proc insert_fotografia @vCF_FOTOGRAFO char(16), @vTITOLO_FOTO varchar(100), @vDIDASCALIA text, @vCATEGORIA varchar(20), @vURL varchar(100), @vDATA_CREAZIONE datetime, @vLUOGO varchar(40), @vNOME_AREAGEO varchar(40) as



33

begin transaction insert into MATERIALE (TITOLO, URL, DATA_CREAZIONE, CATEGORIA,

TIPO_MATERIALE, LUOGO, NOME_AREAGEO) values (@vTITOLO_FOTO, @vURL, @vDATA_CREAZIONE, @vCATEGORIA, 'FOTOGRAFIA', @vLUOGO, @vNOME_AREAGEO)

if @@error <> 0 begin print 'errore nell’’inderimento in materiale !!! ' rollback tran return end insert into FOTOGRAFIE (TITOLO_FOTO, CF_FOTOGRAFO, DIDASCALIA) values (@vTITOLO_FOTO, @vCF_FOTOGRAFO, @vDIDASCALIA) if @@error <> 0 begin print 'errore nell’’inserimento in notizie!!!' rollback tran return end /* aggiorno il dato derivato: incremento il numero di fotografie proposte*/ update FOTOGRAFI set N_FOTOPROP = N_FOTOPROP + 1 where CF_FOTOGRAFO = @vCF_FOTOGRAFO commit transaction go • Stored procedure per inserimento fotografie in articoli

La procedura serve per correlare una fotografia ad un articolo. Tali informazioni vengono inserite nella tabella CONTIENE. I parametri passati sono: titolo dell’articolo, titolo della fotografia.

if exists (select NAME from sysobjects where NAME = 'insert_fotografia_in_articolo' and type = 'P') drop procedure insert_fotografia_in_articolo go /*creazione della procedura*/ create proc insert_fotografia_in_articolo @vTITOLO_ARTICOLO varchar(100),



@vTITOLO_FOTO varchar(100) as begin transaction insert into CONTIENE (TITOLO_ARTICOLO, TITOLO_FOTO) values (@vTITOLO_ARTICOLO, @vTITOLO_FOTO) if @@error <> 0 begin print 'errore !!! ' rollback tran return end commit transaction go • Stored procedure per inserimento parole chiave in fotografie

La procedura serve per inserire una parola chiave in una fotografia. Tali informazioni vengono inserite nella tabella PAROLE_CHIAVE_FOTO. I parametri passati sono: parola chiave, titolo della fotografia.

if exists (select NAME from sysobjects where NAME = 'insert_parola_chiave_foto' and type = 'P') drop procedure insert_parola_chiave_foto go /*creazione della procedura*/ create proc insert_parola_chiave_foto @vPAROLA_CHIAVE varchar (20), @vTITOLO_FOTO varchar (100) as begin transaction insert into PAROLE_CHIAVE_FOTO (PAROLA_CHIAVE,TITOLO_FOTO) values (@vPAROLA_CHIAVE, @vTITOLO_FOTO) if @@error <> 0 begin print 'errore !!! ' rollback tran return end commit transaction



35

go • Stored procedure per inserimento Articoli

La procedura inserisce nel database l’ articolo redatto da un giornalista. L’ inserimento avviene in cascata nelle tabelle MATERIALE e ARTICOLI. Viene inoltre aggiornato il dato derivato N_ARTPROP contenente il numero di articoli proposti dal giornalista. I parametri passati sono: codice fiscale del giornalista, titolo dell’ articolo, categoria alla quale appartiene l’ articolo, url in cui è memorizzata l’ articolo, data creazione dell’ arti-colo, luogo e area geografica ai quali si riferiscono l’articolo.

if exists (select NAME from sysobjects where NAME = 'insert_articolo' and type = 'P') drop procedure insert_articolo go /*creazione della procedura*/ create proc insert_articolo @vCF_GIORNALISTA char(16), @vTITOLO_ARTICOLO varchar(100), @vCATEGORIA varchar(20), @vURL varchar(100), @vDATA_CREAZIONE datetime, @vLUOGO varchar(40), @vNOME_AREAGEO varchar(40) as begin transaction insert into MATERIALE (TITOLO, URL, DATA_CREAZIONE, CATEGORIA,

TIPO_MATERIALE, LUOGO, NOME_AREAGEO) values (@vTITOLO_ARTICOLO, @vURL, @vDATA_CREAZIONE,

@vCATEGORIA, 'ARTICOLO', @vLUOGO, @vNOME_AREAGEO)

if @@error <> 0 begin print 'errore !!! ' rollback tran return end insert into ARTICOLI (TITOLO_ARTICOLO, CF_GIORNALISTA) values (@vTITOLO_ARTICOLO, @vCF_GIORNALISTA) if @@error <> 0 begin print 'errore: giornalista non esistente!!!'



rollback tran return end /* aggiorno il dato derivato: incremento il numero di articoli proposti*/ update GIORNALISTI set N_ARTPROP = N_ARTPROP + 1 where CF_GIORNALISTA = @vCF_GIORNALISTA commit transaction go • Stored procedure per inserimento articoli correlati

La procedura serve per correlare un articolo a un altro articolo. Tali informazioni vengono inserite nella tabella RIFERIMENTI_ARTICOLI. I parametri passati sono: titolo dell’articolo sorgente da cui parte il riferimento, titolo dell’articolo destinazione.

if exists (select NAME from sysobjects where NAME = 'insert_articolo_correlato' and type = 'P') drop procedure insert_articolo_correlato go /*creazione della procedura*/ create proc insert_articolo_correlato @TITOLO varchar(100), @vARTICOLO_CORRELATO varchar(100) as begin transaction insert into RIFERIMENTI_ARTICOLI (ARTICOLO_FROM, ARTICOLO_TO) values (@TITOLO, @vARTICOLO_CORRELATO) if @@error <> 0 begin print 'errore !!! ' rollback tran return end commit transaction go • Stored procedure per inserimento parole chiave in Articoli



37

La procedura serve per inserire una parola chiave in un articolo. Tali informazioni vengo-no inserite nella tabella PAROLE_CHIAVE_ARTICOLI. I parametri passati sono: parola chiave, titolo dell’articolo.

if exists (select NAME from sysobjects where NAME = 'insert_parola_chiave_articolo' and type = 'P') drop procedure insert_parola_chiave_articolo go /*creazione della procedura*/ create proc insert_parola_chiave_articolo @vPAROLA_CHIAVE varchar (20), @vTITOLO_ARTICOLO varchar (100) as begin transaction insert into PAROLE_CHIAVE_ARTICOLI (PAROLA_CHIAVE,TITOLO_ARTICOLO) values (@vPAROLA_CHIAVE, @vTITOLO_ARTICOLO) if @@error <> 0 begin raiserror ('errore: Parola chiave già associata all'' articolo !!!', 16, 1) rollback tran return end commit transaction go • Stored procedure per la pubblicazione di materiale

La procedura permette a un caporedattore di pubblicare un servizio (articolo, notizia o fo-tografia). Tali informazioni vengono inserite nella tabella MATERIALE_PUBBLICATO. La procedura controlla se il materiale è già stato pubblicato. Se il materiale era stato ar-chiviato viene cancellato da MATERIALE_ARCHIVIATO e inserito in MATERIA-LE_PUBBLICATO. Viene aggiornato il dato derivato N_NOTPUBBL, N_ARTPUBBL o N_FOTOPUBBL, cioè il numero di servizi pubblicati dall’autore, a seconda il servizio sia rispettivamente una notizia, un articolo o una fotografia. Se il servizio da pubblicare è un articolo allora vengono pubblicate anche tutte le fotografie in esso contenute. Se un capo-redattore prova a pubblicare un servizio di una categoria diversa dalla propria scatta il trigger INSERIMENTO_MAT_PUBBLICATO. I parametri passati sono: titolo del srvizio, data di pubblicazione del servizio, capo redat-tore che vuole pubblicare il servizio, corretto che indica se il caporedattore ha apportato delle modifiche al testo .

if exists (select NAME from sysobjects where NAME = 'pubblica_materiale' and type = 'P') drop procedure pubblica_materiale



go /*creazione della procedura*/ create proc pubblica_materiale as go alter proc pubblica_materiale @vTITOLO_MAT_PUBBLICATO varchar (100), @vDATA_PUBBLICAZIONE datetime, @vCF_CAPO_REDATTORE char (16), @vCORRETTO varchar (2) as /* verifico se il materiale che voglio pubblicare in realta` non sia gia` pubblicato */ if @vTITOLO_MAT_PUBBLICATO in (select TITOLO_MAT_PUBBLICATO from MATERIALE_PUBBLICATO) begin RAISERROR('Errore, materiale già pubblicato!!!', 16, 1) return end begin transaction /* Se il materiale era stato archiviato => lo si cancella da MATERIALE_ARCHIVIATO e lo si inserisce in MATERIALE_PUBBLICATO se non era stato archiviato la seguente delete non ha alcun effetto*/ delete from MATERIALE_ARCHIVIATO where TITOLO_MAT_ARCHIVIATO = @vTITOLO_MAT_PUBBLICATO insert into MATERIALE_PUBBLICATO(TITOLO_MAT_PUBBLICATO, DA-TA_PUBBLICAZIONE, CF_CAPO_REDATTORE, CORRETTO) values (@vTITOLO_MAT_PUBBLICATO, @vDATA_PUBBLICAZIONE, @vCF_CAPO_REDATTORE, @vCORRETTO) if @@error <> 0 begin print 'errore !!! ' rollback tran return end /* aggiorno il dato derivato: */ /* memorizzo il tipo del materiale da pubblicare in @vTIPO */ declare @vTIPO varchar(20)



39

set @vTIPO = (select TIPO_MATERIALE from MATERIALE where TITOLO = @vTITOLO_MAT_PUBBLICATO) /*controllo se è una notizia */ if @vTIPO = 'NOTIZIA' begin /*incremento il numero di notizie pubblicate nel giornalista che ha redatto quella notizia, se il titolo pubblicato è una notizia */ update GIORNALISTI set N_NOTPUBBL = N_NOTPUBBL + 1 where CF_GIORNALISTA in (select CF_GIORNALISTA from NOTIZIE where TITOLO_NOTIZIA = @vTITOLO_MAT_PUBBLICATO) end else /*controllo se è un articolo */ if @vTIPO = 'ARTICOLO' begin /*incremento il numero di articoli pubblicati dal giornalista che ha redatto quell' articolo, se il titolo pubblicato è una articolo */ update GIORNALISTI set N_ARTPUBBL = N_ARTPUBBL + 1 where CF_GIORNALISTA in (select CF_GIORNALISTA from ARTICOLI where TITOLO_ARTICOLO = @vTITOLO_MAT_PUBBLICATO) /* devo controllare se a questo articolo sono correlate delle fotografie */ declare CUR_CONTIENE cursor for select C.TITOLO_FOTO from CONTIENE C where C.TITOLO_ARTICOLO = @vTITOLO_MAT_PUBBLICATO declare @vTITOLO_FOTO varchar (100) open CUR_CONTIENE fetch next from CUR_CONTIENE into @vTITOLO_FOTO while (@@FETCH_STATUS = 0) begin exec pubblica_materiale @vTITOLO_FOTO, @vDATA_PUBBLICAZIONE, @vCF_CAPO_REDATTORE, 'no' fetch next from CUR_CONTIENE into @vTITOLO_FOTO end close CUR_CONTIENE



deallocate CUR_CONTIENE end else /*controllo se è una foto */ if @vTIPO = 'FOTOGRAFIA' begin /*incremento il numero di foto pubblicate dal fotografo che ha scattato quella foto, se il titolo pubblicato è una foto */ update FOTOGRAFI set N_FOTOPUBBL = N_FOTOPUBBL + 1 where CF_FOTOGRAFO in (select CF_FOTOGRAFO from FOTOGRAFIE where TITOLO_FOTO = @vTITOLO_MAT_PUBBLICATO) end commit transaction go • Stored procedure per l’ archiviazione di materiale

La procedura permette a un caporedattore di archiviare un servizio (articolo, notizia o fo-tografia). Tali informazioni vengono inserite nella tabella MATERIALE_ARCHIVIATO. La procedura controlla se il materiale è già stato pubblicato o archiviato. Se il servizio da pubblicare è un articolo allora vengono archiviate anche tutte le fotografie in esso conte-nute. Se un caporedattore prova ad archiviare un servizio di una categoria diversa dalla propria scatta il trigger INSERIMENTO_MAT_ARCHIVIATO. I parametri passati sono: titolo del srvizio, capo redattore che vuole archiviare il servizio, corretto che indica se il caporedattore ha apportato delle modifiche al testo .

if exists (select NAME from sysobjects where NAME = 'archivia_materiale' and type = 'P') drop procedure archivia_materiale go /*creazione della procedura*/ create proc archivia_materiale as go alter proc archivia_materiale @vTITOLO_MAT_ARCHIVIATO varchar (100), @vCF_CAPO_REDATTORE char (16), @vCORRETTO varchar (2)



41

as /* Se un materiale è attualmente pubblicato non può essere archiviato... */ if @vTITOLO_MAT_ARCHIVIATO in (select TITOLO_MAT_PUBBLICATO from MATERIALE_PUBBLICATO) begin RAISERROR('Materiale già pubblicato!!!', 16, 1) return end /* ...oppure se e` gia` stato archiviato lo segnalo all'utente */ if @vTITOLO_MAT_ARCHIVIATO in (select TITOLO_MAT_ARCHIVIATO from MATERIALE_ARCHIVIATO) begin RAISERROR('materiale già archiviato!!!', 16, 1) return end begin transaction insert into MATERIALE_ARCHIVIATO(TITOLO_MAT_ARCHIVIATO, CF_CAPO_REDATTORE, CORRETTO) values (@vTITOLO_MAT_ARCHIVIATO, @vCF_CAPO_REDATTORE, @vCORRETTO) if @@error <> 0 begin print 'errore !!! ' rollback tran return end /* Se il Materiale è un articolo devo archiviare anche le foto in esso contenute */ declare @vTIPO_MATERIALE varchar(20) set @vTIPO_MATERIALE = (select TIPO_MATERIALE from MATERIALE where TITOLO = @vTITOLO_MAT_ARCHIVIATO) if @vTIPO_MATERIALE = 'ARTICOLO' begin /* devo controllare se a questo articolo sono correlate delle fotografie */ declare CUR_CONTIENE cursor for select C.TITOLO_FOTO from CONTIENE C where C.TITOLO_ARTICOLO = @vTITOLO_MAT_ARCHIVIATO declare @vTITOLO_FOTO varchar (100)



open CUR_CONTIENE fetch next from CUR_CONTIENE into @vTITOLO_FOTO while (@@FETCH_STATUS = 0) begin exec archivia_materiale @vTITOLO_FOTO, @vCF_CAPO_REDATTORE, 'no' fetch next from CUR_CONTIENE into @vTITOLO_FOTO end close CUR_CONTIENE deallocate CUR_CONTIENE end commit transaction go • Stored procedure per inserimento nuovo cliente

La procedura inserisce nel database le informazioni relative a un nuovo cliente. Tali in-formazioni vengono inserite nella tabella CLIENTI. I parametri passati sono: codice fiscale o partita iva, nome, cognome, via, numero civico, città di residenza, provincia di residenza, cap o zip, stato di residenza, e_mail, numero te-lefono, numero cellulare, privato_mezzodicomunicazione che indica se è un cliente priva-to o un mezzo di comunicazione, username e password.

if exists (select NAME from sysobjects where NAME = 'insert_clienti' and type = 'P') drop procedure insert_clienti go /*creazione della procedura*/ create proc insert_clienti @vCF_PIVA varchar(20), @vNOME varchar(40), @vCOGNOME varchar(40), @vVIA varchar(40), @vN_CIVICO int, @vCITTA varchar(40), @vPROVINCIA char(2), @vCAP_ZIP int, @vSTATO varchar(40), @vEMAIL varchar(40), @vTEL varchar(11), @vCELL char(11),



43

@vPRIVATO_MEZZODICOMUNICAZIONE varchar(2), @vUSERNAME varchar(20), @vPASSWORD varchar(10) as begin transaction insert into CLIENTI (CF_PIVA, NOME, COGNOME, VIA, N_CIVICO, CITTA, PROVIN-CIA, CAP_ZIP, STATO, EMAIL, TEL, CELL, PRIVATO_MEZZODICOMUNICAZIONE, USERNAME, PASSWORD) values (@vCF_PIVA ,@vNOME , @vCOGNOME , @vVIA , @vN_CIVICO , @vCITTA, @vPROVINCIA, @vCAP_ZIP, @vSTATO, @vEMAIL, @vTEL, @vCELL, @vPRIVATO_MEZZODICOMUNICAZIONE, @vUSERNAME, @vPASSWORD) if @@error <> 0 begin print 'errore : insert_clienti !!! ' rollback tran return end commit transaction go • Stored procedure per inserimento abbonamenti per categoria

La procedura inserisce nel database un nuovo abbonamento per categoria. L’ inserimento avviene in cascata nelle tabelle ABBONAMENTI e ABBONAMEN-TI_PER_CATEGORIA. I parametri passati sono: codice fiscale o partita iva del cliente, categoria alla quale si ri-ferisce l’abbonamento, data inizio abbonamento, durata in giorni dell’abbonamento.

if exists (select NAME from sysobjects where NAME = 'SOTTOSCRIZIONE_ABBONAMENTO_XCATEGORIA' and

type = 'P') drop procedure SOTTOSCRIZIONE_ABBONAMENTO_XCATEGORIA go /*creazione della procedura*/ create proc SOTTOSCRIZIONE_ABBONAMENTO_XCATEGORIA @vCF_PIVA char (20), @vCATEGORIA varchar (20), @vDATA_INIZIO_ABBONAMENTO datetime,



@DURATA_ABBONAMENTO int as begin transaction insert into ABBONAMENTI(CF_PIVA, CATEGORIA, DATA_INIZIO_ABBONAMENTO) values (@vCF_PIVA, @vCATEGORIA, @vDATA_INIZIO_ABBONAMENTO) if @@error <> 0 begin print 'errore !!! ' rollback tran return end insert into ABBONAMENTI_PER_CATEGORIA(CF_PIVA, CATEGORIA,

DURATA_ABBONAMENTO) values (@vCF_PIVA, @vCATEGORIA, @DURATA_ABBONAMENTO) if @@error <> 0 begin print 'errore !!! ' rollback tran return end commit transaction go



45

• Stored procedure per inserimento abbonamenti per cumulativi

La procedura inserisce nel database un nuovo abbonamento cumulativo. L’ inserimento avviene in cascata nelle tabelle ABBONAMENTI e ABBONAMENTI_CUMULATIVI. I parametri passati sono: codice fiscale o partita iva del cliente, categoria alla quale si ri-ferisce l’abbonamento, data inizio abbonamento, numero totale di servizi che l’abbonato può prelevare.

if exists (select NAME from sysobjects where NAME = 'SOTTOSCRIZIONE_ABBONAMENTO_CUMULATIVO' and

type = 'P') drop procedure SOTTOSCRIZIONE_ABBONAMENTO_CUMULATIVO go /*creazione della procedura*/ create proc SOTTOSCRIZIONE_ABBONAMENTO_CUMULATIVO @vCF_PIVA char (20), @vCATEGORIA varchar (20), @vDATA_INIZIO_ABBONAMENTO datetime, @vN_SERVIZI_ABBONAMENTO int as begin transaction insert into ABBONAMENTI(CF_PIVA, CATEGORIA, DATA_INIZIO_ABBONAMENTO) values (@vCF_PIVA, @vCATEGORIA, @vDATA_INIZIO_ABBONAMENTO) if @@error <> 0 begin print 'errore !!! ' rollback tran return end insert into ABBONAMENTI_CUMULATIVI(CF_PIVA, CATEGORIA,

N_SERVIZI_ABBONAMENTO) values (@vCF_PIVA, @vCATEGORIA, @vN_SERVIZI_ABBONAMENTO) if @@error <> 0 begin print 'errore !!! ' rollback tran return end commit transaction go



• Stored procedure per cancellare un abbonamento

La procedura cancella dal database un abbonamento. Se l’abbonamento è per categoria la cancellazione avviene in cascata nelle tabelle SERVIZI_INVIATI, ABBONAMEN-TI_PER_CATEGORIA e ABBONAMENTI. Se l’abbonamento è cumulativo la cancella-zione avviene in cascata nelle tabelle DISPONIBILITA_DI_ARTICOLI, DISPONIBILI-TA_DI_NOTIZIE, DISPONIBILITA_GIORNALIERE, ABBONAMEN-TI_CUMULATIVI, ABBONAMENTI. Le informazioni relative all’abbonamento cancel-lato vengono salvate in ABBONAMENTI_SCADUTI. I parametri passati sono: codice fiscale o partita iva del cliente, categoria alla quale si ri-ferisce l’abbonamento, che identificano univocamente l’abbonemento.

if exists (select NAME from sysobjects where NAME = 'cancella_abbonamento' and type = 'P') drop procedure cancella_abbonamento go /*creazione della procedura*/ create proc cancella_abbonamento /*parametri : */ /*chiave di abbonamento*/ @vCF_PIVA varchar(20), @vCATEGORIA varchar(20) as declare @vTIPO_ABBONAMENTO char(3) declare @vDATA_INIZIO_ABBONAMENTO datetime /* memorizzo la data di inizio dell' abbonamento,se non esiste abbonamento => @vDATA_INIZIO_ABBONAMENTO conterrà il valore null*/ set @vDATA_INIZIO_ABBONAMENTO = null set @vDATA_INIZIO_ABBONAMENTO = (select DATA_INIZIO_ABBONAMENTO from ABBONAMENTI where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA) if @vDATA_INIZIO_ABBONAMENTO is null begin RAISERROR('errore nella cancellazione: ABBONAMENTO NON TROVATO !!! ', 16, 1) return end begin transaction



47

/* controllo di che tipo è l' abbonamento */ if exists (select * from ABBONAMENTI_PER_CATEGORIA where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA) begin /* l' abbonamento è per categoria */ set @vTIPO_ABBONAMENTO = 'CAT' end else begin /* l' abbonamento è cumulativo */ set @vTIPO_ABBONAMENTO = 'CUM' end /* salvo le informazioni relative all' abbonamento nell' archivio ABBONAMENTI_SCADUTI*/ insert into ABBONAMENTI_SCADUTI (CF_PIVA, CATEGORIA, DA-TA_INIZIO_ABBONAMENTO, TIPO_ABBONAMENTO) values (@vCF_PIVA, @vCATEGORIA, @vDATA_INIZIO_ABBONAMENTO, @vTIPO_ABBONAMENTO) if @vTIPO_ABBONAMENTO = 'CAT' begin /* Cancello tutti i servizi inviati da SERVIZI_INVIATI */ delete from SERVIZI_INVIATI where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA /* Cancello l' abbonamento da ABBONAMENTI_PER_CATEGORIA */ delete from ABBONAMENTI_PER_CATEGORIA where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA end else /* l'abbonamento è cumulativo */ begin /* Cancello tutti i servizi inviati da DISPONIBILITA_DI_ARTICOLI */ delete from DISPONIBILITA_DI_ARTICOLI where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA /* Cancello tutti i servizi inviati da DISPONIBILITA_DI_NOTIZIE */ delete from DISPONIBILITA_DI_NOTIZIE where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA /* Cancello tutti i servizi inviati da DISPONIBILITA_GIORNALIERE */ delete from DISPONIBILITA_GIORNALIERE where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA /* Cancello l' abbonamento da ABBONAMENTI_CUMULATIVI */ delete from ABBONAMENTI_CUMULATIVI where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA



end /* Cancello l' abbonamento da ABBONAMENTI */ delete from ABBONAMENTI where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA commit transaction go • Stored procedure per la memorizzazione dei servizi inviati ad un abbonamento per

categoria La procedura inserisce nel database le informazioni relative a un servizio inviato a un cliente. Tali informazioni vengono inserite nella tabella SERVIZI_INVIATI. La procedu-ra controlla se il materiale prelevato è della stessa categoria dell’abbonamento. Se l’abbonamento è scaduto viene eliminato dal database. Se il servizio da inviare è un arti-colo allora vengono inviate anche tutte le fotografie in esso contenute. I parametri passati sono: codice fiscale o partita iva del cliente, categoria alla quale si ri-ferisce l’abbonamento, titolo del servizio da inviare.

if exists (select NAME from sysobjects where NAME = 'insert_servizi_inviati_xcategoria' and type = 'P') drop procedure insert_servizi_inviati_xcategoria go /*creazione della procedura*/ create proc insert_servizi_inviati_xcategoria /*parametri : */ /*chiave di abbonamento*/ @vCF_PIVA varchar(20), @vCATEGORIA varchar(20), /*chiave del materiale*/ @vTITOLO_MAT_INVIATO varchar(100) as declare @vDURATA_ABBONAMENTO int declare @vDATA_INIZIO_ABBONAMENTO datetime declare @vPUBBLICATO varchar(2) declare @vPRIVATO_MEZZODICOMUNICAZIONE varchar(2) declare @vCATEGORIA_SERVIZIO varchar(20) declare @vTIPO_MATERIALE varchar(20) /* Controllo se la categoria del titolo che si vuole prelevare è uguale alla categoria dell' abbonamento*/ select @vCATEGORIA_SERVIZIO = CATEGORIA, @vTIPO_MATERIALE = TIPO_MATERIALE from MATERIALE where TITOLO = @vTITOLO_MAT_INVIATO



49

if @vCATEGORIA <> @vCATEGORIA_SERVIZIO begin RAISERROR('errore : titolo non appartenente alla categoria del proprio abbonamento !!! ', 16, 1) return end /* Controllo se il materiale è stato pubblicato */ if not exists (select * from MATERIALE_PUBBLICATO where TITOLO_MAT_PUBBLICATO = @vTITOLO_MAT_INVIATO) begin RAISERROR('errore : Si possono prelevare solo servizi pubblicati!!!', 16, 1) return end /*Prelevo da ABBONAMENTI_PER_CATEGORIA la durata dell' abbonamento*/ set @vDURATA_ABBONAMENTO = ( select DURATA_ABBONAMENTO from ABBONAMENTI_PER_CATEGORIA where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA ) /*Prelevo da ABBONAMENTI la data di inizio dell' abbonamento*/ set @vDATA_INIZIO_ABBONAMENTO = ( select DATA_INIZIO_ABBONAMENTO from ABBONAMENTI where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA ) /*Controllo se l' abbonamento è scaduto */ if getdate() > (@vDATA_INIZIO_ABBONAMENTO + @vDURATA_ABBONAMENTO) begin RAISERROR(' ABBONAMENTO SCADUTO ! ', 16, 1) exec cancella_abbonamento @vCF_PIVA, @vCATEGORIA return end /*Se il cliente è un cliente privato allora PUBBLICATO = null Se il cliente è un mezzo privato allora PUBBLICATO = no come valore di default */ set @vPUBBLICATO = null if 'MC' = (select PRIVATO_MEZZODICOMUNICAZIONE from CLIENTI where CF_PIVA = @vCF_PIVA ) begin set @vPUBBLICATO = 'no' end begin transaction insert into SERVIZI_INVIATI (CF_PIVA, CATEGORIA, TITOLO_MAT_INVIATO,



PUBBLICATO) values (@vCF_PIVA, @vCATEGORIA, @vTITOLO_MAT_INVIATO, @vPUBBLICATO) if @@error <> 0 begin print 'errore !!! ' rollback tran return end /*se il servizio prelevato è un articolo inserisco in SERVIZI_INVIATI anche tutte le foto da esso contenute*/ if @vTIPO_MATERIALE = 'ARTICOLO' begin declare @vTITOLO_FOTO_CONTENUTA varchar(100) /* creo un cursore contenente tutte le fotografie contenute nell'articolo*/ declare CUR_FOTO_CONTENUTE cursor for select TITOLO_FOTO from CONTIENE where TITOLO_ARTICOLO = @vTITOLO_MAT_INVIATO open CUR_FOTO_CONTENUTE fetch next from CUR_FOTO_CONTENUTE into @vTITOLO_FOTO_CONTENUTA while (@@FETCH_STATUS = 0) begin insert into SERVIZI_INVIATI (CF_PIVA, CATEGORIA, TITOLO_MAT_INVIATO,

PUBBLICATO) values (@vCF_PIVA, @vCATEGORIA, @vTITOLO_FOTO_CONTENUTA,

@vPUBBLICATO) if @@error <> 0 begin close CUR_FOTO_CONTENUTE deallocate CUR_FOTO_CONTENUTE print 'errore !!! ' rollback tran return end fetch next from CUR_FOTO_CONTENUTE into @vTITOLO_FOTO_CONTENUTA end close CUR_FOTO_CONTENUTE deallocate CUR_FOTO_CONTENUTE end commit transaction



51

go • Stored procedure per la memorizzazione dei servizi inviati ad un abbonamento cu-

mulativo La procedura inserisce nel database le informazioni relative a un servizio inviato a un cliente. L’ inserimento avviene in cascata nelle tabelle DISPONIBILI-TA_GIORNALIERE, DISPONIBILITA_DI_NOTIZIE (se si è prelevata una notizia), DISPONIBILITA_DI_ARTICOLI (se si è prelevato un articolo). La procedura controlla se il materiale prelevato è della stessa categoria dell’abbonamento. Viene aggiornato il da-to derivato N_SERVIZI_PRELEVATI. Se l’abbonamento è scaduto viene eliminato dal database. I parametri passati sono: codice fiscale o partita iva del cliente, categoria alla quale si ri-ferisce l’abbonamento, titolo del servizio da inviare.

if exists (select NAME from sysobjects where NAME = 'insert_servizi_inviati_xcumulativo' and type = 'P') drop procedure insert_servizi_inviati_xcumulativo go /*creazione della procedura*/ create proc insert_servizi_inviati_xcumulativo /*parametri : */ /*chiave di abbonamento*/ @vCF_PIVA varchar(20), @vCATEGORIA varchar(20), /*chiave del materiale*/ @vTITOLO_MAT_INVIATO varchar(100) as declare @vN_SERVIZI_ABBONAMENTO int declare @vN_SERVIZI_PRELEVATI int declare @vCATEGORIA_MATERIALE varchar(20) declare @vTIPO_MATERIALE varchar(20) declare @vGIORNO datetime /* Seleziono da materiale la categoria e il tipo di materiale */ select @vCATEGORIA_MATERIALE = CATEGORIA, @vTIPO_MATERIALE = TIPO_MATERIALE from MATERIALE where TITOLO = @vTITOLO_MAT_INVIATO /* Controllo se la categoria del titolo che si vuole prelevare è uguale alla categoria dell' abbonamento*/ if @vCATEGORIA <> ( select CATEGORIA from MATERIALE where TITOLO = @vTITOLO_MAT_INVIATO ) begin



RAISERROR('errore : titolo non appartenente alla categoria del proprio abbonamento !!! ',16 ,1) return end if (@vTIPO_MATERIALE <> 'NOTIZIA') and (@vTIPO_MATERIALE <> 'ARTICOLO') begin

RAISERROR('errore : Con l'' abbonamento cumulativo si possono prelevare solo NOTIZIE

e ARTICOLI !!! ',16 ,1) return end /* Controllo se il materiale è stato pubblicato */ if not exists (select * from MATERIALE_PUBBLICATO where TITOLO_MAT_PUBBLICATO = @vTITOLO_MAT_INVIATO) begin RAISERROR('errore : Si possono prelevare solo NOTIZIE e ARTICOLI pubblicati!!!', 16, 1) return end /* Prelevo da ABBONAMENTI_CUMULATIVI il N_SERVIZI_ABBONAMENTO e N_SERVIZI_PRELEVATI */ select @vN_SERVIZI_ABBONAMENTO = N_SERVIZI_ABBONAMENTO, @vN_SERVIZI_PRELEVATI = N_SERVIZI_PRELEVATI from ABBONAMENTI_CUMULATIVI where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA /* Controllo se si è superata la quota a disposizione di servizi che si possono prelevare */ if @vN_SERVIZI_PRELEVATI >= @vN_SERVIZI_ABBONAMENTO begin RAISERROR('errore : ABBONAMENTO SCADUTO ! ',16 ,1) exec cancella_abbonamento @vCF_PIVA, @vCATEGORIA return end /* @vGIORNO conterrà la data odierna senza l' orario */ set @vGIORNO = (str(datepart (yy,getdate()),4) ) + '-' + str(datepart (mm,getdate()),2) + '-' + str(datepart (dd,getdate()),2) /* Se il tipo di materiale prelevato è una notizia */ if (@vTIPO_MATERIALE = 'NOTIZIA') begin /* Controllo se si è superata la disponibilità max di notizie prelevabili giornalmente */ if ( 20 <= (select count(*) from DISPONIBILITA_DI_NOTIZIE where CF_PIVA = @vCF_PIVA



53

and CATEGORIA = @vCATEGORIA) ) begin RAISERROR('errore : Finita la disponibilità giornaliera di notizie ! ', 16, 1) return end begin transaction /* Se è il primo servizio del giorno prelevato allora devo inserire la tupla in DISPONIBILITA_GIORNALIERE */ if not exists (select * from DISPONIBILITA_GIORNALIERE where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA and GIORNO = @vGIORNO) begin insert into DISPONIBILITA_GIORNALIERE values (@vCF_PIVA, @vCATEGORIA, @vGIORNO) end /* aggiorno la tabella DISPONIBILITA_DI_NOTIZIE */ insert into DISPONIBILITA_DI_NOTIZIE values (@vCF_PIVA, @vCATEGORIA, @vGIORNO, @vTITOLO_MAT_INVIATO) /* aggiorno il numero di servizi prelevati */ update ABBONAMENTI_CUMULATIVI set N_SERVIZI_PRELEVATI = N_SERVIZI_PRELEVATI + 1 where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA end /* Se il tipo di materiale prelevato è un articolo */ if (@vTIPO_MATERIALE = 'ARTICOLO') begin /* Controllo se si è superata la disponibilità max di articoli prelevabili giornalmente */ if ( 10 <= (select count(*) from DISPONIBILITA_DI_ARTICOLI where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA) ) begin RAISERROR('errore : Finita la disponibilità giornaliera di articoli ! ', 16, 1) return end begin transaction /* Se è il primo servizio del giorno prelevato allora devo inserire la tupla in DISPONIBILITA_GIORNALIERE */ if not exists (select * from DISPONIBILITA_GIORNALIERE where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA and GIORNO = @vGIORNO) begin insert into DISPONIBILITA_GIORNALIERE values (@vCF_PIVA, @vCATEGORIA, @vGIORNO)



end /* aggiorno la tabella DISPONIBILITA_DI_ARTICOLI */ insert into DISPONIBILITA_DI_ARTICOLI values (@vCF_PIVA, @vCATEGORIA, @vGIORNO, @vTITOLO_MAT_INVIATO) /* aggiorno il numero di servizi prelevati */ update ABBONAMENTI_CUMULATIVI set N_SERVIZI_PRELEVATI = N_SERVIZI_PRELEVATI + 1 where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA end commit transaction go



55

5.3 IMPLEMENTAZIONI DELLE INTERROGAZIONI INDICATE DA SPECIFICHE 5.3.1 OPERAZIONE 1 • Stored procedure per la ricerca delle ultime X notizie pubblicate

La procedura inserisce in una tabella temporanea le ultime X notizie pubblicate in ordine decrescente di data pubblicazione. È risultato necessario l’utilizzo di una tabella tempora-nea per poter passare all’interfaccia il risultato della ricerca. Il parametro passato è il numero di notizie da visualizzare.

if exists (select NAME from sysobjects where NAME = 'visualizza_notizie_pubblicate' and type = 'P') drop procedure visualizza_notizie_pubblicate go /*creazione della procedura*/ create proc visualizza_notizie_pubblicate @vMAX_NOTIZIE_VISUALIZZABILI int as declare @vTITOLO varchar(100) declare @vDATA_PUBBLICAZIONE datetime declare @vCONT int /*declare @vURL varchar(100)*/ /*creo una tabella temporanea*/ create table ##mytable (TITOLO varchar(100) not null, DATA_P datetime not null, constraint PK_mytable primary key (TITOLO)) declare CUR_PER_VIS cursor for select TITOLO, DATA_PUBBLICAZIONE from MATERIALE_PUBBLICATO MP, MATERIALE MT where MT.TIPO_MATERIALE = 'NOTIZIA' and MP.TITOLO_MAT_PUBBLICATO = MT.TITOLO order by DATA_PUBBLICAZIONE desc open CUR_PER_VIS fetch next from CUR_PER_VIS into @vTITOLO, @vDATA_PUBBLICAZIONE set @vCONT = 1 while (@@FETCH_STATUS = 0) and (@vCONT <= @vMAX_NOTIZIE_VISUALIZZABILI)



begin insert into ##mytable (TITOLO, DATA_P) values (@vTITOLO, @vDATA_PUBBLICAZIONE) fetch next from CUR_PER_VIS into @vTITOLO, @vDATA_PUBBLICAZIONE set @vCONT = @vCONT + 1 end close CUR_PER_VIS deallocate CUR_PER_VIS go • Stored procedure per la visualizzazione del risultato della ricerca

La procedura seleziona i dati dalla tabella temporanea. if exists (select NAME from sysobjects where NAME = 'sel_da_tab_temp' and type = 'P') drop procedure sel_da_tab_temp go /*creazione della procedura*/ create proc sel_da_tab_temp as select TITOLO, DATA_P from ##mytable order by DATA_P desc go • Stored procedure per ripristinare l’operazione di ricerca

La procedura elimina la tabella temporanea. /*procedura necessaria per eliminare la tabella temporanea ##mytable*/ if exists (select NAME from sysobjects where NAME = 'elimina_pab_temporanea' and type = 'P') drop procedure elimina_pab_temporanea go /*creazione della procedura*/ create proc elimina_pab_temporanea as drop table ##mytable go 5.3.2 OPERAZIONE 2



57

• Stored procedure per la ricerca delle ultime X notizie pubblicate La procedura inserisce in una tabella temporanea gli ultimi X Articoli e Notizie pubblicate che riguardano una certa categoria di interesse per una determinata area geografica o di una determinata sede. I parametri passati sono: numero di Articoli e/o notizie da visualizzare, categoria, area geografica o sede agenzia.

if exists (select NAME from sysobjects where NAME = 'visualizza_servizi_pubblicati' and type = 'P') drop procedure visualizza_servizi_pubblicati go /*creazione della procedura*/ create proc visualizza_servizi_pubblicati @vMAX_SERVIZI_VISUALIZZABILI int, @vCATEGORIA varchar(40), @vAREAGEOG varchar(40), @vSEDEAG varchar(40) as declare @vTITOLO varchar(100) declare @vCONT int declare @vURL varchar(100) declare @vDATA_PUBBLICAZIONE datetime /*creo una tabella temporanea*/ create table ##temp_op2 (TITOLO varchar(100) not null, constraint PK_mytable primary key (TITOLO)) if @vSEDEAG = '' /* Eseguo la ricerca per categoria e area geografica*/ begin declare CUR_PER_VIS cursor for select TITOLO, DATA_PUBBLICAZIONE from MATERIALE_PUBBLICATO MP, MATERIALE MT, AREE_GEOGRAFICHE ARG where (MT.TIPO_MATERIALE = 'ARTICOLO' or MT.TIPO_MATERIALE = 'NOTIZIA') and MP.TITOLO_MAT_PUBBLICATO = MT.TITOLO and MT.NOME_AREAGEO = ARG.NOME and MT.CATEGORIA = @vCATEGORIA and ARG.NOME = @vAREAGEOG order by DATA_PUBBLICAZIONE desc



open CUR_PER_VIS fetch from CUR_PER_VIS into @vTITOLO, @vDATA_PUBBLICAZIONE set @vCONT = 1 while (@@FETCH_STATUS = 0) and (@vCONT <= @vMAX_SERVIZI_VISUALIZZABILI) begin insert into ##temp_op2 values (@vTITOLO) fetch from CUR_PER_VIS into @vTITOLO, @vDATA_PUBBLICAZIONE set @vCONT = @vCONT + 1 end close CUR_PER_VIS deallocate CUR_PER_VIS end else if @vAREAGEOG = '' /* Eseguo la ricerca per categoria e sede agenzia*/ begin declare CUR_PER_VIS cursor for select MP.TITOLO_MAT_PUBBLICATO, DATA_PUBBLICAZIONE, MT.URL from MATERIALE_PUBBLICATO MP, NOTART NA,MATERIALE MT, GIORNALI-STI_ACCREDITATI GA where MP.TITOLO_MAT_PUBBLICATO = NA.TITOLO and MP.TITOLO_MAT_PUBBLICATO = MT.TITOLO and NA.CF = GA.CF and MT.CATEGORIA = @vCATEGORIA and GA.NOME_SEDE = @vSEDEAG order by DATA_PUBBLICAZIONE desc /* effettuo il join tra NOTART (dove ho tutti gli articoli e notizie redatti dai giornalisti)*/ /* per ottenere il CF dei giornalisti che in GIORNALISTI_ACCREDITATI mi permette di */ /* recuperare la sede di agenzia selezionata e MATERIALE, MATERIALE_PUBBLICATO per recuperare */ /* la categoria selezionata*/ open CUR_PER_VIS fetch next from CUR_PER_VIS into @vTITOLO, @vDATA_PUBBLICAZIONE, @vURL



59

set @vCONT = 1 while (@@FETCH_STATUS = 0) and (@vCONT <= @vMAX_SERVIZI_VISUALIZZABILI) begin insert into ##temp_op2 values (@vTITOLO) fetch next from CUR_PER_VIS into @vTITOLO, @vDATA_PUBBLICAZIONE, @vURL set @vCONT = @vCONT + 1 end close CUR_PER_VIS deallocate CUR_PER_VIS end else /* ERRORE nel caso in cui vengano inseriti contemporaneamente la sede dell'agenzia*/ begin /* e l'area geografica */ print 'errore' end go • Stored procedure per visualizzazione del risultato della ricerca

La procedura seleziona i dati dalla tabella temporanea. /*procedura necessaria per selezionare la tuple dalla tabella temporanea ##temp_op2*/ if exists (select NAME from sysobjects where NAME = 'sel_da_tab_temp_op2' and type = 'P') drop procedure sel_da_tab_temp_op2 go /*creazione della procedura*/ create proc sel_da_tab_temp_op2 as select titolo from ##temp_op2 go • Stored procedure per ripristinare l’operazione di ricerca

La procedura elimina la tabella temporanea. /*procedura necessaria per eliminare la tabella temporanea ##temp_op2*/



if exists (select NAME from sysobjects where NAME = 'elimina_tab_temporanea_temp_op2' and type = 'P') drop procedure elimina_tab_temporanea_temp_op2 go /*creazione della procedura*/ create proc elimina_tab_temporanea_temp_op2 as drop table ##temp_op2 go 5.3.3 OPERAZIONE 3 • Stored procedure per la ricerca di articoli per parola chiave



61

La procedura inserisce in una tabella temporanea gli Articoli che fanno riferimento a una determinata parola chiave. Il riferimento può essere diretto (cioè l' articolo descritto dalla parola chiave oppure associato a una fotografia con quella parola chiave) o indiretto (cioè l' articolo correlato ricorsivamente ad un altro articolo riferito a quella parola chiave). Prima viene eseguita la ricerca degli articoli per i riferimenti diretti, poi per ognuno di questi viene eseguita la procedura ricorsiva “ ricerca_articoli_correlati ” per i riferimenti indiretti. Il parametro passato è la parola chiave.

if exists (select NAME from sysobjects where NAME = 'ricerca_per_chiave' and type = 'P') drop procedure ricerca_per_chiave go /*creazione della procedura*/ create proc ricerca_per_chiave @vPAROLA_CHIAVE varchar(20) as /* Creo la tabella temporanea globale dove andrà a finire il risultato della ricerca */ create table ##ARTICOLI_CON_CHIAVE (TITOLO varchar(100) not null, constraint PK_ARTICOLI_CON_CHIAVE primary key (TITOLO)) /* Inserisco nella tabella i titoli degli articoli associati direttamente a una parola chiave*/ insert into ##ARTICOLI_CON_CHIAVE select distinct (ART.TITOLO_ARTICOLO) from ARTICOLI ART, PAROLE_CHIAVE_ARTICOLI PCA, CONTIENE, PARO-LE_CHIAVE_FOTO PCF where (PCA.PAROLA_CHIAVE = @vPAROLA_CHIAVE and ART.TITOLO_ARTICOLO = PCA.TITOLO_ARTICOLO) or (PCF.PAROLA_CHIAVE = @vPAROLA_CHIAVE and CONTIENE.TITOLO_FOTO = PCF.TITOLO_FOTO and ART.TITOLO_ARTICOLO = CONTIENE.TITOLO_ARTICOLO) /**/ /*Inserisco in ARTICOLI_CON_CHIAVE gli articoli correlati*/ declare @vTITOLO varchar(100) /*Dichiaro il cursore*/ declare CUR_ARTICOLI_CON_CHIAVE cursor for select * from ##ARTICOLI_CON_CHIAVE open CUR_ARTICOLI_CON_CHIAVE fetch next from CUR_ARTICOLI_CON_CHIAVE



into @vTITOLO /* Per ogni titolo trovato con la ricerca diretta eseguo una ricerca degli articoli correlati */ while (@@FETCH_STATUS = 0) begin /* Ricerco gli articoli correlati*/ exec ricerca_articoli_correlati @vTITOLO /* Passo al titolo dell'articolo sucessivo */ fetch next from CUR_ARTICOLI_CON_CHIAVE into @vTITOLO end close CUR_ARTICOLI_CON_CHIAVE deallocate CUR_ARTICOLI_CON_CHIAVE go • Stored procedure per la ricerca degli articoli correlati a un articolo

La procedura inserisce in una tabella temporanea tutti gli Articoli correlati all’articolo passato come parametro. La ricerca avviene in modo ricorsivo ed evita ciclicità infinite. Il parametro passato è il titolo dell’articolo.

/*Questa procedura inserisce in ##ARTICOLI_CON_CHIAVE tutti gli articoli correlati all' articolo passato come parametro*/ if exists (select NAME from sysobjects where NAME = 'ricerca_articoli_correlati' and type = 'P') drop procedure ricerca_articoli_correlati go /*creazione della procedura*/ create proc ricerca_articoli_correlati as go alter proc ricerca_articoli_correlati @vTITOLO varchar(100) as declare @vTITOLO_CORRELATO varchar(100) /*Dichiaro una variabile cursore */ declare @vCUR_ARTICOLI_CORRELATI cursor



63

/* La variabile cursore conterrà tutti gli articoli che fanno riferimento all'articolo @vTITOLO passato come parametro*/ set @vCUR_ARTICOLI_CORRELATI = cursor for select ARTICOLO_FROM from RIFERIMENTI_ARTICOLI where ARTICOLO_TO = @vTITOLO open @vCUR_ARTICOLI_CORRELATI fetch next from @vCUR_ARTICOLI_CORRELATI into @vTITOLO_CORRELATO /*ripeto il ciclo finchè il cursore è alla fine della tabella */ while (@@FETCH_STATUS = 0) begin /* Se il titolo dell'articolo non esiste nella tabella lo inserisco e richiamo la procedura ricerca_articoli_correlati che mi ricerca gli articoli correlati a questo titolo*/ /* Nota: Questo controllo serve per evitare ciclicità infinite*/ if not exists (select * from ##ARTICOLI_CON_CHIAVE where TITOLO = @vTITOLO_CORRELATO) begin insert into ##ARTICOLI_CON_CHIAVE values (@vTITOLO_CORRELATO) exec ricerca_articoli_correlati @vTITOLO_CORRELATO end /*Avanzo il cursore*/ fetch next from @vCUR_ARTICOLI_CORRELATI into @vTITOLO_CORRELATO end close @vCUR_ARTICOLI_CORRELATI deallocate @vCUR_ARTICOLI_CORRELATI go • Stored procedure per la ricerca di articoli per parola chiave e categoria in materiale

pubblicato Questa procedura richiama la procedura “ ricerca_per_chiave ” e sul risultato seleziona gli articoli pubblicati della categoria passata come parametro. I parametri passati sono: parola chiave e categoria.

if exists (select NAME from sysobjects where NAME = 'ricerca_per_chiave_cat_mat_pubbl' and type = 'P') drop procedure ricerca_per_chiave_cat_mat_pubbl go /*creazione della procedura*/ create proc ricerca_per_chiave_cat_mat_pubbl



@vPAROLA_CHIAVE varchar(20), @vCATEGORIA varchar(20) as exec ricerca_per_chiave @vPAROLA_CHIAVE /* Il risultato della ricerca va nella tabella temporanea locale ##ARTICOLI_CON_CHIAVE */ /* Ora cancello dalla tabella ##ARTICOLI_CON_CHIAVE gli articoli che non sono della categoria "@vCATEGORIA" e che non sono stati pubblicati */ delete from ##ARTICOLI_CON_CHIAVE where TITOLO not in (select M.TITOLO from MATERIALE M, MATERIALE_PUBBLICATO MP where M.CATEGORIA = @vCATEGORIA and M.TITOLO = MP.TITOLO_MAT_PUBBLICATO) go • Stored procedure per visualizzazione del risultato della ricerca

La procedura seleziona i dati dalla tabella temporanea. if exists (select NAME from sysobjects where NAME = 'ricerca_per_chiave_seleziona' and type = 'P') drop procedure ricerca_per_chiave_seleziona go /*creazione della procedura*/ create proc ricerca_per_chiave_seleziona as select TITOLO from ##ARTICOLI_CON_CHIAVE go • Stored procedure per ripristinare l’operazione di ricerca

La procedura elimina la tabella temporanea. if exists (select NAME from sysobjects where NAME = 'ricerca_per_chiave_cancella' and type = 'P') drop procedure ricerca_per_chiave_cancella go /*creazione della procedura*/ create proc ricerca_per_chiave_cancella as drop table ##ARTICOLI_CON_CHIAVE go



65

5.3.4 OPERAZIONE 4 • Stored procedure visualizza i giornalisti più produttivi di articoli

La procedura visualizza i giornalisti più produttivi: dal giornalista che ha pubblicato più articoli a quello che ne ha pubblicati meno.

if exists (select NAME from sysobjects where NAME = 'giornalisti_articoli_produttivi' and type = 'P') drop procedure giornalisti_articoli_produttivi go /*creazione della procedura*/ create proc giornalisti_articoli_produttivi as select GA.USERNAME, GA.NOME, GA.COGNOME, G.N_ARTPUBBL, G.N_ARTPROP from GIORNALISTI G, GIORNALISTI_ACCREDITATI GA where G.CF_GIORNALISTA = GA.CF order by N_ARTPUBBL DESC go • Stored procedure visualizza i giornalisti più produttivi di notizie

La procedura visualizza i giornalisti più produttivi: dal giornalista che ha pubblicato più notizie a quello che ne ha pubblicate meno.

if exists (select NAME from sysobjects where NAME = 'giornalisti_notizie_produttivi' and type = 'P') drop procedure giornalisti_notizie_produttivi go /*creazione della procedura*/ create proc giornalisti_notizie_produttivi as select GA.USERNAME, GA.NOME, GA.COGNOME, G.N_NOTPUBBL, G.N_NOTPROP from GIORNALISTI G, GIORNALISTI_ACCREDITATI GA where G.CF_GIORNALISTA = GA.CF order by N_NOTPUBBL DESC go 5.4 STORED PROCEDURE UTILIZZATE PER L’INTERFACCIA • Stored procedure verifica_login

Tale procedura si occupa di verificare se nelle tabelle CAPI_REDATTORI, GESTO-RI_CLIENTI, GIORNALISTI_ACCREDITATI , CLIENTI sono presenti username e password dell’utente che vuole accedere al database. Se l’utente è registrato si determina



la tipologia d’appartenenza (capo redattore, gestore dei clienti, cliente, giornalista, …), in-fatti ogni utente ha a disposizione un numero limitato e selezionato di azioni, cioè ad ogni utente vengono presentate solo le maschere di propria competenza in modo tale da garan-tire un grado elevato di sicurezza. I parametri passati sono lo username e la password dell’utente.

if exists (select NAME from sysobjects where NAME = 'verifica_login' and type = 'P') drop procedure verifica_login go /*creazione della procedura*/ create proc verifica_login @vUSERNAME varchar(20), @vPASSWORD varchar(10) as declare @vCF char(16) declare @vCF_PIVA varchar(20) /*Controllo se l'utente è un capo redattore*/ set @vCF = (select CF_CAPO_REDATTORE from CAPI_REDATTORI where USERNAME = @vUSERNAME and PASSWORD = @vPASSWORD) if (@vCF <> '') begin select 'CAPO_REDATTORE', @vCF return end /*Controllo se l'utente è un gestore clienti*/ set @vCF = (select CF from GESTORI_CLIENTI where USERNAME = @vUSERNAME and PASSWORD = @vPASSWORD) if (@vCF <> '') begin select 'GESTORE_CLIENTI', @vCF return end /*Controllo se l'utente è un giornalista accreditato*/ set @vCF = (select CF from GIORNALISTI_ACCREDITATI where USERNAME = @vUSERNAME and PASSWORD = @vPASSWORD) if (@vCF <> '')



67

begin select RUOLO, @vCF from GIORNALISTI_ACCREDITATI where CF = @vCF return end /*Controllo se l'utente è un cliente*/ set @vCF_PIVA = (select CF_PIVA from CLIENTI where USERNAME = @vUSERNAME and PASSWORD = @vPASSWORD) if (@vCF_PIVA <> '') begin select 'CLIENTE' ,@vCF_PIVA return end select 'ERRORE' go • Stored procedure seleziona_abbonamento

Questa procedura è utilizzata dal front end per stabilire il tipo d’abbonamento del cliente. In questo modo si personalizza il front end al particola utente. I parametri passati sono il codice fiscale o la partita iva del cliente e la categoria dell’abbonamento che è stato sottoscritto.

if exists (select NAME from sysobjects where NAME = 'seleziona_abbonamento' and type = 'P') drop procedure seleziona_abbonamento go /*creazione della procedura*/ create proc seleziona_abbonamento @vCF_PIVA varchar(20), @vCATEGORIA varchar(20) as if exists (select * from ABBONAMENTI_PER_CATEGORIA where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA ) begin select 'CAT'



return end if exists (select * from ABBONAMENTI_CUMULATIVI where CF_PIVA = @vCF_PIVA and CATEGORIA = @vCATEGORIA ) begin select 'CUM' return end go



69

5.5 GESTIONE DELLA SICUREZZA

La gestione della sicurezza nel database è stata implementata sia attraverso la conces-sione di privilegi (GRANT) diversi in base all’utente, sia attraverso la limitazione del campo d’azione dell’utente stesso fatta a livello di front end (vedi stored procedure verifica_login di-scussa sopra). A tutti gli utenti è ovviamente concessa la possibilità di eseguire la procedura di login e di compiere la sola selezione nelle tabelle CATEGORIE e AREE_GEOGRAFICHE che vengo-no utilizzate in diverse maschere nelle caselle combinate. grant EXECUTE on verifica_login to PUBLIC go grant SELECT on CATEGORIE to PUBLIC go grant SELECT on AREE_GEOGRAFICHE to PUBLIC go In questa fase di realizzazione del database sono previsti cinque utenti: UNO, DUE, TRE, QUATTRO, CINQUE: i primi due sono capi redattori rispettivamente della categoria sport e politica; l’utente TRE è il gestore dei clienti cioè colui che si occupa dell’inserimento di nuo-vi clienti, della sottoscrizione e del rinnovo degli abbonamenti; l’utente QUATTRO è un giornalista; l’utente CINQUE è un cliente privato. I privilegi accordati agli utenti riguardano l’esecuzione delle sole stored procedure di loro competenza. Grant per capi redattori

grant select on CAPI_REDATTORI to UNO, DUE go grant EXECUTE on pubblica_materiale to UNO, DUE go grant EXECUTE on archivia_materiale to UNO, DUE go /*grant per visualizazione servizi pubblicati*/ grant EXECUTE on visualizza_servizi_pubblicati to UNO, DUE go



grant EXECUTE on sel_da_tab_temp_op2 to UNO, DUE go grant EXECUTE on elimina_tab_temporanea_temp_op2 to UNO, DUE go /*grant per la visualizzazione dei giornalisti più produttivi*/ grant EXECUTE on giornalisti_articoli_produttivi to UNO, DUE go grant EXECUTE on giornalisti_notizie_produttivi to UNO, DUE go Grant per gestore clienti

grant EXECUTE on insert_clienti to TRE go grant EXECUTE on SOTTOSCRIZIONE_ABBONAMENTO_XCATEGORIA to TRE go grant EXECUTE on SOTTOSCRIZIONE_ABBONAMENTO_CUMULATIVO to TRE go Grant per giornalisti

grant EXECUTE on insert_articolo to QUATTRO go grant EXECUTE on insert_articolo_correlato to QUATTRO go grant EXECUTE on insert_fotografia_in_articolo to QUATTRO go grant EXECUTE on insert_parola_chiave_articolo to QUATTRO go



71

grant EXECUTE on insert_notizia to QUATTRO go /*grant per visualizazione servizi pubblicati*/ grant EXECUTE on visualizza_servizi_pubblicati to QUATTRO go grant EXECUTE on sel_da_tab_temp_op2 to QUATTRO go grant EXECUTE on elimina_tab_temporanea_temp_op2 to QUATTRO go /*grant per visualizazione ultime notizie pubblicate */ grant EXECUTE on visualizza_notizie_pubblicate to QUATTRO go grant EXECUTE on sel_da_tab_temp to QUATTRO go grant EXECUTE on elimina_pab_temporanea to QUATTRO go Grant per clienti

grant EXECUTE on seleziona_abbonamento to CINQUE go grant EXECUTE on insert_servizi_inviati_xcategoria to CINQUE go grant EXECUTE on insert_servizi_inviati_xcumulativo to CINQUE go grant EXECUTE on ricerca_per_chiave_cat_mat_pubbl to CINQUE go



grant EXECUTE on ricerca_per_chiave_seleziona to CINQUE go grant EXECUTE on ricerca_per_chiave_cancella to CINQUE go grant EXECUTE on ricerca_per_chiave to CINQUE go grant EXECUTE on ricerca_articoli_correlati to CINQUE go 5.6 CREAZIONE TRIGGER • Trigger INSERIMENTO_MATERIALE_PUBBLICATO



73

Il trigger INSERIMENTO_MATERIALE_PUBBLICATO verifica che l’utente capo redatto-re pubblichi solamente il materiale della categoria di propria pertinenza. Nel caso in cui il trigger rilevi un’incompatibilità tra la categoria del capo redattore e del materiale che si vuole pubblicare, viene segnalato l’errore con un messaggio, tramite la primitiva RAISERROR, e si effettua il ROLLBACK della transazione. if exists (select [name]from sysobjects where [name]='INSERIMENTO_MAT_PUBBLICATO'and type='TR') drop trigger [INSERIMENTO_MAT_PUBBLICATO] go create trigger INSERIMENTO_MAT_PUBBLICATO on MATERIALE_PUBBLICATO for insert as declare @vTITOLO_MAT_PUBBLICATO varchar(100) declare @vDATA_PUBBLICAZIONE datetime declare @vCF_CAPO_REDATTORE char(16) declare @vCORRETTO varchar(2) select @vTITOLO_MAT_PUBBLICATO = inserted.TITOLO_MAT_PUBBLICATO, @vDATA_PUBBLICAZIONE = inserted.DATA_PUBBLICAZIONE, @vCF_CAPO_REDATTORE = inserted.CF_CAPO_REDATTORE , @vCORRETTO = inserted.CORRETTO from inserted /*controllo se la categoria del capo redattore corrisponde a quella del materiale pubblicato*/ if NOT EXISTS (select * from MATERIALE, CAPI_REDATTORI ,inserted where inserted.TITOLO_MAT_PUBBLICATO = MATERIALE.titolo and inserted.CF_CAPO_REDATTORE = CA-PI_REDATTORI.CF_CAPO_REDATTORE and MATERIALE.categoria = CAPI_REDATTORI.categoria) begin raiserror ('Errore: il capo redattore non è della stessa categoria del materiale!!!', 16, 1) rollback transaction end go • Trigger INSERIMENTO_MAT_ARCHIVIATO Per quanto riguarda l’inserimento del materiale archiviato si possono fare analoghe conside-razioni a quanto visto sopra.



if exists (select [name]from sysobjects where [name]='INSERIMENTO_MAT_ARCHIVIATO'and type='TR') drop trigger [INSERIMENTO_MAT_ARCHIVIATO] go create trigger INSERIMENTO_MAT_ARCHIVIATO on MATERIALE_ARCHIVIATO for insert as declare @vTITOLO_MAT_ARCHIVIATO varchar(100) declare @vCF_CAPO_REDATTORE char(16) declare @vCORRETTO varchar(2) select @vTITOLO_MAT_ARCHIVIATO = inserted.TITOLO_MAT_ARCHIVIATO, @vCF_CAPO_REDATTORE = inserted.CF_CAPO_REDATTORE , @vCORRETTO = inserted.CORRETTO from inserted /*controllo se la categoria del capo redattore corrisponde a quella del materiale archiviato*/ if NOT EXISTS (select * from MATERIALE, CAPI_REDATTORI ,inserted where inserted.TITOLO_MAT_ARCHIVIATO = MATERIALE.titolo and inserted.CF_CAPO_REDATTORE = CA-PI_REDATTORI.CF_CAPO_REDATTORE and MATERIALE.categoria = CAPI_REDATTORI.categoria) begin raiserror ('Errore: il capo redattore non è della stessa categoria del materiale!!!', 16, 1) rollback transaction end go • Trigger INSERIMENTO_IN_CONTIENE



75

Il trigger INSERIMENTO_IN_CONTIENE verifica che la fotografia contenuta in un articolo sia della stessa categoria. Se viene rilevata un’anomalia si segnala l’errore con un messaggio e si effettua il ROLLBACK della transazione. Nel trigger si utilizzano le viste CATART, CATFOTO.

if exists (select [name]from sysobjects where [name]='INSERIMENTO_IN_CONTIENE'and type='TR') drop trigger [INSERIMENTO_IN_CONTIENE] go create trigger INSERIMENTO_IN_CONTIENE on CONTIENE for insert as declare @vTITOLO_ARTICOLO varchar(100) declare @vTITOLO_FOTO varchar(100) select @vTITOLO_ARTICOLO = inserted.TITOLO_ARTICOLO, @vTITOLO_FOTO = inserted.TITOLO_FOTO from inserted if NOT EXISTS (select * from CATART, CATFOTO, inserted where inserted.TITOLO_ARTICOLO = CATART.TITOLO and inserted.TITOLO_FOTO = CATFOTO.TITOLO and CATART.CATEGORIA = CATFOTO.CATEGORIA) begin raiserror ('Errore: foto non inserita perchè la categoria è diversa da quella dell`articolo!!!', 16, 1) rollback transaction end go



• Triggers per controllo username in GIORNALISTI_ACCREDITATI, GESTO-RI_CLIENTI, CAPI_REDATTORI, CLIENTI

I quattro triggers seguenti verificano l’unicità dello username all’interno del database. L’attributo username, originariamente caratterizzante l’entità UTENTI, è stato ereditato dopo il ‘collasso verso il basso’ dalle entità GIORNALISTI_ACCREDITATI, GESTO-RI_CLIENTI, CAPI_REDATTORI, CLIENTI Trigger unico_username_ga if exists (select [name]from sysobjects where [name]='unico_username_ga'and type='TR') drop trigger [unico_username_ga] go create trigger unico_username_ga on GIORNALISTI_ACCREDITATI for insert as if EXISTS ( select * from CAPI_REDATTORI CP, inserted where CP.USERNAME = inserted.USERNAME) begin raiserror ('Errore: Username già presente !!!', 16, 1) rollback transaction end if EXISTS ( select * from GESTORI_CLIENTI GC, inserted where GC.USERNAME = inserted.USERNAME) begin raiserror ('Errore: Username già presente !!!', 16, 1) rollback transaction end if EXISTS ( select * from CLIENTI CL, inserted where CL.USERNAME = inserted.USERNAME) begin raiserror ('Errore: Username già presente !!!', 16, 1) rollback transaction end go



77

• Trigger unico_username_cr if exists (select [name]from sysobjects where [name]='unico_username_cr'and type='TR') drop trigger [unico_username_cr] go create trigger unico_username_cr on CAPI_REDATTORI for insert as if EXISTS ( select * from GIORNALISTI_ACCREDITATI GA, inserted where GA.USERNAME = inserted.USERNAME) begin raiserror ('Errore: Username già presente !!!', 16, 1) rollback transaction end if EXISTS ( select * from GESTORI_CLIENTI GC, inserted where GC.USERNAME = inserted.USERNAME) begin raiserror ('Errore: Username già presente !!!', 16, 1) rollback transaction end if EXISTS ( select * from CLIENTI CL, inserted where CL.USERNAME = inserted.USERNAME) begin raiserror ('Errore: Username già presente !!!', 16, 1) rollback transaction end go • Trigger unico_username_gc if exists (select [name]from sysobjects where [name]='unico_username_gc'and type='TR') drop trigger [unico_username_gc] go create trigger unico_username_gc on GESTORI_CLIENTI for insert as if EXISTS ( select * from GIORNALISTI_ACCREDITATI GA, inserted



where GA.USERNAME = inserted.USERNAME) begin raiserror ('Errore: Username già presente !!!', 16, 1) rollback transaction end if EXISTS ( select * from CAPI_REDATTORI CP, inserted where CP.USERNAME = inserted.USERNAME) begin raiserror ('Errore: Username già presente !!!', 16, 1) rollback transaction end if EXISTS ( select * from CLIENTI CL, inserted where CL.USERNAME = inserted.USERNAME) begin raiserror ('Errore: Username già presente !!!', 16, 1) rollback transaction end go • Trigger unico_username_cl if exists (select [name]from sysobjects where [name]='unico_username_cl'and type='TR') drop trigger [unico_username_cl] go create trigger unico_username_cl on CLIENTI for insert as if EXISTS ( select * from GIORNALISTI_ACCREDITATI GA, inserted where GA.USERNAME = inserted.USERNAME) begin raiserror ('Errore: Username già presente !!!', 16, 1) rollback transaction end if EXISTS ( select * from CAPI_REDATTORI CP, inserted where CP.USERNAME = inserted.USERNAME) begin raiserror ('Errore: Username già presente !!!', 16, 1) rollback transaction end



79

if EXISTS ( select * from GESTORI_CLIENTI GC, inserted where GC.USERNAME = inserted.USERNAME) begin raiserror ('Errore: Username già presente !!!', 16, 1) rollback transaction end go • Triggers per controllo codice fiscale in CAPI_REDATTORI, GESTORI_CLIENTI,

GIORNALISTI_ACCREDITATI. I seguenti tre trigger verificano che il cf inserito in una delle tabelle CAPI_REDATTORI, GESTORI_CLIENTI, GIORNALISTI_ACCREDITATI non sia già presente nelle due rima-nenti. Questi trigger si sono resi necessari in quanto si è optato per un ‘collasso verso il bas-so’ della gerarchia PERSONALE. • Trigger unico_cf_ga if exists (select [name]from sysobjects where [name]='unico_cf_ga'and type='TR') drop trigger [unico_cf_ga] go create trigger unico_cf_ga on GIORNALISTI_ACCREDITATI for insert as if EXISTS ( select * from CAPI_REDATTORI CP, inserted where CP.CF_CAPO_REDATTORE = inserted.CF) or EXISTS ( select * from GESTORI_CLIENTI GC, inserted where GC.CF = inserted.CF) begin raiserror ('Errore: Codice Fiscale già presente !!!', 16, 1) rollback transaction end go • Trigger unico_cf_cr if exists (select [name]from sysobjects where [name]='unico_cf_cr'and type='TR') drop trigger [unico_cf_cr] go



create trigger unico_cf_cr on CAPI_REDATTORI for insert as if EXISTS ( select * from GIORNALISTI_ACCREDITATI GA, inserted where GA.CF = inserted.CF_CAPO_REDATTORE) OR EXISTS ( select * from GESTORI_CLIENTI GC, inserted where GC.CF = inserted.CF_CAPO_REDATTORE) begin raiserror ('Errore: Codice Fiscale già presente !!!', 16, 1) rollback transaction end go • Trigger unico_cf_gc if exists (select [name]from sysobjects where [name]='unico_cf_gc' and type='TR') drop trigger [unico_cf_gc] go create trigger unico_cf_gc on GESTORI_CLIENTI for insert as if EXISTS ( select * from GIORNALISTI_ACCREDITATI GA, inserted where GA.CF = inserted.CF) or EXISTS ( select * from CAPI_REDATTORI CR, inserted where CR.CF_CAPO_REDATTORE = inserted.CF) begin

raiserror ('Errore: Codice Fiscale già presente !!!', 16, 1) rollback transaction

end go • Triggers per controllo della disponibilità giornaliera di articoli

Il seguente trigger controlla che non venga superata la disponibilità giornaliera di articoli, cioè controlla che venga mantenuta la cardinalità (0,10) fra l'entità DISPONIBILI-TA_GIORNALIERE e l'associazione DISPONIBILITA_DI_ARTICOLI.

if exists (select [name]from sysobjects where [name]='cardinal_0_10_disp_articoli' and type='TR') drop trigger [cardinal_0_10_disp_articoli]



81

go create trigger cardinal_0_10_disp_articoli on DISPONIBILITA_DI_ARTICOLI for insert as if 10 <= ( select count(*) from DISPONIBILITA_DI_ARTICOLI DDA, inserted where DDA.CF_PIVA = inserted.CF_PIVA and DDA.CATEGORIA = inserted.CATEGORIA and DDA.GIORNO = inserted.GIORNO) begin raiserror ('Finita la disponibilità giornaliera di articoli !!!', 16, 1)

rollback transaction end go • Triggers per controllo della disponibilità giornaliera di notizie

Il seguente trigger controlla che non venga superata la disponibilità giornaliera di notizie, ovvero controlla che venga mantenuta la cardinalità (0,20) fra l'entità DISPONIBILI-TA_GIORNALIERE e l'associazione DISPONIBILITA_DI_NOTIZIE.

if exists (select [name]from sysobjects where [name]='cardinal_0_20_disp_notizie' and type='TR') drop trigger [cardinal_0_20_disp_notizie] go create trigger cardinal_0_20_disp_notizie on DISPONIBILITA_DI_NOTIZIE for insert as if 20 <= ( select count(*) from DISPONIBILITA_DI_NOTIZIE DDN, inserted where DDN.CF_PIVA = inserted.CF_PIVA and DDN.CATEGORIA = inserted.CATEGORIA and DDN.GIORNO = inserted.GIORNO) begin raiserror ('Finita la disponibilità giornaliera di notizie !!!', 16, 1)

rollback transaction end go 6. CONNESSIONE ADO



Per la comunicazione fra la base di dati in SQL SERVER 7.0 ed il front-end implementato in MSAccess e Visual Basic si è utilizzata la connessione ADO (ActiveX Data Objects). ADO è un modello ad oggetti sviluppato su ActiveX, caratteristica che lo rende Internet Oriented. ADO è una DLL basata su OLE DB che è uno standard che consente alle applicazioni di dia-logare con qualsiasi fonte di dati, sia essa relazionale (come nel nostro caso) o meno. I principali oggetti di ADO sono: • Connection: rappresenta la connessione con un data provider; • Recordset: contiene informazioni, nel nostro caso il risultato di query, relative al data

provider; • Command: consente l’esecuzione di comandi sul data provider; • Error: contiene informazioni sugli errori che possono verificarsi eseguendo connessioni o

comandi verso il data provider. Per effettuare e gestire la connessione al database creato in SQL SERVER 7.0 si è usato l’oggetto Connection. Al caricamento di ciascuna form si è aperta la connessione al database utilizzando il seguente codice: Dim cn As New ADODB.Connection si dichiara la variabile cn come A-DODB.Connection Dim cmd As New ADODB.Command si dichiara la variabile cmd come A-

DODB.Command Dim stringa_di_connessione As String la variabile stringa_di_connessione contiene i

parametri necessari per effettuare la connessione cn.Provider = “SQLOLEDB” stringa_di_connessione = “Server = NOUMENO;Database = baralf Database;UID = userna-

me dell’utente che effettua la connessione;PWD = password dell’utente che effettua la connessione”;

cn.Open stringa_di_connessione Set cmd.ActiveConnection = cn A questo punto si è stabilita la connessione con SQL SERVER 7.0 Alla chiusura del form si è chiusa la connessione con la seguente istruzione: cn.Close

1. SPECIFICHE PROGETTUALI · Tesina Basi di Dati – Anno Accademico 1999/2000 GRUPPO 2: Baraldi...

Documents

Transcript of 1. SPECIFICHE PROGETTUALI · Tesina Basi di Dati – Anno Accademico 1999/2000 GRUPPO 2: Baraldi...