Question

mysql> DESC shows;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| name         | varchar(50) | YES  |     | NULL    |                |
| network_id   | int(11)     | YES  |     | NULL    |                |
| network_name | varchar(30) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

mysql> SELECT * FROM shows;
+----+----------------+------------+--------------+
| id | name           | network_id | network_name |
+----+----------------+------------+--------------+
|  1 | Raven's Home   |         10 | Disney       |
|  2 | Friends        |         30 | NBC          |
|  3 | The Good Place |         30 | NBC          |
|  4 | Young Sheldon  |         20 | CBS          |
+----+----------------+------------+--------------+

In MySQL using the table above, Assume each TV show is only associated with one network: the network that originally aired it (no syndication...). And each network has many different TV shows, of course.

Convert the table shows into an equivalent collection of tables in 3NF.

Use the following table representation in your answer, where primary keys are denoted by (pk) and foreign keys are denoted by (fk):

   Table_name (attribute_name (pk), attribute_name, attribute_name, attribute_name (fk), …).

For example, the table of shows currently is represented as:

   shows (id (pk), name, network_id, network_name)

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

Normalization in database :

• Normalization in database is used to decompose table into simple and smaller form.
• Due to normalization duplicate data will be removed and data consistency will be maintained.

First Normal Form (1NF) :

• Given table shows is in the first normal form because all columns in the table are atomic in nature.
• This table does not contains any column with multiple values or duplicate columns.

Second Normal Form(2NF) :

• Table shows need to normalize into 2NF to remove partial dependency.
• Partial dependency means non key column in the table must be depends on primary key column.
• Here need to identify new tables like
  • shows :This table stores show id and show name
  • network :This table stores network_id and network_name

below are tables in 2NF.

1.Table Name :Shows

Schema :shows(Id(pk),name)

FD:id==>name

Below is table data

id	name
1	Raven's Home
2	Friends
3	The Good Place
4	Young Sheldon

2.Table Name :network

Schema :network(network_Id(pk),network_name)

FD:network_id==>network_name

Below is table data

network_id	network_name
10	Disney
20	CBS
30	NBC

Third Normal Form (3NF):

• Above table needs to normalize into 3NF to remove transitive dependency.
• Transitive dependency means non key column in the table should depends upon non key column in the table.

Below are tables in 3NF.

1.Table Name :Shows

Schema :shows(Id(pk),name)

FD:id==>name

Below is table data

id	name
1	Raven's Home
2	Friends
3	The Good Place
4	Young Sheldon

2.Table Name :network

Schema :network(network_Id(pk),network_name)

FD:network_id==>network_name

Below is table data

network_id	network_name
10	Disney
20	CBS
30	NBC

3.Table Name :ShowsNetwork

Schema :ShowsNetwork(id(fk),network_Id(fk))

Below is table data

id	network_id
1	10
2	30
3	30
4	20

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.