Lesson 1.2: Relational Database Concepts

In the previous lesson, we introduced the concept of databases. Now, we'll dive deeper into the core components of Relational Databases, which are fundamental to understanding how data is organized and accessed using SQL.

Tables, Columns, and Rows

Relational databases organize data into structures called tables. Think of a table as a spreadsheet:

• Table: A collection of related data. For example, a table might store information about customers, products, or orders.
• Column: A vertical set of data within a table. Each column represents a specific attribute or characteristic of the data. For instance, in a "Customers" table, columns might be "CustomerID," "FirstName," "LastName," and "Email."
• Row: A horizontal set of data within a table. Each row represents a single instance or record of the data. In a "Customers" table, each row would represent a single customer.

Example:

Let's visualize a simple "Customers" table:

• The entire structure is the table named "Customers."
• "CustomerID," "FirstName," "LastName," and "Email" are the columns.
• Each line (e.g., "1 | John | Doe | john.doe@example.com") is a row.

Keys: Ensuring Data Integrity

Keys are a critical concept in relational databases. They are used to establish relationships between tables and enforce data integrity. Here are the main types of keys:

Primary Key

• A Primary Key is a column (or a set of columns) that uniquely identifies each row in a table.
• Characteristics of a Primary Key:
  • Unique: No two rows can have the same primary key value.
  • Not Null: A primary key column cannot contain NULL values.
• In our "Customers" table, "CustomerID" is a good candidate for the primary key because each customer has a unique ID, and it cannot be empty.

Foreign Key

• A Foreign Key is a column (or a set of columns) in one table that refers to the Primary Key in another table.
• Foreign keys establish relationships between tables.
• For example, if we have an "Orders" table, it might have a "CustomerID" column that is a foreign key referencing the "CustomerID" in the "Customers" table. This links each order to the customer who placed it.

Unique Key

• A Unique Key is a column (or a set of columns) that ensures that the values in the column(s) are unique across all rows in the table.
• Difference from Primary Key:
  • A table can have only one primary key, but it can have multiple unique keys.
  • Unique key columns can allow NULL values (though implementations vary slightly).
• In our "Customers" table, "Email" could be a unique key, ensuring that each customer has a unique email address.

Importance of These Concepts

Understanding tables, columns, rows, and keys is fundamental to working with relational databases.

• They define how data is structured and organized.
• They allow us to query and retrieve specific information efficiently.
• Keys ensure data integrity and establish relationships between different sets of data.

In the following lessons, we will build upon these concepts as we learn to use SQL to interact with relational databases.