Requêtes pour la création et la mise à jour

Temps estimé de réalisation : 1h15

Objectifs

Question à laquelle cette activité va permettre de répondre :

  • Comment écrire un script SQL pour créer ou modifier une base de données ?

Objectifs pédagogiques visés :

  • Manipuler les mots-clefs CREATE TABLE et ALTER TABLE
  • Mettre à jour une base de données avec les mots-clefs INSERT et UPDATE
  • Identifier les clefs primaires et relationnelles et les définir correctement lors de la création (ou de la modification) d’une base de données

Connexion à la base de données

Consultez la page Accès base de données pour savoir comment accéder à la base de données.

Pour information, le script SQL utilisé pour créer la base de données de cette activité :

Script permettant la création de la bases de données

Avertissement

Pour les requêtes SQL utilisez la documentation suivante et aucune autre : https://docs.postgresql.fr/10/index.html (il s’agit de la bonne version pour la base de données installée sur JupyterHub)

Etude de cas : les cabinets médicaux

Dans ce TP nous allons nous intéresser à la conception d’une base de données pour gérer un réseau de cabinets médicaux. La première étape est de créer une table pour gérer le personnel non-soignant du réseau. A noter que chaque membre du personnel n’est affecté qu’à un seul cabinet.

CABINET (id, nom, adresse)
PERSONNEL (id, nom, prenom, fonction, cabinet)

Création d’une table

La syntaxe suivante permet de créer une table dont la clef primaire est id et dont attribut2 est une clef référentielle vers une autre table :

CREATE TABLE nom
(   
    id          type primary key,
    attribut1   type propriete_facultative,
    attribut2   type propriete_facultative REFERENCES table_referencee (attribut_table_referencee),
    ...
);

type peut être remplacé par :

propriete_facultative peut être remplacé par :

  • UNIQUE pour spécifier que la valeur ne peut pas se répéter
  • NOT NULL pour spécifier que la valeur ne peut pas être manquante.

Si (attribut_table_referencee) n’est pas spécifié, la clef référentielle pointera automatiquement sur la clef primaire de la table référencée.

Question 1 : Le schéma logique proposé dans le paragraphe « Étude de cas » ne permet pas d’avoir une base de données intègre. Pourquoi ?

Question 2 : Définissez les contraintes de clés primaires et de clés référentielles sur le schéma de la base de données pour corriger ce problème.

Question 3 : Écrivez les requêtes SQL permettant de créer les tables CABINET et PERSONNEL. Vous devrez au préalable créer un notebook et effectuer la connexion à la base de données.

Clefs composites

Définition

Clef composite

On dit qu’une clef est composite si elle est composée de plusieurs attributs.

CREATE TABLE nom_table
(   
    attribut1  type,
    attribut2  type,
    attribut3  type propriete_facultative,
    attribut4  type propriete_facultative,
    ...,
    primary key(attribut1, attribut2),
    foreign key (attribut3, attribut4)  references nom_table_referencee (attribut1_table_referencee, attribut2_table_referencee),
);

Insertion dans une table

La requête suivante permet d’insérer les valeurs de tous les attributs dans une ligne de la table :

INSERT INTO nom_table VALUES (valeur1, valeur2, valeur3);

Si vous ne désirez insérer que les valeurs de certaines colonnes utilisez la syntaxe :

INSERT INTO nom_table (attribut1, attribut3, attribut9) VALUES (valeur1, valeur3, valeur9);

Si pour la clef primaire vous avez spécifié le type serial, ajoutez la valeur DEFAULT dans la commande INSERT.

Question 4 : Créez un premier cabinet médical.

Question 5 : Insérez deux membres du personnel : M. Thomas Dupont (dont le poste n’est pas encore connu) et une secrétaire médicale Mme Elisabeth Aimée.

Suppression de lignes et destruction d’une table

DELETE FROM nom_table WHERE condition;

DROP TABLE nom_table;

Attention si la table à détruire est référencée, il faut tout d’abord détruire la ou les tables qui la référence.

Question 6 : Mme Aimée ne souhaite pas rester à l’issue de sa période d’essai. Supprimez-la de la base de données.

Mise à jour d’une ligne

UPDATE nom_table SET attribut1 = valeur1, attribut2 = valeur2, ...,
WHERE condition;

Question 7 : M. Dupont est affecté à un poste de brancardier. Mettez à jour la base de données.

Modification de la structure d’une table

Ajout d’un attribut

ALTER TABLE nom_table ADD COLUMN nom_colonne type contrainte;

Ajout d’une clef primaire

ALTER TABLE nom_table ADD CONSTRAINT nom_clef_PK PRIMARY KEY (attributs);

Ajout d’une clef référentielle

ALTER TABLE nom_table ADD CONSTRAINT nom_clef_FK
FOREIGN KEY (liste_attributs_clef_referentielle) REFERENCES nom_table_referencee(liste_attributs_clef_primaire);

Il est possible de supprimer une contrainte un utilisant le mot-clef DROP CONSTRAINT à la place de ADD CONSTRAINT.

  • Question 8 : Définissez la ou les clefs à ajouter à la table MEDECIN afin que les médecins puissent être affectés à un CABINET. Écrivez ensuite la requête SQL nécessaire pour modifier la table MEDECIN.

Vérification de l’intégrité des données

Question 9 : Pour une clef primaire, proposez une requête INSERT et une requête UPDATE qui testent le bon comportement du système en matière de contrainte d’intégrité.

Question 10 : Pour une clef référentielle, proposez une requête INSERT et une requête UPDATE qui testent le bon comportement du système en matière de contrainte d’intégrité.

Après avoir réalisé le TP vous pouvez consulter la correction.