What a database is

Data, database and DBMS

Data means facts: a student's name, the price of a pressure cooker, the date of an order. On their own, facts are not very useful. They become useful when they are organised so you can find them, check them and change them.

A database is an organised collection of data, stored so that a computer can find any part of it quickly. A DBMS, short for database management system, is the software that looks after a database. It stores the data on disk, answers questions about it, stops two people from breaking each other's changes, and controls who may see what.

MySQL, PostgreSQL, SQLite, Oracle and Microsoft SQL Server are all DBMSs. In daily talk people say "the database" for both the data and the software, and that is fine. Just remember that the data and the program that manages it are two different things.

Why not just use Excel?

A spreadsheet is great for one person and a few thousand rows. A real app needs much more, and a database is built for exactly those needs.

Relational and NoSQL databases

A relational database keeps data in tables of rows and columns, and links tables to each other through shared values called keys. You talk to it in SQL (Structured Query Language). PostgreSQL, MySQL, SQLite, Oracle and SQL Server are relational. NoSQL databases store data in other shapes: documents that look like JSON (MongoDB), key and value pairs (Redis), or wide columns (Cassandra). They suit data whose shape changes often or that must spread over many servers. Relational databases are the safe default for most apps, especially anything with money, and this course focuses on them. Module 11 compares NoSQL in more detail.

Where the database sits in an app

You never talk to a big app's database directly. Your phone or browser talks to the app's server, and the server talks to the database in SQL.

Your phone / browser  -->  App server (Java, Python, Node.js...)  -->  Database (PostgreSQL, MySQL...)
      "Book a seat"            runs SQL: find a free seat, save booking       stores trains, seats, bookings

• A train booking site like IRCTC keeps trains, coaches, seats, passengers and bookings in tables. When you check a PNR, the server runs a query that finds one booking among crores.
• A bank keeps customers, accounts and every transaction. A transfer must take money from one account and add it to another together, or not at all.
• A school portal keeps students, classes, marks and fees, so teachers can enter marks and parents can see report cards.

Your first query

In this course you write SQL right here in the browser. The site runs SQLite, a small but complete database engine, inside the page. The practice table students holds eight college students. The query below reads all of it.

SELECT * FROM students;

Your roadmap for this course

Common mistakes

• Thinking SELECT changes data. SELECT only reads; it never adds, edits or deletes anything. You can run it as often as you like.
• Misspelling a table name. SELECT * FROM student gives "no such table: student". Check the exact name; here it is students.
• Using a shared spreadsheet as the "database" of a growing business. It works for a month, then two people overwrite each other. Move to a real database early.
• Expecting SQL to be one product. SQL is a language; MySQL, PostgreSQL and SQLite are products that speak it with small differences.