MySQL Cours TP 06

De
Publié par

INSIA Bases de données ING 1 MySQL – Cours et TP 06 Clé primaire concaténée LA BIBLE : MySQL 5.0 Reference Manual http://dev.mysql.com/doc/refman/5.0/fr/index.html Site officiel MySql : http://www-fr.mysql.com/ Documentation MySQL : http://mysql.org/ La Base de Données Open Source la plus Populaire au Monde Bertrand LIAUDET SOMMAIRE SOMMAIRE 1 MODÉLISATION : MODELE RELATIONNEL - SUITE 2 1. Clé primaire concaténée : une difficulté du modèle relationnel 2 SQL : CONSULTATION DE LA BD - SUITE 6 TP N°6 : TABLES-VERBES 7 Présentation 7 Exercice 1 : interrogation de la BD 7 Exercice 2 : Mise à jour de la BD 9 Première édition : septembre 2007 Deuxième édition : octobre 2008 INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 06 - page 1/9 - Bertrand LIAUDET MODÉLISATION : MODELE RELATIONNEL - SUITE PRINCIPALES NOTIONS Clé primaire concaténée Table-verbe Table-nom 1. Clé primaire concaténée : une difficulté du modèle relationnel Exemple traité Une bibliothèque gère les emprunts des livres de ses adhérents. Les livres ont un titre et un auteur. Les exemplaires physiques des livres ont un numéro différent par exemplaire. Ils correspondent à un livre et ont un éditeur. Les adhérents ont un nom, un prénom, une adresse et un téléphone. On souhaite archiver tous les emprunts. Un livre ne peut pas être rendu le jour même de son emprunt. La durée maximum d'emprunt doit être est de 14 jours. La bibliothèque souhaite pouvoir connaître à ...
Publié le : samedi 24 septembre 2011
Lecture(s) : 325
Nombre de pages : 9
Voir plus Voir moins


INSIA
Bases de données
ING 1
MySQL – Cours et TP 06

Clé primaire concaténée

LA BIBLE : MySQL 5.0 Reference Manual
http://dev.mysql.com/doc/refman/5.0/fr/index.html

Site officiel MySql : http://www-fr.mysql.com/
Documentation MySQL : http://mysql.org/
La Base de Données Open Source la plus Populaire au Monde
Bertrand LIAUDET


SOMMAIRE
SOMMAIRE 1
MODÉLISATION : MODELE RELATIONNEL - SUITE 2
1. Clé primaire concaténée : une difficulté du modèle relationnel 2
SQL : CONSULTATION DE LA BD - SUITE 6
TP N°6 : TABLES-VERBES 7
Présentation 7
Exercice 1 : interrogation de la BD 7
Exercice 2 : Mise à jour de la BD 9
Première édition : septembre 2007
Deuxième édition : octobre 2008

INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 06 - page 1/9 - Bertrand LIAUDET
MODÉLISATION : MODELE RELATIONNEL - SUITE
PRINCIPALES NOTIONS
Clé primaire concaténée
Table-verbe Table-nom
1. Clé primaire concaténée : une difficulté du modèle relationnel
Exemple traité
Une bibliothèque gère les emprunts des livres de ses adhérents. Les livres ont un titre et un
auteur. Les exemplaires physiques des livres ont un numéro différent par exemplaire. Ils
correspondent à un livre et ont un éditeur. Les adhérents ont un nom, un prénom, une adresse et
un téléphone. On souhaite archiver tous les emprunts. Un livre ne peut pas être rendu le jour
même de son emprunt. La durée maximum d'emprunt doit être est de 14 jours.
La bibliothèque souhaite pouvoir connaître à tout moment la situation de chaque abonné
(nombre de livres empruntés, retards éventuels). Elle souhaite aussi pouvoir faire des statistiques
sur la pratique des clients (nombre de livres empruntés par an, répartition des emprunts par
genre, nombre d’emprunts par livre, etc.
Modèle relationnel
Tables des Adhérents et des Oeuvres
De l’analyse du texte précédent, on extrait aisément la table des adhérents et celle des oeuvres :
ADHERENTS (NA, nom, prenom, adr, tel)
OEUVRES (NO, titre, auteur)
Table des Livres
On arrive aussi à extraire assez facilement la table des livres physiques :
LIVRES (NL, editeur, #NO)
Remarque
La table des livres physique est nommée : « Livres », ce qui facilitera l’interprétation des
questions (un adhérent vient rendre un livre, quels sont les livres en retard, etc.).
Tables des Emprunts
On peut envisager une table des emprunts. La table des emprunts c’est une liste de livres par
adhérent, mais aussi une liste d’adhérents par livre.
Les attributs de cette table sont les suivants :
EMPRUNTER (#NA, #NL, datemp, datretmax, datret)
Quelle est la clé primaire de cette table ?
On pourrait penser créer un attribut : « NEMP » et en faire la clé primaire.
INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 06 - page 2/9 - Bertrand LIAUDET Une difficulté du modèle relationnel : quand on a plusieurs clés étrangères dans une table

Règle fondamentale de modélisation relationnelle :
Quand on a plus d’une clé étrangère dans une table, il faut se demander si la
concaténation de plusieurs attributs de la table n’est pas clé primaire de la table.

Méthode pour déterminer la clé primaire quand on a plusieurs clés étrangères
La méthode de recherche de la clé primaire sera la suivante :
1) Se demander si la concaténation des clés étrangères ne forme pas la clé primaire.
2) Si ç’est le cas, se demander si on ne peut pas retirer quelques clés étrangères de la
concaténation.
3) Si ce n’était pas le cas, essayer d’ajouter des attributs non clé étrangère pour trouver la clé
primaire.
4) Une fois trouvé, essayer de supprimer des attributs clés étrangères de la nouvelle clé
primaire concaténée.
Application
ère
1 hypothèse : EMPRUNTER(#NA, #NL, datemp, datretmax, datret)
Est-ce que NA et NL forment bien la clé primaire ? Non : un adhérent peut emprunter plusieurs
fois le même livre à des dates différentes.
ème2 hypothèse : on ajoute datemp : EMPRUNTER(#NA, #NL, datemp, datretmax, datret)
Le tripmet (NA, NL, datemp) est clé primaire
ème
3 hypothèse : on supprime NL : EMPRUNTER(#NA, #NL, datemp, datretmax, datret)
Le couple (NA, datemp) n’est pas clé primaire.
ème
4 hypothèse : on supprime NA : EMPRUNTER(#NA, #NL, datemp, datretmax, datret)
Le couple (NL, datemp) est clé primaire.
Conclusion : EMPRUNTER(#NL, datemp, datretmax, datret, #NA)
Schéma de la BD
ADHERENTS (NA, nom, prenom, adr, tel)
OEUVRES (NO, titre, auteur)
LIVRES (NL, editeur, #NO)
EMPRUNTER(#NL, datemp, datretmax, datret, #NA)
Formalisme
1. Les clés primaires sont soulignées et placées en premier dans la liste des attributs.
ère
2. Le nom d’une clé primaire simple est constitué de : « N »+1 lettre de la table (NA).
3. Dans une clé primaire concaténée, les attributs clés étrangères sont placés en premier.
4. Les clés étrangères sont précédées d’un #.
5. Les clés étrangères sont mises en dernier dans la liste des attributs.
INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 06 - page 3/9 - Bertrand LIAUDET Distinction entre table-nom et table-verbe
On a donc deux grands types de tables : les tables-noms et les tables-verbes
Les tables-noms
En général, les tables noms représentent une réalité matérielle : les adhérents, les livres. Les
oeuvres sont aussi une table-nom.
Elles ont une clé primaire simple.
Les tables-noms
En général, les tables verbes représentent une relation, un lien entre deux tables noms.
Formalisme
Le nom des tables-noms est un nom commun au pluriel : les Adhérents.
Le nom des tables-verbes est un verbe à l’infinitif : Emprunter. Ce verbe désigne la relation que
la table verbe établit entre les deux tables-noms : les adhérents empruntent des livres.
Intérêt de la clé primaire concaténée
Pourquoi n’a-t-on pas utilisé un attribut NE (numéro d’emprunt) comme clé primaire ?
Pour 3 raisons :
• En déclarant (NL, datemp) comme clé primaire, on garantit l’unicité du couple NL, datemb,
ce qui garantit la cohérence sémantique des données.
• On évite de créer un attribut inutile.
• On met au jour le fait qu’un emprunt est défini par le couple (NL, datemb). Cela permet de
mieux comprendre les données.
Clé primaire concaténée ou clé secondaire concaténée ?
Une fois qu’on sait que (NL, datemb) devrait être clé primaire, on peut aussi ajouter NE comme
clé primaire et faire de (NL, datemb) une clé secondaire, c’est-à-dire un couple d’attributs
unique.
Formalisme
EMPRUNTER(NE, (#NL, datemp), datretmax, datret, #NA)
On met la clé secondaire juste après la clé primaire, entre parenthèses pour la repérer.
MySQL
UNIQUE (NL, datemp)
Méthode
On évitera toujours cette solution !
INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 06 - page 4/9 - Bertrand LIAUDET Syntaxe
Clé primaire concaténée
CREATE TABLE emprunter (
NL integer not null, foreign key(NL) references livres(NL),
datEmp date not null,

primary key (NL, datEmp)
);
Clé étrangère concaténée
Une clé primaire concaténée peut aussi devenir clé étrangère dans une autre table :
CREATE TABLE test (
NL integer not null,
datEmp date not null,
foreign key(NL, datemp) references emprunter(NL, datemp)
) ;
Graphe des tables
EMPRUNTER



LIVRES ADHERENTS



OEUVRES

INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 06 - page 5/9 - Bertrand LIAUDET SQL : CONSULTATION DE LA BD - SUITE
Les requêtes SQL qu’on applique sont les mêmes que celles déjà abordées dans les cours/TP
précédents :
• Projection
• Restriction
• Tri
• Statistiques
• Jointures naturelles

Les jointures naturelles fonctionnent selon le même principe que celui abordé dans le cours/TP
précédent.
INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 06 - page 6/9 - Bertrand LIAUDET TP N°6 : TABLES-VERBES
Présentation
L’exercice consiste à écrire des requêtes SQL d’interrogation de la BD.
On utilise la base des employés.
Exercice 1 : interrogation de la BD
Présentation
On travaillera sur les tables suivantes :

OEUVRES(NO, TITRE, AUTEUR)
• NO numéro de l’œuvre. Clé primaire.
• LIVRE titre de l’oeuvre
• AUTEUR auteur de l’oeuvre

ADHERENTS(NA, NOM, VILLE)
• NA numéro d’adhérent. Clé primaire.
• NOM nom de l’adhérent.
• PRENOM prénom de l’adhérent.
• ADR adresse de l’adhérent.
• TEL téléphone de l’adhérent.

LIVRES (NL, EDITEUR, #NO)
• NL numéro du livre. Clé primaire.
• EDITEUR éditeur du livre
• NO numéro de l’oeuvre. Clé étrangère.

EMPRUNTER(#NL, DATEMP, DATRETMAX, DATRET, #NA)
• #NL numéro de livre. Clé primaire. Clé étrangère.
• DATEMP date d’emprunt du livre. Clé primaire.
• DATRETMAX date limite de retour autorisée.
• DATRET date de retour effectif du livre.
• #NA numéro d’adhérent. Clé étrangère.
INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 06 - page 7/9 - Bertrand LIAUDET Travail à faire
• Dans un fichier texte à votre nom+TP06.txt, écrire les questions et les réponses les unes à la
suite des autres.
• Après chaque requête, on met, en commentaire, le nom de l’attribut clé primaire de la table
résultat
• Mettez le résultat obtenu dans le fichier (copier-coller).
• Sur le papier : faire le graphe de la question.
Méthode de travail
A partir du fichier texte votreNomTP06.txt, faire des copier-coller dans la calculette SQL ou
dans utiliser un fichier de test avec un source, et copier coller la bonne requête dans le rapport
de TP.
Ordre de projection des attributs
Requête sans fonction de groupe
Attributs de tri, Cle Primaire, Clé Significative, Attributs demandés, Attributs de restriction
Requête avec fonctions de groupe
Attributs de tri, attributs du group by, fonctions de groupe demandées, fonctions de groupe de
restriction (du having).
Interrogation de la BD
1. Télécharger le script de création de la BD : BiblioTP06.txt
2. Faire le graphe des tables.
3. Lancer ce script de création de la BD.
4. Consulter le contenu de toutes les tables.
5. Faire le produit cartésien de la table Emprunter avec la table des Livres et la table des
Adhérents.
Combien y a-t-il d’attributs dans la table résultat. Afficher les résultats en mode « fiche »
(\G).
Combien y a-t-il de tuples dans la table résultat ?
Refaire la question en projetant uniquement les clés primaires et significatives.
Quel tri faudrait-il faire pour clarifier l’interprétation des résultats ?
6. Faire la jointure naturelle de la table Emprunter avec la table des Livres et la table des
Adhérents.
Combien y a-t-il d’attributs dans la table résultat. Afficher les résultats en mode « fiche »
(\G).
Combien y a-t-il de tuples dans la table résultat ?
Refaire la question en projetant uniquement les clés primaires et significatives.
Quel tri faudrait-il faire pour clarifier l’interprétation des résultats ?
INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 06 - page 8/9 - Bertrand LIAUDET 7. Faire la jointure naturelle de la table Emprunter avec la table des Livres, la table des Oeuvres
et la table des Adhérents.
Combien y a-t-il d’attributs dans la table résultat.
Combien y a-t-il de tuples dans la table résultat ?
Refaire la question en projetant uniquement les clés primaires et significatives.
8. Quels sont les livres actuellement empruntés ?
9. Quels sont les livres empruntés par Jeannette Lecoeur ? Vérifier dans la réponse qu’il n’y a
pas d’homonymes.
10. Quels sont tous les livres empruntés en août 2008.
11. Tous les adhérents qui ont emprunté un livre de Fedor Dostoievski.
12. Quels sont le ou les auteurs du titre « Voyage au bout de la nuit »
13. Quels sont les ou les éditeurs du titre « Narcisse et Goldmund »
14. Quels sont les adhérents actuellement en retard ?
15. Quels sont les livres actuellement en retard ?
16. Quels sont les adhérents en retard avec le nombre de livre en retard et la moyenne du
nombre de jour de retard.
17. Nombre de livres empruntées par auteur.
18. Nombre de livres empruntés par éditeur.
19. Durée moyenne des emprunts rendus. On commencera par afficher les durée des emprunts
rendus.
20. Durée moyenne des retards pour l’ensemble des emprunts.
La fonction « if » permet de tester une valeur et de renvoyer ce qu’on souhaite selon la
vérité ou la fausseté de la valeur testée.
if(a<0, 0, a) : permet de ramener les valeurs négatives de a à 0.
21. Durée moyenne des retards parmi les seuls retardataires.
Exercice 2 : Mise à jour de la BD

22. La bibliothèque vient d’acquérir un nouveau livre de la peste, toujours chez Hachette. Faire
la mise à jour de la BD.
23. La bibliothèque vient d’acquérir un nouveau livre : « Du scribe au savant » de Yves Gingras
aux éditions PUF. Faire la mise à jour de la BD.
24. Un nouvel adhérent vient s’inscrire : Olivier DUPOND, 76, quai de la Loire, 75019 Paris,
téléphone :
25. Martine CROZIER vient emprunter Les fleurs du mal chez Hachette, livre n° 22 et Le rouge
et le noir chez Hachette, livre n°23. Faire les mises à jour de la BD.
26. M. Cyril FREDERIC ramène les livres qu’il a empruntés. Faire la mise à jour de la BD.
INSIA - BASES DE DONNÉES – ING 1 – Piscine Cours 06 - page 9/9 - Bertrand LIAUDET

Soyez le premier à déposer un commentaire !

17/1000 caractères maximum.