Database Fundamentals

A database is an organized collection of data. A database management system, or DBMS, stores that data and controls how applications read and change it.

The distinction matters. The database is the data and its structure. The DBMS is the software that manages storage, queries, concurrent access, permissions, and recovery.

Why databases exist

A plain file can hold data. It becomes difficult to manage when several users update it, records refer to one another, or a failed change must be undone.

A DBMS gives you shared rules around the data:

• a schema describes its structure;
• constraints reject invalid states;
• queries retrieve and combine records;
• transactions group related changes;
• permissions limit who can perform each operation;
• backup and restore procedures protect against data loss.

These features make a database useful as a system of record. An application can change while the data keeps a defined shape and meaning.

The relational mental model

A relational database organizes data into tables. Each table has named columns and rows. A column has a data type. A row holds one occurrence of the thing the table represents.

Consider an online store:

customers
customer_id | name  | email
------------+-------+-----------------
101         | Amina | amina@example.com

orders
order_id | customer_id | placed_at
---------+-------------+------------
5001     | 101         | 2026-07-15

The customer_id column identifies a customer. The same value in orders connects an order to that customer. The tables store separate facts, while the keys preserve their relationship.

SQL is the language commonly used to define and work with relational data. Its operations include creating tables, inserting rows, selecting rows, updating values, and deleting rows. A join combines related rows from multiple tables for one result.

SQL does not guarantee row order unless a query requests an order. Treat an unsorted result as unordered, even if repeated runs happen to look stable.

Model the facts before the screens

A schema should represent durable facts and relationships. Start by naming the entities your system tracks. Then identify each entity's attributes, identifier, and relationships.

For the store, customers and orders are entities. A customer can place many orders. Each order belongs to one customer. This is a one-to-many relationship.

A many-to-many relationship needs an intermediate table. An order can contain many products, and a product can appear in many orders. An order_items table connects them and can also hold quantity and price-at-purchase.

Normalization organizes relational data to reduce redundant facts and inconsistent dependencies. Store a customer address once with the customer instead of copying it into every order. This reduces the number of places that must change when the address changes.

Normalization is a design tool, not a contest. A deliberate duplicate can serve a measured read pattern or preserve historical facts. Document why the duplicate exists and how it remains consistent.

Keys and constraints protect meaning

A primary key uniquely identifies each row and cannot be null. A foreign key requires a value to match a key in a referenced table. This rule preserves referential integrity.

Other constraints express local rules:

• NOT NULL requires a value;
• UNIQUE prevents duplicate values or value combinations;
• CHECK requires a Boolean condition;
• a data type limits the kind of value a column accepts.

Put stable rules in the database when the database can enforce them. Application validation improves feedback, but it does not protect data written by another application, an import, or an administration tool.

Null means missing or unknown. It is not zero or an empty string. Comparisons involving null use SQL's three-valued logic, so test for null explicitly.

Queries turn stored facts into answers

A query states the result you want. The DBMS chooses an execution plan that can produce it.

Filtering limits rows. Projection chooses columns. A join combines related tables. Aggregation summarizes groups. Sorting defines result order.

This separation is useful. You describe the answer without prescribing every storage step. The query planner can use statistics and indexes to choose an access path.

An index is a separate structure that helps the DBMS find rows without scanning every row. Indexes can speed reads, but they consume space and must be updated when data changes. Add them for observed query patterns, then inspect execution plans to confirm they help.

Transactions protect multi-step changes

A transaction treats several statements as one unit. A commit makes its changes durable. A rollback discards its changes.

Imagine transferring credit between two accounts. Decreasing one balance without increasing the other leaves an invalid result. Put both changes in one transaction so they commit together or roll back together.

The common ACID summary describes four transaction goals:

• Atomicity: the unit completes or has no effect.
• Consistency: enforced rules remain satisfied as a transaction moves the database between valid states.
• Isolation: concurrent transactions have controlled visibility into one another's work.
• Durability: committed changes survive failures covered by the system's guarantees.

Isolation has tradeoffs. Stronger isolation can prevent more concurrency anomalies, but it may increase waiting or transaction retries. Keep transactions focused, understand your DBMS defaults, and test behavior under concurrency.

A database is an operated system

A correct schema is only the beginning. Production work also includes access control, monitoring, capacity planning, migrations, backups, and tested restores.

Grant the smallest set of privileges each role needs. Keep schema changes versioned and reversible where practical. Monitor slow queries and resource use. Back up on a schedule that matches the amount of data the organization can afford to lose.

A backup is useful only if you can restore it. Test restores and record the time they take. Replication can improve availability, but it does not replace an independent recovery plan because unwanted changes can also replicate.

Choosing a database model

Relational databases fit data with clear relationships, constraints, and transactions. Other models serve different access patterns.

• A document database stores records as documents and can allow fields to vary between documents.
• A key-value database retrieves values by key and may provide specialized data structures.
• A graph database represents data as nodes and relationships, which makes connected paths central to the model.

Do not choose from labels alone. Write down the required queries, update patterns, integrity rules, transaction boundaries, scale, latency, and recovery needs. Then compare concrete systems against that workload.

Limits of this course

This course gives you a working map. It does not teach a complete SQL dialect, tune a particular engine, or replace practice with failures and restores.

Your next steps are to learn SQL, design a small relational schema, test constraints and transactions, inspect query plans, and operate a database with least privilege and verified backups.