Question

Databases

Terrier Television wants to create a database of its TV series recordings. Each Series consists of 13 episodes. Actors may appear in more than one series. Terrier TV wants the database to be searchable be series, episode and actor.

1. Why would a relational database be the most appropriate solution for this?
2. List the tables and attributes for when the database is implemented in Access and identify the primary key and the foreign key for each table.

Answer 1

First Part:

A relational database be the most appropriate solution for the given situation due to the following reasons:

(i) Relational database will provide a simple and easily understandable model.

(ii) It will provide non-repetitive accurate data.

(iii) It will provide easy and effective data access.

(iv) The integrity of the database will be most secured if relational database is used.

(v) It will provide flexibility.

(vi) The database will be normalized as relational database removes redundancy.

(vii) Any modification for the future can be made easily.

(viii) Relational database will provide a very good security.

Second Part:

The tables and attributes will be following (primary keys are underlined):

Actor (actor_id, actor_first_name, actor_last_name, age, gender)

Casting (series_id, episode_id, actor_id (FK), role)

In the casting table, both the attributes series_id and episode_id will form a composite primary key as both are required to uniquely identify every entry of the table. the actor_id will be a foreign key which will refer to the primary key of the table Actor (actor_id).

Direction (director_id, director_name)

TV_Series (series_id, episode_id, episode_name, duration, rating, date_of_release)

In the TV_Series table, both the attributes series_id and episode_id will form a composite primary key as both are required to uniquely identify every entry of the table.

Please comment in case of any doubt.
Please upvote if this helps.