Comprehensive-DBMS-Question-Bank

For more notes visit

https://rtpnotes.vercel.app

Qno Question Answer

1 DBMS provides the facility of accessing data from a B
database
through
a) DDL b) DML c) DBA d) Schema
2 Architecture of the database can be viewed as C
A)Two levels
B)Four levels
C)Three levels
D)One levels
3 If a multivalued dependency holds and is not implied by the D
corresponding functional dependency, it usually arises
from one of
the following sources.
a) A many-to-many relationship set
b) A multivalued attribute of an entity set
c) A one-to-many relationship set
d) Both A many-to-many relationship set and A multivalued
attribute of an entity se
4 Which of the following is generally used for performing D
tasks like
creating the structure of the relations, deleting relation?
a. DML(Data Manipulation Language)
b. Query
c. Relational Schema
d. DDL(Data Definition Language)
5 An entity set that does not have sufficient attributes to form B
a
primary key is a
A.strong entity set
B.weak entity set
C.simple entity set
D. primary entity set
Qno Question Answer
6 Dependency preservation is not guaranteed in B
a)3NF (b) BCNF c) 1NF (d) 2NF
7 The problem of being unable to represent some B
iinformation, can be
solved by introducing.A)pointers b) null values. C) 0
values. D) null
pointer
8 _ can help us detect poor E-R design. D
a) Database Design Process
b) E-R Design Process
c) Relational scheme
d) Functional dependencies
9 Consider the following four relational schemas. For each B
schema, all
non-trivial functional dependencies are listed. The
underlined
attributes are the respective primary keys.

Schema I: Registration (rollno, courses) Field ‘courses’ is a

set-valued attribute containing the set of courses a student
has
registered for. Non-trivial functional dependency: rollno
courses

Schema II: Registration (rollno, courseid, email) Non-trivial

functional dependencies: rollno, courseid email email rollno

Schema III: Registration (rollno, courseid, marks, grade)

Non-trivial
functional dependencies: rollno, courseid marks, grade
marks
grade

Schema IV: Registration (rollno, courseid, credit) Non-trivial

functional dependencies: rollno, courseid credit courseid
credit
Which one of the relational schemas above is in 3NF but
not in
BCNF?

(A) Schema I (B) Schema II (C) Schema III (D) Schema IV

10 In a relational model, relations are termed as: C
A)Tuples
Qno Question Answer
B) Attributes
C) Tables
D) Rows
11 There are _ levels of data independence. A
a. 2
b. 1
c. 4
d. 3
12 Database locking concept is used to solve the problem of D
a) Lost Update
b) Uncommitted Dependency
c) Inconsistent Data
d) All of the above
13 In an Entity-Relationship (ER) model, suppose R is a many- A
to-one
relationship from entity

set E1 to entity set E2. Assume that E1 and E2 participate

totally in
R and that the cardinality of E1 is greater than the
cardinality of E2.
Which one of the following is true about R?

(A) Every entity in E1 is associated with exactly one entity

in E2.
(B) Some entity in E1 is associated with more than one
entity in E2.
(C) Every entity in E2 is associated with exactly one entity
in E1.
(D) Every entity in E2 is associated with at most one entity
in E1.
14 Which of the following creates a temporary relation for the A
query on
which it is defined?
A) With
B) From
C) Where
D) Select
15 Which of the join operations do not preserve non matched C
tuples?
a) Left outer join
b) Right outer join
Qno Question Answer
c) Inner join
d) Natural join
16 A ___ consists of a sequence of query and/or update A
statements.
a) Transaction
b) Commit
c) Rollback
d) Flashback
17 __________ is a process used to gain ownership of shared B
resources without
creating the possibility for deadlock. It breaks up the
modification of
shared data into "two phases".
a) One phase locking
b) Two phase locking
c) Three phases locking
d) Multiple phase locking
18 What is the best way to represent the attributes in a large B
database?
a) Relational-and
b) Concatenation
c) Dot representation
d) All of the mentioned
19 A ___ is a special kind of a store procedure that B
executes in response to certain action on the table like
insertion, deletion or updation of data.
a) Procedures
b) Triggers
c) Functions
d) None of the mentioned
20 Data integrity constraints are used to: C

a) Control who is allowed access to the data

b) Ensure that duplicate records are not entered into the
table
c) Improve the quality of data entered for a specific
property (i.e.,
table column)
d) Prevent users from changing
21 The attribute AGE is calculated from DATE_OF_BIRTH. D
The
attribute AGE is
a) Single valued
Qno Question Answer
b) Multi valued
c) Composite
d) Derived
22 Database systems are designed to manage B
A. Small bodies of informations
B. Large bodies of informations
C. Plain bodies of knowledge
D. Complex bodies of knowledge
23 If a multivalued dependency holds and is not implied by the D
corresponding functional dependency, it usually arises
from one of
the following sources.

a) A many-to-many relationship set

b) A multivalued attribute of an entity set
c) A one-to-many relationship set
d) Both A many-to-many relationship set and A multivalued
attribute of an entity set
24 The designer can review the schema to ensure it meets all A
the
functional requirements, at the stage
A. Conceptual design
B. Execution design
C. Controlling design
D. Initial planning
25 In E-R diagram, relationship type is represented by D
a) Ellipse
b) Dashed ellipse
c) Rectangle
d) Diamond
26 Which relationship relates one record of any object to one A
record of
another object?
a) One-to-one
b) One-to-many
c) Many-to-many
27 Who is more concerned about the conceptual level of the A
DBMS
a)DBA
b)The end user
c)The Systems programmer
d)Client
Qno Question Answer
28 Underlying the structure of a database is A
a) Data model b)schema diagram c)Instances d)entity
29 A huge collection of the information or data accumulated C
form
several different sources is known as:
A)Data Management
B)Data Mining
C)Data Warehouse
D)Both B and C
30 What is the best way to represent the attributes in a large C
database?
a) Relational-and
b) Concatenation
c) Dot representation
d) All of the mentioned
31 Which of the following can be considered as the maximum B
size that
is supported by FAT?
a)8GB
b) 4GB
c) 4TB
d) None of the above
32 SELECT * FROM employee WHERE dept_name="Comp C) “Comp Sci”
Sci"; For any string
In the SQL given above there is an error . Identify the error. operations single
a) Dept_name quoted(‘) must beused
b) Employee to enclose.
c) “Comp Sci”
d) From
33 In which of the following, a separate schema is created B
consisting of
that attribute and the primary key of the entity set.
a) A many-to-many relationship set
b) A multivalued attribute of an entity set
c) A one-to-many relationship set
d) All of the mentioned
34 Which normal form is based on the idea of full functional 2nf
dependency
35 A Database Management System is a type of _ software. A
A) It is a type of system software
B) It is a kind of application software
Qno Question Answer
C) It is a kind of general software
D) Both A and C
36 The collection of tuples stored in a database at a particular C
moment is called
a)schema
b)view
c)instance
d)relation
37 Consider the following relational schema: D
Employee (empId, empName, empDept)
Customer (custId,custName, salesRepId, rating)
SalesRepId is a foreign key referring to empId of the
employee relation. Assume that each
employee makes a sale to at least one customer. What
does the following query return?

SELECT empName
FROM employee E
WHERE NOT EXISTS (SELECT custId
FROM customer C
WHERE C. salesRepId = E. empId
AND C. rating < > ‘GOOD’)

(A) Names of all the employees with at least one of their

customers having a ‘GOOD’ rating.

(B) Names of all the employees with at most one of their

customers having a ‘GOOD’ rating.

(C) Names of all the employees with none of their

customers having a ‘GOOD’ rating.

(D) Names of all the employees with all their customers

having a ‘GOOD’ rating.
38 The file organization that provides very fast access to any C
arbitrary record of a file is

A)Ordered file
B)Unordered file
C)Hashed file
D)B tree
39 Which command is used to remove rows from a table ? C
A) delete
Qno Question Answer
B) remove
C) truncate
D ) both A & B
40 SELECT empname C
FROM department
WHERE dept_name LIKE ____ Computer Science';

In the above-given Query, which of the following can be

placed in the Query's blank portion to select the
"dept_name" that also contains Computer Science as its
ending string?

A) &
B) ``
C%
D$
41 Which of the following is a top-down approach in which the C
entity's higher level can be divided into two lower sub-
entities?

A) Aggregation
B) Generalization
C) Specialization
D) All of the above
42 WITH max_budget (VALUE) AS D
(SELECT MAX(budget)
FROM department)
SELECT budget
FROM department, max_budget
WHERE department.budget = MAX budget.value;

In the query given above which one of the following is a

temporary relation?

a) Budget
b) Department
c) Value
d) Max_budget
43 Which one of the following given statements possibly D (from is missing,
contains the causing the error)
error?
A. select * from emp where empid = 10003;
B. select empid from emp where empid = 10006;
Qno Question Answer
C. select empid from emp;
D. select empid where empid = 1009 and Lastname =
'GELLER'
44 Consider the relations r(A, B) and s(B, C), where s.B is a C
primary key and r.B is a foreign key referencing s.B.
Consider the query
Q: r ⋈ (σB<5(s))
Let LOJ denote the natural left outer-join operation.
Assume that r and s contain no null values.
Which one of the following queries is NOT equivalent to Q?

(A) σB<5(r ⋈ s)
(B) σB<5(r LOJ s)
(C) r LOJ (σB<5(s))
(D) σB<5(r) LOJ s
45 Views are useful for _____ unwanted information, and for A
collecting
together information from more than one relation into a
single view.

A.Hiding
B.Deleting
C.Highlighting
D. All of the above
46 In general, a file is basically a collection of all related D
______
a.Rows & Columns
b.Fields
c.Database
d.Records
47 Relations produced from an E-R model will always be in C
a) 1NF
b) 2NF
c) 3NF
d) 4NF
e) Other
48 4NF is designed to cope with C
a)Transitive dependency
b)Join dependency
c)Multivalued dependency
d)None of these
49 What do you mean by one to many relationships? B
Qno Question Answer
A)One class may have many teachers
B)One teacher can have many classes
C)Many classes may have many teachers
D)Many teachers may have many classes
50 Which of the following refers to the level of data abstraction B
that describes exactly how the data actually stored?

A:Conceptual Level
B:Physical Level
C:File Level
D:Logical Level
51 In SQL the spaces at the end of the string are removed by C
_______ function.
a) Upper
b) String
c) Trim
d) Lower
52 Which of the following is the property of transaction that C
protects data from system failure?

a) Atomicity
b) Isolation
c) Durability
d) Consistency
53 Which normalization form is based on the transitive C
dependency?
a) 1NF
b) 2NF
c) 3NF
d) BCNF
54 Which of the following SQL command is used for removing Drop
(or deleting) a relation form the
database?
55 Which of the following is known as minimal super key? Candidate key
56 Given the following relation instance. YZ -> X and Y ->
xyz Z
142
153
163
322
Qno Question Answer
Which of the following functional dependencies are
satisfied by the instance?
57 Consider the following relational schema: Find the names
of all suppliers
Suppliers(sid:integer, sname:string, city:string, street:string) who
Parts(pid:integer, pname:string, color:string) have
Catalog(sid:integer, pid:integer, cost:real) supplied a non-
Consider the following relational query on the above blue part
database:

SELECT S.sname
FROM Suppliers S
WHERE S.sid NOT IN (SELECT C.sid
FROM Catalog C
WHERE C.pid NOT IN (SELECT P.pid
FROM Parts P
WHERE P.color<> 'blue'))

Assume that relations corresponding to the above schema

are not empty. Which one of the following
is the correct interpretation of the above query
58 An entity in A is associated with at most one entity in B. An Many-to-one
entity in B, however, can be associated
with any number (zero or more) of entities in A.
59 Which commands are used to control access over objects GRANT &
in relational database? REVOKE
60 Consider the ORACLE relationships below:One (x, y) = 1 and 2
{<2, 5>, <1, 6>, <1, 6>, <1, 6>, <4, 8>, <4,
8>} Two (x, y) = {<2, 55>, <1, 1>, <4, 4>, <1, 6>, <4, 8>,
<4, 8>, <9, 9>, <1, 6>}.

Consider the following SQL queries, SQ1 and SQ2,

respectively:
SQ1 : SELECT FROM One)
EXCEPT
(SELECT FROM Two);
SQ2 : SELECT FROM One)
EXCEPT ALL
(SELECT FROM Two);

What is the cardinality of the result generated on the

execution of each SQL query on the instances
above?
Qno Question Answer
61 Which of the following is TRUE? Every
relation in
BCNF is also
in 3NF
62 Typically, a database administrator is responsible for Schema definition,
modification, Granting
of authorization for
data access
63 Which of the following queries will retrieve students whose SELECT rollNo FROM
name has p as the second letter? student where name
LIKE _p%
64 Consider a relation R(A,B,C,D,E) and a set of all FDs that R is in 3NF not in BCN
hold on R as given below

{A -> BC,CD -> E,B->D,E->A}

65 Consider the following 2 sets of functional dependencies F and G are equivalent

F = {A->C,AC->D,E->AD,E->H}
G = {A->CD,E->AH}
66 To be conflict serializable, all transaction should follow Two phase locking
67 Which of the following is NOT a type of database model? Object oriented model

a) Relational Model b) Network Model c) Hierarchical

model d) Object Oriented model
68 Which of the following database model represents data as NoSQL model
a collection of key value pairs?
69 Which SQL function is used to calculate the total number of COUNT
records in a table?
70 Consider the statements given below S1: True, S2:True
S1: Data abstraction is the DBMS characteristic that allows
program-data independence
S2: Data models allow representation of a database at
different levels of detail
71 Let E1 and E2 be two entities in an ER diagram with The minimum number
simple single-valued attributes. R1 and R2 are two of tables required is 3:
relationships between E1 and E2. Where R1 is one to one table for each
many and R2 is many to many. R1 and R2 do not have any entity (E1, E2) and
attributes of their own. What is the minimum number of one table for the many-
tables required to represent this situation in the relational to-many relationship
model? (R2).
Qno Question Answer
72 Consider the join of a relation R with relation S. If R has m The maximum size of
tuples and S has n tuples then the maximum size of join is the join of relation R
with relation S is mn
tuples.
73 An index record appears for every search key value in the Dense index
file
74 If a relation is in BCNF then it is in 3NF
75 Which of the following is a DDL command? Create
a) Select b) Create c) Insert d) Delete
76 The number of attributes in a relation is called its Degree
77 Consider a database implemented using B+ tree for file 3
indexing and installed on a disk drive with block size of
4KB. The size of search key is 12 bytes and the size of
tree/disk pointer is 8 bytes. Assume the database has one
million records. Also assume that no node of the B+ tree
and no records are present initially in main memory.
Consider that each record fits into one disk block. THe
minimum number of disk accesses required to retrieve any
record in the database is
78 Conisder the relation R(A,B,C,D,E) and the set F = {AB- BCNF
>CE,E->AB,C->D} What is the highest normal form of this
relation?
79 What is the lostupdate problem known as W-W Conflict
80 Consider the following transactions with data items P and B) A schedule that is
Q initialized to zero not conflict serializable

T1: read(P);
read(Q);
if P = 0 then Q:=Q+1
write(Q);
T2:read(Q)
read(P);
if Q = 0 then P:=P+1
write(P)

Any non serial interleaving of T1 and T2 for concurrent

execution leads to

a) A serializable schedule b) A schedule that is not conflict

serializable c) A conflict serializable schedule d) A schedule
for which precedence graph cannot be drawn