Elements of the Database Environment

Elements of the Database Environment

Figure 9-3 presents a breakdown of the database environment into four primary elements: users, the DBMS, the database administrator, and the physical database. In this section we examine each of these elements.

USERS

Figure 9-3 shows how users access the database in two ways. The first is via user application programs that systems professionals prepare. These programs send data access requests (calls) to the DBMS, which validates the requests and retrieves the data for processing. Under this mode of access, the presence of the DBMS is transparent to the users. Data processing procedures (both batch and real-time) for transactions such as sales, cash receipts, and purchases are essentially the same as they would be in the flat-file environment.

The second method of database access is via direct query, which requires no formal user programs. The DBMS has a built-in query facility that allows authorized users to process data independent of professional programmers. The query facility provides a friendly environment for integrating and retrieving data to produce ad hoc management reports. This feature has been an attractive incentive for users to adopt the database approach.

DATABASE MANAGEMENT SYSTEM

The second element of the database approach depicted in Figure 9-3 is the database management system. The DBMS provides a controlled environment to assist (or prevent) user access to the database and to efficiently manage the data resource. Each DBMS model accomplishes these objectives differently, but some typical features include:

1. Program development. The DBMS contains application development software. Both programmers and end users may employ this feature to create applications to access the database.

2. Backup and recovery. During processing, the DBMS periodically makes backup copies of the physical database. In the event of a disaster (for example, disk failure, program error, or malicious act) that renders the database unusable, the DBMS can recover an earlier version that is known to be correct. Although some data loss may occur, without the backup and recovery feature, the database would be vulnerable to total destruction.

3.Database usage reporting. This feature captures statistics on what data are being used, when they are used, and who uses them. The database administrator (DBA) uses this information to help in assigning user authorization and in maintaining the database. We discuss the role of the DBA later in this section.

4. Database access. The most important feature of a DBMS is to permit authorized user access to the database. Figure 9-3 shows the three software modules that facilitate this task. These are the data definition language, data manipulation language, and the query language.

Data Definition Language

Data definition language (DDL) is a programming language used to define the physical database to the DBMS. The definition includes the names and the relationship of all data elements, records, and files that constitute the database. The DDL defines the database on three levels called views: the internal view, the conceptual view (schema), and the user view (subschema). Figure 9-4 shows the relationship between these views.

INTERNAL VIEW. The internal view presents the physical arrangement of records in the database. This is the lowest level of representation, which is one step removed from the physical database. The internal view describes the structure of records, the linkages between them, and the physical arrangement and sequence of records in a file. There is only one internal view of the database.

CONCEPTUAL VIEW (SCHEMA). The conceptual view or schema represents the database logically and abstractly, rather than the way it is physically stored. This view allows users’ programs to call for data without knowing or needing to specify how the data are arranged or where the data reside in the physical database. There is only one conceptual view for a database.

USER VIEW (SUBSCHEMA). The user view defines how a particular user sees the portion of the database that he or she is authorized to access. To the user, the user view is the database. Unlike the internal and conceptual views, many distinct user views exist. For example, a user in the personnel department may view the database as a collection of employee records and is unaware of the supplier and inventory records seen by the users in the inventory control department.

DBMS OPERATION. To illustrate the roles of these views, let’s look at the typical sequence of events that occurs in accessing data through a DBMS. The following description is hypothetical, and certain technical details are omitted.

1. A user program sends a request (call) for data to the DBMS. The call is written in a special data manipulation language (discussed later) that is embedded in the user program.

2. The DBMS analyzes the request by matching the called data elements against the user view and the conceptual view. If the data request matches, it is authorized and processing proceeds to Step 3. If it does not match the views, access is denied.

3. The DBMS determines the data structure parameters from the internal view and passes them to the operating system, which performs the actual data retrieval. Data structure parameters describe the organization and access method (an operating system utility program) for retrieving the requested data.

4. Using the appropriate access method, the operating system interacts with the disk storage device to retrieve the data from the physical database.

5. The operating system then stores the data in a main memory buffer area managed by the DBMS.

6. The DBMS transfers the data to the user’s work location in main memory. At this point, the user’s program is free to access and manipulate the data.

7. When processing is complete, Steps 4, 5, and 6 are reversed to restore the processed data to the database.

Data Manipulation Language

Data manipulation language (DML) is the proprietary programming language that a particular DBMS uses to retrieve, process, and store data. Entire user programs may be written in the DML or, alternatively, selected DML commands can be inserted into programs that are written in universal languages, such as PL/1, COBOL, and FORTRAN. Inserting DML commands enables legacy application programs, which were originally written for the flat-file environment or earlier types of DBMSs, to be easily converted to work in the current database environment. The use of standard language programs also provides the orga- nization with a degree of independence from the DBMS vendor. If the organization decides to switch its vendors to one that uses a different DML, it will not be necessary to rewrite all the user programs. By replacing the old DML commands with the new commands, user programs can be modified to function in the new environment.

Query Language

The query capability of the DBMS permits end users and professional programmers to access data in the database directly without the need for conventional programs. IBM’s structured query language (SQL, pronounced sequel) has emerged as the standard query language for both mainframe and microcomputer DBMSs. SQL is a fourth-generation, nonprocedural language with many commands that allow users to input, retrieve, and modify data easily. The SELECT command is a powerful tool for retrieving data. The example in Figure 9-5 illustrates the use of the SELECT command to produce a user report from a data- base called Inventory.

SQL is an efficient data processing tool. Although not a natural English language, SQL requires far less training in computer concepts and fewer programming skills than many languages. In fact, many database query systems require no SQL knowledge at all. Users select data visually by pointing and clicking at the desired attributes. The visual user interface then generates the necessary SQL commands automatically. This feature places ad hoc reporting and data processing capability in the hands of the user/manager. By reducing reliance on professional programmers, managers are better able to deal with problems that arise.

DATABASE ADMINISTRATOR

Refer to Figure 9-3 and note the administrative position of database administrator (DBA). This position does not exist in the flat-file environment. The DBA is responsible for managing the database resource. Multiple users sharing a common database requires organization, coordination, rules, and guidelines to protect the integrity of the database.

In large organizations the DBA function may consist of an entire department of technical personnel under the database administrator. In smaller organizations someone within the computer services group

may assume DBA responsibility. The duties of the DBA fall into the following areas1: database planning, database design, database implementation, database operation and maintenance, and database change and growth. Table 9-1 presents a breakdown of specific tasks within these broad areas.

Organizational Interactions of the DBA

Figure 9-6 shows some of the organizational interfaces of the DBA. Of particular importance is the relationship among the DBA, the end users, and the systems professionals of the organization. Refer again to Figure 9-3 during the examination of this relationship.

As information needs arise, users send formal requests for computer applications to the systems professionals (programmers) of the organization. The requests are handled through formal systems development procedures, which produce the programmed applications. Figure 9-3 shows this relationship as the line from the user’s block to the systems development process block. The user requests also go to the DBA, who evaluates these to determine the user’s database needs. Once this is established, the DBA grants the user access authority by programming the user’s view (subschema). This relationship is shown as the lines between the user and the DBA and between the DBA and DDL module in the DBMS.

By keeping access authority separate from systems development (application programming), the organization is better able to control and protect the database. Intentional and unintentional attempts at unauthorized access are more likely to be discovered when these two groups work independently. The rationale for this separation of duties is developed in Chapter 15.

The Data Dictionary

Another important function of the DBA is the creation and maintenance of the data dictionary. The data dictionary describes every data element in the database. This enables all users (and programmers) to share a common view of the data resource and greatly facilitates the analysis of user needs.

THE PHYSICAL DATABASE

The fourth major element of the database approach as presented in Figure 9-3 is the physical database. This is the lowest level of the database. The physical database consists of magnetic spots on magnetic disks. The other levels of the database (for example, the user view, conceptual view, and internal view) are abstract representations of the physical level.

At the physical level, the database is a collection of records and files. Relational databases are based on the indexed sequential file structure. This structure, illustrated in Figure 9-7, uses an index in conjunction with a sequential file organization. It facilitates both direct access to individual records and batch processing of the entire file. Multiple indexes can be used to create a cross-reference, called an inverted list, which allows even more flexible access to data. Two indexes are shown in Figure 9-7. One contains the employee number (primary key) for uniquely locating records in the file. The second index contains record addresses arranged by year-to-date earnings. Using this nonunique field as a secondary key per- mits all employee records to be viewed in ascending or descending order according to earnings. Alternatively, individual records with selected earnings balances can be displayed. Indexes may be created for each attribute in the file, allowing data to be viewed from a multitude of perspectives.

The next section examines the principles that underlie the relational model and the techniques, rules, and procedures for creating relational tables from indexed sequential files. You will also see how tables are linked to other tables to permit complex data representations.