The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+26 votes
3.5k views

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.9k points)
edited by | 3.5k views

5 Answers

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

Here candidate keys are,

Name+course

roll_no+course

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
0
name, courseNo -> roll_no, courseNo -> grade.

Why isn't this considered as a transitive dependency?
0
Why not BCNF? key can be considered superkey of itself right?
+1

@Barney Ross 

because 3rd and 4th FD's have prime attribute at left hand side...

so relation is not in BCNF

0
Prime attribute -> prime attribute

key -> key

is in 3NF ? right
0
@vidyadhar

X-->Y

If X is candidate key or y is prime attribute then it is in 3NF
0

@Suneel+Padala ... i know brother  but  am i right ?  

0

@VIDYADHAR SHELKE 1 

If X->Y is an FD then For 3NF :

X must be a superkey and Y can be any attribute

OR

X can be any attribute and Y must be prime attribute

0

@Harshada...just tell me yes or no ...what wrote above......i am confused

+1

Whatever you wrote is in 3NF but actual condition which must be checked for 3NF is this:

A relation is in third normal form if it holds atleast one of the following conditions for every non-trivial function dependency X → Y.

  1. X is a super key.
  2. Y is a prime attribute, i.e., each element of Y is part of some candidate key.
0
That is OCD(overlapping candidate key dependency)
0

@Harshada  By the way thanks for confirmation......I know 3NF condition😂

+1
:D You're welcome.
+17 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)
+1
Please explain why not BCNF?
+2
  • name → rollNo
  • rollNo  name
  • in these two left side candidate key is not present
  • 3nf because right side prime attribute present.
0
Thanks I got it.
+11 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.3k points)
+3 votes
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.
answered by (283 points)
0
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
0
got it.
+4

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

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

–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 (8.1k points)
Answer:

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
47,881 questions
52,231 answers
182,123 comments
67,649 users