Three-Level Architecture

Have we ever wondered how an online store, like Amazon, can show us a personalized homepage with our order history, while a warehouse manager sees a completely different screen with stock levels and supplier details, and a data analyst sees yet another view for sales reports?

They are all accessing the same database, but they see it in ways tailored to their specific needs.

This magic is made possible by a clever design principle known as the three-level architecture. It separates what the user sees from how the data is actually stored, making databases incredibly flexible and powerful. In this lesson, we’ll explore this foundational concept.

Three-level architecture

The three-level architecture, also known as the ANSI-SPARC architecture, is a foundational framework for designing database systems and defining how data models are structured at different levels of abstraction.

Its primary objective is to separate the user’s view of the data from the way the data is physically stored. Consider this: if every application needed to know the exact location and storage format of data on a disk, even minor changes—such as relocating a data file or optimizing a storage structure—would necessitate rewriting all related applications.

Clearly, that would be a maintenance nightmare.

The ANSI-SPARC architecture addresses this challenge by introducing three distinct layers of abstraction, or levels. Each level serves a specific purpose and effectively conceals the complexity of the level beneath it, enabling flexibility, data independence, and easier system maintenance.

Abstract and structural model architecture

The three-level architecture can also be understood in terms of abstract and structural views of a database system.

• The conceptual level represents an abstract model of the database, describing the overall structure of data and relationships without implementation details.

• The internal level represents the structural model architecture, focusing on how data is physically stored and organized in the system.

This layered approach helps separate high-level data modeling from low-level storage details, making database systems more flexible and easier to maintain.

Let’s imagine we’re building a house.

• The External level is like a series of rooms designed for different family members. A teenager’s room looks different from the kitchen, and the living room serves a different purpose altogether.

• The Conceptual level is the architect’s complete blueprint for the entire house, showing how all the rooms connect.

• The Internal level is the construction plan, detailing the plumbing, wiring, and foundation—the technical details that the residents don’t need to see.

Now, let’s explore what each of these levels means in the world of databases. The architecture divides a database system into three distinct levels, each with its own schema.

External level (user views)

This is the highest level of abstraction and is the one closest to the users. It defines how a specific group of users or an application sees the data. A database can have multiple external views. Each view can be tailored to display only the data relevant to a particular user, hiding the rest of the database for simplicity and security.

In our OnlineStore database, different users would have different external views:

• A customer logging into their account would see a view showing their own orders (Orders table) and personal details (Customers table). They won't see other customers’ data or product stock levels.

• An inventory manager would have a view that displays product names, current stock levels, and supplier information, but they may not need to view customer emails.

Conceptual level (logical view)

This is the middle level, and it represents the entire database’s structure and logic.

It provides a unified view of the data for the entire community of users. The conceptual schema defines all the tables, their columns, and the data types for each column (like INT, VARCHAR), the relationships between tables (using primary and foreign keys), and any constraints.

For our OnlineStore database, the conceptual schema is the complete set of CREATE TABLE statements we used to define the Customers, Products, Orders, and other tables, along with all their relationships.

This level describes what data is stored in the database and the relationships among that data, but it doesn’t specify how the data is physically stored. This is the level where database designers and administrators do most of their work.

Internal level (physical view)

This is the lowest level of abstraction, describing how data is physically stored on a storage device, such as a hard drive or SSD. The internal schema deals with low-level details that are hidden from programmers and end-users.

These details include:

• Storage allocation: How and where the data is stored on the disk (e.g., in which files).

• Data structures: The specific data structures used to store the data, such as B-trees for indexes or heaps for tables.

• Data compression: Techniques used to save storage space.

• Access paths: The methods used to retrieve data from the disk efficiently (e.g., using an index).

For our OnlineStore database, the internal level would specify that the Customers table is stored in a file named customers.dat, that an index exists on the Products.ProductName column to speed up searches, and that a DECIMAL(10, 2) value occupies 5 bytes of storage. We, as users, never interact directly with this level; the DBMS handles it all for us.

Now that we understand the three levels, let’s examine the primary advantage this architecture offers: data independence.

Data independence

Data independence is the main reason for the three-level architecture. It’s the ability to modify the schema at one level without having to change the schema at the next higher level. This makes databases incredibly flexible and reduces the cost of maintenance. There are two types of data independence: logical and physical data independence.

Logical data independence

This refers to the ability to change the conceptual schema without affecting the external schemas or user applications.

For example, we could make changes to the overall database structure, and as long as the user views are still supported, the applications using them won't break. Imagine we decide to improve our OnlineStore database by adding a new field to the Products table (e.g. ProductDescription), users’ views should not be impacted unless explicitly required.

The application code does not need to be rewritten. This is an example of logical data independence in action.

Physical data independence

This refers to the ability to change the internal schema without affecting the conceptual schema. Changes at the physical level are often made to improve performance.

For instance, a database administrator (DBA) for our OnlineStore might make these changes:

• Move the database files from a slower hard disk drive (HDD) to a faster solid-state drive (SSD).

• Change the file organization of a table.

• Add a new index to the Orders table on the OrderDate column to speed up date-based searches.

None of these changes alter the CREATE TABLE definitions (the conceptual schema) or the user views (the external schema). The DBA can fine-tune the system’s performance without disrupting any users or developers. This is physical data independence, and it’s a crucial feature for managing large-scale databases.

In this lesson, we explored the foundational blueprint of modern database systems—the three-level architecture. This model distinguishes user views at the External Level from the overall database structure at the Conceptual Level, which is further separated from the physical storage details at the Internal Level.

The central advantage of this layered design is data independence.

Logical data independence enables modification of the database design without disrupting user applications, while physical data independence allows performance optimizations to occur transparently. Understanding this architecture clarifies why we can execute simple SQL queries without needing to comprehend the intricate mechanics of data storage and retrieval.

It is a powerful framework that makes databases more manageable, scalable, and adaptable to change. Keep this foundational concept in mind as we progress—excellent work so far, and let’s maintain the momentum.