Haaff elementary school lunch menu

Chapter Highlights Section I Technical Foundations of Database Management Database Management Fundamental Data Concepts Real World Case: Beyond Street Smarts: Data-Driven Crime Fighting Database Structures Database Development Section II Managing Data Resources Data Resource Management Types of Databases Real World Case: Duke University Health System, Beth Israel Deaconess Medical Center, and Others: Medical IT Is Getting Personal Data Warehouses and Data Mining Traditional File Processing The Database Management Approach Real World Case: Cogent Communications, Intel, and Others: Mergers Go More Smoothly When Your Data Are Ready Real World Case: Applebee’s, Travelocity, and Others: Data Mining for Business Decisions

Learning Objectives 1. Explain the business value of implementing data

resource management processes and technologies in an organization.

2. Outline the advantages of a database manage- ment approach to managing the data resources of a business, compared with a file processing approach.

3. Explain how database management software helps business professionals and supports the operations and management of a business.

4. Provide examples to illustrate each of the follow- ing concepts:

a. Major types of databases. b. Data warehouses and data mining. c. Logical data elements. d. Fundamental database structures. e. Database development.

177

CHAPTER 5

DATA RESOURCE MANAGEMENT

M o d u l e I I

Business Applications

Development Processes

Management Challenges

Foundation Concepts

Information Technologies

obr76817_ch05_177-216.indd Page 177 8/2/10 2:40 PM user-f498obr76817_ch05_177-216.indd Page 177 8/2/10 2:40 PM user-f498 /Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387/Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387

178 ● Module II / Information Technologies

SECTION I Technical Foundations of Database Management

Just imagine how difficult it would be to get any information from an information sys- tem if data were stored in an unorganized way or if there were no systematic way to re- trieve them. Therefore, in all information systems, data resources must be organized and structured in some logical manner so that they can be accessed easily, processed ef- ficiently, retrieved quickly, and managed effectively. Data structures and access methods ranging from simple to complex have been devised to organize and access data stored by information systems efficiently. In this chapter, we will explore these concepts, as well as the managerial implications and value of data resource management. See Figure 5.1 . It is important to appreciate from the beginning the value of understanding data- bases and database management. In today’s world, just about every piece of data you would ever want to access is organized and stored in some type of database. The ques- tion is not so much “Should I use a database?” but rather “What database should I use?” Although many of you will not choose a career in the design of databases, all of you will spend a large portion of your time—whatever job you choose—accessing data in a myriad of databases. Most database developers consider accessing the data to be the business end of the database world, and understanding how data are structured, stored, and accessed can help business professionals gain greater strategic value from their organization’s data resources. Read the Real World Case 1 on the use of data for crime fighting and law enforcement. We can learn a lot about the many uses of data assets from this case.

Before we go any further, let’s discuss some fundamental concepts about how data are organized in information systems. A conceptual framework of several levels of data has been devised that differentiates among different groupings, or elements, of data. Thus, data may be logically organized into characters, fields, records, files, and databases , just as writing can be organized into letters, words, sentences, paragraphs, and documents. Examples of these logical data elements are shown in Figure 5.2 .

The most basic logical data element is the character , which consists of a single alphabetic, numeric, or other symbol. You might argue that the bit or byte is a more elementary data element, but remember that those terms refer to the physical storage elements provided by the computer hardware, as discussed in Chapter 3. Using that understanding, one way to think of a character is that it is a byte used to represent a particular character. From a user’s point of view (i.e., from a logical as opposed to a physical or hardware view of data), a character is the most basic element of data that can be observed and manipulated.

The next higher level of data is the field , or data item. A field consists of a grouping of related characters. For example, the grouping of alphabetic characters in a person’s name may form a name field (or typically, last name, first name, and middle initial fields), and the grouping of numbers in a sales amount forms a sales amount field. Specifically, a data field represents an attribute (a characteristic or quality) of some entity (object, person, place, or event). For example, an employee’s salary is an at- tribute that is a typical data field used to describe an entity who is an employee of a business. Generally speaking, fields are organized such that they represent some logi- cal order, for example, last_name, first_name, address, city, state, and zip code.

All of the fields used to describe the attributes of an entity are grouped to form a record . Thus, a record represents a collection of attributes that describe a single instance of an entity . An example is a person’s payroll record, which consists of data

Database Management

Fundamental Data Concepts

Character

Field

Record

obr76817_ch05_177-216.indd Page 178 8/2/10 2:40 PM user-f498obr76817_ch05_177-216.indd Page 178 8/2/10 2:40 PM user-f498 /Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387/Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387

Chapter 5 / Data Resource Management ● 179

standards for interoperability, and exploiting those digital resources in the most effective manner. The era of data-driven law enforcement began in the early 1990s in New York City. There, police chief William Bratton sought to impress newly elected mayor Rudolph Giuliani with a radical approach to policing that came to be known as Comp- Stat. CompStat put an emphasis on leveraging data—accurate, detailed, and timely—to optimize police work. “Police departments are powerful collectors of data,” says Michael Berkow, president of Altegrity Security Consulting (ASC), a newly launched division of security firm Altegrity. Before joining ASC last month, Berkow was chief of the Savannah-Chatham police department, and before that he was second in command to Bratton in Los Angeles after Bratton left New York to be chief of the Los Angeles Police Department. Police departments were motivated to implement or upgrade IT systems by the Y2K frenzy, Berkow says. “By 2000-2001, everybody had some level of digital informa- tion,” he says. That and CompStat led to a movement known by the initials ILP, which stand for “information-led policing” or, according to some, “intelligence-led policing.” The concept is simple: Leverage data to help position limited police resources where they can do the most good. It’s an effort to be more proactive, to “change the environ- ment,” Berkow says, from the reactive, response-oriented methods of the past. To a great extent, data are about the context of criminal behavior. “We know that the same small group of criminals is responsible for a disproportionate amount of crime,” says Berkow. Police refer to that group as PPOs: persistent prolific offenders. Past criminal behavior, such as domestic violence, can be a strong indicator of potential future problems. When Berkow was chief in Savannah, his department went through data on recent homicide cases and noticed an interesting data point: Of about 20 arrests for homicide, 18 of those people had prior arrests for possession of firearms. “We started this very detailed review of every aspect of our gun arrests,” he says. Law enforcement officials often refer to the need for ac- tionable information. One of the first ways police agencies used incident-report data in digital form was in conjunction with geographical information systems, in support of what’s known as electronic crime mapping, or hot-spot analysis. Police in the city of Edmonton, Alberta, brought in data analysis technology from business intelligence vendor Cognos (now part of IBM) a few years ago. In their first project, police officials concentrated on using the reporting tool in conjunction with a new geographic-based resource deployment model being implemented by the agency. “Our business analytics reports became a key component of how we deployed policemen around the city,” says John Warden, staff sergeant in the business performance section of the Edmonton Police Service.

On a Saturday afternoon last summer, Mark Rasch took his son to his baseball game at a park in Georgetown, Maryland. The ballpark is located in an area that has zone parking with a two-hour limit. Rasch was forced to park in a spot that was a bit of a hike from the ball field. He later eyed an opening closer to the park and moved his car there. The game ended. Rasch packed up and was ready to pull away when he noticed a parking enforcement officer writing tickets. “I’m OK, right?” he asked, assuming that because he had moved his car she wouldn’t know he’d been parked in the zone for more than two hours. Wrong. The officer not only knew that he had moved his car but when and how long he’d been parked within the zone. Fortunately, she didn’t write him a ticket as he was about to pull out. But the encounter left Rasch, who is a lawyer and a cybersecurity consultant, a little spooked at the realization of just how much information law enforcement is generating. If there was a time when law enforcement agencies suf- fered from an information deficit, it has passed. Of the more than 18,000 law enforcement agencies across the United States, the vast majority has some form of technology for collecting crime-related data in digital form. The biggest city agencies have sophisticated data warehouses, and even the most provincial are database savvy. So it’s not surprising that law enforcement and criminal justice agencies are running into the same data-related prob- lems that CIOs have been experiencing for years: ensuring data quality and accessibility, developing and enforcing

Beyond Street Smarts: Data-Driven Crime Fighting

REAL WORLD

CASE 1

Source: © Thinkstock/PunchStock.

Law enforcement agencies have stepped up the use of data in not only fighting, but also preventing, crime.

F IGURE 5.1

obr76817_ch05_177-216.indd Page 179 8/2/10 2:40 PM user-f498obr76817_ch05_177-216.indd Page 179 8/2/10 2:40 PM user-f498 /Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387/Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387

Now the agency is using the data to plot criminal activ- ity according to both geographic area and comparative his- tory. “We’re really delving into those analytics in terms of place and time,” says Warden. The holy grail of informa- tion-led policing is what’s referred to as predictive policing: being able to predict where and when crimes may occur. That’s where Chicago wants to go. The Chicago Police Department operates what Jonathan Lewin, commander of information services, refers to as “the largest police transaction database in the United States.” Costing $35 million, Chicago’s Citizen and Law Enforcement Analysis and Reporting (CLEAR) system processes “all the arrests for all the depart- ments in Cook county—about 120—in real time,” Lewin says, and 450 local, state, and federal law enforcement agencies have query access to it. Lewin’s IT shop has about 100 staffers and employs between 10 and 20 contract workers from Oracle, whose database technology the system is based on. Chicago’s police department is working with the Illinois Institute of Technology (IIT), by way of a $200,000 grant from the National Institute of Justice, on an “initial explora- tion” of a predictive policing model. The grant was awarded partly on the basis of work done by Dr. Miles Wernick of IIT in the area of medical imaging and pattern recognition, and the project involves exploring “nontraditional disci- plines” and how they might apply to crime projection. “We’re going to be using all the data in the CLEAR system,” Lewin says, including arrests, incidents, calls for service, street-gang activity, as well as weather data and community concerns such as reports of streetlights out. “This model will seek to use all these variables in attempting to model future patterns of criminal activity,” he says. SPSS is a name often associated with predictive policing. The statistical-analysis software developer, recently acquired by IBM, has customer histories that tout the success of its tools in the criminal justice environment, such as the Mem- phis, Tennessee, police force, which SPSS says reduced rob- beries by 80 percent by identifying a particular hot spot and proactively deploying resources there. But can software really predict crime? “It’s not a binary yes or no; it’s more of an assessment of risk—how probable something is,” says Bill Haffey, technical director for the public sector at SPSS.

The private sector is also doing its part. CargoNet, the first-ever national database of truck theft information, is a joint project from insurance data provider ISO and the Na- tional Insurance Crime Bureau (NICB). CargoNet will col- lect up to 257 fields of data detailing everything from destination, plate number, and carrier; to the time, data, and location of the theft; to serial numbers and other identifying details on the stolen goods. Refreshed several times per day, CargoNet is expected to track more than 10,000 events per year, driving both a national alerting system and a corre- sponding truck stop watch program. Truck theft happens mostly on weekends, and it’s rife around the Los Angeles basin, Atlanta, Miami, Dallas/Ft. Worth, and Memphis, Tennessee. Trucks and trailers typi- cally slip away in the dark of night from truck stops, rest ar- eas, distribution centers, and transfer points. The goods most often hit are consumer electronics, food, wine and spir- its, clothing, and other items easily sold on the street. These historical patterns are well known, but cops on the beat need up-to-the-minute information on the latest truck stops and distribution centers hit, the time of day per- petrators strike, and the type of goods stolen. Carriers and manufacturers want fresh, nationwide in- formation so they can change the timing of deliveries and avoid specific truck stops and routes. Insurers want a single source of data so they can get a better gauge risk and bring the problem under control nationwide. All this collecting, warehousing, and mining crime- related data begs the question: How much is too much? The Georgetown incident still bothers Rasch. “What it meant was that D.C. was keeping a database of people who are le- gally parked,” says Rasch, which, from a privacy standpoint, is “more intrusive than chalking the tires.” Pertinent questions include: How long do they hold onto that data? And with whom do they share it? It’s an important discussion to have, both in terms of privacy and effective po- lice methods. After all, as Rasch points out, it was a parking ticket that led to the arrest of serial killer Son of Sam.

Source: Adapted from John Soat, “Beyond Street Smarts,” InformationWeek , November 16, 2009; and Doug Henschen, “ National Database Tracks Truck Thefts,” InformationWeek , January 26, 2010.

1. What are some of the most important benefits derived by the law enforcement agencies mentioned in the case? How do these technologies allow them to better fight crime? Provide several examples.

2. How are the data-related issues faced by law enforce- ment similar to those that could be found in companies? How are they different? Where do these problems come from? Explain.

3. Imagine that you had access to the same crime-related information as that managed by police departments. How would you analyze this information, and what actions would you take as a result?

1. The case discusses many issues related to data quality, sharing, and accessibility that both government bodies and for-profit organizations face. Go online and re- search how these issues manifest themselves in compa- nies, and some of the approaches used to manage them. Would those apply to police departments? Prepare a report to share your findings.

2. The case discusses the large volume of very detailed in- formation collected daily by law enforcement agencies. Knowing this, how comfortable do you feel about the storing and sharing of that data? What policies would you put in place to assuage some of those concerns? Break into small groups with your classmates to discuss these issues and arrive at some recommendations.

REAL WORLD ACTIVITIES CASE STUDY QUESTIONS

180 ● Module II / Information Technologies

obr76817_ch05_177-216.indd Page 180 8/2/10 2:41 PM user-f498obr76817_ch05_177-216.indd Page 180 8/2/10 2:41 PM user-f498 /Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387/Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387

Chapter 5 / Data Resource Management ● 181

fields describing attributes such as the person’s name, Social Security number, and rate of pay. Fixed-length records contain a fixed number of fixed-length data fields. Variable- length records contain a variable number of fields and field lengths. Another way of looking at a record is that it represents a single instance of an entity. Each record in an employee file describes one specific employee. Normally, the first field in a record is used to store some type of unique identifier for the record. This unique identifier is called the primary key . The value of a pri- mary key can be anything that will serve to uniquely identify one instance of an entity, and distinguish it from another. For example, if we wanted to uniquely identify a sin- gle student from a group of related students, we could use a student ID number as a primary key. As long as no one shared the same student ID number, we would always be able to identify the record of that student. If no specific data can be found to serve as a primary key for a record, the database designer can simply assign a record a unique sequential number so that no two records will ever have the same primary key.

A group of related records is a data file (sometimes referred to as a table or flat file ). When it is independent of any other files related to it, a single table may be referred to as a flat file . As a point of accuracy, the term flat file may be defined either narrowly or more broadly. Strictly speaking, a flat file database should consist of nothing but data and de- limiters. More broadly, the term refers to any database that exists in a single file in the form of rows and columns, with no relationships or links between records and fields except the table structure. Regardless of the name used, any grouping of related records in tabular (row-and-column form) is called a file . Thus, an employee file would contain the records of the employees of a firm. Files are frequently classified by the application for which they are primarily used, such as a payroll file or an inventory file, or the type of data they contain, such as a document file or a graphical image file . Files are also classified by their permanence, for example, a payroll master file versus a payroll weekly transaction file . A transaction file, therefore, would contain records of all transactions occurring dur- ing a period and might be used periodically to update the permanent records contained in a master file. A history file is an obsolete transaction or master file retained for backup purposes or for long-term historical storage, called archival storage .

A database is an integrated collection of logically related data elements. A database consolidates records previously stored in separate files into a common pool of data

File

Database

Employee Record 1

Employee Record 2

Employee Record 3

Employee Record 4

Human Resource Database

Payroll File Benefits File

Insurance Field

50,000

SS No. Field

617-87-7915

Name Field

Porter M.L.

Name Field

Jones T. A.

SS No. Field

275-32-3874

Salary Field

20,000

Name Field

Klugman J. L.

SS No. Field

349-88-7913

Salary Field

28,000

Name Field

Alvarez J.S.

SS No. Field

542-40-3718

Insurance Field

100,000

F IGURE 5.2 Examples of the logical data elements in information systems. Note especially the examples of how data fields, records, files, and databases relate.

obr76817_ch05_177-216.indd Page 181 8/2/10 2:41 PM user-f498obr76817_ch05_177-216.indd Page 181 8/2/10 2:41 PM user-f498 /Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387/Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387

182 ● Module II / Information Technologies

elements that provides data for many applications. The data stored in a database are independent of the application programs using them and of the type of storage devices on which they are stored. Thus, databases contain data elements describing entities and relationships among entities. For example, Figure 5.3 outlines some of the entities and relationships in a database for an electric utility. Also shown are some of the business applications (bill- ing, payment processing) that depend on access to the data elements in the database. As stated in the beginning of the chapter, just about all the data we use are stored in some type of database. A database doesn’t need to look complex or technical to be a database; it just needs to provide a logical organization method and easy access to the data stored in it. You probably use one or two rapidly growing databases just about every day: How about Facebook, MySpace, or YouTube? All of the pictures, videos, songs, messages, chats, icons, e-mail addresses, and eve- rything else stored on each of these popular social networking Web sites are stored as fields, records, files, or objects in large databases. The data are stored in such a way to ensure that there is easy access to it, it can be shared by its respective owners, and it can be protected from unauthorized access or use. When you stop to think about how simple it is to use and enjoy these databases, it is easy to forget how large and complex they are. For example, in July 2006, YouTube reported that viewers watched more than 100 mil- lion videos every day, with 2.5 billion videos in June 2006 alone. In May 2006, users added 50,000 videos per day, and this increased to 65,000 videos by July. In January 2008 alone, almost 79 million users watched more than 3 billion videos on YouTube. In August 2006, The Wall Street Journal published an article revealing that YouTube was hosting about 6.1 million videos (requiring about 45 terabytes of storage space), and had about 500 accounts. As of March 2008, a YouTube search turned up about 77.3 million videos and 2.89 million user channels. Perhaps an even more compelling example of ease of access versus complexity is found in the popular social networking Web site Facebook. Some of the basic statistics are nothing short of amazing! Facebook reports more than 200 million users with more than 100 million logging in at least once each day. The average user has 120 friend relationships established. More than 850 million photos, 8 million videos, 1 bil- lion pieces of content, and 2.5 million events are uploaded or created each month. More than 40 language translations are currently available on the site, with more than 50 more in development. More than 52,000 software applications exist in the Face- book Application Directory, and more than 30 million active users access Facebook through their mobile devices. The size of their databases is best measured in petabytes,

F IGURE 5.3 Some of the entities and relationships in a simplified electric utility database. Note a few of the business applications that access the data in the database.

Billing

Meter reading

Payment processing

Service start / stop

Entities:

Customers, meters, bills, payments, meter readings

Relationships:

Bills sent to customers, customers make payments, customers use meters, . . .

Electric Utility Database

Source: Adapted from Michael V. Mannino, Database Application Development and Design (Burr Ridge, IL: McGraw-Hill/Irwin, 2001), p. 6.

obr76817_ch05_177-216.indd Page 182 8/2/10 2:41 PM user-f498obr76817_ch05_177-216.indd Page 182 8/2/10 2:41 PM user-f498 /Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387/Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387

Chapter 5 / Data Resource Management ● 183

which is equal to one quadrillion bytes. All of this from a database and a simple access method launched in 2004 from a dorm room at Harvard University. The important point here is that all of these videos, user accounts, and information are easily accessed because the data are stored in a database system that organizes it so that a particular item can be found on demand.

The relationships among the many individual data elements stored in databases are based on one of several logical data structures, or models. Database management system (DBMS) packages are designed to use a specific data structure to provide end users with quick, easy access to information stored in databases. Five fundamental database struc- tures are the hierarchical, network, relational, object-oriented, and multidimensional models. Simplified illustrations of the first three database structures are shown in Figure 5.4 .

Database Structures

Source: Adapted from Michael V. Mannino, Database Application Development and Design (Burr Ridge, IL: McGraw-Hill/Irwin, 2001), p. 6.

F IGURE 5.4 Example of three fundamental database structures. They represent three basic ways to develop and express the relationships among the data elements in a database.

Project A Data Element

Department Data Element

Project B Data Element

Employee 1 Data Element

Employee 2 Data Element

Hierarchical Structure

Department A

Project A

Department Table

Deptno Dept A Dept B Dept C

Employee Table

Empno Emp 1 Emp 2 Emp 3 Emp 4 Emp 5 Emp 6

Deptno Dept A Dept A Dept B Dept B Dept C Dept B

Dname Dloc Dmgr Ename Etitle Esalary

Network Structure

Relational Structure

Employee 1

Department B

Project B

Employee 3

Employee 2

obr76817_ch05_177-216.indd Page 183 8/2/10 2:41 PM user-f498obr76817_ch05_177-216.indd Page 183 8/2/10 2:41 PM user-f498 /Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387/Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387

184 ● Module II / Information Technologies

Early mainframe DBMS packages used the hierarchical structure , in which the relation- ships between records form a hierarchy or treelike structure. In the traditional hierarchi- cal model, all records are dependent and arranged in multilevel structures, consisting of one root record and any number of subordinate levels. Thus, all of the relationships among records are one-to-many because each data element is related to only one element above it. The data element or record at the highest level of the hierarchy (the depart- ment data element in this illustration) is called the root element. Any data element can be accessed by moving progressively downward from a root and along the branches of the tree until the desired record (e.g., the employee data element) is located.

The network structure can represent more complex logical relationships and is still used by some mainframe DBMS packages. It allows many-to-many relationships among records; that is, the network model can access a data element by following one of several paths because any data element or record can be related to any number of other data elements. For example, in Figure 5.4 , departmental records can be related to more than one employee record, and employee records can be related to more than one project record. Thus, you could locate all employee records for a particular de- partment or all project records related to a particular employee. It should be noted that neither the hierarchical nor the network data structures are commonly found in the modern organization. The next data structure we discuss, the relational data structure, is the most common of all and serves as the foundation for most modern databases in organizations.

The relational model is the most widely used of the three database structures. It is used by most microcomputer DBMS packages, as well as by most midrange and mainframe sys- tems. In the relational model, all data elements within the database are viewed as being stored in the form of simple two-dimensional tables , sometimes referred to as relations . The tables in a relational database are flat files that have rows and columns. Each row rep- resents a single record in the file, and each column represents a field. The major difference between a flat file and a database is that a flat file can only have data attributes specified for one file. In contrast, a database can specify data attributes for multiple files simultaneously and can relate the various data elements in one file to those in one or more other files. Figure 5.4 illustrates the relational database model with two tables representing some of the relationships among departmental and employee records. Other tables, or relations, for this organization’s database might represent the data element relationships among projects, divisions, product lines, and so on. Database management system pack- ages based on the relational model can link data elements from various tables to provide information to users. For example, a manager might want to retrieve and display an employee’s name and salary from the employee table in Figure 5.4 , as well as the name of the employee’s department from the department table, by using their common de- partment number field (Deptno) to link or join the two tables. See Figure 5.5 . The rela- tional model can relate data in any one file with data in another file if both files share a common data element or field. Because of this, information can be created by retrieving data from multiple files even if they are not all stored in the same physical location.

Hierarchical Structure

Network Structure

Relational Structure

F IGURE 5.5 Joining the employee and department tables in a relational database enables you to access data selectively in both tables at the same time.

Department Table

Deptno Dept A Dept B Dept C

Employee Table

Empno Emp 1 Emp 2 Emp 3 Emp 4 Emp 5 Emp 6

Deptno Dept A Dept A Dept B Dept B Dept C Dept B

Dname Dloc Dmgr Ename Etitle Esalary

obr76817_ch05_177-216.indd Page 184 8/2/10 2:41 PM user-f498obr76817_ch05_177-216.indd Page 184 8/2/10 2:41 PM user-f498 /Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387/Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387

Chapter 5 / Data Resource Management ● 185

Three basic operations can be performed on a relational database to create useful sets of data. The select operation is used to create a subset of records that meet a stated criterion. For example, a select operation might be used on an employee database to create a subset of records that contain all employees who make more than $30,000 per year and who have been with the company more than three years. Another way to think of the select operation is that it temporarily creates a table whose rows have records that meet the selection criteria. The join operation can be used to combine two or more tables temporarily so that a user can see relevant data in a form that looks like it is all in one big table. Using this operation, a user can ask for data to be retrieved from multiple files or databases with- out having to go to each one separately. Finally, the project operation is used to create a subset of the columns contained in the temporary tables created by the select and join operations. Just as the select operation creates a subset of records that meet stated criteria, the project operation creates a subset of the columns, or fields, that the user wants to see. Using a project operation, the user can decide not to view all of the columns in the table but instead view only those that have the data necessary to answer a particular question or construct a specific report. Because of the widespread use of relational models, an abundance of commercial products exist to create and manage them. Leading mainframe relational database ap- plications include Oracle 10g from Oracle Corp. and DB2 from IBM. A very popular midrange database application is SQL Server from Microsoft. The most commonly used database application for the PC is Microsoft Access.

The multidimensional model is a variation of the relational model that uses multidi- mensional structures to organize data and express the relationships between data. You can visualize multidimensional structures as cubes of data and cubes within cubes of data. Each side of the cube is considered a dimension of the data. Figure 5.6 is an ex- ample that shows that each dimension can represent a different category, such as prod- uct type, region, sales channel, and time. Each cell within a multidimensional structure contains aggregated data related to elements along each of its dimensions. For example, a single cell may contain the total sales for a product in a region for a specific sales channel in a single month. A major benefit of multidimensional databases is that they provide a compact and easy-to- understand way to visualize and manipulate data elements that have many interrelation- ships. So multidimensional databases have become the most popular database structure for the analytical databases that support online analytical processing (OLAP) applica- tions, in which fast answers to complex business queries are expected. We discuss OLAP applications in Chapter 10.

The object-oriented model is considered one of the key technologies of a new genera- tion of multimedia Web-based applications. As Figure 5.7 illustrates, an object con- sists of data values describing the attributes of an entity, plus the operations that can be performed upon the data. This encapsulation capability allows the object-oriented model to handle complex types of data (graphics, pictures, voice, and text) more easily than other database structures. The object-oriented model also supports inheritance ; that is, new objects can be automatically created by replicating some or all of the characteristics of one or more parent objects. Thus, in Figure 5.7 , the checking and savings account objects can inherit both the common attributes and operations of the parent bank account object. Such capabilities have made object-oriented database management systems (OODBMS) popular in computer-aided design (CAD) and a growing number of applications. For example, object technology allows designers to develop product designs, store them as objects in an object-oriented database, and replicate and modify them to create new product de- signs. In addition, multimedia Web-based applications for the Internet and corporate intranets and extranets have become a major application area for object technology.

Relational Operations

Multidimensional Structure

Object-Oriented Structure

obr76817_ch05_177-216.indd Page 185 8/3/10 5:43 PM user-f498obr76817_ch05_177-216.indd Page 185 8/3/10 5:43 PM user-f498 /Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387/Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387

186 ● Module II / Information Technologies

F IGURE 5.6 An example of the different dimensions of a multidimensional database.

Camera

TV

VCR

Audio

Camera

TV

VCR

Audio

February March

Sales

Margin

East

West

San Francisco

Los Angeles

Denver

Actual Budget Actual Budget

January

February

March

Qtr 1

January

February

March

Qtr 1

Actual West

TV

VCR

Sales

COGS

Margin

Total Expenses

Profit

East Budget Actual Budget

East

West

South

Total

East

West

South

Total

Actual Budget

TV

VCR

January

February

March

Qtr 1

April

Sales Margin Sales Margin

Actual

Budget

Forecast

Variance

Actual

Budget

Forecast

Variance

Sales Margin

East

West

January

February

March

Qtr 1

April

TV VCR TV VCR

F IGURE 5.7 The checking and savings account objects can inherit common attributes and operations from the bank account object.

Checking Account Object

Attributes

Customer Balance Interest

Operations

Deposit (amount) Withdraw (amount) Get owner

Attributes

Number of withdrawals Quarterly statement

Operations

Calculate interest paid Print quarterly statement

Attributes

Credit line Monthly statement

Operations

Calculate interest owed Print monthly statement

Savings Account Object

Bank Account Object

Inheritance Inheritance

Source: Adapted from Ivar Jacobsen, Maria Ericsson, and Ageneta Jacobsen, The Object Advantage: Business Process Reengineering with Object Technology (New York: ACM Press, 1995), p. 65. Copyright © 1995, Association for Computing Machinery. Used by permission.

obr76817_ch05_177-216.indd Page 186 8/2/10 2:41 PM user-f498obr76817_ch05_177-216.indd Page 186 8/2/10 2:41 PM user-f498 /Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387/Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387

Chapter 5 / Data Resource Management ● 187

Object technology proponents argue that an object-oriented DBMS can work with complex data types such as document and graphic images, video clips, audio segments, and other subsets of Web pages much more efficiently than relational database management systems. However, major relational DBMS vendors have countered by adding object- oriented modules to their relational software. Examples include multimedia object exten- sions to IBM’s DB2 and Oracle’s object-based “cartridges” for Oracle 10g. See Figure 5.8 .

The hierarchical data structure was a natural model for the databases used for the struc- tured, routine types of transaction processing characteristic of many business operations in the early years of data processing and computing. Data for these operations can easily be represented by groups of records in a hierarchical relationship. However, as time pro- gressed, there were many cases in which information was needed about records that did not have hierarchical relationships. For example, in some organizations, employees from more than one department can work on more than one project (refer to Figure 5.4 ). A network data structure could easily handle this many-to-many relationship, whereas a hierarchical model could not. As such, the more flexible network structure became popu- lar for these types of business operations. Like the hierarchical structure, the network model was unable to handle ad hoc requests for information easily because its relation- ships must be specified in advance, which pointed to the need for the relational model. Relational databases enable an end user to receive information easily in response to ad hoc requests. That’s because not all of the relationships among the data elements in a relationally organized database need to be specified when the database is created. Database management software (such as Oracle 11g, DB2, Access, and Approach) cre- ates new tables of data relationships by using parts of the data from several tables. Thus, relational databases are easier for programmers to work with and easier to maintain than the hierarchical and network models. The major limitation of the relational model is that relational database manage- ment systems cannot process large amounts of business transactions as quickly and efficiently as those based on the hierarchical and network models; they also cannot process complex, high-volume applications as well as the object-oriented model. This performance gap has narrowed with the development of advanced relational database software with object-oriented extensions. The use of database management software based on the object-oriented and multidimensional models is growing steadily, as these technologies are playing a greater role for OLAP and Web-based applications.

Evaluation of Database Structures

F IGURE 5.8 Databases can supply data to a wide variety of analysis packages, allowing for data to be displayed in graphical form.

Source : Courtesy of Microsoft®.

obr76817_ch05_177-216.indd Page 187 8/2/10 2:41 PM user-f498obr76817_ch05_177-216.indd Page 187 8/2/10 2:41 PM user-f498 /Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387/Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387

188 ● Module II / Information Technologies

Database Pioneer Rethinks the Best Way to Organize Data

Is there a better way to build a data warehouse? For years, relational databases, which organize data in tables composed of vertical columns and horizontal rows, have served as the foundation of data warehouses. Now database pioneer Michael Stone- braker is promoting a different way to organize them, promising much faster response times. As a scientist at the University of California at Berkeley in the 1970s, Stonebraker was one of the original architects of the Ingres relational database, which spawned several commercial variants. A row-based system like Ingres is great for executing transactions, but a column-oriented system is a more natural fit for data warehouses, Stonebraker now says. SQL Server, Sybase, and Teradata all have rows as their central design point. Yet in data warehousing, faster performance may be gained through a column layout. Stone- braker says all types of queries on “most data warehouses” will run up to 50 times faster in a column database. The bigger the data warehouse, the greater the performance gain. Why? Data warehouses frequently store transactional data, and each transaction has many parts. Columns cut across transactions and store an element of information that is standard to each transaction, such as customer name, address, or purchase amount. A row, by comparison, may hold 20–200 different elements of a transaction. A standard relational database would retrieve all the rows that reflect, say, sales for a month, load the data into system memory, and then find all sales records and gener- ate an average from them. The ability to focus on just the “sales” column leads to improved query performance. There is a second performance benefit in the column approach. Because columns contain similar information from each transaction, it’s possible to derive a compres- sion scheme for the data type and then apply it throughout the column. Rows cannot be compressed as easily because the nature of the data (e.g., name, zip code, and ac- count balance) varies from record to record. Each row would require a different compression scheme. Compressing data in columns makes for faster storage and retrieval and reduces the amount of disk required. “In every data warehouse I see, compression is a good thing,” Stonebraker says. “I expect the data warehouse market to become completely column-store based.”

Source: Adapted from Charles Babcock, “Database Pioneer Rethinks the Best Way to Organize Data,” Information- Week , February 23, 2008.

Database management packages like Microsoft Access or Lotus Approach allow end users to develop the databases they need easily. See Figure 5.9 . However, large or- ganizations usually place control of enterprisewide database development in the hands of database administrators (DBAs) and other database specialists. This delegation im- proves the integrity and security of organizational databases. Database developers use the data definition language (DDL) in database management systems like Oracle 11g or IBM’s DB2 to develop and specify the data contents, relationships, and structure of each database, as well as to modify these database specifications when necessary. Such information is cataloged and stored in a database of data definitions and specifications called a data dictionary , or metadata repository , which is managed by the database man- agement software and maintained by the DBA. A data dictionary is a database management catalog or directory containing metadata (i.e., data about data). A data dictionary relies on a specialized database soft- ware component to manage a database of data definitions, which is metadata about the structure, data elements, and other characteristics of an organization’s databases. For example, it contains the names and descriptions of all types of data records and their interrelationships; information outlining requirements for end users’ access and use of application programs; and database maintenance and security.

Database Development

obr76817_ch05_177-216.indd Page 188 8/2/10 2:41 PM user-f498obr76817_ch05_177-216.indd Page 188 8/2/10 2:41 PM user-f498 /Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387/Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387

Chapter 5 / Data Resource Management ● 189

The database administrator can query data dictionaries to report the status of any aspect of a firm’s metadata. The administrator can then make changes to the defini- tions of selected data elements. Some active (versus passive ) data dictionaries automati- cally enforce standard data element definitions whenever end users and application programs access an organization’s databases. For example, an active data dictionary would not allow a data entry program to use a nonstandard definition of a customer record, nor would it allow an employee to enter a name of a customer that exceeded the defined size of that data element. Developing a large database of complex data types can be a complicated task. Database administrators and database design analysts work with end users and systems analysts to model business processes and the data they require. Then they determine (1) what data definitions should be included in the database and (2) what structures or relationships should exist among the data elements.

As Figure 5.10 illustrates, database development may start with a top-down data planning process . Database administrators and designers work with corporate and end-user management to develop an enterprise model that defines the basic business process of the enterprise. They then define the information needs of end users in a business process, such as the purchasing/receiving process that all businesses have. Next, end users must identify the key data elements that are needed to perform their specific business activities. This step frequently involves developing entity relationship diagrams (ERDs) that model the relationships among the many entities involved in business processes. For example, Figure 5.11 illustrates some of the rela- tionships in a purchasing/receiving process. The ERDs are simply graphical models of the various files and their relationships, contained within a database system. End users and database designers could use database management or business modeling software to help them develop ERD models for the purchasing/receiving process. This would help identify the supplier and product data that are required to automate their pur- chasing/receiving and other business processes using enterprise resource management (ERM) or supply chain management (SCM) software. You will learn about ERDs and other data modeling tools in much greater detail if you ever take a course in systems analysis and design.

Data Planning and Database Design

F IGURE 5.9 Creating a database table using the Table Wizard of Microsoft Access.

Source : Courtesy of Microsoft®.

obr76817_ch05_177-216.indd Page 189 8/2/10 2:41 PM user-f498obr76817_ch05_177-216.indd Page 189 8/2/10 2:41 PM user-f498 /Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387/Volumes/203/MHSF225/foe94488_disk1of1/0073385387/foe85387

190 ● Module II / Information Technologies

Such user views are a major part of a data modeling process, during which the re- lationships among data elements are identified. Each data model defines the logical relationships among the data elements needed to support a basic business process. For example, can a supplier provide more than one type of product to us? Can a customer have more than one type of account with us? Can an employee have several pay rates or be assigned to several project workgroups? Answering such questions will identify data relationships that must be represented in a data model that supports business processes of an organization. These data mod- els then serve as logical design frameworks (called schema and subschema ). These frame- works determine the physical design of databases and the development of application programs to support the business processes of the organization. A schema is an overall logical view of the relationships among the data elements in a database, whereas the

F IGURE 5.10 Database development involves data planning and database design activities. Data models that support business processes are used to develop databases that meet the information needs of users.