IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Schéma Discussion :

Valider mon schéma


Sujet :

Schéma

  1. #1
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 922
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 922
    Points : 51 717
    Points
    51 717
    Billets dans le blog
    6
    Par défaut Valider mon schéma
    Bonjour,

    comme je suis en train de récrire mon livre sur le SQL et qu'il sera disponible gratuitement sous forme PDF, j'entreprends la mise en place d'une nouvelle base de données pour les exemples et les tests.
    Outre la partie triviale qui concerne les personnes (physiques ou morale, avec téléphones, emails et adresses) que je ne présenterais pas ici, voici la partie 2 qui concerne les employés. C'est un modèle très générique dans le cadre d'une entreprise de transport (de nom LOGISTYX, histoire de traverser le fleuve mythologique...) pour laquelle nous avons besoin d'une certaines finesse sur le recueil des informations de permis de conduire...

    Je vous en donne le graphique, n'hésitez pas à me demander de plus amples informations...

    Nom : 02 employés 2022-12-30.gif
Affichages : 496
Taille : 110,1 Ko

    Pour informations j'ai au moins deux autres parties en analyse : la gestion des colis dans les entrepôts et la gestion des tournées de livraisons ou ramassage....

    A +

  2. #2
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 120
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 120
    Points : 31 629
    Points
    31 629
    Billets dans le blog
    16
    Par défaut
    Salut Fred,

    Je ne sais pas trop ce que tu attends de nous en termes de validation, mais voici en tout cas quelques observations.


    Règles de gestion :

    En leur absence, difficile d’interpréter en toute objectivité ton MCD, mais je suppose qu’elles seront présentes dans la nouvelle édition de ton ouvrage.


    Pour ne pas alourdir, pour nous faciliter la lecture et éviter que l’on se pose plein de questions, dans le MCD préférer nommer les attributs de manière naturelle et simple plutôt que technique et rébarbative. Par exemple, je ne sais pas ce que veut dire EMP_NNI_DN, mais à supposer qu’il s’agisse du nom du département, rien n’empêche qu’on nomme cet attribut Département dans le MCD et EMP_NNI_DN dans le MLD ainsi que dans le code SQL. A titre indicatif, Looping permet très simplement cela au stade MCD :

    Nom : sqlpro(mcd personnes)rubrique_nom_logique.png
Affichages : 435
Taille : 2,3 Ko

    Redondances :  

    Prenons par exemple le cas de l’entité-type (classe d’entité) Service : on apprend que SRV_ID est identifiant, mais on l’apprend aussi de manière redondante : PK_SRV est certes clé primaire, mais inutile de le faire figurer dans le MCD, le MLD est là pour ça. Même chose pour les identifiants alternatifs (clé UK_SRV_CODE).

    Pour ma part, j’ai laissé tomber PowerAMC et les autres, et suis passé à Looping, gratuit, très convivial, et en plus, on peut débattre avec son très sympathique papa, Paprick, ce que j’apprécie beaucoup.

    Exemple d’ébauche de ma part avec Looping :
    Nom : sqlpro(mcd personnes)mcd_loo.png
Affichages : 456
Taille : 11,5 Ko

    Nom : sqlpro(mcd personnes)mld_loo.png
Affichages : 462
Taille : 13,2 Ko

    Et bon réveillon !

  3. #3
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 120
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 120
    Points : 31 629
    Points
    31 629
    Billets dans le blog
    16
    Par défaut
    Fred,

    Suite à ce que j’ai écrit, j’ai repris ton ouvrage, SQL Server 2014, soigneusement rangé dans ma bibliothèque et j’ai relu quelques pages choisies. Je me rends compte que nous ne n’adoptons ni ne recommandons les mêmes conventions de nommage, mais peu importe, autant de concepteurs, autant de recommandations. Notre but est quand même bien que nous n’induisions pas les utilisateurs en erreur et que leurs bases de données soient valides.

    Encore bon réveillon,Nom : fetard75.png
Affichages : 424
Taille : 2,7 Ko

    François

  4. #4
    Membre émérite
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    712
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Professeur des Universités
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2019
    Messages : 712
    Points : 2 877
    Points
    2 877
    Par défaut
    Bonjour, et tous mes vœux pour cette nouvelle année !

    Sans surprise, j'irai dans le sens de François ! Et pas seulement parce que ses préconisations sont excellentes en terme de logiciel de modélisation !
    En effet, je pense qu'au niveau MCD, le modèle doit être aisément lisible, y compris par des non-spécialistes des BD.
    Le modèle tel que tu le proposes est parfait au niveau MLD, mais mériterait d'être plus parlant au niveau conceptuel.
    Pour cela, distinguer les noms conceptuels et les noms logiques, tel que proposé par François, me parait être le bon chemin à suivre.

    Il est vrai que Power-AMC est une référence incontournable mais, au niveau MCD, je lui préfère largement des outils tels que Win'Design... ou Looping (dont je ne me permettrais pas de vanter les qualités !), en particulier par leur capacité à représenter les CIF et autres contraintes inter-associations.

  5. #5
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 922
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 922
    Points : 51 717
    Points
    51 717
    Billets dans le blog
    6
    Par défaut
    Oh les enfants.... bonne année !

    NNI : Numéro National d'Identité

    Le schéma 01 (non présenté) montre une entité Personne dont les héritiers sont : "personne morale", "personne physique".

    Au niveau des règles de gestion, que du trivial sur cette partie...

    Un employé est affecté successivement à différents postes, voir au même poste avec des conditions différentes
    Je m'aperçoit, d'ailleurs qu'il manque la salaire de référence au niveau de l'entité "poste". L'attribut base salaire an" ne devrait pas figurer au niveau du contrat

    Au niveau des permis, certains permis (poids lourd, car...) nécessite des validations régulières

    Donc rien de bien folichon....

    Au niveau de l'outil de modélisation... WinDesign, génial, mais trop de bugs... j'avais écrit à Dominique Nancy.... mais bon... j'ai perdu plusieurs fois mon travail alors poubelle.

    Sans vouloir te vexer Paprick, il manque encore beaucoup de choses à Looping pour avoir des fonctionnalités équivalente à Power Designer... Ne serait ce que la validation des modèles (vérification de cycles par exemple) ou la création automatique des déclencheurs d'exclusion pour les héritages...

    A +

  6. #6
    Membre émérite
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    712
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Professeur des Universités
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2019
    Messages : 712
    Points : 2 877
    Points
    2 877
    Par défaut
    Bonsoir,
    Citation Envoyé par SQLpro Voir le message
    Sans vouloir te vexer Paprick, il manque encore beaucoup de choses à Looping pour avoir des fonctionnalités équivalente à Power Designer... Ne serait ce que la validation des modèles (vérification de cycles par exemple) ou la création automatique des déclencheurs d'exclusion pour les héritages...
    Rassure-toi, ça ne me vexe pas du tout ! D'autant plus que ces manques sont, pour certains, des choix assumés : par exemple, les déclencheurs d'exclusion, tout comme la gestion des contraintes d'inclusion si chère à notre ami François , ainsi que toutes les contraintes inter-associations, sont toutes proposées graphiquement au niveau représentation conceptuelle (contrairement d'ailleurs à Power Designer, ce que je ne comprends d'ailleurs pas vu la qualité et l'expérience du produit...), mais, c'est vrai, pas au niveau de la génération DDL. J'ai pour le moment jugé (peut-être à tord, je l'admets) que la génération du code correspondant dépendait très souvent du SGBD visé, et surtout d'une certaine vision du DBA : c'est pourquoi Looping se contente de proposer une zone spécifique de saisie libre de code SQL pour régler ces contraintes (y compris pour les déclencheurs d'exclusion pour les héritages : d'ailleurs, si tu as une démarche formelle de génération standard SQL pour traiter ces contraintes, je suis preneur !).
    Par ailleurs, je reconnais ne pas bien maitriser Power Designer, mais je n'ai pas trouvé comment gérer graphiquement les CIF... Certes on peut gérer les contraintes correspondantes de manière à obtenir le bon MLD, mais je n'ai rien trouvé pour avoir la représentation graphique qui va avec, ce qui est pour moi un vrai problème si l'on souhaite que le MCD exprime correctement le SI. Peux-tu éclairer ma lanterne sur ce point, ainsi que sur les représentations graphiques des autres contraintes, y compris les exclusions inter-associations hors héritage ?
    Merci !

  7. #7
    Membre émérite
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    712
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Professeur des Universités
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2019
    Messages : 712
    Points : 2 877
    Points
    2 877
    Par défaut
    Autre question : qu'entends-tu pas "vérification de cycles" ?

  8. #8
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 922
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 922
    Points : 51 717
    Points
    51 717
    Billets dans le blog
    6
    Par défaut
    cycle :
    facture avec ref à client et client avec ref à facture.... Mais cela peut être beaucoup plus long... du genre client --> commande --> bon de livraison --> facture --> --> paiement --> client...
    (appelées circularité dans Power Designer)

  9. #9
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 922
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 922
    Points : 51 717
    Points
    51 717
    Billets dans le blog
    6
    Par défaut
    Citation Envoyé par Paprick Voir le message
    Bonsoir,

    Rassure-toi, ça ne me vexe pas du tout ! D'autant plus que ces manques sont, pour certains, des choix assumés : par exemple, les déclencheurs d'exclusion, tout comme la gestion des contraintes d'inclusion si chère à notre ami François , ainsi que toutes les contraintes inter-associations, sont toutes proposées graphiquement au niveau représentation conceptuelle (contrairement d'ailleurs à Power Designer, ce que je ne comprends d'ailleurs pas vu la qualité et l'expérience du produit...)
    tu peux les mettre aussi, mais c'est juste graphique... icône "lien de dépendance étendu"

    , mais, c'est vrai, pas au niveau de la génération DDL. J'ai pour le moment jugé (peut-être à tord, je l'admets) que la génération du code correspondant dépendait très souvent du SGBD visé
    oui
    , et surtout d'une certaine vision du DBA
    bof...
    : c'est pourquoi Looping se contente de proposer une zone spécifique de saisie libre de code SQL pour régler ces contraintes (y compris pour les déclencheurs d'exclusion pour les héritages : d'ailleurs, si tu as une démarche formelle de génération standard SQL pour traiter ces contraintes, je suis preneur !).
    Par ailleurs, je reconnais ne pas bien maitriser Power Designer, mais je n'ai pas trouvé comment gérer graphiquement les CIF... Certes on peut gérer les contraintes correspondantes de manière à obtenir le bon MLD, mais je n'ai rien trouvé pour avoir la représentation graphique qui va avec, ce qui est pour moi un vrai problème si l'on souhaite que le MCD exprime correctement le SI. Peux-tu éclairer ma lanterne sur ce point, ainsi que sur les représentations graphiques des autres contraintes, y compris les exclusions inter-associations hors héritage ?
    Merci !
    Donc lien de dépendance étendu...

    A +

  10. #10
    Membre émérite
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    712
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Professeur des Universités
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2019
    Messages : 712
    Points : 2 877
    Points
    2 877
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    cycle :
    facture avec ref à client et client avec ref à facture.... Mais cela peut être beaucoup plus long... du genre client --> commande --> bon de livraison --> facture --> --> paiement --> client...
    (appelées circularité dans Power Designer)
    C'est bien ce que à quoi je pensais, mais je n'étais pas sûr... En fait Looping le gère très bien et signale en temps réel des "Références circulaires" dans la partie MLD textuel (et refuse d'afficher le DDL).

  11. #11
    Membre émérite
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    712
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Professeur des Universités
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2019
    Messages : 712
    Points : 2 877
    Points
    2 877
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    tu peux les mettre aussi, mais c'est juste graphique... icône "lien de dépendance étendu"
    Ok, je viens de voir ça... par contre ce n'est vraiment que graphique, et je n'ai pas trouvé comment mettre en correspondance une contrainte graphique donnée avec son code...
    Mais, ce que je trouve dommage, c'est la non prise en compte spécifique des CIFs définies graphiquement au niveau MCD avec déduction automatique des clés dans le MLD.
    C'est encore plus vrai pour les CIF à unicité incomplète susceptibles de générer automatiquement des clés alternatives.
    C'est un point sur lequel, en particulier grâce à l'exigence et l'obstination de François, Looping est devenu parfaitement incollable (surtout dans la future version 4.1 à venir), contrairement à des outils qui ne proposent rien (même Win'Design qui le traite graphiquement, ne propose pas de clés alternatives, et ne génère pas le bon MLD dès que ça se complique...).
    Quoiqu'il en soit, Looping n'a que 4 ans d'existence, et ne demande qu'à s'améliorer .

  12. #12
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 922
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 922
    Points : 51 717
    Points
    51 717
    Billets dans le blog
    6
    Par défaut
    Citation Envoyé par Paprick Voir le message
    Ok, je viens de voir ça... par contre ce n'est vraiment que graphique, et je n'ai pas trouvé comment mettre en correspondance une contrainte graphique donnée avec son code...
    Mais, ce que je trouve dommage, c'est la non prise en compte spécifique des CIFs définies graphiquement au niveau MCD avec déduction automatique des clés dans le MLD.
    C'est encore plus vrai pour les CIF à unicité incomplète susceptibles de générer automatiquement des clés alternatives.
    C'est un point sur lequel, en particulier grâce à l'exigence et l'obstination de François, Looping est devenu parfaitement incollable (surtout dans la future version 4.1 à venir), contrairement à des outils qui ne proposent rien (même Win'Design qui le traite graphiquement, ne propose pas de clés alternatives, et ne génère pas le bon MLD dès que ça se complique...).
    Quoiqu'il en soit, Looping n'a que 4 ans d'existence, et ne demande qu'à s'améliorer .
    Pour le problème des déclencheurs, il n'existe aucun code commun entre PostGreSQL MySQL et SQL Server ni Oracle. Je peux te donner le code de triggers d'exclusion au moins pour l'héritage pour MS SQL Server et tenterais de te donner la chose pour PostGreSQL....

    En fait c'est assez simple... En syntaxe Backus Naur :

    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
    CREATE TRIGGER nom_trigger
    ON <schema_table_cible>.<table_cible>
    FOR INSERT, UPDATE
    AS
    IF NOT UPDATE(<colonne_clé>)
       RETURN;
    IF EXISTS(SELECT *
              FROM   <schema_table_1_exclusion>.<table_1_exclusion>
              WHERE  <colonne_clé> IN (SELECT <colonne_clé>
                                       FROM  inserted)
    
    [         UNION ALL
              SELECT *
              FROM   <schema_table_2_exclusion>.<table_2_exclusion>
              WHERE  <colonne_clé> IN (SELECT <colonne_clé>
                                       FROM  inserted)
    [ ... ]
    ]
             )
    BEGIN
       ROLLBACK;
       RAISERROR('Exclusion mutuelle table %s : au moins une des tables enfant (%s) possède la même clé que celle que vous tentez d''insérer.', 16, 1, 
                 '<schema_table_cible>.<table_cible>', 
                 '<schema_table_1_exclusion>.<table_1_exclusion>[, <schema_table_2_exclusion>.<table_2_exclusion> [, ... ] ]');

  13. #13
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 120
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 120
    Points : 31 629
    Points
    31 629
    Billets dans le blog
    16
    Par défaut
    Ave,  

    Citation Envoyé par SQLpro
    Pour le problème des déclencheurs, il n'existe aucun code commun entre PostGreSQL MySQL et SQL Server ni Oracle. Je peux te donner le code de triggers d'exclusion au moins pour l'héritage pour MS SQL Server et tenterais de te donner la chose pour PostGreSQL....
    De mon côté, j’avais surtout programmé des triggers dans le contexte DB2 puisque c’est le SGBD que j’ai toujours utilisé quand j‘étais actif. Ces p... de triggers ne m’ont jamais emballé, car on y programme (on quitte le What pour le How) en tenant compte de ce foutu How dans le genre : pour chaque table impliquée, je dois prévoir la programmation à mettre en oeuvre en fonction des opérations qu’elle peut subir : INSERT, UPDATE,, DELETE, et si c’est à effectuer avant ou après l’exécution de l’opération.
    Personnellement, programmer ne me fait pas peur, j'ai développé des grosses applications en assembleur pendant 40 ans.
    Depuis que j’ai des loisirs, j’ai découvert Developpez.com et me suis mis à aider comme je le pouvais, aussi ai-je été amené, bon gré, mal gré, à proposer des triggers, et surtout, le plus souvent possible, des solutions sans triggers. Comme je me tue à le répéter, pourquoi ne pas pouvoir profiter de l’instruction CREATE ASSERTION ? Les choses seraient tellement plus simples ! Chez les éditeurs de SGBD et dans les colloques, les frileux et les faux-culs expliquent en long et en large que cette instruction coûterait la feau des pesses, mais ce sont les mêmes qui, fin des années quatre-vingts, nous expliquaient que l’intégrité référentielle serait épouvantable quant à la performance des requêtes, donc à laisser aux développeurs... J’ai été un des tous premiers en France, sinon le premier à la mettre en oeuvre (en 1988), et en bon DBA je l’ai secouée puis mise en oeuvre à Clermont-Ferrand pour l’application hyper sensible Prise de commandes, et ce fut une réussite. J’aimerais tant pouvoir disposer de cette instruction CREATE ASSERTION, et montrer qu’un bon DBA saurait l’utiliser sans provoquer de problème de performance. Même chose pour la clause CHECK de l’instruction CREATE TABLE, ne permettant que de mettre en oeuvre les contrôles les plus basiques du genre « Le salaire doit être au moins supérieur à 15000 euros »  

    Si dans le CREATE TABLE je veux coder un CHECK comportant un NOT EXISTS ou équivalent, je me fais renvoyer sans douceur dans mes vingt-deux :

    Citation Envoyé par SQL Server
    Msg 1046, Niveau 15, État 1
    Les sous-requêtes ne sont pas autorisées dans ce contexte. Seules sont permises les expressions scalaires.
    De la tartuferie pudique, m’empêchant de faire du relationnel...  

    Quant à éviter les triggers, j’ai déterré une discussion (cf. le post #9 d’une discussion ouverte par le Capitaine) où je le fais pour une contrainte d’exclusion. L’élégance du code laisse à désirer, mais ça fonctionne...

    Etant donné que le MCD fourni par le Capitaine semble avoir disparu, je le fournis ici (s’il veut bien s’afficher).

    Nom : exclusion_par_Escartefigue.png
Affichages : 582
Taille : 37,7 Ko

  14. #14
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 922
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 922
    Points : 51 717
    Points
    51 717
    Billets dans le blog
    6
    Par défaut
    Citation Envoyé par fsmrel Voir le message
    Ave,  



    De mon côté, j’avais surtout programmé des triggers dans le contexte DB2 puisque c’est le SGBD que j’ai toujours utilisé quand j‘étais actif. Ces p... de triggers ne m’ont jamais emballé, car on y programme (on quitte le What pour le How) en tenant compte de ce foutu How dans le genre : pour chaque table impliquée, je dois prévoir la programmation à mettre en oeuvre en fonction des opérations qu’elle peut subir : INSERT, UPDATE,, DELETE, et si c’est à effectuer avant ou après l’exécution de l’opération.
    Personnellement, programmer ne me fait pas peur, j'ai développé des grosses applications en assembleur pendant 40 ans.
    Depuis que j’ai des loisirs, j’ai découvert Developpez.com et me suis mis à aider comme je le pouvais, aussi ai-je été amené, bon gré, mal gré, à proposer des triggers, et surtout, le plus souvent possible, des solutions sans triggers. Comme je me tue à le répéter, pourquoi ne pas pouvoir profiter de l’instruction CREATE ASSERTION ? Les choses seraient tellement plus simples ! Chez les éditeurs de SGBD et dans les colloques, les frileux et les faux-culs expliquent en long et en large que cette instruction coûterait la feau des pesses, mais ce sont les mêmes qui, fin des années quatre-vingts, nous expliquaient que l’intégrité référentielle serait épouvantable quant à la performance des requêtes, donc à laisser aux développeurs... J’ai été un des tous premiers en France, sinon le premier à la mettre en oeuvre (en 1988), et en bon DBA je l’ai secouée puis mise en oeuvre à Clermont-Ferrand pour l’application hyper sensible Prise de commandes, et ce fut une réussite.
    pour info le seul SGBDR, qui, à ma connaissance implémentait les assertions était RDB de Digital.

    Si les assertions sont intellectuellement très satisfaisante elles posent d'énormes problèmes de performances....

    Voici ce que je dis dans mon nouvel ouvrage à ce sujet :

    "
    3.5 Les assertions
    Les assertions sont des contraintes dont l'étendue porte sur la base entière notamment pour permettre des règles de validation entre différentes colonnes de différentes tables ou vues à l'aide de prédicats. Les assertions au sens de la norme SQL sont donc des objets de la base de données.
    Pour créer une assertion, il faut utiliser la commande CREATE ASSERTION et utiliser une contrainte de validation CHECK.

    La syntaxe de création d'une assertion est la suivante :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    CREATE ASSERTION [nom_schéma.]nom_assertion
    CHECK ( predicat )
    [ <attribut_assertion> ]
    
    <attribut_assertion> ::
      {INITIALLY DEFERRED | INITIALLY IMMEDIATE} [ [ NOT ] DEFERRABLE ]
    | [NOT] DEFERRABLE [INITIALLY DEFERRED | INITIALLY IMMEDIATE]
    Les règles de déferrabilité, introduites par la clause <attribut_assertion> seront discutées au paragraphe suivant.

    Nous prendrons comme exemple une unicité de clé devant porter sur deux tables distinctes, ce qui est le cas dans tous les héritages « exclusif » :
    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
    Exemple 3.27* – Tables en héritage exclusif nécessitant une assertion d’exclusion
    CREATE TABLE S_PRS.T_PERSONNE_GENERIQUE_PSG
    (PSG_ID              INTEGER PRIMARY KEY,
     PSG_DATE_CREATION   DATE NOT NULL);
    
    CREATE TABLE S_PRS.T_PERSONNE_PHYSIQUE_PSP
    (PSG_ID              INTEGER PRIMARY KEY 
        REFERENCES S_PRS.T_PERSONNE_GENERIQUE_PSG(PSG_ID),
     PSP_NOM             VARCHAR(32) NOT NULL,
     PSP_PRENOM          VARCHAR(25),
     PSP_DATE_NAISSANCE  DATE);
    
    CREATE TABLE S_PRS.T_PERSONNE_MORALE_PSM
    (PSG_ID              INTEGER PRIMARY KEY 
        REFERENCES S_PRS.T_PERSONNE_GENERIQUE_PSG(PSG_ID),
     PSM_RAISON_SOCIALE  VARCHAR(256) NOT NULL,
     PSM_ENSEIGNE        VARCHAR(64),
     PSM_SIRET           CHAR(14) NOT NULL UNIQUE);
    Dans ce type de modélisation, les personnes morales et physiques héritent des caractéristiques d’une personne générique. Au niveau inférieur, il faut dissocier les PRS_ID en deux groupes, les valeurs affectées aux personnes physiques ne devant en aucun cas se retrouver dans la table des personnes morales et vice-versa.

    Dans un tel cas on recoure à une assertion comme celle-ci :
    Exemple 3.28* – Assertion d’exclusion pour le cas d’un héritage mutuellement exclusif
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    CREATE ASSERTION A_UNIQUE_ID_PRS
    CHECK 
       (NOT EXISTS 
          (SELECT PRS_ID
           FROM   S_PRS.T_PERSONNE_PHYSIQUE_PSP 
           INTERSECT
           SELECT PRS_ID
           FROM   S_PRS.T_PERSONNE_MORALE_PSM);
    La sous requête SELECT de la clause CHECK qui forme le prédicat de l'assertion, met en correspondance les tables S_PRS.T_PERSONNE_PHYSIQUE_PSP et S_PRS.T_PERSONNE_MORALE_PSM en faisant l’intersection des ensembles formée de la clé de chacune des tables. Dans le cas présent, afin d'assurer l'unicité de la clé à travers les deux tables, il ne faut pas qu'une personne physique porte le même numéro de clé qu'une personne morale. En aucun cas, la requête de doit retourne un résultat. Cela est assuré à l'aide de l'expression EXISTS qui renvoit vrai au cas où un résultat est retourné et faux dans le cas où la requête ne retourne aucune ligne.

    L'assertion est vérifiée chaque fois que l’un des deux tables fait l’objet d’une mise à jour (INSERT, UPDATE, DELETE…) et la contrainte valide la commande si le prédicat CHECK vaut vrai, donc si la requête d'extraction ne produit jamais aucun résultat. Dans le cas contraire le viol de la contrainte intervient et l'ordre SQL d'insertion ou de modification est empêché qu'il porte sur l'une ou l'autre table.
    Dans le cas de l’exemple 3.29, toute suppression de ligne d’une des deux tables filles ne violera jamais cette contrainte, mais par sa nature, l’assertion impose que cette vérification soit faite même en cas de suppression, ce qui est un travail inutile…

    Une contrainte de table ne suffit pas à exprimer une telle règle de validation. Seul l'usage de l'assertion (contrainte propre à la base) le permet.

    NOTE
    Compte tenu de la complexité de mise en œuvre de la vérification des assertions et de leur coût exorbitant, la plupart des éditeurs de SGBD relationnel ont préféré ne pas implémenter à la lettre ce genre de contraintes, mais de leur substituer la notion de déclencheur (trigger) que nous verrons au chapitre 7
    "

    Dans l'exemple cité il y a plusieurs problématiques :
    1) inutile de vérifier l'assertion pour le DELETE
    2) inutile de vérifier l'assertion pour toutes les lignes de deux tables (INSERT, UPDATE)
    3) inutile de vérifier l'assertion si la clé n'est pas modifiée (UPDATE)

    C'est pourquoi les éditeurs de SGBDR ont renoncé à cela... Comme vles vues "misajourabl" en théorie de Codd....



    J’aimerais tant pouvoir disposer de cette instruction CREATE ASSERTION, et montrer qu’un bon DBA saurait l’utiliser sans provoquer de problème de performance. Même chose pour la clause CHECK de l’instruction CREATE TABLE, ne permettant que de mettre en oeuvre les contrôles les plus basiques du genre « Le salaire doit être au moins supérieur à 15000 euros »  

    Si dans le CREATE TABLE je veux coder un CHECK comportant un NOT EXISTS ou équivalent, je me fais renvoyer sans douceur dans mes vingt-deux :


    De la tartuferie pudique, m’empêchant de faire du relationnel...  
    Je serais très curieux de voir ton code car les déclencheurs SQL Sever sont purement ensemblistes... AMHA tu dois le coder mal !



    Quant à éviter les triggers, j’ai déterré une discussion (cf. le post #9 d’une discussion ouverte par le Capitaine) où je le fais pour une contrainte d’exclusion. L’élégance du code laisse à désirer, mais ça fonctionne...

    Etant donné que le MCD fourni par le Capitaine semble avoir disparu, je le fournis ici (s’il veut bien s’afficher).

    Nom : exclusion_par_Escartefigue.png
Affichages : 582
Taille : 37,7 Ko
    Sauf que les contraintes d'exclusion ça n'existe pas en SQL...

    Et pour ce qui concerne les contraintes de tables qui scrute d'autres lignes ou d'autre table, même punition !

    A +

  15. #15
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 388
    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 : 10 388
    Points : 39 901
    Points
    39 901
    Billets dans le blog
    9
    Par défaut
    Concernant ce point particulier
    Citation Envoyé par fsmrel Voir le message
    Etant donné que le MCD fourni par le Capitaine semble avoir disparu, je le fournis ici (s’il veut bien s’afficher).
    J'ai fait du ménage il y a quelques mois dans mes pièces jointes, pensant bien faire (à la suite de messages m'indiquant que j'en avais un trop grand nombre).
    Mal m'en a pris ! J'ai découvert à mes dépens que ces PJ étaient ce faisant supprimées des messages y faisant référence (et donc une grande partie de mes vieilles réponses). Je croyais qu'elles y étaient dupliquées, il n'en est rien.
    Si j'arrive à remettre la main sur la clef USB contenant une grande partie des MCD des sujets de "developpez.net" je pourrai arranger ça, au moins en partie , cette clef est trop bien rangée quelque part, là, dans mon m€®Ð¦€®

  16. #16
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 120
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 8 120
    Points : 31 629
    Points
    31 629
    Billets dans le blog
    16
    Par défaut
    Salve,

    Citation Envoyé par SQLpro
    pour info le seul SGBDR, qui, à ma connaissance implémentait les assertions était RDB de Digital.
    Que devient RDB ?

    Citation Envoyé par SQLpro
    Je serais très curieux de voir ton code car les déclencheurs SQL Sever sont purement ensemblistes... AMHA tu dois le coder mal 
    Je sais quand même coder un déclencheur, j’en ai bien sûr codé un paquet, ne serait-ce que pour aider les développeurs chez DVP.
    Par curiosité, j’ai voulu voir la réaction de SQL Server en lui proposant une requête d’esprit ensembliste et évidemment il m’a renvoyé dans mes vingt-deux :

    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
    CREATE TABLE PSN_CAT 
    (
        PersonneId INT NOT NULL
      , CategorieId INT NOT NULL
      , PermisDate DATE NOT NULL
      , PRIMARY KEY (PersonneId, CategorieId)
    );
    CREATE TABLE CONDUIRE 
    (
        PersonneId INT NOT NULL
      , VehiculeId INT NOT NULL
      , PRIMARY KEY (PersonneId, VehiculeId)
    );
    
    ALTER TABLE CONDUIRE 
    ADD CONSTRAINT inclusion_contrainte CHECK 
      (PersonneId IN (SELECT PersonneId FROM PSN_CAT))
    Je tente une infraction :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    INSERT INTO CONDUIRE  VALUES (2, 4) ;
    =>
    Citation Envoyé par SQL Server
    Msg 1046, Niveau 15, État 1
    Les sous-requêtes ne sont pas autorisées dans ce contexte. Seules sont permises les expressions scalaires.
    C’est quand même très réducteur ! Techniquement, qu’est-ce qui empêche SQL Server de traiter une telle contrainte (de nature ensembliste) sans que la performance en souffre ? Dans le cas d’INSERT/UPDATE, il y a les bons index, dans le cas de DELETE, le SGBD doit être capable de comprendre que la contrainte n’a pas besoin d’être vérifiée.

    Cela dit, au moins pour les contraintes de partitionnement, (par exemple personnes physiques vs personnes morales), contraint (sic) et forcé, je continuerai à utiliser des triggers, bon gré, mal gré...

    En passant, dans ton assertion, tu fais mention de l’attribut PRS_ID, absent dans les CREATE TABLE. Pardonne-moi, j’ai relevé quelques fautes d’orthographe, mais je suppose que tu auras des relecteurs.

  17. #17
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 388
    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 : 10 388
    Points : 39 901
    Points
    39 901
    Billets dans le blog
    9
    Par défaut
    bonjour,

    pour en revenir au sujet de base, j'ai quelques remarques :

    Les salaires sont liés au poste, mais pas à l'employé, tous les employés d'un même poste touchent donc le même salaire ?
    Ça semble très restrictif, comment gère-t-on en ce cas les temps partiels, l'ancienneté, les primes...

    Le poste associé à un et un seul employé ne permet pas de gérer les équipes 2/8, 3/8 où plusieurs employés se relayent à un même poste.
    À adapter dans certains contextes.

    Le modèle ne permet pas de connaître l'historique d'affectation des employés aux postes, alors qu'une asso ternaire employé/poste/date le permettrait.

    Concernant la validation des permis, il serait bénéfique de l'identifier relativement au permis (présence de parenthèses autour des cardinalités pour power-AMC) : avec un index cluster sur identifiant permis + chrono validation, on aura des perfs optimales

    @François : j'ai rétabli le MCD dans la réponse du fil de discussion incriminé, il me reste beaucoup de travail pour faire de même dans les autres réponses de ce fil et dans les autres sujets. Je doute d'avoir le courage de le faire...

  18. #18
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 388
    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 : 10 388
    Points : 39 901
    Points
    39 901
    Billets dans le blog
    9
    Par défaut
    Quelques points supplémentaires

    Associer le poste à la personne morale caractérisée par un SIRET est parfois imprécis : le SIRET, c'est l'établissement, or, dans l'industrie, certains établissements couvrent plusieurs hectares (ex : le site Peugeot Sochaux à la belle époque). Il est probable que le gestionnaire souhaitera plus précisément savoir que le poste est localisé dans tel bâtiment, telle aile, peut-être même telle travée.

    Il me semble que déduire des informations unitaires à partir du NNI (code sexe, année et mois de naissance...) n'est pas autorisé par la CNIL, mais je n'ai pas retrouvé les textes le mentionnant. À vérifier.
    De plus, il existe quelques cas, très marginaux, de changements de sexe (transsexuels ayant changé de civilité). Or, le NNI n'est que très rarement modifié dans ce cas précis.

  19. #19
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 922
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 922
    Points : 51 717
    Points
    51 717
    Billets dans le blog
    6
    Par défaut
    Citation Envoyé par fsmrel Voir le message
    ...

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    ...
    ALTER TABLE CONDUIRE 
    ADD CONSTRAINT inclusion_contrainte CHECK 
      (PersonneId IN (SELECT PersonneId FROM PSN_CAT))
    C'est ça qui merde... Pas ton INSERT !

    Double clique sur le message d'erreur !

    Donc à faire en déclencheur...

    A +

  20. #20
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 922
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 922
    Points : 51 717
    Points
    51 717
    Billets dans le blog
    6
    Par défaut
    Citation Envoyé par escartefigue Voir le message
    Quelques points supplémentaires

    Associer le poste à la personne morale caractérisée par un SIRET est parfois imprécis : le SIRET, c'est l'établissement, or, dans l'industrie, certains établissements couvrent plusieurs hectares (ex : le site Peugeot Sochaux à la belle époque). Il est probable que le gestionnaire souhaitera plus précisément savoir que le poste est localisé dans tel bâtiment, telle aile, peut-être même telle travée.
    Regarde les cardinalité. Un poste est afférent à une combinaison personne / fonction / ...


    Il me semble que déduire des informations unitaires à partir du NNI (code sexe, année et mois de naissance...) n'est pas autorisé par la CNIL, mais je n'ai pas retrouvé les textes le mentionnant. À vérifier.
    De plus, il existe quelques cas, très marginaux, de changements de sexe (transsexuels ayant changé de civilité). Or, le NNI n'est que très rarement modifié dans ce cas précis.
    Non, pas du tout interdit.

    A +

Discussions similaires

  1. Réponses: 7
    Dernier message: 26/04/2007, 13h04
  2. Message d'erreur qd je valide mon projet
    Par yanis97 dans le forum Maven
    Réponses: 1
    Dernier message: 04/01/2007, 10h30
  3. Réponses: 7
    Dernier message: 17/11/2006, 12h17
  4. [C#] Comment valider mon formulaire ?
    Par LE NEINDRE dans le forum ASP.NET
    Réponses: 5
    Dernier message: 02/08/2006, 16h37
  5. [debutante]Validation XML schéma
    Par ameliepim dans le forum Valider
    Réponses: 7
    Dernier message: 03/07/2006, 16h44

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