1. #1
    Membre à l'essai Avatar de gautier1er
    Homme Profil pro
    Webmaster
    Inscrit en
    août 2017
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 23
    Localisation : Côte d'Ivoire

    Informations professionnelles :
    Activité : Webmaster

    Informations forums :
    Inscription : août 2017
    Messages : 30
    Points : 18
    Points
    18

    Par défaut Quelques requêtes en SQL concernant MERISE 2

    Bonjour amis du Club ,etant débutant
    j'ai besoin de votre aide pour créer les tables COURRIER , COURRIER DEPART et COURRIER ARRIVE ; ainsi que quelques requete d'insertion , modification et suppression sur les tables COURRIER , COURRIER DEPART et COURRIER ARRIVE en SQL car c'est la premiere fois que je tombe sur un MCD avec heritage ,en plus je n'ai jamais utilisé triggers .
    aidez moi SVP!

    voici mon MCD ci-joint et quelque relations

    COURRIER (NumCour , NumOrdreCourArriv, NumOrdreCourDep ,ObjetCour , DateEcritureCour,
    # CodePers)

    COURRIER ARRIVE (NumOrdreCourArriv , NumCour , DateArrivCourrServCour , NumOrgCourArriv )

    COURRIER DEPART (NumOrdreCourDep , NumCour , DateExpedCourDep , NatureCourDep , NumCourServDest )

    REPONDRE (NumOrdreCourArriv ,#NumOrdreCourDep)



    cordialement
    Images attachées Images attachées

  2. #2
    Modérateur
    Avatar de al1_24
    Homme Profil pro
    Ingénieur d'études décisionnel
    Inscrit en
    mai 2002
    Messages
    7 442
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 56
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 7 442
    Points : 22 564
    Points
    22 564

    Par défaut

    Quelles requêtes as-tu déjà écrites ?
    Où rencontres-tu exactement un problème ?
    Des triggers ? Pour quoi faire ?
    Modérateur Langage SQL
    Règles du forum Langage SQL à lire par tous, N'hésitez pas à consulter les cours SQL
    N'oubliez pas le bouton et pensez aux balises
    [code]
    Si une réponse vous a aidé à résoudre votre problème, n'oubliez pas de voter pour elle en cliquant sur
    Aide-toi et le forum t'aidera : Un problème exposé sans mentionner les tentatives de résolution infructueuses peut laisser supposer que le posteur attend qu'on fasse son travail à sa place... et ne donne pas envie d'y répondre.

  3. #3
    Membre confirmé Avatar de Cincinnatus
    Homme Profil pro
    Développeur Java
    Inscrit en
    mars 2007
    Messages
    220
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Développeur Java
    Secteur : Service public

    Informations forums :
    Inscription : mars 2007
    Messages : 220
    Points : 622
    Points
    622

    Par défaut

    Si le problème se situe au niveau de l'héritage, la solution est à trouver au niveau du MLD ou du MPD : faut-il fusionner ou séparer les type de courriers ?
    Donc :
    soit une table regroupant COURRIER / COURRIER ARRIVE / COURRIER DEPART + la table REPONDRE
    soit une table COURRIER + les tables COURRIER ARRIVE, COURRIER DEPART et la table REPONDRE, avec les contraintes d'intégrité qui vont bien,
    soit des tables (COURRIER + COURRIER ARRIVE), (COURRIER + COURRIER DEPART), REPONDRE.

    Le SQL découle de ce choix au niveau logique.

  4. #4
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 996
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 2 996
    Points : 6 582
    Points
    6 582
    Billets dans le blog
    1

    Par défaut

    Bonjour,

    Si votre modèle de données a été créé à l'aide d'un logiciel de modélisation, alors le script (DDL) de création des tables sera créé automatiquement lors de la génération du MPD (après avoir choisi le SGBD qui va bien)
    Vous aurez donc une base de DDL qu'il ne restera plus qu'à customiser

    Pour les requêtes d'insertion, modification et suppression, il faut justement ce DDL pour pouvoir écrire les requêtes correctement

  5. #5
    Membre à l'essai Avatar de gautier1er
    Homme Profil pro
    Webmaster
    Inscrit en
    août 2017
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 23
    Localisation : Côte d'Ivoire

    Informations professionnelles :
    Activité : Webmaster

    Informations forums :
    Inscription : août 2017
    Messages : 30
    Points : 18
    Points
    18

    Par défaut

    J'ai modifié mon MCD.MCD AMELIORE.pdf
    voici la liste de mes tables conçernées .


    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    CREATE TABLE ville
    (
    IdVille TINYINT(3) NOT NULL AUTO_INCREMENT,
    NomVille VARCHAR(50) NOT NULL,
    UNIQUE NomVille(NomVille),
    PRIMARY KEY(IdVille)
    )ENGINE=InnoDB;
     
     
     
    CREATE TABLE direction
    (
    IdDirect CHAR(10) NOT NULL,
    NomDirect VARCHAR(255) NOT NULL,
    SigleDirect CHAR(20) NOT NULL,
    IdVille TINYINT(3) NOT NULL ,
    PRIMARY KEY (IdDirect),
    FOREIGN KEY(IdVille) REFERENCES ville (IdVille)
    ON DELETE CASCADE ON UPDATE CASCADE
    )ENGINE=InnoDB;
     
    CREATE TABLE service
    (
    IdService CHAR(10) NOT NULL,
    NomService VARCHAR(255) NOT NULL, 
    SigleService CHAR(20) NOT NULL,
    TelService  DECIMAL(8,0) NOT NULL,
    EmailService VARCHAR(100) NOT NULL,
    IdDirect CHAR(10) NOT NULL,
    PRIMARY KEY (IdService),
    FOREIGN KEY(IdDirect) REFERENCES direction(IdDirect)
    ON DELETE CASCADE ON UPDATE CASCADE	
    )ENGINE=InnoDB;
     
     
    CREATE TABLE agent
    (
    MatAgent INT  NOT NULL ,
    NomAgent VARCHAR(50) NOT NULL ,
    PrenAgent VARCHAR(100) NOT NULL ,
    TelAgent DECIMAL(8,0)  NOT NULL,
    SexeAgent ENUM('homme','femme') NOT NULL,
    EmailAgent VARCHAR(50) DEFAULT NULL ,
    DateNaisAgent DATE DEFAULT NULL,
    CodePostAgent CHAR(10) DEFAULT NULL,
    BoitePostAgent CHAR(10) DEFAULT NULL,
    IdService CHAR(10) NOT NULL ,
    IdVille TINYINT(3) NOT NULL,
    FOREIGN KEY(IdVille) REFERENCES ville (IdVille)
    ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(IdService) REFERENCES service(IdService)
    ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(MatAgent),
    INDEX index_nom_prenom_ag (NomAgent,PrenAgent) /*pour les recherches   */
    )ENGINE=InnoDB;
     
     
    CREATE TABLE correspondante
    (
    CodeCoresp BIGINT(10) NOT NULL AUTO_INCREMENT,
    NomCoresp VARCHAR(50) NOT NULL,
    StatutCoresp ENUM('expediteur','destinataire') NOT NULL,
    IdVille TINYINT(3) NOT NULL,
    FOREIGN KEY(IdVille) REFERENCES ville (IdVille)
    ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(CodeCoresp)
    )ENGINE=InnoDB;
     
    CREATE TABLE personne_morale
    (
    CodeCoresp BIGINT(10) NOT NULL, 
    SiglePers CHAR(20) NOT NULL,
    RaisonSocial VARCHAR(255) NOT NULL,
    FOREIGN KEY(CodeCoresp) REFERENCES  correspondante(CodeCoresp)
    ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(CodeCoresp)
    )ENGINE=InnoDB;
     
     
    CREATE TABLE personne_physique
    (
    CodeCoresp BIGINT(10) NOT NULL, 
    PrenPers VARCHAR(255) NOT NULL,
    EmailPers VARCHAR(100) DEFAULT NULL,
    DateNaisPers DATE DEFAULT NULL,
    SexePers ENUM('homme','femme') NOT NULL,
    TelPers  DECIMAL(8,0) NOT NULL,
    BoitePostPers  CHAR(10) DEFAULT NULL,
    CodePostPers CHAR(10) DEFAULT NULL,
     
    FOREIGN KEY(CodeCoresp) REFERENCES  correspondante(CodeCoresp)
    ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(CodeCoresp)
    )ENGINE=InnoDB;
     
     
     
     
    CREATE TABLE courrier
    (
    NumCour BIGINT(10)  NOT NULL AUTO_INCREMENT, 
    ObjetCour VARCHAR(255) NOT NULL,
    DateEcritureCour DATE NOT NULL, 
    annee  YEAR NOT NULL,
    MatAgent INT  NOT NULL ,
    CodeCoresp BIGINT(10) NOT NULL ,
    FOREIGN KEY(CodeCoresp) REFERENCES  correspondante(CodeCoresp)
    ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(MatAgent) REFERENCES  agent(MatAgent)
    ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (NumCour)
    )ENGINE=InnoDB;
     
     
     
    CREATE TABLE courrierDepart
    (
    NumOrdreCourDep CHAR(10)  NOT NULL ,
    DateExpedCourDep DATE NOT NULL, 
    NumCourServDest CHAR(10) DEFAULT NULL, 
    NatureCourDep ENUM('ordinaire','special') DEFAULT "ordinaire",
     NumCour BIGINT(10) NOT NULL,
     FOREIGN KEY (NumCour) REFERENCES courrier (NumCour)
     ON DELETE CASCADE ON UPDATE CASCADE, 
      UNIQUE NumCour(NumCour), 
      PRIMARY KEY (NumOrdreCourDep)
     
    )ENGINE=InnoDB;
     
    CREATE TABLE courrierArrive
    (
    NumOrdreCourArriv CHAR(10)  NOT NULL ,
    DateArrivCourServCo DATE NOT NULL, 
    NumOrgCourArriv  CHAR(10) DEFAULT NULL, 
    DateRetCourServCo DATE DEFAULT NULL,
    DateRecepServDest DATE DEFAULT NULL,
     NumCour BIGINT(10) NOT NULL,
     IdDirect CHAR(10) DEFAULT NULL,
     FOREIGN KEY (NumCour) REFERENCES courrier (NumCour)
     ON DELETE CASCADE ON UPDATE CASCADE, 
     FOREIGN KEY(IdDirect) REFERENCES direction(IdDirect)
    ON DELETE CASCADE ON UPDATE CASCADE,	
     UNIQUE NumCour(NumCour), 
     PRIMARY KEY (NumOrdreCourArriv)
    )ENGINE=InnoDB;
     
     
    CREATE TABLE repondre
    (
    NumOrdreCourArriv CHAR(10)  NOT NULL ,
    NumOrdreCourDep CHAR(10)  NOT NULL ,
      FOREIGN KEY(NumOrdreCourArriv) REFERENCES courrierArrive(NumOrdreCourArriv)
    ON DELETE CASCADE ON UPDATE CASCADE,
     FOREIGN KEY(NumOrdreCourDep) REFERENCES courrierDepart(NumOrdreCourDep)
    ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(NumOrdreCourArriv)
     
    )ENGINE=InnoDB;
    J'utilise le logiciel POWER AMC et quand je genère le MPD je remarque que la structure de d'autres tables sont différentes de la mienne par exemple :
    la table courrier arrivée avec POWER AMC :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    create table COURRIER_ARRIVE
    (
       NUMCOUR              numeric(10,0) not null,
       NUMORDRECOURARRIV    varchar(10) not null,
       IDDIRECT             varchar(10) not null,
       COU_NUMCOUR          numeric(10,0),
       NUMORDRECOURDEP      varchar(10),
       CODECORESP           numeric(10,0),
       MATAGENT             varchar(20),
       COR_CODECORESP       numeric(10,0),
       OBJETCOUR            varchar(255),
       DATEECRITURECOUR     date,
       DATEARRIVCOURSERVCO  date,
       NUMORGCOURARRIV      numeric(10,0),
       DATERETCOURSERVCO    date,
       DATERECEPSERVDEST    date,
       primary key (NUMCOUR, NUMORDRECOURARRIV)
    );
    quelle est la bonne methode ?

  6. #6
    Membre confirmé Avatar de Cincinnatus
    Homme Profil pro
    Développeur Java
    Inscrit en
    mars 2007
    Messages
    220
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Développeur Java
    Secteur : Service public

    Informations forums :
    Inscription : mars 2007
    Messages : 220
    Points : 622
    Points
    622

    Par défaut

    Citation Envoyé par gautier1er Voir le message
    J'utilise le logiciel POWER AMC et quand je genère le MPD je remarque que la structure de d'autres tables sont différentes de la mienne par exemple :
    la table courrier arrivée avec POWER AMC
    Là, Power AMC a utilisé la troisième possibilité avec la fusion des tables mère et dérivée : (COURRIER + COURRIER ARRIVE)
    Il me semble que dans l'outil il est possible de préciser le type d'héritage souhaité. Sinon, il faut reprendre le SQL à la main.

    Citation Envoyé par gautier1er Voir le message
    quelle est la bonne methode ?
    La bonne méthode est celle qui convient le mieux à votre besoin.

  7. #7
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 996
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 2 996
    Points : 6 582
    Points
    6 582
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par Cincinnatus Voir le message
    Là, Power AMC a utilisé la troisième possibilité avec la fusion des tables mère et dérivée : (COURRIER + COURRIER ARRIVE)
    Il me semble que dans l'outil il est possible de préciser le type d'héritage souhaité. Sinon, il faut reprendre le SQL à la main.
    En effet, il faut double-cliquer sur le symbole de l'héritage, (le demi-cercle) puis choisir l'onglet "génération" de la fenêtre "propriété de l'héritage" et enfin activer le bouton "n'hériter que des attributs primaires"

    Nom : AMC_heritage.png
Affichages : 129
Taille : 15,3 Ko

  8. #8
    Membre à l'essai Avatar de gautier1er
    Homme Profil pro
    Webmaster
    Inscrit en
    août 2017
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 23
    Localisation : Côte d'Ivoire

    Informations professionnelles :
    Activité : Webmaster

    Informations forums :
    Inscription : août 2017
    Messages : 30
    Points : 18
    Points
    18

    Par défaut

    Citation Envoyé par Cincinnatus Voir le message
    Là, Power AMC a utilisé la troisième possibilité avec la fusion des tables mère et dérivée : (COURRIER + COURRIER ARRIVE)
    Il me semble que dans l'outil il est possible de préciser le type d'héritage souhaité. Sinon, il faut reprendre le SQL à la main.
    .
    j'ai fais le SQL a la main puis j'ai comparé avec celle généré par POWER AMC

    voir en haut

  9. #9
    Membre à l'essai Avatar de gautier1er
    Homme Profil pro
    Webmaster
    Inscrit en
    août 2017
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 23
    Localisation : Côte d'Ivoire

    Informations professionnelles :
    Activité : Webmaster

    Informations forums :
    Inscription : août 2017
    Messages : 30
    Points : 18
    Points
    18

    Par défaut

    Citation Envoyé par escartefigue Voir le message
    En effet, il faut double-cliquer sur le symbole de l'héritage, (le demi-cercle) puis choisir l'onglet "génération" de la fenêtre "propriété de l'héritage" et enfin activer le bouton "n'hériter que des attributs primaires"

    Nom : AMC_heritage.png
Affichages : 129
Taille : 15,3 Ko
    j'ai fais exactement ça mais le problème est que la clé primaire de la fille est constituée de :id de la fille + id de la mère c'est le cas avec le logiciel power AMC
    or moi j'ai déclaré id de la mère en tant que clé étrangère

  10. #10
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 996
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 2 996
    Points : 6 582
    Points
    6 582
    Billets dans le blog
    1

    Par défaut

    Ici il s'agit d'un héritage, donc les sous-type héritent de la PK du sur-type : nul besoin d'un identifiant dans les sous-type "courrier départ" et "courrier arrivée"

    De plus, vous avez défini des identifiants PK de type varchar, ce qu'il ne faut surtout pas faire, les identifiants PK doivent être idéalement de type integer (small, integer ou bigint selon le nombre d'occurrences maxi estimé), et de préférence attribués par le SGBD. Cette solution est la plus pérenne et la plus performante.

  11. #11
    Membre à l'essai Avatar de gautier1er
    Homme Profil pro
    Webmaster
    Inscrit en
    août 2017
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 23
    Localisation : Côte d'Ivoire

    Informations professionnelles :
    Activité : Webmaster

    Informations forums :
    Inscription : août 2017
    Messages : 30
    Points : 18
    Points
    18

    Par défaut

    Citation Envoyé par escartefigue Voir le message
    Ici il s'agit d'un héritage, donc les sous-type héritent de la PK du sur-type : nul besoin d'un identifiant dans les sous-type "courrier départ" et "courrier arrivée"
    j'ai fais une généralisation et non une spécialisation







    Citation Envoyé par escartefigue Voir le message
    De plus, vous avez défini des identifiants PK de type varchar, ce qu'il ne faut surtout pas faire, les identifiants PK doivent être idéalement de type integer (small, integer ou bigint selon le nombre d'occurrences maxi estimé), et de préférence attribués par le SGBD. Cette solution est la plus pérenne et la plus performante.
    voici mon idée
    le courrier arrivée ou départ doit avoir une réference tel : 2017-13 si le numéro d'ordre du courrier est 13 , parce que chaque année les numéros des courriers sont rémis a zéro en debut d'année pour commencer . on peut avoir un courrier numero 13 mais pour l'année 2018 donc la reference sera 2018-13.

    en plus les courriers departs et les courriers arrivées ne sont pas numérotés dans le meme ordre .

  12. #12
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 996
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 2 996
    Points : 6 582
    Points
    6 582
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par gautier1er Voir le message
    j'ai fais une généralisation et non une spécialisation
    L'un et l'autre vont de pair la généralisation concerne ce qui est commun à tous les courriers, c'est l'objet de la table mère "courrier" et la spécialisation concerne les deux autres tables.


    Citation Envoyé par gautier1er Voir le message
    le courrier arrivée ou départ doit avoir une réference tel : 2017-13 si le numéro d'ordre du courrier est 13 , parce que chaque année les numéros des courriers sont rémis a zéro en debut d'année pour commencer . on peut avoir un courrier numero 13 mais pour l'année 2018 donc la reference sera 2018-13.
    en plus les courriers departs et les courriers arrivées ne sont pas numérotés dans le meme ordre .
    Ces règles sont purement fonctionnelles, elles ne doivent aucunement piloter l'identifiant primaire de vos tables.
    Si vous avez besoin d'un identifiant fonctionnel construit à partir de la date et d'un numéro d'ordre, stockez simplement les composantes de cet identifiant fonctionnel dans des attributs non PK (une colonne année, une colonne n° d'ordre). Rien ne vous empêche de positionner une contrainte d'unicité sur un identifiant non PK
    Pour l'identifiant primaire, j'insiste, utilisez une colonne technique attribuée par le SGBD et surtout pas du (n)char et encore moins du (n)varchar !

  13. #13
    Modérateur
    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    15 063
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 15 063
    Points : 29 038
    Points
    29 038
    Billets dans le blog
    4

    Par défaut

    Pour compléter la réponse de escartefigue, lire ce billet de SQLPro.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  14. #14
    Membre à l'essai Avatar de gautier1er
    Homme Profil pro
    Webmaster
    Inscrit en
    août 2017
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 23
    Localisation : Côte d'Ivoire

    Informations professionnelles :
    Activité : Webmaster

    Informations forums :
    Inscription : août 2017
    Messages : 30
    Points : 18
    Points
    18

    Par défaut

    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    CREATE TABLE courrier
    (
    NumCour BIGINT(10)  NOT NULL AUTO_INCREMENT, 
    ObjetCour VARCHAR(255) NOT NULL,
    DateEcritureCour DATE NOT NULL, 
    annee  YEAR NOT NULL,
    MatAgent INT  NOT NULL ,
    CodeCoresp BIGINT(10) NOT NULL ,
    FOREIGN KEY(CodeCoresp) REFERENCES  correspondante(CodeCoresp)
    ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(MatAgent) REFERENCES  agent(MatAgent)
    ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (NumCour)
    )ENGINE=InnoDB;
     
     
     
    CREATE TABLE courrierDepart
    (
    NumCour BIGINT(10) NOT NULL,
    NumOrdreCourDep SMALLINT(5)  NOT NULL ,
    DateExpedCourDep DATE NOT NULL, 
    NumCourServDest CHAR(10) DEFAULT NULL, 
    NatureCourDep ENUM('ordinaire','special') NOT NULL,
     
     FOREIGN KEY (NumCour) REFERENCES courrier (NumCour)
     ON DELETE CASCADE ON UPDATE CASCADE, 
      PRIMARY KEY (NumCour),
       UNIQUE NumOrdreCourDep (NumOrdreCourDep),
     
     
     
    )ENGINE=InnoDB;
     
    CREATE TABLE courrierArrivee
    (
     NumCour BIGINT(10) NOT NULL,
    NumOrdreCourArriv SMALLINT(5)  NOT NULL ,
    DateArrivCourServCo DATE NOT NULL, 
    NumOrgCourArriv  CHAR(10) DEFAULT NULL, 
    DateRetCourServCo DATE DEFAULT NULL,
    DateRecepServDest DATE DEFAULT NULL,
    IdDirect TINYINT(3) NOT NULL,
     FOREIGN KEY (NumCour) REFERENCES courrier (NumCour)
     ON DELETE CASCADE ON UPDATE CASCADE, 
     FOREIGN KEY(IdDirect) REFERENCES direction(IdDirect)
    ON DELETE CASCADE ON UPDATE CASCADE,	 
     PRIMARY KEY (NumCour),
      UNIQUE NumOrdreCourArriv (NumOrdreCourArriv)
     
    )ENGINE=InnoDB;

    Je veux créer un index sur deux colonnes de tables différentes pour faire des recherches .
    L'utilisateur peut chercher un courrier à travers l'année (table mère) et le numéro d'ordre (table fille).
    C’est possible ?

  15. #15
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 996
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 2 996
    Points : 6 582
    Points
    6 582
    Billets dans le blog
    1

    Par défaut

    Il vous faut d'abord corriger votre DDL

    La contrainte suivante de la table COURRIER doit à mon avis être modifiée.
    FOREIGN KEY(CodeCoresp) REFERENCES correspondante(CodeCoresp) ON DELETE CASCADE ON UPDATE CASCADE,
    Car je pense que même si vous supprimez physiquement un correspondant (ce qui doit être assez rare) il faut conserver quand même le courrier qu'il a émis.
    Un courrier est une chose importante, il est très certainement nécessaire de le conserver en fonction d'autres paramètres fonctionnels (par exemple la durée légale ou réglementaire de conservation, ou le fait que ce courrier est une pièce d'un dossier non clôturé etc... )
    Les règles de conservation et de purge sont des règles de gestion, c'est donc à vos experts métier de vous les communiquer
    C'est un point extrêmement important : sauf erreur de ma part, le non respect de certaines règles de conservation des pièces règlementaires est d'ailleurs passible d'amendes et/ou de peines de prison, attention donc.

    La contrainte suivante dans la table COURRIER_DEPART est erronée :
    PRIMARY KEY (NumCour), UNIQUE NumOrdreCourDep (NumOrdreCourDep),
    En effet, vous créez une contrainte PK et donc unique, sur un chrono dont vous avez expliqué précédemment que la valeur est remise à zéro chaque année !
    Vos contraintes sur la table COURRIER_DEPART devraient donc être :
    CONSTRAINT COUR_DEPART_PK PRIMARY KEY (NumCour) CONSTRAINT COUR_DEPART_FK1 FOREIGN KEY (NumCour) REFERENCES COURRIER(NumCour) ON DELETE CASCADE ON UPDATE CASCADE CONSTRAINT COUR_DEPART_AK1 UNIQUE (Annee, NumOrdreCourDep )
    Il faut évidemment faire de même avec les courriers en arrivée
    Attention : la syntaxe du DDL varie en fonction des SGBD, à adapter donc à votre cas

    Ensuite, votre index doit porter sur des colonnes de la table, il faut donc que la colonne année soit présente dans les tables COURRIER_DEPART et COURRIER_ARRIVEE
    vous créerez les index correspondant à votre recherche, c'est à dire année+séquence, comme suit (à adpater là aussi selon le SGBD) :
    CREATE UNIQUE INDEX IX1_COUR_DEP ON COURRIER_DEPART (Annee ASC, NumOrdreCourDep ASC)

  16. #16
    Membre à l'essai Avatar de gautier1er
    Homme Profil pro
    Webmaster
    Inscrit en
    août 2017
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 23
    Localisation : Côte d'Ivoire

    Informations professionnelles :
    Activité : Webmaster

    Informations forums :
    Inscription : août 2017
    Messages : 30
    Points : 18
    Points
    18

    Par défaut

    Merci
    Voici une fonction qui génère le numéro d’ordre du courrier départ
    Code php : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
     
          function    referenceCourrierDepart()
           {
                $bdd=connection_db();
             //on commpte le nombre d'enregistrement dans les courriers depart de l'année en cours
           $req=$bdd->prepare('SELECT COUNT(NumOrdreCourDep)
           FROM  courrierDepart
           WHERE  AnneeCD=?');
           $req->execute(array(date('Y')) );      //l'année en cours
             $nbreLigne=$req->rowCount();
             if($nbreLigne==0)
             {
               //(s'il n'ya pas de courrier départ enregistré dans l'année en cours ) alors le n° d'ordre du courrier aura la valeur 1
              $id=1;
             }
             else{
               //dans le cas contraire  on recupère le dernier n°d'ordre du courrier départ
              $id=$nbreLigne+1;
     
              }
     
     
     
               return $id;
           }

  17. #17
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    17 332
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 17 332
    Points : 40 139
    Points
    40 139
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par gautier1er Voir le message
    Merci
    Voici une fonction qui génère le numéro d’ordre du courrier départ
    Code php : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
          function    referenceCourrierDepart()...
    Votre fonction est stupide car elle ne supporte pas les accès concurrents. En effet, si deux utilisateurs lancent cette même fonction au même moment, vous obtiendrez le même numéro.

    Si vous voulez être sûr d'obtenir un numéro unique, il faut écrire une procédure stockée qui calcule le n° en verrouillant de manière exclusive tout accès à la table. Comme un verrouillage exclusif total de la table est la pire des choses à faire, on utilise un compteur externe à la table (SEQUENCE par exemple) ou interne (IDENTITY), ces deux mécansimes étant normalisés.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  18. #18
    Membre à l'essai Avatar de gautier1er
    Homme Profil pro
    Webmaster
    Inscrit en
    août 2017
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 23
    Localisation : Côte d'Ivoire

    Informations professionnelles :
    Activité : Webmaster

    Informations forums :
    Inscription : août 2017
    Messages : 30
    Points : 18
    Points
    18

    Par défaut

    Je vais utiliser une procédure stockée pour calculer le numéro d’ordre,mais j’aimerai savoir aussi , pour les requêtes d’insertion , de suppression et de modification dans les tables : courrier , courrier_depart et courrier_arrivee , j’utilise aussi les procédures stockées ?

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Quelques Requêtes SQL à vérifier
    Par safowan dans le forum Requêtes
    Réponses: 0
    Dernier message: 12/12/2016, 22h36
  2. Quelques requêtes sql
    Par sqlman dans le forum Langage SQL
    Réponses: 12
    Dernier message: 28/04/2011, 12h13
  3. Requête sql concernant des dates
    Par webtheque dans le forum Langage SQL
    Réponses: 25
    Dernier message: 29/08/2008, 21h20
  4. petit probleme dans une requte POSTGRE SQL
    Par ghis le fou dans le forum Requêtes
    Réponses: 5
    Dernier message: 08/09/2003, 14h51

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo