Discussion

Chapter 3: Database, Data Warehouse and Data Mining

Contributors: John Richardson, Mary Hoferek, Kim Wandersee, Tanis Stewart This chapter emphasizes management of the data resources of computer-using organizations. It also reviews key database management concepts and applications in business information systems. 

 

Introduction

An articulate friend once tried to describe my job as a Database Administrator (DBA) to my mother. After several unsuccessful attempts, the friend finally used an analogy. She told my mother to imagine going into the Library of Congress and thinking of all the books in the library. Then imagine the card catalog where you could search for each book by author or title. When you found the location of the book, you could give the information to a clerk who then got the book for you. When you were done with the book, you gave the book back and it was returned to its correct shelf. My mother had been to the Library of Congress and could envision the processing of the book. My mother could also visualize stack after stack filled with books.

Now, the friend said, imagine that all of the books, the whole card catalog, and all of the clerks are inside a box about the size of a desk. None of the books can be lost. All of the books have to in the right place. All of the clerks have to handle the books right and the users cannot wait long. Thousands of users come into the box for their books and thousands return the books every second. Your daughter's job as the database administrator is to be sure that nothing goes wrong. My mother understood at a high level what a database does through the library analogy.

Essentially, databases receive information, store it, and allow users to retrieve it. It sounds easy, but the software components of today's databases are highly complex. To take full advantage of the database requires careful planning and design. The design step of large databases can take a long time because many people may have information they want to be part of the database. The information may be related to other information. The requirements of the information and relationships need to be defined as the design effort proceeds.

Relational Databases

Most databases in use today are relational. The relational databases are the third generation in the evolution of our technology. The first generation was called hierarchical because it had "parent-child" relationships between the tables. The hierarchical databases were fast, but fairly inflexible, so it was hard to represent some of the information people wanted to store. The networked databases were the second generation and were more flexible because the "child" data could have more than one parent. The relational databases are even more flexible than their predecessors so it is easier to represent more information; for example, relational databases allowed us to show a relationship between many students and their several teachers as a many-to-many relationship.

Most database engines are really sophisticated software products. They are usually written in C or Java. In addition to their basic functions, they control more complex functions like locking, multi-user operations, optimizing query performance, backup and recovery, and so forth. Working with these sophisticated operations can be very challenging technically.

Queries

A database is useful only if it can organize the data to provide meaningful answers to queries put to it. Queries many take many form, all of which cannot be foreseen in advance, so that the database design must be flexible enough to accommodate such queries and the query method must be similarly flexible.

With standards and well-defined databases, managers can use query languages like SQL (Structured Query Language) or QBE (Query By Example) to ask questions and retrieve data in any manner. Current database systems help users build complex reports and applications simply by placing details on the screen with a mouse.   In the 1980s, James L. Flanagan at Bell Laboratories conducted early research on automatic speech recognition. Processing power and storage then was hardly adequate to the task, but as these capabilities have grown, voice recognition has become commonplace. It is highly popular for simple queries to databases, with many commercial applications from airline reservations to on-line purchasing to transcription of your doctor's recorded medical notes about your case.

Object Orientation

The newer relational databases are trying to incorporate some of the object-oriented concepts and allow us to represent more information. For example, some databases are now called object-relational and allow us to store information like whole documents, audio, and video. This is an exciting development in the database technology and is an area where a lot of development work is being done. Database vendors like Oracle, Sybase, Microsoft, and IBM know that databases will have to meet the storage, search, and retrieval needs of the Internet to support users and, therefore, are rushing to incorporate Internet applications into their products.   

Object-oriented database management systems did not become a major player as expected.  OO databases remain niche players for areas like CAD (Computer-aided design) and telecommunications because of its capability to handle multi-media data.  Unfortunately, it did not scale up to high transaction volumes as well as relational databases.  Relational databases also incorporated OO features that handle video, audio, text, or other types of media.  For this and other reasons, OO databases did not become a major player in the database market. .   Search Engines

Search engines basically consist of five main items: the web crawler, the database together with its management system, the indexer, the query engine, and the page ranker.

The web crawler uses the Internet to find web sites and the web site's metadata (data about the web site's data). It saves this information to the database. The process of the web crawler starts with the queue. The queue is a list of URLs for the web crawler to search and download. The database adds old URLs to the queue for verification and updating. New URLs are also added to the queue when the web crawler recognizes a hyperlink that is not currently in the database.

The database management system counts the keywords in the HTML meta tag or the frequency of all of the words in a document for use in prioritizing the web site's relevancy to a given subject. Thus it simply keeps a record of important words and the location on the World Wide Web of the documents that contain them. Obviously it would be both impossible and unnecessary for the database to store the entire Web.

The indexer normally creates a forward or an inverted index of the database. A forward index contains at least a column of documents and a corresponding column of important words in each document. An inverted index contains at least a column of words and a corresponding column of all documents that contain each word.

Users go to the search engine's portal and submit keywords to search. The portal then gives the submission to the database's query engine and is translated into a query the database will understand. The query engine queries the database and its index.

Then the page ranker determines the relevancy of the web pages in the database and returns the results to the portal for the user. One way to estimate relevancy is by the number of times the keywords are in a document. In the mid-1990s, Sergey Brin and Lawrence Page (Google founders) created a new type of relevancy ranking system called PageRank. PageRank basically ranks the relevancy of web page based on the number of times other web sites link to it. Another, non-objective, way of ranking relevancy is that companies pay the search engine company to rank their web site higher than others.

Brin and Page wrote the seminal paper on search engines, " The Anatomy of a Large-Scale Hypertextual Web Search Engine ," while they were at Stanford University.

Today's search engine companies often use the business model of advertising companies. Google receives revenue by returning a client's web site if the search results relate to the client's products or services. If the searcher clicks on the client's link, Google charges the client. Some search engines like Yahoo also sell banner space or use pop-up advertising. Other revenue comes from leasing their technology to enterprises and selling analytical statistics from their database operation.

The prospect of losing out to Google on search engine popularity and its advertising revenues drove Microsoft's unsuccessful attempt to acquire Yahoo by means of a $46 billion hostile takeover bid.   Examples of Databases

Databases are the basis for many of the capabilities that are part of our society. When you use an ATM machine to get money, register to take this class, jump on the Internet, buy books, go to the doctor, get tickets for the Washington Redskins, pay with your credit card, or work out at the gym, you have probably used a database. So many people are using databases that we now separate transaction databases from data warehouses. The transaction databases perform their functions, like transferring payments, quickly. A data warehouse is a different kind of database. It collects and organizes transaction data and other data for subsequent query. It usually handles long queries that analyze a lot of data to support decision making. For example, sales trends is a typical data warehouse operation that is used to decide what products the business should continue.

Given a data warehouse, it is productive to seek non-obvious relationships among the data by the process of data mining. For example, telephone calling patterns can be assembled to identify communities of individuals who might have criminal or terrorist intent.

Many databases are becoming extremely large. Five or six hundred tables are not unusual in some companies and agencies. Such databases have easily grown beyond our original estimates of terabytes. Managing a database in the petabyte (peta = 1015) range is a great technical challenge.

An example of a large database is the IRIS (Incorporated Research Institutions for Seismology) Data Management System. It provides an interactive educational display of global seismically that allows you to monitor earthquake locations in near real-time, view records of ground motion, and visit seismic stations around the world. Current output from this database can be found at Seismic Monitor (http://www.iris.edu/seismon). Notice that with the IRIS database, you can drill down to get more detailed information. The IRIS Data Management Center now generates maps and text files for certain time periods on a daily basis. Below is a map for events as of January 13, 2007, showing a magnitude 8 earthquake off the coast of Japan. That day's events are in bright red. The prior two weeks' events are in yellow. The past five years' events are in purple.   Like many databases that incorporate images, the IRIS database stores the actual images in flat files outside the database engine and stores pointers to the image files. Nonetheless, the tables inside the engine store 25 gigabytes of information. A significant portion of the IRIS database also stores information about earthquakes. There are lists of earthquake locations, times, and magnitudes. The IRIS database also keeps track of waveform files (seismograms). The waveform data takes up approximately 22 terabytes of space on tapes. To manage that amount of data, a robot retriever is used and may handle up to 6000 tapes.

 

At the National Library of Medicine, there is a huge database of computerized tomography (CT) scans of the human body. To see a fascinating animation of these sections, proceeding from head to toes, click on Visible Human Project® Gallery (at http://www.nlm.nih.gov/research/visible/visible_gallery.html), and then click on "animated trip through the Visible Human male cryosections."

Challenges in Database Technology

With these large databases, like a data warehouse, one of the toughest problem areas is database performance. The more data that is stored in databases, the harder it is to retrieve information. An exciting area of technology is in a field called optimization. The optimizer in a database engine figures out the fastest and most efficient way to extract data and present it to a user.

New data structures are also being devised to meet the performance challenges. For example, the B-Tree index has served in industry well in the past, but is being modified to meet the new needs. Structures like bit-mapped indexes are replacing the standard B-Tree in warehouse technology. Other structures will need to be developed.

Another challenging area is the type of data that users want to store in databases. Our early relational databases could only store text and numbers. Now users want to store large documents, images, and video inside databases. In the IRIS example, only pointers were stored in the database and the images were in flat files outside the engine. One of the technical challenges in the future is to bring the image under the database engine control.

Google's total database consists of more than 200 petabytes, so large that it must be distributed over about two dozen data centers around the globe. Some 450,000 servers are needed to handle 100 million queries per day to Google. Such massive databases present challenges in operating software, input-output bit rate, replication of data against loss, and power consumption.

Security and Privacy

Database work is highly responsible employment because corporations and agencies view the information stored in databases as corporate assets. Think about it. If a corporation lost its databases, it might lose information like customer accounts, sales and inventory control information, payroll, and so forth. Many companies literally could not afford to lose information that is stored in databases. They could not perform their fundamental functions and would go bankrupt. Accordingly, backup and recovery take on high priority in database management.

In addition, much of the information that is stored in databases is sensitive. For example, financial information about someone's mortgage or credit rating is considered private. Health history information could affect someone's ability to obtain insurance or a mortgage or a job. Accordingly, the Health Insurance Portability and Accountability Act (HIPAA) requires strict security and privacy of health data. Some databases are vital to national defense. People who work with database information may be required to enter into a contract not to divulge the contents of the stored data. There is a legal obligation, therefore, for employees to protect the information. And the databases must be made secure from hackers and inadvertent disclosure of information affecting individual privacy. Database Technologies to Watch

Big Data

Data that an organization has access to have expanded beyond the structured data consisting of known alpha-numeric data to data that is unstructured. Structure data is typically generated by enterprise applications and stored in relational databases. It is usually straightforward to use. Unstructured data presents itself non-alpha-numeric data formats such as audio, photos, video and freeform data formats such as clickstreams and logs from websites, XML documents, and freeform blocks of text such as email messages, Tweets, and product reviews. Many organizations are acquiring information that has more data types, generated by more sources, retained for longer periods, and utilized by more types of applications. This occurrence is currently known as “Big Data”. The growth of data requires organizations to know how they will manage their information and to have a plan in place for ensuring the data an organization can own can be turned into knowledge and wisdom an organization can use. No organization has earned a penny for storing their data but has outperformed their competitors by making sense of what their data means. Data veracity is a term that refers to the quality or trustworthiness of the data. With the advent of Big Data, organizations information management strategies must determine how to transform the data into trustworthy insights and discard noise (data that is not of value).

Comparing the size of the databases can demonstrate the new challenges organizations are now facing in managing and using the data they own.

Database Size		Common Characteristics
1 gigabyte		· Information generated by traditional enterprise applications · Typically consists of transactional data, stored in relational databases · Uses Structured Query Language (SQL) as the access method
1 terabyte		· Standard size for data warehouses · Often aggregated from multiple databases in the 1 – 100 gigabyte range · Drives enterprise analytics and business intelligence
1 petabyte		· Frequently populated by mass data collection-often automated · Regularly contains unstructured information · Services as a catalyst for exploring new Big Data-related technologies

Open Source Databases There are a handful of open source companies, such as MySQL, PostgreSQL and Ingres, are building businesses around the edges of the database industry. Typically, these products offer basic database capabilities at significantly lower costs (sometimes the licensing costs are free). Because of the open nature of the products, some claimed that open source databases are more secure than the proprietary ones. There are some concerns about the technical support and the documentation provided by open source companies. One must also consider the total cost of ownership in that an open source database maybe more expensive overall when considering the cost of implementation and maintenance.

XML Databases One of the challenges in database technology is capturing something called unstructured data.  These do not fit in a standard data model (e.g., name, address, zip code, etc.). Examples include books, documents, audio, video, e-mail, or a web page. Merrill Lynch estimates that 80 percent of all information is in this form. Standard database management systems do not manage this type of data very well. An alternative is the use of tagging. A tag is a label which serves as a type of metadata that helps describe an item and allow it to be located by browsing or search. A set of rules for managing these tags is called Extensible Markup Language (XML) that allows the data to be machine readable. Here are examples of XML tags used for defining data: <exam>

<question>

What is the meaning of life?

</question>

<response>

A breakfast cereal.

</response>

</exam> A new type of data technology is an XML database.  This approach allows data to be stored in XML so they could be later manipulated, queried or exported. One example of a commercial XML database system is the Tamino XML Server . This product supports the native XML format.  This compares with traditional relational database management systems (such as DB2) with XML aware columns which supports the tagging schema. Conclusions Databases are critical elements of corporate computing that provides for storage, retrieval and analysis of data.  They allow for organizations to perform important functions ranging from managing their financial accounts to tracking equipment inventory in the office environment. The database industry is dominated by relational database management systems which include Oracle, IBM, and Microsoft.  It is important for managers to understand its strengths and weaknesses in order to effectively utilize these key IT tools.