Key Concepts in Databases: Tables, Rows, Columns, and Keys

Introduction Relational databases organize data into tables, which makes it easy to store, retrieve, and manipulate information. In this post, we’ll explore these fundamental building blocks and their roles in database design. 1. Tables A table is like a spreadsheet where data is stored in rows and columns. Each table typically represents an entity, such as users, orders, or products. ID Name Email Age 1 Alice alice@example.com 30 2 Bob bob@example.com 25 2. Rows Each row in a table represents a single record. For example, in the users table above: Row 1 represents Alice. Row 2 represents Bob. Code Example: Add a New Row INSERT INTO users (Name, Email, Age) VALUES ('Charlie', 'charlie@example.com', 27); 3. Columns Columns define the attributes or properties of the entity. For example: Name: Stores user names. Email: Stores email addresses. Challenge: Add a new column for user phone numbers. What SQL query would you write? 4. Keys Keys are special fields used to identify and link data. Primary Key: A unique identifier for each row. Example: ID in the users table. Ensures no two rows have the same value in the key column. Example Query: SELECT * FROM users WHERE ID = 1; Foreign Key: Links one table to another, establishing relationships. Example: The customer_id in an orders table links to the ID in the users table. Example Query: SELECT * FROM orders WHERE customer_id = 1; -- Retrieves orders for Alice Challenge: Spot the Issues Scenario: Consider this incomplete table design. ID Name Age Phone 1 Alice 30 123-456-7890 2 Bob NULL 555-555-5555 What issues do you see? What constraints would you add to ensure data quality (e.g., no NULL in Age)?

Mar 13, 2025 - 08:25

Key Concepts in Databases: Tables, Rows, Columns, and Keys

Introduction
Relational databases organize data into tables, which makes it easy to store, retrieve, and manipulate information. In this post, we’ll explore these fundamental building blocks and their roles in database design.

1. Tables
A table is like a spreadsheet where data is stored in rows and columns. Each table typically represents an entity, such as users, orders, or products.

ID	Name	Email	Age
1	Alice	alice@example.com	30
2	Bob	bob@example.com	25

2. Rows
Each row in a table represents a single record. For example, in the users table above:
Row 1 represents Alice.
Row 2 represents Bob.

Code Example: Add a New Row

INSERT INTO users (Name, Email, Age) 
VALUES ('Charlie', 'charlie@example.com', 27);

3. Columns
Columns define the attributes or properties of the entity. For example:
Name: Stores user names.
Email: Stores email addresses.

Challenge: Add a new column for user phone numbers. What SQL query would you write?

4. Keys
Keys are special fields used to identify and link data.

Primary Key: A unique identifier for each row.

Example: ID in the users table.
Ensures no two rows have the same value in the key column.
Example Query:

SELECT * FROM users WHERE ID = 1;

Foreign Key: Links one table to another, establishing relationships.

Example: The customer_id in an orders table links to the ID in the users table.

Example Query:

SELECT * 
FROM orders
WHERE customer_id = 1; -- Retrieves orders for Alice

Challenge: Spot the Issues
Scenario: Consider this incomplete table design.

ID	Name	Age	Phone
1	Alice	30	123-456-7890
2	Bob	NULL	555-555-5555

What issues do you see?
What constraints would you add to ensure data quality (e.g., no NULL in Age)?