Consider an Entity-Relationship (ER) model in which entity sets \(E_1\) and…
2015
Consider an Entity-Relationship (ER) model in which entity sets \(E_1\) and \(E_2\) are connected by an \(m : n\) relationship \(R_{12}\). \(E_1\) and \(E_3\) are connected by a \(1 : n\) (1 on the side of \(E_1\) and \(n\) on the side of \(E_3\)) relationship \(R_{13}\).
\(E_1\) has two single-valued attributes \(a_{11}\) and \(a_{12}\) of which \(a_{11}\) is the key attribute. \(E_2\) has two singlevalued attributes \(a_{21}\) and \(a_{22}\) of which \(a_{21}\) is the key attribute. \(E_3\) has two single-valued attributes \(a_{31}\) and \(a_{32}\) of which \(a_{31}\) is the key attribute. The relationships 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 _______.
Attempted by 218 students.
Show answer & explanation
Correct answer: 4
Answer: 4 relations.
Relation for E1: (a11, a12) with a11 as the primary key.
Relation for E2: (a21, a22) with a21 as the primary key.
Relation for E3: (a31, a32, a11) with a31 as the primary key and a11 as a foreign key referencing E1. The foreign key implements the 1 (E1) to n (E3) relationship without creating an extra relation.
Relation for the m:n relationship between E1 and E2: (a11, a21) with the composite primary key (a11, a21) and foreign keys referencing E1(a11) and E2(a21).
Because none of the relationships have attributes, no additional relations are required. This yields a minimum of 4 relations in 3NF.
A video solution is available for this question — log in and enroll to watch it.