In the first lesson we defined the DBMS from the outside: the software that sits between the applications and the files on disk. The time has come to open the box. Understanding what is inside a database manager is not a theoretical luxury: it is what will later let you understand why one query takes ten milliseconds while an almost identical one takes thirty seconds, why indexes exist, what a transaction really guarantees and what is going on when something blocks.

The lesson has two parts. The first is conceptual: the internal components of a DBMS, the complete journey of a query, the three-level architecture that makes data independence possible, the difference between client-server and embedded, and the human roles that work around a database. The second is practical: by the end of it you will have PostgreSQL and SQLite installed, you will know how to connect to both and you will have created the biblioredb database, where all the SQL for the rest of the course will run. Do not leave this lesson half-done: module 2 starts by writing SQL, and you will need the environment ready.

1. The internal components of a DBMS
2. The journey of a query, step by step
3. The three-level ANSI/SPARC architecture
4. Logical and physical data independence
5. Client-server versus embedded database
6. Human roles around a database
7. Installing PostgreSQL
8. First steps with psql and creating biblioredb
9. Installing and first steps with SQLite
10. Alternatives: Docker and online consoles
11. Common mistakes and tips
12. Exercises
13. Conclusion

A modern DBMS is a complex system, but it is organized into a fairly stable set of components, common to PostgreSQL, MySQL, Oracle and SQL Server (SQLite has them all too, though in a reduced form).

This is the front door. It receives the client's connection, authenticates the user (password, certificate, operating system) and assigns them a session. From then on, every operation goes through authorisation checks: verifying that this user has permission on that table and that operation.

In BiblioRed, this component is what will let the front-desk user insert loans but not delete members. Permissions in detail are covered in lesson 06-04.

The brain of the system. It receives a query in SQL —a piece of text stating what is wanted— and produces an execution plan stating how to get it. Its phases:

• Syntactic analysis (parser): checks that the SQL is well written and turns it into a tree.
• Semantic analysis: verifies against the catalog that the tables and columns exist and that the types fit.
• Rewriting: applies equivalent transformations (expanding views, simplifying conditions).
• Optimization: generates several possible plans, estimates the cost of each one using statistics about the data and picks the cheapest.
• Execution: runs through the chosen plan and produces the rows.

This piece is the direct inheritance of System R (lesson 01-03) and it is what allows SQL to be declarative.

This is the part that knows how the data really sits on disk: in which files, organized into pages or blocks (usually 4 or 8 KB), in which row format, and which indexes exist to reach a specific row sooner. It offers the rest of the system elementary operations: "give me row X", "scan this table", "search this index".

Reading from disk is orders of magnitude slower than reading from memory. The buffer manager keeps the most-used pages in RAM and decides which ones to evict when space runs short. It is responsible for the second query on something being far faster than the first.

In PostgreSQL this space is called the shared buffers; in practice, a properly sized server serves the vast majority of reads from memory.

It guarantees that a set of operations is applied in full or not at all, even if the power is cut halfway. It rests on two mechanisms:

• The write-ahead log (WAL): before modifying the data, what is about to happen is written to a sequential log. If the system goes down, that log is re-read on startup and a consistent state is rebuilt.
• Concurrency control: locks or versioning (PostgreSQL uses MVCC, Multi-Version Concurrency Control) so that many simultaneous sessions do not corrupt each other.

This is what stops two BiblioRed front desks lending the same copy at the same time. Transactions and isolation levels are the content of lessons 06-01 and 06-02.

The database about the database itself: which tables exist, with which columns, types, constraints, indexes, views, users and permissions. And also statistics about the data (how many rows each table has, how the values are distributed), which the optimizer uses to decide.

The interesting part is that in a relational system the catalog is just normal tables, queryable with SQL like any other. In PostgreSQL it lives in the information_schema schema and in the pg_catalog tables.

Around the core there are tools for bulk loading, backup and restore, replication and monitoring. They are usually standalone programs (pg_dump, pg_restore, psql).

Let's follow the complete route with a specific BiblioRed query. You do not need to understand the syntax yet —that is module 2—; what matters is the path.

In plain language: "tell me which loans are still open at the North branch, with the member's name and the book's title".

Let's walk through the interesting decisions:

1. Connection and authentication. The client opens a session. In PostgreSQL, the main process spawns a dedicated process to serve it.

2. Parser. If you typed SELCT instead of SELECT, it stops here with a syntax error. No data has been looked at yet.

3. Semantic analysis. The catalog is consulted: does the loans table exist? does it have a return_date column? is c.branch_id comparable with the number 2? "Column does not exist" errors are born here.

4. Rewriting. If loans were a view, it would be replaced by its definition.

5. Optimization. Here is the meaty part. The system has to decide, among many options:

   • In which order should the four tables be joined? Joining loans with copies first and filtering by branch may leave 300 rows; starting with books would leave 40,000. The order changes the execution time by orders of magnitude.
   • Scan the whole table (sequential scan) or use an index? If there are only 300 open loans among a million historical rows, an index wins; if 80% of the table has to be read, scanning it all is faster.
   • Which join algorithm? Nested loop, merge join or hash join, depending on the sizes.

   These decisions are taken using the statistics in the catalog. That is why a database with stale statistics chooses badly. In lesson 06-03 you will learn to read the chosen plan with EXPLAIN.

6. Execution. The executor runs the plan asking for pages. Every request goes through the buffer manager: if the page is in memory, it is served instantly; if not, it is read from disk and cached.

7. Transactional visibility. Every candidate row is checked against the transaction manager: under MVCC, a row modified by a transaction that has not committed yet is not visible to this session. That is how you read without blocking whoever is writing.

8. Permissions and result. It is verified that the user can read those tables and the rows travel back to the client.

The idea to take away: you write the what; the DBMS decides the how, and that decision is where performance is won or lost. It is exactly the independence Codd proposed, in action.

In 1975, the ANSI/X3/SPARC committee proposed a framework for organizing any DBMS into three levels of abstraction. It is still the conceptual reference used to explain databases, because it describes why one layer can change without breaking the others.

What each type of user sees. There is not just one: there are as many as there are usage profiles. Each external view shows a subset of the data, perhaps reorganised or computed, and hides the rest.

In BiblioRed:

• The front desk sees the loans and members of its branch, with the contact details needed to chase a return.
• The public web catalog sees title, author, cover and availability. It does not see who has each copy on loan: that would be a privacy breach.
• Management sees aggregated totals by branch and month, with no member names.

All three profiles are looking at the same data, presented differently.

The complete and single description of what data the database contains: all the entities, all the attributes, all the relationships and all the integrity constraints. It is independent of who uses them and of how they are stored.

This is the level the designer works at, and the one we will produce in module 4 with entity-relationship diagrams and their transformation into a relational schema.

How it is actually stored: which files there are, how rows are grouped into pages, which indexes exist and of what type, what is compressed, on which disk each table lives.

It is the responsibility of the administrator and of the DBMS itself, and the ordinary user should not need to know it in order to write correct queries (they do need it to write fast ones, hence lesson 06-03).

The reason the three levels exist is the two independences. They are a DBMS's most valuable property, and the one that was entirely missing from the pre-relational models.

The internal level can be changed without touching the conceptual level or the applications.

Examples in BiblioRed:

• The administrator creates an index on loans.loan_date because the monthly reports are slow. No query changes; they simply start running faster.
• The historical loans table is moved to a different disk, or compressed. The applications never notice.
• The server is migrated to different storage. The SQL stays the same.

This independence is very solid in today's DBMSs: it is practically total.

The conceptual level can be changed without touching the external views or the applications that use them.

Examples in BiblioRed:

• It is decided to split the members table into members and member_contacts to separate the personal data. If the applications query a view called members that brings both tables together, they keep working with no changes.
• A preferred_language column is added to members. No existing application is affected, because none of them asked for it.
• The reservations entity is added, new to the system. The loans applications do not even notice.

This independence is harder to achieve than the physical one, and you only get it if you have had the discipline to make applications go through views instead of straight to the tables. It is a design decision, not a gift from the system.

The two managers used in this course represent the two extremes of deployment architecture, and comparing them clarifies a great deal.

The DBMS is a standalone process —often on another machine— listening on a network port (5432 in PostgreSQL). Clients connect over the network, send SQL and receive results.

It implies:

• Real concurrent access from many machines, managed by the server.
• Centralized access control: users, roles and permissions.
• Administration: it has to be installed, started, configured, upgraded and backed up (or you pay somebody to do it, as we saw with managed services in lesson 01-03).
• Network latency on every operation, usually negligible but not zero.

There is no server. The engine is a library linked inside the program itself, and the whole database is one file on the local disk. When your application queries, it does not talk over a network: it calls a function.

It implies:

• Zero administration: no service, no port, no users. Copying the database is copying a file.
• Minimal latency: no network and no serialization between processes.
• Limited concurrency: many simultaneous readers yes, but only one writer at a time on the file.
• No access control of its own: security is the file system's. Whoever can read the file reads everything.

Neither is better than the other: they solve different problems. SQLite is by far the most widely deployed database engine in the world (it is in every phone, every browser and every aeroplane), and it does not compete with PostgreSQL: it competes with fopen().

For BiblioRed in production the choice is PostgreSQL, because there are four branches writing at the same time and personal data to protect. For learning, SQLite is perfect, which is why we will keep it as an alternative throughout the course.

In a small organization one person plays all three parts; in a large one they are separate teams. It is worth knowing what is expected of each.

Responsible for the system working, being secure and not losing data:

• Installing, configuring and upgrading the DBMS.
• Defining users, roles and permissions.
• Planning and testing backups and restores (an unverified backup is not a backup).
• Monitoring performance, tuning configuration and creating indexes.
• Planning capacity, replication and disaster recovery.

In BiblioRed this would be the person who makes sure the central branch's server is backed up every night and that staff only see what they are entitled to.

Responsible for the schema and the queries being correct and efficient:

• Designing tables, relationships and constraints (modules 4 and 5).
• Writing and optimizing SQL queries.
• Managing schema migrations under version control.
• Integrating the database with the application.

This is the role this course prepares you for most directly.

Responsible for extracting information from the existing data:

• Writing analytical and aggregation queries (lesson 02-05).
• Building reports and dashboards.
• Spotting data quality problems.

In BiblioRed, the person who answers "which genres should we reinforce at the South branch?".

A fourth role, the data architect, decides in large systems which technologies are used and how they are integrated; this is the person who would take the decisions from lesson 01-02.

From here on, let's get to work. Pick your operating system.

postgresql installs the server; postgresql-contrib adds useful extensions. On Debian and Ubuntu the service starts on its own after installation.

The graphical alternative is Postgres.app, which you download, drag to Applications and start with a click. It is the most convenient option if you would rather not use Homebrew.

1. Download the EDB installer from postgresql.org/download/windows.
2. Run it and accept the default options, writing down the password you set for the postgres user: you will need it to connect.
3. Leave pgAdmin ticked if you want a graphical interface, and Command Line Tools (essential, it includes psql).

Afterwards, from the start menu, open SQL Shell (psql) and press Enter at each prompt until the password.

On any system, verify that the client responds:

If the command is not found on macOS with Homebrew, add the directory to your PATH:

psql is PostgreSQL's command-line client. It is the tool we will use throughout the course: it is the most direct one and it is available on any server.

On Linux, the installation creates a system user called postgres that is the database superuser:

On macOS with Homebrew, your own user is usually a superuser:

On Windows, use the SQL Shell (psql) shortcut.

You will see a banner like this:

The postgres=# prompt tells you: current database postgres, and # means superuser (a > would indicate a normal user).

Notice two things: the statement ends in a semicolon (without it, psql will wait for more text), and the result is presented as a table with the row count at the end.

Always working as a superuser is bad practice. Create a user of your own:

CREATEDB lets it create databases, which is what we need.

That terse reply is the confirmation that it worked. biblioredb is the database where we will run all the SQL in the course: the members, books, copies and loans tables we design from module 2 onwards will live here.

Commands starting with a backslash are not SQL: they are instructions for the psql client and they take no semicolon.

Let's try them out:

There is biblioredb. The template0 and template1 databases are internal system templates: leave them alone.

The prompt has changed to biblioredb=#: we are inside. And \dt says there are no tables, which is correct: the database is empty and we will fill it in module 2.

For your next sessions, save steps by connecting in one go:

• -U student: user.
• -d biblioredb: database.
• -h localhost: server (needed so that it asks for a password instead of using system authentication).

You can also run a query without entering the interactive session:

SQLite is plan B (and sometimes plan A) for practising: there is no server to start.

On Windows, download the sqlite-tools package from sqlite.org/download.html, unzip it into a folder (for example C:\sqlite) and add that folder to your PATH, or simply run sqlite3.exe from there.

In SQLite, creating a database means creating a file. There is no CREATE DATABASE:

One important detail: the file is not created on disk until you write something inside it. If you quit without creating any table, there will be no file.

Here the client's commands start with a dot, not a backslash:

The first two are the ones you should always run when you open sqlite3, because the default output is very bare:

Empty, as expected.

To have .headers on and .mode box applied every time, create a .sqliterc file in your home folder with those two lines.

The difference in typing is the most relevant one for learning: SQLite is permissive and will let through things PostgreSQL would reject. If you practise in SQLite, be aware that PostgreSQL will be stricter — and that strictness is a virtue, not an obstacle, as we saw in lesson 01-01.

If you cannot or do not want to install anything on your machine, there are two perfectly valid ways out.

Spin up a disposable PostgreSQL in a container:

What each option does:

• --name pg-biblioredb: container name, so you can refer to it later.
• -e POSTGRES_PASSWORD=...: password for the postgres user (mandatory).
• -e POSTGRES_DB=biblioredb: creates the course database at startup.
• -p 5432:5432: exposes the port on your machine.
• -d: in the background.

To get in with psql without installing it locally:

Managing the container:

Keep that last warning in mind: without a mounted volume, deleting the container deletes the database.

Without installing anything at all, they are good enough to follow the course and do the exercises:

• db-fiddle.com and sqliteonline.com: they let you choose PostgreSQL or SQLite and run SQL in the browser.
• pgexercises.com: PostgreSQL with built-in exercises.

Their limitation is that they do not persist between sessions: save your scripts in a separate .sql file. For module 2 they are enough; to work comfortably from module 4 onwards, a local installation or Docker is preferable.

If you prefer a visual interface, pgAdmin (it comes with the Windows installer), DBeaver (cross-platform, works with PostgreSQL, SQLite and MongoDB) or DB Browser for SQLite are good options. Recommendation: learn with psql and sqlite3 first. The command line forces you to understand what is happening and it is available on any server you connect to.

• Forgetting the semicolon in psql. If you type SELECT version() and press Enter, the prompt changes to postgres-# and it looks like it has hung. It has not: it is waiting for you to finish the statement. Type ; and Enter.
• Confusing metacommands with SQL. \dt takes no semicolon and only works in psql; .tables only works in sqlite3. They are not part of the SQL language and they do not work from an application.
• psql: FATAL: role "your_username" does not exist on Linux. It happens when you run a bare psql: PostgreSQL tries to authenticate you with your system user name, which does not exist as a role. Fix: sudo -u postgres psql and create your user, as in section 8.
• could not connect to server. The server is not running. Check with sudo systemctl status postgresql (Linux) or brew services list (macOS) and start it if needed.
• Believing SQLite creates the file when you open it. It only materialises when you write something. If ls does not show biblioredb.sqlite, it is not a failure: you just have not created any table yet.
• Practising only in SQLite and getting a surprise. SQLite accepts text in an INTEGER column; PostgreSQL does not. If the goal is to work with PostgreSQL, practise in PostgreSQL whenever you can.
• Always working as a superuser. It works, but it teaches you nothing about permissions and in a real environment it is dangerous. Creating a student user costs one line.
• Tip: save your queries in .sql files from day one, and run them with psql -f script.sql or with .read script.sql in SQLite. Having your work in version-controllable files is the difference between practising and building something.

For each situation, state which DBMS component is mainly involved and at which ANSI/SPARC level the change sits (where applicable):

1. You type SELCT * FROM members; and get a syntax error.
2. You type SELECT name FROM membres; and get "relation does not exist".
3. The same query takes 400 ms the first time and 8 ms the second.
4. The administrator creates an index and a report goes from 30 s to 0.2 s, without changing the query.
5. The power is cut in the middle of a loan and, on startup, the database is consistent.
6. The front-desk user runs DELETE FROM members; and gets "permission denied".
7. The members table is split in two, but the applications keep working thanks to a view.

Carry out these checks and note down the output of each one:

1. Check the psql version from the terminal.
2. Connect to PostgreSQL and show the server version with SQL.
3. Create the biblioredb database if you do not have it yet, and check with a metacommand that it appears in the listing.
4. Connect to biblioredb and check that it has no tables.
5. With SQLite, create biblioredb.sqlite, turn on headers and box mode, and show the version.
6. In psql, find out which database and user you are connected as using a single metacommand.

For each scenario, choose between PostgreSQL (client-server) and SQLite (embedded), and justify it in two sentences:

1. BiblioRed's real system, with four branches recording loans simultaneously.
2. A mobile app that lets members check their history with no internet connection.
3. The automated tests for the BiblioRed application, which have to create and destroy a clean database on every run.
4. A management dashboard accessed by eight people from different buildings.
5. A desktop program a librarian uses to prepare the annual inventory on their laptop.

Solution 1

Solution 2

biblioredb should appear in the listing.

If all six steps work, you have the course environment ready.

Solution 3

With this lesson we close the introductory module, and we do so with the working environment up and running. We have seen:

• The internal components of a DBMS: connection manager and access control, query processor and optimizer, storage engine, buffer manager, transaction and recovery manager, and data catalog.
• The complete journey of a query, from syntactic analysis to the rows returned, with cost-based optimization as the decisive piece for performance.
• The three-level ANSI/SPARC architecture —external, conceptual and internal— and the two data independences: physical (changing the storage without touching the queries) and logical (changing the schema without breaking the applications that use views).
• The difference between client-server and embedded, with PostgreSQL and SQLite as the representatives of each extreme, and why BiblioRed will use the first in production and the second for practising.
• The human roles: administrator, developer and analyst.
• And, in practice: installing PostgreSQL and SQLite on Linux, macOS and Windows, connecting with psql and sqlite3, checking versions, creating the biblioredb database and handling the basic metacommands (\l, \c, \dt, \d, .tables, .schema), plus the alternatives with Docker and online consoles.

We now know what a database is and what problems it solves, which families exist and how to choose between them, where all of this comes from, what happens inside the manager and how it is organized. And, above all, we have biblioredb waiting, empty. In module 2, Relational Databases, we start filling it: lesson 02-01, The Relational Model, formalises exactly what a relation is, what primary and foreign keys are and which integrity rules govern the model; from there, lesson 02-02 introduces SQL and we will write the first real statements over BiblioRed's members, books, copies and loans tables. The theory ends here; from the next lesson onwards, you type.