Computer Knowledge

Database Management Systems

5,543 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 three property areas must be configured when using the ODBC connector stage as a target in your job design? (Choose three.)

  1. Define the connection properties to an ODBC data source.

  2. Define columns for the output link.

  3. Specify properties for the input link.

  4. Define columns for the input link data.
Reveal answer Fill a bubble to check yourself
A,C,D Correct answer
Explanation

When using the ODBC connector stage as a target, you must define: (1) connection properties to reach the ODBC data source, (2) input link properties to control how data flows into the stage, and (3) column definitions for the input data to map incoming fields. Output link column definition (Option B) is not required for a target stage since data flows in through the input link, not out.

Multiple choice technology databases

Which action produces the most accurate Table Definitions for usage with the Oracle Enterprise stage?

  1. Use import "Oracle Table Definitions" .

  2. Use import "Via bridges".

  3. Use import "Plug-in Meta Data Definitions".

  4. Use import "Orchestrate Schema Definitions" using orchdbutil.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The most accurate method is using Orchestrate Schema Definitions via orchdbutil because it directly queries Oracle's system tables to extract precise column definitions, data types, lengths, and constraints. This native Oracle bridge captures metadata more completely than generic importers. Options A, B, and C use higher-level import mechanisms that may lose precision or require additional manual verification.

Multiple choice technology mainframe

Please identify the utility to kill DB2 utilities, which is running / existing on table.

  1. A.KILL(Utilityname)

  2. B. TERM(UTILITY NAME)

  3. C.TERM

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

The DB2 command used to cancel or stop a running utility is -TERM UTILITY(utility-id), where TERM is short for terminate. KILL is not a valid DB2 command for terminating utilities, and TERM alone without specifying the utility name or ID is syntactically incomplete.

Multiple choice technology mainframe

DB2 query to get the bind plans attached with the tables.

  1. A. SELECT * FROM DB2P.SYSIBM.SYSPLANDEP WHERE BCREATOR = 'DBNAME' AND BTYPE = 'T' ORDER BY BNAME, DNAME

  2. B. SELECT BNAME, BQUALIFIER, BTYPE, DCOLLID, DNAME FROM DB2P.SYSIBM.SYSPACKDEP WHERE BQUALIFIER = 'DATABASE NAME' AND BTYPE = 'T' ORDER BY BNAME, DNAME

  3. C. (A) or (B)

  4. D. Neither (A) nor (B)
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

DB2 system catalogs track plan-table dependencies. Both SYSIBM.SYSPLANDEP and SYSIBM.SYSPACKDEP contain this information depending on the DB2 version and configuration, so either query could work depending on your environment.

Multiple choice technology mainframe

Which of the following is not DATABASE privilege?

  1. A. REPAIR

  2. B. UPDATE

  3. C. LOAD

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

In DB2, REPAIR, LOAD, and IMAGCOPY are database-level utilities that require special privileges. UPDATE is a standard DML (Data Manipulation Language) operation that operates on table-level data, not a database administration privilege.

Multiple choice technology databases

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

  1. Default tablespace for the user

  2. Oracle will give an error

  3. System tablespace

  4. Users tablespace
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Oracle automatically stores a user's objects in their default tablespace when no TABLESPACE clause is specified. This default tablespace is assigned when the user is created. The SYSTEM tablespace is reserved for data dictionary objects, and user objects are not stored there by default.

Multiple choice technology mainframe

Drop dependencies

  1. A. STORAGE GROUP -->DATABASE --> TABLESPACE -->TABLE--> VIEW

  2. B.TABLESPACE -> DATABASE-> TABLE-> VIEW--> STORAGE GROUP

  3. C. DATABASE --> TABLE --> TABLE SPACE --> VIEW-->STORAGE GROUP

  4. D. VIEW --> TABLE --> DATABASE--> TABLE SPACE --> STORAGE GROUP
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The correct hierarchy in database storage organization is Storage Group (physical containers) → Database (logical grouping) → Tablespace (storage organization) → Table (actual data) → View (virtual table representation). This represents the progression from physical storage containers to logical database structures, with views being derived from tables.

Multiple choice technology

Which of the following about the data and index caches of a lookup transformation are true?

  1. Index cache stores data for the connected output ports and unconnected return ports

  2. Data cache stores data for the connected output ports and not for unconnected return ports

  3. Index cache stores data for the columns used in the lookup condition.

  4. Data cache stores data for the columns used in the lookup condition
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In Informatica lookup transformations, the index cache stores data for columns used in the lookup condition to optimize performance. The data cache stores data for the connected output ports. This separation allows faster lookup operations by indexing the condition columns.

Multiple choice technology

SQL scripts invoked by Report Types are generally placed in the

  1. Application server

  2. Database server

  3. Local server

  4. Remote server
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

SQL scripts invoked by Report Types are typically placed on the Application server rather than the Database server. This architecture allows the application server to manage report execution and SQL script invocation, keeping database servers focused on data storage and query execution rather than report logic.

Multiple choice technology

The Workflow Monitor provides session-specific details that can help to tune

  1. Buffer block size

  2. Index and data cache size for Aggregator

  3. Index and data cache size for Joiner

  4. Index and data cache size for Source qualifier
Reveal answer Fill a bubble to check yourself
A,B,C Correct answer
Explanation

The Workflow Monitor provides session-specific details that help tune performance. Buffer block size can be optimized based on performance counters. Index and data cache sizes for Aggregator and Joiner transformations can be tuned based on session details. Source Qualifier does not have cache settings - cache is configured at the source/target level, not for Source Qualifier itself.

Multiple choice technology

Select the correct ones with respect to XML target caches

  1. Index cache-Stores primary and foreign key information in separate caches

  2. Index cache-Stores XML row data while it generates the XML target

  3. Data cache-Stores primary and foreign key information in separate caches

  4. Data cache-Stores XML row data while it generates the XML target
Reveal answer Fill a bubble to check yourself
A,D Correct answer
Explanation

For XML target caches in Informatica, the index cache stores primary key and foreign key information in separate caches, while the data cache stores the actual XML row data during XML target generation. Option B is incorrect because index cache stores key information, not row data. Option C is incorrect because data cache stores row data, not key information.

Multiple choice technology

What are all the methods that can be used to identify source bottlenecks for relational sources?

  1. Use a Filter transformation to measure the time it takes to read source data

  2. Use a read test mapping

  3. Use a database query

  4. Use a Sorter transformation to measure the time it takes to read source data
Reveal answer Fill a bubble to check yourself
A,B,C Correct answer
Explanation

Identifying relational source bottlenecks requires measuring query/data retrieval performance: Filter transformation with timing, read test mapping (single-source mapping to test speed), or running the source database query directly. A Sorter transformation processes already-read data and doesn't measure source read time.

Multiple choice technology

Data warehousing is a key technology on the way

  1. to establishing business intelligence.

  2. designs and manufactures automated solutions

  3. to Adoption of Key Infrastructure

  4. None of the above.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Data warehousing consolidates data from multiple operational systems into a unified repository. This consolidated view enables business intelligence (BI) tools to perform analytics, reporting, and data mining across the entire organization, supporting strategic decision-making that operational systems cannot provide.

Multiple choice technology

Operational data is application oriented where as DW is

  1. subject oriented.

  2. connection oriented

  3. object oriented.

  4. service oriented.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Operational systems are organized around business processes and applications (order processing, payroll, inventory). Data warehouses are organized around business subjects (customer, product, sales) to facilitate cross-functional analysis. This subject orientation allows reporting across the entire organization rather than within application silos.