Examen Bases de Données - Modèle Entité-association - Requêtes SQL Algèbre - Calcul Tuple - Normalisation

Question 1 - Modélisation

On vous demande de modéliser le réseau social suivant à l’aide du modèle entité-association et ensuite de le traduire vers le modèle relationnel.

Les utilisateurs possèdent un identifiant, un nom, un prénom, une date d’inscription et une date de naissance. Chaque utilisateur construit son réseau d’amis : le système retient la date à laquelle deux utilisateurs sont devenus amis ainsi que le type de lien qu’ils ont déclaré : école, enfance, travail, . . .

Ils peuvent créer des groupes pour lesquels le système retient le nom du groupe, une brève description, le créateur et la date de création. Tous les utilisateurs peuvent rejoindre les groupes qu’ils désirent mais ne peuvent jamais s’en désinscrire. Le système retient alors la date à laquelle l’utilisateur a rejoint le groupe.

Finalement un utilisateur peut envoyer des messages à ses amis ou à un groupe auquel il appartient. Ces messages ont un sujet (titre), une date d’envoi, un expéditeur et un récepteur (le groupe ou l’ami). Un message peut être soit un message standard (texte), soit une vidéo, soit une photo. Dans le cas d’un message vidéo, on retient en plus le fichier vidéo, la durée totale, la taille du fichier et la résolution. Dans le cas d’un message photo, on retient également le fichier photo, le type de fichier (jpeg, tiff, . . . ) et les dimensions horizontale et verticale de l’image (en pixels).

On vous demande de :

• Donner un modèle ”entité-association” complet relatif à ce problème. Lorsque vous utilisez la généralisation, veuillez spécifier son type.
• Émettre des hypothèses si vous estimez que des détails vous manquent.
• Préciser les contraintes d’intégrité non représentées par votre schéma.
• Justifier le choix de la clé de chaque entité.
• Traduire le schéma conceptuel en son équivalent relationnel. Indiquez la ou les clés de chaque relation et toutes les contraintes d’intégrité résultantes de votre traduction.

Question 2 - Requêtes

Voici un modèle relationnel représentant une championnat de course cycliste. Les temps sont donnés en secondes.

–  Equipes (EquipeID, Nom, Directeur)

–  Coureurs (CoureurID, Nom, EquipeID, Pays)

        - EquipeID référence Equipes.EquipeID

–  Etapes (EtapeID, Départ, Arrivée, Kilomètres)

–  Temps (CoureurID, EtapeID, Temps)

        - CoureurID référence Coureurs.CoureurID

        - EtapeID référence Etapes.EtapeID

On vous demande de traduire les requêtes suivantes dans les langages indiqués.

1. Le nom des coureurs ayant participé à toutes les étapes. [Algèbre, SQL]

2. Les coureurs ayant couru plus rapidement que ’Poulidor’ lors d’au moins trois étapes.

[Algèbre, Calcul Tuple, SQL]

3. Les coureurs ayant participé à toutes les étapes auxquelles ’Armstrong’ a participé. [Calcul Tuple, SQL]

4. Le classement général : numéro, nom du coureur ainsi que le temps global de ce coureur, trié par temps global décroissant en ne retenant que les coureurs qui ont participé à toutes les étapes. [SQL]

5. Chaque étape avec le nombre de participants à cette étape, ainsi que le temps moyen de parcours. [SQL]

Question 3 - Normalisation

Soit la relation R(A, B, C, D, E, F, G, H) et les dépendances fonctionnelles suivantes :

–         AB → HG

–          B → D

–          BD → C

–          E → GC

–          F → DBE

–          H → G

1. Donner la clé de cette relation.

2. Décomposer R de façon à obtenir des relations normalisées au maximum sans perte d’information ni perte de dépendance. Justifier.