Question

2) 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.

(Date, ObserverName, Location, ObserverPhone, ObserverAddress, Time, CloudCover, Temperature, Humidity)

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)