Assignment Information

The following relational schema (primary keys are underlined) are used by the Forum Management as described in Assignment 1.

Users(username, nickname, contactInfo, signupDate, status)
Hosts(username)
Channels(channelName, description, creator, createTime)
ActiveSubscriptions(username, channel, subscribeTime)
Messages(messageID, messageBody, poster, channel, postTime, replyToMessage)
SubscribeHistory(username, channel, subscribeTime, unsubscribeTime)

This assignment is based on the above relational schema.

Many more details, including constraints, data types, etc, are captured in the SQL definitions of these tables.

You may use the supplied table definitions to create empty tables. You will then need to populate the tables (insert sample data) yourself in order to test your SQL queries.

Your Tasks:

Express each of the following queries in an equivalent single SQL query and Relational Algebra query respectively against the relational schema defined above. The extra requirement is that the SQL query must be a pure PSJ (projection, selection and join) one that doesn't have ANY sub-queries.

1. List the username and status of each user who has posted at least one message in the channel named "Tech News" and posted at least one message in the channel named "House Renovation".
   (There shouldn't be any duplicates in the result.)
2. List the name of each channel in which nobody has ever posted any messages.

Express each of the following queries in an equivalent single SQL query and Datalog query respectively against the relational schema defined above. (No extra requirement.)

3. List the username and contact information of each user who is actively subscribed to EVERY channel.
4. For each host, list the host's username and contact information, and the name and description of the first channel created by this host. (The first channel created by a host is the channel that has the earliest create time among all channels created by this host.)

Express the following query in a single SQL update query against the relational schema defined above.

5. For each user whose status is not "Active", copy move all this user's active subscription (plus the unsubscribe information) from ActiveSubscriptions table to the subscribeHistory table. Use the current date/time (literally written as current_date) as the unsubscribe time.
6. Remove all the active subscription of each user whose status is not "Active" from the activeSubscriptions table.

How to Submit

You are expected to include a legend to explain your relational algebra operators and Datalog logical operators used in this assignment if they are different from the ones used in our lectures, and to include your Datalog and SQL queries in a text file (with the extension name .txt or .sql), and to include your Relational Algebra queries in the same text file or in a separated PDF file.

Make your queries logically correct and easy to understand. Test run your SQL queries in Oracle to make sure that their syntax is acceptable by Oracle database engine.

You can submit your assignment solutions using one of the following two ways:

• Submit a hard copy before the due date/time.
• Login to VIU Learn, upload all your solution files to A6 under CSCI 370's assessment and assignments tab.