Database Management Systems Flashcards

In a data warehouse context, what is an 'ETL process'?

Answer: Extract, Transform, Load

ETL stands for Extract, Transform, Load — the process of pulling data from source systems, transforming it into a consistent format, and loading it into a data warehouse.

Which SQL aggregate function returns the number of rows that match a specified condition?

Answer: COUNT()

The COUNT() function returns the total number of rows that match the specified criteria in a query.

What does 'data integrity' mean in a database system?

Answer: The accuracy, consistency, and reliability of data stored in the database

Data integrity ensures that data stored in a database is accurate, consistent, and reliable throughout its lifecycle, enforced through constraints and validation rules.

A 'stored procedure' in a database is best described as:

Answer: A precompiled set of SQL statements stored in the database that can be executed on demand

A stored procedure is a precompiled collection of SQL statements saved in the database that can be called and executed repeatedly, improving performance and code reuse.

In database indexing, a clustered index:

Answer: Sorts and stores the actual table data rows in the order of the indexed column

A clustered index determines the physical order of data storage in the table itself, meaning the table rows are stored sorted by the indexed column.

Which concurrency control technique assigns a unique timestamp to each transaction to determine the serialization order?

Answer: Timestamp ordering protocol

The timestamp ordering protocol assigns each transaction a unique timestamp and uses these timestamps to enforce a consistent serialization order, preventing conflicts.

Which of the following is an example of a DDL command in SQL?

Answer: CREATE TABLE departments (dept_id INT, dept_name VARCHAR(50))

CREATE TABLE is a Data Definition Language (DDL) command that defines the structure of a new database table, specifying column names and data types.