Decomposizione Schemi e Trigger

Università di Modena e Reggio Emilia 1

DB

Gro

up @

uni

mo

Decomposizione Schemi Decomposizione Schemi e Triggere Trigger


DB

Gro

up @

uni

mo

Tabella RTabella R

Squadra Giocatore Ruolo

Italia Bergomi TerzinoDestro

Italia Bergomi Libero

Italia Moriero AlaDestra

Brasile Ronaldo Centravanti

Argentina Simeone Centrocampista

Dipendenze funzionali: F = {SR G, G S}

La tupla (Argentina, Zanetti, Centrocampista) non è ammessa perché viola la dipendenza SR G.


DB

Gro

up @

uni

mo

Decomposizione che non preserva i datiDecomposizione che non preserva i dati

Squadra Ruolo

Italia TerzinoDestro

Italia Libero

Italia AlaDestra

Brasile Centroavanti

Argentina Centrocampista

Giocatore Squadra

Bergomi Italia

Moriero Italia

Ronaldo Brasile

Simeone Argentina

πSR(R)

πGS(R)

Giocatore Squadra Ruolo

Bergomi Italia TerzinoDestro

Moriero Italia TerzinoDestro

Bergomi Italia Libero

Moriero Italia Libero

Bergomi Italia AlaDestra

Moriero Italia AlaDestra

Ronaldo Brasile Centroavanti

SimeoneArgentina

Centrocampista

πGS(R) join πSR(R)

Questa decomposizione non preserva le dipendenze funzionali.

E’ possibile inserire nelle tabelle le due tuple: (Argentina, Centrocampista) e (Zanetti, Argentina)


DB

Gro

up @

uni

mo

Decomposizione che preserva i datiDecomposizione che preserva i dati

Giocatore Ruolo

Bergomi TerzinoDestro

Bergomi Libero

Moriero AlaDestra

Ronaldo Centroavanti

Simeone Centrocampista

Giocatore Squadra

Bergomi Italia

Moriero Italia

Ronaldo Brasile

Simeone Argentina

πGR(R)

πGS(R)

πGS(R) join πSR(R)

Giocatore Ruolo Squadra

Bergomi Libero Italia

Bergomi TerzinoDestro Italia

Moriero AlaDestra Italia

Ronaldo Centroavanti Brasile

SimeoneCentrocampista

Argentina

Questa decomposizione preserva i dati.


DB

Gro

up @

uni

moTrigger su RTrigger su R

Per conservare le dipendenze funzionali sulla tabella R prima di tutto dichiariamo una chiave composta da tutti i campi. Primary key(squadra, giocatore, ruolo)

Per conservare le dipendenze funzionali serve un trigger sulla tabella R che controlli gli inserimenti e gli update sulle tuple della tabella R.


DB

Gro

up @

uni

mo

Trigger su R (cont.)Trigger su R (cont.)CREATE TRIGGER Controllo_DF_R

ON R

FOR INSERT, UPDATE

AS

--- Dichiaro delle variabili di comodo

Declare @s nvarchar(50),

@g nvarchar(50),

@is nvarchar(50),

@cont int

-- controllo S,R --> G

-- recupero i valori inseriti facendo uso della tabella temporanea "inserted"

Select @cont = count(*)

from R, inserted

where R.squadra = inserted.squadra

and R.ruolo = inserted.ruolo

and R.giocatore <> inserted.giocatore

if @cont > 0

begin

raiserror('Il ruolo che si vuole inserire per la squadra scelta ha già un giocatore.',16,1)

rollback transaction

end


DB

Gro

up @

uni

moTrigger su RTrigger su R

-- controllo G --> S

Select @g = R.giocatore,

@s = R.squadra,

@is = inserted.squadra

from R, inserted

where R.giocatore = inserted.giocatore

and R.squadra <> inserted.squadra

and R.ruolo <> inserted.ruolo

if (@g <> '') and (@s <> @is)

begin

raiserror('Il giocatore che si vuole inserire può appartenere a una sola squadra.',16,1)


end

go


DB

Gro

up @

uni

mo

Trigger su GRTrigger su GRSulla tabella GR viene definita la chiave PRIMARY KEY(giocatore, ruolo)

e viene definito il seguente trigger per preservare le dipendenze.Trigger per il controllo degli update.

CREATE TRIGGER Controllo_DF_GR_UP

ON GR

FOR UPDATE

AS


Declare @contG int,

@cont int

-- recupero i valori inseriti facendo uso della tabella temporanea "inserted"

Select @contG = count(*)

from GS,inserted

where GS.giocatore = inserted.giocatore


DB

Gro

up @

uni

mo

Trigger su GRTrigger su GR-- recupero i valori inseriti facendo uso della tabella temporanea "inserted"Select @cont = count(*)from GR, GS,insertedwhere GR.giocatore = GS.giocatoreand GR.ruolo = inserted.ruoloand GR.giocatore <> inserted.giocatore

if @contG = 0begin

raiserror('Il giocatore non appartiene a nessuna squadra!',16,1)rollback transaction

endelse if @cont > 0begin

raiserror('Il ruolo che si vuole inserire ha già un giocatore per quella squadra!',16,1)rollback transaction

endgo


DB

Gro

up @

uni

mo

Trigger su GRTrigger su GRCreo un trigger per controllare gli inserimenti.

CREATE TRIGGER Controllo_DF_GRON GRFOR INSERTAS

--- Dichiaro delle variabili di comodoDeclare @contG int,

@cont int

-- recupero i valori inseriti facendo uso della tabella temporanea "inserted"Select @contG = count(*)from GS,insertedwhere GS.giocatore = inserted.giocatore


DB

Gro

up @

uni

mo

Trigger su GRTrigger su GR-- recupero i valori inseriti facendo uso della tabella temporanea "inserted"Select @cont = count(GR.ruolo)from GR, GS,insertedwhere GR.giocatore = GS.giocatoreand GR.ruolo = inserted.ruoloand GS.giocatore <> inserted.giocatoreand GS.squadra = (select squadra from GS, inserted where GS.giocatore = inserted.giocatore)group by GR.ruolo

if @contG = 0begin

raiserror('Il giocatore non appartiene a nessuna squadra!',16,1)rollback transaction

endelse if @cont > 0begin

raiserror('Il ruolo che si vuole inserire ha già un giocatore per quella squadra!',16,1)

rollback transactionendgo


DB

Gro

up @

uni

mo

Trigger su GSTrigger su GSSulla tabella GS viene definita una chiave solo sul giocatore. In questo modo è conservata la dipendenza funzionale G S.Primary key(giocatore)

Scrivo un trigger per controllare le modifiche fatte alle tuple presenti in GS. In particolare controllo che nel caso si cambi la squadra di un giocatore, esso non abbia ruoli già presenti per la squadra modificata.

CREATE TRIGGER Controllo_DF_GS

ON GS

FOR UPDATE

AS


Declare @cont int


DB

Gro

up @

uni

mo

Trigger su GSTrigger su GS-- recupero i valori inseriti facendo uso della tabella temporanea "inserted"

Select @cont = count(GR.ruolo)

from GR, GS,inserted

where GR.giocatore = GS.giocatore

and GR.ruolo in (select GR.ruolo from GR, inserted where GR.giocatore = inserted.giocatore)

and GS.giocatore <> inserted.giocatore

and GS.squadra = (select GS.squadra from GS, inserted where GS.giocatore = inserted.giocatore)

if @cont > 0

begin

raiserror('Non è possibile modificare la squadra del giocatore!',16,1)


end

go

Decomposizione Schemi e Trigger

Documents

Transcript of Decomposizione Schemi e Trigger