10,539 views

Indicate which of the following statements are true:

A relational database which is in $3$NF may still have undesirable data redundancy because there may exist:

1. Transitive functional dependencies
2. Non-trivial functional dependencies involving prime attributes on the right-side.
3. Non-trivial functional dependencies involving prime attributes only on the left-side.
4. Non-trivial functional dependencies involving only prime attributes.

edited

When it can’t be 3NF: Non-key → non-key

Prime attributes: attributes of CK.

Non-trivial dependency means for a functional dependency, X → Y, Y shouldn’t be a subset of X.

It’s mentioned non-trivial dependencies and prime attributes, so that means on LHS if we have a primary key, then we can’t have prime attributes on RHS as the definition of non-trivial dependency will be violated. So, let’s assume on LHS we have PK (hence, we can also say LHS has SK, or prime attributes) and on RHS we have non-prime attributes, so this format is also satisfying 3NF, right? So, option C is also satisfied and should be the correct answer?

Now, about option (D): if it’s a non-trivial dependency and contains only prime attributes then it does satisfy the definition of 3NF. So, can we say that option (D) is actually the union of what option (B) and option (C) say?

Conclusion : (B),(C) and (D) are correct. Please correct me if I am wrong.

edited

@ankit3009 Prime attributes are the part of candidate key not the full candidate key so we can’t treat it as super key. For ex if AB is a candidate key then A and B are prime attributes while keys containing AB both are the super keys.

(C) is saying prime attributes are on the LHS, this is not in 3NF because according to 3NF definition there shouldn’t be any transitive dependency meaning CK should not transitively determine non-prime attribute. So FD of the form Prime attribute → non-prime attribute satisfies option (C) but it is violating the 3NF definition,
Consider example R(A,B,C), AB is candidate key so A, B are prime attribute and C is non prime attribute, now there is a FD B->C which is as per option (C), we can create transitive dependency AB -> B -> C where a candidate key is transitively determining a non prime attribute hence violation of 3NF condition.

hence (C) is not an answer.

Yes @adad20. I understood and got some misconceptions cleared. Thank you :)

Correct Answer : A,B,D. $\color{red}{\text{ }}$

Partial Dependency, Transitive Dependency, 2NF, 3NF etc. are the concepts which are Wrongly Understood by majority of students.

Consider the following two GATE Questions:

https://gateoverflow.in/1260/gate-cse-2007-question-62-ugcnet-june2014-ii-47

https://gateoverflow.in/84054/gate-cse-1990-question-3-ii

For these two GATE questions, $\color{brown}{\text{Find Video Solution Below:}}$

Detailed Video Solution of GATE CSE 1990, 2007 Questions

For Correct Concepts of Normalization, Refer Standard Resources Or Watch the playlist below.

Normalization Complete Course Playlist

$\color{red}{\text{Misconception 1:}}$ A Relation in 3NF cannot have Transitive Dependency.

The above statement is FALSE.

(If this statement is True then in the following GATE question, Option $C$ would also be False. : https://gateoverflow.in/1260/Gate-cse-2007-question-62-ugcnet-june2014-ii-47)

$\color{green}{\text{Correct Concept 1:}}$ A Relation in 3NF may still have Transitive Dependency, but a relation in 3NF can Never have Transitive Dependencies where some Candidate Key is transitively determining some non-prime attribute.

$\color{blue}{\text{Third Normal Form :}}$
The relation must be in 2NF and Every non-prime attribute of the relation is non-transitively dependent on Each candidate key of the relation.

Restated, a relation $R$ is in Third Normal Form if it is in 2NF and NO non-key attribute of R is transitively dependent on any candidate key of $R$.

After Watching the above playlist, solve the following GATE Question:

https://gateoverflow.in/1260/Gate-cse-2007-question-62-ugcnet-june2014-ii-47

In this GATE 2007 Question, Option C is True because in a relation in 3NF, we can have Transitive Dependency where Some Prime Attribute is Transitively Dependent on a candidate key.

$\color{red}{\text{Misconception 2:}}$ A Relation in 2NF cannot have Partial Dependency.

$\color{green}{\text{Correct Concept 2:}}$ A Relation in 2NF may still have Partial Dependency, but a relation in 2NF can Never have a Dependency where some Candidate Key is partially determining some non-prime attribute.

Watch this playlist for Correct Understanding of Concepts, and correct your misconceptions:

Normalization Complete Course Playlist - Learn Correct Concepts

https://bohr.wlu.ca/cp363/notes/theory/18_3nf.php

Codd’s Original Research Paper for Normal Forms :

Refer Standard Books : Korth OR Navathe. Or watch Goclasses DBMS Course.

This answer deserves 100+ upvotes for uplifting the darkness of understanding of 2 NF and 3 NF. Kudos to you sir !!
I will say this particular answer as a one stop solution about normalization

A . Transitive functional dependency. Therefore it is not in 3NF

B. 3NF because right side is prime attribute

C. Not in 3NF because let us suppose ABC is a candidate key ( you can assume any candidate key with any no of attribute) .

now consider AB -> non-prime attribute  which shows it is not in 3NF

D. involving only prime attribute so the Right side should definitely contain only prime attribute. therefore it is in 3NF

so B, D is the answer

Edited on 24th Nov 2020 by Gurdeep saini

in option c- if candidate keys are AB and CD and B-->C is a functional dependency. Then  in this prime attribute is on the left side and aslo satisfies 3nf)

In your option D explanation you are contradicting with option C explanation, I think it's typo

D. involving only prime attribute so Right side should definitely contain only prime attribute so in 3NF

yes please correct the typo, answer should be B,D. just there is a typo in the last point, should have been right side instead of left
Thanks Abhineet, corrected

A relation is in $3NF$ if it is in $2NF$ and no non-prime attribute should be determined by anything but a super key (no transitive dependency). This means $3NF$ still allows FDs like $A \to B,$ where $A$ is not a super key as long as $B$ is a prime attribute. That is, a proper subset of some candidate key (which means some combination of prime attributes) determining some other (if its same it becomes trivial) prime attribute(s).

Now lets see the options

1. Transitive functional dependencies
Is possible in $3NF$ as long as RHS is a prime attribute.
2. Non-trivial functional dependencies involving prime attributes on the right-side
Possible as explained above. But any attribute(s) determining a prime attribute automatically becomes a prime(s) attribute implying the left-side here must also be prime-attribute(s).
3. Non-trivial functional dependencies involving prime attributes only on the left-side
Not possible in $3NF$
4. Non-trivial functional dependencies involving only prime attributes
This is possible and infact same as option B as explained for option B.

by

Best answer,In option a, Upon reading "Transitive Dependency", immediately I wrongly concluded not possible in 3NF..

But at option C, we cant say "Not possible" as CK->Non key is allowed.

But given answer is correct as there will be no redundancy here.

Please correct me if I'm wrong.

@Arjun sir, thanks a lot! It is indeed a demystifying explanation!

Option (A) tells transitive functional dependencies so it is not in 3NF so it is wrong

Option (B) tells prime attributes on the right side it means there is no non prime attribute in right side so it should be in 3NF because 3 NF me NP->NP nahi hona chahiye and for 2NF P->NP bhi nahi so right side me agar NP attributes hai to problem hai bcoz use case left side me only key honi chahiye means right side me agar Prime attributes hai to left me kuch bhi koi problem nahi hai, but it may or may not be in BCNF. So it may contain  undesirable data redundancy

Option (C) prime attributes in left side means if Prime -> NP then it is no in 2NF so not in 3NF

option (D) involving only prime attributes means it is in BCNF so no data redundancy

So Option "B" is correct

I think last answer is perfectly written so it should be the best answer
your approach is nice but for option D your conclusion is wrong.

See if all the attributes are prime attributes in the system of functional dependencies, only then we can say that the system is in BCNF,

BUT here it is just talking about individual FDs. For example, suppose in R(ABCD), A and B are prime attributes and none of them is the key, Let AC and BC be the key and D be a non-prime attribute, then $A\rightarrow B$ will cause redundancy.

And therefore, D is correct too.
When only prime attributes are involved then the relation is in 3NF but not in BCNF. So there is the possibility of data redundancy.

Hence option D is also correct.