The Relational Model

Teachers

Thinking in Relations

In a Relational Database (RDBMS), data is organized into Tables. This structure helps avoid repeating data and keeps it accurate.

The Anatomy of a Table

Imagine a spreadsheet for a simple "Students" list:

id	first_name	last_name	email	age
1	John	Doe	[email protected]	25
2	Jane	Smith	[email protected]	22

**Table:** The whole grid. It represents a real-world thing (like Students or Products).
**Column:** A vertical slice. It represents one piece of data (like first_name or age). Every row must have a value for this column (even if it is empty, or NULL).
**Row:** A horizontal slice. It represents one specific item (like one student).

Data Types

In JavaScript, variables can hold any type of data. In a database, **Column s** are strict. You must decide what type of data goes in a column.

INTEGER: Whole numbers (e.g., age, count).
TEXT: Text strings (e.g., name, email).
REAL: Decimal numbers (e.g., price, temperature).
BOOLEAN: True/False (often stored as 1 for True, 0 for False).
DATE: Dates and times.

Constraints

Constraints are rules for your data. They ensure the data is good quality.

NOT NULL: The column cannot be empty.
UNIQUE: No two rows can have the same value (e.g., each email must be different).
DEFAULT: If you don't provide a value, the database uses this one automatically.

Keys: Connecting the Data

The "Relational" part comes from how we link tables together. We use Keys for this.

Primary Key (PK)

A unique ID for a specific row.

Every table should have a Primary Key.
It acts like a permanent ID card number for that record.
Common practice: An auto-counting number called id.

Foreign Key (FK)

A field in one table that links to the Primary Key of another table.

Example: We have Students and Classes. A student can sign up for a class. We create an Enrollments table to link them.

Table: Students

id (PK)	name
1	John
2	Jane

Table: Classes

id (PK)	title
101	Intro to SQL
102	Advanced JS

Table: Enrollments

id (PK)	student_id (FK)	class_id (FK)
1	1 (John)	101 (Intro to SQL)
2	1 (John)	102 (Advanced JS)
3	2 (Jane)	101 (Intro to SQL)

In the Enrollments table, student_id is a Foreign Key. It points to the id in the Students table.

Relationships

One-to-One

One record in Table A matches exactly one record in Table B.

Example: A Person and a Passport. One person has one passport. One passport belongs to one person.

One-to-Many (Most Common)

One record in Table A matches many records in Table B.

Example: An Author and Books. One author can write many books. But one specific book usually has only one author.

Many-to-Many

Many records in Table A match many records in Table B.

Example: Students and Classes. A student takes many classes. A class has many students.
How to do it: You need a "Join Table" (like the Enrollments table above). You cannot just put a list of IDs in one cell.

Normalization (The "Why")

Why do we split data into many tables? Why not put everything in one big "Enrollments" spreadsheet? Answer: To avoid copying data and making mistakes.

Imagine we stored the student's name in every Enrollment row:

Waste: We write "John" 50 times if he takes 50 classes.
Mistakes: If John changes his name to "Jon", we have to update 50 rows. If we miss one, our data is wrong.

By keeping Students in one table and just using the ID 1 in Enrollments, we only update the name in one place. This is called Normalization.

The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0

CC BY-NC-SA 4.0 Icons

*https://hackyourfuture.net/*

Found a mistake or have a suggestion? Let us know in the feedback form.