What is a database

What is a database

A database is an organised collection of data that a program can store, find and change quickly and safely. Almost every app you use keeps its data in one: your messages, your bank balance, the songs in a playlist, the items in a shopping basket.

How data is stored in a database

Most databases you will meet store data in tables. A table looks like a spreadsheet sheet with stricter rules. Here is a table called books:

• A table holds one kind of thing: books, customers, orders.
• The id column is the primary key: a value that is different for every row, so each book can be found without confusion, even if two books share a title.

What is a row, column and cell

A table is a grid, and each part of the grid has a name. Using the books table above:

How to work with a database

You talk to most databases in SQL (Structured Query Language). A request is called a query. This one asks for books published before 1950, oldest first:

-- Books published before 1950, oldest first
SELECT title, year
FROM books
WHERE year < 1950
ORDER BY year;

The database answers with a new, smaller table:

It reads almost like English:

• SELECT title, year picks the columns you want back.
• FROM books names the table to look in.
• WHERE year < 1950 keeps only the rows that match.
• ORDER BY year sorts the result.

What is a DBMS

A database management system (DBMS) is the software that runs a database. The database is the data itself; the DBMS is the program that looks after it. Your app never opens the data files directly: it sends a query to the DBMS, and the DBMS sends back the answer.

A DBMS does four jobs for you:

• Stores the data on disk in a layout built for fast lookups.
• Runs your queries, working out the quickest way to answer each one.
• Keeps the data correct, refusing anything that breaks the rules you set and never leaving a change half done.
• Controls access, so each user or app can only read and change what it is allowed to.

Popular ones you will hear about often, with where to install them:

• SQLite keeps a whole database in a single file. It is built into every Android phone and iPhone, and it is the easiest place to start.
• PostgreSQL and MySQL run as servers that many apps connect to at once. They power a large share of the web.

The relational ones store data in tables and speak SQL. MongoDB stores data as documents and Redis as key-value pairs; you will meet those kinds later on.

Words to know