Relational Database Management Systems (RDBMS) Knowledge

Kip Landergren

(Updated: )

My Relational Database Management Systems (RDBMS) knowledge base explaining key concepts.

Contents#

Overview#

A Relational Database Management System (RDBMS) is the software surrounding a database that allows for a relational model to be configured as the database schema and for queries and operations to be run safely and consistently.

Core Idea#

Create a suite of software to manage an underlying database, provide an interface for users and applications, and ensure that key properties like safety, reliability, and persistence are maintained.

Key Concepts#

Relational Algebra#

Refer to Relational Algebra Knowledge article.

Relational Design Theory and Database Normalization#

The Boyce-Codd Normal Form (BCNF) is a form of database that removes all redundancy based on functional dependencies. Functional dependencies represent a constraint between attributes of a relation such that the set on the left hand side uniquely identifies the right hand side.

Structured Query Language (SQL)#

Refer to Structured Query Language (SQL) Knowledge article.

Terminology#

ACID#
1.
atomic#
1.

Either happens completely or not at all.

Related:

atomic type#
1.

An indivisable kind of data; e.g. a boolean.

Related:

attributes#
1.

The columns; or the divisions of data kinds.

Related:

catalog#
1.

Database; used in the SQL standard.

Compare:

cluster#
1.

A collection of databases.

constraint#
1.
cross join#
1.
Data Definition Language (DDL)#
1.
data directory#
1.
data integrity#
1.
data type#
1.
database#
1.
domain#
1.
dump#
1.

Generate a file with SQL commands that, when fed back into the server, will recreate the database in the same state as it was at the time of the dump.

durability#
1.

A guarantee of the recording of committed transactions even if the server crashes or loses power.

Related:

execution plan#
1.

The actual sequence of steps necessary to execute the SQL statement.

Related:

extensibility#
1.
foreign key constraint#
1.
host-based authentication (HBA)#
1.
index#
1.
index#
1.
insert#
1.
instance#
1.

The data stored in the relations (which are described by the schema) at any given point.

Related:

join#
1.

Queries that access multiple tables (or multiple instances of the same table) at one time.

— https://www.postgresql.org/docs/16/tutorial-join.html

Related:

key#
1.

Attribute whose value is unique in each tuple (note: could also be a set of attributes)

Related:

lock#
1.
operator#
1.
partition#
1.

Splitting a single table into smaller tables.

partition#
1.
qualify#
1.

Adding more specificity to a field or query; e.g. using SELECT tbl1.foo instead of SELECT foo; or adding a clause to a query (like WHERE) that restricts the results.

query#
1.

How to retrieve data from a table.

2.

The process of retrieving, or the command to retrieve, data from a database.

query plan#
1.
query planner#
1.

Component of DBMS that determines sequence of steps necessary to execute the SQL statement.

Related:

referential integrity#
1.
relation#
1.

Essentially: the mathematical term for a table.

Compare:

relations#
1.

The tables; or the structures that store related data.

Compare:

Related:

reliability#
1.
replication#
1.
rewrite rules#
1.
rule#
1.
SQL Standard#
1.
schema#
1.

Structural description of relations; the name, the attributes, and the types of those attributes.

Related:

sequential scan#
1.
sequential scan#
1.
stored function#
1.
stored procedure#
1.
structured type#
1.

A composed kind of data, possibly user-defined; e.g. a PersonType

Related:

table#
1.

A named collection of rows.

Related:

table expression#
1.

An expression that computes a table.

Related:

tablespaces#
1.
transaction (xact)#
1.

Bundles multiple steps into a single, all-or-nothing operation.

Related:

transaction block#
1.

A group of statements surrounded by BEGIN and COMMIT.

Related:

transaction isolation level#
1.
transactional integrity#
1.
trigger#
1.
tuples#
1.

The rows; or records of data across attributes.

Related:

type#
1.

The domains; or the kind of data stored in each attribute.

Related:

update#
1.

Modifying data that is already present in the database.

vacuum#
1.
view#
1.
window frame#
1.
window function#
1.

Performs a calculation across a set of table rows that are somehow related to the current row.

Related: