The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
+25 votes

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

  1. $2NF$
  2. $3NF$
  3. $\text{BCNF}$
  4. $4NF$
asked in Databases by Veteran (59.7k points)
edited by | 3.2k views

5 Answers

+27 votes
Best answer
Option $B$ is correct, because:

Here candidate keys are,



that makes name, roll_no and course_no prime attribute( or part of key )

functional dependencies $3$ and $4$ are not partial $FD$s

rule of $FD$ not belonging to $2NF$ is,

for $FD$ x-->y,  $x$ should be prime attribute and $y$ should be non prime attribute, here y is also a partial key

so, this is $2NF$, because $y$ is also prime attribute

but for $\text{BCNF}$, for every $FD$, of x-->y ,  x should be super key, so this is not $\text{BCNF}$, because $x$ is not super key

in $3NF$, for every $FD$, x-->y ,  condition is $x$ can be super key or $y$ can be prime attribute, $x$ is not super key, but $y$ is prime attribute.

that is why this condition holds and relation is in $3NF$
answered by Active (3.5k points)
edited by
name, courseNo -> roll_no, courseNo -> grade.

Why isn't this considered as a transitive dependency?
Why not BCNF? key can be considered superkey of itself right?
+16 votes
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.
answered by Active (3.5k points)
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.
+10 votes

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. 

answered by Loyal (8.1k points)
+3 votes
there are two candidate keys



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.
answered by (283 points)
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.

–1 vote
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
answered by Loyal (7.7k points)

Related questions

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

44,249 questions
49,743 answers
65,842 users