What Are Database Fundamentals? | Learn IT Fundamentals with Teacher Pochi

Key points

Database fundamentals are the knowledge of how to organize and safely store and retrieve large amounts of data — the "shape" your data takes.
The core ideas are "table design," "normalization," and "SQL."
Understanding them lets you read a system's data structure and reason about the causes of bugs or performance problems.

🗂️ Build a container for data Table design (build the table) Normalization (prevent duplication and conflicts)

SQL
(the language for working with data)

💻 Use the data Search, add, update, delete

Teacher Pochi works with data like this every day, too

🛒 Online shopping 📝 Signing up 🔍 Searching products

Table DesignMy order history page lists everything I've ever bought, all neatly organized — how is that even arranged behind the scenes? NormalizationWhy don't I have to retype my address every time I sign up for something new? SQL BasicsHow can it find the exact item I want out of tens of thousands of products in an instant?

What is a "database," anyway?

A database is a mechanism that organizes large amounts of data according to a fixed set of rules so a computer can efficiently store, search, and update it.

Most systems use a "relational database (RDB)," which manages data in the form of "tables." A table is made up of "rows" and "columns," where a single row corresponds to one record of data, and a column corresponds to one type (field) of data.

Scattered
data

Organize it

Table

Search & update

Used by
the app

Teacher Pochi's hintPicture reorganizing a messy pile of loose papers into labeled folders in a filing box. Once everything's sorted, you no longer have to dig around wondering "where did I put that document?"

Why do we need a database?

You could store data in Excel files or plain text files too, but as a system grows larger, problems like these start to appear.

Can't edit at the same timeWhen multiple people open the same file at once, one person's edits are easily overwritten by another's.

Slow searching and aggregationThe more data there is, the longer it takes to find the information you're after.

A database solves thisIt comes with built-in mechanisms for managing concurrent access and searching quickly.

A database is a purpose-built mechanism that shines in exactly these situations — many users, large volumes of data, and the need for fast access.

The basic structure: tables, rows, and columns

A database table can be explained with three terms.

idColumn = a field

1, John Smith, ...Row (record) = one unit of data

users tableTable = the table itself

A "table" is the table itself, a "column" is a type of field such as a name or email address, and a "row (record)" is one complete unit of data — for example, one person's worth of information. We cover this in more detail in the dedicated table design topic.

Teacher Pochi's hintThink of a class roster. The roster as a whole is the "table," field names like "name" and "student number" are the "columns," and each filled-in line for one student is a "row."

Primary keys and foreign keys: connecting tables

A marker used to uniquely identify a single row within a table is called a "primary key." A mechanism that connects two tables by holding one table's primary key inside another table is called a "foreign key."

customers
(primary key: id)

Referenced via customer_id

orders
(foreign key: customer_id)

This mechanism lets you relate multiple tables to each other without repeating the same customer information across tables. We cover this in more detail in the dedicated table design topic.

Teacher Pochi's hintThis resembles a library patron's "member number." Instead of writing out a patron's full name on every checkout record, the library records just the member number — the details can always be looked up in the patron register using that number.

Normalization: an organizing technique that prevents duplication and conflicts

If you cram every piece of information into a single table, the same data ends up being written over and over, which leads to missed updates and inconsistencies. "Normalization" is a design approach that splits data into tables at an appropriate level of granularity to prevent these problems.

One giant table
(full of duplication)

Organize and split it

Multiple tables,
one per role

Teacher Pochi's hintPicture keeping the class roster and each student's personal details on separate sheets — a "class roster" and a "student register" — instead of cramming them onto a single page. We cover this in more detail in the dedicated normalization topic.

SQL: the language for working with databases

SQL is a dedicated language for telling a database to "find," "add," "rewrite," or "delete" data.

SELECTFind and retrieve data

INSERTAdd new data

UPDATERewrite existing data

DELETEDelete data

Behind the scenes of an application, SQL like this is sent to the database every time a screen action happens. We cover this in more detail in the dedicated SQL basics topic.

Where are databases used?

Databases run behind the scenes of nearly all software, from business systems to smartphone apps.

🛒 E-commerce sites Product information Order history

👤 Membership services Member information Login history

🏢 Business systems Inventory management Sales aggregation

It's invisible from the outside, but most of the apps we use every day are accessing a database behind the scenes to work.

Summary

Database fundamentals cover the basic structure of tables, rows, and columns; how primary and foreign keys connect tables together; normalization to prevent duplication; and SQL for operating on data — together, the knowledge of how to hold and shape your data.

You can dig deeper into each of these mechanisms in the dedicated topics below.