Key Concepts in Database Management Systems | Quizlet

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.

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.

ACID Properties

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

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.

Consistency

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

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.

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.

Records

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

Attributes

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

Schema

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

Instance Data

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

Key

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

Primary Key

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

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.

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.

Data Wrangling

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

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.

Cubes

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

Facts

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

Dimensions

In a data cube, these are the characteristics or aspects by which the facts are categorised and analysed (e.g., time, location, product).

Granularity

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

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).

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).

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.

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).

Declarative Language

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

Composable

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

Projection

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

Selection

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

Join

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

Cartesian Product

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

Self-Join

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

INNER JOIN

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

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.

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.

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.

CROSS JOIN

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

Aggregation

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

GROUP BY

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

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.

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.

Indexes

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

B-tree Index

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

R-tree Index

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

Composite Index

An index created on multiple columns of a table.

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.

OLTP (On-Line Transaction Processing)

A type of database usage characterised by a high volume of relatively simple transactions, typically used for operational 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.

UML (Unified Modeling Language)

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

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.

Express

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

Routes

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

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.

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.

JDBC (Java Database Connectivity)

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

ODBC (Open Database Connectivity)

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

Distributed Database

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

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.

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.

External schema

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

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.