Relational model preview

Relational databases organize data into tables (relations). Each row is a record; each column is a typed attribute. Relationships link tables through keys—foundation for JOINs later.

Tables, rows, columns

Think spreadsheet with rules:

• Table — entity type (customers, orders)
• Row — one instance (one customer)
• Column — one attribute (email, total)
• Schema — names and types of all tables

Sample schema

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  total REAL,
  ordered_at TEXT
);

INSERT INTO customers (id, name, email) VALUES
  (1, 'Ada', 'ada@example.com'),
  (2, 'Lin', 'lin@example.com');

INSERT INTO orders (id, customer_id, total, ordered_at) VALUES
  (101, 1, 49.99, '2025-01-10'),
  (102, 1, 12.50, '2025-02-01'),
  (103, 2, 99.00, '2025-02-15');

Practice: Copy SQL into sqlite3 practice.db, DB Fiddle, or a local Postgres session. Compare row counts and column names with the lesson.

Reuse this mini schema in later lessons when practicing JOINs and aggregates.

Keys preview

id columns identify rows uniquely. customer_id in orders references customers.id—a foreign key relationship covered in depth later.

Important interview questions and answers

1. Q: What is a primary key?
   A: A column or set of columns that uniquely identifies each row in a table.
2. Q: Why normalize data across tables?
   A: Reduces duplication, keeps updates consistent, and models real-world relationships cleanly.

Self-check

1. How many rows does the sample orders table have after the INSERTs?
2. Which column links orders to customers?

Tip: Save the customers/orders CREATE script; reuse it for JOIN and aggregate practice.