MODULE 1 OF 8: FOUNDATIONS • LESSON 2 OF 4~16 min read

Relational Tables, Keys and Relationships

Learn how relational tables represent facts and how keys connect them. These ideas help you read schemas, understand JOINs later, and avoid duplicate or inconsistent data.

•

Previous Next Lesson

Learn how relational tables represent facts and how keys connect them. These ideas help you read schemas, understand JOINs later, and avoid duplicate or inconsistent data.

Key Takeaways at a Glance:

•A table represents an entity with a specific 'grain' (the exact real-world meaning of a single row).
•A Primary Key uniquely identifies a row and strictly forbids NULLs; composite keys combine multiple columns.
•A Foreign Key enforces referential integrity by pointing to an existing Primary Key in another relation.
•One-to-many relationships require the foreign key on the 'many' side; many-to-many relationships require an associative junction table.

In Simple Words (The Mental Model)

Think of a spreadsheet where each tab is an entity. Instead of copying a department's name, floor, and budget onto every single employee row (which causes update anomalies and typos), you assign the department a unique number (Primary Key) and only store that number on the employee row (Foreign Key).

Real-World Analogy

Think of a passport. Your Passport Number is your Primary Key—unique to you alone, never null. When you check into an airport flight, the airline links your ticket to your Passport Number (Foreign Key). The airline doesn't need to re-record your entire medical history or DNA on the ticket; they just reference your passport.

Execution Flow Internals

The SQL Query Lifecycle Inside the RDBMS

Step 1 of 4

Entity Grain

Phase 1: Design

Define what 1 row represents (e.g. 1 employee, 1 invoice line).

Foundational Principles

Core Concept Breakdown

Essential database invariants, relational theory, and execution behavior.

1What are tables, rows, columns and table grain?

A table stores records about one kind of thing or event. Columns describe the attributes stored for each record, and rows hold the values for one record. The table's grain is the meaning of one row. For employees, the grain may be 'one row per employee'; for order_items, it may be 'one row per product line within an order'. State the grain before joining or counting records, because a single order may contain several order-item rows.

2Why keys matter

Keys let a database identify records and express relationships reliably. Without a stable identifier, two people with the same name could be confused, and a name change could break references. Keys also help prevent duplicate records and allow the database to enforce consistency. In interviews, explain the role a key plays, not just its definition.

3Primary keys

A primary key uniquely identifies each row in a table and cannot contain NULL. A primary key can be a single column, such as staff_id, or a combination of columns, called a composite key. For example, employee_office may use (staff_id, office_id) as a composite primary key so the same employee-office pair cannot be inserted twice. A primary key should identify a record, not carry a descriptive value that may change.

4Foreign keys and referential integrity

A foreign key is a column or group of columns whose values reference a key in another table, commonly its primary key. If employees.unit_id references departments.unit_id, the database can prevent an employee from referring to a department that does not exist. A nullable foreign key may be NULL when the relationship is optional. Exact constraint and cascading-action behavior depends on the schema and database engine.

5One-to-one, one-to-many and many-to-many

In a one-to-one relationship, a record corresponds to at most one record on the other side. In a one-to-many relationship, one department may have many employees, while each employee belongs to zero or one department in this example. In a many-to-many relationship, many students can take many courses. A junction table such as student_courses represents each student-course pairing using foreign keys. The relationship is determined by the business rules and constraints, not by column names alone.

6Separate facts, then combine them when needed

Suppose every employee row repeats the department name and floor. If the department name changes, many employee rows must be updated consistently. A departments table stores department facts once, while employees stores employee facts and a department reference. A query can combine those tables with a JOIN when both sets of information are needed. This is an introductory design principle; deeper normalization and functional dependencies belong in DBMS theory.

7How a relationship appears in a query

A foreign key documents the relationship, but SQL queries still need an explicit join condition when combining tables. Joining employees to departments on matching unit_id values returns employee details alongside department details. If a parent has several matching child rows, it can appear several times in the joined result. LEFT JOIN can preserve employees even when no matching department row exists; the join lesson covers this behavior in depth.

Architectural Trade-offs

Comparative Analysis

Aspect / Dimension	Declarative SQL	Imperative Code

Canonical Syntax Template

Standard SQL Query Template

SQLsyntax-template.sql

1CREATE TABLE departments (

2 unit_id INT PRIMARY KEY,

3 unit_name VARCHAR(80) NOT NULL

4);

6CREATE TABLE employees (

7 staff_id INT PRIMARY KEY,

8 full_name VARCHAR(90) NOT NULL,

9 unit_id INT,

10 FOREIGN KEY (unit_id) REFERENCES departments(unit_id)

11);

Visual Data Transformations

Interactive Query Walkthroughs

Inspect the source tables, examine the query logic, and verify the resulting dataset.

1Example 1 — Identify the table grain

Table: employees

3 rows

staff_id	full_name	unit_id
101	Aarav Sharma	1
102	Pooja Hegde	2
103	Rohan Verma	1

Grain: Exactly one row per staff member

SQLexample-1.sql

1-- employees: one row per employee

2SELECT staff_id, full_name, unit_id

3FROM employees;

Projected Employees

3 rows

staff_id	full_name	unit_id
101	Aarav Sharma	1
102	Pooja Hegde	2
103	Rohan Verma	1

What Happens: Each row represents one employee. unit_id identifies the employee's department relationship; it is not the employee's primary identifier.

2Example 2 — Define a primary key

Table: departments_schema

2 rows

Column	Data Type	Constraints
unit_id	INT	PRIMARY KEY (Unique & NOT NULL)
unit_name	VARCHAR(80)	NOT NULL

DDL Definition: Enforces unit_id as the unique non-null primary key

SQLexample-2.sql

1CREATE TABLE departments (

2 unit_id INT PRIMARY KEY,

3 unit_name VARCHAR(80) NOT NULL

4);

departments

3 rows

unit_id	unit_name
1	Engineering
2	Product Operations
3	Financial Risk

Sample rows stored safely under PK constraint

What Happens: unit_id must uniquely identify each department and cannot be NULL. NOT NULL also requires a department name.

3Example 3 — Add a foreign-key relationship

Table: departments (Parent Table)

2 rows

unit_id	unit_name
1	Engineering
2	Product Operations

Parent relation with PK unit_id

SQLexample-3.sql

1CREATE TABLE employees (

2 staff_id INT PRIMARY KEY,

3 full_name VARCHAR(90) NOT NULL,

4 unit_id INT,

5 FOREIGN KEY (unit_id) REFERENCES departments(unit_id)

6);

employees (Child Table)

3 rows

staff_id	full_name	unit_id
101	Aarav Sharma	1
102	Pooja Hegde	2
103	Kabir Sen	NULL

Child relation: unit_id links to valid department

What Happens: Each non-NULL unit_id must refer to a department key that exists, assuming the constraint is enabled and the database enforces it. Because unit_id is nullable here, an employee may have no department assigned.

4Example 4 — Combine related tables

Table: employees & departments

3 rows

staff_id	full_name	unit_id	unit_name
101	Aarav Sharma	1	Engineering
102	Pooja Hegde	2	Product Operations
103	Kabir Sen	NULL	NULL

Joining child employees with parent departments on unit_id

SQLexample-4.sql

1SELECT e.full_name, d.unit_name

2FROM employees AS e

3LEFT JOIN departments AS d

4 ON d.unit_id = e.unit_id;

Query Result Set

3 rows

full_name	unit_name
Aarav Sharma	Engineering
Pooja Hegde	Product Operations
Kabir Sen	NULL

LEFT JOIN preserves Kabir Sen even without a unit

What Happens: The query returns employee names and matching department names. LEFT JOIN keeps an employee row even if unit_id is NULL or no department matches; the department columns are then NULL.

5Example 5 — Represent a many-to-many relationship

Table: student_courses (Junction Table)

3 rows

student_id	course_id	enrolled_on
501	10	2024-01-10
501	20	2024-01-12
502	10	2024-01-15

Composite PK (student_id, course_id) links students to multiple courses

SQLexample-5.sql

1CREATE TABLE student_courses (

2 student_id INT NOT NULL,

3 course_id INT NOT NULL,

4 PRIMARY KEY (student_id, course_id)

5);

Valid Unique Pairs

3 rows

student_id	course_id
501	10
501	20
502	10

What Happens: The composite key permits a student to take many courses and a course to have many students, while preventing the same pair from being stored twice. In a full schema, both columns should reference their parent tables.

Important Invariants & Best Practices

Primary keys uniquely identify rows and cannot be NULL.
Foreign keys represent references and can enforce referential integrity.
A nullable foreign key can represent an optional relationship.
Composite keys use more than one column to identify a row or relationship.
Many-to-many relationships are usually represented with a junction table.
Understand the grain of each table before joining or counting rows; one-to-many joins can multiply result rows.

Frequent Interview Pitfalls & Candidate Traps

Avoid these common misconceptions that lead to bugs or failed technical interviews:

•Using a person's name or another changeable attribute as if it were guaranteed unique.
•Assuming a foreign key automatically joins tables in a SELECT query.
•Assuming every foreign key must be populated even when the column permits NULL.
•Joining tables without checking relationship cardinality and then counting duplicated parent rows.
•Storing a comma-separated list of related IDs in a single column instead of modelling the relationship.

Placement Interview Perspective

Be prepared to explain primary keys, foreign keys, composite keys, referential integrity, relationship cardinality, junction tables and table grain. Use a concrete department-employee example and explain how a one-to-many join affects row counts.