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.
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.
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.)
Express the following query in a single SQL update query against the relational schema defined above.
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: