Fundamental Database Concepts Flashcards

What is the purpose of the ROLLBACK command in SQL?

Answer: To undo all changes made since the last COMMIT

ROLLBACK undoes all data modifications made during the current transaction, restoring the database to its state at the last COMMIT or SAVEPOINT.

In First Normal Form (1NF), what requirement must be met?

Answer: Each column must contain atomic (indivisible) values with no repeating groups

1NF requires that each table cell contain a single, indivisible value and that there are no repeating groups or arrays within a row.

Which SQL aggregate function counts only non-NULL values in a column?

Answer: COUNT(column_name)

COUNT(column_name) counts only non-NULL values in the specified column, while COUNT(*) counts all rows including those with NULLs.

What is a database index, and what is its primary trade-off?

Answer: A data structure that speeds up reads; trade-off is slower writes and extra storage

An index speeds up SELECT queries by providing fast data lookup paths, but it slows down INSERT, UPDATE, and DELETE operations because the index must also be maintained.

What does 'referential integrity' mean in a relational database?

Answer: Foreign key values must match existing primary key values in the referenced table or be NULL

Referential integrity ensures that a foreign key value in a child table must either match an existing primary key value in the parent table or be NULL.

Which SQL set operator returns only the rows that appear in both result sets?

Answer: INTERSECT

INTERSECT returns only the distinct rows that exist in both the first and second query result sets.

What is the key difference between a database and a database management system (DBMS)?

Answer: A database is an organized collection of data; a DBMS is the software that manages and provides access to that data

A database is the structured collection of data, while a DBMS (like Oracle) is the software system that stores, retrieves, and manages that data.