Questions & Answers of Integrity Constraints, Normal Forms

Weightage of Integrity Constraints, Normal Forms

Total 16 Questions have been asked from Integrity Constraints, Normal Forms topic of Databases subject in previous GATE papers. Average marks 1.44.

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, courseid, email)
            Non-trivial functional dependencies:
             rollno, courseid email
             email rollno
 
Schema III: 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, courseid credit
             courseid credit

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

The following functional dependencies hold true for the relational schema R {V, W, X, Y, Z}:

                                                                                $\style{font-family:'Times New Roman'}{\begin{array}{l}V\rightarrow W\\VW\rightarrow X\\Y\rightarrow VX\\Y\rightarrow Z\end{array}}$

Which of the following  is irreducible equivalent  for this  set of functional dependencies ?

Consiider the following tables T1 and T2

T1
P Q
2 2
3 8
7 3
5 8
6 9
8 5
9 8
T2
R S
2 2
8 3
3 2
9 7
5 7
7 2

In table T1, P is the primary key and Q is the foreign key referencing R in table T2 with on-delete cascabe and on-update cascade. In table T2, R is the primary key and S is the forign key refrencing P in table T1 with on-delete set NULL and ON-update cascade. In order to delete record $ \left\langle3,8\right\rangle $ from table T1, the number of additional records that need to be deleted from table T1 is _____________________.

A database of research articles in a journal uses the following schema.
     (VOLUME, NUMBER, STARTPAGE, 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?

Consider the relation X(P, Q, R, S, T, U) with the following set of functional dependencies

F = {
          {P, R} → {S, T},
          {P, S, U} → {Q, R}
      }

Which of the following is the trivial functional dependency in F+, where F+ is closure of F?

Consider the relation scheme R = (E,F, G, H, I, J, K, L, M, N) and the set of functional dependencies {{E, F} → {G}, {F} → {I, J}, {E, H} → {K, L}, {K} → {M}, {L} → {N}} on R. What is the key for R ?

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?

The maximum number of superkeys for the relation schema R(E,F,G,H) with E as the key is _____.

A prime attribute of a relation scheme R is an attribute that appears

An index is clustered, if

Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F={CH→G, A→BC, B→CFH, E→A, F→EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R.
How many candidate keys does the relation R have?

Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F={CH→G, A→BC, B→CFH, E→A, F→EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R.

The relation R is

Which of the following is TRUE?

The following functional dependencies hold for relations R(A, B, C) and S(B, D, E)
B A,
A C
The relation R contains 200 tuples and the relation S contains 100 tuples. What is the maximum number of tuples possible in the natural join R $\bowtie$ S ?

Consider the following relational schemes for a library database:
Book (Title, Author, Catalog_no, Publisher,Year,Price)
Collection (Title, Author, Catalog_no)
with 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 bot schemes.Which of the following statements is true?

Which one of the following statements is FALSE?