Exercice 1 : Dépendances fonctionnelles

Soit un restaurant. Au cours d’un service, décrit par sa date et son heure, les clients s’installent à des tables, commandent des plats et sont servis par des serveurs. Un plat appartient à une catégorie (entrée, principal, dessert, …). Soient les attributs {date, heure, id_client, id_serveur, num_table, id_plat, catégorie}.

Exprimez les dépendances fonctionnelles suivantes:

1. Un plat appartient à une catégorie unique
2. Une table est servie par un serveur unique au cours d’un même service
3. Un client ne peut changer de table au cours d’un même service
4. Un client ne peut commander deux plats de la même catégorie au cours d’un même service

Exercice 2 : Modélisation

On considère une usine produisant des voitures, constituée de plusieurs ateliers. Un atelier, décrit par son numéro, peut contenir plusieurs lignes de production. Chaque ligne de production est décrite par son numéro au sein de l’atelier, sa production journalière (en nombre d’unités), le modèle produit, sa superficie. On conserve également les données concernant les salariés de l'usine. Chaque salarié est décrit par ses nom et prénom, ainsi que son poste, sa date de naissance et son adresse. Chaque salarié est affecté à un atelier unique. Chaque ligne est sous la responsabilité d'un contremaître unique. Un contremaître peut gérer plusieurs lignes de production au sein d’un même atelier.

1. Dessiner ce problème sous la forme d’un schéma entité/association, en précisant les contraintes de cardinalité. Tous les attributs nécessaires doivent être indiqués sur le schéma. N’oubliez pas de définir les clés primaires de chaque ensemble d’entités.
2. Traduire ce schéma sous forme de modèle relationnel, en indiquant les clés primaires et les clés étrangères.

Exercice 3 : Algèbre relationnelle

Une course nautique est décrite par le modèle relationnel suivant:

• Bateau(id_équipage, type, sponsor)
• Equipier(id_équipier, nom, prénom, id_équipage)
• Etape(num_etape, port_départ, port_arrivée, distance)
• Performance(id_équipage, num_etape, chronomètre, classement)

1. Dessinez le schéma entité-association dont est issu ce modèle relationnel
2. Exprimez en algèbre relationnelle les requêtes suivantes :
   1. Numéro de l’étape partant de Pointe-à-Pitre.
   2. Liste des équipiers (nom, prénom) dont le sponsor est “Région Aquitaine”.
   3. Identifiant de l’équipage du monocoque (type) arrivé premier à l’étape partant de Port-au-Prince.
   4. Liste des équipages de catamarans (type) n’ayant pas participé à l’étape arrivant à Valparaiso.
   5. Liste des équipiers (nom, prénom) étant dans le même équipage que Loïck Peyron.
   6. Liste des étapes (num_étape, port départ, port arrivée) auxquelles ont participé à la fois Loïck Peyron et Eric Tabarly.

Exercice 4 : SQL

On considère la base de données de schéma suivant (enregistrant les achats effectués par un ensemble de clients dans un magasin de grande distribution):

• Client(id_client, nom, prénom, sexe, adresse)
• Achète(id_client, code_barre, date)
• Produit(code_barre, nom_produit, catégorie, prix)

1. Ecrivez la commande SQL permettant de vous enregistrer comme nouveau client dans la base.
2. Ecrivez la commande SQL permettant de modifier votre adresse.
3. Exprimez en SQL les requêtes permettant d’obtenir :
   1. Le nombre de produits achetés le 22 juin 2016 dans la catégorie “cosmétiques”.
   2. Le chiffre d’affaires par catégorie.
   3. Le chiffre d’affaires en fonction du sexe dans la catégorie “prêt-à-porter”.

Exercice 5

Soient deux tableaux de tuples r et s. r obéit au schéma R(a₁,a₂,a₃) et s au schéma S(a₃,a₄,a₅). Les deux schémas ont l’attribut a₃ en commun.

• 1. Quelle est l’opération d’algèbre relationnelle réalisée par l’algorithme suivant? Justifiez votre réponse.
  • Algo 1:
    • arguments: x, s
    • retourne: L
      • L ← ∅
      • pour tout tuple e ∈ s:
        • si e est tel que e[a3] = x :
          • ajouter e à L
      • retourner L
• 2. Quelle est l’opération d’algèbre relationnelle réalisée par l’algorithme suivant? Justifiez votre réponse.
  • Algo 2:
    • arguments: r, s
    • retourne: L
      • L ← ∅
      • pour tout tuple e ∈ r:
        • pour tout tuple f ∈ s:
          • si e[a3]=f[a3]:
            • ajouter {e U f} à L
      • retourner L
• 3. Sachant que l’attribut a₃ est clé primaire de la table s, on suppose qu’il existe une fonction g(x) de complexité O(log|s|) qui retourne l’unique tuple de s s'il existe tel que a₃ = x. Proposez une méthode pour accélérer l’Algo 2 à l’aide de la fonction g.