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

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

2.
```sql
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 composition exacte de la PK peut varier selon le barème ; l’essentiel est les deux FK et l’unicité du prêt.)

3.
```sql
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 …,
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

1.
```sql
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.