Le chemin à l'envers

[45 minutes]

Soit une base de données, composée d'une seule table et de deux vues, permettant de gérer les chanteurs préférés des françaises et des français.

Cette base est définie par le code SQL LDD ci-après.

1
CREATE TABLE t_personnes (
2
  pk_n INTEGER,
3
  numss CHAR(13) UNIQUE NOT NULL,
4
  nom VARCHAR(50),
5
  prenom VARCHAR(50),
6
  sexe CHAR(1),
7
  conjoint CHAR(13) UNIQUE,
8
  chanteur_prefere CHAR(50) NOT NULL,
9
  nationalite_chanteur_prefere CHAR(20),
10
  PRIMARY KEY (pk_n),
11
  CHECK (sexe IN ('H', 'F'))
12
) ;
1
CREATE VIEW v_hommes (pk_n, numss, nom, prenom, conjoint, chanteur_prefere, nationalite_chanteur_prefere)
2
  AS
3
    SELECT pk_n, numss, nom, prenom, conjoint, chanteur_prefere, nationalite_chanteur_prefere
4
    FROM t_personnes
5
    WHERE sexe='H' ;
6
7
CREATE VIEW v_femmes (pk_n, numss, nom, prenom, conjoint, chanteur_prefere, nationalite_chanteur_prefere)
8
  AS
9
    SELECT pk_n, numss, nom, prenom, conjoint, chanteur_prefere, nationalite_chanteur_prefere
10
    FROM t_personnes
11
    WHERE sexe='F';
On notera que, selon ce modèle, le conjoint d'une femme ou d'un homme est un homme ou une femme ; que X est conjoint de Y n'implique pas que Y est conjoint de X ; et que X peut être conjoint de X.
Question
Quel attribut  est la clé primaire de t_personnes ? Comment appelle-t-on ce genre de clé ? Quel est le statut de numss ? Quel est le statut de conjoint ?
Solution
La clé primaire de t_personnes est pk_n, c'est une clé artificielle. numss est une clé candidate car elle est unique pour chaque enregistrement et non nulle. conjoint n'est pas une clé, car l'attribut peut être nul.
Question
Expliquez pourquoi si conjoint référence la table t_personnes, alors son domaine n'est pas correct.
Modifiez le domaine de conjoint, puis ajoutez une contrainte à la table t_personnes pour que conjoint soit une clé étrangère vers t_personnes, en s'assurant que les contraintes d'intégrité référentielles seront respectées. Vous utiliserez pour cela deux instructions ALTER.
Solution
Le domaine de conjoint doit être celui de la clé primaire de t_personne, donc INTEGER.
1
DROP VIEW v_hommes ;
2
DROP VIEW v_femmes ;
3
4
ALTER TABLE t_personnes ALTER conjoint TYPE INTEGER USING conjoint::INTEGER;
5
6
ALTER TABLE t_personnes ADD FOREIGN KEY (conjoint) REFERENCES t_personnes(pk_n) ;
Complément
Sous PostgreSQL, il n'est pas possible de modifier le type d'une colonne utilisé dans une vue ou une règle, il faut donc commencer par supprimer les vues avant d'effectuer la modification. Il suffit de recréer les vues par la suite.
De même, le passage d'un type CHAR ou VARCHAR vers INTEGER n'est pas possible sans utiliser le transtypage qui décrit comment devra être transformé le contenu de la colonne en cours de transformation. En précisant que la conversion est effectuée vers un entier, la modification devient possible.
Question
Énoncez les DF du modèle relationnel sous-jacent à cette implémentation (en se fondant sur la vraisemblance) sous la forme d'une couverture minimale des DFE.
À partir de la couverture minimale des DFE, prouvez que ce schéma est en 2NF, mais pas en 3NF.
Solution
  • pk_n →  numss
  • pk_n →  nom
  • pk_n →  prenom
  • pk_n →  sexe
  • pk_n →  conjoint
  • pk_n →  chanteur_prefere
  • numss → pk_n
  • chanteur_prefere → nationalite_chanteur_prefere
Le schéma est en 1NF puisqu'il y a une clé et que les valeurs sont atomiques.
Le schéma est en 2NF puisqu'il n'existe pas d'attribut dépendant d'une partie seulement d'une clé.
					Tout schéma dont les clés ne sont composés que d'un seul attribut est en 2NF.
Le schéma n'est pas en 3NF car chanteur_prefere → nationalite_chanteur_prefere et donc un attribut 
					non clé dépend d'un autre attribut non clé.
Question
Proposez un programme SQL permettant de décomposer le schéma de cette BD afin qu'il soit en 3NF, sans perdre d'information, sans perdre de DF 
				et sans perdre les données déjà existantes dans la BD.
Pour ce faire vous utiliserez une instruction CREATE TABLE permettant de créer la nouvelle table engendrée par la décomposition, 
				puis une instruction INSERT permettant d'initialiser correctement cette nouvelle table avec les valeurs existantes dans t_personnes, 
				et enfin deux instructions ALTER pour modifier la table t_personne de façon à en supprimer la redondance et à établir la référence à la nouvelle table.
On fera l'hypothèse que la BD ne contient pas d'incohérence.
Solution
1
CREATE TABLE t_chanteurs (
2
  nom VARCHAR(50) PRIMARY KEY,
3
  nationalite VARCHAR(20)
4
) ;
5
6
INSERT INTO t_chanteurs (nom, nationalite)
7
  SELECT DISTINCT chanteur_prefere, nationalite_chanteur_prefere
8
  FROM t_personnes ;
9
10
ALTER TABLE t_personnes
11
  DROP nationalite_chanteur_prefere ;
12
13
ALTER TABLE t_personnes
14
  ADD FOREIGN KEY (chanteur_prefere) REFERENCES t_chanteurs(nom) ;
Question
En UML, rétro-concevez le MCD qui aurait permis d'arriver directement à votre modèle après normalisation. Justifiez.
On notera la présence des vues dans le schéma initial de la BD et l'on ne reportera pas sur ce schéma les clés artificielles.
Solution
Modèle UML
On notera que les vues sont le résultat d'une association d'héritage ; et que l'association conjoint est une relation 1:1 classique (grâce à la clause d'unicité sur la clé étrangère), et donc (malgré la non conformité avec la réalité) : si A a pour conjoint B, alors B n'a pas forcément pour conjoint A et A peut être conjoint de A. 
Question
Écrivez une requête qui compte le nombre de personnes qui ont le même chanteur préféré que leur conjoint.
On notera que cette requête est équivalente sur le schéma avant ou après normalisation.
Solution
1
SELECT COUNT(P1.pk_n) AS result
2
FROM t_personnes P1, t_personnes P2
3
WHERE P1.conjoint = P2.pk_n
4
AND P1.chanteur_prefere = P2.chanteur_prefere ;