Question

Consider an Entity-Relationship (ER) model in which entity sets E₁ and E₂ are connected by an m : n relationship R₁₂. E₁ and E₃ are connected by a 1 : n (1 on the side of E₁ and n on the side of E₃) relationship R₁₃.

E₁ has two single-valued attributes a₁₁ and a₁₂ of which a₁₁ is the key attribute. E₂ has two single-valued attributes a₂₁ and a₂₂ of which a₂₁ is the key attribute. E₃ has two single-valued attributes a₃₁ and a₃₂ of which a₃₁ is the key attribute. The relationship do not have any attributes.

If a relational model is derived from the above ER model, then the minimum number of relations that would be generated if all the relations are in 3NF is _______.

Answer 1

Concept:

A relation is in 3NF for every functional dependency if X -> A  and one of the following condition holds true .

1) X is a superkey  or

2) A is a prime attribute.

Explanation:

E₁ has attributes a₁₁ and a₁₂. a₁₁ is the primary key.

Entity E₂ has attributes a₂₁ and a₂₂ where a₂₁ is the primary key.

Entity E₃ has attributes a₃₁ and a₃₂ where a₃₁ is the primary key.

R₁₂ is many to many relationship between E₁ and E₂. So, R₁₂ contains attributes a₁₁ and a_21.

R₁₃ is a one to many relationship between E₁ and E_3. R₁₃ contains attributes a₁₁ and a₃₁

In this we have 5 tables 3 for three entities and 2 for two relation.

But we have to find minimum number of tables or relations considering if all the relations are in 3 NF.

Here, we have to keep a separate table for R₁₂ because if we combine it with any other relation it will violates 1 NF and hence 3NF. Because it will contain composite key since it is a many to many relation.

But we can combine relation R₁₃ with entity E₃.

R₁₃E₃ (a₁₁, a₃₁, a₃₂)

It will not violates 3 NF . Because here a_11, a₃₁, a₃₂ is the key.  Here, it will not create any partial dependency. Hence it satisifies 3NF.

So, minimum 4 relations are required here.