GESTIONE DATI DEL MOTOMONDIALE

GRAND PRIX (GESTIONE DATI DEL MOTOMONDIALE)
Introduzione
Per la realizzazione di un buon progetto è innanzitutto necessaria una conoscenza approfondita dei
requisiti necessari, da la scelta dell’argomento.
La scelta del DBMS è ricaduta su SQL Server 7.0, ultima versione prodotta da Microsoft del sistema
per la gestione di database relazionali che funziona in background con Windows NT, Windows 95 o
98 . Tale scelta è dettata dall’enorme diffusione del prodotto e dalla facilità di reperire materiale di
supporto. Microsoft ha introdotto numerose procedure guidate e strumenti di supporto che
permettono sostanzialmente a tutti di accostarsi al prodotto; un’ulteriore spinta alla sua diffusione.
Abbiamo utilizzato principalmente due strumenti di SQL Server 7.0:
• Enterprise Manager
Applicazione che permette di controllare e manipolare quasi tutti gli aspetti della base di dati;
• Query Analizer
Permette di effettuare una qualsiasi istruzione SQL e di visualizzarne i risultati.
L’elemento distintivo e discriminante di un database rimane comunque la progettazione.
Specifiche
Si vogliono gestire i dati relativi al campionato del mondo di motociclismo nelle classi 125, 250 e 500.
Ogni Casa Costruttrice partecipa al campionato fornendo moto ad una o più squadre nelle diverse
classi.
Le moto fornite sono classificate in base al modello, identificato univocamente da una sigla che
permette di risalire alla Casa Costruttrice e dalla classe, e si dividono in ufficiali e clienti: i primi sono
più evoluti e competitivi dei secondi.
Tutte le moto ricevono durante l’anno gli aggiornamenti proposti dalla Casa Costruttrice anche se con
diverse priorità: più alta alle moto dei piloti delle squadre ufficiali ed a quelle di alcuni piloti di squadre
semiufficiali (a seconda dei contratti).
Per ogni modello si deve inoltre riportare il numero di cilindri, mentre altre caratteristiche tecniche,
come ad esempio la cilindrata e il peso, variano in base all’anno in cui un certo modello viene
prodotto.
Le squadre, identificate dal proprio nome, sono composte da un direttore sportivo, che si occupa di
curare i contatti con gli sponsor e di scegliere i piloti, e da un team di meccanici per ogni singolo pilota.
Una stessa squadra può partecipare al campionato in un certo anno in più classi contemporaneamente
e in questo caso riceve per ogni classe la fornitura di un unico tipo di moto con cui può far correre da
uno a tre piloti; le moto fornite a una stessa squadra per le diverse classi devono provenire dalla stessa
Casa Costruttrice.
Ogni Casa Costruttrice può avere, in un anno, una sola squadra ufficiale per classe, ossia una squadra
di cui la Casa Costruttrice stessa è proprietaria.
Le squadre il cui proprietario non è il costruttore della moto sono invece dette non ufficiali; per queste
è il direttore sportivo ad essere proprietario. Per le squadre che partecipano come ufficiali si ha invece
un diverso direttore sportivo per ogni classe al fine di assicurare una gestione differenziata più snella e
più efficiente della squadra ufficiale stessa.
Una squadra non ufficiale è detta semiufficiale se partecipa al campionato con una moto ufficiale,
mentre è detta privata se partecipa con una moto clienti.
1
Nelle squadre ufficiali ho soltanto moto ufficiali dell’anno in corso; mentre una squadra semiufficiale
o privata può partecipare ad un campionato con una moto “vecchia”, ossia con anno di produzione
precedente a quello in cui corre.
Le squadre e i piloti possono avere diversi sponsor che partecipano con una certa quota e una stessa
azienda può a sua volta sponsorizzare più squadre o piloti .
Il campionato consiste di un certo numero di Gran Premi, ognuno dei quali viene identificato dal nome
(Gran Premio d’Italia, Gran Premio di Spagna, …) e può essere disputato una volta sola all’anno
mentre in anni diversi lo stesso Gran Premio può essere disputato anche su circuiti diversi.
Le informazioni riportate per ogni circuito oltre al nome, che e’ univoco, sono la localita’ e la nazione
in cui e’ situato e la lunghezza della pista; su un circuito si puo’ correre al massimo un Gran Premio
nello stesso campionato.
Un Gran Premio si articola poi nello svolgimento di tre gare, una per ogni classe, ognuna con un
numero differente di giri del circuito da effettuare.
Per ogni gara occorre memorizzare la classifica dei piloti con i relativi punti conquistati, l’ordine di
partenza, il pilota che ha effettuato il giro più veloce e il relativo tempo; nell’ordine di partenza e di
arrivo un pilota può occupare una sola posizione ed una posizione può essere occupata da un solo
pilota. Oltre a ciò, per ogni gara, si tiene conto dei ritiri riportando: il pilota ritirato, la causa e il giro in
cui tale ritiro avviene. Si memorizzano poi le squalifiche e le penalità inflitte ai piloti durante la corsa.
E’ interessante tenere inoltre memoria per ogni circuito su cui si e’ disputata almeno una gara del
tempo piu’ basso mai realizzato e del pilota che lo ha ottenuto, nonche’ dell’anno in cui e’ stato
registrato il record e della casa costruttrice fornitrice della moto.
Una parte delicata delle specifiche è rappresentata dal ruolo del proprietario e del direttore sportivo.
Al fine di chiarire i vincoli riportiamo di seguito un approfondimento.
Il direttore sportivo ha tra le proprie mansioni quella di curare i contatti con gli sponsor della squadra
e quella di scegliere i piloti, questo è il motivo per cui nelle squadre più piccole, cioè quelle private, il
proprietario è anche il direttore sportivo; è lui infatti ad occuparsi della ricerca del budget e della scelta
di chi mettere alla guida delle moto nei suoi team.
Al fine di garantire una concorrenza corretta un dirigente può essere proprietario di al massimo una
squadra in un anno.
Nelle squadre ufficiali si ha un diverso direttore sportivo per ogni categoria il quale, dopo aver
selezionato gli sponsor, dovrà confrontarsi con i direttori sportivi della stessa squadra nelle altre
classi, in modo che lo sponsor sovvenzioni la squadra ufficiale in tutte le categorie in cui partecipa.
Un dirigente che ricopra il ruolo di direttore sportivo di una squadra ufficiale relativamente ad una
certa classe non puo’ ricoprire nello stesso anno tale ruolo ne’ in una squadra non ufficiale, ne’
all’interno della stessa squadra ufficiale relativamente alle altre classi.
Operazioni
op1) Inserimento dei dati relativi a una gara.
op2) Visualizzazione delle seguenti classifiche:
a)classifica piloti che riporta il nome del pilota e il punteggio totale;
b)classifica Case Costruttrici che riporta la casa costruttrice e il punteggio totale;
c)classifica parziale che riporta per ogni pilota l’elenco dei circuiti su cui si sono disputate
gare con relativo punteggio ottenuto e il punteggio totale.
2
Op3) Albo d‘oro: per ogni classe si visualizza il nome dei piloti vincitori di ameno un titolo
mondiale e il numero di titoli vinti nelle relativa classe.
Op4) Carriera di un pilota:
a)numero gare vinte,
b)numero gare effettuate,
c)numero giri veloci,
d)posizione finale in classifica per ogni anno,
e)numero mondiali vinti e in che classe.
Op5) Visualizzazione delle caratteristiche di un circuito e relativi record nelle tre classi.
L’Op1 può essere eseguita da una figura di utente che si occupa della manutenzione del database; tale
utente potrà infatti eseguire inserimenti e cancellazioni controllate da triggers e stored procedures.
Le altre sono operazioni di visualizzazione che non comportano modifiche nel DB e sono concesse ad
un utente generico.
I diritti concessi o negati ai vari tipi di utenti saranno approfonditi più avanti in una sezione dedicata.
Volume dei Dati ( 1980 – 2000 )
9
9
9
9
9
9
9
9
9
9
PILOTI -------------- 400
CIRCUITI ----------- 30
GRAN PREMI ------ 20
MOTO --------------- 300
SQUADRE ---------- 200
TECNICI ------------ 1600
SPONSOR ---------- 2000
GARE ---------------- 900
CLASSIFICHE ----- 900 (ciascuna contenente in media 15 piloti)
PART_PIL ---------- 1600
Frequenza delle Operazioni
Operazione 1 : 1,25 al mese
Operazione 2 : 20 al giorno
Operazione 3 : 5 al giorno
Operazione 4 : 10 al giorno
Operazione 5 : 5 al giorno
Progettazione Concettuale
3
La filosofia di progetto che intendiamo seguire è quella di cercare di esprimere il maggior numero
possibile di vincoli a partire già dallo schema E/R.
Tale scelta ci è suggerita sia dal fatto che non abbiamo vincoli di progettazione per quanto riguarda
l’occupazione di memoria e la velocità d’accesso, sia per l’importanza che la fase di progetto in
questione riveste nel corso di Base di Dati.
Scopo di questo paragrafo è, a partire dalla specifica dei requisiti in linguaggio naturale, la
realizzazione dello schema concettuale tramite il modello E/R.
SCHEMA E/R COMPLETO
4
SPIEGAZIONE SULLE SCELTE FATTE
Segue l’analisi dello schema con alcune considerazioni sulle problematiche incontrate e le scelte fatte.
Per una più agevole comprensione riprendiamo lo schema pezzo per pezzo:
•
MOTO
CASA_COSTR
PRODUZIONE
SIGLA
(1,N)
(1,1)
CLASSE
MODELLO
N_CILINDRI
(T,E)
MODUFF
MODCLIENTI
(1,N)
(1,N)
(1,1)
ANNOP
CILIINDRATA
MOTOUFF
PESO
(1,1)
ANNOP
MOTOCLIENTI
CILINDRATA
PESO
La sigla della moto è un codice che identifica la Casa Costruttrice, può essere lo stesso anche in anni
diversi e mi dice se la moto è ufficiale o meno, da questo e dalla classe in cui la moto partecipa posso
ottenere anche informazione sul numero dei cilindri e sulla loro disposizione (4 in linea, 2 a V, etc).
Una stessa casa può partecipare ad un campionato con moto aventi sigle differenti (anche nella stessa
cilindrata). A parità di sigla si hanno generalmente nelle diverse classi diversi numeri di cilindri e a
seconda dell’anno di produzione (ANNOP) si possono avere a parità di classe e sigla variazioni di
caratteristiche dinamiche della moto quali peso e cilindrata (intesa come cubatura effettiva). Alla
gerarchia in figura si applica la traduzione standard.
5
Es: Casa Costruttrice Honda, sigla NSR: sta ad indicare le moto ufficiali della casa giapponese.
•
SQUADRA
(1,1)
SU_
IN_CLASSE
CLASSE
SSU_
IN_CLASSE
CLASSE
(1,1)
SP_
N_CLASSE
CLASSE
(1,1)
DIRSP
(1,1)
(0,N)
(0,N)
DIRIGENTE
(1,3)
(1,3)
(1,3)
PROPR1
(1,1)
CASA_COSTR_TIT
SQUFFICIALE
(0,N)
SQSEMIUFF
SQPRIVATA
PROPR2
(1,1)
NOME_SQ
ANNO
NOME_SP
(T,E)
QUOTA
PART_SQ
SQUADRA
(1,N)
(1,1)
SP_SQ
(0,N)
TIPO_AZ
SPONSOR
(0,N)
Le squadre partecipano in genere a più edizioni del campionato del mondo e vengono identificate con
il loro nome. A seconda dell’anno di partecipazione una squadra può essere ufficiale, semiufficiale o
privata. Può avere rapporti di sponsorizzazione con diverse aziende ognuna delle quali partecipa al
budget con una certa quota. Una squadra può anche autofinanziarsi, è questo il motivo della cardinalità
minima 0.
Nelle squadre semiufficiali o private il proprietario, che è anche direttore sportivo, è una figura
identificata nel database con la qualifica di dirigente. Nelle squadre ufficiali il proprietario è la Casa
Costruttrice che fornisce le moto. Una squadra in uno stesso anno può partecipare in una o più classi
ma tutte le moto che fa correre indipendentemente dalla cilindrata devono essere o ufficiali o clienti.
Ogni squadra a seconda della classe fa parte delle entità SU, SSU o SP_IN_CLASSE che stanno per
SQ.UFF., SQ.SEMIUFF, SQ.PRIVATA.
L’associazione tra DIRIGENTE e SU_IN_CLASSE esprime il fatto che nelle squadre ufficiali si ha
un direttore sportivo per ogni classe; al contrario per gli altri tipi di squadra l’aver legato dirigente
direttamente a SQSEMIUFF e a SQPRIVATA esprime il vincolo che il direttore sportivo sia lo
stesso per le diverse classi.
6
L’attributo “casa_costr_tit” di SQUFFICIALE è un dato derivato, un trigger ne garantisce la coerenza;
si è reso necessario introdurlo per inserire in Relazionale il vincolo che in un anno una Casa
Costruttrice partecipi con non più di una squadra per classe.
Per la gerarchia su PART_SQ si utilizza la traduzione standard.
CILIINDRATA
ANNOP
ANNOP
CILINDRATA
MOTOUFF
MOTOCLIENTI
PESO
PESO
(0,N)
(0,1)
(1,1)
CLASSE
(1,1)
(1,1)
CLASSE
SU_
IN_CLASSE
(0,N)
CLASSE
SSU_
IN_CLASSE
SP_
IN_CLASSE
Le squadre UFFICIALI e SEMIUFFICIALI partecipano al campionato con moto ufficiali, mentre
quelle PRIVATE corrono con moto non ufficiali (MOTOCLIENTI).
Nelle associazioni considerate si evidenzia la fornitura, non l’utilizzo della singola moto: una squadra
ha una sola fornitura, ma può ricevere più moto.
Si noti che una moto ufficiale può essere utilizzata da una sola squadra ufficiale, che è poi la squadra
di proprietà della Casa Costruttrice della moto; inoltre una squadra ufficiale, se partecipa al
campionato, usa solo moto prodotte quell’anno stesso (vincolo risolto in Relazionale). Da tali
considerazioni deriva la cardinalità (0,1) della partecipazione dell’entità MOTOUFF nell’associazione
con SU_IN_CLASSE.
Può accadere che una moto non sia utilizzata nell’anno di produzione, ma in anni seguenti da squadre
non ufficiali; da qui le cardinalità minime nulle.
Il vincolo che una squadra possa partecipare ad un campionato in più classi, ma con moto prodotte
tutte della stessa Casa Costruttrice, sarà realizzato con un trigger.
7
•
PERSONALE
PUNTIP
QUOTA
CLASSE
SP_PIL
PART_PIL
(1,N)
NUMGARA
(0,N)
ANNO
(1,1)
1,1)
1
ANNO
PART_IN_TEAM
(0,N)
RUOLO
(1,1)
(0,N)
DIRIGENTE
COGNOME
PILOTA
NOME
TECNICO
0,N)
(T,S)
TIPO_AZ
COGNOME
NOME
PERSONA
SPONSOR
NAZIONALITA'
LUOGON
(1,1)
DATAN
CODPERS
La gerarchia PERSONA è Totale e Sovrapposta perché accade spesso che un pilota con esperienza a
fine carriera rimanga nel settore assumendo altri ruoli.
Gli attributi “nome” e “cognome” su PILOTA sono dati derivati da PERSONA, quindi bisognerà
controllare che i corrispondenti sulle due entità contengano informazioni sempre coerenti (utilizzo di
apposito trigger).
Abbiamo assunto come chiave alternativa di PILOTA “nome” e “cognome” per rendere più
comprensibili i dati presenti nel database. La scelta è permessa dal fatto che i piloti sono personaggi
di cui si tiene spesso traccia e che sono ricordati quindi con nomi univoci o soprannomi.
ES: KENNY ROBERTS è il nome che hanno in comune due piloti padre e figlio, in questo caso
l’omonimia è risolta riferendo il figlio, che corre tuttora, con KENNY JR. Entrambi hanno avuto
ottimi risultati e quando si fa riferimento ad uno di loro per ricordare un RECORD o un piazzamento
non si hanno confusioni.
8
La chiave di PART_PIL (partecipazione pilota) è rappresentata dalla terna (nome, cognome, anno),
ma esiste una chiave alternativa dovuta al fatto che i numeri di gara, scelti dai piloti, devono essere
univoci in una categoria ed in un anno.
Dallo schema appare evidente l’importanza assunta dal PILOTA nel database, sono infatti ben 9 le
associazioni che coinvolgono la sua partecipazione (cfr schema completo).
“classe “ è un attributo di PART_PIL derivabile dalle associazioni in cui è coinvolta l’entità, ma è
utile riportarlo per poter esprimere alcuni vincoli in Relazionale.
Il discorso da fare sullo SPONSOR è del tutto analogo a quello affrontato nel caso delle squadre.
Si noti il rilassamento delle cardinalità delle associazioni che coinvolgono le entità PILOTA e
TECNICO: vogliamo in questo modo consentire l’inserimento di relativi ad una persona senza
l’obbligo di indicare subito il suo ruolo. Se la cardinalità minima fosse unitaria occorrerebbe far
rispettare la consistenza.
Si noti infine che ogni pilota è seguito da un team di meccanici personale; la coerenza tra gli attributi
“anno” nelle due entità (PART_PIL e PART_IN_TEAM) è affrontato nello Schema Relazionale.
PART_IN_TEAM indica l’appartenenza di un tecnico ad un team nel corso di un campionato.
Per la gerarchia PERSONA si utilizza la traduzione standard.
CLASSE
(1,3)
CLASSE
(0,1)
PIL_SU
SU_
IN_CLASSE
ANNO
NUMGARA
1
PART_PIL
(0,1)
CLASSE
PIL_SSU
SSU_
IN_CLASSE
CLASSE
(1,3)
SP_
IN_CLASSE
PUNTIP
PRIORITA
(0,1)
PIL_SP
(1,3)
Una squadra partecipa ad una categoria con almeno un pilota e al massimo tre. Anche uno solo dei
piloti di una squadra semiufficiale può godere della priorità sugli aggiornamenti tecnici a seconda dei
contratti, cosa di cui godono invece tutti i piloti delle squadre ufficiali.
PIL_SU = partecipazione di un pilota in una squadra ufficiale .
PIL_SSU = partecipazione di un pilota in una squadra semiufficiale .
PIL_SP = partecipazione di un pilota in una squadra privata .
La coerenza tra l’anno di partecipazione di un pilota e quello della squadra in cui corre è realizzata in
relazionale.
9
•
GP
CASA_COSTR
COGNOMEPIL
NOMEPIL
ANNO
RECORD
TEMPO
NOME
GP
CLASSE
(1,1)
(1,N)
(3,3)
LOCALITA
(1,1)
(0,N)
(1,1)
(3,3)
CIRCUITO
LOCAZIONE
(1,1)
LUNGHEZZA
NOMECIRCUITO
GIORNO
MESE
ANNO
CLASSE
GARA
NUMGIRI
Nell’entità RECORD sono riportate per ogni classe le informazioni relative al giro piu’ veloce mai
realizzato su un certo circuito nel corso di tutte le gare ivi disputate; tale entita` è introdotta per
agevolare l’operazione di visualizzazione delle caratteristiche di un circuito e mantiene elementi
derivabili dai dati contenuti nelle altre entita’; consideriamo quindi l’entita` RECORD un dato derivato
delle cui informazioni viene garantita la coerenza tramite triggers.
Ogni GP (Grand Premio) è identificato da un nome univoco, in un anno si può svolgere al più una
volta ed in anni diversi può essere organizzato in circuiti diversi: il GP non è strettamente legato al
CIRCUITO.
Su di un CIRCUITO si può correre al massimo una volta all’anno e nel corso degli anni può essere
utilizzato per GP diversi.
Si noti infine che nel giorno in cui si organizza un GP, non se ne può svolgere un altro e che in tale
occasione si corrono tutte e tre classi (125, 250, 500).
10
Si noti infine la partecipazione opzionale di CIRCUITO nell’associazione con LOCAZIONE: si è
scelto così di non appesantire le operazioni di inserimento e cancellazione. In questo modo non
occorre affrontare il problema della consistenza, perché è permessa l’esistenza nel database di un
CIRCUITO non ancora legato ad un GP.
MOTIVOSQ
(1,N)
PUNTEGGIO
SQUALIFICATO
(1,1)
POS_FIN
PUNTI
CAUSARIT
NON
CLASSIFICATO
CLASSIFICATO
NUMGIRO
(T,E)
(0,30)
(1,1)
CLASSE
CLASSIFICA
GARA
(1,1)
POS_INIZ
(0,N)
(1,1)
NUMGIRI
TIPO
(1,N)
DESCRIZ
CAUSA
GVEL
IN
TEMPO
PENALITA
NUMGIRO
(0,N)
(0,N)
(0,N)
PUNTIP
PART_PIL
NUMGARA
CLASSE
ANNO
In occasione dello svolgimento di una gara occorre archiviare alcuni dati: ordine di partenza e arrivo,
giri veloci, ritiri e penalita’ .
L’ entita’ classifica tiene traccia dei piazzamenti di un determinato pilota in una determinata gara. Una
posizione (finale o iniziale) puo’ essere occupata da un solo PILOTA, analogamente un pilota puo’
occupare in una determinata gara una sola posizione iniziale o finale.
In una gara un pilota puo’ prendere piu’ penalita’ (motivo dell’ impiego di un attributo multiplo e
composto sull’ associazione PENALITA’). Per ogni gara (per ognuna delle tre classi di un GP) viene
riportato il giro veloce, ossia il tempo piu’ basso realizzato durante la corsa.
La chiave alternativa di CLASSIFICATO che coinvolge la posizione finale non e’ modellabile nello
schema rappresentato, il vincolo e’ percio’ realizzato solo in relazionale.
Si noti inoltre che nell’identificatore di CLASSIFICA e’ presente la chiave di GARA ma il gruppo di
attributi che cosi’ ne deriva rappresenta una superchiave per CLASSIFICA; abbiamo quindi
provveduto in relazionale a togliere l’attributo ‘classe’ dalla chiave primaria di tale tabella.
La coerenza tra l’attributo “anno” di GARA, PENALITA, CLASSIFICA ed il corrispondente di
PART_PIL è risolta in Relazionale.
Siccome non tutti i partecipanti riescono a concludere la competizione, abbiamo ritenuto opportuno
introdurre una gerarchia: ai piloti classificati è possibile assegnare i punti conquistati grazie
all’associazione con PUNTEGGIO. Per quanto riguarda i non classificati viene riportata la distinzione
fra squalificati e ritirati.
11
Progettazione Logica
Semplificazione schema: eliminazione gerarchie ed identificatori esterni, normalizzazione di attributi
composti e multipli.
ELIMINAZIONE DELLE GERARCHIE
1) MODELLO
MODELLO
MODUFF
(T,E)
MODUFF
MODCLIENTI
MODCLIENTI
L’entità di generalizzazione MODELLO è stata tradotta col collasso verso il basso: gerarchia totale ed
esclusiva.
Si noti che in questo modo si hanno due associazioni con PRODUZIONE anziché una.
2) PART_SQ
PART_SQ
PART SQ
(0,1)
(0,1)
(1,1)
(1,1)
0,1)
(T,E)
SQUFFICIALE
SQSEMIUFF
SQPRIVATA
SQUFFICIALE
12
SQSEMIUFF
1,1)
SQPRIVATA
Abbiamo scelto la traduzione standard perché sia le entità padre che figlie partecipano a diverse
associazioni e la copertura è totale ed esclusiva.
3) CLASSIFICA
CLASSIFICA
CLASSIFICA
NON
CLASSIFICATO
CLASSIFICATO
NON
CLASSIFICATO
CLASSIFICATO
motivisq
SQUALIFICATO
motivosq(0,1)
Ancora traduzione standard per la gerarchia.
Traduzione SUBSET con collasso verso l’alto.
4) PERSONA
PERSONA
PERSONA
(0,1)
0,1)
(0,1)
(T,E)
(1,1)
DIRIGENTE
PILOTA
(1,1)
1,1)
TECNICO
DIRIGENTE
PILOTA
TECNICO
In questo caso la copertura è totale e sovrapposta, quindi la gerarchia è stata tradotta in modo
standard per evitare ripetizioni e ridondanza di dati.
Si noti che l’ATTRIBUTO COMPOSTO è stato eliminato e si sono considerati i suoi componenti
come attributi semplici.
13
TRASFORMAZIONE DI ATTRIBUTI
Oltre all’attributo composto su PERSONA, abbiamo un attributo COMPOSTO e RIPETUTO sulla
associazione PENALITA.
nordine
(1,N)
DESCRIZ
(1,1)
(0,N)
(0,N)
PENALITA
PENALITA
(1,1)
(0,N)
(0,N)
Anche se improbabile, un pilota può incorrere più volte nella stessa penalità e in più penalità nello
stesso giro. Si rende quindi necessario introdurre un numero d’ordine che identifichi il particolare
evento.
14
Traduzione schema E/R in schema relazionale
CIRCUITO (nomecircuito , località , lunghezza , siglanazione)
RECORD (nomecircuito , classe , cognomepil , nomepil , casa_costr , anno , tempo)
FK : nomecircuito REFERENCES CIRCUITO
GP (nome)
LOCAZIONE (anno , mese , giorno , nomecircuito , nomegp)
AK : anno , nomegp
AK : anno , mese , giorno
FK : nomegp REFERENCES GP
FK : nomecircuito REFERENCES CIRCUITO
PERSONA (codpers , cognome , nome , datan , luogon , nazionalità)
DIRIGENTE (coddirigente)
FK : coddirigente REFERENCES PERSONA
TECNICO (codtecnico)
FK : codtecnico REFERENCES PERSONA
PILOTA (copilota , cognome , nome)
AK : codpilota
FK : codpilota REFERENCES PERSONA
OSSERVAZIONE : “nome” e “cognome” sono attributi derivabili dai corrispondenti dell’entità
PERSONA.
SQUADRA (nome_sq)
OSSERVAZIONE : SQUADRA, come GP, sono tabelle di look-up (forniscono una lista valida di
valori dalla quale scegliere i dati).
PART_SQ (anno , nome_sq)
15
FK : nome_sq REFERENCES SQUADRA
PART_PIL (anno , cognomepil , nomepil , numgara , classe , puntip)
AK : anno , numgara , classe
FK : cognomepil , nomepil REFERENCES PILOTA
OSSERVAZIONE : L’attributo “classe” è un dato derivato caricato da apposito trigger.
GARA (anno , classe , nomecircuito , numgiri , tempo , cognomepil , nomepil)
FK : anno , cognomepil , nomepil REFERENCES PART_PIL
FK : anno , nomecircuito REFERENCES LOCAZIONE
PENALITA (anno , classe , nomecircuito , numgiro , cognomepil , nomepil , nordine , causa , tipo)
FK : anno , cognomepil , nomepil REFERENCES PART_PIL
FK : anno , classe , nomecircuito REFERENCES GARA
CLASSIFICA (anno , classe , nomecircuito , pos_iniz , cognomepil , nomepil)
AK : anno , classe , nomecircuito , pos_iniz
FK : anno , cognomepil , nomepil REFERENCES PART_PIL
FK : anno , classe , nomecircuito REFERENCES GARA
PART_IN_TEAM (anno , cognomepil , nomepil , codtecnico , ruolo)
FK : codtecnico REFERENCES TECNICO
FK : anno , cognomepil , nomepil REFERENCES PART_PIL
OSSERVAZIONE : Nelle ultime quattro tabelle (GARA, PENALITA, CLASSIFICA,
PART_IN_TEAM) compare una sola volta “anno” tra gli attributi; abbiamo così aggiunto un vincolo
che non era in E/R per avere consistenza tra gli anni in cui un pilota ha corso e quelli in cui si vuol
tenere traccia di qualcosa che lo riguarda.
PUNTEGGIO (pos_fin , punti)
CLASSIFICATO (anno , classe , nomecircuito , pos_fin , cognomepil , nomepil)
AK : anno , classe , nomecircuito, pos_fin
FK : anno , nomecircuito , cognomepil , nomepil REFERENCES CLASSIFICA
FK : pos_fin REFERENCES PUNTEGGIO
OSSERVAZIONE : L’AK non era esprimibile nello schema E/R
NONCLASSIFICATO (anno , classe , nomecircuito , numgiro , cognomepil , nomepil , causarit ,
motivosq)
FK : anno , nomecircuito , cognomepil , nomepil REFERENCES CLASSIFICA
PRODUZIONE (sigla , casa_costr)
MODUFF (classe , sigla , n_cilindri)
FK : sigla REFERENCES PRODUZIONE
16
MODCLIENTI (classe , sigla , n_cilindri)
FK : sigla REFERENCES PRODUZIONE
MOTOUFFICIALE (annop , classe , sigla , cilindrata , peso)
FK : classe , sigla REFERENCES MODUFF
MOTOCLIENTI (annop , classe , sigla , cilindrata , peso)
FK : classe , sigla REFERENCES MODCLIENTI
SPONSOR (nome_sp , tipo_az)
SP_SQ (anno , nome_sq , nome_sp , quota)
FK : nome_sp REFERENCES SPONSOR
FK : anno , nome_sq REFERENCES PART_SQ
SP_PIL (anno , cognomepil , nomepil , nome_sp , quota)
FK : nome_sp REFERENCES SPONSOR
FK : anno , cognomepil , nomepil REFERENCES PART_PIL
SQUFFICIALE (anno , nome_sq , casa_costr_tit)
AK : anno , casa_costr_tit
FK : anno , nome_sq REFERENCES PART_SQ
OSSERVAZIONE : Abbiamo aggiunto un altro vincolo inespresso in E/R, cioè che in un anno una
Casa Costruttrice possa partecipare con al più una squadra per classe al campionato: abbiamo
introdotto la chiave alternativa (anno, casa_costr_tit).
“casa_costr_titolare” è un attributo derivabile (dato derivato) da “casa_costr” nell’entità
PRODUZIONE; per mantenere la coerenza è caricato automaticamente da un trigger.
SU_IN_CLASSE (anno , classe , nome_sq , sigla ,coddirsp)
AK : anno , coddirsp
FK : anno , nome_sq REFERENCES SQUFFICIALE
FK : anno , classe , sigla REFERENCES MOTOUFFICIALE
FK : coddirsp REFERENCES DIRIGENTE
SQSEMIUFF (anno , nome_sq , coddirsp)
AK: anno , coddirsp
FK : anno , nome_sq REFERENCES PART_SQ
FK : coddirsp REFERENCES DIRIGENTE
SQPRIVATA (anno , nome_sq , coddirsp)
AK : anno , coddirsp
FK : anno , nome_sq REFERENCES PART_SQ
FK : coddirsp REFERENCES DIRIGENTE
17
OSSERVAZIONE :Con le chiavi alternative (anno, coddirsp) nelle relazioni SQSEMIUFF,
SQPRIVATA e SU_IN_CLASSE si è garantito che un dirigente non possa essere direttore sportivo di
più di una squadra dello stesso tipo nello stesso anno. Per garantire il vincolo più stringente che un
dirigente possa essere direttore sportivo al massimo di una squadra in un anno, di qualunque tipo essa
sia, dobbiamo inserire un ulteriore controllo (trigger).
SSU_IN_CLASSE (anno , classe , nome_sq , sigla , annop)
FK : anno , nome_sq REFERENCES SQSEMIUFF
FK : annop , classe , sigla REFERENCES MOTOUFFICIALE
SP_IN_CLASSE (anno , classe , nome_sq , sigla , annop)
FK : anno , nome_sq REFERENCES SQPRIVATA
FK : annop , classe , sigla REFERENCES MOTOCLIENTI
OSSERVAZIONE : Abbiamo voluto introdurre nello Schema Relazionale il vincolo, inespresso in
E/R, che una squadra possa partecipare ad un campionato in una data categoria solo con una moto
della stessa classe: in particolar modo non abbiamo ripetuto l’attributo “classe” relativo alla moto
nella traduzione delle entità SP_IN_CLASSE, SSU_IN_CLASSE, SU_IN_CLASSE.
Allo stesso modo abbiamo introdotto il vincolo che le squadre ufficiali possano partecipare ad un
campionato solo con moto prodotte quell’anno stesso: non abbiamo ripetuto l’attributo “annop”
relativo a MOTOUFFICIALE nella traduzione delle entità SU_IN_CLASSE.
PIL_SP (anno , classe , nome_sq , cognomepil , nomepil)
FK : anno , cognomepil , nomepil REFERENCES PART_PIL
FK : anno , classe , nome_sq REFERENCES SP_IN_CLASSE
PIL_SSU (anno , classe , nome_sq , cognomepil , nomepil , priorità)
FK : anno , cognomepil , nomepil REFERENCES PART_PIL
FK : anno , classe , nome_sq REFERENCES SSU_IN_CLASSE
PIL_SU (anno , classe , nome_sq , cognomepil , nomepil)
FK : anno , cognomepil , nomepil REFERENCES PART_PIL
FK : anno , classe , nome_sq REFERENCES SU_IN_CLASSE
OSSERVAZIONE : Inserendo una volta sola l’attributo “anno” nelle relazioni PIL_SU, PIL_SSU e
PIL_SP abbiamo realizzato il vincolo di coerenza tra l’anno di partecipazione della squadra al
campionato e l’anno di partecipazione del pilota.
Si osservi che non si fa lo stesso discorso con l’attributo “classe”, in quando in PART_PIL esso è un
dato derivato.
18
CONCETTO
PART_PIL
IN
CLASSIFICA
CLASSIFICATO
TIPO
VOLUME
E
R
E
E
16.000
16.000*15 = 240.000
16.000*15 = 240.000
16.000*10 = 160.000
Dato Derivato
1) puntip
Attributo di PART_PIL derivabile da operazioni di conteggio di istanze e da attributi di altre entità
OPERAZIONE
Op1
Op2
TIPO
I
I
FREQUENZA
20/giorno ≈ 600/mese
15/anno ≈ 1,2/mese
Tabella
dei
Volumi
OSS: Si sono considerati valori medi. Volume di 400 piloti che corrono 4 anni ciascuno. Ogni anno un
pilota partecipa a 15 gare; di queste ne termina 10, delle quali 6 nei primi 15 classificati (prende
almeno un punto).
Operazioni
Op1 _ Visualizzazione dei punti di un pilota alla fine di un campionato;
Op2 _ Inserimento di un pilota classificato con eventuale aggiornamento dei punti per mantenere la
consistenza.
Tabella delle operazioni
19
Tabella degli accessi
• Con dato derivato
OSS: Andiamo in PUNTEGGIO ed aggiorniamo il dato derivato solo se il pilota è nei primi 15.
•
Senza dato derivato
Op1
53 accessi in lettura
53*600 = 31800/mese
Op1
1 accesso in lettura
1*600 = 600/mese
Op2
15/5 accessi in lettura
1+3/5 accessi in scrittura
6.2*1.2 ≈ 7/mese
CONCETTO
PART_PIL
IN
CLASSIFICA
CLASSIFICATO
DEL
PUNTEGGIO
ACCESSO
1
15
15
10
6
6
TIPO
L
L
L
L
L
L
CONCETTO
PART_PIL
ACCESSO
1
TIPO
L
CLASSIFICATO
DEL
PUNTEGGIO
CLASSIFICA
IN
PART_PIL
PART_PIL
1
3/5
3/5
3/5
3/5
3/5
3/5
S
L
L
L
L
L
S
20
Op2
1 accesso in scrittura
1*1.2 = 2.4/mese
CLASSIFICATO
1
S
Conviene tenere il dato derivato.
Il risultato è in linea con le aspettative, perché il database realizzato risulta molto “statico”: su di esso
si agisce più frequentemente per la consultazione che per variare gli elementi costituenti.
2) RECORD
Entità che raccoglie elementi derivabili da attributi di altre entità.
Lo studio in questo caso risulta problematico in quanto coinvolge molte entità ed associazioni. D’altra
parte il mantenimento del dato derivato appare conveniente per le caratteristiche del database
sopraccitate. Per questi motivi affrontiamo uno studio qualitativo con alcune approssimazioni.
Tabella dei Volumi
CONCETTO
CIRCUITO
RECORD
GARA
GVEL
TIPO
VOLUME
E
E
E
R
30
90
900
900
Operazioni
Op1 _ Visualizzazione dei dati di un circuito, compresi i record relativi alle tre classi;
Op2 _ Inserimento dei tre giri veloci di una gara con eventuale aggiornamento dei record della pista.
Tabella delle operazioni
OPERAZIONE
Op1
Op2
TIPO
I
I
Tabella degli accessi
•
Con dato derivato
21
FREQUENZA
10/giorno ≈ 300/mese
15/anno ≈ 1,2/mese
OSS: Supponiamo che 2 volte su 3 il giro veloce sia anche il record della pista (ciò è dovuto al fatto
che i progressi tecnici rendono sempre più veloci le moto).
Consideriamo che le maggiori probabilità di segnare il giro veloce siano a favore di piloti di squadre
Op1
4 accessi in lettura
4*300 = 1200/mese
Op2
10/3 accessi in lettura
10 accessi in scrittura
13.3*1.2 ≈ 16/mese
CONCETTO
CIRCUITO
RECORD
ACCESSO
1
3
TIPO
L
L
GARA
GVEL
PIL_SU
PIL_SSU
PIL_SP
SQ_IN_CLASSE
PRODUZIONE
RECORD
3
3
3*2/3
1*2/3
1/3*2/3
3*2/3
3*2/3
3*2/3
S
S
S
L
L
L
L
S
ufficiali e poi semiufficiali; abbiamo così supposto la possibilità di trovare un pilota ufficiale tre volte
superiore rispetto ad uno semiufficiale e nove volte rispetto ad uno privato: su nove volte che
entriamo in PIL_SU, tre volte dovremo entrare anche in PIL_SSU e tra queste una addirittura in
PIL_SP.
Una volta trovata la squadra di appartenenza si entra in SU,SSU o SP_IN_CLASSE per risalire alla
sigla della moto; con quest’ultima si ottiene la Casa Costruttrice considerando i dati contenuti in
PRODUZIONE.
•
Senza dato derivato
Op1
72.3 accessi in lettura
72.3*300 = 21690/mese
Op2
6 accessi in scrittura
12*1.2 ≈ 14/mese
CONCETTO
CIRCUITO
LOCAZIONE
GARA
GVEL
PIL_SU
PIL_SSU
PIL_SP
SQ_IN_CLASSE
PRODUZIONE
GARA
GVEL
ACCESSO
1
1
30
30
3
1
1/3
3
3
3
3
TIPO
L
L
L
L
L
L
L
L
L
S
S
Il calcolo del costo computazionale dovuto agli accessi contiene grossolane ed inevitabili
approssimazioni, ma la differenza tra i risultati risulta macroscopica ed l’esito finale è ancora in linea
con le nostre aspettative; si può quindi affermare che conviene ancora il dato derivato.
3) casa_costr_tit
22
Attributo di SU_IN_CLASSE. In questo caso il dato derivato è stato introdotto non già per avere
benefici dal punto di vista computazionale, ma per poter esprimere un vincolo che non era
realizzabile diversamente.
4) classe
Attributo di PART_PIL. Analogamente a prima il dato derivato è stato introdotto per agevolare i
controlli necessari.
5) nome , cognome
Attributi di PILOTA. Sono stati introdotti per una maggiore leggibilità dei dati contenuti nel DB.
23
Implementazione delle Tabelle
TIPI DI DATI DEFINITI DA NOI
Oltre ai tipi di dati disponibili è possibile crearne dei nuovi. La creazione consiste in realtà nel
configurare opportunamente le proprietà di un tipo di dato standard, in modo da non dover
provvedere ogni volta a queste operazioni di configurazione.
Vediamo quelli creati da noi:
sp_addtype annogp, smallint, ‘not null’
sp_addtype categoria, smallint, ‘not null’
Consideriamo ora alcuni oggetti di database che sono utilizzati nell’implementazione delle tabelle:
REGOLE
Una regola si collega o associa ad una colonna in una tabella ed indica a SQL server i valori validi per
inserire o aggiornare i dati. E’ possibile applicare una regola anche ad un tipo definito dall’utente.
Le fasi necessarie alla creazione di una regola in SQL Server 7.0 consistono nel creare la regola per poi
associarla a una o più colonne (o a un tipo di dato), onde attivare la regola stessa.
Vediamo quelle create da noi:
create RULE annorule
as @anno >= $1980
create RULE classerule
as @classe in (‘125’,’250’,’500’)
sp_bindrule ‘annorule’,’annogp’
/*assegnamo le regole ai dati*/
sp_bindrule ’classerule’,’categoria’
Un altro modo per creare una regola consiste nell’usare uno speciale vincolo chiamato CHECK come
parte dell’istruzione CREATE TABLE.
VALORI PREDEFINITI
Un valore predefinito, o default, è un metodo per indicare a SQL Server 7.0 che valore usare per una
colonna in una tabella se lo stesso è omesso oppure è nullo.
Analogamente al precedente le fasi che portano alla creazione di un valore predefinito consistono nel
creare tale valore per poi associarlo a una o più colonne in una tabella per attivarlo.
create DEFAULT DEFcrono
as ‘9:99:999’
create DEFAULT DEFzero
as ‘0’
24
sp_binddefault ‘DEFcrono’,’GARA.tempo’
/*assegnamo i valori predefiniti ai dati*/
sp_binddefault ‘DEFcrono’,’RECORD.tempo’
sp_binddefault ‘DEFzero’,’PIL_SSU.priorita’
sp_binddefault ‘DEFzero’,’PART_PIL.puntip’
E’ possibile anche specificare un valore predefinito all’atto della creazione di una tabella.
REALIZZAZIONE DELLA STRUTTURA DELLA BASE DI DATI
Di seguito è riportato il codice SQL di generazione della base di dati.
create table CIRCUITO (
nomecircuito varchar (30) not null,
localita varchar (30) not null,
lunghezza smallint null,
siglanazione char (3) not null,
constraint primary1 primary key (nomecircuito)
)
create table RECORD (
nomecircuito varchar (30) not null,
classe categoria not null,
cognomepil varchar (30) null,
nomepil varchar (30) null,
casa_costr varchar (15) null,
anno annogp null,
tempo char (8) null,
constraint primary2 primary key (nomecircuito, classe),
constraint fk2 foreign key (nomecircuito) references CIRCUITO
)
create table GP (
nome varchar (30) not null,
constraint primary3 primary key (nome)
)
create table LOCAZIONE (
anno annogp not null,
mese varchar (10) not null,
check (mese in ('Gennaio', 'Febbraio', 'Marzo', 'Aprile', 'Maggio', 'Giugno', 'Luglio', 'Agosto',
'Settembre', 'Ottobre', 'Novembre', 'Dicembre')),
giorno tinyint not null,
check ((giorno>=1) and (giorno<=31)),
25
nomecircuito varchar (30) not null,
nomegp varchar (30) not null,
constraint primary4 primary key (anno, nomecircuito),
constraint ak41 unique (anno, nomegp),
constraint ak42 unique (anno, mese, giorno),
constraint fk41 foreign key (nomegp) references GP,
constraint fk42 foreign key (nomecircuito) references CIRCUITO
)
create table PERSONA (
codpers varchar (20) not null,
cognome varchar (30) not null,
nome varchar (30) not null,
datan smalldatetime null,
luogon varchar (30) null,
nazionalita char (3) null,
constraint primary5 primary key (codpers)
)
create table DIRIGENTE (
coddirigente varchar (20) not null,
constraint primary6 primary key (coddirigente),
constraint fk6 foreign key (coddirigente) references PERSONA
)
create table TECNICO (
codtecnico varchar (20) not null,
constraint primary7 primary key (codtecnico),
constraint fk7 foreign key (codtecnico) references PERSONA
)
create table PILOTA (
codpilota varchar (20) not null,
cognome varchar (30) not null,
nome varchar (30) not null,
constraint primary8 primary key (cognome, nome),
constraint ak8 unique (codpilota),
constraint fk8 foreign key (codpilota) references PERSONA
)
create table SQUADRA (
nome_sq varchar (30) not null,
constraint primary9 primary key (nome_sq)
)
create table PART_SQ (
anno annogp not null,
26
nome_sq varchar (30) not null,
constraint primary10 primary key (anno, nome_sq),
constraint fk10 foreign key (nome_sq) references SQUADRA
)
create table PART_PIL (
anno annogp not null,
cognomepil varchar (30) not null,
nomepil varchar (30) not null,
numgara tinyint null,
classe categoria null,
puntip smallint null,
constraint primary11 primary key (anno, cognomepil, nomepil),
constraint ak11 unique (anno, numgara, classe),
constraint fk11 foreign key (cognomepil, nomepil) references PILOTA
)
create table GARA (
anno annogp not null,
classe categoria not null,
nomecircuito varchar (30) not null,
numgiri tinyint null,
tempo char (8) null,
cognomepil varchar (30) null,
nomepil varchar (30) null,
constraint primary12 primary key (anno, classe, nomecircuito),
constraint fk121 foreign key (anno, cognomepil, nomepil) references PART_PIL,
constraint fk122 foreign key (anno, nomecircuito) references LOCAZIONE
)
create table PENALITA (
anno annogp not null,
classe categoria not null,
nomecircuito varchar (30) not null,
numgiro tinyint null,
cognomepil varchar (30) not null,
nomepil varchar (30) not null,
nordine tinyint not null,
causa varchar (50) null,
tipo varchar (50) null,
constraint primary13 primary key (anno, nomecircuito, cognomepil, nomepil, nordine),
constraint fk131 foreign key (anno, cognomepil, nomepil) references PART_PIL,
constraint fk132 foreign key (anno, classe, nomecircuito) references GARA
)
create table CLASSIFICA (
anno annogp not null,
27
classe categoria not null,
nomecircuito varchar (30) not null,
pos_iniz tinyint not null,
check ((pos_iniz>=1)and(pos_iniz<=30)),
cognomepil varchar (30) not null,
nomepil varchar (30) not null,
constraint primary14 primary key (anno, nomecircuito, cognomepil, nomepil),
constraint ak14 unique (anno, classe, nomecircuito, pos_iniz),
constraint fk141 foreign key (anno, cognomepil, nomepil) references PART_PIL,
constraint fk142 foreign key (anno, classe, nomecircuito) references GARA
)
create table PART_IN_TEAM (
anno annogp not null,
cognomepil varchar (30) not null,
nomepil varchar (30) not null,
codtecnico varchar (20) not null,
ruolo varchar (30) not null,
constraint primary15 primary key (anno, codtecnico),
constraint fk151 foreign key (codtecnico) references TECNICO,
constraint fk152 foreign key (anno, cognomepil, nomepil) references PART_PIL
)
create table PUNTEGGIO (
pos_fin tinyint IDENTITY (1,1) not null,
check (pos_fin<=30),
punti tinyint null,
constraint primary16 primary key (pos_fin)
)
create table CLASSIFICATO (
anno annogp not null,
classe categoria not null,
nomecircuito varchar (30) not null,
pos_fin tinyint not null,
cognomepil varchar (30) not null,
nomepil varchar (30) not null,
constraint primary17 primary key (anno, nomecircuito, cognomepil, nomepil),
constraint ak17 unique (anno, classe, nomecircuito, pos_fin),
constraint fk171 foreign key (anno, nomecircuito, cognomepil, nomepil) references
CLASSIFICA,
constraint fk172 foreign key (pos_fin) references PUNTEGGIO
)
create table NONCLASSIFICATO (
anno annogp not null,
classe categoria not null,
28
nomecircuito varchar (30) not null,
numgiro tinyint null,
cognomepil varchar (30) not null,
nomepil varchar (30) not null,
causarit varchar (30) null,
motivosq varchar (50) null,
constraint primary18 primary key (anno, nomecircuito, cognomepil, nomepil),
constraint fk18 foreign key (anno, nomecircuito, cognomepil, nomepil) references
CLASSIFICA
)
create table PRODUZIONE (
sigla varchar (5) not null,
casa_costr varchar (15) not null,
constraint primary19 primary key (sigla)
)
create table MODUFF (
classe categoria not null,
sigla varchar (5) not null,
n_cilindri tinyint not null,
check (n_cilindri<=4),
constraint primary20 primary key (classe, sigla),
constraint fk20 foreign key (sigla) references PRODUZIONE
)
create table MODCLIENTI (
classe categoria not null,
sigla varchar (5) not null,
n_cilindri tinyint not null,
check (n_cilindri<=4),
constraint primary21 primary key (classe, sigla),
constraint fk21 foreign key (sigla) references PRODUZIONE
)
create table MOTOUFFICIALE (
annop annogp not null,
classe categoria not null,
sigla varchar (5) not null,
cilindrata smallint null,
peso tinyint null,
constraint primary22 primary key (annop, classe, sigla),
constraint fk22 foreign key (classe, sigla) references MODUFF
)
create table MOTOCLIENTI (
annop annogp not null,
29
classe categoria not null,
sigla varchar (5) not null,
cilindrata smallint null,
peso tinyint null,
constraint primary23 primary key (annop, classe, sigla),
constraint fk23 foreign key (classe, sigla) references MODCLIENTI
)
create table SPONSOR (
nome_sp varchar (20) not null,
tipo_az varchar (50) null,
constraint primary24 primary key (nome_sp)
)
create table SP_SQ (
anno annogp not null,
nome_sq varchar (30) not null,
nome_sp varchar (20) not null,
quota int null,
constraint primary25 primary key (anno, nome_sq, nome_sp),
constraint fk251 foreign key (nome_sp) references SPONSOR,
constraint fk252 foreign key (anno, nome_sq) references PART_SQ
)
create table SP_PIL (
anno annogp not null,
cognomepil varchar (30) not null,
nomepil varchar (30) not null,
nome_sp varchar (20) not null,
quota int null,
constraint primary26 primary key (anno, cognomepil, nomepil, nome_sp),
constraint fk261 foreign key (nome_sp) references SPONSOR,
constraint fk262 foreign key (anno, cognomepil, nomepil) references PART_PIL
)
create table SQUFFICIALE (
anno annogp not null,
nome_sq varchar (30) not null,
casa_costr_tit varchar (15) null,
constraint primary27 primary key (anno, nome_sq),
constraint ak27 unique (anno, casa_costr_tit),
constraint fk27 foreign key (anno, nome_sq) references PART_SQ
)
create table SU_IN_CLASSE (
anno annogp not null,
classe categoria not null,
30
nome_sq varchar (30) not null,
sigla varchar (5) not null,
coddirsp varchar (20) not null,
constraint primary28 primary key (anno, classe, nome_sq),
constraint ak28 unique (anno, coddirsp),
constraint fk281 foreign key (anno, nome_sq) references SQUFFICIALE,
constraint fk282 foreign key (anno, classe, sigla) references MOTOUFFICIALE,
constraint fk283 foreign key (coddirsp) references DIRIGENTE
)
create table SQSEMIUFF (
anno annogp not null,
nome_sq varchar (30) not null,
coddirsp varchar (20) not null,
constraint primary29 primary key (anno, nome_sq),
constraint ak29 unique (anno, coddirsp),
constraint fk291 foreign key (anno, nome_sq) references PART_SQ,
constraint fk292 foreign key (coddirsp) references DIRIGENTE
)
create table SQPRIVATA (
anno annogp not null,
nome_sq varchar (30) not null,
coddirsp varchar (20) not null,
constraint primary30 primary key (anno, nome_sq),
constraint ak30 unique (anno, coddirsp),
constraint fk301 foreign key (anno, nome_sq) references PART_SQ,
constraint fk302 foreign key (coddirsp) references DIRIGENTE
)
create table SSU_IN_CLASSE (
anno annogp not null,
classe categoria not null,
nome_sq varchar (30) not null,
sigla varchar (5) not null,
annop annogp not null,
constraint primary31 primary key (anno, classe, nome_sq),
constraint fk311 foreign key (anno, nome_sq) references SQSEMIUFF,
constraint fk312 foreign key (annop, classe, sigla) references MOTOUFFICIALE
)
create table SP_IN_CLASSE (
anno annogp not null,
classe categoria not null,
nome_sq varchar (30) not null,
sigla varchar (5) not null,
annop annogp not null,
31
constraint primary32 primary key (anno, classe, nome_sq),
constraint fk321 foreign key (anno, nome_sq) references SQPRIVATA,
constraint fk322 foreign key (annop, classe, sigla) references MOTOCLIENTI
)
create table PIL_SP (
anno annogp not null,
classe categoria not null,
nome_sq varchar (30) not null,
cognomepil varchar (30) not null,
nomepil varchar (30) not null,
constraint primary33 primary key (anno, cognomepil, nomepil),
constraint fk331 foreign key (anno, cognomepil, nomepil) references PART_PIL,
constraint fk332 foreign key (anno, classe, nome_sq) references SP_IN_CLASSE
)
create table PIL_SSU (
anno annogp not null,
classe categoria not null,
nome_sq varchar (30) not null,
cognomepil varchar (30) not null,
nomepil varchar (30) not null,
priorita bit null,
constraint primary34 primary key (anno, cognomepil, nomepil),
constraint fk341 foreign key (anno, cognomepil, nomepil) references PART_PIL,
constraint fk342 foreign key (anno, classe, nome_sq) references SSU_IN_CLASSE
)
create table PIL_SU (
anno annogp not null,
classe categoria not null,
nome_sq varchar (30) not null,
cognomepil varchar (30) not null,
nomepil varchar (30) not null,
constraint primary35 primary key (anno, cognomepil, nomepil),
constraint fk351 foreign key (anno, cognomepil, nomepil) references PART_PIL,
constraint fk352 foreign key (anno, classe, nome_sq) references SU_IN_CLASSE
)
Triggers
Un TRIGGER è un oggetto di database che il DBMS esegue automaticamente quando si inseriscono,
aggiornano o cancellano dati in una tabella.
Di seguito sono riportati i triggers che si attivano in inserimento.
32
• TRGmoduff
I trigger TRGmoduff e TRGmodclienti servono per garantire l'esclusività nella copertura della
gerarchia MOTO.
if exists ( select name
from sysobjects
where name = 'TRGmoduff'
and type = 'tr'
)
drop trigger TRGmoduff
go
create trigger TRGmoduff
on MODUFF
for insert
as
declare @sigla
varchar (5)
declare @classe
categoria
select @sigla = sigla, @classe = classe
from INSERTED
if exists ( select *
from INSERTED I, MODCLIENTI MC
where I.sigla = MC.sigla
and I.classe = MC.classe
)
begin
RAISERROR ('Esiste già un modello con sigla %s , classe %d, di tipo MODCLIENTI.',16,1,
@sigla, @classe)
ROLLBACK TRANSACTION
end
go
•
TRGmodclienti
if exists ( select name
from sysobjects
where name = 'TRGmodclienti'
and type = 'tr'
)
drop trigger TRGmodclienti
go
create trigger TRGmodclienti
on MODCLIENTI
for insert
33
as
declare @sigla
varchar (5)
declare @classe
categoria
select @sigla = sigla, @classe = classe
from INSERTED
if exists ( select *
from INSERTED I, MODUFF MU
where I.sigla = MU.sigla
and I.classe = MU.classe
)
begin
RAISERROR ('Esiste già un modello con sigla %s , classe %d, di tipo MODUFF.',16,1,
@sigla, @classe)
ROLLBACK TRANSACTION
end
go
• TRGaggiornapunti
Il trigger TRGaggiornapunti si occupa di aggiornare il dato derivato ‘puntip’ nella tabella PART_PIL
all’atto dell’inserimento nella tabella CLASSIFICATO: vengono aggiunti i punti corrispondenti alla
posizione finale conquistata dal pilota nella gara considerata. Si noti che solo i primi 15 classificati
conquistano punti.
if exists ( select name from sysobjects
where name = 'TRGaggiornapunti'
and type = 'tr'
)
drop trigger TRGaggiornapunti
go
create trigger TRGaggiornapunti
on CLASSIFICATO
for insert
as
declare @puntiA tinyint
declare @puntiB smallint
declare @puntiC smallint
declare @anno annogp
declare @posizione tinyint
declare @cognome varchar(30)
declare @nome varchar(30)
select @anno=anno, @posizione=pos_fin, @cognome=cognomepil, @nome=nomepil
from INSERTED
select @puntiA=punti
from PUNTEGGIO
34
where pos_fin=@posizione
if (@posizione <= 15)
begin
select @puntiB=puntip
from PART_PIL
where anno=@anno
and cognomepil=@cognome
and nomepil=@nome
set @puntiC=@puntiA+@puntiB
update PART_PIL
set puntip=@puntiC
where anno=@anno
and cognomepil=@cognome
and nomepil=@nome
end
go
/*casting automatico*/
• TRGrecord
Trigger che si occupa di aggiornare il dato derivato record di un circuito nella tabella RECORD all’atto
dell’inserimento del giro veloce nella tabella GARA nel caso il tempo inserito sia più basso di quello
precedentemente conseguito. Si noti che è stato assegnato un valore di default per il campo tempo sia
in GARA che in RECORD.
if exists ( select name from sysobjects
where name = 'TRGrecord'
and type = 'tr'
)
drop trigger TRGrecord
go
create trigger TRGrecord
on GARA
for insert, update
as
declare @anno annogp
declare @classe categoria
declare @circuito varchar(30)
declare @nuovotempo char(8)
declare @cognomepil varchar(30)
declare @nomepil varchar(30)
declare @casacostr varchar(15)
select @anno=anno, @classe=classe, @circuito=nomecircuito, @nuovotempo=tempo,
@cognomepil=cognomepil, @nomepil=nomepil
from INSERTED
if exists ( select *
35
from RECORD R, INSERTED I
where I.classe = R.classe
and I.nomecircuito = R.nomecircuito
and I.tempo < R.tempo
)
/*Occorre recuperare il dato relativo alla Casa Costruttrice della moto con cui il pilota ha segnato il
record della pista*/
begin
select @casacostr = PR.casa_costr
from INSERTED I, PIL_SP PSP, SP_IN_CLASSE SPIC, PRODUZIONE PR
where I.anno = PSP.anno
and I.cognomepil = PSP.cognomepil
and I.nomepil = PSP.nomepil
and PSP.anno = SPIC.anno
and PSP.classe = SPIC.classe
and PSP.nome_sq = SPIC.nome_sq
and SPIC.sigla = PR.sigla
select @casacostr = PR.casa_costr
from INSERTED I, PIL_SSU PSS, SSU_IN_CLASSE SSIC, PRODUZIONE PR
where I.nomepil = PSS.nomepil
and I.cognomepil = PSS.cognomepil
and I.anno = PSS.anno
and PSS.anno = SSIC.anno
and PSS.classe = SSIC.classe
and PSS.nome_sq = SSIC.nome_sq
and SSIC.sigla = PR.sigla
select @casacostr = PR.casa_costr
from INSERTED I, PIL_SU PSU, SU_IN_CLASSE SUIC, PRODUZIONE PR
where I.nomepil = PSU.nomepil
and I.cognomepil = PSU.cognomepil
and I.anno = PSU.anno
and PSU.anno = SUIC.anno
and PSU.classe = SUIC.classe
and PSU.nome_sq = SUIC.nome_sq
and SUIC.sigla = PR.sigla
/*solo una delle tre select da un risultato e fa l’assegnamento*/
update RECORD
set cognomepil=@cognomepil, nomepil=@nomepil, casa_costr=@casacostr, anno=@anno,
tempo=@nuovotempo
where nomecircuito=@circuito
and classe=@classe
end
go
36
• TRGclassec
Questo trigger verifica la coerenza tra la classe di un campionato a cui partecipa un pilota e quella
della CLASSIFICA in cui voglio inserire un risultato relativamente a quel pilota (es: evito che un
pilota che corre nella classe 250 compaia nella classifica di una gara della classe 125)
if exists ( select name
from sysobjects
where name = 'TRGclassec'
and type = 'tr'
)
drop trigger TRGclassec
go
create trigger TRGclassec
on CLASSIFICA
for insert
as
declare @anno
annogp
declare @classe
categoria
declare @cognomepil varchar (30)
declare @nomepil
varchar(30)
select @anno=anno, @classe=classe, @cognomepil=cognomepil, @nomepil=nomepil
from INSERTED
if not exists ( select *
from PART_PIL
where nomepil=@nomepil
and cognomepil=@cognomepil
and anno=@anno
and classe=@classe
)
begin
RAISERROR ('Errore: il pilota %s %s non risulta correre nella classe %d nell’anno %d, quindi non
può essere inserito nella classifica relativa a tale classe.',16, 1, @nomepil, @cognomepil, @classe,
@anno)
ROLLBACK TRANSACTION
end
go
• TRGclasseg
Questo trigger verifica la coerenza tra la classe di un campionato a cui partecipa un pilota e quella della
GARA in cui voglio inserire un giro veloce (es: evito che un pilota che corre nella classe 250 risulti
aver compiuto un giro veloce in una gara della classe 125). Siccome l’inserimento di un giro veloce puó
37
avvenire anche tramite procedura con un update su GARA, il trigger vale sia per l’inserimento che
per l’aggiornamento.
if exists ( select name
from sysobjects
where name = 'TRGclasseg'
and type = 'tr'
)
drop trigger TRGclasseg
go
create trigger TRGclasseg
on GARA
for insert, update
/*insert per l’inserimento diretto da parte del dbo, update per la procedura di inserimento*/
as
declare @anno
annogp
declare @classe
categoria
declare @cognomepil varchar (30)
declare @nomepil
varchar (30)
select @anno=anno, @classe=classe, @cognomepil=cognomepil, @nomepil=nomepil
from INSERTED
if ((@cognomepil<>null) and (@nomepil<>null))
if not exists ( select *
from PART_PIL
where nomepil=@nomepil
and cognomepil=@cognomepil
and anno=@anno
and classe=@classe
)
begin
RAISERROR ('Errore: il pilota %s %s non risulta correre nella classe %d nell’anno %d, quindi non
può effettuare il giro veloce di una gara relativa a tale classe.',16, 1, @nomepil, @cognomepil,
@classe, @anno)
ROLLBACK TRANSACTION
end
go
• TRGclassep
Questo trigger verifica la coerenza tra la classe di un campionato a cui partecipa un pilota e quella della
gara in cui voglio inserire delle PENALITA’ relative a quel pilota (es: evito che un pilota che corre
nella classe 250 subisca una penalità in una gara della classe 125).
if exists ( select name
from sysobjects
where name = 'TRGclassep'
38
and type = 'tr'
)
drop trigger TRGclassep
go
create trigger TRGclassep
on PENALITA
for insert
as
declare @anno
annogp
declare @classe
categoria
declare @cognomepil varchar (30)
declare @nomepil
varchar (30)
select @anno=anno, @classe=classe, @cognomepil=cognomepil, @nomepil=nomepil
from INSERTED
if not exists ( select *
from PART_PIL
where nomepil=@nomepil
and cognomepil=@cognomepil
and anno=@anno
and classe=@classe
)
begin
RAISERROR ('Errore: il pilota %s %s non risulta correre nella classe %d nell’anno %d, quindi non
può essere inserito in penalità relativamente a tale classe.',16, 1, @nomepil, @cognomepil, @classe,
@anno)
ROLLBACK TRANSACTION
end
go
• TRGlocazione
Trigger che si occupa di verificare la validità dei dati giorno e mese per i mesi con un numero di giorni
inferiore a 31; per gli altri la validità é garantita dal vincolo di colonna su giorno nella tabella
LOCAZIONE.
if exists ( select name from sysobjects
where name = 'TRGlocazione'
and type = 'tr'
)
drop trigger TRGlocazione
go
create trigger TRGlocazione
on LOCAZIONE
for insert
as
39
declare @giorno
tinyint
declare @mese
varchar (10)
select @giorno=giorno, @mese=mese
from INSERTED
if (@mese = 'Febbraio' and @giorno > 29)
begin
RAISERROR ('ERRORE! Febbraio ha 29 giorni. ', 16, 1)
ROLLBACK TRANSACTION
end
else
if (@mese = 'Aprile' and @giorno = 31)
begin
RAISERROR ('ERRORE! Aprile ha 30 giorni. ', 16, 1)
ROLLBACK TRANSACTION
end
else
if (@mese = 'Giugno' and @giorno = 31)
begin
RAISERROR ('ERRORE! Giugno ha 30 giorni. ', 16, 1)
ROLLBACK TRANSACTION
end
else
if (@mese = 'Settembre' and @giorno = 31)
begin
RAISERROR ('ERRORE! Settembre ha 30 giorni. ', 16, 1)
ROLLBACK TRANSACTION
end
else
if (@mese = 'Novembre' and @giorno = 31)
begin
RAISERROR ('ERRORE! Novembre ha 30 giorni. ', 16, 1)
ROLLBACK TRANSACTION
end
go
• TRGsp_in_classe
Questo trigger controlla che una squadra privata partecipi al campionato in un certo anno nelle diverse
classi con moto fornite dalla stessa Casa Costruttrice.
if exists ( select name
from sysobjects
where name = 'TRGsp_in_classe'
and type = 'tr'
)
drop trigger TRGsp_in_classe
40
go
create trigger TRGsp_in_classe
on SP_IN_CLASSE
for insert
as
declare @anno
annogp
declare @classe
categoria
declare @nomesq varchar (30)
declare @sigla
varchar (5)
declare @casacostr varchar (15)
begin
select @anno=anno, @classe=classe, @nomesq=nome_sq, @sigla=sigla
from INSERTED
select @casacostr = casa_costr
from PRODUZIONE
where sigla = @sigla
if exists ( select *
from SP_IN_CLASSE SIC, PRODUZIONE P
where SIC.anno = @anno
and SIC.nome_sq = @nomesq
and SIC.sigla = P.sigla
and P.casa_costr <> @casacostr
)
begin
RAISERROR ('Le squadre con nome %s non possono partecipare nell`anno %d al campionato con
moto di case costruttrici differenti, errore.', 16, 1, @nomesq, @anno)
ROLLBACK TRANSACTION
end
end
go
• TRGssu_in_classe
Questo trigger controlla che una squadra semiufficiale partecipi al campionato nelle diverse classi con
moto fornite dalla stessa Casa Costruttrice.
if exists ( select name
from sysobjects
where name = 'TRGssu_in_classe'
and type = 'tr'
)
drop trigger TRGssu_in_classe
go
create trigger TRGssu_in_classe
on SSU_IN_CLASSE
for insert
as
41
declare @anno
annogp
declare @classe
categoria
declare @nomesq
varchar (30)
declare @sigla
varchar (5)
declare @casacostr varchar (15)
select @anno=anno, @classe=classe, @nomesq=nome_sq, @sigla=sigla
from INSERTED
select @casacostr = casa_costr
from PRODUZIONE
where sigla = @sigla
if exists ( select *
from SSU_IN_CLASSE SSIC, PRODUZIONE P
where SSIC.anno = @anno
and SSIC.nome_sq = @nomesq
and SSIC.sigla = P.sigla
and P.casa_costr <> @casacostr
)
begin
RAISERROR ('Le squadre con nome %s non possono partecipare nell`anno %d al campionato con
moto di case costruttrici differenti, errore.', 16, 1, @nomesq, @anno)
ROLLBACK TRANSACTION
end
go
• TRGsu_in_classe
Questo trigger controlla che:
1)il direttore sportivo di una squadra ufficiale non possa essere direttore sportivo, nel medesimo anno,
di squadre di diverso tipo. Il fatto che non possa essere direttore sportivo di un’altra squadra ufficiale
e’ garantito dalla chiave alternativa nella tabella SU_IN_CLASSE.
2)una squadra ufficiale partecipi al campionato nelle diverse classi in un certo anno con moto fornite
dalla stessa casa costruttrice.
Inoltre si occupa di settare il dato derivato casa_costr_tit nella relazione SQUFFICIALE nel caso nella
relazione SU_IN_CLASSE non ci sia ancora nessuna tupla con i valori degli attributi nome_sq e anno
uguali a quelli inseriti.
if exists ( select name
from sysobjects
where name = 'TRGsu_in_classe'
and type = 'tr'
)
drop trigger TRGsu_in_classe
go
create trigger TRGsu_in_classe
on SU_IN_CLASSE
for insert
42
as
declare @anno
annogp
declare @classe
categoria
declare @nomesq
varchar (30)
declare @sigla
varchar (5)
declare @coddirsp
varchar (20)
declare @casacostr
varchar (15)
declare @cognomepropr varchar (30)
declare @nomepropr
varchar (30)
select @anno=anno, @classe=classe, @nomesq=nome_sq, @sigla=sigla, @coddirsp=coddirsp
from INSERTED
select @casacostr = casa_costr
from PRODUZIONE
where sigla = @sigla
if not exists ( select *
from SU_IN_CLASSE
where nome_sq = @nomesq
and anno = @anno
and classe <> @classe
)
update SQUFFICIALE
set casa_costr_tit = @casacostr
where nome_sq = @nomesq
and anno = @anno
if exists ( select *
from SQSEMIUFF
where coddirsp = @coddirsp
and anno = @anno
)
begin
select @cognomepropr = cognome, @nomepropr = nome
from PERSONA
where codpers = @coddirsp
RAISERROR ('Il dirigente %s %s possiede già una squadra semiufficiale nell`anno %d, errore.', 16,
1,@cognomepropr,@nomepropr,@anno)
ROLLBACK TRANSACTION
end
if exists ( select *
from SQPRIVATA
where coddirsp = @coddirsp
and anno = @anno
)
begin
select @cognomepropr = cognome, @nomepropr = nome
from PERSONA
where codpers = @coddirsp
43
RAISERROR ('Il dirigente %s %s possiede già una squadra privata nell`anno %d, errore.', 16, 1,
@cognomepropr, @nomepropr, @anno)
ROLLBACK TRANSACTION
end
if exists ( select *
from SU_IN_CLASSE SUIC, PRODUZIONE PR
where SUIC.anno = @anno
and SUIC.nome_sq = @nomesq
and SUIC.sigla = PR.sigla
and PR.casa_costr <> @casacostr
)
begin
RAISERROR ('Le squadre con nome %s non possono partecipare nell`anno %d al campionato con
moto di case costruttrici differenti, errore.', 16, 1, @nomesq, @anno)
ROLLBACK TRANSACTION
end
go
• TRGsqsemiuff
Questo trigger controlla che il direttore sportivo di una squadra semiufficiale non possa essere
direttore sportivo, nel medesimo anno, di squadre di diverso tipo. Il fatto che non possa essere
direttore sportivo di un’altra squadra semiufficiale e’ garantito dalla chiave alternativa nella tabella
SQSEMIUFF.
if exists ( select name
from sysobjects
where name = 'TRGsqsemiuff'
and type = 'tr'
)
drop trigger TRGsqsemiuff
go
create trigger TRGsqsemiuff
on SQSEMIUFF
for insert
as
declare @anno
annogp
declare @codice
varchar (20)
declare @cognome
varchar (30)
declare @nome
varchar (30)
select @anno=anno, @codice=coddirsp
from INSERTED
if exists ( select *
from SQPRIVATA
where coddirsp = @codice
and anno = @anno
44
)
begin
select @cognome = cognome, @nome = nome
from PERSONA
where codpers = @codice
RAISERROR ('Il proprietario %s %s possiede già una squadra privata nell`anno %d, errore.', 16, 1,
@cognome, @nome, @anno)
ROLLBACK TRANSACTION
end
if exists ( select *
from SU_IN_CLASSE
where coddirsp = @codice
and anno = @anno
)
begin
select @cognome = cognome, @nome = nome
from PERSONA
where codpers = @codice
RAISERROR ('Il proprietario %s %s è già direttore sportivo di una squadra ufficiale nell`anno %d,
errore.', 16, 1, @cognome, @nome, @anno)
ROLLBACK TRANSACTION
end
go
• TRGsqprivata
Questo trigger controlla che il direttore sportivo di una squadra privata non possa essere direttore
sportivo, nel medesimo anno, di squadre di diverso tipo. Il fatto che non possa essere direttore
sportivo di un’altra squadra privata è garantito dalla chiave alternativa nella tabella SQPRIVATA.
if exists ( select name
from sysobjects
where name = 'TRGsqprivata'
and type = 'tr'
)
drop trigger TRGsqprivata
go
create trigger TRGsqprivata
on SQPRIVATA
for insert
as
declare @anno
annogp
declare @codice
varchar (20)
declare @cognome
varchar (30)
declare @nome
varchar (30)
45
select @anno=anno, @codice=coddirsp
from INSERTED
if exists ( select *
from SQSEMIUFF
where coddirsp = @codice
and anno = @anno
)
begin
select @cognome = cognome, @nome = nome
from PERSONA
where codpers = @codice
RAISERROR ('Il proprietario %s %s possiede già una squadra privata nell`anno %d, errore.', 16, 1,
@cognome, @nome, @anno)
ROLLBACK TRANSACTION
end
if exists ( select *
from SU_IN_CLASSE
where coddirsp = @codice
and anno = @anno
)
begin
select @cognome = cognome, @nome = nome
from PERSONA
where codpers = @codice
RAISERROR ('Il proprietario %s %s è già direttore sportivo di una squadra ufficiale nell`anno %d,
errore.', 16, 1, @cognome, @nome, @anno)
ROLLBACK TRANSACTION
end
go
• TRGpil_sp
Questo trigger controlla che:
1)un pilota inserito in una squadra privata un certo anno, non sia già presente in squadre di tipo
diverso nello stesso anno.
2)una squadra privata un certo anno non possa correre con più di tre piloti.
Inoltre si occupa di settare il valore del dato derivato classe nella tabella PART_PIL lasciato a NULL
fino all`inserimento di una tupla nella tabella PIL_SP.
if exists ( select name
from sysobjects
where name = 'TRGpil_sp'
and type = 'tr'
)
drop trigger TRGpil_sp
go
46
create trigger TRGpil_sp
on PIL_SP
for insert
as
declare @nome
varchar (30)
declare @cognome
varchar (30)
declare @anno
annogp
declare @nomesq
varchar (30)
declare @classe
categoria
select @nome=nomepil, @cognome=cognomepil, @anno=anno, @nomesq=nome_sq,
@classe=classe
from INSERTED
if exists ( select *
from PIL_SU PSU, INSERTED I
where PSU.anno = I.anno
and PSU.nomepil = I.nomepil
and PSU.cognomepil = I.cognomepil
)
begin
RAISERROR ('Il pilota %s %s corre già in una squadra ufficiale di nome %s ', 16, 1, @nome,
@cognome, @nomesq)
ROLLBACK TRANSACTION
end
if exists ( select *
from PIL_SSU PSS, INSERTED I
where PSS.anno = I.anno
and PSS.nomepil = I.nomepil
and PSS.cognomepil = I.cognomepil
)
begin
RAISERROR ('Il pilota %s %s corre già in una squadra semiufficiale di nome %s ', 16, 1, @nome,
@cognome, @nomesq)
ROLLBACK TRANSACTION
end
if 3 < ( select count (*)
from INSERTED I, PIL_SP PSP
where I.classe = PSP.classe
and I.anno = PSP.anno
and I.nome_sq = PSP.nome_sq
)
begin
RAISERROR ('La squadra %s non può partecipare con più di tre piloti nell’anno %d e nella classe
%d. ', 16, 1, @nomesq, @anno, @classe)
ROLLBACK TRANSACTION
end
update PART_PIL
set classe=@classe
47
where nomepil=@nome
and cognomepil=@cognome
and anno=@anno
go
• TRGpil_ssu
Questo trigger controlla che:
1)che un pilota inserito in una squadra semiufficiale in un certo anno non sia già presente in squadre
di tipo diverso nello stesso anno;
2)che una squadra semiufficiale in un certo anno non possa correre con più di tre piloti.
Inoltre si occupa di settare il valore del dato derivato classe nella tabella PART_PIL lasciato a NULL
fino all`inserimento di una tupla nella tabella PIL_SSU.
if exists ( select name
from sysobjects
where name = 'TRGpil_ssu'
and type = 'tr'
)
drop trigger TRGpil_ssu
go
create trigger TRGpil_ssu
on PIL_SSU
for insert
as
declare @nome
varchar (15)
declare @cognome varchar (15)
declare @nomesq
varchar (15)
declare @classe
categoria
declare @anno
annogp
select @nome = nomepil, @cognome = cognomepil, @nomesq = nome_sq, @anno=anno,
@classe=classe
from INSERTED
if exists ( select *
from PIL_SP PSP, INSERTED I
where PSP.anno = I.anno
and PSP.nomepil = I.nomepil
and PSP.cognomepil = I.cognomepil
)
begin
RAISERROR ('Il pilota %s %s corre già in una squadra privata di nome %s ', 16, 1, @nome,
@cognome, @nomesq)
ROLLBACK TRANSACTION
end
if exists ( select *
from PIL_SU PSU, INSERTED I
48
where PSU.anno = I.anno
and PSU.nomepil = I.nomepil
and PSU.cognomepil = I.cognomepil
)
begin
RAISERROR ('il pilota %s %s corre già in una squadra ufficiale di nome %s ', 16, 1, @nome,
@cognome, @nomesq)
ROLLBACK TRANSACTION
end
if 3 < ( select count (*)
from INSERTED I, PIL_SSU PSS
where I.classe = PSS.classe
and I.anno = PSS.anno
and I.nome_sq = PSS.nome_sq
)
begin
RAISERROR ('La squadra %s non può partecipare con più di tre piloti nell’anno %d e nella
classe %d ', 16, 1, @nomesq, @anno, @classe)
ROLLBACK TRANSACTION
end
update PART_PIL
set classe=@classe
where nomepil=@nome
and cognomepil=@cognome
and anno=@anno
go
• TRGpil_su
Questo trigger controlla che:
1)che un pilota inserito in una squadra ufficiale in un certo anno non sia già presente in squadre di
tipo diverso nello stesso anno;
2)che una squadra ufficiale in un certo anno non possa correre con più di tre piloti.
Inoltre si occupa di settare il valore del dato derivato classe nella tabella PART_PIL lasciato a NULL
fino all`inserimento di una tupla nella tabella PIL_SU.
if exists ( select name
from sysobjects
where name = 'TRGpil_su'
and type = 'tr'
)
drop trigger TRGpilsu
go
create trigger TRGpil_su
on pil_su
for insert
49
as
declare @nome
varchar (15)
declare @cognome varchar (15)
declare @nomesq varchar (15)
declare @classe
categoria
declare @anno
annogp
select @nome = nomepil, @cognome = cognomepil, @nomesq = nome_sq, @anno=anno,
@classe=classe
from INSERTED
if exists ( select *
from PIL_SP PSP, INSERTED I
where PSP.anno = I.anno
and PSP.nomepil = I.nomepil
and PSP.cognomepil = I.cognomepil
)
begin
RAISERROR ('Il pilota %s %s corre giÀ in una squadra privata di nome %s ', 16, 1, @nome,
@cognome, @nomesq)
ROLLBACK TRANSACTION
end
if exists ( select *
from PIL_SSU PSS, INSERTED I
where PSS.anno = I.anno
and PSS.nomepil = I.nomepil
and PSS.cognomepil = I.cognomepil
)
begin
RAISERROR ('Il pilota %s %s corre giá in una squadra semiufficiale di nome %s ', 16, 1,
@nome, @cognome, @nomesq)
ROLLBACK TRANSACTION
end
if 3 < ( select count (*)
from INSERTED I, PIL_SU PSU
where I.classe = PSU.classe
and I.anno = PSU.anno
and I.nome_sq = PSU.nome_sq
)
begin
RAISERROR ('La squadra %s non puó partecipare con più di tre piloti nell’anno %d e nella
classe %d ', 16, 1, @nomesq, @anno, @classe)
ROLLBACK TRANSACTION
end
update PART_PIL
set classe=@classe
where nomepil=@nome
and cognomepil=@cognome
and anno=@anno
50
go
Delete Triggers
Di seguito sono riportati i triggers che si attivano in cancellazione.
• TRDtoglipunti
Trigger di cancellazione su CLASSIFICATO. Il trigger aggiorna automaticamente i punti dei piloti
nella tabella PART_PIL.
Si noti il comportamento speculare rispetto al trigger “TRGaggiornapunti”.
if exists ( select name from sysobjects
where name = 'TRDtoglipunti'
and type = 'TR'
)
drop trigger TRDtoglipunti
go
create trigger TRDtoglipunti
on CLASSIFICATO
for delete
as
declare @puntiA tinyint
declare @puntiB smallint
declare @puntiC smallint
declare @nome varchar(30)
declare @cognome varchar(30)
declare @anno annogp
declare @posizione tinyint
declare cur cursor for
(select nomepil, cognomepil, anno, pos_fin
from DELETED)
open cur
fetch next from cur
into @nome, @cognome, @anno, @posizione
while (@@fetch_status = 0)
begin
select @puntiA = punti
from PUNTEGGIO
where pos_fin = @posizione
if (@posizione <= 15)
begin
select @puntiB = puntip
from PART_PIL
where nomepil = @nome
and cognomepil = @cognome
51
and anno = @anno
set @puntiC = @puntiB - @puntiA
update PART_PIL
set puntip = @puntiC
where nomepil = @nome
and cognomepil = @cognome
and anno = @anno
end
fetch next from cur
into @nome, @cognome, @anno, @posizione
end
close cur
deallocate cur
go
• TRDdirigente
Questi triggers mantengono la totalità della copertura della gerarchia PERSONA in caso di
cancellazione.
if exists ( select name
from sysobjects
where name = 'TRDdirigente'
and type = 'tr'
)
drop trigger TRDdirigente
go
create trigger TRDdirigente
on DIRIGENTE
for delete
as
declare @coddir varchar (20)
begin
select @coddir = coddirigente
from DELETED
if not exists ( select *
from PILOTA
where codpilota = @coddir
)
if not exists ( select *
from TECNICO
where codtecnico = @coddir
)
delete from PERSONA
where codpers = @coddir
end
52
go
•
TRDpilota
if exists ( select name
from sysobjects
where name = 'TRDpilota'
and type = 'tr'
)
drop trigger TRDpilota
go
create trigger TRDpilota
on PILOTA
for delete
as
declare @codpil varchar (20)
begin
select @codpil=codpilota
from DELETED
if not exists ( select *
from TECNICO
where codtecnico = @codpil
)
if not exists ( select *
from dirigente
where coddirigente = @codpil
)
delete from PERSONA
where codpers = @codpil
end
go
•
TRDtecnico
if exists ( select name
from sysobjects
where name = 'TRDtecnico'
and type = 'tr'
)
drop trigger TRDtecnico
go
53
create trigger TRDtecnico
on TECNICO
for delete
as
declare @codtec varchar (20)
begin
select @codtec=codtecnico
from DELETED
if not exists ( select *
from PILOTA
where codpilota = @codtec
)
if not exists ( select *
from dirigente
where coddirigente = @codtec
)
delete from PERSONA
where codpers = @codtec
end
go
• TRDgara
Trigger di cancellazione su GARA. Una volta mandata in esecuzione la procedura DELdatigara, si può
provare a cancellare la corrispondente gara. Se sono presenti dati relativi alle altre classi l’operazione
non è permessa; se così non è, il trigger mantiene le cardinalità nell’associazione con LOCAZIONE:
cancella le tre tuple relative alle tre classi in GARA e propaga la cancellazione alla corrispondente
tupla in LOCAZIONE.
if exists ( select name
from sysobjects
where name = 'TRDgara'
and type = 'tr'
)
drop trigger TRDgara
go
create trigger TRDgara
on GARA
for delete
as
declare @anno annogp
declare @nomecircuito varchar (30)
select @anno = anno, @nomecircuito = nomecircuito
from DELETED
delete from GARA
where anno = @anno
54
and nomecircuito = @nomecircuito
if exists ( select *
from GARA
where anno = @anno
and nomecircuito = @nomecircuito
)
/*Se è presente una o due tuple significa che non sono stati cancellati i dati di tutte le classi, quindi
occorre fare il rollback*/
begin
RAISERROR ('Impossibile cancellare la gara, controllare che siano stati cancellati i dati relativi alle
altre classi.',16,1)
ROLLBACK TRANSACTION
end
else
begin
delete from LOCAZIONE
where anno = @anno
and nomecircuito = @nomecircuito
end
go
• TRDlocazione
Trigger di cancellazione su LOCAZIONE. Si occupa di verificare la cardinalità minima: se il GP era
stato introdotto quell’unico anno, occorre cancellare anche la corrispondente tupla nella tabella GP.
if exists ( select name
from sysobjects
where name = 'TRDlocazione'
and type = 'tr'
)
drop trigger TRDlocazione
go
create trigger TRDlocazione
on LOCAZIONE
for delete
as
declare @nomegp
varchar(20)
select @nomegp=nomegp
from DELETED
if not exists ( select *
from LOCAZIONE
where nomegp = @nomegp
)
delete from GP
where nome = @nomegp
55
go
• TRDmotouff
Triggers di cancellazione su MOTOUFFICIALE e MOTOCLIENTI. Il trigger verifica la cadinalità
minima: se la moto cancellata era stata introdotta per quell’unico anno, occorre cancellare anche la
corrispondente tupla in MODUFF o MODCLIENTI.
Si noti che quest’ultima cancellazione avrà un analogo controllo che potrà portare alla propagazione
del processo fino all’entità PRODUZIONE.
if exists ( select name
from sysobjects
where name = 'TRDmotouff'
and type = 'TR'
)
drop trigger TRDmotouff
go
create trigger TRDmotouff
on MOTOUFFICIALE
for delete
as
declare @sigla varchar(5)
declare @classe categoria
select @sigla = sigla, @classe = classe
from DELETED
if not exists ( select *
from MOTOUFFICIALE
where sigla = @sigla
and classe = @classe
)
delete from MODUFF
where sigla = @sigla
and classe = @classe
go
•
TRDmotoclienti
if exists ( select name
from sysobjects
where name = 'TRDmotoclienti'
and type = 'TR'
)
drop trigger TRDmotoclienti
go
56
create trigger TRDmotoclienti
on MOTOCLIENTI
for delete
as
declare @sigla varchar(5)
declare @classe categoria
select @sigla = sigla, @classe = classe
from DELETED
if not exists ( select *
from MOTOCLIENTI
where sigla = @sigla
and classe = @classe
)
delete from MODCLIENTI
where sigla = @sigla
and classe = @classe
go
• TRDmoduff
Triggers di cancellazione su MODUFF e MODCLIENTI. Il trigger verifica la cadinalità minima:
analogamente al caso precedente se il modello cancellato era stato introdotto per quell’unica classe,
occorre cancellare anche la corrispondente tupla in PRODUZIONE.
Si noti che non è necessario porre vincoli di cancellazione su PRODUZIONE in quanto la
consistenza è assicurata dalla foreign key.
if exists ( select name
from sysobjects
where name = 'TRDmoduff'
and type = 'TR'
)
drop trigger TRDmoduff
go
create trigger TRDmoduff
on MODUFF
for delete
as
declare @sigla varchar(5)
select @sigla = sigla
from DELETED
if not exists ( select *
from MODUFF
where sigla = @sigla
)
delete from PRODUZIONE
where sigla = @sigla
57
go
•
TRDmodclienti
if exists ( select name
from sysobjects
where name = 'TRDmodclienti'
and type = 'TR'
)
drop trigger TRDmodclienti
go
create trigger TRDmodclienti
on MODCLIENTI
for delete
as
declare @sigla varchar(5)
select @sigla = sigla
from DELETED
if not exists ( select *
from MODCLIENTI
where sigla = @sigla
)
delete from PRODUZIONE
where sigla = @sigla
go
• TRDpart_sq
Trigger di cancellazione su PARTSQ. Non è possibile cancellare una tupla nella entità in questione, se
non si è prima eliminata la corrispondente nelle sottoentità; quindi il trigger è attivato da uno dei
precedenti.
Si verifica la cardinalità minima: se la squadra partecipa in quell’unico anno al campionato, occorre
cancellare anche la corrispondente tupla in SQUADRA.
Si noti che non è necessario porre vincoli di cancellazione su SQUADRA in quanto la consistenza è
assicurata dalla foreign key.
if exists (select name
from sysobjects
where name = 'TRDpart_sq'
and type = 'tr'
)
drop trigger TRDpart_sq
go
create trigger TRDpart_sq
58
on PART_SQ
for delete
as
declare
@nomesq
varchar (30)
select @nomesq = nome_sq
from DELETED
if not exists ( select *
from PART_SQ
where nome_sq = @nomesq
)
delete from SQUADRA
where nome_sq = @nomesq
go
• TRDpil_sp
Trigger di cancellazione su PIL_SP. Il trigger verifica la cardinalità minima: se il pilota cancellato era
l’unico della sua squadra a partecipare a quella classe, occorre aggiornare anche la partecipazione della
squadra al campionato.
Si noti che la cardinalità minima uguale a zero di PART_PIL nell’associazione è dovuta alla possibilità
multipla, si noti inoltre che non ha senso mantenere dati relativi alla partecipazione di un pilota ad un
campionato se non legato ad una squadra; per queste considerazioni, risulta opportuno propagare la
cancellazione a PART_IN_TEAM e a PART_PIL.
if exists ( select name
from sysobjects
where name = 'TRDpil_sp'
and type = 'tr'
)
drop trigger TRDpil_sp
go
create trigger TRDpil_sp
on PIL_SP
for delete
as
declare @anno annogp
declare @classe categoria
declare @nomesq varchar (30)
declare @nomepil
varchar (30)
declare @cognomepil varchar (30)
select @anno = anno, @nomepil = nomepil, @cognomepil = cognomepil, @classe = classe, @nomesq
= nome_sq
from DELETED
if not exists ( select *
from PIL_SP
where classe = @classe
59
and anno = @anno
and nome_sq = @nomesq
)
begin
delete from SP_IN_CLASSE
where classe = @classe
and anno = @anno
and nome_sq = @nomesq
end
delete from PART_IN_TEAM
where anno = @anno
and nomepil = @nomepil
and cognomepil = @cognomepil
delete from PART_PIL
where anno = @anno
and nomepil = @nomepil
and cognomepil = @cognomepil
if @@rowcount = 0
begin
RAISERROR ('Non è possibile cancellare la partecipazione del pilota %s %s nell’anno %d perché
ha partecipato ad una o più gare.',16,1, @nomepil, @cognomepil, @anno)
ROLLBACK TRANSACTION
end
go
• TRDpil_ssu
Trigger di cancellazione su PIL_SSU. Valgono considerazioni analoghe a quelle precedenti.
if exists ( select name
from sysobjects
where name = 'TRDpil_ssu'
and type = 'tr'
)
drop trigger TRDpil_ssu
go
create trigger TRDpil_ssu
on PIL_SSU
for delete
as
declare @anno annogp
declare @classe categoria
declare @nomesq varchar (30)
declare @nomepil
varchar (30)
declare @cognomepil varchar (30)
60
select @anno = anno, @nomepil = nomepil, @cognomepil = cognomepil, @classe = classe, @nomesq
= nome_sq
from DELETED
if not exists ( select *
from PIL_SSU
where classe = @classe
and anno = @anno
and nome_sq = @nomesq
)
begin
delete from SSU_IN_CLASSE
where classe = @classe
and anno = @anno
and nome_sq = @nomesq
end
delete from PART_IN_TEAM
where anno = @anno
and nomepil = @nomepil
and cognomepil = @cognomepil
delete from PART_PIL
where anno = @anno
and nomepil = @nomepil
and cognomepil = @cognomepil
if @@rowcount = 0
begin
RAISERROR ('Non è possibile cancellare la partecipazione del pilota %s %s nell’anno %d perché
ha partecipato ad una o più gare.',16,1, @nomepil, @cognomepil, @anno)
ROLLBACK TRANSACTION
end
go
• TRDpil_su
Trigger di cancellazione su PIL_SU.
if exists ( select name
from sysobjects
where name = 'TRDpil_su'
and type = 'tr'
)
drop trigger TRDpil_su
go
create trigger TRDpil_su
on PIL_SU
for delete
as
declare @anno annogp
61
declare @classe categoria
declare @nomesq varchar (30)
declare @nomepil
varchar (30)
declare @cognomepil varchar (30)
select @anno = anno, @nomepil = nomepil, @cognomepil = cognomepil, @classe = classe, @nomesq
= nome_sq
from DELETED
if not exists ( select *
from PIL_SU
where classe = @classe
and anno = @anno
and nome_sq = @nomesq
)
begin
delete from SU_IN_CLASSE
where classe = @classe
and anno = @anno
and nome_sq = @nomesq
end
delete from PART_IN_TEAM
where anno = @anno
and nomepil = @nomepil
and cognomepil = @cognomepil
delete from PART_PIL
where anno = @anno
and nomepil = @nomepil
and cognomepil = @cognomepil
if @@rowcount = 0
begin
RAISERROR ('Non è possibile cancellare la partecipazione del pilota %s %s nell’anno %d perché
ha partecipato ad una o più gare.',16,1, @nomepil, @cognomepil, @anno)
ROLLBACK TRANSACTION
end
go
• TRDsu_in_classe
Trigger di cancellazione su SU_IN_CLASSE. Il trigger verifica la cardinalità minima: se la squadra
ufficiale partecipa al campionato dell’anno considerato in quell’unica classe, occorre cancellare anche
la corrispondente tupla in SQUFFICIALE.
A questo punto occorre mantere la copertura totale della gerarchia PARTSQ propagando il processo
di cancellazione a PARTSQ; infatti una squadra ufficiale può partecipare in un anno a più classi, ma in
tutte deve comparire come ufficiale.
if exists ( select name
from sysobjects
where name = 'TRDsu_in_classe'
62
and type = 'tr'
)
drop trigger TRDsu_in_classe
go
create trigger TRDsu_in_classe
on SU_IN_CLASSE
for delete
as
declare
@nomesq
varchar (30)
declare
@annoannogp
declare
@classe
categoria
select @nomesq = nome_sq, @anno = anno, @classe = classe
from DELETED
if not exists ( select *
from SU_IN_CLASSE
where nome_sq = @nomesq
and anno = @anno
)
begin
delete from SQUFFICIALE
where nome_sq = @nomesq
and anno = @anno
delete from PART_SQ
where nome_sq = @nomesq
and anno = @anno
/*Se la cancellazione della tupla in PARTSQ fallisce (sponsorizzazione) non si può accettare la
cancellazione in SU_IN_CLASSE*/
if @@rowcount = 0
begin
RAISERROR ('Prima occorre cancellare le sponsorizzazioni della squadra.',16,1)
ROLLBACK TRANSACTION
end
end
go
• TRDssu_in_classe
Trigger di cancellazione su SSU_IN_CLASSE. Il trigger verifica la cardinalità minima: se la squadra
semiufficiale partecipa al campionato dell’anno considerato in quell’unica classe, occorre cancellare
anche la corrispondente tupla in SQSEMIUFF.
A questo punto occorre mantere la copertura totale della gerarchia PARTSQ propagando il processo
di cancellazione a PARTSQ; infatti una squadra semiufficiale può partecipare in un anno a più classi,
ma in tutte deve comparire come semiufficiale.
if exists (select name
from sysobjects
63
where name = 'TRDssu_in_classe'
and type = 'tr'
)
drop trigger TRDssu_in_classe
go
create trigger TRDssu_in_classe
on SSU_IN_CLASSE
for delete
as
declare
@nomesq
varchar (30)
declare
@anno
annogp
declare
@classe
categoria
select @nomesq = nome_sq, @anno = anno, @classe = classe
from DELETED
if not exists ( select *
from SSU_IN_CLASSE
where nome_sq = @nomesq
and anno = @anno
)
begin
delete from SQSEMIUFF
where nome_sq = @nomesq
and anno = @anno
delete from PART_SQ
where nome_sq = @nomesq
and anno = @anno
/*Se la cancellazione della tupla in PARTSQ fallisce (sponsorizzazione) non si può accettare la
cancellazione in SSU_IN_CLASSE*/
if @@rowcount = 0
begin
RAISERROR ('Prima occorre cancellare le sponsorizzazioni della squadra.',16,1)
ROLLBACK TRANSACTION
end
end
go
• TRDsp_in_classe
Trigger di cancellazione su SP_IN_CLASSE. Il trigger verifica la cardinalità minima: se la squadra
privata partecipa al campionato dell’anno considerato in quell’unica classe, occorre cancellare anche la
corrispondente tupla in SQPRIVATA.
A questo punto occorre mantere la copertura totale della gerarchia PARTSQ propagando il processo
di cancellazione a PARTSQ; infatti una squadra privata può partecipare in un anno a più classi, ma in
tutte deve comparire come privata.
64
if exists (select name
from sysobjects
where name = 'TRDsp_in_classe'
and type = 'tr'
)
drop trigger TRDsp_in_classe
go
create trigger TRDsp_in_classe
on SP_IN_CLASSE
for delete
as
declare
@nomesq
varchar (30)
declare
@anno
annogp
declare
@classe
categoria
select @nomesq = nome_sq, @anno = anno, @classe = classe
from DELETED
if not exists ( select *
from SP_IN_CLASSE
where nome_sq = @nomesq
and anno = @anno
)
begin
delete from SQPRIVATA
where nome_sq = @nomesq
and anno = @anno
delete from PART_SQ
where nome_sq = @nomesq
and anno = @anno
/*Se la cancellazione della tupla in PARTSQ fallisce (sponsorizzazione) non si può accettare la
cancellazione in SP_IN_CLASSE.*/
if @@rowcount = 0
begin
RAISERROR ('Prima occorre cancellare le sponsorizzazioni della squadra.',16,1)
ROLLBACK TRANSACTION
end
end
go
Stored Procedures
Una “Procedura Memorizzata” è un pacchetto software di codice SQL che viene compilato e
memorizzato in SQL Server come singolo modulo e può essere in seguito chiamato ed eseguito.
65
Abbiamo usato questo strumento per implementare le operazioni; abbiamo così creato un’astrazione,
dato che tutte le funzionalità che avvengono a basso livello sono astratte dalla persona che chiama la
procedura. Tutto ciò che la persona in questione sa è che viene chiamata una procedura, vengono
passati alcuni parametri e le tabelle giuste vengono popolate con i dati giusti e nel rispetto dei vincoli.
Quando possibile abbiamo preferito implementare i vincoli con triggers piuttosto che con stored
procedures, in questo modo è possibile un controllo anche sulle operazioni effettuate dal dbo
direttamente sul database.
CANCELLAZIONE
• DELdatigara
La cancellazione dei dati relativi ad una gara risulta essere un’operazione molto delicata con diversi
problemi di consistenza dei dati (es: ordine d’arrivo, giro veloce, …); per questo abbiamo negato i
diritti di cancellazione sulle tabelle CLASSIFICATO, NONCLASSIFICATO, CLASSIFICA e
RECORD.
D’altra parte abbiamo ritenuto necessario poter consentire una tale operazione; per questo abbiamo
realizzato una procedura che in modo ordinato e un po’ “drastico” azzera tutti i dati introdotti
relativamente a una certa gara.
Si osservi che la consistenza sui punti dei piloti nel campionato è assicurata dal trigger TRDtoglipunti.
if exists ( select name
from sysobjects
where name = 'DELdatigara'
and type = 'p'
)
drop procedure DELdatigara
go
create procedure DELdatigara
@classe
categoria,
@anno
annogp,
@nomecircuito
varchar (30)
as
declare @nuovotempo
char (8)
declare @nuovocognome varchar (30)
declare @nuovonome
varchar (30)
declare @nuovacasacostr varchar (15)
declare @nuovoanno
annogp
delete from PENALITA
where nomecircuito = @nomecircuito
and anno = @anno
and classe = @classe
delete from CLASSIFICATO
where nomecircuito = @nomecircuito
and anno = @anno
and classe = @classe
delete from NONCLASSIFICATO
66
where nomecircuito = @nomecircuito
and anno = @anno
and classe = @classe
delete from CLASSIFICA
where nomecircuito = @nomecircuito
and anno = @anno
and classe = @classe
update GARA
set tempo = ‘9:99:999’, cognomepil = null, nomepil = null
where nomecircuito = @nomecircuito
and anno = @anno
and classe = @classe
/*Occorre controllare che il giro veloce della gara che si intende cancellare non sia anche il record della
pista; altrimenti bisogna aggiornare la tabella RECORD*/
if exists ( select *
from RECORD
where nomecircuito = @nomecircuito
and classe = @classe
and anno = @anno
)
begin
select @nuovotempo = tempo, @nuovocognome = cognomepil, @nuovonome = nomepil,
@nuovoanno=anno
from GARA G
where nomecircuito = @nomecircuito
and classe = @classe
and tempo <= ALL ( select tempo
from GARA
where nomecircuito = @nomecircuito
and classe = @classe)
if @@rowcount <> 0
/*Occorre recuperare il dato relativo alla Casa Costruttrice*/
begin
select @nuovacasacostr = PR.casa_costr
from PIL_SP PSP, SP_IN_CLASSE SPIC, PRODUZIONE PR
where @nuovonome = PSP.nomepil
and @nuovocognome = PSP.cognomepil
and @nuovoanno = PSP.anno
and PSP.anno = SPIC.anno
and PSP.classe = SPIC.classe
and PSP.nome_sq = SPIC.nome_sq
and SPIC.sigla = PR.sigla
select @nuovacasacostr = PR.casa_costr
from PIL_SSU PSS, SSU_IN_CLASSE SSIC, PRODUZIONE PR
where @nuovonome = PSS.nomepil
and @nuovocognome = PSS.cognomepil
and @nuovoanno = PSS.anno
67
and PSS.anno = SSIC.anno
and PSS.classe = SSIC.classe
and PSS.nome_sq = SSIC.nome_sq
and SSIC.sigla = PR.sigla
select @nuovacasacostr = PR.casa_costr
from PIL_SU PSU, SU_IN_CLASSE SUIC, PRODUZIONE PR
where @nuovonome = PSU.nomepil
and @nuovocognome = PSU.cognomepil
and @nuovoanno = PSU.anno
and PSU.anno = SUIC.anno
and PSU.classe = SUIC.classe
and PSU.nome_sq = SUIC.nome_sq
and SUIC.sigla = PR.sigla
/*solo una delle tre select da un risultato*/
RAISERROR ('Il nuovo record del circuito %s è %s ',16,1, @nomecircuito, @nuovotempo)
update RECORD
set nomepil=@nuovonome, cognomepil=@nuovocognome, casa_costr=@nuovacasacostr,
anno=@nuovoanno, tempo=@nuovotempo
where nomecircuito = @nomecircuito
and classe = @classe
end
else
/*Vengono rimessi i valori a NULL in quanto non ci sono altre gare disputate */
update RECORD
set nomepil=null, cognomepil=null, casa_costr=null, anno=null, tempo='9:99:999'
where nomecircuito= @nomecircuito
and classe = @classe
end
go
INSERIMENTI
• INScircuito
La procedura di inserimento INScircuito si occupa di inserire i dati relativi a un circuito caricando i
valori relativi alle tre classi nella tabella RECORD. Il campo tempo viene inizializzato al valore di
default.
if exists ( select name
from sysobjects
where name = ’INScircuito’
and type = ‘p’
)
drop procedure INScircuito
go
68
create procedure INScircuito
@nomecircuito varchar (30),
@localita
varchar (30),
@lunghezza
smallint,
/*NULL*/
@siglanazione char (3)
as
begin
insert into CIRCUITO (nomecircuito, localita, lunghezza, siglanazione)
values (@nomecircuito, @localita, @lunghezza, @siglanazione)
insert into RECORD (nomecircuito, classe)
values (@nomecircuito, 125)
insert into RECORD (nomecircuito, classe)
values (@nomecircuito, 250)
insert into RECORD (nomecircuito, classe)
values (@nomecircuito, 500)
end
go
• INSdirigente
Le procedure di inserimento che seguono si occupano di inserire i dati di nuovi piloti, tecnici e
dirigenti aggiornando di conseguenza la tabella PERSONA per mantenere la copertura totale della
gerarchia. Essendo la gerarchia a copertura sovrapposta prima dell’inserimento nella relazione
PERSONA devo controllare se i dati sono già presenti.
if exists ( select name from sysobjects
where name = 'INSdirigente'
and type = 'p'
)
drop procedure INSdirigente
go
create procedure INSdirigente
@codpers varchar (20),
@nome
varchar (30),
@cognome varchar (30),
@datan
smalldatetime,
@luogon
varchar (30),
@nazio char (3)
as
if exists (select *
from PERSONA
where codpers=@codpers
and (nome <> @nome
or cognome <> @cognome
or datan <> @datan
or luogon <>@luogon
/*NULL*/
/*NULL*/
/*NULL*/
69
or nazionalita <> @nazio))
RAISERROR ('Errore: è già presente nel database una persona con lo stesso codice ma con
dati diversi.',16,1)
else
begin
if not exists ( select *
from PERSONA
where codpers=@codpers
)
insert into PERSONA (codpers, nome, cognome, datan, luogon, nazionalita)
values (@codpers, @nome, @cognome, @datan, @luogon, @nazio)
insert into DIRIGENTE (coddirigente)
values (@codpers)
end
go
• INSpilota
if exists ( select name from sysobjects
where name = 'INSpilota'
and type = 'p'
)
drop procedure INSpilota
go
create procedure INSpilota
@codpers
varchar (20),
@nome
varchar (30),
@cognome varchar (30),
@datan
smalldatetime,
@luogon
varchar (30),
@nazio char (3)
as
if exists (select *
from PERSONA
where codpers=@codpers
and (nome <> @nome
or cognome <> @cognome
or datan <> @datan
or luogon <>@luogon
or nazionalita <> @nazio))
RAISERROR ('Errore: è già presente nel database una persona con lo stesso codice ma con
dati diversi.',16,1)
else
BEGIN TRANSACTION
if not exists ( select *
from PERSONA
70
where codpers=@codpers
)
insert into PERSONA (codpers, nome, cognome, datan, luogon, nazionalita)
values (@codpers, @nome, @cognome, @datan, @luogon, @nazio)
insert into PILOTA (codpilota, nome, cognome)
values (@codpers, @nome, @cognome)
if @@rowcount = 0
begin
RAISERROR('Errore: esiste un pilota con lo stesso nome e cognome',16,1)
ROLLBACK TRANSACTION
end
else
COMMIT TRANSACTION
go
• INStecnico
if exists ( select name
from sysobjects
where name = 'INStecnico'
and type = 'p'
)
drop procedure INStecnico
go
create procedure INStecnico
@codpers varchar (20),
@nome varchar (30),
@cognome varchar (30),
@datan smalldatetime,
@luogon varchar (30),
@nazio char (3)
as
if exists ( select *
from PERSONA
where codpers=@codpers
and (nome <> @nome
or cognome <> @cognome
or datan <> @datan
or luogon <>@luogon
or nazionalita <> @nazio))
RAISERROR ('Errore: è già presente nel database una persona con lo stesso codice ma con
dati diversi.',16,1)
else
begin
if not exists ( select *
from PERSONA
71
where codpers=@codpers
)
insert into PERSONA (codpers, nome, cognome, datan, luogon, nazionalita)
values (@codpers, @nome, @cognome, @datan, @luogon, @nazio)
insert into TECNICO (codtecnico)
values (@codpers)
end
go
• INSlocazione
La procedura di inserimento INSlocazione si occupa dell’inserimento dei dati nella tabella
LOCAZIONE. Inoltre per rispettare la cardinalità che lega l’entità LOCAZIONE all’entità GARA
inserisce in quest’ultima i dati relativi alle tre diverse classi, lasciando a NULL gli attributi riguardanti
il giro veloce. Infine effettua l’inserimento nella tabella GP nel caso il gran premio non sia già stato
immesso.
if exists (select name from sysobjects
where name = ‘INSlocazione’
and type =’p’
)
drop procedure INSlocazione
go
create procedure INSlocazione
@nomecircuito varchar (30),
@anno
annogp,
@nomegp
varchar (30),
@giorno
tinyint,
@mese
varchar (10),
@ngiri125
tinyint,
/*NULL*/
@ngiri250
tinyint,
/*NULL*/
@ngiri500
tinyint
/*NULL*/
as
BEGIN TRANSACTION INSLOC
if not exists ( select *
from GP
where nome = @nomegp
)
insert into GP (nome)
values (@nomegp)
insert into LOCAZIONE (nomecircuito, anno, nomegp, giorno, mese)
values (@nomecircuito, @anno, @nomegp, @giorno, @mese)
if @@rowcount <>0
begin
72
insert into GARA (anno, classe, nomecircuito, numgiri)
values (@anno, 125, @nomecircuito, @ngiri125)
insert into GARA (anno, classe, nomecircuito, numgiri)
values (@anno, 250, @nomecircuito, @ngiri250)
insert into GARA (anno, classe, nomecircuito, numgiri)
values (@anno, 500, @nomecircuito, @ngiri500)
COMMIT TRANSACTION INSLOC
end
else
begin
RAISERROR ('Errore: l`inserimento non verrà effettuato.',16,1)
ROLLBACK TRANSACTION INSLOC
end
go
• INSmotoufficiale
Le procedure di inserimento INSmotoufficiale e INSmotoclienti si occupano dell’inserimento dei dati
nelle tabelle MOTOUFFICIALE e MOTOCLIENTI. La procedura mantiene intatti i vincoli di
integrita’ dei dati verificando se la nuova moto da inserire è di un modello già presente nel database, se
così non è effettua l’inserimento anche nelle tabelle MODUFF e MODCLIENTI. Per analoghi motivi
risale eventualmente ad effettuare l’inserimento fino alla tabella PRODUZIONE.
if exists ( select name
from sysobjects
where name = 'INSmotoufficiale'
and type = 'p'
)
drop procedure INSmotoufficiale
go
create procedure INSmotoufficiale
@sigla
varchar (5),
@classe
categoria,
@annop
annogp,
@cilindrata smallint,
@peso
tinyint,
/*NULL*/
@ncilindri tinyint,
/*NULL*/
@casacostr varchar(15)
as
BEGIN TRANSACTION
if not exists ( select *
from PRODUZIONE
where sigla = @sigla
)
insert into PRODUZIONE (sigla, casa_costr)
values (@sigla, @casacostr)
73
if not exists ( select *
from MODUFF
where sigla = @sigla
and classe = @classe
)
begin
insert into MODUFF (sigla, classe, n_cilindri)
values (@sigla, @classe ,@ncilindri)
if @@rowcount = 0
ROLLBACK TRANSACTION
end
insert into MOTOUFFICIALE (sigla, classe, annop, cilindrata, peso)
values (@sigla, @classe, @annop, @cilindrata, @peso)
if @@rowcount <> 0
COMMIT TRANSACTION
go
• INSmotoclienti
if exists ( select name
from sysobjects
where name = ‘INSmotoclienti’
and type = ‘p’
)
drop procedure INSmotoclienti
go
create procedure INSmotoclienti
@sigla
varchar (5),
@classe
categoria,
@annop
annogp,
@cilindrata smallint,
@peso
tinyint,
/*NULL*/
@ncilindri tinyint,
/*NULL*/
@casacostr varchar(15)
as
BEGIN TRANSACTION
if not exists ( select *
from PRODUZIONE
where sigla = @sigla
)
insert into PRODUZIONE (sigla, casa_costr)
values (@sigla, @casacostr)
if not exists ( select *
from MODCLIENTI
where sigla = @sigla
and classe = @classe
74
)
begin
insert into MODCLIENTI (sigla, classe, n_cilindri)
values (@sigla, @classe,@ncilindri)
if @@rowcount = 0
ROLLBACK TRANSACTION
end
insert into MOTOCLIENTI (sigla, classe, annop, cilindrata, peso)
values (@sigla, @classe, @annop, @cilindrata, @peso)
if @@rowcount <> 0
COMMIT TRANSACTION
go
• INSpart_in_team
Procedure di inserimento in PART_IN_TEAM.
if exists ( select name
from sysobjects
where name = 'INSpart_in_team'
and type = 'p'
)
drop procedure INSpart_in_team
go
create procedure INSpart_in_team
@anno annogp,
@cognomepil varchar(30),
@nomepil varchar (30),
@codice varchar (20),
@ruolo varchar (30)
as
insert into PART_IN_TEAM (anno, cognomepil, nomepil, codtecnico, ruolo )
values (@anno, @cognomepil, @nomepil, @codice, @ruolo)
go
• INSsp_in_classe
La procedure INSsp_in_classe si occupa dell’inserimento dei dati nella tabella SP_IN_CLASSE. La
procedura verifica se la partecipazione della squadra privata al campionato, individuata dall’anno e
dalla classe passati come parametri, avviene per una squadra già iscritta nello stesso anno in un’altra
classe: se così non è effettua l’inserimento nella tabella PART_SQ e successivamente in SQPRIVATA
per mantenere intatta l’integrità dei dati: per inserire un dato all’estremità di una gerarchia devo infatti
prima aggiornare tutte le entità della gerarchia a livello superiore. Infine si occupa dell’inserimento in
SQUADRA nel caso di prima partecipazione assoluta, garantendo così il rispetto della cardinalità
minima fra le entità SQUADRA e PART_SQ. Inoltre, per garantire la presenza di almeno un pilota
per ogni partecipazione di una squadra al campionato, alla procedura devono essere passati come
parametri il nome e il cognome di un pilota e il relativo numero di gara; si provvede poi all’inserimento
75
dei dati relativi al pilota nella tabella PART_PIL e successivamente nella tabella PIL_SP. Tramite una
chiamata alla procedura PROCnumgara si controlla che non vi sia già un altro pilota nella classe e
nell’anno considerati con lo stesso numero di gara.
if exists ( select name
from sysobjects
where name = 'INSsp_in_classe'
and type = 'p'
)
drop procedure INSsp_in_classe
go
create procedure INSsp_in_classe
@nomesq
varchar(30),
@anno
annogp,
@classe
categoria,
@sigla
varchar(5),
@annop
annogp,
@proprietario
varchar(20),
@nomepil
varchar(30),
@cognomepil
varchar(30),
@numgara
tinyint
as
declare @control tinyint
set @control=0
BEGIN TRANSACTION SQPRIV
/*Chiamata alla procedura PROCnumgara che controlla non ci sia già un pilota nella stessa classe e
nello stesso anno con lo stesso numero di gara del pilota che si deve inserire.*/
EXEC @control = procnumgara @anno, @classe, @numgara
if @control=1
begin
RAISERROR ('Errore: esiste già un pilota nella classe %d nell`anno %d col numero di gara
%d.', 16, 1, @classe, @anno, @numgara)
ROLLBACK TRANSACTION SQPRIV
end
else
begin
if not exists ( select *
from SQUADRA
where nome_sq = @nomesq
)
insert into SQUADRA (nome_sq)
values (@nomesq)
if not exists ( select *
from PART_SQ
where nome_sq = @nomesq
and anno = @anno
76
)
begin
insert into PART_SQ (nome_sq, anno)
values (@nomesq, @anno)
insert into SQPRIVATA (nome_sq, anno, coddirsp)
values (@nomesq, @anno, @proprietario)
if @@rowcount = 0
begin
RAISERROR ('Errore: l`inserimento non verrà effettuato.', 16, 1)
ROLLBACK TRANSACTION SQPRIV
end
end
if exists ( select *
from SP_IN_CLASSE
where nome_sq = @nomesq
and classe = @classe
and anno = @anno
)
begin
RAISERROR ('La squadra %s è già presente nell`anno %d nella classe %d.', 16, 1, @nomesq,
@anno, @classe)
ROLLBACK TRANSACTION SQPRIV
end
else
begin
insert into SP_IN_CLASSE (nome_sq, anno, classe, sigla, annop)
values (@nomesq, @anno, @classe, @sigla, @annop)
if @@rowcount =0
begin
RAISERROR ('Errore: l`inserimento non verrà effettuato.', 16, 1)
ROLLBACK TRANSACTION SQPRIV
end
else
begin
insert into PART_PIL (nomepil, cognomepil, anno, numgara)
values (@nomepil, @cognomepil, @anno, @numgara)
if @@rowcount<>0
begin
insert into PIL_SP (nomepil, cognomepil, anno, nome_sq, classe)
values (@nomepil, @cognomepil, @anno, @nomesq, @classe)
COMMIT TRANSACTION SQPRIV
end
else
begin
RAISERROR ('Errore: l`inserimento non verrà effettuato.', 16, 1)
ROLLBACK TRANSACTION SQPRIV
end
77
end
end
end
go
• INSsp_sq
Le procedure di inserimento INSsp_sq e INSsp_pil si occupano dell’inserimento dei dati relativi alle
sponsorizzazioni delle squadre e dei piloti. In caso di prima sponsorizzazione da parte dello sponsor,
si occupa anche dell’inserimento nella tabella sponsor.
if exists ( select name
from sysobjects
where name = 'INSsp_sq'
and type = 'p'
)
drop procedure INSsp_sq
go
create procedure INSsp_sq
@anno annogp,
@nomesq varchar (30),
@nomesp varchar (20),
@quota int,
/*NULL*/
@tipoaz varchar (50)
/*NULL*/
as
BEGIN TRANSACTION
if not exists ( select *
from SPONSOR
where nome_sp = @nomesp
)
insert into SPONSOR (nome_sp, tipo_az)
values (@nomesp, @tipoaz)
insert into SP_SQ (nome_sp, anno, nome_sq, quota)
values (@nomesp, @anno, @nomesq, @quota)
if @@rowcount = 0
/*potrebbe fallire per la “RULE” su anno*/
ROLLBACK TRANSACTION
else COMMIT TRANSACTION
go
• INSsp_pil
if exists ( select name
from sysobjects
where name = 'INSsp_pil'
and type = 'p'
)
78
drop procedure INSsp_pil
go
create procedure INSsp_pil
@anno annogp,
@cognome varchar(30),
@nome varchar (30),
@nomesp varchar (20),
@quota int,
@tipoaz varchar (50)
as
BEGIN TRANSACTION
if not exists ( select *
from SPONSOR
where nome_sp = @nomesp
)
insert into SPONSOR (nome_sp, tipo_az)
values (@nomesp, @tipoaz)
insert into SP_PIL (nome_sp, anno, nomepil, cognomepil, quota)
values (@nomesp, @anno, @nome, @cognome, @quota)
if @@rowcount = 0
ROLLBACK TRANSACTION
else COMMIT TRANSACTION
go
/*NULL*/
/*NULL*/
• INSssu_in_classe
La procedura INSssu_in_classe agisce in modo del tutto identico alla procedura INSsp_in_classe.
if exists ( select name
from sysobjects
where name = 'INSssu_in_classe'
and type = 'p'
)
drop procedure INSssu_in_classe
go
create procedure INSssu_in_classe
@nomesq
varchar(30),
@anno
annogp,
@classe
categoria,
@sigla
varchar(5),
@annop
annogp,
@proprietario
varchar(20),
@nomepil
varchar(30),
@cognomepil
varchar(30),
@numgara
tinyint,
79
@priorita
bit
as
declare @control tinyint
set @control=0
BEGIN TRANSACTION SQSEMIUFF
/*chiamata alla procedura PROCnumgara che controlla non ci sia già un pilota nella stessa classe e
nello stesso anno con lo stesso numero di gara del pilota che si deve inserire.*/
EXEC @control = procnumgara @anno, @classe, @numgara
if @control=1
begin
RAISERROR ('Errore: esiste già un pilota nella classe %d nell`anno %d col numero di gara
%d.', 16, 1, @classe, @anno, @numgara)
ROLLBACK TRANSACTION SQSEMIUFF
end
else
begin
if not exists ( select *
from SQUADRA
where nome_sq = @nomesq
)
insert into SQUADRA (nome_sq)
values (@nomesq)
if not exists ( select *
from PART_SQ
where nome_sq = @nomesq
and anno = @anno
)
begin
insert into PART_SQ (nome_sq, anno)
values (@nomesq, @anno)
insert into SQSEMIUFF (nome_sq, anno, coddirsp)
values (@nomesq, @anno, @proprietario)
if @@rowcount = 0
begin
RAISERROR ('Errore: l`inserimento non verrà effettuato.', 16, 1)
ROLLBACK TRANSACTION SQSEMIUFF
end
end
if exists ( select *
from SSU_IN_CLASSE
where nome_sq = @nomesq
and classe = @classe
and anno = @anno
)
begin
RAISERROR ('La squadra %s è già presente nell`anno %d nella classe %d.', 16, 1, @nomesq,
@anno, @classe)
80
ROLLBACK TRANSACTION SQSEMIUFF
end
else
begin
insert into SSU_IN_CLASSE (nome_sq, anno, classe, sigla, annop)
values (@nomesq, @anno, @classe, @sigla, @annop)
if @@rowcount =0
begin
RAISERROR ('Errore: l`inserimento non verrà effettuato.', 16, 1)
ROLLBACK TRANSACTION SQSEMIUFF
end
else
begin
insert into PART_PIL (nomepil, cognomepil, anno, numgara)
values (@nomepil, @cognomepil, @anno, @numgara)
if @@rowcount<>0
begin
insert into PIL_SSU (nomepil, cognomepil, anno, nome_sq, classe, priorita)
values (@nomepil, @cognomepil, @anno, @nomesq, @classe, @priorita)
COMMIT TRANSACTION SQSEMIUFF
end
else
begin
RAISERROR ('errore: l`inserimento non verra`effettuato.', 16, 1)
ROLLBACK TRANSACTION SQSEMIUFF
end
end
end
end
go
• INSsu_in_classe
La procedura di inserimento INSsu_in_classe agisce in modo del tutto identico alla procedura
INSsp_in_classe.
if exists ( select name
from sysobjects
where name = 'INSsu_in_classe'
and type = 'p'
)
drop procedure INSsu_in_classe
go
create procedure INSsu_in_classe
@nomesq
varchar(30),
@anno
annogp,
81
@classe
categoria,
@sigla
varchar(5),
@coddirsp
varchar(20),
@nomepil
varchar(30),
@cognomepil
varchar(30),
@numgara
tinyint
as
declare @control tinyint
set @control=0
begin transaction squff
/*Chiamata alla procedura PROCnumgara che controlla non ci sia già un pilota nella stessa classe e
nello stesso anno con lo stesso numero di gara del pilota che si deve inserire.*/
EXEC @control = procnumgara @anno, @classe, @numgara
if @control=1
begin
RAISERROR ('Errore: esiste già un pilota nella classe %d nell`anno %d col numero di gara
%d.', 16, 1, @classe, @anno, @numgara)
ROLLBACK TRANSACTION SQUFF
end
else
begin
if not exists ( select *
from SQUADRA
where nome_sq = @nomesq
)
insert into SQUADRA (nome_sq)
values (@nomesq)
if not exists ( select *
from PART_SQ
where nome_sq = @nomesq
and anno = @anno
)
begin
insert into PART_SQ (nome_sq, anno)
values (@nomesq, @anno)
insert into SQUFFICIALE (nome_sq, anno)
values (@nomesq, @anno)
if @@rowcount = 0
begin
RAISERROR ('Errore: l`inserimento non verrà effettuato.', 16, 1)
ROLLBACK TRANSACTION SQUFF
end
end
if exists (select *
from SU_IN_CLASSE
where nome_sq = @nomesq
and classe = @classe
82
and anno = @anno)
begin
RAISERROR ('La squadra %s è già presente nell`anno %d nella classe %d.', 16, 1, @nomesq,
@anno, @classe)
ROLLBACK TRANSACTION SQUFF
end
else
begin
insert into SU_IN_CLASSE (nome_sq, anno, classe, sigla, coddirsp)
values (@nomesq, @anno, @classe, @sigla, @coddirsp)
if @@rowcount =0
begin
RAISERROR ('errore: l`inserimento non verra` effettuato.', 16, 1)
ROLLBACK TRANSACTION SQUFF
end
else
begin
insert into PART_PIL (nomepil, cognomepil, anno, numgara)
values (@nomepil, @cognomepil, @anno, @numgara)
if @@rowcount<>0
begin
insert into PIL_SU (nomepil, cognomepil, anno, nome_sq, classe)
values (@nomepil, @cognomepil, @anno, @nomesq, @classe)
COMMIT TRANSACTION SQUFF
end
else
begin
RAISERROR ('errore: l`inserimento non verra`effettuato.', 16, 1)
ROLLBACK TRANSACTION SQUFF
end
end
end
end
go
• INSclassificato
La procedura INSclassificato si occupa di inserire i dati relativi ai risultati ottenuti da un pilota in una
certa gara. Vengono passati come parametri, fra gli altri, la posizione di partenza e quella di arrivo del
pilota in questione e la procedura si occupa di effettuare l’inserimento nella tabella CLASSIFICA e in
seguito in CLASSIFICATO, garantendo la copertura totale ed esclusiva della gerarchia. L’ultimo
parametro rappresenta il tempo del giro veloce effettuato dal pilota nella gara; si noti che la procedura
inserisce in GARA solo il più basso tra quelli inseriti (giro veloce della gara).
if exists ( select name
from sysobjects
where name = 'INSclassificato'
83
and type = 'p'
)
drop procedure INSclassificato
go
create procedure INSclassificato
@nomepil
varchar (30),
@cognomepil
varchar (30),
@classe
categoria,
@anno
annogp,
@nomecircuito
varchar (30),
@pos_iniz
tinyint,
@pos_fin
tinyint,
@tempo
char (8)
as
BEGIN TRANSACTION
insert into CLASSIFICA (nomepil, cognomepil, classe, anno, nomecircuito, pos_iniz)
values (@nomepil, @cognomepil, @classe, @anno, @nomecircuito, @pos_iniz)
if @@rowcount=0
begin
RAISERROR ('Errore: l’inserimento non verrà effettuato.',16,1)
ROLLBACK TRANSACTION
end
else
begin
insert into CLASSIFICATO (nomepil, cognomepil, anno, classe, nomecircuito,
pos_fin)
values (@nomepil, @cognomepil, @anno, @classe, @nomecircuito, @pos_fin)
if @@rowcount = 0
begin
RAISERROR ('Errore: l`inserimento non verrà effettuato.',16,1)
ROLLBACK TRANSACTION
end
else
begin
if (@tempo is not null)
if exists ( select *
from GARA
where classe=@classe
and anno=@anno
and nomecircuito=@nomecircuito
and @tempo<tempo
)
update GARA
set tempo = @tempo, nomepil = @nomepil, cognomepil = @cognomepil
where nomecircuito = @nomecircuito
and classe = @classe
84
and anno = @anno
COMMIT TRANSACTION
end
end
go
• INSnonclassificato
La procedura di inserimento in NONCLASSIFICATO si occupa di inserire i dati relativi a un pilota
che non ha portato a termine la gara. Vengono passati come parametri, fra gli altri, la posizione di
partenza e la causa del ritiro del pilota in questione e la procedura si occupa di effettuare l’inserimento
nella tabella CLASSIFICA e in seguito in NONCLASSIFICATO, garantendo la copertura totale ed
esclusiva della gerarchia.
if exists ( select name
from sysobjects
where name = ‘INSnonclassificato'
and type = 'p'
)
drop procedure INSnonclassificato
go
create procedure INSnonclassificato
@nomepil
varchar (30),
@cognomepil
varchar (30),
@classe
categoria,
@anno
annogp,
@nomecircuito
varchar (30),
@causarit
varchar (30),
@numgiro
tinyint,
@motivosq
varchar (30),
@pos_iniz
tinyint
as
begin
insert into CLASSIFICA (nomepil, cognomepil, classe, anno, nomecircuito, pos_iniz)
values (@nomepil, @cognomepil, @classe, @anno, @nomecircuito, @pos_iniz)
if @@rowcount<>0
insert into NONCLASSIFICATO (nomepil, cognomepil, anno, classe, nomecircuito, numgiro,
causarit, motivosq)
values (@nomepil, @cognomepil, @anno, @classe, @nomecircuito, @numgiro, @causarit,
@motivosq)
else
RAISERROR ('Errore: l’inserimento non verrà effettuato.',16,1)
end
go
85
• INSpenalita
La procedura di inserimento in PENALITA si occupa di inserire le penalità inflitte a un pilota durante
una gara. Vengono passati come parametri, fra gli altri, il giro in cui la penalità è stata inflitta, la causa
di tale provvedimento e il tipo di penalità prevista. Si noti che, potendo essere presenti più penalità
per un pilota in una certa gara, la procedura si occupa di settare la variabile numord che servirà ad
identificare la penalità inserita nel database.
if exists ( select name
from sysobjects
where name = 'INSpenalita'
and type = 'p'
)
drop procedure INSpenalita
go
create procedure INSpenalita
@nomepil
varchar (30),
@cognomepil
varchar (30),
@nomecircuito
varchar (30),
@anno
annogp,
@classe
categoria,
@causa
varchar (30),
@numgiro
tinyint,
@tipo
varchar (30)
as
declare
@numord
tinyint
if not exists ( select *
from PENALITA
where nomepil = @nomepil
and cognomepil = @cognomepil
and anno = @anno
and nomecircuito = @nomecircuito
)
set @numord=1
else
begin
set @numord = ( select max(nordine)
from PENALITA
where nomepil = @nomepil
and cognomepil = @cognomepil
and anno = @anno
and nomecircuito = @nomecircuito
)
86
set @numord = @numord + 1
end
if exists ( select *
from CLASSIFICA
where nomepil = @nomepil
and cognomepil = @cognomepil
and anno = @anno
and classe = @classe
and nomecircuito = @nomecircuito
)
insert into PENALITA (anno, classe, nomecircuito, cognomepil, nomepil, nordine, causa, numgiro,
tipo) values (@anno, @classe, @nomecircuito, @cognomepil, @nomepil, @numord, @causa,
@numgiro, @tipo)
else
RAISERROR ('Impossibile inserire la penalità, il pilota cui si fa riferimento non compare nella
classifica.', 16, 1)
go
• INSpil_sp
La procedura d’inserimento INSpil_sp si occupa dell’ inserimento dei dati nella tabella PIL_SP dopo
aver controllato, tramite una chiamata alla procedura PROCnumgara, che non vi sia gia’ un altro pilota
nella classe e nell’anno considerati con lo stesso numero di gara e dopo aver effettuato l’inserimento
nella tabella PART_PIL dei dati riguardanti il pilota che si vuole inserire.
if exists ( select name
from sysobjects
where name = 'INSpil_sp'
and type = 'p'
)
drop procedure INSpil_sp
go
create procedure INSpil_sp
@nomepil
varchar (30),
@cognomepil varchar (30),
@anno
annogp,
@nome_sq varchar (30),
@classe
categoria,
@numgara
tinyint
as
declare @control tinyint
set @control=0
if exists ( select *
from PART_PIL
where nomepil = @nomepil
and cognomepil = @cognomepil
87
and anno = @anno
)
RAISERROR ('Errore: il pilota è già inserito in una squadra per l’anno %d.', 16, 1, @anno)
else
begin
/*Chiamata alla procedura PROCnumgara che controlla non ci sia gia` un pilota nella stessa classe e
nello stesso anno con lo stesso numero di gara del pilota che si deve inserire.*/
EXEC @control = procnumgara @anno, @classe, @numgara
if @control=1
RAISERROR ('Errore: esiste già un pilota nella classe %d nell`anno %d col numero di gara %d.', 16,
1, @classe, @anno, @numgara)
else
if not exists ( select *
from pilota
where nome = @nomepil
and cognome = @cognomepil
)
RAISERROR ('Errore: i dati del pilota non sono stati immessi.',16,1)
else
BEGIN TRANSACTION
insert into PART_PIL ( nomepil, cognomepil, numgara, anno)
values (@nomepil, @cognomepil, @numgara, @anno)
insert into PIL_SP ( nomepil, cognomepil, anno, classe, nome_sq)
values (@nomepil, @cognomepil, @anno, @classe, @nome_sq)
if @@rowcount<>0
COMMIT TRANSACTION
else
begin
RAISERROR ('Errore: la squadra %s non è presente nel database relativamente all`anno %d e
alla classe %d.', @nome_sq, @anno, @classe, 16,1)
ROLLBACK TRANSACTION
end
end
go
• INSpil_ssu
La procedura di inserimento INSpil_ssu agisce in modo del tutto identico alla procedura INSpil_sp.
if exists ( select name
from sysobjects
where name = 'INSpil_ssu'
and type = 'p'
)
drop procedure INSpil_ssu
go
88
create procedure INSpil_ssu
@nomepil
varchar (30),
@cognomepil varchar (30),
@anno
annogp,
@nome_sq varchar (30),
@classe
categoria,
@numgara
tinyint,
@priorita
bit
as
declare @control tinyint
set @control=0
if exists ( select *
from PART_PIL
where nomepil = @nomepil
and cognomepil = @cognomepil
and anno = @anno
)
RAISERROR ('Errore: il pilota è già inserito in una squadra per l’anno %d.', 16, 1, @anno)
else
begin
/*Chiamata alla procedura PROCnumgara che controlla non ci sia già un pilota nella stessa classe e
nello stesso anno con lo stesso numero di gara del pilota che si deve inserire.*/
EXEC @control = procnumgara @anno, @classe, @numgara
if @control=1
RAISERROR ('Errore: esiste già un pilota nella classe %d nell`anno %d col numero di gara %d.', 16,
1, @classe, @anno, @numgara)
else
if not exists ( select *
from PILOTA
where nome = @nomepil
and cognome = @cognomepil
)
RAISERROR ('Errore: i dati del pilota non sono stati immessi.',16,1)
else
BEGIN TRANSACTION
insert into PART_PIL ( nomepil, cognomepil, numgara, anno)
values (@nomepil, @cognomepil, @numgara, @anno)
insert into PIL_SSU ( nomepil, cognomepil, anno, classe, nome_sq, priorita)
values (@nomepil, @cognomepil, @anno, @classe, @nome_sq, @priorita)
if @@rowcount<>0
COMMIT TRANSACTION
else
begin
RAISERROR ('Errore: la squadra %s non è presente nel database relativamente all`anno %d e
alla classe %d.', @nome_sq, @anno, @classe, 16,1)
ROLLBACK TRANSACTION
end
89
end
go
• INSpil_su
La procedura di inserimento INSpil_su agisce in modo del tutto identico alla procedura INSpil_sp.
if exists ( select name
from sysobjects
where name = 'INSpil_su'
and type = 'p'
)
drop procedure INSpil_su
go
create procedure INSpil_su
@nomepil
varchar (30),
@cognomepil varchar (30),
@anno
annogp,
@nome_sq varchar (30),
@classe
categoria,
@numgara
tinyint
as
declare @control tinyint
set @control=0
if exists ( select *
from PART_PIL
where nomepil = @nomepil
and cognomepil = @cognomepil
and anno = @anno
)
RAISERROR (' errore: il pilota è già inserito in una squadra per l’anno %d.', 16, 1, @anno)
else
begin
/*Chiamata alla procedura PROCnumgara che controlla non ci sia già un pilota nella stessa classe e
nello stesso anno con lo stesso numero di gara del pilota che si deve inserire.*/
EXEC @control = procnumgara @anno, @classe, @numgara
if @control=1
RAISERROR ('Errore: esiste già un pilota nella classe %d nell`anno %d col numero di gara %d.', 16,
1, @classe, @anno, @numgara)
else
if not exists ( select *
from PILOTA
where nome = @nomepil
and cognome = @cognomepil
)
RAISERROR ('errore: i dati del pilota non sono stati immessi.',16,1)
90
else
BEGIN TRANSACTION
insert into PART_PIL ( nomepil, cognomepil, numgara, anno)
values (@nomepil, @cognomepil, @numgara, @anno)
insert into PIL_SU ( nomepil, cognomepil, anno, classe, nome_sq)
values (@nomepil, @cognomepil, @anno, @classe, @nome_sq)
if @@rowcount<>0
COMMIT TRANSACTION
else
begin
RAISERROR ('errore: la squadra %s non e` presente nel database relativamente all`anno %d e
alla classe %d.', @nome_sq, @anno, @classe, 16,1)
ROLLBACK TRANSACTION
end
end
go
• PROCnumgara
Questa procedura controlla che non sia già presente nel database un pilota che corre con il numero di
gara passato come parametro nello stesso anno e nella stessa classe considerati: ritorna il valore 0 se
non trova nulla, il valore 1 se trova un altro pilota con lo stesso numero.
if exists ( select name
from sysobject
where name = ‘PROCnumgara’
and type = ‘p’
)
drop procedure PROCnumgara
go
create procedure PROCnumgara
@anno
annogp,
@classe
categoria,
@numgara tinyint
as
begin
if exists ( select *
from PART_PIL
where anno=@anno
and classe=@classe
and numgara = @numgara
)
return 1
else
return 0
end
91
go
IMPLEMENTAZIONE DELLE OPERAZIONI RICHIESTE DA SPECIFICHE
NOTA: Nelle tabelle del database sono stati inseriti solo i dati di stretto interesse al fine di poter eseguire e verificare le
operazioni, pertanto sono presenti alcune incoerenze e una quantita’di dati limitata.
Operazione 1: l’ inserimento dei dati di una gara non viene implementato mediante una procedura
ma viene realizzato direttamente in visual basic mediante una maschera che
richiama le seguenti procedure di inserimento : PRINSCLASSIF,
PRINSNONCLASSIF,PRINSPENALITA .
• proccreatmp
La procedura proccreatmp ha il compito di creare una tabella temporanea ##TMP e riempirla con i
nomi dei piloti presenti nel database riportando, per ognuno degli anni in cui il pilota ha partecipato al
campionato mondiale, la classe in cui ha corso, i punti conquistati e il numero di vittorie ottenute in
quella stagione. Tale tabella viene poi utilizzata da diverse store procedures, dedicate
all’implementazione delle operazioni richiete da specifiche, tramite previa chiamata alla procedura
proccreatmp. Si noti l`utilizzo di un cursore che recupera le tuple della tabella PART_PIL che in
seguito serviranno per riempire la tabella temporanea.
if exists ( select name
from sysobjects
where name = 'proccreatmp'
)
drop procedure proccreatmp
go
create procedure proccreatmp
as
declare @nome varchar (30)
declare @cognome varchar (30)
declare @punti smallint
declare @vittorie tinyint
declare @anno annogp
declare @classe categoria
create table ##TMP (
anno smallint,
check (anno>=1980),
classe smallint,
check ((classe=125) or (classe=250) or (classe=500)),
92
nomepil varchar (30) not null,
cognomepil varchar (30) not null,
puntip smallint null,
n_vitt tinyint null
constraint primarytmp primary key (nomepil, cognomepil, anno)
)
/*dichiarazione del cursore cur_class*/
declare cur_class cursor for
select anno, classe, nomepil, cognomepil, puntip
from PART_PIL
order by puntip desc
open cur_class
fetch next from cur_class
into @anno, @classe, @nome, @cognome, @punti
while (@@fetch_status=0)
begin
select @vittorie = count (*)
from CLASSIFICATO
where anno=@anno
and classe=@classe
and nomepil=@nome
and cognomepil=@cognome
and pos_fin=1
insert into ##TMP (anno, classe, nomepil, cognomepil, puntip, n_vitt)
values (@anno, @classe, @nome, @cognome, @punti, @vittorie)
fetch next from cur_class
into @anno, @classe, @nome, @cognome, @punti
end
close cur_class
deallocate cur_class
go
• PROCclasspil
Questa procedura si occupa di visualizzare la classifica dei piloti relativamente all’anno e alla classe
passati come parametri. Accanto ai nomi dei piloti vengono riportati i punti conquistati e il numero di
vittorie ottenute. Si noti che la procedura sfrutta i dati contenuti nella tabella temporanea ##TMP,
creata e riempita dalla procedura proccreatmp, invocata all’inizio del codice.
if exists ( select name
from sysobjects
where name = 'PROCclasspil'
)
drop procedure PROCclasspil
go
93
create procedure PROCclasspil
@anno annogp,
@classe categoria
as
exec proccreatmp
select nomepil, cognomepil, puntip, n_vitt
from ##TMP
where anno=@anno
and classe=@classe
order by puntip desc, n_vitt desc
drop table ##TMP
go
• PROCclassificacc
Questa procedura si occupa di visualizzare la classifica delle case costruttrici relativamente all’anno e
alla classe passati come parametri in ingresso. Accanto ad ogni casa costruttrice vengono riportati i
punti ottenuti nelle varie gare del campionato.
if exists (select * from sysobjects
where name = ‘PROCclassificacc’
and type = ‘p’
)
drop procedure PROCclassificacc
go
CREATE procedure PROCclassificacc
@anno annogp, @classe categoria
as
if @classe = 125
select casa_costr, sum(punti) as punti
from VEWsq125 VS, VEWpil125 VP
where VS.nome_sq=VP.nome_sq
and VS.anno=VP.anno
and VS.anno=@anno
group by casa_costr
order by 2 desc
else
if @classe = 250
select casa_costr, sum(punti) as punti
from VEWsq250 VS, VEWpil250 VP
where VS.nome_sq=VP.nome_sq
and VS.anno=VP.anno
and VS.anno=@anno
group by casa_costr
order by 2 desc
else
94
if @classe = 500
select casa_costr, sum(punti) as punti
from VEWsq500 VS, VEWpil500 VP
where VS.nome_sq=VP.nome_sq
and VS.anno=VP.anno
and VS.anno=@anno
group by casa_costr
order by 2 desc
go
• PROCclassparz
Questa procedura si occupa di visualizzare la classifica parziale dei piloti relativamente all’anno e alla
classe passati come parametri in ingresso. Accanto al nominativo di ogni pilota vengono riportati i
diversi circuiti su cui si sono disputate gare del campionato con i relativi punti ottenuti e infine la
somma totale dei punti in classifica. Si noti che la procedura sfrutta i dati contenuti nella tabella
temporanea ##TMP, creata e riempita dalla procedura proccreatmp, invocata all’inizio del codice.
if exists (select * from sysobjects
where name = ‘PROCclassparz’
and type = ‘p’
)
drop procedure PROCclassparz
go
create procedure Procclassparz
@anno annogp,
@classe categoria
as
(select P.cognomepil, P.nomepil, G.nomecircuito, PU.punti, P.puntip
from PART_PIL P, GARA G, CLASSIFICATO CL, PUNTEGGIO PU
where G.anno=@anno
and G.classe=@classe
and CL.anno=G.anno
and CL.classe=G.classe
and CL.nomecircuito=G.nomecircuito
and P.nomepil=CL.nomepil
and P.cognomepil=CL.cognomepil
and P.anno=@anno
and CL.pos_fin=PU.pos_fin)
union
(select P.cognomepil, P.nomepil, G.nomecircuito, 0, P.puntip
from PART_PIL P, GARA G
where P.anno=@anno
and P.classe=@classe
and G.anno=P.anno
and P.classe=G.classe
95
and not exists (select *
from CLASSIFICATO CL
where CL.nomepil=P.nomepil
and CL.cognomepil=P.cognomepil
and CL.nomecircuito=G.nomecircuito
and Cl.anno=G.anno)
)
order by 5 desc, 1, 2, 3
go
• PROCalbodoro
Questa procedura implementa l’operazione N. 3, ossia la visualizzazione dell’albo d’oro relativamente
agli anni precedenti all’anno che viene passato come parametro in ingresso. Per ogni classe vengono
riportati i nomi dei piloti che hanno vinto almeno un mondiale con affianco il numero dei mondiali
vinti nella classe considerata. Si noti che la procedura sfrutta i dati contenuti nella tabella temporanea
##TMP, creata e riempita dalla procedura proccreatmp, invocata all’inizio del codice.
if exists ( select name
from sysobjects
where name = 'PROCalbodoro'
)
drop procedure PROCalbodoro
go
create procedure PROCalbodoro
@anno annogp
as
exec proccreatmp
print 'CLASSE 125'
/*Albo d'oro classe 125*/
(select nomepil, cognomepil, count(*) as mondiali_vinti
from ##TMP P
where classe=125
and anno<@anno
and not exists( select *
from ##TMP P1
where P.anno=P1.anno
and P.classe=P1.classe
and P.puntip<P1.puntip)
and not exists ( select *
from ##TMP P2
where P.anno=P2.anno
and P.classe=P2.classe
and P.puntip=P2.puntip
and P.n_vitt<P2.n_vitt)
and anno=any( select anno
from PART_PIL
96
where nomepil=P.nomepil
and cognomepil=P.cognomepil)
group by P.nomepil, P.cognomepil)
order by 3 desc, cognomepil, nomepil
print 'CLASSE 250'
/*Albo d'oro classe 250 */
(select nomepil, cognomepil, count(*) as mondiali_vinti
from ##TMP P
where classe=250
and anno < @anno
and not exists( select *
from ##TMP P1
where P.anno=P1.anno
and P.classe=P1.classe
and P.puntip<P1.puntip)
and not exists ( select *
from ##TMP P2
where P.anno=P2.anno
and P.classe=P2.classe
and P.puntip=P2.puntip
and P.n_vitt<P2.n_vitt)
and anno=any( select anno
from PART_PIL
where nomepil=P.nomepil
and cognomepil=P.cognomepil)
group by P.nomepil, P.cognomepil)
order by 3 desc, cognomepil, nomepil
print 'CLASSE 500'
/*Albo d'oro classe 500*/
(select nomepil, cognomepil, count(*) as mondiali_vinti
from ##TMP P
where classe=500
and anno < @anno
and not exists( select *
from ##TMP P1
where P.anno=P1.anno
and P.classe=P1.classe
and P.puntip<P1.puntip)
and not exists ( select *
from ##TMP P2
where P.anno=P2.anno
and P.classe=P2.classe
and P.puntip=P2.puntip
and P.n_vitt<P2.n_vitt)
and anno=any( select anno
from PART_PIL
where nomepil=P.nomepil
and cognomepil=P.cognomepil)
group by P.nomepil, P.cognomepil)
97
order by 3 desc, cognomepil, nomepil
go
• PROCcarrierapil
Questa procedura implementa una operazione abbastanza articolata: la visualizzazione della carriera di
un pilota il cui nominativo viene passato come parametro in ingresso. Vengono riportate in uscita
diverse informazioni: nell’ordine si visualizza per ogni classe il numero di gare vinte, di gare disputate
e di giri veloci compiuti; segue la posizione finale raggiunta in classifica al termine di ogni anno in cui
il pilota ha partecipato al campionato e infine il numero di mondiali vinti in ogni classe. Si noti che per
ottenere alcuni di questi risultati la procedura sfrutta i dati contenuti nella tabella temporanea ##TMP,
creata e riempita dalla procedura proccreatmp, invocata in alcuni punti del codice.
if exists ( select name
from sysobjects
where name = 'PROCcarrierapil'
)
drop procedure PROCcarrierapil
go
create procedure PROCcarrierapil
@nome varchar(30),
@cognome varchar(30)
as
declare @cont125 tinyint
declare @cont250 tinyint
declare @cont500 tinyint
declare @classe categoria
set @cont125=0
set @cont250=0
set @cont500=0
/*Utilizzo il cursore cur_vitt per recuperare le tuple dalla tabella CLASSIFICATO e contare le
vittorie di un pilota nelle tre diverse classi aumentando il valore dei relativi contatori dichiarati come
variabili*/
declare cur_vitt cursor for
( select classe
/*op 4a) numero di vittorie*/
from CLASSIFICATO
where nomepil=@nome
and cognomepil=@cognome
and pos_fin=1
)
open cur_vitt
fetch next from cur_vitt
into @classe
while (@@fetch_status = 0)
begin
if @classe=125
98
set @cont125= @cont125 + 1
if @classe=250
set @cont250=@cont250 + 1
if @classe = 500
set @cont500=@cont500 + 1
fetch next from cur_vitt
into @classe
end
RAISERROR ('Il pilota %s %s ha vinto %d gare nella classe 125, %d nella classe 250 e %d nella
classe 500',16,1,@nome,@cognome,@cont125, @cont250, @cont500)
close cur_vitt
deallocate cur_vitt
select @cont125=count(*)
/*op 4b) numero di gara fatte*/
from CLASSIFICA
where nomepil=@nome
and cognomepil=@cognome
and classe=125
select @cont250=count(*)
from CLASSIFICA
where nomepil=@nome
and cognomepil=@cognome
and classe=250
select @cont500=count(*)
from CLASSIFICA
where nomepil=@nome
and cognomepil=@cognome
and classe=500
RAISERROR ('Il pilota %s %s ha partecipato a %d gare nella classe 125, a %d gare nella classe 250 e
a %d gare nella classe 500',16,1,@nome, @cognome, @cont125, @cont250, @cont500)
select @cont125=count(*)
/*OP 4c) numero di giri veloci*/
from GARA
where nomepil=@nome
and cognomepil=@cognome
and classe=125
select @cont250=count(*)
from GARA
where nomepil=@nome
and cognomepil=@cognome
and classe=250
select @cont500=count(*)
from GARA
where nomepil=@nome
and cognomepil=@cognome
and classe=500
RAISERROR ('Il pilota %s %s ha fatto %d giri veloci nella classe 125, %d nella classe 250, %d nella
classe 500',16,1,@nome, @cognome, @cont125, @cont250, @cont500)
99
exec proccreatmp
(select anno, classe, count(*)+1 as posizione_finale
/*OP 4d)posizione in classifica al */
from ##TMP P
/*termine del campionato per ogni anno*/
where exists( select *
from ##TMP P1
where nomepil=@nome
and cognomepil=@cognome
and P.anno=P1.anno
and P.classe=P1.classe
and P.puntip>P1.puntip)
or exists ( select *
from ##TMP P2
where nomepil=@nome
and cognomepil=@cognome
and P.anno=P2.anno
and P.classe=P2.classe
and P.puntip=P2.puntip
and P.n_vitt>P2.n_vitt)
and anno=any( select anno
from PART_PIL
where nomepil=@nome
and cognomepil=@cognome)
group by P.anno, P.classe)
union
(select anno, classe, 1 as posizione_finale
from ##TMP P
where nomepil=@nome
and cognomepil=@cognome
and not exists( select *
from ##TMP P1
where P.anno=P1.anno
and P.classe=P1.classe
and P.puntip<P1.puntip)
and not exists ( select *
from ##TMP P2
where P.anno=P2.anno
and P.classe=P2.classe
and P.puntip=P2.puntip
and P.n_vitt<P2.n_vitt)
and anno=any( select anno
from PART_PIL
where nomepil=@nome
and cognomepil=@cognome)
group by P.anno, P.classe)
order by anno, classe
drop table ##TMP
exec proccreatmp
/*OP 4e) mondiali vinti*/
100
select @cont125=count(*)
from ##TMP P
where nomepil=@nome
and cognomepil=@cognome
and classe=125
and not exists( select *
from ##TMP P1
where P.anno=P1.anno
and P.classe=P1.classe
and P.puntip<P1.puntip)
and not exists ( select *
from ##TMP P2
where P.anno=P2.anno
and P.classe=P2.classe
and P.puntip=P2.puntip
and P.n_vitt<P2.n_vitt)
and anno=any( select anno
from PART_PIL
where nomepil=@nome
and cognomepil=@cognome)
select @cont250=count(*)
from ##TMP P
where nomepil=@nome
and cognomepil=@cognome
and classe=250
and not exists( select *
from ##TMP P1
where P.anno=P1.anno
and P.classe=P1.classe
and P.puntip<P1.puntip)
and not exists ( select *
from ##TMP P2
where P.anno=P2.anno
and P.classe=P2.classe
and P.puntip=P2.puntip
and P.n_vitt<P2.n_vitt)
and anno=any( select anno
from PART_PIL
where nomepil=@nome
and cognomepil=@cognome)
select @cont500=count(*)
from ##TMP P
where nomepil=@nome
and cognomepil=@cognome
and classe=500
and not exists( select *
101
from ##TMP P1
where P.anno=P1.anno
and P.classe=P1.classe
and P.puntip<P1.puntip)
and not exists ( select *
from ##TMP P2
where P.anno=P2.anno
and P.classe=P2.classe
and P.puntip=P2.puntip
and P.n_vitt<P2.n_vitt)
and anno=any( select anno
from PART_PIL
where nomepil=@nome
and cognomepil=@cognome)
RAISERROR ('Il pilota %s %s ha vinto %d mondiali nella classe 125, %d nella classe 250, %d nella
classe 500.',16,1, @nome, @cognome, @cont125, @cont250, @cont500)
drop table ##TMP
go
• PROCvisualcircuito
Questa procedura implementa l’operazione N.5: visualizza i dati di un circuito il cui nome è passato
come parametro in ingresso. Vengono riportati anche i record stabiliti sul circuito relativamente alle
tre diverse classi.
if exists (select * from sysobjects
where name = ‘PROCvisualcircuito’
and type = ‘p’)
drop procedure PROCvisualcircuito
go
create procedure PROCvisualcircuito
@nome varchar(30)
as
select *
from CIRCUITO
where nomecircuito = @nome
print 'Visualizzazione del record del circuito nelle tre classi'
print ''
select *
from RECORD
where nomecircuito = @nome
and classe = 125
select *
from RECORD
where nomecircuito = @nome
and classe = 250
select *
102
from RECORD
where nomecircuito = @nome
and classe = 500
go
VISTE
Riportiamo di seguito il codice di sei viste che sono state utilizzate per l’ implementazione delle
operazioni.
Queste prime tre: VEWpil125, VEWpil250, VEWpil500, a seconda della cilindrata riportano i
seguenti dati: per ogni anno in cui un pilota ha gareggiato nome e cognome del pilota, nomesq, punti.
If exists
(
select table_name
From information_schema.views
Where table_name = ‘VEWpil125’
)
drop view VEWpil125
go
create view VEWpil125 (anno, cognome, nome, nomesq, punti)
as
select PP.anno, PP.cognomepil, PP.nomepil, PSP.nome_sq, PP.puntip
from PIL_SP PSP, PART_PIL PP
where PSP.classe = 125
and PP.anno = PSP.anno
and PP.cognomepil=PSP.cognomepil
and PP.nomepil=PSP.nomepil
UNION
select PP.anno, PP.cognomepil, PP.nomepil, PSU.nome_sq, PP.puntip
from PIL_SU PSU, PART_PIL PP
where PSU.classe = 125
and PP.anno=PSU.anno
and PP.cognomepil=PSU.cognomepil
and PP.nomepil=PSU.nomepil
UNION
select PP.anno, PP.cognomepil, PP.nomepil, PSS.nome_sq, PP.puntip
from PIL_SSU PSS, PART_PIL PP
where PSS.classe = 125
and PP.anno=PSS.anno
and PP.cognomepil=PSS.cognomepil
and PP.nomepil=PSS.nomepil
go
If exists
(
select table_name
103
From information_schema.views
Where table_name = ‘VEWpil250’
)
drop view VEWpil250
go
create view VEWpil250 (anno, cognome, nome, nomesq, punti)
as
select PP.anno, PP.cognomepil, PP.nomepil, PSP.nome_sq, PP.puntip
from PIL_SP PSP, PART_PIL PP
where PSP.classe = 250
and PP.anno=PSP.anno
and PP.cognomepil=PSP.cognomepil
and PP.nomepil=PSP.nomepil
UNION
select PP.anno, PP.cognomepil, PP.nomepil, PSU.nome_sq, PP.puntip
from PIL_SU PSU, PART_PIL PP
where PSU.classe = 250
and PP.anno=PSU.anno
and PP.cognomepil=PSU.cognomepil
and PP.nomepil=PSU.nomepil
union
select PP.anno, PP.cognomepil, PP.nomepil, PSS.nome_sq, PP.puntip
from PIL_SSU PSS, PART_PIL PP
where PSS.classe = 250
and PP.anno=PSS.anno
and PP.cognomepil=PSS.cognomepil
and PP.nomepil=PSS.nomepil
go
If exists
(
select table_name
From information_schema.views
Where table_name = ‘VEWpil500’
)
drop view VEWpil500
go
create view VEWpil500 (anno, cognome, nome, nomesq, punti)
as
select PP.anno, PP.cognomepil, PP.nomepil, PSP.nome_sq, PP.puntip
from PIL_SP PSP, PART_PIL PP
where PSP.classe = 500
and PP.anno=PSP.anno
and PP.cognomepil=PSP.cognomepil
and PP.nomepil=PSP.nomepil
104
UNION
select PP.anno, PP.cognomepil, PP.nomepil, PSU.nome_sq, PP.puntip
from PIL_SU PSU, PART_PIL PP
where PSU.classe = 500
and PP.anno=PSU.anno
and PP.cognomepil=PSU.cognomepil
and PP.nomepil=PSU.nomepil
UNION
select PP.anno, PP.cognomepil, PP.nomepil, PSS.nome_sq, PP.puntip
from PIL_SSU PSS, PART_PIL PP
where PSS.classe = 500
and PP.anno=PSS.anno
and PP.cognomepil=PSS.cognomepil
and PP.nomepil=PSS.nomepil
go
Queste ultime tre: VEWsq125, VEWsq250, VEWsq500 a seconda della cilindrata forniscono per ogni
anno in cui una squadra ha partecipato al campionato del mondo: il nome della squadra appunto e la
casa cotruttrice che le forniva le moto
If exists
(
select table_name
From information_schema.views
Where table_name = ‘VEWsq125’
)
drop view VEWsq125
go
create view VEWsq125 (anno, nomesq, casacostr)
as
select SPIC.anno, SPIC.nome_sq, PR.casa_costr
from SP_IN_CLASSE SPIC, PRODUZIONE PR
where SPIC.classe=125
and SPIC.sigla=PR.sigla
UNION select SSC.anno, SSC.nome_sq, PR.casa_costr
from SSU_IN_CLASSE SSC, PRODUZIONE PR
where SSC.classe=125
and SSC.sigla=PR.sigla
UNION
select SUIC.anno, SUIC.nome_sq, PR.casa_costr
from SU_IN_CLASSE SUIC, PRODUZIONE PR
where SUIC.classe=125
and SUIC.sigla=PR.sigla
go
If exists
(
select table_name
105
From information_schema.views
Where table_name = ‘VEWsq250’
)
drop view VEWsq250
go
create view VEWsq250 (anno, nomesq, casacostr)
as
select SPIC.anno, SPIC.nome_sq, PR.casa_costr
from SP_IN_CLASSE SPIC, PRODUZIONE PR
where SPIC.classe=250
and SPIC.sigla=PR.sigla
UNION
select SSC.anno, SSC.nome_sq, PR.casa_costr
from SSU_IN_CLASSE SSC, PRODUZIONE PR
where SSC.classe=250
and SSC.sigla=PR.sigla
UNION
select SUIC.anno, SUIC.nome_sq, PR.casa_costr
from SU_IN_CLASSE SUIC, PRODUZIONE PR
where SUIC.classe=250
and SUIC.sigla=PR.sigla
go
If exists
(
select table_name
From information_schema.views
Where table_name = ‘VEWsq500’
)
drop view VEWsq500
go
create view VEWsq500 (anno, nomesq, casacostr)
as
select SPIC.anno, SPIC.nome_sq, PR.casa_costr
from SP_IN_CLASSE SPIC, PRODUZIONE PR
where SPIC.classe=500
and SPIC.sigla=PR.sigla
UNION
select SSC.anno, SSC.nome_sq, PR.casa_costr
from SSU_IN_CLASSE SSC, PRODUZIONE PR
where SSC.classe=500
and SSC.sigla=PR.sigla
UNION
select SUIC.anno, SUIC.nome_sq, PR.casa_costr
from SU_IN_CLASSE SUIC, PRODUZIONE PR
where SUIC.classe=500
106
and SUIC.sigla=PR.sigla
go
CONNESSIONE ODBC
Per la comunicazione tra la base di dati in SQL SERVER 7.0 ed il front-end realizzato in VISUAL
BASIC sfruttando la libreria degli oggetti DAO, e’ necessario utilizzare uno strumento che consenta di
tradurre il linguaggio di un sistema nell’altro. Nel nostro caso la scelta e’caduta su ODBC acronimo di
Open Data Base Connectivity che e’uno standard di interconnessione fra sistemi.
La base di dati in SQL SERVER rappresenta la fonte dati ODBC; VISUAL BASIC
comunichera’direttamente con ODBC e quindi indirettamente con la base di dati.
Per settare l’interfacciamento tra i due componenti e’necessaria l’impostazione dal pannello di
controllo della fonte dati ODBC.
INTERFACCIA GRAFICA IN AMBIENTE VISUAL BASIC
Si intendono illustrare brevemente le metodologie di accesso ai dati utilizzate per permettere agli
utenti di dialogare tramite l’ interfaccia con la base di dati.
Sono stati utilizzati per implementare l’ interfaccia gli oggetti della libreria DAO (Data Acces Object)
e mediante ODBC e’ stato possibile accedere a fonti di dati remoti in modo totalmente trasparente.
DAO e’ una gerarchia di oggetti e collezioni che ci fornisce un’ interfaccia ad oggetti finalizzata all’
accesso ai dati.
Ogni oggetto dispone di proprieta’ che ne caratterizzano lo stato e di metodi che eseguono le azioni
sull’oggetto stesso.
L’oggetto padre DBEngine contiene il motore Jet Database Engine.
Il DBEngine dispone di una collezione di workspace, cioe’ aree di lavoro nelle quali si possono creare
e aprire database.
La filosofia DAO e’ che ogni oggetto possiede una collezione di oggetti che nella gerarchia si trovano
ad un livello inferiore. Per esempio l’oggetto Database dispone di una collezione di Recordset che sua
volta ha al suo interno una collezione di oggetti Field. Per convenzione le collezioni di oggetti hanno lo
stesso nome dell’ oggetto al plurale(es: Recordset,Recordsets).
Gli oggetti fondamentali della gerarchia sono :
DATABASE, incapsula un database, locale o remoto che sia.
RECORDSET, e’una vista logica specificata da una tabella di selezione SQL. I metodi di un oggetto
recordset
permettono svariate operazioni sui dati ( inserimenti, ricerche, aggiornamenti etc. ). Un
recordset
equivale a cio’che nel corso di BASI DI DATI viene chiamato CURSORE.
107
TABLEDEF, e’l’oggetto che racchiude informazioni sulla struttura delle tabelle.
RELATION, tiene traccia delle relazioni logiche che intercorrono tra le entita’del database.
INDEX, consente di associare indici alle tabelle per migliorare le prestazioni in fase di retrieval.
QUERYDEF, permette di definire e memorizzare query SQL utili alle applicazioni per interagire con
il database.
La strutturazione di un sistema basato su DAO consente un’interfaccia uniforme per accessi locali e
remoti. Ne schematizziamo un modello di tipo CLIENT-SERVER.
CLIENT
APPLICAZIONE
DAO
JET
ODBC
SERVER
SQL Server 7.0
DB
108
GESTIONE DELLA SICUREZZA
Per garantire la sicurezza della base di dati, si è prevista l’esistenza di tre diverse tipologie di utente
che possono accedere alle informazioni memorizzate con differenti diritti:
• Utente è l’utente generico, che accede alla base di dati per ottenere informazioni di vario genere sul
campionato dell’anno in corso o di anni precedenti. Pertanto ha il diritto di effettuare qualunque
tipo di selezione e di eseguire le procedure che implementano operazioni di visualizzazione dei
dati (classifiche, carriera di un pilota, etc…)
• Manutentore è colui che si occupa di mantenere aggiornata la base di dati, ossia di inserire gli esiti
delle varie gare del campionato in corso e tutte le nuove partecipazioni di squadre, piloti, etc…
all’inizio di ogni nuovo campionato. Pertanto ha il diritto di eseguire tutte le procedure di
inserimento (non può effettuare direttamente l’istruzione INSERT ma deve usare le procedure
predisposte) e le cancellazioni (ha diritto di usare l’istruzione DELETE su quasi tutte le tabelle,
per le altre sono previste procedure di cancellazione oppure esistono appositi trigger per
mantenere coerenti le informazioni)
• Db_owner è un utente che deve possedere una conoscenza completa della struttura della base di
dati. Ha infatti gli stessi diritti del db_creator, ossia può effettuare qualsiasi tipo di inserimento,
cancellazione, aggiornamento e selezione sulle tabelle.
Ad ogni tipologia di utente viene assegnata una nuova login, ossia uno username e una password, che
lo identificheranno al momento dell’accesso alle informazioni. All’atto della creazione di questa login,
viene selezionato o creato un profilo utente (ROLE) da assegnare alla tipologia di utente in questione;
proprio definendo le caratteristiche di questo ROLE il db_creator può indicare quali sono le
operazioni che l’utente è autorizzato a compiere.
All’avvio dell’applicazione si presenta la seguente maschera di login in cui si devono inserire username
e password differenti per le tipologie di utente previste :
utente
manutentore
Username: utente
Username: manutentore
Password: utente
Password: manutentore
109
Nel riconoscimento dell’utente all’interno del codice della maschera di login abbiamo appositamente
trascurato il fatto che facendo un confronto diretto tra i valori immessi per loggarsi e quelli di
username e password degli utenti abilitati, si lascia una traccia nel client che permetterebbe ad un
intruso che riescisse a violare il codice del client di accedere al server sfruttando le informazioni
messegli a disposizione da questo confronto. La cosa migliore sarebbe cercare di mettere tutte le
informazioni relative a username e password all’interno del server ed accedervi ad esempio mediante
stored procedure. Abbiamo preferito non appesantire il nostro elaborato oltre quanto era richiesto.
Ai due tipi di utente si presentano due maschere differenti:
Maschera opzione1 per l’utente generico e opzione2 per il manutentore in cui compaiono le
operazioni consentite: sono state implementate solo alcune delle operazioni a cui l’utente ha diritto
110
.
CODICE DI ALCUNE DELLE MASCHERE IMPLEMENTATE
- Maschera di login con controllo dello username e delle password
Private Sub inizia_Click()
If Text1.Text = "utente" Then
If Text2.Text = "utente" Then
opzione1.Show vbModal
Else
MsgBox "password non valida", vbInformation, "Messaggio di informazione"
Text2.Text = ""
End If
Else
If Text1.Text = "manutentore" Then
If Text2.Text = "manutentore" Then
opzione2.Show vbModal
Else
MsgBox "password non valida", vbInformation, "Messaggio di informazione"
Text2.Text = ""
End If
Else
MsgBox "username non valido", vbInformation, "Messaggio di informazione"
Text1.Text = ""
End If
End If
End Sub
111
Maschera per l’inserimento dei dati di una gara (implementa l’operazione 1)
Public work As Workspace
Public db As Database
Const stringadiconnessione = "ODBC;DSN=connettimanutentore;UID=DUE;PWD=pippo2000;"
Private Sub boxanno_Change()
Combocircuito.Clear
If Len(boxanno.Text) = 4 Then
Dim istr As String
Dim rs As Recordset
istr = "select nomecircuito from LOCAZIONE where anno= " & boxanno.Text & ""
Set rs = db.OpenRecordset(istr, dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
Combocircuito.AddItem rs!nomecircuito
rs.MoveNext
Loop
End If
End Sub
Private Sub command_Click()
Unload insgara
End Sub
Private Sub Form_Load()
Dim istr As String
On Error GoTo errore
'connessione OBDC
Set work = DBEngine.CreateWorkspace("odbcwork", "DUE", "", dbUseODBC)
DBEngine.DefaultType = dbUseODBC
Set db = Workspaces(0).OpenDatabase("connection", dbDriverNoPrompt, False,
stringadiconnessione)
‘L`inserimento deve iniziare dal primo classificato
112
classificato.pos = 1
Comboclasse.AddItem "125"
Comboclasse.AddItem "250"
Comboclasse.AddItem "500"
Exit Sub
errore: 'errore di connessione
If Err.Number = 3146 Then
MsgBox Errors(0).Description, vbExclamation, "Messaggio di sistema"
End If
Unload insgara
End Sub
Private Sub inserisci_Click()
If ((Len(boxanno.Text) <> 4) Or (boxanno.Text < 1980)) Then
MsgBox "Anno inserito non valido", vbInformation, "Messaggio di informazione"
Else
If class.Value Then
'nascondo i campi relativi all'inserimento di un pilota non classificato e visualizzo l'intestazione
‘relativa all'inserimento dei classificati
classificato.titoloc1.Visible = True
classificato.titoloc2.Visible = True
classificato.boxpos.Visible = True
classificato.titolonon.Visible = False
classificato.boxcausarit.Visible = False
classificato.causarit.Visible = False
classificato.boxmotivosq.Visible = False
classificato.motivosq.Visible = False
classificato.aggiunta.Visible = False
classificato.tempo.Caption = "tempo"
classificato.Show vbModal
Else
If nonclass.Value Then
‘visualizzo i campi relativi all'inserimento di un pilota non classificato e nascondo l'intestazione
‘ relativa all'inserimento dei classificati
classificato.titoloc1.Visible = False
classificato.titoloc2.Visible = False
classificato.boxpos.Visible = False
classificato.titolonon.Visible = True
classificato.boxcausarit.Visible = True
classificato.causarit.Visible = True
classificato.boxmotivosq.Visible = True
classificato.motivosq.Visible = True
classificato.aggiunta.Visible = True
classificato.tempo.Caption = "ultimo giro"
113
classificato.Show vbModal
Else
MsgBox "Selezionare un pulsante di opzione", vbInformation, "Messaggio di informazione"
End If
End If
End If
End Sub
- Maschera per l’inserimento dei piloti classificati, non classificati e delle penalita’ loro
inflitte
Public pos As Integer
Public work As Workspace
Public db As Database
Const stringadiconnessione = "ODBC;DSN=connettimanutentore;UID=DUE;PWD=pippo2000;"
Private Sub Form_Load()
'connessione OBDC
Set work = DBEngine.CreateWorkspace("odbcwork", "DUE", "", dbUseODBC)
DBEngine.DefaultType = dbUseODBC
Set db = Workspaces(0).OpenDatabase("connection", dbDriverNoPrompt, False,
stringadiconnessione)
boxpos.Text = pos
End Sub
Private Sub Inspenalita_Click()
Dim proc As String 'stringa che contiene l 'istruzione sql
Dim rs As Recordset
On Error GoTo errore
proc = "exec prinspenalita '" & boxnome.Text & "','" & boxcognome.Text & "','" &
insgara.Combocircuito.Text & "'," & insgara.boxanno.Text & "," & insgara.Comboclasse.Text & ",'" &
boxmotivo.Text & "'," & boxngiro.Text & ",'" & boxtipo.Text & "'"
Set rs = db.OpenRecordset(proc, dbOpenDynaset)
MsgBox "Inserimento avvenuto correttamente", vbInformation, "Messaggio di informazione"
boxmotivo.Text = ""
boxtipo.Text = ""
boxngiro.Text = ""
114
Exit Sub
errore:
If Err.Number = 3146 Then
MsgBox Errors(0).Description, vbExclamation, "Messaggio di sistema"
End If
End Sub
Private Sub Inspilota_Click()
Dim variabile As String
Dim proc As String 'stringa che contiene l 'istruzione sql
Dim rs As Recordset
On Error GoTo errore
If insgara.Class.Value Then
If boxtempo.Text = "" Then
variabile = "null"
Else
variabile = "'" & boxtempo.Text & "'"
End If
proc = "exec prinsclassif '" & boxnome.Text & "', '" & boxcognome.Text & "', " &
insgara.Comboclasse.Text & ", " & insgara.boxanno.Text & ", '" & insgara.Combocircuito.Text & "', "
& boxpos_iniz.Text & ", " & boxpos.Text & ", " & variabile
Set rs = db.OpenRecordset(proc, dbOpenDynaset)
MsgBox "Inserimento avvenuto correttamente", vbInformation, "Messaggio di informazione"
pos = pos + 1
End If
If insgara.nonclass.Value Then
If boxmotivosq.Text = "" Then
variabile = "null"
Else
variabile = "'" & boxmotivosq.Text & "'"
End If
proc = "exec prinsnonclassif '" & boxnome.Text & "', '" & boxcognome.Text & "', " &
insgara.Comboclasse.Text & ", " & insgara.boxanno.Text & ", '" & insgara.Combocircuito.Text & "', '"
& boxcausarit.Text & "'," & boxtempo.Text & ", " & variabile & "," & boxpos_iniz.Text
Set rs = db.OpenRecordset(proc, dbOpenDynaset)
MsgBox "Inserimento avvenuto correttamente", vbInformation, "Messaggio di informazione"
End If
‘Disabilito i campi relativi ai dati del pilota inserito per permettere l'inserimento di eventuali
‘penalita'
115
boxnome.Enabled = False
boxcognome.Enabled = False
boxpos_iniz.Enabled = False
boxtempo.Enabled = False
boxcausarit.Enabled = False
boxmotivosq.Enabled = False
Exit Sub
errore:
If Err.Number = 3146 Then
MsgBox Errors(0).Description, vbExclamation, "Messaggio di sistema"
End If
End Sub
Private Sub Inssuccessivo_Click()
'resetto e riabilito i campi relativi ai dati del pilota
boxnome.Text = ""
boxcognome.Text = ""
boxpos_iniz.Text = ""
boxtempo.Text = ""
boxcausarit.Text = ""
boxmotivosq.Text = ""
boxnome.Enabled = True
boxcognome.Enabled = True
boxpos_iniz.Enabled = True
boxtempo.Enabled = True
boxcausarit.Enabled = True
boxmotivosq.Enabled = True
boxpos.Text = pos
End Sub
Private Sub Menuprec_Click()
'resetto e riabilito i campi relativi ai dati del pilota
boxnome.Text = ""
boxcognome.Text = ""
boxpos_iniz.Text = ""
boxtempo.Text = ""
boxcausarit.Text = ""
boxmotivosq.Text = ""
boxnome.Enabled = True
116
boxcognome.Enabled = True
boxpos_iniz.Enabled = True
boxtempo.Enabled = True
boxcausarit.Enabled = True
boxmotivosq.Enabled = True
‘Torno al menu’ precedente
classificato.Hide
End Sub
- Maschera per la visualizzazione delle classifiche (implementa l’operazione 2)
Public work As Workspace
Public db As Database
Public rs As Recordset
Public proc As String
Const stringadiconnessione = "ODBC;DSN=connettiutente;UID=UNO;PWD=pippo2000;"
Private Sub classcc_Click()
If ((Len(boxannoc.Text) <> 4) Or (boxannoc.Text < 1980)) Then
MsgBox "Anno inserito non valido", vbInformation, "Messaggio di informazione"
Else
List1.Visible = False
List2.Visible = True
List2.Clear
List3.Visible = True
List3.Clear
Label1.Caption = ""
Label2.Caption = "casa costruttrice"
Label3.Caption = "punti"
Label4.Caption = ""
Label5.Caption = ""
On Error GoTo errore
proc = "exec procclassificacc " & boxannoc.Text & "," & comboclassec.Text & ""
Set rs = db.OpenRecordset(proc, dbOpenDynaset)
Do While Not rs.EOF
List2.AddItem rs!casacostr
List3.AddItem rs!punti
rs.MoveNext
117
Loop
End If
Exit Sub
errore:
If Err.Number = 3146 Then
MsgBox Errors(0).Description, vbExclamation, "Messaggio di sistema"
End If
End Sub
Private Sub classparz_Click()
Dim firsttime As Boolean
Dim nome As String
Dim cognome As String
Dim spazio1 As Integer
Dim spazio2 As Integer
Dim pilota As String
Dim circuito As String
Dim i As Integer
Dim punti As Integer
Dim puntitot As Integer
If ((Len(boxannoc.Text) <> 4) Or (boxannoc.Text < 1980)) Then
MsgBox "Anno inserito non valido", vbInformation, "Messaggio di informazione"
Else
List1.Visible = True
List1.Clear
List2.Visible = False
List3.Visible = False
Label1.Caption = "pilota"
Label2.Caption = "circuito"
Label3.Caption = "punti"
Label4.Caption = "punti"
Label5.Caption = "totali"
On Error GoTo errore
proc = "exec procclassparz " & boxannoc.Text & "," & comboclassec.Text & ""
Set rs = db.OpenRecordset(proc, dbOpenDynaset)
If Not rs.EOF Then
nome = rs!nomepil
cognome = rs!cognomepil
firsttime = True
118
End If
Do While Not rs.EOF
If ((firsttime <> True) And ((nome = rs!nomepil) And (cognome = rs!cognomepil))) Then
circuito = rs!nomecircuito
spazio2 = 25 - Len(circuito)
For i = 1 To spazio2
circuito = circuito & " "
Next
pilota = ""
For i = 1 To 25
pilota = pilota & " "
Next
punti = rs!punti
If punti < 10 Then
List1.AddItem pilota & " " & circuito & " " & punti
Else
List1.AddItem pilota & " " & circuito & " " & punti
End If
Else
pilota = rs!nomepil & " " & rs!cognomepil
spazio1 = 25 - Len(pilota)
For i = 1 To spazio1
pilota = pilota & " "
Next
circuito = rs!nomecircuito
spazio2 = 25 - Len(circuito)
For i = 1 To spazio2
circuito = circuito & " "
Next
puntitot = rs!puntip
punti = rs!punti
If punti < 10 Then
List1.AddItem pilota & " " & circuito & " " & punti & " " & puntitot
Else
List1.AddItem pilota & " " & circuito & " " & punti & " " & puntitot
End If
firsttime = False
End If
nome = rs!nomepil
cognome = rs!cognomepil
rs.MoveNext
Loop
End If
Exit Sub
errore:
119
If Err.Number = 3146 Then
MsgBox Errors(0).Description, vbExclamation, "Messaggio di sistema"
End If
End Sub
Private Sub classpil_Click()
If ((Len(boxannoc.Text) <> 4) Or (boxannoc.Text < 1980)) Then
MsgBox "Anno inserito non valido", vbInformation, "Messaggio di informazione"
Else
List1.Visible = False
List2.Visible = True
List2.Clear
List3.Visible = True
List3.Clear
Label1.Caption = ""
Label2.Caption = "pilota"
Label3.Caption = "punti"
Label4.Caption = ""
Label5.Caption = ""
On Error GoTo errore
proc = "exec procclasspil " & boxannoc.Text & "," & comboclassec.Text & ""
Set rs = db.OpenRecordset(proc, dbOpenDynaset)
Do While Not rs.EOF
List2.AddItem rs!nomepil & " " & rs!cognomepil
List3.AddItem rs!puntip
rs.MoveNext
Loop
End If
Exit Sub
errore:
If Err.Number = 3146 Then
MsgBox Errors(0).Description, vbExclamation, "Messaggio di sistema"
End If
End Sub
120
Private Sub command_Click()
Unload Visualclassifica
End Sub
Private Sub Form_Load()
List1.Visible = False
List2.Visible = False
List3.Visible = False
Label1.Caption = ""
Label2.Caption = ""
Label3.Caption = ""
Label4.Caption = ""
Label5.Caption = ""
On Error GoTo errore
'connessione OBDC
Set work = DBEngine.CreateWorkspace("odbcwork", "UNO", "", dbUseODBC)
DBEngine.DefaultType = dbUseODBC
Set db = Workspaces(0).OpenDatabase("connection", dbDriverNoPrompt, False,
stringadiconnessione)
comboclassec.AddItem "125"
comboclassec.AddItem "250"
comboclassec.AddItem "500"
Exit Sub
errore: 'errore di connessione
If Err.Number = 3146 Then
MsgBox Errors(0).Description, vbExclamation, "Messaggio di sistema"
End If
Unload Visualclassifica
End Sub
Visualizzazione dati di un circuito
Public work As Workspace
Public db As Database
Const stringadiconnessione = "ODBC;DSN=connettiutente;UID=UNO;PWD=pippo2000;"
Private Sub Command1_Click()
Unload visualcirc
121
End Sub
Private Sub Form_Load()
Label6.Caption = ""
Label7.Caption = ""
Label8.Caption = ""
Label9.Caption = ""
Label2.Caption = ""
Label11.Caption = ""
Label13.Caption = ""
Label14.Caption = ""
Label15.Caption = ""
On Error GoTo errore
'connessione ODBC
Set work = DBEngine.CreateWorkspace("odbcwork", "UNO", "", dbUseODBC)
DBEngine.DefaultType = dbUseODBC
Set db = Workspaces(0).OpenDatabase("connection", dbDriverNoPrompt, False,
stringadiconnessione)
Dim rs As Recordset
Dim istr As String
istr = "SELECT nomecircuito FROM circuito"
Set rs = db.OpenRecordset(istr, dbOpenDynaset)
Do While Not rs.EOF
nome.AddItem rs!nomecircuito
rs.MoveNext
Loop
Exit Sub
errore:
If Err.Number = 3146 Then
MsgBox Errors(0).Description, vbExclamation, "Messaggio di sistema"
End If
Unload visualcirc
End Sub
Private Sub visualizza_Click()
Dim tempo As String
Dim rs As Recordset
Dim rz As Recordset
Dim rv As Recordset
Set rs = db.OpenRecordset("SELECT * FROM circuito WHERE nomecircuito = '" & nome.Text &
"'", dbOpenDynaset)
Set rz = db.OpenRecordset("SELECT * FROM record WHERE nomecircuito = '" & nome.Text & "'",
dbOpenDynaset)
Set rv = db.OpenRecordset("SELECT giorno,mese,anno FROM locazione WHERE nomecircuito = '"
& nome.Text & "'", dbOpenDynaset)
localita.Text = ""
lunghezza.Text = ""
nazione.Text = ""
122
'rendo visibili le label e le caselle di testo in cui compariranno i risultati
gare.Visible = True
gare.Clear
record.Visible = True
record.Clear
Label6.Caption = "giorno"
Label7.Caption = "mese"
Label8.Caption = "anno"
Label9.Caption = "GARE DISPUTATE"
Label2.Caption = "classe"
Label11.Caption = " pilota"
Label13.Caption = "anno"
Label14.Caption = "casa costruttrice"
Label15.Caption = "tempo"
If Not rs.EOF Then
localita.Text = rs!localita
nazione.Text = rs!siglanazione
If Not IsNull(rs!lunghezza) Then
lunghezza.Text = rs!lunghezza
End If
Else
MsgBox "il circuito non e'presente nel db ", vbInformation, "messaggio di informazione"
End If
If Not rv.EOF Then
Do While Not rv.EOF
gare.AddItem rv!giorno & "
" & rv!mese & "
rv.MoveNext
Loop
Else
gare.AddItem "non si sono disputate gare"
End If
rv.Close
Do While Not rz.EOF
If rz!tempo = "9:99:999" Then
tempo = "non effettuato"
Else
tempo = rz!tempo
End If
record.AddItem rz!classe & " " & tempo & "
rz!casa_costr & " " & rz!anno
rz.MoveNext
Loop
rz.Close
" & rv!anno
" & rz!cognomepil & " " & rz!nomepil & " " &
End Sub
Indice
Introduzione
1
123
Specifiche
Operazioni
Volume dei dati
Progettazione concettuale
Schema E/R completo
Traduzione dello schema E/R in schema relazionale
Dato derivato
Implementazione delle tabelle
Triggers
Dolete triggers
Stored procedures
Implementazione delle operazioni
Viste
Connessione ODBC
Interfaccia grafica
Gestione della sicurezza
Maschere
Codice maschere
Bibliografia
Bibliografia
•
Moto Sprint
• Progetto di Basi di Dati Relazionali (Beneventano, Bergamaschi, Vincini)
• SQL Server 7 di Anthoni T.Mann
• www.dorna.com società organizzatrice del mondiale
• Visual Basic e la programmazione dei database (Naccarato)
124
1
2
3
3
4
15
19
22
31
48
62
87
97
101
101
103
103
105
115