Fundamentals of Databases

This is part one of a series on databases and structured query language (SQL).

Databases are a fundamental part of many enterprise-level applications. Virtually all websites on the Internet today are essentially just an interface to a database. Content is created by the user, read by the user, and edited by the user.

There are many different database systems, the most popular of which include MySQL, SQLite, PostgreSQL, and MSSQL. These aforementioned languages all have one thing in common: they are relational databases (data that is organized formally according to a preexisting user-defined model) and they all use SQL.

In addition to SQL-based databases, there are more modern database systems that claim to supersede the traditional SQL model. Those will not be covered in this series, but I encourage you to read about MongoDB or CouchDB if you’re willing.

When I speak of databases in this series, I refer to relational databases using SQL.

Databases are organized into tables, also known as schema. Each table has a list of fields, each of which has a name, a type, and properties. For example:

It is customary for each table to have its first column known as an id. This identification number is typically unique, required, and automatically incremented as new rows are added to the table. In this case, the id is a primary key.

In the example, each student’s class_id matches a class’s id. Consider the case where a class’s teacher is changed from Mr. Jones to Mrs. Pilgrim. If the teacher’s name were stored with each student, then the name would have to be changed in several different places, once for each student in the class. Having the teacher’s name separate removes this redundant information from the database, and a name only needs to be changed in 1 place: in the teachers table.

Some more exploration of terminology:

Each field (column) in a database needs to have a type. Different databases support different types. Here are a couple basic ones supported in almost all database systems:

The next part (part 2) in this series will cover first of the 4 basic database operations: create, read, update, and delete (CRUD for short).