Database Management & SQL Flashcards

Which normal form eliminates transitive dependencies between non-key attributes?

Answer: Third Normal Form (3NF)

3NF requires that no non-key attribute depends on another non-key attribute, removing transitive dependencies.

A PRIMARY KEY constraint in a relational database ensures which two properties?

Answer: Uniqueness and NOT NULL

A PRIMARY KEY enforces both uniqueness (no duplicate values) and NOT NULL (every row must have a value).

Which type of database relationship exists when one employee can hold many certifications, but each certification belongs to only one employee?

Answer: One-to-Many

One-to-Many means one record on the parent side links to multiple records on the child side.

What is the purpose of a FOREIGN KEY constraint?

Answer: To enforce referential integrity between two tables

A FOREIGN KEY ensures that a value in one table corresponds to an existing value in a referenced table, maintaining referential integrity.

In database design, what does 'atomicity' mean in the context of First Normal Form?

Answer: Each column contains only a single, indivisible value

1NF atomicity means each column cell holds a single, indivisible value — no repeating groups or arrays.

Which schema design pattern stores frequently queried aggregate data in a separate table to improve read performance?

Answer: Materialized view

A materialized view stores the result of a precomputed query physically, speeding up complex reads without re-executing the query each time.

A database storing employee roles has a many-to-many relationship between employees and projects. How is this typically resolved?

Answer: By creating a junction (bridge) table with foreign keys to both tables

A junction table holds foreign keys referencing both related tables, decomposing a many-to-many relationship into two one-to-many relationships.