Question

The Relation below stores information about weather observations made in the Albany area. You can assume that there are many observations made each day, that observer names are all unique, and that each observer measures the weather at just one location per day. Convert the relation them into a 3NF model and show the referential relationships. For this problem (And only this problem) don't add any additional fields

Answer 1

Step1: Check if data is in 1NF.

Data is already in 1NF.

Step2: Check if data is in 2NF

Data is not in 2NF, as there is partial dependency in data i.e.

ObserverName -> ObserverPhone, ObserverAddress

We need to convert it to 2NF.

Break table into 2 different tables to follow 2NF rules.

ObserverTable(ObserverName, ObserverPhone, ObserverAddress)

DataTable(Date, ObserverName, Location, Time, CloudCover, Temperature, Humidity)

So now data is in 2NF.

Step3: Check if data is in 3NF

Data is already in 3NF now as there is no transitive dependency in both tables.

Finally 3NF relations are:

ObserverTable(ObserverName, ObserverPhone, ObserverAddress)

DataTable(Date, ObserverName, Location, Time, CloudCover, Temperature, Humidity)

Explanation:

Firstly, we check that if the data is in the 1NF than the data is already in 1NF either it moves to the next step.

Than, we again check the that if the data is in the 2NF than the data is already in 1NF either it ask for its name, phone, and address and then convert that data in the 2NF and then break the table in to 2 different table and follow some rules and now data is in the 2NF.

Then, check data for 3NF.