For a simple BBS (Bulletin Board System) we use the following SQL statements to create two tables: one storing all posted messages and the other users who can post them.



CREATE TABLE Message (

mesgid INTEGER,

poster INTEGER,

subject CHAR(50),

body CHAR(255),

postdate DATETIME,

PRIMARY KEY mesgid,

FOREIGN KEY poster REFERENCES User (userid)

ON DELETE CASCADE

ON UPDATE CASCADE

)

CREATE TABLE User (

userid CHAR(50),

password CHAR(50),

email CHAR(50),

status CHAR(1),

PRIMARY KEY(userid)

)

(a) There is an error in one of the above statements. Point out the error, explain why it is
wrong and correct the error by rewriting that SQL statement.

(b) Suppose there is a user with userid John in the database who has posted 100 messages.
What will the DBMS do if we delete John from table User?
What if we change John's userid to Michael?

(c) Write an SQL statement to create a view of those messages with all their attributes that
are posted by 'John'.

(d) Write an SQL statement to create a domain such that the status attribute can only take
two values, i.e., 'j' and 's'.

(e) Suppose occasionally the system will post some announcement messages, but unfortunately, the system is not a user (thus it does not appear in the User table). How can you
allow these messages being posted while not adding a ?system user? and not violating
the foreign key constraint?

(f) One desirable advanced feature of the BBS system is that each user can post messages
not only to the public, but also to a subset of other users that are explicitly specied by
userid when the message is posted. How would you change the denitions of the above
two tables so that this new feature can be implemented? (You may introduce other tables
if necessary.)

(a) In the tableMessage, poster is of type INTEGER and it references userid in User which
is of type CHAR(50). The poster column type in the table Message should be changed
to CHAR(50) for it to be consistent.
Also, the userid in User could be changed to INTEGER. (theoretically still correct)

(b) As specied in CREATE TABLE Message, if we delete John from the table User, all
messages with the poster = 'John' will be deleted.
When John's user id updated, tuples in the Message table for which the poster attribute
has the value 'John', will be updated to John's new userid i.e. Michael.

(c) CREATE VIEW JohnsMessage AS
SELECT * FROM Message WHERE poster = 'John';

(d) CREATE DOMAIN StatusValue CHAR(1)
CHECK ( VALUE IN ('s', j') );

(e) SQL allows foreign keys to have null values. So poster can be null in Message. Therefore,
the current structure already supports the requested feature.

(f) Introduce a new table say PostMessageTo. This table has the following structure:



CREATE TABLE PostMessageTo (

mesgid INTEGER,

receiver CHAR(50),

FOREIGN KEY poster REFERENCES User (userid),

FOREIGN KEY receiver REFERENCES User (userid)

)

Now for every message there is a list of users to whom the message is to be sent, which
is maintained in the PostMessageTo table.