GATE CSE 2018 | Question: 42

Question

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}$?

• A. $\text{Schema I}$
• B. $\text{Schema II}$
• C. $\text{Schema III}$
• D. $\text{Schema IV}$

Comments

Even if you missed the word then also option 1 is incorrect because they want 3NF but not BCNF.

---

Schema A: BCNF, as LHS is a super key as there is only one attribute(key) present.

Schema B: Clearly 3NF because (roll no, course id) is CK and roll no is prime. No way, 3NF violation possible. But it is not BCNF because in email → roll no, “email” isn’t SK.

Schema C: In  marks→ grade, marks isn’t a super key and grade is non prime attribute. Clearly 3NF violation.

Schema D: In course id → credit, course id isn’t a super key and credit is non-prime attribute. Even 2NF isn’t satisfied as “course” is proper subset of CK which determines non prime attribute.

---

this type of question first check from BCNF→ 3NF→ 2NF→ 1NF beacuse,

 

Answer 1

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}$.

Comments

Set valued attribute means multi valued attribute.

---

If you see like that BCNF, 3NF, 2NF, 1NF i just saw a pattern and nownI get to see that first was not bcnf aah.

---

can't even find the meaning of set valued attribute.

Answer 2

For Concepts check: https://gateoverflow.in/31082/what-best-check-bcnf-partial-transitive-partial-dependencies

(B) is correct

Comments

Courses is a set-valued attribute meaning it is multivalued entitiy. It is not in 1NF.

Answer 3

option b

Comments

X----Y , is in 3NF.

If x is key or y is prime attribute.

Here second fd is

courese id------> credit

Courese id is not a key or credit is not prime attribute so it is not in 3NF.

Answer 4

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.