Question 1
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?
A
3
B
4
C
5
D
6
GATE CS 2013    Database Design(Normal Forms)    
Discuss it


Question 1 Explanation: 
A+ is ABCEFGH which is all attributes except D. B+ is also ABCEFGH which is all attributes except D. E+ is also ABCEFGH which is all attributes except D. F+ is also ABCEFGH which is all attributes except D. So there are total 4 candidate keys AD, BD, ED and FD
Question 2
Consider the FDs given in above question. The relation R is
A
in 1NF, but not in 2NF.
B
in 2NF, but not in 3NF.
C
in 3NF, but not in BCNF.
D
in BCNF
GATE CS 2013    Database Design(Normal Forms)    
Discuss it


Question 2 Explanation: 
The table is not in 2nd Normal Form as the non-prime attributes are dependent on subsets of candidate keys. The candidate keys are AD, BD, ED and FD. In all of the following FDs, the non-prime attributes are dependent on a partial candidate key. A -> BC B -> CFH F -> EG
Question 3
Which of the following is TRUE?
A
Every relation in 3NF is also in BCNF
B
A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R
C
Every relation in BCNF is also in 3NF
D
No relation can be in both BCNF and 3NF
GATE CS 2012    Database Design(Normal Forms)    
Discuss it


Question 3 Explanation: 

BCNF is a stronger version 3NF. So every relation in BCNF will also be in 3NF.

Question 4
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 S∩UID is NULL then S∪UID is also a superkey
GATE CS 2011    Database Design(Normal Forms)    
Discuss it


Question 4 Explanation: 
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.
Question 5
Consider the following relational schema:
Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)
Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema?
A
The schema is in BCNF
B
The schema is in 3NF but not in BCNF
C
The schema is in 2NF but not in 3NF
D
The schema is not in 2NF
GATE-CS-2009    Database Design(Normal Forms)    
Discuss it


Question 5 Explanation: 
A relation is in BCNF if for every one of its dependencies X → Y, at least one of the following conditions hold:
    X → Y is a trivial functional dependency (Y ⊆ X)
    X is a superkey for schema R 
Since (sname, city) forms a candidate key, there is no non-tirvial dependency X → Y where X is not a superkey
Question 6
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?
A
Both Book and Collection are in BCNF
B
Both Book and Collection are in 3NF only
C
Book is in 2NF and Collection is in 3NF
D
Both Book and Collection are in 2NF only
Database Design(Normal Forms)    GATE CS 2008    
Discuss it


Question 6 Explanation: 
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 
  • The table "Collection" is in BCNF as there is only one functional dependency “Title Author –> Catalog_no” and {Author, Title} is key for collection.
  • Book is not in BCNF because Catalog_no is not a key and there is a functional dependency “Catalog_no –> Title Author Publisher Year”.
  • Book is not in 3NF because non-prime attributes (Publisher Year) are transitively dependent on key [Title, Author].
  • Book is in 2NF because every non-prime attribute of the table is either dependent on the whole of a candidate key [Title, Author], or on another non prime attribute. In table book, candidate keys are {Title, Author} and {Catalog_no}. In table Book, non-prime attributes (attributes that do not occur in any candidate key) are Publisher, Year and Prince
Please refer Database Normalization | Normal Forms for details of normal forms.
Question 7
Consider the relation scheme R = {E, F, G, H, I, J, K, L, M, M} 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?
A
{E, F}
B
{E, F, H}
C
{E, F, H, K, L}
D
{E}
Database Design(Normal Forms)    GATE-CS-2014-(Set-1)    
Discuss it


Question 7 Explanation: 
All attributes can be derived from {E, F, H} To solve these kind of questions that are frequently asked in GATE paper, try to solve it by using shortcuts so that enough amount of time can be saved. Fist Method: Using the given options try to obtain closure of each options. The solution is the one that contains R and also minimal Super Key, i.e Candidate Key.

A) {EF}+ = {EFGIJ} ≠ R(The given relation)

B) {EFH}+ = {EFGHIJKLMN} = R (Correct since each member of the 
                                    given relation is determined)

C) {EFHKL}+ = {EFGHIJKLMN} = R (Not correct although each member 
                                of the given relation can be determined 
                                but it is not minimal, since by the definition
                                of Candidate key it should be minimal Super Key)

 D) {E}+ = {E} ≠ R

Second Method:
Since, {EFGHIJKLMN}+ =  {EFGHIJKLMN}

{EFGHIJKLM}+ =  {EFGHIJKLMN} ( Since L -> {N}, hence can replace N by L)

In a similar way K -> {M} hence replace M by K

{EFGHIJKL}+ =  {EFGHIJKLMN} 

Again {EFGHIJ}+ =  {EFGHIJKLMN} (Since  {E, H} -> {K, L}, hence replace KL by EH)

{EFGH}+ =  {EFGHIJKLMN} (Since {F} -> {I, J} )

{EFH}+ =  {EFGHIJKLMN} (Since {E, F} -> {G} )

This explanation is contributed by Manish Rai. Learn more here: Finding Attribute Closure and Candidate Keys using Functional Dependencies
Question 8
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?
A
S1 is TRUE and S2 is FALSE.
B
Both S1 and S2 are TRUE.
C
S1 is FALSE and S2 is TRUE.
D
Both S1 and S2 are FALSE.
Database Design(Normal Forms)    GATE-CS-2014-(Set-1)    
Discuss it


Question 8 Explanation: 
 
S1: Every table with two single-valued 
      attributes is in 1NF, 2NF, 3NF and BCNF.
A relational schema R is in BCNF iff in Every non-trivial Functional Dependency X->Y, X is Super Key. If we can prove the relation is in BCNF then by default it would be in 1NF, 2NF, 3NF also. Let R(AB) be a two attribute relation, then
  1. If {A->B} exists then BCNF since {A}+ = AB = R
  2. If {B->A} exists then BCNF since {B}+ = AB = R
  3. If {A->B,B->A} exists then BCNF since A and B both are Super Key now.
  4. If {No non trivial Functional Dependency} then default BCNF.
Hence it's proved that a Relation with two single - valued attributes is in BCNF hence its also in 1NF, 2NF, 3NF. Hence S1 is true.
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.
As we know Minimal Cover is the process of eliminating redundant Functional Dependencies and Extraneous attributes in Functional Dependency Set. So each dependency of F = {AB->C, D->E, AB->E, E->C} should be implied in minimal cover. As we can see AB->E is not covered in minimal cover since {AB}+ = ABC in the given cover {AB->C, D->E, E->C} Hence, S2 is false. This explanation has been contributed by Manish Rai. Learn more about Normal forms here: Database Normalization | Introduction Database Normalization | Normal Forms
Question 9
The maximum number of superkeys for the relation schema R(E,F,G,H) with E as the key is
A
5
B
6
C
7
D
8
Database Design(Normal Forms)    GATE-CS-2014-(Set-2)    
Discuss it


Question 9 Explanation: 
Maximum no. of possible superkeys for a table with n attributes = 2^(n-1) Here, n = 4. So, the possible superkeys = 24-1 = 8 The possible superkeys are : E, EH, EG, EF, EGH, EFH, EFG, EFGH
Question 10
Given the STUDENTS relation as shown below. GATECS2014Q22 For (StudentName, StudentAge) to be the key for this instance, the value X should not be equal to
A
18
B
19
Database Design(Normal Forms)    GATE-CS-2014-(Set-2)    
Discuss it


Question 10 Explanation: 
There is already an entry with same name and age as 19. So the age of this entry must be something other than 19.
Question 11
Which one of the following statements about normal forms is FALSE?
A
BCNF is stricter than 3NF
B
Lossless, dependency-preserving decomposi­tion into 3NF is always possible
C
Lossless, dependency-preserving decomposi­tion into BCNF is always possible
D
Any relation with two attributes is in BCNF
Database Design(Normal Forms)    GATE-CS-2005    
Discuss it


Question 11 Explanation: 
Question 12
Let r be a relation instance with schema R = (A, B, C, D). We define r1 = ΠA, B, C (r) and r2 = ΠA.D (r). Let s = r1 * r2 where * denotes natural join. Given that the decomposition of r into r1 and r2 is lossy, which one of the following is TRUE?
A
s ⊂ r
B
r ∪ s
C
r ⊂ s
D
r * s = s
Database Design(Normal Forms)    GATE-CS-2005    
Discuss it


Question 12 Explanation: 
Question 13
Consider a relation scheme R = (A, B, C, D, E, H) on which the following functional dependencies hold: {A–>B, BC–>D, E–>C, D–>A}. What are the candidate keys of R?
A
AE, BE
B
AE, BE, DE
C
AEH, BEH, BCH
D
AEH, BEH, DEH
Database Design(Normal Forms)    GATE-CS-2005    
Discuss it


Question 13 Explanation: 
Question 14
Let R1 (A, B, C) and R2 (D, E) be two relation schema, where the primary keys are shown underlined, and let C be a foreign key in R1 referring to R2. Suppose there is no violation of the above referential integrity constraint in the corresponding relation instances r1 and r2. Which one of the following relational algebra expressions would necessarily produce an empty relation ? GATECS2004
A
1
B
2
C
3
D
4
Database Design(Normal Forms)    GATE-CS-2004    
Discuss it


Question 14 Explanation: 
Since C is a foreign key in R1 and there is no violation of the above referential integrity constraint, the set of values in C must be a subset of values in R2.
Question 15
The relation scheme Student Performance (name, courseNo, rollNo, grade) has the following functional dependencies:
name, courseNo → grade
rollNo, courseNo → grade
name → rollNo
rollNo → name 
The highest normal form of this relation scheme is
A
2 NF
B
3 NF
C
BCNF
D
4NF
Database Design(Normal Forms)    GATE-CS-2004    
Discuss it


Question 15 Explanation: 
For easy understanding let's say attributes (name, courseNo, rollNo, grade)
be (A,B,C,D)

Then given FDs are as follows:
AB->D, CB->D, A->C, C->A

Here there are two Candidate keys, AB and CB.

Now AB->D and CB->D satisfy BCNF as LHS is superkey in both.

But, A->C and C->A, doesn't satisfy BCNF. Hence we check for 3NF for
these 2 FDs.

As C and A on RHS of both the FDs are prime attributes, they satisfy 3NF.

Hence for the whole relation the highest normal form is 3NF. 
Question 16
Consider the relation Student (name, sex, marks), where the primary key is shown underlined, pertaining to students in a class that has at least one boy and one girl. What does the following relational algebra expression produce? (Note: r is the rename operator). GATECS2004Q51 The condition in join is "(sex = female ^ x = male ^ marks ≤ m)"
A
names of girl students with the highest marks
B
names of girl students with more marks than some boy student
C
names of girl students with marks not less than some boy students4)
D
names of girl students with more marks than all the boy students
Database Design(Normal Forms)    GATE-CS-2004    
Discuss it


Question 16 Explanation: 
The above relational algebra expression has two sub expressions.
The first one takes as input the Student relation (Student) and filters 
out all the tuples where sex=female(r sex=female (Student)) 
and then projects their names (P name r sex=female (Student)). 
So we get a new relation with names of all the female students.
The second one takes as input the Student relation and performs a rename 
operation on one with attributes name, sex and marks renamed as n, x, m 
respectively (r n, x, m(Student)) and then followed by a self-Cartesian
 product on the Student relation. The condition (sex = female ^ m = male ^ marks ≤ m) 
filters tuples with all female students from the first relation, 
male students from the second relation and performs a Cartesian product where 
marks of the female student is either less than or equal to a male student and 
then projects their names. So we get a new relation with names of all female 
students whose marks are lesser than at least one of the male student.
The difference operator(-) between the two subexpressions gives the names of all female students whose marks are more than all male students of the class. (From all the female students’ names we remove all those whose marks are at least more the one male student) This explanation has been contributed by Yashika Arora.
Question 17
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:
A
In second normal form but not in third normal form
B
In third normal form but not in BCNF
C
In BCNF
D
None of the above
Database Design(Normal Forms)    GATE-CS-2003    
Discuss it


Question 17 Explanation: 
The given table is not in 2NF as age is dependent on date of birth.
Question 18
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.
A
Zero
B
More than zero but less than that of an equivalent 3NF decomposition
C
Proportional to the size of F+
D
Indeterminate
Database Design(Normal Forms)    GATE-CS-2002    
Discuss it


Question 18 Explanation: 
If a relational schema is in BCNF then all redundancy based on functional dependency has been removed, although other types of redundancy may still exist. Source: http://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form
Question 19
With regard to the expressive power of the formal relational query languages, which of the following statements is true?
A
Relational algebra is more powerful than relational calculus
B
Relational algebra has the same power as relational calculus
C
Relational algebra has the same power as safe relational calculus
D
None of the above
Database Design(Normal Forms)    GATE-CS-2002    
Discuss it


Question 19 Explanation: 

A query can be formulated in relational calculus if and only if it can be formulated in relational algebra. So, relational algebra has the same power as relational calculus.
But, it is possible to write syntactically correct relational calculus queries that have infinite number of answers. Such queries are unsafe. Queries that have an finite number of answers are safe relational calculus queries.
Thus, Relational algebra has the same power as safe relational calculus.
 
Thus, option (C) is the answer.
 
Please comment below if you find anything wrong in the above post.
Question 20
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).
A
Dependency-preservation
B
Lossless-join
C
BCNF definition
D
3NF definition
Database Design(Normal Forms)    GATE-CS-2002    
Discuss it


Question 20 Explanation: 
Answer is (C) since to identify BCNF we need BCNF definition. One relation which satisfies will be in BCNF and other will be in 3NF. 1st is wrong because dependency may be preserved by both 3NF and BCNF. 2nd is wrong Because both 3NF and BCNF decomposition can be lossless. 4th is wrong because 3NF and BCNF both are in 3NF also.
Question 21
From the following instance of a relation scheme R (A, B, C), we can conclude that :
A B C
1 1 1
1 1 0
2 3 2
2 3 2
A
A functionally determines B and B function­ally determines C
B
A functionally determines B and B does not functionally determine C
C
B does not functionally determine C
D
A does not functionally determine B and B does not functionally determine C
Database Design(Normal Forms)    GATE-CS-2002    
Discuss it


Question 21 Explanation: 
Generally Normalization is done on the schema itself. From the relational instance given,we may strike out FD s that do not hold. e.g.B does not functionally determine C(This is true). But we cannot say that A functionally determines B for the entire relation itself. This is because that, A->B holds for this instance, but in future there might be some tuples added to the instance that may violate A->B. So overall on the relation we cannot conclude that A->B, from the relational instance which is just a subset of an entire relation. Refer http://geeksquiz.com/database-normalization-introduction/ for more details.
Question 22
Consider a schema R(A,B,C,D) and functional dependencies A->B and C->D. Then the decomposition of R into R1(AB) and R2(CD) is
A
dependency preserving and lossless join
B
lossless join but not dependency preserving
C
dependency preserving but not lossless join
D
not dependency preserving and not lossless join
Database Design(Normal Forms)    GATE-CS-2001    
Discuss it


Question 22 Explanation: 
Dependency Preserving Decomposition:
Decomposition of R into R1 and R2 is a dependency preserving decomposition if closure of functional dependencies after decomposition is same as closure of of FDs before decomposition.
A simple way is to just check whether we can derive all the original FDs from the FDs present after decomposition.

In the above question R(A, B, C, D) is decomposed into R1 (A, B) and R2(C, D) and there are only two FDs A -> B and C -> D. So, the decomposition is dependency preserving

Lossless-Join Decomposition:
Decomposition of R into R1 and R2 is a lossless-join decomposition if at least one of the following functional dependencies are in F+ (Closure of functional dependencies)

    R1 ∩ R2 → R1
   OR
    R1 ∩ R2 → R2

In the above question R(A, B, C, D) is decomposed into R1 (A, B) and R2(C, D), and R1 ∩ R2 is empty. So, the decomposition is not lossless.

Question 23
Suppose the adjacency relation of vertices in a graph is represented in a table Adj(X,Y). Which of the following queries cannot be expressed by a relational algebra expression of constant length?
A
List of all vertices adjacent to a given vertex
B
List all vertices which have self loops
C
List all vertices which belong to cycles of less than three vertices
D
List all vertices reachable from a given vertex
Database Design(Normal Forms)    GATE-CS-2001    
Discuss it


Question 23 Explanation: 
(A) This is simple query as we need to find (X, Y) for a given X. (B) This is also simple as need to find (X, X) (C) :-> Cycle < 3 . Means cycle of length 1 & 2. Cycle of length 1 is easy., Same as self loop. Cycle of length 2 is is also not too hard to compute. Though it'll be little complex, will need to do like (X,Y) & (Y, X ) both present & X != Y,. We can do this with constant RA query. (D) :-> This is most hard part. Here we need to find closure of vertices. This will need kind of loop. If the graph is like skewed tree, our query must loop for O(N) Times. We can't do with constant length query here. Answer is :-> D
Question 24
Let r and s be two relations over the relation schemes R and S respectively, and let A be an attribute in R. then the relational algebra expression GATECS200Q25B is always equal to GATECS200Q25
A
A
B
B
C
C
D
D
Database Design(Normal Forms)    GATE-CS-2001    
Discuss it


Question 24 Explanation: 
The above expression evaluates A = a for tables r and s Option A : is only displaying attributes of table r on the select condition Option B : is only displaying attributes of table r Option C: evaluates A = a by joining tables r and s  efficiently , thus correct Therefore, Answer C  
Question 25
R(A,B,C,D) is a relation. Which of the following does not have a lossless join, dependency preserving BCNF decomposition?
A
A->B, B->CD
B
A->B, B->C, C->D
C
AB->C, C->AD
D
A ->BCD
Database Design(Normal Forms)    GATE-CS-2001    
Discuss it


Question 25 Explanation: 
Background :
  • Lossless-Join Decomposition:
    Decomposition of R into R1 and R2 is a lossless-join decomposition if at least one of the following functional dependencies are in F+ (Closure of functional dependencies)

        R1 ∩ R2 → R1
       OR
        R1 ∩ R2 → R2
    
  • dependency preserving :
    Decomposition of R into R1 and R2 is a dependency preserving decomposition if closure of functional dependencies after decomposition is same as closure of of FDs before decomposition.
    A simple way is to just check whether we can derive all the original FDs from the FDs present after decomposition.

Question : We know that for lossless decomposition common attribute should be candidate key in one of the relation. A) A->B, B->CD R1(AB) and R2(BCD) B is the key of second and hence decomposition is lossless. B) A->B, B->C, C->D R1(AB) , R2(BC), R3(CD) B is the key of second and C is the key of third, hence lossless. C) AB->C, C->AD R1(ABC), R2(CD) C is key of second, but C->A violates BCNF condition in ABC as C is not a key. We cannot decompose ABC further as AB->C dependency would be lost. D) A ->BCD Already in BCNF. Therefore, Option C AB->C, C->AD is the answer.
Question 26
Which of the following relational calculus expressions is not safe? GATECS2000Q49
A
A
B
B
C
C
D
D
Database Design(Normal Forms)    GATE-CS-2001    
Discuss it


Question 26 Explanation: 
A tuple relational calculus expression may at times generate an infinite relation. It may also contain values that do not even appear in the database. Such expressions are said to be unsafe. A safe tuple relational calculus expression is the one which surely generates finite results. To pose a restriction over the unsafety of expressions in tuple relational calculus there is a concept of domain of a tuple relational formula denoted by dom (P) is the set of values referenced by P i.e. values there in P or values in tuple of a relation mentioned in P. Eg: The expression {t | ¬ (t € R)} is not safe because there are infinitely many tuples that do not occur in R relation . In the above question Options (A), (B) and option (D) produce finite set of tuples as each gives out tuples restricted from a particular relation and hence are safe. Option (C) produces infinite number of tuples as it generates all the tuples not in R1 i.e. it can have tuples from any other relation other than R1.Hence it is not safe. codex.cs.yale.edu/avi/db-book/db6/slide-dir/PPT-dir/ch6.ppt This solution is contributed by Yashika Arora.  
Question 27
Consider a relation geq which represents “greater than or equal to”, that is, (x,y) ∈ geq only if y >= x.
create table geq
( 
  ib integer not null
  ub integer not null
  primary key 1b
  foreign key (ub) references geq on delete cascade 
)
Which of the following is possible if a tuple (x,y) is deleted?
A
A tuple (z,w) with z > y is deleted
B
A tuple (z,w) with z > x is deleted
C
A tuple (z,w) with w < x is deleted
D
The deletion of (x,y) is prohibited
Database Design(Normal Forms)    GATE-CS-2001    
Discuss it


Question 27 Explanation: 
In the above question, the relation schema is ( lb , ub ), where lb is the primary key, and ub is the foreign key which is referencing the primary key of its own relation. Hence the table geq is both the master ( which has the referenced key ) as well as the child table (which has the referencing key). The table has two constraint, one is that if there is a tuple ( x, y ), then y is greater than or equal to x, And the other is referential integrity constraint, which is on-cascade-delete on the foreign key. On-cascade-delete says, that "When the referenced row is deleted from the other table (master table), then delete also from the child table".
Suppose the instance in the given relation is the following:

x y
-----
5 6
4 5
3 4
6 6
Now if we delete tuple (5,6) then tuple ( 4,5 ) should also be deleted ( as 5 in the tuple (4, 5) was referencing to 5 in the tuple(5,6) which no longer exist, hence the referencing tuple should also be deleted), and as (4,5) got deleted hence tuple (3,4) should also be deleted for the same reason. Therefore in total 3 rows have to be deleted if tuple ( 5,6 ) is deleted. Now from the above instance we can say that if (x,y), i.e. ( 5,6 ) gets deleted then a tuple ( z, w) i.e, ( 3, 4) is also deleted. And we can see here that w < x. Hence option C.
Question 28
Given the relations
employee (name, salary, deptno) and
department (deptno, deptname, address)
Which of the following queries cannot be expressed using the basic relational algebra operations (U, -, x, \pi, \sigma , p)?
A
Department address of every employee
B
Employees whose name is the same as their department name
C
The sum of all employees’ salaries
D
All employees of a given department
Database Design(Normal Forms)    GATE-CS-2000    
Discuss it


Question 28 Explanation: 
Question 29
Given the following relation instance.
x  y  z
1  4  2
1  5  3
1  6  3
3  2  2  
Which of the following functional dependencies are satisfied by the instance? (GATE CS 2000)
A
XY -> Z and Z -> Y
B
YZ -> X and Y -> Z
C
YZ -> X and X -> Z
D
XZ -> Y and Y -> X
Database Design(Normal Forms)    GATE-CS-2000    
Discuss it


Question 29 Explanation: 
Question 30
Consider an Entity-Relationship (ER) model in which entity sets E1 and E2 are connected by an m : n relationship R12, E1 and E3 are connected by a 1 : n (1 on the side of E1 and n on the side of E3) relationship R13. E1 has two single-valued attributes a11 and a12 of which a11 is the key attribute. E2 has two single-valued attributes a21 and a22 is the key attribute. E3 has two single-valued attributes a31 and a32 of which a31 is the key attribute. The relationships do not have any attributes. If a relational model is derived from the above ER model, then the minimum number of relations that would be generated if all the relations are in 3NF is ___________.
A
2
B
3
C
4
D
5
Database Design(Normal Forms)    GATE-CS-2015 (Set 1)    
Discuss it


Question 30 Explanation: 
Entity E1.
a1  a12
--------
a11 is key

Entity E2
a21  a22
--------
a22 is key

Entity E3
a31  a32
--------
a31 is key

R12 is m:n Relationship between E1 and E2
R12
a11     a22
-------------
(a11, a22) is key.


R13 is 1:n Relationship between E1 and E3
R13
a11   a31
-----------
(a11, a31) is key.


We need minimum no. of tables. 
Can we remove any of the above tables without 
loosing information and keeping the relations in 3NF?


We can combine R13 and R12 into one.  
a11   a31   a22
------------------
(a11, a31, a22) is key.


The relation is still in 3NF as for every functional 
dependency X -> A, one of the following holds
1) X is a superkey or
2) A-X is prime attribute 
Question 31
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+ is closure of F?
A
{P,R}→{S,T}
B
{P,R}→{R,T}
C
{P,S}→{S}
D
{P,S,U}→{Q}
Database Design(Normal Forms)    GATE-CS-2015 (Set 3)    
Discuss it


Question 31 Explanation: 
A functional dependency X -> Y is trivial if Y is a subset of X.
Question 32
Consider the following entity relationship diagram (ERD), where two entities E1 and E2 have a relation R of cardinality 1 : m.
err
The attributes of E1 are A11, A12 and A13 where A11 is the key attribute. The attributes of E2 are A21, A22 and A23 where A21 is the key attribute and A23 is a multi-valued attribute. Relation R does not have any attribute. A relational database containing minimum number of tables with each table satisfying the requirements of the third normal form (3NF) is designed from the above ERD. The number of tables in the database is
A
2
B
3
C
5
D
4
Database Design(Normal Forms)    GATE-IT-2004    
Discuss it


Question 32 Explanation: 
Step 1: 1NF T1: A11, A12, A13 T2: A11, A21, A22, A23  //because A23 is multivalued ,it has to be included in Key attribute Step 2: 2NF // A23 is Multivalued attribute and not allowed in 2NF therefore new tables are: T1: A11, A12, A13 T2: A11, A21, A22 T3: A21, A23 Step 3: 3NF // There is no transitive functional dependency in all tables , So in 3NF Therefore answer is B
Question 33
A relational database contains two tables student and department in which student table has columns roll_no, name and dept_id and department table has columns dept_id and dept_name. The following insert statements were executed successfully to populate the empty tables:
Insert into department values (1, 'Mathematics')
 Insert into department values (2, 'Physics')

Insert into student values (l, 'Navin', 1)

Insert into student values (2, 'Mukesh', 2)

Insert into student values (3, 'Gita', 1)
How many rows and columns will be retrieved by the following SQL statement?
Select * from student, department
A
0 row and 4 columns
B
3 rows and 4 columns
C
3 rows and 5 columns
D
6 rows and 5 columns
Database Design(Normal Forms)    GATE-IT-2004    
Discuss it


Question 33 Explanation: 
  Simple,Cartesian product of two tables will result Rows = 3*2=6 Columns= 3+2=5 So Answer is D  
Question 34
Consider the entities 'hotel room', and 'person' with a many to many relationship 'lodging' as shown below: er00If we wish to store information about the rent payment to be made by person (s) occupying different hotel rooms, then this information should appear as an attribute of  
A
Person
B
Hotel Room
C
Lodging
D
None of these
Database Design(Normal Forms)    Gate IT 2005    
Discuss it


Question 34 Explanation: 
  Lodging is the only attribute relating person and hotel room.
Question 35
A table has fields Fl, F2, F3, F4, F5 with the following functional dependencies   F1 → F3   F2→ F4   (F1 . F2) → F5 In terms of Normalization, this table is in  
A
1 NF
B
2 NF
C
3 NF
D
none
Database Design(Normal Forms)    Gate IT 2005    
Discuss it


Question 35 Explanation: 
  First Normal Form A relation is in first normal form if every attribute in that relation is singled valued attribute. Second Normal Form A relation is in 2NF iff it has No Partial Dependency, i.e., no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table. This table has Partial Dependency f1->f3, f2-> f4 given (F1,F2) is Key So answer is A
Question 36
Which of the following is NOT a superkey in a relational schema with attributes V, W, X, Y, Z and primary key V Y ?
A
V X Y Z
B
V W X Z
C
V W X Y
D
V W X Y Z
Database Design(Normal Forms)    GATE-CS-2016 (Set 1)    
Discuss it


Question 36 Explanation: 
Super key = Candidate Key + other attributes. But option B does not include Y which is a part of PK or candidate key.
Question 37
Let R (A, B, C, D, E, P, G) be a relational schema in which the following functional depen­dencies are known to hold: AB → CD, DE → P, C → E, P → C and B → G. The relational schema R is
A
in BCNF
B
in 3NF, but not in BCNF
C
in 2NF, but not in 3NF
D
not in 2NF
Database Design(Normal Forms)    Gate IT 2008    
Discuss it


Question 37 Explanation: 
Candidate key = AB
B->G is partial dependency
So, not in 2NF
Question 38
Which option is true about the SQL query given below?
SELECT firstName, lastName 
FROM Employee 
WHERE lastName BETWEEN 'A%' AND 'D%';
A
It will display all the employees having last names starting with the alphabets 'A' till 'D' inclusive of A and exclusive of D.
B
It will throw an error as BETWEEN can only be used for Numbers and not strings.
C
It will display all the employees having last names starting from 'A' and ending with 'D'.
D
It will display all the employees having last names in the range of starting alphabets as 'A' and 'D' excluding the names starting with 'A' and 'D'.
Database Design(Normal Forms)    GATE 2017 Mock    
Discuss it


Question 38 Explanation: 
The BETWEEN operator works with the range of character values also.
Question 39
Which of the given options define a transaction correctly?
A
A transaction consists of DDL statements on the database schema.
B
A transaction consists of COMMIT or ROLLBACK in a database session.
C
A transaction consists of either a collection of DML statements or a DDL or DCL or TCL statement to form a logical unit of work in a database session.
D
A transaction consists of collection of DML and DDL statements in different sessions of the database.
Database Design(Normal Forms)    GATE 2017 Mock    
Discuss it


Question 39 Explanation: 
A database transaction consists of one or more DML statements to constitute one consistent change in data, or a DDL statement or a DCL command (GRANT or REVOKE). It starts with the first DML statement and ends with a DCL or DDL or TCL (COMMIT or ROLLBACK) command. Note that DDL and DCL commands hold auto commit feature.
There are 39 questions to complete.

GATE CS Corner


See Placement Course for placement preparation, GATE Corner for GATE CS Preparation and Quiz Corner for all Quizzes on GeeksQuiz.