IKM - International Knowledge Measurement Database Administration Concepts Questions and Answers 1 — 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?
- Full weekly backups
- Differential daily backups
- Transaction log backups (Correct answer)
- Offline cold backups
Correct answer: Transaction log backups
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?
- A view
- A stored procedure
- A trigger
- An index (Correct answer)
Correct answer: An index
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?
- Assigning all developers the DBA role in the development environment.
- Granting a reporting user account `SELECT`, `INSERT`, and `DELETE` permissions on all tables.
- Creating a specific role with only `SELECT` permissions on required views for an analytics team. (Correct answer)
- Using a single shared user account for all application services to connect to the database.
Correct answer: Creating a specific role with only `SELECT` permissions on required views for an analytics team.
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?
- First Normal Form (1NF)
- Second Normal Form (2NF) (Correct answer)
- Third Normal Form (3NF)
- Fourth Normal Form (4NF)
Correct answer: Second Normal Form (2NF)
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?
- Atomicity
- Consistency
- Isolation
- Durability (Correct answer)
Correct answer: Durability
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.
Question 6: Which of the following is a core responsibility of a Database Administrator (DBA) related to ensuring data availability?
- Writing application-layer business logic.
- Designing the user interface for client applications.
- Developing and regularly testing a backup and recovery strategy. (Correct answer)
- Performing end-user acceptance testing.
Correct answer: Developing and regularly testing a backup and recovery strategy.
A principal responsibility of a DBA is to make data available to users and to protect it from loss. This involves designing, implementing, and, most importantly, regularly testing a backup and recovery plan to ensure data can be restored effectively in case of a failure.
IKM - International Knowledge Measurement Database Administration Concepts Questions and Answers 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?