Database Administration Concepts Flashcards

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?

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.

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?

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.

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?

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.

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?

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.

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?

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.

Which of the following is a core responsibility of a Database Administrator (DBA) related to ensuring data availability?

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.