Sql exercice trigger

Version imprimable

Bonjour à tous,

est-ce quelqu'un pourrait me confirmer ou me corriger cet exerice svp, merci.

donc c'est un schéma de données relatives à une entreprise réalisant la fabrication de produit :

Table Fabrication : une fabrication d'un produit

id : id de la fabrication
date : date de la fabrication
produit : id du produit fabrique
responsable : id du personnel responsable(clé étrangère sur Personnel.id)

Table Personnel : un membre du personnel de l'entreprise

id : id du personnel

Table Participation : participation d'un membre du personnel à une fabrication

personnel : id du personnel participant à la fabrication ( clé étrangère sur personnel.id)
fabrication : id de la fabrication à laquelle il participe ( clé étrangère sur fabrication.id)
(personnel,fabrication) -> clé primaire

la question est : Pour être responsable d'une fabrication, un membre du personnel doit être un des fabricants de celle-ci.

Mon raisonnement rajouter trigger à la table fabrication qui va récuperer l'id de fabrication ainsi que le responsable de la fabrication, est vérifie qu'il existe un tuple dans la table participation:
donc en gros un truc du style :
Code:

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 CREATE TRIGGER test BEFORE UPDATE,INSERT ON Fabrication FOR EACH ROW BEGIN DECLARE var integer; DECLARE prsnl integer; DECLARE fab integer; prsnl := NEW.personnel fab := NEW.fabrication select id into var from participation where personnel = persn and fabrication = fab Si aucune exception se leve laisse passer la requette, sinon si aucun tuple renvoye refuser la requete. END |

08/08/2017, 17h03
escartefigue

Bonsoir,

Il y a un souci de logique :
- la table participation utilise un contrainte de type référence vers la table fabrication, jusque là pas de souci, il faut juste que l'occurrence de fabrication soit créée avant l'occurrence de participation qui lui est rattachée.
- dans votre trigger, vous voulez vérifier que l'id fabrication soit présent dans la table préparation, c'est bien sur impossible en cas d'insert à cause de la contrainte de type référence qui précède
08/08/2017, 17h31
wear12

à oui bien vu, donc voici une autre solution

je rajoute d'abord une contrainte check à la table fabrication qui oblige que le responsable au depart est null
(responsable is null).

ensuite je garde le même trigger mais uniquement pour le update.
08/08/2017, 17h55
aieeeuuuuu

Bonjour,

Vous serez en effet de toutes façons contraint de procéder en plusieurs étapes :
1/ création de la ligne de fabrication (avec le responsable à NULL, car il ne pourra pas déjà exister dans PARTICIPATION comme expliqué par escartefigue)
2/ création des lignes dans la table PARTICIPATION (Au moins le responsable, et les autres le cas échéant)
3/ mise à jour de la ligne de fabrication afin de renseigner le responsable

Le problème du trigger, c'est qu'il faudra en créer plusieurs, sur les deux tables, afin de s'assurer que la règle de gestion est toujours respectée : il faudra par exemple un trigger pour interdire la suppression d'un ligne de participation si le personnel est renseigné comme responsable.

Il y a beaucoup plus simple : ajouter simplement une contrainte d'intégrité référentielle, qui veillera a ce que la règle de gestion soit vérifiée en toutes circonstances.

PS : une alternative :
Changer le modèle, en supprimant la colonne responsable de la table FABRICATION et en ajoutant une colonne EstResponsable dans la table PARTICIPATION, avec une contrainte d'unicité sur la couple (fabrication, EstResponsable). ça simplifiera les insertions (pas besoin de la mise à jour finale pour renseigner le responsable)
09/08/2017, 03h21
wear12

merci pour votre réponse, j'ai 2 petites "questions"

1)"Il y a beaucoup plus simple : ajouter simplement une contrainte d'intégrité référentielle, qui veillera a ce que la règle de gestion soit vérifiée en toutes circonstances." pouvez-vous m'expliquer parce que je ne vois pas comment.

2) la 2ème solution qui consiste à changer le modele, est a ajouter un attribut estResponsable à la table participation, dans le cas d'une participation où le membre n'est pas responsable l'attribut faudra null c'est ça?

Merci.
09/08/2017, 08h46
escartefigue

Bonjour,

Vu la présence de 2 FK (personnel et produit) dans la table Fabrication, cela signifie que votre modèle conceptuel était le suivant :
[PRODUIT] 0,n --- (Fabriquer) --- 1,1 [FABRICATION] 1,1 ---- (Participation) --- 0,n [PERSONNEL]

Or dans l'énoncé, il est mentionné :

Citation:

Envoyé par wear12

la question est : Pour être responsable d'une fabrication, un membre du personnel doit être un des fabricants de celle-ci.

Il devrait donc y avoir plusieurs personnes possibles pour une même fabrication, ce qui change tout !
Le MCD devient donc
[PRODUIT] 0,n --- (Fabriquer) --- 1,1 [FABRICATION] 1,n ---- (Participation) --- 0,n [PERSONNEL]
La relation "Participation" pourra porter l'attribut rôle.
Et comme la relation Participation possède une cardinalité max "n" de chaque coté, elle devient une table, dont la PK est id_fabrication + id_personne et qui a pour attribut le rôle de la personne.
Du coup, plus besoin de trigger ;)

Notez que par convention, on utilise des verbes à l'infinitif pour identifier les relations, ce qui donne :
[PRODUIT] 0,n --- (Fabriquer) --- 1,1 [FABRICATION] 1,n ---- (Participer) --- 0,n [PERSONNEL]
09/08/2017, 16h09
wear12

merci pour votre aide encore,

en faite je n'ai pas très bien compris vous me dites de mettre l'id de fabrication ainsi que l'id du responsable, de la table Fabrication comme clé primaire?
09/08/2017, 16h19
aieeeuuuuu

Citation:

Envoyé par wear12

1)"Il y a beaucoup plus simple : ajouter simplement une contrainte d'intégrité référentielle, qui veillera a ce que la règle de gestion soit vérifiée en toutes circonstances." pouvez-vous m'expliquer parce que je ne vois pas comment.

Je veux dire créer une contrainte d'intégrité référentielle (clef étrangère) sur la table FABRICATION (responsable, id) référençant la table PARTICIPATION (personnel, fabrication)
09/08/2017, 16h46
escartefigue

Avez vous noté ma remarque (réponse n°6) concernant l'incohérence entre la question posée et le modèle de données ?
09/08/2017, 16h59
wear12

mais dans ce cas là est-ce qu'on retombe pas dans le probleme de escartefigue à savoir qu'il n'y aura aucun tuple dans la table participation

par exemple si je veux insérer:
le tuple (8,6) dans la table fabrication
-> 8 : id de la fabrication
-> 6 : id du personnel

il faudrait qu'il existe un tuple avec comme id de fabrication 8 dans la table participation mais comme je viens de l'inserer c'est impossible qu'il est existe un tuple avec comme id de fabrication 8 dans la table participation.

escartefigue : oui mais je ne l'ai pas compris.
09/08/2017, 17h22
escartefigue

Citation:

Envoyé par wear12

escartefigue : oui mais je ne l'ai pas compris.

Vous aviez mentionné

Citation:

Envoyé par wear12

la question est : Pour être responsable d'une fabrication, un membre du personnel doit être un des fabricants de celle-ci.

Ca signifie que plusieurs personnes peuvent participer à une fabrication

Mais vous mentionnez aussi que la table FABRICATION avait 2 foreign Key, dont la FK identifiant la personne ce qui implique qu'il n'y a qu'une et une seule personne par fabrication, c'est contradictoire !

C'est pourquoi je vous propose la modélisation suivante, qui rend votre modèle conforme à la question posée, et rend le contrôle de participation du responsable (et du coup votre triger) inutile :
[PRODUIT] 0,n --- (Fabriquer) --- 1,1 [FABRICATION] 1,n ---- (Participer) --- 0,n [PERSONNEL]

Est-ce plus clair ainsi ?
09/08/2017, 17h28
aieeeuuuuu

si, il faudra insérer d’abord la ligne de fabrication sans responsable, puis ajouter les lignes dans la table participation et enfin mettre à jour le responsable de la fabrication.
09/08/2017, 17h30
aieeeuuuuu

Citation:

Envoyé par escartefigue

Mais vous mentionnez aussi que la table FABRICATION avait 2 foreign Key, dont la FK identifiant la personne ce qui implique qu'il n'y a qu'une et une seule personne par fabrication, c'est contradictoire !

Non, la deuxième FK désigne bien un personnel mais comme responsable (unique en effet), pas juste comme participant.
enfin c'est ce que j'ai compris
09/08/2017, 17h39
escartefigue

Citation:

Envoyé par aieeeuuuuu

Non, la deuxième FK désigne bien un personnel mais comme responsable (unique en effet), pas juste comme participant.
enfin c'est ce que j'ai compris

Ah.... (mode révélation is on :D) je comprends mieux :)
09/08/2017, 17h56
wear12

oui il y a plusieurs participation pour une même fabrication mais un membre ne peut pas participer 2 fois à la même fabrication.
09/08/2017, 22h01
StringBuilder

Personnellement, l'affirmation "le responsable doit être parmi les participants à la fabrication", je la comprendrais éventuellement "le responsable participe de facto à la fabrication".

Ainsi, sur INSERT, j'aurais un trigger qui ajouterais le responsable à la liste des participants de la fabrication.

Et ensuite, sur UPDATE, un second trigger qui se contente de vérifier que le responsable modifié est bien déjà présent dans la liste des participants (et éventuellement, comme pour le trigger INSERT, plutôt que de planter, l'ajoute dans la liste des participants)
09/08/2017, 22h04
StringBuilder

Citation:

Envoyé par wear12

oui il y a plusieurs participation pour une même fabrication mais un membre ne peut pas participer 2 fois à la même fabrication.

Ben ça c'est pas compliqué, une simple contrainte d'unicité sur (fabrication_id, personne_id) dans la table des participations permet de garantir cette règle.
10/08/2017, 10h07
aieeeuuuuu

Citation:

Envoyé par StringBuilder

Personnellement, l'affirmation "le responsable doit être parmi les participants à la fabrication", je la comprendrais éventuellement "le responsable participe de facto à la fabrication".

Ainsi, sur INSERT, j'aurais un trigger qui ajouterais le responsable à la liste des participants de la fabrication.

Et ensuite, sur UPDATE, un second trigger qui se contente de vérifier que le responsable modifié est bien déjà présent dans la liste des participants (et éventuellement, comme pour le trigger INSERT, plutôt que de planter, l'ajoute dans la liste des participants)

Mais il faudrait aussi des triggers sur la table PARTICIPATION afin de ne pas pouvoir supprimer un responsable, ou le modifier.
Comme je le disais, ici une simple contrainte d'intégrité référentielle suffit pour répondre au besoin, pourquoi mettre en place un système avec de nombreux triggers sur plusieurs tables ?...