TOC Database Management & SQL 3 — Questions and Answers
Question 1: Which normal form eliminates transitive dependencies between 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)
3NF requires that no non-key attribute depends on another non-key attribute, removing transitive dependencies.
Question 2: A PRIMARY KEY constraint in a relational database ensures which two properties?
- Uniqueness and NOT NULL (Correct answer)
- Uniqueness and DEFAULT value
- NOT NULL and FOREIGN KEY reference
- Auto-increment and uniqueness
Correct answer: Uniqueness and NOT NULL
A PRIMARY KEY enforces both uniqueness (no duplicate values) and NOT NULL (every row must have a value).
Question 3: Which type of database relationship exists when one employee can hold many certifications, but each certification belongs to only one employee?
- Many-to-Many
- One-to-One
- One-to-Many (Correct answer)
- Self-referencing
Correct answer: One-to-Many
One-to-Many means one record on the parent side links to multiple records on the child side.
Question 4: What is the purpose of a FOREIGN KEY constraint?
- To create an index for faster lookups
- To enforce referential integrity between two tables (Correct answer)
- To prevent NULL values in a column
- To encrypt sensitive column data
Correct 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.
Question 5: In database design, what does 'atomicity' mean in the context of First Normal Form?
- Each table has exactly one primary key
- Each column contains only a single, indivisible value (Correct answer)
- All transactions must complete fully or not at all
- Each row is uniquely identified
Correct 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.
Question 6: Which schema design pattern stores frequently queried aggregate data in a separate table to improve read performance?
- Snowflake schema
- Star schema
- Data mart
- Materialized view (Correct answer)
Correct 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.
Question 7: A database storing employee roles has a many-to-many relationship between employees and projects. How is this typically resolved?
- By adding a FOREIGN KEY directly on the employees table
- By creating a junction (bridge) table with foreign keys to both tables (Correct answer)
- By using a UNION query at read time
- By denormalizing into a single table
Correct 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.
TOC Database Management & SQL 3
Which normal form eliminates transitive dependencies between non-key attributes?