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 platforms and products

The value proposition of the Sun Oracle Database Machine is based primarily on extreme performance.

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

This statement is false - the Sun Oracle Database Machine's value proposition is NOT based primarily on extreme performance. While performance is important, the key value propositions include workload consolidation, reduced complexity, lower TCO, extreme reliability, and efficiency. Performance is one benefit among many, not the primary value driver.

Multiple choice technology platforms and products

Which of the following are not key value propositions for the Sun Oracle Database Machine

  1. Data Warehousing

  2. OLTP

  3. Middleware Integration

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

The question contains a logical error. Data Warehousing, OLTP, and Consolidation ARE key value propositions for the Oracle Database Machine. Middleware Integration is the outlier - it's not typically listed as a primary value proposition for this database appliance, which focuses on data processing workloads and consolidation.

Multiple choice technology platforms and products

Which of the following is not an official hardware upgrade path for the Sun Oracle Database Machine?

  1. Basic System + Basic System Upgrade = Quarter Rack

  2. Quarter Rack + Quarter Rack Upgrade = Half Rack

  3. Half Rack + Quarter Rack Upgrade = 3/4 Rack

  4. Half Rack + Half Rack Upgrade = Full Rack
Reveal answer Fill a bubble to check yourself
A,C Correct answer
Explanation

Official upgrade paths follow a structured progression. Basic System to Quarter Rack, Quarter to Half Rack, and Half to Full Rack are the documented paths. Half Rack + Quarter Rack to 3/4 Rack is NOT an official path - Oracle supports Quarter/Half/Full Rack configurations, not 3/4 Rack.

Multiple choice technology platforms and products

Which of the following are considered "Yellow lights" around a Proof of Value (POV) for the Sun Oracle Database Machine?

  1. The POV must only prove that performance is "faster" than current performance or the competition.

  2. There is no commitment to buy upon POV success

  3. The customer does not assign a full-time resource to the POV.

  4. A key decision maker is not involved in the POV process.
Reveal answer Fill a bubble to check yourself
A,B,C,D Correct answer
Explanation

Yellow lights indicate risks that could jeopardize a POV. Performance must be proven measurable, not just faster. No purchase commitment reduces urgency. Lack of dedicated resources undermines success. Missing key decision maker buy-in prevents adoption. All are valid warning signs.

Multiple choice technology databases

Which of the following is applicable for Exadata Hybrid Columnar Compression?

  1. a. The database stores the same column for a group of rows together

  2. b. The data block does not store the rows in the row-major format

  3. c. The database stores the identical rows together with a reference for each row

  4. d. Database operations works transparently against compressed objects without the need of changing application code
Reveal answer Fill a bubble to check yourself
A,B,D Correct answer
Explanation

EHCC stores column data for a group of rows together instead of using traditional row-major formatting, allowing database operations to execute fully transparently against compressed objects. It does not store identical rows with references, which makes option c incorrect. Therefore, options a, b, and d are correct.

Multiple choice technology databases

The database stores a set of rows in an internal structure called a compression unit in case of Exadata Hybrid Columnar Compression.

  1. a. Compression unit can span across multiple data blocks

  2. b. The value of particular column can span across multiple blocks

  3. c. If a column in a compression unit is updated, the database will take a lock only on the updated rows

  4. d. If a column in a compression unit is updated, the database will take a lock on the entire compression unit
Reveal answer Fill a bubble to check yourself
A,B,D Correct answer
Explanation

Exadata Hybrid Columnar Compression uses Compression Units (CU) that can span multiple blocks. Individual column values can also cross block boundaries. By default, updates to a compressed row lock the entire CU, decreasing concurrency. Therefore, options a, b, and d are correct, while c is incorrect because row-level locking is not default.

Multiple choice technology databases

When tables use Exadata Hybrid Columnar Compression, Oracle DML locks larger blocks of data (compression unit) which might reduce concurrency

  1. a. For basic and online archival compression inserted and updated rows are uncompressed

  2. b. Higher compression levels of Exadata Hybrid Columnar Compression are achieved only with data that is direct-path inserted

  3. c. High CPU overhead in case Warehouse compression

  4. d. Conventional inserts and updates are supported in Exadata Hybrid Columnar Compression but the extent of compression is low
Reveal answer Fill a bubble to check yourself
A,B,C,D Correct answer
Explanation

Exadata Hybrid Columnar Compression (EHCC) stores data in compression units, which are larger blocks that Oracle locks during DML operations, potentially reducing concurrency. Basic compression leaves inserted/updated rows uncompressed until they are moved during maintenance operations. Higher compression levels (Archive) require direct-path insert operations to achieve maximum compression ratios. Conventional DML is supported but results in lower compression efficiency, while warehouse compression can incur higher CPU overhead during compression and decompression operations.

Multiple choice technology databases

Which of the following statement(s) is (are) true?

  1. a. COMPRESS FOR QUERY HIGH should be used where load performance is critical

  2. b. COMPRESS FOR QUERY LOW should be used where load performance is critical

  3. c. COMPRESS FOR ARCHIVE HIGH should be used for rarely accessed application

  4. d. COMPRESS FOR ARCHIVE HIGH should be used for heavily accessed application
Reveal answer Fill a bubble to check yourself
B,C Correct answer
Explanation

COMPRESS FOR QUERY LOW is designed for load performance, using lower compression overhead for faster data loading while still providing query performance benefits. COMPRESS FOR QUERY HIGH provides better compression but slower load times. COMPRESS FOR ARCHIVE HIGH is for rarely accessed data requiring maximum compression, not for frequently accessed applications. Archive compression levels add significant overhead during data loading and modification, making them unsuitable for active workloads.

Multiple choice technology databases

DBMS_COMPRESSION package helps to determine the expected compression level. COMPRESSION and COMPRESS_FOR column of user_tables provides whether compression is enabled or disabled and compression method respectively

  1. a. CELL_OFFLOAD_PLAN_DISPLAY

  2. OPT_PARAM

  3. c. CELL_OFFLOAD_PROCESSING

  4. d. CELL_PARTITION_LARGE_EXTENTS
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The CELL_OFFLOAD_PROCESSING parameter controls whether predicate filtering and other operations are offloaded to Exadata storage cells. This parameter can be set at system or session level and overrides the default behavior of using cell offload capabilities. The other options (CELL_OFFLOAD_PLAN_DISPLAY, OPT_PARAM, CELL_PARTITION_LARGE_EXTENTS) are not the correct parameter names for controlling cell offload processing behavior.

Multiple choice technology databases
  1. CELL_OFFLOAD_PROCESSING parameter can be used to compare the performance of Exadata Cell with conventional storage

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

CELL_OFFLOAD_PROCESSING parameter controls whether predicate evaluation is offloaded to storage cells, not the overall performance comparison between Exadata and conventional storage. To compare Exadata Cell with conventional storage, you would disable cell offload features or use different testing methodologies. This parameter specifically targets offload processing behavior, not comprehensive storage subsystem performance.

Multiple choice technology databases

Which of the following statement(s) is (are) true?

  1. a. CELL_PARTITION_LARGE_EXTENTS initialization parameter can be used to set the INITIAL storage parameter to a preferred value for all segments in a partitioned object

  2. b. This parameter is relevant only to LMT with AUTOALLOCATE option

  3. c. This parameter is relevant only to LMT with UNIFORM option

  4. d. This parameter does not impact non-partitioned object and has valid values as TRUE, ALWAYS and DEFAULT
Reveal answer Fill a bubble to check yourself
A,B,D Correct answer
Explanation

The initialization parameter _CELL_PARTITION_LARGE_EXTENTS (often used in Exadata) changes default initial extents for partitioned tables/indexes to 8MB. It applies to Locally Managed Tablespaces (LMT) with AUTOALLOCATE, affects only partitions, and accepts TRUE, ALWAYS, and DEFAULT.

Multiple choice technology databases

For heavily partitioned tables, do not use SQL*Loader to load data. Instead put the data in a flat file and use parallel INSERT direct load from the flat file using the external table features of database.

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

For heavily partitioned tables, SQL*Loader may not be the most efficient loading mechanism. Using external tables with parallel INSERT direct load provides better performance by leveraging parallel processing and direct-path insert capabilities. External tables read from flat files and enable parallel direct-path inserts, which are optimized for partitioned table loading scenarios.