Click here to load reader
Upload
ouaheb-ouaheb
View
22
Download
3
Embed Size (px)
Citation preview
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;
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.
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;
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
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.
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);
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 .
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);
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);