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.

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.