MadeEasy Test Series: Databases - Database Normalization closed

Question

Comments

Answer should be c definitely in 2NF but transitive dependency may present.

---

Counter example ...not in 2NF and only single candidate key.

R(ABCD)

AB->CD

B->C. (Partial dependency)

Candidate key:->AB

---

yeah right sorry i thought single attribute candidate key.

Answer 1

A 3NF table that does not have multiple overlapping candidate keys is guaranteed to be in BCNF.

As here there is only single candidate key means there is no multiple overlapping candidate keys so if this is in 3NF then it is compulsory in bcnf.

U may see example here https://en.m.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form

So B is correct ans.

Comments

@Pankaj  @srestha @Akriti @cse23

Plz verify me!!

Here question interpretation is required as well some English grammar.

Option A & C:->False bcz

If R has only one candidate key ,then R is in 2NF.(option A&C 's antecedent part says that it is compulsory in 2NF which need not be..see below counter example)

 

R(ABCD)

AB->CD

B->C. (Partial dependency)

Candidate key:->AB

So this relation is Not in 2NF.

-> if option C would [If R is in 2NF,then may not be in 3NF ;then it is abs. correct.]

Here option B is correct..Plz see my ans(though some intelli has downvoted it ;p)

---

i think option B is correct cuz i could not find any counter example that if a relation is in 3NF and it has only one candidate key then it is not in BCNF..and clearly A and C are false as you explained.

but is it also true that if a relation has more than 1 candidate key that too overlapping aNd if it is in 3NF then may or not may be it is in BCNF.

see below example

R(A,B,C,D,E)

 

bc - > ade

cd  - > b

//here it is in 3NF as (bc,cd) are keys that too overlapping and it is also in BCNF.

aM I RIGHT??

---

Yes ; U are correct.

And I suggest U to Visit my ans. Links below portion.

"3NF table not meeting BCNF (Boyce–Codd normal form)"

Answer 2

Let a relation R(A,B,C,D)  now AB is a ck . so fds can be A->C  , B->D  now they are not BCNF,3NF,2NF so option d looks right.

But b option specifically says if R is in 3NF so we have to take fds that are in 3NF only. Like AB->CD which is 3NF now it is also in BCNF . we cannot make any fd here with ab as ck which is 3nf but not bcnf

so option d is correct.

Answer 3

Option B is True.

Find Proof below.

Statement $S$: If a relation $R$ is in $3NF$ but not in $BCNF$ then relation $R$ must have at least two overlapped candidate keys.

This statement is TRUE.

So, a 3NF relation is not in BCNF only if there exists at least two candidate keys which are composite & overlapping.

The BCNF differs from the 3NF only when there are more than one candidate keys and the keys are composite and overlapping.

---

First understand what is "overlapping candidate keys". 

Overlapping Candidate Keys: Two candidate keys $M,N$ are "overlapping" candidate keys if they are cpmposite & not disjoint.

For example, candidate keys $AB, BC$ are overlapping, candidate keys $AB, CD$ are not overlapping. Also, $AB, B$ are Not overlapping candidate keys because we can't have these two as candidate keys simultaneously.

Proof of statement $S$: 

Since $R$ is in 3NF but not in BCNF, So, there is a Non-trivial FD $Y \rightarrow A$ where $A$ is a single attribute & prime, & $Y$ is Non-superkey. & since this FD is non-trivial, so $A \notin Y$. 

Since $A$ is a Prime attribute, So, $A  \in X$ where $X$ is a candidate key. 

Note that $X$ is a Composite CK (because If Not then $X = A$ & then $Y$ will become SK, which is a Contradiction.)

So, $X = ZA$ where $Z$ is a Nonempty set of attributes.

Now, we know that $YZ$ will be a SK because $YZ \rightarrow AZ.$ 

So, $YZ$ is a SK. 

We know, Every superkey is a superset of some candidate key.

So, $YZ$ has a subset which is a CK.. But this subset must have at least one attribute from $Z$ as well as at least one attribute from $Y$ because $Y$ is Not a SK & $Z$ is Not a SK. 

So, $YZ$ has a subset $H$ which is a CK, & $H$ contains at least one attribute of $Z$ & at least one attribute of $Y$. 

So, Now we have two candidate keys $H, X$ which are overlapping & composite (Overlapping because of an attribute of $Z$)

Hence Proved.

---

Another Easy to understand proof (But not a clever proof like the above) is to consider the following cases for Contradiction:

1. No Composite Key

2. Single Composite Key

3. Multiple Composite Keys But None Overlapping.. 

In all 3 cases, we get some contradiction for "3NF But Not in BCNF"..

So, we can say that we must have at least two Overlapping candidate keys.

---

Useful Videos:

GATE 2020 Question: 3NF But Not in BCNF

4 Times in GATE: Relation with 2 attributes is Always in BCNF.

Misconception in "3NF But Not BCNF": Misconception in Normal Forms

Normalization Complete Playlist: Complete Normalization & All GATE PYQs

DBMS Complete Summary & GATE PYQs: DBMS Summary & GATE PYQs