Database Administration

A Database Management System (DBMS) is a software that enables users to create and maintain a database. It facilitates the process of defining, constructing, and manipulating databases for various applications. Commonly known examples include DB2, Oracle, Sybase, and Ingres.

A DBMS requires a basic architecture and structure to handle the process and files related to the database software, which means that a physical and logical architecture of an Operational System must exist.

A basic database and instance structure is based on physical files and logical files/structure. The physical files represent the real files generated by the DBMS and written directly to the Operational System disk or Storage Area. The logical files/structure are handled by the DBMS and have reference to the physical files. Basically, a database has a main concept of tablespaces (or database files), which contain the information about all database objects like tables, indexes, procedures, etc. The DBMS is responsible for handling this logical structure of database objects and saving them on physical files.

A database administrator (DBA) is an IT professional responsible for managing and maintaining a DBMS and taking care of databases. In some cases, DBAs have special authorization, which gives them the ability to do almost everything in the database subsystem as well as the responsibility of taking care of all of the databases.

Inside the database administration role, there are two specific and distinct sub-categories: physical DBAs, also known as system DBAs, and logical DBAs. It is true that in some cases we have a DBA working in both physical and logical administration, but we need to remember that each role has its own distinct responsibilities.

Logical Database Administration

A logical database administrator is responsible for generating the database model and tables design, defining the model of the database objects, and creating procedures and functions for the application and its users. It's unusual to see a logical DBA taking care of DBMS installation and upgrades, applying version fixes, verifying the database CPU and memory consumption, or addressing anything related to database backup strategies.

The logical DBA usually works closely with the application owner and developers to define the structure of the database model. The logical database model is a set of conceptual tools used to describe data and their relationship and restriction of consistency and integrity (i.e. Entity-relationship model and Relational model). 

Entity–relationship model example from Lucidchart

This part of the database design is extremely important since the logical DBA and application developers generate the model with all tables and the definitions and relationships between them. This model contains all tables and the specification of each one, including all columns for each table with their respective datatypes and lengths. This logical model has the basic information about how the data will be logically stored inside the DBMS.

With the model in hand, the logical DBA can provide an average for the size of the rows in each table, which is valuable information considering that the data will be saved and retrieved from a disk/storage - the most expensive operation in a system.

Physical Database Administration

As mentioned before, a DBMS is a software that requires an Operating System. The physical DBA is the professional responsible for installing, upgrading, migrating, and maintaining the DBMS. To be a physical DBA, you don't need to have a deep understanding of the purpose of the database or what exactly the application and users are storing inside the tables - the business purpose of the database is a subject that doesn't directly concern the physical DBA. 

Other responsibilities of a physical DBA include authorizing access to database and database objects, monitoring database usage, acquiring software and hardware resources as needed, monitoring and tuning database performance, designing backup and recovery strategies, and defining/modifying the physical organization structure.

Once the DBMS is installed and ready to be used - with new instances and the database already created - it's time to start thinking about the other part of the physical database architecture: the physical data model. The physical data model provides a low-level concept that describes the details of how data is stored on the computer. The physical model is extremely important since it supports the detailed functionality of the DBMS, including how it will save and retrieve data from the disk/storage.

DBMS Architecture Sample -  Image courtesy of IBM LabChats Oracle® and IBM DB2® Architecture

As depicted in the image above, this is where the physical and logical DB worlds meet. Based on the logical model, the physical DBA can build an improved physical model. The average size of the table rows, the large objects (LOBs), indexes, and constraints will drive the DBA to build a physical model that focuses on the performance and improvement of I/O operations, calculating how many tablespaces will be required and what will be stored in each one - usually separated by normal data types, large objects, and indexes - and the memory allocation size (buffer pools) to better fit the data retrieved from the tablespaces.

Final Considerations

You can think about all of this information in this way: the database is a box. Everything inside this box is the responsibility of the logical DBA (tables, procedures, functions, etc.). Everything outside the box that keeps the box up and running is the responsibility of the physical DBA (database storage configuration, DBMS health, memory and disk utilization improvement, etc.).

This article is meant to give an overview of a DBMS and to offer a more defined understanding of the responsibilities of physical and logical DBAs and the relationship between them. It is true that DBA roles are not limited to the above-mentioned tasks. There are a thousand other activities related to database administration and database manipulation - like ETL, KDD process, and Business Intelligence--but this overview is meant to clarify any ambiguities about the database and the responsibilities of logical and physical DBAs.