Database Project

Consider the design of a database for a web site of jokes. Each joke is identified by a unique joke ID, a title, a description of the joke, the date the joke is posted, and a list of tags (each tag is a single word in lower cases). Only registered users can post, read, comment on jokes. Each registered user is identified by a user ID, a password, a first name, a last name, an email, the gender, and his/her age. A user can give at most one review for each joke, and on a particular day, the user can post at most 5 jokes and 5 reviews. Meanwhile, a joke can have no or many reviews. The review given by a user provides a score of “Excellent, Good, fair, or poor” and then a short remark. A user can modify an existing review that she/he gave earlier. One special user called “root” is designated as the administrator user. The root user can ban any other user into a black list. Once banned, a user will not be able to login into the system unless he/she is unbanned from the blacklist subsequently. In addition, each user has two dynamic lists: my favorite friends and my favorite jokes, which can be modified by the user when necessary by insert, delete or update. . Some simple GUI interfaces are required for each functionality. All functionality must be performed via the interface of your system, direct SQL statement execution via any tools (MySQL workbench) can only be used for debugging purporses. Based on the reference solution given to Problem 1, a). Write a relational algebra expression to return those users who have posted “excellent” reviews but never “poor” reviews. b). Write a relational algebra expression to return those jokes that have been reviewed by all users. c). Write a relational algebra expression to return the jokes in which ‘John Smith’ gives “excellent” reviews while ‘David Liu’ gives ‘poor’ reviews. d). Write a relational algebra expression to return those jokes that have been reviewed by both male and female users. e). Write an SQL query to return the jokes that have been reviewed by all users of age 20. f). Write an SQL query to return the jokes that have been reviewed ONLY by users younger than 20 (not including 20). g). Write an SQL query to return the users who posted the most number of jokes on 1/1/2019. h). Define an SQL view JokesNum that gives the number of jokes each user posts on each day. i). Write an SQL CREATE ASSERTION statement to force the constraint “one user cannot post more than five jokes per day”. j). Write an SQL CREATE TRIGGER statement to force the constraint “a user cannot provide more than five reviews per day”.