CDP Database Administration and Security 1 — Questions and Answers
Question 1: What does ACID stand for in the context of database transactions?
- Automated, Consistent, Isolated, Durable
- Atomicity, Consistency, Isolation, Durability (Correct answer)
- Accurate, Concurrent, Integrated, Distributed
- Asynchronous, Cached, Indexed, Distributed
Correct answer: Atomicity, Consistency, Isolation, Durability
ACID (Atomicity, Consistency, Isolation, Durability) are the four properties that guarantee reliable database transactions even in the event of errors or system failures.
Question 2: What is the purpose of database indexing?
- To encrypt data stored in tables
- To speed up data retrieval by creating a separate data structure that allows faster lookups (Correct answer)
- To enforce referential integrity between tables
- To compress data to reduce storage space
Correct answer: To speed up data retrieval by creating a separate data structure that allows faster lookups
Database indexes create a separate, sorted data structure (like a book's index) that allows the database engine to find rows quickly without scanning the entire table.
Question 3: What is 'role-based access control' (RBAC) in database security?
- Granting permissions to individual users directly
- Assigning permissions to roles and then assigning users to appropriate roles to manage access efficiently (Correct answer)
- Restricting access based on the time of day
- Encrypting data based on a user's department
Correct answer: Assigning permissions to roles and then assigning users to appropriate roles to manage access efficiently
RBAC assigns permissions to roles (e.g., read-only analyst, data editor) and then assigns users to those roles, simplifying access management at scale.
Question 4: What is 'data masking' used for in database environments?
- Hiding database tables from query optimizers
- Replacing sensitive data with realistic but fictitious values to protect privacy in non-production environments (Correct answer)
- Encrypting data during network transmission
- Compressing data to save storage space
Correct answer: Replacing sensitive data with realistic but fictitious values to protect privacy in non-production environments
Data masking replaces sensitive production data (like SSNs or credit card numbers) with realistic but fake values for use in development, testing, and analytics environments.
Question 5: What is the purpose of a database transaction log?
- To store user login history
- To record all changes made to the database, enabling recovery and replication (Correct answer)
- To track query performance metrics
- To store backup copies of database tables
Correct answer: To record all changes made to the database, enabling recovery and replication
The transaction log records every modification to the database, enabling point-in-time recovery, undo/redo operations, and replication to secondary servers.
Question 6: What does 'database replication' achieve?
- Creates duplicate copies of database schemas in documentation
- Synchronizes data from a primary database to one or more secondary databases for availability and read scaling (Correct answer)
- Compresses database tables to reduce storage
- Validates database backup integrity
Correct answer: Synchronizes data from a primary database to one or more secondary databases for availability and read scaling
Database replication copies and maintains data from a primary server to one or more replicas, providing high availability, disaster recovery, and read scalability.
CDP Database Administration and Security 1
What does ACID stand for in the context of database transactions?