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

Temps estimé de réalisation : 1h30

Objectifs

Question à laquelle cette activité va permettre de répondre :
- Quel est l’impact de la structure des données sur leur consultation et leur mise à jour ?

Objectifs pédagogiques visés :
- Identifier clés primaires et étrangères
- Expliquer la notion de redondance

Contexte

Considérons une entreprise dans laquelle on désire stocker des données concernant les employés et les départements dans lesquels travaillent ces employés. Nous allons tout au long de ce cas d’étude examiner différentes façons de représenter ces données au moyen de tableaux que nous allons manipuler avec votre tableur favori. Ces exercices doivent vous permettre d’appréhender la modélisation de données persistantes et de comprendre les difficultés liées à une représentation tabulaire des données, telles qu’elles le sont dans les bases de données.

A l’intérieur de l’entreprise, quelques règles de gestion sont mises en œuvre. Vous devrez veiller à les respecter lors de cette étude :

  • Un employé peut appartenir à plusieurs départements
  • Un employé n’a qu’un seul poste dans un même département
  • Un employé n’a qu’un seul manager même s’il occupe plusieurs postes
  • Deux départements, situés dans la même ville, ne peuvent pas avoir le même nom

Les données stockées sont les suivantes :

  • EMPNO : numéro de l’employé
  • ENAME : nom
  • JOB : poste
  • MANAGER : numéro du manager
  • SAL : salaire en €
  • HIREDATE : date d’embauche
  • COMMISSION : commission en €
  • DEPTNO : numéro du département
  • DNAME : nom du département
  • LOC : localisation du département
  • cas1.xls (22 ko)
  • cas2a.xls (24 ko)
  • cas3.xls (23 ko)

    • Quelques définitions avant de débuter

    Définition

    Relation (définition informelle)

    Une relation (ou table) en base de données correspond à un ensemble d’attributs (les colonnes de la table) qui prennent chacun leurs valeurs dans un ensemble défini que l’on appelle domaine.

    Par exemple, si je veux modéliser les données de mes clients je vais créer une table (ou relation) CLIENT qui contiendra des attributs comme leur nom, prénom, adresse, etc.

    Dans cette activité, les tables que nous allons manipuler TABLEAU_1, TABLEAU_2, etc. sont des relations.

    Définition

    Intégrité des données

    Dans le domaine des bases de données, l’intégrité des données correspond à l’exhaustivité, l’exactitude et la cohérence des données.

    Par exhaustivité on entend que les données restent complètes (il n’y a pas de disparitions). Par exactitude et cohérence on entend le respect de l’intégrité logique : intégrité d’entité (dans une table une ligne est unique), intégrité référentielle (la cohérence entre les différentes tables est maitenue), intégrité de domaine (les données sont dans l’ensemble des valeurs acceptables définies) et l’intégrité définie par l’utilisateur (les règles propres définies par l’entreprise).

    Cette activité a pour objectif de vous faire découvrir les principes du modèle relationnel (les contraintes d’intégrité) qui définissent un ensemble de règles et de normes apppliquées lors de la phase de conception pour maintenir l’intégrité des données.

    Par exemple, si vous modifiez l’adresse d’un client, vous souhaitez sûrement que l’information soit modifiée partout et ne pas vous retrouver avec la nouvelle adresse dans le profil du client et l’ancienne adresse dans les commandes à envoyer.

    C’est ce que nous allons explorer dès à présent. Soyez vigilent aux pièges qui vous seront tendus !

    La notion de clef primaire et candidate

    Cas n°1 : une relation unique

    Le fichier cas1.xls présente de manière partielle une seule feuille de calcul avec la relation suivante :

    TABLEAU_1 (EMPNO, ENAME, JOB, SAL, MANAGER, HIREDATE, COMMISSION, DEPTNO, DNAME, LOC)

    • Question 1 : Cherchez le lieu de travail de l’employé DOE. Dans le pire des cas, combien de lignes devez-vous parcourir ? Peut-il y avoir plusieurs lignes avec la même valeur de l’attribut ENAME ?

    • Question 2 : Faites les mises à jour nécessaires pour renommer en REAL ESTATE le département dans lequel travaille l’employé TURNER. Dans le pire des cas, combien de lignes devez-vous parcourir en lecture ? Combien de lignes avez-vous dû modifier ? À quoi ce dernier nombre correspond-il ?

    Définition

    Clef candidate

    Une clef candidate est un attribut ou un ensemble d’attributs minimal identifiant de manière unique chaque tuple d’une relation.

    Définition

    Clef primaire

    La clef candidate choisie comme identifiant de la relation est appelée clef primaire. Une clef primaire est unique et doit être renseignée (elle ne peut pas prendre la valeur NULL).

    Par convention, on souligne (d’un trait plein) la clé primaire d’une relation.

    • Question 3 : Identifiez la ou les clefs candidates de la relation et choisissez une clef primaire.

    Définition

    Donnée redondante

    Une donnée est considérée redondante si elle se répète sans apporter d’information supplémentaire.

    • Question 4 : Considérer les 2 lignes concernant l’employé SCOTT. Choisissez la (les) proposition(s) ci-dessous qui vous semble(nt) exacte(s) en justifiant votre choix :
      • Les valeurs de EMPNO sont redondantes
      • Les valeurs de MGR sont redondantes
      • Les valeurs de JOB sont redondantes
      • Les valeurs de LOC sont redondantes

    La notion de clef étrangère

    Définition

    Clef étrangère ou clef référentielle

    On dit que l’ensemble K des attributs de R1 est clef étrangère et qu’elle référence R2 si les attributs K de R1 ont les mêmes domaines que les attributs de la clé primaire de la relation R2 et que toutes les valeurs de la clef étrangère correspondent bien à une valeur de clef primaire ou à NULL.

    Par convention, on souligne (d’un trait pointillé) la clef référentielle d’une relation et on explicite par du texte quelle clef primaire est référencée par la clef étrangère.

    Exemple :

    Client (Numéro, Nom, Adresse, Téléphone)

    Produit (Numéro, Désignation, Prix)

    Vente (Numéro, NuméroClient, NuméroProduit, Date) où NuméroClient référence Client(Numéro) et NuméroProduit référence Produit(Numéro)

    Cas n°2A : première proposition de décomposition en deux relations

    Le fichier cas2a.xls présente deux feuilles de calcul avec les tableaux suivants :

    TABLEAU_1(EMPNO, ENAME, JOB, SAL, MANAGER, HIREDATE, COMMISSION, DNAME) où DNAME référence TABLEAU_2(DNAME)

    TABLEAU_2(DEPTNO, DNAME, LOC)

    • Question 5 : Dans quelle mesure cette nouvelle structure de données peut-elle réduire les redondances rencontrées lors de l’étude du cas n°1 ?

    • Question 6 : Les clefs primaires et référentielles définies sur TABLEAU_1 et TABLEAU_2 sont-elles correctes ? Pourquoi ?

    • Question 7 : Pouvez-vous renommer en REAL ESTATE le département dans lequel travaille l’employé TURNER ? Pourquoi ?

    CAS n°2B : votre proposition de décomposition en deux relations

    • Question 8 : Proposez une décomposition en 2 relations corrigeant les relations du cas n°2A afin de résoudre le problème que vous avez identifié. Faites valider votre proposition par un enseignant.

    • Question 9 : Cherchez le lieu de travail de l’employé DOE. Dans le pire des cas, combien de lignes devez-vous parcourir ? Peut-il y avoir plusieurs lignes avec le même ENAME ?

    • Question 10 : Faites les mises à jour nécessaires pour renommer en REAL ESTATE le département dans lequel travaille l’employé TURNER. Dans le pire des cas, combien de lignes devez-vous parcourir en lecture ? Combien de lignes avez-vous dû modifier ? A quoi ce dernier nombre correspond-t-il ?

    • Question 11 : Quelles données peut-on encore considérer comme redondantes ?

    Une décomposition sans redondance

    Cas n°3 : décomposition en trois relations

    Le fichier cas3.xls présente trois feuilles de calcul avec les tableaux suivants :

    TABLEAU_1(EMPNO, ENAME, SAL, MANAGER, HIREDATE, COMMISSION)

    TABLEAU_2(DEPTNO, DNAME, LOC)

    TABLEAU_3(EMPNO, DEPTNO, JOB)

    • Question 12 : Identifiez, pour chacune des trois relations, les clefs primaires et référentielles si elles existent.

    • Question 13 : Remplissez les feuilles de calculs en vous aidant des données du cas n°1.

    • Question 14 : Dans quelle mesure cette nouvelle structure de données peut-elle réduire les redondances rencontrées lors de l’étude du cas n°2B ?

    • Question 15 : Cherchez le lieu de travail de l’employé DOE. Dans le pire des cas, combien de lignes devez-vous parcourir ? Peut-il y avoir plusieurs lignes avec le même ENAME ?

    • Question 16 : Faites les mises à jour nécessaires pour renommer en REAL ESTATE le département dans lequel travaille l’employé TURNER. Dans le pire des cas, combien de lignes devez-vous parcourir en lecture ? Combien de lignes avez-vous dû modifier ? A quoi ce dernier nombre correspond-t-il ?

    • Question 17 : Quelles données peut-on considérer comme redondantes ?

    Bilan

    Remplissez la fiche de synthèse ci-dessous en considérant les critères suivants :

    1. Consultation des données : la difficulté à effectuer la requête se compte en nombre de lignes « lues » afin d’obtenir la réponse à la question posée.

    2. Mise à jour des données : la difficulté à effectuer la mise à jour se compte en nombre de lignes concernées afin d’effectuer la modification.

    Utilisez la légende suivante pour remplir votre tableau :

    LégendeSymbole à utiliser
    Situation de référence ●
    Consultation ou mise à jour plus compliquée - et - -
    Consultation ou mise à jour plus simple + et ++
    • Question 18 : Remplissez chaque case en comparant le changement par rapport au cas n°1 :
    Consultation des donnéesMise à jour des données
    Cas n°1 ● ●
    Cas n°2B
    Justifiez :
    Cas n°3
    Justifiez :
    • Question 19 : Quelle structure de données présentée dans les différents cas jugez-vous la plus pertinente ? Justifiez votre réponse.

    Après avoir réalisé le TD vous pouvez consulter la correction.