Introduction to Databases

Let’s explore the basics of databases and DBMS (Database Management Systems) with practical examples. Understand data models, flat file limitations, and how modern systems ensure data integrity and efficiency.

This article is based on the lessons I teach about DBMS at Fondazione PIN in Prato in 2025.

Introduction

#

Let’s start by answering the most important question, what’s a database? Think about it for a second: are SQLite and PostgreSQL databases? And what about your phone’s contacts list?

A database is a collection of information that models some aspect of the real world.

So your phone’s contacts list is a database. SQLite and PostgreSQL are relational database management systems, rather than individual databases.

A Database Management System (DBMS) is software that allows applications to store and analyze information in a database. A generic DBMS supports the definition, creation, querying, updating and administration of a database according to a given data model. And it allows us to manage and organize that data in a structured and efficient way.

But is it really necessary to use a DBMS?

Suppose responses.csv contains questionnaire responses, and our boss asks us to calculate the average experience for each programming language.

How do we do it?

timestamp,language,experience
06/01/2025 11.39.03,Java,1
06/01/2025 11.40.33,Python,4
06/01/2025 11.40.41,C,1
06/01/2025 11.41.06,Java,2
06/01/2025 11.41.13,Java,3
06/01/2025 11.41.18,C,1
06/01/2025 11.41.25,Python,5
06/01/2025 11.41.32,Java,3
20/01/2025 9.46.58,Python,3
20/01/2025 9.47.17,Python,3
20/01/2025 9.47.21,C,4
20/01/2025 9.47.27,Python,3
20/01/2025 9.47.47,C,4
20/01/2025 9.47.47,Python,3
20/01/2025 9.47.47,Python,3
20/01/2025 9.47.50,Python,3
20/01/2025 9.47.50,C,1
20/01/2025 9.48.14,Python,3
20/01/2025 9.48.20,Python,3
20/01/2025 9.48.33,C,4
20/01/2025 9.48.45,Python,3
20/01/2025 9.48.45,Python,3
20/01/2025 9.48.47,Java,3
20/01/2025 9.48.47,Java,2
20/01/2025 9.49.08,Python,2
20/01/2025 9.49.19,Python,1
20/01/2025 9.49.26,Python,2
20/01/2025 9.49.28,Python,3
20/01/2025 9.49.37,Python,2

We could use Python:

import csv

with open("responses.csv") as file:
    reader = csv.DictReader(file)
    counts = {}
    total_experience = {}

    for row in reader:
        favorite = row["language"]
        if favorite in counts:
            counts[favorite] += 1
            total_experience[favorite] += int(row["experience"])
        else:
            counts[favorite] = 1
            total_experience[favorite] = int(row["experience"])

for favorite in sorted(counts, key=counts.get, reverse=True):
    print(
        f"{favorite}: {counts[favorite]} | experience {total_experience[favorite]/counts[favorite]}"
    )

Or we could achieve the same result using a DBMS like SQLite.

sqlite3 response.db

Let’s create a table

 CREATE TABLE response (timestamp text, language text, experience int);

Import csv file

.mode csv

.import --skip 1 responses.csv response

What’s the average experience of every language?

SELECT language, count(*) AS n, AVG(experience) as experience FROM response GROUP BY language ORDER BY n DESC;

We achieve the same result, so why bother? Let’s talk about a flat file format like csv.

Flat file

#

The information is saved comma separated (CSV) and the files are managed by our application.

• use one file per entity
• in this simple implementation without indexes, the application scans the relevant file to find a record to read or update.

Artist

name,year,country
"Linkin Park",1996,"USA"
"Adele",2006,"UK"
"Harry Styles",2010,"UK"

Album

name,artist,year
"Hybrid Theory","Linking Park",2000
"21","Adele",2011
"Fine Line","Harry Styles",2019

If we save the album CSV as albums.csv, we can read that file to find the year Hybrid Theory was released.

import csv

with open("albums.csv") as file:
    reader = csv.DictReader(file)
    for row in reader:
        favorite = row["name"]
        if favorite == "Hybrid Theory":
            print(int(row["year"]))

Correct right? Or did you spot the error?

The lookup still returns 2000, but it does not detect the misspelled artist name.

Linkin Park is written without the final ‘g’, our record inside the Albums’ file is wrong.

What are the problems with this implementation?

Data integrity:

• How can we be sure that the artist is the same for each album?
• What if someone changes the year to an invalid value?
• How do we handle multiple artists per album?
• How do we handle deletions?

Implementation:

• How do we find a specific record?
• What if multiple applications need to access the same data set? What if they run on different machines?

Durability:

• What if our machine crashes while our program is writing/updating a record?
• How can we replicate it across multiple machines to increase availability in case of crashes?

Database Management System

#

We have already defined a DBMS as software that allows applications to store and analyze information in a database. A DBMS is responsible for:

• Managing access to the database. In embedded systems such as SQLite, access control also depends on the application and operating system.
• Applying authorization controls and validation procedures, to ensure the integrity and security of the information.
• Providing a standard interface for data manipulation.
• Managing concurrency, avoiding conflicts when multiple users access and modify data at the same time.
• Ensuring data persistence and recovery, using backup and recovery techniques in the event of failures or errors.
• Optimizing performance, using indexes and caching techniques to speed up data access.

Data Model

#

While a DBMS handles the practical tasks of storing, securing, and retrieving data, a data model is a set of concepts for describing data in a database.

Think of a data model as the set of ideas or rules that helps a database know how to organize and understand all the information it holds. It’s like the blueprint for data, outlining concepts like relationships and data types. How that data is organized, that’s a schema. So a schema is a specific description of how a particular collection of data fits into that blueprint—it’s the detailed plan showing exactly what data is stored and how it’s structured.

There are different types of data model:

During the following lessons we will take a look at the Relational one using SQLite and also key/value with Redis and Document with MongoDB.

Practice with Seagull

#

If you want to move from theory to hands-on SQL practice, try Seagull.

Seagull is a small browser-based project I built for database courses: students open an exercise, run SQL directly in the browser, inspect results, and get immediate feedback without installing a local DBMS.

Next lessons

#

If you want to continue the DBMS path, these are the most useful follow-ups:

• History of Databases and SQL for the transition from early systems to the relational model;
• Relational Data Modeling for tables, keys, and relationships;
• Database Normalization for 1NF, 2NF, 3NF, and BCNF;
• DBMS Architecture for query processing, storage, and memory;
• SQLite Indexes Explained for EXPLAIN QUERY PLAN, composite indexes, and tuning.