Normalisation d'un schéma relationnel

5 Normalisation d'un schéma relationnel

On va maintenant créer des tables, y insérer des informations et effectuer des requêtes sur le schéma obtenu. L'application visée est le système d'information d'un zoo, et on suppose que l'on se trouve dans la situation suivante : une personne peu avertie (elle n'a pas suivi les enseignements du CNAM !) a créé en tout et pour tout une seule table dans laquelle on trouve toutes les informations. Voici le schéma de cette table.

CREATE TABLE Zoo (Animal          Number(4),
                  Nom             VARCHAR2 (20),
                  Annee_naissance NUMBER(4),
                  Espece          VARCHAR2(10),
                  Gardien         VARCHAR2 (20),
                  Prenom          VARCHAR2 (10),
                  Salaire         NUMBER (10,2),
                  Classe          VARCHAR2 (10),
                  Origine         VARCHAR2 (10),
                  Emplacement     NUMBER(4),
                  Surface         NUMBER (3),
                  Type_empl       NUMBER(2),
                  Libelle_empl    VARCHAR2 (20));

Chaque ligne corrrespond à un animal auquel on attribue un nom propre, une année de naissance et une espèce (Ours, Lion, Boa, etc.). Cet animal est pris en charge par un gardien (avec prénom et salaire) et occupe un emplacement dans le zoo (numéro d'emplacement, surface, type_emplacement et libellé_emplacement : savane, désert, forêt, etc.). Enfin chaque espèce appartient à une classe (les mammifères, poissons, reptiles, batraciens ou oiseaux) et on considère pour simplifier qu'elle provient d'une origine unique (Afrique, Europe, etc.).

Vous pouvez consulter avec SQL le contenu de cette table Zoo qui vous est acessible en lecture. On constate à l'oeil nu de nombreuses redondances et anomalies (les voyez-vous ?). Commencez par copier la table chez vous avec la commande suivante :

CREATE TABLE zoo AS
SELECT * FROM zoo;

Le schéma n'est évidemment pas correct : on n'y trouve même pas de contraintes (NOT NULL) ou de définition de clé primaire. Le premier travail est donc de définir un bon schéma relationnel. Pour cela, on vous donne les spécifications suivantes, sous forme de dépendances fonctionnelles :

Animal Nom, Année_naissance, Espèce, Emplacement.
Nom, Espèce Animal.
Espèce Origine, Classe.
Gardien Prénom, Salaire.
Emplacement Surface, Type_emplacement, Gardien.
Type_emplacement Libellé_emplacement.

Questions

Le contenu actuel de la table est-il conforme à ces spécifications ? Indication : pour chaque dépendance fonctionnelle A B, où B est un attribut, exécutez l'ordre SQL

SELECT A, count(DISTINCT B)
FROM zoo 
GROUP BY A;

Si la table respecte la DF, count(*) doit valoir ... (à votre avis ?). Vous pouvez donc chercher les anomalies en ajoutant un HAVING COUNT (DISTINCT B) > ... à la requête ci-dessus.

Solution :


SELECT animal, count (distinct nom), count (distinct annee_naissance), 
        count (distinct espece), count (distinct  emplacement)
FROM zoo
GROUP BY  animal
HAVING count (distinct nom) >1 OR count(distinct annee_naissance) > 1
OR count(distinct espece) > 1 OR count (distinct emplacement) >1;

SELECT nom, espece, COUNT (distinct animal)
FROM  zoo
GROUP BY nom, espece
HAVING COUNT (distinct animal) > 1;

SELECT espece, count(distinct origine), count(distinct classe)
FROM zoo
GROUP BY  espece
HAVING count (distinct origine)>1 OR count (distinct classe) >1;

SELECT gardien, count(distinct prenom), count(distinct salaire)
FROM zoo
GROUP BY  gardien
HAVING count (distinct prenom)>1 OR count (distinct salaire) >1;

SELECT emplacement,  count(distinct surface), 
        count(distinct type_empl), count (distinct gardien)
FROM zoo
GROUP BY  emplacement
HAVING count (distinct surface)>1 OR count (distinct gardien) >1
OR     count (distinct type_empl) > 1;

SELECT type_empl,  count(distinct libelle_empl)
FROM zoo
GROUP BY  type_empl
HAVING count (distinct libelle_empl) >1;

Effectuez les corrections nécessaires avec des ordres UPDATE. Quand il y a une anomalie ou une incohérence entre deux lignes, on considère que la première est la bonne. IMPORTANT : on valide une mise-à-jour avec la commande commit;.

Solution :

Exemple : atribuer un nouveau numéro à l'un des animaux no 2 ; corriger l'ours-batracien en ours-mammifère ; donner un nouveau nom à l'un des deux ours appelés 'Martin', etc. Voici les corrections pour la DF Espece :
```
UPDATE zoo SET classe = 'Mammifere'
WHERE    espece = 'Ours';

UPDATE zoo SET  origine = 'Asie' 
WHERE espece = 'Tigre';
```
Montrer que Animal et Nom, Espèce sont des clés de la table Zoo ?
⁽^trèfle⁾Montrer que ce sont les seules clés.

Solution :

Il est clair que ce sont des clés. Ce sont les seules qui permettent de déduire Nom, donc elles appartiennent à toute clé.
Est-elle en troisième forme normale (donnez un argument formel) ? Y-a-t-il des redondances/anomalies prévisibles ? Les retrouvez-vous dans la table Zoo ?

Solution :

On a déjà corrigé les animalies. Pour les redondances, il n'y a pratiquement que ça !

Trouvez un schéma en troisième forme normale, créez les ordres CREATE TABLE correspondant (avec des contraintes PRIMARY KEY, FOREIGN KEY NOT NULL, UNIQUE) et exécutez-les. ATTENTION : une table à laquelle on fait référence dans un FOREIGN KEY doit avoir été créée avant. Il faut donc faire attention à l'ordre de création des tables.

Solution :

Les tables sont : ANIMAL, ESPECE, GARDIEN, EMPLACEMENT et TYPE_EMPL. Attention aux contraintes. Voici les commandes :


CREATE TABLE TYPE_EMPL (Type_empl       NUMBER(2) NOT NULL,
                  Libelle_empl    VARCHAR2 (20) NOT NULL,
                  PRIMARY KEY (Type_empl));

CREATE TABLE GARDIEN (Gardien         VARCHAR2 (20) NOT NULL,
                  Prenom          VARCHAR2 (10),
                  Salaire         NUMBER (10,2) NOT NULL,
                  PRIMARY KEY (Gardien));

CREATE TABLE EMPLACEMENT (Emplacement     NUMBER(4),
                  Surface         NUMBER (3),
                  Type_empl       NUMBER(2),
                  Gardien       VARCHAR2(20),
                  PRIMARY KEY (Emplacement),
                  FOREIGN KEY (Type_empl) REFERENCES TYPE_EMPL,
                  FOREIGN KEY (Gardien) REFERENCES GARDIEN);

CREATE TABLE ESPECE (Espece      VARCHAR2(10) NOT NULL,      
                  Classe          VARCHAR2 (10) NOT NULL,
                  Origine         VARCHAR2 (10) NOT NULL,
                  PRIMARY KEY (Espece));

CREATE TABLE ANIMAL (Animal  NUMBER (3) NOT NULL,
                     Nom     VARCHAR2(30) NOT NULL,
                      Annee_Naissance NUMBER(4),
                  Espece          VARCHAR2(10) NOT NULL,
                  Emplacement     NUMBER(4)  NOT NULL,
                PRIMARY KEY  (Animal),
                FOREIGN KEY (Espece) REFERENCES ESPECE,
                FOREIGN KEY (Emplacement) REFERENCES EMPLACEMENT,
                UNIQUE (Nom, Espece));

Une fois le schéma créé, insérez les données dans les tables du 'bon' schéma en les copiant à partir de la table Zoo. Pour copier des données d'une table A vers une table B(B1,B2, ... Bn) , SQL fournit une commande qui est un mélange de SELECT et de INSERT.
```
         INSERT INTO TABLE B (B1, B2, ... Bn)
         SELECT DISTINCT A1, A2, ... An
         FROM A
         WHERE ...;
      
```
En fait l'ordre SELECT peut accéder à plusieurs tables (jointures, différences). Contrainte importante : il doit y avoir autant de Ai que de Bi, et pour les mêmes types. Exemple :
```
INSERT INTO ESPECE (espece, classe, origine)
SELECT DISTINCT espece, classe, origine FROM zoo;
```

Vous devez maintenant avoir un bon schéma et un mauvais (la table Zoo toute seule). Sur ces deux schémas, exprimez les requêtes SQL qui suivent.

Quels sont les Ours du zoo ?
Quels animaux s'appellent Martin ?
Quels animaux habitent dans la jungle ?
De quels animaux s'occupe le gardien Dupond ?
⁽^trèfle⁾Sur quel(s) emplacement(s) y-a-il des animaux de classes diffrentes (no, surface et libellé du type de l'emplacement).
Somme des salaires des gardiens.
...

Solution :

Facile, sauf une :


SELECT e.emplacement, e.surface,  t.libelle_empl
FROM   animal a1, animal a2, emplacement e, type_empl t,
           espece es1, espece es2
WHERE     a1.emplacement = a2.emplacement
AND       a1.espece = es1.espece
AND       a2.espece = es2.espece
AND       es1.classe != es2.classe
AND       e.emplacement = a1.emplacement
AND       e.type_empl = t.type_empl;

Considérons les anomalies qui existaient initialement : sont-elles encore possibles dans le 'bon' schéma. D'un autre côté, y-a-t-il des requêtes que l'on peut exprimer sur Zoo et pas sur le nouveau schéma ? (autrement dit : a-t-on perdu de l'information ?)

Conclusion : qu'a-t-on gagné, qu'a-t-on perdu ?

Solution :

On a supprimé la possibilité d'avoir des anomalies, tout en ne perdant pas d'information.