Logical vs. Physical Data Models

The Principle of Abstraction: Data Independence

The ability to separate the user's view of the data from its physical storage is a cornerstone of database design. This is achieved through a concept known as the three-schema architecture. To get a clear overview of this principle, let's watch a short video.

As the video explained, we have three levels of data representation:

• External Schema: Tailored views for specific applications or users (e.g., a report that only shows employee names and departments).
• Conceptual Schema: The unified, holistic view of the entire database's structure. This is what we call the logical data model. It describes the entities, their attributes, and their relationships.
• Internal Schema: The description of how the data is physically stored on disk. This is the physical storage structure.

The most important takeaway here is Physical Data Independence. This is the ability to change the physical storage structure (the Internal Schema) without affecting the logical data model (the Conceptual Schema). For example, a database administrator (DBA) could move the data to a new, faster disk, or add a new index to speed up queries, all without any application developer having to change a single line of their code. This abstraction is fundamental to database management.

Now, let's look at the logical and physical levels in more detail.

The Logical Data Model: The Blueprint

The logical data model is the architect's blueprint for the database. It's an abstract representation that defines all the pieces of data and how they relate to one another, based on business requirements.

Key characteristics of a logical data model:

• Focuses on business entities: It models concepts like Customers, Products, and Orders.
• Defines attributes and relationships: It specifies that a Customer has a name and address, and that a Customer can place multiple Orders.
• Technology-agnostic: A logical model isn't tied to any specific database system (like PostgreSQL or MySQL). It can be implemented on any relational database.

This next video provides a great walkthrough of how a conceptual idea evolves into a detailed logical model.

As you saw, the logical model introduces structure. It identifies unique identifiers (primary keys) and establishes links between entities using foreign keys.

Here is an example of what a logical data model looks like in a diagram:

When you write an SQL query, you are interacting with the logical data model. You ask for data from the Employee table and join it to the Department table, without needing to know where or how those tables are stored on disk.

The Physical Storage Structure: The Implementation

If the logical model is the blueprint, the physical storage structure is the concrete implementation built from that blueprint. It describes all the low-level details of how the data is actually stored on a physical medium.

Key characteristics of a physical storage structure:

• Technology-specific: It is tied to a particular DBMS. The physical model for PostgreSQL will differ from that for SQL Server.
• Defines storage details: It specifies table names, column names (often abbreviated), exact data types (VARCHAR(100), INTEGER), and how tables are organized into files.
• Includes performance mechanisms: It defines indexes, file arrangements, and other structures that the Storage Engine uses to access data efficiently.

Let's continue with the video to see how the logical model is translated into a physical one.

This is where your interest in low-level systems comes into play. The "physical model" isn't just about table and column names. It's about the sophisticated data structures the Storage Engine uses to manage data. In the previous lesson, we mentioned that the "Access Methods" component manages files and indexes. Let's look at two of the most common data structures used for this.

Structures like B-Trees and LSM-Trees are the heart of the physical layer. When you create an INDEX in SQL (a topic for a future lesson), you are telling the DBMS to build a B-Tree or a similar structure at the physical level to speed up data retrieval. The Query Optimizer knows about these physical structures and uses them to create efficient execution plans.

Comparison: Logical vs. Physical

To solidify the distinction, let's review a clear breakdown of the differences.

Here is a summary table capturing the most important distinctions:

Conclusion

Today we've formalized the crucial separation between the "what" and the "how" in a database. This abstraction is not just a theoretical concept; it's a practical design choice that provides immense flexibility and power.

Key Takeaways:

• A Logical Data Model is the abstract blueprint of the database. It defines entities and relationships from a business perspective and is what you, as a developer, will primarily interact with.
• The Physical Storage Structure is the concrete implementation. It deals with low-level details like files, data types, and indexes (like B-Trees) and is specific to the chosen DBMS.
• The separation between these two layers provides Physical Data Independence, allowing the underlying storage to be changed and optimized without breaking applications that use the database.
• This separation is what allows you to write a declarative SQL query (SELECT name FROM users) and trust the DBMS to figure out the best physical way to retrieve that data.

Preview of the Next Lesson:
We've now completed our tour of database architecture! You understand why we use a DBMS, how it communicates, its internal components, and the vital separation between logical and physical design.

With this foundation in place, we are finally ready to start working with the most common logical model: the Relational Model. In our next lesson, we will kick off Module 2 by explaining how the relational model organizes data into the familiar structure of tables, rows, and columns, setting the stage for you to write your first SELECT statement.