Key Concepts in Database Management Systems | Quizlet

ACID Properties

A set of properties (Atomicity, Consistency, Isolation, Durability) that guarantee reliable processing of database transactions.

Aggregation

Computing a single value from multiple rows in an SQL query using aggregate functions (e.g., COUNT(), SUM(), AVG(), MAX(), MIN()).

Atomic

An ACID property ensuring that a transaction is treated as a single, indivisible unit; it is either fully completed or not executed at all.

Attributes

Columns in a database table, representing characteristics or properties of the records.

B-tree Index

A common type of database index that organises data in a tree structure, efficient for searching, sorting, and range queries.

Cartesian Product

The result of joining two tables without a join condition, producing every possible combination of rows from both tables.

Composable

The ability to combine smaller parts of a language (like SQL queries) into larger, more complex structures (e.g., nested queries or views).

Composite Index

An index created on multiple columns of a table.

Consistency

An ACID property ensuring that a transaction brings the database from one valid state to another, maintaining all integrity constraints.

CROSS JOIN

Returns the Cartesian product of the tables, combining every row from the first table with every row from the second table.

Cubes

A multi-dimensional database or data structure suitable for analytics, often visualized as a spreadsheet with more than two dimensions.

Data Definition Language (DDL)

The part of SQL used for defining and managing the database schema, including commands like CREATE TABLE, ALTER TABLE, and DROP TABLE.

Data Manipulation Language (DML)

The part of SQL used for manipulating the data within the database, including commands like INSERT, UPDATE, and DELETE.

Data Semantics

The meaning and interpretation of the data, including the meaning of rows, columns, and the presence of exceptional cases, missing values, or inconsistencies.

Data Wrangling

The process of cleaning, transforming, and unifying raw data into a suitable format for analysis or other purposes.

DBMS (Database Management System)

Software that manages databases, allowing actions such as searching, mutating (changing), and managing data while ensuring its safety, availability, reliability, performance, scalability, and security.

Declarative Language

A programming language where you specify what you want to achieve, rather than how to achieve it (e.g., SQL).

Dimensions

An attribute (or a table) that characterizes a fact.

Distributed Database

A database where data is stored across multiple physical locations (servers), but managed as a single logical database by a DBMS.

Document Store

A type of NoSQL database that stores data in flexible, semi-structured formats known as documents (often JSON or BSON), rather than rigid table structures.

Drill-down

An operation in data cube analysis that moves from a summary level to a more detailed level of data (e.g., from sales by year to sales by month).

Durable

An ACID property ensuring that once a transaction is committed (declared complete), its results are permanent and will not be lost, even in the event of system failures.

Express

A popular, minimalist web application framework for Node.js, providing tools for building web applications and APIs.

External schema

User- or application-specific view of the database, showing only relevant parts of the overall structure.

Facts

In a data cube, these are the numerical measures or values being analysed (e.g., sales amount).

Federative Database

A system that integrates data from multiple autonomous databases, allowing users to query and manipulate data as if it were in a single database, even if the underlying databases use different systems or schemas.

Foreign Key

Attribute(s) in one table that reference the key (usually the primary key) of one or more records in another table, establishing a relationship between the tables.

FULL OUTER JOIN

Returns all rows when there is a match in either the left or right table. Rows with no match in the other table will have NULL values.

Granularity

The level of detail at which facts are recorded in a data cube (e.g., sales by day vs. sales by month).

GROUP BY

An SQL clause used with aggregate functions to group rows that have the same values in specified columns.

Indexes

Data structures (like B-trees) created on one or more columns of a database table to speed up data retrieval operations.

INNER JOIN

Returns only the rows where there is a match in both tables being joined.

Instance Data

The actual data content stored within the tables of a database.

Isolated

An ACID property ensuring that concurrent transactions appear to execute in isolation from each other; the result is the same as if they were executed sequentially.

JDBC (Java Database Connectivity)

An API for the Java programming language that defines how a client may access a database.

Join

Combining rows from two or more tables based on related columns between them in an SQL query.

Key

One or more attributes that uniquely identify a record in a table.

LEFT (OUTER) JOIN

Returns all rows from the left table and the matching rows from the right table. If there is no match in the right table, NULL values are returned for the right table's columns.

Most-Left Prefix Principle

A rule for composite indexes (like B-trees) stating that the index can be used efficiently for queries that filter or sort based on the leftmost columns included in the index definition.

Node.js

A JavaScript runtime environment that allows developers to run JavaScript code outside of a web browser, commonly used for building web servers and applications.

ODBC (Open Database Connectivity)

A standard API for accessing database management systems. Independent of programming languages and database systems.

OLAP (On-Line Analytic Processing)

A type of database usage characterised by complex queries and analysis of large volumes of data, typically used for decision support and business intelligence.

OLTP (On-Line Transaction Processing)

A type of database usage characterised by a high volume of relatively simple transactions, typically used for operational systems.

Prepared Statement

A feature used to execute similar SQL statements repeatedly with high efficiency. The SQL statement template is sent to the database and parsed once, and then executed multiple times with different parameter values.

Primary Key

The chosen 'most important' key that uniquely identifies each record in a table.

Projection

Selecting specific columns (attributes) from a table in an SQL query.

R-tree Index

A type of database index specifically designed for spatial data and queries.

Records

Rows in a database table, representing a single entry or instance.

RIGHT (OUTER) JOIN

Returns all rows from the right table and the matching rows from the left table. If there is no match in the left table, NULL values are returned for the left table's columns.

Roll-up

An operation in data cube analysis that aggregates data from a detailed level to a more summarised level (e.g., from sales by city to sales by country).

Routes

In a web application framework like Express, routes define how the application responds to client requests to specific URLs.

Schema

The description of the structure of a database table, including the attributes, their data types, and relationships.

Selection

Filtering rows (records) from a table based on specified conditions in an SQL query (using the WHERE clause).

Self-Join

Joining a table to itself, often used to compare rows within the same table based on conditions.

SQL (Structured Query Language)

A standard language used for managing and manipulating relational databases. It includes sub-languages for querying (QL), data definition (DDL), and data manipulation (DML).

Star Schema

A simple database schema for data warehousing where a central fact table is connected to multiple dimension tables, forming a star-like structure.

Surrogate Key

An artificially added code or number to a table to serve as a simple primary key, often used when natural keys are complex or composite.

The GET function

The router.get() function runs when the server starts. It tells the server what to do when someone visits a specific page (like the homepage). The code inside it runs only when a user actually visits that page.

Transactions

A sequence of operations performed as a single logical unit of work. In a DBMS, transactions are designed to maintain data integrity and consistency.

UML (Unified Modeling Language)

A standard graphical language used for visualising, specifying, constructing, and documenting the artifacts of a software-intensive system.

View

A virtual table based on the result set of an SQL query. It does not store data itself but provides a dynamic window into the underlying data.

Views

In the context of web development, views are typically templates or files responsible for rendering the user interface or output that is sent back to the client's browser.

ANSI/SPARC architecture

A foundational model for database management systems that organizes data into three levels: external, conceptual, and internal.

Client-side rendering

The server sends data and chart-generating code to the browser, which builds the visualization on the client.

Composite key

Any primary key that is constructed out of more than one attribute.

OLAP (Online Analytical Processing)

Supports complex queries and data analysis on historical data for decision-making.

OLTP (Online Transaction Processing)

Handles real-time, day-to-day operations with fast insert/update/delete actions and minimal data redundancy.

Referential integrity

The principle that ensures the values in a foreign key column of a database table must match existing values in the primary key column of the referenced table.

Relational database

Ensured with the use of foreign keys.

Server-side rendering

The server processes the data and sends a fully generated chart to the browser.

Concurrency

Means that the database is used by a lot of people simultaneously, because of this the DBMS has to ensure the correctness and integrity of the data collection.

Queries

Include all information requests and searches.

Storage Definition Language (SDL)

A type of programming language used to define the structure and organization of data in a database.

Relation

A table in which the sequence of rows is irrelevant OR a central concept in relational model OR a set of tuples.