Table of Contents

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)
1. - 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.ville

ORDER 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)}

Système d’information pour la gestion d’un parc de véhicules

La validation

Avec les formes normales

1NF

2NF

3NF

3NF de BOYCE-CODD-KENT

4NF

5NF

Méthode des paquets

Méthode

Résultat

Différence avec la modélisation

La carte des concepts

Bilan global du projet

Mise en place

Les différentes étapes

Fonctionnement du groupe

Conclusion

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

Extraits du jeu d’essai

Les Requêtes demandées en SQL

Requêtes en algèbre et calcul relationnel

J’aime ça :

Articles similaires

Faire un don ponctuel

Faire un don mensuel

💎 Boostez vos finances avec ces offres exclusives ! 🚀

La validation

Avec les formes normales

1NF

2NF

3NF

3NF de BOYCE-CODD-KENT

4NF

5NF

Méthode des paquets

Méthode

Résultat

Différence avec la modélisation

La carte des concepts

Bilan global du projet

Mise en place

Les différentes étapes

Fonctionnement du groupe

Conclusion

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

Extraits du jeu d’essai

Les Requêtes demandées en SQL

Requêtes en algèbre et calcul relationnel

Partager :

J’aime ça :

Articles similaires

Publications similaires

Résolution d’un Problème de Connectivité Wi-Fi lié à la MTU dans un Environnement Ruckus/Ucopia

Résoudre les problèmes DHCP sur Ruckus et Ucopia sous VMware : le rôle clé du Promiscuous Mode, MAC Address Changes et Forged Transmits

Gestion des conflits de réplication Active Directory après une restauration de contrôleur de domaine