Computer Science Roadmap

Learn computing fundamentals, programming, algorithms, systems, and software engineering.

Overall Progress0%

Databases

Explanation

A database is an organised collection of data, and a Database Management System (DBMS) is the software that stores, queries, and protects it. Most applications store data in a relational database, where information lives in tables with rows and columns and is linked by keys. This lesson covers how to model real-world data as tables, how to normalise a schema to remove duplication, how to query and join tables with SQL, why indexes speed up reads, and what the ACID guarantees mean for reliability. By the end you will be able to design a small relational schema and write the SQL to answer real questions about it.

Relational Databases

A relational database stores data in tables. Each table has rows (records) and columns (fields). A primary key uniquely identifies each row; a foreign key references a row in another table.

Concept	Meaning
Table	A collection of related records
Row	One record
Column	One field of a record
Primary key	Uniquely identifies a row
Foreign key	References a primary key in another table

Relationships

Relationship	Example
One-to-one	A person has one passport
One-to-many	One customer has many orders
Many-to-many	Students enrolled in many courses

Normalization: 1NF, 2NF, 3NF

Normalization removes duplicate data and dependency problems by reshaping tables. It is not about memorising rules — it is about asking "what fact depends on what key?"

1NF: every cell holds a single value (no repeating groups or lists).
2NF: every non-key column depends on the whole primary key (no partial dependency).
3NF: every non-key column depends only on the key (no transitive dependency).

Unnormalized table

↓

1NF (atomic cells)

↓

2NF (no partial dependency)

↓

3NF (no transitive dependency)

SQL Basics

sql

1SELECT name, price FROM products WHERE price > 20 ORDER BY price DESC;2 3INSERT INTO products (name, price) VALUES ('Keyboard', 45);4 5UPDATE products SET price = 40 WHERE name = 'Keyboard';6 7DELETE FROM products WHERE name = 'Keyboard';

JOINs

A JOIN combines rows from two tables based on a matching condition.

Join	Returns
INNER JOIN	Only rows with matches in both tables
LEFT JOIN	All left rows, plus matches (null where none)

Aggregation and GROUP BY

Aggregate functions summarise groups of rows: COUNT, SUM, AVG, MIN, MAX. GROUP BY forms the groups; HAVING filters them after aggregation.

Indexes

An index is a data structure that lets the database find rows without scanning the whole table. It speeds up reads but slows down writes and uses extra space, so add indexes only on columns you search or join on often.

Transactions and ACID

A transaction is a group of operations that must all succeed or all fail together. ACID are the guarantees that make transactions safe.

Property	Meaning
Atomicity	All-or-nothing
Consistency	Valid state before and after
Isolation	Concurrent transactions do not interfere
Durability	Committed changes survive crashes

A money transfer that debits one account and credits another is the classic example: either both happen or neither does.

Syntax

An e-commerce schema and querysql

1CREATE TABLE users (id INT PRIMARY KEY, name TEXT);2CREATE TABLE products (id INT PRIMARY KEY, name TEXT, price DECIMAL);3CREATE TABLE orders (id INT PRIMARY KEY, user_id INT REFERENCES users(id));4CREATE TABLE order_items (5  id INT PRIMARY KEY,6  order_id INT REFERENCES orders(id),7  product_id INT REFERENCES products(id),8  quantity INT9);10 11SELECT u.name, SUM(oi.quantity * p.price) AS revenue12FROM users u13JOIN orders o ON o.user_id = u.id14JOIN order_items oi ON oi.order_id = o.id15JOIN products p ON p.id = oi.product_id16GROUP BY u.name;

This query computes total revenue per user by joining the four tables.

Examples

Filtering and aggregating

Count products priced above 20.

sql

1SELECT COUNT(*) FROM products WHERE price > 20;

Output

Inner join

List each order with the customer name.

sql

1SELECT orders.id, users.name2FROM orders3JOIN users ON users.id = orders.user_id;

Output

1 Ada
2 Bob

Key Takeaways

Relational data lives in tables linked by primary and foreign keys.
Normalization (1NF/2NF/3NF) removes duplication and dependency problems.
SQL selects, joins, and aggregates data across tables.
Indexes speed up reads but slow writes and use space.
ACID transactions make multi-step changes safe and reliable.

← PreviousOperating Systems Next →Computer Networks