edited by
14,315 views
35 votes
35 votes

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 by

5 Answers

Best answer
67 votes
67 votes

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 Questionshttps://youtu.be/xqIm-Y3oVDk 

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$.

https://youtube.com/playlist?list=PLIPZ2_p3RNHhJjQGnZB2jORa0JxLdMp4X

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 :

https://forum.thethirdmanifesto.com/wp-content/uploads/asgarosforum/987737/00-efc-further-normalization.pdf

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

edited by
12 votes
12 votes

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).

Reference: https://gatecse.in/demystifying-database-normalization/ 

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.

Correct Answer: A;B;D

edited by
10 votes
10 votes

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

edited by
6 votes
6 votes
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  
Answer:

Related questions

10 votes
10 votes
2 answers
1
makhdoom ghaya asked Nov 19, 2016
2,558 views
Match the pairs in the following questions:$$\begin{array}{|ll|ll|}\hline (a) & \text{Secondary index} & (p) & \text{Function dependency} \\\hline (b) & \text{Non-proced...
29 votes
29 votes
6 answers
3
makhdoom ghaya asked Nov 22, 2016
9,204 views
Indicate which of the following well-formed formulae are valid:$\left(P\Rightarrow Q\right) {\wedge} \left(Q \Rightarrow R\right) \Rightarrow \left(P \Rightarrow R\right)...
20 votes
20 votes
3 answers
4
makhdoom ghaya asked Nov 22, 2016
9,025 views
Let $R_{1}$ and $R_{2}$ be regular sets defined over the alphabet $\Sigma$ Then:$R_{1} \cap R_{2}$ is not regular.$R_{1} \cup R_{2}$ is regular.$\Sigma^{*}-R_{1}$ is regu...