DBMS Test - 1 - PDF Flipbook
DBMS Test - 1
374 Views
54 Downloads
PDF 5,677,302 Bytes
GATE
CSE
Database
ManagementSystem
Test-01Solutions
DATABASE MANAGEMENT SYSTEM
1. For a database relation R(a, b, c, d), where the domains of a, b,
c, d include only atomic values, only the following functional
dependencies and those that can be inferred from them hold:
A→cb→d
The relation is
a) In 1st NF but not in 2nd NF
b) In 2nd NF but not in 3rd NF
c) In 3rd NF
d) None of the above
Answer: (a)
Solution:
Key of the relation is ab is partial dependency. Hence relation is
in 1 NF.
2. 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 ib
(foreign key(ub) references geq on delete cascade)
}
Which of the following is possible if a tuple (x, y) is deleted?
1
a) A tuple (z, w) with z > y is deleted
b) A tuple (z, w) with w > x is deleted
c) A tuple (z, w) with w < x is deleted
d) The deletion of (x, y) is prohibited
Answer: (c)
Solution:
On delete cascade says, that when the referenced row is deleted
from the parent table (master table) then delete also from the
child table".
XY
24
12
01
44
Now if we delete tuple (2, 4) then tuple (1, 2) should also be
deleted (as 2 in the tuple (1, 2) was referencing to 2 in the tuple
(2, 4) which no longer exist, hence the referencing tuple should
also be deleted), and as (1, 2) got deleted hence tuple (0, 1)
should also be deleted for the same reason. Therefore in total 3
rows have to be deleted if tuple (2, 4) is delete. Now from the
above instance we can say that if (x, y), i.e., (2, 4) gets deleted
then a tuple (z, w) i.e, (0, l) is also deleted. And we can see here
that w < x. Hence option c.
2
3. Which of the following is the recovery management technique
in DBMS?
a) 2PC (Two Phase Commit)
b) Backup
c) immediate update
d) All of the above
Answer: (d)
Solution:
Portable software is specially designed to run on different
computers with compatible operating systems and processors
without any machine dependent installation; it is sufficient to
transfer specificities directories and their contents.
4. Consider a relational table r with sufficient number of records,
having attributes
A1, A2, A3.... An and let 1 ≤ p ≤ n.
Two queries Q1 and Q2 are given below
Q1: π A1 … Ap (σ AP = c (r))
Where is a constant
Q2: π A1 …Ap (σ c1 ≤ Ap ≤ c2 (r))
Where c1 and c2 & are constants
The database can be configured to do order indexing on Ap or
hashing on Ap Which of the following statements is TRUE?
a) Order indexing will always outer form handling for both
queries
3
b) Hashing will always outperform ordered indexing for both
queries
c) Hashing will outperform ordered indexing on Q1 but not on
Q2
d) Hashing will outperform ordered indexing on Q2, but not on
Q1
Answer: (c)
Solution:
Q1: returns only few tuples that satisfies the condition, therefore
it requires Hashing.
Q2: returns the range of tuples based on condition therefore it
requires ordered index.
5. A transaction can include following basic database access
operations:
a) Read item(x)
b) Write item(x)
c) Both (a) and (b)
d) None of these
Answer: (c)
Solution:
A transaction can be defined as an action or series of action that
is carried out by a single user or application the contents of the
database. The operations can include retrieval (read), instruction
(write), deletion and modification. A transition must be either
4
completed or aborted. A transition is a program unit whose
execution may change the content of a database.
6. How to express that some person keeps animals as pets?
a)
b)
c)
d)
Answer: (a)
Solution:
It should be one to many relationships because a person may
have none or more animals as pets.
7. SELECT operation in SQL is equivalent to
a) the selection operation in relational algebra
b) the selection operation in relational algebra, except that
SELECT in SQL retains duplicates
c) the projection operation in relational algebra
d) the projection operation in relational algebra, except that
SELECT in SQL retains duplicates
Answer: (d)
Solution:
Relation Algebra eliminates the duplicates.
5
8. Assume e that, in the supplier’s 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 in not in 2NF
Answer: (b)
Solution:
P id is a primary key, sname and city is a candidate key. Hence
all are prime attributes and there is a relationship between prime
attributes hence it is not in BCNF but in 3 NF.
9. Consider a database that has the relation schema EMP (EmpID,
EmpNames, and DeptName). An instance of the schema EMP
and a SQL query on it are given below.
EMP
EmpID Emp Name Dept Name
1 XYA AA
2 XYB AA
3 XYC AA
4 XYD AA
5 XYE AB
6
6 XYF AB
7 XYG AB
8 XYH AC
9 XYI AC
10 XYJ AC
11 XYK AD
12 XYL AD
13 XYM AE
SELECT AVG(EC>Num)
FROM EC
WHERE (DeptName, Num)IN
(SELECTED DeptName, COUNT(EmpId) AS
EC(DeptName, Num)
FROM EMP
GROUP BY DeptName
The output of executing the SQL query is ______.
Answer: 2.6
10. Consider the following database table having A, B, C and Das
its four attributes and four possible candidate keys (I, II, III and
IV) for this table
A B CD
a1 b1 c1 d1
a2 b3 c2 d1
a1 b2 c1 d2
7
I. {B}
II. {B, C}
III. {A, D}
IV. {C, D}
if different symbols stand for different values in the table (e.9., 4
is d1 definitely not equal to d2), then which of the above could
not be the candidate key for the database table?
a) I and III only
b) III and IV only
c) II only
d) I only
Answer: (c)
Solution:
Attribute B has distic tuples. So, B is candidate key and {BC} is
super key because a candidate key is minimal super key.
Key {A, D} and{C, D} has distic tuples, so, both are candidate
key.
11. Which of the following checks cannot be carried out on the
input data to a system
a) Consistency check
b) Syntax check
c) Range check
d) All the above
Answer: (b)
8
Solution:
A consistency check is a test performed to determine if the data
has any internal conflict. So, it is not check anything about
semantics.
The syntax refers to grammatical structure.
A range check is a check to make sure a number is within
certain range; for example, to ensure that a value about to be
assigned to16-bit integer is within the capavcity of a 16-bit
integer(i.e., cheecking against wrap arouns).
12. Which 'Normal Form' is based on the concept of 'full functional
dependency' is
a) First Normal Form
b) Second Normal Form
c) Third Normal Form
d) Fourth Normal Form
Answer: (b)
Solution:
A functional dependency X → Y is a full functional dependency
if removal of any attribute from X will conclude that, the
dependency does not hold any more. Second normal form is
based on concept of full functional dependency.
9
13. Consider the following three schedules of transactions T1, T2
and T3. (Notation: In the following NYO represents the action
Y(R for read W for write) performed by transaction N on object
O)
(S1) 2RA 2WA 3RC 2WB 3WA
3WC 1RA 1RB 1WA 1WB
(S2) 3RC 2RA 2WA 2WB 3WA 1RA
1RB 1WA 1WB 3WC
(S3) 2RA 3RC 3WA 2WA 2WB
3WC 1RA 1RB 1WA 1WB
Which of the following statements is TRUE?
a) S1, S2 and S3 are all conflict equivalent to each other
b) No two of S1, S2 and S3 are conflict equivalent to each other
c) S2 is conflict equivalent to S3, but not to S1
d) S1 is conflict equivalent to S2, but not to S3
Answer: (d)
Solution:
Sl and S2 are conflict equivalent to serial schedule T2, T3, Tl.
S3 is not conflict equivalent as 2RA, 3WA (T2 < T3) and 3WA,
2WA (T3 < T2) are the conflict operations. There is no serial
schedule that satisfies both T2 < T3 and T3 < T2.
10
14. Let R = (A, B, C, D, E, F) be a relation scheme with the
following dependencies: C → F, E → A, EC → D, A → B.
Which of the following is a key for R?
a) CD
b) EC
c) AE
d) AC
Answer: (b)
Solution:
Compute closure for the options, if any closure covers all the
attributes of relation then that is the key for a relation, if so then
EC+ = ECFADB, hence EC is Key for relation.
15. Which of the following is not a UML DIAGRAM?
a) Use case
b) Class diagram
c) Analysis diagram
d) Swimland diagram
Answer: (c)
Solution:
An analysis diagram is a simplified activity diagram, which is
used to capture high level business processes and early models
of system behaviour and elements. It is not a UML diagram.
11
16. Two phase protocols in a database management system is:
a) a concurrency mechanism that is not deadlock free
b) a recovery protocol used for restoring a database after a crash
c) Any update to the system log done in 2-phases
d) not effective in Database
Answer: (a)
Solution:
Using locks that block process, 2PL may be subject to deadlock
that results from the mutual blocking two are more transitions.
Lock block data-access operations, Mutual blocking transitions
is results in deadlocks, where execution of transmissions is
shared and transitions can’t be successfully terminated.
17. Referential integrity is directly related to
a) Relation key
b) foreign key
c) primary key
d) candidate key
Answer: (b)
Solution:
A foreign key is field (or collection of fields) is one table that
uniquely identifies a row of another table or same table. This is
sometime called a reference key. A candidate key is a column,
or set of attribute, in a table that can uniquely identify any
database record without referring to any other idea.
12
18. Consider the following schedules involving two transactions.
Which one of the following statements is TRUE?
S1: r1(X); r1(Y); r2(X); r2(Y); w2(Y); w1(X)
S2: r1(X); r2(X); r2(Y); W2(Y); r1(Y); w1(X)
a) Both S1 and S2 are conflict serializable.
b) S1 is conflict serializable and S2 is not conflict serializable.
c) S1 is not conflict serializable and S2 is conflict serializable.
d) Both S1 and S2 are not conflict serializable.
Answer: (c)
Solution:
S1: r1(Y), w2(Y) are conflict, hence T1
Data Loading...