Question 1 
Consider the following relational schema.
Students(rollno: integer, sname: string) Courses(courseno: integer, cname: string) Registration(rollno: integer, courseno: integer, percent: real)Which of the following queries are equivalent to this query in English?
"Find the distinct names of all students who score more than 90% in the course numbered 107"
I, II, III and IV  
I, II and III only  
I, II and IV only  
II, III and IV only 
Discuss it
Question 1 Explanation:
Option A: This is a SQL query expression. It first perform a cross product of Students and Registration, then WHERE clause only keeps those rows in the cross product set where the student is registered for course no 107, and percentage is > 90. Then select distinct statement gives the distinct names of those students as the result set.
Option B: This is a relational algebra expression. It first perform a NATURAL JOIN of Students and Registration (NATURAL JOIN implicitly joins on the basis of common attribute, which here is rollno ), then the select operation( sigma) keeps only those rows where the student is registered for courseno 107, and percentage is > 90. And then the projection operation (pi) projects only distinct student names from the set. Note: Projection operation (pi) always gives the distinct result.
Option C: This is a Tuple Relational Calculus (TRC) language expression, It is not a procedural language (i.e. it only tells “what to do”, not “how to do”). It just represents a declarative mathematical expression. Here T is a Tuple variable. From left to right, it can be read like this, “It is a set of tuples T, where, there exists a tuple S in Relation Students, and there exist a tuple R in relation Registration, such that S.rollno = R.rollno AND R.couseno = 107 AND R.percent > 90 AND T.sname = S.sname”. And the schema of this result is (sname), i.e. each tuple T will contain only student name, because only T.sname has been defined in the expression. As TRC is a mathematical expression, hence it is expected to give only distinct result set.
Option D: This is a Domain Relational Calculus (DRC) language expression. This is also not procedural. Here SN is a Domain Variable. It can be read from left to right like this “The set of domain variable SN, where, there exist a domain variable SR , and a domain variable Rp, such that, SN and SR domain variables is in relation Students and SR,107,RP is a domain variables set in relation Registration, AND RP > 90 “ Above, SN represents sname domain attribute in Students relation, SR represents rollno domain attribute in Students relation, and RP represents percentage domain attribute in Registration relation. The schema for the result set is (SN), i.e. only student name. As DRC is a mathematical expression, hence it is expected to give only distinct result set.
Question 2 
Given the basic ER and relational models, which of the following is INCORRECT?
An attribute of an entity can have more than one value  
An attribute of an entity can be composite  
In a row of a relational table, an attribute can have more than one value  
In a row of a relational table, an attribute can have exactly one value or a NULL value 
Discuss it
Question 2 Explanation:
The term ‘entity’ belongs to ER model and the term ‘relational table’ belongs to relational model.
A and B both are true. ER model supports both multivalued and composite attributes See this for more details.
(C) is false and (D) is true. In Relation model, an entry in relational table can can have exactly one value or a NULL.
Question 3 
Suppose (A, B) and (C,D) are two relation schemas. Let r1 and r2 be the corresponding relation instances. B is a foreign key that refers to C in r2. If data in r1 and r2 satisfy referential integrity constraints, which of the following is ALWAYS TRUE?
A  
B  
C  
D 
Discuss it
Question 3 Explanation:
See Question 3 of http://www.geeksforgeeks.org/databasemanagementsystemset2/
Question 4 
Consider the following relations A, B, C. How many tuples does the result of the following relational algebra expression contain? Assume that the schema of A U B is the same as that of A.
Table A Id Name Age  12 Arun 60 15 Shreya 24 99 Rohit 11 Table B Id Name Age  15 Shreya 24 25 Hari 40 98 Rohit 20 99 Rohit 11 Table C Id Phone Area  10 2200 02 99 2100 01
7  
4  
5  
9 
Discuss it
Question 4 Explanation:
Question 5 
Consider a relational table r with sufficient number of records, having attributes A1, A2,…, An and let 1 <= p <= n. Two queries Q1 and Q2 are given below.
The database can be configured to do ordered indexing on Ap or hashing on Ap. Which of the following statements is TRUE?
Ordered indexing will always outperform hashing for both queries  
Hashing will always outperform ordered indexing for both queries  
Hashing will outperform ordered indexing on Q1, but not on Q2  
Hashing will outperform ordered indexing on Q2, but not on Q1. 
Discuss it
Question 5 Explanation:
If record are accessed for a particular value from table, hashing will do better. If records are accessed in a range of values, ordered indexing will perform better. See this for more details.
Question 7 
1) Let R and S be two relations with the following schema
R (P,Q,R1,R2,R3)
S (P,Q,S1,S2)
Where {P, Q} is the key for both schemas. Which of the following queries are equivalent?
Only I and II  
Only I and III  
Only I, II and III  
Only I, III and IV 
Discuss it
Question 7 Explanation:
See Question 1 of http://www.geeksforgeeks.org/databasemanagementsystemsset7/
Question 8 
Consider the following ER diagram.
The minimum number of tables needed to represent M, N, P, R1, R2 is
2  
3  
4  
5 
Discuss it
Question 8 Explanation:
Answer is B, i.e, 3 minimum tables.
M, P are strong entities hence they must be represented by separate tables.
Manytoone and onetomany relationship sets that are total on the manyside can be represented by adding an extra attribute to the “many” side, containing the primary key of the “one” side. ( This way no extra table will be needed for Relationship sets )
M table is modified to include primary key of P side(i.e. P1). N is weak entity, and is modified to include primary key of P (i.e, P1).
Therefore there would be minimum of 3 tables with schema given below :
M ( M1, M2, M3, P1) P ( P1, P2 ) N ( P1, N1, N2 )Note: This modification of a table in the case of onemany or manyone to include relationship set at the many side works well, but only in the case when the relationship set doesn't have its own attributes. If the relationship set has its own attribute then we need to make a separate table for the relationship set also.
Question 9 
Consider the data given in above question. Which of the following is a correct attribute set for one of the tables for the correct answer to the above question?
{M1, M2, M3, P1}  
{M1, P1, N1, N2}  
{M1, P1, N1}  
{M1, P1} 
Discuss it
Question 9 Explanation:
As given in the explanation http://quiz.geeksforgeeks.org/gategatecs2008question82/
We get 3 tables.
M: {M1, M2, M3, P1}
P: {P1, P2}
N: {P1, N1, N2}
The only attribute set that matches the given table sets is A. Therefore, option A
Question 10 
Information about a collection of students is given by the relation studinfo(studId, name, sex). The relation enroll(studId, courseId) gives which student has enrolled for (or taken) that course(s). Assume that every course is taken by at least one male and at least one female student. What does the following relational algebra expression represent?
Courses in which all the female students are enrolled.  
Courses in which a proper subset of female students are enrolled.  
Courses in which only male students are enrolled.  
None of the above 
Discuss it
Question 10 Explanation:
See Question 1 of http://www.geeksforgeeks.org/databasemanagementsystemsset11/
Question 11 
Consider the relation employee(name, sex, supervisorName) with name as the key. supervisorName gives the name of the supervisor of the employee under consideration. What does the following Tuple Relational Calculus query produce?
Names of employees with a male supervisor.  
Names of employees with no immediate male subordinates.  
Names of employees with no immediate female subordinates.  
Names of employees with a female supervisor. 
Discuss it
Question 11 Explanation:
See question 2 of http://www.geeksforgeeks.org/databasemanagementsystemsset11/
Question 12 
Consider a join (relation algebra) between relations r(R)and s(S) using the nested loop method. There are 3 buffers each of size equal to disk block size, out of which one buffer is reserved for intermediate results. Assuming size(r(R)) < size(s(S)), the join will have fewer number of disk block accesses if
relation r(R) is in the outer loop.  
relation s(S) is in the outer loop.  
join selection factor between r(R) and s(S) is more than 0.5.
 
join selection factor between r(R) and s(S) is less than 0.5. 
Discuss it
Question 12 Explanation:
Nested loop join is one of the methods to implement database in memory. A nested loop join is an algorithm that joins two sets by using two nested loops.
According to nested join,given relation R and S
For each tuple r in R do
For each tuple s in S do
If r and s satisfy the join condition
Then output the tuple <r,s>
Cost estimations for the above loop:
– b(R) and b(S) number of blocks in R and in S
– Each block of outer relation is read once
– Inner relation is read once for each block of outer relation
Summing up : IO= b(R)+b(R)*b(S) total IO operations
Lets assume R>S i.e b(R) =10 and b(s) =3
Now, if R is outer relation then, IO= 10+10*3=40
if S is outer relation then IO=3+10*3=33
As it can be observed , that total IO is lesser if the value of outer variable is less and as it is already given that R<S.Therefore, Relation r(R) should be in the outer loop to have fewer number of disk block accesses.
References:
Question 13 
A  
B  
C  
D 
Discuss it
Question 13 Explanation:
The Relational Algebra expression in the question above, does 4 operations, step by step ( innermost braces first ) .
1. Select those tuples from relation r which satisfies expression/condition F1, say the result of this operation is set A. 2. Select those tuples from set A which satisfies expression/condition F2, say the result of this operation is set B. 3. Select attrributes set A2 from set B, say the result of this operation is set C. 4. Select attrributes set A1 from set C, say the result is set D which is the final result.Now to optimize this expression, we can combine operations/steps 1 and 2 by AND operator between F1 and F2 condition, like F1 ^ F2, and instead of selecting first attribute set A2, we can directly select attribute set A1 from the result of the combined operation, which is represented by expression in Option A .
Question 14 
Consider the relational schema given below, where eId of the relation dependent is a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation.
employee (empId, empName, empAge) dependent(depId, eId, depName, depAge)Consider the following relational algebra query: The above query evaluates to the set of empIds of employees whose age is greater than that of
some dependent.  
all dependents.  
some of his/her dependents  
all of his/her dependents. 
Discuss it
Question 14 Explanation:
Question 15 
Let E1 and E2 be two entities in an E/R diagram with simple singlevalued attributes. R1 and R2 are two relationships between E1 and E2, where R1 is onetomany and R2 is manytomany. R1 and R2 do not have any attributes of their own. What is the minimum number of tables required to represent this situation in the relational model?
2  
3  
4  
5 
Discuss it
Question 15 Explanation:
The answer is B, i.e minimum 3 tables.
Strong entities E1 and E2 are represented as separate tables.
In addition to that manytomany relationships(R2) must be converted as seperate table by having primary keys of E1 and E2 as foreign keys.
Onetomany relaionship (R1) must be transferred to 'many' side table(i.e. E2) by having primary key of one side(E1) as foreign key( this way we need not to make a seperate table for R1).
Let relation schema be E1(a1,a2) and E2( b1,b2).
Relation E1( a1 is the key)
a1 a2  1 3 2 4 3 4Relation E2( b1 is the key, a1 is the foreign key, hence R1(onemany) relationship set satisfy here )
b1 b2 a1  7 4 2 8 7 2 9 7 3Relation R2 ( {a1, b1} combined is the key here , representing manymany relationship R2 )
a1 b1  1 7 1 8 2 9 3 9Hence we will have minimum of 3 tables.
Question 16 
In a schema with attributes A, B, C, D and E following set of functional dependencies are given
A → B
A → C
CD → E
B → D
E → A
Which of the following functional dependencies is NOT implied by the above set?
CD → AC  
BD → CD  
BC → CD  
AC → BC 
Discuss it
Question 16 Explanation:
Please see this article to learn the concept
http://geeksquiz.com/equivalenceoffunctionaldependenciessets/
Question 17 
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) > PRICEThe 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 
Discuss it
Question 17 Explanation:
Volume, Number > Year is partial dependency. So it does not follow 2NF. But decomposed relation follows.
Question 18 
Which of the following relational query languages have the same expressive power?
 Relational algebra
 Tuple relational calculus restricted to safe expressions
 Domain relational calculus restricted to safe expressions
II and III only  
I and II only  
I and III only  
I, II and III 
Discuss it
Question 18 Explanation:
Relational algebra is a procedural query language where we input  relations and it yields relations as output. It provides method to get the result. It is performed recursively on a relation and the in between results are relations(output).
Basic set of operations for the relational model. Relational calculus is a non  procedural query language. It provides the query to get result. Higher level declarative language for specifying relational queries. Tupple Relational Calculus operates on each tupple.
Domain Relational Calculus operates on each column or attribute. Safe expression means fixed no. of tupple or column or attribute as a result But all of them has same expressive power. Just different ways to do so.
This solution is contributed by Mohit Gupta.
Question 19 
A Relation R with FD set {A>BC, B>A, A>C, A>D, D>A}. How many candidate keys will be there in R?
1  
2  
3  
4 
Discuss it
Question 19 Explanation:
Simple candidate key means single attributed key. As (A)^{+} = {A, B, C, D}, (B)^{+} = {B,A, C, D}, (C)^{+} = {C} and (D)^{+} = {D, A, B, C}. So, A, B and D are candidate keys which are simple as well. So, correct option is 3.
Question 20 
What is the min and max number of tables required to convert an ER diagram with 2 entities and 1 relationship between them with partial participation constraints of both entities?
Min 1 and max 2
 
Min 1 and max 3
 
Min 2 and max 3
 
Min 2 and max 2

Discuss it
Question 20 Explanation:
Maximum number of tables required is 3 in case of many to many relationships between entities. Minimum number of tables is 1 in case of unary relationship and total participation of atleast one entity. But in case of partial participation of both entities, minimum number of tables required is 2.
There are 20 questions to complete.