AMCAT Computer Science: DBMS Concepts 2 — Questions and Answers

Question 1: Which normal form eliminates transitive dependencies?

1NF
2NF
3NF (Correct answer)
BCNF

Correct answer: 3NF

Third Normal Form (3NF) eliminates transitive dependencies where a non-key attribute depends on another non-key attribute.

In 3NF, for every functional dependency X → Y, either X is a superkey or Y is a prime attribute. This eliminates transitive dependencies where a non-key column depends on another non-key column.

Question 2: What does ACID stand for in database transactions?

Atomicity, Consistency, Isolation, Durability (Correct answer)
Accuracy, Consistency, Integrity, Durability
Atomicity, Concurrency, Isolation, Durability
Atomicity, Consistency, Integrity, Distribution

Correct answer: Atomicity, Consistency, Isolation, Durability

ACID = Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent transactions behave serially), Durability (committed data persists).

ACID properties ensure reliable transaction processing: Atomicity (transaction is indivisible), Consistency (data stays valid), Isolation (transactions don't interfere), Durability (committed transactions persist even after failure).

Question 3: Which SQL clause is used to filter groups after a GROUP BY?

WHERE
HAVING (Correct answer)
FILTER
GROUP FILTER

Correct answer: HAVING

HAVING filters groups formed by GROUP BY, whereas WHERE filters individual rows before grouping.

After GROUP BY creates groups, HAVING applies a condition to those groups. Example: SELECT dept, COUNT(*) FROM employees GROUP BY dept HAVING COUNT(*) > 5.

Question 4: Which join returns all rows from both tables, with NULLs where there is no match?

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN (Correct answer)

Correct answer: FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables, filling NULLs where there is no matching row in the other table.

FULL OUTER JOIN = LEFT JOIN union RIGHT JOIN. Rows without a match in the other table have NULLs for that table's columns.

Question 5: What is the purpose of a foreign key?

To uniquely identify rows
To enforce referential integrity between tables (Correct answer)
To speed up queries
To encrypt data

Correct answer: To enforce referential integrity between tables

A foreign key enforces referential integrity so that values reference existing rows in the related table.

A foreign key ensures that a value in a column must match an existing value in the referenced table's primary key. This prevents orphan records and maintains relational integrity.

Question 6: Which of the following is NOT a type of database index?

B-Tree index
Hash index
Clustered index
Loop index (Correct answer)

Correct answer: Loop index

B-Tree, Hash, and Clustered are all real index types. 'Loop index' is not a database index type.

Database indexes include B-Tree (most common), Hash (for equality lookups), Clustered (data physically sorted), Non-clustered, and Bitmap. 'Loop index' does not exist as an index type.

AMCAT Computer Science: DBMS Concepts 2

Which normal form eliminates transitive dependencies?