Sign up now
to enroll in courses, follow best educators, interact with the community and track your progress.
Download
GATE 2018 and GATE 2011 Questions on Normalization (in Hindi)
227 plays

More
This course will cover Normalization in Databases with 25 Previous Year Questions plus some practice questions from beginning level to expert.

Sweta Kumari is teaching live on Unacademy Plus

Sweta Kumari
YouTuber (channel : GATE NoteBook) | Verified Educator | 2+ year Online Teaching Experience

U
Unacademy user
good !! thank you ,..............................................................................................................
In question 1, Schema II email -> rollno => NPA determines a Prime Attribute. How this satisfies for 3NF?
  1. GATE 2011 Consider a relational table with a single record for each registered student with the following attributes. 1. Registration_Num: Unique registration number of each registered student 2. UID: Unique identity number, unique at the national level for each citizen 3. BankAccount_Num: Unique account number at the bank. A student can have multiple accounts or join accounts. This attribute stores the primary account number. 4. Name: Name of the student 5. Hostel_Room: Room number of the hostel Which one of the following option is INCORRECT? (A) BankAccount_Num is candidate key (B) Registration_Num can be a primary key (C) UID is candidate key if all students are from the same country (D) If S is a superkey such that SnUID is NULL then SUUID is also a superkey


  2. GATE 2011 Consider a relational table with a single record for each registered student with the following attributes. 1. Registration_Num: Unique registration number of each registered student 2. UID: Unique identity number, unique at the national level for each citizern 3. BankAccount _Num: Unique account number at the bank. A student can have multiple accounts or join accounts. This attribute stores the primary account number. 4. Name: Name of the student 5. Hostel_Room: Room number ofthe hostel Which one of the following option is INCORRECT? (A) BankAccount_Num is candidate key (B) Registration_Num can be a primary key (C) UID is candidate key if all students are from the same country (D) If S is a superkey such that SnUID is NULL then SUUID is also a superkey A Candidate Key value must uniquely identify the corresponding row in table. BankAccount Number is not a candidate key. As per the question "A student can have multiple accounts or joint accounts. This attributes stores the primary account number". If two students have a joint account and if the joint account is their primary account, then BankAccount_Number value cannot uniquely identify a row.


  3. 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 l: 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 courses Schema Il: Registration (rollno, coursid, email) Non-trivial functional dependencies: rollno, courseid email email rollno Schema IlI: Registration (rollno, courseid, marks, grade) Non-trivial functional dependencies: rollno, courseid, marks, grade marks - grade Schema IV: Registration (rollno, courseid, credit) Non-trivial functional dependencies: rollno, courseidcredit courseidcredit GATE 2018 Which one of the relational schemas above is in 3NF but not in BCNF? (A) Schema l (B) Schema Il (C) Schema Ill (D) Schema IV


  4. Schema l: 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 courses Since, rollno is primary key, so this relation is in BCNF as well as 3 NF. Schema lI: Registration (rollno, coursid, email) Non-trivial functional dependencies: rollno, courseid email email- rollno Since, (rollno, coursid) is primary key so rollno and coursid are prime attributes. email is non-prime attribute. Functional depedency (FD) rollno, courseid - email is in BCNF and 3NF, but FD email> rollno violates the rule of BCNF because email is not superkey. But it satifies rule of 3 NF because rollno is prime-attribute. So, overall this relation is in 3 NF but not in BCNF.


  5. Schema IIl: Registration (rollno, courseid, marks, grade) Non-trivial functional dependencies rollno, courseid, marks, grade marks > grade Since rollno, courseid is primary key, so rollno and courseid are prime attributes and marks and grade are non-prime attributes FD rollno, courseid, marks, grade satisfies BCNF as well as 3 NF. FD marks grade does not satifies 3 NF because nither marks is superkey nor grade is prime-attribute. So, aslo can not be in BCNF So, overall this relation is not in 3 NF and not in BCNF but it does not violates rule of 2 NF, so can be only in 2 NF. Schema IV: Registration (rollno, courseid, credit) Non-trivial functional dependencies: rollno, courseid credit courseidcredit Since, rollno, courseid is primary key, so rollno and courseid are prime-attributes and credit is non-prime attribute. FD rollno, courseid credit satifies BCNF as well as 3 NF. FD courseid- credit violates rule of 2 NF, so can not be in 2NF. So, overall this is not in 2 NF, 3 NF, and BCNF. But it is only in 1 NF.