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

Which of the following DB2 products can only be installed on a System i server?

  1. DB2 for z/OS

  2. DB2 for i5/OS

  3. DB2 Data Warehouse Edition

  4. DB2 Enterprise Server Edition
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

DB2 for i5/OS is specifically designed to run only on IBM System i servers (formerly AS/400). DB2 for z/OS runs on mainframes, while Data Warehouse Edition and Enterprise Server Edition can run on multiple platforms including Linux, Unix, and Windows.

Multiple choice technology databases

Which of the following DB2 tools allows a user to set DB2 registry parameters?

  1. Task Center

  2. Visual Explain

  3. Configuration Assistant

  4. Satellite Administration Center
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

This is a duplicate of question 153119. The Configuration Assistant (db2ca) is the DB2 tool specifically designed for setting and managing DB2 registry parameters and instance configuration. Task Center is for scheduling tasks, Visual Explain shows query access plans, and Satellite Administration Center manages satellite databases.

Multiple choice technology databases

Which of the following tasks can NOT be performed using the Developer Workbench?

  1. Develop and debug an SQL stored procedure

  2. Develop and debug a user-defined data type

  3. Develop and debug a user-defined function

  4. Develop and run XML queries
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The Developer Workbench supports developing, debugging, and deploying stored procedures, user-defined functions (UDFs), and XML queries. However, it does not support the development or debugging of user-defined data types (UDTs), which are typically managed via command-line DDL statements.

Multiple choice technology databases

Which of the following can be viewed with the Journal?

  1. Historical information about tasks, database changes, messages, and notifications

  2. Information about licenses associated with each DB2 9 product installed on a particular system

  3. Graphical representations of data access plans chosen for SQL statements

  4. Warning and alarm thresholds for database indicators
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The DB2 Journal provides a comprehensive historical log of administrative activities, tracking task executions, database modifications, system messages, and notification events. This allows administrators to review past operations and diagnose issues. License information is viewed through the License Center, graphical access plans through Visual Explain, and warning thresholds through the Health Monitor.

Multiple choice technology databases

Which of the following is NOT a characteristic of an OLTP database?

  1. Granular transactions

  2. Current data

  3. Optimized for queries

  4. Frequent updates
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To solve this question, the user needs to have an understanding of OLTP (Online Transaction Processing) databases and their characteristics.

Now, let's go through each option and explain why it is right or wrong:

A. Granular transactions: This is a characteristic of OLTP databases. OLTP databases are designed to handle small, granular transactions that involve the insertion, deletion, or modification of individual records in real-time.

B. Current data: This is also a characteristic of OLTP databases. OLTP databases are optimized for real-time data processing and are designed to handle the most up-to-date information.

C. Optimized for queries: This is NOT a characteristic of OLTP databases. OLTP databases are optimized for transaction processing, not for complex queries or data analysis.

D. Frequent updates: This is a characteristic of OLTP databases. OLTP databases are designed to handle frequent updates to individual records in real-time.

Therefore, the correct answer is:

The Answer is: C

Multiple choice technology databases

Which of the following is NOT a characteristic of a data warehouse?

  1. Summarized queries that perform aggregations and joins

  2. Heterogeneous data sources

  3. Voluminous historical data

  4. Sub-second response time
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Sub-second response times are characteristic of Online Transaction Processing (OLTP) systems rather than data warehouses. Data warehouses focus on complex analytical queries (OLAP) involving heavy aggregations and joins over massive volumes of historical, heterogeneous data, which typically require more processing time.

Multiple choice technology databases

Which of the following products is used to shred extensible markup language documents?

  1. DB2 AVI Extender

  2. DB2 Text Extender

  3. DB2 XML Extender

  4. DB2 Spatial Extender
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The DB2 XML Extender provides capabilities to store, retrieve, and shred XML documents in DB2 databases, enabling integration of XML data with traditional relational data structures. Shredding means decomposing XML documents into relational table columns. The AVI Extender handles multimedia files, Text Extender manages text search, and Spatial Extender processes geospatial data.

Multiple choice technology databases

Which of the following best describes the difference between the DB2 Spatial Extender and the DB2 Geodetic Extender?

  1. The DB2 Spatial Extender uses a latitude-longitude coordinate system; the DB2 Geodetic Extender uses a planar, x- and y-coordinate system

  2. The DB2 Geodetic Extender is used to describe points, lines, and polygons; the DB2 Spatial Extender is used to find area, endpoints, and intersects

  3. The DB2 Spatial Extender treats the world as a flat map; the DB2 Geodetic Extender treats the world as a round globe

  4. The DB2 Geodetic Extender can be used to manage information like the locations of office buildings or the size of a flood zone; the DB2 Spatial Extender can be used for calculations and visualizations in disciplines like military command/control and asset
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The DB2 Spatial Extender uses a planar coordinate system treating the earth as a flat map with x-y coordinates, suitable for small-scale applications. The DB2 Geodetic Extender uses a spherical coordinate system treating the earth as a round globe with latitude-longitude, essential for accurate long-distance and global calculations. Option A reverses this distinction, while options B and D describe overlapping capabilities of both extenders.

Multiple choice technology databases

Which of the following is true about XML columns?

  1. XML columns are used to store XML documents as a hierarchical set of entities

  2. Only XQuery can be used to retrieve an XML document from an XML column

  3. XML columns must be altered to accommodate additional parent/child relationships if they are used in referential constraints

  4. In order to access any portion of an XML document stored in an XML column, the entire document must be retrieved
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

XML columns store XML documents natively in their hierarchical structure, preserving the document's entity relationships and organization. DB2 supports multiple retrieval methods including XQuery, SQL/XML functions, and XPath. You can extract portions of XML documents without retrieving the entire document, and XML columns don't require structural alterations for referential constraints as they maintain document integrity internally.

Multiple choice technology databases

Which of the following is the major difference between relational data and XML data?

  1. A. Relational data is self-describing; XML data is not

  2. B. Relational data has inherent ordering; XML data does not

  3. C. Relational data must be tabular; XML data does not have to be tabular

  4. D. Relational data is comprised of entities; XML data is comprised of numbers, characters, and dates
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Relational data MUST be organized in tabular format with rows and columns, adhering to a predefined schema. XML data is hierarchical and flexible, representing data as nested elements and attributes without requiring tabular structure. XML is actually MORE self-describing than relational data (A is wrong), XML preserves document order (B is wrong), and both can represent similar data types (D is not the major difference).

Multiple choice technology databases

Which of the following is the major difference between relational data and XML data?

  1. A. Relational data is self-describing; XML data is not

  2. B. Relational data has inherent ordering; XML data does not

  3. C. Relational data must be tabular; XML data does not have to be tabular

  4. D. Relational data is comprised of entities; XML data is comprised of numbers, characters, and dates
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Relational databases strictly store data in a flat tabular format consisting of rows and columns. In contrast, XML data is hierarchical and semi-structured, which does not require a tabular layout. Distractors are incorrect because XML data is self-describing, while relational data is not.

Multiple choice technology databases

Which of the following products is allowed to access other DB2 servers, but cannot accept requests from other remote clients?

  1. DB2 Personal Edition

  2. DB2 Workgroup Server Edition

  3. DB2 Enterprise Server Edition

  4. DB2 Data Warehouse Edition
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

DB2 Workgroup Server Edition is designed for workgroup environments where it can initiate connections to other DB2 servers while having certain restrictions on accepting remote client requests compared to Enterprise Edition. Personal Edition is local-only with no remote capabilities, while Enterprise and Data Warehouse Editions have full client/server capabilities without such restrictions. Workgroup balances client connectivity with server accessibility limitations.

Multiple choice technology databases

Which of the following products must be installed on an AIX server in order to build an application for AIX that will access a DB2 for z/OS database?

  1. DB2 Enterprise Server Edition

  2. DB2 Personal Developer's Edition

  3. DB2 Universal Developer's Edition

  4. DB2 Universal Database Enterprise Edition and DB2 Connect Enterprise Edition
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

DB2 Universal Developer's Edition includes the tools and libraries needed to build applications that will access DB2 for z/OS databases. This edition provides the development SDK with connectivity support for mainframe databases.

Multiple choice technology databases

Which of the following tools can be used to catalog a database?

  1. Visual Explain

  2. Alert Center

  3. Journal

  4. Configuration Assistant
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Visual Explain is the DB2 tool specifically designed to analyze and help optimize database access plans. While it primarily shows execution plans, it can also be used to catalog databases as part of the database setup and optimization workflow. The other options (Alert Center, Journal, Configuration Assistant) serve different purposes.