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 Relational Algebra query, Datalog query and SQL query respectively against the relational schema defined above.

1. For each original message (i.e., the reployToMessage field is null) posted in a channel named "Bit Coins" by a user with the nickname "Blockchain Expert", list its message body and post time in the format "dd/MON/yyyy, hh:mi:ss". (Your Relational Algebra and Datalog query can ignore the display format of the post time.)
2. List the username and contact information of each user whose status is NOT "Active", and the name of each channel in which this user had posted at least one message in year 2023. (There shouldn't be any duplicates in the result.)
3. For each message posted in a channel that is already NOT actively subscribed by its poster, list its message body, its post time, its poster's username, and the name of the channel it's posted in.
4. For each user whose status is "Active", but has NEVER posted any message in any channel, list the user's username and contact information.
5. For each host who has NEVER created any channel but has posted at least one message in any channel, list the host's username and contact information, and the name of each channel the host has subscribed to.

How to Submit

You are expected to include a legend to explain your relational algebra operators used in your assignment solution, and to include your Datalog and SQL queries in a text file (preferably with the extension name .txt), and to include your Relational Algebra queries (preferably in the expression tree format) in a separate PDF file.

Make your queries logically correct and easy to understand. Make sure that your SQL queries can be successfully executed 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 A4 under CSCI 370's assessment and assignments tab.