### Cours sur la Création d'une Base de Données avec Contraintes
#### Introduction à SQL Server et SSMS
Microsoft SQL Server est un système de gestion de bases de données relationnelles (SGBDR) utilisé
pour stocker et gérer des données. SQL Server Management Studio (SSMS) est un outil graphique
pour interagir avec SQL Server.
#### Concepts Clés
1. **Base de données** : Un ensemble organisé de données.
2. **Table** : Une structure au sein de la base de données qui stocke les données en lignes et
colonnes.
3. **Contraintes** : Règles appliquées aux colonnes pour garantir l'intégrité des données.
#### Types de Contraintes
1. **PRIMARY KEY** : Une contrainte unique qui identifie de manière unique chaque ligne d'une
table.
2. **FOREIGN KEY** : Une contrainte qui assure la relation entre deux tables.
3. **NOT NULL** : Assure qu'une colonne ne contiendra pas de valeurs NULL.
4. **UNIQUE** : Garantit que toutes les valeurs d'une colonne sont distinctes.
5. **CHECK** : Assure que les valeurs d'une colonne répondent à une condition spécifique.
6. **DEFAULT** : Assigne une valeur par défaut à une colonne si aucune valeur n'est spécifiée.
#### Création d'une Base de Données avec SSMS
1. **Ouvrir SSMS** et se connecter à l'instance SQL Server.
2. **Créer une nouvelle base de données** :
- Cliquez avec le bouton droit sur "Bases de données" et sélectionnez "Nouvelle base de
données...".
- Nommez la base de données (par exemple, `ProductionDB`) et cliquez sur "OK".
#### Création de Tables avec Contraintes
##### Exemple de Base de Données de Production
**1. Création de la Table `Produits`**
```sql
USE ProductionDB;
GO
CREATE TABLE Produits (
ProduitID INT PRIMARY KEY, -- Clé primaire
Nom NVARCHAR(50) NOT NULL, -- Contrainte NOT NULL
Prix DECIMAL(10, 2) CHECK (Prix > 0) -- Contrainte CHECK
);
GO
```
**2. Création de la Table `Machines`**
```sql
USE ProductionDB;
GO
CREATE TABLE Machines (
MachineID INT PRIMARY KEY, -- Clé primaire
Nom NVARCHAR(50) NOT NULL, -- Contrainte NOT NULL
HeuresDisponibles INT CHECK (HeuresDisponibles >= 0) -- Contrainte CHECK
);
GO
```
**3. Création de la Table `Production`**
```sql
USE ProductionDB;
GO
CREATE TABLE Production (
ProductionID INT PRIMARY KEY, -- Clé primaire
ProduitID INT, -- Clé étrangère vers Produits
MachineID INT, -- Clé étrangère vers Machines
HeuresUtilisees INT CHECK (HeuresUtilisees >= 0), -- Contrainte CHECK
CONSTRAINT FK_Produit FOREIGN KEY (ProduitID) REFERENCES Produits(ProduitID),
CONSTRAINT FK_Machine FOREIGN KEY (MachineID) REFERENCES Machines(MachineID)
);
GO
```
#### Insertion de Données
```sql
-- Insérer des données dans Produits
INSERT INTO Produits (ProduitID, Nom, Prix)
VALUES
(1, 'ProduitA', 320.00),
(2, 'ProduitB', 500.00);
-- Insérer des données dans Machines
INSERT INTO Machines (MachineID, Nom, HeuresDisponibles)
VALUES
(1, 'Machine1', 300),
(2, 'Machine2', 500),
(3, 'Machine3', 200);
```
**Ajouter des entrées dans la table `Production`**
```sql
-- Ajout d'une entrée de production
INSERT INTO Production (ProductionID, ProduitID, MachineID, HeuresUtilisees)
VALUES
(1, 1, 1, 20), -- ProduitA utilise Machine1 pour 20 heures
(2, 1, 2, 50), -- ProduitA utilise Machine2 pour 50 heures
(3, 1, 3, 10), -- ProduitA utilise Machine3 pour 10 heures
(4, 2, 1, 30), -- ProduitB utilise Machine1 pour 30 heures
(5, 2, 2, 50), -- ProduitB utilise Machine2 pour 50 heures
(6, 2, 3, 40); -- ProduitB utilise Machine3 pour 40 heures
```
### Conclusion
Vous avez maintenant une base de données simple avec des contraintes qui assurent l'intégrité des
données. Vous pouvez étendre cette base de données avec des tables et des contraintes
supplémentaires selon les besoins spécifiques de votre application.
### Pratique avec un Exemple Complet
1. **Créer la base de données :**
```sql
CREATE DATABASE ProductionDB;
GO
```
2. **Créer les tables :**
```sql
USE ProductionDB;
GO
CREATE TABLE Produits (
ProduitID INT PRIMARY KEY,
Nom NVARCHAR(50) NOT NULL,
Prix DECIMAL(10, 2) CHECK (Prix > 0)
);
CREATE TABLE Machines (
MachineID INT PRIMARY KEY,
Nom NVARCHAR(50) NOT NULL,
HeuresDisponibles INT CHECK (HeuresDisponibles >= 0)
);
CREATE TABLE Production (
ProductionID INT PRIMARY KEY,
ProduitID INT,
MachineID INT,
HeuresUtilisees INT CHECK (HeuresUtilisees >= 0),
CONSTRAINT FK_Produit FOREIGN KEY (ProduitID) REFERENCES Produits(ProduitID),
CONSTRAINT FK_Machine FOREIGN KEY (MachineID) REFERENCES Machines(MachineID)
);
GO
```
3. **Insérer des données :**
```sql
INSERT INTO Produits (ProduitID, Nom, Prix)
VALUES
(1, 'ProduitA', 320.00),
(2, 'ProduitB', 500.00);
INSERT INTO Machines (MachineID, Nom, HeuresDisponibles)
VALUES
(1, 'Machine1', 300),
(2, 'Machine2', 500),
(3, 'Machine3', 200);
INSERT INTO Production (ProductionID, ProduitID, MachineID, HeuresUtilisees)
VALUES
(1, 1, 1, 20),
(2, 1, 2, 50),
(3, 1, 3, 10),
(4, 2, 1, 30),
(5, 2, 2, 50),
(6, 2, 3, 40);
```
### Révision des Données
Pour vérifier les heures disponibles restantes après allocation :
```sql
SELECT
m.MachineID,
m.Nom,
m.HeuresDisponibles - ISNULL(SUM(p.HeuresUtilisees), 0) AS HeuresRestantes
FROM Machines m
LEFT JOIN Production p ON m.MachineID = p.MachineID
GROUP BY m.MachineID, m.Nom, m.HeuresDisponibles;
```
Ce guide vous montre comment créer une base de données avec des contraintes en SQL Server. Vous
pouvez étendre cette structure pour répondre aux besoins de votre projet spécifique.
Bien sûr, je vais vous donner un autre exemple de base de données avec des contraintes. Prenons le
cas d'une base de données pour une bibliothèque, où nous voulons gérer les livres, les auteurs, et les
emprunts de livres par les membres.
### Création d'une Base de Données pour une Bibliothèque
#### Étape 1 : Créer la Base de Données
```sql
CREATE DATABASE BibliothequeDB;
GO
```
#### Étape 2 : Créer les Tables avec des Contraintes
##### Table `Auteurs`
```sql
USE BibliothequeDB;
GO
CREATE TABLE Auteurs (
AuteurID INT PRIMARY KEY, -- Contrainte de clé primaire
Nom NVARCHAR(50) NOT NULL, -- Contrainte NOT NULL
Prenom NVARCHAR(50) NOT NULL -- Contrainte NOT NULL
);
GO
```
##### Table `Livres`
```sql
USE BibliothequeDB;
GO
CREATE TABLE Livres (
LivreID INT PRIMARY KEY, -- Contrainte de clé primaire
Titre NVARCHAR(100) NOT NULL, -- Contrainte NOT NULL
AuteurID INT, -- Clé étrangère vers Auteurs
DatePublication DATE,
QuantiteDisponible INT CHECK (QuantiteDisponible >= 0), -- Contrainte CHECK
CONSTRAINT FK_Auteur FOREIGN KEY (AuteurID) REFERENCES Auteurs(AuteurID)
);
GO
```
##### Table `Membres`
```sql
USE BibliothequeDB;
GO
CREATE TABLE Membres (
MembreID INT PRIMARY KEY, -- Contrainte de clé primaire
Nom NVARCHAR(50) NOT NULL, -- Contrainte NOT NULL
Prenom NVARCHAR(50) NOT NULL, -- Contrainte NOT NULL
DateInscription DATE DEFAULT GETDATE(), -- Contrainte DEFAULT
Email NVARCHAR(100) UNIQUE -- Contrainte UNIQUE
);
GO
```
##### Table `Emprunts`
```sql
USE BibliothequeDB;
GO
CREATE TABLE Emprunts (
EmpruntID INT PRIMARY KEY, -- Contrainte de clé primaire
LivreID INT, -- Clé étrangère vers Livres
MembreID INT, -- Clé étrangère vers Membres
DateEmprunt DATE NOT NULL, -- Contrainte NOT NULL
DateRetour DATE,
CONSTRAINT FK_Livre FOREIGN KEY (LivreID) REFERENCES Livres(LivreID),
CONSTRAINT FK_Membre FOREIGN KEY (MembreID) REFERENCES Membres(MembreID)
);
GO
```
#### Étape 3 : Insérer des Données
##### Insérer des Données dans `Auteurs`
```sql
INSERT INTO Auteurs (AuteurID, Nom, Prenom)
VALUES
(1, 'Hugo', 'Victor'),
(2, 'Dumas', 'Alexandre'),
(3, 'Verne', 'Jules');
GO
```
##### Insérer des Données dans `Livres`
```sql
INSERT INTO Livres (LivreID, Titre, AuteurID, DatePublication, QuantiteDisponible)
VALUES
(1, 'Les Misérables', 1, '1862-04-03', 5),
(2, 'Le Comte de Monte-Cristo', 2, '1844-08-28', 3),
(3, 'Vingt Mille Lieues sous les mers', 3, '1870-06-20', 7);
GO
```
##### Insérer des Données dans `Membres`
```sql
INSERT INTO Membres (MembreID, Nom, Prenom, Email)
VALUES
GO
```
##### Insérer des Données dans `Emprunts`
```sql
INSERT INTO Emprunts (EmpruntID, LivreID, MembreID, DateEmprunt, DateRetour)
VALUES
(1, 1, 1, '2024-05-01', NULL), -- Jean Dupont emprunte Les Misérables
(2, 2, 2, '2024-05-02', '2024-05-20'), -- Claire Martin emprunte Le Comte de Monte-Cristo et l'a rendu
(3, 3, 3, '2024-05-03', NULL); -- Louis Bernard emprunte Vingt Mille Lieues sous les mers
GO
```
### Vérifications et Requêtes
#### Vérifier les Livres Disponibles
```sql
SELECT
L.LivreID,
L.Titre,
L.QuantiteDisponible - ISNULL(E.EnCours, 0) AS QuantiteRestante
FROM
Livres L
LEFT JOIN (
SELECT LivreID, COUNT(*) AS EnCours
FROM Emprunts
WHERE DateRetour IS NULL
GROUP BY LivreID
) E ON L.LivreID = E.LivreID;
GO
```
#### Requête pour Trouver les Emprunts en Cours
```sql
SELECT
E.EmpruntID,
M.Nom AS NomMembre,
M.Prenom AS PrenomMembre,
L.Titre AS TitreLivre,
E.DateEmprunt
FROM
Emprunts E
JOIN Membres M ON E.MembreID = M.MembreID
JOIN Livres L ON E.LivreID = L.LivreID
WHERE E.DateRetour IS NULL;
GO
```
### Conclusion
Vous avez maintenant un exemple complet d'une base de données pour une bibliothèque avec des
contraintes variées, incluant des clés primaires, des clés étrangères, des contraintes de vérification,
des contraintes d'unicité et des valeurs par défaut. Ce modèle peut être étendu et modifié selon les
besoins spécifiques de votre projet.
La commande SQL `CONSTRAINT FK_Auteur FOREIGN KEY (AuteurID) REFERENCES Auteur(AuteurID)`
sert à définir une contrainte de clé étrangère sur une table dans une base de données relationnelle.
Cette contrainte établit une relation entre deux tables en utilisant une clé étrangère.
Voici une explication détaillée de chaque élément de cette commande :
1. **CONSTRAINT FK_Auteur** :
- `CONSTRAINT` : Ce mot-clé est utilisé pour définir une contrainte sur une colonne ou un groupe de
colonnes.
- `FK_Auteur` : C'est le nom de la contrainte. Ce nom est choisi par le créateur de la table et il doit
être unique dans la base de données. Il est souvent utile de donner des noms significatifs aux
contraintes pour faciliter la compréhension et la maintenance de la base de données.
2. **FOREIGN KEY (AuteurID)** :
- `FOREIGN KEY` : Indique que la colonne spécifiée est une clé étrangère.
- `(AuteurID)` : La colonne `AuteurID` dans la table actuelle (la table où cette contrainte est définie)
est la clé étrangère. Cela signifie que les valeurs de cette colonne doivent correspondre aux valeurs
de la colonne de clé primaire d'une autre table.
3. **REFERENCES Auteur(AuteurID)** :
- `REFERENCES` : Ce mot-clé est utilisé pour spécifier la table et la colonne de clé primaire à laquelle
la clé étrangère fait référence.
- `Auteur(AuteurID)` : Indique que la colonne `AuteurID` de la table actuelle fait référence à la
colonne `AuteurID` de la table `Auteur`. Cela signifie que pour chaque valeur de `AuteurID` dans la
table actuelle, il doit y avoir une valeur correspondante dans la colonne `AuteurID` de la table
`Auteur`.
### Exemple Concret
Supposons que nous avons deux tables : `Livres` et `Auteurs`. La table `Auteurs` contient les
informations sur les auteurs, et la table `Livres` contient les informations sur les livres. Chaque livre
est écrit par un auteur, et nous voulons assurer l'intégrité de cette relation.
#### Table `Auteurs`
```sql
CREATE TABLE Auteurs (
AuteurID INT PRIMARY KEY,
Nom NVARCHAR(50) NOT NULL,
Prenom NVARCHAR(50) NOT NULL
);
```
#### Table `Livres` avec la Contrainte de Clé Étrangère
```sql
CREATE TABLE Livres (
LivreID INT PRIMARY KEY,
Titre NVARCHAR(100) NOT NULL,
AuteurID INT,
DatePublication DATE,
QuantiteDisponible INT CHECK (QuantiteDisponible >= 0),
CONSTRAINT FK_Auteur FOREIGN KEY (AuteurID) REFERENCES Auteurs(AuteurID)
);
```
### Explications
- **Table `Auteurs`** :
- `AuteurID` est la clé primaire de la table `Auteurs`. Cela signifie que chaque auteur aura un
identifiant unique.
- **Table `Livres`** :
- `LivreID` est la clé primaire de la table `Livres`.
- `AuteurID` est une clé étrangère. La contrainte `FOREIGN KEY (AuteurID) REFERENCES
Auteurs(AuteurID)` assure que chaque valeur de `AuteurID` dans la table `Livres` correspond à une
valeur existante de `AuteurID` dans la table `Auteurs`.
### Utilité
Cette contrainte garantit que :
- Vous ne pouvez pas insérer une valeur dans `AuteurID` de la table `Livres` qui ne correspond pas à
une `AuteurID` existante dans la table `Auteurs`.
- Si vous essayez de supprimer une ligne dans la table `Auteurs` qui est référencée dans la table
`Livres`, une erreur sera générée (sauf si vous configurez des actions spécifiques comme `ON DELETE
CASCADE` pour supprimer automatiquement les enregistrements associés).
### Exemple d'Insertion de Données
Si vous avez les deux tables créées comme indiqué ci-dessus, voici comment vous pourriez insérer
des données :
```sql
-- Insérer des données dans Auteurs
INSERT INTO Auteurs (AuteurID, Nom, Prenom)
VALUES (1, 'Hugo', 'Victor');
-- Insérer des données dans Livres
INSERT INTO Livres (LivreID, Titre, AuteurID, DatePublication, QuantiteDisponible)
VALUES (1, 'Les Misérables', 1, '1862-04-03', 5);
```
Dans cet exemple, vous ne pouvez pas insérer un livre avec `AuteurID` égal à 2 dans la table `Livres` si
`AuteurID` 2 n'existe pas dans la table `Auteurs`. Cela garantit l'intégrité référentielle entre les deux
tables.
