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

Ce rapport, reflet de l’avancement étape par étape d'un projet de création d’une base de données pour une société de transport et la gestion de son parc de véhicules, contient premièrement le cahier des charges ainsi que le modèle entité/association et relationnel avec la validation associée et le bilan global du projet puis, en annexe, le dictionnaire des données ainsi que l’implantation, insertion des données dans la base et les requêtes demandées.

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

080309_1520_Systmedinfo5.png

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

    080309_1520_Systmedinfo6.png

     

    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

ImmatriculationMarqueTypeCarburantAnnee sortieAnnee achatPuissanceConsoNb placesPermis 
0140 AQ 43Toyotacamiondiesel200320055012 E 
0032 AD 34Hondavoituresuper198820061014 B 
0423 AZ 31volvovanSP95199119921815 D 
0318 BZ 31Renault4×4SP98200620071510 B 
0789 ER 31PeugeotminibusGPL20062007154 B 
958 ak 47audivoitureSP98200320031512 B 
987 PX 31mazdaminibusdiesel199420011211 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)}

Pages 1 2
Étiquettes

Publications similaires