Databases and database systems
Data is a set of recorded facts: a laptop serial number, a hire date, the status of a help desk ticket. A database is an organized collection of related data, stored so that people and programs can find it, change it and protect it. Information is data put in context so someone can act on it, for example: the help desk closed 312 tickets in March, 14 percent more than in February.
A database management system (DBMS) is the software that creates and runs databases. It stores the data on disk, answers requests, stops two users from overwriting each other, enforces rules, controls who can see what, and recovers after a crash. MySQL, PostgreSQL, Microsoft SQL Server, Oracle Database and SQLite are relational DBMSs. MongoDB and Redis are non-relational (NoSQL) systems. A database application is a program built on top of a database, such as a ticketing system or a payroll system.
Many teams start with a spreadsheet. That works for one person and a few thousand rows. It breaks down as soon as the data is shared and important.
| Need | Spreadsheet | DBMS |
|---|---|---|
| Many people editing at once | Conflicting copies; the last save wins | Locking and transactions keep every change consistent |
| Rules on the data | Anyone can type anything in any cell | Data types, keys and constraints reject bad data |
| Security | Whoever has the file sees everything | Each user gets only the permissions granted |
| Size | Excel stops at 1,048,576 rows per sheet | Millions or billions of rows, found quickly with indexes |
| Recovery | Whatever was last saved | A log and backups restore a consistent state |
People work with a database in different roles. The same person can hold more than one role in a small company.
| Role | What the role does | Help desk example |
|---|---|---|
| Database administrator (DBA) | Secures the database, manages user accounts and permissions, runs backups, tunes performance | Grants the reporting team read-only access |
| Database designer | Decides the structure: tables, columns, keys, relationships and rules | Designs the Ticket and Device tables |
| Database programmer | Writes programs and queries that read and change the data | Builds the screen that opens a new ticket |
| Database user | Uses an application or simple queries to get work done | A technician who updates ticket status |
Inside a DBMS, a few components share the work. You do not need to know how they are programmed, but you should know what each one is responsible for.
| Component | Responsibility |
|---|---|
| Query processor | Reads each SQL statement, checks its syntax and the user's permissions, and passes it to the optimizer, which picks the fastest way to run it (for example, use an index instead of reading the whole table) |
| Storage manager | Turns the chosen plan into reads and writes of blocks on disk, using indexes to find rows |
| Transaction manager | Makes sure each transaction finishes completely or not at all, keeps concurrent users from interfering, and restores a consistent state after a failure |
| Log | A file that records every insert, update and delete, written before the change itself, so work can be redone or undone after a crash |
| Catalog (data dictionary) | Metadata: data about the data, such as table names, columns, data types, keys, indexes and permissions |
A transaction is a group of statements that must succeed or fail as one unit. Moving a laptop from one employee to another is a transaction: take it off the first employee's record and put it on the second. If the system stopped halfway, the laptop would belong to nobody. Transactions follow four rules, known as ACID.
| Property | Meaning | Example |
|---|---|---|
| Atomicity | All statements in the transaction take effect, or none do | Both halves of the laptop transfer happen, or neither |
| Consistency | A transaction moves the database from one valid state to another; every rule still holds afterward | No device ends up assigned to an employee who does not exist |
| Isolation | Concurrent transactions do not see each other's unfinished work | A report running during the transfer sees the laptop in one place, never in two |
| Durability | Once committed, a change survives crashes and power loss | After the commit, a reboot does not undo the transfer |
ACID in plain words: all or nothing, rules still hold, work in private, done stays done.
Most business systems are relational: data lives in tables that are linked by keys and queried with SQL. NoSQL systems give up some of that structure in exchange for scale or flexibility, and they are common for very large, fast-changing or loosely structured data.
| Model | Stores data as | Products | Typical use |
|---|---|---|---|
| Relational | Tables of rows and columns linked by keys | MySQL, PostgreSQL, SQL Server, Oracle | Orders, payroll, inventory, tickets |
| Key-value | A key and a value the database does not inspect | Redis, Amazon DynamoDB | Caching, user sessions, shopping carts |
| Document | JSON-like documents that can differ from one another | MongoDB | Product catalogs, content, user profiles |
| Wide-column (column family) | Rows with flexible groups of columns spread across many servers | Apache Cassandra | Very large, write-heavy data such as logs and telemetry |
| Graph | Nodes and the edges that connect them | Neo4j | Social networks, fraud rings, network maps |
Big data is usually described by volume (how much), velocity (how fast it arrives) and variety (how many shapes it takes). A question that describes huge, fast, varied data, such as sensor feeds or website click streams, is pointing toward NoSQL or a distributed system, not a spreadsheet or a single small server.
Word bank
All definitions →- ACID
- Atomicity, consistency, isolation, durability: the guarantees a transaction provides.
- Database
- An organized collection of related data.
- Database management system (DBMS)
- Software that creates, runs, secures and recovers databases.
- Metadata
- Data that describes other data, such as column names and data types; stored in the catalog.
- Transaction
- A group of statements that must all succeed or all fail.
Knowledge check
Which DBMS component decides the most efficient way to run a query, such as using an index?
A server loses power halfway through a transaction that moves a device between two employees. After the restart, neither change is present. Which ACID property is at work?
Who would normally grant a reporting team read-only access and schedule nightly backups?