Examen Bases de données relationnelles SGBDR Requête SQL algèbre relationnelle Exercice entité association Dépendances fonctionnelles et normalisation

Exercice 1 : « Sahara Découvertes »

Le responsable de l'agence « Sahara Découvertes » est content du résultat de la dernière année. 

Lancé dans l'organisation d'expéditions dans le désert pour touristes, il veut mieux gérer son agence et organiser l'information sur les divers voyages proposés, les clients qui s'y inscrivent ainsi que les accompagnateurs qui ont la responsabilité d'un groupe de clients pendant un voyage.

 
L'agence propose des circuits et des voyages. Un circuit correspond à un numéro, un nom, une description du programme particulier proposé, et un nombre de participants maximum. « Sahara Découvertes » propose une dizaine de circuits différents, un peu pour tous les goûts. Un circuit donné, représentant une certaine durée, peut être proposé à différentes dates pendant la saison (de novembre à avril) chacune correspondant à un voyage désigné par un numéro et un nom. Les prix des voyages proposés dépendent des dates, il y a un prix standard, et un prix un peu plus élevé au moment des vacances de Noël et de Pâques.

Les clients quant à eux s'inscrivent en donnant leurs noms, prénoms, adresses (numéro, rue, ville et code postal) et numéros de téléphone. Ils désignent un voyage correspondant à un circuit à une certaine date. En fait, ils peuvent  être inscrits sous divers statuts (réservé,  partiellement ou totalement payé). L'inscription devient ferme après paiement, au plus tard un mois avant le départ, de 30% du prix du voyage. Le montant total est dû quinze jours avant le départ. 

 

L'agence s'est fait une bonne clientèle de personnes qui d'une année à l'autre repartent avec « Sahara Découvertes » pour faire un circuit différent, ou nouvellement organisé, et donc elle garde trace de tous les anciens clients ne serait-ce que pour les tenir au courant des nouveaux programmes. Il y a également les clients potentiels qui sont montrés intéressés aux expéditions proposées même s'ils n'y ont pas donné suite. 

 
Les accompagnateurs sont des personnes connaissant bien le Sahara et ayant été formés par l'agence. Chaque accompagnateur a ou pas certaines aptitudes requises ou non par un circuit. A titre d'exemple, un accompagnateur ne peut assurer le circuit "Méharée Timimoun et Grand Erg Occidental" que s'il a la maîtrise d'une  expédition avec des chameaux. Ainsi, chaque circuit demande une ou plusieurs qualifications que possèdent ou non les accompagnateurs. Sur la base de cette information, on procède alors à l'affectation des accompagnateurs aux divers voyages. Les accompagnateurs confirmés sont affectés pour les circuits « Aventure » par contre les accompagnateurs débutants sont  attribués aux circuits « Promenade ». Quand cela est nécessaire, on essaie de voir comment opérer l'affectation d'un nouvel accompagnateur susceptible d'assurer le voyage. Dans tous les cas on garde le numéro d‘accompagnateur, son nom et prénom(s), son numéro de téléphone et son adresse (numéro, rue, ville et code postal). 

On demande de :  

 

• 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. 

• 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.

Exercice 2

Soit une base de données de gestion des projets qui contient les relations suivantes:

Employe(Matr, NomE, Grade, DatEmb, Sup, Salaire, Commission, NDept) 
Département (NumDept, NomDept, Lieu) 
Projet(CodeP, NomP) 
Participation(MatrEmp, CdeP, Heures) 

où :

 
•  L’attribut NDept dans Employé correspond au NumDept dans Département.
•  L’attribut CdeP dans Participation correspond au CodeP dans Projet.
•  L’attribut MatrEmp dans Participation correspond au Matr dans Employé.

Soient les requêtes suivantes :

1) Donner les numéros des départements qui participent à tous les projets.
2) Donner les noms de départements qui ont tous les grades (comptable, programmeur, analyste…). 
3) Donner les noms des employés qui ont le plus gros salaire de leur département.
4) Donner le nom du (ou des) département(s) qui a (ont) le plus grand nombre d'employés.
 

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

  

Exercice 3
  

On s’intéresse à une base de données stockant les performances des différents athlètes participant aux meetings de la Fédération Internationale d’Athlétisme. La base de données est décrite par les tables suivantes

Athlète (NumLicence, Nom, Prénom, NumFéd)
Fédération(NumFédération, NomPays, Continent)
Résultat(NumLic, NomMeeting, Epr, Rang)
RecordDuMonde(Epreuve, NumLic, Valeur) 

 
où :

 
•  L’attribut NumFéd dans Athlète correspond au NumFédération dans Fédération.
•  L’attribut NumLic dans Résultat correspond au NumLicence dans Athlète.
•  L’attribut Epr dans Résultat  correspond a Epreuve dans RecordDuMonde.
 

La table RecordDuMonde contient le record du monde actuel pour chaque épreuve. L’attribut Epreuve a des valeurs comme «100m masculin», «Saut en hauteur féminin» pour qui correspond une valeur du record. Par exemple, pour «  100m masculin » la valeur du record est « 9s79 ».

1. Donner le nom de l’athlète qui détient le record du monde du 10000m masculin.
2. Donner les noms des athlètes qui ont gagné tous les meetings (l’athlète doit être classé le premier).
3. Donner le(s) pays qui a (ont) le plus de records du monde.
4. Donner les noms des athlètes étant montés à la fois sur le podium (1er place)  d’au moins une épreuve de 100m féminin et d’au moins une épreuve de saut en longueur féminin.

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

Exercice 4 

 On considère une relation R construite sur  les attributs Occupant, NbPersonnes, NoApt, Adresse, NbPièces et Propriétaire où un n-uplet (o, nb1,n,a,nb2,p) a la signification suivante: 

la personne o habite avec nb1 personnes dans l'appartement numéro n a l’adresse a ayant nb2 pièces dont le propriétaire est p. 

Une analyse de cette relation nous fournit un ensemble initial E de dépendances fonctionnelles : 

 
Occupant → Adresse 
Occupant → NoApt 
Occupant → NbPersonnes 
Adresse, NoApt → Propriétaire 
Adresse, NoApt → Occupant 
Adresse, NoApt → NbPièces  

1) Quelles sont les clés potentielles de R ? 
2) R est-elle en 3ème forme normale ? Si elle ne l'est pas, proposer une décomposition en 3FN.