Carnets de voyages

[45 minutes]

Soit la relation suivante, donnée avec une couverture minimale de ses DFE :

1
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

1
Auteur(#numAuteur:integer, nomAuteur:string, prenomAuteur:string)
2
Ville(#numVille:integer, nomVille:string, nomPays:string)
3
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

Modèle des carnets de voyage

Question

Écrivez le code SQL permettant de créer ce schéma normalisé dans une base de données standard.

Solution

1
CREATE TABLE Auteur (
2
  numAuteur INTEGER PRIMARY KEY,
3
  nomAuteur VARCHAR,
4
  prenomAuteur VARCHAR
5
);
6
7
CREATE TABLE Ville (
8
  numVille INTEGER PRIMARY KEY,
9
  nomVille VARCHAR,
10
  nomPays VARCHAR
11
);
12
13
CREATE TABLE CarnetDeVoyage (
14
  numAuteur INTEGER REFERENCES Auteur(numAuteur), 
15
  numVille INTEGER REFERENCES Ville(numVille),
16
  description VARCHAR,
17
  PRIMARY KEY (numAuteur, numVille)
18
);
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.
Modèle des distances
Solution
1
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
1
CREATE TABLE Trajet (
2
  nom VARCHAR,
3
  depart INTEGER  REFERENCES Ville(numVille), 
4
  arrivee INTEGER REFERENCES Ville(numVille),
5
  distance INTEGER,
6
  PRIMARY KEY (nom, depart, arrivee)
7
);
1
GRANT ALL PRIVILEGES ON Auteur TO Admin ;
2
GRANT ALL PRIVILEGES ON Ville TO Admin ;
3
GRANT ALL PRIVILEGES ON CarnetDeVoyage TO Admin ;
4
GRANT ALL PRIVILEGES ON Trajet TO Admin ;
5
6
GRANT SELECT ON Auteur TO PUBLIC ;
7
GRANT SELECT ON Ville TO PUBLIC ;
8
GRANT SELECT ON CarnetDeVoyage TO PUBLIC ;
9
GRANT SELECT ON Trajet TO PUBLIC ;