dbms architectureDataBase 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:

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.

Featured course

70-462: SQL Server Database Administration (DBA)

Last Updated January 2020

  • 9.5 hours
  • 96 lectures
  • Beginner Level
4.5 (1,058)

Beginner-Intermediate practical experience in Database Administration (DBA) SQL Server. Good for Microsoft exam 70-462. | By Phillip Burton

Explore Course

Database Management students also learn

Empower your team. Lead the industry.

Get unlimited access to 3,500+ of Udemy’s top courses for your team. Learn and improve skills across business, tech, design, and more.

Request a demo