I have compiled this article using information from my actual hard-copy notebook which I use to write notes for the classes I've attended at school. At the time of writing this, I am studying towards a Masters degree in Software Engineering. This article has definitions related to Database Administration and could be helpful to Scholars or professionals.

Types of Databases

Databases are organised collections of data managed by a Database Management System, categorised by structure and use case.

Relational Databases

These store data in structured tables with rows and columns, using SQL for management. They prioritise consistency and are ideal for structured data. Examples include MySQL, PostgreSQL, and Oracle.

NoSQL/Non-Relational Databases

Designed for large volumes of unstructured, semi-structured data or rapidly changing data, offering high flexibility and horizontal scaling. We have document databases like MongoDB, key-value stores like Redis, Graph databases like Neo4j, and wide-column stores like Cassandra.

Cloud Databases

These operate over the internet, in a virtualised environment such as SaaS or PaaS. They provide on-demand scaling and reduced infrastructure overhead. They are available on platforms such as AWS, Google Cloud Platform, and Microsoft Azure.

Other Specialised Databases

In-memory databases store data in RAM rather than on disk, offering blazing-fast access (e.g. Redis). Time-series databases are optimised for tracking data over time (e.g. InfluxDB). Vector databases are specialised for AI and machine learning applications to store and query vector embeddings.

Stream Processing

Stream Processing is a big data technology that enables real-time analysis, transformation, and action on continuous data streams as they are generated, rather than in delayed batches.

Data Warehouse

A centralised repository that aggregates structured and semi-structured data from multiple disparate sources such as CRM, ERPs, and transactional databases into a single, consistent, and time-variant system. It enables business intelligence (BI), analytics, and volumes of historical data for complex queries rather than daily transactions.

Database Auditing

Database auditing is the systematic monitoring, recording, and analysis of all actions preformed within a database, including user logins, data modifications, and privilege changes. It ensures data security, integrity, and regulatory compliance (e.g. GDPR). Auditing tracks who accessed what data, when, and from where.

Database Access Control

Database access control is a security practice that restricts database access to authorised users, protecting data integrity and confidentiality. It relies on authentication and authorisation to manage user interactions via methods like Role-Based Access Control (RBAC) and SQL commands like "GRANT" and "REVOKE".

Database Security

Database Security is the collective set of measures, tools, and processes such as encryption, authentication, and auditing used to protect database management systems from malicious threats and unauthorised access. It ensures the confidentiality, integrity, and availability (CIA) of data across on-premises or cloud infrastructure.

Concurrency Control

Database concurrency control is a set of mechanisms in a Database Management System that enables multiple users or transactions to access and modify data simultaneously while maintaining data consistency, accuracy, and reliability. Concurrency control ensures the ACID properties (Atomicity, Consistency, Isolation, and Durability).

Database Replication

The process of copying and maintaining data across multiple servers to enhance system reliability, fault tolerance, and performance. It ensures high availability by creating redundant copies, allowing systems to remain functional if one server fails, and reduces read latency by distributing traffic.

Database Partitioning

A design technique that splits large database objects (tables or indexes) to improve performance, scalability, and manageability. It enhances query speed by allowing the database to scan only relevant subsets of data rather than the entire dataset.

Database Sharding

A horizontal scaling technique that splits a large database into smaller, faster, more manageable pieces called "shards". Each shard acts as an independent database stored on a separate server, distributing the read-write load and allowing systems to handle massive data volumes that exceed single-machine capacity.

Database Architecture

A structural framework for organising, managing, and accessing data, defining how components such as query processors and storage managers interact to support applications. It determines how data is stored and accessed, directly influencing a systems scalability, security, and performance, with common models including 1-tier, 2-tier, and 3-tier architectures.

Distributed Database

A collection of multiple, logically interrelated databases spread across different physical locations, nodes, or servers, yet appearing as a single, unified database to users. It improves performance and resilience by storing data closer to users, enabling parallel processing, and allowing horizontal scaling, making it ideal for large-scale, high-traffic applications.

Database Indexing Strategies

These improve performance by creating specialised data structures that allow the engine to locate records without scanning entire tables.

Clustered Indexing

This strategy physically reorders data in the table to match the index order. It is highly efficient for range queries and sorting because related data is stored together.

Non-Clustered Indexing

These indexes maintain a separate structure from the actual table data, using pointers to reference row locations. You can have multiple non-clustered indexes on a single table to support different query patterns.

Composite Indexing

Creating an index on multiple columns can optimise queries that filter on several fields at once. For maximum efficiency, place the column with the most unique values first in the index.

Covering Indexing

This involves including all columns requested by a query within the index itself. This allows the database to return results directly from the index, avoiding a secondary "lookup" to the main table.

Filtered (Partial) Indexing

Useful for indexing only a specific subset of rows (e.g., WHERE status = 'active';). These are smaller, faster, and reduce the maintenance overhead during data updates.

Data Models

Hierarchical

Organises data into a tree-like structure with one-to-many relationships. A parent can have multiple children, but a child can have only one parent. It is fast for traversing simple structures but lacks flexibility and causes data redundancy.

Network

Uses a directed graph structure, allowing more complex connections. A child node can have multiple parents (many-to-many relationships). They are more flexible than hierarchical models but complex to design and manage. They were used in early database management systems before relational models became standard.

Object-Oriented

Stores data as objects, similar to object-oriented programming (OOP), where objects contain both data and methods. It supports encapsulation, inheritance, and polymorphism. It is efficient for complex, data-rich applications but less suitable for simple data processing. It is used for CAD (Computer-Aided Design), multimedia databases, and scientific applications.

Locking Mechanisms

These are concurrency control protocols used to ensure data consistency and integrity when multiple transactions occur simultaneously. These mechanisms manage access to data resources, preventing conflicts like dirty reads or lost updates.

• Functional Lock Types: These define what operations a transaction can perform on a locked data item (S-Lock, X-Lock, U-Lock, Schema Lock).
• Specialised Locking (Key-Range Lock): Is used to prevent "phantom reads" by locking not just existing rows, but also the range between rows in an index. Deadlock Prevention has protocols such as "wait-die" and "wound-wait" which manage the risks of transactions holding locks on resources needed by others, creating a "deadlock".

Two-Phase Locking

A pessimistic concurrency control protocol used in DBMS to ensure conflict-serialisability, guaranteeing database consistency by managing how transactions acquire and release locks. It restricts transactions to two distinct phases: acquiring all locks in the "growing" phase and releasing them in the "shrinking" phase.

Database Normalisation

A systematic process of organising data in a relational database to reduce redundancy and improve integrity. It involves dividing large, complex tables into smaller, related ones to ensure that every piece of data is stored only once. Normalisation follows a series of progressive rules called Normal Forms. Each level builds upon the previous one.

• 1NF (First): Requires atomicity and each column must contain only single, indivisible values. There are no repeating groups or multi-valued attributes.
• 2NF (Second): Must be in 1NF and have no partial dependencies. Every non-key attribute must depend on the entire primary key.
• 3NF (Third): Must be in 2NF and have no transitive dependencies. Non-key columns should not depend on other non-key columns.
• BCNF (Boyce-Codd): A stricter version of 3NF where every determinant must be a superkey.

Database Procedures

Commonly referred to as stored procedures, are reusable pieces of SQL code that are saved directly in the database management system. They act as subroutines that can execute multiple SQL statements, handle complex business logic, and accept input parameters to return specific results.

Database Triggers

A piece of procedural code that runs automatically when a specific event occurs in a database. Unlike standard stored procedures that you must call manually, triggers are "event-driven listeners" that fire implicitly when they detect data or schema changes.

ACID Properties

Stands for Atomicity, Consistency, Isolation, and Durability. They are a set of database transaction principles that guarantee data reliability, integrity, and predictability, especially during failures. They ensure that transactions are processed reliably, making them essential for financial and critical data applications.

Write-Ahead Logging (WAL)

A foundational technique in database systems and file systems designed to ensure atomicity and durability. The core principle of WAL is that any changes made to data files (tables, indexes) are recorded in a persistent, append-only log before the actual data files are updated. WAL reduces the need for expensive disk I/O, as random data updates can be deferred while only fast, sequential writes are made to the log.

Row Level Security (RLS)

A database mechanism that restricts data access at the row level based on user characteristics (e.g. role, department), ensuring users only view authorised data. It works as a transparent filter applied before query results are returned, enhancing data security and simplifying application logic by managing access control within the database itself.

Database Languages

Specialised programming languages used to define, manipulate, and control access to data within a Database Management System. While SQL is the most widely used standard, database languages are typically categorised into four main sub-languages based on their function:

1. Data Definition Language (DDL): Used to define the database structure or "schema". Uses SQL keywords like CREATE, ALTER, and DROP.
2. Data Manipulation Language (DML): Used to manage and manipulate the actual data stored in the tables. Uses SQL keywords like INSERT, UPDATE, DELETE.
3. Data Control Language (DCL): Controls access and permissions for different users using SQL keywords like GRANT and REVOKE.
4. Transaction Control Language (TCL): Manages transactions to ensure data integrity, grouping tasks into a single executable unit. Uses SQL commands like COMMIT, ROLLBACK, and SAVEPOINT.