Database Systems for Mangement This book is licensed under a Creative Commons Attribution 3.0 License Database Systems for Management Third edition James F. Courtney David B. Paradice Kristen L. Brewer Julia C. Graham Copyright © 2010 James F. Courtney and David B. Paradice For any questions about this text, please email: [email protected] The Global Text Project is funded by the Jacobs Foundation, Zurich, Switzerland This book is licensed under a Creative Commons Attribution 3.0 License DATABASE SYSTEMS FOR MANAGEMENT, THIRD EDITION James F. Courtney, David B. Paradice Kristen L. Brewer, and Julia C. Graham PART ONE Database Fundamentals CHAPTER CONTENTS Data as an Organizational Asset Managing Organizational Data Data versus Information Elements of Database Systems People Computer Hardware and Software Databases Database Planning and Design Techniques Database Systems and Other Organizational Information Systems Effective Systems and Efficient Systems . Case Example: Data Management at the Conventional National Bank . Case Example: Byer Kaufman's Portfolio Management System Objectives of Database Systems Access Flexibility Data Integrity Data Security Data Independence and the ANSI/SPARC Model Reduced Data Redundancy Data Shareability Data Relatability Standardization Personnel Productivity Disadvantages of the Database Approach 1-1 CHAPTER 1 Introduction to Database Systems Organizations capture and process data for a variety of reasons. Accounting data are used to measure the financial health and vitality of the enterprise: It is useful to internal corporate management in keeping track of how the firm is performing; it is useful to people outside the firm, such as investors interested in the firm's stock and bankers to whom the firm has applied for a loan. Records on a host of other organizational entities, including employees, inventories, customers, and suppliers, are required as well. One of the main uses of data relates to making decisions. A manager must decide, for example, on the price of the firm's product or on how many units of the product to produce. An investor must decide whether to invest in a firm; a banker must decide whether to make a loan. The Federal Reserve Board must decide whether to increase or decrease interest rates, or hold them stable. In all of these cases, it is desirable to have reliable information on which to base the decision. Information, and the data on which it is based, are valuable organizational assets that warrant careful management. DATA AS AN ORGANIZATIONAL ASSET Major functional areas in organizations have evolved from recognition that the function being managed is of vital importance. Financial and accounting departments, for example, manage predominantly monetary assets of an organization. Few would argue that these assets are unimportant to the organization. Marketing departments handle the advertising for new and existing products, carry out research studies, and distribute products. These activities are obviously important. Production, personnel, and other departments also specialize in efficient and effective management of the assets in these areas. Similarly, organizational data and information must be regarded as an asset of vital importance to the organization. A few examples should make clear how important data and information can be. Consider a company that produces a newsmagazine with national distribution. It can collect data about its subscribers that can be used in many ways to gain an advantage in the marketplace; it can, for example, use data about personal interests to help select the stories to be printed or to entice organizations to purchase advertising space. Or imagine an organization that, thanks to a secret formula, manufactures the most popular product in a highly competitive market. In fact, the company maintains the results of hundreds of consumer tests of variations of the formula in a database. These can be analyzed whenever consumer preferences appear to be changing, giving the company the appearance of an uncanny ability to anticipate consumer preferences. Were this data to fall into the hands of competitors, all advantage in the marketplace would be lost. Or consider such routine information as salary or production schedule data. Salary data in the hands of a competing organization could result in the loss of top employees through offers of higher compensation. Production data could allow a competitor to anticipate an organization's strategy in the near future. Each of these actions could have devastating results on an organization. These examples are intended to emphasize the point that data are indeed a valuable asset to an organization. You can doubtlessly think of other examples. As with any valuable asset, the organization should have persons and procedures to effectively manage the asset. The management of organizational data is the main topic of concern in this book. MANAGING ORGANIZATIONAL DATA Organizations of all sizes now use computers to perform the data processing functions required to provide information for management decision-making. Experience with business applications of data processing has shown that the data itself is a valuable organizational resource that must be carefully 1-2 managed. The task of managing organizational data and data processing functions is often called data management or information management. The data resources of an organization are usually stored in databases, which are highly integrated sets of files shared by users throughout the organization. Because it is impractical to store all the data for even relatively small organizations in one database, organizations design database systems, which are integrated collections of databases along with users who share the databases, personnel who design and manage databases, techniques for designing and managing databases, and computer systems to support them. These are rather simple definitions of databases and database systems, but they will suffice for now. These definitions are expanded throughout the book. This book provides an introduction to management information systems with an emphasis on special technologies and techniques for designing and managing organizational database systems. After studying the material in this book, you should have mastered at least the basic principles underlying information systems, the technology used to manage databases and the techniques used to design and implement effective database systems. When reading this chapter, think about what is meant by data as an organizational resource and about how managers use data. Keep in mind the idea that the main goal of database systems is to provide managers with information so that they can make effective decisions about how to run the organization. The basic theme of this book is that reliable information and accurate data are required for effective managerial decisions. The object of the book is to demonstrate how to design and manage effective database systems. The users of database systems are the customers of the information systems department. If these customers do not use the product (the database system), then the system has not been designed effectively. To emphasize the viewpoint of database system users, each chapter includes a section entitled "The User's View" in which we discuss the concerns of users as related to that chapter's topics. DATA VERSUS INFORMATION Information and data are not the same thing. Information is sometimes defined as processed data, but simply because data has been processed does not mean that it is useful to an organization. The average price of corn in Iowa may be meaningful to thousands of farmers but useless to an automobile manufacturer. For the purposes of this book, data is defined simply as numbers, words, names, and other symbols that can be stored in a computer system. Information is simply useful data. If a single data value (such as the price of a competitor's product) is useful, then it is information. Several data values may be manipulated or processed to create information (such as in computing the average price of several competing products). If the resulting data value is not useful, then it is not information, no matter how much it has been processed. ELEMENTS OF DATABASE SYSTEMS The principal elements in a database environment are people, computer hardware, computer software, databases, and techniques for planning, designing, implementing, and managing databases. Each of these elements is examined in more detail in the next chapter. A brief overview is given here. People The people involved with database systems can be divided into two groups: those who use information provided by the system and those who design, develop, and manage the system itself. If a database system is to be of value to the organization, it is imperative that it be designed to support the needs of information users. This point is emphasized repeatedly throughout the book; it cannot be overemphasized. The design of user-oriented database systems is considered in detail in the chapters on 1-3 system design, implementation, and administration. Those people responsible for the design, development, and administration of database systems are often referred to as database administrators. The position of database administrator is highly challenging and should be a high-level managerial position. Database administrators must possess well-developed skills in both technical and managerial aspects of information management, because they are the primary liaisons between the community of users and the systems development staff. The personnel responsible for developing database systems are information analysts or database analysts. Information analysts work closely with users of information to carefully define information requirements and to structure these requirements into a logical form. Database analysts use database technology to design systems satisfying those requirements. This book is aimed primarily at those who seek to develop successful database systems and who may ultimately become database administrators, but it is of interest to users of database systems as well. Thus the book is concerned with both technical and managerial aspects of information management. Database administration is discussed in detail in Chapters 10 and 11. Computer Hardware and Software Computer hardware and software are two different elements of a database environment, but they are discussed together in this chapter. Hardware and software form the technological foundation for database systems. This book contains a limited discussion of the hardware for database management; hardware is an extensive and complex topic requiring special treatment far beyond the scope of the book. Software for information management can be separated into two groups: applications software and database management systems. Database management systems (DBMS) are commercial software packages designed to provide the basis for building database systems. Many such packages are on the market today, and thousands of organizations use them. These packages are part of the technology of information management and usually depend heavily on the operating system for many of their basic functions. Organizations acquire database management systems and develop or acquire applications software to satisfy their particular database processing requirements. You are undoubtedly familiar with the way ordinary programming languages such as Visual Basic (VB) describe data files. As is illustrated shortly, this approach may not provide ready access to information. The database approach is intended to overcome these problems. Databases As shown in Figures 1-1 and 1-2, database management systems are designed to allow various programs to share the data they have in common. In Figure 1-1, Joe has his files and his data, and Sue has her files and her data. Joe uses the manufacturing budget in his program, but Sue does not. Sue uses salary data, but Joe does not. In fact, Joe is not even allowed to see or access the salary data; it is privileged information. Both programmers use product prices. In Figure 1-2 the programmers have merged their data into an integrated database ("our" data). The integrated database is defined in the schema, which describes all the data items and relationships in the entire database. Note, however, that each programmer still has some data (salary and manufacturing) that is inaccessible to the other, even though it is stored in the integrated database. Each programmer's individual view of the database is defined in a subschema. The use of a database management system to develop an integrated database is discussed later in this chapter. For now, note that a database is an integrated collection of shared files. The database management system acts as a sophisticated buffer between applications programs and the data those programs need. This approach separates data from applications programs and makes data management (the treatment of data as an important organizational resource) possible. 1-4 Database Planning and Design Techniques Since database systems involve people from all parts of the organization with a variety of information needs, the development and operation of database systems must be very carefully planned and managed. The databases themselves must be carefully designed to provide efficient access to information required by the various users. The first step in converting to a database approach should be the development of (1) a master plan that specifies in general terms the various applications and databases to be included in the overall system and (2) a schedule for detailed design and implementation of these applications and databases. Detailed database design consists of three major phases: information requirements analysis, logical design, and physical design. Information requirements analysis is work done with users to define information needs. Logical design is the development of schema and subschema definitions. Physical design establishes exactly how the data will be organized and stored on the storage devices. Database planning and design are previewed in the next chapter and examined extensively in other chapters of the text. DATABASE SYSTEMS AND OTHER ORGANIZATIONAL INFORMATION SYSTEMS Databases and database management systems provide the infrastructure on which other organizational information systems are built. Organizational information systems include transaction processing systems (TPS), management information systems (MIS), and decision support systems (DSS). Relative to the database system, these are applications programs that derive data from the database via the DBMS. The relationships among these various systems are illustrated in Figure 1-3. The entire set of organizational information systems is shown as a pyramid with the DBMS and databases below the base. Transaction processing systems form the bottom of the pyramid and are the lifeblood systems of the organization because they support the processing of "production" data--for example, for inventory maintenance, accounts receivable and payable, and other accounting functions. These transactions provide most of the internal data used as the basis for generating information for managerial decision-making. Transactions themselves are usually well defined; forms displayed on terminal screens can be used to gather and display data. These systems are used primarily by clerical personnel and first-line managers. Management information systems are in the middle of the pyramid and are used primarily by middle management to control the organization. These systems derive much of their information by summarizing and abstracting data from transaction processing systems. They tend to be report oriented; standard reports are produced periodically (weekly, monthly, or annually) for use by middle managers to support tasks such as budget decisions and personnel assignments. Decision support systems are designed to provide information for managerial decision making in cases where the decision is not clear-cut (ill-structured or semi-structured in DSS jargon). These problems tend to occur at the apex of the organizational pyramid. Decision support systems often use mathematical and statistical techniques to manipulate and analyze data. It is difficult to anticipate information needs in a DSS environment, so these systems must be flexible and adaptable. Database management technology has been used as the basis for some decision support systems. These are referred to as data-oriented decision support systems. Many of the examples in this book are data-oriented decision support systems. Since decision support systems have become quite popular and often rely on data extracted from database systems, it is important to understand their relationship to database management systems. Decision support systems are discussed in Chapter 13. 1-5 EFFECTIVE SYSTEMS AND EFFICIENT SYSTEMS It is useful to distinguish between effective database systems and efficient database systems. Effective systems provide correct, current information that is relevant to the decision at hand. Ahituv and Neumann (1986) refer to effectiveness as "doing the right thing." To provide effective database systems, analysts must work closely with managers and other users to carefully specify information requirements. The system cannot be effective unless information needs are accurately determined and the database system designed to serve those needs. Efficient systems, on the other hand, perform a task in a cost-effective manner. A database system must provide the required information at a reasonable cost. Ahituv and Neumann call this "doing the thing right." Of the two, the concern for effectiveness predominates. If a database system does not provide correct, current, relevant information, it does not matter how efficiently the data are processed--it is still useless. Conversely, if the system is effective, it may contain certain inefficiencies but still be highly useful. Effective database systems are expensive to develop, but an organization cannot afford to go bankrupt in developing effective systems. The value of the information provided must exceed the cost of providing that information. To illustrate some of the concepts already introduced in this chapter and to explain the need for effective data management, two case examples are presented next. The first is that of the Conventional National Bank, which uses the customary file approach to data management. The second contains a portfolio management example that illustrates the database approach. Case Example: Data Management at the Conventional National Bank The Conventional National Bank (CNB) acquired a computer in the mid-1960s to automate check processing. The bank soon realized that the computer was useful for many other purposes, such as maintaining information on mortgage loans, auto loans, savings accounts, and portfolios of common stocks, bonds, and other securities. During the late 1960s and early 1970s, the bank converted these applications from manual to computer-based systems. Most of the software to support these applications was written in COBOL. By the mid-1970s, thousands of COBOL programs were in use. Some of these programs were enormous--several thousand lines long. Together, all the programs comprised well over a million lines of COBOL code. The bank is organized on the basis of the functions it performs: there are departments for checking accounts, savings accounts, auto loans, mortgage loans, trusts, and so on. To reduce the need for interdepartmental communication, separate data files are maintained for each department. Because the bank has several thousand customers, and most customers have several accounts, these files now occupy several billion characters of disk storage. Tia Fuentes has been a regular customer of CNB for 15 years. Tia began her association with the bank when she graduated from college and moved to Conventional City to begin a position as manager trainee for Silver Flatware Products. Over the years, Tia has accumulated some savings, which she keeps in a passbook savings account with the bank. Two years ago she purchased a new car with a loan she got at CNB. Three months ago, Tia was promoted to assistant manager, and shortly thereafter she moved into a new home. Tia now resides comfortably in her new estate near the flatware plant. After an important staff meeting one day, Tia returned to her office to find a telephone message from Gilda Kerr, an auto loan officer at CNB. Tia returned the call only to discover that Gilda was calling to ask why Tia had not made car payments for the past three months. After all, CNB had sent the usual computer-prepared reminder each month with a preprinted return envelope for her convenience. Tia was stunned! In the excitement of her promotion and move, she had completely forgotten about the car payment. She had called the bank to give them her new address, and she had received her monthly 1-6 bank statements and notices on the mortgage loan payments. She had not received any notices on the car payments, and they had completely slipped her mind. Gilda knew Tia to be honest and reliable, so she said she would look into the matter. The manager of Information Services (IS), Henry Lew, showed Gilda some diagrams (Figure 1-4) and explained that all the departmental files had originally been set up independently and that Information Services got updates for departmental files from the departments themselves. IS was not at fault because they did not get a notice to change Tia's address from the auto loan department. Gilda asked why one update form did not suffice, since the system could use the data from that form to change all occurrences of the address. Henry explained that no one in IS had any way of knowing if a person's name and address were in a file. A program could be written to search all the files, but it would be extremely inefficient because so many files would have to be examined. Gilda wondered aloud why CNB could not just have one file of names and addresses that was shared by all the departments in the bank. Henry replied that they could, but then all the existing programs using names and addresses would have to be changed, and IS did not really even know all the programs that used that data. Just tracking down those programs and the programmers who wrote them (if they were still at CNB) would be a big job. In desperation, Gilda asked if IS could not at least find each name occurring in more than one file and then compare the addresses to see if they were consistent in all files. Henry said that IS would be glad to do that, but that it would take a special program. IS was already overloaded with a waiting list of two years' worth of programming. With luck, however, they might be able to develop the program in 18 months. Gilda was flabbergasted! How could it take 18 to 24 months to produce something as simple as a list of people whose names and addresses appeared in more than one place in the computer system? She left the data processing department more discouraged than ever about computers. Why should the bank waste time putting data into the computer system, when it took forever to get it out in a usable form, and even then was wrong half the time? The intent of the preceding scenario is to illustrate some of the problems that arose from the conventional file approach to data management. Special programs have to be written for simple tasks. It may be difficult to extract data in the system if it resides in separate files. Users (and data processing personnel) find these problems exasperating and inefficient. The next scenario shows some of the advantages of the database approach. Case Example: Byer Kaufman's Portfolio Management System Byer Kaufman was known as the child prodigy of his high school class. While still in high school he studied the stock market and developed a highly successful investment strategy that he implemented on his personal computer. Using his strategy, Byer had become a teenage millionaire. Instead of going to college, getting married, or joining the army as most of his friends had, Byer started his own investment counseling firm and was doing extremely well. He had converted his investment software to a larger computer and now maintained data on thousands of securities in his database. In addition, he had added a simple query language that allowed his clients to access the database and his software directly so that they could select their own investments and manage their own portfolios if they chose to do so. His clients could even copy ("download") portions of his database to their own personal computer (PC) and manipulate the data there with a PC version of his query language. Many of his clients, however, were busy with other tasks--they preferred to have Byer manage their portfolios for them. Byer is presently demonstrating his system to a potential new client, Joan Yasuda. Joan is interested in searching Byer's database for some underpriced stocks: "What would I do to find a stock in the airline industry whose price is less than five times its earnings?" "That's a good question," Byer replied, "One that requires what database people call an 'ad hoc' query. It's ad hoc because the database designer may not have anticipated that precisely that question would be posed to the system. Nevertheless, the system can still answer it. The name of the stock is 1-7 FIRM_NAME in the system, and it's stored in a table named STOCK_DATA_TABLE along with current price, earnings, and dividends and the firm's industry [see Figure 1-5a]. We can compute price-earnings ratios just by dividing price by earnings, so you would type in this:" SELECT FIRM_NAME FROM STOCK_DATA_TABLE WHERE INDUSTRY = 'AIRLINE' AND (PRICE / EARNINGS) < 5.00 Almost immediately the system displayed a list of firms meeting these criteria. "Next," said Byer, "I'll show you how I manage portfolios for my clients who don't have time to do all the analysis and so forth themselves." "That's a good idea," said Joan. "Okay," said Byer, "My system works like this. First, I have an enormous database consisting of 20 years of data from financial statements and stock market data on about 5000 companies. This database is about 2.5 billion characters in size, and it is updated every evening. I have several mathematical models based on sophisticated statistical techniques to analyze market trends and the behavior of individual securities to pick 200 or so that are good investments at any one time. I also have software based on management science models that I use to select and revise portfolios for my clients from the 200 good securities. I developed these models myself and the software is legally protected as a trade secret, so no one else can provide you with my approach. "Data for individual portfolios is maintained in separate files, so I can manage them separately. These portfolios contain only the name of the securities you hold and the number of shares of each [see Figure 1-5b]. However, they are all integrated into a common database with the stock data, so I can extract prices, earnings, and dividends from the STOCK-DATA-TABLE and send you monthly reports on the value of your account; that is, I know the firm name from your portfolio file and can relate that to the stock data file to retrieve current prices to compute the current value of your portfolio. The database system makes it easy to do that either on-line or via a program. "At the end of the year, I send you an annual report to help your accountant prepare your income tax return. This diagram [see Figure 1-6] shows how all the pieces fit together. Let me show you how the portfolio selection system works." Byer hit the key labeled "MAIN SCREEN," and the main menu appeared on the screen (Figure 1-7). He selected the option to initiate a new portfolio by entering "2," because he wanted the system to continue displaying menus so that Joan could see the options available at that point. He could have entered the word "initiate" and the system would have entered Command Mode, which is faster for experienced users because it does not display options but expects the user to type in commands without special prompts. Next the prompt in Figure 1-8 appeared. Byer had developed these menus and prompts to assist in gathering the data for new clients. Byer filled in the blanks as shown in Figure 1-9. Since Joan had plenty of income from other sources, he put in growth as the primary objective; meaning Joan wants to maximize the increase in the money invested ($1,000,000) rather than trying to get immediate cash. Also, because Joan has plenty of money, she can accept high risk in the hopes of getting a high return. The system responded, OK. I'M GOING TO SELECT A HIGH-RISK, GROWTH PORTFOLIO FOR YOUR $ 1.0 MILLION DOLLARS. THAT IS WHAT YOU WANT ME TO DO, ISN'T IT? Byer typed in "yes," and the system displayed: PLEASE WAIT WHILE I SELECT A GOOD PORTFOLIO FOR YOU. IT WILL TAKE A FEW SECONDS TO DO ALL THE SEARCHING AND CALCULATIONS. 1-8