Type de
Table Attribut données Taille Désignation
Identifiant du
avis IdPassager int 11 passager
IdTrajet int 11 Identifiant du trajet
Note attribuée au
Note int 5 trajet
Commentair
e varchar 100 Commentaire
conducteu IdConducte Identifiant du
r ur int Auto incrementation conducteur
NomC varchar 20 Nom du conducteur
Date de naissance
DateNC date 10 conducteur
Téléphone du
TelC int 12 conducteur
Adresse du
adresseC varchar 30 conducteur
motdepasse Mot de passe du
C varchar 15 conducteur
Date d’obtention du
DatePermis date 10 permis
Genre du
Genre enum (homme/femme) conducteur
marqueV varchar 15 Marque du véhicule
Année de mise en
AnneeCircu date 4 circulation
Identifiant du
passager IdPassager int Auto incrementation passager
NomP varchar 20 Nom du passager
Date de naissance
DateNP date 10 du passager
Téléphone du
TelP int 12 passager
adresseP varchar 30 Adresse du passager
motdepasse Mot de passe du
P varchar 15 passager
reservatio IdReservati Identifiant de la
n on int Auto incrementation réservation
Identifiant du
IdPassager int 11 passager
IdConducte Identifiant du
ur int 11 conducteur
(En attente, Confirmée, État de la
Etat enum Annulée) réservation
IdTrajet int 11 Identifiant du trajet
trajet IdTrajet int Auto incrémentation Identifiant du trajet
LieuD varchar 15 Lieu de départ
LieuA varchar 14 Lieu d'arrivée
HeureD time 5 Heure de départ
HeureA time 5 Heure d'arrivée
Type de
Table Attribut données Taille Désignation
Nombre de places
NbPlaces enum (1/2/3/4) disponibles
Tarif decimal 10,4 Tarif du trajet
Arret1 varchar 20 Premier arrêt
Arret 2 varchar 20 Deuxième arrêt
('Pas D''arrêts', 'Non Fumeur',
Options set 'Conducteur Certifié’) Options du trajet
DateD date 10 Date de départ
IdConducte Identifiant du
ur int 11 conducteur
Schéma relationnel :
Conducteur(IdConducteur,nomC,DateNC,TelC,adresseC,motdepasseC,DatePermis,Genre,marqueV,An
neeCirculation)
IdConducteur (clé primaire)
Passager(IdPassager, NomP ,DateNP ,TelP, adresseP, motdepasseP)
IdPassager (clé primaire)
Trajet(IdTrajet,LieuD,LieuA,HeureD,HeureA,NbPlaces,Tarif,Arret1,Arret2,Options,DateD,IdConducteur
*)
IdTrajet (clé primaire)
IdConducteur* (clé étrangère référence IdConducteur dans la table Conducteur)
Reservation(IdReservation,Etat,IdPassager*,IdConducteur*, IdTrajet*)
IdReservation (clé primaire)
IdPassager* (clé étrangère référence IdPassager dans la table Passager)
IdConducteur* (clé étrangère référence IdConducteur dans la table Conducteur)
IdTrajet* (clé étrangère référence IdTrajet dans la table Trajet)
Avis(IdPassager*,IdTrajet*, Note, Commentaire)
IdPassager* (clé étrangère référence IdPassager dans la table Passager)
IdTrajet* (clé étrangère référence IdTrajet dans la table Trajet)
(IdPassager+IdTrajet) clé primaire
Règles de Gestion
- Un passager ne peut pas être à la fois un conducteur dans la même base de données.
- Un passager ne peut pas réserver deux fois le même trajet à la même date, donc la combinaison de
IdPassager et IdTrajet dans la table Reservation est unique.
-L’adresse d'un passager ou d'un conducteur doit être unique pour l’authentification.
- Un passager ne peut réserver qu'un trajet existant et disponible, et un avis ne peut être émis que
par un passager ayant réellement participé au trajet.
-Options dans la table trajet est de type « SET » afin de permettre a l’utilisateur de choisir une ou
plusieurs options entres celles proposées.
-L’Etat dans la table Reservation est par défaut mis à « en attente ».
Requêtes SQL :
1-Ajouter une nouvelle demande de réservation :
INSERT INTO Reservation (IdPassager, IdConducteur, IdTrajet, Etat) VALUES (1, 1, 1, 'En attente');
2-Modifier l'état d’une demande de réservation donnée :
UPDATE Reservation
SET Etat = 'Confirmee'
WHERE IdReservation = 1;
3-Rechercher la date, l'heure et le lieu de départ/arrivée des trajets d'un conducteur donné :
SELECT DateD, HeureD, LieuD, LieuA
FROM Trajet
WHERE IdConducteur = 1;
4-Rechercher les trajets allant du lieu X vers le lieu Y à la date Z avec un nombre de places N :
SELECT *
FROM Trajet
WHERE LieuD = 'X' AND LieuA = 'Y' AND DateD = 'Z' AND NbPlaces >= N;
5-Rechercher les demandes de réservation pour un trajet donné :
SELECT *
FROM Reservation
WHERE IdTrajet = 1;
6-Rechercher les détails d'un trajet donné, y compris toutes ses options et les informations du
conducteur suivantes :
L'évaluation des utilisateurs, notée E :
SELECT AVG(Note) AS E
FROM Avis
JOIN Trajet ON [Link] = [Link]
WHERE [Link] = 1;
L'ancienneté de son permis, notée A :
SELECT YEAR(NOW()) - YEAR(DatePermis) AS A
FROM Conducteur
WHERE IdConducteur = 1;
-Le nombre de trajets effectués sur le site, noté T :
SELECT COUNT(*) AS T
FROM Trajet
WHERE IdConducteur = 1;
Le nombre d'annulations de trajets effectuées, noté C :
SELECT COUNT(*) AS C
FROM Reservation
JOIN Trajet ON [Link] = [Link]
WHERE [Link] = 'Annulée' AND [Link] =1;
SELECT*,
CASE
WHEN X > 80 THEN 'certifié'
WHEN X > 50 THEN 'habitué'
WHEN X > 10 THEN 'moyen'
ELSE 'débutant'
END AS Q
FROM
SELECT (E + A + T) / (C + 1) AS X
FROM
SELECT AVG(Note) AS E
FROM Avis
JOIN Trajet ON [Link] = [Link]
WHERE [Link] = 1
) AS E,
SELECT YEAR(NOW()) - YEAR(DatePermis) AS A
FROM Conducteur
WHERE IdConducteur = 1
) AS A,
SELECT COUNT(*) AS T
FROM Trajet
WHERE IdConducteur = 1
) AS T,
SELECT COUNT(*) AS C
FROM Reservation
JOIN Trajet ON [Link] = [Link]
WHERE [Link] = 'Annulée' AND [Link] = valeurIdConducteur
) AS C ) ;