TD 1 : Langage de Définition de Données (LDD)

Énoncé

Une entreprise de gestion des réservations de vacances en Tunisie utilise une base de données pour enregistrer les informations relatives aux clients, hôtels, et réservations. La représentation textuelle de cette base est la suivante :

Client(Email, NomClient, Téléphone, Pays)
Hotel (IDHotel, NomHotel, Ville, Classification, Capacité)
Réversation (EmailClient#, IDHotel#, Date, NombreNuitée, Montant)

Légende : Clé primaire | Clé étrangère

La base de données doit aussi respecter les règles de gestions suivantes :

R1 : L'email d'un client doit être une adresse email valide nom@serveur.domaine
R2 : chaque client doit avoir un numéro de téléphone non vide et unique
R3 : La capacité minimale d'un hôtel est 50
R4 : Le nombre des nuitées dans une réservation varie entre 1 et 30
R5 : Un client peut réserver dans le même hôtel dans des périodes différentes

Lors de la création de cette base de données, l'administrateur a oublié plusieurs contraintes. Cependant, la base contient plusieurs anomalies qui affectent son intégrité et sa fiabilité. Des extraits de données de cette base sont décrits dans les tableaux suivants :

Table Client

Email	NomClient	Téléphone	Pays
ali@gmail.com	Ali Ben Ali	+216 22 022 516	Tunisie
nadia.t@gmail.com	Nadia Trabelsi	+33 2 12 34 56 78	France
ali.benali@gmail.com	Ali Ben Ali	NULL	Maroc
amine.com@topnet	Amine Jabli	+216 22 022 516	Tunisie
fatma.lamine@gmail.com	Fatma Lamine	+213 21 457 689	Algérie

Table Hotel

IDHotel	NomHotel	Ville	Classification	Capacité
1	Hotel El Mouradi	Hammamet	5	200
2	Hotel Le Sultan	Tunis	4	150
2	Hotel SdraBaal	Sousse	4	100
4	Hotel El Mouradi	Hammamet	5	200
5	Hotel Sahara Beach	Monastir	3	45

Table Réservation

EmailClient	IDHotel	Date	NombreNuitée	Montant
ali@gmail.com	1	2024-06-01	6	1200
fatma.lamine@gmail.com	2	2024-07-05	1	800
ali.benali@gmail.com	1	2024-08-15	10	1500
ali.benali@gmail.com	4	2024-06-15	3	10
ali@gmail.com	6	2024-09-01	100	1000

Liste des Questions

Question 1

La colonne idHotel de la table hotel présente une anomalie.

a. Identifier le type d'anomalie.

b. Proposez une requête SQL permettant d'éviter cette anomalie.

Correction

a. L'anomalie est la duplication de la valeur de la clé primaire (IDHotel = 2), ce qui viole le principe d'unicité.

b. Pour corriger cela , on peut utiliser une requête ALTER TABLE pour ajouter une contrainte de clé primaire .

SQL

ALTER TABLE Hotel
ADD CONSTRAINT pk_hotel PRIMARY KEY (IDHotel);

Question 2

Le champs email ne respecte pas une règle de gestion.

a. Identifiez la règle concernée.

b. Proposez une requête SQL permettant de forcer la règle.

Correction

a. La règle R1 (L'email doit être une adresse valide) n'est pas respectée par l'enregistrement "amine.com@topnet".

b. On ajoute une contrainte CHECK pour valider le format.

SQL

ALTER TABLE Client
ADD CONSTRAINT chk_email_format CHECK (Email LIKE '_%@__%.__%');

Question 3

La règle R2 n'est pas respectée dans cette implémentation.

a. Identifier les enregistrements qui montrent que cette règle n'est pas respectée.

b. Ecrivez une requête SQL permettant de forcer la règle de gestion R2.

Correction

a. Les enregistrements "ali@gmail.com" et "amine.com@topnet" ont le même téléphone (non-unicité). L'enregistrement "ali.benali@gmail.com" a un téléphone NULL (non-vide).

b. On modifie la colonne pour ajouter les contraintes NOT NULL et UNIQUE.

SQL

ALTER TABLE Client
MODIFY COLUMN Téléphone VARCHAR(20) NOT NULL UNIQUE;

Question 4

Lors de l'insertion de la nouvelle réservation décrite ci-dessous, le système affiche un message d'erreur.

EmailClient: ali@gmail.com, ID_Hotel: 6, Date_Debut: 2024-09-15, NombreNuitée: 5, Montant: 800

a. Identifiez la règle de gestion non respectée.

b. Proposez une nouvelle description de la table réservation afin de respecter la règle identifiée.

c. Donnez une requête SQL permettant de supprimer la table réservation et la récréer selon la nouvelle description.

Correction

a. La règle non respectée est R5 : "Un client peut réserver dans le même hôtel dans des périodes différentes". La structure actuelle de la clé primaire ne le permet pas.

b. La nouvelle description de la table avec une clé primaire composée pour respecter R5 est :

Réversation (EmailClient#, IDHotel#, Date, NombreNuitée, Montant)

c. Les requêtes pour recréer la table sont :

SQL

DROP TABLE Réservation;

CREATE TABLE Réservation (
    EmailClient VARCHAR(100),
    IDHotel INT,
    Date DATE,
    NombreNuitée INT,
    Montant DECIMAL(10, 2),
    PRIMARY KEY (EmailClient, IDHotel, Date),
    FOREIGN KEY (EmailClient) REFERENCES Client(Email),
    FOREIGN KEY (IDHotel) REFERENCES Hotel(IDHotel)
);

Question 5

Dans la table des réservations, une contrainte d’intégrité référentielle n’a pas été définie.

a. Identifiez l’enregistrement qui viole cette contrainte d’intégrité référentielle.

b. Écrivez une requête SQL permettant de corriger cette anomalie et d’empêcher l’insertion de tels enregistrements à l'avenir.

Correction

a. L'enregistrement avec IDHotel = 6 (pour ali@gmail.com) viole la contrainte, car l'hôtel 6 n'existe pas dans la table Hotel.

b. Pour empêcher de futures anomalies, on ajoute une contrainte de clé étrangère.

SQL

ALTER TABLE Réservation
ADD CONSTRAINT fk_reservation_hotel
FOREIGN KEY (IDHotel) REFERENCES Hotel(IDHotel);

Question 6

L’hôtel dont l'identifiant est 5 ne respecte pas une contrainte de domaine.

a. Identifiez la règle de gestion non respectée.

b. Proposez une requête permettant de respecter cette règle de gestion.

Correction

a. La règle R3 ("La capacité minimale d'un hôtel est 50") n'est pas respectée par l'hôtel 5 qui a une capacité de 45.

b. On ajoute une contrainte CHECK sur la colonne Capacité.

SQL

ALTER TABLE Hotel
ADD CONSTRAINT chk_capacite_min CHECK (Capacite >= 50);

Question 7

L’enregistrement suivant ne respecte pas une règle de gestion.

EmailClient: ali@gmail.com, ID_Hotel: 6, Date_Debut: 2024-09-01, NombreNuitée: 100, Montant: 1000

a. Identifiez la règle de gestion non respectée.

b. Proposez une requête permettant de respecter cette règle de gestion.

Correction

a. La règle R4 ("Le nombre des nuitées... varie entre 1 et 30") n'est pas respectée (NombreNuitée = 100).

b. On ajoute une contrainte CHECK sur la colonne NombreNuitée.

SQL

ALTER TABLE Réservation
ADD CONSTRAINT chk_nuitees_range CHECK (NombreNuitée BETWEEN 1 AND 30);

Question 8

La table client contient une redondance dans les valeurs de l’un de ces champs.

a. Identifiez le champ qui contient la redondance.

b. Proposez une nouvelle représentation de la table client et de toutes nouvelles tables indispensables pour corriger cette anomalie.

c. Ecrivez la(les) requête(s) SQL permettant de respecter cette nouvelle représentation.

Correction

a. Le champ Pays contient des redondances.

b. La nouvelle représentation textuelle après normalisation est :

Pays(IDPays, NomPays)
Client(Email, NomClient, Téléphone, IDPays#)

c. Les requêtes SQL pour mettre en place cette nouvelle structure sont :

SQL

-- 1. Créer la nouvelle table Pays
CREATE TABLE Pays (
    IDPays INT PRIMARY KEY AUTO_INCREMENT,
    NomPays VARCHAR(50) NOT NULL UNIQUE
);

-- 2. Supprimer l'ancienne colonne dans Client
ALTER TABLE Client DROP COLUMN Pays;

-- 3. Ajouter la nouvelle colonne de clé étrangère dans Client
ALTER TABLE Client ADD COLUMN IDPays INT;

-- 4.  Ajouter la contrainte de clé étrangère
ALTER TABLE Client
ADD CONSTRAINT fk_client_pays
FOREIGN KEY (IDPays) REFERENCES Pays(IDPays);