Databases with SQLite

Some History

We've all heard of databases, and have an idea that they manage data. But what exactly does that mean?

Long away and far ago (before computers), we had to use paper files and index cards stored in filing cabinets to keep track of lists and collections of things.

In the early 1960's Charles Bachman developed the first computerized database system.

Two more names are identified with relational database development:
Edgar F. Codd
C.J. Date

Nowadays, we have many options when choosing which database to use.

Database Types

Not only are there choices of which system to use, there are different types of databases to choose from, depending on what kind of data we want to manage.

• Relational
  most common, using tables and columns
• Spatial
  designed for numeric / spatial data
• Key-Value
  uses associative arrays, sometimes known as a dictionary
• Vector
  uses nearest neighbour algorithms
• Object
  data is represented as an object, as in object-oriented programming

All the above fall into the family of operational databases: they collect, maintain, and modify data, and are thus dynamic.

Analytical databases are designed for historical and time-dependent data, such as work trends, or statistical data over a long period of time.

SQL

The SQL we see when reading about relational databases stands for Structured Query Language, initially developed in the 1970's at IBM. This is the language we use to create, maintain, and modify the database.

Hundreds of books have been written about SQL - I mention several in the Resources page.

If you are new to databases, please visit that page - I will not attempt to add more to the existing knowledge base regarding SQL.

From here on we will be discussing SQLite (the database) and how it works.
SQL is used extensively for this, so a solid foundation is required.

SQLite

SQLite is a Relational Database Management System (RDBMS), developed by D. Richard Hipp. It is maintained by him and a team of programmers. It is used extensively in billions of devices and computers world-wide.

Their web site and complementary user forum provide excellent instruction and assistance.

There are GUI front ends available - a popular one is DB Browser for SQLite (DB4S).

I've been using SQLite for several years, both as my local DB manager and in a web environment, after first working with MySQL and MariaDB. In the following pages I hope to instill an interest in the reader about giving it a workout for either personal or other use.