18 Juin 2001
Durée : 1h30. Documents autorisés.
Recommandations : choisir des solutions simples et lisibles et des réponses succinctes.
Les organisateurs d’une célèbre course de bateaux voudraient créer une base de données permettant de retrouver toutes les informations relatives à l'organisation de la course.
La course se déroule en plusieurs épreuves sanctionnées chacune par un classement. Chaque épreuve débute et se termine dans un port, le port d'arrivée pouvant être différent du port de départ, cependant il n'y a jamais plus d'une épreuve par jour. Chaque bateau est financé par un ou plusieurs sponsors et armé d'un équipage composé d'un skipper et d'équipiers. Le skipper d'un bateau ne peut changer d'une épreuve à l'autre de la course, mais cette contrainte ne touche pas les équipiers. Ces derniers ne peuvent pas changer de bateau au cours d'une même épreuve.
La base de données doit permettre de répondre, parmi d'autres, aux questions suivantes:
¨ Quels sont les sponsors (nom, secteur d’activité) d'un bateau ?
¨ Quel est le montant de la subvention d'un sponsor particulier à un bateau particulier ?
¨ Quels bateaux (matricule, nom, type) sont engagés dans l'épreuve qui débute le 27 avril ?
¨ Quels sont les équipiers (nom, prénom et age) du bateau qui a gagné la première épreuve ?
¨
Sur quels bateaux de plus de 12 mètres y a-t'il un
équipier médecin ?
Travail demandé :
1. Proposer un schéma Entité/Association pour cette base de données.
2.
Déduire un schéma relationnel pour cette base. Préciser
son degré de normalisation.
Partie II : SQL
Une entreprise implantée à l’échelle nationale, spécialisée dans la vente de produits informatiques, possède une base de données pour la gestion de son service de vente. Le schéma relationnel de cette base de données est le suivant :
Projets (num_projet, désignation, succursale, budget)
Ventes (num_vendeur, num_pièce, num_projet, quantité, date_vente)
Pièces (num_pièce, désignation, poids, num_succ_stock, prix_unitaire, quantité_stockée, seuil_stock)
Succursales (num_succursale, désignation, ville, surface, capital)
Vendeurs (num_vendeur, nom, prénom, ville, succursale, salaire_fixe, commission)
Les clés primaires des différentes relations sont indiquées en gras et les clés étrangères sont indiquées en italique.
Num_succ_stock dans la relation Pièces référence la succursale de stockage de la pièce.
Succursale dans la relation Vendeurs référence la succursale de rattachement du vendeur.
Succursale dans la relation Projets référence la succursale qui a en charge le projet.
Le triplet num_vendeur, num_pièce, num_projet dans la table Ventes référence le vendeur qui a effectué la vente, le numéro de la pièce objet de la vente et le numéro du projet auquel est destinée la pièce.
Ecrire les requêtes suivantes en SQL :
1 Liste des vendeurs qui n’ont rien vendu.
2 Liste de toutes les équipes possibles de deux vendeurs d’une même succursale.
3 Liste de tous les ensembles possibles de trois pièces dont la somme des prix est inférieure à 6000F.
4 Liste des projets (numéro) pour lesquels il y a eu des ventes, mais aucune pour une pièce de plus de 5000 francs.
5 Liste des noms de vendeurs qui correspondent à au moins deux vendeurs (homonymes).