+1 vote
148 views

Consider the following ER diagram:

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

1. 2
2. 3
3. 5
4. 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?

edited | 148 views
0
2 is correct. because

1:1  relation with total participation at atleast one side can be merged.
0

@kumar.dilip

Please check my doubt and help if possible..

+2

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?

0

@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 :)

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

+1 vote