Table des matières
La validation
-
Avec les formes normales
On part du cahier des charges pour former une première relation R contenant tous les attributs trouvés :
R
(Nomcli, PrénomCli, AdrCli, TelCli1, TelCli2, NomEntr, AdrEntr, NomResp, PrenomResp, TelResp1, TelResp2, Marque, Num_Immat, Type, Carburant, AnneeSortie, AnneeAchat, Puissance, NbPlacesAssises, NomCond, PrénomCond, DateNaissCond, Prime, Contrat_Cond, Debut_Contrat_Cond, Durée_Contrat_Cond, Salaire_Cond, AdrCond, TelCond1, TelCond2, Numéro, NomComm, PrénomComm, Contrat_Comm, DateNaissComm, Debut_Contrat_Comm, Durée_Contrat_Comm, Salaire_Comm, TelComm1, TelComm2, AdrComm, Num_Contrat_Transp, Date_Contrat_Transp, Trajet, DateHeure_départ, DateHeure_arrivée, Distance, Intervalle, Régulier, Nb_passagers_Contrat_Transp)Cli = Client
Entr = Entreprise
Resp = Demandeur (nous l’avons appelé responsable)
Cond = Conducteur
Comm = Commercial
Nous avons ajouté Num_Immat qui n’est pas dans le cahier des charges car un véhicule est quand même principalement caractérisé par son numéro d’immatriculation. Ainsi que Num_Contrat_Transp, Date_Contrat_Transp pour caractériser un contrat de transport. L’ajout de ces attributs est nécessaire pour la suite de la validation (clés primaires).
L’attribut Distance est ajouté sur demande du client. Pour les circuits, il reste le problème de l’ordre des trajets qui n’est pas défini, il faudrait ajouter des attributs : DateHeure_départ, DateHeure_arrivée même si ce n’est pas écrit clairement dans le cahier des charges.
1NF
-
La relation admet au moins une clé :
R
(Nomcli, PrénomCli, NomResp, PrenomResp, Num_Immat, NomCond, PrénomCond, Numéro, Num_Contrat_Transp, Trajet, DateHeure_départ, AdrCli, TelCli1, TelCli2, NomEntr, AdrEntr, TelResp1, TelResp2, Marque, AnneeSortie, AnneeAchat, Type, Carburant, Puissance, NbPlacesAssises, DateNaissCond, Prime, Contrat_Cond, Debut_Contrat_Cond, Durée_Contrat_Cond, Salaire_Cond, AdrCond, TelCond1, TelCond2, NomComm, PrénomComm, Contrat_Comm, DateNaissComm, Debut_Contrat_Comm, Durée_Contrat_Comm, Salaire_Comm, TelComm1, TelComm2, AdrComm, Date_Contrat_Transp, DateHeure_arrivée, Intervalle, Distance, Régulier, Nb_passagers_Contrat_Transp)
-
Tout attribut est atomique :
Adresse = Adresse, CodePostal, Ville
Trajet = Lieu_Départ, Lieu_Arrivée
Lieu_Départ = AdresseDep, CodePostalDep, VilleDep
Lieu_Arrivée = AdresseArr, CodePostalArr, VilleArr
R
(Nomcli, PrénomCli, NomResp, PrenomResp, Num_Immat, NomCond, PrénomCond, Numéro, Num_Contrat_Transp, DateHeure_départ, AdresseCli , CodePostalCli, VilleCli, TelCli1, TelCli2, NomEntr, AdresseEntr, CodePostalEntr, VilleEntr, TelResp1, TelResp2, Marque, AnneeSortie, AnneeAchat, Type, Carburant, Puissance, NbPlacesAssises, DateNaissCond, Prime, Contrat_Cond, Debut_Contrat_Cond, Durée_Contrat_Cond, Salaire_Cond, AdresseCond , CodePostalCond, VilleCond, TelCond1, TelCond2, NomComm, PrénomComm, Contrat_Comm, DateNaissComm, Debut_Contrat_Comm, Salaire_Comm, Durée_Contrat_Comm, TelComm1, TelComm2, AdresseComm, CodePostalComm, VilleComm, Date_Contrat_Transp, DateHeure_arrivée, AdresseDep, CodePostalDep, VilleDep, AdresseArr, CodePostalArr, VilleArr, Intervalle, Distance, Nb_passagers_Contrat_Transp, Régulier)
2NF
-
Tout attribut n’appartenant pas à une clé ne dépend pas d’une partie de la clé. Les DF entre la clé et les autres attributs sont élémentaires.
On liste toutes les dépendances fonctionnelles correspondant à cette définition. On en déduit de nouvelles relations :
Nomcli, PrénomCli → AdresseCli, TelCli1, TelCli2, CodePostalCli, VilleCli
NomResp, PrenomResp → TelResp1, TelResp2, NomEntr, AdresseEntr, CodePostalEntr, VilleEntr
Num_Immat → Marque, AnneeSortie, AnneeAchat, Type, Carburant, Puissance, NbPlacesAssises
NomCond, PrénomCond
→ DateNaissCond, Prime, AdresseCond, CodePostalCond, VilleCond, TelCond1, TelCond2, Debut_Contrat_Cond, Contrat_Cond, Durée_Contrat_Cond, Salaire_Cond
NomCond, PrénomCond, Debut_Contrat_Cond → Contrat_Cond, Durée_Contrat_Cond, Salaire_Cond
Numéro → NomComm, PrénomComm, DateNaissComm, TelComm1, TelComm2, AdresseComm, CodePostalComm, VilleComm, Debut_Contrat_Comm, Durée_Contrat_Comm, Salaire_Comm, Contrat_Comm
Numero, Debut_Contrat_Comm → Durée_Contrat_Comm, Salaire_Comm, Contrat_Comm
Num_Contrat_Transp → NomCli, PrenomCli, NomEntr, Num_Immat, NomCond, PrénomCond, Numéro Nb_passagers_Contrat_Transp, Date_Contrat_Transp, Régulier
Num_Contrat_Transp, DateHeure_départ → DateHeure_arrivée, AdresseDep, CodePostalDep, VilleDep, AdresseArr, CodePostalArr, VilleArr, Intervalle, Distance
Client (NomCli, PrenomCli, AdresseCli, TelCli1, TelCli2, CodePostalCli, VilleCli)
-
Ici on a supposé que deux personnes ayant le même nom, ont un prénom différent.
Responsable (NomResp, PrenomResp, TelResp1, TelResp2, NomEntr, AdresseEntr, CodePostalEntr, VilleEntr)
Véhicule (Num_Immat, Marque, AnneeSortie, AnneeAchat, Type, Carburant, Puissance, NbPlacesAssises)
Conducteur (NomCond, PrénomCond, DateNaissCond, Prime, AdresseCond, CodePostalCond, VilleCond, TelCond1, TelCond2)
ContratCond (NomCond, PrénomCond, Debut_Contrat_Cond, Contrat_Cond, Durée_Contrat_Cond, Salaire_Cond)
- Le client veut que l’on puisse conserver l’historique des contrats des employés.
Commercial (Numéro, NomComm, PrénomComm, Contrat_Comm, DateNaissComm, TelComm1, TelComm2, AdresseComm, CodePostalComm, VilleComm)
ContratComm (Numéro, Debut_Contrat_Comm, Durée_Contrat_Comm, Salaire_Comm)
Contrat (Num_Contrat_Transp, NomCli, PrenomCli, NomEntr, Num_Immat, NomCond, PrénomCond, Numéro Nb_passagers_Contrat_Transp, Date_Contrat_Transp, Régulier)
Etape (Num_Contrat_Transp, DateHeure_départ, DateHeure_arrivée, AdresseDep, CodePostalDep, VilleDep, AdresseArr, CodePostalArr, VilleArr, Intervalle, Distance)
3NF
-
Tout attribut n’appartenant pas à une clé ne dépend pas d’un attribut non-clé. Les DF entre la clé et les autres attributs sont toutes élémentaires et directes.
On liste toutes les dépendances fonctionnelles correspondant à cette définition.
NomEntr → AdresseEntr, CodePostalEntr, VilleEntr
AdresseDep, CodePostalDep, VilleDep, AdresseArr, CodePostalArr, VilleArr → Distance
On en déduit de nouvelles relations :
Client (NomCli, PrenomCli, AdresseCli, TelCli1, TelCli2, CodePostalCli, VilleCli)
Responsable (NomResp, PrenomResp, TelResp1, TelResp2, NomEntr)
Entreprise (NomEntr, AdresseEntr, CodePostalEntr, VilleEntr)
Véhicule (Num_Immat, Marque, AnneeSortie, AnneeAchat, Type, Carburant, Puissance, NbPlacesAssises)
Conducteur (NomCond, PrénomCond, DateNaissCond, Prime, AdresseCond, CodePostalCond, VilleCond, TelCond1, TelCond2)
ContratCond (NomCond, PrénomCond, Debut_Contrat_Cond, Contrat_Cond, Durée_Contrat_Cond, Salaire_Cond)
Commercial (Numéro, NomComm, PrénomComm, Contrat_Comm, DateNaissComm, TelComm1, TelComm2, AdresseComm, CodePostalComm, VilleComm)
ContratComm (Numéro, Debut_Contrat_Comm, Durée_Contrat_Comm, Salaire_Comm)
Contrat (Num_Contrat_Transp, NomCli, PrenomCli, NomEntr, Num_Immat, NomCond, PrénomCond, Numéro Nb_passagers_Contrat_Transp, Date_Contrat_Transp, Régulier)
Etape (Num_Contrat_Transp, DateHeure_départ, DateHeure_arrivée, AdresseDep, CodePostalDep, VilleDep, AdresseArr, CodePostalArr, VilleArr, Intervalle)
Etape_Dist (AdresseDep, CodePostalDep, VilleDep, AdresseArr, CodePostalArr, VilleArr, Distance)
3NF de BOYCE-CODD-KENT
- Dans chaque DF, le déterminant est une clé : pas de changement ici.
4NF
- Les seules Dépendances Multivaluées élémentaires sont celles dans lesquelles une clé multi-détermine un attribut.
Dépendance Multivaluée :
Soit R (A, B, C)
On a une dépendance multivaluée dans R si et seulement si, chaque fois que et apparaissent dans R, alors et apparaissent dans R.
Nous n’en avons pas ici.
5NF
- Toute dépendance de jointure de R est impliquée par des clés candidates de R.
Si la décomposition se fait avec des pertes d’information lors de la reconstitution, c’est qu’on ne peut pas appliquer la 5 NF.
Explication des différences avec notre modèle :
Client (NomCli, PrenomCli, AdresseCli, TelCli1, TelCli2, CodePostalCli, VilleCli)
Responsable (NomResp, PrenomResp, TelResp1, TelResp2, #NomEntr)
- # = clé étrangère
Entreprise (NomEntr, AdresseEntr, CodePostalEntr, VilleEntr)
Véhicule (Num_Immat, Marque, AnneeSortie, AnneeAchat, Type, Carburant, Puissance, NbPlacesAssises)
Conducteur (NomCond, PrénomCond, DateNaissCond, Prime, AdresseCond, CodePostalCond, VilleCond, TelCond1, TelCond2)
Commercial (Numéro, NomComm, PrénomComm, Contrat_Comm, DateNaissComm, TelComm1, TelComm2, AdresseComm, CodePostalComm, VilleComm)
-
Pour les tables ci-dessus, il n’a pas de grosses différences avec notre modèle. Nous avons juste utilisé la spécialisation (choix de modélisation cf. p5.) et des attributs en plus (à la demande du client). Nous avons aussi choisi de prendre de nouvelles clés uniques qui enlève le problème du « nom, prénom » : id_cli, id_cond …
ContratCond (#[NomCond, PrénomCond], Debut_Contrat_Cond, Contrat_Cond, Durée_Contrat_Cond, Salaire_Cond)
ContratComm (#Numéro, Debut_Contrat_Comm, Durée_Contrat_Comm, Salaire_Comm)
- Ces tables permettent d’avoir un historique de tous les contrats des employés, en fonction de leur date de début.
Contrat (Num_Contrat_Transp, #[NomCli, PrenomCli], #NomEntr, #Num_Immat, #[NomCond, PrénomCond],#Numéro, Nb_passagers_Contrat_Transp, Date_Contrat_Transp, Régulier)
-
Dans notre modèle, nous n’avons pas gardé l’attribut régulier car nous avons décidé de répéter l’association avec étape autant de fois qu’il y a de répétitions du trajet en mettant des dates différentes pour chaque jour, chaque heure ou chaque mois pendant lequel le trajet est effectué.
Etape (Num_Contrat_Transp, DateHeure_départ, DateHeure_arrivée, #[AdresseDep, CodePostalDep, VilleDep, AdresseArr, CodePostalArr, VilleArr], Intervalle)
Etape_Dist (AdresseDep, CodePostalDep, VilleDep, AdresseArr, CodePostalArr, VilleArr, Distance)
-
Ici, nous avons choisi de faire une table Lieu, pour éviter les redondances.
Méthode des paquets
Méthode
– Chercher dans le cahier des charges la liste de tous les attributs.
– Rendre tous les attributs atomiques (1ère Forme normale).
– Relier tous les attributs par leurs Dépendances Fonctionnelle Élémentaire : une DF entre X → A est élémentaire si X est minimum (X contient le minimum d’attribut) et A est simple (il ne contient qu’un seul attribut). On obtient un graphe avec comme sommets les attributs et comme arcs les DF.
– Faire la couverture minimale de ce graphe : éliminer toutes les DF qui peuvent être déduite par transitivité (Si A → B, B →C et A →C alors on peut supprimer A →C).
– Regrouper dans un même paquet tous les attributs ayant des dépendances fonctionnelles de même origine (origine comprise).
– Dresser la liste des relations correspondant à chaque paquet avec comme clef primaire l’origine de la dépendance fonctionnelle. On obtient une liste de relation respectant la BCNF (Boyce-Codd-Kent) et donc aussi la 3ème Forme Normale.
-
Résultat
CONTRAT(NumContratTransp, Nomcli, PrénomCli, PrenomResp, NomResp, NumCom, NbPassagersContratTransp, Régulier, DateContratTransp, NomCond, PrénomCond, NumImmat)
CLIENT(Nomcli, PrénomCli, TelCli1, TelCli2, AdresseCli, CodePostalCli, VilleCli)
RESPONSABLE(PrenomResp, NomResp, TelResp1, TelResp2, NomEntr)
ENTREPRISE(NomEntr, CodePostalEntr, AdresseEntr, VilleEntr)
COMMERCIAU(NumCom, NomComm, PrénomComm, TelComm1, TelComm2, AdresseComm, CodePostalComm, VilleComm, DateNaissComm)
COMMERCIAU_CONTRAT(NumCom, DebutContratComm, DuréeContratComm, SalaireComm, TypeContratComm)
ETAPE(NumContratTransp, DateDepart, HeureDepart, AdresseDepart, CodePostalDepart, VilleDepart, AdresseArrive, CodePostalArrive, VilleArrive, DateArrive, HeureArrive, Intervalle)
DISTANCE(AdresseDepart, CodePostalDepart, VilleDepart, AdresseArrive, CodePostalArrive, VilleArrive, Distance)
CONDUCTEUR(NomCond, PrénomCond, TelCond1, TelCond2, AdresseCond, CodePostalCond, VilleCond, DateNaissCond)
CONDUCTEUR_CONTRAT(NomCond, PrénomCond, DebutContratCond, SalaireCond, ContratCond, DuréeContratCond)
VEHICULE(NumImmat, AnneeAchat, AnneeSortie, Marque, Carburant, NbPlacesAssises, Type, Puissance)
-
Différence avec la modélisation
– La notion de généralisation spécialisation n’apparais pas avec cette méthode. Alors que l’on a fait le choix de la mettre en œuvre dans notre modèle.
– Pour le client, le responsable, conducteur, étape, entreprise et le lieu on a créé des clefs primaires qui n’ont pas vraiment de sens sémantiques. Mais il sera plus facile de consulter la base en faisant des jointures. De plus on pouvait avoir des problèmes si on avait 2 personnes portant le même nom et prénom.
– On a créé une table supplémentaire lieu qui permettra de réutiliser les mêmes adresses notamment pour l’aller et le retour.
– On a supprimé la table distance qui ne supprimera pas beaucoup de redondances et qui est assez lourde a mettre en place avec des jointures.
– On a supprimé l’attribut régulier car dans notre base de données on stockera toutes les occurrence des étapes donc on na plus besoin de savoir si un trajet est régulier ou non .
La carte des concepts
Bilan global du projet
Mise en place
Puisque nous sommes un groupe d’extérieurs, nous venons tous de milieux différents, il a donc été difficile d’uniformiser les points de vus au niveau de la conception de la base. Chacun avait sa propre vision du problème et de la façon dont le résoudre. La principale difficulté qui ressort de ce projet a donc été de se mettre d’accord, étape par étape, sur les fonctionnalités à mettre en place.
Les différentes étapes
-
Analyse des besoins du client
-
Interprétation de ces besoins
-
Reformulation des besoins
-
Modélisation du modèle Entité/Association
-
Correction du modèle
-
Implémentation
-
Validation de l’implémentation
-
Implémentation réelle sur plateforme postgresql
-
Implémentation requêtes
-
Vérification requêtes
-
Création interface
-
Ajout de fonctionnes sur l’interface
On a suivi le cahier des charges, étape par étape, en passant, parfois, plus de temps sur les concepts qui nous paraissaient plus importants. Nous avons, notamment, passé beaucoup de temps sur le modèle Entité/Association car il nous semblait essentiel d’avoir une base répondant, le plus fidèlement, possible aux critères du client. Voici les diverses étapes avec les séances associées :
-
Définition des étapes et phases du projet (1ère séance)
-
Bilan de nos compétences pour adapter la démarche au niveau du groupe (1ère séance)
-
Traduction du cahier des charges en 1er modèle E/A (2ème séance)
-
Prise de RDV avec le client pour éclaircir les problèmes d’ambiguïté et imprécisions dans le cahier des charges (2ème séance)
-
Correction du 1er modèle E/A (3ème séance)
-
Création du MLD (3ème séance)
-
Validation avec les NF (en cours) (4ème séance)
-
Création de la carte des concepts (4ème séance)
-
- Un nom, prénom, adresse, nom entreprise, nom de ville a au maximum 32 caractères
- Un code postal a 5 caractères
- Un numéro de téléphone a au maximum 20 caractères (caractères séparateurs compris)
- Une prime d’un personnel ne peut être négative
- Un personnel doit être majeur (plus de 18 ans)
- Un permis de conduire ne peut avoir comme valeur que ‘A’,’B’,’C’,’D’,’E’
- Un type_contrat ne peut avoir comme valeur que (‘CDI’, ‘CDD’, ‘temporaire’, ‘interim’, ‘temps partiel’, ‘apprentissage’, ‘qualification’, ’emploi jeune’, ‘CIE’, ‘CES’, ‘CEC’)
- Un salaire est forcément positif
- Une date de début d’un contrat doit être inférieur à la date de fin d’un contrat
- Si le contrat est de type CDI, il n’y a pas de date de fin
- Le type de véhicule doit être (‘voiture’, ‘bus’, ‘camion’, ‘minibus’, ‘van’, ‘4×4’, ‘autre’)
- Le carburant doit être (‘diesel’, ‘super’, ‘SP95’, ‘SP98’, ‘GPL’, ‘electrique’, ‘autre’)
- L’année d’achat doit être supérieur ou égale à l’année de sortie
- Une année doit être positive
- L’année d’achat ou de sortie doit être une année inférieur ou égale a l’année actuelle
- Une puissance est forcément positive
- Une consommation moyenne est positive
- Un nombre de places est compris entre 0 et 400
- Le prix d’un voyage est positif
- La date de départ est inférieur à la date d’arrivée
- La distance d’une étape est comprise entre 0 et 65000
Fonctionnement du groupe
Après avoir modélisé la base, nous avons pu mieux répartir les taches puisque nous les avions bien défini. Nous avons finalement atteint les objectifs du module rapidement ce qui nous a laissé plus de temps pour améliorer et développer des fonctionnalités concernant l’interface PHP ainsi que de créer l’ensemble des vues, indexes et triggers.
Nous avons aussi pu nous remettre à niveau en améliorant et développant nos connaissances concernant notamment les requêtes en algébre et calcul relationnel.
Les résultats de la modélisation des requêtes, l’implémentation de la base ( requêtes d’insertion, de création, les indexes, vues et triggers) ainsi que le dictionnaire des données sont disponibles en Annexes.
Conclusion
L’expérience de la réalisation de bases de données, nous a permis de tirer un certain nombre d’enseignements.
Elle nous a permis de valider une démarche d’analyse et de conception basée à la fois sur une modélisation du système ainsi que sur les concepts associés.
L’approche originale de ce cours basée sur un projet concret nous a permis de nous familiariser avec les différentes contraintes auxquelles nous serons confrontés plus tard au sein du monde professionnel.
Nous avons aussi pu développer l’ensemble de nos connaissances grâce à l’importance des diverses parties théoriques, essentielles à un apprentissage complet et cohérent.
Au fur et à mesure de l’avancement de ce projet, nous avons pu remettre en cause notre modèle, confronter notre point de vue avec les attentes du client et modifier notre modèle en fonction.
Cette démarche nous a permis d’arriver, finalement, à un modèle qui semble répondre le plus aux attentes du client.
Annexe
Contraintes
Les Requêtes SQL de création et d’insertion
————————————–
— CLIENT —
————————————–
CREATE TABLE client (
id_cl SERIAL NOT NULL,
nom VARCHAR(32) NOT NULL,
prenom VARCHAR(32) NOT NULL,
CONSTRAINT pk_client PRIMARY KEY (id_cl)
);
— CREATE UNIQUE INDEX idx_client_pk ON client (id_cl); generer automatiquement car clef primaire
CREATE INDEX idx_client_nom ON client (nom);
INSERT INTO client (nom, prenom) VALUES (‘dupond’, ‘george’);
INSERT INTO client (nom, prenom) VALUES (‘Pote’, ‘jessica’);
INSERT INTO client (nom, prenom) VALUES (‘DuDu’, ‘Jeremie’);
INSERT INTO client (nom, prenom) VALUES (‘Bidule’, ‘jihed’);
INSERT INTO client (nom, prenom) VALUES (‘Machin’, ‘Pascal’);
INSERT INTO client (nom, prenom) VALUES (‘The boss’, ‘gregory’);
INSERT INTO client (nom, prenom) VALUES (‘The charte graphique man’, ‘Raphael’);
INSERT INTO client (nom, prenom) VALUES (‘The SQL Girl’, ‘aurelie’);
————————————–
— ENTREPRISE —
————————————–
CREATE TABLE entreprise (
id_en SERIAL NOT NULL,
nom VARCHAR(32) NOT NULL,
adresse VARCHAR(32),
code_postal VARCHAR(5),
ville VARCHAR(32),
tel VARCHAR(32),
CONSTRAINT pk_entreprise PRIMARY KEY (id_en)
);
— CREATE UNIQUE INDEX idx_entreprise_pk ON entreprise (id_en); generer automatiquement car clef primaire
CREATE INDEX idx_entreprise_nom ON entreprise (nom);
INSERT INTO entreprise (nom, adresse, code_postal, ville, tel) VALUES (‘INSAT’, ‘5 rue du puits’, ‘31000’, ‘Toulouse’, ‘05.68.48.25.37’);
————————————–
— DEMANDEUR —
————————————–
CREATE TABLE demandeur (
id_cl INTEGER NOT NULL,
id_en INTEGER NOT NULL,
tel_bureau VARCHAR(32),
CONSTRAINT pk_demandeur PRIMARY KEY (id_cl),
CONSTRAINT fk_demandeur_client FOREIGN KEY (id_cl) REFERENCES client (id_cl),camion
CONSTRAINT fk_demandeur_entreprise FOREIGN KEY (id_en) REFERENCES entreprise (id_en)
);
— CREATE UNIQUE INDEX idx_demandeur_pk ON demandeur (id_cl); generer automatiquement car clef primaire
CREATE INDEX idx_demandeur_fk_en ON demandeur (id_en);
INSERT INTO demandeur VALUES (3, 1, ‘05.31.45.78.54’);
INSERT INTO demandeur VALUES (4, 1, ‘05.31.48.65.78’);
INSERT INTO demandeur VALUES (5, 1, ‘05.31.65.84.97’);
INSERT INTO demandeur VALUES (6, 1, ‘05.31.97.68.14’);
INSERT INTO demandeur VALUES (7, 1, ‘05.31.48.65.74’);
INSERT INTO demandeur VALUES (8, 1, ‘05.31.48.45.78’);
————————————–
— PARTICULIER —
————————————–
CREATE TABLE particulier (
id_cl INTEGER NOT NULL,
adresse VARCHAR(32),
code_postal VARCHAR(5),
ville VARCHAR(32),
tel_portable VARCHAR(32),
tel_domicile VARCHAR(32),
CONSTRAINT pk_particulier PRIMARY KEY (id_cl),
CONSTRAINT fk_particulier_client FOREIGN KEY (id_cl) REFERENCES client (id_cl)
);
— CREATE UNIQUE INDEX idx_particulier_pk ON particulier (id_cl); generer automatiquement car clef primaire
INSERT INTO particulier VALUES (1, ‘4 rue des ministres’, ‘12150’, ‘severac-le-chateau’, ‘06.84.68.45.74’, ‘05.67.98.44.58’);
INSERT INTO particulier VALUES (2, ‘5 rue de la bonne vanne’, ‘66666’, ‘Perpignan City’, ‘06.66.34.65.88’, ‘05.06.66.98.76’);
————————————–
— PERSONNEL —
————————————–
CREATE TABLE personnel (
id_personnel SERIAL NOT NULL,
nom VARCHAR(32) NOT NULL,
prenom VARCHAR(32) NOT NULL,
date_naissance DATE NOT NULL,
nss BIGINT NOT NULL,
adresse VARCHAR(32),
tel_portable VARCHAR(32),
tel_domicile VARCHAR(32),
code_postal VARCHAR(5),
ville VARCHAR(32),
prime MONEY,
CONSTRAINT pk_personnel PRIMARY KEY (id_personnel),
CONSTRAINT ck_personnel_prime CHECK (prime >’0.00′),
CONSTRAINT ck_personnel_majeur CHECK (extract(year from age(date_naissance)) > 18)
);
— CREATE UNIQUE INDEX idx_personnel_pk ON personnel (id_personnel); generer automatiquement car clef primaire
CREATE INDEX idx_personnel_nom ON personnel (nom);
CREATE UNIQUE INDEX idx_personnel_nss ON personnel (nss);
INSERT INTO personnel VALUES (DEFAULT, ‘Geofre’, ‘Marcel’, ‘1982-01-04’, 123456789, ‘2 rue des chaufard’, ‘06.48.98.47.65’, ‘05.48.98.47.65’, ‘31457’, ‘Toulouse’, NULL);
INSERT INTO personnel VALUES (DEFAULT, ‘Tourbin’, ‘Michel’, ‘1982-03-10’, 987654321, ‘3 rue des commercants’, ‘06.45.36.78.61’, ‘05.45.36.78.61’, ‘31150’, ‘Ramonville’, NULL);
INSERT INTO personnel VALUES (DEFAULT, ‘The SQL girl’, ‘Aurelie’, ‘1976-09-22′, 28606545411518, ’23, rue jean paul laurens’, ‘0689657458’, ‘0524587854’, ‘31400’, ‘Toulouse’, ‘2,500.00’);
INSERT INTO personnel VALUES (DEFAULT, ‘Fourchette’, ‘Frank’, ‘1974-05-29′, 12014984984988, ’12 impasse sans retour’, ‘0615487956’, ‘0415655894’, ‘66000’, ‘Les Angles’, ‘500.00’);
INSERT INTO personnel VALUES (DEFAULT, ‘Segozy’, ‘paul’, ‘1959-05-06′, 1200554644984, ’13 rue du vendredi’, ‘0614758963’, ‘0512457896’, ‘31500’, ‘toulouse’, NULL);
————————————–
— COMMERCIAL —
————————————–
CREATE TABLE commercial (
id_personnel INTEGER NOT NULL,
CONSTRAINT pk_commercial PRIMARY KEY (id_personnel),
CONSTRAINT fk_commercial_personnel FOREIGN KEY (id_personnel) REFERENCES personnel (id_personnel)
);
— CREATE UNIQUE INDEX idx_commercial_pk ON commercial (id_personnel); generer automatiquement car clef primaire
INSERT INTO commercial VALUES (2);
INSERT INTO commercial VALUES (3);
INSERT INTO commercial VALUES (4);
————————————–
— CONDUCTEUR —
————————————–
CREATE TABLE conducteur (
id_personnel INTEGER NOT NULL,
type_permis CHAR(2),
CONSTRAINT pk_conducteur PRIMARY KEY (id_personnel),
CONSTRAINT fk_conducteur_personnel FOREIGN KEY (id_personnel) REFERENCES personnel (id_personnel),
CONSTRAINT ck_conducteur_permis CHECK ( type_permis IN (‘A’,’B’,’C’,’D’,’E’))
);
— CREATE UNIQUE INDEX idx_conducteur_pk ON conducteur (id_personnel); generer automatiquement car clef primaire
INSERT INTO conducteur VALUES (1, ‘D’);
INSERT INTO conducteur VALUES (5, ‘B’);
————————————–
— CONTRAT DE TRAVAIL —
————————————–
CREATE TABLE contrat_travail (
id_contrat SERIAL NOT NULL,
id_personnel INTEGER NOT NULL,
salaire MONEY NOT NULL,
type_contrat VARCHAR(15) NOT NULL,
date_deb date NOT NULL,
date_fin date,
CONSTRAINT pk_contrat_travail PRIMARY KEY (id_contrat),
CONSTRAINT fk_contrat_travail_personnel FOREIGN KEY (id_personnel) REFERENCES personnel (id_personnel),
CONSTRAINT ck_contrat_travail_type_contrat CHECK ( type_contrat IN (‘CDI’, ‘CDD’, ‘temporaire’, ‘interim’, ‘temps partiel’, ‘apprentissage’, ‘qualification’, ’emploi jeune’, ‘CIE’, ‘CES’, ‘CEC’, ‘CNE’)),
CONSTRAINT ck_contrat_travail_salaire_positif CHECK (salaire >’0.00′),
CONSTRAINT ck_contrat_travail_date CHECK (date_deb < date_fin),
CONSTRAINT ck_contrat_travail_date_fin CHECK (type_contrat=’CDI’ AND date_fin IS NULL OR type_contrat!=’CDI’ AND date_fin IS NOT NULL)
);
— CREATE UNIQUE INDEX idx_contrat_travail_pk ON contrat_travail (id_contrat); generé automatiquement car clef primaire
CREATE INDEX idx_contrat_travail_fk_personnel ON contrat_travail (id_personnel);
INSERT INTO contrat_travail VALUES (DEFAULT, 2, ‘1,700.00’, ‘CDI’, ‘2007-01-01’, NULL);
INSERT INTO contrat_travail VALUES (DEFAULT, 3, ‘1,700.00’, ‘CDI’, ‘2006-03-02’, NULL);
INSERT INTO contrat_travail VALUES (DEFAULT, 4, ‘1,300.00’, ‘CDD’, ‘2006-04-22’, ‘2006-05-22’);
INSERT INTO contrat_travail VALUES (DEFAULT, 2, ‘1,600.00’, ‘CDD’, ‘2006-10-06’, ‘2006-12-09’);
————————————–
— VEHICULE —
————————————–
CREATE TABLE vehicule (
num_immat CHAR(11) NOT NULL,
marque VARCHAR(32),
type_vehicule VARCHAR(32),
carburant VARCHAR(32),
annee_sortie SMALLINT,
annee_achat SMALLINT,
puissance SMALLINT,
consommation_moyenne SMALLINT,
nbr_places SMALLINT,
type_permis CHAR(1),
CONSTRAINT pk_vehicule PRIMARY KEY (num_immat),
CONSTRAINT ck_vehicule_type_vehicule CHECK ( type_vehicule IN (‘voiture’, ‘bus’, ‘camion’, ‘minibus’, ‘van’, ‘4×4’, ‘autre’)),
CONSTRAINT ck_vehicule_carburant CHECK ( carburant IN (‘diesel’, ‘super’, ‘SP95’, ‘SP98’, ‘GPL’, ‘electrique’, ‘autre’)),
CONSTRAINT ck_vehicule_annee CHECK (annee_achat >= annee_sortie),
CONSTRAINT ck_vehicule_annee_positive CHECK (annee_achat >= 0 AND annee_sortie >= 0),
CONSTRAINT ck_vehicule_annee_passe CHECK (annee_achat <= extract(year from current_date) AND annee_sortie <= extract(year from current_date)),
CONSTRAINT ck_vehicule_puissance_positive CHECK (puissance >= 0),
CONSTRAINT ck_vehicule_consommation_positive CHECK (consommation_moyenne >= 0),
CONSTRAINT ck_vehicule_nbre_places CHECK (0 <= nbr_places AND nbr_places <= 400),
CONSTRAINT ck_vehicule_permis CHECK ( type_permis IN (‘A’,’B’,’C’,’D’,’E’))
);
— CREATE UNIQUE INDEX idx_vehicule_pk ON vehicule (num_immat); generé automatiquement car clef primaire
CREATE INDEX idx_vehicule_type ON vehicule (type_vehicule);
CREATE INDEX idx_vehicule_nbr_places ON vehicule (nbr_places);
INSERT INTO vehicule VALUES (‘0140 AQ 43’, ‘Toyota’, ‘camion’, ‘diesel’, 2003, 2005, 50, 12, 3, ‘E’);
INSERT INTO vehicule VALUES (‘0032 AD 34’, ‘Honda’, ‘voiture’, ‘super’, 1988, 2006, 10, 14, 4, ‘B’);
INSERT INTO vehicule VALUES (‘0423 AZ 31’, ‘volvo’, ‘van’, ‘SP95’, 1991, 1992, 18, 15, 47, ‘D’);
INSERT INTO vehicule VALUES (‘0318 BZ 31’, ‘Renault’, ‘4×4’, ‘SP98’, 2006, 2007, 15, 10, 8, ‘B’);
INSERT INTO vehicule VALUES (‘0789 ER 31’, ‘Peugeot’, ‘minibus’, ‘GPL’, 2006, 2007, 15, 4, 5, ‘B’);
INSERT INTO vehicule VALUES (‘7894 TY 31’, ‘Cariane’, ‘bus’, ‘electrique’, 2006, 2007, 50, 10, 100, ‘D’);
INSERT INTO vehicule VALUES (‘3214 SR 31’, ‘Ariane’, ‘autre’, ‘autre’, 2007, 2007, 2000, 10, 3, ‘E’);
————————————–
— CONTRAT DE TRANSPORT —
————————————–
CREATE TABLE contrat (
id_contrat serial NOT NULL,
num_immat CHAR(11) NOT NULL,
id_cl INTEGER NOT NULL,
id_conducteur INTEGER NOT NULL,
id_commercial INTEGER NOT NULL,
date DATE NOT NULL,
nbre_passagers SMALLINT NOT NULL,
prix MONEY NOT NULL,
CONSTRAINT pk_contrat PRIMARY KEY (id_contrat),
CONSTRAINT fk_contrat_vehicule FOREIGN KEY (num_immat) REFERENCES vehicule (num_immat),
CONSTRAINT fk_contrat_client FOREIGN KEY (id_cl) REFERENCES client (id_cl),
CONSTRAINT fk_contrat_conducteur FOREIGN KEY (id_conducteur) REFERENCES conducteur (id_personnel),
CONSTRAINT fk_contrat_commercial FOREIGN KEY (id_commercial) REFERENCES commercial (id_personnel),
CONSTRAINT ck_contrat_nbre_passagers CHECK (0 <= nbre_passagers AND nbre_passagers <= 400),
CONSTRAINT ck_contrat_prix CHECK (prix >’0.00′)
);
— CREATE UNIQUE INDEX idx_contrat_pk ON contrat (id_contrat); generer automatiquement car clef primaire
CREATE INDEX idx_contrat_fk_vehicule ON contrat (num_immat);
CREATE INDEX idx_contrat_fk_client ON contrat (id_cl);
CREATE INDEX idx_contrat_fk_conducteur ON contrat (id_conducteur);
CREATE INDEX idx_contrat_fk_commercial ON contrat (id_commercial);
CREATE INDEX idx_contrat_date ON contrat (date);
CREATE INDEX idx_contrat_nbre_passagers ON contrat (nbre_passagers);
CREATE INDEX idx_contrat_prix ON contrat (prix);
INSERT INTO contrat VALUES (1, ‘0140 AQ 43’, 1, 1, 2, ‘2007-01-14’, 4, ‘300.00’);
INSERT INTO contrat VALUES (2, ‘0423 AZ 31’, 3, 1, 3, ‘2007-04-02’, 40, ‘3000.00’);
INSERT INTO contrat VALUES (3, ‘0423 AZ 31’, 6, 1, 2, ‘2007-05-04’, 43, ‘2500.00’);
INSERT INTO contrat VALUES (5, ‘0140 AQ 43’, 1, 5, 2, ‘2006-12-15’, 2, ‘1500.00’);
INSERT INTO contrat VALUES (4, ‘0140 AQ 43’, 3, 1, 2, ‘2005-12-15’, 2, ‘1500.00’);
————————————–
— LIEU —
————————————–
CREATE TABLE lieu (
id_lieu SERIAL NOT NULL,
ville VARCHAR(32) NOT NULL,
adresse VARCHAR(32) NOT NULL,
code_postal CHAR(5) NOT NULL,
CONSTRAINT pk_lieu PRIMARY KEY (id_lieu)
);
— CREATE UNIQUE INDEX idx_contrat_pk ON lieu (id_lieu); generer automatiquement car clef primaire
CREATE INDEX idx_contrat_ville ON lieu (ville);
CREATE INDEX idx_contrat_code_postal ON lieu (code_postal);
INSERT INTO lieu VALUES (DEFAULT, ‘Toulouse’, ‘Rue de la pomme’, ‘31400’);
INSERT INTO lieu VALUES (DEFAULT, ‘Paris’, ‘Rue de Dagobert’, ‘75000’);
INSERT INTO lieu VALUES (DEFAULT, ‘Tarbes’, ‘Rue du 11 Novembre’, ‘65000’);
INSERT INTO lieu VALUES (DEFAULT, ‘Montauban’, ‘Rue de la poire’, ‘82000’);
INSERT INTO lieu VALUES (DEFAULT, ‘Foix’, ’56, boulevard Jean Jaures’, ‘09000’);
INSERT INTO lieu VALUES (DEFAULT, ‘Rodez’, ’33, rue au coq’, ‘12000’);
INSERT INTO lieu VALUES (DEFAULT, ‘Messanges’, ‘rue foin’, ‘40500’);
INSERT INTO lieu VALUES (DEFAULT, ‘Bordeau’, ‘Rue des girondins’, ‘33000’);
INSERT INTO lieu VALUES (DEFAULT, ‘Marseille’, ‘Rue des aloutettes’, ‘13000’);
INSERT INTO lieu VALUES (DEFAULT, ‘Lyon’, ‘Rue des champions de france’, ‘69000’);
INSERT INTO lieu VALUES (DEFAULT, ‘Agen’, ‘Rue du lot et garonne’, ‘47000’);
INSERT INTO lieu VALUES (DEFAULT, ‘La rochelle’, ‘Rue des moules’, ‘17000’);
————————————–
— ETAPE —
————————————–
CREATE TABLE etape (
id_trj SERIAL NOT NULL,
id_contrat INTEGER NOT NULL,
id_lieu_depart INTEGER NOT NULL,
id_lieu_arrivee INTEGER NOT NULL,
date_heure_dep TIMESTAMP without time zone NOT NULL,
date_heure_arv TIMESTAMP without time zone NOT NULL,
distance SMALLINT,
CONSTRAINT pk_etape PRIMARY KEY (id_trj),
CONSTRAINT fk_etape_contrat FOREIGN KEY (id_contrat) REFERENCES contrat (id_contrat),
CONSTRAINT fk_etape_depart FOREIGN KEY (id_lieu_depart) REFERENCES lieu (id_lieu),
CONSTRAINT fk_etape_arrivee FOREIGN KEY (id_lieu_arrivee) REFERENCES lieu (id_lieu),
CONSTRAINT ck_etape_date_heure CHECK (date_heure_dep < date_heure_arv),
CONSTRAINT ck_etape_distance CHECK (0 <= distance AND distance <= 10000)
);
— CREATE UNIQUE INDEX idx_etape_pk ON etape (id_trj); generer automatiquement car clef primaire
CREATE INDEX idx_etape_fk_contrat ON etape (id_contrat);
CREATE INDEX idx_etape_fk_lieu_dep ON etape (id_lieu_depart);
CREATE INDEX idx_etape_fk_lieu_arv ON etape (id_lieu_arrivee);
CREATE INDEX idx_etape_date_dep ON etape (date_heure_dep);
CREATE INDEX idx_etape_date_arv ON etape (date_heure_arv);
CREATE INDEX idx_etape_distance ON etape (distance);
INSERT INTO etape VALUES (DEFAULT, 1, 1, 3, ‘2007-01-26 15:12:00’, ‘2007-01-26 16:56:00’, 150);
INSERT INTO etape VALUES (DEFAULT, 1, 3, 1, ‘2007-01-29 20:00:00’, ‘2007-01-29 21:30:00’, 150);
INSERT INTO etape VALUES (DEFAULT, 2, 1, 7, ‘2007-04-20 12:23:00’, ‘2007-04-20 15:40:00’, 250);
INSERT INTO etape VALUES (DEFAULT, 2, 7, 8, ‘2007-04-25 16:20:00’, ‘2007-04-25 18:00:00’, 180);
INSERT INTO etape VALUES (DEFAULT, 2, 8, 1, ‘2007-04-30 20:00:00’, ‘2007-04-30 22:30:00’, 270);
INSERT INTO etape VALUES (DEFAULT, 3, 1, 3, ‘2007-05-15 18:22:00’, ‘2007-05-15 20:00:00’, 150);
INSERT INTO etape VALUES (DEFAULT, 4, 1, 12, ‘2007-04-30 12:23:00’, ‘2007-04-30 15:40:00’, 600);
INSERT INTO etape VALUES (DEFAULT, 5, 10, 1, ‘2007-04-10 15:00:00’, ‘2007-04-10 19:00:00’, 600);
————————————–
— VUES —
————————————–
— liste de tout les client avec toutes ces informations (personnel ou entreprise)
CREATE VIEW client_tout
AS SELECT c.id_cl, c.nom, c.prenom, p.adresse AS adresse_perso, p.code_postal AS cp_perso, p.ville AS ville_perso, p.tel_portable, p.tel_domicile,e.id_en, e.nom AS nom_entreprise, e.adresse AS adresse_en, e.code_postal AS cp_en, e.ville AS ville_en, e.tel AS tel_en, d.tel_bureau
FROM client AS c
LEFT JOIN demandeur AS d ON c.id_cl=d.id_cl
LEFT JOIN entreprise AS e ON e.id_en=d.id_en
LEFT JOIN particulier AS p ON c.id_cl=p.id_cl;
— liste de tout le personnel avec toutes leurs informations (commercial ou conducteur)
CREATE VIEW personnel_tout
AS SELECT p.id_personnel, p.nom, p.prenom, p.date_naissance, p.nss, p.adresse, p.tel_portable, p.tel_domicile, p.code_postal, p.ville, p.prime,
c.type_permis, ‘conducteur’ AS type_personnel
FROM conducteur AS c
LEFT JOIN personnel AS p ON c.id_personnel=p.id_personnel
UNION
SELECT p.id_personnel, p.nom, p.prenom, p.date_naissance, p.nss, p.adresse, p.tel_portable, p.tel_domicile, p.code_postal, p.ville, p.prime,
‘ ‘ AS type_permis, ‘commercial’ AS type_personnel
FROM commercial AS v
LEFT JOIN personnel AS p ON v.id_personnel=p.id_personnel;
— personnel travaillant actuellement dans l’entreprise
CREATE VIEW personnel_actuel
AS SELECT pt.id_personnel, ct.id_contrat, ct.salaire, ct.type_contrat, ct.date_deb, ct.date_fin,
pt.nom, pt.prenom, pt.date_naissance, pt.nss, pt.adresse, pt.tel_portable, pt.tel_domicile, pt.code_postal, pt.ville, pt.prime,
pt.type_permis, pt.type_personnel
FROM contrat_travail AS ct
JOIN personnel_tout AS pt ON ct.id_personnel=pt.id_personnel
WHERE date_fin IS NULL OR date_fin>now() AND date_deb
— afficher la liste des contrat avec les villes par lesquels il doivents passer
CREATE VIEW etape_ville
AS SELECT id_contrat, id_trj, d.ville AS depart, a.ville AS arrivee, date_heure_dep, date_heure_arv, distance
FROM etape AS e
JOIN lieu AS d ON e.id_lieu_depart=d.id_lieu
JOIN lieu AS a ON e.id_lieu_arrivee=a.id_lieu;
Extraits du jeu d’essai
Vehicules
-
| Immatriculation | Marque | Type | Carburant | Annee sortie | Annee achat | Puissance | Conso | Nb places | Permis | |
| 0140 AQ 43 | Toyota | camion | diesel | 2003 | 2005 | 50 | 12 | E | ||
| 0032 AD 34 | Honda | voiture | super | 1988 | 2006 | 10 | 14 | B | ||
| 0423 AZ 31 | volvo | van | SP95 | 1991 | 1992 | 18 | 15 | D | ||
| 0318 BZ 31 | Renault | 4×4 | SP98 | 2006 | 2007 | 15 | 10 | B | ||
| 0789 ER 31 | Peugeot | minibus | GPL | 2006 | 2007 | 15 | 4 | B | ||
| 958 ak 47 | audi | voiture | SP98 | 2003 | 2003 | 15 | 12 | B | ||
| 987 PX 31 | mazda | minibus | diesel | 1994 | 2001 | 12 | 11 | E |
-
Conducteurs
Nom Prenom Adresse Ville Tel fixe Tel portable Permis Geofre Marcel 2 rue des chaufard 31457 Toulouse 05.48.98.47.65 06.48.98.47.65 D Segozy paul 13 rue du vendredi 31500 toulouse 0512457896 0614758963 B Commerciaux
Nom Prenom Adresse Ville Tel fixe Tel portable Tourbin Michel 3 rue des commercants 31150 Ramonville 05.45.36.78.61 06.45.36.78.61 The SQL girl Aurelie 23, rue jean paul laurens 31400 Toulouse 0524587854 0689657458 Fourchette Frank 12 impasse sans retour 66000 Les Angles 0415655894 0615487956 graulle jeremie 564 rue de la barre de faire 31854 plouf les bains 0598754632 0698587954 Clients
Nom Prenom Adresse Ville Tel fixe Tel portable dupond george 4 rue des ministres 12150 severac-le-chateau 05.67.98.44.58 06.84.68.45.74 Pote jessica 5 rue de la bonne vanne 66666 Perpignan City 05.06.66.98.76 06.66.34.65.88 Contrats
Date Immatriculation Passagers Prix Conducteur Commercial Client 2007-01-14 0140 AQ 43 4 $300.00 Geofre Tourbin dupond george 2007-04-02 0423 AZ 31 40 $3,000.00 Geofre The SQL girl DuDu Jeremie 2007-05-04 0423 AZ 31 43 $2,500.00 Geofre Tourbin The boss gregory 2006-12-15 0140 AQ 43 2 $1,500.00 Segozy Tourbin dupond george 2005-12-15 0140 AQ 43 2 $1,500.00 Geofre Tourbin DuDu Jeremie Etapes d’un contrat
Depart Date Depart Arrivee Date Arrivee Distance Rue de la pomme 31400 Toulouse 2007-04-20 12:23:00 rue foin 40500 Messanges 2007-04-20 15:40:00 250 rue foin 40500 Messanges 2007-04-25 16:20:00 Rue des girondins 33000 Bordeau 2007-04-25 18:00:00 180 Rue des girondins 33000 Bordeau 2007-04-30 20:00:00 Rue de la pomme 31400 Toulouse 2007-04-30 22:30:00 270 Les Requêtes demandées en SQL
01) — Donner la liste des villes visitées par tous les circuits ou voyages avec pour chacune le nombre de passages réels effectués. La liste sera ordonnée par ordre décroissant du nombre de passages.
SELECT l.ville, COUNT(l.ville) AS nbr
FROM lieu l, etape e
WHERE e.id_lieu_depart=l.id_lieu
OR (e.date_heure_arv=(SELECT MAX(e2.date_heure_arv) FROM etape AS e2 WHERE e2.id_contrat=e.id_contrat)
AND e.id_lieu_arrivee=l.id_lieu)
GROUP BY l.ville, l.id_lieu
HAVING NOT EXISTS (SELECT *
FROM contrat c
WHERE NOT EXISTS (SELECT *
FROM etape e2
WHERE (e2.id_lieu_arrivee=l.id_lieu OR e2.id_lieu_depart=l.id_lieu)
AND c.id_contrat = e2.id_contrat))
ORDER BY nbr DESC;
— on compte une fois la ville de départ (e.id_lieu_depart=l.id_lieu) et la ville d’arrivée finale (MAX(e2.date_heure_arv)), puis les villes suivantes en faisant attention de pas les compter deux fois (si la destination d’une étape et départ de la suivante sont la même ville). Le Having sert à traduire le « tous les circuits».
ville nbr Toulouse 7 02) –Donner les coordonnées de chaque client avec une indication pour savoir s’il est client individuel, professionnel ou les deux. Classez la liste par ordre alphabétique.
SELECT NOM,PRENOM,TYPE,ADRESSE FROM (
SELECT ‘Ind’ AS TYPE, NOM, PRENOM, ADRESSE FROM CLIENT NATURAL JOIN PARTICULIER
UNION
SELECT ‘Pro’ AS TYPE, CLIENT.NOM, CLIENT.PRENOM, ENTREPRISE.ADRESSE FROM CLIENT NATURAL JOIN DEMANDEUR, ENTREPRISE
UNION
SELECT ‘IndPro’ AS TYPE, NOM, PRENOM, ADRESSE FROM CLIENT NATURAL JOIN PARTICULIER NATURAL JOIN DEMANDEUR) AS TEST
ORDER BY NOM,PRENOM— On a fait une union des clients individuels, professionnels ou les deux, puis on ajouté un type qui est égal à ‘Pro’, ‘Ind’ ou ‘IndPro’ suivant le cas.
nom prenom type adresse Bidule jihed Pro 5 rue du puits DuDu Jeremie Pro 5 rue du puits Machin Pascal Pro 5 rue du puits Pote jessica Ind 5 rue de la bonne vanne The SQL Girl aurelie Pro 5 rue du puits The boss gregory Pro 5 rue du puits The charte graphique man Raphael Pro 5 rue du puits dupond george Ind 4 rue des ministres 03 ) — Donner pour chaque client de la base : son identité et la liste de contrats passée avec notre société (numéro, dates, départ, destination), ordonnée chronologiquement .
SELECT c.nom AS Nom, c.prenom AS Prenom, con.id_contrat AS Numero, con.date AS date, d.ville AS Depart, a.ville AS Destination
FROM Client AS c
LEFT JOIN Contrat AS con ON con.id_cl = c.id_cl
LEFT JOIN Etape AS e ON e.id_contrat = con.id_contrat
LEFT JOIN Lieu AS d ON e.id_lieu_depart = d.id_lieu
LEFT JOIN Lieu AS a ON e.id_lieu_arrivee = a.id_lieu
ORDER BY con.date, e.date_heure_dep ;
— Lorsque les champs sont vides c’est que les clients n’ont pas encore passé de contrat.
nom prenom numero date depart destination DuDu Jeremie 4 2005-12-15 Toulouse La rochelle dupond george 18 2006-12-15 Lyon Toulouse dupond george 1 2007-01-14 Toulouse Tarbes dupond george 1 2007-01-14 Tarbes Toulouse DuDu Jeremie 2 2007-04-02 Toulouse Messanges DuDu Jeremie 2 2007-04-02 Messanges Bordeau DuDu Jeremie 2 2007-04-02 Bordeau Toulouse The boss gregory 3 2007-05-04 Toulouse Tarbes Pote jessica Bidule jihed Machin Pascal The charte graphique man Raphael The SQL Girl aurelie 04 ) — Donner la liste des voyages de l’année par ordre croissant de leur longueur.
SELECT c.id_contrat, c.date, SUM(e.distance) as longueur
FROM contrat AS c
JOIN etape AS e ON c.id_contrat=e.id_contrat
WHERE extract(year from date) = extract(year from current_date)
GROUP BY c.id_contrat, date
ORDER BY longueur
— Pour avoir la longueur, on somme les distances des étapes par voyage ( sum et group by c.id_contrat, date). Pour dire de l’année, on utilise la condition : extract(year from date) = extract(year from current_date).
id_contrat date longueur 3 2007-05-04 150 1 2007-01-14 300 2 2007-04-02 700 05) –Donner pour chaque commercial, son identité et la liste des contrat qu’il a signé avec le nom des clients, liste donnée par ordre chronologique.
SELECT contrat.date,personnel.nom, personnel.prenom, contrat.id_contrat, client.nom, client.prenom
FROM personnel, client,contrat
WHERE (contrat.id_commercial=personnel.id_personnel and contrat.id_cl=client.id_cl)
GROUP BU contrat.date,personnel.nom, personnel.prenom,contrat.id_contrat, client.nom, client.prenom, contrat.date
ORDER BY contrat.date
date nom prenom id_contrat nom prenom 2005-12-15 Tourbin Michel 4 DuDu Jeremie 2006-12-15 Tourbin Michel 18 dupond george 2007-01-14 Tourbin Michel 1 dupond george 2007-04-02 The SQL girl Aurelie 2 DuDu Jeremie 2007-05-04 Tourbin Michel 3 The boss gregory 06) –Donner la liste des clients (entreprise, nom, prénom, adresse) ordonné par ordre décroissant du nombre de contrats passés.
SELECT COUNT(ct.id_cl) as nb_contrats, e.nom as entreprise, c.nom, c.prenom, e.adresse, e.code_postal, e.ville
FROM client c, demandeur d, entreprise e, contrat ct
WHERE c.id_cl = d.id_cl AND d.id_en = e.id_en AND c.id_cl = ct.id_cl
GROUP BY e.nom , c.nom, c.prenom, e.adresse, e.code_postal, e.ville
UNION
SELECT COUNT(ct.id_cl) as nb_contrats, null, c.nom, c.prenom, p.adresse, p.code_postal, p.ville
FROM client c, particulier p, contrat ct
WHERE c.id_cl = p.id_cl AND c.id_cl = ct.id_cl
GROUP BY c.nom, c.prenom, p.adresse, p.code_postal, p.villeORDER by nb_contrats desc
— on compte le nombre de contrat passés par chacun des clients (COUNT(ct.id_cl) et GROUP BY c.nom, c.prenom, p.adresse, p.code_postal, p.ville) par ordre décroissant de ce nombre (ORDER by nb_contrats desc).
nb_contrats entreprise nom prenom adresse code_postal ville 1 INSAT The boss gregory 5 rue du puits 31000 Toulouse 2 INSAT DuDu Jeremie 5 rue du puits 31000 Toulouse 2 dupond george 4 rue des ministres 12150 severac-le-chateau 07) –Donner les coordonnées du client le plus ancien et du client le plus jeune (entreprise, nom, prénom, adresse, ancienneté).
SELECT (date(‘now’)-MIN(ct.date)) as anciennete, e.nom as entreprise, c.nom, c.prenom, e.adresse, e.code_postal, e.ville
FROM client c, demandeur d, entreprise e, contrat ct
WHERE c.id_cl = d.id_cl AND d.id_en = e.id_en AND c.id_cl = ct.id_cl
GROUP BY e.nom , c.nom, c.prenom, e.adresse, e.code_postal, e.ville
UNION
SELECT (date(‘now’)-MIN(ct.date)) as anciennete, null, c.nom, c.prenom, p.adresse, p.code_postal, p.ville
FROM client c, particulier p, contrat ct
WHERE c.id_cl = p.id_cl AND c.id_cl = ct.id_cl
GROUP BY c.nom, c.prenom, p.adresse, p.code_postal, p.ville
ORDER BY anciennete DESC— On calcule l’ancienneté du client (date(‘now’)-MIN(ct.date) => date d’aujourd’hui – date du premier contrat signé par le client). On utilise l’union pour grouper les particuliers et entreprises.
anciennete entreprise nom prenom adresse code_postal ville 538 INSAT DuDu Jeremie 5 rue du puits 31000 Toulouse 173 dupond george 4 rue des ministres 12150 severac-le-chateau 33 INSAT The boss gregory 5 rue du puits 31000 Toulouse 08) –Donner l’identité du ou des chauffeurs ayant conduit tous les modèles de véhicule de la société.
SELECT NOM,PRENOM,TYPE_VEHICULE
FROM CONDUCTEUR
NATURAL JOIN PERSONNEL
JOIN CONTRAT ON ID_PERSONNEL=ID_CONDUCTEUR
JOIN VEHICULE ON VEHICULE.NUM_IMMAT=CONTRAT.NUM_IMMAT
GROUP BY NOM,PRENOM, TYPE_VEHICULE
HAVING COUNT(*) =
(SELECT MAX(SUP)
FROM (SELECT COUNT(*) AS SUP
FROM VEHICULE
GROUP BY TYPE_VEHICULE) AS NUM_TYPE)— Le having sert à ajouter la condition « le nombre de type de véhicules conduits par le chauffeur doit être égal au nombre de type de véhicule total».
(désolé mais personne ne conduit tous les types de véhicules parmis nos chauffeurs)
09) –Donner pour chaque commercial ses nom, prénom et ancienneté totale.
select nom,prenom,sum(coalesce(date_fin,date(‘now’))- date_deb) as anciennete
from personnel, commercial,contrat_travail
where commercial.id_personnel = personnel.id_personnel
and contrat_travail.id_personnel = personnel.id_personnel
group by nom,prenom ;
— (coalesce(date_fin,date(‘now’))- date_deb) sert à calculer l’ancienneté. En fait on ajoute les périodes des CDD et CDI. Si on a un CDI, il date_fin du contrat est null, on fait la soustraction avec la date d’aujourd’hui (fonction coalesce). Si la personne a démissionné, il y a une date de fin au CDI.
nom prenom anciennete Tourbin Michel 220 Fourchette Frank 30 The SQL girl Aurelie 461 10) –Donner les numéros, entreprise, nom et prénom du ou des clients qui ont contracté chaque année sans interruption depuis leur premier contrat.
select clientFidele.id_cl, client.nom, client.prenom, entreprise.nom as « Nom Entreprise » from
— Nombre d’années depuis la signature du premier contrat
(select id_cl, max(extract(year from now()) – extract(year from contrat.date))+1 as nbAn from contrat
where contrat.id_cl IN (select id_cl from client)
group by id_cl
intersect
— Nombre de contrats dont les années sont distinctes
select id_cl, count(distinct extract(year from contrat.date)) as nbAn from contrat
where contrat.id_cl IN (select id_cl from client)
group by id_cl) as clientFidele
natural join client
left join demandeur on demandeur.id_cl=client.id_cl
left join entreprise on entreprise.id_en= demandeur.id_en
— Avec le intersect, on compare les deux résultats et si ceux ci sont égaux, cela veut dire que le client a contracté chaque année sans interruptions depuis la première année de signature. On peut alors afficher les caractéristiques correspondantes.
id_cl nom prenom Nom Entreprise 1 dupond george 6 The boss gregory INSAT 11) –Quelle est la ville la plus visitée au départ de Toulouse dans tous les voyages ?
select ville_plus_visit_dep_Toulouse
from (select lieu.ville as ville_plus_visit_dep_Toulouse,count(lieu.ville) as nb_passages
from lieu, etape
where lieu.id_lieu = etape.id_lieu_arrivee
and etape.id_lieu_depart in (select id_lieu_depart from etape,lieu where etape.id_lieu_depart = lieu.id_lieu and lieu.ville = ‘Toulouse’)
group by lieu.ville) as Villes_Dep_Toulouse
where nb_passages = (select max(nb_passages)
from(select lieu.ville,count(lieu.ville) as nb_passages
from lieu, etape
where lieu.id_lieu = etape.id_lieu_arrivee
and etape.id_lieu_depart in (select id_lieu_depart from etape,lieu where etape.id_lieu_depart = lieu.id_lieu and lieu.ville = ‘Toulouse’)
group by lieu.ville) as max_passages);
— On compte le nombre de fois où apparaît chaque ville au départ de Toulouse, puis on ajoute comme condition « avoir le max de ce nombre ».
ville_plus_visit_dep_toulouse Tarbes 12) –Donner pour chaque client (entreprise ou personne), son identité et le chiffre d’affaire total qu’il a amené dans la société. La liste sera classée par ordre décroissant de ce total.
(select client.nom, client.prenom, sum(contrat.prix) as ca_total, null as entreprise
from client,contrat,demandeur, particulier
where (contrat.id_cl=client.id_cl) and (particulier.id_cl=client.id_cl)
group by client.nom,client.prenom)
UNION
(select client.nom, client.prenom, sum(contrat.prix) as ca_total, entreprise.nom
from client,contrat,entreprise,demandeur
where (contrat.id_cl=client.id_cl) and (client.id_cl=demandeur.id_cl)
group by client.nom,client.prenom,entreprise.nom)
order by CA_Total desc
— « Null as entreprise » sert à avoir le même nombre de colonnes sélectionnées de chaque côté de l’union. Comme pour les précédentes, l’union sert à avoir les clients individuels et les entreprises.
nom prenom ca_total entreprise dupond george $10,800.00 DuDu Jeremie $4,500.00 INSAT The boss gregory $2,500.00 INSAT 13) –Donner la liste des chauffeurs ayant conduit cette année avec, pour chacun, le nombre de voyages auquel il a participé cette année.
select personnel.nom, personnel.prenom, count(*) as nombre_voyages
from personnel,contrat
where (contrat.id_conducteur=personnel.id_personnel) and (extract(year from contrat.date) = extract(year from current_date))
group by personnel.nom, personnel.prenom
— La condition « cette année » est représentée par (extract(year from contrat.date) = extract(year from current_date))
nom prenom nombre_voyages Geofre Marcel 3 14) –Donner pour chaque chauffeur, le nombre total de kilomètres parcourus depuis le début de l’année et la distance moyenne des voyages. Ordonner alphabétiquement.
*** Description des étapes de construction de la requête :
*** Première partie, affichage de la distance parcourue
select p.nom, p.prenom, sum(e.distance) as distance_parcourus
from personnel as p, contrat as c, etape as e
where (c.id_conducteur=p.id_personnel) and (c.date >= extract(year from current_date)) and (e.id_contrat=c.id_contrat)
group by p.nom, p.prenom
order by distance_parcourus desc
***calculer la longueur des voyages effectués par chaque chauffeur
select p.nom, p.prenom, c.id_contrat, sum(e.distance) as longueur_voyage
from personnel as p, etape as e, contrat as c
where (c.id_conducteur=p.id_personnel) and (e.id_contrat=c.id_contrat)
group by p.nom,p.prenom,c.id_contrat
****3ème étape, calculer la longueur moyenne des voyages effectués par chaque chauffeur, on va utiliser la 2ème requête
select avg(longueur_voyage) from
(select p.nom, p.prenom, c.id_contrat, sum(e.distance) as longueur_voyage
from personnel as p, etape as e, contrat as c
where (c.id_conducteur=p.id_personnel) and (e.id_contrat=c.id_contrat)
group by p.nom,p.prenom,c.id_contrat
) as longueur_moyenne_des_voyages
****4ème étape // voici la requête finale :
select nom, prenom, sum(longueur_voyage) as distance_parcourus, avg(longueur_voyage) as longueur_moy_voyage from
(select p.nom as nom, p.prenom as prenom, c.id_contrat as contrat, sum(e.distance) as longueur_voyage
from personnel as p, etape as e, contrat as c
where (c.id_conducteur=p.id_personnel) and (e.id_contrat=c.id_contrat)
group by p.nom,p.prenom,c.id_contrat
) as R1
group by nom, prenom
order by distance_parcourus desc
nom prenom distance_parcourus longueur_moy_voyage Geofre Marcel 1750 437.50 Segozy paul 600 600.00 15) –Donner la liste des commerciaux présents dans l’entreprise cette année avec le nombre total de clients avec lesquels ils ont contracté.
16)
SELECT COUNT(ID_CL) AS NOMBRE_CLIENTS, NOM, PRENOM
FROM CONTRAT, (SELECT *
FROM PERSONNEL
NATURAL JOIN COMMERCIAL
NATURAL JOIN CONTRAT_TRAVAIL
WHERE extract(year from (DATE_FIN))>=extract(year from now()) OR DATE_FIN IS NULL) AS COM
WHERE COM.ID_PERSONNEL=CONTRAT.ID_COMMERCIAL
GROUP BY NOM,PRENOM— « présents dans l’entreprise cette année » se traduit par extract(year from (DATE_FIN))>=extract(year from now()) où DATE_FIN est la date de fin du contrat d’un commercial.
nombre_clients nom prenom 4 Tourbin Michel 1 The SQL girl Aurelie 16) impossible à faire avec notre base.
17) –Donner la durée moyenne des contrats de travail à durée déterminée de l’entreprise depuis la création de la base.
select avg(date_fin – date_deb) as moyenne_duree_des_CDD
from contrat_travail
where type_contrat = ‘CDD’;
moyenne_duree_des_cdd 47.00 18) –Quel est le ou les vehicule(s) n’ayant pas encore été conduit(s) par tous les chauffeurs (avec le nom de ceux-ci) ?
select vehicule.num_immat, personnel.nom, personnel.prenom
from vehicule, personnel, conducteur
where personnel.id_personnel = conducteur.id_personnel
and vehicule.num_immat not in (select contrat.num_immat
from conducteur, contrat
where conducteur.id_personnel = contrat.id_conducteur
group by contrat.num_immat
having count(distinct contrat.id_conducteur) = (select count(*) from conducteur))
and conducteur.id_personnel not in (select conducteur.id_personnel
from conducteur, contrat
where conducteur.id_personnel = contrat.id_conducteur
and contrat.num_immat not in (select contrat.num_immat
from conducteur, contrat
where conducteur.id_personnel = contrat.id_conducteur
group by contrat.num_immat
having count(distinct contrat.id_conducteur) = (select count(*) from conducteur)));
— La condition est que le conducteur ne soit pas (not in) dans la liste des conducteurs qui conduisent un véhicule qui n’est pas (not in) dans les véhicules ayant été conduit par tous les chauffeurs (having count(distinct contrat.id_conducteur) = (select count(*) from conducteur)), et que le véhicule ne soit pas non plus dans les véhicules ayant été conduits par tous les chauffeurs.
num_immat nom prenom 0032 AD 34 Segozy paul 0423 AZ 31 Segozy paul 0318 BZ 31 Segozy paul 0789 ER 31 Segozy paul 958 ak 47 Segozy paul 987 PX 31 Segozy paul 987 HT 31 Segozy paul 987 AD 31 Segozy paul 7894 TY 31 Segozy paul 19) –Donner le palmarès des chiffres d’affaires des commerciaux cette année avec leur montant.
select p.nom,p.prenom,sum(c.prix) as Chiffre_Affaires
from personnel as p, contrat as c
where (c.id_commercial=p.id_personnel) and (extract(year from c.date) = extract(year from current_date))
group by p.nom, p.prenom
order by chiffre_affaires desc
— on additionne le prix des contrats signés par chacun des commerciaux (group by) durant cette année (extract(year from c.date) = extract(year from current_date)).
nom prenom chiffre_affaires The SQL girl Aurelie $3,000.00 Tourbin Michel $2,800.00 20) –Quel est le nombre de clients qui n’ont signé aucun contrat cette année ?
SELECT COUNT(*) AS NB_CLIENTS_AUCUN_CONTRAT_CETTE_ANNEE
FROM CLIENT
WHERE ID_CL NOT IN (SELECT ID_CL FROM CONTRAT WHERE extract(year from date) = extract(year from now()))— La condition est que le client ne soit pas (not in) dans la liste des clients ayant signé un contrat cette année (extract(year from date)=extract(year from now())).
nb_clients_aucun_contrat_cette_annee 5 Requêtes en algèbre et calcul relationnel
3°) Donner pour chaque client de la base : son identité et la liste de contrats passée avec notre société (numéro, dates, départ, destination), ordonnée chronologiquement .
Calcul relationnel :
{Nom,Prenom,NumContrat,Date,Depart,Destination/CONTRAT(id_cl : Numero,nom : nom,prenom : prenom, date_contrat : date) ^ ETAPE(id_contrat : Numero, id_lieu_dep : x, id_lieu_dest : y) ^ LIEU(id_clieu_dep : x, nom_ville : Depart) ^ LIEU(id_lieu_dest : y, nom_ville : Destination)}
17°) Donner la durée moyenne des contrats de travail à durée déterminée de l’entreprise depuis la création de la base.
Calcul relationnel :
{moyenne_duree/ il existe date_fin,date_deb, CONTRAT_TRAVAIL(C) ^ (C.date_fin – C.date_deb : duree) ^ (moyenne(duree) : moyenne_duree)}
20°) Quel est le nombre de clients qui n’ont signé aucun contrat cette année ?
Calcul relationnel :
{nb_Client_aucun_contrat_cette_annee/CLIENT(C) ^ (CONTRAT(CT) ^ (année(CT.date) = année(aujourd’hui)) ^ (C.id_cl /= CT.id_cl) ^ (compter(C) : nb_Client_aucun_contrat_cette_annee)}
