Question

Question 1 Design a database, satisfying the rules of third normal form (3NF), to represent the data in Figure 1. This data set contains information about different breakfast cereals and the companies that make them You can work with a pen and paper to decide how many tables wll be in your database and the contents of each table Company Cereal Colourful Company's country Region Kellogg's Kellogg' s Uncle Toby's Uncle Toby's Harraways Froot Loops Corn Flakes Vita Brits Weeties Oats USA USA Australia Australia New Zealand Yes North America North America Oceania Oceania Oceania No 0 Figure 1: The breakfast cereal data. Each row represents information about a kind of cereal and the company that makes it

Answer 1

I HOPE ITS HELPFUL TO YOU IF YOU HAVE ANY DOUBTS PLS COMMENTS BELOW..I WILL BE THERE TO HELP YOU...ALL THE BEST...

ANS ::-

AS FOR GIVEN DATA...

Normalization :

• Normalization in database helps to decompose large table into simple forms.
• After normalization duplicate data will be removed and consistent data will be stored in the table.

First Normal Form (1NF) :

• 1NF says all the columns in the table should be automic in nature.
• Here in 1NF duplicate columns and multivalued columns are not allowed.
• Given table is in the 1NF because all columns are automic in nature and no duplicate and multivalued columns exists in the table.

Second Normal Form (2NF) :

• 2NF says table should be in the 1NF.
• All non key column in the table should partially depends on primary key column.
• In 2NF partial dependencies are not allowed.

Here above table given in the question needs to normalize into 2NF to remove partial dependencies.Here need to identify tables like

• Company :Here some of the columns like Company's country and Region depends upon Company column. This table stores all company details.

Below is the table in 2NF.

1.Table Name :Company

Description :This table stores Company Name , Country and Region along with company ID.

Schema :Company (CompanyID,CompanyName,Country,Region)

Note :Here CompanyID column is added to uniquely identify each country.

FD :CompanyID $\rightarrow$CompanyName,Country,Region

Below is the table data

CompanyID	CompanyName	Country	Region
1	Kellogg's	USA	North America
2	Uncle Toby's	Australia	Oceania
3	Harraways	New Zealand	Oceania

Note :Remaining column in the table used in the 3NF.

Third Normal Form (3NF) :

• 3NF says table should be in the 2NF.
• All non key column in the table should depends upon non key column in the table.
• In 3NF transitive dependencies are not allowed.

Here above table needs to normalize into 3NF to remove transitive dependency.Also primary key of one table is used as foreign key in another table.

Below are tables in 3NF.

1.Table Name :Company

Description :This table stores Company Name , Country and Region along with company ID.

Schema :Company (CompanyID,CompanyName,Country,Region)

Note :Here CompanyID column is added to uniquely identify each country.

FD :CompanyID $\rightarrow$CompanyName,Country,Region

Below is the table data

CompanyID	CompanyName	Country	Region
1	Kellogg's	USA	North America
2	Uncle Toby's	Australia	Oceania
3	Harraways	New Zealand	Oceania

2.Table Name :CerealDetails

Description :This table stores cereal details such as Cereal name and Color details.

Schema :CerealDetails(CerealID,Cereal,Colourful,CompanyID)

FD :CerealID,CompanyID $\rightarrow$Cereal,Colourful

Below is the table data

CerealID	CompanyID	Cereal	Colourful
1	1	Froot Loops	Yes
2	1	Corn Flakes	No
3	2	Vita Brits	No
4	2	Weeties	No
5	3	Oats	No

I HOPE YOU UNDERSTAND...

PLS RATE THUMBS UP....ITS HELPS ME ALOT...

THANK YOU...!!