IKM Database Administration Concepts 2 — Questions and Answers
Question 1: What is the purpose of database normalization?
- Increase query speed
- Eliminate data redundancy and improve integrity (Correct answer)
- Compress storage
- Create backups
Correct answer: Eliminate data redundancy and improve integrity
Normalization organizes data to reduce redundancy and prevent update anomalies.
Normal forms: 1NF (atomic values), 2NF (no partial dependencies), 3NF (no transitive dependencies), BCNF (every determinant is a candidate key).
Question 2: Which index type organizes data in a sorted tree for efficient range queries?
- Hash index
- B-tree index (Correct answer)
- Bitmap index
- Full-text index
Correct answer: B-tree index
B-tree indexes store data in a balanced tree maintaining sorted order for efficient searches.
B-tree indexes support searches, insertions, and deletions in O(log n) time. B+ tree variants store data only in leaf nodes which are linked for range scans.
Question 3: What does ACID stand for in database transactions?
- Automated, Consistent, Independent, Durable
- Atomicity, Consistency, Isolation, Durability (Correct answer)
- Access, Control, Integrity, Distribution
- Availability, Concurrency, Isolation, Delivery
Correct answer: Atomicity, Consistency, Isolation, Durability
ACID ensures reliable transactions: Atomicity, Consistency, Isolation, Durability.
Atomicity: all or nothing. Consistency: valid state transitions. Isolation: concurrent independence (via MVCC or locking). Durability: committed data survives failures (via WAL).
Question 4: What is a stored procedure?
- Temporary table
- Precompiled set of SQL statements stored in the database (Correct answer)
- Backup schedule
- Access control list
Correct answer: Precompiled set of SQL statements stored in the database
A stored procedure is a named collection of SQL statements that can be executed on demand.
Stored procedures accept parameters, contain control flow logic, and return results. Benefits include reduced network traffic, precompiled plans, and consistent business logic.
Question 5: What is the difference between clustered and non-clustered indexes?
- Clustered are faster for all queries
- Clustered determines physical row order; non-clustered creates a separate structure (Correct answer)
- Non-clustered cannot be on primary keys
- Clustered use hash storage
Correct answer: Clustered determines physical row order; non-clustered creates a separate structure
A clustered index sorts actual data rows; a non-clustered index creates a separate lookup structure.
A table can have only one clustered index. Non-clustered index leaf nodes contain pointers back to data. Covering indexes include all needed columns to avoid bookmark lookups.
Question 6: What is database sharding?
- Creating read replicas
- Horizontally partitioning data across multiple servers (Correct answer)
- Encrypting data at rest
- Archiving old data
Correct answer: Horizontally partitioning data across multiple servers
Sharding distributes rows across multiple instances based on a shard key.
The shard key determines which shard stores each row. Benefits include linear scalability. Challenges include cross-shard queries and data rebalancing.
IKM Database Administration Concepts 2
What is the purpose of database normalization?