Srie 3 : Exercices sur les langages de manipulation de donnes relationnelles I.

Corrig de l'algbre relationnelle

1. [DateNaissance, Adresse] [Prnom = Juliette Nom = Rochat] Employ 2. [Nom, Adresse] ( Employ [NomD = Recherche] Dpartement ) 3. [Nom, Adresse] ( Employ [NAvs->Suprieur] [NAvs] [Prnom = Juliette Nom = Rochat] Employ ) 4. [Nom] ( Employ [Heures>10] Travaille * [Lieu="Genve"] [NPro, Lieu] Projet ) 5. [NomP] (Projet *Travaille * [NAvs] [(Nom=Muller Prnom="Jean") (Nom=Grandjean Prnom="Annie")] Employ ) 6. [NomP] (Projet *Travaille * [NAvs] [Nom=Muller Prnom="Jean"] Employ )

[NomP] (Projet *Travaille * [NAvs] [Nom=Grandjean Prnom="Annie"] Employ )

7. [Nom, Prnom] ( Employ ( Employ * [NAvs] Travaille) ) 8. ProDept = [NPro, NDep] (Travaille * Employ) Rsultat = ProDept / [NDep] Dpartement Autre formulation, sans la division. Dans ce cas, il faut calculer l'inverse, savoir: les projets qui n'ont pas de participant dans chaque dpartement. La relation P contient les couples (NPro, NDep) tels que le projet NPro n'a pas de participant du dpartement NDep: P = (( [NPro] Projet) x ( [NDep] Dpartement)) - [NPro, NDep] ( Travaille * Employ ) Rsultat = ( [NPro] Projet) - ( [NPro] P) 9. On calcule d'abord les employs qui travaillent sur au moins un projet de Genve (EmpG). EmpG = [Nom, NAvs] (Employ * Travaille * [NPro] [Lieu="Genve"] Projet ) (On conserve le NAvs, bien qu'il ne soit pas demand dans le rsultat, au cas o il y ait des homonymes parmi les employs) Rsultat = [Nom] ( ( [Nom, NAvs] Employ) EmpG ) 10. On calcule d'abord les employs qui travaillent sur au moins un projet qui n'est pas Genve (EmpNG). EmpNG = [Nom, NAvs] ( Employ * Travaille * [NPro] [Lieu"Genve"] Projet ) Rsultat = ( [Nom] ( EmpG EmpNG ) II Traduction en franais: 1. Noms et prnoms des employs qui gagnent plus que leur suprieur immdiat. 2. Noms des projets sur lesquels ne travaille aucun employ du dpartement du projet.

II. Corrig en calcul relationnel des tuples

1. e Employ { [Link], [Link] / [Link] = Juliette [Link] = Rochat } 2. e Employ, d Dpartement { [Link], [Link] / d ([Link] = [Link] [Link] = Recherche) } 3. e1, e2 Employ {[Link], [Link] / e2 ([Link] = [Link] [Link] = Juliette [Link] = Rochat) } 4. e Employ, t Travaille, p Projet { [Link] / t p ([Link] = [Link] [Link] = [Link] [Link] > 10 [Link] = Genve ) } 5. e Employ, t Travaille, p Projet {[Link] / e t ( (([Link]=Muller [Link]="Jean") ([Link]=Grandjean [Link]="Annie")) [Link]=[Link] [Link]=[Link] ) } 6. e1, e2 Employ, t1, t2 Travaille, p Projet {[Link] / e1 e2 t1 t2 ( [Link]=Muller [Link]="Jean" [Link]=[Link] [Link]=[Link] [Link]=Grandjean [Link]="Annie" [Link]=[Link] [Link]=[Link] ) } 7. e Employ, t Travaille { [Link] / t ([Link] [Link]) } 8. e Employ, t Travaille, p Projet, d Dpartement { [Link] / d e t ([Link] = [Link] [Link] = [Link] [Link] = [Link]) } 9. e Employ, t Travaille, p Projet { [Link] / t p ( [Link] = [Link] [Link] = [Link] [Link] = Genve ) } 10. e Employ, t Travaille, p Projet { [Link] / t ( [Link] [Link] p ( [Link] = [Link] [Link] = Genve )) t ( [Link] = [Link] ) } ou { [Link] / t p ( [Link] = [Link] [Link] = [Link] [Link] = Genve ) t ( [Link] = [Link] ) } Dans ces deux requtes, la dernire partie de la formule : t ( [Link] = [Link] ) } sert liminer les employs qui ne travaillent sur aucun projet.

II Traduction en franais:
1. Noms et prnoms des employs qui travaillent sur un mme projet que Juliette Rochat. 2. Noms et prnoms des employs qui travaillent sur tous les projets de leur dpartement.

III. Corrig du SQL

Question I 1. SELECT DateNaissance, Adresse FROM Employ WHERE Nom='Rochat' AND Prnom='Juliette' ; 2. SELECT Nom, Adresse FROM Employ WHERE NDep IN (SELECT NDep FROM Dpartement WHERE NomD='Recherche' ) ; 2. (autre solution) SELECT Nom, Adresse FROM Employ, Dpartement WHERE [Link]=[Link] AND NomD='Recherche' ; 3. SELECT [Link], [Link] FROM Employ E, Employ JR WHERE [Link]='Rochat' AND [Link]='Juliette' AND [Link]=[Link]; 4. SELECT Nom FROM Employ, Travaille, Projet WHERE [Link]=[Link] AND [Link]=[Link] AND Lieu='Genve' AND Heures>10; 5. SELECT NomP FROM Employ, Travaille, Projet WHERE [Link]=[Link] AND [Link]=[Link] AND ((Nom='Muller' AND Prnom='Jean') OR (Nom='Greanjean' AND Prnom='Annie')) ; 6. ( SELECT NomP FROM Employ, Travaille, Projet WHERE [Link]=[Link] AND [Link]=[Link] AND Nom='Muller' AND Prnom='Jean' ) INTERSECT ( SELECT NomP FROM Employ, Travaille, Projet WHERE [Link]=[Link] AND [Link]=[Link] AND Nom='Greanjean' AND Prnom='Annie') ; 7. SELECT Nom, Prnom FROM Employ WHERE NOT EXISTS ( SELECT * FROM Travaille WHERE [Link]=[Link] ) ;

7. (autre solution) SELECT Nom, Prnom FROM Employ WHERE NAVS NOT IN ( SELECT NAVS FROM Travaille ) ; 8. SELECT NPro FROM Projet WHERE NOT EXISTS ( SELECT * FROM Dpartement WHERE NOT EXISTS ( SELECT * FROM Employ, Travaille WHERE [Link]=[Link] AND [Link]=[Link] AND [Link]=[Link] ) ) ; 9. SELECT Nom FROM Employ WHERE NOT EXISTS ( SELECT * FROM Travaille, Projet WHERE [Link]=[Link] AND [Link]=[Link] AND Lieu='Genve' ) ; 10. SELECT Nom FROM Employ WHERE NOT EXISTS ( SELECT * FROM Travaille, Projet WHERE [Link]=[Link] AND [Link]=[Link] AND Lieu'Genve' ) AND EXISTS ( SELECT * FROM Travaille WHERE [Link]=[Link] ) ; 10. (autre solution) SELECT Nom FROM Employ WHERE 'Genve' = ALL ( SELECT Lieu FROM Travaille, Projet WHERE [Link]=[Link] AND [Link]=[Link] ) ;

Question IV

SELECT NomD, COUNT(NAVS) FROM Employ, Dpartement WHERE [Link]=[Link] GROUP BY NomD ; CREATE VIEW DepPro (NDep, NbPro) AS SELECT NDep, COUNT(NPro) FROM Projet GROUP BY NDep ; SELECT NDep FROM Projet GROUP BY NDep HAVING COUNT(NPro) < ( SELECT AVG(NbPro) FROM DepPro ) ;