Question

Consider the design of a database for a web site of a web site of videos that teach people how to do things by yourself, such as house renovation. We will call videos as DIY videos. First, all registered users can post DIY questions, such as “how to paint a wall”. Each DIY question is identified by a question ID, the question itself, and a list of tags (each tag is a lower case word) to annotate the question, and the date the question is posted,. For example, for the question “how to paint a wall”, we can add the tags “wall, paint, roller, brush”. A registered user can also post a DIY video (from the existing video website) to an existing DIY question. Each video is identified by a unique URL (use the URL when you click the share button at the video website page, a title and a description of the video, the date on which the video is posted to your database system. A registered user can search for videos by typing a question (ideally, support fuzzy search since a user might not be able to type the exact phrase in a question), all relevant video links will be returned as a list so that a user can click any video link in the result list to play that video. Each registered user is identified by a unique username, which must be an email, a password, a first name, a last name, the gender, and his/her birthday. A registered user can give at most one review for each video, but on a particular day, the user can post at most three videos. The review given by a user has a score of {“Poor”, “Fair”, “Good”, “Excellent”} and then a short remark. A user can also modify an existing review as well as deleting an existing review. Each user has a private favorite list of DIY videos(called my favorite DIYs). Each user can insert or delete a DIY video from that favorite list. When each video is shown, the DIY question will also be shown beside the title of the video, so that a user can click it to see other videos for this DIY question. While a video is on display, there is a button under the video called “Add to my favorite DIYs” so that a user can easily add this video to his/her favorite DIYs. The system has a distinguished user called “root” whose username is root (this is the only username that is not an email).

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

- 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

THANK YOU. Please give an upvote. If any queries or changes needed please comment will respond ASAP!!!!!!