Relations | Base de données

class: main-title

<h1>Base de données</h1>

<h2>Relations et jointures</h2>

---

<ul>
<li>Relations et multiplicités</li>
<li>Jointures</li>
</ul>

---
class: section-title

<h1>Relations</h1>

---

<h1 class="menu first">Relations</h1>

<p>Les SGBD relationnels possèdent un mécanisme, <em>clés primaires et clés étrangères</em>, permettant d'<strong>assurer l'intégrité des associations</strong> qui existent entre 2 tables.</p>

<ul>
<li>Regrouper les enregistrement de même structure dans une table et établir des liens avec les enregistrements d'autres tables</li>
<li>Éviter la répétition de l'information, on récupère les données reliées/complémentaires en parcourant les relations</li>
<li>La structure des données est séparées en entitées plus facile à comprendre, gérer et organiser</li>
</ul>

---

<h1 class="menu">Normalisation</h1>

<p>Une étape importante de conception d'une base de données relationnelle organisée est la <strong>normalisation</strong></p>

---

.sample-table tr:first-child td {
        border-bottom: 1px solid grey;
        padding-bottom: 8px;
    }
</style>

<h1 class="slide-only">Normalisation - Exemple</h1>

<p><strong>Une seule table</strong> <em>Recipes</em></p>

<table class="sample-table">
    <tr>
        <td>name</td>
        <td>desc</td>
        <td>ingr</td>
        <td>steps</td>
        <td>prep_time</td>
        <td>category</td>
        <td>cook_name</td>
        <td>cook_email</td>
        <td>cook_join_date</td>
    </tr>
    <tr>
        <td>a</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>james</td>
        <td>j@mail.ca</td>
        <td>YYYY-MM-DD</td>
    </tr>
    <tr>
        <td>b</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>james</td>
        <td>j@mail.ca</td>
        <td>YYYY-MM-DD</td>
    </tr>
    <tr>
        <td>c</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>mathieu</td>
        <td>m@mail.ca</td>
        <td>YYYY-MM-DD</td>
    </tr>
    <tr>
        <td>d</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>james</td>
        <td>j@mail.ca</td>
        <td>YYYY-MM-DD</td>
    </tr>
</table>

<p><br class="doc-only">
<strong>Devient</strong></p>

<p><em>Cooks</em><br></p>

<table class="sample-table">
    <tr>
        <td>id</td>
        <td>name</td>
        <td>email</td>
        <td>join_date</td>
    </tr>
    <tr>
        <td>1</td>
        <td>james</td>
        <td>j@mail.ca</td>
        <td>YYYY-MM-DD</td>
    </tr>
    <tr>
        <td>2</td>
        <td>mathieu</td>
        <td>m@mail.ca</td>
        <td>YYYY-MM-DD</td>
    </tr>
</table>

</div>

<p><em>Recipes</em><br></p>

<table class="sample-table">
    <tr>
        <td>name</td>
        <td>desc</td>
        <td>ingr</td>
        <td>steps</td>
        <td>prep_time</td>
        <td>category</td>
        <td>cook_id</td>
    </tr>
    <tr>
        <td>a</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>1</td>
    </tr>
    <tr>
        <td>b</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>1</td>
    </tr>
    <tr>
        <td>c</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>2</td>
    </tr>
    <tr>
        <td>d</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>...</td>
        <td>1</td>
    </tr>
</table>

</div>

---

<h1 class="slide-only">Relation 1-1</h1>

---

<h1 class="slide-only">Relation 1-*</h1>

---

<h1 class="slide-only">Relation *-*</h1>

---
class: section-title

<h1>Clés primaires et étrangères</h1>

---

<h1 class="menu">Clés primaires et étrangères</h1>

<p>Mécanisme permettant d'assurer l'<strong>intégrité relationnelle</strong></p>

<ul>
<li>Les liens, relations, entre les enregistrements sont exprimés explicitement et sont enforcés par le SGBD.</li>
<li>1 seule <code>PRIMARY KEY</code> par table</li>
<li>Possibilité de plusieurs <code>FOREIGN KEYS</code></li>
</ul>

<p>📚 <a href="https://mariadb.com/kb/en/getting-started-with-indexes/#primary-key" target="_blank" rel="noopener noreferrer">Primary key</a></p>

<p>📚 <a href="https://mariadb.com/kb/en/foreign-keys/" target="_blank" rel="noopener noreferrer">Foreign keys</a></p>

---

<h1 class="slide-only">Clé primaire</h1>

<p>Identifie de façon <strong>UNIQUE</strong> et <strong>NOT NULL</strong> chaque enregistrement par la valeur d'une <em>ou plusieurs</em> colonnes</p>

<pre><code class="language-sql">CREATE OR REPLACE TABLE teachers (
  -- Raccourci directement à la création de la colonne
  -- PRIMARY est optionnel, mais on précise pour être explicite
  employee_number INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL
);
-- DESCRIBE teachers; pour constater le NOT NULL ajouté automatiquement

CREATE OR REPLACE TABLE courses (
  code CHAR(10) NOT NULL,
  name VARCHAR(200) NOT NULL,
  teacher_employee_number INT UNSIGNED, 
  semester CHAR(5) NOT NULL,

-- Définition dédiée, permet de nommer PLUSIEURS colonnes: CLÉ COMPOSITE
  PRIMARY KEY (code, teacher_employee_number, semester)
);
</code></pre>

---

<h1 class="slide-only">CléS étrangèreS</h1>

<p><strong>Force</strong> un lien vers un enregistrement identifié par une <code>PRIMARY KEY</code></p>

<pre><code class="language-sql">CREATE OR REPLACE TABLE courses (
  code CHAR(10) NOT NULL,
  name VARCHAR(200) NOT NULL,
  semester CHAR(5) NOT NULL,
  teacher_employee_number INT UNSIGNED, -- Interet de mettre NOT NULL ???
  -- Le type des 2 colonnes doit correspondre
  FOREIGN KEY (teacher_employee_number) REFERENCES teachers (employee_number)
);

CREATE OR REPLACE TABLE grades (
  student_da VARCHAR(7),
  grade SMALLINT UNSIGNED NOT NULL,
  course_code CHAR(10) NOT NULL,
  course_teacher INT UNSIGNED NOT NULL,
  course_semester CHAR(5) NOT NULL,
  PRIMARY KEY (student_da, course_code, course_teacher, course_semester),
  FOREIGN KEY (course_code, course_teacher, course_semester) 
    REFERENCES courses (code, teacher_employee_number, semester)
  -- , FOREIGN KEY (student_da) REFERENCES students (da) -- On peut définir plusieurs FK
);
</code></pre>

---

<h1 class="slide-only">Cascade</h1>

<p>On peut préciser le comportement à implémenter lors de la modification de la <code>PRIMARY KEY</code> ou suppression d'un enregistrement parent</p>

<pre><code class="language-sql">FOREIGN KEY (col[, ...])
  REFERENCES table_name (col[, ...])
  [ON DELETE { RESTRICT | CASCADE | SET NULL }]
  [ON UPDATE { RESTRICT | CASCADE | SET NULL }]
</code></pre>

<ul>
<li><strong>RESTRICT</strong> Comportement par défaut, l'action est interdite et génère une erreur SQL</li>
<li><code>CASCADE</code> L'action est propagée. Les enfants sont supprimés, la mise à jour de <code>PRIMARY KEY</code> est répercutée</li>
<li><code>SET NULL</code> La valeur NULL est assignée à la <code>FOREIGN KEY</code></li>
</ul>

---
class: section-title

<h1>Jointures</h1>

---

<h1 class="menu">Jointures</h1>

<p>Permet de récupérer des données de plusieurs tables en <strong>exploitant les relations</strong> établies entres elles</p>

<pre><code class="language-sql">SELECT select_expr [, select_expr ...]
FROM table_ref [, table_ref ...]

table_ref:
    table_factor
  | CROSS JOIN
  | INNER JOIN table_factor ON conditional_expr
  | { LEFT | RIGHT } JOIN table_factor ON conditional_expr
</code></pre>

---
class: section-title

<p>👉  <a href=/sessions/2023A/0Q7/documentation/5.1-relations#base-de-donnees-de-demonstration-menu>Démarche</a></p>