TP QBD (R4.03) - Normalisation d’une BD existante

Ce TP consiste à normaliser une base de données (BD) relationnelle existante en utilisant le principe de normalisation 3NF. Il est divisé en deux parties principales: l’audit de la BD existante et la proposition d’une nouvelle version normalisée de la BD.

1. Audit de la BD existante

(1) Schéma logique de la BD existante

En utilisant le dictionnaire de données d’Oracle, nous avons obtenu le schéma logique suivant pour la BD existante:

Table 1. Commande
nbc (DECIMAL)	dtc (DATE)	ncli (DECIMAL)	prenom (VARCHAR(30))	nom (VARCHAR(30))	adr (VARCHAR(100))

Table 2. Produit
np (DECIMAL)	lib (VARCHAR(50))	prixu (DECIMAL(8,2))	ntva (DECIMAL(1))	tva (DECIMAL(5,2))	ncat (CHAR(3))	lcat (VARCHAR(50))

Table 3. Ligne
nbc (DECIMAL)	np (DECIMAL)	ncli (DECIMAL)	prenom (VARCHAR(30))	nom (VARCHAR(30))	qte (DECIMAL(5))

(2) Étude des relations

Nous avons étudié les relations en utilisant la couverture minimale suivante et en indiquant le niveau de forme normale pour chacune d’entre elles :

df1 : ncli → prenom, nom, adr (3NF)
df2 : nbc → dtc, ncli (2NF)
df3 : np → lib, prixu, ntva, ncat (3NF)
df4 : nbc, np → qte (2NF)
df5 : ncat → lcat (1NF)
df6 : ntva → tva (1NF)

(3) Proposition d’une nouvelle version 3NF de la BD

En utilisant la théorie de la normalisation, nous avons proposé une nouvelle version 3NF de la BD. Cette version est présentée ci-dessous:

Table 4. Commande
nbc (DECIMAL)	dtc (DATE)	ncli (DECIMAL)

Table 5. Produit
np (DECIMAL)	lib (VARCHAR(50))	prixu (DECIMAL(8,2))	ntva (DECIMAL(1))	ncat (CHAR(3))

Table 6. Categorie
ncat (CHAR(3))	lcat (VARCHAR(50))

Table 7. Ligne
nbc (DECIMAL)	np (DECIMAL)	qte (DECIMAL(5))

Dans cette nouvelle version, nous avons séparé la table Produit en deux tables distinctes: Produit et Categorie. Nous avons également supprimé les colonnes prenom et nom de la table Ligne car elles sont déjà présentes dans la table Commande.

(4) Script de migration de la BD

Le script de migration permettant de conserver les tuples existants peut être obtenu en créant les nouvelles tables de la base de données normalisée à partir des relations normalisées, puis en insérant les données existantes dans ces nouvelles tables. Le script de migration est le suivant :

-- Création de la nouvelle version normalisée de la base de données
DROP TABLE Ligne;
DROP TABLE Produit;
DROP TABLE Commande;
DROP TABLE Client;
DROP TABLE Categorie;

CREATE TABLE Commande
(
nbc DECIMAL,
dtc DATE,
ncli DECIMAL,
CONSTRAINT pk_commande PRIMARY KEY (nbc)
);

CREATE TABLE Client
(
ncli DECIMAL,
prenom VARCHAR(30),
nom VARCHAR(30),
adr VARCHAR(100),
CONSTRAINT pk_client PRIMARY KEY (ncli)
);

CREATE TABLE Produit
(
np DECIMAL,
lib VARCHAR(50),
prixu DECIMAL(8,2),
ncat CHAR(3),
CONSTRAINT pk_produit PRIMARY KEY (np),
CONSTRAINT ck_produit_prixu CHECK (prixu > 0),
CONSTRAINT fk_produit_categorie FOREIGN KEY (ncat) REFERENCES Categorie (ncat)
);

CREATE TABLE Categorie
(
ncat CHAR(3),
lcat VARCHAR(50),
CONSTRAINT pk_categorie PRIMARY KEY (ncat)
);

CREATE TABLE Ligne
(
nbc DECIMAL,
np DECIMAL,
qte DECIMAL(5),
CONSTRAINT pk_ligne PRIMARY KEY (nbc,np),
CONSTRAINT fk_ligne_commande FOREIGN KEY (nbc) REFERENCES Commande (nbc),
CONSTRAINT fk_ligne_produit FOREIGN KEY (np) REFERENCES Produit (np)
);

-- Insertion des données existantes dans les nouvelles tables
INSERT INTO Commande (nbc, dtc, ncli)
SELECT nbc, dtc, ncli FROM Commande;

INSERT INTO Client (ncli, prenom, nom, adr)
SELECT ncli, prenom, nom, adr FROM Commande;

INSERT INTO Produit (np, lib, prixu, ncat)
SELECT np, lib, prixu, ncat FROM Produit;

INSERT INTO Categorie (ncat, lcat)
SELECT ncat, lcat FROM Produit;

INSERT INTO Ligne (nbc, np, qte)
SELECT nbc, np, qte FROM LigneCommande;

-- Suppression des anciennes tables
DROP TABLE Commande;
DROP TABLE Client;
DROP TABLE Produit;
DROP TABLE Categorie;
DROP TABLE LigneCommande;

-- Renommage des nouvelles tables
ALTER TABLE Commande_new RENAME TO Commande;
ALTER TABLE Client_new RENAME TO Client;
ALTER TABLE Produit_new RENAME TO Produit;
ALTER TABLE Categorie_new RENAME TO Categorie;
ALTER TABLE Ligne_new RENAME TO Ligne;

COMMIT;

-- Vérification des résultats
SELECT * FROM Commande;
SELECT * FROM Client;
SELECT * FROM Produit;
SELECT * FROM Categorie;
SELECT * FROM Ligne;

-- Fin de la transaction
END;

Conclusion

En vérité, mes honorables interlocuteurs, il me plairait grandement de vous exposer encore davantage les joyaux de la normalisation de base de données, mais hélas ! Le temps imparti à notre échange touche à sa fin. Qu’il me soit permis cependant, avant de prendre congé de votre auguste assemblée, de vous prodiguer un dernier conseil : si jamais vous vous trouvez confrontés à une base de données déstructurée, souvenez-vous de la maxime du grand sage Confucius : "Le commencement de toutes les sciences, c’est l’étonnement de ce que les choses sont ce qu’elles sont". Étonnez-vous donc de la complexité de votre base de données, puis plongez hardiment dans l’abîme des règles de normalisation, et vous en ressortirez, tels des Phénix, avec une base de données parfaitement structurée et des requêtes qui exécutent plus vite que l’éclair ! Sur ce, je vous salue, messeigneurs et mesdames, et vous souhaite une excellente journée, emplie de joie et de lumière. Amen.