INFO404 – Bases de données relationnelles

Examen Session 2

L2 Informatique – L2 PEIP – L2 SPI

28 Juin 2022 – 9h00 à 11h00
Aucun document n’est autorisé – Dictionnaire de traduction seulement
Calculatrice, téléphone et ordinateur interdits

Note :
- Le barème est donné à titre indicatif.
- Chaque partie est indépendante.

1. Concepts [4 points]

La société GoodCoorp est une grande entreprise qui existe depuis de nombreuses années. Aujourd’hui,
elle cherche à structurer son système de données : la gestion de ses employés, de ses clients, des
commandes, des livraisons, du stock…

a. Etant un expert dans le domaine, donnez les avantages et les inconvénients d’utiliser une
plateforme pour chaque gestion de données.
b. Il existe des plateformes génériques qui permettent d’organiser à un seul endroit toutes ces
informations. Expliquez en quelques mots leur fonctionnement.
c. Ces outils ne correspondent pas aux ententes de l’entreprise car ils sont trop généraux. Vous
proposez donc, après réflexion, d’organiser toutes les données de l’entreprise au sein d’un
système de gestion de base de données relationnelle.
Quels seraient vos choix méthodologiques (schémas, modèles, types…) et techniques
(technologies utilisées, format de données…) pour mener à bien votre intervention ?

2. Modélisation [8 points]

L’aéroport de BigCity connait une forte affluence de voyageurs depuis peu et a vu sa fréquentation
doublée en un an. De ce fait, le directeur du site souhaite faire évoluer le système de données de son
aéroport afin de proposer une meilleure coordination pour les équipes qui gèrent le départ et/ou
l’arrivée des voyageurs. C’est pour cela qu’il a fait appel à vous.

L’aéroport est divisé en 3 terminaux : le terminal A qui a 3 étages, ainsi que les terminaux B1 et B2 qui
ont chacun 2 étages. Il est essentiel de parler des terminaux, parce qu’au sein de chacun d’eux se
trouve les portes qui permettent l’embarquement des voyageurs pour le décollage et l’atterrissage des
avions.

Les portes d’embarquement de 1 à 36 se situent dans le terminal A, les portes de 101 à 112 dans le
terminal B1, enfin les portes de 201 à 224 dans le terminal B2. Il sera important de noter le nombre de
sièges pour l’accueil des voyageurs qui peut varier d’une porte d’embarquement à une autre.

Pour mieux visualiser le fonctionnement d’un décollage, un voyageur reçoit sur son billet d’avion le
nom du terminal et le numéro de la porte à laquelle va décoller son avion. Une fois la sécurité du
terminal passée, le voyageur doit se diriger vers sa porte d’embarquement. Ici, il peut attendre son
avion en s’asseyant sur les sièges prévus et/ou en allant dans les boutiques et les brasseries les plus
proches. Lorsque l’avion est prêt, le personnel de la porte d’embarquement appelle les voyageurs pour
procéder aux dernières vérifications avant le décollage.
L’atterrissage d’un avion est moins compliqué en termes d’organisation pour le personnel. Lorsque les
voyageurs quittent l’avion, ils arrivent dans l’aéroport par une porte mais ne restent pas et doivent
soit reprendre un autre avion en allant vers la porte d’embarquement correspondante, soit se diriger
vers la sortie. Un membre du personnel est présent pour guider les voyageurs dans l’aéroport.

Les voyageurs montent ou descendent de l’avion par une porte d’embarquement en fonction du
numéro du vol.

- Le voyageur Gilbert Hist a embarqué dans un avion Bouing763 de la compagnie « In The Air »
par la porte 23 à destination de Okowa, le 18 juin 2022 à 16h43 ; son vol portait le n°78931.
- La voyageuse Jolly Ka-heur a débarqué de l’avion Aimecar37 de la compagnie « Travel Plane »
par la porte 111 en provenance de Glosgaw, le 21 juin 2022 à 9h34 ; son vol portait le n°32189.

A l’image de cet exemple, le voyageur doit au moins être enregistré avec son prénom et son nom, et
l’avion avec sa référence et sa compagnie aérienne. Dans les informations du vol, doit apparaître, en
plus de son numéro, de sa date et de sa destination, si l’avion décolle de l’aéroport ou atterrit car les
équipes qui interviennent et les pistes occupées ne sont pas les mêmes.

Au sein de la première version du système, il est souhaitable de ne prendre en compte que les
employés qui travaillent au niveau des portes d’embarquement. Les autres équipes seront impliquées
plus tard. Afin de bien détailler leur partie, un employé possède un identifiant unique, un prénom, un
nom de famille et la date à laquelle il a été embauché. Celle-ci lui permettra d’évoluer en fonction de
son nombre d’années d’ancienneté.

- L’employée Aneth Litteram, identifiée RT8739, travaille au niveau de la porte 23 du terminal A

et a été embauchée le 12 avril 2014.
- L’employé Théo Vereaux, identifié HD9202, travaille au niveau de la porte 111 du terminal B1
et a été embauché le 8 septembre 2015.

Les destinations doivent également être présentes au sein du système. Il est impératif de connaître de
quelle destination provient un avion ou vers quelle destination il va. Sur ce principe, une destination
est identifiée par un code unique.

- L’aéroport d’Okowa est noté « OKO » et se trouve au Japon.

- L’aéroport de Glosgaw est noté « GGW » et se trouve en Allemagne.

Afin d’éviter les destinations qui pourraient être nommées de la même manière, le pays de l’aéroport
de la destination est renseigné. Tous ces éléments sont affichés sur des panneaux de contrôle pour le
personnel et les voyageurs au niveau de chaque porte d’embarquement.

a. Proposez un schéma Entités/Associations qui respectent toutes les contraintes données par
l’aéroport. Les exemples cités plus haut doivent bien évidemment être pris en compte.
b. Transformez ce schéma en modèle relationnel en expliquant vos choix.
c. Chaque relation de votre modèle devient une table.
Donnez des exemples de tuples pour chaque table en reprenant les cas réels.
3. Cohérence [4 points]

Des étudiants ont proposé les tables suivantes pour essayer de répondre aux trois problèmes.

A chaque fois, construisez le graphe des dépendances afin de vérifier si leur travail respecte bien les
trois formes normales. Dans le cas contraire, donnez la ou les formes normales non respectées en
réécrivant votre version du modèle avec les n-uplets associés.

a. Problème 1 : Les chefs-lieux et les nouvelles régions françaises.

Ville Habitant Superficie Nom Population

Lyon 500 715 47,87 km² Auvergne-Rhône-Alpes 7 916 889
Strasbourg 276 170 78,26 km² Grand Est 5 555 186
Toulouse 466 297 118,3 km² Occitanie 5 808 435
Rouen 110 117 21,38 km² Normandie 3 499 280
Bordeaux 246 586 49,36 km² Nouvelle-Aquitaine 5 935 603

Exemple : La ville de Lyon compte 500k habitants et s’étend sur 47,87 km², elle est le chef-lieu de
la nouvelle région Auvergne-Rhône-Alpes qui a une population d’environ 8 millions de personnes.

b. Problème 2 : Les gagnants au tirage d’une loterie.

Tirage Référence Lot Grille Prénom Nom

1 M26 Micro-onde 4585 Marie Iksan
2 C23 Console de jeu 4025 Roy Lloyd
3 T75 Télévision 4782 Kevin Kar
4 M26 Micro-onde 4533 Fred Valeo
5 V48 Voiture 4213 Yassine Hervey
6 T75 Télévision 4754 Samantha Bonard

Exemple : Le micro-onde de référence M26 a été gagné au 1er tirage par la grille n°4585 de Marie
Iksan, puis un second micro-onde de même référence au 4e tirage par la grille n°4533 de Fred
Valeo. Yassine Hervey a remporté la voiture de référence V48 avec la grille 4213 lors du 5e tirage.

c. Problème 3 : Le marchand de fruits et légumes.

Code Produit Date d’envoi Date d’arrivée Fournisseur Nom Provenance

458 Fraise 2022-06-25 2022-06-26 FF Fruit Forme Espagne
875 Tomate 2022-06-24 2022-06-25 LC Légume Coop France
439 Banane 2022-06-25 2022-06-27 FF Fruit Forme Espagne
423 Pèche 2022-06-24 2022-06-26 BV Belle Vie Espagne
753 Pomme 2022-06-24 2022-06-25 RV Rouge & Verte France
486 Olive 2022-06-25 2022-06-26 LC Légume Coop France

Exemple : Les fraises de code 458 ont été envoyées le 25 Juin par Fruit Forme (FF) d’Espagne et
sont arrivées le lendemain. Les bananes de code 439, envoyées par le même fournisseur, ont été
livrées un jour plus tard, le 27 Juin.
4. Manipulation [4 points]

Les organisateurs du tournoi de football qui avaient fait appel à vous pour modéliser leur système de
données, ont une nouvelle fois besoin de vous.

A partir des extraits des tables ci-dessous, proposez les requêtes SQL qui fournissent les résultats
attendus.

Equipe Match
Code Nom Date Réf. Equipe 1 Equipe 2 Date
FCC FC Chambéry 2022-05-23 G1-1 FCC LFC 2022-08-20 18:30
LFC La Feclaz Club 2022-06-01 G1-2 null null 2022-08-20 18:30
CF Cognin Football 2022-05-19 G1-3 null FCC 2022-08-23 15:00
ASC AS Challes 2022-05-30 G1-4 LFC null 2022-08-23 15:00
JSN JS Nivolet 2022-04-24 G2-1 CF ASC 2022-08-20 15:00
ASLR AS La Ravoire 2022-04-28 G2-2 JSN null 2022-08-20 15:00
BSAFC Barby St Alban FC 2022-05-11 G2-3 ASC JSN 2022-08-23 18:30
SBC St Baldophe Club 2022-05-08 G2-4 null CF 2022-08-23 18:30
ASCB AS Chambérienne 2022-06-06 G2-5 CF JSN 2022-08-26 17:00
ELM Entente La Motte 2022-05-23 G2-6 ASC null 2022-08-26 20:30

Joueur
Id Prénom Nom Date_Naissance N° Equipe
67263 Florien Cétone 2002-04-10 1 JSN
68092 Mill Douzens 2003-01-02 10 JSN
68545 Karim Bawer 2001-12-23 1 FCC
69184 Paul Kohīma 2002-10-06 9 FCC
69568 Nabil Adjar 2003-05-02 8 JSN
69687 Mikael Polk 2002-09-26 1 CF

- « Date » de « Equipe » représente la date d’inscription de l’équipe au tournoi.

- « Date » de « Match » donne la date à laquelle le match sera joué.
- « N° » est le numéro du joueur (1 pour le gardien).

a. Créez la table Equipe.

b. Créez la table Match.
c. Sélectionnez les noms et prénoms des joueurs qui ont moins de 20 ans.
d. Sélectionnez tous les gardiens ordonnés par leur nom puis leur prénom.
e. Sélectionnez tous les joueurs de l’équipe « JS Nivolet ».
f. Sélectionnez tous les matchs de l’équipe « JS Nivolet » ordonnés par leur date.