Database introduction 1 - background info

Content:

1. Introduction
2. Database types
3. Database products
4. Relational databases
   1. History
   2. Terminology
   3. Normalization
   4. Object-relational impedance mismatch
5. Database API's
   1. SQL
   2. Database specific API's/Libraries for relational databases
   3. Embedded SQL
   4. Standardized API's
   5. Database specific API's for NoSQL databases
   6. ORM's
6. Topologies

Introduction:

Most applications utilize some form of database to persist data in.

Knowledge about databases is therefore essential for a software developer.

This article targets beginners and attempt to provide them with a basic understanding of database types, products and terminology with the intent to provide the necessary background information to work with databases.

If you want to skip the background information and go straigth to the "HowTo", then got to this article.

Database types

Databases are often categorized based on their storage model:

if data is large (> 250 TB) then chose a column store
if data are unstructured (different structure for each piece of data) XML or JSON then chose a document store
if data only have one key, need for queries is very limited and performance is critical then chose key value store
for all other cases chose a relational database

Databases can also be categorized based on how the application communicate with it:

Database server

Application communicate with database server process via network protocol. The database server process may run on the same system as the application or on another system.

Embedded database

Application communicate with database software using normal function/method calls. The database code is in the same process as the application code and therefore also on the same system.

Database products

Some of the more popular database products are:

Relational databases

Relational databases are still the most widely used databases and furthermore there is a tendency to measure non-relational databases against relational database, so it is worth looking a little bit into relational databases.

History

The relational model was invented by Edgar Codd in 1969/1970.

It was later advocated by Chris Date, whose book "An Introduction to Database Systems" can be considered the bible of relational databases (and note that the term "introduction" in the title should not be take literally - it is a book at a relative high level).

Relational database became popular during the 1980's when products like IBM DB2, Oracle DB, DEC RDB, Sybase and Informix arrived in the market.

Terminology

There are many confusing aspects of relational database theory including the terms used for the most basic concepts:

The different key types are:

Candidate key(CK)

Column or combination of columns that can uniquely identify a row

Primary key (PK)

The candidate key chosen for uniquely identifing a row

Alternate key (AK)

A candidate key not chosen

Foreign key (FK)

A column in one table referring to the primary key in another table

The different relation types are:

1:1 relation / one-to-one relation

each row in table₁ has a relation to only one row in table₂ and each row in table₂ has a relation to only one row in table₁

1:M relation / one-to-many relation

each row in table₁ has a relation to many rows in table₂ and each row in table₂ has a relation to only one row in table₁

M:M relation / many-to-many relation

each row in table₁ has a relation to many rows in table₂ and each row in table₂ has a relation to many rows in table₁

Joining is combing two tables from a relation. The different join types are:

JOIN or INNER JOIN

only combinations that have rows in both tables

LEFT JOIN or LEFT OUTER JOIN

all rows from left table combined with row from right table if exists otherwise filled with NULL values

RIGHT JOIN or RIGHT OUTER JOIN

all rows from right table combined with row from left table if exists otherwise filled with NULL values

Normalization

Normalization is the process of optimizing database tables to renmove any redundancy in the data.

There are different levels of normalization:

Object-relational impedance mismatch

It should be obvious by now that a relational database model and an object model used in an application is not a 1:1 match.

This is known as the object-relational impedance mismatch.

Some consider this a major problem for the mix of object oriented programming and relational databases. But I don't see it as a major problem. Due to both OOP and RDBMS being very popular then methodologies and tools has been developed that mitigate most of the problems.

Database API's

SQL

All relational databases today supports SQL (Structured Query Language).

Its history goes back to 1974.

It has been standardized by ANSI and ISO in 1986, 1992, 1999, 2003, 2006, 2008 and 2011.

Note that unlike traditional programming languages then the new features are rarely used. Most SQL usage just require SQL 1992.

Database specific API's/Libraries for relational databases

These API's are typical using SQL.

Common API's/Libraries are:

I don't see much need to use these API's today. I would use a standardized API instead.

For examples of DBLIB see here.

For examples of MySQL Connector/C see here.

For examples of MySQL Connector/C++ see here.

For examples of PostgreSQL C API see here.

For example of LIBOCI see here.

For examples of mysql extension see here.

For examples of mysqli extension see here.

For examples of pgsql extension see here.

Embedded SQL

Embedded SQL was the primary way to access relational databases for many years and are still used in IBM mainframe, commercial Unix and OpenVMS environments.

The concept is that one write a program with:

• ordinary statements in whatever language is used (most common is Cobol and PL/I)
• SQL statements prefixed with EXEC SQL

and then a preproceser convert the embedded SQL statements to call to database specific API and then it is compiled.

Embedded SQL is not something I would consider if there are any alternatives.

For examples of embedded SQL (using C) see here.

Java has a special flavor of embedded SQL called SQLJ. For example see here.

Standardized API's

These API's are typical using SQL.

Common API's are:

For relational databases these should be the preferred API for all usage that does not fit well with ORM. This include operations on record sets instead of individual records - often in a DWH context.

For examples of JDBC see here.

For examples of ODBC see here.

For examples of ADO see here.

For examples of ADO.NET see here.

For examples of PDO see here.

For examples of Qt SQL see here.

Database specific API's for NoSQL databases

These are very different between different NoSQL databases.

The API typical consists of:

1. Library API implemented in multiple languages
2. RESTFul web service API

In my opinion these API's are often rather cumbersome to use. But if one need to use a NoSQL database, then one does not have any choice.

ORM's

The concept is to write:

• data classses
• instructions to the ORM about how to map those to databasetables

and then the ORM handles the database access without the programmer needing to write any SQL.

ORM's are typical using an ORM specific query lanaguage for advanced queries.

Common ORM's are:

For relational databases these should be the preferred API for CRUD operating on single records - usually in an OLTP context.

In theory ORM can be used for NoSQL databases as well, but it is very rare.

For examples of using Hibernate see here.

For examples of using MyBatis see here.

For examples of using LINQ to SQL see here.

For examples of using EF see here.

For examples of using NHibernate see here.

For examples of using Doctrine see here.

For examples of using Eloquent see here.

Topologies:

Active/passive cluster with replication:

Applications connect to the active database server.

The active database server replicate all updates to the passive database server.

If active database server goes down then then the passive database server becomes active and applications reconnect to it.

Model:

After failure:

Active/passive cluster with shared storage:

Applications connect to the active database server.

If active database server goes down then then the passive database server becomes active and applications reconnect to it.

Due to the shared storage the passive database server is always uptodate.

Model:

After failure:

Active/active cluster with shared storage:

Applications connect to a ny database server.

Due to the shared storage all database server are always uptodate.

Model:

After failure:

Active/active cluster with sharding/partioning:

Each database server has a fraction of the data.

Applications access the database server that has the data they needs.

There is no redundancy - unless combined with one of the previous models.

Model:

With redundancy added:

For some reason the term sharding is most common in OLTP context while the term partitioning is most common in DWH context.

Large scale distributed databases:

This is a rather complicated topology and only applicablae when dozens/hundreds/thousands of database servers.

Implementations differ sligthly but typical it works like:

• there are N database servers
• all N database servers are active
• each bucket of data exist on M nodes
• each database server has M/N of data buckets
• all updates get replicated to the M-1 copies
• application route request to a database server that has the data it needs
• if a database server goes down then the remaining N-1 database servers split its responsibility so each bucket still exist on M nodes

Article history:

Other articles:

See list of all articles here

Comments:

Please send comments to Arne Vajhøj