Modèle relationnel : théorie

1. Conception d'une BD

Pour être utiles, les données d'une BD doivent être exactes, complètes et organisées de manière à pouvoir être récupérées en cas de besoin et dans le format souhaité. Aussi, lors de la conception, faut-il éviter :

• la duplication des données (redondance)
  
  • Elle peut engendrer un problème d'incohérence, notamment lors de la mise à jour (les données dupliquées doivent être identiques, ce qui n'est pas évident à tenir).
  • Elle consomme par ailleurs de l'espace mémoire.
• anomalie d’insertion (les données ont-elles le même nombre d’attributs ?)
• anomalie de suppression ;
• Les problèmes liés aux identifiants significatifs.
  Exemple : un identifiant client faisant référence au code postal (si le client change de commune, il faudra changer son identifiant.)

2. Vocabulaire des BD

Ci-après un extrait des données d'une entreprise commerciale nommée Bangoua-Enterprise.

2.1. Entités et leurs attributs

Dans le modèle relationnel, une BD est composé de tableaux à double entrée qui représentent chacun une entité de la BD.
Une relation ou table est la définition d'un tableau avec des colonnes (attributs) et des lignes (n-uplets. On dit aussi tuples ou enregistrements). La définition spécifie ce qui sera contenu dans chaque colonne de la table.
Propriétés d'une table

• Chaque table porte un nom unique.
• À l'intérieur de la table, le nom de chaque attribut est unique et désigne une colonne avec des propriétés spécifiques.
• Une table peut contenir un nombre quelconque d'attributs, l'ordre des colonnes dans la table est indifférent.
• L'un des attributs ou une combinaison d'attributs identifie de façon unique chaque tuple dans la table et sera la clé primaire.
• Une table peut contenir un nombre quelconque de tuples, l'ordre des tuples dans la table est indifférent.

2.2. Propriétés d'une colonne (attribut)

• nom
  Dans une table, chaque colonne est identifiée par un nom qui doit être unique.
  Mais, dans le même schéma de BD relationnelle, deux tables ou plus peuvent avoir des colonnes avec les mêmes noms.
  Lorsque le même nom de colonne apparaît dans plusieurs tables et que des tables contenant cette colonne sont utilisées dans la même opération de manipulation de données, pour éviter la confusion, on préfixe le nom de la colonne du nom de sa table et d'un point, comme dans client.idClient
• domaine
  Un domaine est l'ensemble des valeurs que peut prendre l'attribut. Par conséquent, les relations sont dites homogènes en colonne. On parle de contrainte de domaine.
  Un domaine peut, par exemple, être un type de données (entier, date, chaîne de caractères...), les valeurs comprises entre 1 800 € et 20 000 €.

2.3. Propriétés d'une ligne (n-uplet)

• Une seule valeur à l'intersection d'une colonne et d'une ligne : Une relation n'autorise pas les attributs à valeurs multiples.
• Unicité : Il n'y a pas de lignes dupliquée (en double) dans une relation.
• clé primaire
  Une clé primaire est une colonne ou une combinaison de colonnes avec une valeur qui identifie de manière unique chaque ligne.

Caractéristiques d'une bonne clé primaire

• Une clé primaire doit être unique. Elle permet d'identifier de manière unique chaque ligne d'une table.
• une clé primaire ne doit pas contenir la valeur NULL (contrainte d'intégrité d'entité).
• Une clé primaire doit être, de préférence, sans signification
Quelquefois, on rencontre une relation qui a deux ou plusieurs attributs (ou combinaisons d'attributs) qui peuvent servir de clé primaire. Chacune de ces clés primaires possibles est appelée clé candidate. On doit donc choisir la clé primaire parmi les clés candidates.
Certaines tables n'ont pas de colonne unique dans laquelle les valeurs ne se dupliquent jamais. Par conséquent, aucune colonne ne peut prétendre à elle seule servir de clé primaire de la table : la clé primaire est obtenue par concaténation d'attributs.
Une clé primaire concaténée doit être composée du plus petit nombre de colonnes nécessaire pour garantir l'unicité de la clé primaire.
Lorsqu'une table contient une colonne (ou une concaténation de colonnes) identique à la clé primaire d'une table de la BD, la colonne est appelée clé étrangère.
Exemple :
Dans la table commande, idClient est clé étrangère.
La correspondance des valeurs de clé étrangère avec les valeurs de clé primaire représente les relations de données dans une BD relationnelle.
En d’autres termes, la clé d’identification d’une table doit figurer dans toutes les tables auxquelles elle doit être reliée (contrainte d'intégrité relationnelle).

3. Représentation des entités et de leurs attributs : schéma relationnel

3.1. Représentations d'une relation

La relation fournisseur s'écrit : fournisseur(idFournisseur, raisonSociale, localité)
Le n-uplet (F3, Chocolatier de Dijon, Bruxelles) est une instance de la relation fournisseur.

3.2. Types d'association

Il existe trois types d'association de base :

• Association un à un (1:1)
  
• Association un à plusieurs (1:M)
• Plusieurs à plusieurs (M:N ou M:M)

En ce qui concerne la structure Bangoua - Enterprise :

• Un client peut passer plusieurs commandes, mais chaque commande est passée par un client.
  Donc entre la table client et la table commande, on a une relation (1:M).
• Une commande contient une ou plusieurs lignes (détails) de commande, mais chaque ligne de détailCommande est associée à une commande.
  Entre commande et détailCommande, on a une relation (1:M).
• Chaque ligne de détailCommande fait référence à un produit. Un produit peut se retrouver sur plusieurs lignes de détailCommande.
  Entre détailCommande et produit, on une a relation (1:M).
• Un fournisseur peut fournir de nombreux produits. Certains vendeurs ne proposent pas (encore ?) de produits. (Par exemple, une liste de fournisseurs peut inclure des fournisseurs potentiels).
  Si un produit est fourni par un fournisseur, ce produit est fourni par un seul fournisseur.
  Entre fournisseur et produit, on a une relation (1:M).
• Certains produits ne sont pas fournis par un fournisseur. (Par exemple, certains produits peuvent être fabriqués en interne ou achetés sur le marché libre.).

3.2. Schéma relationnel

Le schéma relationnel de la BD de Bangoua- Entreprise est le suivant :
client(idClient, civilité, nom, prénom, adresse, codePostal, commune, compte)
commande(idCom, *idClient, dateCom)
fournisseur(idFournisseur, raisonSociale, localité)
detailCom(*idCom, *idProduit, qtéCom)
produit(idProduit, *idFournisseur, désignation, prixUnitaire, qtéStock)
Les clés primaires sont soulignées, les clés étrangères sont précédées d'un astérisque.

Traduction par des diagrammes entité - association
a. Modèle de l'Ingénierie de l'Information (IE, ou "pieds d'oie")

3.3. Vues

un SGBD sert d'intermédiaire entre l'utilisateur et le stockage physique des données.
Le modèle de données relationnel inclut un moyen de fournir aux utilisateurs finaux leur propre fenêtre dans la base de données, une fenêtre qui masque les détails de la conception globale de la base de données et interdit l'accès direct aux tables de base : les vues.

4. Algèbre relationnelle

4.1. Projection

Une projection (SELECT en SQL) d'une relation est une nouvelle relation créée en copiant une ou plusieurs colonnes de la relation source dans une nouvelle table.
Exemple :

          SÉLECTIONNER LES COLONNES idClient, civilité, nom, prénom
          DE LA TABLE client
          On obtient la table suivante : 
        

4.2. Sélection

L'opération de sélection (WHERE en SQL) demande à un SGBD de choisir des lignes qui répondent à certains critères logiques.
Exemple :

          SÉLECTIONNER TOUTES LES LIGNES
          DE LA TABLE client
          OÙ civilité = "Madame"
          On obtient la table suivante : 
        

4.2. Projeter puis sélectionner (SELECT et WHERE)

Exemple :

            SÉLECTIONNER LES COLONNES idClient, civilité, nom, prénom
            DE LA TABLE client
            OÙ civilité = "Madame"
            On obtient la table suivante : 
          

4.3. Union

L'opération d'union crée une nouvelle table en plaçant toutes les lignes de deux tables source dans une seule table de résultats, en plaçant les lignes les unes au-dessus des autres.
Les deux tables source sur lesquelles l'union est effectuée doivent avoir des colonnes avec les mêmes types de données et tailles, dans le même ordre.

4.4. Produit ou jointure (JOIN ou CROSS JOIN en SQL)

Le produit est sans doute l'opération d'algèbre relationnelle la plus utile, car elle combine deux tables, voire plus en une seule, généralement via une relation clé primaire - clé étrangère.
Il génère toutes les paires de lignes possibles à partir de deux tables. Ainsi, si une table a six lignes et l'autre table a trois lignes, le PRODUIT donne une liste composée de 6 × 3 = 18 lignes.
Il s'agit en fait du produit cartésien, en mathématiques.
Dans la pratique, le produit est toujours accompagné d'une condition.

4.4.1. Équi-jointure ou jointure naturelle (NATURAL JOIN ou INNER JOIN)

Elle forme de nouvelles lignes lorsque les données des deux tables source correspondent.
Exemple :
Considérons les tables suivantes :

4.4.2. θ-Jointure

Une équi-jointure est un exemple spécifique d'une classe plus générale de jointure connue sous le nom de Θ-jointure (thêta-jointure). Une Θ-jointure combine deux tables sous certaines conditions, qui peuvent être l'égalité ou autre chose.

Jointures externes

Une jointure externe est une jointure qui inclut des lignes dans une table de résultats même s'il peut ne pas y avoir de correspondance entre les lignes des deux tables jointes. Partout où le SGBD ne peut pas faire correspondre les lignes, il place des valeurs NULL dans les colonnes pour lesquelles aucune donnée n'existe.
Jointure externe gauche (LEFT OUTER JOIN)
La jointure externe gauche inclut toutes les lignes de la première table dans l'expression de jointure y compris celles qui n'ont pas de valeur correspondante dans la deuxième table.

4.5. Différence

Parmi les requêtes de base de données les plus puissantes figurent celles formulées par la négative, telles que « montrez-moi tous les clients qui n'ont pas acheté chez nous en 2021 ».
Cette opération récupère toutes les lignes qui se trouvent dans la table client mais pas dans commande en 2021.

          client MOINS commande en 2021.
        

4.6. Intersection

L'opération d'intersection produit un résultat contenant toutes les lignes qui apparaissent dans les deux relations. L'intersection ne peut donc être effectuée que sur deux relations union-compatibles.