Exercice : Zéro Faute [Conception de bases de données]

Zéro Faute

[40 min]

L'entreprise ZéroDéfo veut répertorier ses fautes de production. Une faute est définie dans le manuel qualité de l'entreprise comme « un défaut constaté sur une pièce produite en fin de chaîne ». L'entreprise veut associer les fautes aux produits concernés. Chaque faute est classifiée dans des catégories et sous-catégories. Chaque produit est basé sur un modèle.

Pour chaque modèle, on veut gérer son code constitué de 8 caractères alphanumériques, son nom et la date de mise sur le marché.
Pour chaque produit, on veut connaître le modèle associé, le numéro de série (6 chiffres) et le numéro de produit (max. 4 caractères) ainsi que l'année de production. Un produit est identifié par son numéro de série et son numéro de produit.
Plusieurs produits partagent le même numéro de série (tous les produits de cette série), et deux produits peuvent avoir (par hasard) le même numéro de produit, dans des séries différentes (qui ont adopté le même système de codage des produits).
Une faute concerne toujours un produit. Elle possède un code unique, un titre et la date de détection. Elle peut éventuellement avoir un commentaire et la date de réparation si le produit a été réparé.
Les fautes sont classifiées dans des sous-catégories et chaque sous-catégorie fait partie d'une catégorie. Une faute est toujours classifiée dans une sous-catégorie au moins (elle peut être classifiée dans plusieurs).
Les catégories et les sous-catégories possèdent un nom et, optionnellement, une description.

Question

Proposez un diagramme UML répondant aux besoins de cette entreprise.

Solution

MCD

Question

Proposez un modèle relationnel en 3NF basé sur votre modèle UML. Justifiez les choix que vous faites (transformation des héritages, associations 1-1...).

Solution

MLD

Modèle(#code : chaîne, nom : chaîne, dateMiseSurMarché : date)
     avec dateMiseSurMarché non-null
Produit(#numSérie : entier, #numProduit : chaîne, annéeProduction : entier, modèle=>Modèle)
     avec annéeProduction non-nul
     avec modèle non-nul
SurCatégorie(#nom : chaîne, description : chaîne)
SousCatégorie(#nom : chaîne, description : chaîne, surCatégorie=>SurCatégorie)
     avec surCatégorie non-null
     avec PROJ(SurCatégorie, nom) = PROJ(SousCatégorie, surCatégorie)
     avec PROJ(SousCatégorie, nom) INTER PROJ(SurCatégorie, nom) = {}
Faute(#code : chaîne, titre : chaîne, dateDétection : date, dateRéparation : date, commentaire : chaîne, produitSérie=>Produit(numSérie), produitNuméro=>Produit(numProduit))
     avec titre non-nul
     avec dateDétection non-nul
     avec produitSérie non-nul
     avec produitNuméro non-nul
ClassementFaute(#faute=>Faute(code), #sousCatégorie=>SousCatégorie(nom))
    PROJ(Faute, code) = PROJ(ClassementFaute, faute)

Modèle(#code : chaîne, nom : chaîne, dateMiseSurMarché : date)
     avec dateMiseSurMarché non-null

Produit(#numSérie : entier, #numProduit : chaîne, annéeProduction : entier, modèle=>Modèle)
     avec annéeProduction non-nul
     avec modèle non-nul

SurCatégorie(#nom : chaîne, description : chaîne)
SousCatégorie(#nom : chaîne, description : chaîne, surCatégorie=>SurCatégorie)
     avec surCatégorie non-null
     avec PROJ(SurCatégorie, nom) = PROJ(SousCatégorie, surCatégorie)
     avec PROJ(SousCatégorie, nom) INTER PROJ(SurCatégorie, nom) = {}

Faute(#code : chaîne, titre : chaîne, dateDétection : date, dateRéparation : date, commentaire : chaîne, produitSérie=>Produit(numSérie), produitNuméro=>Produit(numProduit))
     avec titre non-nul
     avec dateDétection non-nul
     avec produitSérie non-nul
     avec produitNuméro non-nul

ClassementFaute(#faute=>Faute(code), #sousCatégorie=>SousCatégorie(nom))
    PROJ(Faute, code) = PROJ(ClassementFaute, faute)

Justification de la 3NF

1NF : toutes les relations ont une clé et chaque attribut est atomique.
2NF :
- Modèle, SurCatégorie, SousCatégorie et Faute ont des clés constituées d'un seul attribut ;
- ClassementFaute ne comporte pas d'attribut en dehors de la clé ;
- dans Produit, il n'existe pas de dépendance fonctionnelle de numSérie vers annéeProduction ou modèle, ou les deux, idem pour numProduit.
3NF : dans les relations Modèle, Produit et Faute, il n'existe pas de dépendance fonctionnelle entre attributs ou groupe d'attributs en dehors de la clé.

Question

Écrivez le code SQL LDD permettant de créer la base de données correspondant au modèle relationnel.

Solution

CREATE TABLE tModele
(
    code CHAR(8) PRIMARY KEY,
    nom VARCHAR NOT NULL,
    dateMiseSurMarche DATE NOT NULL
);
CREATE TABLE tProduit
(
    numSerie NUMERIC(6) NOT NULL,
    numProduit VARCHAR(4) NOT NULL,
    anneeProduction INTEGER NOT NULL,
    modele CHAR(8) NOT NULL,
    PRIMARY KEY (numSerie, numProduit),
    FOREIGN KEY (modele) REFERENCES tModele(code)
);    
CREATE TABLE tSurCategorie
(
    nom VARCHAR PRIMARY KEY,
    description VARCHAR
);
CREATE TABLE tSousCategorie
(
    nom VARCHAR PRIMARY KEY,
    description VARCHAR,
    surCategorie VARCHAR,
    FOREIGN KEY (surCategorie) REFERENCES tSurCategorie(nom)
);
CREATE TABLE tFaute
(
    code VARCHAR PRIMARY KEY,
    titre VARCHAR NOT NULL,
    dateDetection DATE NOT NULL,
    dateReparation DATE,
    commentaire VARCHAR,
    produitSerie NUMERIC(6) NOT NULL,
    produitNumero VARCHAR(4) NOT NULL,
    FOREIGN KEY (produitSerie, produitNumero) REFERENCES tProduit(numSerie, numProduit)
);
CREATE TABLE tClassementFaute
(
    faute VARCHAR NOT NULL,
    sousCategorie VARCHAR NOT NULL,
    PRIMARY KEY (faute, sousCategorie),
    FOREIGN KEY (faute) REFERENCES tFaute(code),
    FOREIGN KEY (sousCategorie) REFERENCES tSouscategorie(nom)
);

CREATE TABLE tModele
(
    code CHAR(8) PRIMARY KEY,
    nom VARCHAR NOT NULL,
    dateMiseSurMarche DATE NOT NULL
);
CREATE TABLE tProduit
(
    numSerie NUMERIC(6) NOT NULL,
    numProduit VARCHAR(4) NOT NULL,
    anneeProduction INTEGER NOT NULL,
    modele CHAR(8) NOT NULL,
    PRIMARY KEY (numSerie, numProduit),
    FOREIGN KEY (modele) REFERENCES tModele(code)
);    
CREATE TABLE tSurCategorie
(
    nom VARCHAR PRIMARY KEY,
    description VARCHAR
);
CREATE TABLE tSousCategorie
(
    nom VARCHAR PRIMARY KEY,
    description VARCHAR,
    surCategorie VARCHAR,
    FOREIGN KEY (surCategorie) REFERENCES tSurCategorie(nom)
);
CREATE TABLE tFaute
(
    code VARCHAR PRIMARY KEY,
    titre VARCHAR NOT NULL,
    dateDetection DATE NOT NULL,
    dateReparation DATE,
    commentaire VARCHAR,
    produitSerie NUMERIC(6) NOT NULL,
    produitNumero VARCHAR(4) NOT NULL,
    FOREIGN KEY (produitSerie, produitNumero) REFERENCES tProduit(numSerie, numProduit)
);
CREATE TABLE tClassementFaute
(
    faute VARCHAR NOT NULL,
    sousCategorie VARCHAR NOT NULL,
    PRIMARY KEY (faute, sousCategorie),
    FOREIGN KEY (faute) REFERENCES tFaute(code),
    FOREIGN KEY (sousCategorie) REFERENCES tSouscategorie(nom)
);

Question

Écrivez une requête SQL permettant de lister le nombre de fautes par nom de modèle et par numéro de série.

Solution

SELECT m.nom, p.numSerie, COUNT(*)
FROM tModele m, tProduit p, tFaute f
WHERE m.code = p.modele
    AND p.numSerie = f.produitSerie
    AND p.numProduit = f.produitNumero
GROUP BY m.nom, p.numSerie
;

SELECT m.nom, p.numSerie, COUNT(*)
FROM tModele m, tProduit p, tFaute f
WHERE m.code = p.modele
    AND p.numSerie = f.produitSerie
    AND p.numProduit = f.produitNumero
GROUP BY m.nom, p.numSerie
;

SELECT m.nom, p.numeroSerie, COUNT(*)
FROM (tModele m
    JOIN tProduit p
        ON m.code = p.modele)
    JOIN tFaute f
        ON (p.numSerie = f.produitSerie AND p.numProduit = f.produitNumero)
GROUP BY m.nom, p.numSerie
;

SELECT m.nom, p.numeroSerie, COUNT(*)
FROM (tModele m
    JOIN tProduit p
        ON m.code = p.modele)
    JOIN tFaute f
        ON (p.numSerie = f.produitSerie AND p.numProduit = f.produitNumero)
GROUP BY m.nom, p.numSerie
;

Attention :

Bien que le numéro de produit ne participe pas à la projection (SELECT) ni au regroupement (GROUP BY), il ne faut pas oublier la jointure entre tProduit.numProduit et tFaute.produitNumero.