SQL › Meet the Database

Databases and RDBMS

What data is

Data is any fact worth keeping. For example, a person's name, birth date, address and bank balance are all data.

Data comes in many forms: numbers, text, dates, and also images, videos and files.

Let's imagine you look up one customer in the course's shop:

Code
SELECT full_name, email, city, loyalty_points
FROM customers
WHERE customer_id = 1;

What a database is

A database is an organised collection of data, stored electronically so it can be found, changed, protected and analysed.

Almost every app and website you use keeps its data in one. For example, every post, photo and message on a social network lives in a database.

How an app uses its database

An app doesn't keep the data itself. It sends requests to the database.

Whatever the app shows is read from the database; whatever you enter is written to it.

Let's imagine a shopper opens a product page and then posts a review:

Code
SELECT product_name, price, stock FROM products WHERE product_id = 3;   -- the page reads the product
 
INSERT INTO reviews (product_id, customer_id, rating, comment)
VALUES (3, 4, 5, 'Fast delivery, works well');                       -- the review is written
SELECT product_id, rating, comment FROM reviews WHERE customer_id = 4;  -- and shows up next time

Database, DBMS and RDBMS

A database is the container. A DBMS (database management system) is the software that creates and manages it.

You never touch the data directly: you send commands to the DBMS, which stores, changes, reads and protects the data for you.

A relational DBMS, or RDBMS, keeps data in tables linked by keys, and you talk to it in SQL. For example, PostgreSQL, MySQL, SQL Server and Oracle are RDBMSs; this course uses PostgreSQL.

Most SQL works the same in all of them. Where PostgreSQL differs from the others, the lessons say so.

How the pieces fit: an RDBMS server holds databases → a database holds schemas → a schema holds tables → a table holds rows and columns. In MySQL a schema and a database are the same thing, and in Oracle a schema belongs to a user.

From files to relational databases

Databases have changed shape over the years. Each step fixed a limit of the one before.

Today big companies often run both kinds side by side.

Tables, rows and columns

In an RDBMS, a table holds one kind of thing, a row is one record, and a column is one fact about every row.

Every column has a name and a data type, a rule most RDBMSs check: only values that fit the type can go in.

Let's imagine you look at the shop's categories, and then try to store a word as a stock count:

Code
SELECT * FROM categories;
-- Fails: stock is a whole number, so 'lots' doesn't fit its data type
UPDATE products SET stock = 'lots' WHERE product_id = 3;

Keys identify rows

A primary key identifies each row. Any other column that could do the same job is a candidate key.

The examples below read pg_constraint, PostgreSQL's own list of a table's rules. Every RDBMS keeps a similar list under its own name.

Let's imagine you check which columns identify a customer:

Code
SELECT conname, pg_get_constraintdef(oid) AS rule
FROM pg_constraint
WHERE conrelid = 'customers'::regclass AND contype IN ('p', 'u');   -- customer_id, and email is unique too

Foreign keys link tables

A foreign key points to a row of another table. That way each fact is stored once and referred to everywhere else.

The RDBMS refuses a change that would point to a row that doesn't exist.

Let's imagine you check how orders point to customers, and then try an order for a customer who doesn't exist:

Code
SELECT conname, pg_get_constraintdef(oid) AS rule
FROM pg_constraint
WHERE conrelid = 'orders'::regclass AND contype = 'f';
 
-- Fails: there is no customer 999 for the order to point to
INSERT INTO orders (customer_id, order_date, status, total_amount, shipping_city)
VALUES (999, DATE '2025-10-31', 'pending', 500, 'Pune');

Composite keys

Sometimes only a pair of columns is unique. For example, a product can appear only once per order, so order_id and product_id together are unique in order_items.

Code
SELECT conname, pg_get_constraintdef(oid) AS rule
FROM pg_constraint
WHERE conrelid = 'order_items'::regclass AND contype IN ('p', 'u');

Tables linked through another table

Two tables don't need a direct link to be connected. For example, customers and products have no key between them, but orders and order_items connect them.

Let's imagine you follow order 20 from the customer to the products, one table at a time:

Code
SELECT order_id, customer_id FROM orders WHERE order_id = 20;               -- placed by customer 8
SELECT product_id, quantity FROM order_items WHERE order_id = 20;          -- products 8 and 16
SELECT product_id, product_name FROM products WHERE product_id IN (8, 16);

In Section 5, joins do these steps in a single query.

All or nothing

A transaction groups changes so they all happen or none do. RDBMSs promise ACID:

• Atomic: all or nothing.
• Consistent: the rules always hold.
• Isolated: users don't see each other's half-done work.
• Durable: committed data survives a crash.

Let's imagine someone deletes every review by mistake, inside a transaction:

Code
BEGIN;
DELETE FROM reviews;
ROLLBACK;                          -- the reviews are back
SELECT COUNT(*) AS reviews FROM reviews;

Relational and non-relational databases

Non-relational (NoSQL) databases don't use linked tables. Each kind stores data in its own way and suits its own job.

Remember

• A database stores the data; a DBMS is the software that manages it; an RDBMS keeps it in tables you query with SQL.
• An app reads what it shows from the database and writes what you enter to it.
• A primary key identifies each row; a foreign key points to a row of another table.
• NULL means unknown, not 0 and not empty text (Oracle is the exception: it stores empty text as NULL).
• In the lab, end each statement with ;; only the last result is shown, and every run starts from the same data.