For a simple BBS (Bulletin Board System) we use the following SQL statements to

Question

For a simple BBS (Bulletin Board System) we use the following SQL statements to cre ate two tables: one storing all posted messages and the other users who can post them. CREATE TABLE MESSAGE mesgid INTEGER, poster INTEGER, subject CHARCS0) body CHARC255), postdate DATETIME, PRIMARY KEY mesgid, FOREIGN KEY poster REFERENCES USER (userid) ON DELETE CASCADE ON UPDATE CASCADE CREATE TABLE USER userid CHARC50), password CHARC5), email CHAR(50), status CHARC1) 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 re writing 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 (d) Write an SQL statement to create a domain such that the status attribute can only take (e) Suppose occasionally the system will post some announcement messages, but unfortu- are posted by John' two values, i.e., j and 's. nately 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? (t) 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 specified by userid when the message is posted. How would you change the definitions of the above two tables so that this new feature can be implemented? (You may introduce other tables if necessary.)

Explanation / Answer

a) The error is present in line: FOREIGN KEY poster REFERENCES User (userid).

Here, poster is of type INTEGER whereas userid is of type CHAR(50). This means that the type of the columns are not same. Hence, poster cannot contain the same values as userid.

The correct syntax is given below.

Hence, the correct SQL statement should be:

CREATE TABLE Message (

mesgid INTEGER,

poster CHAR(50),

subject CHAR(50),

body CHAR(255,

PRIMARY KEY mesgid,

FOREIGN KEY (poster) REFERENCES User (userid)

ON DELETE CASCADE

ON UPDATE CASCADE

);

b) DELETE * FROM User WHERE userid = 'John';

UPDATE User SET userid = 'Michael' WHERE userid = 'John';

c) CREATE VIEW messages_by_john AS SELECT * FROM Message WHERE userid = 'John';

d)

NOTE: As per Chegg policy, I am allowed to answer only 4 questions on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.