Database Design and Transaction Management Review

Vocabulary flashcards covering key concepts from database design, storage, query processing, and transaction management, based on lecture notes from Spring 2025.

E-R Model

A high-level data model used in the conceptual design phase of database design, representing entities, relationships, and attributes.

Entity Set

Objects of the same type that share common properties.

Relationship Instance

An association between two or more entity instances.

Mapping Cardinality

Specifies how an entity set participates in a relationship set; can be total or partial.

Weak Entity Set

An entity set whose existence depends on another entity set (the owner).

Normalization

The process of organizing data in a database to reduce redundancy and improve data integrity, often involving functional dependencies.

Functional Dependency

A constraint between two attributes in a relation, where the value of one attribute determines the value of the other.

Candidate Key

A minimal (cannot identify tuple if removed) set of attributes that uniquely identifies a tuple in a relation.

Third Normal Form (3NF)

A normal form that aims to reduce redundancy and improve data integrity, based on functional dependencies.

For all a → B in F+, AT LEAST 1 of the following:

• a is a superkey

• a→B is trivial

• Each attribute A in B, a is contained in a candidate key for R

Boyce-Codd Normal Form (BCNF)

A normal form stricter than 3NF, addressing certain types of redundancy related to functional dependencies.

Loss-less-join (LJ) Decomposition

A decomposition of a relation schema where the natural join of the decomposed relations results in the original relation.

Functional Dependency Preserving Decomposition

A decomposition where the functional dependencies of the original relation can be inferred from the functional dependencies of the decomposed relations.

Armstrong's Axioms

A set of rules used to derive new functional dependencies from a given set of functional dependencies.

Closure (F+)

The set of all functional dependencies that can be inferred from a given set of functional dependencies.

Canonical Cover (Fc)

A simplified form of a set of functional dependencies, removing redundancy and ensuring minimal representation.

File Organization

Techniques and structures used to organize and store data on physical storage devices.

Indexing

A technique to speed up data access in databases by creating a separate file that maps search keys to data records.

Ordered Index

An index where the search key values are stored in sorted order, facilitating efficient searching and retrieval.

Hash Index

An index organized using a hash function to map search keys to bucket addresses, providing fast access to data records.

Primary Index

Ordered index: An index where the data file is sequentially ordered according to the search key.

Secondary Index

Ordered index: An index where the search key is not used to order the data file records.

Dense Index

An index where every search key value in the data file is represented in the index file.

Sparse Index

An index where only some search key values are represented in the index file.

B+-Tree Index

A tree-structured index that maintains balance, ensuring equal access time to all leaf nodes.

Query Optimization

The process of transforming a query into an equivalent but more efficient form for evaluation.

Query Cost Measures

Measures used to estimate the cost of query execution, such as disk I/O, CPU usage, and network communication.

Transaction

A series of operations that perform a logical unit of work on a database, ensuring data consistency and integrity.

ACID Properties

Atomicity, Consistency, Isolation, Durability - the four key properties that guarantee reliable transaction processing.

Schedule

the sequence of operations performed by multiple transactions in a database system.

Serial Schedule

A schedule in which transactions are executed one after another, without interleaving.

Concurrent Schedule

A schedule in which transactions' operations are interleaved, allowing for concurrent execution.

Conflict Serializable Schedule

A schedule that is equivalent to a serial schedule in terms of the order of conflicting operations.

Precedence Graph

A directed graph representing the dependencies between transactions in a schedule, used to detect cycles and determine serializability.

Concurrency Control

A technique used to manage concurrent access to database resources, preventing conflicts and ensuring data integrity.

Lock

A mechanism to control access to database resources, ensuring that only one transaction can modify a resource at a time.

Shared Lock

A lock that allows multiple transactions to read a resource concurrently but prevents any transaction from modifying it.

Exclusive Lock

A lock that grants a transaction exclusive access to a resource, preventing other transactions from reading or modifying it.

Locking Protocol

A set of rules that govern how transactions acquire and release locks, ensuring consistency and preventing concurrency conflicts.

Two-Phase Locking Protocol

A locking protocol that guarantees conflict serializability by dividing a transaction's execution into a growing phase and a shrinking phase.

Deadlock

A situation where two or more transactions are blocked indefinitely, waiting for each other to release locks.

Deadlock Prevention

A strategy for preventing deadlocks by imposing restrictions on how transactions can acquire locks.

Deadlock Detection

A strategy for detecting deadlocks by analyzing transaction dependencies and identifying cycles in the wait-for graph.

Wait-For Graph

A graph representing the dependencies between transactions waiting for each other to release locks, used in deadlock detection.