CODEWITHSUNDEEP
relational-databasefunctional-dependencies
Median Hilal
Median Hilal

Reputation: 1531

Relational Model: Multiple, overlapping functional dependencies

Let's consider the following relation which models the customers somewhere

R1 = (customer_num, birth_city, living_city, birth_city_location, living_city_location)

where birth_city is the city that customer was born in while living_city is the city where the customer lives in. Additionally we consider that one city value determines one location value.

and let's consider we have the following list of functional dependencies:

customer_num --> birth_city
customer_num --> living_city

birth_city --> birth_city_location
living_city --> living_city_location

A best decomposition to achieve normal forms will be:

(customer_num, birth_city, living_city)
(birth_city, birth_city_location)
(living_city, living_city_location)

This means creating two relations for the city.

Indeed, I think that one relation is enough because if we know city we know its location and these two relations mean data redundancy. But if we consider birth_city = living_city in the basic relation we will lose the semantic that birth_city may be different from living_city for each customer.

Considering two relations for city and location is not fair, because their values may overlap and if we decide to change some city's location of the birth_city values and this city exists in the living_city values, then we leave the DB in an inconsistent state.

I can rephrase the question more generally: 

R = (A, X1, X2, Y1, Y2)

where

A --> X1
A --> X2

X1 --> Y1
X2 --> Y2

and we have a special situation that is:

if t1[X1] = t2[X2] then t1[Y1] = t2[Y2]

How to model the relation?

Upvotes: 1

Views: 449

Answers (1)

DrabJay
DrabJay

Reputation: 3099

Although your model does follow the Principle of Full Normalization it does not follow the Principle of Orthogonal Design i.e. you have two relations that have overlapping meanings.

If you were to follow this principle the relations would be:

Customers (customer_num, birth_city, living_city)
Cities(city, city_location)

such that:

ρcity/birth_city(∏birth_city(Customers)) ⊆ ∏city(Cities)
ρcity/living_city(∏living_city(Customers)) ⊆ ∏city(Cities)

where

∏: Projection
ρ: Rename

I would note there is not agreement about the precise definition of Orthogonal Design.

Upvotes: 1

Related Questions