GATE CSE 2004 | Question: 50

Question

The relation scheme $\text{Student Performance (name, courseNo, rollNo, grade)}$ has the following functional dependencies:

• name, courseNo, $\rightarrow$ grade
• rollNo, courseNo $\rightarrow$ grade
• name $\rightarrow$ rollNo
• rollNo $\rightarrow$ name

The highest normal form of this relation scheme is

• A. $\text{2NF}$
• B. $\text{3NF}$
• C. $\text{BCNF}$
• D. $\text{4NF}$

Comments

Detailed Video Explanation: https://www.youtube.com/watch?v=HdaHz0iY2ws&t=9923s

---

DBMS Complete Summary & GATE PYQs Playlist: https://youtube.com/playlist?list=PLIPZ2_p3RNHh3otU-TnAK-GkqrvvOO33C&feature=shared 

---

1NF - any relation is always in 1NF.

2NF- if proper subset of any candidate key does not derive any non prime attribute then it is in         2NF.

3NF - > Not a SK should not  derive any non prime attribute.

BCNF->  if X->Y then X is always super key , where our given FD is non trivial

---

3NF  :-  if LHS is not Super Key  then  RHS should be  Prime Attribute

---

the given relation is in third normal form

option B is correct

Answer 1

Here candidate keys are,

• name, courseNo
• rollNo, courseNo

That makes name, rollNo, and courseNo prime attributes (part of some candidate key)

Functional dependencies $3$ and $4$ are not partial $\text{FD}$s.

If a relation schema is not in $\text{2NF},$ then for some $\textsf{FD}\; x\rightarrow y,  x$ should be a proper subset of some candidate key and $y$ should be a non-prime attribute.

$\textsf{FD}$s $3$ and $4$ are not violating $\text{2NF}$, because the RHS are prime attributes.

For a relation to be in $\textsf{3NF}$, for every $\text{FD}, x\rightarrow y,$ $x$ should be a super key or $y$ is a prime attribute. For $\textsf{FD}$s $3$ and $4,$ LHS are not super keys, but RHS are prime attributes. So, they are not violating $\textsf{3NF}.$

For a relation to be in $\textsf{BCNF}$, for every $\text{FD},$ $x \rightarrow y ,  x$ should be super key. This is clearly violated for $\textsf{FD}$s $3$ and $4$ and so the relation scheme is not in $\textsf{BCNF}$ and hence not in $\textsf{4NF}$ also.

Correct option: B.

Comments

if I rewrite question like
name->grade
courseno -> grade
rollno->grade
courseno->grade

name->rollno

rollno->name

is it okay?
here c.k= name+course and roll+course
then it won't satisfy 3nf?

---

no

---

first of all u can't split on LHS  u can only combine the LHS and we can split on RHS

Answer 2

If we proceed with given FDs then either Name,courseno or RollNo,courseNo becomes the candidate key. So no non-prime attributes are partially dependent on any Key attributes. Hence 2NF test passed.

Again for 3rd and 4th FD, Name,roll are part of the key and for first 2 FDs, determinants are candidate key itself. So 3NF test  passed but not BCNF. Hence 3NF answer. b.

Comments

Please explain why not BCNF?

---

• name → rollNo
• rollNo →→ name
• in these two left side candidate key is not present
• 3nf because right side prime attribute present.

---

Thanks I got it.

Answer 3

Candidate key1-name,course no. 

CandCandikey 2-roll no., course no. 

Checking for bcnf:

Name->roll no

Name not c.Key so not in bcnf

Checking for 3nf:

In first two dependencies  both are cand keys 3 nf satisfied. 

In next two dependencies, no non prime attribute so condition  satishfied for 2 nf as well as 3 nf. 

Answer 4

there are two candidate keys

1(name,course_no)

2(roll_no,course_no)

when i consider 1(name,course_no) as key roll_no becomes non-prime attribute and fd name->roll_no. violets 2nf.and same situation when 2 is considered as key.

or is it so that all the attributes which are part of single or different composite candidate key are considered as prime attribute in all the cases.

arjun sir plz clear my doubt.

Comments

Prime attribute are the attributes of all the candidate keys. One of the candidate key you choose as primary key which should not contain null values,. No matter which candidate key we choose as primary key, prime attributes will  be all the attributes that belong to to one or more candidate keys

---

got it.

---

Prime attribute: An attribute that is part of SOME candidate key. 

http://www.cs.miami.edu/~burt/learning/Csc598.073/notes/reldb.html

Answer 5

BEST ANSWER

FD GIVEN

N,C---> G 

R,C----> G

N---->R

R---->N

Relation Schema Given (N, C, R, G)

Candidate keys = NC, RC

Check BCNF

Proper subset of One CK ----->  Proper subset of another  CK    (Voilation condition) 

N,C---> G   (BCNF)

R,C----> G  (BCNF)

N---->R  (Volation of BCNF)

R---->N  (Volation of BCNF)

Check 3NF

Non Key Attribute  ----->  Non Key Attribute     (Voilation condition) 

N,C---> G   (3NF)

R,C----> G  (3NF)

N---->R  (3NF)

R---->N  (3NF)

So Highest Normal form is 3NF

Answer 6

1 st 2 FD's satisfy BCNF ... other 2 is in 3 NF but not in BCNF
So the highest normal form of the schema is 3 NF