DBMS Architecture: An Overview of the 3-Tier ANSI-SPARC Architecture

DataBase Management Systems (DBMS) are very relevant in today’s world where information matters. Most business operations of large companies are dependent on their databases in some way or the other. Many companies use their data analysis methods to leverage the data in their databases and provide better service to customers and compete with their business rivals.

Databases are collections of data that has been organized in a certain way. The term DBMS is a commonly used to refer to computer program that can help you store, change and retrieve the data in your database. Most DBMS software products use SQL as the main query language – the language that lets you interact with and extract results from your database quickly. SQL is the language used to query popular database systems like Oracle, SQL Server and MySQL. Learning SQL and DBMS can help you become a database administrator. This course can help give you a preview into database design.

In this tutorial, we’re going to take an overview of the most popular database architecture – the three tier ANSI-SPARC DBMS Architecture.

DBMS Architecture

DBMS architecture is the way in which the data in a database is viewed (or represented to) by users. It helps you represent your data in an understandable way to the users, by hiding the complex bits that deal with the working of the system. Remember, DBMS architecture is not about how the DBMS software operates or how it processes data.

We’re going to take a look at the ANSI-SPARC DBMS standard model. ANSI is the acronym for American National Standards Institute. It sets standards for American goods so that they can be used anywhere in the world without compatibility problems. In the case of DBMS software, ANSI has standardized SQL, so that most DBMS products use SQL as the main query language. The ANSI has also standardized a three level DBMS architecture model followed by most database systems, and it’s known as the abstract ANSI-SPARC design standard. You can learn more about database design with SQL in this course.

The ANSI-SPARC Database Architectture is set up into three tiers. Let’s take a closer look at them.

The Internal Level (Physical Representation of Data)

The internal level is the lowest level in a three tiered database. This level deals with how the stored data on the database is represented to the user. This level shows exactly how the data is stored and organized for access on your system. This is the most technical of the three levels. However, the internal level view is still abstract –even if it shows how the data is stored physically, it will not show how the database software operates on it.

So how exactly is data stored on this level? There are several considerations to be made when storing data. Some of them include figuring out the right space allocation techniques, data compression techniques (if necessary), security and encryption and the access paths the software can take to retrieve the data. Most DBMS software products make sure that data access is optimized and that data uses minimum storage space. The OS you’re running is actually in charge of managing the physical storage space.

The Conceptual Level (Holistic Representation of Data)

The conceptual level tells you how the database was structured logically. This level tells you about the relationship between the data members of your database, exactly what data is stored in it and what a user will need to use the database. This level does not concern itself with how this logical structure will actually be implemented. It’s actually an overview of your database.

The conceptual level acts as a sort of a buffer between the internal level and the external level. It helps hide the complexity of the database and hides how the data is physically stored in it.

The database administrator will have to be conversant with this layer, because most of his operations are carried out on it. Only a database administrator is allowed to modify or structure this level. It provides a global view of the database, as well as the hardware and software necessary for running it – all important info for a database admin to get an overview of database administration, you can take this special course designed for absolute beginners.

The External Level (User Representation of Data)

This is the uppermost level in the database. It implements the concept of abstraction as much as possible. This level is also known as the view level because it deals with how a user views your database. The external level is what allows a user to access a customized version of the data in your database. Multiple users can work on a database on the same time because of it.

The external level also hides the working of the database from your users. It maintains the security of the database by giving users access only to the data which they need at a particular time. Any data that is not needed will not be displayed.

Three “schemas” (internal, conceptual and external) show how the database is internally and externally structured, and so this type of database architecture is also known as the “three-schema” architecture. If you’d like to learn how you go about accessing and structuring the various levels of a database, you can sign up for our course.

Advantages of the Three Tiered ANSI-SPARC Architecture

The ANSI-SPARC standard architecture is three tiered- that is, there are three levels through which it is represented. This three tiered representation offers several advantages, which as follows:

• It makes the database abstract. It hides the details of how the data is stored physically in an electronic system, which makes it easier to understand and easier to use for an average user. It also allows the user to concentrate on the data rather than worrying about how it should be stored.
• It lets users view the same data, but it makes the data customizable to fit each user. Each user should be able to access a source of data, and any change made to their customized data source should not affect data sources being used by other users. This means that the data in your database is independent.
• The three tiered architecture model also allows migration to another system to be seamless. The database appears the same on different systems, even if the physical storage is changed. Its structure remains the same, which makes it portable.
• The model also allows a database admin to make changes to the database structure or make upgrades to it without disturbing a user currently on the system.
• The model allows a database admin to change the storage medium of the database without disturbing a user who is currently on the system.

Most modern DBMS software products offer these advantages. The goal of this kind of database architecture is to allow a user with little to no knowledge of programming or database administration to operate a DBMS product successfully. This course can help you get started easily with your database design.