Correction - Modèle relationnel et intégrité des données

Le concept de clé primaire et de clé candidate

Cas 1 : une relation unique

Exercice 1 : Pour trouver le lieu de travail de l’employé DOE dans la TABLE_1 dans le pire des cas :

La réponse correcte est a) Il faut parcourir toutes les lignes de la table

L’employé DOE travaille à DALLAS et CHICAGO. Il peut exister plusieurs lignes avec la même valeur de l’attribut ENAME (par exemple pour DOE).

Exercice 2 : Concernant la possibilité d’avoir plusieurs lignes avec la même valeur de l’attribut ENAME dans TABLE_1 :

La réponse correcte est c) Il est possible et acceptable d’avoir plusieurs lignes avec le même ENAME. En effet, comme nous le voyons avec l’exemple de DOE, un employé peut travailler dans plusieurs départements, ce qui génère plusieurs lignes avec le même ENAME.

Exercice 3 : Pour renommer en IMMOBILIER le département dans lequel travaille l’employé TURNER :

  • En lecture : dans le pire des cas, on parcourt tout le tableau
  • En modification : 5 lignes modifiées, ce qui correspond au nombre de personnes dans le département SALES de CHICAGO Note : Les départements sont définis par leur numéro (DEPTNO), il ne faut pas confondre les départements qui ont le même nom.

Exercice 4 : Identification des clés candidates :

Les clés candidates possibles sont :

  • {EMPNO, DNAME, LOC}
  • {EMPNO, DEPTNO} Ces ensembles constituent chacun un ensemble minimal d’attributs permettant d’identifier de manière unique chaque ligne de la table.

Exercice 5 : Concernant les données redondantes pour l’employé SCOTT :

  • EMPNO n’est pas redondant car il est la clé d’identification d’un employé
  • MGR est redondant car un employé n’a qu’un seul manager même s’il occupe plusieurs postes
  • JOB n’est pas redondant car un employé peut avoir plusieurs jobs dans des départements différents
  • LOC est redondant car la localisation est associée à un département et sera donc commune à tous les membres de ce département

Exercice 6 : Les redondances identifiées dans la table concernent :

  • Les informations du manager (MGR) qui sont répétées pour chaque poste d’un même employé
  • Les informations de localisation (LOC) qui sont répétées pour tous les employés d’un même département Ces redondances peuvent causer des problèmes de cohérence lors des mises à jour.

Le concept de clé étrangère

Cas 2 : Décomposition en plusieurs relations

Exercice 7 : Une décomposition possible en 3 relations :

TABLE_1 (EMPNO, ENAME, SAL, MANAGER, HIREDATE, COMMISSION) avec EMPNO en clé primaire.

TABLE_2 (DEPTNO, DNAME, LOC) avec DEPTNO en clé primaire.

TABLE_3 (EMPNO, DEPTNO, JOB) avec {EMPNO, DEPTNO} en clé primaire et où EMPNO référence TABLE_1(EMPNO) et DEPTNO référence TABLE_2(DEPTNO).

Exercice 8 : Les clés sont identifiées dans l’exercice 7 ci-dessus avec les justifications de leurs rôles.

Exercice 9 : Pour le remplissage des tables, il faut être attentif à ne pas dupliquer les informations qui étaient redondantes dans la table unique initiale.

Exercice 10 : Pour trouver le lieu de travail de l’employé DOE :

La réponse correcte est c) Il faut joindre les tables pour obtenir l’information.

Processus :

  1. Dans TABLE_1 on cherche le EMPNO de DOE (7904)
  2. Dans TABLE_3 on cherche le n° de département de l’employé 7904
    • On trouve deux tuples : (7904, 20, CLERK) et (7904, 30, SALESMAN)
  3. Dans TABLE_2 on cherche la localisation des départements 20 (DALLAS) et 30 (CHICAGO)

Dans le pire des cas, on doit parcourir toutes les lignes des trois tables.

Exercice 11 : Pour renommer en IMMOBILIER le département dans lequel travaille l’employé TURNER :

  1. Dans TABLE_1 on cherche le EMPNO de TURNER (7844)
  2. Dans TABLE_3 on cherche le n° de département de l’employé 7844
    • On trouve : (7844, 30, SALESMAN)
  3. Dans TABLE_2 on modifie le département 30 : (30, IMMOBILIER, CHICAGO)

En lecture : dans le pire des cas, parcours de toutes les tables En modification : une seule ligne (correspond au nombre de départements concernés)

Exercice 12 : Cette nouvelle structure réduit les redondances car :

  • Les informations sur les employés ne sont stockées qu’une seule fois dans TABLE_1
  • Les informations sur les départements ne sont stockées qu’une seule fois dans TABLE_2
  • La relation entre employés et départements est gérée par TABLE_3 sans duplication d’information

Synthèse

Exercice 13 : Comparaison avec la structure initiale à table unique :

Structure multi-tables :

  • Interrogation des données : - (plus compliquée car nécessite des jointures)
  • Mise à jour des données : + (plus simple car une seule modification nécessaire)

Justification : La séparation des informations complique un peu la consultation des données mais simplifie grandement leur mise à jour. Il n’y a plus de risque d’incohérence lors des modifications.

Exercice 14 : Pour un système de gestion des ressources humaines d’une grande entreprise, la structure multi-tables est la plus avantageuse car :

  • Elle évite les redondances et donc les risques d’incohérence
  • Elle facilite les mises à jour qui sont fréquentes dans un tel système
  • Elle permet une meilleure gestion des droits d’accès aux différentes informations
  • Elle offre une meilleure évolutivité du système
  • Les performances de consultation sont gérées efficacement par les SGBD modernes

Exercice 15 : Pour une application de gestion de tâches personnelle, la structure à table unique serait plus adaptée car :

  • Le volume de données est limité
  • Les risques d’incohérence sont faibles
  • La simplicité de la structure facilite le développement
  • Les performances de consultation sont meilleures avec une seule table pour un petit volume de données