Computer Knowledge

Database Management Systems

5,344 Questions

Database Management Systems (DBMS) form the core framework for data storage, retrieval, and security in modern software applications. Concepts such as the E-R model, backup planning, SQL integration, and big data architecture are essential for computer knowledge sections. This hub offers a comprehensive set of practice questions to master DBMS fundamentals and advanced database operations.

E-R Model ConceptsBackup and RecoverySQL Server UpgradesJDBC and ODBCBig Data CharacteristicsData VirtualizationOracle Database

Database Management Systems Questions

Multiple choice technology databases

You want to identify the top 10 queries in SQL Server that have been recompiled the most times on your server. What can you do?

  1. Use the sys.dm_exec_requests dynamic management view.

  2. Use the sys.dm_exec_sessions dynamic management view

  3. Use the sys.dm_exec_query_stats dynamic management view

  4. Use the sys.dm_exec_query_optimizer_info dynamic management view
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The sys.dm_exec_query_stats DMV returns performance statistics for cached query plans, including columns like sql_handle, plan_handle, and execution_count that track recompilation events. The requests and sessions DMVs track current activity, while query_optimizer_info contains optimizer statistics but not per-query recompilation counts.

Multiple choice technology databases

When a user creates an object without a TABLESPACE clause, where will Oracle store the segment?

  1. System tablespace

  2. Users tablespace

  3. Default tablespace for the user

  4. Default tablespace for system
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

When a user creates an object (table, index, etc.) without specifying a TABLESPACE clause, Oracle stores the segment in that user's default tablespace, as defined when the user was created or altered. The system tablespace is for data dictionary objects, and there is no concept of a 'default tablespace for system' in this context.

Multiple choice technology databases

Which one of the following library type is used in DLI jobs to dynamically allocate database clusters?

  1. PSBLIB

  2. DBDLIB

  3. ACBLIB

  4. USERLIB
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In IMS DLI environments, USERLIB is the library type used for dynamically allocating database clusters. PSBLIB is for partitioned datasets, DBDLIB contains database descriptions, and ACBLIB holds access control blocks - none of these are used for dynamic cluster allocation.

Multiple choice technology databases

Which of the following is an important consideration when tuning an SQL statement?

  1. The number of CPUs on the server

  2. The degree of parallelism on the tables

  3. The use of bitmap indexes

  4. The quality of the SQL optimization
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

SQL optimization quality is the most critical factor for statement performance because poorly written SQL cannot be fixed by hardware or infrastructure. While CPUs (A), parallelism (B), and bitmap indexes (C) can help, they cannot compensate for fundamentally inefficient query logic.

Multiple choice technology databases

Which of the following database design features is most important to SQL performance?

  1. Removal of data redundancy

  2. The introduction of data redundancy

  3. The introduction of non-first normal form relations

  4. The introduction of SQL*Plus
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Introducing data redundancy (denormalization) is a key design feature used to optimize SQL performance in read-heavy applications. By replicating data, it reduces the need for expensive table joins. Although normal forms remove redundancy to protect data integrity, they can degrade query performance due to join overhead.

Multiple choice technology databases

Why is searching for large-table full-table scans critical to SQL tuning?

  1. They indicate an optimized execution plan.

  2. They may be able to be tuned to use an index

  3. The full-table scan should be normalized from the database design

  4. A full-table scan is always sub-optimal.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To answer this question, the user needs to understand the basics of SQL tuning and database optimization.

The correct answer is:

B. They may be able to be tuned to use an index

Explanation:

A full-table scan is a process in which the database engine reads all the rows of a table to find the relevant data. This can be a time-consuming process, especially for large tables. Full-table scans are not always sub-optimal, but they can be inefficient if the table is not properly indexed. When a table is not indexed correctly, the database engine must read every row of the table to find the relevant data, even if only a small subset of the data is needed.

Therefore, searching for full-table scans is critical to SQL tuning because it allows a database administrator to identify tables that are not properly indexed. Once identified, the administrator can then tune the full-table scan to use an index, which can improve performance and reduce the time it takes to retrieve the data.

Option A is incorrect because a full-table scan does not indicate an optimized execution plan. It may be the only option available, but it is not necessarily optimized.

Option C is incorrect because a full-table scan is not something that should be normalized from the database design. It is a method of retrieving data from a table and can be optimized through proper indexing.

Option D is incorrect because a full-table scan is not always sub-optimal. It depends on the size of the table and how well it is indexed.

Multiple choice technology databases

What tool should you recommend for a junior DBA to use to create a simple backup strategy for a SQL Server database?

  1. Database Maintenance Plan Wizard

  2. Database Engine Tuning Advisor

  3. SQL Server Configuration Manager

  4. Database Maintenance design surface
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The Database Maintenance Plan Wizard is a user-friendly tool built into SQL Server Management Studio designed to guide DBAs through setting up routine maintenance tasks, including backups. The Engine Tuning Advisor optimizes index performance, and Configuration Manager manages network protocols.

Multiple choice technology databases

You have a 1TB SalesDB database running on SQL Server 2003 Enterprise Edition. You have noticed that the nightly DBCC CHECKDB ('SalesDB') job is starting to take too long, and you want to reduce the amount of time that the check will take. What command should you schedule to run nightly instead?

  1. DBCC CHECKDB ('SalesDB', REPAIR_ALLOW_DATA_LOSS)

  2. DBCC CHECKDB ('SalesDB', REPAIR_REBUILD)

  3. DBCC CHECKDB ('SalesDB') WITH PHYSICAL_ONLY

  4. DBCC CHECKDB ('SalesDB') WITH DATA_PURITY
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Using DBCC CHECKDB with the WITH PHYSICAL_ONLY option reduces check time on large databases by limiting checks to physical page structure, page integrity, and allocation. This skips resource-intensive logical checks. Options like REPAIR_ALLOW_DATA_LOSS or REPAIR_REBUILD are used to fix errors.

Multiple choice technology databases

You want to create a replication strategy that will allow users in four branch offices to have a fresh copy of data from the database in the central office every day at 5 a.m. for their daily reports. Although there are many changes to the data during the course of a day, the entire publication is not that large. Which replication strategy should you use in SQL Server?

  1. Transactional replication

  2. Peer-to-peer replication

  3. Merge replication

  4. Snapshot replication
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Snapshot replication is ideal for this scenario because it delivers a complete data copy daily at 5am, matching the reporting requirement. Transactional replication is continuous, peer-to-peer is for multi-master scenarios, and merge replication handles conflict resolution - all unnecessary for this daily batch refresh pattern.

Multiple choice technology databases

You write a SQL Server stored procedure that will return data for a report. The accuracy of the data is not so important; however, you want to avoid dirty reads. Which isolation level should you avoid?

  1. Read Committed

  2. Read Uncommitted

  3. Repeatable Read

  4. Serializable
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The requirement is to avoid dirty reads, which Read Uncommitted explicitly allows (it reads uncommitted data). Read Committed, Repeatable Read, and Serializable all prevent dirty reads. Since dirty reads must be avoided, Read Uncommitted is the level to avoid.

Multiple choice technology databases

You want to identify the top 10 queries in SQL Server that have been recompiled the most times on your server. What can you do?

  1. Use the sys.dm_exec_requests dynamic management view.

  2. Use the sys.dm_exec_sessions dynamic management view

  3. Use the sys.dm_exec_query_optimizer_info dynamic management view

  4. Use the sys.dm_exec_query_stats dynamic management view
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The sys.dm_exec_query_stats Dynamic Management View returns performance statistics for cached query plans. It includes the plan_generation_num column, which increments each time a query is recompiled, allowing you to identify top recompiled queries. Other views like sys.dm_exec_requests monitor active sessions.

Multiple choice technology databases

Which of the following tables contains a list of all valid transaction identifiers and their associated Programs for CICS?

  1. Temporary Storage Table (TST)

  2. Program Processing Table (PPT)

  3. Program Control Table (PCT)

  4. File Control Table (FCT)
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In IBM CICS, the Program Control Table (PCT) contains definitions of all transaction identifiers and maps them to their respective initial application programs. The PPT lists programs and their locations, the TST defines temporary storage queues, and the FCT defines files and their access characteristics.

Multiple choice technology databases

Which of the following table contains information about DB2?

  1. Temporary Storage Table (TST)

  2. Program Processing Table (PPT)

  3. Resource Control Table (RCT)

  4. File Control Table (FCT)
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The Resource Control Table (RCT) in CICS contains DB2 interface information including transaction-to-plan bindings and security parameters. RCT defines which CICS transactions can access which DB2 plans and resources. TST manages temporary storage queues, PPT contains program and mapset definitions, and FCT controls file access for VSAM files.