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 :

Contrôle des cardinalités maximales


Sujet :

Schéma

  1. #1
    Membre émérite
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    683
    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 : 683
    Points : 2 753
    Points
    2 753
    Par défaut Contrôle des cardinalités maximales
    Bonjour,

    Je déplace un sujet que j'avais placé avec la gestion des contraintes d'inclusion.

    Il s'agit d'un autre petit cas à traiter pour le contrôle des cardinalités maximales.
    Imaginons une bibliothèque qui possède plusieurs ouvrages (chacun ayant un nombre exemplaires disponible) mis à la disposition des étudiants.
    Une version simple du MCD donne cela :


    Que proposer (en PostgreSQL si possible) pour contrôler :

    • d'une part le fait qu'un étudiant ne peut pas emprunter plus de 10 ouvrages (sachant que, quand il ramène un ouvrage, on supprime le lien dans "Emprunt" sans garder l'historique),
    • d'autre part, qu'un même ouvrage possède un nombre d'exemplaires limité.

    J'imagine un CHECK qui renvoie à une fonction (cf. contrainte "X" pour l'héritage) ou un trigger INSTEAD OF INSERT (peut-être plus sympa)...
    Qu'en pensez-vous ?
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  2. #2
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 157
    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 157
    Points : 38 963
    Points
    38 963
    Billets dans le blog
    9
    Par défaut
    Bonjour Paprick

    Dans ce cas de figure, le lecteur emprunte non pas un ouvrage, mais un exemplaire, chaque exemplaire étant associé à un ouvrage.
    Il suffit donc de compter le nombre d'occurrences actives (date de fin non échue) de l'association "emprunter" pour connaître le nombre d'emprunts en cours et vérifier ainsi qu'on n'a pas atteint la limite
    Le plus souvent, cette limite n'est pas bloquante, le bibliothécaire peut autoriser son dépassement.

    Exemple de MCD dans cet autre fil de discussion
    https://www.developpez.net/forums/d2...ercice-rendre/


    Dans un cas plus général où cette objection est hors sujet, je pense en effet qu'un trigger ou une fonction associée à une contrainte check sont applicables, mais attention toutefois : MySQL et MariaDB ne connaissent pas les trigger "instead of"

  3. #3
    Membre émérite
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    683
    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 : 683
    Points : 2 753
    Points
    2 753
    Par défaut
    Bonjour Capitaine,

    Effectivement, quand on parle d'emprunter un ouvrage (un titre au sens large), il s'agit pour l'étudiant d'en prendre un exemplaire... s'il en reste... et s'il ne dépasse pas ses 10 emprunts autorisés (limite donnée pour l'exemple afin de provoquer une contrainte à coder !).
    La table "Emprunt" ne contient alors que les emprunts en cours : on supprime l'occurrence de la table "Emprunt" quand l'étudiant ramène l'exemplaire (ce qui recrédite automatiquement ses droits à emprunt).
    L'objectif de cet exemple est d'être le plus simple possible pour se focaliser sur le codage de la contrainte.
    Pour le SGBD, connaissant les limites de MySQL ou MariaDB, j'ai préféré suggérer PostgreSQL.
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  4. #4
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 016
    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 016
    Points : 30 971
    Points
    30 971
    Billets dans le blog
    16
    Par défaut
    @Paprick

    Je fais référence à la discussion précédente :

    Citation Envoyé par Paprick Voir le message
    L'important est de bloquer un nouvel emprunt s'il n'y a plus d'exemplaire disponible.
    Je pense avoir la solution avec PostgreSQL

    A savoir une contrainte, comme dans l’exemple de l’exclusion...

    Je te tiens au courant.
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  5. #5
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 016
    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 016
    Points : 30 971
    Points
    30 971
    Billets dans le blog
    16
    Par défaut
    Exemple avec PostgreSQL.
     
    (1) Création des tables
     
    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
    CREATE TABLE Ouvrage
    (
       CodeOuv VARCHAR(8)
     , Titre VARCHAR(50) NOT NULL
     , NbExemplaires SMALLINT NOT NULL
     , CONSTRAINT Ouvrage_PK PRIMARY KEY(CodeOuv)
    );
     
    CREATE TABLE Etudiant
    (
       NumEtu VARCHAR(8)
     , Nom VARCHAR(50) NOT NULL
     , Prenom VARCHAR(50) NOT NULL
     , CONSTRAINT Etudiant_PK PRIMARY KEY(NumEtu)
    );
    CREATE TABLE Emprunt
    (
       CodeOuv VARCHAR(8)
     , NumEtu VARCHAR(8)
     , CONSTRAINT Emprunt_PK PRIMARY KEY(CodeOuv, NumEtu)
     , CONSTRAINT Emprunt_Ouvrage_FK FOREIGN KEY(CodeOuv) REFERENCES Ouvrage(CodeOuv)
     , CONSTRAINT Emprunt_Etudiant_FK FOREIGN KEY(NumEtu) REFERENCES Etudiant(NumEtu)
    );
     
    (2) Fonction
     
    La fonction récupère le paramètre qu’on lui transmet (LeCodeOuvrage) et qui dans la requête est nommé $1.
    Le code qui suit peut être rendu plus élégant (évacuation des variables par exemple), mais il a l’avantage de la lisibilité et de la simplicité.
     
    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
    CREATE OR REPLACE FUNCTION emprunt_max_fonction (LeCodeOuvrage varchar)
    RETURNS BOOLEAN  
    AS  
    $$  
    declare n1 integer ;
    declare n2 integer ;
     
    BEGIN
    select count(*) into n1
    from emprunt
    where codeouv = $1
    ;
    select nbexemplaires into n2
    from Ouvrage
    where codeouv = $1
    ;
    RETURN CASE
               WHEN n2 > n1
                  THEN true
               ELSE false
            END;    
    END;  
    $$ LANGUAGE 'plpgsql'
    ;
     
    (3) l’alter table
     
    Déclaration d’une contrainte pour la table Emprunt :
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    alter TABLE Emprunt
      add constraint Eprunt_max
        check (emprunt_max_fonction (CodeOuv)) ;
     
    (4) Création d’ouvrages
     
    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
    insert into Ouvrage (CodeOuv, Titre, NbExemplaires)
    values 
       ('o01', 'Merise en 3 jours', 4)
     , ('o02', 'UML en 3 jours', 3)
     , ('o03', 'Fantasia chez SQL', 5) 
     , ('o04', 'SQL Server pour les nuls', 3) 
     , ('o05', 'MySQL pour les nuls', 3) 
     , ('o06', 'PostgreSQL pour les nuls', 5) 
     , ('o07', 'Db2 for z/OS en 10 minutes', 2) 
     , ('o08', 'SQL-99 Complete, Really', 5) 
     , ('o09', 'An Introduction to Database Systems', 6) 
     , ('o10', 'Modélisation Conceptuelle de Données', 5) 
     , ('o11', 'Le Sorry Query Language (aka SQL)', 5) 
     , ('o12', 'Tintin à Toulouse', 5) 
    ;
     
    (5) Création d’étudiants
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    insert into Etudiant (NumEtu, Nom, Prenom)
    values
       ('e01', 'Naudin', 'Fernand')
     , ('e02', 'Volfoni', 'Raoul')
     , ('e03', 'Volfoni', 'Paul')
     , ('e04', 'Monpetit', 'Patricia')
     , ('e05', 'Delafoy', 'Antoine')
     , ('e06', 'Delafoy', 'Amédée')
     , ('e07', 'Folace', 'Francis')
     , ('e08', 'Jean', 'Jeannot')
    ;
     
    (6) Création d’emprunts
     
    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
    insert into Emprunt (CodeOuv, NumEtu)
    values 
       ('o01', 'e01')
     , ('o01', 'e02')
     , ('o01', 'e03')
     , ('o01', 'e04')
     
     , ('o02', 'e01')
     , ('o02', 'e03')
     , ('o02', 'e05')
     
     , ('o03', 'e01')
     , ('o03', 'e04')
     , ('o03', 'e05')
     
     , ('o04', 'e01')
    ;

    (7) Viol de la contrainte
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    insert into Emprunt (CodeOuv, NumEtu)
    Values  ('o02', 'e07') ;
     
    Au résultat :
     
    ERREUR: la nouvelle ligne de la relation « emprunt » viole la contrainte de vérification « eprunt_max » DETAIL: La ligne en échec contient (o02, e07).


    Est-ce bien ce à quoi il fallait s’attendre ?
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  6. #6
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 016
    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 016
    Points : 30 971
    Points
    30 971
    Billets dans le blog
    16
    Par défaut
    Au sujet du nombre maximum d’emprunts par les étudiants.

    Par rapport à mon message précédent, je garde tout, mais supprime le contenu de la table Emprunt pour y voir plus clair.
    Je créée une fonction pour contrôler que les étudiants puissent emprunter un nombre maximum d’exemplaires :
     
    (1) Création de la fonction
     
    Au numéro d’étudiant LeNumEtu correspond la variable $1 ;
    A la valeur maxi à ne pas dépasser LeMax correspond la variable $2.
    On peut se dispenser de la variable n1, mais là encore ne rendons pas le code hermétique.
     
    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
    CREATE OR REPLACE FUNCTION Emprunt_Etudiant_max_fonction (LeNumEtu varchar, LeMax integer)
    RETURNS BOOLEAN  
    AS $$  
    declare n1 integer ;
    BEGIN
        select count(*) into n1
        from emprunt
        where NumEtu = $1
        ;
        return case 
          when n1 > $2
            then false
            else true
           end ;   
    END;  
    $$ LANGUAGE 'plpgsql' ;
     
    (2) Ajout de la contrainte qui va bien à la table Emprunt
     
    Ici, la limite du nombre d’exemplaires par étudiant est fixé à 8.
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    alter TABLE Emprunt
      add constraint Emprunt_max_par_etudiant
        check (Emprunt_Etudiant_max_fonction (NumEtu, 8)) ;
     
    (3) Les inserts dans table Emprunt
     
    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
    insert into Emprunt (CodeOuv, NumEtu)
    values 
       ('o01', 'e01')
     , ('o01', 'e02')
     , ('o01', 'e03')
     , ('o01', 'e04')
     
     , ('o02', 'e01')
     , ('o02', 'e03')
     , ('o02', 'e05')
     , ('o02', 'e07') 
     , ('o02', 'e08') 
     
     , ('o03', 'e01')
     , ('o03', 'e02')
     , ('o03', 'e04')
     , ('o03', 'e05')
     
     , ('o04', 'e01')
     , ('o04', 'e02')
     , ('o05', 'e02')
     , ('o06', 'e02')
     , ('o07', 'e02')
     , ('o08', 'e02')
     , ('o09', 'e02')
     , ('o10', 'e02’)
    ;
     
    Curieusement, PostgreSQL fixe la limite à 9 au lieu de 8, mais bon, on essaiera de comprendre pourquoi. Si quelqu’un a une idée...
    Bref, j’ajoute un délinquant :
     
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    insert into Emprunt (CodeOuv, NumEtu)
    values ('o12', 'e02') ;
     
    Au résultat :
     
    ERREUR: la nouvelle ligne de la relation « emprunt » viole la contrainte de vérification « emprunt_max_par_etudiant »
    DETAIL: La ligne en échec contient (o12, e02).


    Vos avis ?
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  7. #7
    Membre émérite
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    683
    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 : 683
    Points : 2 753
    Points
    2 753
    Par défaut
    Bonsoir François,

    J'ai procédé de la même manière, mais en groupant les 2 contraintes : c'est plus court mais cela ne permet pas de distinguer laquelle des 2 contraintes est violée...
    Mais pour l'exercice, c'est sympa ! Voici ce que donne mon DDL :
    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
    CREATE TABLE Etudiant(
       NumEtu INTEGER,
       Nom VARCHAR(50),
       Prenom VARCHAR(50),
       CONSTRAINT PK_Etudiant PRIMARY KEY(NumEtu)
    );
    
    CREATE TABLE Ouvrage(
       CodeOuv INTEGER,
       Titre VARCHAR(50),
       Nb_Exemplaires SMALLINT,
       CONSTRAINT PK_Ouvrage PRIMARY KEY(CodeOuv)
    );
    
    CREATE TABLE Emprunt(
       NumEtu INTEGER,
       CodeOuv INTEGER,
       CONSTRAINT PK_Emprunt PRIMARY KEY(NumEtu, CodeOuv),
       CONSTRAINT FK_Emprunt_Etudiant FOREIGN KEY(NumEtu) REFERENCES Etudiant(NumEtu),
       CONSTRAINT FK_Emprunt_Ouvrage FOREIGN KEY(CodeOuv) REFERENCES Ouvrage(CodeOuv)
    );
    
    CREATE FUNCTION Nombre_Emprunts(Etu INTEGER, Ouv INTEGER) RETURNS BOOLEAN AS '
    BEGIN
       RETURN CASE
          WHEN (((SELECT Nb_Exemplaires FROM Ouvrage WHERE CodeOuv=$2) > (SELECT COUNT(*) FROM Emprunt WHERE CodeOuv=$2))
              AND ((SELECT COUNT(*) FROM Emprunt WHERE NumEtu=$1)<10))
          THEN TRUE
          ELSE FALSE
       END;    
    END;  
    ' LANGUAGE plpgsql;
    
    ALTER TABLE Emprunt
       ADD CONSTRAINT Emprunt_Max
          CHECK(Nombre_Emprunts(NumEtu,CodeOuv));
    Par ailleurs, penses-tu qu'on puisse faire la même chose avec un trigger INSTEAD OF INSERT ?
    Histoire de proposer une comparaison entre les 2 techniques...
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  8. #8
    Expert éminent sénior
    Homme Profil pro
    Responsable Données
    Inscrit en
    Janvier 2009
    Messages
    5 206
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 50
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Responsable Données

    Informations forums :
    Inscription : Janvier 2009
    Messages : 5 206
    Points : 12 787
    Points
    12 787
    Par défaut
    Bonjour,
    Citation Envoyé par fsmrel Voir le message
    Curieusement, PostgreSQL fixe la limite à 9 au lieu de 8, mais bon, on essaiera de comprendre pourquoi. Si quelqu’un a une idée...
    Dans le code tu vérifies si le nombre d'emprunts est strictement supérieur à la limite (8) avant de faire l'insertion.
    Donc quand tu insères le 9ème emprunts, il n'y en a que 8 en base, le test est faux, donc la fonction renvoie vrai, et l'insertion du 9ème peut se faire.

    La fonction devrait être:
    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
     
    CREATE OR REPLACE FUNCTION Emprunt_Etudiant_max_fonction (LeNumEtu varchar, LeMax integer)
    RETURNS BOOLEAN  
    AS $$  
    declare n1 integer ;
    BEGIN
        select count(*) into n1
        from emprunt
        where NumEtu = $1
        ;
        return case 
          when n1 >= $2
            then false
            else true
           end ;   
    END;  
    $$ LANGUAGE 'plpgsql' ;

    Tatayo.

    P.S. LeNumEtu varchar ? vraiment ?

  9. #9
    Membre émérite
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    683
    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 : 683
    Points : 2 753
    Points
    2 753
    Par défaut
    Bonjour,
    Citation Envoyé par tatayo Voir le message
    Dans le code tu vérifies si le nombre d'emprunts est strictement supérieur à la limite (8) avant de faire l'insertion.
    Donc quand tu insères le 9ème emprunts, il n'y en a que 8 en base, le test est faux, donc la fonction renvoie vrai, et l'insertion du 9ème peut se faire.
    Oui, c'est tout à fait ça : j'ai moi-même galéré un peu avec mon "<10"... j'avais commencé avec un "<=10" et j'autorisais donc le 11ème emprunt !
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  10. #10
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 016
    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 016
    Points : 30 971
    Points
    30 971
    Billets dans le blog
    16
    Par défaut
    Bonjour,

    Citation Envoyé par tatayo Voir le message
    Dans le code tu vérifies si le nombre d'emprunts est strictement supérieur à la limite (8) avant de faire l'insertion.
    Donc quand tu insères le 9ème emprunts, il n'y en a que 8 en base, le test est faux, donc la fonction renvoie vrai, et l'insertion du 9ème peut se faire.
     
    Merci Tatayo pour ton explication. Par analogie avec le moment (before/after) où le contrôle est effectué dans un trigger, on serait ici dans la logique before, alors je raisonnais after.
    Je ne connais pas grand-chose à PostgreSQL, mais au moins je retiendrai ce que tu as écrit.
     
     
    Citation Envoyé par tatayo Voir le message
    P.S. LeNumEtu varchar ? vraiment ?
     
    Dans la vraie vie, il est évident que ce type varchar est ici à bannir, car un identifiant doit être non significatif, invariant, inaccessible à l’utilisateur.
     
    Cela dit, en regard de ses exemples, je cite le papa de l’excellent Looping (mâtin, quel bel outil ) :
     
    Citation Envoyé par Paprick Voir le message
    ne nous prenons pas la tête avec les histoires d'identifiants non significatifs
     
    Dont acte !
     
    Tatayo, pour te rassurer, à titre d’exemple en 1995 j’ai audité les MCD prévus pour le projet FIBEN de la Banque de France. J’ai fait mettre à la poubelle un monceau de code applicatif. En effet, le SIREN des entreprises avait été retenu comme identifiant de l’entité-type ENTREPRISE, et sachant que l’INSEE procède régulièrement à des changements de ce SIREN, un code énorme et compliqué avait été développé pour répercuter ces changements dans les applications. Quand j’ai expliqué aux chefs de projets que l’identifiant devait être invariant, donc que le SIREN était à ravaler au rang d’identifiant alternatif (donc non concerné par les problèmes du genre intégrité référentielle), modifiable sans conséquence sur le reste, le MCD en cause a été rapidement mis à jour et la poubelle bien remplie. 
     
    Comme à chaque fois où j’ai eu à expliquer le danger des identifiants significatifs, j’ai systématiquement rappelé ce qu’à écrit avec bon sens un des père de Merise, Yves Tabourier, au début des années quatre-vingts (dans "De l’autre côté de Merise") :
     
     
    Te voilà rassuré Tatayo ?
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  11. #11
    Membre émérite
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    683
    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 : 683
    Points : 2 753
    Points
    2 753
    Par défaut
    Bonjour,
    Citation Envoyé par fsmrel Voir le message
    Comme à chaque fois où j’ai eu à expliquer le danger des identifiants significatifs, j’ai systématiquement rappelé ce qu’à écrit avec bon sens un des père de Merise, Yves Tabourier, au début des années quatre-vingts (dans "De l’autre côté de Merise").
    Te voilà rassuré Tatayo ?
    Pour rassurer encore plus Tatayo, et comme le montre mon DDL, j'ai prévu des codes en numérique !
    Mais, je ne peux m'empêcher de repartir en croisade contre le diktat des identifiants non significatifs !

    Certes, nous admettons tous qu'un identifiant doit être pérenne et ne doit donc jamais être modifié.
    Il est vrai qu'in identifiant non significatif n'a aucune raison de varier et est donc, à ce titre, un candidat idéal.
    MAIS, si "non significatif" garantit la pérennité, il est FAUX de dire que "significatif" entraîne la non pérennité !!!
    Deux exemples me viennent à l'esprit :
    • Un numéro de facture ne peut pas varier (règle comptable incontournable), alors à quoi bon rajouter un "Id_Facture" qui n'apporte rien ?
    • Quid des dates ? Elles sont largement présentes dans les PK et sont particulièrement significatives, non ?

    Alors, certes les dates, qui gèrent souvent les historiques, n'ont justement pas de raison de varier : donc tout va bien, mais parce qu'elles sont pérennes, bien que significatives.

    Je sais que je mène un combat perdu d'avance, mais cette fameuse "règle d'or" ne s'appuie pas sur la vraie cause : la cause, c'est la pérennité (et éventuellement le type pour des raisons de performance), mais pas la signification.

    Bon, allez, encore un coup d'épée dans l'eau, mais je n'ai pas pu m'en empêcher !

    Mais revenons à nos moutons ! Des idées pour passer notre petit problème avec un INSTEAD OF INSERT en PostgreSQL ?
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  12. #12
    Membre émérite
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    683
    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 : 683
    Points : 2 753
    Points
    2 753
    Par défaut
    Rebonjour,

    Voici ma proposition de INSTEAD OF INSERT : avec PostgreSQL, on doit passer par une vue (strictement équivalente à la table, mais qui permet les TRIGGER) ...

    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
    CREATE TABLE Etudiant(
       NumEtu INTEGER,
       Nom VARCHAR(50),
       Prenom VARCHAR(50),
       CONSTRAINT PK_Etudiant PRIMARY KEY(NumEtu)
    );
    
    CREATE TABLE Ouvrage(
       CodeOuv INTEGER,
       Titre VARCHAR(50),
       Nb_Exemplaires SMALLINT,
       CONSTRAINT PK_Ouvrage PRIMARY KEY(CodeOuv)
    );
    
    CREATE TABLE Emprunt(
       NumEtu INTEGER,
       CodeOuv INTEGER,
       CONSTRAINT PK_Emprunt PRIMARY KEY(NumEtu, CodeOuv),
       CONSTRAINT FK_Emprunt_Etudiant FOREIGN KEY(NumEtu) REFERENCES Etudiant(NumEtu),
       CONSTRAINT FK_Emprunt_Ouvrage FOREIGN KEY(CodeOuv) REFERENCES Ouvrage(CodeOuv)
    );
    
    CREATE VIEW VueEmprunt(NumEtu, CodeOuv) AS
       SELECT Emprunt.NumEtu, Emprunt.CodeOuv
       FROM Emprunt, Ouvrage
       WHERE Emprunt.CodeOuv=Ouvrage.CodeOuv;
    
    CREATE FUNCTION Insertion_Emprunt() RETURNS TRIGGER AS '
    BEGIN
          IF ((SELECT COUNT(*) FROM Emprunt WHERE NumEtu=NEW.NumEtu)<10)
              AND ((SELECT Nb_Exemplaires FROM Ouvrage WHERE CodeOuv=NEW.CodeOuv) >
                         (SELECT COUNT(*) FROM Emprunt WHERE CodeOuv=NEW.CodeOuv)) THEN
             INSERT INTO Emprunt VALUES(NEW.NumEtu, NEW.CodeOuv);
          END IF;
    END;  
    ' LANGUAGE plpgsql;
    
    CREATE TRIGGER Insert_Emprunt INSTEAD OF INSERT ON VueEmprunt
    FOR EACH ROW EXECUTE FUNCTION Insertion_Emprunt();
    Qu'en pensez-vous ?
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  13. #13
    Membre émérite
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    683
    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 : 683
    Points : 2 753
    Points
    2 753
    Par défaut
    Bonsoir,

    Je pense avoir trouvé plus simple sans avoir recours à une vue :
    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
    CREATE TABLE Etudiant(
       NumEtu INTEGER,
       Nom VARCHAR(50),
       Prenom VARCHAR(50),
       CONSTRAINT PK_Etudiant PRIMARY KEY(NumEtu)
    );
    
    CREATE TABLE Ouvrage(
       CodeOuv INTEGER,
       Titre VARCHAR(50),
       Nb_Exemplaires SMALLINT,
       CONSTRAINT PK_Ouvrage PRIMARY KEY(CodeOuv)
    );
    
    CREATE TABLE Emprunt(
       NumEtu INTEGER,
       CodeOuv INTEGER,
       CONSTRAINT PK_Emprunt PRIMARY KEY(NumEtu, CodeOuv),
       CONSTRAINT FK_Emprunt_Etudiant FOREIGN KEY(NumEtu) REFERENCES Etudiant(NumEtu),
       CONSTRAINT FK_Emprunt_Ouvrage FOREIGN KEY(CodeOuv) REFERENCES Ouvrage(CodeOuv)
    );
    
    CREATE FUNCTION Nb_Emprunts() RETURNS TRIGGER AS '
    BEGIN
       IF ((SELECT COUNT(*) FROM Emprunt WHERE NumEtu=NEW.NumEtu)<10)
       AND ((SELECT Nb_Exemplaires FROM Ouvrage WHERE CodeOuv=NEW.CodeOuv) > (SELECT COUNT(*) FROM Emprunt WHERE CodeOuv=NEW.CodeOuv)) THEN
          RETURN NEW;
       ELSE
          RAISE integrity_constraint_violation; 
          RETURN NULL;
       END IF;
    END;  
    ' LANGUAGE plpgsql;
    
    CREATE TRIGGER Emprunt_Max BEFORE INSERT OR UPDATE ON Emprunt
    FOR EACH ROW EXECUTE FUNCTION Nb_Emprunts();
    C'est mieux, non ?
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  14. #14
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 157
    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 157
    Points : 38 963
    Points
    38 963
    Billets dans le blog
    9
    Par défaut
    Bonjour Paprick

    Pourquoi pas, mais tu crées une fonction et appelles une procédure...
    C'est peut être une particularité de postgre d'accepter cette syntaxe, mais ça ne passera pas avec d'autres SGBD, du coup si tu cherches une solution applicable à peu près partout, ça n'ira pas.

  15. #15
    Membre émérite
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    683
    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 : 683
    Points : 2 753
    Points
    2 753
    Par défaut
    Bonjour Capitaine,
    Citation Envoyé par escartefigue Voir le message
    Pourquoi pas, mais tu crées une fonction et appelles une procédure...
    C'est peut être une particularité de postgre d'accepter cette syntaxe, mais ça ne passera pas avec d'autres SGBD, du coup si tu cherches une solution applicable à peu près partout, ça n'ira pas.
    Oui, je m'en suis rendu compte après avoir publié le message et j'ai corrigé mon code.
    Effectivement, il est étrange que Postpre ait accepté ça (sachant que ça fonctionne parfaitement malgré cette incohérence).
    Mais bon, autant faire les choses proprement ! (et je m'en vais corrigé mon précédent message sur le champ !).
    Petite question pour clôturer le sujet : quelle version préférez-vous ? CHECK ou TRIGGER ?
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  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 016
    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 016
    Points : 30 971
    Points
    30 971
    Billets dans le blog
    16
    Par défaut
    Ave,

    Citation Envoyé par Paprick Voir le message
    j'ai prévu des codes en numérique !
     
    Et la Banque de France elle aussi, avec le numéro SIREN...
    Numéro qui, à mon initiative, a été rendu identifiant alternatif pour les raisons que j’ai exposées dans mon message précédent 
     
    Et je ne parle pas du passage à l’époque du passage de la numérotation à 10 chiffres des téléphones, opération dans laquelle j’ai bien trempé chez je ne sais plus quel opérateur, que de sueur, d’énergie dépensée à cause de clés primaires à 8 chiffres, pourtant réputées pérennes...
     
    Et je pourrais remonter à 1965, quand l’X de service avait cru bon d’intégrer dans l’identifiant la date de naissance des fonctionnaires de l’Assistance Publique à Paris (date concaténée avec le nom). Certes, il avait tenu compte des doublons et triplons : si on a par exemple deux Albert nés le 31 janvier, alors l’un deux sera né le 31+31, soit le 62 janvier. S’ils sont trois, le 3e sera né le 93 janvier. Admettons. On est partis là-dessus. Petit problème pourtant : si les gens ne connaissent que leur année de naissance, l’instruit a oublié que de facto ils ont étés déclarés comme nés le 31 décembre. de ladite année... Panique à bord !
    Des trucs comme ça, y en a des wagons.
     
     
    Citation Envoyé par Paprick Voir le message
    Quid des dates ? Elles sont largement présentes dans les PK et sont particulièrement significatives, non ?
     
    Dans les historiques, oui. Sinon je veux bien qu’on me présente des contre-exemples.
    Je peux me tromper, mais hors historiques et assimilés, je vois mal la date participer à la clé primaire d’une table pour laquelle on a respecté les règles de normalisation énoncés par Codd et Boyce.

    Un exemple de base :
    Citation Envoyé par fsmrel Voir le message
    un éticien est membre d’une structure DEPUIS telle date.

    Citation Envoyé par Paprick Voir le message
    quelle version préférez-vous ? CHECK ou TRIGGER ?
    Dans la mesure où la contrainte (CHECK) est de nature ensembliste (indépendamment de ce qui peut se passer sous le capot), c’est-à-dire pour imager, qu’on balance directement la boîte de petits pois dans la casserole, je préfère. De son côté, le TRIGGER avec ses NEW ne présente pas ce caractère ensembliste, on balance les petits pois chacun son tour, autant revenir au pré-relationnel. Cela dit, dans la vraie vie, du temps où il fallait que j’engage mon entreprise sur la performance chez nos clients (ce qui fut mon lot quotidien du temps où j’étais opérationnel) : montage d'un prototype avec des tables bourrées ras la gueule, de millions de lignes, afin de comparer les performances respectives en l'occurrence de CHECK vs TRIGGER et donc pouvoir trancher.
    J’aimerais avoir l’avis du Capitaine...
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  17. #17
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 157
    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 157
    Points : 38 963
    Points
    38 963
    Billets dans le blog
    9
    Par défaut
    Je n'ai pas d'avis préconçu, dans ce genre de cas, seule l'épreuve de la charge vaut sentence !

    Pour info : SQL server applique l'optimisation sémantique sur les contraintes check, ce qui plaiderait en leur faveur pour ce SGBD.
    Mais il est à peu près certain que ce n'est valable que pour celles codées directement dans la contrainte et non pas les fonctions associées à ces contraintes (sinon je vois mal comment l'optimiseur pourrait en deviner le résultat...).
    Si SQLpro passe par là, il pourra sans doute le confirmer.

  18. #18
    Membre émérite
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    Juin 2019
    Messages
    683
    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 : 683
    Points : 2 753
    Points
    2 753
    Par défaut
    Salut Francois,
    Citation Envoyé par fsmrel Voir le message
    Et la Banque de France elle aussi, avec le numéro SIREN...
    Je sais : mais pour les codes, j'ai annoncé ne pas vouloir me préoccuper de leur pérennité.

    Et je ne parle pas du passage à l’époque du passage de la numérotation à 10 chiffres des téléphones, opération dans laquelle j’ai bien trempé chez je ne sais plus quel opérateur, que de sueur, d’énergie dépensée à cause de clés primaires à 8 chiffres, pourtant réputées pérennes...
    Le téléphone : c'est le contre-exemple de la pérennité ! Là dessus pas de débat : pas de PK pour un numéro de téléphone pour des tas de raisons !

    Et je pourrais remonter à 1965, quand l’X de service avait cru bon d’intégrer dans l’identifiant la date de naissance des fonctionnaires de l’Assistance Publique à Paris (date concaténée avec le nom). Certes, il avait tenu compte des doublons et triplons : si on a par exemple deux Albert nés le 31 janvier, alors l’un deux sera né le 31+31, soit le 62 janvier. S’ils sont trois, le 3e sera né le 93 janvier. Admettons. On est partis là-dessus. Petit problème pourtant : si les gens ne connaissent que leur année de naissance, l’instruit a oublié que de facto ils ont étés déclarés comme nés le 31 décembre. de ladite année... Panique à bord !
    Des trucs comme ça, y en a des wagons.
    Oui, et tous ces wagons sont remplis d'identifiants non pérennes, on est d'accord... mais il existe aussi des exemples d'identifiants pérennes qui sont significatifs ! Encore une fois : le problème c'est la pérennité, pas la signification : tu peux le tourner dans tous les sens et trouver des milliers d'exemples, on en revient toujours à ça.
    C'est sûr que si l'on considère que, systématiquement sans la moindre réflexion, "signification = non pérennité", alors le débat est clos.

    Dans les historique s, oui. Sinon je veux bien qu’on me présente des contre-exemples.
    Je peux me tromper, mais hors historiques et assimilés, je vois mal la date participer à la clé primaire d’une table pour laquelle on a respecté les règles de normalisation énoncés par Codd et Boyce.
    D'accord, mais ça n'en reste pas moins des identifiants significatifs.

    Mais bon, je sais que c'est un débat sans fin dans lequel je n'ai aucune chance de convaincre !
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

Discussions similaires

  1. Pb au niveau des cardinalités
    Par sebac dans le forum Access
    Réponses: 1
    Dernier message: 26/04/2014, 10h08
  2. Réponses: 1
    Dernier message: 24/07/2007, 11h44
  3. liaisons par des cardinalités minimales égales à 1
    Par pcappell dans le forum WinDev
    Réponses: 6
    Dernier message: 28/05/2007, 16h54
  4. comment faire quand on a des cardinalités 0,1 1,n ?
    Par zana74 dans le forum Requêtes
    Réponses: 6
    Dernier message: 25/08/2006, 09h06
  5. [Together] Gestion des cardinalités
    Par cladsam dans le forum Autres
    Réponses: 3
    Dernier message: 03/08/2005, 21h33

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