Cette page contient des exercices corrigés en Algèbre Relationnelle.
Exercice 1
Nous allons étudier l’algèbre relationnelle à travers un exemple de base de données et un ensemble de requêtes exprimées sur cette base de données. La base de données considérée est décrite par le schéma suivant:
JOUEUR(Nom,Prénom,Age,Nationalité)
RENCONTRE(NomGagnant,NomPerdant,LieuTournoi,Année,Score)
GAIN(NomJoueur, LieuTournoi,Année,Rang,Prime,NomSponsor)
SPONSOR(Nom,LieuTournoi,Année,Adresse,MtContribution)
La relation JOUEUR contient tous les joueurs licenciés.
La relation RENCONTRE décrit pour chaque tournoi, l’ensemble π des rencontres opposant deux joueurs (un gagnant et un perdant). La relation décrit aussi le score réalisé à chaque rencontre. Les hypothèses suivantes sont faites sur les tournois:
- deux joueurs ne se rencontrent qu’une fois dans un tournoi,
- un joueur peut gagner contre plusieurs autres joueurs dans un tournoi,
- un joueur peut perdre plusieurs fois dans un tournoi,
- un joueur participe à un ou plusieurs tournois.
La relation GAIN contient les primes et les sponsors des joueurs ayant déjà participé à un tournoi. Quel que soit le résultat d’un joueur, il perçoit une seule prime par tournoi (donc il n’a qu’un gain par tournoi), dont le montant est corrélé au rang qu’il occupe dans ce tournoi.
La relation SPONSOR contient les références des sponsors et le montant de leur contribution à chaque tournoi.
Exprimer sur cette base de données les requêtes suivantes à l’aide de l’algèbre relationnelle. Donner les expressions fonctionnelles et les arbres algébriques correspondants (c’est-à-dire les graphes d’opérateurs relationnels).
R1: Nom et primes des joueurs sponsorisés par Peugeot entre 1985 et 1990.
R2: Nom et âge des joueurs ayant participé au tournoi de Roland Garros de 1989.
R3: Nom et nationalité des joueurs sponsorisés par Peugeot et ayant gagné au moins une rencontre à Roland Garros.
R4: Nom et nationalité des joueurs ayant participé à la fois au tournoi de Roland Garros et à celui de Wimbledon, en 1985.
R5: Nom des joueurs ayant toutes leurs primes des tournois de Roland Garros supérieures à 1MF.
R6: Nom, prénom, age et nationalité des joueurs ayant participé à tous les Roland Garros.
Exercice 2
Traduire, à l’aide d’une ou deux phrases en français, la signification des questions algébriques suivantes:
a) Join(Join(Div(RENCONTRE{NomGagnant,NomPerdant,LieuTournoi,Année}, RENCONTRE{LieuTournoi,Année}), JOUEUR / NomGagnant = Nom), JOUEUR / NomPerdant = Nom){NomGagnant,Prénom,NomPerdant,Prénom}
b) Diff(Join(RENCONTRE{NomPerdant}, JOUEUR{Nom,Prénom} / Nom = NomPerdant), Join(RENCONTRE {NomGagnant}, JOUEUR{Nom,Prénom} / Nom = NomGagnant)).
En français, les requêtes algébriques proposées sont paraphrasées comme suit:
a) Nom et prénom des couples de joueurs se rencontrant à tous les tournois et dont le premier a toujours gagné contre le second.
b) Nom et prénom des joueurs ayant toujours perdu.
Exercice 3
Donner l’expression algébrique de la division.
R(X,Y) / S(Y) = piX (R) – piX ((S x piX (R) – R).
Exercice 4
Soit la base suivante :
Que valent les expressions suivantes :
Exercice 5
Exercice 3 On considère les relations suivantes:
PERSONNE (CIN, NOM, Prenom, Adresse)
Voiture(NCarteGrise, CIN, Modele)
Moto(NCarteGrise, CIN, Modele)
Ecrire les expressions représentant:
1. Afficher les personnes qui possèdent une voiture mais pas de moto ?
2. Afficher les personnes qui possèdent une voiture et une moto ?
3. Afficher les personnes qui ne possèdent ni voiture ni moto?
Exercice 6
Soit le schéma de la base de données Bibliothèque suivante :
Etudiant(NumEtd,NomEtd,PrenomEdt,AdresseEtd)
Livre(NumLivre,TitreLivre,NumAuteur,NumEditeur,NumTheme,AnneeEdition)
Auteur(NumAuteur,NomAuteur,AdresseAuteur)
Editeur(NumEditeur,NomEditeur,AdresseEditeur)
Theme(NumTheme,IntituléTheme)
Prêt(NumEtd,NumLivre,DatePret,DateRetour)
En gras les clés primaires et en italique les clés étrangères
Ecrire en langage algébrique les requêtes suivantes :
1. Le nom, le prénom et l’adresse de l’étudiant de nom ‘Alami’
2. Le numéro de l’auteur ‘Alami’
3. la liste des livres de l’auteur numéro 121
4. les livres de l’auteur nom ‘Alami’
5. le numéro de l’auteur du livre ‘comment avoir 20 en BDD’
6. le nom et l’adresse de l’auteur du livre ‘comment avoir 20 en BDD’
7. Les livres de l’auteur ‘Alami’ «édités chez l’éditeur ‘Nul part’
8. les livres de l’auteur ‘Alami’ ou ‘Belhadj’
9. les livres qui n’ont jamais été empruntés
Exercice 7
Dans la suite du TD, nous allons considérer les schémas de relations suivantes avec leurs significations intuitives :
CJH (IdCours, Jour, Heure) Le cours dont l’identifiant apparaît dans la première composante d’un n-uplet a lieu le jour spécifié dans la deuxième composante, à l’heure qui apparaît dans la troisième composante.
CS (IdCours, IdSalle) Le cours de la première composante a lieu dans la salle indiquée dans la seconde composante.
ENA (IdEtudiant, Nom, Adresse) Les étudiants dont l’identifiant apparaît comme la première composante d’un n-uplet ont un nom et une adresse qui apparaissent respectivement dans la deuxième et troisième composante.
CEN (IdCours, IdEtudiant, Note) L’étudiant de la deuxième composante a obtenu la note spécifiée dans la troisième composante au cours spécifié dans la première composante.
Ces quatre schémas forme le schéma de la base de données qui servira pour les exercices. Un exemple de valeur courante possible pour la base de données figure ci-dessous.
1.7. En s’inspirant de 1.5, montrer que la division peut être obtenue à partir de la différence, du produit cartésien et de la projection.
Exercice 8
Sur les mêmes tables que l’exercice 7, exprimer les requêtes ci-dessous dans le langage algébrique :
2.1. Donner les noms des étudiants qui suivent le cours ‘Algo’.
2.2. Donner les notes en ‘Archi’ des étudiants dont le nom est ‘Titi’.
2.3. Donner les couples (jour, heure) pour lesquels la salle ‘S1’ est occupée par un cours.
2.4. Donner les identifiants des étudiants qui n’ont que des notes ‘A’
2.5. Donner la salle où se trouve ‘Toto’ le lundi à 9h.
Exercice 9
Sur les mêmes bases de l’exercice 7 et les requêtes de l’exercice 8.
3.1. Dessinez l’arbre d’expression de la requête 2.1, faîtes descendre la restriction aussi bas que vous pouvez.
3.2. Dessinez l’arbre d’expression de la requête 2.5, faîtes descendre les restrictions et les projections aussi bas que vous pouvez.
Exercice 10
Soient R et S les relations, où les attributs A, B, C sont définis sur les domaines des lettres de l’alphabet. Donnez le résultat des requêtes suivantes :
Puis vérifier les équations suivantes :
Les trois requêtes sont en haut, les 2 équations sont fausses, le résultat est en bas.
Exercice 11
Soient les deux tables :
Relation des Employés (EMP)
EMP(ENO, ENOM, PROF, DATEEMB, SAL, COMM, DNO)
ENO: numéro d’employé, clé
ENOM: nom de l’employé
PROF: profession (directeur n’est pas une profession)
DATEEMB: date d’embauche
SAL: salaire
COMM: commission (un employé peut ne pas avoir de commission)
DNO: numéro de département auquel appartient l’employé
Relation des Départements (DEPT)
DEPT(DNO, DNOM, DIR, VILLE)
DNO: numéro de département, clé
DNOM: nom du département
DIR: directeur du département
VILLE: lieu du département (ville)
Exprimez les requêtes suivantes en algèbre relationnelle puis en SQL :
Requête 1: Donner tous les n-uplets de DEPT.
Requête 2: Donner tous les n-uplets de EMP.
Requête 3: Donner les noms et les salaires des employés.
Requête 4: Donner les professions des employés (après élimination des duplicats).
Requête 5: Donner les dates d’embauche des techniciens.
Requête 6: Faire le produit cartésien entre EMP et DEPT.
Requête 7: Donner les noms des employés et les noms de leur département.
Requête 8: Donner les numéros des employés travaillant à BOSTON.
Requête 9: Donner les noms des directeurs des départements 1 et 3. Attention : directeur n’est pas une profession !
Requête 10: Donner les noms des employés travaillant dans un département avec au moins un ingénieur.
Requête 11: Donner le salaire et le nom des employés gagnant plus qu’un (au moins un) ingénieur.
Requête 12: Donner le salaire et le nom des employés gagnant plus que tous les ingénieurs.
Requête 13: Donner les noms des employés et les noms de leurs directeurs.
Requête 14: Trouver les noms des employés ayant le même directeur que JIM. Attention : un employé peut être directeur de plusieurs départements.
