(Solved) : Part 1 15 Points Consider Social Network Relational Database Schema Description Provided U Q44004502 . . .

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, Birth Date) • POST (PostID, PostPersonNick, WallPersonNick, Datetime, Content) • FRIENDS (PersonNick1, PersonNick2) • LIKE (PostID, PostPersonNick, LikePersonNick, Datetime, Type) • LIKETYPE(LikeType, LikeName) COMMENT (CommentID, PostID, PostPersonNick, CommentPersonNick, Datetime, Text) • PRIVATE MESSAGE (MessageID, Sender PersonNick, Receiver PersonNick, 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. Show transcribed image text 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, Birth Date) • POST (PostID, PostPersonNick, WallPersonNick, Datetime, Content) • FRIENDS (PersonNick1, PersonNick2) • LIKE (PostID, PostPersonNick, LikePersonNick, Datetime, Type) • LIKETYPE(LikeType, LikeName) COMMENT (CommentID, PostID, PostPersonNick, CommentPersonNick, Datetime, Text) • PRIVATE MESSAGE (MessageID, Sender PersonNick, Receiver PersonNick, 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.
Expert Answer
Answer to PART 1 (15 points): Consider the SOCIAL NETWORK relational database schema description provided below which is used to m…
Related