ICC Database Management 2 — Questions and Answers
Question 1: Which database normalization form eliminates transitive dependencies among non-key attributes?
- 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 2: In a permit tracking database, a foreign key constraint ensures which of the following?
- Unique values in the referenced column
- Referential integrity between related tables (Correct answer)
- Faster query execution on joined tables
- Automatic indexing of the child table
Correct answer: Referential integrity between related tables
A foreign key constraint enforces referential integrity by ensuring values in the child table exist in the referenced parent table.
Question 3: A building department wants to prevent duplicate permit numbers in its database. Which constraint should be applied?
- CHECK constraint
- DEFAULT constraint
- UNIQUE constraint (Correct answer)
- NOT NULL constraint
Correct answer: UNIQUE constraint
A UNIQUE constraint ensures no two rows in a column or combination of columns share the same value.
Question 4: What is a database transaction's ACID property that ensures partial updates are not committed on failure?
- Atomicity (Correct answer)
- Consistency
- Isolation
- Durability
Correct answer: Atomicity
Atomicity guarantees that a transaction is treated as a single unit — either all operations succeed or none are committed.
Question 5: An ICC code enforcement officer needs read-only access to inspection records. Which SQL command grants this permission?
- GRANT SELECT ON inspections TO officer_role (Correct answer)
- GRANT ALL ON inspections TO officer_role
- GRANT INSERT ON inspections TO officer_role
- GRANT EXECUTE ON inspections TO officer_role
Correct answer: GRANT SELECT ON inspections TO officer_role
GRANT SELECT provides read-only access, allowing the user to query data without modifying it.
Question 6: Which type of database index is most efficient for columns frequently used in range queries on inspection dates?
- Hash index
- Full-text index
- B-tree index (Correct answer)
- Bitmap index
Correct answer: B-tree index
B-tree indexes support range queries efficiently because they store data in a sorted, hierarchical structure.
Question 7: In a code compliance database, which isolation level prevents 'dirty reads' but allows non-repeatable reads?
- Read Uncommitted
- Read Committed (Correct answer)
- Repeatable Read
- Serializable
Correct answer: Read Committed
Read Committed prevents dirty reads by only returning committed data, but does not prevent non-repeatable reads within the same transaction.
ICC Database Management 2
Which database normalization form eliminates transitive dependencies among non-key attributes?