Assignment Information

The relations and constraints translated strictly from Assignment 2 ER diagram is shown here

However, there is over-constraint in A2's ER diagram. In order to support future activities in the Forum, there is some changes in the relational and physical schema that will actually be used for this assignment.

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

1. List each user's username, nickname and contact information;
2. For each host whose contact information include a gmail account (i.e., the contact information contains "@gmail.com", list the host's username, nickname and his/her sign up date in the format of "Month dd, yyyy".
3. For each channel created on or after March 15th, 2020, list the channel's name, description and its creator's username and contact information.
4. For each active subscription, list the subscriber's username, nickname and contact information, the subscribed channel's channel name and description.
5. For each user who posted at least one message on September 22nd, 2021, in any channel created by a host with the username "viuadmin", list the user's username, nickname and contact information. This query shouldn't return any duplicates.

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 A3 under CSCI 370's assessment and assignments tab.