retagged by
11,632 views
37 votes
37 votes

Consider the following four relational schemas. For each schema , all non-trivial functional dependencies are listed, The bolded attributes are the respective primary keys.

$\text{Schema I:}$ Registration(rollno, courses)

Field ‘courses’ is a set-valued attribute containing the set of courses a student has registered for.

Non-trivial functional dependency

rollno $\rightarrow$ courses

$\text{Schema II:}$ Registration (rollno, coursid, email)

Non-trivial functional dependencies:

rollno, courseid $\rightarrow$ email

email $\rightarrow$ rollno

$\text{Schema III:}$ Registration (rollno, courseid, marks, grade)

Non-trivial functional dependencies:

rollno, courseid, $\rightarrow$ marks, grade

marks $\rightarrow$ grade

$\text{Schema IV:}$ Registration (rollno, courseid, credit)

Non-trivial functional dependencies:

rollno, courseid $\rightarrow$ credit

courseid $\rightarrow$ credit

Which one of the relational schemas above is in $\text{3NF}$ but not in $\text{BCNF}$?

  1. $\text{Schema I}$
  2. $\text{Schema II}$
  3. $\text{Schema III}$
  4. $\text{Schema IV}$
retagged by

4 Answers

Best answer
39 votes
39 votes

Answer is (B).

rollno, courseid $\to$ email

$($rollno, courseid is a super key,so it comes under $\text{3NF}$ as well as $\text{BCNF}).$

email $\to$ rollno

Here, email is not a key though but rollno comes under prime-attribute. Hence it's in $\text{3NF}$ but not $\text{BCNF}$.

edited by
6 votes
6 votes

option b

0 votes
0 votes
Answer is option (B).

Since, in functional dependency, rollno, courseid→email, (rollno,courseid) forms candidate key, so it follows the property of BCNF but the other functional dependency email→rollno, satisfies the property of rollno being prime attribute but fails the property of  L.H.S. being super key. So, it is in 3NF only.
Answer:

Related questions