Introduction to Databases

What Is a Database?

A database is an organised collection of data stored electronically so it can be retrieved, updated, and managed efficiently. Before databases existed, businesses stored data in spreadsheets and text files. These fell apart quickly — duplicates crept in, files got corrupted, and finding related information across multiple files was tedious and error-prone.

A database solves all of that by enforcing structure. Every row follows the same rules. Bad data gets rejected before it is ever stored. Multiple users can read and write at the same time without corrupting each other's work.

• Data is stored in a consistent structure enforced on every write
• Multiple users can work simultaneously without conflicts
• Relationships between data are defined once and enforced automatically
• Security controls who can see or change what — down to individual columns
• If an application crashes mid-operation, the database leaves nothing in a broken state

Why Databases Power Everything Around You

Every time you place an order, check your bank balance, or stream a video, a database is working behind the scenes. The app you see is just a front end — every piece of data it shows or saves comes from a database.

Without a database, answering "which customers spent more than $500 last month?" means manually scanning every file. With SQL, it is one statement that returns the answer in milliseconds — whether you have 500 records or 500 million.

• Banks process millions of transactions per second, each one guaranteed accurate
• Hospitals store patient records that must be instantly available and never corrupted
• E-commerce platforms track live inventory so two customers cannot buy the last unit simultaneously
• Payroll systems ensure every employee is paid the right amount on the right date
• Your phone's contacts list is itself a small local relational database

Tables, Rows, and Columns

A relational database organises data into tables. Each table represents one type of entity — products, orders, users. Every table has columns that define the attributes each record must have, and rows that hold the actual records.

Unlike a spreadsheet, the structure is enforced. If the Price column is defined as a required decimal number, no row can exist without a valid price — the database rejects it. Each table also has a primary key — a column whose value uniquely identifies every row. And a foreign key links a row in one table to a row in another, creating an enforced relationship. This is what keeps an order connected to a real customer and a real product.

• A table holds one entity type — Products, Orders, Users
• A row is one complete record — one product, one order, one user
• A column is one attribute shared by every row — Price, OrderDate, Email
• A primary key uniquely identifies every row — no two rows can share it
• A foreign key links a row to a row in another table — Orders.UserId → Users.UserId
• Normalisation means storing each fact once and referencing it, not duplicating it

What Is a Database Management System?

A DBMS (Database Management System) is the software between your application and the raw data on disk. You never touch database files directly — you send SQL to the DBMS and it handles reading, writing, indexing, security, and crash recovery for you.

When the DBMS uses the relational model it is called an RDBMS. MS SQL Server, MySQL, and PostgreSQL are all RDBMSs. They all accept SQL, enforce schemas, and support ACID transactions. MS SQL Server is what we use throughout this course.

• Your application sends SQL — the DBMS handles everything else
• It manages thousands of simultaneous connections without interference
• It enforces every constraint — data types, required fields, foreign key links
• It maintains indexes so queries stay fast as data grows
• It handles backup, recovery, and failover automatically

SQL — The Language of Relational Databases

SQL (Structured Query Language) is the standard language for communicating with any relational database. Developed at IBM in the 1970s and standardised in 1987, it is still the dominant database language today and one of the most valuable skills in data and engineering careers.

SQL is declarative — you describe the result you want and the engine figures out how to get it. You do not write loops or manage memory. SQL is organised into four categories: DML for reading and changing data (SELECT, INSERT, UPDATE, DELETE), DDL for defining structure (CREATE TABLE, ALTER TABLE), DCL for permissions (GRANT, REVOKE), and TCL for transactions (BEGIN TRANSACTION, COMMIT, ROLLBACK).

-- Your first SQL statements.
-- SQL reads like plain English — you say WHAT you want,
-- not HOW to get it. The engine handles the rest.
-- Return every column from every row in Products
SELECT * FROM Products;
-- Return only the Name and Price columns
SELECT Name, Price FROM Products;
-- Return only products that cost less than $30
-- WHERE filters which rows come back
SELECT Name, Price
FROM   Products
WHERE  Price < 30;

• SELECT * returns every column — useful for exploration, but name specific columns in production code
• WHERE Price < 30 filtered seven rows down to four — only the products under $30 came back
• The same syntax works identically on a table with 7 rows or 70 million rows
• SQL keywords are case-insensitive but uppercase by convention — SELECT not select

ACID — What Makes a Database Trustworthy

Relational databases make four guarantees about every operation, known as ACID. These are why banks, hospitals, and e-commerce platforms trust their most critical data to a relational database.

Atomicity — a group of operations either all succeed or all fail. No halfway states. If placing an order involves deducting stock and recording the order row, and something fails mid-way, both steps are undone automatically.

Consistency — every transaction leaves the database in a valid state. Every rule you have defined — a price cannot be negative, an order must belong to a real customer — is enforced on every operation.

Isolation — concurrent transactions do not interfere with each other. Two customers buying the last unit of a product simultaneously will not both succeed — the database serialises them correctly.

Durability — once a transaction is committed, it is permanently saved. Even if the server loses power one second later, the committed data is not lost.

• Atomicity — all or nothing, no partial writes
• Consistency — rules are enforced on every transaction
• Isolation — concurrent transactions do not see each other's uncommitted changes
• Durability — committed data survives crashes and power loss
• ACID is what separates a production database from a simple file store or cache

Types of Databases

Relational databases are the most widely deployed type, but they are not the only option. NoSQL databases emerged to handle workloads the relational model handles awkwardly — very high write volumes, flexible or unpredictable data shapes, and graph-style relationships. Understanding the landscape helps you pick the right tool for each problem.

For structured business data with enforced relationships and reporting requirements — which covers the vast majority of enterprise applications — a relational database is the right answer. That is why this course is built on MS SQL Server.

Lesson Summary

Practice Questions

Quiz

Next up — SQL vs NoSQL — How the two database types differ, when to use each one, and why SQL Server is a relational database.