Database Tutorial (Part 1) – About Databases, Creating Databases, Tables

This article is part 1 of the database tutorial.

Here, we cover the following:

  • What is a database?
  • What is a Database Management System (DBMS)?
  • Types of databases
  • What does a relational database look like?
  • Creating a database
  • Creating tables
  • Data types

So before we get into the details of databases, let’s define what a database actually is.

What is a Database?

A database is a collection of structured data.

In a database, data is stored in a way that makes it easy to retrieve. A database should make it easy for you to find the exact data that you require. A database enables you to pick only the data that you require on any given occasion.

So in theory, a comma separated file is a database. If you were to create a file, save it with a .csv extension, and include the following text, it would technically be a database.

ID, Name, Email
1, Bruce, bruce@example.com

2, Cindy, cindy@example.org

3, John, john@example.net

This is a database, because it stores data in a structured way. Each line is a new record and the commas separate each field of data. So you could find all information on Cindy simply by looking at the whole line. The same data could also be in an Excel spreadsheet and technically, it would also be a database.

But these days, databases are a bit more advanced than text files and spreadsheets. Most modern day databases have been built with a database management system.

What is a Database Management System (DBMS)?

A database management system (DBMS) is specialised software that is used for building and managing databases.

Most database management systems enable you to do things like create databases, add data, query the data, etc. Many have more advanced features such as advanced security features, analytical tools, scheduled backups and more. And some DBMSs allow you to do things like create forms, reports, import/export data, etc.

Examples of DBMSs include:

  • Microsoft Access. Popular with individuals and small businesses.
  • SQL Server. Also from Microsoft, SQL Server is a more sophisticated and robust DBMS than Access.
  • MySQL. Widely used open source DBMS.

Types of Databases

There are many types of databases. Here are some of the main ones:

The text file example we used previously would be referred to as a “flat file” database.

The most common type of database is a relational database.

This tutorial focuses mainly on relational databases.

What does a Relational Database Look Like?

The biggest hurdle many people find when learning about relational databases is that, they have trouble visualising what a database should actually look like. This is especially true if you’re used to using spreadsheet software to store your data. Having a mental picture can be a great help in understanding relational databases.

Consider the following diagram:

Basic diagram of a relational database.

We can take the above model, and create a database with real data:

Basic diagram of database with a database name, table names and field names.

So one of the first steps in creating a database is to create the tables and name them. When you create the tables, you also create the columns (or fields) that will hold the data and name them too.

See how we’ve named the three tables and their columns? This will make the data easier to find than if they’d been named A, B, C, etc. If you want to see a list of artists, you know to look in the Artists table. Same with albums and ratings.

Creating a Database

Most of the popular RDBMSs allow you to do most tasks either via the GUI (Graphical User Interface) or programatically. This holds true for creating databases too.

Using the GUI

Using the GUI involves simply “pointing and clicking” your way through the various options/prompts as you would with any other software interface.

For example, here’s how you can create a database in Access:

Screenshot of the Access welcome screen
The Microsoft Access welcome screen allows you to create a database from scratch. You can also search through thousands of templates for a prebuilt database that you can use to get started.
Screenshot of prompt to name the database.
After clicking on “Blank Desktop Database” you will be prompted to name your database before clicking “Create”.
Screenshot of blank database
This is a newly created blank desktop database. A table has automatically been created. You can now customise this database to your own needs.

Other systems might require you to right-click on a Databases node or similar but the process is essentially the same – simply select a Create Database option and name the file.

Once the blank database is created, you can then add tables and other objects as needed.

Then once all of that has been set up, you can then add data.

Creating a Database Programatically

Most relational database management systems allow you to create databases (and most other tasks) programatically using SQL.

SQL (Structured Query Language) is a language designed specifically for working with databases. It is used to create databases, tables, etc. It is also used to query data from the database.

In fact, when you use the GUI to create a database, the system actually uses SQL behind the scenes to generate it.

Seeing as this a database tutorial for beginners, we won’t be going into much detail with SQL. Just know that most tasks mentioned in this tutorial can be done either via the GUI or SQL.

Creating Tables

Tables can also be created via the GUI or programatically.

Here’s the button for creating a table via the Access GUI:

Screenshot of the CREATE tab of the Ribbon in Microsoft Access 2013
Clicking the “Table” icon from the “CREATE” tab creates a new table.

When you create tables, you also create the columns and specify their data types.

Data Types

Data type is an important concept in the world of databases. Most database management systems require that you specify a data type for each column.

Data type refers to the type of data that will be stored in that column. For example, it could be a number, some text, or a date, etc.

Here’s a screenshot of selecting a data type in Access. This dropdown menu appears as soon as you try to add a new field:

Screenshot of dropdown list of data types
When you click to add a new field, you’ll be prompted to select a data type first.

One of the main benefits of specifying the data type for each column is that it helps prevent wrong data being entered.

For example, it can prevent the user from accidentally entering a date into a field intended to store a phone number. If this was allowed to happen, your database would probably end up with inaccurate and inconsistent data. Imagine trying to find a person’s phone number, only to find a date instead.

So setting up data types is an important part of creating a database. It helps to maintain data integrity.

Database Tutorial (Part 2)

In part 2 of this tutorial we cover relationships, adding data, and querying a database.