CIW Database Design Specialist Flashcards

What does ACID stand for in the context of database transactions?

Answer: Atomicity, Consistency, Isolation, Durability

ACID stands for Atomicity, Consistency, Isolation, and Durability — the four properties that guarantee reliable database transactions.

Which SQL clause is used to filter groups after a GROUP BY operation?

Answer: HAVING

The `HAVING` clause filters the result of GROUP BY aggregations, similar to how WHERE filters individual rows.

What type of relationship exists when a row in Table A can relate to many rows in Table B, and a row in Table B can also relate to many rows in Table A?

Answer: Many-to-many

A many-to-many relationship requires a junction (bridge) table containing foreign keys to both related tables.

Which SQL constraint ensures that a column cannot store NULL values?

Answer: NOT NULL

The `NOT NULL` constraint prevents a column from accepting NULL values, requiring every row to have a valid value in that column.

What is a stored procedure in a relational database?

Answer: A precompiled set of SQL statements stored in the database that can be executed by name

A stored procedure is a precompiled collection of SQL statements saved in the database and executed as a single callable unit.

In database design, what is denormalization?

Answer: Intentionally introducing redundancy to improve read performance

Denormalization deliberately adds redundant data to reduce complex joins, trading write efficiency for faster read queries.