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 of the following DB2 objects are publicly referenced names that require no special authority or privilege to use them?

  1. View

  2. Alias

  3. Table

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

An alias is a publicly referenced object name in DB2 that requires no privileges to use. Any user can reference an alias, as authorization checks are deferred to the underlying tables or views when SQL statements are compiled. View, Table, and Package require explicit access privileges.

Multiple choice technology databases

A sequence was created with the DDL statement shown below: CREATE SEQUENCE my_sequence CACHE 10 ORDER The following statements are successfully executed in sequence through separate database connections: CONNECTION1 - VALUES NEXT VALUE FOR my_sequence INTO :con1hvar CONNECTION2 VALUES NEXT VALUE FOR my_sequence INTO :con2hvar CONNECTION1 - VALUES NEXT VALUE FOR my_sequence INTO :con1hvar What is the current value of the :con1hvar host variable?

  1. 2

  2. 3

  3. 11

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

With CACHE 10 and ORDER specified, the sequence allocates values in a thread-safe manner. CONNECTION1 gets 1 (first call), CONNECTION2 gets 2 (second call from different connection), then CONNECTION1 gets 3 (third call overall). The ORDER clause guarantees strict ordering even with multiple connections. Option A (2) would be correct only for the second call, and options C and D incorrectly assume larger jumps or connection-specific caching.

Multiple choice technology databases

A DRDA host database resides on a z/OS or an i5/OS system and listens on port 446. The TCP/IP address for this system is 192.168.10.1 and the TCP/IP host name is myhost. Which of the following commands is required to update the local node directory so that a DB2 client can access this DRDA database?

  1. CATALOG TCPIP NODE myhost REMOTE db2srv SERVER 446

  2. CATALOG TCPIP NODE mydb2srv REMOTE myhost SERVER 446

  3. CATALOG TCPIP NODE myhost REMOTE db2srv SERVER 192.168.10.1

  4. CATALOG TCPIP NODE mydb2srv REMOTE myhost SERVER 192.168.10.1
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The CATALOG TCPIP NODE command syntax is CATALOG TCPIP NODE REMOTE SERVER . The local node name (mydb2srv) is an alias you create - it is not the remote hostname. Option B correctly specifies mydb2srv as the local node name and myhost as the remote system. Options A and C incorrectly use the remote hostname as the local node name. Option D incorrectly uses the IP address instead of the port number for the SERVER parameter.

Multiple choice technology databases

A stored procedure object is created into which DB2 object?

  1. Alias

  2. Schema

  3. Package

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

In DB2, stored procedures are database objects that are created within a schema. A schema is a namespace that organizes related database objects including tables, views, routines (stored procedures and functions), and other objects. When you create a stored procedure without explicitly specifying a schema, it is created in the default schema for the user. Options A (Alias), C (Package), and D (Routine Space) are not containers for stored procedures.

Multiple choice technology databases

An application needs to store a 5MB JPEG image in a DB2 table. Which datatype should be used to specify a column that will be used for storing A image ?

  1. GRAPHIC

  2. BINARY

  3. IMAGE

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

To solve this question, the user needs to know the different datatypes that can be used to store binary data, such as images, in a DB2 table.

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

A. GRAPHIC: This datatype is used to store fixed-length character data in Unicode format. It is not appropriate for storing binary data such as images.

B. BINARY: This datatype is used to store binary data in a fixed-length format. It is a valid option for storing images, but it does not provide compression capabilities.

C. IMAGE: This datatype is used to store large objects in a compressed format. It is an option for storing images, but it is not recommended as it has been deprecated in recent versions of DB2.

D. BLOB: This datatype is used to store large binary objects in a compressed format. It is the recommended option for storing images in a DB2 table as it provides compression capabilities and can handle large objects.

Therefore, the correct answer is:

The Answer is: D. BLOB

Multiple choice technology databases

In which situation is shredding XML data recommended?

  1. When the data is naturally tabular

  2. When the number of nodes are volatile

  3. When the data by nature has sparse attributes

  4. When the data is of low volume and requires a complex star-schema topology
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Shredding XML means decomposing XML documents into relational tables. This is recommended when the XML data has a natural tabular structure - where elements consistently map to columns and rows. The data can then be queried and joined efficiently using SQL. Option B is incorrect because volatile nodes would require frequent schema changes. Option C is incorrect because sparse attributes are better handled in XML's flexible structure rather than fixed relational columns. Option D is incorrect because complex star-schema topology and low volume data don't justify the overhead of shredding.

Multiple choice technology databases

Which datatype stores data outside Oracle Database?

  1. UROWID

  2. BFILE

  3. BLOB

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

BFILE is an Oracle data type that stores a locator to a binary file operating system file outside the database. Unlike BLOB which stores data internally, BFILE maintains only a pointer to external files, allowing large objects to be stored outside database while keeping reference.

Multiple choice technology

For connecting two tables from the different databases we need to use the following transformation

  1. Source qualifier

  2. Associated Source qualifier

  3. Joiner

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

Joiner transformation in Informatica is specifically designed to combine data from heterogeneous sources, including different databases. Source qualifiers only work within a single database connection and cannot join across different databases.

Multiple choice technology

we connect to the databases in informatica through the ........ client

  1. Database source name

  2. Database specific name

  3. Data structure norms

  4. Data set name
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Informatica connects to databases using database clients and connection definitions. The term 'database source name' likely refers to ODBC Data Source Names (DSN) or similar connection identifiers used to establish database connections in workflows.

Multiple choice technology

How to add partition points in a session?

  1. From the Mappings tab of the session properties.

  2. From the properties tab of the session properties.

  3. From the config object tab of the session properties.

  4. rom the components tab of the session properties.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

In Informatica session properties, partition points are configured through the Mappings tab. This is where you define how data should be partitioned as it flows through the pipeline. The Config Object and Properties tabs handle different aspects of session configuration.

Multiple choice technology mainframe

Name the database to see inforce scorecard changes to the policy

  1. PLPDATA

  2. PLPINFSCA

  3. PLPIFSC

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

PLPIFSC database is specifically designed to store inforce scorecard information for insurance policies. The 'IFSC' in the name directly corresponds to 'inforce scorecard,' making it the correct database to query for these policy changes. PLPDATA is a general policy database, while PLPINFSCA appears to be a distractor with similar but incorrect naming.