Examen Base de de Données SGBD - Requête en SQL et Algèbre - dépendances fonctionnelles - MCD - MLD

Question 1 : Organisation d’un cirque prestigieux.

Les organisateurs d’un cirque prestigieux  qui se déroule une fois par an veulent monter une base de données pour gérer les inscriptions des artistes participants et des invités prestigieux, le choix des numéros  et l’organisation des différents spectacles. Le cirque prestigieux dure sept jours et chaque demi-journée est consacrée à un spectacle qui regroupe des numéros portant sur le même thème (magie, acrobaties,

dressage d’animaux ...).

Les numéros présentés au cirque prestigieux sont choisis de la façon suivante: ce sont des numéros de 20 à 25 minutes, présentant des numéros sélectionnés, proposés par une (ou plusieurs) personne, appelée ici artiste, qui appartient officiellement à un des cirques. Un jury regroupant une vingtaine d’experts fait la sélection. Chaque numéro est évalué par cinq experts qui donnent leurs évaluations et mettent chacun une note.

Les experts ne doivent pas proposer eux-mêmes des numéros ni être du même cirque d’origine que les artistes des numéros qu’ils évaluent. A partir des évaluations et des notes, le jury classe les numéros, choisit les meilleurs et les affecte aux différents spectacles.

Les organisateurs veulent conserver les informations suivantes pour la préparation du prochain cirque prestigieux:

Chaque numéro proposé est caractérisé par un code, son nom, son thème et sa durée moyenne en minutes. De plus on souhaite savoir l’instrument utilisé pour les numéros musicaux, l’animal concerné par les numéros de dressage et le type des acrobaties (contorsionnisme, équilibrisme, trapèze volant). On maintient aussi le nombre de ou des artistes présents sur scène avec mention de l’artiste principal à qui envoyer la

réponse d'acceptation ou de refus, les cinq experts avec les notes qu’ils ont mises au numéro. Si le numéro est accepté, le spectacle et l’heure à laquelle il sera présenté. S’il y a plusieurs artistes, celui qui le présentera tout au début et qui est appelé artiste présentateur.

Chaque artiste possède un numéro (en général son numéro de carte d’identité), son nom, son prénom(s), sa date de naissance, son ou ses pseudonymes, son cirque d'origine, son adresse, le ou les numéros qu’il propose puisque les artistes sont susceptibles de présenter plusieurs numéros. S’il est artiste principal, on enregistre en plus ses numéros de téléphone. S’il est artiste présentateur, on garde un résumé de son

discours sur quelques lignes pour que le président puisse le présenter tout au début du spectacle. Pour chaque expert, on note son nom, son prénom(s), son cirque d'origine, son adresse, son numéro de téléphone et les numéros qu’il évalue avec la note qu’il leur attribue.

Pour chaque spectacle, on garde le code, le thème, le jour, l’heure de début, l’heure de fin, le président (celui qui anime le spectacle, présente les artistes, etc., c’est un expert, un artiste invité ou un artiste  d’un autre numéro), la liste des numéros du spectacle, avec leur heure de passage et le coût d'entrée pour le spectacle (tous les spectacles n’ont pas le même coût d’entrée). Pour des raisons de statistiques et de publicité, on conserve pour chaque invité son nom, son ou ses prénoms, son adresse, et s’il a déjà participé à ce cirque prestigieux une ou des années précédentes et en quelles années. On enregistre aussi les spectacles auxquelles il s’inscrit et s’il a réglé son invitation.

Travail  faire :

1) Définir le modèle entité-association le plus complet possible relatif à ce problème.
N'hésitez pas à émettre des hypothèses si vous estimez que des détails vous manquent. Précisez également les contraintes d’intégrité non représentées par le diagramme. 

2) Effectuer la conversion du diagramme entité-association obtenu vers le modèle relationnel. Indiquer la (les) clé(s) de  chaque relation, les contraintes d'intégrité référentielles, les contraintes de domaine, ainsi que toute autre contrainte.


Question 2 

La base de données relationnelle de la gestion des notes des étudiants est la suivante :

−  Etudiant(NumEtud, NomEtud, DateNaiss, Sexe, Faculté)
Faculté référence Faculté.CodeFac

−  Enseignant(NumEns, NomEns, Grade, Ancienneté)

 
−  Cours(NumCours, Intitulé, HeuresTheo, HeuresTP, NumEns)
NumEns référence Enseignant.NumEns

     
−  Notes(NumEtud, NumCours, note)
NumEtud référence Etudiant.NumEtud
NumCours référence Cours.NumCours

          
−  Faculté(CodeFac, LibelléFac)

Requêtes :

1. Donner le nombre total d’étudiants et le nombre d’étudiantes qui ont réussi leur diplôme. Pour réussir la moyenne doit être supérieure à 10.

2. Donner le nom et le numéro des étudiants qui n'ont pas de note en « Base des Données ».

 
3. Donner le nom, l'âge et le sexe des étudiants qui ont eu une note en « Informatique » supérieure à la moyenne générale de la classe. 

    
4. Donner les notes des étudiants de la faculté de Sciences Appliquées qui ont suivi toutes les matières données par l’enseignant « Eric Bastin »

Exprimer toutes les requêtes en SQL et les requêtes 1 et 4 en algèbre.

Question 3
 
Soit le modèle de données décrit à l’aide des relations suivantes :

−  Produit (CodeProduit, LibelléProduit, QuantitéStock)
−  Client (N°Client, NomClient, AdresseClient, Solde)
−  Commande (N°Commande, DateCommande, N°Client)
N°Client référence Client.N°Client
−  LigneCommande (N°Commande, CodeProduit, Quantité)
N°Commande référence Commande.N°Commande
CodeProduit référence Produit.CodeProduit

Requêtes :

1. Donner les libellés des produits qui n’ont jamais été commandés.
2. Donner les noms des clients qui ont commandé tous les produits.
3. Donner les codes des produits commandés par le client « Jean Claude ».
4. Donner le nom du client qui a commandé la plus grande quantité du produit « Omo »

Exprimer en calcul relationnel tuple les requêtes 1, 2, et 2, et en calcul relationnel domaine les requêtes 2, 3 et 4.

Question 4

On considère le schéma relationnel R (A,B,C,D,E,F,G,H,I,J) et l’ensemble des dépendances fonctionnelles suivantes :

  
D = { A → C; AB → CG ; AB → DE; DE → F; H → I; H → J }

a) Donner le graphe minimal en utilisant les règles d’inférence d’Amstrong, et en simplifiant l’ensemble D en un ensemble D’. 

b) Quelle est la clé de la relation R ? Montrer qu'elle est unique. 

c) Quelle est la forme normale de la relation R ? Si elle n'est pas en 3FN proposer une décomposition en 3FN.