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?
3  
4  
5  
6 
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
in 1NF, but not in 2NF.  
in 2NF, but not in 3NF.  
in 3NF, but not in BCNF.  
in BCNF 
Discuss it
Question 2 Explanation:
The table is not in 2nd Normal Form as the nonprime attributes are dependent on subsets of candidate keys.
The candidate keys are AD, BD, ED and FD. In all of the following FDs, the nonprime attributes are dependent on a partial candidate key.
A > BC
B > CFH
F > EG
Question 3 
Which of the following is TRUE?
Every relation in 3NF is also in BCNF  
A relation R is in 3NF if every nonprime attribute of R is fully functionally dependent on every
key of R  
Every relation in BCNF is also in 3NF  
No relation can be in both BCNF and 3NF 
Discuss it
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 hostelWhich one of the following option is INCORRECT?
BankAccount_Num is candidate key  
Registration_Num can be a primary key  
UID is candidate key if all students are from the same country  
If S is a superkey such that S∩UID is NULL then S∪UID is also a superkey 
Discuss it
Question 4 Explanation:
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.
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?
The schema is in BCNF  
The schema is in 3NF but not in BCNF  
The schema is in 2NF but not in 3NF  
The schema is not in 2NF 
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 RSince (sname, city) forms a candidate key, there is no nontirvial 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 > PriceAssume {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 
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 nonprime attributes (Publisher Year) are transitively dependent on key [Title, Author].
 Book is in 2NF because every nonprime 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, nonprime attributes (attributes that do not occur in any candidate key) are Publisher, Year and Prince
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?
{E, F}  
{E, F, H}  
{E, F, H, K, L}  
{E} 
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} ≠ RSecond 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 singlevalued 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. 
Discuss it
Question 8 Explanation:
S1: Every table with two singlevalued attributes is in 1NF, 2NF, 3NF and BCNF.A relational schema R is in BCNF iff in Every nontrivial 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
 If {A>B} exists then BCNF since {A}+ = AB = R
 If {B>A} exists then BCNF since {B}+ = AB = R
 If {A>B,B>A} exists then BCNF since A and B both are Super Key now.
 If {No non trivial Functional Dependency} then default 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.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
5  
6  
7  
8 
Discuss it
Question 9 Explanation:
Maximum no. of possible superkeys for a table with n attributes = 2^(n1)
Here, n = 4.
So, the possible superkeys = 2^{41} = 8
The possible superkeys are : E, EH, EG, EF, EGH, EFH, EFG, EFGH
Question 10 
Given the STUDENTS relation as shown below.
For (StudentName, StudentAge) to be the key for this instance, the value X should not be equal to
18  
19 
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?
BCNF is stricter than 3NF  
Lossless, dependencypreserving decomposition into 3NF is always possible  
Lossless, dependencypreserving decomposition into BCNF is always possible  
Any relation with two attributes is in BCNF 
Discuss it
Question 11 Explanation:
See question 1 of http://www.geeksforgeeks.org/databasemanagementsystemsset8/
Question 12 
Let r be a relation instance with schema R = (A, B, C, D). We define r_{1} = Π_{A, B, C} (r) and r_{2} = Π_{A.D} (r). Let s = r_{1} * r_{2} where * denotes natural join. Given that the decomposition of r into r_{1} and r_{2} is lossy, which one of the following is TRUE?
s ⊂ r  
r ∪ s  
r ⊂ s  
r * s = s 
Discuss it
Question 12 Explanation:
See Question 1 of http://www.geeksforgeeks.org/databasemanagementsystemsset10/
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?
AE, BE  
AE, BE, DE  
AEH, BEH, BCH  
AEH, BEH, DEH 
Discuss it
Question 13 Explanation:
See question 3 of http://www.geeksforgeeks.org/databasemanagementsystemsset10/
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 ?
1  
2  
3  
4 
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 → nameThe highest normal form of this relation scheme is
2 NF  
3 NF  
BCNF  
4NF 
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).
The condition in join is "(sex = female ^ x = male ^ marks ≤ m)"
names of girl students with the highest marks  
names of girl students with more marks than some boy student  
names of girl students with marks not less than some boy students4)  
names of girl students with more marks than all the boy students 
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 selfCartesian 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) → GradeThe 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 
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.
Zero  
More than zero but less than that of an equivalent 3NF decomposition  
Proportional to the size of F+  
Indeterminate 
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?
Relational algebra is more powerful than relational calculus  
Relational algebra has the same power as relational calculus  
Relational algebra has the same power as safe relational calculus  
None of the above 
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).
Dependencypreservation  
Losslessjoin  
BCNF definition  
3NF definition 
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 functionally determines B and B functionally determines C
 
A functionally determines B and B does not functionally determine C  
B does not functionally determine C  
A does not functionally determine B and B does not functionally determine C 
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/databasenormalizationintroduction/ 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
dependency preserving and lossless join  
lossless join but not dependency preserving  
dependency preserving but not lossless join  
not dependency preserving and not lossless join 
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.
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
LosslessJoin Decomposition:
Decomposition of R into R1 and R2 is a losslessjoin 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?
List of all vertices adjacent to a given vertex  
List all vertices which have self loops  
List all vertices which belong to cycles of less than three vertices  
List all vertices reachable from a given vertex 
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 is always equal to
A  
B  
C  
D 
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>B, B>CD  
A>B, B>C, C>D  
AB>C, C>AD  
A >BCD 
Discuss it
Question 25 Explanation:
Background :
LosslessJoin Decomposition:
Decomposition of R into R1 and R2 is a losslessjoin 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 26 
A  
B  
C  
D 
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/dbbook/db6/slidedir/PPTdir/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 tuple (z,w) with z > y is deleted  
A tuple (z,w) with z > x is deleted  
A tuple (z,w) with w < x is deleted  
The deletion of (x,y) is prohibited 
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 oncascadedelete on the foreign key.
Oncascadedelete 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 6Now 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, , , p)?
Department address of every employee  
Employees whose name is the same as their department name  
The sum of all employees’ salaries  
All employees of a given department 
Discuss it
Question 28 Explanation:
See question 1 of http://www.geeksforgeeks.org/databasemanagementsystemset1/
Question 29 
Given the following relation instance.
x y z 1 4 2 1 5 3 1 6 3 3 2 2Which of the following functional dependencies are satisfied by the instance? (GATE CS 2000)
XY > Z and Z > Y  
YZ > X and Y > Z  
YZ > X and X > Z  
XZ > Y and Y > X 
Discuss it
Question 29 Explanation:
See question 2 of http://www.geeksforgeeks.org/databasemanagementsystemset1/
Question 30 
Consider an EntityRelationship (ER) model in which entity sets E1 and E2 are connected by an m : n relationship R_{12}, E1 and E3 are connected by a 1 : n (1 on the side of E1 and n on the side of E3) relationship R_{13}.
E1 has two singlevalued attributes a_{11} and a_{12} of which a_{11} is the key attribute. E2 has two singlevalued attributes a_{21} and a_{22} is the key attribute. E3 has two singlevalued attributes a_{31} and a_{32} of which a_{31} 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 ___________.
2  
3  
4  
5 
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) AX 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?
{P,R}→{S,T}  
{P,R}→{R,T}  
{P,S}→{S}  
{P,S,U}→{Q} 
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.
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 multivalued 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
2  
3  
5  
4 
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
0 row and 4 columns  
3 rows and 4 columns  
3 rows and 5 columns  
6 rows and 5 columns 
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:
If 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
Person  
Hotel Room  
Lodging  
None of these 
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
1 NF  
2 NF  
3 NF  
none 
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 nonprime 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 ?
V X Y Z  
V W X Z  
V W X Y  
V W X Y Z 
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 dependencies are known to hold: AB → CD, DE → P, C → E, P → C and B → G. The relational schema R is
in BCNF  
in 3NF, but not in BCNF  
in 2NF, but not in 3NF  
not in 2NF 
Discuss it
Question 37 Explanation:
Candidate key = AB
B>G is partial dependency
So, not in 2NF
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%';
It will display all the employees having last names starting with the alphabets 'A' till 'D' inclusive of A and exclusive of D.
 
It will throw an error as BETWEEN can only be used for Numbers and not strings.  
It will display all the employees having last names starting from 'A' and ending with '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'.

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 transaction consists of DDL statements on the database schema.  
A transaction consists of COMMIT or ROLLBACK in a database session.  
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.  
A transaction consists of collection of DML and DDL statements in different sessions of the database.

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.