✅ Tables are the main structures in the relational database. Each table always represents a single specific subject. The logical order of rows and columns within the table is of absolutely no importance. Every table contains at least one column, known as a primary key, that uniquely identifies each of its rows. In fact, data in a relational database can exist independent of the way it is physically stored in the computer because of these last two table characteristics. This is great news for users because they aren't required to know the physical location of a row in order to retrieve its data. The subject that a given table represents can be either an object or an event. When the subject is an object, the table represents something that is tangible, such a person, place, or thing. Regardless of its type, every object has characteristics that can be stored as data. You can then process this data in almost infinite numbers of ways. Pilots, products, machines, students, buildings, and equipment are all examples of objects that can be represented by a table.
✅ Columns define the type of data stored (like “Name” or “Age”).
A column is the smallest unit in a database. It represents a specific characteristic of the subject of the table. Columns store data, and you can retrieve that data to present it in nearly any format. The quality of the information you get from your data depends heavily on how well you maintain the structure and integrity of the columns. It's essential to ensure that columns are well-designed and contain accurate data.
Every column in a well-designed database contains one and only one value, and its name tells you the type of value it holds. This makes it easy to enter data. For example, columns with names like FirstName, LastName, City, State, and ZipCode clearly indicate what kind of data belongs there. This also makes it simple to sort data by a particular column (e.g., by State) or search for all records where the LastName is “Smith.”
✅ Rows represent a single entry or record in the table. For example, imagine a table storing information about books:
| ID | Title | Author | Year |
| 1 | The Great Gatsby | F. Scott Fitzgerald | 1925 |
| 2 | 1984 | George Orwell | 1949 |
| 3 | To Kill a Mockingbird | Harper Lee | 1960 |
Each row represents a different book, while the columns store information about the book’s title, author, and year of publication. Relational databases are called "relational" because tables can be connected (or related) to each other. For example, an "Authors" table could store author details separately, linking to the "Books" table. SQL helps you create, manage, and retrieve data from these tables efficiently. In the next sections, we’ll start writing SQL queries to interact with a database!
✅ Keys are special columns that play very specific roles within a table. The type of key determines its purpose within the table. Although a table might contain several types of keys: the primary key and the foreign key.
A primary key consists of one or more columns that uniquely identify each row in a table. In other words, no two rows can have the same value for a primary key.
When a primary key is made up of multiple columns, it is known as a composite primary key.
The primary key is important for two main reasons:
It uniquely identifies a specific row throughout the entire database.
It helps maintain table-level integrity by ensuring that each row in the table is unique.
Example of a Primary Key:
In a table called Agents, the AgentID column is a primary key because it uniquely identifies each agent in the table.
| AgentID | AgentFirstName | AgentLastName | DateHired | AgentHomePhone |
|---|---|---|---|---|
| 1 | William | Thompson | 15-May-01 | 555-2681 |
| 2 | Scott | Bishop | 10-Feb-03 | 555-2666 |
| 3 | Carol | Viescas | 09-Sep-00 | 555-2571 |
A foreign key is a column in a table that refers to the primary key of another table. This helps establish a relationship between two tables.
For example, the Entertainers table has a foreign key (AgentID) that refers to the primary key (AgentID) in the Agents table. This relationship helps link each entertainer to a specific agent.
| EntertainerID | AgentID (Foreign Key) | EntertainerName | EntertainerPhone |
|---|---|---|---|
| 1001 | 1 | Carol Peacock Trio | 555-2691 |
| 1002 | 3 | Topazz | 555-2591 |
| 1003 | 3 | JV & the Deep Six | 555-2511 |
In this example:
AgentID in the Agents table is the primary key.
AgentID in the Entertainers table is the foreign key that helps link each entertainer to the corresponding agent.
Foreign keys are crucial because they ensure that relationships between tables are maintained and that data integrity is preserved. For example, if an agent is deleted from the Agents table, the corresponding entertainers in the Entertainers table will either need to be updated or deleted, preventing orphaned rows (records that are no longer related to any other table).
In relational databases, a view is like a virtual table that allows you to look at data from one or more tables in a specific way. Unlike a regular table, a view doesn’t store data itself. Instead, it pulls data from existing tables.
Views are useful because they allow you to present data in different ways without modifying the underlying tables. For example, if you have multiple tables storing customer data, order data, and engagement data, you could create a view to show a summary of all customer engagements in one place.
A view can pull data from multiple tables, making it easier to see related information. In this example, a view could combine data from the Customers and Engagements tables.
In a relational database, a view can be created using SQL queries. The view will only store its structure (the query) and not the actual data.
Example of a View:
Let’s say you want to view customer engagement details along with customer names. You could create a view called Customer_Engagements:
| EngagementNumber | CustFirstName | CustLastName | StartDate | EndDate |
|---|---|---|---|---|
| 3 | Doris | Hartwig | 2016-09-10 | 2016-09-15 |
| 13 | Peter | Brehm | 2016-09-17 | 2016-09-20 |
| 14 | Doris | Hartwig | 2016-09-29 | 2016-09-29 |
Here’s a simplified example of how to create a view using an SQL query:
CREATE VIEW Customer_Engagements AS
SELECT EngagementNumber, CustFirstName, CustLastName, StartDate, EndDate
FROM Customers
JOIN Engagements ON Customers.CustomerID = Engagements.CustomerID;
This query pulls the relevant columns from both Customers and Engagements tables and combines them into a view. The view then presents the data as though it’s a single table, but it’s really just a saved query.
Keys help maintain data integrity by ensuring that each row is unique and that relationships between tables are properly established.
Foreign keys are essential for linking tables and ensuring that data across tables stays consistent.
Views are a powerful tool in SQL that allow you to work with data from multiple tables in a flexible way without changing the underlying data structure.
As you continue to learn SQL and relational databases, understanding how to use keys and views will be crucial for creating efficient, maintainable databases.
On April 2, 2025 Iryna wrote:
You did a great job!!! 👏