The following is the script for the SQL: set sql_safe_updates=0; drop table if exists message; drop table if exists person; create table person ( pid integer primary key, pname varchar(20) not null, msgs_sent integer default 0, msgs_rcvd integer default 0 ); create table message ( msg varchar(200) not null, sendtime datetime, senderid integer, receiverid integer, constraint message_pk primary key (sendtime,senderid,receiverid), constraint message_fk1 foreign key (senderid) references person(pid), constraint message_fk2 foreign key (receiverid) references person(pid) ); DROP TRIGGER IF EXISTS MESSAGE_SEND; set sql_safe_updates=0; DELIMITER $$ CREATE TRIGGER MESSAGE_SEND BEFORE INSERT ON message FOR EACH ROW BEGIN update person set msgs_sent = msgs_sent+1 where pid=new.senderid; update person set msgs_rcvd = msgs_rcvd+1 where pid=new.receiverid; END $$ DELIMITER ; In SQL script create a trigger that fires on deletes to 'message', to keep the sent/received counts correct even when messages are deleted. Test it by a. Creating some message from BATMAN0 to BATMAN1 b. Doing SELECT * FROM person WHERE pid=1000; c. Deleting the message you just created. d. Doing SELECT * FROM person WHERE pid=1000; which should show the mesgs_sent decreasing by 1.

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Related questions

Question

The following is the script for the SQL:

set sql_safe_updates=0;

drop table if exists message;

drop table if exists person;

create table person ( pid integer primary key, pname varchar(20) not null, msgs_sent integer default 0, msgs_rcvd integer default 0 );

create table message ( msg varchar(200) not null, sendtime datetime, senderid integer, receiverid integer, constraint message_pk primary key (sendtime,senderid,receiverid),

constraint message_fk1 foreign key (senderid) references person(pid),

constraint message_fk2 foreign key (receiverid) references person(pid) );

DROP TRIGGER IF EXISTS MESSAGE_SEND;
set sql_safe_updates=0;
DELIMITER $$
CREATE TRIGGER MESSAGE_SEND BEFORE INSERT ON message
FOR EACH ROW
BEGIN
update person set msgs_sent = msgs_sent+1 where pid=new.senderid;
update person set msgs_rcvd = msgs_rcvd+1 where pid=new.receiverid;
END $$
DELIMITER ;

In SQL script create a trigger that fires on deletes to 'message', to keep the sent/received counts correct even when messages are deleted. Test it by

a. Creating some message from BATMAN0 to BATMAN1

b. Doing SELECT * FROM person WHERE pid=1000;

c. Deleting the message you just created.

d. Doing SELECT * FROM person WHERE pid=1000; which should show the mesgs_sent decreasing by 1.

Expert Solution