Database Terms
Key terminology for databases, SQL, data modeling, and data management.
A set of properties (Atomicity, Consistency, Isolation, Durability) that guarantee reliable database transactions.
Aggregate Function
Section titled “Aggregate Function”A SQL function that performs a calculation on a set of values and returns a single value (e.g., COUNT, SUM, AVG, MAX, MIN).
Atomicity
Section titled “Atomicity”A property ensuring that a transaction is treated as a single unit, which either succeeds completely or fails completely.
B-Tree
Section titled “B-Tree”A self-balancing tree data structure used in databases for maintaining sorted data and allowing efficient insertion, deletion, and search operations.
Backup
Section titled “Backup”A copy of database data taken to protect against data loss and enable recovery in case of failure.
An alternative to ACID (Basically Available, Soft state, Eventually consistent) used in NoSQL databases prioritizing availability over consistency.
Candidate Key
Section titled “Candidate Key”A minimal set of attributes that can uniquely identify a record in a table.
Cardinality
Section titled “Cardinality”The number of unique values in a column or the number of rows in a table; also refers to the relationship between tables (one-to-one, one-to-many, many-to-many).
Cascade
Section titled “Cascade”An action that automatically propagates changes (update or delete) from a parent table to related child tables through foreign key relationships.
Clustered Index
Section titled “Clustered Index”An index that determines the physical order of data in a table; a table can have only one clustered index.
Column
Section titled “Column”A vertical entity in a table that contains all information associated with a specific field.
Composite Key
Section titled “Composite Key”A primary key composed of two or more columns used to uniquely identify a record.
Concurrency
Section titled “Concurrency”The ability of a database to allow multiple users to access and modify data simultaneously while maintaining data integrity.
Consistency
Section titled “Consistency”A property ensuring that a transaction brings the database from one valid state to another, maintaining all defined rules and constraints.
Constraint
Section titled “Constraint”A rule enforced on data columns to ensure data integrity (e.g., PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK).
Cursor
Section titled “Cursor”A database object used to retrieve, manipulate, and navigate through a result set one row at a time.
Data Definition Language (DDL)
Section titled “Data Definition Language (DDL)”SQL statements used to define and modify database structure (CREATE, ALTER, DROP, TRUNCATE).
Data Manipulation Language (DML)
Section titled “Data Manipulation Language (DML)”SQL statements used to manipulate data in tables (SELECT, INSERT, UPDATE, DELETE).
Data Warehouse
Section titled “Data Warehouse”A centralized repository designed for query and analysis rather than transaction processing.
Database
Section titled “Database”An organized collection of structured data stored electronically in a computer system.
Database Management System (DBMS)
Section titled “Database Management System (DBMS)”Software that manages the creation, maintenance, and use of databases.
Deadlock
Section titled “Deadlock”A situation where two or more transactions are waiting for each other to release locks, resulting in a standstill.
Denormalization
Section titled “Denormalization”The process of adding redundant data to optimize read performance, sacrificing some write performance and storage efficiency.
Durability
Section titled “Durability”A property ensuring that once a transaction is committed, the changes persist even in the case of system failure.
Entity
Section titled “Entity”A thing or object in the real world that is distinguishable from other objects, represented as a table in a database.
Entity-Relationship (ER) Diagram
Section titled “Entity-Relationship (ER) Diagram”A visual representation of entities and their relationships in a database design.
ETL (Extract, Transform, Load)
Section titled “ETL (Extract, Transform, Load)”A process that extracts data from sources, transforms it to fit operational needs, and loads it into a target database.
Foreign Key
Section titled “Foreign Key”A column or set of columns that references the primary key of another table, establishing a relationship between tables.
Full-Text Search
Section titled “Full-Text Search”A technique for searching text-based data by matching keywords and phrases rather than exact values.
Graph Database
Section titled “Graph Database”A NoSQL database using graph structures with nodes, edges, and properties to represent and store data.
A database object that improves the speed of data retrieval operations by providing quick access to rows in a table.
Isolation
Section titled “Isolation”A property ensuring that concurrent transactions do not interfere with each other, appearing to execute sequentially.
Isolation Level
Section titled “Isolation Level”The degree to which transactions are isolated from each other (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE).
A SQL operation that combines rows from two or more tables based on a related column.
Junction Table
Section titled “Junction Table”A table used to implement many-to-many relationships between two other tables.
Key-Value Store
Section titled “Key-Value Store”A NoSQL database that stores data as a collection of key-value pairs, optimized for simple lookups.
Migration
Section titled “Migration”The process of moving data, schema, or entire databases from one environment to another.
Multiversion Concurrency Control (MVCC)
Section titled “Multiversion Concurrency Control (MVCC)”A concurrency control method that allows multiple versions of data to exist simultaneously, enabling high concurrency.
Non-Clustered Index
Section titled “Non-Clustered Index”An index that creates a separate structure from the table data, containing pointers to the actual data rows.
Normalization
Section titled “Normalization”The process of organizing data to minimize redundancy and dependency by dividing large tables into smaller ones and defining relationships.
A category of databases that don’t follow the traditional relational model, designed for specific use cases like document storage, key-value pairs, or graphs.
A special marker indicating the absence of a value in a database field.
OLAP (Online Analytical Processing)
Section titled “OLAP (Online Analytical Processing)”A category of software tools for analyzing multidimensional data for business intelligence and decision-making.
OLTP (Online Transaction Processing)
Section titled “OLTP (Online Transaction Processing)”A category of data processing focused on managing transaction-oriented applications, typically involving many short online transactions.
ORM (Object-Relational Mapping)
Section titled “ORM (Object-Relational Mapping)”A programming technique that converts data between incompatible type systems using object-oriented programming languages.
Partition
Section titled “Partition”The division of a large table into smaller, more manageable pieces while maintaining the logical table structure.
Primary Key
Section titled “Primary Key”A column or combination of columns that uniquely identifies each row in a table.
Procedure (Stored Procedure)
Section titled “Procedure (Stored Procedure)”A prepared SQL code that can be saved and reused, accepting parameters and performing operations on the database.
A request for data or information from a database, typically written in SQL.
Query Optimization
Section titled “Query Optimization”The process of improving query performance by choosing the most efficient execution plan.
Referential Integrity
Section titled “Referential Integrity”A property that ensures relationships between tables remain consistent, enforced through foreign key constraints.
Replication
Section titled “Replication”The process of copying and maintaining database objects in multiple databases to improve availability and performance.
Rollback
Section titled “Rollback”The operation of undoing changes made during a transaction, returning the database to its previous state.
A horizontal entity in a table representing a single record.
Schema
Section titled “Schema”The structure of a database, including tables, columns, relationships, and constraints.
Sharding
Section titled “Sharding”A database partitioning technique that splits data across multiple database instances to improve performance and scalability.
SQL (Structured Query Language)
Section titled “SQL (Structured Query Language)”A standard language for managing and manipulating relational databases.
Subquery
Section titled “Subquery”A query nested inside another query, used to perform operations that require multiple steps.
A collection of related data organized in rows and columns within a database.
Transaction
Section titled “Transaction”A sequence of database operations treated as a single logical unit of work that must be completed entirely or not at all.
Trigger
Section titled “Trigger”A database object that automatically executes in response to specific events (INSERT, UPDATE, DELETE) on a table.
Unique Constraint
Section titled “Unique Constraint”A constraint that ensures all values in a column or set of columns are distinct.
A virtual table based on the result of a SQL query, providing a way to present data without storing it physically.
Understanding these database terms is crucial for effective data management and SQL proficiency.