Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 17

Relational Database Fundamentals

Recommended resources

Hello! Welcome to the first lesson of Module 3.

In our last lesson, we traced the entire request-response cycle in Django. We saw how a request flows through middleware to a view, which then uses a template to generate a response. A key part of that cycle, which we deliberately skipped, was the view's interaction with the "Model" layer. Today, we begin to fill that gap.

Our learning outcome is to explain the core concepts of the relational database model (tables, rows, columns, keys). This is the theoretical foundation upon which Django's database functionality—the "M" in its MTV pattern—is built. While you've likely worked with tabular data in your data science projects, this lesson will formalize those concepts from a database engineering perspective, providing the vocabulary and principles needed to design robust data structures.

1. What is a Relational Database?

At its core, a relational database is a system for storing and organizing structured data. The "relational" aspect, first proposed by computer scientist E.F. Codd in 1970, comes from the mathematical concept of relations, which provides a formal way to define connections between different sets of data.

Let's begin with a short video that introduces the history and fundamental terminology of the relational model.

Introduction to Relational Data Model

This video from Neso Academy will introduce you to the origins of the relational model and define its most basic terms.

Watch the segments from 00:06 to 03:00. Pay close attention to the three core terms introduced: 'relation', 'tuple', and 'attribute'.

As the video explained, the relational model provides a structured and reliable way to manage data. This makes it ideal for applications that require:

  • Structured Data: Information that fits neatly into a predefined format (e.g., user profiles, product catalogs).
  • Data Integrity: Rules that ensure the data is accurate and consistent.
  • Complex Queries: The ability to retrieve and combine data from multiple sources in sophisticated ways.

2. The Anatomy of a Relational Database

The video introduced the formal, mathematical terms for the components of a relational database. Let's solidify this vocabulary, as it's essential for understanding database theory.

  • Relation (or Table): A collection of data organized into columns and rows. It represents a single "entity type," like Users or Products.
  • Attribute (or Column/Field): A named column of a relation. It defines a specific property of the entity, such as username or price.
  • Tuple (or Row/Record): A single entry in a relation. It represents one specific "instance" of the entity, such as a particular user 'alice' or a specific product 'Laptop'.

The following image provides a fantastic visual summary of these terms, along with a few others we'll discuss shortly.

Relational Database Model Concepts
This diagram shows a `STUDENT` table. The entire table is a **Relation**. Each row is a **Tuple**, and each column header (like `NAME` or `CITY`) is an **Attribute**. The number of tuples is the **Cardinality**, and the number of attributes is the **Degree**. The diagram also labels the set of possible values for each attribute as its **Domain** and identifies `ROLL_NO` as the **Primary Key**.

To bridge theory and practice, it's useful to map these relational terms to their common SQL counterparts.

Relational database

This section from the Wikipedia article on Relational databases provides a handy table summarizing the terminology.

Please read the section titled 'Terminology' and focus on the table that maps relational terms (like Tuple) to SQL terms (like Row).

3. Domains and Atomicity: Defining a Column's Rules

Every attribute (column) in a relation is governed by two important principles: domain and atomicity.

Domain

A domain is the set of all possible, permissible values for an attribute. It's a constraint that defines the data type and format. For example:

  • The domain for an age attribute might be positive integers.
  • The domain for a status attribute might be the set of strings {'active', 'inactive', 'suspended'}.

Atomicity

A value is atomic if it is indivisible. In database design, this means that each cell (the intersection of a row and column) should hold only one single, indivisible piece of information. This principle is critical for data integrity and makes it much easier to query and manipulate data.

The video below gives an excellent, practical example of why atomicity is so important.

A Beginner's Guide to Designing a Relational Database (Databases 101)

The following clip from 'A Beginner's Guide to Designing a Relational Database' demonstrates the concept of atomic values using a common mistake: storing a full name in a single column.

Watch from 09:37 to 10:50. Note how splitting the name column into first_name and last_name makes future operations like sorting or filtering much simpler.

4. Keys: The Heart of Relationships

Keys are special attributes that are fundamental to the power of the relational model. They allow us to uniquely identify records and, crucially, to form links between different tables.

Primary Key (PK)

A primary key is an attribute (or a set of attributes) whose value uniquely identifies every tuple (row) in a relation. A primary key has two strict rules:

  1. It must be unique: No two rows can have the same primary key value.
  2. It cannot be null: A primary key must always have a value.

This enforcement of a unique, non-null primary key for every row is called entity integrity.

A common practice is to use a surrogate key: an artificial key (usually an auto-incrementing integer like id) that has no business meaning but serves purely to identify the row. This is generally preferred over a natural key (like a username or email), which has business meaning but might need to change over time, which is problematic for a key.

Foreign Key (FK)

A foreign key is an attribute in one table that refers to the primary key of another table. This is how we create relationships. For example, in a Posts table, a user_id column would be a foreign key that points to the id (primary key) of the Users table. This establishes that each post belongs to a user.

Using foreign keys to ensure that relationships between tables are consistent is called referential integrity. For instance, it can prevent you from creating a post with a user_id that doesn't exist in the Users table.

This next video provides a comprehensive explanation of the different types of keys and the important concept of data integrity.

A Beginner's Guide to Designing a Relational Database (Databases 101)

Let's return to the 'A Beginner's Guide' video to explore keys and integrity constraints in detail.

Watch the segment from 10:50 to 14:14 to learn about various key types (primary, unique, surrogate, foreign). Then, watch from 07:07 to 09:37, which explains the three types of data integrity (entity, referential, domain) that keys help enforce.

Test your understanding!

Imagine you are designing a database for a simple e-commerce site. You start with a single table called ORDERS to store all order information. Here is a sample row:

order_id customer_details products_ordered order_date total_price
101"John Doe (john.doe@email.com)""1x Laptop, 2x Mouse"2023-10-271550.00

Based on the principles of atomicity and relational design, identify at least two major problems with this table structure. How would you begin to fix them?

Show answer

There are two significant problems with this table design:

  1. Violation of Atomicity:

    • The customer_details column is not atomic. It combines the customer's name and email into a single string. This makes it difficult to, for example, search for all orders by the last name "Doe" or send an email to the customer without parsing the string. This should be split into customer_name and customer_email columns (and even better, into first_name and last_name). Ideally, this customer information would live in a separate CUSTOMERS table.
    • The products_ordered column is also not atomic. It stores multiple products and their quantities in one string. This makes it nearly impossible to perform queries like "how many mice were sold in total?" or "which customers bought a laptop?".

  2. Lack of Relational Structure: The products_ordered column is a clear sign that this structure isn't properly relational. A single order can have multiple products, which implies a "many-to-many" or "one-to-many" relationship. The correct approach would be to create additional tables: a PRODUCTS table to store product information (name, price) and a ORDER_ITEMS link table to connect orders and products. The ORDER_ITEMS table would have columns like order_id, product_id, and quantity, with each row representing one product within one order.

order_id is a good candidate for a primary key for the ORDERS table.

Conclusion

In this lesson, you've learned the fundamental language and rules of the relational database model. This theoretical groundwork is indispensable for building well-structured, reliable backend systems.

Key Takeaways:

  • A relational database organizes structured data into relations (tables).
  • Each table consists of attributes (columns) that define the data's properties and tuples (rows) that represent individual records.
  • The values in each column must conform to a specific domain and be atomic (indivisible).
  • Primary Keys uniquely identify each row in a table, ensuring entity integrity.
  • Foreign Keys link tables together by referencing primary keys, ensuring referential integrity.

Preview of the Next Lesson:
Now that you understand what a relational database is, you might be wondering how we interact with one from our Django application. Do we have to write complex SQL commands in our Python code?

Thankfully, no. In the next lesson, you will learn how the Django ORM maps Python classes to database tables. We will explore Django's Object-Relational Mapper (ORM), a powerful abstraction layer that allows you to define your database structure and interact with it using simple, intuitive Python code, without ever writing a line of SQL directly.