CentraleSupélec — Département informatique
Plateau de Moulon
3 rue Joliot-Curie
F-91192 Gif-sur-Yvette cedex
Modèle physique d'une base de données relationnelle

Un modèle physique d'une base de données est dérivé d'un modèle logique en ajoutant :

  • un type à chaque colonne.
  • toute contrainte d'intégrité qu'un système de gestion de base de données (SGBD) doit appliquer aux données.

Contrairement au modèle logique, le modèle physique est lié à un SGBD choisi ; tous les types de colonnes et les contraintes d'intégrité doivent être reconnus par le SGBD choisi.


Qu'est-ce qu'un système de gestion de base de données (SGBD) ?

Un système de gestion de base de données (SGBD) est un logiciel utilisé pour créer, maintenir et fournir un accès contrôlé aux bases de données des utilisateurs (J.Hoffer, V. Ramesh, H. Topi, Modern database management). Un système de gestion de base de données relationnel (SGBDR) est un SGBD qui gère des bases de données relationnelles.

La plupart des SGBD sont des programmes exécutés sur un ordinateur et qui attendent des connexions d'autres applications sur un port déterminé. Dans cette configuration, le SGBD agit comme un serveur qui attend les requêtes des clients (les autres applications). C'est pour cette raison qu'ils sont appelés des SGBD client-serveur. Il est à noter que :

  • Les données sont stockées sur l'ordinateur où le SGBD s'exécute.
  • Les applications clientes ne s'exécutent généralement pas sur le même ordinateur que le SGBD.

Exemples de SGBD client-serveur : MySQL, Oracle DB, Microsoft SQL Server, PostgreSQL.

Au lieu de s'exécuter comme des applications, d'autres SGBD sont implémentés sous forme de bibliothèques logicielles. Il s'agit de collections de fonctions qu'une application peut appeler et exécuter. L'application gère directement la base de données par l'intermédiaire de la bibliothèque logicielle. En d'autres termes, le système de gestion de base de données est étroitement intégré (ou embarqué) dans l'application. C'est pourquoi ces SGBD sont appelés "embarqués". Il est à noter que les données sont stockées sur le même ordinateur que celui sur lequel l'application s'exécute.

Exemples de SGBD embarqués : SQLite, Apache Derby, HSQLDB.


👉 Dans notre cours, nous utilisons SQLite comme SGBD relationnel.

Types de colonnes

SQLite reconnaît les types de colonnes suivants :

  • INTEGER. La valeur est un entier signé, stocké dans 0, 1, 2, 3, 4, 6, ou 8 octets en fonction de la magnitude de la valeur.
  • REAL. La valeur est une valeur à virgule flottante, stockée sous la forme d'un nombre à virgule flottante IEEE de 8 octets.
  • TEXT. La valeur est une chaîne de caractères, stockée en utilisant le codage de la base de données (UTF-8, UTF-16BE ou UTF-16LE).
  • BLOB. La valeur est une séquence binaire.

Booléens

SQLite n'a pas de type spécifique pour les valeurs booléennes. Les valeurs booléennes sont stockées sous forme d'entiers : 0 signifie "faux", 1 signifie "vrai".

Dates et heures

SQLite n'a pas de type spécifique pour représenter les dates et les heures. Au lieu de cela, les date et les heures peuvent être stockées sous la forme d'un TEXTE, d'un REAL ou d'un INTEGER :

  • TEXT, en tant que chaîne ISO8601 ("YYYY-MM-DD HH:MM:SS.SSS").
  • REAL, indiquant le nombre de jours écoulés depuis midi à Greenwich le 24 novembre 4714 avant J.-C. selon le calendrier grégorien proleptique.
  • INTEGER, indiquant le nombre de secondes depuis 1970-01-01 00:00:00 UTC.

👉 Dans notre cours nous utiliserons la première option (TEXT).

👉 SQLite fournit des fonctions pour manipuler les dates et les heures. Cependant, nous ne les utiliserons pas dans notre cours. Au lieu de cela, nous lirons une date/heure à partir d'une base de données telle quelle, et nous la manipulerons avec des fonctions Python.

Contraintes d'intégrité

Lorsque nous créons une nouvelle table, nous pouvons spécifier une ou plusieurs contraintes pour chaque colonne. Les contraintes suivantes sont couramment utilisées :

  • PRIMARY KET. Identifie de manière unique chaque ligne d'une table. Les colonnes qui forment une clé primaire ne peuvent pas contenir de valeurs NULL.
  • NOT NULL. Une colonne ne peut pas avoir de valeur NULL.
  • UNIQUE. Toutes les valeurs d'une colonne sont différentes.
  • FOREIGN KEY. Préserve l'intégrité référentielle entre les tables.
  • CHECK. Les valeurs d'une colonne doivent satisfaire à une condition spécifique.
  • DEFAULT. Définit une valeur par défaut pour une colonne si aucune valeur n'est spécifiée.

Un exemple

Considérons le modèle logique que nous avons obtenu ici.


segment(seg_id, seg_color, x1, y1, x2, y2, label_id)
point(x, y, p_color, label_id)
label(label_id, label_text, font_size, font_color)
polygon(pol_id, label_id)
side(seg_id, pol_id, index)

Foreign keys:

  • segment(x1, y1) references point(x, y)
  • segment(x2, y2) references point(x, y)
  • segment(label_id) references label(label_id)
  • point(label_id) references label(label_id)
  • polygon(label_id) references label(label_id)
  • side(seg_id) references segment(seg_id)
  • side(pol_id) references polygon(pol_id)

Table label

Voici la définition de la table label. 

label (
  label_id REAL PRIMARY KEY, 
  label_text REAL NOT NULL, 
  font_size INTEGER CHECK(font_size > 0) DEFAULT 11, 
  font_color TEXT DEFAULT "#000000" CHECK (SUBSTR(font_color, 1, 1)='#')
)

Notez que :

  • Nous pouvons spécifier plus d'une contrainte d'intégrité pour une colonne (voir les colonnes font_size et font_color).
  • La contrainte CHECK prend en argument un prédicat.
  • Nous pouvons utiliser n'importe quelle fonction supportée par le SGBD dans la définition d'une contrainte CHECK (par exemple, la fonction SUBSTR). Vous pouvez trouver la liste des fonctions supportées par SQLite dans la documentation.
  • Pour des contraintes CHECK plus complexes, nous devrions définir nos propres fonctions. Cependant, SQLite ne supporte pas les fonctions définies par l'utilisateur par défaut. Les fonctions SQL définies par l'utilisateur ne sont pas au programme de ce cours.

Table point

Voici la définition de la table point. 

point (
  x REAL, 
  y REAL, 
  p_color TEXT DEFAULT "#000000" CHECK (SUBSTR(p_color, 1, 1)='#'),
  label_id INTEGER,
  PRIMARY KEY(x, y),
  FOREIGN KEY(label_id) REFERENCES label(label_id)
)

👉 Lorsque la clé primaire est composée de plus d'une colonne, nous devons la spécifier à la fin de la définition de la table.

👉 La colonne label_id de la table point est liée à la colonne label_id de la table label par une contrainte de clé étrangère.

  • Chaque valeur apparaissant dans la colonne label_id de la table point doit impérativement apparaître dans la colonne label_id de la table label. C'est ce qu'on appelle une contrainte d'intégrité référentielle. En d'autres termes, un point ne peut pas avoir une étiquette qui n'existe pas dans la table label.
  • D'autre part, la colonne label_id de la table label peut contenir des valeurs qui ne figurent pas nécessairement dans la colonne label_id de la table point. En d'autres termes, une étiquette n'est pas nécessairement associée à un point.

Table segment

Voici la définition de la table segment. 

segment (
   seg_id INTEGER PRIMARY KEY, 
   seg_color TEXT DEFAULT "#000000" CHECK (SUBSTR(seg_color, 1, 1)='#'), 
   x1 REAL NOT NULL, 
   y1 REAL NOT NULL, 
   x2 REAL NOT NULL, 
   y2 REAL NOT NULL, 
   label_id INTEGER,
   FOREIGN KEY label REFERENCES label(label_id),
   FOREIGN KEY (x1, y1) REFERENCES point(x, y) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY (x2, y2) REFERENCES point(x, y) ON DELETE CASCADE ON UPDATE CASCADE
)
  • Dans ce tableau, deux contraintes de clé étrangère lient des paires de colonnes. Ces contraintes garantissent qu'un segment ne peut pas avoir comme extrémité un point qui n'existe pas.
  • Les options ON DELETE et ON UPDATE indiquent comment le SGBD doit se comporter lorsqu'une valeur de la colonne x ou y de la table point est supprimée ou mise à jour.
  • ON DELETE CASCADE signifie que si un point est supprimé de la table point, tous les segments faisant référence à ce point sont également supprimés.
  • ON UPDATE CASCADE signifie que si la coordonnée x (respectivement, la coordonnée y) d'un point est mise à jour, les valeurs des colonnes x1 et x2 (respectivement, y1 et y2) faisant référence à ce point sont mises à jour en conséquence.
  • Si nous ne spécifions pas les options ON DELETE et ON UPDATE, le SGBD ne permettra pas la suppression ou la mise à jour d'un point.

Table polygon

Voici la définition de la table polygon. 

polygon (
  pol_id INTEGER PRIMARY KEY, 
  label_id INTEGER,
  FOREIGN KEY(label_id) REFERENCES label(label_id) 
)

Table side

Voici la définition de la table side. 

side (
  seg_id INTEGER, 
  pol_id INTEGER,
  index INTEGER NOT NULL, 
  PRIMARY KEY(seg_id, pol_id),
  FOREIGN KEY(seg_id) REFERENCES segment(seg_id),
  FOREIGN KEY(pol_id) REFERENCES polygon(pol_id),
)