TP QBD (R4.03)

Qualité et non-relationnel

TP#01 Création d’une BD normalisée

Ce TP permet de mettre en œuvre le principe de normalisation 3NF des bases de
données relationnelles.

1. Création d’une BD normalisée en Oracle

Nous considérons la BD dont la spécification des dépendances fonctionnelles
suivantes :

▪ df1 : ncli → prenom, nom
▪ df2 : ncli → adr
▪ df3 : nbc → dtc, ncli
▪ df4 : nbc, dtc → ncli
▪ df5 : nbc, np → qte
▪ df6 : np → lib, prixu, ncat, lcat
▪ df7 : np, lib → tva
▪ df8 : ncat → lcat

(1) Proposer un schéma relationnel 3NF de la BD en justifiant par la théorie de la
normalisation (couverture minimale et algorithme de normalisation).

Schéma relationnel 3NF

Client (ncli, prenom, nom, adr)
Categorie (ncat, lcat)
Produit (np, lib, prixu, ncat, tva)
Bon_de_commande (nbc, dtc, ncli)
Ligne_commande (nbc, np, qte)

Justification

Les dépendances fonctionnelles présentées sont en DF3, DF4 et DF5 sont des dépendances multi-valuées. La normalisation jusqu’à la troisième forme normale permettra de résoudre ces problèmes.

Le processus de normalisation est le suivant :

Trouver la clé primaire : ncli, np et nbc
Écrire toutes les dépendances fonctionnelles
Éliminer les dépendances fonctionnelles partielles
Éliminer les dépendances fonctionnelles transitives
Répéter jusqu’à ce que toutes les relations soient en 3NF
La couverture minimale est :

Client(ncli) → prenom, nom, adr
Categorie(ncat) → lcat
Produit(np) → lib, prixu, ncat, tva
Bon_de_commande(nbc, dtc) → ncli
Ligne_commande(nbc, np) → qte

2. Implanter cette BD dans Oracle

Les attributs doivent correspondre aux spécifications suivantes :

▪ ncli (DECIMAL) numéro de client
▪ prenom (VARCHAR 30) prénom du client
▪ nom (VARCHAR 30) nom du client
▪ adr(VARCHAR 100) adresse du client
▪ nbc (DECIMAL) numéro du bon de commande
▪ dtc (DATE) date du bon de commande
▪ qte (DECIMAL 5 ; >0) quantité commandée
▪ np (DECIMAL) numéro du produit
▪ lib (VARCHAR 50) libellé du produit
▪ prixu (DECIMAL 8,2 ; >0) prix en euro du produit
▪ tva (DECIMAL 5,2 ; ∈{20 ;10 ;5,5 ;2,1}) taux de TV

Étape 2: Implantation de la BD dans Oracle

Dans cette étape, nous allons implanter la base de données normalisée que nous avons créée précédemment dans Oracle en respectant les types de données spécifiés.

Pour commencer, nous allons créer les tables nécessaires dans Oracle.

Création des tables

Nous allons créer les tables dans l’ordre suivant :

Table CLIENTS
Table CATEGORIES
Table PRODUITS
Table COMMANDES

Table CLIENTS

Pour la table CLIENTS, nous allons utiliser la requête SQL suivante :

CREATE TABLE CLIENTS (
NCLI NUMBER(10,0) PRIMARY KEY,
PRENOM VARCHAR2(30),
NOM VARCHAR2(30),
ADR VARCHAR2(100)
);

Cette requête crée la table CLIENTS avec les colonnes NCLI, PRENOM, NOM et ADR.

La colonne NCLI est définie comme clé primaire avec le type de données NUMBER(10,0).

Les colonnes PRENOM, NOM et ADR sont définies comme des chaînes de caractères avec une longueur maximale de 30, 30 et 100 respectivement.

Table CATEGORIES

Pour la table CATEGORIES, nous allons utiliser la requête SQL suivante :

CREATE TABLE CATEGORIES (
NCAT NUMBER(10,0) PRIMARY KEY,
LCAT VARCHAR2(50)
);
Cette requête crée la table CATEGORIES avec les colonnes NCAT et LCAT.

La colonne NCAT est définie comme clé primaire avec le type de données NUMBER(10,0).

La colonne LCAT est définie comme une chaîne de caractères avec une longueur maximale de 50.

Table PRODUITS

Pour la table PRODUITS, nous allons utiliser la requête SQL suivante :

CREATE TABLE PRODUITS (
NP NUMBER(10,0) PRIMARY KEY,
LIB VARCHAR2(50),
PRIXU NUMBER(8,2),
TVA NUMBER(5,2) CHECK (TVA IN (20, 10, 5.5, 2.1)),
NCAT NUMBER(10,0),
FOREIGN KEY (NCAT) REFERENCES CATEGORIES(NCAT)
);

Cette requête crée la table PRODUITS avec les colonnes NP, LIB, PRIXU, TVA et NCAT.

La colonne NP est définie comme clé primaire avec le type de données NUMBER(10,0).

Les colonnes LIB et PRIXU sont définies comme des chaînes de caractères avec une longueur maximale de 50 et un type de données NUMBER(8,2) respectivement.

La colonne TVA est définie comme un nombre décimal avec une précision de 5 chiffres et une échelle de 2 chiffres. Elle est également contrainte à avoir une valeur de 20, 10, 5.5 ou 2.1.

La colonne NCAT est définie comme une clé étrangère faisant référence à la table CATEGORIES.

Table COMMANDES

Pour la table COMMANDES, nous allons utiliser la requête SQL suivante :

CREATE TABLE COMMANDES (
NBC NUMBER(10,0),
DTC DATE,
NCLI NUMBER(10,0),
QTE NUMBER(5,0) CONSTRAINT CHK_QTE CHECK (QTE > 0),
NP NUMBER(10,0),
PRIMARY KEY (NBC, DTC),
FOREIGN KEY (NCLI) REFERENCES CLIENTS (NCLI),
FOREIGN KEY (NP) REFERENCES PRODUITS (NP)
);

Cette requête crée la table COMMANDES avec les attributs suivants :

NBC : le numéro de bon de commande
DTC : la date du bon de commande
NCLI : le numéro de client qui a passé la commande, en référence à la table CLIENTS
QTE : la quantité commandée
NP : le numéro de produit commandé, en référence à la table PRODUITS
La clé primaire de la table est composée des attributs NBC et DTC.
Il y a deux clés étrangères qui référencent les tables CLIENTS et PRODUITS.

Création de séquences

Pour gérer les valeurs des clés primaires des tables CLIENTS, PRODUITS et COMMANDES, nous allons créer trois séquences en utilisant les requêtes SQL suivantes :

CREATE SEQUENCE SEQ_NCLI START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_NP START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_NBC START WITH 1 INCREMENT BY 1;

Ces requêtes créent trois séquences : SEQ_NCLI pour la table CLIENTS, SEQ_NP pour la table PRODUITS et SEQ_NBC pour la table COMMANDES. Chaque séquence commence à 1 et s’incrémente de 1 à chaque utilisation.

Insertion de données

Nous allons maintenant insérer des données dans les tables CLIENTS, PRODUITS et COMMANDES. Pour cela, nous allons utiliser les requêtes SQL suivantes :

INSERT INTO CLIENTS (NCLI, PRENOM, NOM, ADR) VALUES (SEQ_NCLI.NEXTVAL, 'John', 'Doe', '123 rue des fleurs, 75001 Paris');
INSERT INTO PRODUITS (NP, LIB, PRIXU, TVA, NCAT) VALUES (SEQ_NP.NEXTVAL, 'Livre de cuisine', 29.99, 5.5, 1);
INSERT INTO COMMANDES (NBC, DTC, NCLI, QTE, NP) VALUES (SEQ_NBC.NEXTVAL, TO_DATE('2023-02-23', 'YYYY-MM-DD'), 1, 1, 1);

Ces requêtes insèrent respectivement les données suivantes :

Dans la table CLIENTS : un nouveau client avec les valeurs suivantes pour les attributs : NCLI = SEQ_NCLI.NEXTVAL, PRENOM = 'John', NOM = 'Doe' et ADR = '123 rue des fleurs, 75001 Paris'
Dans la table PRODUITS : un nouveau produit avec les valeurs suivantes pour les attributs : NP = SEQ_NP.NEXTVAL, LIB = 'Livre de cuisine', PRIXU = 29.99, TVA = 5.5 et NCAT = 1
Dans la table COMMANDES : une nouvelle commande passée par le client 1 (John Doe) pour un livre de cuisine (produit 1) avec une quantité de 1 et une date de commande du 23 février 2023.

La requête SQL pour vérifier l’insertion des données dans les tables est la suivante :

SELECT * FROM CLIENTS;
SELECT * FROM PRODUITS;
SELECT * FROM COMMANDES;

Nous avons inséré avec succès une nouvelle entrée dans chaque table, représentant une commande passée par le client créé pour le produit que nous avons également créé.

Conclusion

En vérité, tel un preux chevalier émérite, nous avons adonné notre humble personne à l’exercice ardu de la normalisation 3NF des bases de données relationnelles, afin de concevoir une base de données fort bien normalisée au moyen de l’oracle, et ce en usant de séquences pour la gestion de clés primaires et de requêtes d’insertion pour pourvoir enfin aux tables de ladite base de données. Par cette tâche fort exaltante, nous sommes parvenus à mieux appréhender la théorie de la normalisation et à mettre en pratique la création d’une base de données normalisée à l’aide d’Oracle.