9

Click here to load reader

Bdd-Correction Serie TD 3

Embed Size (px)

Citation preview

Page 1: Bdd-Correction Serie TD 3

Correction de la série de TD N° 3

Exercice 1 1.a. Contrainte de domaine (CDM) Il ya trois possibilités pour définir cette contrainte dans la commande de création de la table NAGEUR:

1) On définit l’attribut QUALITE comme suit : QUALITE VARCHAR(10) NOT NULL CHECK QUALITE IN (‘excellente’, ‘bonne’, ‘médiocre’)

2) On créer tout d’abord un domaine appelé D_QUALITE comme suit : CREATE DOMAINE D_QUALITE AS VARCHAR(10) CHECK D_QUALITE IN (‘excellente’, ‘bonne’, ‘médiocre’); puis on définit l’attribut QUALITE comme suit : QUALITE D_QUALITE NOT NULL

3) On définit l’attribut QUALITE comme suit : QUALITE VARCHAR(10) NOT NULL, puis on ajoute à la fin de la commande de création de la table NAGEUR la contrainte explicite (nommée) suivante :

• CONSTRAINT CONT_QUAL_NAG CHECK QUALITE IN (‘excellente’, ‘bonne’, ‘médiocre’),

ou tout simplement la contrainte implicite (sans nom) : • CHECK QUALITE IN (‘excellente’, ‘bonne’, ‘médiocre’)

1.b. Contraintes référentielles (CRF) Il y a deux possibilités pour définir les deux contraintes référentielles dans la commande de création de la

table BAIGNADE : 1) On définit les attributs NN et NP comme suit :

NN INT NOT NULL REFERENCES NAGEUR(N), NP INT NOT NULL REFERENCES PLAGE(NP)

2) On définit les attributs NN et NP comme suit : NN INT NOT NULL, NP INT NOT NULL puis on ajoute à la fin de la commande de création de la BAIGNADE les contraintes explicites suivantes :

• CONSTRAINTE CONT_REF_NAG FOREIGN KEY NN REFERENCES NAGEUR ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINTE CONT_REF_PLA FOREIGN KEY NP REFERENCES PLAGE ON DELETE CASCADE ON UPDATE CASCADE ou tout simplement les contraintes implicites (sans nom) :

• FOREIGN KEY NN REFERENCES NAGEUR(NN) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY NP REFERENCES PLAGE(NP) ON DELETE CASCADE ON UPDATE CASCADE

1.c. Contrainte de dépendance fonctionnelle (CDF)

CREATE ASSERTION DF_TYPE_REGION_POLLUTION BEFOR COMMIT CHECK ( NOT EXISTS ( SELECT * FROM PLAGE GROUP BY TYPE, REGION HAVING

COUNT( DISTINCT POLLUTION) >1)) FOR PLAGE;

1.d. Contrainte avec agrégat (CA) CREATE ASSERTION CA_DUREE BEFOR COMMIT CHECK (SELECT SUM(DUREE) FROM BAIGNADE GROUP BY NN, DATE ) <=120 FOR BAIGNADE;

Page 2: Bdd-Correction Serie TD 3

2. Contraintes vérifiées après chaque opération de mise à jour Contrainte INSERT UPDATE DELETE A Oui: si l’opération INSERT est

appliquée sur la table NAGEUR Oui: l’opération UPDATE est appliquée sur la table NAGEUR

Non

B Oui si l’opération INSERT est appliquée sur la table BAIGNADE

Oui : si l’opération UPDATE est appliquée sur la table BAIGNADE, NAGEUR ou PLAGE

Oui : si l’opération DELETE est appliquée sur la table NAGEUR ou PLAGE

C Oui: si l’opération INSERT est appliquée sur la table PLAGE

Oui: si l’opération UPDATE est appliquée sur la table PLAGE

Non

D Oui: si l’opération INSERT est appliquée sur la table BAIGNADE

Oui: si l’opération UPDATE est appliquée sur la table BAIGNADE

Non

3. Algorithmes 3.1) Contrainte de domaine (CDM) A) Algorithme exécuté après une chaque opération de mise à jour O sur la table NAGEUR Algorithme Vérifier_CMD_Après_FinOpération(O ∈{ ‘Insert’, ‘Update’ }) Début

Var Résultat_Vérification : booléen; /*Variable calculant le résultat de vérification*/

t1, t2: tuple ;// variables représentant des tuples Resultat_Vérification:= Vrai; Pour chaque tuple t1 ∈ NAGEUR+ faire

Début Si t1.QUALITE ∉ {‘ excellente ’, ‘ bonne ’,’ médiocre ’} alors Début

t2:=Tuple_Correspondant(t1, NAGEUR- ); Annuler_Opération(‘ DELETE’, t2,‘ NAGEUR’); Annuler_Opération(‘ INSERT’, t1, ‘ NAGEUR’); Résultat_Vérification:= Faux;

Fin; Fin; Retourner Résultat_Vérification;

Fin.

B) Algorithme exécuté à la fin de la transaction T mettant à jour la table NAGEUR Algorithme Vérifier_CMD_Après_Fin_Transaction(T : Transaction) Début

Var Résultat_Vérification : booléen; t1: tuple; // variable représentant un tuple Résultat_Vérification:= Vrai;

Pour chaque tuple t1 ∈ NAGEUR+ et Résultat_Vérification faire Début

Si t1.QUALITE ∉ {‘ excellente ’, ‘ bonne ’,’ médiocre ’} alors Début

Annuler_Transaction(T); Résultat_Vérification:= Faux ;

Fin; Retourner Résultat_Vérification;

Fin.

• Tuple_Correspondant (t, NOMTABLE) est une fonction permettant de déterminer le tuple correspondant à t dans la table NOMTABLE

• Annuler_Opération(O, t, NOMTABLE) est une fonction permettant d’annuler l’effet de l’opération O de t sur la table NOMTABLE.

• Annuler_Transaction(T) est une fonction permettant d’annuler les effets de la transaction T.

Page 3: Bdd-Correction Serie TD 3

3.2) Contraintes référentielles A) Algorithme exécuté après chaque opération de mise à jour O sur les tables BAIGNADE, NAGEUR et PLAGE

Algorithme Vérifier_CRF_Après_FinOpération(O ∈{ ‘Insert’, ‘Update’, ‘Delete’ }, Nom_Table ∈{ ‘NAGEUR’,’PLAGE’,’BAIGNADE’ }, Nom_Contrainte ∈{ ‘CONT_REF_NAG’, ‘CONT_REF_PLA’ }) Début

Var Résultat_Vérification : booléen; Opt_Contrainte ∈ { ‘’,‘CASCADE’,’SET DEFAULT’, ‘SET NULL’ }; t1, t2: tuple ;

Résultat_Vérification:= Vrai; Si O ∈{ ’INSERT’ , ’UPDATE’ } et Nom_Table=’ BAIGNADE’ alors

Pour chaque tuple t1 ∈ BAIGNADE+ faire Début

Si Nom_Contrainte= ’CONT_REF_NAG’ et t1.NN ∉ { NAGEUR.NN} alors Début

t2:=Tuple_Correspondant(t1, BAIGNADE- ); Annuler_Opération(‘ DELETE’, t2,Nom_Table); Annuler_Opération(‘ INSERT’, t1, Nom_Table); Résultat_Vérification:= Faux;

Fin; Sinon

Si Nom_Contrainte= ’CONT_REF_PLA’ et t1.NP ∉ { PLAGE.NP} alors Début

t2:=Tuple_Correspondant(t1, BAIGNADE- ); Annuler_Opération(‘ DELETE’, t2,Nom_Table); Annuler_Opération(‘ INSERT’, t1, Nom_Table); Résultat_Vérification:= Faux;

Fin Fin;

Sinon Début Opt_Contrainte:= Get_Option(Nom_Contrainte, O); Si O= ’DELETE’ alors

Début Si Nom_Table = ‘NAGEUR’ alors

Pour chaque tuple t1 ∈ NAGEUR- faire Début

Si t1.NN ∈ { BAIGNADE.NN} et Opt_contrainte ∉{ ’CASCADE’,’SET NULL’ } et Opt_Contrainte= ’SET DEFAULT’ et Valeur_Defaut( BAIGNADE.NN) ∉ { NULL ∪ ( NAGEUR.NN – NAGEUR-

.NN)} alors Début

Annuler_Opération(O, t1, Nom_Table); Résultat_Vérification:= Faux;

Fin; Fin;

Sinon Si Nom_Table = ‘PLAGE’ alors Pour chaque tuple t1 ∈ PLAGE- faire Début

Si t1.NP ∈ { BAIGNADE.NP} et Opt_contrainte ∉{ ’CASCADE’,’SET NULL’ } et Opt_Contrainte= ’SET DEFAULT’ et Valeur_Defaut( BAIGNADE.NP) ∉ { NULL ∪∪∪∪ ( PLAGE.NP – PLAGE -

.NP )} alors Début

Annuler_Opération(O, t1, Nom_Table); Résultat_Vérification:= Faux;

Fin; Fin;

Page 4: Bdd-Correction Serie TD 3

Fin Sinon Si O= ’UPDATE’ alors Début

Si Nom_Table = ‘NAGEUR’ alors Pour chaque tuple t1 ∈ NAGEUR+ faire Début

t2:=Tuple_Correspondant(t1, NAGEUR- ) ; Si t1.NN ∉ { BAIGNADE.NN} et t2.NN ∈ { BAIGNADE.NN} et

Opt_contrainte ∉{’ CASCADE’,’ SET NULL’} et Opt_Contrainte= ’SET DEFAULT’ et Valeur_Defaut( BAIGNADE.NN) ∉ { NULL ∪ ( NAGEUR.NN – NAGEUR- .NN)} alors

Début Annuler_Opération(‘ DELETE’, t2, Nom_Table); Annuler_Opération(‘ INSERT’, t1,Nom_Table); Résultat_Vérification:= Faux;

Fin; Fin ;

Sinon Si Nom_Table = ‘PLAGE’ alors Pour chaque tuple t1 ∈ PLAGE+ faire Début

t2:=Tuple_Correspondant(t1, PLAGE- ) ; Si t1.NP ∉ {BAIGNADE.NP} et t2.NN ∈ {BAIGNADE.NP} Opt_contrainte ∉{’CASCADE’,’SET NULL’} et Opt_Contrainte=’SET DEFAULT’ et Valeur_Defaut(BAIGNADE.NP) ∉ {NULL ∪ (PLAGE.NP – PLAGE- .NP)} alors Début

Annuler_Opération(‘ DELETE’, t2, Nom_Table); Annuler_Opération(‘ INSERT’, t1,Nom_Table); Résultat_Vérification:= Faux;

Fin; Fin ;

Fin; Fin

Fin; Fin.

B) Algorithme exécuté à la fin de transaction T mettant à jour sur les tables BAIGNADE, NAGEUR et PLAGE

Algorithme Vérifier_CRF_Après_FinTransaction(T: Transaction , Nom_Table ∈{ ‘NAGEUR’,’PLAGE’,’BAIGNADE’ }, Nom_Contrainte ∈{ ‘CONT_REF_NAG’, ‘CONT_REF_PLA’ }) Début

Var Résultat_Vérification : booléen; Opt_Contrainte ∈ { ‘’,‘CASCADE’,’SET DEFAULT’, ‘SET NULL’ }; t1, t2: tuple ;

Résultat_Vérification:= Vrai; Si Nom_Table=’ BAIGNADE’ alors

Pour chaque tuple t1 ∈ BAIGNADE+ et Résultat_Vérification faire Début

Si Nom_Contrainte= ’CONT_REF_NAG’ et t1.NN ∉ {NAGEUR.NN} alors Début

Annuler_Transaction(T); Résultat_Vérification:= Faux;

Fin; Sinon

Si Nom_Contrainte= ’CONT_REF_PLA’ et t1.NP ∉ {PLAGE.NP} alors Début

Annuler_Transaction(T); Résultat_Vérification:= Faux;

Fin

Page 5: Bdd-Correction Serie TD 3

Fin; Sinon Début Opt_Contrainte:= Get_Option(Nom_Contrainte, O); Si Nom_Table = ‘NAGEUR’ alors

Pour chaque tuple t1 ∈ NAGEUR- et Résultat_Vérification faire Début

t2:=Tuple_Correspondant(t1, NAGEUR+) ; Si t1.NN ∈ {BAIGNADE.NN} et (t2.NN ∉ {BAIGNADE.NN} ou t2= ∅) et

Opt_contrainte ∉{ ’CASCADE’,’SET NULL’ } et Opt_Contrainte= ’SET DEFAULT’ et Valeur_Defaut(BAIGNADE.NN) ∉ {NULL ∪ (NAGEUR.NN – NAGEUR- .NN)} alors

Début Annuler_Transaction(T); Résultat_Vérification:= Faux;

Fin; Fin;

Sinon Si Nom_Table = ‘PLAGE’ alors Pour chaque tuple t1 ∈ PLAGE- et Résultat_Vérification faire Début

t2:=Tuple_Correspondant(t1, PLAGE+) ; Si t1.NP ∈ {BAIGNADE.NP} et (t2.NP ∉ {BAIGNADE.NP} ou t2= ∅) et

Opt_contrainte ∉{ ’CASCADE’,’SET NULL’ } et Opt_Contrainte= ’SET DEFAULT’ et Valeur_Defaut(BAIGNADE.NP) ∉ {NULL ∪ (PLAGE.NP – PLAGE- .NP)} alors

Début Annuler_Transaction(T); Résultat_Vérification:= Faux;

Fin; Fin;

Fin; Retourner Résultat_Vérification;

Fin.

• Get_Option(Nom_Contrainte, O) est une fonction permettant de récupérer l’option associée à l’opération O dans la contrainte Nom_Contrainte .

3.3) Contrainte de dépendance fonctionnelle A) Algorithme exécuté après chaque opération de mise à jour sur la table PLAGE

Algorithme Vérifier_CDF_Après_FinOpération(O ∈{ ‘Insert’, ‘Update’ }) Début

Var Résultat_Vérification : booléen; NBP: entier ;

t1, t2: tuple ;// variable représentant des tuples R: table ;//Une variable représentant une table relationnell e

Resultat_Vérification:= Vrai; Pour chaque tuple t1 ∈ PLAGE+ faire

Début R:= PLAGE – PLAGE - ;

NBT:=NB_Pollutions ( R,‘TYPE’,’REGION’, t1 . TYPE, t1.REGION, t1.POLLUTION) ; Si NBP ≠≠≠≠0 alors Début t2:=Tuple_Correspondant(t1, PLAGE- ); Annuler_Opération(‘ DELETE’, t2, ‘ PLAGE’); Annuler_Opération(‘ INSERT’, t1, ‘ PLAGE’);

Resultat_Vérification:= Faux; Fin; Fin; Retourner Résultat_Vérification; Fin.

Page 6: Bdd-Correction Serie TD 3

B) Algorithme exécuté à la fin de la transaction T mettant à jour la table PLAGE Algorithme Vérifier_CDF_Après_FinTransaction(T: Transaction ) Début

Var Résultat_Vérification: booléen ; NBP: entier; t1: tuple ;// Une variable représentant un tuple R: table ;//Une variable représentant une table relationnell e Resultat_Vérification:= Vrai ; Pour chaque tuple t1 ∈ PLAGE+ et Résultat_Vérification faire Début

R:=PLAGE - PLAGE - ; NBT:=NB_Pollutions(R, ‘ TYPE’,’ REGION’,t1.TYPE, t1.REGION, t1.POLLUTION);

Si NBP ≠0 alors Début Annuler_Transaction(T); Resultat_Vérification:= Faux ; Fin; Retourner Résultat_Vérification;

Fin.

• NB_Pollutions(R, A1, A2, V1, V2, V3) est une fonction permettant de calculer dans la relation R le nombre de différentes valeurs de l’attribut POLLUTION hormis la valeur V3 correspondant aux valeurs V1et V2 de l’attributs A1 et A2.

3.4) Contrainte avec agrégat A) Algorithme exécuté après chaque opération de mise à jour sur la table BAIGNADE

Algorithme Vérifier_CA_Après_FinOpération(O ∈{ ‘Insert’, ‘Update’ }) Début

Var Résultat_Vérification : booléen; DR: entier ;/*Utiliser pour calculer la somme des durée en min utes des

baignade par nageur et par jour*/ t1, t2: tuple ;// Une variable représentant un tuple

R: table ;//Une variable représentant une table relationnell e Resultat_Vérification:= Vrai;

Pour chaque tuple t1 ∈ BAIGNADE+ faire Début

R:= BAIGNADE – BAIGNADE - ∪∪∪∪ BAIGNADE+; DR:=Durée_Baignade(R, t1.NN, t1.DATE); Si NBP > 120 alors Début

t2:=Tuple_Correspondant(t1, BAIGNADE- ); Annuler_Opération(‘ DELETE’, t2,’ BAIGNADE’); Annuler_Opération(‘ INSERT’, t1, ‘ BAIGNADE’); Resultat_Vérification:= Faux;

Fin; Fin; Retourner Résultat_Vérification; Fin.

B) Algorithme exécuté à la fin de la transaction T mettant à jour la table BAIGNADE Algorithme Vérifier_CA_Après_FinTransaction(T: Transaction ) Début

Var Résultat_Vérification : booléen; DR: entier ;/*Utiliser pour calculer la somme des durées en mi nutes des

baignades par nageur et par jour*/ t1: tuple ;// Une variable représentant un tuple

R: table ;//Une variable représentant une table relationnell e Resultat_Vérification:= Vrai;

Pour chaque tuple t1 ∈ BAIGNADE+ et Resultat_Vérification faire Début

R:= BAIGNADE – BAIGNADE - ∪∪∪∪ BAIGNADE+; DR:= Durée_Baignade(R, t1.NN, t1.DATE);

Page 7: Bdd-Correction Serie TD 3

Si DR > 120 alors Début Annuler_Transaction(T);

Resultat_Vérification:= Faux; Fin; Fin; Retourner Résultat_Vérification; Fin.

• Durée_Baignade(R, V1, V2) est une fonction permettant de calculer dans la relation R la somme de durées des baignades en minutes du nageur numéro V1 durant le jour V2.

3.5) Combinaison des deux types de vérification On se contente ici de démontrer uniquement la combinaison des deux types de vérification sur la contrainte de domaines. Les démonstrations sur les autres types de contraintes sont pareilles à la démonstration présentée. Algorithme Vérifier_CMD_Après_FinOpération_Transaction(T: Tran saction) Début

Var Résultat_Vérification : booléen; /*Variable calculant le résultat de vérification*/

t1, t2: tuple ;// variables représentant des tuples R: table ;//Une variable représentant une table relationnell e

Resultat_Vérification:= Vrai; Pour chaque O ∈{ ‘Insert’, ‘Update’ } de T et Résultat_Vérification Début R:= Table_DifférentiellePlus(O,‘ NAGEUR’); Pour chaque tuple t1 ∈ R et Résultat_Vérification faire

Début Si t1.QUALITE ∉ {‘ excellente ’, ‘ bonne ’,’ médiocre ’} alors Début Annuler_Transaction(T); Résultat_Vérification:= Faux ; Fin;

Fin; Fin; Retourner Résultat_Vérification;

Fin.

• Table_DifférientiellePlus(O, NomTable) est une fonction permettant de calculer la relation différentielle NomTable + suite à l’exécution de l’opération O sur la table NomTable .

4. Optimisation de la vérification des contraintes avec agrégats Pour optimiser la vérification des contraintes d’intégrité avec agrégats on gère dans la méta-base des agrégats redondants. Par exemple, pour vérifier que la somme des durées des baignades d’un nageur par jour doit rester inférieure ou égale à 2 heures (ou 120 minutes), on gère cette somme (notée SommeDurée) dans une table (notée Table_Durée_Baignade )

de la méta-base et qui comporte trois attributs (NN, DATE, SommeDurée ). Un pré-test

simple lors de l’insertion d’une nouvelle baignade (notée B) consistera alors à vérifier que la

SommeDurée dans la table Table_Durée_Baignade correspondant à B.NN et B.DATE

+ B.durée ≤ 120 .

Page 8: Bdd-Correction Serie TD 3

Exercice 2

1. Définition de la table FILM en SQL

CREATE TABLE FILM ( NUMF INT NOT NULL PRIMARY KEY , TITRE VARCHAR(100) NOT NULL UNIQUE, DATEF DATE, LONGUEUR DEC(4,2), BUDGET DEC(10,2), REALISTEUR INT REFERENCES PERSONNE(NUMP), SALAIRER DEC (8,2));

2. Définition de la table GENERIQUE en SQL

CREATE TABLE GENERIQUE(FILM INT NOT NULL , ACTEUR INT NOT NULL , ROLE VARCHAR(50), SALAIRE DEC(8,2), PRIMARY KEY(FILM, ACTEUR, ROLE), FOREIGN KEY FILM REFERENCES FILM(NUMF), FOREIGN KEY ACTEUR REFERENCES

ACTEUR(NUMA))

3. Expression de contrainte d’intégrité générale

CREATE ASSERTION CG_BUDGET_FILM BEFOR COMMIT CHECK ( NOT EXISTS ( SELECT NUMF FROM FILM WHERE BUDGET <= ( SELECT SUM(SALAIRE) FROM GENERIQUE WHERE FILM=NUMF)) FOR FILM;

4. Déclencheur pour la mise à jour de FILM

CREATE TRIGGER MAJFIL AFTER UPDATE OF NUMF ON FILM REFERENCING OLD AS O, NEW AS N ( UPDATE GENERIQUE SET FILM=N WHERE FILM=O; UPDATE PASSE SET NUMF=N WHERE NUMF=O FOR EACH ROW);

5. Déclencheur pour le calcul du nombre de salles exposant un FILM

CREATE TRIGGER CALCUL_NB_SALLES AFTER INSERT, UPDATE, DELETE ON PASSE REFERENCING NEW_TABLE AS N, OLD_TABLE AS O ( UPDATE FILM SET NBSALLES=( SELECT COUNT(SALLE) FROM PASSE WHERE PASSE.NUMF=O.NUMF) WHERE NUMF=O.NUMF; UPDATE FILM SET NBSALLES=( SELECT COUNT(SALLE) FROM PASSE WHERE PASSE.NUMF=N.NUMF) WHERE NUMF=N.NUMF FOR EACH ROW);

Page 9: Bdd-Correction Serie TD 3

6. Déclencheur pour empêchant la suppressions des FILM avant 2000

CREATE TRIGGER EMPECHE_SUP_FILM BEFOR DELETE ON FILM REFERENCING OLD_TABLE AS O WHEN YEAR(O.DATE) <2000 SIGNAL.SQLSTATE ‘75001’ (’On ne peut pas supprimer ce FILM : ’+O.T ITRE) FOR EACH ROW);

Ou bien en utilisant la clause INSTEAD OF CREATE TRIGGER EMPECHE_SUP_FILM INSTEAD OF DELETE ON FILM REFERENCING OLD_TABLE AS O IF YEAR(O.DATE) <2000 SIGNAL.SQLSTATE ‘75001’ (’On ne peut pas supprimer ce FILM : ’+O.T ITRE) ELSE DELETE FROM FILM WHERE NUMF=O.NUMF FOR EACH ROW);