Question

1. Consider the following information to design a database for a catalogue of an online video service that offers access to popular TV series.

The following is the description of the application:

• Each series has a name and a description.
• Each series has many episodes. Each episode of a series has an episode number, the date it was first aired, and the length of the episode in minutes. The episode number uniquely identifies an episode with respect to the series, but two different series can have the same episode number.
• Viewers can comment on any episode. For each comment, we want to record its post date, content and the first name/last name of the viewer who commented.
• In this database:
  • The name of each series is unique.
  • A user can comment on one or more episodes of any series.
  • You may add extra columns if you need for primary keys.
  • You can model the user as a separate entity but registering the username with each comment is also acceptable.

Based on the above scenario, draw an ER or UML diagram. You can draw the diagram using https://app.diagrams.net/ or manually. Please mention if you make any assumptions.                                                                                                    [15 Marks]

Diagram

Answer 1

A series has two attributes- name and description. Name is the Primary key as it is unique.

An episode has attributes- episode number, series name, duration, date

As episode number can be same for two series, the primary key for an episode is a composite of series name and episode number.

A comment has attributes - username, date and content.

An episode can have comments from many users. Similarly, there can be comments from a user on different episodes.