TME 9: Modification des données – contraintes d'intégrité sous H2

Ce TME réutilise le schéma Entreprise du TD 9 et vise à illustrer la modification des tables et l'interaction avec les contraintes d'intégrité. Ce schéma est décrit ci-dessous :

• EMPLOYE(NumSS, NomE, PrenomE, VilleE, DateNaiss)
• PROJET (NumProj, NomProj, RespProj*, VilleP, Budget)
• EMBAUCHE (NumSS*, NumProj*, DateEmb, Profil*)
• GRILLE_SAL (Profil, salaire)

Pour faciliter la saisie des données, une légère modification est apporté : désormais, l'attribut NumSS de Employe a une longueur fixe de 5 chiffres et l'attribut NumProj de Projet contient 3 chiffres. Pour créer le schéma de cette base, exécuter le script accessible depuis tme8-creations-h2.

Les tables que vous venez de créer sont vides. Vous pouvez le vérifier en exécutant une requête du style

 SELECT COUNT(*) FROM nom_table ;

Dans un premier temps (Première Partie), vous allez tenter d'insérer des n-uplets, supprimer des n-uplets et modifier des n-uplets avec le schéma que vous venez de créer.Vous remarquerez que plusieurs de ces opérations seront rejetées à cause des contraintes d'intégrité. Prenez soin d'observer les messages d'erreurs renvoyés par Oracle.

Dans un second temps (Seconde Partie), vous allez recharger le même schéma avec des contraintes plus souples et constater que plusieurs opérations interdites avec la configuration initiale seront acceptées avec cette nouvelle configuration.

1. Première Partie

Insertions rejetées

• 1. Proposer une insertion dans la table Employe qui ne respecte pas la contrainte de clé primaire (pk_emp).

• 2. Proposer une insertion dans la table Employe qui ne respecte pas la contrainte de limite d'âge (limite_age).

• 3. Proposer une insertion dans la table Employe qui ne respecte pas la contrainte de longueur de l'attribut NumSS (format_nss).

• 4. Proposer une insertion dans la table Employe qui ne respecte pas la contrainte sur les villes possibles (ville_emp).

Pour la suite des questions, il vous est demandé d'insérer de vider Employe et d'insérer trois nouveaux n-uplets en exécutant les commandes suivantes :

DELETE FROM Employe;
 
INSERT INTO Employe (NumSS, NomE, PrenomE, VilleE, DateNaiss)
SELECT * FROM (
       SELECT	22334, 'Adam', 'Funk', 'Paris',  PARSEDATETIME('1-12-1982',  'dd-mm-yyyy')		UNION ALL
       SELECT	45566, 'Rachid', 'Allaoui', 'Lyon', PARSEDATETIME('13-4-1986',  'dd-mm-yyyy')	     	UNION ALL
       SELECT	77889, 'Florent', 'Girac' , 'Marseille', PARSEDATETIME('4-11-1990', 'dd-mm-yyyy')
);
SELECT * FROM Employe;

• 5. Proposer une insertion dans la table Projet qui ne respecte pas la contrainte référentielle vers Employe (fk_resp).

• 6. Proposer une insertion dans la table Projet qui ne respecte pas la contrainte de ville du responsable d'un projet (ville_proj).

A présent, il est vous demandé d'insérer dans Projet trois nuplets en exécutant les commandes suivantes :

 
INSERT INTO Projet VALUES (123, 'ADOOP', 22334, 'Paris', 120000);
INSERT INTO Projet VALUES (757, 'SKALA', 45566, 'Lyon', 180000);
INSERT INTO Projet VALUES (890, 'BAJA', 22334, 'Paris', 24000);

• 7. Peut on insérer des n-uplets dans Embauche en l’état ?

Insérer les nuplets suivants dans la table Grille_sal trois nuplets comme suit :

INSERT INTO grille_sal VALUES ('Admin', 80000);
INSERT INTO grille_sal VALUES ('Deve',45000);
INSERT INTO grille_sal VALUES ('Tech',35000);

• 8. Proposer une insertion dans Embauche qui ne respecte pas au moins une contrainte parmi celles définies pour cette table.

Suppressions rejetées

Afin de répondre aux questions qui suivent, exécuter les commandes se trouvant dans Creation puis celles dans Insertions

• 9. Proposer une suppression de nuplets de Employe qui ne respecte pas une contrainte référentielle d'une autre table. Précisez laquelle puis essayer de contourner les contraintes jusqu'à pouvoir supprimer les nuplets de Employe référencés nulle part.

• 10. Même question pour Projet.

• 11. Même question pour Grille_sal.

• 12. Est-ce possible de supprimer les nuplets de Embauche sans rencontrer les mêmes problèmes que précédemment ?

Mises à jour rejetées

Remettre la base à l'état initial en exécutant les commandes se trouvant dans Creation puis celles dans Insertions

• 13. Proposez un mise à jour de Employe qui ne respecte pas les contraintes référentielles puis tenter de trouver une mise-a-jour qui les respecte toutes.

• 14. Même question avec Projet.

• 15. Proposez une mise-a-jour qui modifie les villes des responsables.

• 16. Proposez une mise-a-jour de Profil qui ne respecte pas les contraintes référentielles.

2. Deuxième Partie

Comme indiqué précédemment, le but de cette partie d'illustrer un cas où les mises-a-jour ne sont pas rejetées mais plutôt propagées. Pour ce faire exécuter les commandes se trouvant dans Creation-contraintes-cascade puis insérer les nuplets en exécutant les commandes de Insertions.

Désormais, il sera possible de supprimer des nuplets d'une table référencées par une autre table. Pour le voir, supprimer les n-uplets de Employe et vérifier que tous les n-uplets des autres tables qui référencent des employés venant d'être supprimés sont aussi supprimés.