Le chemin à l'envers

[30 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.

num. | Ligne | texte | copie |
CTRL+C pour copier, CTRL+V pour coller
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
) ;
CREATE TABLE t_personnes (
  pk_n INTEGER, 
  numss CHAR(13) UNIQUE NOT NULL,
  nom VARCHAR(50),
  prenom VARCHAR(50),
  sexe CHAR(1), 
  conjoint CHAR(13) UNIQUE, 
  chanteur_prefere CHAR(50) NOT NULL,
  nationalite_chanteur_prefere CHAR(20),
  PRIMARY KEY (pk_n),
  CHECK (sexe IN ('H', 'F'))
) ;
num. | Ligne | texte | copie |
CTRL+C pour copier, CTRL+V pour coller
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';
CREATE VIEW v_hommes (pk_n, numss, nom, prenom, conjoint, chanteur_prefere, nationalite_chanteur_prefere)
  AS
    SELECT pk_n, numss, nom, prenom, conjoint, chanteur_prefere, nationalite_chanteur_prefere
    FROM t_personnes
    WHERE sexe='H' ;

CREATE VIEW v_femmes (pk_n, numss, nom, prenom, conjoint, chanteur_prefere, nationalite_chanteur_prefere)
  AS
    SELECT pk_n, numss, nom, prenom, conjoint, chanteur_prefere, nationalite_chanteur_prefere
    FROM t_personnes
    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 ?
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.
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.
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.
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.
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.