Full-Stack Development Database Design & Management 1 — Questions and Answers
Question 1: Which normal form eliminates partial dependencies on a composite 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)
2NF removes partial dependencies, ensuring every non-key attribute is fully dependent on the entire composite primary key.
Question 2: In a relational database, what does the ACID acronym stand for?
- Atomicity, Consistency, Isolation, Durability (Correct answer)
- Accuracy, Completeness, Integrity, Distribution
- Atomicity, Concurrency, Integrity, Durability
- Accuracy, Consistency, Isolation, Dependency
Correct answer: Atomicity, Consistency, Isolation, Durability
ACID stands for Atomicity, Consistency, Isolation, and Durability — the four properties that guarantee reliable database transactions.
Question 3: Which SQL JOIN type returns all rows from the left table even if there is no match in the right table?
- INNER JOIN
- RIGHT JOIN
- LEFT JOIN (Correct answer)
- FULL OUTER JOIN
Correct answer: LEFT JOIN
A LEFT JOIN returns all rows from the left table and matched rows from the right table, filling NULLs where no match exists.
Question 4: What is the purpose of a database index?
- To enforce referential integrity between tables
- To speed up data retrieval operations at the cost of additional storage (Correct answer)
- To automatically backup table data
- To encrypt sensitive columns
Correct answer: To speed up data retrieval operations at the cost of additional storage
Indexes create a data structure that enables the database engine to locate rows faster, trading storage space for query performance.
Question 5: Which NoSQL database type is best suited for storing data as key-value pairs with very fast read/write performance?
- Document store (e.g., MongoDB)
- Column-family store (e.g., Cassandra)
- Key-value store (e.g., Redis) (Correct answer)
- Graph database (e.g., Neo4j)
Correct answer: Key-value store (e.g., Redis)
Key-value stores like Redis are designed for ultra-fast access using a unique key to retrieve a value, making them ideal for caching and session storage.
Question 6: What SQL clause is used to filter results after a GROUP BY aggregation?
- WHERE
- FILTER
- HAVING (Correct answer)
- LIMIT
Correct answer: HAVING
HAVING filters groups after aggregation, while WHERE filters rows before grouping occurs.
Question 7: In database design, what is a foreign key?
- A key that uniquely identifies each row in a table
- A column that references the primary key of another table (Correct answer)
- An encrypted version of the primary key
- A composite key made from multiple columns
Correct answer: A column that references the primary key of another table
A foreign key is a column (or set of columns) in one table that references the primary key of another table, enforcing referential integrity.
Full-Stack Development Database Design & Management 1
Which normal form eliminates partial dependencies on a composite primary key?