SQL & Database Management

How Relational Databases Work

25 min

A relational database stores data in tables (like spreadsheets with strict rules), links tables with keys, and answers questions with SQL. PostgreSQL, MySQL, SQLite and SQL Server all speak SQL with small dialect differences.

Vocabulary

Term Meaning
Table a set of rows of one kind (users, orders)
Row one record
Column one attribute, with a type (email TEXT)
Primary key a column that uniquely identifies each row (id)
Foreign key a column that points to another table's primary key
Index a lookup structure that makes searches fast
Transaction a group of changes that succeed or fail together

Why not just JSON files?

Databases give you:

  • Integrity: types, NOT NULL, unique constraints and foreign keys stop bad data at the door.
  • Concurrency: many users can read and write safely at the same time.
  • Querying: ask new questions without writing new code.
  • Durability: committed data survives crashes.

Relationships

  • One-to-many: one customer has many orders. orders.customer_id references customers.id.
  • Many-to-many: projects and tags. A join table project_tags(project_id, tag_id) links them.
  • One-to-one: a user and their profile settings (often just extra columns instead).

Choosing a database

Database Good for
SQLite local development, small apps, embedded, tests. A single file, no server.
PostgreSQL the default choice for web apps: robust, feature-rich, free
MySQL / MariaDB widely hosted, WordPress and many PHP apps

PandaDev uses SQLite in development and PostgreSQL in production through SQLAlchemy, which hides most differences.

Try it now

SQLite needs nothing installed beyond Python:

sqlite3 shop.db        # or: python -c "import sqlite3" if the CLI is missing

Or use a browser playground such as sqliteonline.com or DB Fiddle for every example in this track.

Check yourself

  • What is the difference between a primary key and a foreign key?
  • How do you model "projects have many tags, tags belong to many projects"?
  • Why use SQLite for tests but PostgreSQL in production?

Seu progresso fica salvo apenas neste navegador.

Enviar para revisão (10 pontos)