Question:
Consider two entity sets E1(A11, A12, A13) and E2(A21,A22,A23) with A11 and A21 as PK. A22 is a multivalued attribute. R12 is a many to many relationship with total participation on both side. What is the min number of relations required to convert this ER model to relational model in 3NF?
Consider two entity sets E1(A11, A12, A13) and E2(A21,A22,A23) with A11 and A21 as PK. A22 is a multivalued attribute. R12 is a many to many relationship with total participation on both side. What is the min number of relations required to convert this ER model to relational model in 3NF?
Show Hint
To quickly find the minimum number of tables from an ER diagram, count them up:
1. One table for each strong entity.
2. One table for each M:N relationship.
3. One extra table for each multivalued attribute.
4. Weak entities can sometimes be merged, but M:N relationships and multivalued attributes almost always require their own table.
Updated On: Feb 23, 2026
Hide Solution
Verified By Collegedunia
Correct Answer: 4
Solution and Explanation
Step 1: Understanding the Question:
We need to determine the minimum number of tables (relations) required to represent a given Entity-Relationship (ER) model in a relational database schema, ensuring the schema is in Third Normal Form (3NF).
Step 2: Key Conversion Rules:
1. Each strong entity set gets its own table.
2. Each many-to-many (M:N) relationship gets its own table.
3. Multivalued attributes require a separate table.
Step 3: Detailed Explanation:
Let's break down the ER model and apply the conversion rules:
- Entity Set E1: This is a strong entity set with simple attributes. It will be converted into one relation. - Table 1: E1(A11, A12, A13). The primary key (PK) is A11. Assuming A12 and A13 are non-transitively dependent on A11, this table is in 3NF.
- Entity Set E2: This entity set has a multivalued attribute, A22. This requires special handling.
- We create a table for the base entity without the multivalued attribute.
- Table 2: E2_base(A21, A23). The PK is A21. This table is in 3NF.
- We create a separate table to handle the multivalued attribute A22.
This table will contain the primary key of the parent entity (A21) and the multivalued attribute itself (A22).
- Table 3: E2_A22(A21, A22). The PK is the composite key (A21, A22). A21 is also a foreign key referencing E2_base(A21). This table is in 3NF.
- Relationship R12: This is a many-to-many (M:N) relationship between E1 and E2. M:N relationships always require a separate table.
- Table 4: R12_Rel(A11, A21). This table contains the primary keys of the participating entities.
The PK is the composite key (A11, A21).
A11 is a foreign key referencing E1(A11) and A21 is a foreign key referencing E2_base(A21).
This table is in 3NF. The "total participation" constraint means these foreign keys should be defined as NOT NULL, but it does not change the number of tables needed.
Step 4: Final Answer:
In total, we require four separate relations to represent the given ER model while adhering to normalization rules.
We need to determine the minimum number of tables (relations) required to represent a given Entity-Relationship (ER) model in a relational database schema, ensuring the schema is in Third Normal Form (3NF).
Step 2: Key Conversion Rules:
1. Each strong entity set gets its own table.
2. Each many-to-many (M:N) relationship gets its own table.
3. Multivalued attributes require a separate table.
Step 3: Detailed Explanation:
Let's break down the ER model and apply the conversion rules:
- Entity Set E1: This is a strong entity set with simple attributes. It will be converted into one relation. - Table 1: E1(A11, A12, A13). The primary key (PK) is A11. Assuming A12 and A13 are non-transitively dependent on A11, this table is in 3NF.
- Entity Set E2: This entity set has a multivalued attribute, A22. This requires special handling.
- We create a table for the base entity without the multivalued attribute.
- Table 2: E2_base(A21, A23). The PK is A21. This table is in 3NF.
- We create a separate table to handle the multivalued attribute A22.
This table will contain the primary key of the parent entity (A21) and the multivalued attribute itself (A22).
- Table 3: E2_A22(A21, A22). The PK is the composite key (A21, A22). A21 is also a foreign key referencing E2_base(A21). This table is in 3NF.
- Relationship R12: This is a many-to-many (M:N) relationship between E1 and E2. M:N relationships always require a separate table.
- Table 4: R12_Rel(A11, A21). This table contains the primary keys of the participating entities.
The PK is the composite key (A11, A21).
A11 is a foreign key referencing E1(A11) and A21 is a foreign key referencing E2_base(A21).
This table is in 3NF. The "total participation" constraint means these foreign keys should be defined as NOT NULL, but it does not change the number of tables needed.
Step 4: Final Answer:
In total, we require four separate relations to represent the given ER model while adhering to normalization rules.
Was this answer helpful?
0
0
Top Questions on Database Management Systems
- Compare CSV, Spreadsheets, and SQL as data storage formats. Which one is best for handling massive, interrelated datasets?
- CBSE Class X - 2026
- Data Science
- Database Management Systems
R(A B C D E)
F = A$\rightarrow$ BC, CD$\rightarrow$ E, E$\rightarrow$A
Which of the following is correct?
- GATE DA - 2026
- Computer Science and IT Engineering
- Database Management Systems
- Consider two relations R(A,B) and S(E,C). A is primary key and E is a FK referring A. Which of the following operations never violate FK constraint?
- GATE DA - 2026
- Computer Science and IT Engineering
- Database Management Systems
- Let $P, Q, R, S$ be relational schemas. Let ``$\rightarrow$'' indicate a functional dependency (FD) in this context. Which of the following best describes the meaning of a functional dependency?
- GATE CS - 2026
- Computer Science & Information Technology
- Database Management Systems
- Match LIST-I with LIST-II
LIST-I
(A) Primary Key
(B) Alternate Key
(C) Super Key
(D) Composite Key
LIST-II
(I) Composed with at least two attributes
(II) Can contain extraneous attributes
(III) Only one such key is permitted in a relation
(IV) Used when the primary key is not working- CUET (PG) - 2025
- Atmospheric Science
- Database Management Systems
View More Questions
Questions Asked in GATE DA exam
- Rishi and Swathi are students of Class 5. Pavan and Tanvi are students of Class 4. Rishi and Pavan are boys. Swathi and Tanvi are girls. These four students played a total of three games of chess. The games were played one after another. A player who lost a game did not participate in any more games. It was observed that:
1. The first game was the only game where two students of the same class played against each other.
2. The students of Class 5 won more games than the students of Class 4.
3. The boys won 2 games and the girl won 1 game.
The student who did not lose any game is:- GATE DA - 2026
- Logical Reasoning
Consider two distinct positive numbers \( m, n \) with \( m > n \). Let \[ x = n^{\log_n m}, \quad y = m^{\log_m n}. \] The relation between \( x \) and \( y \) is -
- GATE DA - 2026
- Logarithms
- Consider the supervised learning task. The objective function being minimized is \( f(w) = w \cdot x \), where \( w \in \mathbb{R} \) is the parameter. Stochastic Gradient Descent with learning rate of 0.10. Let \( w = 10.00 \) be the \( i^{\text{th}} \) iteration (\( w_i \)). The value of \( w \) at the end of iteration \( (i+1) \) is if \( x = 10 \) ________. Round off 2 decimal.
- GATE DA - 2026
- Machine Learning
- The value of \[ \sum_{j=0}^{\infty} \sum_{i=1}^{\infty} 2^{-j} 3^{-i} \] is ________.
- GATE DA - 2026
- Sequences and Series
- Consider the Ridge LRR is being used to learn prediction function \( y_{\text{pred}} = w^T x \) where \( w, x \in \mathbb{R}^2 \) & mean absolute error (MAE) is used to measure the prediction error. A weight of 0.20 is associated with the regularizer. At an intermediate step of training process assume that the parameter \( w = [-3.00, 4.00]^T \). In the next step for the I/P \( x = [1.00, 2.00]^T \), the predicted value of \( y \) is noted. Let the relation b/w \( x = [x_1, x_2]^T \) & the true value of \( y \) be \( y_{\text{true}} = x_1 + x_2 \). The value of the overall regularized loss for instance is __________ (upto 2 decimal).
- GATE DA - 2026
- Machine Learning
View More Questions