Carnets de voyages
[45 minutes]
Soit la relation suivante, donnée avec une couverture minimale de ses DFE :
CarnetDeVoyage (numAuteur:integer, nomAuteur:string, prenomAuteur:string, numVille:integer, nomVille:string, nomPays:string, description:string)
numAuteur → nomAuteur
numAuteur → prenomAuteur
numVille → nomVille
numVille → nomPays
numAuteur, numVille → description
Question
Rappeler la définition formelle d'une clé.
Énoncez la ou les clés existantes.
Pour chaque clé, énoncer les DF qui prouvent que c'est une clé et explicitez les axiomes d'Armstrong utilisés pour établir ces DF.
Dites en quelle forme normale est la relation (montrez pourquoi).
Solution
Clé
Une clé est un groupe d'attributs minimal qui détermine tous les attributs de la relation.
Il y a une unique clé (numAuteur, numVille).
numAuteur, numVille → nomAuteur
numAuteur, numVille → prenomAuteur
numAuteur, numVille → nomVille
numAuteur, numVille → nomPays
numAuteur, numVille → description
La réflexivité et la transitivité sont utilisées à chaque fois de la même façon, par exemple, pour le premier :
numAuteur, numVille → numAuteur ET numAuteur → nomAuteur DONC numAuteur, numVille → nomAuteur
NF
La relation est en 1NF, on a identifié une clé et les attributs sont atomiques. Elle n'est pas en 2NF car des attributs faisant partie de la clé déterminent d'autres attributs, par exemple : numAuteur → nomAuteur.
Question
Proposez un schéma normalisé en 3NF, sans perte, en faisant apparaître les clés.
Solution
Auteur(#numAuteur:integer, nomAuteur:string, prenomAuteur:string)
Ville(#numVille:integer, nomVille:string, nomPays:string)
CarnetDeVoyage(#numAuteur=>Auteur(numAuteur), #numVille=>Ville(numVille), description:string)
Question
Rétro-concevez le modèle UML qui aurait permis d'arriver directement à ce résultat normalisé.
Solution
![](../res/carnetDeVoyage.png)
Question
Écrivez le code SQL permettant de créer ce schéma normalisé dans une base de données standard.
Solution
CREATE TABLE Auteur (
numAuteur INTEGER PRIMARY KEY,
nomAuteur VARCHAR,
prenomAuteur VARCHAR
);
CREATE TABLE Ville (
numVille INTEGER PRIMARY KEY,
nomVille VARCHAR,
nomPays VARCHAR
);
CREATE TABLE CarnetDeVoyage (
numAuteur INTEGER REFERENCES Auteur(numAuteur),
numVille INTEGER REFERENCES Ville(numVille),
description VARCHAR,
PRIMARY KEY (numAuteur, numVille)
);
Question
De nouvelles informations doivent être ajoutées à la base, afin de connaître les distances entre les villes. Ces nouvelles spécifications sont présentées sous la forme d'un package complémentaire. Effectuez le passage au relationnel de ce schéma (n'ajoutez que ce qui est nouveau). Expliquez ce que permet la clé locale nom
de trajet
.
![](../res/trajet.png)
Solution
Trajet (#nom:string, #depart=>Ville(numVille), #arrivee=>Ville(numVille), distance:integer)
#nom
permet de définir plusieurs trajets différents entre deux mêmes villes.
Question
Écrivez le code SQL permettant d'implémenter le package distances
.
Puis, donnez tous les droits sur l'ensemble du schéma à un utilisateur Admin
, et les droits en lecture sur l'ensemble du schéma à tous les utilisateurs de la base.
Solution
CREATE TABLE Trajet (
nom VARCHAR,
depart INTEGER REFERENCES Ville(numVille),
arrivee INTEGER REFERENCES Ville(numVille),
distance INTEGER,
PRIMARY KEY (nom, depart, arrivee)
);
GRANT ALL PRIVILEGES ON Auteur TO Admin ;
GRANT ALL PRIVILEGES ON Ville TO Admin ;
GRANT ALL PRIVILEGES ON CarnetDeVoyage TO Admin ;
GRANT ALL PRIVILEGES ON Trajet TO Admin ;
GRANT SELECT ON Auteur TO PUBLIC ;
GRANT SELECT ON Ville TO PUBLIC ;
GRANT SELECT ON CarnetDeVoyage TO PUBLIC ;
GRANT SELECT ON Trajet TO PUBLIC ;