Relational Data Model

Once upon a time, back when dinosaurs were huge... well, actually, in 1970, a guy named Edgar Codd decided that the chaos in data had gone way past "creative mess" and was more like a black hole sucking up time and resources. Edgar thought long and hard and came up with a way to organize everything, basically laying the foundation for a whole world where data is lined up in neat tables, like books on a shelf in a perfect library. Rows, columns, order—none of that "eh, good enough" or "well, you get the idea" stuff.

The relational model is all about representing data as tables, where each table is made up of rows and columns. It's pretty intuitive: rows are records, columns are properties or attributes of those records. This approach makes it way easier to process and mess with your data.

Imagine you're placing an order in an online store. For your order to be processed and delivered, the system needs to keep track of a bunch of details: who you are (the customer), which delivery service you picked, where to deliver, and how to track the package status.

If all that info was stored in one giant table, it would be a mess: data would get duplicated, updating stuff would be a pain, and finding what you need would be even harder. The relational approach helps keep things tidy by splitting info into tables that can share data with each other.

This diagram shows an example of how data can be organized for tracking deliveries:

• Customers – this is where info about each client is stored, with a unique Customer Number.
• Delivery Services – this table lists the services that can deliver stuff, each with its own unique Service Number.
• Order Delivery – this is the central, connecting table. Each row here is a specific delivery. It has its own Delivery Number, and also references the customer, the delivery service, maybe a specific order, the date, and the current status.

The arrows show how records from the Order Delivery table are linked to records in the Customers and Delivery Services tables.

Here's a sample Order Delivery table:

Why is this approach awesome?

• Info about customers or delivery services is stored just once, so you don't get repeats in every single delivery record.
• Links via unique numbers (aka keys) make sure you can't have, say, a delivery without a real customer or through a non-existent service.
• It's easy to combine data from different tables to get fancy reports or specific selections.
• When you update data, you do it in one place and it's instantly available for all related operations (like a delivery service's phone number).

Structure of a Relational Database

The core of a relational database is the tables. Each table has:

• A name (Table Name) so we can identify it—like customers.
• A set of columns (Columns/Attributes), which define the properties of the object.
  • When naming columns that are unique numbers (primary keys), we usually use the name_id pattern. The _id suffix means "identifier". For example: Customer Number becomes customer_id.
  • For the customers table, for example: you might have customer_id, first_name, email, and so on.
• Data in rows (Rows/Records) – one row in the customers table will have all the info about a specific customer (101, Alex Song, and so on).

Let's look at a sample table—customers:

Table Keys

To uniquely identify each row in a table, we use a primary key (PK). This is a column (or a few) whose values are unique for every row and can't be empty. Think of it as a unique number for each record, like customer_id in our customers table: it uniquely identifies every customer.

The primary key is like a passport, or more specifically, its number: it's unique for each object. And it helps avoid problems with duplicate rows.

To set up relationships between tables, we use a foreign key (FK). This is a column in one table that points to the primary key in another table. Foreign keys are the "bridges" between tables. Often, the foreign key column name matches the primary key it's referencing. For example, in the deliveries table, the customer_id column is a foreign key holding values from the customer_id column in the customers table, linking the delivery to the customer.

This is the same scheme we saw before, but now it looks more realistic.

• The customers table stores info about clients; its primary key is customer_id.
• The delivery_services table stores data about delivery services; its primary key is service_id.
• The orders table (shown for completeness, since order_id is referenced from the deliveries table) is for order info, with order_id as its primary key.
• The deliveries table is the central one for tracking delivery operations. It has:
  • Its own primary key: delivery_id.
  • Three foreign keys to set up relationships:
    • customer_id (FK): links the delivery to a specific customer from the customers table.
    • service_id (FK): links the delivery to the chosen service from the delivery_services table.
    • order_id (FK): links the delivery to the corresponding order from the orders table.
  • The created_date field shows when the delivery record was created.

This use of primary and foreign keys ensures data integrity. The system won't let you create a delivery record if the specified customer_id or order_id doesn't exist in the related tables, and it lets you flexibly pull related info.

Advantages of the Relational Model

The relational model has a bunch of big advantages that make it the leader among other approaches:

1. Simple structure. Tables with columns and rows are easy to get and work with.
2. Flexible data handling. You can easily add or change data without breaking things.
3. Support for complex queries. With SQL, you can pull data from tons of tables, combine it, filter it, and basically do whatever your task needs. Like, finding all students enrolled in a specific course—easy!
4. Data integrity via keys. Using primary and foreign keys guarantees your data stays consistent. You can't, for example, add a student to a course if that course doesn't exist.

The relational data model is the foundation of modern databases. It's simple, powerful, and perfect for storing structured data. Like Edgar Codd said: "Organize or die!" (okay, he said it a bit differently, but you get the idea...). In the next lecture, we'll see how the relational model is different from other types of databases (like NoSQL) and where each one works best.