Database Management Systems Lecture Notes

A set of practice flashcards covering key concepts from the lecture on database management systems.

What is a database application?

Software that helps business users interact with database systems.

What is the role of a database administrator?

Responsible for securing the database system against unauthorized users, enforcing procedures for user access and database system availability.

What does authorization in a database context refer to?

Individual users are authorized to access specific data within the database, limited to specific tables, columns, or rows.

What is query optimization?

The process by which the query processor ensures the most efficient instructions are executed on the data.

What is the function of the storage manager?

Translates query processor instructions into low-level file-system commands that modify or retrieve data.

What does the transaction manager do?

Ensures transactions are executed properly, prevents conflicts between concurrent transactions, and restores the database to a consistent state after a failure.

What SQL statement is used to create a new table?

CREATE TABLE statement.

What is a tuple in a database context?

An ordered collection of elements enclosed in parentheses, representing a row of values.

What is data independence?

The principle that physical design never affects query results, allowing for improved query performance by changing data organization.

What does the ALTER TABLE statement do?

It adds, deletes, or modifies columns on an existing table.

What is a primary key?

A column or group of columns used to uniquely identify a row in a table.

How is a foreign key defined?

A column or group of columns that refers to a primary key in another table.

What is the purpose of the BETWEEN operator in SQL?

To determine if a value is between two other values.

What is the HAVING clause used for?

To filter group results after a GROUP BY clause in SQL.

What is an equijoin?

A join that compares columns of two tables using the = operator.

What is normalization?

The process of organizing data to reduce redundancy by decomposing tables.

What does a materialized view do?

Stores data for a view at all times, which must be refreshed when base tables change.

What is an artificial key?

A single-column primary key created when no suitable primary or composite key exists.

What is the purpose of an index in a database?

To quickly locate data and improve query performance.

What is the difference between a dense index and a sparse index?

A dense index contains an entry for every table row, while a sparse index contains an entry for every table block.