1.4 The Database Approach

An information system that uses a Database Management System (DBMS) to manage its information has a particular structure, comprising three components: Data, DBMS, and Application software. This structure, as described below, is referred to as the database approach to information system development.

The central component of the database approach is the DBMS. This software is also referred to as the “database engine” or the “back end.” With regard to the data it manages, it has several responsibilities including the following:

• Data Definition: providing a way to define and build the database

• Data Manipulation: providing a way to insert and update data in the database

• Query Execution: retrieving information from the data in the database

• Data Integrity: ensuring that data stored is well-formed

• Data Security: enforcing restrictions about who is able to access what data

• Provenance: logging capabilities to provide an audit trail for data changes

• Multiuser Concurrency: supporting the activities of many users at the same time

As can be seen from the above list, a DBMS is a complex software application. While all database management systems may not provide all of these features, these are the general characteristics of today’s DBMSs. Using a database requires considerable expertise and knowledge about the specific DBMS being used. Some of the more popular DBMSs today are MySQL, Microsoft SQL Server, Oracle, PostgreSQL, Microsoft Access, and IBM's DB2.

The second component in the database approach is the data. Although the physical location or manner in which the data are stored may be important for performance reasons, the location of the data does not determine whether or not a system is developed using the database approach. As long as the DBMS has access to and can perform its responsibilities in regards to the data, the details of the data storage are not relevant.

The final component of the database approach is the application, also called "front end" software. Application software interacts with the DBMS to provide information to a user. It may also provide a way for a user to invoke other functionality of the DBMS. In fact, the DBMS software itself is non-visual, meaning that the user does not interact directly with the DBMS. Any software that provides an interface for the user to invoke procedures in the DBMS we will define as application software.

Once the application has determined what the user is trying to accomplish, it sends a request to the DBMS. The request may be an instruction to change data or a request for information such as the list of employees who were hired on a particular date. All relational databases use a standard language to receive and process requests.  The standard language is called Structured Query Language (SQL).

The DBMS receives the request and determines if the operation requested is allowed for the authenticated user. If the operation is allowed, the DBMS completes the operation and sends a response to the application. The application then communicates the information to the user. If the operation is not authorized for the user or if there is an error in fulfilling the operation, the DBMS responds with an appropriate message. Again, it is up to the application to display that to the user. It is a critical feature of the database approach that the application never bypasses the DBMS to access stored data directly.

Figure 1.1 illustrates some of the primary components of a typical DBMS and how they are used in an information system. The user interacts with the DBMS generally by writing SQL statements through the front end. (Although a sophisticated front end could format the SQL statements itself based on other types of user input.)  These SQL statements are interpreted and executed by the DBMS by either updating the data or returning results from the data.  In this class, we will focus on query statements, whose purpose is to retrieve data from the database and present it in a form that is understandable by the user.