Question

Suppose a relation looks like this: CUSTOMER(CustID, CustName, DateOfBirth, City, CityPopulation) Assume that City determines the CityPopulation.

Write the logical model for this relation in which each relation is at least up to 3rd NF.

Answer 1

dependencies are

CustID->CustName

CustID->DateOfBirth

CustID->City

CustID->CityPopulation

City->CityPoputation

candidate key is :CustID

above customer relation is in 1NF.

2NF->no partial dependencies present..means part of candidate key should not determine any other attribute..

here in custoer table we have only one attribute in candidate key..so there is no chance of partial dependencies ..

therefore above relation is in 2NF..

3NF: X->Y

2 conditions 1)x is superkey 2) y is prime attribute

either one of above 2 conditions satisfy then the relation is in 3NF

note:prime attributes: attribute which are present in candidate key

CustID->CustName -->CustID is super key ..therefore this dependency is in 3NF

CustID->DateOfBirth   -->CustID is super key ..therefore this dependency is in 3NF

CustID->City   -->CustID is super key ..therefore this dependency is in 3NF

CustID->CityPopulation -->CustID is super key ..therefore this dependency is in 3NF

City->CityPoputation --->City is not super key and City population is not prime attribute ...therefore this dependency violate 3NF condition.so split the table Customer

3NF TABLES ARE

R1(CustID, CustName, DateOfBirth, City)

DEPENDENCIES FOR R1   

CustID->CustName

CustID->DateOfBirth

CustID->City

CustID->CityPopulation   

R2(City, CityPopulation)

DEPENDENCIES FOR R2

City->CityPoputation