DBMS Test - 2 - PDF Flipbook
DBMS Test - 2
297 Views
0 Downloads
PDF 5,249,242 Bytes
GATE
CSE
Database
ManagementSystem
Test-02Solutions
DATABASE MANAGEMENT SYSTEM
1. Which of the following is aggregate function in SQL?
a) Avg
b) Select
c) Ordered by
d) Distinct
Answer: (a)
Solution:
'Avg' is an aggregate function is SOL. It takes the average of the
input set provided to the query.
2. Multi valued dependency among attribute is checked at which
level?
a) 2 NF
b) 3 NF
c) 4 NF
d) 5 NF
Answer: (c)
Solution:
A table is in 4NF if it is already in BCNF and it has no
multivalued dependences.
3. Given relations r(w, x) and s(y, z)the result of select distinct w,
x From r, s is guaranteed to be the same as r, provided:
a) r has no duplicates and s is non- empty
b) r and s have no duplicates
c) s has no duplicates and r is non-empty
1
d) r and s have the same number of tuples
Answer: (a)
Solution:
To have same rows of 'r' then 'r' should not have duplicates.
Also if ‘s’ is empty then overall result is also empty hence it
should not be empty.
4. Amongst the ACID Properties of a transaction, the 'Durability'
property requires that the changes made to the database by a
successful transaction Persist
a) Except in case of an Operating System crash
b) Except in case of a Disk crash
c) Except in case of a power failure
d) Always, even if there is a failure of any kind
Answer: (d)
Solution:
Irrespective of failures, the changes made by a committed
transaction must be permanent.
5. Consider the following ER diagram:
The minimum number of tables required to represent M, N, P,
R1, R2 is
a) 2
b) 3
2
c) 4
d) 5
Answer: (b)
Solution:
M [M1, M2, M3, P1]
P [P1, P2]
N [N1, N2, P1] considering p1 to be the key of the relation ‘P’
6. In DML, RECONNCT command cannot be used with
a) OPTIONAL Set
b) FIXED Set
c) MANDATOR Set
d) All of these
Answer: (b)
Solution:
Reconnect command: if the connection to the server is lost,
automatically try to reconnect, a single reconnect attempt is
made each time the connection is lost. To suppress reconnection
behavior, use “skip-reconnect”
Fixed: a member record cannot exist on its own. Moreover, once
it is inserted in a set occurrence, if is fixed if cannot be
reconnected to another set of occurrence
7. In functional dependency, Armstrong's inference rules refers to
a) Reflexive, Augmentation and Decomposition
b) Transitive, Augmentation and Reflexive
c) Augmentation, Transitive, Reflexive and Decomposition
3
d) Reflexive, Transitive and Decomposition
Answer: (b)
Solution:
Armstrong’s interface rule refers to transitive augmentation and
reflexive these 3 rules are:
Reflexivity: if, Y is a subset of X then X→Y
Augmentation: if X→Y, then XZ→YZ
Transitivity: if X→Y, and Y→Z then X→Z.
All these combine to given Armstrong interfaces.
8. Match the following:
List: I
R
A.
R
B.
R
C.
R
D.
List: II
1. One to one relationship
2. Relationship
3. Many to many relationship
4. Many to one relationship
4
Code:
AB C D
a) 3 4 2 1
b) 4 3 2 1
c) 2 3 4 1
d) 3 4 1 2
Answer: (c)
Solution:
ER-diagram Description
R relationship
Many to many
R relationship
R Many to one
relationship
One to one
R relation ship
9. In RDBMS, the constraint that no key attribute (column)may be
NULL is referred to as:
a) Referential integrity
b) Multi-valued dependency
c) Entity integrity
d) Functional dependency
Answer: (c)
5
Solution:
The entity integrity constraint states that primary keys cannot be
null. There must be a proper value in the primary key field.
The basic of referential integrity is foreign keys. A foreign key
in one table reference a primary key another table
10. A table 'student' with schema (roll, name, hostel, marks) and
another table 'hobby' with schema (roll, hobby, name) contains
records as shown below:
Table: Student
Roll Name Hostel Marks Roll Hobby Name
1798 Manoj Rathod 7 95 1798 Chess
2154 Soumic benerji 5 68 1798 Music
2369 Gumma reddy 7 86 2154 Music
2581 Pradeep pandse 6 92 2369 Swimming
2643 Sushal kulkarni 5 78 2581 Cricket
2711 Nithin kadam 8 72 2643 Chess
2872 Kiran Vora 5 92 2643 Hockey
2926 Manoj 5 94 2711 Volleyball
kunkalikar
2959 Hemanth 7 88 2872 football
3125 Rajesh Doshi 5 82 2926 Cricket
2959 Photography
3125 Music
3125 chess
6
The following SQL query is executed on the above tables:
Select hostel
From student natural join hobby
Where marks > = 75 and roll
Between 200 and 3000;
Relations S and H with the same schema as those of these two
tables respectively contain the same information as tuples. A
new
Relation S' is obtained by the following relational algebra
operation:
S’= π hostel ((σ s.roll = H.roll (σ marks>75 and roll >2000 and roll < 3000 (S)) X
(H))
The difference between the number of rows output by the SQL
statement and the number of tuples in S' is
a) 6
b) 4
c) 2
d) 0
Answer: (a)
Solution:
SQL query returns 10 rows (2154, 2369, 2581, 2643 with marks
92, 2711, 2872, 2926, 2959, 3125, and 3125 with Chess as
hobby)
Relational algebra query produces 4 rows hence difference is 6
rows.
7
11. Which of the following scenarios may lead to an irrecoverable
error in a database system?
a) A transaction writes a data item after it is read by an
uncommitted transaction
b) A transaction reads a data item after it is read by an
uncommitted transaction
c) A transaction reads a data item after it is written by a
committed transaction
d) A transaction reads a data item after it is written by an
uncommitted transaction
Answer: (d)
Solution:
Reading uncommitted data is called as dirty read. If transaction
reads uncommitted data and commits before the transaction that
writes data item leads to non-recoverable schedule if and if
transaction that writes data item rolled back.
12. Consider the following two commands C1 and C2 on the
relation R from an SQL database;
C1: drop table R;
C2: delete from R;
Which of the following statements is TBUE?
I. Both Cl and C2 delete the schema for R.
II. C2 retains relation R but deletes all tuples in R
III. C1 deletes not only all tuples of R, but also the schema for
R.
8
a) I only
b) I and II only
c) II and III only
d) I, II and III
Answer: (c)
Solution:
Drop command removes all tuples of the relation as well as
schema also
Delete command is SQL removes tuples from the relation but
rain but retain the schema of table in database
13. The 'command' used to change contents of one database using
the contents of another database by linking them on a common
key field?
a) Replace
b) Join
c) Change
d) Update
Answer: (a)
Solution:
Join command is used to change contents of one database using
the contents of another database by linking them on a common
field.
14. In generalization differences between number of an entry is
a) maximized
b) minimized
9
c) both (a) and (b)
d) None of these
Answer: (b)
Solution:
Generalization is a process of minimizing the difference
between members of an entity by identification of common
characters here as specialization is a process of maximization
the difference between members of entity by identification
between members of entity by identification of distinguishing
characters.
15. The dependency preservation decomposition is a property to
decompose database schema D, in which each functional
dependency X→Y specified in F
a) Appeared directly in one of the relation schemas Ri in the
decomposed D.
b) could be inferred from dependencies that appear in some Ri
c) both (a)and (b)
d) None of these
Answer: (c)
Solution:
A functional dependency is preserved if it appeared directly or
can be inferred from dependencies that appears in Ri.
c
10
16. For a database relation R (a, b, c, d) where the domains of a, b,
c, d include only the atomic values. The functional dependency
cy a → c, b →s d holds in the following relation
a) in 1 NF not in 2 NF
b) in 2 NF not in 3 NF
c) in 3 NF
d) in 1 NF
Answer: (a)
Solution:
Given relation is R (a, b, c, d) with functional dependences: {a
→ c, b → d}
So candidate key is {ab}
The given relation has partial dependency (both: {a → c and b
→d). Such kind of dependencies are not allowed in 2NF
so, relation R is not in 2NF, but is in 1NF
17. BCNF is not used for cases where a relation has
a) Two (or more) candidate keys
b) Two candidate keys and composite
c) The candidate key overlap
d) Two mutually exclusive foreign keys
Answer: (b)
Solution:
BCNF is not used for cases where a relation has two candidate
keys and composite.
11
18. Match the followings:
List-I
A. 2NF
B. 3NF
C. 4NF
D. 5NF
List-II
1. Transitive dependencies eliminated
2. Multivalued attribute removed
3. Contain no partial functional dependencies
4. Contains no join dependency
Codes:
AB CD
a) 1 3 2 4
b) 4 1 2 3
c) 3 4 1 2
d) 4 2 1 3
Answer: (d)
Solution:
2NF can not have partial dependences
3NF can not have transitive dependences
4NF can not have multivalued dependences
5NF can not have join dependences
12
19. Given the following statements:
S1: Every context-sensitive language L is recursive.
S2: There exists a recursive language that is not context
sensitive'
Which statement is correct?
a) S1 is not correct and 52 is not correct'
b) S1 is not correct and 32 is correct'
c) S1 is correct and 32 is not correct'
d) S1 is correct and 52 is correct'
Answer: (d)
Solution:
Context sensitive language are subset languages are subset of
recursive languages.
Hence, both statements are correct.
20. Identify the minimal key for relational scheme R (A, B, C, D, q
with functional dependencies F = {A → B, B → C, AC → D}
a) A
b) AE
c) BE
d) CE
Answer: (b)
Solution:
Closure of given attributes (A+) = ABCD
Hence is not by A determining.
So, attribute A is minimal Key
13
21. Consider the following relational schemas for a library
database:
Book (Title, Author, catalog no, Publisher, Year, Price)
Collection (Title, Author, Catalog no) with the following
functional dependencies:
I. Title, Author Catalog no
II. Catalog no ‒ + Title, Author, Publisher, Year
III. Publisher, Title, Year ‒ + Price Assume (Author, Title) is
the key for both schemas.
Which one of the following is true?
a) Both Book and Collection are in BCNF.
b) Both Book and Collection are in 3NF.
c) Book is in 2NF and Collection in 3NF.
d) Both Book and Collection are in 2NF.
Answer: (c)
Solution:
Given that {author, title} is the kay for both schemes FD’s are;
{Title, Author}→ {catalog-no}
{Catalog-no} → {Title, Author, Publisher, Year}
{Author, Publisher, Year}
Relation collection (Title, Author, Catalog-no) FD is only
{Title, Author} → {catalog-no} and {Catalog-no} → {Title} is
in 3NF, since {Author, Title} is candidate key and both ‘Author’
and ‘Title’ are prime attributes.
14
Now, Relation Book (Title, Author, Catalog-no, publisher, year,
price) Since, candidate key{Title, Author} is candidate key and
prime attribute are ‘Title’ and ‘Author’ functional
dependency{publisher, Title, Year} → Price is transitive
dependency which is not allowed in 3NF, but it is 2NF.
Therefore, Book is in 2NF and Collection in 3NF
22. The transform which possesses the highest' energy compaction'
property is
a) Slant transform
b) Cosine transforms
c) Fourier transform
d) Karhunen- Loeve transform
Answer: (d)
Solution:
The slant transformation has been utilized in several transform
image coding systems for monochrome and color images.
A discrete cosine transform expresses a finite sequence of data
points in terms of a sum of cosine functions oscillating at
different frequencies.
The Fourier transform is an important image processing too
which is used to decomposition tool which is used to decompose
an image into its sine and cosine components.
A karhumen lows transform depends on the second order
statistics of the input data. It is an optimal transform with
respect to energy compaction.
15
23. Which of the following is not provided as a service in cloud
computing?
a) Infrastructure as a service
b) Architecture as a service
c) Software as a service
d) Platform as a service
Answer: (b)
Solution:
Cloud computing provides three services that are infrastructure
as a service, software as a service and platform - as a service.
24. Let B = ABCDE is a relational schema with functional
dependency set
F = {A→B, B → C, AC → D} the attribute closures of A and E
are
a) ABCD, Ф
b) ABCD, E
c) ø, Ф
d) ABC, E
Answer: (b)
Solution:
Given relation is R (ABCDE) with FD’s
{A → B, B → C, AC → D}
So, {a+} = {A, B, C, D}
So, {E} = {E}
16
25. The physical location of a record determined by a formula that
transforms a file key into a record location
a) Hashed file
b) B-Tree file
c) Indexed file
d) Sequential file
Answer: (a)
Solution:
A hash file transforms a file key into a record location. By
knowing the key and the hash function used, the record location
can be known.
26. Consider a "CUSTOMER’S" database table having a column
"CITY" filled with all the names of Indian cities (in capital
letters) The SQL statement that finds all cities that have GAR"
somewhere in its name is:
a) Select x from customers
Where city = '%GAR’;
b) Select x from customers
Where city = '$GAR$'
c) Select * from customers
Where city like '%GAR';
d) Select x from customers
Where city as '%GAR';
Answer: (c)
17
Solution:
Where like determines it a character strong matches a pattern.
We use where like when only a fragment of a value is known. It
supports two wildcard match options: %and -. %GAR% saying
find string which contain “GAR” consultancy and used “LIKE”
for string matching.
27. The join operation can be defined as
a) a Cartesian product of two relations followed by a selection
b) a Cartesian product of two relations
c) a union of two relations followed by Cartesian product of the
two relations
d) a union of two relations
Answer: (a)
Solution:
The join operation is a combination of Cartesian product
followed by a selection process. The join operation can be
defined as a Cartesian product of two relations followed by a
selection.
28. Consider the following schema:
Emp (Empcode, Name, Sex, Salary, DeptName)
A simple SQL query is executed as follows:
SELECT DeptName FROM Emp
GROUP by Dept
Having avg (Salary) > {select avg (Salary) from Emp}
The output will be
18
a) Average salary of male employee is the average salary of the
organization
b) Average salary of male employee is less than the average
salary of the organization
c) Average salary of male employee is equal to the average
salary of the organization
d) Average salary of male employee is more than the average
salary of the organization
Answer: (d)
Solution:
Select DeptName
From employee
Where sex = ’M’
Grouply DeptName
Having avg (alary) (select avg (alary)
From employees)
[Avg salary of male
employees in each [Avg salary in the
company]
department]
It returns the names of departments in which the average salary
of male employees is more than the average salary of the
organization.
29. Calculate the order of leaf (Pleaf) and non-leaf (P) nodes of a B+
tree based on the information given below.
Search key field = 12 field
Record pointer = 10 bytes
19
Block pointer = 8 bytes
Block size = 1 KB
a) fleaf = 51 and P = 46
b) fleaf = 47 and P = 52
c) fleaf = 46 and p = 51
d) fleaf = 52 and p = 47
Answer: (c)
Solution:
For a leaf node, n (K + Rp) + Pnext ≤ Block size
⟹ n(12 + 10) + 8 ≤ 1024B
⟹ n ≤ 46.18≈46
For a non-leaf node, n (p) + n-1(k) ≤ Block size
⟹ n (8) + n – 1(12) ≤ 1024B
⟹ 8n + 12n – 12 ≤ 1024
⟹ n ≤ 51.8 ≈ 51
30. Aggregate functions in SQL are:
a) GREATEST, LEAST and ABS
b) SUM, COUNT and AVG
c) UPPER, LOWER and LENGTH
d) SORT, POWER and MOD
Answer: (b)
Solution:
SQL aggregate functions return a single value, calculated from
values of attribute.
20
Aggregate functions are, Avg, Sum, Min, Max and Count in the
SQL.
21
Data Loading...