Bases de Données Relationnelles: Concepts Clés

• Ce document porte sur le Modèle Logique de Données.
• Le cours est coordonné par Malika Charrad.
• L'adresse e-mail de Malika Charrad est [email protected].

Objectifs : Vers un schéma relationnel

• Un modèle de données définit un mode de représentation de l'information selon 3 composants: les structures de données, l'ensemble de contraintes et un ensemble d'opérations.
• Les structures de données définissent des règles à respecter par la base de données.
• L'ensemble d'opérations permet de manipuler les données, notamment en interrogation et en mise à jour.
• Les points 1 et 2 définissent le Langage de Définition des Données (LDD ou DDL) et permettent la description du schéma d'une BD.
• Le point 3 représente le Langage de Manipulation des Données (LMD ou DML), qui permet l'interrogation de la BD.
• Le modèle relationnel a été inventé par Codd en 1970 et repose sur la théorie ensembliste.
• Le modèle relationnel est l'étape précédant le modèle physique.
• Il est possible de transformer votre modèle conceptuel en modèle relationnel via un programme spécifique ou par vous-même.
• Il offre une totale indépendance entre les représentations logique et physique, en tenant compte de l'organisation des données.
• La simplicité du modèle relationnel réside dans l'utilisation de la notion de relation, où une relation est représentée sous forme de table.

MR : Relation, clé primaire et attributs

• Les données sont stockées dans des relations/tables, représentées par des tableaux à deux dimensions.
• Une relation/table est un ensemble de t-uples.
• Un t-uple est défini par un ou plusieurs attributs.

Exemple de table "Livre"

• ISBN : 9782738108395, Auteur : Samuel P. Huntington, Titre : Le choc des civilisations, Prix : 25.
• ISBN : 2-213-02191-0, Auteur : Nicolas Grimal, Titre : Histoire de l'Égypte ancienne, Prix : 42.
• ISBN : 2-89448-084-9, Auteur : Jacques Lacoursière, Titre : Histoire populaire du Québec, Prix : 15.

Éléments d'une table

• Le nom de la table est "LIVRE".
• Les attributs (colonnes) sont ISBN, Titre, et Prix.
• L'élément #NomAuteur est une clé étrangère, qui est la clé primaire d'une autre relation/table (Auteur).
• La clé primaire (ISBN) est en gras et soulignée.
• Clé secondaire (Titre) est en gras.

Clé Primaire (Identifiant de la relation)

• Une clé primaire est un sous-ensemble des attributs tel qu'il ne peut exister à aucun moment plus d'une ligne possédant les mêmes valeurs de ces attributs.
• La clé primaire est un identifiant minimal.
• Tout ensemble d'attributs dont une partie stricte est un identifiant est aussi un identifiant (mais non minimal).
• Plusieurs identifiants minimaux peuvent coexister dans une relation.
• Un attribut peut appartenir à plusieurs identifiants.
• Il est possible de calculer automatiquement les identifiants d'une relation.

Clé Secondaire (Identifiant secondaire)

• Dans la table Etudiant, un étudiant se défini grace à des identifiants.
• Dans Etudiant(NumEtudiant, CIN_Etudiant, PrenomEtudiant, NomEtudiant), NumEtudiant est la clé primaire, et CIN_Etudiant est la clé secondaire.

Production du Schéma Relationnel à partir du modèle E/A

• L'objectif est de produire, à partir du schéma conceptuel (modèle E/A), un schéma de base de données relationnel en Langage de Description des Données (LDD).
• Pour produire le schéma relationnel il faut traduire le schéma conceptuel sous forme de tables, de colonnes, de clés primaires et secondaires et de clés étrangères.

Règles de transformation

• Les différentes règles de transformation permettent de passer du modèle conceptuel au schéma relationnel.
• Une entité devient une relation (table).
• Chaque propriété de l'entité devient un attribut (colonne de la table).
• L'identifiant de l'entité devient la Clé Primaire de la relation.
• L'identifiant secondaire de l'entité devient la Clé Secondaire de la relation.

Exemple du MCD (Modèle Conceptuel de Données)

• Entité : Etudiant avec les attributs NumEtudiant, CIN_Etudiant, Nom_Etudiant, et Prenom_Etudiant.

Schéma Relationnel (MLD - Modèle Logique de Données)

• Etudiant = (NumEtudiant INT, CIN_Etudiant INT, Nom_Etudiant VARCHAR(50), Prenom_Etudiant VARCHAR(50)).

Relation binaire aux cardinalités (X,1)-(X,N) avec X=0 ou X=1

• La clé primaire de la table à la cardinalité (X,N) devient une Clé étrangère dans la table à la cardinalité (X,1).

Exemple

• Un employé a une et une seule société. Une société 1 ou N employés.
• Tables EMPLOYÉ et SOCIÉTÉ liées par "TRAVAILLER" :
• EMPLOYÉ(ID_EMPLOYE INT, NOM_EMPLOYE VARCHAR(50)), SOCIÉTÉ(ID_SOCIETE INT, NOM_SOCIETE VARCHAR(50)).
• Le modèle logique des données est : SOCIETE = (ID_SOCIETE INT, NOM_SOCIETE VARCHAR(50)); EMPLOYE = (ID_EMPLOYE INT, NOM_EMPLOYE VARCHAR(50), #ID_SOCIETE).
• Relation binaire aux cardinalités (X,N)-(X,N), avec X=0 ou X=1.
• Création d'une table supplémentaire ayant comme Clé Primaire une clé (Concaténation des deux clés primaires des deux tables en relations).
• Si la relation est porteuse de données, celles-ci deviennent des attributs pour la nouvelle table.

Exemple

• Une commande est composée de 0 ou N produits distincts en certaine quantité. Un produit est présent dans 0 ou N commandes en certaine quantité".
• Tables COMMANDE et PRODUIT liées par la relation "COMPOSER".
• COMMANDE(ID_COMMANDE, DATE_COMMANDE), et PRODUIT(ID_PRODUIT, LIBELLE), COMPOSER(QUANTITE).
• COMMANDE = (ID_COMMANDE INT, DATE_COMMANDE DATE); PRODUIT = (ID_PRODUIT INT, LIBELLE VARCHAR(50)); COMPOSER = (#ID_COMMANDE, #ID_PRODUIT, QUANTITE INT).

Relation n-aire (quelles que soient les cardinalités)

• Cela signifie qu'il faut créer une table supplémentaire ayant comme clé primaire la concaténation des identifiants des entités participants à la relation.

Exemple

• Un étudiant parle une ou plusieurs langues avec un certain niveau. Chaque langue est utilisée par 0 ou n étudiants avec un niveau. Pour chaque niveau, il y a 0 ou plusieurs étudiants qui parlent une langue.
• Tables ETUDIANT, NIVEAU, LANGUE liées par PARLER : ETUDIANT(ID_ETUDIANT, NOM_ETUDIANT), NIVEAU(ID_NIVEAU, NOM_NIVEAU), LANGUE(ID_LANGUE, Nom_LANGUE) et PARLER.
• ETUDIANT = (ID_ETUDIANT INT, NOM_ETUDIANT VARCHAR(50)); NIVEAU = (ID_NIVEAU INT, NOM_NIVEAU VARCHAR(50)); LANGUE = (ID_LANGUE VARCHAR(50), NOM_LANGUE VARCHAR(50)); PARLER = (#ID_ETUDIANT, #ID_NIVEAU, #ID_LANGUE).

Association réflexive

Premier cas : cardinalité (X,1) - (X,N), avec X=0 ou X=1

• La clé primaire de l'entité se dédouble et devient une clé étrangère dans la relation.

Exemple

• Table EMPLOYÉ se dirigeant elle même, EMPLOYÉ dirigeant une relation DIRIGER entre les enregistrements.
• EMPLOYÉ(ID_EMPLOYE, NOM_EMPLOYER).
• EMPLOYÉ = (ID_EMPLOYE INT, NOM_EMPLOYER VARCHAR(50), #ID_EMPLOYE_DIRECTEUR).

Deuxième cas: cardinalité (0,1) – (1,1)

• La clé primaire de la table à la cardinalité (0,1) devient une clé étrangère dans la table à la cardinalité (1,1).

Exemple

• Table PERSONNE se mariant elle même, PERSONNE peut SE MARIER

• PERSONNE(ID_PERSONNE, NOM_PERSONNE).

• PERSONNE = (ID_PERSONNE INT, NOM_PERSONNE VARCHAR(50), #ID_PERSONNE_PEUT).

• Cette transformation s'applique également dans le cas d'une relation binaire 0,1 → 1,1.

Troisième cas : cardinalité (X,N) – (X,N)

• Il faut créer une table supplémentaire ayant comme clé primaire la concaténation de deux clés étrangères.

• La première correspondant à la clé primaire de l'entité et la deuxième qui représente une duplication de la première.

Exemple

• Table PERSONNE : Personne avec les attributs (ID,Nom).

• Creation d'une table SE_MARIER

• PERSONNE = (ID_PERSONNE INT, Nom_Personne VARCHAR(50));

• SE_MARIER = (#ID_PERSONNE, #ID_PERSONNE_1);

Retour sur les associations n-aire

• Si on respecte les conseils donnés dans le cours MCD, une relation n-aire peut être transformée de telle sorte à ce qu'elle devienne une nouvelle entité.
• Dans ce cas, la transformation sera appliquée en utilisant IFN et 2FN.

Étude de Cas: Base de Données de Livres et d'Auteurs

• L'exercice consiste à modéliser une base de données pour une bibliothèque numérique, répertoriant les livres et leurs auteurs.
• La bibliothèque stocke des informations essentielles sur les livres (titre, année de publication) et les auteurs (nom, prénom, année de naissance). Elle doit permettre de savoir quels auteurs ont écrit quels livres.
• Chaque livre est associé à un auteur unique, et un auteur peut avoir écrit plusieurs livres.
• Une combinaison du nom et du prénom de l'auteur est utilisée pour identifier chaque auteur de manière unique.
• Chaque livre est identifié de manière unique par son ISBN.

Questions pour le TP

• Établir le MCD et le MLD (Modèle Logique de données).
• Écrire les requêtes SQL permettant de créer les tables, insérer des données et mettre à jour la table.

Ce quiz explore les concepts fondamentaux du modèle relationnel, y compris les contraintes d'intégrité référentielle et les clés primaires. Il aborde également l'impact des opérations CASCADE et la distinction entre les langages de définition et de manipulation de données. Testez vos connaissances sur la conception et la gestion des bases de données relationnelles.