IKM - International Knowledge Measurement Database Administration Concepts Questions and Answers

Question 1

A database administrator is tasked with creating a backup strategy for a critical financial database. The business has specified a Recovery Point Objective (RPO) of 15 minutes and a Recovery Time Objective (RTO) of 2 hours. To meet the RPO, which of the following backup methods is MOST crucial to implement frequently?

Accepted Answer

Transaction log backups

Answer

The Recovery Point Objective (RPO) of 15 minutes dictates the maximum acceptable amount of data loss. Transaction log backups capture all transactions and can be taken very frequently (e.g., every 15 minutes), allowing for point-in-time recovery and thus meeting the stringent RPO. Full and differential backups are performed less frequently and would not meet this requirement on their own.

Question 2

A database is experiencing slow query performance, specifically on queries involving `WHERE` clauses on non-primary key columns. Which of the following database objects should be created to MOST effectively improve the performance of these queries?

Accepted Answer

An index

Answer

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Creating an index on columns frequently used in `WHERE` clauses allows the database to find the matching rows quickly without performing a full table scan, which is a common cause of slow query performance.

Question 3

As part of a security audit, a Database Administrator is reviewing access control policies. Which of the following practices best adheres to the principle of least privilege?

Accepted Answer

Creating a specific role with only `SELECT` permissions on required views for an analytics team.

Answer

The principle of least privilege dictates that users and processes should be granted only the permissions necessary to perform their intended function. Creating a role with only `SELECT` permissions on specific views for the analytics team perfectly illustrates this, as it restricts their access to only the data they need to see and the actions they need to perform, minimizing potential security risks.

Question 4

A company's database contains a single 'Projects' table with columns for ProjectID, ProjectName, EmployeeID, EmployeeName, and EmployeeDepartment. This design leads to the employee's name and department being repeated for every project they are assigned to. This is an example of a violation of which database normalization form?

Accepted Answer

Second Normal Form (2NF)

Answer

This design violates Second Normal Form (2NF). 2NF is violated when a non-key attribute (like EmployeeName or EmployeeDepartment) is dependent on only a part of the composite primary key. In a potential 'Project_Assignments' table with a composite key of (ProjectID, EmployeeID), the employee details are dependent only on EmployeeID, not the full key. To fix this, you would create separate tables for Employees and Projects.

Question 5

Which of the ACID properties of a database transaction ensures that once a transaction has been committed, it will remain so, even in the event of a power loss or system crash?

Accepted Answer

Durability

Answer

The Durability property of ACID ensures that after a transaction is successfully committed, the changes are permanent and will survive any subsequent system failure, such as a power outage or crash. The changes are written to non-volatile storage.

IKM - International Knowledge Measurement Practice Test

IKM - International Knowledge Measurement Practice Test

IKM - International Knowledge Measurement Database Administration Concepts Questions and Answers