The CTO of TopicalBirdscom is worried that Russian hackers h

The CTO of TopicalBirds.com is
worried that Russian hackers have been targeting the company, and she wants to tighten up
the security and integrity of the database to prevent further intrusions. As a step in that
direction, you have been asked to write a pair of stored procedures to support the site’s web
pages for chirping and for parroting chirps. Once these stored procedures are in place, the
CTO will revoke everyone’s INSERT privileges on the Chirp table and make the stored
procedures the only way to add new chirps by granting execution access (only) for the
procedures instead.

    a. Create and exercise a SQL stored procedure called NewChirp(…) that the
application can use to add a newly created (non-parroted) chirp to the database. The stored
procedure should automatically use the current date and time to set those fields of the new
chirp, and it should automatically generate the new chirp’s number by adding one to the
chirper’s previous highest chirp number.
i) Using the following skeletal stored procedure code as inspiration, use MySQL’s
stored procedure creation wizard by clicking the circled button as shown on the previous page
to create the NewChirp(…) stored procedure.
    /*CREATE PROCEDURE NewChirp(
      new_btag VARCHAR(30),
      loc_lat DECIMAL(10,6),
      loc_long DECIMAL(10,6),
     sentiment DECIMAL(2,1),
    content VARCHAR(255))
    BEGIN
    DECLARE new_cno INT(11);
   SET new_cno = ( SELECT … );
    INSERT INTO Chirp ( … ) VALUES ( … );
    END ; */

Cno number btag string id number aid number caption string picture string wtag string Bird btag string gender string first name string last name string birthdate date income string Bird Listen tag string btag string Chirp btag String Cno number date date location latitude numbe location longitude numbe parrots btag string parrots cno number sentiment number text string Interest tag string id number level number Peacock ptag string variety string Topic d number name string description string Topic Listen tag string id number Use tag string email string password string signup date date address number string address street string address city string address state string address country string an arm arm am am an be be be CO CO dfo df

Solution

CREATE PROCEDURE NewChirp(
      new_btag VARCHAR(30),
      loc_lat DECIMAL(10,6),
      loc_long DECIMAL(10,6),
     sentiment DECIMAL(2,1),
    content VARCHAR(255))
    BEGIN
    DECLARE new_cno INT(11);
   SET new_cno = ( SELECT MAX(cno) FROM Chirp );
    INSERT INTO Chirp (btag,cno,date,location_latitude,location_longitude,sentiment,text) VALUES (new_btag,(new_cno+1),UNIX_TIMESTAMP(),loc_lat,loc_long,sentiment,content);
    END ;