Examen Conception de Base de données

Exercice 1 - Lecture des relations ternaires

Le texte issu de l'énoncé illustre les règles de lecture des cardinalités pour une association ternaire dans le modèle Entité-Association (type Merise). L'exercice demande de restituer la signification complète de ces deux modèles conceptuels.

Modèle 1 : Professeur, Matière, Classe
Dans une relation ternaire liant ces trois entités, la lecture se fait en prenant une occurrence de la première entité et en regardant ses liens avec le couple des deux autres entités.
Les trois lectures correctes sont :

1. Un professeur peut enseigner 1 à n fois une matière dans une classe donnée.
2. Une matière peut être enseignée 1 à n fois par un professeur dans une classe donnée.
3. Dans une classe, une matière peut être enseignée 1 à n fois par un professeur.

Modèle 2 : Client, Hôtel, Séjour
De la même manière, avec la contrainte spécifique au séjour :

1. Un client peut effectuer 1 à n fois un séjour dans un hôtel.
2. Dans un hôtel, il peut être effectué 0 à n fois un séjour par un client.
3. Un séjour ne peut être effectué qu'une et une seule fois par un client dans un hôtel. (Le séjour est ici considéré comme une entité faible ou un événement daté unique, d'où la cardinalité 1,1).

Exercice 2 - Dépendances fonctionnelles et clés

L'énoncé définit trois relations avec leurs ensembles de dépendances fonctionnelles (DF).
Bureau(NumBureau, NumTelephone, Taille) avec FBureau = { NumBureau → NumTelephone, Taille ; NumTelephone → NumBureau }
Occupant(NumBureau, PersonneID) avec FOccupant = { NumBureau → PersonneID }
Materiel(NumBureau, NumPC) avec FMateriel = { NumPC → NumBureau }

Question 1 - Vérification des contraintes

(a) "Un bureau peut contenir plusieurs postes téléphoniques."
Réponse : Non vérifiée.
Explication : La dépendance fonctionnelle NumBureau → NumTelephone signifie que pour un numéro de bureau donné, il n'existe qu'un seul numéro de téléphone.
Correction : Pour que cette contrainte soit vérifiée, il faut supprimer la dépendance fonctionnelle NumBureau → NumTelephone (et, par conséquent, NumTelephone → NumBureau n'aurait probablement plus de sens d'identification unique).

(b) "Il y a une et une seule personne par bureau."
Réponse : Vérifiée.
Explication : La dépendance fonctionnelle NumBureau → PersonneID garantit que pour un numéro de bureau donné, il correspond au maximum une seule personne (identifiant unique). La relation modélise donc bien une personne unique par bureau.

(c) "Un bureau contient un seul ordinateur."
Réponse : Non vérifiée.
Explication : La DF actuelle est NumPC → NumBureau, ce qui signifie qu'un PC se trouve dans un seul bureau, mais n'empêche pas un bureau de posséder plusieurs PC différents.
Correction : Pour imposer un seul ordinateur par bureau, il faut ajouter la dépendance fonctionnelle NumBureau → NumPC.

Question 2 - Clés minimales

La clé minimale d'une relation est un ensemble d'attributs permettant de déterminer tous les autres attributs de la relation de manière unique, sans contenir d'attributs superflus.

• Relation Bureau : NumBureau détermine NumTelephone et Taille. De plus, NumTelephone détermine NumBureau (qui à son tour détermine Taille). Il y a donc deux clés candidates minimales possibles : {NumBureau} et {NumTelephone}.
• Relation Occupant : NumBureau détermine PersonneID. La clé minimale est donc {NumBureau}.
• Relation Materiel : NumPC détermine NumBureau. La clé minimale est donc {NumPC}.

Exercice 3 - Algèbre relationnelle (Produit cartésien vs Jointure)

Différence entre produit cartésien et jointure :
Le produit cartésien (R × S) combine chaque tuple de la relation R avec chaque tuple de la relation S. Si R a "n" lignes et S a "m" lignes, le résultat aura "n × m" lignes. Aucune condition logique ne relie les données.
La jointure (R ⋈ S) est une opération dérivée consistant à effectuer un produit cartésien suivi d'une sélection (filtre) sur une condition (généralement l'égalité entre les attributs communs des deux tables, appelée équi-jointure ou jointure naturelle). Elle sert à associer les tuples qui ont un sens ensemble.

Note : L'énoncé indique "Soit les deux relations suivantes :" mais ne fournit aucune table R et S ni leurs valeurs. Il est donc impossible de donner le résultat numérique du produit cartésien et de la jointure appliqués à ces relations absentes.

Exercice 3 (Suite) - Formes Normales (FNs)

Soit la relation Banque(NumPersonne, NumCompte, solde, activité).

Question 1 - Dépendances fonctionnelles et multivaluées

a. Un compte bancaire a un seul solde.
Il s'agit d'une dépendance fonctionnelle (DF). Le numéro de compte détermine de façon unique le solde.
NumCompte → solde

b. Une personne peut avoir plusieurs comptes indépendamment de ses activités.
Il s'agit d'une dépendance multivaluée (DMV). Pour une personne donnée, l'ensemble de ses comptes est indépendant de l'ensemble de ses activités.
NumPersonne ↠ NumCompte
NumPersonne ↠ activité

Question 2 - Identifiants de la relation Banque

Puisqu'une personne possède plusieurs comptes et exerce plusieurs activités de manière indépendante, chaque combinaison de (compte, activité) pour cette personne générera un tuple dans la table (produit cartésien interne dû aux DMV). L'attribut solde dépend uniquement de NumCompte.
Pour identifier une ligne unique dans cette relation globale, il faut connaître la personne, le compte et l'activité.
L'identifiant (clé primaire) de la relation Banque est donc : (NumPersonne, NumCompte, activité).

Question 3 - Normalisation jusqu'en 4NF

Forme normale actuelle et anomalies :
La relation Banque est en 1ère Forme Normale (1NF) car tous ses attributs sont atomiques.
Cependant, elle n'est pas en 2NF. La règle de la 2NF stipule que tout attribut n'appartenant pas à la clé doit dépendre de la totalité de la clé. Ici, l'attribut solde dépend seulement de NumCompte, qui n'est qu'une partie de la clé primaire (NumPersonne, NumCompte, activité).
Anomalies :

• Redondance : le solde d'un compte est répété autant de fois que la personne a d'activités.
• Mise à jour : modifier le solde d'un compte oblige à mettre à jour plusieurs lignes, avec un risque d'incohérence.

Décomposition en 4NF :

1. Passage en 2NF / 3NF : On élimine la dépendance fonctionnelle partielle en isolant l'attribut solde avec son déterminant NumCompte.
   On obtient :

• Compte(NumCompte, solde) (Clé : NumCompte)
• PersonneBanque(NumPersonne, NumCompte, activité) (Clé : NumPersonne, NumCompte, activité)

Passage en 4NF : La relation PersonneBanque ne contient plus de dépendance fonctionnelle, elle est donc en BCNF. Cependant, elle contient les dépendances multivaluées indépendantes NumPersonne ↠ NumCompte et NumPersonne ↠ activité. Cela crée une redondance massive (violation de la 4NF).
On la décompose en deux relations distinctes autour du déterminant de la DMV :

• Posseder(NumPersonne, NumCompte) (Clé : NumPersonne, NumCompte)
• Exercer(NumPersonne, activité) (Clé : NumPersonne, activité)

Schéma final en 4NF :

• Compte(NumCompte, solde)
• Posseder(NumPersonne, NumCompte)
• Exercer(NumPersonne, activité)

Exercice 4 - Modèle Entité-Association et SQL

Note sur la notation : L'énoncé utilise le caractère # pour désigner les clés étrangères (ex: num_module#). Dans les requêtes SQL, nous retirerons ce # car c'est une convention de modélisation non standardisée en syntaxe SQL stricte, et certains SGBD le rejetteraient.

Question 1 - Diagramme Entité/Association

D'après le schéma relationnel fourni, le diagramme Entité/Association (E/A) se compose de :

• Entités principales :
  • MODULE (num_module, nom_module, nb_heure, thème)
  • CANDIDAT (num_candidat, nom_candidat, prénom_candidat, job, âge)
  • ENSEIGNANT (num_ens, nom, prénom, salaire, num_dept)
• Hiérarchie / Entités faibles (Documents) :
  • Il existe une notion sous-jacente de document reliée au module. Le modèle traduit trois entités EXAMEN, TD et COURS identifiées par num_document et possédant des attributs communs (date, nb_page, type) et des attributs spécifiques (session pour Examen, nb_exercice pour TD). Chacune possède une association 1:N avec MODULE (un document concerne un module précis).
• Associations (Relations) :
  • S'INSCRIRE (entre CANDIDAT et MODULE) : Relation N:M traduite par la table INSCRIT.
  • ASSURER (entre ENSEIGNANT et MODULE) : Relation N:M traduite par la table ASSURE portant la propriété semestre.
  • DIRIGER / SUPERVISER (réflexive sur ENSEIGNANT) : Association 1:N traduite par la clé étrangère num_responsable pointant vers num_ens.

Question 2 - Requêtes SQL

(a) Quels sont les modules pour lesquels on a proposé le maximum de TD ?

SELECT num_module
FROM TD
GROUP BY num_module
HAVING COUNT(*) >= ALL (
    SELECT COUNT(*)
    FROM TD
    GROUP BY num_module
);

(b) Quels sont les enseignants qui sont mieux rémunérés que leur responsable ?

SELECT E.nom, E.prénom
FROM ENSEIGNANT E
JOIN ENSEIGNANT R ON E.num_responsable = R.num_ens
WHERE E.salaire > R.salaire;

(c) Quels sont les enseignants qui ont assuré au moins un module suivi par le candidat ‘MF’ et non suivis par le candidat ‘BR’ ? (répondre de deux manières)

Méthode 1 : Avec l'opérateur d'appartenance IN et NOT IN appliqués au module.

SELECT DISTINCT E.nom, E.prénom
FROM ENSEIGNANT E
JOIN ASSURE A ON E.num_ens = A.num_ens
WHERE A.num_module IN (
    SELECT I.num_module
    FROM INSCRIT I
    JOIN CANDIDAT C ON I.num_c = C.num_candidat
    WHERE C.nom_candidat = 'MF'
)
AND A.num_module NOT IN (
    SELECT I2.num_module
    FROM INSCRIT I2
    JOIN CANDIDAT C2 ON I2.num_c = C2.num_candidat
    WHERE C2.nom_candidat = 'BR'
);

Méthode 2 : Avec l'opérateur ensembliste EXCEPT (ou MINUS selon le SGBD) pour isoler d'abord la liste des modules correspondants.

SELECT DISTINCT E.nom, E.prénom
FROM ENSEIGNANT E
JOIN ASSURE A ON E.num_ens = A.num_ens
WHERE A.num_module IN (
    SELECT I.num_module
    FROM INSCRIT I
    JOIN CANDIDAT C ON I.num_c = C.num_candidat
    WHERE C.nom_candidat = 'MF'
    EXCEPT
    SELECT I2.num_module
    FROM INSCRIT I2
    JOIN CANDIDAT C2 ON I2.num_c = C2.num_candidat
    WHERE C2.nom_candidat = 'BR'
);

(d) Quels sont les candidats inscrits dans tous les modules ?
Il s'agit d'une opération de division relationnelle. On recherche les candidats pour lesquels il n'existe aucun module où ils ne sont pas inscrits.

SELECT C.nom_candidat, C.prénom_candidat
FROM CANDIDAT C
WHERE NOT EXISTS (
    SELECT M.num_module
    FROM MODULE M
    WHERE NOT EXISTS (
        SELECT I.num_module
        FROM INSCRIT I
        WHERE I.num_c = C.num_candidat 
        AND I.num_module = M.num_module
    )
);

(Une alternative courante et acceptée consiste à grouper par candidat et vérifier que le COUNT de ses modules est égal au COUNT total de la table MODULE).

Méthode

Face à une épreuve de conception de bases de données, la clé réside dans la lecture attentive du monde réel décrit par l'énoncé.

1. Contraintes et DF : Ne vous fiez pas à votre propre intuition du domaine, mais uniquement aux dépendances fonctionnelles mathématiquement listées. Si l'énoncé dit NumPC → NumBureau, alors plusieurs PC peuvent s'entasser dans le même bureau, peu importe ce que dit le bon sens.
2. Normalisation : Respectez les étapes chronologiques. Déterminez d'abord la ou les clés primaires complètes. Séparez ensuite ce qui dépend d'une partie de la clé (passage en 2NF), puis ce qui dépend d'attributs non-clés (3NF). Enfin, soyez vigilants aux concepts distincts (comptes vs activités) qui génèrent des combinatoires (DMV) requérant la 4NF.
3. Traduction SQL : Identifiez toujours la question fondamentale avant de coder : s'agit-il d'une agrégation (GROUP BY), d'une hiérarchie réflexive (jointure de la table avec elle-même), ou d'une division (tous les / chaque) ? Traitez les sous-requêtes séparément pour valider votre logique avant de les emboîter.