Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
COMP2865 Exercise about Chapter
Question 1 (20 marks):
Figure below shows the ER diagram for a movie recommender system.
a. Complete the above diagram to capture the following constraints:
(1) Each user rates at least one movie.
(2) Some movie may not be rated by any user.
b. Complete the above diagram to capture the following constraints:
(1) Each user must rate exactly only one movie.
(2) There should not be any movie that is not rated by any user.
Question 2 (40 marks):
Draw an ER diagram for a music recommender system with the following properties.
1) Artist information
• For each artist, we keep the following information (suggested attribute names
are in parentheses): Artist id number (artist_id), artist name (artist_name), and
artist popularity (artist_pop). There can be artists with the same name.
• An artist must record at least one track.
• Each artist is uniquely identified by artist_id.
2) Album information
• For each album, we keep the following information: album id number
(album_id), album name (album_name), album popularity (album_pop), and
release date (release_date). Each album is uniquely identified by album_id.
• Each album must contain at least one track.
3) Track information
• Each track represents one piece of music that could be listened by users. For each
track, we keep the following information: track number (track_id), track name
(track_name), duration (duration), and tempo (tempo). Each track is uniquely
identified by track_id.
• Each track must belong to exactly one album.
• Each track can be recorded by one or more artists.
4) User information
• Anyone who registers in the music recommender system becomes a user of the
system. It is assumed that for each user, we can keep the following information:
user id (user_id), user name (user_name), age (age), nationality (nationality), and
the number of tracks the user has listened to (num_track_listened). Each user is
uniquely identified by user_id.
• Each user should listen to one or more tracks. Some tracks may be listened to by
one or multiple users, while some tracks may not be listened to by any user.
5) Follow information
• Each following behavior refers to a user following an artist in the music
recommender system. For each following behavior, we can record the date when a
user has started to follow an artist (follow_date).
• Users can follow as many (including 0) artists as s/he likes. Artists may be followed
by one or multiple users, while some artists may not be followed by any user.
Note: The popularity of an artist or an album is an integer from 0 to 100, and the
duration of a track refers to the track length in milliseconds (integer).
Question 3 (40 marks):
(1) Write SQL statements to convert the ER diagram you draw for Question 2 to
table definitions. Capture as many constraints as possible.
(2) State the constraints that your translation cannot capture, if any.