Getty Images
The 3-level DBMS schema architecture
The three-level DBMS architecture makes database design more secure, extensible and accessible for client applications. Learn the basics of how it works, with examples.
There are many ways to describe a database: the role it plays in a distributed architecture, the various internal components that make it work, and the various levels that define specific roles and responsibilities.
Databases are complicated, so it's understandable that there are many ways to describe how they are built, structured and organized. This article discusses how to better understand databases by organizing them into three schema layers.
What is a 3-level DBMS architecture?
The three-level database management system (DBMS) architecture splits databases into three distinct layers:
- An internal DBMS schema for low-level storage tasks.
- A conceptual DBMS schema for logical designs.
- An external DBMS schema for querying and viewing data.
This layering makes databases more pluggable, secure, extensible and accessible for clients.
The idea of a three-tier DBMS architecture may sound familiar to application developers, but it's not quite the same thing. We'll explain this distinction below.
Physical DBMS schema layer
Some databases store gargantuan amounts of data, with petabytes of data going through their networks every hour. Other databases might only handle a few transactions an hour. How databases manage and store their data is defined in the internal DBMS layer, also known as the physical layer.
This part of the three-layer database architecture concerns itself with how data is stored on a physical computer and how to implement related tasks such as transaction isolation and concurrency control.
There are as many database storage strategies as there are database vendors, and many vendors offer multiple physical schemas. For example, MySQL allows users to choose between the XA-compliant InnoDB and the read-optimized MyISAM database engines during install.
The physical DBMS schema level is responsible for the following tasks:
- Index tables.
- Identify which types of storage media to support.
- Determine where to write log files.
- Perform low-level authentication.
- Select what data types to implement and how.
After the install, the physical DBMS schema choice should be hidden from the administrators who manage database schemas, as well as developers who many need to query or update the data. Those users are more concerned with the external and conceptual DBMS architecture levels.
Conceptual DBMS schema layer
Databases are made up of tables that are joined together in numerous ways. Those tables contain rows of data that are made up of logically related fields.
The design of a database, and how to determine the relationships between tables and organization of data, are monumentally difficult tasks that can be make or break any software development project.
The way to structure data for a database, along with the tools provided to allow database administrators to build the logical database layer, is part of the conceptual DBMS schema layer.
The conceptual DBMS schema layer includes the following common responsibilities:
- Implement database designs captured in an entity relationship diagram (ERD).
- Create indexes for commonly queried tables.
- Assign security attributes to tables and columns.
- Choose from a variety of different data types.
The administrator's choice of data types is an interesting aspect of the logical layer. All databases support a limited number of data types. In the logical layer, an administrator can choose between 32-bit integers, 64-bit floating point numbers or even binary large objects, or BLOBs, and character large objects, or CLOBs. However, those options are available at the logical layer only if they are implemented in the physical layer.
The physical implementation is hidden at the logical DBMS level, but the underlying implementation is still required.
External DBMS schema layer
Client applications don't need to see every table in an enterprise database. Instead, to simplify client access and enhance security of the backend system, client applications receive an external view of the logical layer that shows only the subset of data the apps require. Developers who write client applications know how to limit direct access to the database, and deliver the apps to the client in many different forms, from a webpage to an iPhone or Android app.
For example, the database for an online ordering system might include information about how many parts are in inventory, or which items are on backorder. By comparison, a microservice that simply registers users would need different information and get its own view schema or user schema to access the data externally.
3-tier vs 3-level DBMS architectures
In common parlance, the term three-tier is often substituted for the term three-level to reference this DBMS schema design strategy.
However, Java developers tend to think in terms of tiers differently. In software development, the term three-tier typically refers to a software deployment architecture that's made up of the following elements:
- A client tier that uses a web server, such as Tomcat or Nginx as a reverse proxy.
- A middle-tier that performs logic. Application servers including Tomcat or JBoss sit on this tier.
- A backend database tier that might use any combination of relational or NoSQL databases.
To disambiguate three-tier deployments with three-level DBMS architectures, remember this: The three-level architecture deals exclusively with databases. A three-tier deployment architecture a database is only associated with one of the tiers.
Karan Adapala is a full-stack developer, app architect and cloud enthusiast with expertise in React, Node.js, TypeScript, Python, Docker, Terraform, Jenkins and Git.
