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

iPE Supports:

  1. Oracle only

  2. SQL Server Only

  3. Oracle & SQL Server Only

  4. Oracle, SQL Server & DB2
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

TIBCO iProcess Engine supports multiple major database platforms. While Oracle is common in enterprise deployments, iProcess also supports Microsoft SQL Server and IBM DB2, giving organizations flexibility in their database choice. Options A, B, and C are all incorrect because they artificially limit the supported platforms.

Multiple choice technology databases

Which of the following statements creates an Oracle account, but lets the operating system authenticate logons?
  1. create user ops$admin identified by os;
  2. create user ops$admin identified externally;
  3. create user ops$admin nopassword;
  4. create user ops$admin authenticated by os;
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

To solve this question, the user needs to have knowledge about Oracle database user authentication methods.

Option A: This option creates an Oracle user account with the name 'ops$admin' and the password 'os'. However, it uses a password file for authentication, not the operating system. Therefore, this option is incorrect.

Option B: This option creates an Oracle user account with the name 'ops$admin' and identifies it as an external user, which means that authentication is done by the operating system. Therefore, this option is correct.

Option C: This option creates an Oracle user account with the name 'ops$admin' and no password. This is not a secure method of authentication and is not related to operating system authentication. Therefore, this option is incorrect.

Option D: This option creates an Oracle user account with the name 'ops$admin' and specifies that it should be authenticated by the operating system. However, it does not create an operating system account for the user. Therefore, this option is incorrect.

The Answer is: B

Multiple choice technology

Content store doesn't contain ___________

  1. Report Specifications

  2. Packages

  3. Connection Information for datasources

  4. Actual Data stored in Query Database
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The Content Store is a database storing Cognos metadata - report specs, packages, models, security settings, data source connections, and portal configuration. It does NOT store actual business data from source databases.

Multiple choice technology

Measure and Regular Dimensions organized into cluster is referred as __________

  1. Star Schema Group

  2. Snow flak Schema Group

  3. Dimension Group

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

When measure dimensions and regular dimensions are organized into clusters in Framework Manager, this grouping is referred to as a Star Schema Group. This follows the dimensional modeling pattern where fact tables (measures) are surrounded by dimension tables in a star pattern. Snowflake is a different, more normalized pattern.

Multiple choice technology databases

Which things should be considered while deciding the size of undo tablespace in your database ( choose three)

  1. The size of an undo block

  2. The size of the redo log files.

  3. The size of the database buffer cache.

  4. The value of the UNDO_RETENTION parameter.

  5. Undo blocks generated per second.
Reveal answer Fill a bubble to check yourself
A,D,E Correct answer
Explanation

The UNDO tablespace size depends on three key factors: the physical size of each undo block, the UNDO_RETENTION parameter value (how long undo data must be kept), and the rate of undo block generation per second. The redo log size and buffer cache size influence general database performance but do not directly determine UNDO tablespace sizing.

Multiple choice technology mainframe

Which one of the following is not an integrity?

  1. Domain Integrity

  2. Entity Integrity

  3. Technology Integrity

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

In relational database theory, the three types of integrity are Domain Integrity (valid values for a column), Entity Integrity (uniqueness of rows/primary keys), and Referential Integrity (foreign key relationships). 'Technology Integrity' is not a recognized concept in database theory.

Multiple choice technology programming languages

When you need to perform complex calculations or access external resources in SQL Server 2005, which one of the following programming methods do you use?

  1. SQL Server Management Studio

  2. CLR User Defined Type

  3. CLR Stored Procedure

  4. T-SQL View
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

CLR Stored Procedures enable developers to write database logic using .NET languages, which is ideal for complex mathematical computations and accessing external system resources.

Multiple choice technology programming languages

Which one of following is false about Microsoft-provided data provider classes in ADO.NET ?

  1. SQLServer.NET data provider is faster than any other provider.

  2. OLE-DB.NET is universal for accessing various data sources other than SQL Server also, but it’s a .NET layer on top of OLE layer, so is comparatively slow.

  3. ODBC.NET is a deprecated layer provided for backward compatibility to ODBC engines.

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

All three statements are accurate. SQL Server.NET is optimized and fastest for SQL Server. OLE-DB.NET adds a layer making it slower but universal. ODBC.NET is indeed deprecated for legacy compatibility. Therefore none are false.

Multiple choice technology programming languages

Which of the following is true about xp command shell in SQL Server 2005:

  1. xp command shell can be enabled by sysadmin by executing following: EXEC sp_configure 'xp_cmdshell', 0;

  2. xp command shell is enabled by default;

  3. xp command shell can be used to run bcp utility

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

xp_cmdshell is an extended stored procedure that executes operating system commands from SQL Server, so it can invoke any command-line utility including bcp (Bulk Copy Program). For security, xp_cmdshell is disabled by default and must be explicitly enabled with sp_configure, setting the value to 1 (not 0, which disables it).

Multiple choice technology programming languages

When you need to perform complex calculations or access external resources in SQL Server 2005, which one of the following programming methods do you use?

  1. SQL Server Management Studio

  2. CLR User Defined Type

  3. CLR Stored Procedure

  4. T-SQL View
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

CLR Stored Procedures allow developers to write database logic using .NET languages, making them ideal for complex calculations, string manipulation, and accessing external system resources that are difficult in T-SQL.

Multiple choice technology databases

Package is a

  1. Compiled form of application plan

  2. Compiled form of Database Requests

  3. Collection of Sql statements

  4. Collection of Application plansCollection of Application plans
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In DB2, a package is a database object containing control structures compiled from Database Request Modules (DBRMs), which are generated during the precompilation of application programs containing embedded SQL.

Multiple choice technology databases

Responsibility of OPTIMIZER is :

  1. Compiles the SQL statements

  2. Complies the list of packages into application plan

  3. Stored the application plan and packages into Directory

  4. Chooses the best access path from catalog tables
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To answer this question, the user needs to know about the role of an optimizer in database management systems.

The optimizer is a component of a database management system (DBMS) that is responsible for generating the most efficient execution plan for a given SQL query.

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

A. Compiles the SQL statements: This option is partially correct. The optimizer does not compile SQL statements, but it does analyze the query and generates an execution plan to execute the SQL statement.

B. Complies the list of packages into application plan: This option is incorrect. The job of the precompiler is to compile the list of packages into an application plan, not the optimizer.

C. Stored the application plan and packages into Directory: This option is incorrect. The job of the precompiler is to store the application plan and packages into a directory, not the optimizer.

D. Chooses the best access path from catalog tables: This option is correct. The optimizer analyzes the query and chooses the best access path from a set of possible access paths based on the metadata stored in the catalog tables.

Therefore, the correct answer is:

The Answer is: D

Multiple choice technology databases

Which utility is used to retrieve data from a Image copy

  1. LOAD

  2. UNLOAD

  3. REORG

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

To answer this question, the user needs to have an understanding of database management and utilities used to manipulate data in a database.

The utility used to retrieve data from an Image copy is the UNLOAD utility. This utility is used to extract data from a table or set of tables and write it to an output data set. The output data set can then be used to restore the data to a database using the LOAD utility.

Option A, LOAD, is incorrect because it is used to load data into a database from an input data set. It is the opposite of the UNLOAD utility.

Option C, REORG, is incorrect because it is used to reorganize a table or set of tables in a database, not to retrieve data from an Image copy.

Option D, RUNSTATS, is incorrect because it is used to collect statistics about the data in a table or set of tables. It is not used to retrieve data from an Image copy.

Therefore, the correct answer is:

The Answer is: B. UNLOAD