Bases de Données Relationnelles

TD/TP 6 – Conception avec UML et implémentation avec SQL

Exercice 1
Le diagramme de classes ci-après a été normalisé (3ème forme normale) pour modéliser une base de
données relationnelle.

W
V
assoc_2 a assoc_1 c
d
* b * *
e

1 f

X
g
h

i
*
assoc_3
Y Z
assoc_4
* k * 0..3 l

(1a) Donnez toutes les dépendances fonctionnelles élémentaires et directes que vous pouvez déduire du
diagramme de classe.

(1b) Donnez les tables de la base de données relationnelle correspondant au diagramme de classes. Pré-
cisez quels sont les attributs qui forment les clés primaires et étrangères.

1
Exercice 2
Le gestionnaire d’un port de commerce souhaite informatiser le trafic généré par les marchandises trans-
portées dans des conteneurs partant ou arrivant au port. Il veut savoir pour chaque bateau quel poids de
marchandise contient chaque conteneur, et, pour chaque conteneur, dans quel pays il a été rempli ou
dans quel pays il sera vidé (un seul pays possible), ainsi que la taille du conteneur afin de gérer son sto-
ckage à terre ou son chargement (on supposera deux tailles pour simplifier : « standard » et « demi »).
Il ne s’intéresse qu’aux bateaux transportant des conteneurs. Il veut pouvoir savoir, par exemple, quel
poids de marchandise a été transporté par un bateau donné mais également quel poids a été livré dans
un pays Il utilise un système d’identification des conteneurs, chaque conteneur a un identifiant unique.
Pour chaque conteneur il souhaite avoir le nom du propriétaire et le nom de chaque bateau.

(2a) UML Modélisez cette base de données en utilisant UML.

(2b) Base de données relationnelle Donnez les représentations textuelles et graphiques de la base de
données permettant de stocker les informations modélisées à la question précédente.

(2c) Création de la base avec SQL Créez sous PostgreSQL la base de données obtenue à la question
précédente.

(2d) Insertion des données Insérez une ligne dans chacune des tables, vous utiliserez des séquences
pour chaque clé primaire des tables.

(2e) Interrogation de la base de données Exprimez en SQL les requêtes ci-après. Pour tester vos re-
quêtes vous ajouterez de nouvelles lignes dans vos tables. Chaque requête sera stockée dans une
vue.
— Affichez les noms des bateaux en ordre alphabétique.
— Quel est le poids total des marchandises arrivées dans le port en 2014 ?
— En 2014, combien de conteneurs ont quitté le port ?
— Pour chaque bateau donnez son nom et la variation du poids des marchandises transportées
entre l’arrivée et le départ du port.
— Même chose que la requête 2.5.4 mais trié en ordre croissant de la variation de poids.
— Quel est le bateau qui a chargé le plus de marchandises en 2014 ?
— Quels sont les pays qui ont expédié vers le port du gestionnaire plus de marchandises que le
Brésil ?

2
Exercice 3
On souhaite construire le diagramme de classes (en 3ème forme normale) d’une application pour une
agence centrale de réservation. Le domaine étudié est la réservation de chambres d’hôtel dans des sta-
tions (sports d’hiver, balnéaires, thermales, climatiques). La liste des variables du dictionnaire de don-
nées est la suivante :
— nom_hotel : Nom de l’hôtel
— adresse_hotel : Adresse de l’hôtel
— num_telephone : Numéro de téléphone de l’hôtel
— capacite_hotel : Capacité de l’hôtel (nombre de chambres)
— categorie_hotel : Catégorie de l’hôtel (nombre d’étoiles)
— capacite_chambre : Capacité de la chambre
— degre_confort : Degré de confort de la chambre,
— exposition_chambre : Exposition de la chambre (nord, sud, est, ouest, nord-est, nord- ouest, ...)
— nom_station : Nom de la station
— type_station : Type de la station (par exemple : sports d’hiver, balnéaire, etc.)
— distance : Distance station-gare
— nom_gare : Nom de la gare
— existe_navette : Existence d’une navette,
— periode : Période tarifaire (bleu, blanc, rouge) – dépend du type de la station
— debut_periode : Date de début de la période tarifaire – dépend du type de la station
— fin_periode : Date de fin de la période tarifaire – dépend du type de la station
— prix : Prix journalier de la chambre : déterminé dans chaque station par la catégorie de l’hôtel, la
période tarifaire et le type de chambre,
— type_chambre : Type de chambre – dépend de la capacité de la chambre, du degré de confort et
de l’exposition
— nom_client : Nom du client (personne émettant une réservation)
— adresse_client : Adresse du client
— nom_resident : Nom du résident (personne occupant une chambre, souvent le client)
— adresse_resident : Adresse du résident
— date_debut : Date de début du séjour,
— date_fin : Date de fin du séjour
— date_reservation : Date d’émission de la réservation
— num_reservation : Numéro de la réservation
— num_client : Numéro de client
— num_telephone : Numéro de téléphone du client
— nb_personnes : Nombre de personnes occupant une chambre de la réservation
— num_chambre : Numéro de la chambre réservée
— montant_arrhes : Montant des arrhes versées

Normalisez la base de données en 3ème forme normale.