CIW CIW Database Design Specialist 2 — Questions and Answers
Question 1: Which normal form requires that every non-key attribute be fully functionally dependent on the entire primary key?
- First Normal Form (1NF)
- Second Normal Form (2NF) (Correct answer)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF)
Correct answer: Second Normal Form (2NF)
Second Normal Form (2NF) requires full functional dependency on the whole primary key, eliminating partial dependencies.
Question 2: What is an entity-relationship (ER) diagram used for in database design?
- Document SQL query execution plans
- Visually model data entities, attributes, and their relationships (Correct answer)
- Monitor database server performance
- Generate automatic backups
Correct answer: Visually model data entities, attributes, and their relationships
An ER diagram is a visual tool that represents the data model, showing entities, their attributes, and how they relate to each other.
Question 3: Which SQL command permanently removes all rows from a table but keeps the table structure?
- DROP TABLE
- DELETE
- TRUNCATE (Correct answer)
- REMOVE
Correct answer: TRUNCATE
The `TRUNCATE` command deletes all rows from a table quickly and cannot be rolled back in most databases, while preserving the table schema.
Question 4: What is the purpose of a database index?
- Encrypt sensitive columns
- Speed up data retrieval operations on a table (Correct answer)
- Enforce data validation rules
- Compress table storage
Correct answer: Speed up data retrieval operations on a table
A database index creates a separate data structure that allows the database engine to find rows much faster without scanning the entire table.
Question 5: In a one-to-many relationship, how is the relationship typically implemented in a relational database?
- By duplicating the table
- By placing a foreign key in the 'many' side table referencing the 'one' side (Correct answer)
- By merging both tables into one
- By using a junction table
Correct answer: By placing a foreign key in the 'many' side table referencing the 'one' side
A one-to-many relationship is implemented by adding a foreign key column in the child ('many') table that references the parent ('one') table's primary key.
Question 6: Which SQL aggregate function returns the number of rows that match a specified condition?
- SUM()
- AVG()
- COUNT() (Correct answer)
- TOTAL()
Correct answer: COUNT()
The `COUNT()` function returns the number of rows (or non-NULL values in a column) that satisfy the query conditions.
CIW CIW Database Design Specialist 2
Which normal form requires that every non-key attribute be fully functionally dependent on the entire primary key?