CIW Database Design Specialist Flashcards

Which normal form requires that every non-key attribute be fully functionally dependent on the entire primary key?

Answer: Second Normal Form (2NF)

Second Normal Form (2NF) requires full functional dependency on the whole primary key, eliminating partial dependencies.

What is an entity-relationship (ER) diagram used for in database design?

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.

Which SQL command permanently removes all rows from a table but keeps the table structure?

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.

What is the purpose of a database index?

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.

In a one-to-many relationship, how is the relationship typically implemented in a relational database?

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.

Which SQL aggregate function returns the number of rows that match a specified condition?

Answer: COUNT()

The `COUNT()` function returns the number of rows (or non-NULL values in a column) that satisfy the query conditions.