Normalization & Normal Forms GATE CS PYQ Quiz

Last Updated :
Discuss
Comments

Question 1

For the relation R(ABCDEFGH) with FD's= {CH->G, A->BC, B->CHF, E->A, F->EG such that F+ is exactly the set of FDs that hold for R.} Consider the FDs given in above question. The relation R is

  • in 1NF, but not in 2NF.

  • in 2NF, but not in 3NF.

  • in 3NF, but not in BCNF.

  • in BCNF

Question 2

In a relational data model, which one of the following statements is TRUE?

  • A relation with only two attributes is always in BCNF.

  • If all attributes of a relation are prime attributes, then the relation is in BCNF.

  • Every relation has at least one non-prime attribute

  • BCNF decompositions preserve functional dependencies

Question 3

A database of research articles in a journal uses the following schema.

(VOLUME, NUMBER, STARTPGE, ENDPAGE, TITLE, YEAR, PRICE)

The primary key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE) and the following functional dependencies exist in the schema. 

(VOLUME, NUMBER, STARTPAGE, ENDPAGE) -> TITLE
(VOLUME, NUMBER)                     -> YEAR
(VOLUME, NUMBER, STARTPAGE, ENDPAGE) -> PRICE

The database is redesigned to use the following schemas. 

(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE)
(VOLUME, NUMBER, YEAR)

Which is the weakest normal form that the new database satisfies, but the old one does not?

  • 1NF

  • 2NF

  • 3NF

  • BCNF

Question 4

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 → courses
  • Schema II: Registration (rollno, coursid, email) Non-trivial functional dependencies: rollno, courseid → emailemail → rollno
  • Schema III: Registration (rollno, courseid, marks, grade) Non-trivial functional dependencies: rollno, courseid, → marks, grademarks → grade
  • Schema IV: Registration (rollno, courseid, credit) Non-trivial functional dependencies: rollno, courseid → creditcourseid → credit

Which one of the relational schemas above is in 3NF but not in BCNF?

  • Schema I

  • Schema II

  • Schema III

  • Schema IV

Question 5

Which one of the following statements about normal forms is FALSE?

  • BCNF is stricter than 3NF

  • Lossless, dependency-preserving decomposi­tion into 3NF is always possible

  • Lossless, dependency-preserving decomposi­tion into BCNF is always possible

  • Any relation with two attributes is in BCNF

Question 6

Relation R is decomposed using a set of functional dependencies, F and relation S is decomposed using another set of functional dependencies G. One decomposition is definitely BCNF, the other is definitely 3NF, but it is not known which is which. To make a guaranteed identification, which one of the following tests should be used on the decompositions? (Assume that the closures of F and G are available).

  • Dependency-preservation

  • Lossless-join

  • BCNF definition

  • 3NF definition

Question 7

Relation R with an associated set of functional dependencies, F is decomposed into BCNF. The redundancy (arising out of functional dependencies) in the resulting set relations is.

  • Zero

  • More than zero but less than that of an equivalent 3NF decomposition

  • Proportional to the size of F+

  • Indeterminate

Question 8

Consider the following functional dependencies in a database: 

  Data_of_Birth → Age
  Age → Eligibility
  Name → Roll_number
  Roll_number → Name
  Course_number → Course_name
  Course_number → Instructor
  (Roll_number, Course_number) → Grade

The relation (Roll_number, Name, Date_of_birth, Age) is:

  • In second normal form but not in third normal form

  • In third normal form but not in BCNF

  • In BCNF

  • None of the above

Question 9

Given the following two statements: 

  S1: Every table with two single-valued 
      attributes is in 1NF, 2NF, 3NF and BCNF.

  S2: AB->C, D->E, E->C is a minimal cover for 
      the set of functional dependencies 
      AB->C, D->E, AB->E, E->C.

Which one of the following is CORRECT?

  • S1 is TRUE and S2 is FALSE.

  • Both S1 and S2 are TRUE.

  • S1 is FALSE and S2 is TRUE.

  • Both S1 and S2 are FALSE.

Question 10

Consider the following relational schemes for a library database: Book (Title, Author, Catalog_no, Publisher, Year, Price) Collection (Title, Author, Catalog_no) with in the following functional dependencies: 

I. Title Author --> Catalog_no
II. Catalog_no --> Title, Author, Publisher, Year
III. Publisher Title Year --> Price

Assume {Author, Title} is the key for both schemes. Which of the following statements is true?

  • Both Book and Collection are in BCNF

  • Both Book and Collection are in 3NF only

  • Book is in 2NF and Collection is in 3NF

  • Both Book and Collection are in 2NF only

There are 14 questions to complete.

Take a part in the ongoing discussion