Session 4

Replacing Excel with SQLite for data management

Author

Francesco Beretta

Published

6 October 2026

Modified

13 October 2026

Beretta Taught by Francesco Beretta.

In this session

  • Why a spreadsheet is not a database
  • Conceptual modelling in practice: draw.io (as VSCode extension: Draw.io Integration by Henning Dieterichs)
  • SQLite: building and querying an actual database, working with DBeaver

Why not Excel

Spreadsheets are excellent for statistics and data analysis, but poor for data production and management:

  • no identifiers for rows, no real rows: you can sort just one column and destroy all your work in a second (only a backup will save you)
  • no relations: the same person entered fifteen times, spelled five different ways
  • formatting as information — a yellow cell that means something only to you
  • silent format coercion: identifiers turned into numbers, dates turned into other dates

Create your first information system in the form of a SQLite database

Continuing from the Intro’s data-modelling session

Exercice 1. Explore the Chinook database

  • Cf. these slides from the morning session.
  • We will first explore the Chinook database:
    • Create a dedicated directory for this exercise.
    • Download and open the database conceptual model with the VSCode Drawio plugin.
    • Download the database and open it with DBeaver.

Basic concepts:

  • Entities, attributes, relations
  • Cardinality: one-to-one, one-to-many, many-to-many
  • Primary and foreign keys
  • Normalisation, far enough to be useful and no further

Exercice 2. Conceptualise some features of a population (prosopography)

The envisaged analysis:

  • map the birthplaces of your population and their development over time.
  • analyse teacher-student relationships using social network analysis.
  • analyse networks of organisations through memberships at different times.

 

  • This GitHub repository will be first presented.

  • Then you can fork it, and reuse the conceptual model and database in view of producing your own one in this Lab session.

SQLite

  • A whole relational database in a single file, with no server: SQLite Tutorial
  • DBeaver as the graphical way to manage the database
  • CREATE TABLE, data types, PRIMARY KEY, FOREIGN KEY, NOT NULL
  • INSERT, SELECT, WHERE, ORDER BY
  • JOIN — the reason the whole exercise is worth it
  • GROUP BY and aggregates
  • Importing a CSV, and exporting back out again
TipIn parallel in the Intro

Data structures: building information systems for research

Back to top