Question

1. 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

. a). Draw an E-R diagram for the system, in particular, use arrows or thick lines to represent constraints appropriately. Write down your assumptions and justifications briefly and clearly.

b). Translate the above E-R diagram into a relational model, i.e., write a set of CREATE TABLE statements. In particular, specify primary key, foreign key and other constraints whenever possible.

Answer 1

Below is the ER diagram of the given scenario. It consist of 2 entities which are connected via 3 relationship. Entities have certain attribute and primary key is mentioned as underlined attribute.

Cardinality is shown using crow's feet notation.