Discussion :

[fsmrel]

 
(52) Trigger sur la vue PARTICIPER_V :

USE Mike ;

DROP TRIGGER IF EXISTS PARTICIPER_TR ;
GO

CREATE TRIGGER PARTICIPER_TR ON PARTICIPER_V INSTEAD OF INSERT, UPDATE
AS
DECLARE @Engueulade AS VARCHAR(512) ;
DECLARE @n as INT ;

-----------------------------------------------------------
-- tables temporaires pour les clés primaires
-----------------------------------------------------------

DECLARE @inserted TABLE
(
    joueurId     INT
  , blindtestId  INT
) ;
DECLARE @deleted TABLE
(
    joueurId     INT
  , blindtestId  INT
) ;
---- pour debug ---- select '' as INSERTED, * from INSERTED  
---- pour debug ---- select '' as DELETED, * from DELETED  

------------------------------------------------------
-- traduction des clés alternatives (naturelles) en
-- clés primaires (artificielles)
------------------------------------------------------

INSERT INTO @inserted (joueurId, blindtestId)
    SELECT DISTINCT y.blindeurId, z.blindtestId
    FROM   INSERTED AS x
      JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
      JOIN BLINDTEST AS z ON x.blindtestCode = z.blindtestCode
;
---- pour debug ---- select '' as '@inserted', * from @inserted 

-------------------------------------------------------
-- comptage des viols de la contrainte d'exclusion
-------------------------------------------------------
SET @n = 
    (SELECT COUNT(*) 
     FROM   @inserted AS x
              JOIN BLINDTEST AS y  
                   ON x.blindtestId = y.blindtestId
                   AND x.joueurId = y.animateurId)

---- pour debug ---- select @n as '@n' 

----------------------------------------------------
-- Si on constate une tentative de viol de la
-- contrainte d'exclusion, on rouspète 
-- et on s'en va.
----------------------------------------------------
IF @n > 0
    BEGIN
        SET @Engueulade = 'Un blinder ne peut pas participer au blindtest qu''il anime !'
        SELECT @Engueulade AS Engueulons  --, * FROM INSERTED
        RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
 ----        RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
        RETURN  
    END

----------------------------------------------------
-- On n'a pas constaté d'anomalie : 
-- on met la table PARTICIPER à jour.
----------------------------------------------------

---------------------------------------------------
-- S’il y a quelque chose dans DELETED
-- alors c’est qu’un UPDATE est en cours,
-- on commence par faire le ménage dans COMPOSER.
---------------------------------------------------
IF (select COUNT(*) FROM DELETED) > 0
    BEGIN 
        INSERT INTO @deleted (joueurId, blindtestId)
            SELECT DISTINCT y.blindeurId, z.blindtestId
            FROM   DELETED AS x
              JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
              JOIN BLINDTEST AS z ON x.blindtestCode = z.blindtestCode
        ;
---- pour debug ---- select '' as '@deleted' , * from @deleted

        DELETE FROM PARTICIPER
            WHERE joueurId IN (SELECT joueurId FROM @deleted)
              AND blindtestId IN (SELECT blindtestId FROM @deleted) 

---- pour debug ---- select '' as 'participer après delete', * from PARTICIPER

    END

---------------------------------
-- On est en INSERT ou UPDATE :
-- on charge.
---------------------------------

INSERT INTO PARTICIPER (joueurId, blindtestId)
    SELECT joueurId, blindtestId 
    FROM @inserted
;
GO

 

[fsmrel]

 
(6) Un bout de jeu d’essai :

USE Mike ;

----------------------------------
-- on vide les tables
----------------------------------
DELETE FROM TROUVER ;
DELETE FROM PARTICIPER ;
DELETE FROM COMPOSER ;
DELETE FROM EXTRAIT ;
DELETE FROM BLINDTEST ;
DELETE FROM BLINDEUR ;

----------------------------------------------
-- blindeurs
----------------------------------------------
INSERT INTO BLINDEUR (pseudo) VALUES
    ('fernand'), ('raoul'), ('paul'), ('mado')
  , ('folace'), ('jean'), ('antoine'), ('patricia') 
;
SELECT pseudo from BLINDEUR
;
----------------------------------------
-- animateurs
----------------------------------------
INSERT INTO ANIMATEUR (animateurId) 
    SELECT blindeurId FROM BLINDEUR WHERE pseudo IN ('fernand', 'raoul', 'paul', 'mado')
;
SELECT pseudo AS Animateur 
FROM ANIMATEUR AS x
JOIN BLINDEUR AS y on x.animateurId = y.blindeurId
;
----------------------------------------
-- joueurs
----------------------------------------
INSERT INTO JOUEUR (joueurId)
    SELECT blindeurId 
    FROM BLINDEUR 
    WHERE pseudo IN ('fernand', 'mado', 'folace', 'jean', 'antoine', 'patricia')
;
------------------------------
-- pour voir
------------------------------
SELECT pseudo AS Joueur 
FROM JOUEUR AS x
JOIN BLINDEUR AS y on x.joueurId = y.blindeurId
;
------------------------------------------------
-- Les blindtests
------------------------------------------------
INSERT INTO BLINDTEST (blindtestCode, animateurId)
    SELECT 'blindt01', blindeurId
    FROM    BLINDEUR
    WHERE pseudo = 'fernand'    
;
INSERT INTO BLINDTEST (blindtestCode, animateurId)
    SELECT 'blindt02', blindeurId
    FROM    BLINDEUR
    WHERE pseudo = 'mado'    
;  
INSERT INTO BLINDTEST (blindtestCode, animateurId)
    SELECT 'blindt03', blindeurId
    FROM    BLINDEUR
    WHERE pseudo = 'fernand'    
;  
INSERT INTO BLINDTEST (blindtestCode, animateurId)
    SELECT 'blindt04', blindeurId
    FROM    BLINDEUR
    WHERE pseudo = 'fernand'    
;
------------------------------
-- pour voir
------------------------------  
SELECT '' as blindtest, blindtestCode, pseudo 
FROM   BLINDTEST AS x
  JOIN BLINDEUR AS y ON x.animateurId = y.blindeurId
;
---------------------------------------------------
-- participation des joueurs aux blindtests
---------------------------------------------------
INSERT INTO PARTICIPER_V (pseudo, blindtestCode) VALUES 
    ('folace', 'blindt01') 
  , ('folace', 'blindt02') 
  , ('jean', 'blindt01') 
;
-- Le blindeur fernand anime le blindtest blindt01 
-- et ne pourra donc y participer, le trigger veillant au grain 
INSERT INTO PARTICIPER_V (pseudo, blindtestCode) VALUES 
    ('fernand', 'blindt01')
;
-- Le blindeur mado a le droit de participer
-- au blindtest blindt01,
-- mais mado anime le blindtest blindt02 
-- et ne pourra donc y participer, le trigger veillant au grain. 
INSERT INTO PARTICIPER_V (pseudo, blindtestCode) VALUES 
    ('mado', 'blindt01')
;
UPDATE PARTICIPER_V
    SET blindtestCode = 'blindt02' WHERE pseudo = 'mado'
;
-- pour jean, pas de problème
update PARTICIPER_V
    SET blindtestCode = 'blindt02' WHERE pseudo = 'jean'
;
------------------------------
-- pour voir
------------------------------  
SELECT '' as participer, blindtestCode, pseudo 
FROM PARTICIPER_V
;
---------------------------------
-- des extraits
---------------------------------
INSERT INTO EXTRAIT (extraitCode, titre) VALUES
    ('denich', 'Le dénicheur'), ('oldleon', 'Old Leon'), ('marquise', 'Marquise')
  , ('scaph', 'Le scaphandrier'), ('platpays', 'Le plat pays')
  , ('vieuxleo', 'Le vieux Léon')
;
------------------------------
-- pour voir
------------------------------  
SELECT extraitCode, titre 
FROM EXTRAIT ;
;
-------------------------------------------
-- compositions blindtests-extraits
--------------------------------------------
INSERT INTO COMPOSER_V (blindtestCode, extraitCode) VALUES
      ('blindt01', 'oldleon')
    , ('blindt01', 'platpays')
    , ('blindt02', 'oldleon')
    , ('blindt02', 'marquise')
   ;
------------------------------
-- pour voir
------------------------------  
SELECT blindtestCode, extraitCode
FROM   COMPOSER_V 
; 
SELECT blindtestCode, x.extraitCode, titre
FROM   COMPOSER_V as x
  JOIN EXTRAIT as y ON x.extraitCode = y.extraitCode 
;
----------------------------------------
-- vue COMPOSER_V : updates
----------------------------------------
UPDATE COMPOSER_V 
   SET blindtestCode  = 'blindt03'
   WHERE blindtestCode = 'blindt01' and extraitCode = 'platpays'
;
UPDATE COMPOSER_V 
   set extraitCode  = 'vieuxleo', blindtestCode  = 'blindt04'
   where extraitCode =  'oldleon'
;
------------------------------
-- pour voir
------------------------------  
SELECT '' as 'après update', blindtestCode, x.extraitCode, titre
FROM   COMPOSER_V as x
  JOIN EXTRAIT as y ON x.extraitCode = y.extraitCode 
;
-------------------------------------------------------
-- Les joueurs qui ont trouvé
-------------------------------------------------------
INSERT INTO TROUVER (joueurId, blindtestId, extraitId) VALUES
      ((SELECT blindeurId FROM BLINDEUR WHERE pseudo = 'folace')
     , (SELECT blindtestId FROM BLINDTEST WHERE blindtestCode = 'blindt02')
     , (SELECT extraitId FROM EXTRAIT WHERE extraitCode = 'marquise'))
;
INSERT INTO TROUVER (joueurId, blindtestId, extraitId) VALUES
      ((SELECT blindeurId FROM BLINDEUR WHERE pseudo = 'jean')
     , (SELECT blindtestId FROM BLINDTEST WHERE blindtestCode = 'blindt02')
     , (SELECT extraitId FROM EXTRAIT WHERE extraitCode = 'marquise'))
;
INSERT INTO PARTICIPER_V (pseudo, blindtestCode) VALUES 
    ('jean', 'blindt04')
;
INSERT INTO TROUVER (joueurId, blindtestId, extraitId) VALUES
      ((SELECT blindeurId FROM BLINDEUR WHERE pseudo = 'jean')
     , (SELECT blindtestId FROM BLINDTEST WHERE blindtestCode = 'blindt04')
     , (SELECT extraitId FROM EXTRAIT WHERE extraitCode = 'vieuxleo'))
;
------------------------------
-- pour voir
------------------------------  
SELECT '' as trouver, pseudo, blindtestCode, extraitCode, titre 
FROM   TROUVER as x
  JOIN BLINDEUR as y ON x.joueurId = y.blindeurId
  JOIN BLINDTEST as z ON x.blindtestId = z.blindtestId
  JOIN EXTRAIT as t ON x.extraitId = t.extraitId
;
GO

Y a plus qu’à...

 

[Mike888]

Merci énormément pour ce récapitulatif, car il est vrai que je me suis un peu perdu à travers tous vos posts...
Après, avec une aide aussi précieuse, je ne vais pouvoir faire que du bon travail !!

Je reviens vers vous si je rencontre un souci !

[fsmrel]

Bonjour Mike,

Après, avec une aide aussi précieuse, je ne vais pouvoir faire que du bon travail !!

Et médailler ! (cf. profil pro)


Si problème avec les tables, n'hésitez pas...

[Mike888]

Je veux bien vous médailler, mais je vois pas trop ce que je dois faire sur votre profil pro...

[fsmrel]

Normalement, dans l’onglet Compétences apparaît quelque chose comme ceci, quand le pointeur de la souris passe par exemple sur la médaille « Merise » :




_________________________________________________________

C’est l’occasion de vérifier qu’on passe de 40 à 41

 

[Mike888]

Ah..., chez moi il ne se passe rien quand je clique...

----------

Sinon, une question par rapport à la DB; j'ai vu que vous lui avez octroyé 16MB. Sachant qu'il va y avoir des centaines (entre 2000 et 3000 je crois) de Blindtests à encoder, n'est-ce pas trop peu ?

[fsmrel]

Même si le pointeur de la souris reste sur la médaille ? (peut-être sommes-nous en concurrence d’accès ?)

j'ai vu que vous lui avez octroyé 16MB. Sachant qu'il va y avoir des centaines (entre 2000 et 3000 je crois) de Blindtests à encoder, n'est-ce pas trop peu ?

La valeur que j’ai fournie est là simplement à titre d’exemple. On peut remplacer "MAXSIZE=16MB" par "MAXSIZE=UNLIMITED"...

Pour être vraiment pertinent, il faut être à même de calculer la volumétrie pour chaque table et chaque index. Il faut aussi estimer la réduction obtenue par compression.

Prenons par exemple le cas de la table EXTRAIT :

CREATE TABLE EXTRAIT 
(
        extraitId        INT  IDENTITY      NOT NULL 
      , extraitCode      VARCHAR(8)         NOT NULL
      , titre            VARCHAR(48)        NOT NULL
    , CONSTRAINT EXTRAIT_PK PRIMARY KEY (extraitId)
    , CONSTRAINT EXTRAIT_AK UNIQUE (extraitCode)
) ;

Créons un extrait :

INSERT INTO EXTRAIT (extraitCode, titre) VALUES
    ('brique', 'Cent onze ans dans un mur, la vie d''une brique')

La colonne extraitId est du type INT : elle occupe 4 octets.

La colonne extraitCode occupe 6 octets + 2 octets de service.

La colonne titre occupe 46 octets + 2 octets de service.

Soit en tout 4 + 8 + 48 = 60 octets.

La longueur moyenne des colonnes extraitCode et titre reste à déterminer... A supposer qu’elles soient de cet ordre de grandeur et qu’on ait 20000 extraits, les données de la table occupent alors de l’ordre de 60 * 20000 = 1,2 MB.

A cela il faut ajouter l’encombrement des index, et là il est préférable de disposer d’un outil de simulation (à défaut charger la table et analyser la taille des index). Il faut déjà prévoir quelles colonnes seront indexées : la colonne extraitId c’est sûr (clé primaire), la colonne extraitCode c’est sûr (clé alternative), la colonne titre c’est probable (c’est-à-dire si on doit effectuer des recherches sur le titre). En passant, un dessin illustrant la structure des index, vus à 10000 mètres...

A la louche, comptons un encombrement au moins égal à celui de la table...

Ce qui vaut pour la table EXTRAIT vaut évidemment pour les autres tables, sachant que les attributs des tables associatives (COMPOSER, PARTICIPER, TROUVER) sont de type INT (4 octets), donc moins gourmands, et comportent seulement deux index (un pour la clé primaire et un pour la clé étrangère qui n’est pas déjà indexée). Concernant les tables spécialisées (ANIMATEUR, JOUEUR), on ne compte qu’un seul index (servant à la fois pour la clé primaire et pour la clé étrangère).

En ce qui concerne la compression, je vous renvoie à la page 514 de l’ouvrage de référence, où il est écrit que la compression n’est pas opérationnelle pour la version Express de SQL Server. Sinon, pour comprimer par exemple par pages entières (c’est-à-dire multilignes) :

CREATE TABLE EXTRAIT 
(
        extraitId        INT  IDENTITY      NOT NULL 
      , extraitCode      VARCHAR(8)         NOT NULL
      , titre            VARCHAR(48)        NOT NULL
    , CONSTRAINT EXTRAIT_PK PRIMARY KEY (extraitId)
    , CONSTRAINT EXTRAIT_AK UNIQUE (extraitCode)
)
WITH (DATA_COMPRESSION = PAGE)
;

Avec un rendement qui (comme dans le cas des fichiers zippés) serait intéressant pour des données du type caractère (CHAR, VARCHAR, ...) de taille relativement conséquente, comme la colonne titre.



 

[Mike888]

Bonsoir fsmrel !

J'ai encore une question :

Je ne saisis pas bien les rôles de "blindtestcode" et "extraitcode"...
Dans la table Blindtest, il y a ce fameux "blindtestcode"; or il y a déjà un identifiant avec "blindtestid"... Donc, quelle est l'utilité de ce xxxcode ? (en fait, c'est le concept de clé alternative qui m'échappe).

[escartefigue]

Bonjour,

Bien que la question soit adressée nominativement à FsmRel, je me permets d'y répondre :
d'une façon générale, toute table de typologie (pays, devises, unités de mesure, types de TVA, moyens de paiement...) comporte a minima un code et un libellé, souvent aussi des dates de début et de fin de validité.
À tous ces attributs fonctionnels, s'ajoute un identifiant technique attribué par le SGBD à utiliser comme identifiant primaire. Je n'ai pas lu l'ensemble des échanges, mais je ne doute pas que FsmRel ait déjà plaidé dans ce sens. L'id technique asémantique et concis étant ce qui se fait de mieux comme PK.

Le code facilite la recherche et permet un affichage et/ou une impression plus concis qu'un libellé souvent encombrant.
Le code est souvent issu d'une nomenclature externe (l'ISO ou l'INSEE par exemple)
Le code sera dépourvu de caractères diacritiques ou spéciaux (pas d'accent, de cédille, de ligature ou de symbole) il sera le plus souvent codé en majuscules uniquement (exception notable : les codes unités de mesure)

Utiliser du varchar pour un code présente peu d'intérêt et peut même coûter plus cher que du char : la plupart des codes ont une longueur identique pour toutes les occurrences, il n'y a donc pas de gain à prévoir avec du varchar. Et comme le varchar ajoute 1 à 3 octets de longueur (ça dépend de la longueur effective et du SGBD), du varchar(2) est finalement plus encombrant que du char(2).
Et comme enfin le varchar présente des inconvénients qui plombent les performances dans certains cas, il est préférable de le réserver aux données dont la longueur varie effectivement d'une occurrence à une autre et dont la longueur totale est supérieure à une vingtaine de caractères.

[fsmrel]

Bonjour Mike, bonjour Capitaine,

Je n’ai pas eu le temps de répondre à la question de Mike, je complète donc.

Je ne saisis pas bien les rôles de "blindtestcode" et "extraitcode"...

Concernant l’attribut blindtestcode, il s’agit en fait de l’attribut Numero de votre entité-type Blindtest (cf. post #4). {Numero} doit être clé alternative et blindtestcode disparaît.

Concernant l’attribut extraitcode, il s’agit d’un attribut fonctionnel (c’est-à-dire naturel) qui mérite d’exister à titre d’identifiant fonctionnel. Comme dit Escartefigue, un objet mérite d’être doté d’au moins deux attributs fonctionnels : un code et un libellé.
Les accès aux données se font au moyen des attributs fonctionnels, donc sémantiquement sensés (pardon pour le pléonasme), et dans le cas de la table EXTRAIT, on ne dispose que de l’attribut Titre, qui est un libellé « long » et susceptible de comporter des doublons, d’où la nécessité d’un attribut naturel supplémentaire tel que extraitCode qui soit court et sans risque de doublonner.

Je reprends ce que j’ai écrit dans le post #9 : « En tout cas, une chose est sûre, il faut éviter d’attribuer une valeur sémantique aux clés de type IDENTITY, les déboires sont très fréquents (voir dans le forum SQL Server) ». Comme le rappelle Escartefigue, une clé primaire comme {extraitid} est construite à partir d’un attribut artificiel valorisé et contrôlé par le SGBD dès qu’IDENTITY est dans le coup et mieux vaut ne pas s’en servir soi-même. Maintenant, libre à vous de manipuler les tables directement au moyen des clés primaires mais votre vigilance devra redoubler une épée de Damoclès sera suspendue au-dessus de chaque table...


 

[Mike888]

Merci pour votre réponse.

En fait, j'ai suivi quelques cours du soir y'a une dizaine/quinzaine d'années maintenant (je code pour le plaisir, ce n'est pas du tout ma profession). D'une part, j'ai un peu oublié certaines choses, et d'autre part, on n'a pas approfondi des masses ! Donc, mes connaissances sont plutôt maigrelettes et j'ai parfois l'impression que vous sur-estimez mes capacités. Par exemple, je ne comprends pas grand chose à cette histoire de clé alternative (qu'on n'a jamais abordé aux cours).

{Numero} doit être clé alternative et blindtestcode disparaît.

C'est à dire ?

- - - - - - - -

Dans mes cours, on n'a jamais travaillé qu'avec la clé primaire.
Ici, avec cette clé alternative, je ne vois pas comment traduire ça dans mon code lors de la création des classes...

Bref, c'est pas simple !

[Mike888]

Bon, ma question est courte :

Est-ce que je peux me passer de ces clés alternatives et qu'est-ce que ça implique par rapport aux tables créées ?

[Mike888]

En fait, ce qui m'embête c'est qu'un extrait (Artiste + Titre) peut exister en plusieurs exemplaires (par ex : "Animals - House of rising sun" peut exister dans les BTs 5, 87 et 364); je ne sais pas si c'est prévu dans votre structure... Et je ne veux surtout pas tout chambouler dans ce qui existe déjà !

[fsmrel]

Bonsoir Mike,

{Numero} doit être clé alternative et blindtestcode disparaît.

C'est à dire ?

Dans votre MCD, l’entité-type BLINDTEST est dotée d’un attribut Numero qui manifestement vous permet de numéroter les blindtests, à condition bien sûr que le même numéro ne soit pas attribué à deux blindtests, autrement dit, {Numero} doit vérifier la propriété d’unicité des clés.


Situation initiale

CREATE TABLE BLINDTEST 
(
        blindtestId      INT  IDENTITY      NOT NULL
      , blindtestCode    VARCHAR(8)         NOT NULL
      , animateurId      INT                NOT NULL 
    , CONSTRAINT BLINDTEST_PK PRIMARY KEY (blindtestId)
    , CONSTRAINT BLINDTEST_AK UNIQUE (blindtestCode)
    , CONSTRAINT BLINDTEST_ANIMATEUR_FK FOREIGN KEY (animateurId)
          REFERENCES ANIMATEUR (animateurId)
) ;

L’attribut blindtestCode rendu caduc, l’attribut Numero le remplace :

CREATE TABLE BLINDTEST 
(
        blindtestId      INT  IDENTITY      NOT NULL
      , Numero           VARCHAR(8)         NOT NULL
      , animateurId      INT                NOT NULL 
    , CONSTRAINT BLINDTEST_PK PRIMARY KEY (blindtestId)
    , CONSTRAINT BLINDTEST_AK UNIQUE (Numero)
    , CONSTRAINT BLINDTEST_ANIMATEUR_FK FOREIGN KEY (animateurId)
          REFERENCES ANIMATEUR (animateurId)
) ;

En passant, vous pouvez remplacer VARCHAR par CHAR.

Je ne comprends pas grand chose à cette histoire de clé alternative (qu'on n'a jamais abordé aux cours)

Complétons le cours... Revenons-donc aux fondements de la théorie relationnelle (le modèle relationnel de données inventé par Codd il y a 50 ans). Qu’est-ce qu’une clé primaire ? Une clé alternative ? Plus généralement une clé candidate ? Puisqu’on en est au stade SQL, j’utiliserai la terminologie propre à ce langage plutôt qu’à celui de la théorie relationnelle.

La notion première est celle de clé candidate (en abrégé clé s’il n’a pas de confusion possible).

Une clé candidate K (sous-ensemble de colonnes) d’une table T doit vérifier les contraintes suivantes :

Unicité : Deux lignes distinctes de T ne peuvent pas avoir simultanément la même valeur pour K.
Irréductibilité : Il n’existe pas de sous-ensemble strict de K garantissant la contrainte d’unicité.

Considérez maintenant le tableau périodique des éléments, dont on pourrait faire une table ELEMENT en SQL. Ce tableau permet de représenter les éléments et on y voit que l’élément de numéro atomique 6 a pour nom Carbone, pour symbole C, pour masse atomique 12, etc. Aucun autre élément n’a les caractéristiques de cet élément : numéro atomique, nom, symbole, masse atomique, etc. sont donc autant de propriétés (naturelles) devant être gouvernées par la règle d’unicité et par la règle d’irréductibilité ⁽¹⁾. Chacune d’elles est donc candidate à être clé : dans la théorie relationnelle on dit que {numéro atomique}, {nom}, {symbole}, {masse atomique}, etc. sont des clés candidates de la table ELEMENT (ou plus simplement clés).

A ces propriétés naturelles, on peut aussi en ajouter une, qualifiée d’artificielle, car dénuée de toute signification contrairement aux autres. Sa finalité est non seulement de garantir l’unicité, mais aussi de garantir l’intégrité référentielle (par exemple la colonne joueurid de la table PARTICIPER ne peut prendre que des valeurs présentes dans la colonne joueurid de la table JOUEUR). Tout cela efficacement, au nom de l’invariance que l’on veut pour une telle clé.

En SQL, on dit que parmi ces clés l’une sera choisie pour être clé primaire, auquel cas les autres seront reléguées au rang de clés alternatives ⁽²⁾.

On pourra objecter que les valeurs prises par les colonnes de la table ELEMENT sont stables, invariantes, donc que l’ajout d’une colonne artificielle est dénuée d’intérêt, mais prenez le cas de la table BLINDEUR et que de {pseudo} on en fasse la clé primaire. Supposons qu’il faille changer la valeur d’un pseudo, par exemple 'Tartempion' à remplacer par 'Tartmuche' : du fait de l’intégrité référentielle, la modification devra aussi affecter les tables ANIMATEUR (sans oublier la table BLINDTEST), JOUEUR (par voie de conséquence les tables PARTICIPER et TROUVER). Par contre, si c’est {blindeurId} qui est clé primaire, la modification ne touchera que la table BLINDEUR.

Est-ce que je peux me passer de ces clés alternatives et qu'est-ce que ça implique par rapport aux tables créées ?

Au vu de ce qui précède, votre désir de manipuler directement les valeurs affectées aux clés primaires est légitime, mais vous serez bien forcé de mettre en oeuvre un mécanisme (disons de la programmation) vous permettant de savoir quelle valeur a été affectée à la clé primaire du blindeur Tartempion, du blitndest 'xyz', de l’extrait 'Tagada tsoin tsoin'. Toutefois, la déclaration des clés alternatives reste nécessaire, sinon rien n’empêchera d’avoir plusieurs fois la valeur 'Tartempion' pour la colonne pseudo.

Dans un monde idéal, pas de problème pour manipuler directement la colonne blindeurId de la table BLINDEUR. Supposons maintenant que le SGBD a affecté la valeur 314 à cette colonne lors de la création d’un blindeur pour lequel vous avez demandé à cette occasion que soit affectée la valeur 'Tartempion' à la colonne pseudo.

Dans le système que j’utilise pour ma part, je répète que les clés artificielles (primaires) sont là pour que le SGBD navigue de table en table, par exemple pour aller chercher les extraits trouvé par Tartempion, mais en l’occurrence je n’utilise pas la valeur 314 de la colonne blindeurId comme point d’entrée, seulement la valeur 'Tartempion' de la colonne pseudo. Maintenant, au fil du temps, si j’ai décidé de renommer Tartempion en Tartmuche, alors je coderai désormais « SELECT ... WHERE pseudo = 'Tartmuche' ».

Avec votre système, le SGBD peut remplacer inopinément, silencieusement 314 par exemple par 271 (suite à reprise sur incident, erreur de manip sur la base de données, etc.), tout en maintenant la cohérence des données c’est son boulot, mais comment serez vous informé du changement ? Dans les tables ANIMATEUR, JOUEUR, PARTICIPER, TROUVER, le SGBD aura remplacé 314 par 271, mais qu’en saurez-vous ? Quel sera le résultat d’un « SELECT ... WHERE blindeurId = 314 » ? Certainement pas celui (valide) obtenu « SELECT ... WHERE pseudo = 'Tartmuche' ».

Donc, rien ne vous empêche d’ignorer les clés alternatives, mais souhaitez que votre système ne connaisse jamais d’incident, soyez extrêmement rigoureux et vigilant et cherchez des moyens vous permettant d’être alerté que la valeur 314 a été remplacée par la valeur 271...

Donc, rien ne vous empêche d’ignorer les clés alternatives, mais souhaitez alors que votre système ne connaisse jamais d’incident perturbant l’affectation des clés primaires, soyez extrêmement rigoureux et vigilant et cherchez des moyens vous permettant d’être alerté que la valeur 314 a été remplacée par la valeur 271...

Et ne donnez jamais de signification aux clé primaires.

______________
⁽¹⁾ Notion de surclé : une surclé est un ensemble de colonnes garantissant l’unicité, mais pas nécessairement l’irréductibilité des clés. Par exemple, les éléments périodiques sont l’objet de regroupements : métaux alcalins, halogènes, etc. faisant qu’on peut définir une colonne Groupe pour la table ELEMENT. {Groupe} ne permet pas de garantir l’unicité des clés, mais {Groupe, Symbole} le permet. Néanmoins, {Groupe, Symbole} viole la règle d’irréductibilité car le sous-ensemble {Symbole} garantit à lui seul l’unicité : {Groupe, Symbole} et {Symbole} sont des surclés, mais de ces 2 surclés seulement {Symbole} est clé candidate.

⁽²⁾ Le distinguo clé primaire/alternative est en fait parfaitement inutile, mais il existait jadis dans la théorie relationnelle (année 1970), et depuis a subi un coup de rasoir d’Ockham en 1993. En SQL il serait plus difficile d’en faire autant (pensez au nombre de tables en production dans le monde dans lesquelles figurent la clause « Primary Key »...)


 

[fsmrel]

[

En fait, ce qui m'embête c'est qu'un extrait (Artiste + Titre) peut exister en plusieurs exemplaires (par ex : "Animals - House of rising sun" peut exister dans les BTs 5, 87 et 364); je ne sais pas si c'est prévu dans votre structure... Et je ne veux surtout pas tout chambouler dans ce qui existe déjà !

J’ai omis de définir l’attribut Artiste, mais je l’ajouterai.

J’espère que la paire de valeurs <Animals>, <House of rising sun> n’est quand même présente qu’une seule fois dans la table EXTRAIT. Qu’en est-il ?


 

[Mike888]

Génial, j'ai enfin compris !!! (oui, je suis parfois long à la détente)
Merci 1000x pour votre pédagogie.

C'est vrai que je n'ai jamais pensé que des incidents pouvaient se produire comme vous l'avez décrit ci-dessus.

Bon, cette fois, je pense que je suis vraiment prêt pour attaquer la phase codage proprement dite...

J’espère que la paire de valeurs <Animals>, <House of rising sun> n’est quand même présente qu’une seule fois dans la table EXTRAIT. Qu’en est-il ?

Oui, bien sur !

[Mike888]

J'ai néanmoins encore une question :

Vous vous basez sur quoi pour définir ce que vous mettez dans "extraitcode" ? Parce que dans mon cas, je vais avoir des milliers d'extraits à encoder et j'aimerais que ça soit le plus automatisé possible; est-ce qu'on ne pourrait pas simplement concaténer Artiste et Titre ensemble avant d'affecter le résultat à "extraitcode" ?

[fsmrel]

J'ai néanmoins encore une question :

Vous vous basez sur quoi pour définir ce que vous mettez dans "extraitcode" ? Parce que dans mon cas, je vais avoir des milliers d'extraits à encoder et j'aimerais que ça soit le plus automatisé possible; est-ce qu'on ne pourrait pas simplement concaténer Artiste et Titre ensemble avant d'affecter le résultat à "extraitcode" ?

Concaténer, ça fait un peu redondant. On peut aussi virer extraitcode et déclarer la paire {artiste, titre} comme clé alternative : je regarderai l'impact sur l'ensemble.

[Mike888]

Donc, si je résume, voici les corrections à apporter par rapport à tout ce qui a été posté dans les messages #18 à #21 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE BLINDTEST 
(
        blindtestId      INT  IDENTITY      NOT NULL
      , Numero           VARCHAR(8)         NOT NULL
      , animateurId      INT                NOT NULL 
    , CONSTRAINT BLINDTEST_PK PRIMARY KEY (blindtestId)
    , CONSTRAINT BLINDTEST_AK UNIQUE (Numero)
    , CONSTRAINT BLINDTEST_ANIMATEUR_FK FOREIGN KEY (animateurId)
          REFERENCES ANIMATEUR (animateurId)
) ;

• "En passant, vous pouvez remplacer VARCHAR par CHAR."
• "J’ai omis de définir l’attribut Artiste, mais je l’ajouterai."
• "On peut aussi virer extraitcode et déclarer la paire {artiste, titre} comme clé alternative : je regarderai l'impact sur l'ensemble. "