Introduction to Databases

Database systems are the bedrock of modern software applications, storing and managing data in ways that can be quickly accessed and manipulated. While the term 'database' is often used generically, there are several types of databases, each with its unique characteristics and use cases. This post will delve into the most commonly used database types: Relational, Column-based, Document-based, Graph, and Time-Series databases.

Relational Databases

Relational Databases are built on a framework that allows data to be identified and accessed about other data in the database. These databases are often managed using Structured Query Language (SQL), a domain-specific language for database programming. The relational model organises data into tables, which can be linked—or related—based on keys.

Practical Application:

Everywhere :)

A relational database could be employed in e-commerce to manage various tasks. For instance, one table could store product information—such as product ID, name, and price—while another could track customer purchases. When a customer makes a purchase, you could execute an SQL query to reduce the stock level of the purchased items, ensuring real-time inventory tracking.

Foundation:

The relational model, proposed by E.F. Codd in 1970, is grounded in set theory and predicate logic. A database table is essentially a mathematical set of tuples, and SQL operations like SELECT, JOIN, and UNION are equivalent to set operations like intersection, Cartesian product, and union, respectively. For instance, a JOIN operation could be mathematically represented as:

\[\text{JOIN}_{\text{condition}}(A, B) = { (a, b) | a \in A \land b \in B \land \text{condition}(a, b) }\]

The underlying logic makes relational databases highly consistent, and their integrity constraints can be mathematically proven.

Column-based Databases

Unlike traditional relational databases that store data row-wise, column-based databases arrange data in columns. This structure is optimal for analytical query processing and is often used in data warehouses and big data storage solutions. Columnar storage allows for highly efficient compression and indexing.

Practical Application:

Imagine operating a weather monitoring system collecting vast amounts of data like temperature, wind speed, and humidity from multiple cities. A column-based database would enable quick and efficient querying of specific attributes, like the average temperature across all cities in a particular range of dates.

Foundation:

The underlying maths for column-based databases isn't drastically different from relational databases but employs data structures like Trie Trees or Bitmap Indexes for efficient storage and retrieval. For example, given n columns C1, C2,..., and Cn​, the data can be represented as separate arrays, allowing vectorised operations, which are computationally more efficient.

\[ C_i = [x_1, x_2, ..., x_n] \]

Document-based Databases

Document-based databases are designed to store, retrieve, and manage semi-structured data. Each piece of data, or 'document', encapsulates all its information, making it easier to modify and scale horizontally. Popular formats include JSON and XML.

Practical Application:

Suppose you're building a blogging platform. In a document-based database, each blog post, metadata, comments, and even author information could be stored as a single, nested document. This eliminates the need for complex joins when retrieving all relevant information to a specific post.

Foundation:

While there's no unique mathematical theory behind document databases, their implementation often involves hash functions for indexing and B-trees for sorting documents. Document similarity and searching could be described using vector space models and algorithms like cosine similarity.

\[\text{Cosine Similarity}(A, B) = \frac{A \cdot B}{||A|| \times ||B||}\]

Graph Databases

Graph databases are designed to handle data from nodes, edges, and properties. They excel at managing interconnected data and complex relationships, making them particularly useful for social networks, recommendation systems, and semantic web applications.

Practical Application:

A graph database would be apt for storing information if you're running a recommendation engine for a bookshop. Each book could be a node, and edges could represent relationships like "similar genre to" or "written by the same author," allowing for intricate queries to recommend books based on various criteria.

Foundation:

Graph databases lean heavily on graph theory. A graph G is formally defined as a set V of vertices and E of edges, G=(V, E). Algorithms like Dijkstra's for shortest path and various traversal algorithms (Depth-First, Breadth-First) are essential in the operation of these databases.

\[\text{Shortest Path} = \min(\text{Path}(v_1, v_2)) \quad \text{for all} , v_1, v_2 \in V\]

Time-Series Databases

Time-series databases (TSDB) are optimised for handling timestamped data. They are commonly used for monitoring systems, IoT devices, and real-time analytics. Time is a primary axis; these databases excel at queries over time ranges and aggregations.

Practical Application:

A Time-Series Database is particularly suitable for tracking financial market data. It could efficiently store stock prices with timestamps and allow for real-time querying of market trends, moving averages, or volatility over specific periods.

Foundation:

Time-series databases often employ Fourier Transforms to convert time-domain data into frequency-domain for more accessible analysis or Discrete Wavelet Transforms for multi-resolution analysis. Mathematical models like Auto-Regressive Integrated Moving Average (ARIMA) may also be used for forecasting.

\[ X(t) = c + \epsilon_t + \sum_{i=1}^{p} \phi_i X(t-i) + \sum_{i=1}^{q} \theta_i \epsilon_{t-i} \]

Here, ϵt​ is white noise, c is a constant, and ϕi​ and θi​ are model parameters.