GATE CS 2018 Set 1 — Question 52
Go beyond PYQs with Success TrackerAI-powered personalised practice and doubt support. Unlimited practice on eligible plans; AI usage limits apply.MCQ+2 / -0.67Medium1NF, 2NF, 3NFNormalizationDatabasesBCNFFunctional Dependencies
Databases → Normalization → 1NF, 2NF, 3NF
Last updated
Question
Consider the following four relational schemas. For each schema, all non-trivial functional dependencies are listed. The underlined attributes are the respective primary keys.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 coursesSchema II: Registration (rollno, courseid, email)
Non-trivial functional dependencies:
rollno, courseid email
email rollnoSchema III: Registration (rollno, courseid, marks, grade)
Non-trivial functional dependencies:
rollno, courseid marks, grade
marks gradeSchema IV: Registration (rollno, courseid, credit)
Non-trivial functional dependencies:
rollno, courseid credit
courseid creditWhich one of the relational schemas above is in 3NF but not in BCNF?
Field ‘courses’ is a set-valued attribute containing the set of courses a student has registered for.
Non-trivial functional dependency:
rollno coursesSchema II: Registration (rollno, courseid, email)
Non-trivial functional dependencies:
rollno, courseid email
email rollnoSchema III: Registration (rollno, courseid, marks, grade)
Non-trivial functional dependencies:
rollno, courseid marks, grade
marks gradeSchema IV: Registration (rollno, courseid, credit)
Non-trivial functional dependencies:
rollno, courseid credit
courseid creditWhich one of the relational schemas above is in 3NF but not in BCNF?
Correct answer
(B) Schema II
Solution
A relation is in 3NF if for every non-trivial functional dependency
X → A, either is a superkey or is a prime attribute. A relation is in BCNF if for every non-trivial functional dependency X → A, is a superkey.- Schema I: . Since is the primary key, it is a superkey. Thus, it is in BCNF.
- Schema II: The candidate keys are and .
- For , the LHS is a superkey, satisfying BCNF.
- For , the LHS is not a superkey, but the RHS is a prime attribute (part of a candidate key). This satisfies 3NF but violates BCNF.
- Schema III: . is not a superkey and is not a prime attribute. This is a transitive dependency, so it is not in 3NF.
- Schema IV: . is not a superkey and is not a prime attribute. This is a partial dependency, so it is not in 3NF.
Continue learning with Success Tracker
A step still unclear? Work through it with support
Use Success Tracker to ask about the reasoning, then try another GATE CS question to check your understanding.
AI-powered practice· Unlimited practice on eligible plans
- PYQs with solutions
- Attempt available previous-year questions, then compare your reasoning with the worked solution. Coverage varies by stream.
- Practice that adapts
- Choose a topic, work on weaker areas and bookmark questions to revisit. Your attempts feed your progress tracking.
- AI doubt support
- Ask follow-up questions about a step or concept while practising, instead of stopping at the final answer.
Unlimited practice is available on eligible plans. Free practice and AI usage have limits; check the current plan allowances before choosing.
This page stays readable without an account. AI responses can be wrong; check them against the solution and source material.
More questions on Normalization
2026 Set 2 Q15In the context of DBMS, consider the two sets T and S given below. | T | S | |---|---| | I:…2026 Set 2 Q20Consider concurrent execution of two transactions and in a DBMS, both of which access a…2026 Set 1 Q30Let and be the attributes of a relation in a relational schema. Let…2026 Set 1 Q31In the context of relational database normalization, which of the following statements is/are true?2026 Set 2 Q42In the context of schema normalization in relational DBMS, consider a set F of functional…