Exercice 1 · Bac Numérique et Sciences Informatiques Liban 2023 (jour 2)

Exercice 1 (4 points)

Cet exercice traite des bases de données.

Pour gérer son activité, une petite entreprise de travaux d'extérieur à domicile utilise un système de gestion de base de données avec les relations suivantes:

prestations

id_prestation

id_client

date

heure_debut

duree

type

employe

876272 · 5232 · 15/06/2020 · 15:00 · 2 · tonte · Didier

876273 · 5231 · 15/06/2020 · 15:30 · 2 · tonte · Sylvie

876274 · 5229 · 16/06/2020 · 09:00 · 2 · haies · Sylvie

876275 · 5229 · 16/06/2020 · 11:00 · 1 · tonte · Sylvie

876276 · 5233 · 16/06/2020 · 10:00 · 1 · tonte · Didier

876277 · 5228 · 16/06/2020 · 14:00 · 2 · tonte · Didier

876278 · 5230 · 16/06/2020 · 14:00 · 2 · tonte · Sylvie

876279 · 5234 · 17/06/2020 · 09:00 · 3 · bucheronnage · Didier

876935 · 5228 · 18/11/2020 · 09:00 · 2 · haies · Didier

876936 · 5231 · 18/11/2020 · 10:00 · 1 · nettoyage · Sylvie

876937 · 5228 · 18/11/2020 · 11:00 · 1 · nettoyage · Didier

876938 · 5234 · 18/11/2020 · 14:00 · 4 · engazonnement · Sylvie

876939 · 5233 · 18/11/2020 · 14:00 · 2 · plantations · Didier

876940 · 5229 · 19/11/2020 · 09:00 · 2 · haies · Sylvie

876941 · 5230 · 19/11/2020 · 09:00 · 3 · bucheronnage · Didier

876942 · 5229 · 19/11/2020 · 11:00 · 1 · nettoyage · Sylvie

876943 · 5235 · 19/11/2020 · 14:00 · 2 · haies · Didier

876944 · 5232 · 19/11/2020 · 14:00 · 1 · haies · Sylvie

876945 · 5232 · 19/11/2020 · 15:00 · 1 · plantations · Sylvie

Remarque: la durée est un nombre entier d'heures.

clients

id_client

nom

adresse

code_postal

ville

telephone

5228 · Ouellet · 8, rue de Verdun · 26200 · Montélimar · 0475016031

5229 · Rouze · 29, rue Reine Elisabeth · 07400 · Meysse · 0475494977

5230 · Bonenfant · 58, rue des Soeurs · 26780 · Espeluche · 0475568463

5231 · Foucault · 67, rue Michel Ange · 26200 · Montélimar · 0475918885

5232 · Croteau · 98, rue du Gue Jacquet · 26200 · Montélimar · 0475460794

5233 · Therriault · 11, rue de l'Epeule · 26160 · Puygiron · 0475091013

5234 · Rivard · 15, rue des Coteaux · 26200 · Montélimar · 0475339127

5235 · Blanchard · 12, rue du Château · 26740 · Sauzet · 0475305408

1. On n'oubliera pas de justifier les réponses pour les questions a. et b.

a)

Identifier pour chacune de ces deux relations une clé primaire possible.

b)

Identifier la relation qui possède une clé étrangère. Donner cette clé étrangère.

c)

Donner le type pour chacun des deux premiers attributs de la relation clients.

On manipule les données en utilisant le langage SQL.

2.

a)

Donner les valeurs renvoyées par la requête suivante:

SELECT nom, telephone

FROM clients

WHERE ville='Montélimar';

b)

Écrire la requête permettant d'obtenir la date et l'heure de début des prestations de plus d'une heure, réalisées par Didier.

3.

Avec les informations présentées en page précédente, donner les valeurs renvoyées par la requête suivante (le mot-clé DISTINCT permet d'éviter les doublons dans le résultat de la requête):

SELECT DISTINCT nom

FROM clients JOIN prestations

ON clients.id_client=prestations.id_client

WHERE prestations.employe='Sylvie';

4. Pour permettre au logiciel de préparer les factures, on souhaite ajouter le tarif horaire de chaque prestation dans ce jeu de données.

a)

Expliquer quel(s) problème(s) peuvent apparaître si on décide d'ajouter uniquement un attribut tarif_horaire à la relation prestations.

b)

Proposer un schéma relationnel qui permet d'éviter ce(s) problème(s). On pourra donner la réponse sous la forme:

Schéma relationnel:

tarifs(premier attribut, deuxième attribut,...)

prestations(premier attribut,...)

clients(premier attribut,...)

On indiquera explicitement pour chaque relation les attributs, la clé primaire et les éventuelles clés étrangères (on ne demande pas d'indiquer les types).