A3.4 Campus ADD_PLACE Procedure

Finally we will create a stored procedure for the CAMPUS database named ADD_PLACE(campus, cuisine, place, error, msg) for inserting a new campus cuisine place. The first three parameters are inputs and the last two are outputs. Use a transaction and the primary keys of the tables to reject entries that would violate the constraints.

ADD_PLACE will perform the following transaction. If the campus is in CAMPUSES and the cuisine is in CUISINES then if the place is not in PLACE_CUISINES it is added. The cuisine field can be empty if the place is already in PLACE_CUISINES. Finally, the campus and place are added to CAMPUS_PLACES.

Define the error outputs as: "0" for no error, “1” not a valid campus, “2” not a valid cuisine, “3” the place is already in PLACE_CUISINES as a different cuisine, “4” there's already a cuisine of that type at that campus. Start with this code:

/* PROCEDURE ADD_PLACE(IN campus1 VARCHAR(10), IN cuisine1 VARCHAR(25), IN place1 VARCHAR(50), OUT `error` INT, OUT msg VARCHAR(200)) */
BEGIN
    DECLARE msg2 VARCHAR(100) DEFAULT '';
    DECLARE cuisine2 VARCHAR(25) DEFAULT '';
    DECLARE place2 VARCHAR(50) DEFAULT '';    
    DECLARE valid INT(11) DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET msg=CONCAT(msg,' -- SQLEXCEPTION - ROLL BACK - nothing was inserted.');
        ROLLBACK;
    END;
    SET error = '0';
    SET valid = (**************);
    IF (valid = 0) THEN    /* Validate campus1 */
        SET msg = CONCAT(campus1,' is not a designated campus');
        SET error = '1';
    END IF;
    START TRANSACTION;
    IF (error='0') THEN
        SET cuisine2 = (**************); /* get cuisine2 if place1 in PLACES already */
        SET valid = (**************); /* confirm cuisine1 valid if it exists */
        IF (valid = 0 AND cuisine2 IS NULL) THEN
            SET msg = CONCAT('Cuisine does not exist or ',cuisine1,' is not a valid cuisine');
            SET error = '2';
        ELSEIF (valid = 1 AND cuisine2 IS NOT NULL AND cuisine1 <> cuisine2) THEN
            SET msg = CONCAT(place1,' exists as a ',cuisine2,' not a ',cuisine1,' cuisine');
            SET error = '3';
        ELSEIF (valid = 1 AND cuisine2 IS NULL) THEN
            SET msg = CONCAT('Unable to insert ',place1,', ',cuisine2,' into PLACE_CUISINES');

            **************;
            SET msg2 = CONCAT('Added ',place1,' as ',cuisine1,' to PLACE_CUISINES. ');
        END IF;
    END IF;
    IF (error='0') THEN
        IF (cuisine2 IS NULL) THEN 
            SET cuisine2 = cuisine1; /*setting this just makes the next SELECT statement easier*/
        END IF;
        SET place2 = (**************);
        IF (place2 IS NOT NULL) THEN
            SET msg = CONCAT(place2,' is already the designated favorite ',cuisine2,' for ',campus1);
            SET error='4';
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
        END IF;
    END IF;
    IF (error='0') THEN
        SET msg = CONCAT('Unable to insert ',place1,' for ',campus1);
  
        **************;
        SET msg = CONCAT(msg2,place1,' has been entered as the favorite ',cuisine2,' at ',campus1);
    END IF;
    COMMIT;
END

The input parameters are campus1, cuisine1, place1 followed by output parameters error, msg. 

The lines with asterisks (**************)indicate missing SQL statements.

Your assignment is to fill in the missing SQL statements so the procedure works as defined. Watch this video for help.

To test your routine, first make sure you are viewing the Database on the screen with the list of tables. Then click the Routines tab to access your ADD_PLACE routine. Click Execute and a form will appear to enter the input arguments for your routine.

To test error code “1”, enter a non-existent Campus for campus1 (e.g., XYZ), any Cuisine for cuisine1 (e.g., Burgers) and any Place for place1 (e.g., Teddy's Bigger Burgers).

To test error code “2”, enter an invalid Cuisine for cuisine1 (e.g., Hamberders) along with a valid Campus for campus1 (e.g., UHM) and a valid Place for place1 (e.g., Teddy's Bigger Burgers).

To test error code “3”, enter a Place for place1 that is not in the PLACE_CUISINES table (e.g., Win-Spuntino) along with a valid campus (e.g., UHM) and cuisine (e.g., Italian).

To test error code “4”, enter a place with a cuisine for a campus that already has a different place of that cuisine. First, make sure there is already a Burger place for UHM (e.g., Teddy’s Bigger Burgers) and that there is another Burgers place (e.g., Big City Diner Waialae). Then try campus1 = UHM, cuisine1 = Burgers, place1 = Big City Diner Waialae.

To test error code “0”, you should try something that you know will work. Assuming there is no favorite Nepalese restaurant yet for UHWO, you can try campus1 = UHWO, cuisine1 = Nepalese, and place1 = Himalayan Kitchen.