edited by
17,695 views
44 votes
44 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. $\text{2NF}$
  2. $\text{3NF}$
  3. $\text{BCNF}$
  4. $\text{4NF}$
edited by

5 Answers

Best answer
45 votes
45 votes

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.

edited by
18 votes
18 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.
14 votes
14 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. 

3 votes
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.
Answer:

Related questions

26 votes
26 votes
2 answers
2
Kathleen asked Sep 18, 2014
11,267 views
The order of an internal node in a $B+$ tree index is the maximum number of children it can have. Suppose that a child pointer takes $6$ bytes, the search field value tak...