ISRO2020-50

Question

If every non-key attribute functionally dependent on the primary key, then the relation will be in

• A. First normal form
• B. Second normal form
• C. Third normal form
• D. Fourth Normal form

Comments

I will claim this question..

---

Yes, it is not mentioned whether it is full or partial functional dependency.

The language of question is very poor as it can vary with different interpretations from $1-NF$, $2-NF$, and $3-NF$.

And if I don't presume anything then according to me $\mathbf{1-NF}$ is the only sure shot answer.

---

R: {X→Y,  Y→Z,  X→Z}

In above relation,  in FD Y→Z, Z is clearly Non key attribute and It is NOT dependent on Primary key Because Primary key is X.  So, Y→Z is not correct FD for given statement. Answer has to be 3NF.

Answer 1

$\underline{\textbf{Answer:}\Rightarrow}\;\mathbf{b.}$

1 NF: atomic values

2 NF: Full Functional Dependency.

3 NF: No transitive dependency.

BCNF: Super Key

Edit: The language of this question is poorly framed as it is not mentioned whether we have a partial or full functional dependency.

So, answer can very according to the interpretations between $1-NF$ to $2-NF$.

Comments

It should be in 3NF.

---

No 2 NF only.

---

They said fully functional dependent but not transitivity.

---

No non-prime attribute would depend on another non-prime attribute. Thus 3NF more suitable answer.

---

https://gateoverflow.in/15778/every-attribute-functionally-dependent-primary-relation

Check this.

---

Answer will be 2NF.

Consider the example R(a,b,c) ac->b, b->c

Here keys are ab, ac.

R is in 3NF and there is no fd where non prime attribute is dependent on primary key.

Consider R(a,b,c) a->b, b->c.

Here key is a.

R is in 2NF and in every fd non prime attribute is functionally dependent on primary key.

---

C depends in b as well right in your last example?

If every non-key attribute functionally dependent on the primary key

It doesnt mention that it can depend on another non key right?

@shashin

what is your view in this question?

---

@Debapaul

From a->b and b->c we get a->c using transitive property of fd.

Hence a->c must be satisfied in R. 

Hence c is functionally dependent on primary key a.

In 2NF , it says that every non prime attribute must depend on primary key either directly or transitively.

This differentiates 3NF and 2NF.

---

@SuvasishDutta

Consider this case, say we have a relation R={a,b,c,d,e} where $ab$ forms the primary key

So $ab->a,b,c,d,e$

Now say, $b->c$

here also $c$ is functionally dependent on $ab$ as $ab$ derives $b$

Here it is not even $2nf$ right?

---

Definition of 3NF:

1. The entity is in second normal form.
2. No non-prime (non-key) attribute is transitively dependent on any key i.e. no non-prime attribute depends on other non-prime attributes. All the non-prime attributes must depend only on the candidate keys.

I don't see anything in the question specifying the underlined bits above. It just says that every non-key is functionally dependent on a key - there could be transitive dependencies along the way. So 2NF is my go-to as well.

I can't think of a way to refute Suvashish's example above either. Another example if you're not convinced:

$R(X, Y, Z),  FD = \{ X \rightarrow Y, Y \rightarrow Z, X \rightarrow Z\}$

Both non-primes Y and Z are functionally dependent on the key X (as given in the question). But Z is also transitively dependent on X through Y (and the question says nothing prohibiting this).

---

@shashin

what about my comment just above yours?

---

In that example, $c$ is not functionally dependent on the key now is it ? It is functionally dependent on $b$ which is in turn functionally dependent on the key. The fact that a key derives everything in the table is a trivial fact. If you see the example I've tried to form - every attribute is functionally dependent on the key, in addition there is a transitive dependency.

---

@shashin

Now check @SuvasishDutta's  comment just above ours.

There also 

 

c is not functionally dependent on the key now is it ? It is functionally dependent on b which is in turn functionally dependent on the key.

---

Okay I retract my statement about Suvashish's example. You made your point. Can we get back to the question at hand ? Can you analyze my example and critique why it does not align with the question ?

---

@Debapaul

in your example , R is in 1nf because non prime attribute c is functionally dependent on b which is part of key.

moreover consider only non trivial fd for normal forms.

and in transitive fd, both sides of an fd must be non prime attributes for the relation to be in 2NF.

if lhs is part of key i.e. prime attribute and rhs is no prime attribute , R is in 1NF not in 2NF.

---

 there could be transitive dependencies along the way

If there is a transitive dependency along the way then again same statement (that i commented for @SuvasishDutta's post) applies here also right?

@SuvasishDutta also tried to prove the same thing that transistive dependency does not hold

---

@Debapaul 

Can you please explain your question once?

---

Let's take this example and close the discussion.

$\textbf{ {X -> Y, Y -> Z, X -> Z}}$

---

The question doesn't say that only non-prime attribute is dependent on the primary keys.

So the above Function dependency is fully correct.

$\therefore $ This relation is in $\mathbf{2NF}$ but not in $\textbf{3NF}$ as every non-key attribute is transitively dependent on the primary key.

Here $\mathbf X$ is the candidate key.

---

https://gateoverflow.in/15778/every-attribute-functionally-dependent-primary-relation

see this, and see the ans other than the selected one..

I think i have actually said the same in the above comments.

Isro framed the question wrong, best ans should be $1NF$

---

@Debapaul 

in that answer the relation is in 3 NF not in 1NF. 

In that relation keys are AC and AB.

Since AC->B and C->B.

---

R: {X→Y,  Y→Z,  X→Z}

In above relation,  in FD Y→Z, Z is clearly Non key attribute and It is NOT dependent on Primary key Because Primary key is X.  So, Y→Z is not correct FD for given statement. Answer has to be 3NF.

---

a relation is in 3nf if either lhs of depenedency is a super key OR rhs is prime attribute.

for bcnf lhs should be super key for all dependencies of the realtion.

here clearly given that rhs is  key which is prime attribute.

I dont see why anyone would argue with this question

Answer 2

Answer : C) Third normal form.

2NF: All non-prime(non-key) attributes are dependent on the primary key.

3NF: All the non-prime(non-key) attributes must depend only on the candidate/primary key.

Comments

It will be 2nf man not 3nf

---

Why it is not in 2NF:

Let's take simple example:

R: {X→Y,  Y→Z,  X→Z}

In above relation,  in FD Y→Z, Z is clearly Non key attribute and It is NOT dependent on Primary key Because Primary key is X.

Y→Z is not valid functional dependency for the given statement "If EVERY non-key attribute (like Z) functionally dependent on the primary key (Which is X)". 

Answer has to be 3NF

---

Counter example taken is not correct, I think.

whenever there is Non-key in RHS, then there has to be a primary key in LHS.( for all Fd)

Y -> Z is not satisfied qsn statement, but X->Z satisfies qsn statement.

why all people are saying 2NF.

Answer 3

It has to be in 3NF as here we have X->Y where x is primary key which will have prime attribute and it will determine non prime attribute which is condition well held in 3NF

Answer 4

If every non-key attribute functionally dependent on the primary key, then the relation will be in

Let us take a relation R(A,B,C). 

Consider a functional dependency: AB -> C (this satisfies the statement). Let us add another fd to this relation. A -> C. Here we have a partial dependency. So it cannot be 2NF. So the relation has to be in 1NF only.

So Basically for relation

R(A, B, C)

 AB -> C

 A -> C

Had they given "If every non-key attribute functionally dependent only on the primary key, then the relation will be in..." then answer would have been 3NF. 

Also this was discussed long before here : https://gateoverflow.in/15778/every-attribute-functionally-dependent-primary-relation?show=323328#a323328

 

So answer is option A:1NF