Exercice 01 :
1) Modèle Entité/Association :
Entités : Client, Voyage, Inscription, Visite, Programme Associations :
Voyage est associé à Inscription (relation 1-N) : Un voyage peut avoir plusieurs inscriptions, mais une inscription ne peut correspondre qu'à un seul voyage.
Client est associé à Inscription (relation 1-N) : Un client peut avoir plusieurs inscriptions, mais une inscription ne peut correspondre qu'à un seul client.
Visite est associé à Programme (relation 1-N) : Une visite peut être programmée pour plusieurs voyages, mais un voyage ne peut contenir qu'une seule visite.
Voyage est associé à Programme (relation 1-N) : Un voyage peut contenir plusieurs visites, mais une visite ne peut être programmée que pour un seul voyage.
Cardinalités :
Voyage : (0,N) avec Inscription, (1,N) avec Programme
Client : (0,N) avec Inscription
Inscription : (1,1) avec Voyage et (1,1) avec Client
Programme : (1,1) avec Voyage et (1,N) avec Visite
Visite : (0,N) avec Programme
2) Requêtes en Algèbre Relationnelle :
a) σ Prix < 2000 (Voyages)
b) Client ⨝ σ Year(DateInscription) = 2023 (Inscription)
c) π Endroit (Programmes ⨝ NoVoyage=V0105 (Voyage) ⨝ NoVisite)
d) Client ⨝ Inscription ⨝ Programme ⨝ σ Endroit = "Université de Dschang" (Visite)
e) π NomClient, Adresse (Client ⨝ Inscription ⨝ Voyage ⨝ σ VilleDépart = "Dschang" ∧ DateDépart = DateDépart(C0103) (Inscription ⨝ Voyage))
Exercice 02 :
1-------
SELECT DISTINCT Visite.Endroit
FROM Visite, Programmes, Voyages
WHERE Visite.NoVisite = Programmes.NoVisite#
AND Programmes.NoVoyage# = Voyages.NoVoyage
AND YEAR(Programmes.DateVisite) = 2023
ORDER BY Visite.Endroit ASC;
2-------
SELECT Inscription.NoVoyage#, COUNT(Inscription.NoClient#) AS NombreClientsInscrits
FROM Inscription
GROUP BY Inscription.NoVoyage#;
3-------
SELECT Voyages.NoVoyage
FROM Voyages
WHERE Voyages.Prix = (SELECT MIN(Voyages.Prix) FROM Voyages);
4------
SELECT Visite.Endroit
FROM Visite, Programmes
WHERE Visite.NoVisite = Programmes.NoVisite#
AND MONTH(Programmes.DateVisite) = 10
GROUP BY Visite.Endroit
HAVING COUNT(*) > 2;
5--------
SELECT DISTINCT Inscription.NoClient#, Client.NomClient
FROM Inscription, Client
WHERE Inscription.NoVoyage# IN (
SELECT Inscription.NoVoyage#
FROM Inscription
WHERE Inscription.NoClient# = 'C0102'
)
AND Inscription.NoClient# = Client.NoClient;
6-----
SELECT Voyages.NoVoyage
FROM Voyages
WHERE Voyages.NoVoyage NOT IN (
SELECT DISTINCT NoVoyage#
FROM Programmes
);
7-----
SELECT DISTINCT Voyages.VilleArrivée
FROM Voyages
WHERE Voyages.Prix > (
SELECT MAX(Voyages.Prix)
FROM Voyages
WHERE Voyages.VilleArrivée = 'Douala'
);
8-------
SELECT Voyages.NoVoyage
FROM Voyages
WHERE Voyages.VilleArrivée = (
SELECT Voyages.VilleArrivée
FROM Voyages
WHERE Voyages.NoVoyage = 'V1010'
)
AND Voyages.Prix > (
SELECT Voyages.Prix
FROM Voyages
WHERE Voyages.NoVoyage = 'V1010'
);
Exercice 03 :
Pour décomposer la relation R en 3ème forme normale, nous allons suivre les étapes suivantes :
Identifier les dépendances fonctionnelles de la relation R.
Diviser la relation en sous-relations qui ne présentent pas de dépendances fonctionnelles transitives.
Vérifier que les sous-relations obtenues sont bien en 3ème forme normale.
Dépendances fonctionnelles de la relation R :
NumEmp → NomEmp, PrénomEmp, MailEmp, gradeEmp, dateDébutEmp, dateFinEmp, salaireEmp, primeEmp NumAg → NomAg, AdrAg, TelAg
Diviser la relation en sous-relations :
Nous avons deux dépendances fonctionnelles dans la relation R, qui nous permettent de créer deux sous-relations :
Sous-relation 1 : Agence (NumAg, NomAg, AdrAg, TelAg) Sous-relation 2 : Employé (NumEmp, NumAg, NomEmp, PrénomEmp, MailEmp, gradeEmp, dateDébutEmp, dateFinEmp)
Vérifier la 3ème forme normale :
La sous-relation Agence ne contient pas de dépendances fonctionnelles, donc elle est en 3ème forme normale.
Pour la sous-relation Employé, nous avons une dépendance fonctionnelle : NumEmp → gradeEmp, salaireEmp, primeEmp. Cette dépendance fonctionnelle ne présente pas de dépendance fonctionnelle transitives, donc la sous-relation Employé est en 3ème forme normale.
Schéma relationnel final :
Agence (NumAg, NomAg, AdrAg, TelAg) Employé (NumEmp, NumAg, NomEmp, PrénomEmp, MailEmp, gradeEmp, dateDébutEmp, dateFinEmp)
Note : Nous avons supprimé les attributs salaireEmp et primeEmp de la sous-relation Employé, car ils peuvent être déduits à partir de l'attribut gradeEmp.
