TD
Modèle Entité Association
Exercice 1
Une personne désire modéliser le système d’information correspondant aux
réceptions qu’elle organise (personnes invitées, menus, ...). Ce système
d’information doit lui permettre, en autre chose, de pouvoir l’aider à organiser une
réception en lui offrant la possibilité de construire sa liste d’invités, ainsi que son
menu et les vins associés. Une réception a lieu à une date donnée et y sont invitées
des personnes dont on connait le nom, le prénom, leur sexe, leur âge et leur
profession (l’identification d’une personne se fait par son nom et son prénom). Le
repas servi lors d’une réception comprend un certain nombre de plats identifiés par
leur nom (" poulet à la mexicaine " par exemple) et leur nature (" entrée froide ",
" dessert " par exemple). Pour pouvoir réussir un menu, il faut que les vins servis
soient en accord avec les plats. On dispose donc pour chaque plat d’une liste de types
de boissons possibles caractérisés par leur région et un type). Pour que la réception
soit réussie, il faut éviter qu’une dispute vienne gâcher l’événement et pour se faire
la connaissance des amitiés et inimitiés entre personnes est primordiale. Enfin le
dernier ingrédient d’une réception réussie est d’offrir au menu des plats que les
invités apprécient et surtout d’éviter de leur servir des plats qu’ils n’aiment pas.
Exercice 2
1. Introduction
Une société de transport aérien décide de repenser l'implantation de son système
d'information en évoluant d'une approche fichiers vers une approche BD
relationnelle. Pour mener à bien cette opération, la direction informatique de
l'entreprise adopte une démarche en deux phases : une première phase dédiée à la
conception des données et des traitements, et une seconde phase consacrée à
l'implantation des données et des traitements à l'aide du SGBD relationnel retenu.
Le but de l'étude de cas proposée est de réaliser la phase de conception des
données uniquement. La démarche préconisée est de représenter la réalité de
l'entreprise en utilisant le modèle entité-association, étendu avec le concept de
généralisation, avec la prise en compte des contraintes d'intégrité (en particulier les
dépendances fonctionnelles). Le résultat de cette modélisation est constitué par un
schéma conceptuel au format entité/association étendu. Ce schéma conceptuel doit
alors être transformé en un schéma relationnel normalisé équivalent.
2. Description du système d'information
Pour vous permettre de construire le schéma conceptuel de la base, nous donnons
maintenant la description des informations à gérer au sein de l'entreprise de
transport aérien.
L'objectif est d'assurer la gestion, d'une part des employés, des vols et des appareils,
d'autre part des clients et des billets.
L'ensemble des employés se compose du personnel navigant et du personnel au sol.
Parmi le personnel navigant, on distingue les pilotes et les membres d'équipage
(hôtesses, stewart, ...).
Un employé est caractérisé par un numéro de sécurité sociale, un nom, un prénom,
une adresse et un salaire. Un personnel navigant comptabilise un certain nombre
d'heures de vol. Un pilote possède une licence définie par un numéro. Un
membre d'équipage assure une certaine fonction (hôtesse, stewart, ...).
Un appareil est identifié de manière unique par un numéro d'immatriculation. Il
possède un type (A320, B747, ...) et il offre une certaine capacité en nombre de
passagers.
Chaque liaison desservie par au moins un vol de la compagnie est définie par une
ville d'origine et une ville de destination; de plus, elle est identifiée par un numéro.
Un vol est identifié par un numéro de vol. Il est caractérisé par une période de validité
définie par deux dates. Durant cette période, ce vol a des horaires fixes (heure départ
et heure arrivée). Un vol dessert une liaison et une seule. Qui plus est, un appareil et
un seul est associé à un vol donné.
Pour chaque vol enregistré, on suppose qu'il existe un départ tous les jours pendant
la période de validité de ce vol. Un départ est défini par un numéro de vol et une date
de départ. A un départ sont associés un ou deux pilotes et deux à quatre membres
d'équipage. Un départ est également caractérisé par un nombre de places libres et un
nombre de places occupées. Une place occupée est une place ayant donné lieu à une
réservation avec émission d'un billet.
Un passager est identifié par un numéro. Il possède un nom, un prénom, une adresse,
une profession et une banque. Il effectue des réservations qui donnent lieu à
l'émission de billets.
Un billet est repéré par un numéro. Il comporte une date d'émission et un prix. Un
billet référence un départ et un seul. Pour simplifier, on suppose qu'un billet concerne
un client et un seul.
TD
Dépendance fonctionnelle
Exercice 1
On considère une relation R construite sur les attributs Propriétaire, Occupant,
Adresse, Noapt, Nbpièces, Nbpersonnes, un nuplet (p, o, a, n, nb1, nb2) ayant la
signification suivante :
la personne o habite avec nb2 personnes l'appartement de numéro n ayant nb1
pièces dont le propriétaire est p.
Une analyse de cette relation nous fournit un ensemble initial E de dépendances
fonctionnelles :
occupant adresse
occupant noapt
occupant nbpersonnes
adresse, noapt propriétaire
adresse, noapt occupant
adresse, noapt nbpièces
1- Donner l'ensemble des dépendances fonctionnelles élémentaires engendrées
par E.
2- Quelles sont les clés potentielles de R ?
3- R est-elle en 3ème forme normale ?
Exercice 2 :
On considère le schéma relationnel R défini sur les attributs suivants, C : cours, P :
professeur, H : heure, S : salle, E : étudiant, N : note
un nuplet (c, p, h, s, e, n) a pour signification que le cours c est fait par le
professeur p à l'heure h dans la salle s par l'étudiant e qui a reçu la note n.
L'ensemble E des dépendances fonctionnelles initiales est le suivant :
CP
H, S C
H, P S
C, E N
H, E S
1- Donner l'ensemble des dépendances fonctionnelles élémentaires engendrées
par E.
2- Quelle est la clé de la relation R ? Montrer qu'elle est unique.
3- Quelle est la forme normale de la relation R ? Si elle n'est pas en 3FN proposer
une décomposition en 3FN.
Corrigé TD1
Modèle Entité Association
Corrigé Exercice 1 :
Corrigé Exercice 2 :
PASSAGER(NoP, nom, prenom, adresse, profession, banque)
EQUIPAGE(NoSecu, nom, adresse, adresse, salaire, NbHrvol, fonction)
PILOTE(NoSecu, nom, prenom, adresse, salaire, NbHrvol, Nolicence)
Pour la relation PILOTE une autre clé possible est Nolicence.
AUSOL(NoSecu, nom, prenom, adresse, salaire)
NAVIGANT(NoSecu, nom, prenom, adresse, salaire, NbHrvol)
C’est une relation construite à partir des relations EQUIPAGE et
PILOTE par la requête SQL :
EMPLOYE(NoSecu, nom, prenom, adresse, salaire)
C’est une relation construite à partir des relations EQUIPAGE, PILOTE
et AUSOL par une reqête SQL avec UNION
PERSONNE(NoP, nom, prenom, adresse) avec NoP <-> NoSecu
C’est est une relation construite à partir des relations PASSAGER,
EQUIPAGE,PILOTE et AUSOL par une requête SQL avec UNION.
BILLET(NoB, dateem, prix, NoP, NoV, DateDep)
NoP clé étrangère sur PASSAGER et NoV, DateDept clé étrangère sur
DEPART.
DEPART(NoVol, DateDep, nblibre, nboccup, NoPil1, NoPil2, NoEquip1,
NoEquip2, NoEquip3, NoEquip4)
NoPil2, NoEquip3 et NoEquip4 peuvent avoir une valeur nulle.
VOL(NoV, DateDeb, DateFin, hrdep, hrarr, NoL, NoImm)
avec NoL clé étrangère sur LIAISON et NoImm clé étrangère sur APPAREIL.
LIAISON(NoL, villedep, villearr)
APPAREIL(NoImm, type, capa)
En dehors des contraintes d'intégrité liées au modèle relationnel (contraintes de
domaine, de non nullité, d'unicité de valeur, et contraintes d'intégrité référentielle) la
base de données associée au schéma relationnel précédent est cohérente si un certain
nombre de contraintes liées à l'application sont vérifiées :
Pour un départ donné D d'un vol V associé à un appareil A, on a :
o A.capa=D.nboccup + D.nblibre
pour un départ donné D d'un vol V on a :
o V.DateDeb <= D.DateDep <= V.DateFin
etc...
Dépendance fonctionnelle
Corrigé Exercice 1
1- Fermeture transitive de E :
occupant --> adresse et occupant --> noapt donc
occupant --> adresse, noapt
Par transitivité on a donc :
occupant --> propriétaire
occupant --> nbpièces
On a donc :
occupant --> adresse, noapt, nbpersonnes, propriétaire, nbpièces
adresse, noapt --> propriétaire, occupant, nbpièces, nbpersonnes
La DF adresse, noapt --> nbpersonnes est obtenue par transitivité avec
occupant
2- Clés potentielles de R?
Une clé est un (ensemble d') attribut qui dérive tous les autres. Si on regarde la
fermeture transitive de E, on voit que :
occupant ainsi que
adresse, noapt
sont dans ce cas. Il y a donc deux clés potentielles.
3- Forme normale de R :
Pour déterminer la forme normale de R, il faut d'abord distinguer les attributs clés
des attributs non clés :
Attributs clés : adresse, occupant, noapt
Attributs non clés : nbpersonnes, propriétaire, nbpièces
Une relation est forcément en 1ère forme normale. Elle est en 2ème forme normale si
tous les attributs non clés dépendent pleinement des clés. Ici c'est le cas, aucun
attribut non clé ne dépend que de adresse ou noapt.
Une relation est en 3eme forme normale s'il n'existe pas de dépendance
fonctionnelle entre deux attributs non clés. C'est le cas ici.
R est donc en 3ème forme normale.
Corrigé Exercice 2
1- Fermeture transitive de E :
C --> P et H, P --> S donc H,C --> S
H, S --> C et C --> P donc H, S --> P
H, P --> S et H, S --> C donc H, P --> C
H, E --> S et H, S --> C donc H, E --> C donc H, E --> P
H, E --> C et C, E --> N donc H, E --> N
En résumé on a :
C --> P
H, C --> S
H, S --> C, P
H, P --> S, C
C, E --> N
H, E --> S, C, P, N
2- Clé de R :
De la fermeture transitive on déduit que H, E est une clé potentielle (dérive tous les
autres attributs).
Elle est unique car H, E sont les seuls attributs qui ne sont pas en partie droite de DF.
Donc ils appartiennent forcément à toutes les clés. Comme H, E est déjà une clé, il
ne peut y en avoir d'autres (critère de minimalité).
3- Décomposition de R :
R1, R2, R3 et R4 sont obtenues en décomposant R comme suit :
1. R1 est obtenue en décomposant le schéma initial selon la DF C, E --> N.
C'est la seule DF de R1 donc la clé est C, E. R1 est bien évidemment en
3eme forme normale (une seule DF).
2. R2 est obtenue par la DF C --> P. Là encore une seule DF, donc C est la clé
de R2 et R2 est en 3eme forme normale.
3. R3 est obtenue par la DF H, S --> C ou la DF H, C --> S. Deux clés
possibles H, S ou bien H, C. R3 est aussi en 3eme forme normale.
4. R4 est obtenue par la DF H, E --> C. La clé est donc H, E et R4 est en
3eme forme normale.
