Search⌘ K
AI Features
Home
Courses
Database Administration for Beginners

Relational Model

Explore the core components of the relational model in databases by learning how tables, rows, and columns organize and represent data. Understand how these elements form the foundation for managing information in applications like online stores, enabling you to structure data efficiently and accurately.

We'll cover the following...

Imagine trying to manage an online store. You have information about products (names, prices, stock levels), customers (names, emails, addresses), and orders (who bought what and when). How would you keep all this information organized and easily accessible? Managing this with simple text files or spreadsheets would quickly become chaotic and inefficient, especially as the store grows. This is where the relational model, with its structured approach using tables, rows, and columns, becomes incredibly powerful. It provides a clear, systematic way to store, manage, and retrieve data, making an online store and many other applications run smoothly.

In this lesson, we’ll dive into the fundamental building blocks of relational databases. By the end of this lesson, we will be able to:

  • Understand the core components of the relational model: tables, rows, and columns.

  • Recognize how these components are used to structure data effectively.

  • Identify tables, rows, and columns in a given database schema.

  • Appreciate why this structured approach is essential for database management.

Let’s get started and explore how these elements help us build robust and organized databases!

Understanding the core: Tables, rows, and columns

In our journey to understand database administration, we’ve learned that relational databases are a common way to store information. The relational part comes from how data is structured and related. The most fundamental concepts in this model are tables, rows, and columns. Think of them as the skeleton of our database, giving it shape and order. Without them, data would be a jumbled mess, difficult to find, and even harder to make sense of.

Tables: The organizers of information

Tables are the primary structures used to organize data in a relational database. Each table is designed to hold information about a specific type of item or concept. For instance, in our OnlineStore database, we wouldn’t mix customer information with product details in the same table; instead, we’d have a separate table for customers and another for products. This separation makes data management logical, efficient, and easier to maintain. It also helps prevent data redundancy, where the same piece of information is unnecessarily repeated in multiple places.

A table, often referred to as a relation in formal database theory, is a collection of data organized into a two-dimensional structure of rows and columns. Imagine a spreadsheet; a table in a database is conceptually similar. It has a name that identifies what kind of information it contains (e.g., Products, Customers, Orders).

Each table is designed to store information about a specific entity type. An entity can be a person, place, thing, event, or concept about which we want to store data. For example, in our OnlineStore database, Products is a table that stores information exclusively about the products the store sells.

Let’s look at the Categories table from our OnlineStore database.

MySQL
-- This is how the Categories table is defined.
-- It stores different categories for products.
CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY AUTO_INCREMENT, -- Unique identifier for each category
    CategoryName VARCHAR(50) NOT NULL UNIQUE -- Name of the category, must be unique
);

This Categories table is designed to hold information about different product categories, like Electronics or Books.

Columns: Defining the attributes

Columns define the structure of a table. They specify what kind of information is stored for each entry in the table. Without columns, we wouldn’t know what each piece of data in a table represents. For example, if a table just had Laptop, 1200.00, 50, we wouldn’t know that Laptop is the product name, 1200.00 is its price, and 50 is the stock quantity unless we have clearly defined columns. Columns also enforce data types, ensuring that a price is stored as a number and a name as text, which is crucial for data integrity and consistency.

A column, also known as an attribute or field, is a vertical component of a table. It represents a specific characteristic or piece of information about the entity the table describes. Each column has a name (e.g., ProductName, Price) and a specific data type (e.g., VARCHAR for text, DECIMAL for currency, INT for whole numbers). The data type dictates what kind of values can be stored in that column.

Consider our Products table. Some of its columns would be:

  • ProductID: An integer to uniquely identify each product.

  • ProductName: A string of characters (text) for the product’s name.

  • Price: A decimal number to store the product’s price.

  • Stock: An integer for the quantity in stock.

Here’s a snippet of the Products table definition, highlighting its columns:

MySQL
-- Snippet of the Products table definition
-- This table stores details about each product available in the online store.
CREATE TABLE Products (
    ProductID INT PRIMARY KEY AUTO_INCREMENT, -- Unique ID for the product
    ProductName VARCHAR(50) NOT NULL UNIQUE, -- Name of the product
    CategoryID INT,                          -- Foreign key linking to Categories table
    Price DECIMAL(10, 2) NOT NULL,           -- Price of the product
    Stock INT NOT NULL,                      -- Current stock level
    -- ... other columns
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

In this structure, ProductID, ProductName, CategoryID, Price, and Stock are all columns. Each column is designed to hold a specific type of information for every product in the table.

Rows: Representing individual records

Rows are where the actual data lives. Each row in a table represents a single, complete record or instance of the entity the table describes. If columns define what information we store, rows provide the actual instances of that information. Without rows, a table would just be an empty structure of column definitions. For an online store, each row in the Products table would represent one specific product the store sells.

A row, also known as a tuple or record, is a horizontal component of a table. It contains a set of related data values, with one value for each column defined in the table. Each row represents a single item or entry.

For example, in the Products table, a single row might look like this:

ProductID

ProductName

CategoryID

Price

Stock

1

Laptop

1

1200.00

50

2

Smartphone

1

800.00

30

4

Novel A

2

15.00

200

Here, the first row represents a Laptop, the second a Smartphone, and the third Novel A. Each of these is a distinct record in the Products table.

When we want to see the actual data, we often query the table. For example, to see all products:

MySQL 8.0
-- Select selected columns and all rows from the Products table
SELECT ProductID, ProductName, CategoryID, Price, Stock
FROM  Products;

This query would return all the rows stored in the Products table, with each row displaying the values for the selected columns, giving us a complete picture of our product inventory.

So, to summarize:

  • We use tables to hold information about a specific type of thing (like Products or Customers).

  • Within a table, columns define the specific pieces of information we want to store for each thing (like ProductName or Email).

  • Rows are the individual things themselves, with specific values for each column (like a particular product, Laptop, or a customer, John Doe).

These three components work together to create a well-organized and efficient database, which is the foundation of the relational model. Understanding them clearly is the first big step in becoming a proficient Database Administrator!

Sample scenarios and examples

Let’s solidify our understanding with examples from our OnlineStore database.

Scenario 1: Managing customer information

Our OnlineStore needs to keep track of its customers. For this, we have a Customers table.

  • Table: Customers

    • This table is dedicated solely to storing information about each customer.

  • Columns:

    • CustomerID (INT): A unique number for each customer.

    • CustomerName (VARCHAR): The customer’s name.

    • Email (VARCHAR): The customer’s email address.

    • Phone (VARCHAR): The customer’s phone number.

    • Address (VARCHAR): The customer’s shipping address.

    • And so on for other relevant details like LastLogin, CustomerTier, etc.

  • Rows: Each row in the Customers table represents one specific customer. For example:

CustomerID

CustomerName

Email

Phone

Address

1

John Doe

johndoe@example.com

555-1111

123 Main St

2

Jane Smith

janesmith@example.com

555-2222

456 Oak Ave

Scenario 2: Tracking orders

When a customer makes a purchase, we need to record that order. This is done in the Orders table.

  • Table: Orders

    • This table stores data for every order placed.

  • Columns:

    • OrderID (INT): A unique number for each order.

    • CustomerID (INT): The ID of the customer who placed the order (linking to the Customers table).

    • OrderDate (DATE): The date the order was placed.

    • TotalAmount (DECIMAL): The total cost of the order.

    • DeliveryStatus (ENUM): The current status of the delivery (e.g., Pending, Shipped, Delivered).

  • Rows: Each row represents a single order made by a customer. For example:

OrderID

CustomerID

OrderDate

TotalAmount

DeliveryStatus

1

1

2025-03-01

150.00

Shipped

2

1

2025-03-05

220.00

Delivered

3

2

2025-03-03

320.00

Delivered

Example: Viewing specific columns from a table

Let’s say we want to see just the names and prices of all products in our OnlineStore. We can use a SELECT statement to specify which columns we’re interested in.

MySQL 8.0
-- Select only the ProductName and Price columns from the Products table
SELECT ProductName, Price
FROM   Products;

In this output:

  • Products is the table.

  • ProductName and Price are the selected columns.

  • Each line of the result (e.g., Laptop, 1200.00) represents data from one row in the Products table, but only for the specified columns.

By understanding how tables, rows, and columns function, we can effectively structure and query data to get exactly the information we need. This is the essence of working with relational databases!

Quiz

Let’s test our understanding with a few questions.

Technical Quiz
1.

In a relational database, what is the primary structure used to organize and store data about a specific type of item (e.g., products, customers)?

A.

A row

B.

A column

C.

A table

D.

A query

1 / 3

Excellent work on completing this lesson! We’ve laid a crucial foundation by understanding tables, rows, and columns. These are the absolute bedrock of how relational databases store and organize information. We saw how tables group related information (like all our Products), columns define the specific attributes of that information (like ProductName and Price), and rows represent each individual item (like a specific Laptop).

As we move forward, these concepts will appear again and again. Keep practicing, keep exploring, and we’ll become more comfortable navigating and understanding database structures. We’re building a strong base for the more advanced topics to come! Keep up the great effort!