Relational Database Concepts

Tables, Rows, and Columns

• In a relational database, data is organized into tables, which are similar to spreadsheets. Each table stores information about a specific type of entity, such as customers, products, or orders.

Table (Entity)

• A collection of related data organized in rows and columns. For example, a table named Customers might store information about all customers of a business.

Row (Record)

• Each row represents a single entry or record in the table. For example, a row in the Customers table might represent one customer, including all of their details like name, email, and phone number.

Column (Field)

• Each column represents a specific attribute of the entity. For example, in the Customers table, columns could include CustomerId, Name, Email, and PhoneNumber. Each column has a defined data type such as numeric, string, or date.

Customers Table Example

CustomerId	Name	Email	PhoneNumber
1	Alice Lee	alice@example.com	555-1234
2	Bob Smith	bob@example.com	555-5678
3	Carol Tan	carol@example.com	555-9012

• Customers is the table.

• Each row (1, Alice Lee, alice@example.com, 555-1234) represents a customer record.

• Each column (CustomerId, Name, Email, and PhoneNumber) represents a specific piece of information about the customers.

Primary Keys

• A Primary Key (PK) is a special column or combination of columns in a table that uniquely identifies each row. No two rows can have the same primary key value, and the value cannot be NULL.

• This ensures that every record in a table can be uniquely referenced, retrieved, or updated without confusion.

• Primary keys help maintain data integrity and make relationships between tables possible.

Primary Key Must Haves

Unique: No duplicate values allowed.

Not Null: Every row must have a value.

Stable: Values should not change over time.

Used for Relationships: Other tables use this key as a reference (with foreign keys).

Primary Key Examples

Table with a Primary Key

CustomerId (PK)	Name	Email
1	Alice Lee	alice@example.com
2	Bob Smith	bob@example.com
3	Carol Tan	carol@example.com

tip

• CustomerId is the primary key and each customer gets a unique id that never repeats.

Table with a Natural Key

SKU (PK)	ProductName	Price
A100	Wireless Mouse	$ 25.00
B205	Keyboard	$ 35.00
C310	USB Charger	$ 15.00

tip

• Sometimes a natural, meaningful column can also be a primary key. The SKU (Stock Keeping Unit) already uniquely identifies each product.

Composite Primary Key

StudentId (PK)	CourseId (PK)	EnrollmentDate
101	CS101	2025-01-10
101	MATH200	2025-01-11
102	CS101	2025-01-12

tip

• A primary key can also be made of two or more columns combined. This is called a composite key.

• A student can enroll in many courses and a course can have many students. But the combination of StudentId + CourseId must be unique.

Foreign Keys

• A Foreign Key (FK) is a column or set of columns in one table that creates a link to the Primary Key in another table. Its main purpose is to establish and enforce relationships between tables in a relational database.

• Foreign keys ensure referential integrity:

  • You cannot insert a value in the foreign key column unless it exists in the referenced table.

  • You cannot delete or update a referenced primary key value if it would break the relationship unless rules like CASCADE are used.

CASCADE

• In SQL, CASCADE is an action you can attach to FOREIGN KEY constraints to automatically propagate changes (DELETE or UPDATE) from a parent table to child tables. This means When you define a foreign key, you can specify what happens when the parent row is deleted or updated.

Foreign Key Attributes

PK - Primary Key FK - Foreign Key

customers table

customer_id (PK)	name
1	Alice
2	Bob
3	Carla

orders table

order_id (PK)	customer_id (FK)	order_date
101	1	2026-08-20
102	1	2026-08-22
103	2	2026-08-23

• Connect related data across tables.

• Because customer_id links the two tables, you can match rows together:

order_id	customer_id	name	order_date
101	1	Alice	2026-08-20
102	1	Alice	2026-08-22
103	2	Bob	2026-08-23

• The foreign key, allows the connection between customers and orders. This means you can get access to the other fields such as name based on the id.

• Prevent invalid or orphaned records.

• If there is an attempt to insert the following data to the orders table:

order_id	customer_id	order_date
104	99	2026-08-24

• This will fail because customer_id 99 does not exist in the customers table. This is a feature of the foreign key constraint, you can only add rows that exists to a related table otherwise it will be rejected.

• Enforce database-level rules that keep data consistent.

• If someone deletes customer_id 1 from the customers table, this will also fail because there are orders (101 and 102) which is currently referencing customer id 1. This is what you call referential integrity. The database will block the delete operation because orders (101 and 102) will end up referencing a customer that does not exists.

Foreign Key Examples

Customers and Orders Table

Customers Table

CustomerId (PK)	Name
1	Alice Lee
2	Bob Smith

Orders Table

OrderId (PK)	OrderDate	CustomerId (FK)
1001	2025-01-10	1
1002	2025-01-11	1
1003	2025-01-12	2

tip

• CustomerId in Orders is a foreign key pointing to CustomerId in Customers.

• This ensures means that every order must belong to an existing customer. For instance you cannot insert an order with CustomerId = 10 because no such customer exists.

Departments and Employees

Departments Table

DeptId (PK)	DeptName
10	HR
20	IT

Employees Table

EmployeeId (PK)	Name	DeptId (FK)
1	Maria Diaz	20
2	John Park	10
3	Sara Kim	20

tip

• DeptId in Employees must match a DeptId in Departments.

• You cannot delete DepartmentId 20 unless employees assigned to it are removed.

Many-to-Many Relationship Using Foreign Keys

Courses Table

CourseId (PK)	CourseName
C101	Database Fundamentals
C102	Web Development

Students Table

StudentId (PK)	Name
S1	Alice Brown
S2	David Lee

StudentCourses Table (Bridge Table)

StudentId (FK)	CourseId (FK)
S1	C101
S1	C102
S2	C101

tip

• Foreign keys are often used in bridge tables.

• Both columns (StudentId and CourseId) in StudentCourses are foreign keys.

• They link students and courses in a many-to-many relationship.

  • A student can enroll in many courses.

  • A course can have many students enrolled.

Data Types

• In a relational database, every column has a data type that defines what kind of values it can store.

• Data types help ensure consistency and prevent invalid data from being inserted.

• Choosing the correct data type is essential for accuracy, performance, and proper data validation.

Complete list of MySQL Datatypes

https://dev.mysql.com/doc/refman/8.4/en/data-types.html

Numeric

• Numeric data types store numbers. They can represent integers (whole numbers) or decimals.

INT

Numeric data type used to store whole numbers or numbers without decimal places. It is one of the most commonly used data types in relational databases.

• Valid values for INT are as follows:

  • SIGNED INT: −2,147,483,648 to +2,147,483,647

  • UNSIGNED INT: 0 to 4,294,967,295

• If the app needs whole numbers and the values will not exceed a few billion. The field is commonly used as an id or counter such as: (User ids, Product ids, Order numbers, and Quantities).

Products Table

ProductId (INT)	Name	Quantity (INT)
1	Keyboard	15
2	Mouse	40
3	USB Cable	200

tip

• ProductId is an INT because each product gets a whole-number identifier.

• Quantity is an INT because stock levels are counted in whole units.

BIGINT

• Numeric data type used to store very large whole numbers (integers). It is similar to INT, but it supports a much larger range, making it ideal for storing values that may grow beyond the limits of regular integers.

• Can be used for ids, counters, or any value that might exceed standard INT limits.

• Valid values for BIGINT are as follows:

  • SIGNED BIGINT: −9,223,372,036,854,775,808 to +9,223,372,036,854,775,807

  • UNSIGNED BIGINT: 0 to 18,446,744,073,709,551,615

Users Table

UserId (BIGINT)	Name
10000000001	Alice Lee
10000000002	Bob Smith

tip

• UserId is a BIGINT because the system may generate billions of unique users over time.

DECIMAL

• The DECIMAL data type is used to store exact numeric values with decimals. Unlike FLOAT or DOUBLE, which can introduce rounding errors, DECIMAL stores numbers precisely, making it ideal for financial and monetary values.

• Use DECIMAL when you need exact values, such as: Prices, Currency amounts, Measurements that require precision, Tax rates, and Interest rates.

Why use decimal

• If you store money using FLOAT, you might get inaccurate results like:

  • 19.99 + 5.00 = 24.9899999999

• But with DECIMAL, it will always be exact:

  • 19.99 + 5.00 = 24.99

• A DECIMAL column is defined using two numbers DECIMAL(p, s) where:

  • p (precision): Total number of digits

  • s (scale): Number of digits after the decimal point

tip

DECIMAL(6,2): Can store numbers up to 9999.99

DECIMAL(10,4): Can store numbers up to 999999.9999

Products Table

ProductId	Price (DECIMAL(6,2))	TaxRate (DECIMAL(4,2))
1	19.99	0.13
2	250.50	0.15
3	5.00	0.05

tip

• Price is stored with 2 decimal places ie: 19.99.

• TaxRate is stored with 2 decimal places, but can be less than 1 ie: 0.13 which is equivalent to 13%.

• Values are exact, no rounding errors.

FLOAT / DOUBLE

• Numeric data types used to store approximate decimal values. Unlike DECIMAL, which stores numbers exactly, floating-point types store numbers using binary approximation, which can introduce small rounding errors.

• They are designed for scientific, mathematical, or high-range calculations where extreme precision is less important than performance or range:

  • Measurements (temperature, distance, weight), Scientific data (coordinates, physics values), Graphics or geometry calculations, Machine learning and statistical data.

• Both can store very large or very small numbers using exponential notation.

FLOAT vs DOUBLE

Type	Precision	Use Case
FLOAT	Lower precision	Good for saving space when exact values aren’t needed.
DOUBLE	Higher precision	Good for more accurate scientific or statistical calculations.

Do not use FLOAT / DOUBLE

• Since FLOAT / DOUBLE are approximations, They may produce results like:

  • 19.99 + 5.00 = 24.989999999.

• Not advisable to use on cases that require exact values / calculation (Money, Prices and Financial calculations).

SensorReadings Table

ReadingId	Temperature (FLOAT)	Pressure (DOUBLE)
1	21.5	1013.256781234
2	22.1	1012.998312678

tip

• Temperature uses FLOAT because slight imprecision is acceptable.

• Pressure uses DOUBLE because higher precision is needed.

String

• String types store text, such as names, descriptions, and email addresses.

VARCHAR

• Stands for Variable Character and is used to store text values with a maximum length of n characters. It is the most common data type for storing text in relational databases.

• The key feature of VARCHAR is that it only uses as much storage as needed, up to the maximum length defined.

• For instance, VARCHAR(50) can store any text up to 50 characters. If a user’s name is "Bob", it only stores 3 characters, not all 50.

• Can be used for fields that have varied text lengths like: names, emails, and comments, set a reasonable limit for data validation and save storage space compared to CHAR.

Users Table

UserId	Name (VARCHAR(50))	Email (VARCHAR(100))
1	Alice Lee	alice@example.com
2	Bob Smith	bob.smith@example.com

tip

• Name is VARCHAR(50) because names rarely exceed 50 characters.

• Email is VARCHAR(100) because emails vary in length and can be longer.

CHAR

• Fixed-length character data type used to store text values. Unlike VARCHAR, which stores variable-length text, CHAR always uses the full length defined by n.

• If the stored text is shorter than the specified length, the database pads the value with spaces to match the fixed size.

• For instance, CHAR(5), Storing "Hi" becomes "Hi   " (padded with 3 spaces)

• Can be used when all values have a fixed, predictable length, storage requirements are consistent and speed and consistency matter more than saving space.

• Common use cases include: Country codes ("CA", "US"), Gender codes ("M", "F") and Status flags ("A", "I")

Countries Table

Code (CHAR(2))	CountryName
US	United States
CA	Canada
GB	United Kingdom

tip

Code is CHAR(2) because every country code is exactly 2 characters.

Status Table

StatusId	StatusCode (CHAR(1))	Description
1	A	Active
2	I	Inactive

tip

• StatusCode is CHAR(1) since it always contains exactly one character.

TEXT

• Used to store large amounts of text, much longer than what VARCHAR typically handles. It is ideal for fields where the length of the text is unpredictable or may exceed typical limits.

• Can store paragraphs, long descriptions, notes, comments, or even entire documents depending on the database system.

• Take note that TEXT type: Does not require a maximum length to be defined, can store very large blocks of text (thousands to millions of characters) and its not usually indexed by default due to its size.

• Use TEXT when: Expect long or unstructured text, cannot predict the maximum length and storing content like: Descriptions, Comments or reviews, Blog posts, Logs, Messages and JSON.

Articles Table

ArticleId	Title (VARCHAR(200))	Body (TEXT)
1	"What Is a Database?"	A long article body...
2	"Understanding SQL Joins"	A detailed explanation...

tip

• Title uses VARCHAR because titles have reasonable limits.

• Body uses TEXT because article content can be very long.

Feedbacks Table

FeedbackId	CustomerId	Comments (TEXT)
1	23	"The service was great! Really fast."
2	45	"I had issues logging in... (long text)"

tip

• Comments may vary from a few words to long paragraphs, perfect for TEXT.

Date / Time

• Database systems provide specialized data types for storing temporal information such as dates, times, and full timestamps. These types ensure consistent formatting, accurate sorting, and correct comparison of date- and time-based data.

DATE

• The DATE data type stores only the calendar date: year, month, and day without any time information.

  • Format: YYYY-MM-DD

• Used to store: Birth dates, Booking dates, Due dates and Simple schedules.

TIME

• The TIME data type stores only the time of day, without any date.

  • Format: HH:MM:SS (24-hour format)

• Used to store: Store opening / closing hours, Event start times and Shift schedules.

TIMESTAMP / DATETIME

• These types store both date and time together, representing a specific moment.

  • Format: YYYY-MM-DD HH:MM:SS

• Used to store: Logging when a record was created, Tracking updates, Event registration times and Audit trails.

TIMESTAMP vs DATETIME

• Different database engines handle them slightly differently:

Type	Automatically Tracks Timezone?	Auto-update Options	Range
TIMESTAMP	Yes (in many DBs)	Often supports auto-update	Smaller range
DATETIME	No	More manual	Larger range

Boolean

• Used to store values that represent true or false conditions. It is commonly used to track the state of an item, the result of a comparison, or any situation where there are only two possible outcomes.

• It is used for cases such as whether an account is active or disabled, whether a user has verified their email, and flags such as:

  • is_deleted
  • is_admin
  • is_available
  • yes / no survey answers
  • feature toggles

• Most relational databases treat BOOLEAN in a similar way. The logical meaning is the same but some store it differently internally:

Database	TRUE	FALSE
MySQL	1	0
PostgreSQL	true	false
SQL Server	1	0 (BIT type)
Oracle	No native BOOLEAN in tables (uses NUMBER(1))

Sample Row

id	full_name	is_active	is_verified
1	Jane Smith	TRUE	FALSE
1	John Doe	TRUE	TRUE

Expressions Using BOOLEAN

• BOOLEAN values often appear in WHERE clauses:

• Active users

SELECT *
FROM users
WHERE is_active = FALSE;

• Non-Active users

SELECT *
FROM users
WHERE is_active = TRUE;