Consider the following four relational schemas. — GATE CS 2018 Q42

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

View worked solution

Answer: B

To determine which schema is in $\text{3NF}$ but not in $\text{BCNF}$ , we analyze each schema individually.

Schema I:

The attribute courses is set-valued (non-atomic). By definition, a relation must have atomic attributes to be in First Normal Form ( $\text{1NF}$ ). Since it is not in $\text{1NF}$ , it cannot be in $\text{3NF}$ .

Schema II:

Relation: Registration(rollno, courseid, email)
Functional Dependencies (FDs):

$\text{rollno, courseid} \rightarrow \text{email}$
$\text{email} \rightarrow \text{rollno}$

Let's find the Candidate Keys (CKs):

$(\text{rollno, courseid})^+ = \{\text{rollno, courseid, email}\}$
$(\text{email, courseid})^+ = \{\text{email, courseid, rollno}\}$

Thus, the Candidate Keys are $\{\text{rollno, courseid}\}$ and $\{\text{email, courseid}\}$ .

Prime attributes (part of any candidate key): $\{\text{rollno, courseid, email}\}$
Non-prime attributes: None

Checking 3NF:
For every non-trivial dependency $X \rightarrow Y$ , either $X$ must be a superkey, or $Y$ must be a prime attribute.

For $\text{rollno, courseid} \rightarrow \text{email}$ : LHS is a superkey. (Satisfied)
For $\text{email} \rightarrow \text{rollno}$ : RHS ( $\text{rollno}$ ) is a prime attribute. (Satisfied)

Therefore, Schema II is in 3NF.

Checking BCNF:
For every non-trivial dependency $X \rightarrow Y$ , $X$ must be a superkey.

For $\text{email} \rightarrow \text{rollno}$ : LHS ( $\text{email}$ ) is not a superkey.

Therefore, Schema II is NOT in BCNF.

Schema III:

Relation: Registration(rollno, courseid, marks, grade)
FDs:

$\text{rollno, courseid} \rightarrow \text{marks, grade}$
$\text{marks} \rightarrow \text{grade}$

Candidate Key is

\{\text{rollno, courseid}\}

.
For the dependency

\text{marks} \rightarrow \text{grade}

\text{marks}

is not a superkey and

\text{grade}

is not a prime attribute. This is a transitive dependency, so Schema III is not in 3NF.

Schema IV:

Relation: Registration(rollno, courseid, credit)
FDs:

$\text{rollno, courseid} \rightarrow \text{credit}$
$\text{courseid} \rightarrow \text{credit}$

Candidate Key is

\{\text{rollno, courseid}\}

.
For the dependency

\text{courseid} \rightarrow \text{credit}

, a proper subset of the candidate key (

\text{courseid}

) determines a non-prime attribute (

\text{credit}

). This is a partial dependency, so Schema IV is not in 2NF (and hence not in 3NF).

Thus, only Schema II is in $\text{3NF}$ but not in $\text{BCNF}$ .

Free practice · Your answers on this page are not saved.

GATE CS 2018 Q42 — Normal Forms

Schema I:

Schema II:

Schema III:

Schema IV:

Related questions