Assignment 2 Relational Algebra Points: 50 Instructions: For this assignment you must submit a document file (in pdf or doc format) with answers to the following questions. PART 1 (15 points): Consider the SOCIAL NETWORK relational database schema description provided below which is used to manage a social network, where: - Persons are the users of the social network They can have friends (a friend relation is symmetric meaning that if person 1 is friend of person2, the person2 is automatically friend of person 1) They can post multiple posts on his wall or in any other friend's wall The Posts have a PostID. It will be a numeric value that will start from 1 for EACH person, (the first post that a person posts will have always postid = 1) and will be incremented by one for each new post of that person. That means that two posts from different persons can have the same PostID. They can like posts with different types of likes: like, love, fun, wow, sad or angry. Only one "like" per post and user. Note: Like Type is a numeric ID and the LikeName is "like" or "wow" or "fun"... They can comment on the posts, they can comment more than one time in the same post. They can send multiple private messages to other people, that will be marked as read when the receiver opens the message. Note, datetime is stored in milliseconds, so it is not possible to post 2 or more posts, make 2 or more comments or send 2 or more private messages in the same exact time by the same user The relations are: PERSON (PersonNick, FullName, BirthDate) POST (PostID, PostPersonNick, WallPersonNick, Datetime, Content) FRIENDS (PersonNick1, PersonNick2) LIKE (PostID, PostPersonNick, LikePersonNick, Datetime, Type) LIKETYPE(Like Type, LikeName) COMMENT (CommentID, PostID, PostPersonNick, CommentPersonNick, Datetime, Text) PRIVATE_MESSAGE (Messageld, SenderPersonNick, ReceiverPersonNick, Datetime, Text, Read) (a) (5 points) Give a primary key for each relation. Are there any relations for which there is an alternate candidate key that you have not chosen as the primary key? If yes, mention the relations, candidate keys and the reason (if any) for your choice of the primary key. (b) (2.5 points) How would you change the previous answer (candidate keys and primary keys) if the datetime attribute, instead store the exact instant with milliseconds... only stored the day, month and year (allowing a user to have more than one post in the same day, or comments, etc...). Explain your answer. (c) (7.5 points) State all referential integrity constraints that should hold on these relations PART 2 (35 points - 5 points each question) Write the following queries in relational algebra, using the schema provided for the MOVIES database. ACTOR (actor_id, first_name, last_name, last_update) LANGUAGE (language_id, name, last_update) CATEGORY (category_id, name, last_update) FILM (film_id, title, description, release_year, language_id, length, rating, last_update) FILM_ACTOR(actor_id, film_id, last_update) FILM_CATEGORY (film_id, category_id, last_update) COMMENTS (review_id, film_id, reviewer_name, comment, score, last_update) To write these queries it is strongly recommended to use "RelAx" (view the document in Canvas about how to use it). The query (set of queries) generated in Relax can be copied and pasted as a solution (in fact that is what is expected). Note: In the assignment page in Canvas, it is attached a document (movie_relax.txt) with the test data to load in relax, and another document (expected_results.txt) with what is expected to have as a result on each of the queries. In that way, you can test your own query to check if it is correct or not, and if not, review it until you find the right solution. NOTE: The right solution must return what is asked, not just match with the expected result in the file. It needs to work as expected for any possible data and not only for the test data. A wrong query could, maybe, return the same correct result with the test data, but still be incorrect for other data so be careful about that. Your queries must not be "state-dependent", that is, they should work without modification even if another instance of the database is given or if the ID's change. That means don't use directly specific ID's in the query (THAT WILL DISCOUNT POINTS), the query will need to find the ID's based in the data provided. For example: If the data provided in the question is the "Movie Title", we don't know a priori the ID of that movie, we will need to write the query in a way that is able to use the movie title, and not directly the ID NOTE: THIS IS NOT MYSQL, this is RELATIONAL ALGEBRA. Any answer provided in Mysql will be graded with a 0. If a query is long, it is recommended to break it up into a series of queries with intermediate answers stored in temporary relations. Note: It is possible to write the queries without using relax, of course, but then you won't be able to check the results of the queries. In that case you can use abbreviations for Relational Algebra symbols such as S for SELECTION, P for PROJECTION, * for NATURAL JOIN, - for SET DIFFERENCE 1. Find all the Comments created by "Andrea12" with score lower than 50 2. Find the Film title from all the movies in the category "Horror" and length bigger than 180 minutes. 3. Find the last name of all the actors in the movies with at least a comment with a score greater than 80. 4. Find the Content and the Reviewer Name for each comment, about "ACE GOLDFINGER" only if the same reviewer has not commented about "ACADEMY DINOSAUR". 5. Retrieve the title of all the Movies in Italian and without actors with first name JOHN". 6. Find all the movie titles where and actor called "TOM" OR an actor called "BEN" acted, where there was another actor called "MARY" acting too. (That means movies with TOM and MARY, movies with BEN and MARY and movies with TOM, BEN and MARY) 7. Find the reviewers that have reviewed movies in all the categories