Nội dung

part Advanced Technologies in Accounting Information Chapter 9 III Database Management Systems 397 Chapter 10 The REA Approach to Database Modeling 459 Chapter 11 Enterprise Resource Planning Systems 489 Chapter 12 Electronic Commerce Systems 523 395 chapter Database Management Systems 9 T his chapter deals with the database approach to managing an organization’s data resources. The database model is a particular philosophy whose objectives are supported by specific strategies, techniques, hardware, and software that are very different from those associated with flat-file environments. Chapter 1 drew a distinction between two general data management approaches: the flat-file model and the database model. Because the best way to present the virtues of the database model is by contrast with the flat-file model, the first section of this chapter examines how traditional flat-file problems are resolved under the database approach. Important features of modern relational databases are covered later in the chapter. The second section describes in detail the functions and relationship between four primary elements of the database environment: the users, the database management system (DBMS), the database administrator (DBA), and the physical database. The third section is devoted to an in-depth explanation of the characteristics of the relational model. A number of database design topics are covered, including data modeling, deriving relational tables from entity relationship (ER) diagrams, the creation of user views, and data normalization techniques. The fourth section concludes the chapter with a discussion of distributed database issues. It examines three possible database configurations in a distributed environment: centralized, partitioned, and replicated databases. I I Learning Objectives After studying this chapter, you should: I Understand the operational problems inherent in the flat-file approach to data management that gave rise to the database concept. I Understand the relationships among the defining elements of the database environment. I Understand the anomalies caused by unnormalized databases and the need for data normalization. I Be familiar with the stages in database design, including entity identification, data modeling, constructing the physical database, and preparing user views. I Be familiar with the operational features of distributed databases and recognize the issues that need to be considered in deciding on a particular database configuration. 398 Advanced Technologies in Accounting Information PART III Overview of the Flat-File versus Database Approach Many so-called legacy systems are characterized by the flat-file approach to data management. In this environment, users own their data files. Exclusive ownership of data is a natural consequence of two problems associated with the legacy-system era. The first problem is a business culture that erects barriers between organizational units that inhibit entity-wide integration of data. The second problem stems from limitations in flat-file management technology that require data files to be structured to the unique needs of the primary user. Thus, the same data, used in slightly different ways by different users, may need to be restructured and reproduced in physically different files. Figure 9-1 illustrates this model. In the figure, the file contents are represented conceptually with letters. Each letter could signify a single data attribute (field), a record, or an entire file. Note also that data element B is present in all user files. This is called data redundancy and is the cause of three types of data management problems: data storage, data updating, and currency of information. Each of these, as well as a fourth problem—taskdata dependency, which is not directly related to data redundancy—will be examined next. DATA STORAGE Chapter 1 showed that an efficient information system captures and stores data only once and makes this single source available to all users who need it. This is not possible in the flat-file environment. To meet the private data needs of users, organizations must incur the costs of both multiple collection and multiple storage procedures. Indeed, some commonly used data may be duplicated dozens, hundreds, or even thousands of times, creating excessive storage costs. DATA UPDATING Organizations have a great deal of data stored on master files and reference files that require periodic updating to reflect operational and economic changes. For example, a change in a customer’s name or F I G U R E 9-1 FLAT-FILE DATA MANAGEMENT DATA User 1 Transactions Program 1 A, B, C User 2 Transactions Program 2 X, B, Y User 3 Transactions Program 3 L, B, M CHAPTER 9 Database Management Systems address must be reflected in the appropriate master files. This piece of information may be important to several user departments in the organization, such as sales, billing, credit, customer services, sales promotion, and catalog sales. When users maintain separate files, any such change must be made separately for each user. This adds significantly to the cost of data management. CURRENCY OF INFORMATION In contrast to the problem of performing multiple updates is the problem of failing to update the files of all users affected by a change. If update messages are not properly disseminated, then some users may not record the change and will perform their duties and make decisions based on outdated data. TASK-DATA DEPENDENCY Another problem with the flat-file approach is the user’s inability to obtain additional information as his or her needs change. This problem is called task-data dependency. The user’s information set is constrained by the data that he or she possesses and controls. For example, in Figure 9-1, if the information needs of User 1 change to include Data L, User 1’s program would not have access to these data. Although Data L exists in the files of another user, keep in mind the culture of this environment. Users do not interact as members of a user community. They act independently. As such, User 1 may be unaware of the presence of Data L elsewhere in the organization. In this environment, it is difficult to establish a mechanism for the formal sharing of data. Therefore, Data L would need to be recreated from scratch. This will take time, inhibit User 1’s performance, add to data redundancy, and drive data management costs even higher. THE DATABASE APPROACH Figure 9-2(a) presents a simple overview of the database approach with the same users and data requirements as in Figure 9-1. The most obvious change from the flat-file model is the pooling of data into a common database that is shared by all the users. F I G U R E 9-2(a) THE DATABASE CONCEPT DATABASE User 1 Transactions Program 1 A, B, User 2 Transactions C, Program 2 X, Y, L, User 3 Transactions M Program 3 399 400 PART III Advanced Technologies in Accounting Information FLAT-FILE PROBLEMS SOLVED Data sharing (the absence of ownership) is the central concept of the database approach. Let’s see how this resolves the problems identified.  No data redundancy. Each data element is stored only once, thereby eliminating data redundancy and reducing storage costs.  Single update. Because each data element exists in only one place, it requires only a single update procedure. This reduces the time and cost of keeping the database current.  Current values. A change any user makes to the database yields current data values for all other users. For example, when User 1 records a customer address change, User 3 has immediate access to this current information.  Task-data independence. Users have access to the full domain of data available to the firm. As users’ information needs expand beyond their immediate domain, the new needs can be more easily satisfied than under the flat-file approach. Only the limitations of the data available to the firm (the entire database) and the legitimacy of their need to access it constrains users. CONTROLLING ACCESS TO THE DATABASE The database approach places all the firm’s information eggs in one basket. It is essential, therefore, to take very good care of the basket. The example in Figure 9-2(a) has no provision for controlling access to the database. Assume Data X is sensitive, confidential, or secret information that only User 3 is authorized to access. How can the organization prevent others from gaining unauthorized access to it? THE DATABASE MANAGEMENT SYSTEM Figure 9-2(b) adds a new element to Figure 9-2(a). Standing between the users’ programs and the physical database is the database management system (DBMS). The purpose of the DBMS is to provide controlled access to the database. The DBMS is a special software system that is programmed to know which data elements each user is authorized to access. The user’s program sends requests for data to the DBMS, F I G U R E 9-2(b) THE DATABASE CONCEPT User 1 Transactions Program 1 A, B, User 2 Transactions Program 2 D B M S C, X, Y, L, User 3 Transactions M Program 3 CHAPTER 9 Database Management Systems which validates and authorizes access to the database in accordance with the user’s level of authority. The DBMS will deny requests for data that the user is unauthorized to access. As one might imagine, the organization’s criteria, rules, and procedures for assigning user authority are important control issues for accountants to consider. THREE CONCEPTUAL MODELS Over the years, several different architectures have represented the database approach. Early database models are as different from modern database models as they were from traditional flat files. The most common database approaches used for business information systems are the hierarchical, the network, and the relational models. Because of certain conceptual similarities, the hierarchical and network databases are termed navigational or structured models. The way that data are organized in these early database systems forces users to navigate between data elements using predefined structured paths. The relational model is far more flexible by allowing users to create new and unique paths through the database to solve a wider range of business problems. Although their limitations are severe and their ultimate demise is inevitable, hierarchical and network models still exist as legacy systems that support mission-critical functions in some companies. Most modern systems, however, employ relational databases. The main text of the chapter focuses on the relational model. The key features of structured database models are outlined in the chapter appendix. 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. 401 402 PART III Advanced Technologies in Accounting Information F I G U R E 9-3 ELEMENTS OF THE DATABASE CONCEPT Database Administrator System Requests System Development Process Applications Transactions U S E R S Transactions User Programs DBMS Data Definition Language User Programs Host Operating System Data Manipulation Language Transactions User Programs Query Language Physical Database Transactions User Programs User Queries 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. Database Management Systems CHAPTER 9 403 F I G U R E 9-4 OVERVIEW OF DBMS OPERATION Main Memory Application Work Location User Access to Data Buffer Area 6 A, B A, B 5 User Program #1 A, B A, B User Program #2 L, M, X L, M, X User Program #3 A, B, L, M, X, Y, Z A, B, L, M, X, Y, Z User Programs User View or Subschema A, B, C, ... L, M, N, ... X, Y, Z Descriptions of Data Structure— File Organization and Access Methods 4 A B C Access Method Programs L M N … 1 … 2 X Y Z 3 Conceptual View or Schema (Logical Description of All Data Elements) Internal View Operating System Physical Database Database Management System 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. 404 PART III Advanced Technologies in Accounting Information 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 organization 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 database 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 Database Management Systems CHAPTER 9 F I G U R E 9-5 EXAMPLE OF SELECT COMMAND USED TO QUERY AN INVENTORY DATABASE Selected Attributes Inventory W-HouseLoc UnitCost VenNum 300 Chicago 1.34 1251 Nut 1/4 500 Chicago .85 1195 1569 Flange 65 Denver 56.75 1251 1570 Disc Tulsa 22.00 1893 1571 End Pipe 93 Denver 7.35 7621 1572 In-Pipe 93 Denver 18.20 1251 1573 Pump 603 Chicago 85.00 1195 Item Desc On-Hand 1567 Bolt 3/8 1568 1000 SQL Command SELECT Item, Desc, On-Hand, W-House-Loc, Ven-Num FROM Inventory WHERE On-Hand > 300 Item Desc 1568 Nut 1/4 1570 Disc 1573 Pump On-Hand 500 1000 603 Selected Records W-HouseLoc VenNum Chicago 1195 Tulsa 1893 Chicago 1195 Report Produced 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. 1 Adapted from F. R. McFadden and J. A. Hoffer, Database Management, 3rd ed. (Redwood City, CA: Benjamin/Cummings Publishing, 1991), 343. 405