MadeEasy Full Length Test 2019: Databases - Er Diagram

Question

Consider the following ER diagram:

How many number of relations are required for the above ER diagram?

• A. 2
• B. 3
• C. 5
• D. 1

Solution:

My doubt is:

Since $E_2$ isn’t involved in total participation with $E_1$ so on merging we might get 2NF violation.

Eg:

Let $E_1$ be:

<p1,q1>

<p2,q1>

<p3,q2>

$p \rightarrow q$

Let $E_2$ be:

<r1,s1>

<r2,s2>

<r3,s2>

<r4,s3>

$r \rightarrow s$

And p1 maps to r1, p2 maps to r2 and p3 maps to r3.

Merging with $E_2$ will give:

<r1,s1,p1,q1>

<r2,s2,p2,q1>

<r3,s2,p3,q2>

<r4,s3,NULL,NULL>

$pr \rightarrow sq$

$r \rightarrow s$

$p \rightarrow q$

2-NF violation.

So by default which case is to be considered when normalization form is not mentioned?? 1-NF?

Comments

MiNiPanda

It is a 1:1 relationship with total participation on one side. As P is pkey of E1 and R is pkey of E2, so on merging E1, E2 and R1, the dependencies that are applicable are: P->Q, P->R, R->S. So P is the pkey of the merged relation. As it isnt a composite key, so there wont be any violation of 2NF right?

Where are you getting a violation?

---

@Somoshree Datta 5

Yes I don't know what was I thinking!! -_- pr is the superkey! :3

I marked 2 and then changed it to 3 thinking this !

So only Null values will be there right?

Thanks :)

---

Ya R will contain null values for some values of P since its partial participation on the side of E2..