CTE Database Administration 3 — Questions and Answers
Question 1: A telecom carrier's OLTP database must be separated from its analytics workload. Which architecture pattern BEST addresses this?
- Increase CPU on the OLTP server to handle both workloads
- Implement a data warehouse with ETL pipelines feeding from the OLTP system (Correct answer)
- Run analytics queries during off-peak hours on the OLTP database
- Store analytics data in flat CSV files
Correct answer: Implement a data warehouse with ETL pipelines feeding from the OLTP system
A data warehouse with ETL pipelines isolates analytics queries from OLTP operations, preventing resource contention.
Question 2: Which normal form eliminates transitive dependencies in a telecom subscriber database schema?
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF) (Correct answer)
- Boyce-Codd Normal Form (BCNF)
Correct answer: Third Normal Form (3NF)
Third Normal Form (3NF) requires that non-key attributes depend only on the primary key, eliminating transitive dependencies.
Question 3: In a high-availability telecom database cluster, what is the purpose of a 'witness' or 'arbiter' node?
- To store backup copies of all data
- To serve read-only queries
- To provide a tiebreaker vote during failover to prevent split-brain scenarios (Correct answer)
- To run ETL jobs without impacting primary nodes
Correct answer: To provide a tiebreaker vote during failover to prevent split-brain scenarios
A witness/arbiter node provides an odd quorum vote to prevent split-brain, ensuring only one node assumes the primary role during failover.
Question 4: A DBA discovers that a long-running report query on the telecom billing database is blocking other transactions. Which action is MOST appropriate?
- Kill all active sessions on the database
- Move the report query to a read replica or reporting database (Correct answer)
- Increase the lock timeout for all sessions
- Disable transaction logging temporarily
Correct answer: Move the report query to a read replica or reporting database
Routing report queries to a read replica isolates analytics workloads from OLTP transactions, eliminating blocking.
Question 5: Which SQL command is used to reclaim storage and update statistics after a large batch delete of expired CDR records in a telecom database?
- TRUNCATE TABLE
- VACUUM or REORGANIZE INDEX (database-specific) (Correct answer)
- DROP TABLE
- ALTER TABLE
Correct answer: VACUUM or REORGANIZE INDEX (database-specific)
VACUUM (PostgreSQL) or REORGANIZE INDEX (SQL Server) reclaims fragmented space and updates statistics after large data deletions.
Question 6: In telecom database security, what is the principle of 'least privilege' as applied to database user accounts?
- All users should have DBA-level access for operational flexibility
- Database accounts should be granted only the permissions required to perform their specific function (Correct answer)
- User passwords should be changed monthly
- All accounts should be shared to reduce administration overhead
Correct answer: Database accounts should be granted only the permissions required to perform their specific function
Least privilege minimizes security risk by granting accounts only the exact permissions needed for their designated function, limiting breach impact.
Question 7: What is the purpose of a materialized view in a telecom reporting database?
- To enforce row-level security on sensitive subscriber data
- To store precomputed query results that can be refreshed periodically for fast reporting (Correct answer)
- To create a real-time mirror of the production database
- To define the physical storage layout of table partitions
Correct answer: To store precomputed query results that can be refreshed periodically for fast reporting
Materialized views persist precomputed results to disk, enabling fast reporting queries without recalculating complex aggregations each time.
CTE Database Administration 3
A telecom carrier's OLTP database must be separated from its analytics workload.
Which architecture pattern BEST addresses this?