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

You have been assigned the task of designing a relational database for a client. The database will be used to store an exact copy of specific XML documents that the client needs to retrieve on a regular basis. The client wants to be able to use an application that they created to enter the documents into the database as well as retrieve them. You are in the process of designing a table for the database. What should you do?

  1. Convert the XML documents to relational data and then store them within the database

  2. Create a table that uses XML as the data type for the column that will store the XML documents.

  3. Nothing. You can not store XML documents within a SQL Server 2005 database

  4. Create a table that uses nvarchar(max) as the data type for the column that will store the XML documents
Reveal answer Fill a bubble to check yourself
D Correct answer
Multiple choice technology databases

You are gathering performance and optimization data for your SQL Server 2005 database. Your company has three custom applications that routinely make use of the database. Each application accesses the database using separate login credentials. You wish to gather information concerning the queries executed by each custom application. To gather this data you decide to create a trace using the SQL Server Profiler. You decide to use one of the predefined SQL Server Profiler templates to define what data should be gathered. Which predefined SQL Server Profiler template would best suit your requirements?

  1. TSQL_Duration

  2. TSQL_Grouped

  3. TSQL_Replay

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

The TSQL_Grouped template in SQL Server Profiler groups trace information by the application name and login credentials, making it ideal for analyzing queries executed by specific applications.

Multiple choice technology programming languages

ORA-01403 Means?

  1. No data found

  2. Internal error

  3. TNS:could not resolve service name"

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

ORA-01403 is the Oracle error code returned when a SELECT...INTO statement or implicit cursor finds no rows to fetch. It's the standard 'no data found' exception in PL/SQL, commonly handled with NO_DATA_FOUND exception handlers. Other options represent different errors: ORA-00600 is internal error, ORA-12154 is TNS naming failure.

Multiple choice technology programming languages

ORA-12154 Means?

  1. TNS:name lookup failure"

  2. TNS:could not resolve service name"

  3. TNS:protocol adapter error"

  4. Package error raised with DBMS_SYS_ERROR.RAISE_SYSTEM_ERROR
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

ORA-12154 is 'TNS:could not resolve the service name' - Oracle's TNS listener cannot find the database service name specified in the connect string. This is a TNS (Transparent Network Substrate) naming/configuration error. Option A would be ORA-12154 (name lookup failure is essentially the same), Option C is ORA-12560 (protocol adapter error). The key issue is service name resolution in tnsnames.ora.

Multiple choice technology packaged enterprise solutions

What Session.flush() do ?

  1. the state of that object will be synchronized with the database.

  2. the state of that object will be synchronized with the SessionFactory.

  3. remove state of that object from Session.

  4. remove state of that object from SessionFactory.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Session.flush() synchronizes the in-memory state of persistent objects with the database. It executes SQL statements (INSERT, UPDATE, DELETE) to make the database reflect the changes made to objects in the Hibernate Session, but doesn't commit the transaction - use commit() for that.

Multiple choice technology packaged enterprise solutions

If instances are not in the session or second-level cache which will give better performance?

  1. Query.list()

  2. Query.iterate()

  3. Both are same

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

When instances are not cached, Query.iterate() can cause the N+1 select problem - it issues one query for the initial list and then additional queries for each uninitialized object proxy. Query.list() loads all data in a single query, making it more efficient for uncached data.

Multiple choice technology performance

This phase of Oracle tuning is directed at looking for resource shortages in the db_block_buffers, shared_pool_size and sort_area_size.

  1. Instance Tuning

  2. Object Tuning

  3. SQL Tuning

  4. Space Management
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Instance Tuning focuses on optimizing memory parameters like db_block_buffers (data cache), shared_pool_size (library cache), and sort_area_size (memory for sorting operations). These are instance-level resource configurations that affect the entire database instance, not individual objects or SQL statements.

Multiple choice technology performance

This parameter lets you tune optimizer behavior for access path selection to be more or less index friendly, that is, to make the optimizer more or less prone to selecting an index access path over a full table scan. The default makes full table scan and index use equal.

  1. Optimizer Index Caching

  2. Optimizer Index Cost Adjustments

  3. Optimizer Mode

  4. Parallel Automatic Tuning
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Optimizer Index Cost Adjustments (OPTIMIZER_INDEX_COST_ADJ) parameter adjusts the cost of index access paths relative to full table scans. A value less than 100 makes indexes cheaper, encouraging index use; greater than 100 makes full table scans more attractive. The default of 100 treats them equally.

Multiple choice technology performance

This tool in Oracle10g defaults to a collection interval every 30 minutes and collects data that is the foundation for all of the other self-tuning features. It is very much like STATSPACK, especially the level-5 STATSPACK collection mechanism where top SQL is collected every hour, based on your rolling thresholds for high-use SQL.

  1. Automatic Maintenance Tasks(AMT)

  2. Automatic Workload Repository(AWR)

  3. Automatic Database Diagnostic Monitor(ADDM)

  4. Automatic Storage Management(ASM)
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The Automatic Workload Repository (AWR) is the built-in repository in Oracle 10g and later that collects performance statistics at regular intervals (default 30 or 60 mins), replacing and expanding upon the functionality of STATSPACK.

Multiple choice technology web technology

As a preventive measure what needs to be done while accessing the Database?

  1. Use of dynamic Query with System Privilege Account

  2. Use of Prepared Statements

  3. Use Least Privilege Account

  4. Both 1 and 3

  5. Both 2 and 3
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

To prevent SQL injection and unauthorized access, best practices dictate using Prepared Statements (to parameterize queries) and using a Least Privilege Account (to limit potential damage).

Multiple choice technology architecture

Manages connection with data source to get and store data

  1. View Helper

  2. DAO

  3. Service to Worker

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

Data Access Object (DAO) pattern abstracts and encapsulates access to a data source. It manages connections and provides CRUD operations while hiding the details of data persistence from the rest of the application. This separates business logic from data access logic.

Multiple choice technology architecture

Used to model, represent, and manage a set of interrelated persistent objects rather than representing them as individual fine-grained beans.

  1. Composite Entity

  2. Service to Worker

  3. Domain store

  4. Value list handler
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Composite Entity is a J2EE pattern used to model, represent, and manage interrelated persistent objects as a coarse-grained entity rather than fine-grained beans. This reduces the number of actual calls and improves performance.

Multiple choice technology security

As a preventive measure what needs to be done while accessing the Database?

  1. Use of dynamic Query with System Privilege Account

  2. Use of Prepared Statements

  3. Use Least Privilege Account

  4. Both 1 and 3

  5. Both 2 and 3
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Using prepared statements prevents SQL injection attacks by parameterizing queries. Using least privilege accounts limits potential damage from attacks by minimizing database permissions. Both are essential security practices.