Modèle relationnel

Introduction

Le modèle relationnel permet de passer d'un modèle conceptuel à une représentation des données dans des tables. La particularité du modèle relationnel est d'inclure les relations (par exemple les associations) directement dans les tables.

Les tables sont proches de la plupart des représentations des données dans des bases de données.

Concepts

Attributs et domaine des attributs

Dans une table, il s'agit du nom des colonnes.

Ces attributs ont un ensemble de valeurs possibles, appelé domaine. Le choix du domaine est laissé au concepteur.rice. Il doit permettre de représenter les données également dans le futur par exemple, mais limiter la mémoire nécessaire.

Exemples de différents attributs et de leur domaine:

attribut 1 : age est un entier positif (arbitrairement, on pourrait aussi utiliser des nombres à virgule, ou des nombres inférieurs à 253 ans)
attribut 2 : nom a un maximum de 250 caractères (la longueur est arbitraire, en partant du principe que la plupart des noms font moins de 250 lettres).

Exercice1

Dans l'image ci-contre, indiquer les attributs de cette relation (relation=table)

source de l'image: wikibooks

1) marque

2) couleur

3) plaque

Pour chacun des attributs de cette relation, quel est le domaine (abritraire)?

1) marque: le domaine est par exemple du texte, entre 2 et 50 caractères. On pourrait considérer entre 1 et 736 caractères également. Mais à priori, il y a au moins 1 ou 2 lettres et ce n'est pas une date ou des chiffres)

2) couleur: le domaine est par exemple du texte, entre 2 et 50 caractères. Comme précédemment, c'est arbitraire.

3) plaque: le domaine est par exemple du texte, entre 2 et 12 caractères. C'est abritraire encore une fois. Ici, il y a 8 éléments dans une plaque, mais le nombre ne peut qu'augmenter au fur et à mesure qu'il y a plus de voiture. Par ailleurs, les espaces sont déjà deux caractères.

Relation

Dans une table donnée, il s'agit de la table elle-même (ses "colonnes" attributs).

Si on a une table avec deux colonnes, elle représente d'une relation composée :

d'un attribut age
d'un attribut nom

On peut représenter, à l'écrit et si la relation a un nom, sous la forme:

NomDeLaRelation(attribut1, attribut2, ..., attributn)

Par exemple, nous aurons la relation suivante: Personne(age, nom)

Exercice2

Dans l'image ci-contre, comment définiriez-vous la relation présentée?

source de l'image: wikibooks

La table est une relation composée des attributs marque, couleur, et plaque.

On pourra donc écrire Voitures(marque, couleur, plaque)

Schéma de relation

Dans une table donnée, il s'agit du nom de la table et de la table elle-même (ses "colonnes" attributs) et du domaine des attributs.

Exemple de schéma de relation:

le schéma Participants est composée
- d'un attribut age (entier positif)
- d'un attribut nom (chaine de caractère entre 1 et 250 caractères)

Exercice3

Dans l'image ci-contre, quel est le schéma de relation ?

source de l'image: wikibooks

Le schéma de relation Voitures est composé des attributs marque (texte entre 2 et 50 caractères), couleur (texte entre 2 et 50 caractères), et plaque( texte entre 2 et 10 caractères)

Occurrence

Jusqu'ici, la "table" était "vide". Une occurence est une ligne de la table, en quelque sorte un enregistrement dans une table.

On appelle aussi n-uplet (qui vient de triplet, quarduplet, quintuplet, ... n-uplet) une occurence, car c'est une ligne avec n attribut renseigné. En anglais, de la même manière, on appelle une ligne une tuple ("t-uplet").

Exemples de plusieurs occurence dans le schéma de relation Participants:

32 "Lauren Thévin"
1 "Tal Maj"
89 "Vincent Lee"
19850 "Docteur Who"

Exercice4

Dans l'image ci-contre, quelles sont les occurences?

source de l'image: wikibooks

Les occurences sont :

1) Renault, bleu, 1233 DC 81

2) BMW, rouge, 1213 DC 95

3) Audi, orange, 2342 AC 66

4) Mercedes, argent, 1234 CD 88

Cardinalité d'une relation

Le nombre d'occurences (i.e. de lignes ou d'enregistrement) d'une relation est appellée cardinalité d'une relation.

Exemple :

Participants a, dans la section précédente, une cardinalité de 4 (car il y a 4 occurences)

Exercice5

Dans l'image ci-contre, quelle est la cardinalité de cette relation?

source de l'image: wikibooks

La cardinalité est 4, car il y a 4 occurrences

Clef

La clef est le contenu d'un attribut ou de plusieurs attributs qui serviront à identifier de manière unique une occurrence (i.e. une ligne).

Une clef peut être un ensemble d'attributs. Cela peut être le trio nom, date de naissance et lieu de naissance par exemple.

Cela doit être unique pour chaque occurrance (on ne pourra pas avoir deux occurrences la même clef).

Cela est extrêment important, car on pourra faire le lien dans d'autres tables en y mettant la clef de cette occurrence.

Exemple: dans Participants, je considère que le nom permet de retrouver de manière unique une occurrence.

implication 1: je ne pourrais pas avoir deux homonymes dans ma table
implication 2: on ne peut jamais avoir deux occurences strictements identiques, car elles différent au moins par la clef
implication 3: on peut créer un attribut supplémentaire qui sert seulement à faire une identifiant (par exemple un nombre qui augmente de 1 à chaque nouvelle occurence)
utilisation : dans un schema de relation InscritsExamen, je pourrais utiliser cette clef pour faire référence à l'ensemble des informations sur le participant en question. Par exemple, je pourrais mettre seulement "Lauren Thevin" pour faire référence à toute l'occurence correspondante. Comme cette clef est unique, on est sûr de retrouver l'occurence dans Participants.

Clef candidate

Une clef candidate est une clef primaire "potentielle". Elle pourra éventuellement être la clef primaire si on la choisi pour être une clef primaire (cf section suivante). Une clef candidate est techniquement le plus petit ensemble possible.

Une clef peut être un ensemble d'attributs. Cela peut être le trio nom, date de naissance et lieu de naissance par exemple. En revanche, si un identifiant tel que le numéro étudiant suffit, l'ensemble le plus petit sera la clef candidate et on ne choisira pas le trio mais le seul numéro étudiant.

Par exemple, imaginons qu'un produit dans un magasin peut être identifié de manière unique par son nom de produit ou par son code barre. Le nom du produit est une clef candidate, et le code barre est également une clef candidate.

Exercice6

Dans l'image ci-contre, quelle est ou quelles sont les clefs candidates?

source de l'image: wikibooks

La plaque d'immatriculation, qui est l'ensemble le plus petit permettant d'identifier une occurrence de Voitures de manière unique.

Clef primaire

La clef primaire est la clef candidate choisie pour être l'identifiant d'une occurrence dans une table. Par convention, on la souligne dans la table. Il n'y a qu'une seule clef candidate qui sera définie comme clef primaire.

Exercice7

Dans l'image ci-contre, quelle est la clef primaire?

source de l'image: wikibooks

La plaque d'immatriculation, car c'est la seule clef candidate

Clef étrangère

La clef étrangère est une clef primaire dans une autre table. Une clef étrangère permet de faire, de manière unique, référence à une occurence d'une autre table. Cela est intégrable dans votre schema de relation.

Par exemple, imaginons le schema de relation Etudiants avec un numéro d'étudiant, un age et un nom. Le numéro étudiant est la clef primaire de Etudiants. Cette table contient les occurrences suivantes:

Etudiants:

#123 32 "Lauren Thévin"
#61 1 "Tal Maj"
#987 89 "Vincent Lee"
#42 19850 "Docteur Who"

Imaginons un schema de relation InscritsExamen, qui lie les UEs et les inscrits aux examents.

Je pourrais utiliser la clef primaire de Etudiants pour faire référence à l'ensemble des informations sur les étudiants inscrits en question dans la table InscritsExamen. Par exemple, je pourrais mettre seulement #123 pour faire référence à toute l'occurence correspondante (Lauren Thevin, 32 ans). On aura ainsi InscritsExamen:

1 UE1 #123
2 UE1 #42
3 UE2 #42
4 UE2 #987
5 UE3 #123

Ces occurrences de InscritsExemen indiquent que:

Lauren Thevin est inscrite UE1
Docteur Who est inscrit.e UE1
Docteur Who est inscrit.e UE2
Vincent Lee est inscrit UE2
Lauren Thevin est inscrite UE3

Exercice8

Dans l'image ci-contre, si Renault est une clef étrangère, est-ce que la valeur Renault existe dans une autre relation? Developper

source de l'image: wikibooks

Oui, forcément, car cela veut dire que Renault est une clef primiaire dans une autre table. Une autre table existe, par exemple Marques, où le nom est la clef primaire. Renault, BMW, Audi et Mercedes seront donc également des clefs primaires de la table Marques.

Schéma relationnel

Le schéma relationnel est l'ensemble des schémas de relation. C'est à dire, l'ensemble de "tables" avec leur noms, leur attributs et leur domaine, sans le contenu (sans les occurrences).

Par exemple, l'ensemble Etudiants, InscritsExamen, et Enseignements avec leurs attributs (noms de colonnes) et leur domaine est un schéma relationnel.

Exercice9

Quel est le schéma relationnel du cas des exercices, en prenant en compte la correction de l'exercice 8 ?

source de l'image: wikibooks

Voitures est composé des attributs marque (texte entre 2 et 50 caractères), couleur (texte entre 2 et 50 caractères), et plaque( texte entre 2 et 10 caractères)
Marques avec le nom (texte entre 2 et 50 caractères)

Base de données relationnelle

L'ensemble des occurences pour le schéma relationnel est une base de données relationnelle. C'est à dire, quand les tables sont "remplies" avec des "enregistrements".

Exercice10

Quelle est la base de données relationnelle, à partir du schéma relationel de la question 9?

source de l'image: wikibooks

Voitures

marque couleur plaque

Renault, bleu, 1233 DC 81

BMW, rouge, 1213 DC 95

Audi, orange, 2342 AC 66

Mercedes, argent, 1234 CD 88

Marques

nom

Renault

BMW

Audi

Mercedes

Transformer un modèle conceptuel des données en modèle relationnel

Les types entités deviennent des relations (= des tables)

Chaque type entité devient une relation (c'est à dire une table). En attribut (c'est à dire en colonne), on trouve forcément tous les attributs du type entité du modèle conceptuel. La clef (soulignée dans le modèle conceptuel) sera la clef primaire dans le modèle relationnel.

Exercice11

Dans l'image ci-dessus, quelles sont les relations créées à partir des types-entités ?

source de l'image : https://ineumann.developpez.com/tutoriels/merise/initiation-merise/images/association_rediger.png

Auteur

id_a nom_a prenom_a date_naissance

id_a est souligné car c'est une clef primaire

Livre

id_l titre_l annee_l resume_l nb_chapitres_tot

id_a est souligné car c'est une clef primaire

Dans la plupart des cas, les types association deviennent des relations (= des tables)

Si chaque lien d'un type association peut avoir une cardinalité supérieure à 1 (ex: 0,2 ou 0,n ou 1,n, ou 1, 4), le type association devient une relation (c'est à dire une table). En attribut (c'est à dire en colonne), on trouve forcément tous les attributs du type association du modèle conceptuel, plus les clefs étrangères des types entités associées.

La clef primaire de cette association sera au choix et par ordre de priorité:

l'attribut du type association s'il existe et si c'est une clef dans le modèle conceptuel
la concaténation minimal des attributs (clefs étrangères, attribut du type association)
l'ajout d'un identifiant automatique

Exercice12

Dans l'image ci-dessus, quelles sont les relations créées à partir des types-associations ?

source de l'image : https://ineumann.developpez.com/tutoriels/merise/initiation-merise/images/association_rediger.png

Redaction

id_r nb chapitres id_a id_l

id_r est souligné car c'est une clef primaire

id_r est un identifiant automatique qui servira de clef (il est unique par conception)

Cas particulier: les types association deviennent des attributs de relations "type entité"

Si un lien d'un type association a une cardinalité maximale égale à 1 ( 0,1 ou 1,1), le type association devient un attribut. Le type entité "concerné" par la cardinalité maximale de 1 devient un attribut de l'autre type entité de la collection (clef étrangère).

Par exemple, si on a :

[TYPE-ENTITÉ-A] -- 0, n -- [TYPE-ASSOCIATION-ALPHA] -- 1,1 -- [TYPE-ENTITÉ-B]

Alors:

[TYPE-ENTITÉ-A] devient une relation avec ses attributs (c'est à dire une table).
[TYPE-ENTITÉ-B] devient une relation avec ses attributs (c'est à dire une table).
[TYPE-ASSOCIATION-ALPHA] ne devient PAS une relation car il y a une cardinalité 1,1 (concernant [TYPE-ENTITÉ-B] ) mais à la place:
- [TYPE-ENTITÉ-B] a en attribut la clef étrangère référant à la clef primaire d'une occurence de [TYPE-ENTITÉ-A]

Exercice13

Dans l'image ci-dessus, quelles sont les relations sont créées à partir des types-associations et des types-entités?

source de l'exercice : https://laurent-audibert.developpez.com/Cours-BD/images/fig3_1.png

Solution 1:

Citoyen(Num-Citoyen, Num-Candidat, Nom, Prénom, Adresse) ;
Candidat(Num-Candidat), Num-Citoyen, Parti)

On remarque que Num-Citoyen est unique dans Candidat, car un citoyen est 0 ou 1 fois candidat. On peut donc simplier le modèle comme ceci:

Solution 2:

Citoyen(Num-Citoyen, Num-Candidat, Nom, Prénom, Adresse) ;
Candidat(Num-Citoyen, Parti)

Pratique

Trouver un modèle conceptuel des données sur internet, et tranformer-le en schéma relationnel

Voici quelques modèles trouvés sur internet ci-après

Source: http://www.edu.upmc.fr/fsm/i02/j02/illustrations/mcd/consultationMedicale.PNG

Source: https://www.cours-gratuit.com/images/images/1797/id-1797-1.PNG

Correction modèle Medecin/Consulation/Patient/Médicament: toutes les étapes

Etape 1: toutes les types entités deviennent automatiquement des relations:

Medecin(matricule, nom, prénom)

Consultation(num-consultation, date-heure)

Medicament(codeSS, nomCommercial)

Patient(numSS, nom, prenom)

Etape 2: toutes les types associations dont les cardinalités sont supérieures à 1 deviennent automatiquement des relations, avec leurs attributs et les clefs étrangères des types entités:

Prescription(num-Prescription, posologie, num-consultation, codeSS)

Etape 3: les types entités dans des types associations dont les cardinalités sont maximum 1 deviennent des clefs étrangères de l'autre type entité:

Il y a un et un seul médecin par consultation. Chaque Consultation aura alors un attribut Medecin

Consultation devient: Consultation(num-consultation, date-heure, matricule)

De plus, il y a un et un seul patient par consultation. Chaque Consultation aura alors un attribut Patient

Consultation devient: Consultation(num-consultation, date-heure, matricule, numSS)

Résultat final des relations:

Medecin(matricule, nom, prénom)

Consultation(num-consultation, date-heure, matricule, numSS)

Medicament(codeSS, nomCommercial)

Patient(numSS, nom, prenom)

Prescription(num-Prescription, posologie, num-consultation, codeSS)

Pour finir il suffit d'ajouter les domaines de tous les attributs (arbitraires)

Medecin(matricule, nom, prénom) : matricule a au moins 10 chiffres, prénom et nom maximum 30 lettres

Consultation(num-consultation, date-heure, matricule, numSS): num consultation est un entier généré automatiquement, date-heure est une date, marticule a le même domaine que dans la table Medecin, et numSS a le même domaine que dans la table Patient

Medicament(codeSS, nomCommercial): codeSS est un code à 12 chiffres, nomCommercial a un maximum de 27 lettres

Patient(numSS, nom, prenom): numSS est un ensemble de 13 chiffres, prénom et nom maximum 30 lettres

Prescription(num-Prescription, posologie, num-consultation, codeSS): num-Prescription est un code à maximum 28 chiffres, posologie est un texte de maximum 500 caractères, num-consultation a le même domaine que dans la table Consultation, et codeSS a le même domaine que dans la table Medicament.

Correction modèle Medecin/Consulation/Patient/Médicament : seulement le résultat final

Medecin(matricule, nom, prénom) : matricule a au moins 10 chiffres, prénom et nom maximum 30 lettres

Medicament(codeSS, nomCommercial): codeSS est un code à 12 chiffres, nomCommercial a un maximum de 27 lettres

Patient(numSS, nom, prenom): numSS est un ensemble de 13 chiffres, prénom et nom maximum 30 lettres

Correction modèle Client/Photocopieur/Technicien/Date: toutes les étapes

Etape 1: toutes les types entités deviennent automatiquement des relations:

Client(n°clt, nom, prénom, Adresse, CP, ville)

Photocopieur(n°série, Marque, modèle, Date)

Date(date)

Technicien(Matricule, nom, prenom)

Etape 2: toutes les types associations dont les cardinalités sont supérieures à 1 deviennent automatiquement des relations, avec leurs attributs et les clefs étrangères des types entités:

Intervention(id-Intervention, Heure, Date, n°série, Matricule)

Etape 3: les types entités dans des types associations dont les cardinalités sont maximum 1 deviennent des clefs étrangères de l'autre type entité:

Il y a un et un seul client par qui loue un photocopieur donnée. Chaque Photocopieur aura alors un attribut faisant réfénce au Client

Photocopieur devient: Photocopieur(n°série, Marque, modèle, Date, n°clt)

Résultat final des relations:

Client(n°clt, nom, prénom, Adresse, CP, ville)

Photocopieur(n°série, Marque, modèle, Date, n°clt)

Date(date)

Technicien(Matricule, nom, prenom)

Intervention(id-Intervention, Heure, Date, n°série, Matricule)

Pour finir il suffit d'ajouter les domaines de tous les attributs (arbitraires)

Client(n°clt, nom, prénom, Adresse, CP, ville): un n°clt est un code à maximum 12 caractère, prénom et nom maximum 30 lettres, adresse maximum 60 caractère, CP maximum 10 catactères (pays étrangers), Ville a maximum 58 caractères (nom de ville le plus long du monde)

Photocopieur(n°série, Marque, modèle, Date, n°clt): n° de série est un code à 18 caractères maximul, Marque du texte avec maximul 14 elttres, modèle maximum 80 catactère, Date est une date, n°clt a le même domaine que dans la table Client

Date(date): Date est une date

Technicien(Matricule, nom, prenom): matricule a au moins 10 chiffres, prénom et nom maximum 30 lettres

Intervention(id-Intervention, Heure, Date, n°série, Matricule): id-Intervention a maximul 23 caractères, heure est une heure (hh-mm), date est une date, n°série a le même domaine que dans Photocopieur, et Matricule a le même domaine que dans Technicien

Correction modèle Client/Photocopieur/Technicien/Date: seulement le résultat final

Date(date): Date est une date

Technicien(Matricule, nom, prenom): matricule a au moins 10 chiffres, prénom et nom maximum 30 lettres

Source: https://www.editions-eni.fr/Open/download/19593b83-67a1-44a1-abf6-75acf32a10ff/images/16RI01N.png

Source: https://laurent-audibert.developpez.com/Cours-BD/images/fig3_3.png

Correction modèle Clients/Materiel/Intervention/Pièce: toutes les étapes

Etape 1: toutes les types entités deviennent automatiquement des relations:

Client(CodeClients, Nom, Prénom, Adresse, CP, ville, Téléphone, Mail)

Matériel(CodeMatériel, Désignation, Fabricant, Date d'achat)

Intervention(NumIntervention, Descriptif Panne, Date d'intervention, Temps Passé)

Pièce(Référence Pièce, Libellé, Prix)

Etape 2: toutes les types associations dont les cardinalités sont supérieures à 1 deviennent automatiquement des relations, avec leurs attributs et les clefs étrangères des types entités:

Utilisation(id-Utilisation, Qté, NumIntervention, Référence Pièce)

Etape 3: les types entités dans des types associations dont les cardinalités sont maximum 1 deviennent des clefs étrangères de l'autre type entité:

Il y a un et un seul client par qui possède un matériel donné. Chaque Materiel aura alors un attribut faisant réfénce au Client

Matériel devient: Matériel(CodeMatériel, Désignation, Fabricant, Date d'achat, CodeClient)

Il y a un et un seul matériel concerné à chaque intervention. Chaque Intervention aura alors un attribut faisant réfénce au Client

Résultat final des relations:

Client(n°clt, nom, prénom, Adresse, CP, ville)

Photocopieur(n°série, Marque, modèle, Date, n°clt)

Date(date)

Technicien(Matricule, nom, prenom)

Intervention(id-Intervention, Heure, Date, n°série, Matricule)

Pour finir il suffit d'ajouter les domaines de tous les attributs (arbitraires)

Date(date): Date est une date

Technicien(Matricule, nom, prenom): matricule a au moins 10 chiffres, prénom et nom maximum 30 lettres

Les formes normales

Les formes normales, en base de données relationnelles, sont des critères pour garantir la qualité des données.

Forme normale 1:

Les attributs sont atomiques. Si on enlève un attribut, on perd de l'information.

Exemple avec la relation suivante:

Personnes(nom de famille, age, lieu de naissance, date de naissance)

La base de données qui contient cette relation ne respecte PAS la forme normale 1, car on peut enlever age sans perdre d'information (on peut retouver l'age avec date de naissance).

Forme normale 2:

Forme normale 1: Les attributs sont atomiques.
La clef primaire est constituée d'un seul attribut, OU (si plusieurs attributs dans la clef) les attributs qui ne sont pas constitutifs de la clef primaires sont dépendants de l'ensemble des attributs de la clef primaire.

Forme normale 3:

Forme normale 1: Les attributs sont atomiques.
Forme normale 2: Forme normale 1 + La clef primaire est constituée d'un seul attribut, OU (si plusieurs attributs dans la clef) les attributs qui ne sont pas constitutifs de la clef primaires sont dépendants de l'ensemble des attributs de la clef primaire.
Toutes les attributs non-clef sont indépendants entre eux, et dépendent directement des valeurs de la clef primaire

Forme normale 3:

Forme normale 1: Les attributs sont atomiques.
Forme normale 2: Forme normale 1 + La clef primaire est constituée d'un seul attribut, OU (si plusieurs attributs dans la clef) les attributs qui ne sont pas constitutifs de la clef primaires sont dépendants de l'ensemble des attributs de la clef primaire.
Toutes les attributs non-clef sont indépendants entre eux, et dépendent directement des valeurs de la clef primaire

Page updated

Report abuse