DISCUSSION PAPERS ON ENTERPRISE BASED (COMPUTER) SYSTEMS

BY Kevin Loughrey CPEng

PART 2 - THE GENERIC NATURE OF A MODERN DATABASE - THE BACK-END

Commenced 19 April 2006

Background

1. An earlier discussion paper of this series dealt with the concept of there being a hierarchy of applications and posited that:
   1. A successful database system is made up of three subsystems these being:
      • Clerical Assistance Sub System (CASS),
      • Administrative Assistance Sub System (AASS), and
      • Management Assistance Sub System(MASS).
   2. Clerical Assistance Sub Systems(CASS) facilitate data input into related Administrative Assistance Sub Systems.  CASS utilise barcode readers and other automated means, such as software "robots" ¹, to facilitate the capture and input of data into the enterprise computer system.
   3. Administrative Assistance Sub Systems(AASS) consist of various applications that provide assistance to administrative functions within a "Line" organisation.  These consist of such things as PC based accounting systems, personnel administration, telephonic communication and production control software as well as Office software for the production/storage/retrieval of documents.
   4. Management Assistance Sub Systems(MASS) provide information to assist management in its decision making.  Without effective CASS and AASS, MASS are usually valueless.  This is the situation confronting many large organisations that have installed monolithic Enterprise Resource Planning applications.

Aim

2. This paper is the 2nd in a series.  Its aim is to inform the reader as to how a modern database is structured and how it functions.  Importantly, it seeks to remove the mystique that surrounds this subject for the benefit of the very senior manager.  From this the reader will gain sufficient knowledge to understand the concepts presented in subsequent papers.

The Evolution of Databases

3. The concept and design of a Database has evolved over the years.  This evolution has simplified databases and has given users and programmers a great deal more power and flexibility.  There are two advances which stand prominent in this development.  They are as follows:
   1. a standardised language for creating and administering² a database and for inserting, deleting, changing and retrieving information from a database, and
   2. a modularization and standardization of the components of a database system.
4. Standardised Language.  A database can be thought of as being a standalone independent entity much like a filing cabinet.  The "Structured Query Language (SQL) Server" is analogous to a Filing Clerk.  You tell the filing clerk you want a particular file or that you want an extract from a file presented to you in a particular format.  Likewise, you may have some information you want the filing clerk to file.  You can also tell the filing clerk to create a file or, if you have sufficient authority, to destroy a file.  The information you give the clerk may be inserted into an existing record/table or may result in a new record/table being created.  The language which has become industry standard for this purpose is called SQL.  It stands for "Structured Query Language".  SQL has developed to go way beyond providing just an ability to ask questions.  Here are a few examples (snippets) of SQL:
1. "SELECT Rank, FirstName, MiddleName, SecondMiddleName, LastName FROM EstablishmentDatabase.PersLstTbl WHERE (Rank = 'Sgt') ORDER BY Rank, LastName, FirstName, MiddleName, SecondMiddleName ASC"
2. "UPDATE EstablishmentDatabase.PersLstTbl, SET LastName = 'Jonston' WHERE (PMKeysNo = 8989898) AND ( LastName 'Johnston')"
3. "DELETE * FROM EstablishmentDataBase.PersLstTbl WHERE (PMKeysNo = 8989898) AND (LastName = 'Jonston')"
5. Each of these snippets are quite readable.  The first directs the "filing clerk" to fetch information from a table called PersLstTbl (to be found in the database EstablishmentDatabase) various personnel details for all personnel of the rank of Sergeant.  The instruction goes further in directing the clerk to present this information in the order of Rank, LastName, FirstName, MiddleName and SecondMiddleName in Ascending order, ie, from A to Z.  Likewise, the second snippet tells the "filing clerk" to change LastName in PersLstTbl from Johnston to Jonston where the PMKeys number equals 8989898 and the LastName = Johnston.  Finally, the last snippet tells the filing clerk to delete everything from the record where the PMKeys number = 8989898 and the LastName = Jonston.  It is important to note the following:
1. Provided that an SQL database server complies with American National Standards Institute (ANSI) Standards, it is possible for any application using this standard language, and with the requisite authority, to make information requests upon the server or give the server directions with respect to adding, editing or deleting data.  (Likewise, anyone trained in this language can rapidly become proficient to write software for any number of systems complying with this standard.)
2. The key to interoperability between applications, authored by different persons/organisations, is that the SQL database server uses Internationally recognised standards.  Microsoft, the software vendor chosen by the Australian Government and Defence, does not comply with these standards. It is not necessary that the application making the requests or giving the directions have the same author or come from the same software company.
3. It is also a simple matter for numerous clients running numerous applications written by different authors to access a number of relational databases at the same time.  In doing this it is possible to have a number of databases that don't have the "whole picture"; only the client with the correct application can put the information of the two databases into a useful format.  Having an arrangement such as this would make penetration by a hostile intelligence agency more difficult than trusting all one's information to one database on one server as is presently the case within Defence.
6. The Method of Storing.  Modern databases store information in what appears to the user to be tables.  The information is stored in such a way that items(fields) of information in one table are often related to other items in other tables.  This is called a relational database structure.  There is no need for the reader to be concerned with this except to know that all modern enterprise³ systems use a relational database structure.  Of interest, it would be possible to store the current records of the whole Standard Defence Supply System (SDSS) database (or its successor, MILIS) and the Personnel Management (PMKeys) Database and the Army Office Establishments (DES) Database comfortably on a number of relational databases running on laptop computer worth around $1,500 at the time of writing.

The Concept of a Front-End and Back-End to a Database

7. Figure 1 shows a number of applications accessing a single SQL database server (often abbreviated to SQL server).  These applications are called Front-Ends.  The relational database with its SQL server is called the Back-End.  The obverse situation can also apply as depicted in Figure 2 where a Front-End may access a number of Back-Ends.
8. This paper is concerned mainly with the Back-End.  Those points of interest concerning the Front-End will be dealt with in the next paper, Figure 3 is a screen shot showing what an administrator would see when using the InterBase Expert Administration Tool.  The Administration Tool is an application running on a PC.  This PC is connected to other PC's on a network.  It is possible each of these other PC's is running an application that consists of a different Relational Database and SQL server.  It is also possible to have more than one relational database and SQL server running on a single PC.  Here you see a screen-shot of actual and distinct databases⁴ dealing with different aspects of administration.  In the case of the Establishment Database, you can see that the list has been expanded to show the various components that go to make up a sophisticated relational database.  This is important.  It is possible for one application to simultaneously be connected to a number of relational databases located on a number of PCs distant from the PC upon which the application is running.

The Anatomy of the Back-End of a Database

9. Figure 1 shows a number of applications accessing a single SQL database server (often abbreviated to SQL server).  These applications are called Front-Ends.  The relational database with its SQL server is called the Back-End.  The obverse situation can also apply as depicted in Figure 2 where a Front-End may access a number of Back-Ends.
10. Figure 4 shows the components of a sophisticated relational database; the most important ones being the Domains, Tables, Procedures,Triggers and User Defined Functions (UDFs).  It is in the tables that the data is stored.  Procedures are routines that can be called from the Front-End or which are executed when a particular condition is triggered.  This brings us to triggers.  Triggers are a way of setting conditions within the database such that should one of these conditions be met, a nominated procedure will be executed.  Triggers therefore provide the means by which one database will automatically update another when "reportable" transactions occur.  Defence, for reasons of security and survivability, has to have distributed databases capable of updating each other in an automatic fashion whenever communications are established.  Database triggers provide that capability.
11. Domains.  (See Figure 5) In database parlance a Domain is a data type.  In the above Figure on the left it can be seen that there have been 147 Domains defined.  These are presented in alphabetical order.  On the right is displayed the details of some of these domains.  Notice for example that the domain "AbbrevLong" (long abbreviation) is a string of up to 10 characters (of any type, ie, numbers, literal strings, punctuation marks, etc).  Domains are important because they form the means of specifying a data dictionary.  If all programmers use the same domains, there is certainty of being able to exchange data between various database Back-Ends.  It is noteworthy that the present Defence Data dictionary does not feature domains.  Nor does it describe relationships between data fields in one table and those in another.  This is basic to having a coherent spectrum of systems across the organisation yet this fundamental administrative requirement has been overlooked.
12. Manufacturers of proprietary software seek to place as much business logic into the Back-End as they can.  In this way it is very difficult for another company to displace them from their customer's workplace.  If the business logic is placed in the Front-End it is possible to have many Front-Ends of differing designs, written by a large number of competing contractors, accessing the same data in the Back-End.  Not only does having the business logic in the Back-End keep out competition, it also effectively ties up the data such that the client loses ownership of their own data.  Defence has consistently engaged software suppliers that place all of their business logic into the Back-End of their databases.

Summation

13. A summation of points covered in this paper are:
1. All modern relational databases, served by a "Structured Query Language" (SQL) server, have similar "structural components".
2. Modern relational databases are "driven" by an SQL server.  The syntax for communicating with this SQL server has been defined by the American National Standards Institute⁵.  Microsoft SQL server, one of a number of SQL servers chosen by Defence IT, does not comply with this standard.
3. A database system typically consists of a large number of "Front-End" applications connecting to a Back-End or series of Back-Ends located on a computer or computers some distance from the PCs upon which the Front-Ends are running.
4. The Front-End of a database system is usually referred to as a software Application.
5. It is possible for a large number of different specifically tailored front-end applications, written by different authors to access an SQL Server, located on a near or distant computer, at the same time.
6. It is possible for any front-end application with sufficient security privilege to connect to and extract and store information out of and into any number of the SQL databases.
7. It is possible for each SQL relational database to check that the information inserted has what is called referential integrity to other information stored in another SQL relational database.  For example, if one database holds the definitive list of Unit ERNs (this would most likely be the Establishment Database) then, when an ERN is inserted into a table within, say, the Maintenance Database, the "database system" software will check that the ERN is both extant and active in the Establishment Database.
8. The use of a common domain library ensures that there is certainty of being able to exchange data between databases without there being a clash of data types or field sizes.

The Next Paper

14. The next paper will deal with the concept of a database "Front-End".  It will show how Front-Ends interact with a Back-End/s.

Footnotes:
1    A robot is a small application (applet) that simulates the keystrokes of a person executing a transaction on a system, eg, inputting a parts indent. In this manner, the robot can take, for example, a list of parts held in a text file and input these as a parts requisition into a system utilising an existing transaction on that system.

2    Administering a database means the ability to create tables, relationships between tables and to create stored procedures within the database which will execute upon request as well as execute automatically if a certain condition within the database is "triggered".

3    The term "enterprise" is used in the IT industry to describe a system which serves a large organisation.  An ERP usually has modules to serve operations, the administration and management of personnel and financial and stores accounting.

4    For the purposes of keeping in the context of the Defence System, these databases have been renamed.

5    Microsoft SQL server and Microsoft Exchange server used for the Microsoft Outlook application does NOT conform to this standard.

6    This paper was distributed in PDF format and was produced, in its initial publication, using Open Office 2.0 (and later in LibreOffice, the Open Source successor to Open Office when Sun Microsystems was acquired by Oracle).  This is a free suite of programs that are capable of reading and writing Microsoft Office Documents.  The source code for Open Office is freely available and users may alter it as they wish.  Open Office is the product of an organised collaborative effort by volunteers around the world.  Their aim is to accelerate the development of nations less fortunate than Australia by providing these countries with not only the applications necessary for higher productivity and competitiveness but also the knowledge as to how these applications are written; thereby providing the opportunity to participate in the global technology market.  Open Source software is therefore an instrument of Strategic Defence the ADF should embrace.

- End of Paper⁶-