Modèle Relationnel et Intégrité des Données

Durée1h15

Présentation & objectifs

Dans ce TP, vous explorerez les concepts fondamentaux du modèle relationnel et de l’intégrité des données, en apprenant à structurer efficacement les données pour divers scénarios d’entreprise. À la fin de ce TP, vous serez capable de :

Identifier les clés primaires et étrangères dans les structures de bases de données relationnelles
Expliquer le concept de redondance des données et ses implications
Comprendre les avantages de la décomposition des données dans la conception des bases de données
Analyser l’impact de la structure des données sur les opérations de requête et de mise à jour

Contexte

Considérons une entreprise où nous voulons stocker des données sur les employés et les départements dans lesquels ces employés travaillent. Tout au long de cette étude de cas, nous examinerons différentes façons de représenter ces données en utilisant des tables que nous manipulerons avec votre logiciel tableur préféré. Ces exercices devraient vous permettre de comprendre la modélisation des données persistantes et de cerner les difficultés liées à une représentation tabulaire des données, telles qu’elles sont dans les bases de données.

Au sein de l’entreprise, certaines règles de gestion sont mises en place. Vous devrez veiller à les respecter durant 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 une 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 de département
DNAME : nom du département
LOC : localisation du département

Téléchargez le fichier suivant

Quelques définitions avant de commencer

Relation (définition informelle)

Une relation (ou table) dans une base de données correspond à un ensemble d'attributs (les colonnes de la table) qui prennent chacun leurs valeurs dans un ensemble défini appelé domaine.

Par exemple, si je veux modéliser mes données 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 TABLE_1, TABLE_2, etc. sont des relations.

Intégrité des données

Dans le domaine des bases de données, l'intégrité des données correspond à la complétude, l'exactitude et la cohérence des données. Par complétude, 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 différentes tables est maintenue), intégrité de domaine (les données sont dans l'ensemble des valeurs acceptables définies) et intégrité définie par l'utilisateur (règles spécifiques définies par l'entreprise).

Cette activité vise à vous faire découvrir les principes du modèle relationnel (contraintes d’intégrité) qui définissent un ensemble de règles et de normes appliqué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 probablement 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 maintenant. Soyez vigilants aux pièges qui vous seront tendus !

Le concept de clé primaire et de clé candidate

Cas 1 : une relation unique

Le fichier cas1.xls présente partiellement une seule feuille de calcul avec la relation suivante :

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

Exercice 1 : Pour trouver le lieu de travail de l’employé DOE dans la TABLE_1 dans le pire des cas, quelle affirmation est correcte ?

a) Il faut parcourir toutes les lignes de la table (14 lignes lues)
b) On peut s’arrêter dès qu’on trouve la première occurrence de DOE (7 lignes lues en moyenne)
c) Il est impossible de trouver cette information (0 ligne lue)

Exercice 2 : Concernant la possibilité d’avoir plusieurs lignes avec la même valeur de l’attribut ENAME dans TABLE_1, quelle affirmation est correcte ? Expliquez votre choix.

a) Il est impossible d’avoir plusieurs lignes avec le même ENAME
b) Il est possible d’avoir plusieurs lignes avec le même ENAME, mais cela violerait les principes de bonne conception de base de données
c) Il est possible et acceptable d’avoir plusieurs lignes avec le même ENAME

Exercice 3 : Effectuez les mises à jour nécessaires pour renommer en IMMOBILIER le département dans lequel travaille l’employé TURNER. Dans le pire des cas, combien de lignes devez-vous parcourir pour la lecture ? Combien de lignes avez-vous dû modifier ? À quoi correspond ce dernier nombre ?

Clé candidate

Une clé candidate est un attribut ou un ensemble minimal d'attributs qui identifie de manière unique chaque tuple dans une relation.

Clé primaire

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

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

Exercice 4 : Identifiez la ou les clé(s) candidate(s) de la relation et choisissez une clé primaire.

Données redondantes

Les données sont considérées comme redondantes si elles se répètent sans apporter d'information supplémentaire.

Exercice 5 : Considérez les 2 lignes concernant l’employé SCOTT. Choisissez la ou les proposition(s) ci-dessous qui vous semblent correctes 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

Exercice 6 : Considérez les données dans la feuille de calcul. Pouvez-vous identifier des lignes qui contiennent des informations redondantes ? Quels problèmes cette redondance pourrait-elle causer ?

Le concept de clé étrangère

Clé étrangère

Une clé étrangère est un attribut ou un ensemble d'attributs dans une table qui fait référence à la clé primaire d'une autre table. La clé primaire qu'elle référence peut être composée d'un ou plusieurs attributs. Il est important que le(s) domaine(s) des attributs de la clé étrangère correspondent au(x) domaine(s) des attributs de la clé primaire correspondante dans la table référencée. Cette relation établit un lien entre les données de deux tables, assurant l'intégrité référentielle.

Décomposons ce concept en utilisant l’exemple ci-dessous :

Client (Numero, Nom, Adresse, Telephone)

Produit (Numero, Description, Prix)

Vente (Numero, NumeroClient, NumeroProduit, Date) 

où NumeroClient référence Client(Numero) et NumeroProduit référence Produit(Numero)

Dans la table Vente, NumeroClient est une clé étrangère qui référence le Numero (clé primaire) dans la table Client. Cela signifie que chaque NumeroClient dans la table Vente doit correspondre à un Numero existant dans la table Client (ou être NULL), et les deux doivent être du même type de données (dans ce cas, entier). Cela garantit que chaque vente est associée à un client valide.

De même, NumeroProduit dans la table Vente est une clé étrangère référençant le Numero dans la table Produit, assurant que chaque vente est liée à un produit valide. Là encore, NumeroProduit dans la table Vente et Numero dans la table Produit doivent être du même type de données.

Par convention, on souligne la clé primaire d’une relation d’un trait continu, et on souligne la clé étrangère d’un trait pointillé. On indique explicitement la clé primaire référencée par chacune des clés étrangères.

Cas 2 : Décomposition en plusieurs relations

Dans ce cas, nous allons décomposer les données en plusieurs relations pour réduire les redondances et améliorer l’intégrité des données. L’objectif est de créer une structure qui :

Sépare les informations sur les employés, les départements et les affectations des employés aux départements.
Élimine les répétitions inutiles d’informations.
Permet de représenter le fait qu’un employé peut travailler dans plusieurs départements.

Exercice 7 : Pour résoudre les problèmes de redondance, nous devons décomposer nos données en plusieurs relations. Proposez une décomposition en 3 relations qui répond aux objectifs mentionnés ci-dessus. Indice : Pensez à séparer les informations sur les employés, les départements et la relation entre les employés et les départements.

Exercice 8 : Pour chacune des trois relations que vous avez proposées, identifiez les clés primaires et étrangères si elles existent. Justifiez vos choix.

Exercice 9 : Remplissez les feuilles de calcul correspondant à votre décomposition en utilisant les données de la feuille de calcul originale. Veillez à éliminer les redondances que vous avez identifiées précédemment dans la question 6.

Exercice 10 : Pour trouver le lieu de travail de l’employé DOE dans votre nouvelle structure, quelle affirmation est correcte ? Expliquez votre choix.

a) Il faut parcourir toutes les lignes de toutes les tables (somme des lignes de toutes les tables)
b) On peut s’arrêter dès qu’on trouve la première occurrence de DOE dans la table des employés (nombre moyen de lignes dans la table des employés / 2)
c) Il faut joindre les tables pour obtenir l’information (somme des lignes de toutes les tables impliquées dans la jointure)
d) Il est impossible de trouver cette information (0 ligne lue)

Exercice 11 : Dans votre nouvelle structure, effectuez les mises à jour nécessaires pour renommer en IMMOBILIER le département dans lequel travaille l’employé TURNER. Dans le pire des cas, combien de lignes devez-vous parcourir pour la lecture ? Combien de lignes avez-vous dû modifier ? À quoi correspond ce dernier nombre ?

Exercice 12 : Dans quelle mesure cette nouvelle structure de données réduit-elle les redondances rencontrées dans la structure initiale à table unique ?

Synthèse

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

Interrogation des données : la difficulté à effectuer la requête est comptabilisée en nombre de lignes “lues” pour obtenir la réponse à la question posée.
Mise à jour des données : la difficulté à effectuer la mise à jour est comptabilisée en nombre de lignes concernées pour effectuer la modification.

Utilisez la légende suivante pour remplir votre tableau :

Légende	Symbole à utiliser
Situation de référence	●
Interrogation ou mise à jour plus compliquée	- et - -
Interrogation ou mise à jour plus simple	+ et ++

Exercice 13 : Remplissez chaque case en comparant l’évolution par rapport à la structure initiale à table unique :

	Interrogation des données	Mise à jour des données
Structure à table unique	●	●
Structure multi-tables
Justifiez :

Exercice 14 : Considérez un système de gestion des ressources humaines d’une grande entreprise. Quelle structure de données (table unique originale ou votre nouvelle structure multi-tables) serait la plus avantageuse pour cette application ? Expliquez votre choix en considérant des facteurs tels que l’intégrité des données, les performances, l’évolutivité et la facilité de développement.

Exercice 15 : Considérez une simple application de gestion de tâches personnelles pour usage individuel. Quelle structure de données (table unique originale ou votre nouvelle structure multi-tables) serait la plus avantageuse pour cette application ? Expliquez votre choix.

Testez vos connaissances

Cette activité vous a permis d’explorer les concepts fondamentaux du modèle relationnel et de l’intégrité des données. Vous avez appris à identifier les clés primaires et étrangères, comprendre les implications de la redondance des données, reconnaître les avantages de la décomposition des données et analyser l’impact de la structure des données sur les opérations de requête et de mise à jour.

Maintenant, testons votre compréhension des concepts que nous avons couverts dans cette activité. Essayez de répondre aux questions suivantes :

--- primary_color: steelblue secondary_color: lightgray text_color: black shuffle_questions: false shuffle_answers: true --- # Qu'est-ce qu'une clé candidate dans une base de données relationnelle ? 1. [x] Un ensemble minimal d'attributs qui identifie de manière unique chaque tuple dans une relation 2. [ ] N'importe quel attribut dans une relation 3. [ ] La clé primaire d'une relation 4. [ ] Une clé étrangère qui référence une autre relation # Laquelle des caractéristiques suivantes N'EST PAS une caractéristique d'une clé primaire ? 1. [ ] Elle doit être unique 2. [ ] Elle ne peut pas être nulle 3. [x] Elle doit être un attribut unique 4. [ ] Elle est choisie parmi les clés candidates # Quel problème la redondance des données peut-elle causer dans une base de données ? 1. [ ] Une amélioration des performances des requêtes 2. [x] Une incohérence lors de la mise à jour des données 3. [ ] Une meilleure sécurité des données 4. [ ] Une réduction des besoins en stockage # Dans une base de données relationnelle, quel est le but d'une clé étrangère ? 1. [ ] Identifier de manière unique un tuple dans sa propre relation 2. [x] Établir un lien entre deux relations 3. [ ] Crypter des données sensibles 4. [ ] Améliorer les performances des requêtes # Lequel des éléments suivants est un avantage de la décomposition des données en plusieurs relations ? 1. [ ] Elle simplifie toujours l'interrogation des données 2. [ ] Elle réduit toujours les besoins en stockage 3. [x] Elle peut réduire la redondance des données 4. [ ] Elle élimine le besoin de jointures dans les requêtes # Quand une structure à table unique pourrait-elle être préférable à une structure multi-tables ? 1. [ ] Pour les grandes bases de données d'entreprise 2. [ ] Quand l'intégrité des données est la priorité absolue 3. [x] Pour les applications simples avec des données et des relations limitées 4. [ ] Quand des requêtes complexes fréquentes sont nécessaires