Skip to content
Tutorials Center
My learning

Topics

Lesson 46 of 55 ยท Working with Computers

What Is a Database?

Learn what a database is: tables, rows and columns, primary and foreign keys, what a DBMS does, why databases beat spreadsheets for shared data, and a first taste of SQL you can run.

Almost every app you use is really a database with a friendly face. A shop keeps its products, customers and orders in a database. A bank keeps your balance there. A social network keeps your posts, friends and likes. When you search, log in or place an order, a program reads or changes records in a database in a fraction of a second.

A database is an organized collection of data stored so that it can be found, changed and protected easily. This lesson explains the vocabulary, shows how data is arranged in tables, and runs your first few queries in SQL. It continues from Spreadsheets and Data Basics, where you saw why a spreadsheet eventually runs out of room.

Why not just use files?

You could store customer data in a text file or a spreadsheet. As the data grows, problems appear:

  • The same information is repeated (a customer's address copied into every order) and gets out of step when it changes.
  • Two people editing the file at once can overwrite each other.
  • Finding one record among millions means reading the whole file.
  • Nothing stops someone from typing "banana" into a price column.
  • A crash halfway through an update can leave the file half-changed.

A database system solves these: it stores data in a structured way, enforces rules, lets many users work at the same time, finds data quickly with indexes, and keeps changes safe.

Tables, rows and columns

The most common kind of database is relational. Data lives in tables, like sheets with strict rules.

id name city email
1 Ana Lisbon [email protected]
2 Ben Oslo [email protected]
3 Chloe Lagos [email protected]
  • A table holds one kind of thing (customers, products, orders).
  • A row (record) is one item: one customer.
  • A column (field) is one attribute, with a data type (whole number, text, date, true/false).

Each table has a primary key: a column (often id) whose value is unique for each row, so a row can always be identified exactly. A foreign key is a column that refers to another table's primary key; it is how tables are linked. An orders table has a customer_id column pointing to a row in customers. Now a customer's address is stored once, and each order only points to it.

erDiagram
  CUSTOMERS ||--o{ ORDERS : places
  ORDERS ||--|{ ORDER_ITEMS : contains
  PRODUCTS ||--o{ ORDER_ITEMS : "appears in"
  CUSTOMERS {
    int id PK
    text name
    text city
  }
  ORDERS {
    int id PK
    int customer_id FK
    date ordered_on
  }
  PRODUCTS {
    int id PK
    text name
    real price
  }
  ORDER_ITEMS {
    int order_id FK
    int product_id FK
    int quantity
  }

A small shop database: lines show how foreign keys connect the tables.

The database management system (DBMS)

You rarely touch the data files directly. A database management system (DBMS) is the program that stores the data and answers requests. Examples: SQLite (a tiny library inside phones and browsers), MySQL, PostgreSQL, SQL Server and Oracle (servers for many users), and non-relational systems such as MongoDB and Redis. A DBMS provides:

  • Storage and fast lookup (indexes).
  • A query language to ask for data.
  • Integrity rules: types, uniqueness, foreign keys, required values.
  • Concurrency: many users at the same time without clashes.
  • Transactions: a group of changes that all succeed or all fail. Moving money between accounts must not subtract from one and then crash before adding to the other.
  • Security and backups: who can see or change what, and how to restore data.

An application talks to the DBMS, which talks to the files on disk: the user never sees the database directly.

SQL: the language of databases

SQL (Structured Query Language, often said "sequel") is the standard language for relational databases. You say what you want, not how to find it. The four basic actions are sometimes called CRUD: Create, Read, Update, Delete, which in SQL are INSERT, SELECT, UPDATE and DELETE.

Python Your first SQL: create, insert, select

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice the last line: the database refused a row without a name because the column is marked NOT NULL. A spreadsheet would accept anything.

Linking tables with a join

Because customers and orders are separate tables, a join combines them when you need both. The next example stores each customer once and lists their orders.

Python Two tables, one question

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Chloe appears with a total of 0 because we used a LEFT JOIN, which keeps customers that have no orders. Joins, grouping and many more tools are covered in the SQL tutorial.

Transactions: all or nothing

A transaction is a group of steps treated as one unit. If anything fails, the database rolls back to the state before the transaction began. This matters whenever several changes belong together.

Python A transfer that fails halfway is undone

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The second transfer first added 500 to Ben, then failed when Ana's balance would go negative. The rollback undid Ben's credit, so no money appeared from nowhere.

Other kinds of database

Relational databases are the default, but not the only choice.

Type Stores Examples Good for
Relational (SQL) Tables with relationships PostgreSQL, MySQL, SQLite Most business data
Document Flexible JSON-like documents MongoDB Varied, nested records
Key-value A value for each key Redis Caches, sessions, counters
Graph Nodes and connections Neo4j Social networks, recommendations
Search Indexed text Elasticsearch, Meilisearch Fast text search

Large systems often use several together. If you plan to build web applications, learning SQL is one of the best investments you can make. Related background: Files, Folders and Organization shows how a file system organizes data in a different way.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is a primary key?

    A primary key is unique for every row, so a row can always be identified exactly.
  2. 2. How do tables in a relational database link to each other?

    A foreign key such as orders.customer_id refers to customers.id, so related data is stored once and connected.
  3. 3. What does a DBMS do?

    A database management system provides storage, a query language, integrity rules, concurrency, transactions and security.
  4. 4. Which SQL statement reads data?

    SELECT retrieves rows; INSERT, UPDATE and DELETE change them.
  5. 5. What guarantee does a transaction give?

    A transaction is all-or-nothing: if any step fails, the database rolls back to its earlier state.

Summary

  • A database is organized data that can be searched, changed and protected; most apps are built on one.
  • A relational database stores data in tables of rows and columns with typed columns.
  • A primary key identifies each row; a foreign key links a row to another table, so facts are stored once.
  • A DBMS (SQLite, MySQL, PostgreSQL, ...) adds storage, queries, integrity rules, concurrency, transactions, security and backups.
  • SQL describes what you want: SELECT, INSERT, UPDATE and DELETE, with JOIN combining tables.
  • A transaction is all-or-nothing, which keeps related changes consistent; other database types (document, key-value, graph) suit other needs.

Next: Project: A Binary Calculator, the first real-world project.