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

Ce TME se base sur le TD 9 et vise à illustrer la modification des tables et l'interaction avec les contraintes d'intégrité. Le schéma Entreprise est celui qui est utilisé avec une légère modification permettant de faciliter la saisie des données:

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

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 les tables de ce schéma, il vous est demandé de télécharger l'archive tme8.tar et d'en extraire le fichier TME8-creations.sql qui contient les instructions de création du schéma Entreprise. Il suffit ensuite de taper la commande @TME8-creations.sql pour créer ce schéma.

Remarque : Pour vérifier que les tables Employe, Projet, Embauche et Grille_sal ont été créées, utiliser, par exemple, la commande @liste.

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

 SELECT * 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 dans Employe les n-uplets suivants :

  	        22334, 'Adam', 'Funk', 'Paris', '1-12-1982'		
      		45566, 'Rachid', 'Allaoui', 'Lyon', '13-4-1986'	     	
      		77889, 'Florent', 'Girac' , 'Marseille', '4-11-1990'

• 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 les nuplets suivants :

                123, 'ADOOP', 22334, 'Paris', 120000
		757, 'SKALA', 45566, 'Lyon', 180000
		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

		'Admin', 80000
		'Deve',    45000
		'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, tapez la commande

 @TME8-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 tapant la commande:

 @TME8-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

Pour cette partie, tapez la commande:

@TME8-insertions-bis

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 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 seront supprimés eux aussi.