Introduction to Databases

A database is an organized collection of digital data, stored and managed electronically.

A Database Management System (DBMS) is the software that interacts with users, applications and the database itself to capture, store and manage that data. It is what you actually talk to. It provides the interface for inserting, updating, deleting and retrieving data, and its job is to keep the data consistent, secure and accessible.

There are two main kinds.

Relational Database Management Systems (RDBMS) store data in tables with predefined relationships between them. The usual query language is SQL (Structured Query Language).

Non-Relational Database Management Systems (NoSQL) store data in other shapes: key-value, document, column-family, or graph. They are known for scaling horizontally and for handling unstructured or semi-structured data.

Popular SQL databases include MySQL, PostgreSQL, Microsoft SQL Server and Oracle. Popular NoSQL databases include MongoDB, Redis, Apache Cassandra and Neo4j.

Five Differences Worth Knowing Now

The rest of the chapter goes into each of these. This is the shape of the argument.

1. Storage. SQL stores data in tables, where each row represents an entity and each column represents a data point about that entity. Storing a car, the columns might be Color, Make and Model. NoSQL uses other models: key-value, document, graph and columnar.

2. Schema. In SQL each record conforms to a fixed schema. The columns are decided before data is entered, and each row has data for each column. The schema can be altered later, but that means modifying the whole database and going offline. In NoSQL, schemas are dynamic. Columns can be added as you go, and a row does not have to hold a value for every column.

3. Querying. SQL databases use SQL to define and manipulate data, which is very powerful. NoSQL queries are focused on a collection of documents, sometimes called UnQL (Unstructured Query Language), and the syntax differs from one database to the next.

4. Scalability. SQL databases are usually scaled vertically, by increasing the memory and CPU of the hardware, which gets expensive. Scaling a relational database across multiple servers is possible but challenging and time-consuming. NoSQL databases scale horizontally: add more servers to handle more traffic, on cheap commodity hardware or cloud instances, which is far more cost-effective. Many NoSQL technologies distribute the data across servers automatically.

5. Reliability and ACID compliance. The vast majority of relational databases are ACID compliant (Atomicity, Consistency, Isolation, Durability), which makes them the better bet when transactions must be safe. Most NoSQL solutions trade ACID compliance away for availability, performance and scalability.

💡 The mistake to avoid in an interview is naming a database before naming the access pattern. "Reads are always by user id, writes are heavy, and there are no cross-entity queries" makes the choice almost automatic. Starting from "I would use Cassandra" invites the question you have not answered yet.

Key takeaway: A DBMS is the software between you and the data, and it comes in two families. Relational systems store data in tables with a fixed schema, query it with SQL, scale vertically, and are ACID compliant. Non-relational systems use key-value, document, graph or columnar models with dynamic schemas, scale horizontally on cheap hardware, and usually trade ACID guarantees for availability and performance.

The next lesson, SQL Databases, looks at the relational side properly.