MCQ 1:

Contiguous allocation में एक फ़ाइल के सभी

ब्लॉक्स कैसे स्टोर किए जाते हैं?
A. Random blocks में
B. एक sequence में लगातार blocks में
C. Index table में
D. अलग-अलग location पर links के साथ
✅ Answer: B. एक sequence में लगातार blocks में
📝 Solution:
Contiguous allocation में, फ़ाइल के सभी blocks
disk पर लगातार (consecutive) sectors में store
होते हैं, जिससे access तेज़ होता है। परन्तु
इसका major drawback external fragmentation है।

🟩 MCQ 2:
Linked Allocation का मुख्य लाभ क्या है?
A. Fast random access
B. No external fragmentation
C. Minimum space required
D. Requires index block
✅ Answer: B. No external fragmentation
📝 Solution:
Linked Allocation में हर file block में next block का
pointer होता है, जिससे external fragmentation
नहीं होती। लेकिन इसमें random access possible
नहीं होता।

🟩 MCQ 3:
Indexed allocation में file blocks को कैसे locate
किया जाता है?
A. File के starting block से
B. Linked list के pointer से
C. एक index block से
D. एक hash table से
✅ Answer: C. एक index block से
📝 Solution:
Indexed allocation में हर फ़ाइल के लिए एक अलग
index block होता है, जिसमें उस फ़ाइल के सभी
block addresses stored होते हैं। इससे direct
access possible होता है।

🟩 MCQ 4:
Linked Allocation का major disadvantage क्या है?
A. External Fragmentation
B. Internal Fragmentation
C. Random access संभव नहीं
D. Sequential access नहीं होता
✅ Answer: C. Random access संभव नहीं
📝 Solution:
Linked Allocation में random access संभव नहीं है
क्योंकि हर block अगले block के address को point
करता है। यदि बीच का block खराब हो जाए, तो
chain टूट जाती है।

🟩 MCQ 5:
निम्न में से कौन सी file allocation technique
random access support करती है?
A. Contiguous
B. Linked
C. Indexed
D. Linked and Indexed दोनों
✅ Answer: C. Indexed
📝 Solution:
Contiguous allocation theoretically random access
allow करती है, लेकिन practical limitations होते
हैं।
Indexed allocation में index block से direct block
address मिल जाता है, इसलिए random access
possible होता है।

🟩 MCQ 6:
Contiguous allocation के नुकसान क्या हैं?
A. File size fix करना पड़ता है
B. Internal fragmentation
C. External fragmentation
D. A और C दोनों
✅ Answer: D. A और C दोनों
📝 Solution:
Contiguous allocation में:
 File size पहले से पता होना चाहिए (fix करना
पड़ता है)
 Disk में fragmentation होता है अगर adequate
continuous space न मिले
इसलिए दोनों नुकसान valid हैं।

🟩 MCQ 7:
Indexed allocation में हर file के लिए क्या बनाना
पड़ता है?
A. Directory
B. FAT
C. Index block
D. Inode
✅ Answer: C. Index block
📝 Solution:
Indexed allocation के अनुसार, हर file के लिए एक
अलग Index block बनाया जाता है जिसमें उस file
के सभी data blocks के addresses होते हैं।

🧠 Bonus Short Summary (Quick Revision):

Rando Extra
Allocation Access Fragmentatio
m Overhea
Type Speed n
Access d
Contiguou Need full
Fast External Yes
s space
Pointer in
Linked Slow No External No each
block
One
Moderat
Indexed No External Yes index
e
block/file

You're looking for a set of 30 multiple-choice

questions (MCQs) with answers covering
Sparse Index, Dense Index, Clustered Index,
and Non-Clustered Index, suitable for
government exams. While I can't guarantee
these are the "top 30" or specifically for "all
govt exams" without knowing the exact
syllabus, I can provide a comprehensive set
that covers the core concepts.

Here are 30 MCQs with answers, designed to

test understanding of these indexing
concepts.

Multiple Choice Questions (MCQs) on

Database Indexing

Instructions: Choose the most appropriate

answer for each question.

Section 1: Sparse Index & Dense Index

Which type of index contains an index entry

for every search key value in the data file?
a) Sparse Index
b) Dense Index
c) Clustered Index
d) Non-Clustered Index
In a dense index, each entry points to:
a) The first record with the search key value
b) The last record with the search key value
c) Every record with the search key value
d) A block containing the records

Which index type is generally smaller in

size?
a) Sparse Index
b) Dense Index
c) Both are similar in size
d) Size depends on the data type

A sparse index can only be used if the data

file is:
a) Unsorted
b) Sorted
c) In random order
d) Partially sorted
Which of the following is true for a dense
index?
a) Less disk space overhead
b) Faster search for records
c) Requires the data file to be sorted
d) Slower for range queries

If a data file is sorted on the search key,

which index type is typically more efficient
for equality searches?
a) Sparse Index
b) Dense Index
c) Both are equally efficient
d) Efficiency depends on the data
distribution

A sparse index entry points to:

a) Every record in the data file
b) The beginning of each block of records
c) The end of each block of records
d) Only unique records
For a large database, maintaining a dense
index compared to a sparse index generally
requires:
a) Less storage and less update overhead
b) More storage and more update overhead
c) Less storage but more update overhead
d) More storage but less update overhead

Which index type is best suited for scenarios

where space is a critical concern?
a) Dense Index
b) Sparse Index
c) Both are equally good
d) Neither, space is always a problem

In a sparse index, to find a record, the

system might have to:
a) Scan the entire index
b) Scan a portion of the data file
c) Access multiple index blocks
d) Rebuild the index
Section 2: Clustered Index & Non-Clustered
Index

A clustered index determines the physical

order of data storage in the table.
a) True
b) False

How many clustered indexes can a table

have?
a) Zero
b) One
c) Multiple
d) Depends on the database system

Which type of index stores the actual data

rows at the leaf level?
a) Non-Clustered Index
b) Clustered Index
c) Sparse Index
d) Dense Index
For a non-clustered index, the leaf level
contains:
a) The actual data rows
b) Pointers to the actual data rows
c) Duplicate copies of data rows
d) Only the primary key

Which of the following is generally faster for

retrieving a range of records?
a) Clustered Index
b) Non-Clustered Index
c) Both are equally fast
d) Speed depends on the join condition

If a table has a clustered index, accessing

data through that index is often referred to
as:
a) Index scan
b) Table scan
c) Bookmark lookup
d) Direct access
A non-clustered index is also known as a:
a) Primary index
b) Secondary index
c) Ordering index
d) Hashed index

What happens to the physical order of data

when a non-clustered index is created?
a) The physical order changes to match the
index order.
b) The physical order remains unchanged.
c) Data is duplicated in the order of the
index.
d) The table is rebuilt in a new location.

When is a bookmark lookup (or key lookup)

typically performed?
a) When using a clustered index for a full
scan.
b) When using a non-clustered index and
needing columns not in the index.
c) When performing an INSERT operation.
d) When the table has no indexes.

Which index type is typically created on the

primary key by default in many database
systems?
a) Non-Clustered Index
b) Clustered Index
c) Sparse Index
d) Dense Index

Deleting a row from a table with both a

clustered and a non-clustered index
requires:
a) Updating only the clustered index.
b) Updating only the non-clustered index.
c) Updating both the clustered and all non-
clustered indexes.
d) No index updates are required for
deletion.

Which index type is generally more suitable

for columns that are frequently used in
ORDER BY clauses?
a) Non-Clustered Index
b) Clustered Index
c) Both are equally suitable
d) Neither, sorting is always done at query
time

Which statement about non-clustered

indexes is TRUE?
a) They store the data physically sorted.
b) They can only be created on unique
columns.
c) They always require a bookmark lookup
to retrieve all columns.
d) They contain pointers to the actual data
rows.

A table without a clustered index is often

referred to as a:
a) Heap table
b) Indexed table
c) Clustered table
d) Sorted table
If you have a query that frequently retrieves
data based on a non-key column, and you
want to avoid full table scans, you would
likely create a:
a) Clustered Index on that column.
b) Non-Clustered Index on that column.
c) Primary Key on that column.
d) Foreign Key on that column.

Section 3: General Indexing Concepts &

Mixed

Which of the following is a disadvantage of

having too many indexes on a table?
a) Increased query execution time for
SELECT statements.
b) Decreased storage space.
c) Slower INSERT, UPDATE, and DELETE
operations.
d) Reduced data integrity.
The main purpose of an index in a database
is to:
a) Ensure data consistency
b) Prevent unauthorized access
c) Speed up data retrieval operations
d) Reduce data redundancy

In a B-tree index, what do the leaf nodes

typically contain?
a) Only pointers to data blocks
b) Search key values and pointers to
records/blocks
c) Intermediate search key values
d) The entire data file

Which of the following statements about

indexes is generally TRUE?
a) Indexes should be created on all columns
in a table.
b) Indexes are always beneficial for
performance.
c) Indexes can be created on one or more
columns.
d) Indexes are only useful for primary keys.

Which of the following is a key characteristic

of a multicolumn index?
a) It can only be a clustered index.
b) The order of columns in the index
definition does not matter.
c) It supports queries that filter or sort on
the leading columns of the index.
d) It is always a sparse index.
Which of the following data structures is primarily
used for disk-based indexing in database systems?
a) Binary Search Tree (BST)
b) AVL Tree
c) B-Tree
d) Red-Black Tree

In a B-Tree, where can data pointers (pointers to

actual records) be stored?
a) Only in leaf nodes
b) Only in internal nodes
c) In both internal and leaf nodes
d) Only in the root node

In a B+ Tree, where are data pointers (pointers to

actual records) stored?
a) Only in leaf nodes
b) Only in internal nodes
c) In both internal and leaf nodes
d) Only in the root node

Which type of tree is generally preferred for range

queries and sequential access in databases?
a) B-Tree
b) B+ Tree
c) AVL Tree
d) Binary Search Tree

What is a key characteristic of the leaf nodes in a

B+ Tree?
a) They are not linked to each other.
b) They contain only keys, no data pointers.
c) They are linked together in a sequential
manner.
d) Their level can vary across the tree.

Which tree structure ensures that all paths from

the root to any leaf node have the same length?
a) Binary Search Tree
b) AVL Tree
c) Both B-Tree and B+ Tree
d) Only B-Tree

The "order" of a B-Tree or B+ Tree (often denoted

as 'm' or 'p') primarily refers to:
a) The number of keys in a node.
b) The maximum number of children a node can
have.
c) The minimum number of children a node can
have.
d) The height of the tree.

Which of the following statements is TRUE

regarding the fanout of B-Trees vs. B+ Trees?
a) B-Trees generally have a higher fanout than B+
Trees.
b) B+ Trees generally have a higher fanout than B-
Trees.
c) Both have similar fanouts.
d) Fanout is not a relevant concept for these trees.

Why do B-Trees and B+ Trees minimize the height

of the tree?
a) To reduce memory consumption.
b) To speed up disk I/O operations.
c) To simplify implementation.
d) To prevent data duplication.

Which tree allows for "early exit" from a search

operation if the exact key is found in an internal
node?
a) B-Tree
b) B+ Tree
c) Both
d) Neither
Section 2: Advantages and Disadvantages

A major advantage of B+ Trees over B-Trees for

database indexing is:
a) Simpler insertion and deletion logic.
b) Faster random access due to data in internal
nodes.
c) Efficient range queries and sequential access.
d) Less memory overhead for internal nodes.

Which of the following is a disadvantage of B-

Trees compared to B+ Trees in a DBMS context?
a) More complex implementation.
b) Slower for exact match queries.
c) Slower for range queries due to scattered data.
d) Higher storage overhead for leaf nodes.

The primary reason B-Trees and B+ Trees are

favored over binary search trees for disk-based
data is:
a) Binary search trees are too slow for large
datasets.
b) They are self-balancing.
c) They are optimized to handle block-based disk
I/O efficiently.
d) They can store duplicate keys.

What is a potential disadvantage of a B+ Tree for

simple equality searches (single key lookup) if the
entire tree fits in memory?
a) It always requires traversing to a leaf node,
even if the key is found in an internal node
(duplicate key).
b) It has a higher memory footprint.
c) Deletion operations are more complex.
d) It is not self-balancing.

Which index structure offers better performance

for full table scans?
a) B-Tree
b) B+ Tree
c) Hash Table
d) Linked List

One advantage of B-Trees is that:

a) They have a simpler structure.
b) Frequently accessed data might be closer to the
root, potentially leading to fewer disk accesses.
c) They support very efficient sequential access.
d) They are less prone to node splits.

The main disadvantage of B+ trees related to key

duplication in internal nodes is:
a) Increased storage space for keys.
b) Slower search for specific records.
c) Increased tree height.
d) Difficulty in managing unique constraints.

In terms of overall disk I/O, for a large database,

which tree structure is generally considered more
efficient?
a) B-Tree
b) B+ Tree
c) Both are equally efficient
d) Neither is efficient for disk I/O

Section 3: Formula-Based & Advanced Concepts

In a B+ Tree of order 'p' (maximum number of

pointers/children), what is the maximum number
of keys an internal node can hold?
a) p
b) p - 1
c) p / 2
d) 2p

Given a B+ Tree of order 'p', what is the minimum

number of keys an internal node (except the root)
must hold?
a) ⌈p/2⌉
b) ⌈p/2⌉−1
c) p−1
d) 2
If a B-Tree node (order 'm') has 'k' keys, how many
children can it have?
a) k
b) k + 1
c) m
d) m - 1

A B+ Tree has a block size of 1KB (1024 bytes).

Each key is 8 bytes, and a child pointer is 6 bytes.
What is the maximum order (p) of an internal
node?
(Formula for internal node: $(p-1) \times (\
text{key_size} + \text{pointer_size}) + \
text{pointer_size} \le \text{block_size}$)
a) 73
b) 74
c) 75
d) 76

Considering the previous question, if the block size

is 1024 bytes, each key is 8 bytes, and a data
record pointer in a leaf node is 7 bytes. What is
the maximum number of (key, data pointer) pairs
a leaf node can hold?
(Formula for leaf node: $N \times (\text{key_size}
+ \text{data_pointer_size}) + \
text{next_leaf_pointer_size} \le \text{block_size}
$) Assume next_leaf_pointer_size is 6 bytes.
a) 67
b) 68
c) 69
d) 70

A B-Tree of order 'm' has 'N' keys. What is the

approximate height of the tree (h)?
a) O(N)
b) O(log
m

N)
c) O(mlogN)
d) O(Nlogm)
If a B-Tree splits a node during an insertion
operation, and 's' splits occur, how many nodes
are written (including the new halves and the
parent of the last split node)?
a) s
b) 2s
c) 2s + 1
d) s + 1

Which of the following is true about deletion in a

B+ Tree?
a) When a key is deleted from a leaf node, it must
also be deleted from all internal nodes.
b) When a key is deleted from a leaf node, it may
remain in internal nodes as a search key.
c) Deletion always causes the tree to shrink in
height immediately.
d) Deletion is always simpler than insertion.

If a B+ Tree has a height 'h' and order 'p', what is

the maximum number of keys it can contain?
a) p
h

b) p
h
−1
c) (p−1)
h

d) h×(p−1)

In a B+ Tree, the internal nodes primarily serve as:

a) Storage for actual data records.
b) Pointers to previous leaf nodes.
c) An index or guide to navigate to the correct leaf
node.
d) Backup copies of leaf nodes.

Consider a B-Tree of order 3. If we insert elements

10, 20, 30, 40, 50 in that order, how many splits
would occur? (Assume a node is full when it has
m-1 keys, and splits into two nodes with (m-1)/2
keys each, promoting the middle key)
a) 0
b) 1
c) 2
d) 3

Why is the block size a critical factor in

determining the order of B-Trees and B+ Trees?
a) To minimize the number of keys per node.
b) To maximize the number of disk I/O operations.
c) To ensure that a node fits perfectly into a single
disk block, optimizing disk reads.
d) To facilitate complex balancing algorithms.

Section 1: Stored Procedures (10 Questions)

1. What is a Stored Procedure in SQL?
a) A virtual table based on the result-set of a SQL
query. b) A named block of SQL statements that
can be stored in the database and executed
repeatedly. c) A special type of SQL query that
only returns a single value. d) An automated action
that executes when a specific event occurs on a
table.
2. Which of the following is an advantage of using
Stored Procedures? a) Increased network traffic. b)
Reduced security. c) Improved performance and
reusability. d) Less control over business logic.
3. How can a Stored Procedure accept input values?
a) Through global variables. b) By declaring
parameters. c) Using subqueries within the
procedure. d) By directly accessing table columns.
4. Which SQL command is used to create a Stored
Procedure? a) CREATE PROCEDURE b) ADD
PROCEDURE c) DEFINE PROCEDURE d) BUILD
PROCEDURE
5. Which of the following statements about Stored
Procedures is FALSE? a) They can return multiple
result sets. b) They can contain DDL (Data
Definition Language) statements. c) They always
require input parameters. d) They can be used to
encapsulate complex business logic.
6. What is the primary purpose of an OUT parameter
in a Stored Procedure? a) To pass a value into the
procedure. b) To return a value from the procedure
to the calling environment. c) To declare a local
variable within the procedure. d) To specify
optional parameters.
7. Which SQL command is used to execute a Stored
Procedure? a) RUN PROCEDURE b) EXECUTE
PROCEDURE c) CALL PROCEDURE d) Both
 EXECUTE PROCEDURE and CALL PROCEDURE
(depending on DBMS).
8. Consider the following partial stored procedure
definition:
SQL
CREATE PROCEDURE GetEmployeeCount
@DeptID INT,
@EmployeeCount INT OUTPUT
AS
BEGIN
SELECT @EmployeeCount =
COUNT(*) FROM Employees WHERE
DepartmentID = @DeptID;
END;
To call this procedure and get the employee count
for DepartmentID 10, which of the following is
correct? a) EXEC GetEmployeeCount 10,
@Count; b) EXEC GetEmployeeCount
@DeptID=10, @EmployeeCount OUTPUT;
c) CALL GetEmployeeCount(10, ?); d)
EXEC GetEmployeeCount 10, @Count
OUTPUT; SELECT @Count;
9. What is the difference between a Stored Procedure
and a Function in SQL? a) Functions can perform
DML operations (INSERT, UPDATE, DELETE),
while Stored Procedures cannot. b) Stored
Procedures can return only one value, while
 Functions can return multiple values. c) Functions
must return a value, while Stored Procedures may
or may not. d) Stored Procedures can be used in a
SELECT statement, while Functions cannot.
10. When a Stored Procedure is executed for the
first time, what typically happens regarding its
performance? a) It is always recompiled every time
it runs. b) The execution plan is generated and
cached for future use. c) It runs slower than
subsequent executions. d) It bypasses the optimizer
for faster execution.

**Section 2: Views (10 Questions) **

11. What is a View in SQL? a) A physical copy of
a table stored on disk. b) A schema object that is a
logical representation of data from one or more
tables. c) A temporary table used for complex
calculations. d) A graphical user interface for
database interaction.
12. Which of the following is an advantage of
using Views? a) Decreased data security. b)
Simplified query writing for complex joins. c)
Reduced disk space usage. d) Faster data
modification.
13. Which DML operations are generally allowed
on a View? a) SELECT only. b) INSERT,
UPDATE, DELETE are always allowed. c)
INSERT, UPDATE, DELETE are allowed under
 certain conditions (e.g., single table view, no
aggregate functions). d) Only UPDATE operations
are permitted.
14. Consider a View created with WITH CHECK
OPTION. What is its primary purpose? a) To
restrict SELECT operations on the view. b) To
ensure that all INSERT and UPDATE operations
performed through the view satisfy the view's
WHERE clause. c) To prevent users from dropping
the base table. d) To automatically refresh the
view's data at regular intervals.
15. What happens to a View if its underlying base
table is dropped? a) The View's definition remains
but it becomes invalid/unusable. b) The View is
automatically dropped as well. c) The View's data
is converted into a standalone table. d) The
database system rebuilds the underlying table.
16. Which SQL command is used to create a
View? a) CREATE VIEW b) DEFINE VIEW c)
MAKE VIEW d) BUILD VIEW
17. A View does NOT store its own data. It always
retrieves data from its underlying tables when
accessed. a) True b) False
18. What is the main security benefit of using
Views? a) They encrypt the data automatically. b)
They allow users to access only specific rows
and/or columns of tables. c) They prevent SQL
injection attacks entirely. d) They authenticate user
logins.
 19. Which type of View is typically used to
simplify complex joins and calculations, providing
a pre-computed or aggregated result? a) Updatable
View b) Materialized View (Indexed View in SQL
Server) c) Simple View d) Partitioned View
20. What is the major difference between a
standard View and a Materialized View? a)
Standard Views store data; Materialized Views do
not. b) Standard Views are always updatable;
Materialized Views are not. c) Materialized Views
store data physically and can be refreshed;
Standard Views do not store data. d) Materialized
Views cannot include aggregate functions.

Section 3: Joins (10 Questions)

21. Which type of JOIN returns only the rows that
have matching values in both tables? a) LEFT
JOIN b) RIGHT JOIN c) FULL OUTER JOIN
d) INNER JOIN
22. Consider TableA with columns ID (1, 2, 3)
and TableB with columns ID (2, 3, 4). How many
rows will an INNER JOIN on ID return? a) 1 b) 2
c) 3 d) 4
23. A LEFT JOIN (or LEFT OUTER JOIN)
returns: a) All rows from the left table, and the
matching rows from the right table. If there's no
match, NULLs appear for the right table's columns.
b) All rows from the right table, and the matching
 rows from the left table. If there's no match,
NULLs appear for the left table's columns. c) Only
rows where there is a match in both tables. d) All
rows from both tables, with NULLs where there's
no match.
24. Which type of JOIN returns all rows from the
left table, and all rows from the right table, with
NULLs in place for non-matching rows from either
side? a) INNER JOIN b) LEFT JOIN c) RIGHT
JOIN d) FULL OUTER JOIN
25. What is a CROSS JOIN (Cartesian Product)?
a) It joins tables based on a common column. b) It
returns the matched rows from two tables. c) It
returns every row from the first table combined
with every row from the second table. d) It joins
three or more tables.
26. To find employees who do NOT have a
department assigned (i.e., DepartmentID is
NULL in the Employees table), given
Employees and Departments tables, which
JOIN clause along with a WHERE condition would
be most appropriate? a) INNER JOIN
Employees e JOIN Departments d ON
e.DepartmentID = d.DepartmentID b)
LEFT JOIN Employees e ON
e.DepartmentID = d.DepartmentID
WHERE d.DepartmentID IS NULL c)
RIGHT JOIN Departments d ON
e.DepartmentID = d.DepartmentID
 WHERE e.DepartmentID IS NULL d) FULL
OUTER JOIN Employees e ON
e.DepartmentID = d.DepartmentID
27. What is a SELF JOIN? a) A join that
combines two different tables with the same
structure. b) A join where a table is joined with
itself. c) A join that only returns unique rows. d) A
join used for hierarchical data.
28. Which of the following clauses is used to
specify the join condition in SQL? a) WHERE b)
GROUP BY c) ORDER BY d) ON
29. To retrieve employees along with their
manager's name (where both employees and
managers are in the Employees table, linked by
EmployeeID and ManagerID), which join type
would you use? a) INNER JOIN b) LEFT JOIN
c) SELF JOIN d) CROSS JOIN
30. Consider two tables: Orders (OrderID,
CustomerID) and Customers
(CustomerID, CustomerName). To get all
customer names and their corresponding order IDs
(including customers who have no orders), which
JOIN would you use? a) SELECT
C.CustomerName, O.OrderID FROM
Customers C INNER JOIN Orders O ON
C.CustomerID = O.CustomerID; b)
SELECT C.CustomerName, O.OrderID
FROM Customers C LEFT JOIN Orders
O ON C.CustomerID = O.CustomerID;
 c) SELECT C.CustomerName, O.OrderID
FROM Customers C RIGHT JOIN Orders
O ON C.CustomerID = O.CustomerID;
d) SELECT C.CustomerName, O.OrderID
FROM Orders O LEFT JOIN Customers
C ON C.CustomerID = O.CustomerID;

Section 4: Triggers (10 Questions)

What is a Trigger in SQL?

a) A type of Stored Procedure that runs
manually.
b) A named block of SQL code that
automatically executes in response to
certain events on a table or view.
c) A security mechanism to prevent data
modification.
d) A function that validates user input
before it's stored.

Which of the following DML events can

typically cause a Trigger to fire?
a) SELECT
b) INSERT, UPDATE, DELETE
c) CREATE TABLE, DROP TABLE
d) GRANT, REVOKE

What is the difference between a BEFORE

trigger and an AFTER trigger?
a) BEFORE triggers fire before the DML
operation; AFTER triggers fire after.
b) BEFORE triggers can modify the data;
AFTER triggers cannot.
c) BEFORE triggers are synchronous; AFTER
triggers are asynchronous.
d) BEFORE triggers are used for auditing;
AFTER triggers are for validation.

Which of the following is a common use case

for a BEFORE INSERT trigger?
a) Auditing changes after a row is inserted.
b) Validating new data values before they
are inserted.
c) Cascading deletions to related tables.
d) Generating reports after data loads.
What is the role of OLD and NEW pseudo-
records/tables within a Trigger?
a) OLD contains the data after modification;
NEW contains data before modification.
b) OLD contains the data before
modification/deletion; NEW contains the
data after insertion/update.
c) Both OLD and NEW contain the same
data.
d) They are used only for SELECT triggers.

Which of the following statements about

Triggers is TRUE?
a) Triggers can always rollback the entire
transaction that fired them.
b) Triggers are visible to users and can be
directly executed.
c) Triggers can increase the complexity of
database logic.
d) Triggers are primarily used for
performance optimization.

Consider an AFTER UPDATE trigger on an

Employees table. Which pseudo-table would
you use to access the new salary value after
an update?
a) OLD
b) NEW
c) UPDATE_VALUES
d) CURRENT_ROW

Which of the following is a potential

disadvantage of using Triggers?
a) Enhanced data integrity.
b) Hidden business logic that can be difficult
to debug.
c) Reduced server load.
d) Improved data consistency.

In Oracle, to create a trigger that fires once

for each row affected by a DML statement,
you would use:
a) FOR EACH STATEMENT
b) FOR EACH ROW
c) FOR ALL ROWS
d) FOR EVERY OCCURRENCE
What is a "recursive trigger"?
a) A trigger that calls another stored
procedure.
b) A trigger that is enabled and then
disabled repeatedly.
c) A trigger that performs an action that
causes the same trigger (or another trigger)
to fire again.
d) A trigger that is executed on a view.
You're looking for MCQs related to DBMS in
a distributed environment, specifically
focusing on multi-server, multi-user, and
multi-process operating systems, and client
interface requirements. This is a crucial area
for government exams.

Here are 40 Multiple Choice Questions

(MCQs) with answers, covering these topics.

DBMS: Distributed Application Environments

& Client Interfaces - Top 40 MCQs
Instructions: Choose the best answer for
each question.

Which of the following best describes a

Distributed Database Management System
(DDBMS)?
a) A database stored on a single server,
accessed by multiple users.
b) A collection of logically interrelated
databases distributed over a computer
network.
c) A database designed for real-time
processing on a single machine.
d) A system where data is stored in text files
across various locations.
Answer: b) A collection of logically
interrelated databases distributed over a
computer network.

What is the primary goal of distribution

transparency in a DDBMS?
a) To hide the physical location of data from
users.
b) To improve query processing speed.
c) To manage concurrent transactions more
effectively.
d) To reduce the cost of hardware.
Answer: a) To hide the physical location of
data from users.

In a multi-server DBMS environment, which

type of transparency allows users to treat a
distributed database as a single logical
database?
a) Transaction transparency
b) Performance transparency
c) Distribution transparency
d) Failure transparency
Answer: c) Distribution transparency

Which of the following is a key advantage of

a Distributed DBMS over a centralized
DBMS?
a) Simpler design and implementation.
b) Reduced security risks.
c) Improved availability and reliability.
d) Lower initial setup cost.
Answer: c) Improved availability and
reliability.

A multi-user operating system allows:

a) Only one program to run at a time.
b) Multiple users to access the same
computer system concurrently.
c) A single user to run multiple programs
simultaneously.
d) Offline processing only.
Answer: b) Multiple users to access the
same computer system concurrently.

Which component in a distributed

application typically initiates requests for
data or services?
a) Server
b) Client
c) Middleware
d) Database
Answer: b) Client
What is the role of a "middleware" in a
distributed application environment?
a) To manage hardware resources on the
server.
b) To provide an interface between clients
and servers.
c) To store the actual database data.
d) To process business logic exclusively on
the client side.
Answer: b) To provide an interface between
clients and servers.

Which of the following is a characteristic of a

multi-process operating system?
a) Only one program can reside in memory
at a time.
b) It supports the execution of multiple
programs or tasks concurrently.
c) It is limited to single-user environments.
d) It prioritizes disk I/O over CPU processing.
Answer: b) It supports the execution of
multiple programs or tasks concurrently.
In a client-server architecture, where does
the bulk of the processing for data
management typically occur?
a) On the client side
b) On the server side
c) Equally distributed between client and
server
d) Within the network infrastructure
Answer: b) On the server side

What is a "thin client" in a distributed

application environment?
a) A client with significant processing power
and local data storage.
b) A client that relies heavily on the server
for processing and data management.
c) A client primarily used for administrative
tasks.
d) A client that runs only command-line
interfaces.
Answer: b) A client that relies heavily on the
server for processing and data
management.
Which of the following is a common
requirement for client interfaces in
distributed application environments?
a) High processing power for complex
calculations.
b) Minimal network latency.
c) User-friendliness and intuitive navigation.
d) Direct access to raw database files.
Answer: c) User-friendliness and intuitive
navigation.

What does the acronym "API" stand for in

the context of client interfaces?
a) Application Program Interface
b) Advanced Process Integration
c) Automated Protocol Indicator
d) Access Point Identifier
Answer: a) Application Program Interface

Which type of distributed database

architecture involves a single global schema
and identical DBMS software at each node?
a) Heterogeneous DDBMS
b) Homogeneous DDBMS
c) Federated DDBMS
d) Centralized DBMS
Answer: b) Homogeneous DDBMS

When data is divided into smaller, logical

units and stored across different sites in a
DDBMS, this is known as:
a) Data replication
b) Data fragmentation
c) Data redundancy
d) Data aggregation
Answer: b) Data fragmentation

Which of the following is a challenge in

managing concurrency control in a multi-
user, multi-server DBMS?
a) Ensuring all users have the same data.
b) Preventing deadlocks and maintaining
data consistency.
c) Minimizing the number of concurrent
users.
d) Reducing the size of the database.
Answer: b) Preventing deadlocks and
maintaining data consistency.

What is the purpose of a Distributed

Transaction Manager (DTM) in a DDBMS?
a) To manage user authentication.
b) To ensure atomicity, consistency,
isolation, and durability (ACID) properties
across distributed transactions.
c) To optimize query performance.
d) To handle data backup and recovery at a
single site.
Answer: b) To ensure atomicity, consistency,
isolation, and durability (ACID) properties
across distributed transactions.

Which of the following is NOT a form of

distribution transparency?
a) Location transparency
b) Fragmentation transparency
c) Replication transparency
d) Security transparency
Answer: d) Security transparency

In a DDBMS, if a data item is replicated

across multiple sites, what is the main
benefit?
a) Reduced storage requirements.
b) Improved data integrity.
c) Enhanced data availability and fault
tolerance.
d) Simplified data updates.
Answer: c) Enhanced data availability and
fault tolerance.

Which of the following operating system

features is crucial for supporting a multi-
user DBMS?
a) Single-tasking
b) Memory protection and resource
allocation
c) Batch processing
d) Direct hardware access for all
applications
Answer: b) Memory protection and resource
allocation.

Client interfaces for distributed applications

often rely on communication protocols like:
a) FTP
b) HTTP/HTTPS
c) SMTP
d) UDP only
Answer: b) HTTP/HTTPS (and often TCP/IP
underneath, but HTTP/HTTPS is the common
application-level protocol for web-based
clients)

What is a "fat client" (or "thick client") in a

client-server architecture?
a) A client with minimal processing and data
storage.
b) A client that performs a significant
amount of processing and data
management locally.
c) A client designed for mobile devices.
d) A client that only connects to one specific
server.
Answer: b) A client that performs a
significant amount of processing and data
management locally.

Which type of database model is most

commonly used in distributed DBMS
environments?
a) Hierarchical
b) Network
c) Relational
d) Object-Oriented
Answer: c) Relational

What is the primary challenge associated

with data replication in a DDBMS?
a) Increased data availability.
b) Maintaining consistency among replicas.
c) Reduced query performance.
d) Simpler recovery procedures.
Answer: b) Maintaining consistency among
replicas.

In a multi-process operating system, what

mechanism allows different processes to
communicate and synchronize their
activities?
a) Single-threaded execution
b) Inter-Process Communication (IPC)
c) Direct memory access
d) Batch processing
Answer: b) Inter-Process Communication
(IPC)

Which of the following is a common

architectural style for distributed
applications that uses middleware for
communication?
a) Peer-to-Peer
b) Client-Server
c) Mainframe
d) Standalone
Answer: b) Client-Server
What is the goal of transaction serializability
in a multi-user DBMS?
a) To execute all transactions sequentially.
b) To ensure that concurrent transactions
produce the same result as some serial
execution.
c) To allow transactions to run indefinitely
without committing.
d) To minimize the number of rollbacks.
Answer: b) To ensure that concurrent
transactions produce the same result as
some serial execution.

Which of the following is a disadvantage of a

Distributed DBMS?
a) Increased reliability
b) Complex design and management
c) Improved local autonomy
d) Enhanced scalability
Answer: b) Complex design and
management
For client interfaces in a distributed
application, what is critical for ensuring data
security?
a) Using plain text communication.
b) Implementing strong authentication and
authorization mechanisms.
c) Storing all sensitive data on the client.
d) Bypassing network firewalls.
Answer: b) Implementing strong
authentication and authorization
mechanisms.

Which component of a multi-user operating

system is responsible for allocating CPU
time to different processes?
a) File system
b) Memory manager
c) Scheduler
d) Device driver
Answer: c) Scheduler

In a distributed database, how is "location

transparency" achieved?
a) By replicating all data at every site.
b) By requiring users to specify the exact
physical address of data.
c) By providing a global schema that masks
the distribution details.
d) By limiting data access to specific
geographic regions.
Answer: c) By providing a global schema
that masks the distribution details.

Which layer in a typical three-tier client-

server architecture is responsible for
business logic processing?
a) Presentation layer (Client)
b) Data layer (Database Server)
c) Application/Business Logic layer
(Application Server)
d) Network layer
Answer: c) Application/Business Logic layer
(Application Server)

What is "fragmentation transparency" in a

DDBMS?
a) Users are unaware that the database is
divided into fragments.
b) Users know the exact fragments they are
accessing.
c) Fragmentation is not allowed in the
system.
d) Only horizontal fragmentation is
supported.
Answer: a) Users are unaware that the
database is divided into fragments.

A multi-user DBMS typically requires robust

mechanisms for:
a) Single-user logging.
b) Concurrency control and recovery.
c) Disabling network access.
d) Manual data entry only.
Answer: b) Concurrency control and
recovery.

Which of the following is a primary role of a

"data dictionary" in a DBMS, especially in a
distributed environment?
a) To store actual user data.
b) To store metadata about the database,
including schema, fragmentation, and
replication information.
c) To process user queries.
d) To handle network communication
between servers.
Answer: b) To store metadata about the
database, including schema, fragmentation,
and replication information.

When designing client interfaces for

distributed applications, ensuring __________
is crucial for user adoption and efficient
workflow.
a) Complexity
b) Intuitiveness
c) High server load
d) Static content
Answer: b) Intuitiveness

Which type of operating system is essential

for a DBMS that serves many users
concurrently and performs multiple tasks
simultaneously?
a) Single-user, single-tasking
b) Multi-user, multi-tasking
c) Batch processing
d) Embedded system
Answer: b) Multi-user, multi-tasking

In a distributed transaction, what protocol is

commonly used to ensure that all
participating sites either commit or abort
the transaction together?
a) Two-Phase Locking (2PL)
b) Two-Phase Commit (2PC)
c) Timestamp Ordering
d) Optimistic Concurrency Control
Answer: b) Two-Phase Commit (2PC)

Which of the following is NOT a typical

function of a client interface in a distributed
application?
a) Presenting data to the user.
b) Collecting user input.
c) Executing complex business logic that
modifies the database schema directly.
d) Sending requests to the server.
Answer: c) Executing complex business logic
that modifies the database schema directly.
(This is usually done by the application or
database server after client request).

The ability of a DDBMS to continue

operating despite the failure of one or more
sites is known as:
a) Scalability
b) Reliability
c) Recoverability
d) All of the above
Answer: d) All of the above (Reliability and
Recoverability contribute to fault tolerance,
which is implied by continuous operation
despite failure).

What is a "service-oriented architecture"

(SOA) and how does it relate to distributed
applications?
a) It's a type of hardware infrastructure that
supports only centralized databases.
b) It's an architectural style where
applications are built from loosely coupled,
interoperable services, often distributed
across a network.
c) It's a programming paradigm limited to
single-user systems.
d) It's a method for securing local file
systems.
Answer: b) It's an architectural style where
applications are built from loosely coupled,
interoperable services, often distributed
across a network.