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 web technology

If you want to let a member of your technical support staff back up a database without having to grant any other permission to a database or SQL Server instance, to which role should you add the staff member?

  1. sysadmin

  2. db_owner

  3. db_accessadmin

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

The db_backupoperator fixed database role is specifically designed to allow backing up databases without granting other permissions. Sysadmin has too many privileges, db_owner allows full control, and db_accessadmin manages access permissions.

Multiple choice technology web technology

You perform a full backup of the Adventure Works database that completes at midnight. Differential backups are scheduled to run every four hours beginning at 04:00. Transaction log backups are scheduled to run every five minutes. What information does the transaction log backup created at 09:15 contain?

  1. All transactions that have been issued since 09:10

  2. Transactions that have been committed since 09:10

  3. Pages that have changed since 09:10

  4. Extents that have changed since 09:10
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Transaction log backups contain only committed transactions since the last log backup. Uncommitted transactions are not captured because they may roll back. Option A is wrong (not ALL transactions), C and D describe differential backup behavior, not log backups.

Multiple choice technology web technology

One of your users has discovered some malicious updates to your data that occurred the day before at about 2 P.M. how can you return the database to the state it was in just before the update occurred?

  1. Perform a full database restoration with point-in-time recovery

  2. Perform a differential database restoration with point-in-time recovery

  3. Perform a transaction log restoration with point-in-time recovery

  4. You cannot return the database to the previous state
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To recover to a specific point in time, you restore the full backup, then the latest differential, then transaction logs up to (but not including) the malicious update time. Transaction log restoration with point-in-time recovery is the correct approach.

Multiple choice technology web technology

You want to access data that is located on two SQL Server 2005 computers. One Server is SQL 1 and the other server is SQL 2. You have permissions to write a stored Procedure on SQL 1 but on SQL 2 you have only permission to select data. You write a stored procedure on SQL 1. The stored procedure accesses SQL 2 by OPENQUERY T-SQL Statement. The query fails. What should you do to solve the problem?

  1. Add SQL 2 as linked server to SQL 1

  2. Add SQL 2 as remote Server to SQL 1

  3. (c) Reference SQL 2 by an alias

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

The OPENQUERY function in SQL Server requires a linked server to be configured first. It cannot directly access remote servers without this link. The claimed answer A correctly states 'Add SQL 2 as linked server to SQL 1' is the solution. Option B suggests 'remote server' which is an older SQL Server concept not used with OPENQUERY. Option C suggests using an alias which won't solve the authentication/connectivity issue. Option D is incorrect since adding the linked server is the correct solution.

Multiple choice technology web technology

What is bcp utility in SQL Server 2005?

  1. Copies data between an instance of Microsoft SQL Server and a data file in a user-specified format

  2. Configures and executes a SQL Server Integration Services package

  3. Transact-SQL statements, system procedures, and script files at the command prompt are possible with this utility

  4. Runs scripts designed for managing Reporting Services report servers
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

BCP (Bulk Copy Program) is a command-line utility that copies data between SQL Server and data files in user-specified formats. It's designed for high-speed bulk data transfer and supports various file formats like text, CSV, and native format. Options B, C, and D describe other SQL Server utilities (dtexec for SSIS packages, sqlcmd for command-line SQL execution, and rsconfig for Reporting Services).

Multiple choice technology web technology

You use the bcp utility to import data during nonworking hours. After importing data into the database, you execute a full backup statement. You need to ensure that the impact on the database during the insert is reduced to a minimum. Which of the following options can help you achieve that?

  1. Set the recovery model to Full

  2. Set the recovery model to Simple

  3. Set the recovery model to Bulk-Logged

  4. Back up the transaction log while performing the inserts
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The Bulk-Logged recovery model minimizes transaction log overhead during bulk operations like bcp imports by logging only the extent allocations rather than each row change. This reduces the performance impact while still allowing point-in-time recovery (unlike Simple recovery, which doesn't support transaction log backups). Full recovery would log every row individually, causing maximum overhead.

Multiple choice technology databases

Is DB2 database managament system?

  1. True

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

DB2 is IBM's family of database management systems. It's a commercial RDBMS that supports SQL, manages structured data, and is widely used in enterprise environments for transaction processing and data warehousing.

Multiple choice technology programming languages

What does the term 'locking' refer to?

  1. Locking is the process of database authentication.

  2. Locking is a process preventing users from reading data being changed by other users, and prevents concurrent users from changing the same data at the same time.

  3. Locking is a process, which logs database usage.

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

Locking prevents concurrent access problems by ensuring that when one user is modifying data, other users cannot read or change that same data simultaneously. This prevents conflicts and ensures data integrity. Option A is incorrect because locking is unrelated to authentication.

Multiple choice technology databases

which of the following are the advantages of DBMS? (Select 2)

  1. Redundancy is controlled

  2. graphical and statistical capabilities

  3. proprietary formats may limit arichival quality of data

  4. Providing multiple user interfaces
Reveal answer Fill a bubble to check yourself
A,D Correct answer
Explanation

DBMS advantages include controlling redundancy through normalized data design and providing multiple interfaces (CLI, GUI, API) for different users. Graphical capabilities are application features, not DBMS features. Proprietary formats limiting archival is a disadvantage.