BD-CBases de données et SQL

CREATE TABLE et ALTER — contraintes d’une bibliothèque

Idée directrice

Écriture de commandes SQL LDD : CREATE TABLE avec clé primaire / clé étrangère, puis ALTER TABLE pour ajouter une colonne. Transposition textuelle du devoir bibliothèque i-s1-04 (sans recopier le barème).

Signature de reconnaissance — l'énoncé se trahit ainsi

« Créer les tables »

« Ajouter la colonne »

« en maintenant les intégrités référentielles »

Sujet principal

Exercice — dans l'esprit des sujets du bac

3 questionsCorrigé masqué

On reprend le schéma : LIVRES (NumLV, Titre, NomAuteur, ValeurLV, NumED#) et PRET (NumLV#, NumLE#, DateP, DateRet), avec EDITEURS et LECTEURS déjà créées.

Écrire la commande SQL CREATE TABLE de LIVRES en imposant : NumLV clé primaire numérique, Titre chaîne obligatoire, NumED clé étrangère vers EDITEURS(NumED).
Écrire le CREATE TABLE de PRET en maintenant les intégrités référentielles vers LIVRES et LECTEURS.
Écrire l’ALTER TABLE qui ajoute à LIVRES la colonne NBpage (numérique, 3 chiffres, obligatoire).

Voir la correction commentéeAprès avoir posé votre démarche

CREATE TABLE LIVRES (
  NumLV NUMERIC(6) PRIMARY KEY,
  Titre VARCHAR(25) NOT NULL,
  NomAuteur VARCHAR(25),
  ValeurLV NUMERIC(6,3),
  NumED NUMERIC(6),
  CONSTRAINT fk_liv_ed FOREIGN KEY (NumED) REFERENCES EDITEURS(NumED)
);

CREATE TABLE PRET (
  NumLV NUMERIC(6),
  NumLE NUMERIC(6),
  DateP DATE,
  DateRet DATE,
  PRIMARY KEY (NumLV, NumLE, DateP),
  FOREIGN KEY (NumLV) REFERENCES LIVRES(NumLV),
  FOREIGN KEY (NumLE) REFERENCES LECTEURS(NumLE)
);

La clé primaire retenue est (NumLV, NumLE, DateP) : deux prêts du même livre au même lecteur ne peuvent se distinguer que par leur date.

ALTER TABLE LIVRES ADD NBpage NUMERIC(3) NOT NULL;

\boxed{\text{CREATE + FK + ALTER ADD}}

Exercices d'entraînement — une nuance à la fois

Entraînement 01 / 02

Drill BD-C.1 — CREATE patient / ordonnance

3 questionsCorrigé masqué

Tables d’un cabinet : patient (numero PK, Nom, …) et ordonnance (code PK, numero# → patient, Matricule# → medecin, …).

Écrire le CREATE TABLE de patient avec numero entier en clé primaire.
Écrire le CREATE TABLE d’ordonnance avec les deux clés étrangères.
Écrire l’ALTER qui ajoute adresse (chaîne) à patient.

Voir la correction commentéeAprès avoir posé votre démarche

1. CREATE TABLE patient (numero INT PRIMARY KEY, Nom VARCHAR(30), …);

2. `CREATE TABLE ordonnance (
code VARCHAR(10) PRIMARY KEY,
numero INT,
Matricule VARCHAR(10),
FOREIGN KEY (numero) REFERENCES patient(numero),
FOREIGN KEY (Matricule) REFERENCES medecin(Matricule)
);`

3. ALTER TABLE patient ADD adresse VARCHAR(50); $\boxed{\text{CREATE patient/ordonnance + ALTER}}$

Entraînement 02 / 02

Drill BD-C.2 — CREATE Fichiers avec FK type

2 questionsCorrigé masqué

Table Types (Type CHAR(1) PK, Designat VARCHAR(25)) déjà créée. Table Fichiers : CodeF PK chaîne 6, TypeF FK vers Types, Taille numérique, Datcreat date obligatoire.

Écrire le CREATE TABLE Fichiers en maintenant l’intégrité référentielle sur le type.
Que se passe-t-il si l’on insère un fichier avec TypeF = 'X' alors que 'X' est absent de Types ?

Voir la correction commentéeAprès avoir posé votre démarche

CREATE TABLE Fichiers (
  CodeF VARCHAR(6) PRIMARY KEY,
  NomF VARCHAR(25),
  Extension CHAR(4),
  TypeF CHAR(1),
  Taille NUMERIC(4,2),
  Datcreat DATE NOT NULL,
  FOREIGN KEY (TypeF) REFERENCES Types(Type)
);

2. L’insertion est rejetée : violation de l’intégrité référentielle (FK vers une clé primaire inexistante). $\boxed{\text{FK TypeF \to Types ; insert orphelin refusé}}$

Méthode / Automatismes

CREATE TABLE : colonnes + types + PRIMARY KEY + FOREIGN KEY … REFERENCES.
ALTER TABLE … ADD pour une colonne nouvelle ; DROP/MODIFY selon le dialecte du sujet.
Toujours créer d’abord les tables mères (EDITEURS, LECTEURS) avant les filles.

Pièges classiques

Créer PRET avant LIVRES (échec de la FK).
Oublier NOT NULL sur une colonne « obligatoire » du cahier des charges.
Mettre la FK dans EDITEURS au lieu de LIVRES.