1

This is a query where a Ternary relationship is being modelled, but the main query is about how to check the parameters of a Stored Procedure.

-- Each piece of Work presented at an Event may have upto 5 (Person who are) Discussants.
-- This is a Ternary Relationship being modelled as multiple Binary Relationships.

CREATE TABLE Work(id INT NOT NULL AUTOINCREMENT,
                  Work_Title VARCHAR(500),
          CONSTRAINT Work_PK PRIMARY KEY (id));
      
CREATE TABLE Event(id INT NOT NULL AUTOINCREMENT,
                   Event_Title VARCHAR(100),
                   CONSTRAINT Event_PK PRIMARY KEY (id));

CREATE TABLE Work_at_Event(id INT NOT NULL AUTOINCREMENT,
                   Work_id INT NULL,
               Event_id INT NULL,
               CONSTRAINT Work_at_Event_PK PRIMARY KEY (id),
               CONSTRAINT Work_at_Event_Candidate_Key UNIQUE (Work_id,Event_id));
                    -- (Work_id,Event_id) is a Candidate key    

CREATE TABLE Person(id INT NOT NULL AUTOINCREMENt,
                First_Name VARCHAR(100),
        Last_Name VARCHAR(100),
                CONSTRAINT Person_PK PRIMARY KEY (id));         

CREATE TABLE Discussant(id INT NOT NULL AUTOINCREMENT,
           Work_at_Event_id INT,      
           Person_id INT,
       CONSTRAINT Discussant_PK PRIMARY KEY (id),
       CONSTRAINT FOREIGN KEY (Work_at_Event_id) REFERENCES Work_at_Event(id),
       CONSTRAINT FOREIGN KEY (Person_id) REFERENCES Person(id),
       CONSTRAINT Discussant_Candidate_Key UNIQUE (Work_at_Event_id,Person_id));
       );
   
Stored Procedure :- Discussant_Add

DELIMITER //                      
CREATE PROCEDURE Discussant_Add (_Work_at_Event_Id INT,_Discussant_1 INT,_Discussant_2 INT,_Discussant_3 INT,_Discussant_4 INT,_Discussant_5 INT)

-- How do I check that _Discussant_1,...,_Discussant_5 are all Distinct and NOT NULL?
-- Here is my attempt.I am checking for NOT NULL, I don't know how to check if they are all Distinct.
-- See below for the answer to the above query.
BEGIN
    IF NOT NULL Discussant_1
    INSERT INTO Discussant VALUES (_Work_at_Event_id,_Discussant_1);
    ENDIF;
    IF NOT NULL Discussant_2
    INSERT INTO Discussant VALUES (_Work_at_Event_id,_Discussant_2);
    ENDIF;
    IF NOT NULL Discussant_3
    INSERT INTO Discussant VALUES (_Work_at_Event_id,_Discussant_3);
    ENDIF;
    IF NOT NULL Discussant_4
    INSERT INTO Discussant VALUES (_Work_at_Event_id,_Discussant_4);
    ENDIF;
    IF NOT NULL Discussant_5
INSERT INTO Discussant VALUES (_Work_at_Event_id,_Discussant_5);
ENDIF;
END//
DELIMITER ;

-- Inspired by the answer by nbk I have rewritten the function Discussant_Add.

DELIMITER //                      

CREATE PROCEDURE Discussant_Add (_Work_at_Event_Id INT,_Discussant_1 INT,_Discussant_2 INT,_Discussant_3 INT,_Discussant_4 INT,_Discussant_5 INT)

BEGIN
    IF NOT NULL _Discussant_1 and _Discussant_1 NOT IN (_Discussant_2,_Discussant_3,_Discussant_4,_Discussant_5)
    INSERT INTO Discussant VALUES (NULL,_Work_at_Event_id,_Discussant_1);
ENDIF;

    IF NOT NULL _Discussant_2 and _Discussant_2 NOT IN (_Discussant_3,_Discussant_4,_Discussant_5)
    INSERT INTO Discussant VALUES (NULL,_Work_at_Event_id,_Discussant_2);
    ENDIF;

    IF NOT NULL _Discussant_3 and _Discussant_3 NOT IN (_Discussant_4,_Discussant_5)
    INSERT INTO Discussant VALUES (NULL,_Work_at_Event_id,_Discussant_3);
    ENDIF;

    IF NOT NULL _Discussant_4 and _Discussant_4 NOT IN (_Discussant_5)
    INSERT INTO Discussant VALUES (NULL,_Work_at_Event_id,_Discussant_4);
    ENDIF;

    IF NOT NULL _Discussant_5
    INSERT INTO Discussant VALUES (NULL,_Work_at_Event_id,_Discussant_5);
    ENDIF;
END//
DELIMITER ;


-- How do I write a function similar to Discussant_Add, which can 1. Read the current Disussants and
-- 2. Update the Discussants. 1. Is easy I will SELECT all Person which are in the Discussant table which correspond to the _Work_at_Event_id, but how to I update them ? How do I check that some Discussants exist, some are to be deleted and some are to be added and then do
-- the needful ? 3.The end goal is to ensure that on updating there are no more than 5 Discussants for each piece of _Work_at_Event_id.

Stored Procedure :- Discussant_Update

CREATE PROCEDURE Discussant_Update (_Work_at_Event_id INT,_Discussant_1 INT,_Discussant_2 INT,_Discussant_3 INT,_Discussant_4 INT,_Discussant_5 INT)
0

You code looks somewhat unfamiliar to me

CREATE DEFINER=`root`@`localhost` PROCEDURE `Discussant_Update`(_Work_at_Event_id INT,_Discussant_1 INT,_Discussant_2 INT,_Discussant_3 INT,_Discussant_4 INT,_Discussant_5 INT)
BEGIN
    DECLARE n  INT;
    SET n = 0;
    DELETE FROM Discussant WHERE id IN (SELECT id FROM (SELECT * FROM Discussant) d WHERE Work_at_Event_id = _Work_at_Event_id AND Person_id NOT IN (_Discussant_1,_Discussant_2,_Discussant_3,_Discussant_4,_Discussant_5));
    loop_Discussant:  LOOP
        IF  n =  6 THEN 
            LEAVE  loop_Discussant;
        END  IF;
        CASE n
            WHEN  1 THEN
               SET @Discussant = _Discussant_1;
            WHEN 2 THEN
               SET @Discussant = _Discussant_2;
            WHEN 3 THEN
               SET @Discussant = _Discussant_3;
            WHEN 4 THEN
               SET @Discussant = _Discussant_4;               
            ELSE
               SET @Discussant = _Discussant_5;
        END CASE;

        IF @Discussant IS NOT NULL  THEN
            IF NOT EXISTS (SELECT 1 FROM Discussant WHERE Work_at_Event_id = _Work_at_Event_id AND Person_id = @Discussant) THEN
                INSERT INTO Discussant VALUES (NULL,_Work_at_Event_id,@Discussant);
            END IF;
       END IF;
        SET  n = n + 1;
    END LOOP;

    SELECT * FROM Discussant;
END

This procedure would delete first all rows that are no in the new list and then add only the new once.

As you can see, i shortened the code a litlle bit, so that i must only correct 1 code and not 5.

| improve this answer | |
  • My apologies for the delay in the reply. Your reply is wonderful because I don't need 2 separate functions - Discussant_Add and Discussant_Update. Discussant_Update alone can Add as well as Update the Discussant_Table. However,Inspired by your answer I have rewritten Discussant_Add. Discussant_Add has a small advantage that it does not hit the database and should be faster than Discussant_Update. – user2338823 Jun 22 at 6:31
  • this actually verry fast, because of indexes and the smal number, but i am gad it works for you – nbk Jun 22 at 9:53
  • ok and thank you once again! – user2338823 Jun 22 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.