WSU Database Management Systems ER Diagram for A Website Exercise
I’m studying for my Computer Science class and need an explanation.
Consider the design of a database for a web site of a web site of youtube videos (say WSUDIY.com) that teach people how to do things by yourself, such as house renovation. We will call these youtube 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 youtube video (from the existing youtube.com website) to an existing DIY question. Each youtube video is identified by a unique URL (use the URL when you click the share button at the youtube page, such as https://youtu.be/buSv1jjAels) ,a title and a description of the youtube, the date on which the youtube is posted to your database system. A registered user can search for youtube 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 youtube links will be returned as a list so that a user can click any youtube link in the result list to play that youtube 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 youtube video, but on a particular day, the user can post at most three youtube 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 youtubes (called my favorite DIYs). Each user can insert or delete a DIY youtube from that favorite list. When each youtube is shown, the DIY question will also be shown beside the title of the youtube, so that a user can click it to see other youtubes for this DIY question. While a youtube is on display, there is a button under the video called “Add to my favorite DIYs” so that a user can easily add this youtube 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.