ICC Database Management 3 — Questions and Answers
Question 1: Which backup strategy captures only the data changed since the last full backup, minimizing backup time?
- Full backup
- Differential backup
- Incremental backup (Correct answer)
- Mirror backup
Correct answer: Incremental backup
An incremental backup captures only changes made since the last backup of any type, making it faster and smaller than a differential backup.
Question 2: A permit database administrator wants to audit all changes to the inspections table. Which database feature should be implemented?
- Stored procedures
- Database triggers (Correct answer)
- Materialized views
- Clustered indexes
Correct answer: Database triggers
Database triggers automatically execute defined actions in response to INSERT, UPDATE, or DELETE events, enabling comprehensive audit logging.
Question 3: What does a database deadlock occur when two or more transactions are each waiting for the other to release?
- Index
- Lock (Correct answer)
- View
- Cursor
Correct answer: Lock
A deadlock occurs when transactions hold locks on resources the other needs, creating a circular wait that prevents both from proceeding.
Question 4: In a building permit system, which query optimization technique stores the results of a frequently executed subquery?
- Common Table Expression (CTE) (Correct answer)
- Clustered index
- Database partitioning
- Row-level locking
Correct answer: Common Table Expression (CTE)
A CTE defines a named temporary result set that can be referenced multiple times within the same query, improving readability and sometimes performance.
Question 5: Which database object provides a virtual table based on a stored SELECT query, simplifying complex joins for code enforcement reports?
- Stored procedure
- Trigger
- View (Correct answer)
- Sequence
Correct answer: View
A view is a stored query that presents data from one or more tables as if it were a single virtual table, simplifying access to complex data.
Question 6: Which data integrity type is violated when a permit record references a contractor ID that does not exist in the contractors table?
- Entity integrity
- Domain integrity
- Referential integrity (Correct answer)
- User-defined integrity
Correct answer: Referential integrity
Referential integrity is violated when a foreign key value in one table has no matching primary key in the referenced table.
Question 7: An inspection database uses database partitioning by year. Which benefit does horizontal partitioning primarily provide?
- Stronger encryption of old records
- Faster queries on large tables by limiting scanned data (Correct answer)
- Automatic normalization of data
- Elimination of duplicate rows
Correct answer: Faster queries on large tables by limiting scanned data
Horizontal partitioning divides table rows into separate physical segments, allowing queries filtered by partition key to scan only relevant partitions.
ICC Database Management 3
Which backup strategy captures only the data changed since the last full backup, minimizing backup time?