Every organization that runs for long enough ends up accumulating data: customers, products, transactions, incidents. At first that data fits in a spreadsheet and everything seems fine. The trouble comes later, when the sheet grows, several people use it at once and nobody knows which of the three versions of the file is the good one. In this lesson we are going to understand why databases exist: which concrete problems they solve, what vocabulary is used to talk about them and what role the software that manages them plays. We will not write a single line of SQL yet; the goal is that, by the time we get to it, you know exactly what you are solving and why.

So that nothing stays abstract, the whole course revolves around a single case: BiblioRed, the municipal library network of a fictional city. You will meet it in this very lesson and we will build it up module by module until we have a complete information system.

1. The scenario: BiblioRed and its spreadsheets
2. Data and information: not the same thing
3. What a database is
4. What a DBMS is (functional definition)
5. Essential vocabulary: entity, attribute, table, row, column, key
6. Schema and instance
7. The six problems a database solves
8. Diagnosing BiblioRed's spreadsheet
9. Common mistakes and tips
10. Exercises
11. Conclusion

BiblioRed is the municipal library network of a fictional city. It has four branches, some 12,000 registered members and a collection of around 40,000 copies. Today it works like this:

• Each branch keeps its own spreadsheet of loans, in a shared folder.
• The book catalog lives in a different sheet, maintained by someone at the central branch.
• New member sign-ups are recorded in a third file, and in some branches still on paper.
• When somebody wants to know whether a book is available, they phone the other branch.

The system "works" in the sense that the library opens every day. But it produces constant errors: duplicate loans, members with two records, books listed as available that are not. Management has decided to migrate to a real database, and we are the team that is going to design it.

In this lesson we limit ourselves to understanding the problem. The design starts in module 4.

This is the founding distinction of the whole discipline, and it pays to be clear about it from the start.

• A datum is a raw fact, with no context: 2026-03-14, 9788420412146, 14.
• Information is the result of interpreting data in a context: "on 14 March 2026, member number 14 borrowed the book with ISBN 9788420412146".
• Knowledge appears when information is aggregated and lets you decide: "fiction loans are up 20% in March, we should reinforce that part of the collection".

A database stores data, but it is designed to make turning that data into information cheap and reliable. That is its reason for existing: not to keep things, but to let you ask questions.

A database is a collection of interrelated data, organized according to a defined structure, stored persistently and managed in such a way that several users and applications can query and modify it in a controlled manner.

It is worth pulling that definition apart, because every word rules something out:

• Collection of interrelated data: it is not a pile of loose files. Loans point to members, members to branches, copies to books. The relationships are part of the database just as much as the data.
• Defined structure: before anything is stored, you decide what shape the data has. That decision is called the schema and we will see it in section 6.
• Persistent: it survives the program closing, the server restarting and, if it is properly administered, a disk failure.
• Controlled, shared access: several people can work at the same time without stepping on each other, and each one sees only what they are entitled to.

A spreadsheet satisfies "persistent" and not much more. That is why it works up to a certain size and then stops working.

The database is the data. The DBMS (Database Management System) is the software that manages it. It is the piece that sits between the applications and the files on disk so that nobody has to touch those files directly.

Functionally, a DBMS offers:

• Data definition: it lets you declare which structures exist (tables, columns, types, constraints).
• Data manipulation: insert, query, modify and delete.
• Access control: who can do what.
• Concurrency control: coordinating many simultaneous users without corrupting anything.
• Failure recovery: if the power goes out halfway through an operation, leaving the data in a consistent state.
• Optimization: deciding how to execute a query as fast as possible.

Notice the key idea in the diagram: no application touches the disk. They all talk to the DBMS, and the DBMS is the only one that knows how the data is really stored. That is what makes it possible to change the storage without breaking the applications.

Examples of DBMSs we will use in the course: PostgreSQL (a full server, our main reference), SQLite (an embedded engine, for practising without installing anything heavy) and MongoDB (document-oriented, in module 3).

What lives inside a DBMS —optimizer, storage engine, buffer manager, catalog— and how its architecture is organized is covered in detail in lesson 01-04. Here it is enough to know what it does seen from the outside.

This is the minimum vocabulary you need to follow the rest of the course. We present it using the BiblioRed example.

Visually, BiblioRed's members table might look like this:

Two points that often confuse beginners:

• Entity is a design concept; table is its materialisation. You design thinking in entities and you implement in tables. When we get to entity-relationship diagrams (lesson 04-02) this distinction will be central.
• Row and record are used almost as synonyms, just like column and field. "Row" and "column" are the terms of the relational model; "record" and "field" come from the world of files. You will see both in the literature.

A key is simply an attribute (or combination of attributes) that serves to identify. It is such an important concept that we will devote part of module 2 to it: without keys there is no way to relate tables, and without relationships there is no relational model.

Another distinction to internalise early:

• The schema is the definition of the structure: which tables exist, which columns each one has, what type they are, which constraints they satisfy. It changes rarely, and when it does it is an event (a "migration").
• The instance (or state) is the content at a given moment: the specific rows that are there right now. It changes constantly, every time somebody borrows a book.

The usual analogy: the schema is like the definition of a blank form; the instance is all the filled-in forms sitting in the filing cabinet today.

This separation is what makes it possible to reason about a database without looking at its data: if you know the schema, you know which questions can be answered. In module 4 you will learn to design schemas and in module 5 to improve them through normalization.

Here is the core of the lesson. A database is not "a bigger spreadsheet": it solves six problems a sheet cannot solve.

The problem: the same piece of data stored in several places. In BiblioRed, the member's name appears in every loan row, in the sign-up sheet and in the overdue list.

What it causes: it takes up space (the least of it) and, above all, it forces you to update in N places. If the member changes email, you have to remember every one of them.

How it is solved: by storing each piece of data only once in its own table and referencing it from wherever it is needed. This is the principle that normalization formalises (module 5).

The problem: a direct consequence of the previous one. If the data is in five places and you only update four, you now have two contradictory truths and no way to tell which one is valid.

How it is solved: by eliminating redundancy and adding integrity constraints that the DBMS enforces at all times, without relying on anybody's discipline.

The problem: nothing stops you writing nonsense. A return date earlier than the loan date. A loan tied to a member who does not exist. A duplicated ID number.

How it is solved: the DBMS lets you declare rules —data types, NOT NULL, uniqueness, foreign keys, checks— and it rejects any operation that violates them. The difference from a spreadsheet is that the rule lives in the data, not in the good intentions of whoever is typing. We will see this in lesson 02-06 and in 04-04.

The problem: two librarians open the sheet at the same time, each records their loan and whoever saves second wins; the first loan vanishes. Or worse: the last copy of a book is lent out twice because both of them read "available: 1".

How it is solved: the DBMS manages transactions and locks, so that simultaneous operations produce the same result as if they had been done one after another. This is the subject of module 6 (lessons 06-01 and 06-02).

The problem: whoever has access to the file sees everything. The intern who records loans also sees the personal details of 12,000 members and could delete them by accident.

How it is solved: users, roles and permissions per table and per operation. The front desk can insert loans but not delete members; management can read statistics but not modify the catalog. This is covered in lesson 06-04.

The problem: in a spreadsheet, the way things are stored and the way they are used are one and the same. If you rearrange the columns, every formula and every report breaks. And if the file gets corrupted, the only copy is the one somebody remembered to make.

How it is solved: the DBMS separates how the data is stored from how it is queried. You can add an index, change the storage or move the table to another disk without touching a single query. This is called data independence and we will formalise it in lesson 01-04. Reliable persistence, in turn, rests on transaction logs and backups.

Let's look at the problem with real (fictional) data. This is part of the sheet loans_branch_north.xlsx as it stands today:

A careful look reveals at least eight defects, and every one of them is an instance of the problems in the previous section:

1. Member redundancy: Marta Alsina's email and phone are written three times. If she changes phone number, three rows have to be corrected (plus those of the other three branches).
2. Book redundancy: the title, author and ISBN of "The Map of Time" are repeated in every loan. With 40,000 copies and years of history, this multiplies the size of the file.
3. Inconsistency in the member's name: "Marta Alsina" and "M. Alsina" are the same person, but no program knows that. Counting distinct members gives the wrong number.
4. Inconsistency in the author: "Ken Follet" and "Ken Follett", "Félix Palma" and "Félix J. Palma". Searching by author returns incomplete results.
5. Undetected wrong data: m.alsina@exemple.org (one letter changed) and the ISBN 978840133909 (one digit missing). Nothing validated those fields when they were typed.
6. A business rule violated: Iván Pereda's loan has a return date (01/04) earlier than the loan date (07/04). That is impossible, and there it is.
7. Inconsistent formatting: "North" and "north"; phone numbers with and without spaces. Any grouping by branch will treat "North" and "north" as different values.
8. Fragmentation: Nuria Bastos's row says "South", but this is the North branch's sheet. The same information lives in two files and nobody knows which one rules.

To this we have to add two problems the table does not show but that happen every day:

• Concurrency: if two people at the front desk open the sheet at the same time, one of the two loans will be lost on save.
• No access control: anybody with access to the shared folder sees the phone numbers and emails of every member.

The solution will consist of moving away from one table that mixes everything together to several tables, each with one responsibility, joined by keys:

With that structure, Marta Alsina's email is written only once; the ISBN of "The Map of Time", only once; the DBMS prevents you registering a loan for a member who does not exist, and a constraint prevents the return date being earlier than the loan date.

How you arrive at that design, how you justify it and how you write it in SQL is exactly the journey of modules 2, 4 and 5. For now, take away the diagnosis: every ill of the spreadsheet has a name of its own and a known solution.

• Confusing "database" with "DBMS". People say "I work with PostgreSQL" when PostgreSQL is the manager, not the database. It is a harmless imprecision in conversation, but it pays to be clear: a single DBMS hosts many databases.
• Believing a database is just a faster store. Its main value is not speed but guarantees: integrity, concurrency and recovery. A small spreadsheet may well open faster; what it cannot do is guarantee anything.
• Thinking redundancy is a space problem. Space is cheap. The problem with redundancy is that it makes inconsistency inevitable: as soon as a piece of data is in two places, sooner or later they will differ.
• Designing the database by copying the spreadsheet as it is. This is temptation number one when migrating. A wide table with 30 columns that mixes everything reproduces exactly the same problems inside the database. Migrating means redesigning.
• Starting to write tables before understanding the domain. Before creating anything you need to know which entities exist and how they relate. Spending an hour talking to the librarians saves weeks of corrections.
• Practical tip: when you analyze an existing system, always do the exercise from section 8. Take a real sample of the data and look for duplicates, contradictory values and broken rules. That inventory is the best possible justification of the project in front of whoever is paying for it.

For each item, say whether it is data, information or knowledge, and justify it briefly:

1. 9788401337208
2. "Copy 3 of The Pillars of the Earth is on loan until 24/04/2026."
3. "The North branch accounts for 45% of the network's loans."
4. Marta Alsina
5. "Members under 25 borrow more comics than the rest."

Based on the description of BiblioRed and the spreadsheet in section 8, list at least four entities that should exist in the database, with three attributes each. Do not design tables or foreign keys yet: just identify the "things" in the domain and their characteristics.

This is another BiblioRed sheet, reservations.xlsx:

List every problem you find, indicating in each case which of the six problems from section 7 it corresponds to.

Solution 1

1. Data. An isolated number; without context we do not even know it is an ISBN.
2. Information. Data (copy, title, date) put into relation and given meaning.
3. Knowledge. It is an aggregated pattern over many loans, useful for deciding (for example, reinforcing staff at North).
4. Data. A text string; on its own it does not say whether it is a member, an author or an employee.
5. Knowledge. A pattern extracted from crossing two dimensions (age and category), directly actionable for the purchasing policy.

Solution 2

One reasonable answer (there are valid variants):

The key point of the exercise is telling Book apart from Copy. "The Pillars of the Earth" is one work, but BiblioRed has six physical copies spread across branches. What gets lent out is a copy, not a book. Confusing the two is one of the most frequent modeling mistakes in library systems, and you can already sense it in the spreadsheet: there is no way of knowing which copy Marta took home.

Solution 3

Notice that problem 6 is especially serious: it is not a formatting error, it is that the system has no idea what the real status of the reservation is. That is the moment an organization discovers it needs a database.

In this lesson we have laid the conceptual foundations of the course:

• We distinguished data, information and knowledge: the database stores the first so that obtaining the second and the third is cheap.
• We defined database (a structured, persistent, shared collection of interrelated data) and DBMS (the software that manages it, guaranteeing definition, manipulation, security, concurrency and recovery).
• We fixed the vocabulary we will use throughout the course: entity, attribute, table, row, column, primary key and foreign key, and we separated schema (the structure) from instance (the data as it is right now).
• We identified the six problems a database solves: redundancy, inconsistency, lack of integrity, lack of concurrency control, lack of security and lack of data independence.
• We applied all of that to BiblioRed, diagnosing a real spreadsheet and anticipating —without designing it yet— the structure of several related tables that will replace it.

We now know what a database is and why we need one. The natural next question is: are all databases the same? The answer is no, not remotely. In lesson 01-02, Types of Databases, we will tour the big families —relational, document, key-value, columnar, graph, and several more— to understand what data structure each one proposes, which problem it was born to solve and how you decide which one suits you. And we will come back to BiblioRed to ask which parts of its system call for a relational database and which might call for something else.