Deuxième séance de TD - UFR SEGMI

Deuxième séance de TD Algèbre relationnelle, premières requêtes en SQL Les premiers exercices doivent se faire sur papier, sans le secours de
l'ordinateur. On considère les tables A et B définies par les tableaux suivants : Identité |Sexe|Nom |Prénom |
|M |MARTIN |André |
|F |BERNARD |Marie |
|F |THOMAS |Clémentine |
|M |MARIE |Louis |
|F |ANDRE |Julie | Département |Numéro |Libellé |
|28 |Eure-et-Loir |
|78 |Yvelines |
|92 |Hauts-de-Seine |
Exercice 1 - Produit cartésien Quelle est la liste des champs du produit cartésien de ces deux relations ? La liste des champs du produit cartésien de deux relations est l'union des
listes de leurs champs. Donc le produit cartésien des relations Identité et
Département a pour champs : Sexe, Nom, Prénom, Numéro, Libellé Combien d'enregistrements le produit cartésien de ces deux relations
comprend-il ? Comment ces enregistrements sont-ils construits ? On construit les enregistrements du produit cartésien en associant chaque
enregistrement de la première relation à chacun des enregistrements de la
seconde. Par conséquent, le nombre des enregistrements du produit cartésien
est égal au nombre des enregistrements de la première relation multiplié
par le nombre des enregistrements de la seconde : | Identité . Département | = | Identité | x | Département | = 15 Ecrire la requête SQL qui calcule la relation obtenue en faisant le
produit cartésien des relations Identité et Département. Select * From Identité, Département ; * signifie que la projection se fait sur tous les champs des deux
relations.
D'autre part, il n'y a pas de restriction, donc pas de clause « Where ».
Exercice 2 - Projection Quelle est la projection de la relation Identité sur Nom et Prénom ? La relation résultante est décrite par le tableau suivant : |Nom |Prénom |
|MARTIN |André |
|BERNARD |Marie |
|THOMAS |Clémentine |
|MARIE |Louis |
|ANDRE |Julie | Quelle est la projection de la relation Identité sur Sexe ? La relation résultante est décrite par le tableau suivant : |Sexe|
|M |
|F |
|F |
|M |
|F |
Ecrire les requêtes SQL permettant de calculer les résultats de ces deux
projections. Select Nom, Prénom From Identité ;
Select Sexe From Identité ; Exercice 3 - Restriction Quelle relation faut-il effectuer pour obtenir une relation qui contienne
les identités des filles (et pas celles des garçons) ? Il faut restreindre la relation aux enregistrements dont la valeur du champ
Sexe est égale au caractère « F » : Select Nom, Prénom From Identité Where Sexe = "F" ; Exercice 4 - Lien logique On veut associer chacun des enregistrements de la table Identité à un
enregistrement de la table Département (le département de résidence de la
personne décrite dans la table Identité). Quelle modification faut-il faire sur la table Identité ? Il faut créer un lien logique entre les deux tables. Numéro est une clef
primaire possible de la table Département. Il faut donc ajouter dans la
table Identité un champ qui servira de clef externe : sa valeur devra être
égale à la valeur de la clef primaire de l'enregistrement associé dans la
table Département. Le type de cette clef externe devra être le même que
celui de la clef primaire de la table Département. Appelons « Réf_Département » cette clef externe. Ecrire la requête SQL qui associe le Nom de chaque personne décrite au
libellé de son département de résidence. Select Nom, Libellé From Identité, Département Where Réf_Département =
Numéro ; Ecrire la requête SQL qui construit la relation dont les champs sont Nom et
Prénom et qui contienne les enregistrements correspondants aux filles
résidant en Eure-et-Loir. Select Nom, Prénom From Identité, Département
Where (Réf_Département = Numéro) And (Sexe = "F" ) And (Numéro =
28) ;
La première condition (Réf_Département = Numéro) restreint le résultat aux
enregistrements associés par le lien logique, les deux autres décrivent les
conditions demandées dans l'énoncé. Les trois conditions doivent être
réalisées simultanément (opération logique « And » - « Et » en français). Les exercices suivants se font sur ordinateur en utilisant la base de
données BD_semaine_2. Cette base de données contient trois tables permettant de décrire des
bouteilles de vin. 1. tabType décrit le type du vin (blanc, rouge, etc.)
Deux champs : [Code type] contient un caractère permettant
d'identifier l'enregistrement et [Type vin] décrit le type
correspondant (texte).
2. tabRégion décrit la région de production du vin (Bourgogne, Bordeaux,
etc.)
Deux champs : [Code région] contient un nombre entier permettant
d'identifier l'enregistrement et [Libellé région] décrit nom de la
région (texte).
3. tabBouteille décrit une bouteille de vin. Six champs :
[N° bouteille] contient un nombre entier permettant d'identifier
l'enregistrement.
[Région] contient un nombre entier. Permet de gérer un lien logique
avec la table tabRégion ;
[Type] contient un caractère. Permet de gérer un lien logique avec la
table tabType.
[Nom vin] décrit le nom du vin contenu dans la bouteille (texte).
[Quantité en cave] contient un nombre entier.
[Prix] contient un nombre réel sous le format monétaire. Exercice 5 - identification des concepts de base Quelles sont les clefs primaires de ces tables et pourquoi ? [Code type], [Code région] et [N° bouteille] permettent « d'identifier »
les enregistrements de leurs tables respectives, c'est-à-dire qu'ils ne
peuvent pas prendre deux fois la même valeur pour deux enregistrements
différents. Ce sont donc les clefs primaires des trois tables. Quelles sont les clefs externes et pourquoi ? [Région] et [Type] permettent de gérer des liens logiques respectivement
avec tabRégion et tabBouteille, ce sont donc les clefs externes.
On peut vérifier que leur type est bien le même que celui de la clef
primaire de la table liée. Exercice 6 - écriture de requêtes en SQL Pour effectuer cet exercice, il faut ouvrir la base de données puis cliquer
sur l'objet « requêtes » (colonne de gauche de la fenêtre de la BD).
Pour chaque nouvelle requête, cliquer sur « Créer une requête en mode
création ». Cet outil permet de décrire les requêtes à l'aide d'une
interface intuitive sous forme de grille. Le but étant d'apprendre SQL, on
ne s'en servira pas (outil indisponible pour les contrôles qui se font sans
ordinateur !). Donc, cliquer sur Fermer dans la fenêtre « Afficher la
table » qui s'est ouverte automatiquement. Puis passer en mode SQL en
cliquant sur le bouton ad hoc dans la barre d'outils (en haut de la fenêtre
et à gauche - la légende de ce bouton de commande change en fonction du
mode d'affichage actif). On obtient une nouvelle fenêtre contenant
« Select ; », il suffit d'y inscrire le texte de la requête voulue.
Pour voir le résultat de la requête (présenté à l'aide d'une « feuille de
données », interface rudimentaire proposé par ACCESS et que nous
n'utiliserons qu'en phase de mise au point), cliquer sur le même bouton que
précédemment (dont l'icône a changé dès qu'on a tapé la requête). Ce bouton
permet de choisir entre plusieurs modes d'affichage, on en obtient la liste
en cliquant sur le triangle noir pointé vers le bas qui se situe juste à
droite du bouton. Ecrire une requête qui affiche le nom du vin des bouteilles valant moins de
10 E. Select [Nom vin] From tabBouteille Where [Prix] < 10 ; Ecrire une requête qui affiche le nom du vin et le libellé de sa région de
production. Select [Nom vin], [Libellé région]
From tabBouteille, tabRégion
Where Région = [Code région] ;
Ecrire une requête qui affiche la liste des vins provenant de la région
« Bourgogne ». Select [Nom vin] From tabBouteille, tabRégion
Where (Région = [Code région]) And ([Libellé région] = "Bourgogne"); Ecrire une requête affichant tous les libellés des régions et les types de
vin qui y sont produits et pour lesquels une bouteille de vin au moins
existe dans la cave. Comment expliquer que plusieurs enregistrements
contiennent exactement les mêmes valeurs (pour tous les champs) ? Select [Libellé région], [Type vin]
From tabBouteille, tabRégion, tabType
Where (Région = [Code région]) And (Type = [Code type]) ; La requête calcule un enregistrement pour chaque bouteille de la cave (la
clause de restriction élimine les associations d'enregistrements ne
respectant pas les liens logiques), mais la projection ne concerne pas les
champs de tabBouteille ; il est normal que deux vins de même type et de
même région soient décrits par deux enregistrements séparés de la relation,
mais la projection en permet pas de les distinguer : les valeurs des champs
[Libellé région] et [Type vin] sont identiques. Il n'y a que 19 enregistrements dans la relation créée par la requête alors
qu'il y en a 20 dans tabBouteille ! C'est parce que le lien entre
tabBouteille et tabRégion n'est pas établi pour le dernier enregistrement
de tabBouteille. Dès lors, la restriction ne permet pas de conserver un
enregistrement correspondant à ce vin (il faut que les deux liens soient
réalisés). Si la base de données avait construite en utilisant les
techniques présentées dans les cours suivants, on n'aurait pas pu
rencontrer pareille anomalie (manque de cohérence)... Terminer la séance en écrivant toutes les requêtes de consultation de la BD
que vous pouvez imaginer (restriction sur le prix, sur la quantité, en
combinant les deux...), puis en terminant les formulaires de la première
séance si besoin. Pas de corrigé pour ça !
Les tables sont suffisamment petites pour que vous puissiez vérifier que le
résultat