What is a database?

What is a database?

Think of a database as a very well organised, very fast filing system. Instead of paper folders, data sits in a structure the computer understands, so it can answer questions like "which customers in Leeds ordered in March?" in a fraction of a second, even with millions of records. Most databases also let many people and programs use the data at once without getting in each other's way.

Tables, records and fields

The most common kind of database stores data in tables, a bit like spreadsheets. Here is a small customers table for an online shop:

• Table: all the data about one kind of thing, here customers.
• Record (row): one customer, such as Tom Ellis.
• Field (column): one piece of information stored for every record, such as email. Each field has a data type, such as text, a whole number or a date.
• Primary key: a field that never repeats, so it picks out exactly one record. Here it is customer_id, because two customers could share a name.

These are the terms the AQA GCSE Computer Science specification expects: table, record, field, data type, primary key and foreign key.

What is database software?

The data on its own is just bytes in files. Database software, properly called a database management system (DBMS), is the program that stores those files, answers questions about them and keeps them safe. When an app saves your order, it doesn't write to the files itself; it sends a request to the DBMS. The DBMS:

• finds data quickly using indexes, like the index at the back of a book;
• lets many users read and write at the same time without mixing up their changes;
• checks rules, such as "every order must belong to a real customer";
• controls who can see or change what;
• makes backups and recovers from crashes without losing finished work.

You probably use SQLite every day without knowing it. Its developers say it is "likely used more than all other database engines combined" and estimate that over one trillion SQLite databases are in active use, according to sqlite.org.

Database vs spreadsheet vs files

A spreadsheet also stores rows and columns, so why use a database? For a small list one person edits, a spreadsheet is fine. Databases win as data grows and more people use it:

Storing each fact once matters. If a customer's address is copied into every order, changing it means finding every copy, and missing one leaves the data contradicting itself. AQA calls these problems data redundancy (needless copies) and data inconsistency (copies that disagree). Try it: this question describes a job and asks whether a database or something simpler is the right tool.

How do you get data out of a database?

Relational databases use SQL (Structured Query Language). You describe what you want, and the DBMS works out how to fetch it. This query asks for the names and emails of customers in Leeds, sorted by name:

SELECT name, email
FROM customers
WHERE city = 'Leeds'
ORDER BY name;

Three more commands cover changing data: INSERT adds a record, UPDATE changes one, and DELETE removes one. Together with reading, these four jobs are often called CRUD: create, read, update, delete. The Databases course has you writing these queries yourself.

Types of databases

"NoSQL" just means "not only SQL": databases that don't use the classic table model. Many apps use more than one type, for example a relational database for orders and a key value store as a fast cache. Try one: this question describes some data and asks whether SQL or NoSQL suits it better.

Keeping data safe: transactions

Say Sam sends Priya £50. The database must take £50 from Sam's balance and add £50 to Priya's. If the power failed between the two steps, money would vanish. Databases prevent this with transactions: a group of changes that either all happen or none do. PostgreSQL's documentation describes a transaction as bundling multiple steps into a single all or nothing operation. That guarantee is a big reason banks, shops and booking systems trust databases with their data.

Database examples in everyday life

• Online shopping: products, stock levels, customers, baskets and orders.
• Banking: accounts, balances and every transaction.
• Social media: profiles, posts, likes and who follows whom.
• Schools: pupils, timetables, attendance and grades.
• Your phone: contacts, messages and app data, often in SQLite files.
• Healthcare: patient records, appointments and prescriptions.

Common mistakes

• Calling the software the database. MySQL is a DBMS; the database is the data it manages. People often say both, but exams expect the difference.
• Mixing up records and fields. A record is a row (one item); a field is a column (one property).
• Choosing a primary key that can repeat, such as a name or a date of birth.
• Storing the same fact in many places. It causes redundancy and inconsistency.
• Thinking NoSQL means no structure. It means a different structure, not none.
• Treating a spreadsheet as a database for a big, shared system. It works until two people edit at once.

Read next

Computer Science · DatabasesWhat is a relational database? Tables, keys and joinsWhat a relational database is: tables linked by primary and foreign keys, one-to-many and many-to-many relationships, SQL joins and why it avoids repeats.