Deuxième séance de TD - UFR SEGMI
Deuxième séance de TD .... interface rudimentaire proposé par ACCESS et que
nous n'utiliserons qu'en phase de mise au point), ... Pas de corrigé pour ça !
part of the document
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 lordinateur.
On considère les tables A et B définies par les tableaux suivants :
Identité
SexeNomPrénomMMARTINAndréFBERNARDMarieFTHOMASClémentineMMARIELouisFANDREJulie
Département
NuméroLibellé28Eure-et-Loir78Yvelines92Hauts-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 lunion 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 denregistrements 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.
Dautre part, il ny 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 :
NomPrénomMARTINAndréBERNARDMarieTHOMASClémentineMARIELouisANDREJulie
Quelle est la projection de la relation Identité sur Sexe ?
La relation résultante est décrite par le tableau suivant :
SexeMFFMF
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 lenregistrement 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.
tabType décrit le type du vin (blanc, rouge, etc.)
Deux champs : [Code type] contient un caractère permettant didentifier lenregistrement et [Type vin] décrit le type correspondant (texte).
tabRégion décrit la région de production du vin (Bourgogne, Bordeaux, etc.)
Deux champs : [Code région] contient un nombre entier permettant didentifier lenregistrement et [Libellé région] décrit nom de la région (texte).
tabBouteille décrit une bouteille de vin. Six champs :
[N° bouteille] contient un nombre entier permettant didentifier lenregistrement.
[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 « didentifier » les enregistrements de leurs tables respectives, c'est-à-dire quils 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 lobjet « 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 à laide dune interface intuitive sous forme de grille. Le but étant dapprendre SQL, on ne sen 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 sest ouverte automatiquement. Puis passer en mode SQL en cliquant sur le bouton ad hoc dans la barre doutils (en haut de la fenêtre et à gauche la légende de ce bouton de commande change en fonction du mode daffichage actif). On obtient une nouvelle fenêtre contenant « Select ; », il suffit dy inscrire le texte de la requête voulue.
Pour voir le résultat de la requête (présenté à laide dune « feuille de données », interface rudimentaire proposé par ACCESS et que nous nutiliserons quen phase de mise au point), cliquer sur le même bouton que précédemment (dont licône a changé dès quon a tapé la requête). Ce bouton permet de choisir entre plusieurs modes daffichage, 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 ¬ .
Select [Nom vin] From tabBouteille Where [Prix]
