TNSI : BDD - Le Modèle Relationnel

Contenus	Capacités Attendues	Commentaires
Modèle relationnel : relation, attribut, domaine, clef primaire, clef étrangère, schéma relationnel.	Identifier les concepts définissant le modèle relationnel.	Ces concepts permettent d’exprimer les contraintes d’intégrité (domaine, relation et référence).
Base de données relationnelle.	Savoir distinguer la structure d’une base de données de son contenu. Repérer des anomalies dans le schéma d’une base de données.	La structure est un ensemble de schémas relationnels qui respecte les contraintes du modèle relationnel. Les anomalies peuvent être des redondances de données ou des anomalies d’insertion, de suppression, de mise à jour. On privilégie la manipulation de données nombreuses et réalistes.

Représentation des Données en Tables/Relations

Le modèle relationnel consiste à représenter les données dans des tableaux, appelés Relations (ce qui donne son nom au modèle), ou Tables, ou encore Entités.

Enregistrements, Attributs & Champs

Def

Dans une relation / table :

• Chaque ligne d'une table est un enregistrement / un record , ou tuple ou t-uplet ou n-uplet ou vecteur
  • Il est interdit que deux enregistrements soient exactement identiques
  • Le nombre d'enregistrements d'une relation/table s'appelle le Cardinal de la relation/table
• Chaque colonne d'une table est appelée un un attribut / attribute
  • Le nombre de Colonnes d'une relation/table s'appelle le Degré de la relation/table
• Un Champ / Field est l'intersection d'un enregistrement avec un attribut : il s'agit donc d'une cellule particulière de la relation/table. Quelquefois néanmoins, par extension, le mot champ est utilisé à la place du mot attribut.

Site Internet de VOD - Vidéo à la Demande

Considérons par exemple un site de VOD, qui loue des films en ligne pour 24h, dont la base de données possède une Relation/Table film :

film
Code	Titre	Réalisateur	Durée	Date de Sortie	Note Spectateurs

Structure d'une Relation/Table film d'un Site de VOD

Enregistrements, Attributs & En-tête d'une Relation/Table

film
Code	Titre	Réalisateur	Durée	Date de Sortie	Note Spectateurs
142	Usual Suspects	Singer	1h46	19/07/1995	4.4
426	Pulp Fiction	Tarantino	2h29	26/12/1994	4.5
305	Lucy	Besson	1h29	6/08/2014	2.9
310	Inception	Nolan	2h28	21/07/2010	4.5
128	Her	Jonze	2h06	19/03/2014	4.1
54	Jason Bourne	Greengrass	2h04	10/08/2016	3.4
84	Un Homme à la Hauteur	Tirard	1h39	04/05/2016	3.0
63	Titanic	Cameron	3h14	07/01/1998	4.3
380	Le Nom de la Rose	Annaud	2h11	17/12/1986	4.2
650	Troie	Petersen	2h35	13/05/2004	3.6
290	Bright	Ayer	1h58	10/04/2020	3.1
589	Django Unchained	Tarantino	2h44	16/01/2013	4.5
740	Salt	Noyce	1h41	25/08/2010	2.9
170	Narco	Lellouche	1h45	1/12/2004	2.6
528	La Môme	Dahan	2h20	14/02/2007	3.8
137	La Liste de Schindler	Spielberg	3h15	02/03/1994	4.6
208	Le Parrain	Coppola	2h55	18/10/1972	4.5
700	Rain Man	Levinson	2h13	15/03/1989	4.3

Insertion de données dans la Relation/Table film

Un enregistrement de la table film est (par exemple):
(426, Pulp Fiction, Tarantino, 2h29, 26/12/1994, 4.5)

Domaine. Degré. Cardinal

Def

Dans une relation / table :

• Le Domaine d'un attribut est le type des données qu'il contient, parmi les plus fréquents :
  • entier souvent noté Int ou int
  • flottant souvent noté Float ou float,
  • chaîne de caractères souvent noté String ou str
  • booléen souvent noté Boolean ou bool
  • date souvent noté date ou Timestamp ou timestamp
  • etc...
• Le Degré d'une relation/table est le nombre de champs/attributs
• Le Cardinal d'une relation/table est le nombre d'enregistements

Site de VOD (suite)

film
Code	Titre	Réalisateur	Durée	Date de Sortie	Note Spectateurs
142	Usual Suspects	Singer	1h46	19/07/1995	4.4
426	Pulp Fiction	Tarantino	2h29	26/12/1994	4.5
...	...	...	...	...	...

Insertion de données dans la Relation/Table film du site VOD

• le domaine :
  • de l'attribut Code est int
  • de l'attribut Titre est str
  • de l'attribut Réalisateur est str
  • de l'attribut Durée est ... ça pourrait dépendre du logiciel de gestion de la base de données:
    • Soit il existe un type spécial pour les durées (TIME par exemple)
    • Soit on décide de stocker les durées en minutes (ce dont nous conviendrons par la suite), auquel cas ce champ serait un entier int.
  • de l'attribut Date de Sortie est Timestamp (un type spécifique pour les dates, encore une fois, en pratique cela pourrait dépendre du logiciel utilisé)
  • de l'attribut Note Spectateurs est Float
• Le degré de la relation utilisateur vaut \(6\), car la relation/table film dispose de \(6\) champs/attributs : Code, Titre, Réalisateur, Durée, Date de Sortie, Note Spectateurs

Schéma Relationnel

Def

Le schéma ou schema (relationnel) d'une relation/table est l'ensemble de :

• ses attributs et
• des domaines associés (=types de données)

du site VOD

Le schéma relationnel de (la table) film est (Code:str, Titre:str, Réalisateur:str, Durée:int, Date de Sortie:Timestamp, Note Spectateurs:Float)

Clés Primaires

Il est fréquent de souhaiter disposer dans une même table, d'un identifiant unique (id) pour chaque enregistrement :

Def

Une Clé Primaire d'une table est un attribut, ou bien un n-uplet d'attributs, qui permet d'identifier de manière unique (sans aucun risque d'ambigüité) tout enregistrement de la table.

Relation/Table des Personnes nées en France

Prénom	Nom	Ville de Naissance	Numéro de Sécurité Sociale
Laura	Breton	Marseille	2010845939026
Paul	Dubreuil	Brest	1021384257228
Leïla	Slimane	Lille	2991037214015
..	..	..	..

Relation/Table personne (née en France)

La clé primaire de la relation des personnes nées en France pourrait (par exemple) être leur numéro de Sécurité Sociale.

des clés primaires dans un schéma de table

film
Code	Titre	Réalisateur	Durée	Date de Sortie	Note Spectateurs
142	Usual Suspects	Singer	1h46	19/07/1995	4.4
426	Pulp Fiction	Tarantino	2h29	26/12/1994	4.5
...	...	...	...	...	...

Relation/Table utilisateur du site VOD

Dans un schéma relationnel (de table), pour signifier qu'un attribut (ou un tuple d'attributs) est la clé primaire de la table, on peut utiliser de manière équivalente (selon les auteurs et les exercices), les notations suivantes :

• on peut souligner le ou les attributs :
  • Schéma : film(Code:str, Titre:str, Réalisateur:str, Durée:int, Date de Sortie:Timestamp, Note Spectateurs:Float)
    Dans cette notation, l'attribut Code est la clé primaire
  • Schéma : film(Code:str, Titre:str, Réalisateur:str, Durée:int, Date de Sortie:Timestamp, Note Spectateurs:Float)
    Dans cette notation, le couple d'attributs (Code, Titre) est la clé primaire
• on peut mettre un astérisque * devant le ou les attributs :
  • Schéma : film(*Code:str, Titre:str, Réalisateur:str, Durée:int, Date de Sortie:Timestamp, Note Spectateurs:Float)
• on peut mettre le ou les attributs en gras :
  • Schéma : film(Code:str, Titre:str, Réalisateur:str, Durée:int, Date de Sortie:Timestamp, Note Spectateurs:Float)
• on peut mettre un émoji clé 🔑 (Ctrl+Shift+U +\(1F511\)) devant le ou les attributs :
  • Schéma : film(🔑Code:str, Titre:str, Réalisateur:str, Durée:int, Date de Sortie:Timestamp, Note Spectateurs:Float)
• On peut mettre un PK - Primary Key devant le ou les attributs
  • Schéma : film(PK Code:str, Titre:str, Réalisateur:str, Durée:int, Date de Sortie:Timestamp, Note Spectateurs:Float)

Choix de la Clé Primaire, et attribut id

• Tout attribut de la Table, ou tout n-uplet de plusieurs attributs de la table, peut être choisi comme clé primaire, dès lors qu'il identifie de manière unique tout enregistrement de la table.
• En pratique (souvent) Attribut id Lorsqu'aucun attribut de la relation ne se dégage très naturellement pour être choisi comme clé primaire, l'architecte de la base de données définit fréquemment mais non obligatoirent un attribut id, de domaine entier, comme clé primaire. Ou quelquefois id_nom_table. Lorsqu'un attribut id existe, et est le premier attribut de la table, il est sous-entendu, sauf mention contraire, et bien que ce ne soit pas le seul choix possible comme clé primaire, que c'est cet attribut id qui est choisi comme clé primaire de la table.
• En pratique (souvent) la clé primaire id est auto-incrémentée avec AUTOINCREMENTIl est fréquent également de déléguer la gestion automatique des numéros d'id au logiciel de gestion de base de donnée, par exemple avec un AUTOINCREMENT dans MySQL

Choix d'une clé primaire dans la relation/table film

• Le choix le plus naturel est de choisir l'attribut Code comme clé primaire de la table film

film
Code	Titre	Réalisateur	Durée	Date de Sortie	Note Spectateurs
142	Usual Suspects	Singer	1h46	19/07/1995	4.4
426	Pulp Fiction	Tarantino	2h29	26/12/1994	4.5
...	...	...	...	...	...

Relation/Table film avec une clé primaire Code

• D'autres choix sont possibles, par exemple:
  • le Couple (Code,Titre) peut être choisi comme clé primaire
  • le Couple (Code,Réalisateur) peut être choisi comme clé primaire
  • etc.. (ici, tout tuple incluant Code sera convenable)
  • aucun autre attribut unique ne conviendra (par exemple : un Réalisateur peut faire plusieurs films, donc l'attribut Réalisateur n'identifie pas de manière unique un enregistrement de la relation film)
  • on aurait également pu ajouter un premier attribut id

Clés primaires des relations utilisateur, location et acteur du Site de VOD (suite)

On décide que le site de VOD dispose des relations/tables supplémentaires suivantes: zone_géographique, utilisateur, location

Question 1°) Clé Primaire de la relation zone_géographiqueCorrection

Déterminer des clés primaires possibles pour la relation zone_géographique du Site VOD :

Col

zone géographique
id	nom
1	Castral-Roc
2	Essos
3	Port-Réal
4	Westeros
5	Winterfell

Relation/Table zone_géographique

Col

Credits : Sébastien Xavier & Julien Perreaut, Game of Thrones

Des choix possibles pour la clé primaire de la relation zone_géographique sont les suivants :

• l'attribut id est le choix le plus naturel de clé primaire de la relation zone_géographique
• l'attribut nom est un choix possible de clé primaire de la relation zone_géographique
• Le couple d'attributs (id, nom) est un choix possible de clé primaire de la relation zone_géographique

Question 2°) Clé Primaire de la relation utilisateurCorrection

Déterminer des clés primaires possibles pour la relation utilisateur du Site VOD :

utilisateur
id	prenom	nom	email	id_zone
70	Daenerys	TARGARYEN	daenerys.targaryen@gmail.com	2
45	Jon	SNOW	jon.snow@gmail.com	5
38	Cersei	LANNISTER	cersei.lannister@gmail.com	3
	...	...	...

Relation/Table utilisateur

Des choix possibles pour la clé primaire sont les suivants

• l'attribut id est le choix le plus naturel de clé primaire de la relation utilisateur
• l'attribut email est un choix possible de clé primaire de la relation utilisateur
• le couple (id, email) est un choix possible de clé primaire de la relation utilisateur
• le couple (id, prenom) est un choix possible de clé primaire de la relation utilisateur
• le couple (prenom, email) est un choix possible de clé primaire de la relation utilisateur
• etc..

Question 3°) Clé Primaire de la relation locationCorrection

Déterminer des clés primaires possibles pour la relation location du Site VOD :

location
id_utilisateur	date	Prénom	Nom	Titre	Réalisateur	Code
70	15/03/2022	Daenerys	TARGARYEN	Troie	Petersen	650
45	24/04/2022	Jon	SNOW	Lucy	Besson	305
70	18/01/2023	Daenerys	TARGARYEN	Bright	Ayer	290
...	...	...	...	...	...	...

Relation/Table location de films

Des choix possibles pour la clé primaire sont les suivants

• l'attribut id_utilisateur ne peut pas être choisi comme clé primaire, car un même utilisateur peut loueur plusieurs films sur le site
• l'attribut Code ne peut pas être choisi comme clé primaire, car un même (Code de) film peut être loué plusieurs fois sur le site
• l'attribut date ne peut pas être choisi comme clé primaire, car plusieurs films peuvent être loués le même jour sur le site
• l'attribut Prénom (resp. Nom) ne peut pas être choisi comme clé primaire, car plusieurs personnes portant le même Prénom (resp. Nom) peuvent louer des films sur le site est le choix le plus naturel de clé primaire de la relation acteur
• l'attribut Titre ne peut pas être choisi comme clé primaire, car un même (Titre de) film peut être loué plusieurs fois sur le site
• etc..
• le couple d'attributs (id_utilisateur, Code) ne peut pas être choisi comme clé primaire, car un même utilisateur peut louer plusieurs fois le même (Code de) film sur le site
• le couple d'attributs (date, Code) ne peut pas être choisi comme clé primaire, car un même (Code de) film peut louer plusieurs fois le même jour sur le site
• le 3-uplet d'attributs (id_utilisateur, date, Code) peut être choisi comme clé primaire, car un même (Code de) film ne peut être loué qu'une seule fois, le même jour, par un même utilisateur sur le site (nous avions dit que les locations de films duraient 24H...)

Clés Étrangères. Notion de Relation entre Tables

Nous avons vu que :

• l'attribut id_utilisateur n'est pas la clé primaire de la relation location précédente
• l'attribut id_utilisateur est la clé primaire de la relation utilisateur

On dit dans ce cas que id_utilisateur est une clé étrangère de la relation location, car elle fait référence à une clé primaire (id) d'une autre table/relation (utilisateur)

Clés Étrangères

• Une clé étrangère est un attribut dans une relation/table, qui fait référence à la clé primaire d'une autre relation/table.
• Une clé étrangère permet de mettre en relation un enregistement d'une table (appelée table fille) qui le contient, avec un enregistrement de la table (appelée table parent) auquel il fait référence
• Dans ce dernier cas, on dit qu'il existe une Relation entre ces deux Tables (entre la table fille et la table mère)

de Clé Étrangère dans la Table utilisateur

utilisateur
id	prenom	nom	email	id_zone
70	Daenerys	TARGARYEN	daenerys.targaryen@gmail.com	2
45	Jon	SNOW	jon.snow@gmail.com	5
38	Cersei	LANNISTER	cersei.lannister@gmail.com	3
...	...	...	...	...

Relation/Table utilisateur avec une clé primaire id
et une clé étrangère id_zone

On a mis en relation deux tables en considérant que l'attribut id_zone de la table utilisateur correspond à l'attribut id de la table utilisateur

des clés étrangères dans un schéma de table

Dans un schéma relationnel (de table), pour signifier qu'un attribut (ou un tuple d'attributs) est une clé étrangère d'une table, on utilise la notation suivante :

• on ajoute usuellement un hashtag # ou FK - Foreign Key devant le ou les attributs :
  • Schéma :
    • utilisateur(id_utilisateur, prenom, nom, email, #id_zone)
    • utilisateur(*id_utilisateur, prenom, nom, email, #id_zone)
    • utilisateur(🔑id_utilisateur, prenom, nom, email, #id_zone)
    • utilisateur(PK id_utilisateur, prenom, nom, email, FK id_zone)
      Dans cette notation, l'attribut id_zone est une clé étrangère
  • Schéma : location(#id_utilisateur, date, Prénom, Nom, Titre, Réalisateur, #Code)
    Dans cette notation :
    • l'attribut id_utilisateur est une clé étrangère de la relation/table (fille) location faisant référence à la clé primaire id de la relation/table (mère) utilisateur
    • l'attribut Code est une clé étrangère de la relation/table (fille) location faisant référence à la clé primaire Code de la relation/table (mère) film
    • Noter que la notation ne permet pas de lever l'ambigüité suivante: Cela pourrait signifier que le couple d'attributs (id_zone, type) est une clé étrangère d'une autre relation/table (ici, ce n'est pas le cas). Néanmoins, comme on tente souvent de choisir des clés primaires qui soient simples, cette situation d'ambigüité se produit rarement.

Interprétation d'un Enregistrement

• Daenerys TARGARYEN habite à Wessos, car son id_zone vaut 3
• Jon SNOW habite à Winterfell, car son id_zone vaut 5
• etc...

Redondance des Données

La relation location contient des informations qui sont déjà disponibles dans d'autres relations : on dit qu'elle est redondante, et c'est quelque chose qu'il faut éviter. À la fois pour des raisons d'espace de stockage mais aussi de cohérence : si une modification doit être faite (un utilisateur change de prénom, ou de nom de famille, etc..), cette modification ne doit être faite qu'à un seul endroit de notre base de données.

Une version non-redondante de la relation location serait donc celle-ci :

location
id_utilisateur	date	Code
70	15/03/2022	650
45	24/04/2022	305
70	18/01/2022	290
...	...	...

Relation/Table location de films, NON REDONDANTE

Contraintes d'Intégrité

Contrainte de Domaine

Tout attribut d'un enregistrement doit respecter le domaine indiqué dans le schéma relationnel.

Attention, certains domaines sont subtils. Par exemple, si une relation possède un attribut "Code Postal", le domaine de cet attribut devra être String plutôt que Entier . Dans le cas contraire, un enregistrement possédant le code postal 03150 serait converti en 3150 (car pour les entiers, 03150 = 3150). Or le code postal 3150 n'existe pas.

Contrainte de Relation

La contrainte de relation/table impose que tout enregistrement soit unique : cette contrainte est réalisée par l'existence obligatoire d'une clé primaire. Cette clé primaire est souvent créée de manière artificielle (voir id_utilisateur dans la table location ci-dessus par exemple).

Contrainte de Référence

La cohérence entre les différentes tables d'une base de données est assurée par les clés étrangères : dans une table, la valeur d'un attribut qui est clé étrangère doit obligatoirement pouvoir être retrouvée dans la table dont cet attribut est clé primaire. (la référence ne peut pas mener nulle part)

Par exemple, la relation location_v2 ci-dessous n'est pas valable :

location_v2
id_utilisateur	date	Code
70	15/03/2022	650
45	24/04/2022	305
70	18/01/2022	290
38	30/06/2022	387
...	...	...

Relation/Table location de films, Non redondante, mais Contrainte de Référence Non Respectée

En effet, la valeur 387 de l'attribut Code (clé étrangère de la table location_v2) ne correspond à aucun enregistrement dans la table film dont il est clé primaire. La relation location_v2 ne respecte donc pas la contrainte de référence, et provoquerait une erreur du SGBD.

film
Code	Titre	Réalisateur	Durée	Date de Sortie	Note Spectateurs
142	Usual Suspects	Singer	1h46	19/07/1995	4.4
426	Pulp Fiction	Tarantino	2h29	26/12/1994	4.5
305	Lucy	Besson	1h29	6/08/2014	2.9
310	Inception	Nolan	2h28	21/07/2010	4.5
128	Her	Jonze	2h06	19/03/2014	4.1
54	Jason Bourne	Greengrass	2h04	10/08/2016	3.4
84	Un Homme à la Hauteur	Tirard	1h39	04/05/2016	3.0
63	Titanic	Cameron	3h14	07/01/1998	4.3
380	Le Nom de la Rose	Annaud	2h11	17/12/1986	4.2
650	Troie	Petersen	2h35	13/05/2004	3.6
290	Bright	Ayer	1h58	10/04/2020	3.1
589	Django Unchained	Tarantino	2h44	16/01/2013	4.5
740	Salt	Noyce	1h41	25/08/2010	2.9
170	Narco	Lellouche	1h45	1/12/2004	2.6
528	La Môme	Dahan	2h20	14/02/2007	3.8
137	La Liste de Schindler	Spielberg	3h15	02/03/1994	4.6
208	Le Parrain	Coppola	2h55	18/10/1972	4.5
700	Rain Man	Levinson	2h13	15/03/1989	4.3

Relation/Table film

Bases De Données (Relationnelles)

Def

Une Base De Donnée (BDD) ou DataBase (DB) est un ensemble de tables, dont certaines peuvent avoir une relation entre elles.

Première Schématisation des Relations entre Tables

On peut schématiser simplement la relation existant entre les tables utilisateur et zone_géographique précédentes (utilisateur et abonnement) comme suit:

Col

classDiagram class utilisateur { 🔑 id prenom nom email 🔑 id_zone } class zone_géographique { 🔑 id_zone nom } utilisateur -- zone_géographique

certains logiciels notent 🔑 pour
les clés primaires et/ou les clés étrangères

Col

classDiagram class utilisateur { * id prenom nom email # id_zone } class zone_géographique { * id_zone nom } utilisateur -- zone_géographique

Autres Notations Possibles :
* Clé primaire ou PK (Primary Key) Clé primaire
# Clé étrangère ou FK (Foreign Key) Clé étrangère

Col

classDiagram class utilisateur { * id # id_zone ----------------- prenom nom email } class zone_géographique { * id_zone nom } utilisateur -- zone_géographique

Pour plus de clarté, il peut arriver
qu'on réunisse "en haut" de la table (préalablement et séparément)
la clé primaire et la/les clés étrangères :
nous ne le ferons pas.

Limites de cette Schématisation

La schématisation précédente, sous forme de Diagramme, est quelquefois suffisante pour modéliser la base de données. Du moins pour un premier jet. Il arrive néanmoins qu'elle trouve ses limites, car trop simpliste, en effet, il lui manque de prendre en compte les différents types de relations pouvant exister entre tables :

• Relation 1 to 1
• Relation 1 to n
• Relation n to n