TOPIC FIVE: FOUNDATIONS OF BUSINESS INTELLIGENCE: DATABASES AND

INFORMATION MANAGEMENT
Learning Objectives
1. Define a database and explain how a relational database organizes data.
2. Describe the principles of a database management system.
3. Identify the principal tools and technologies for accessing information from databases to
improve business performance and decision making.
4. Explain why data governance and data quality assurance are essential for managing a
firm’s data resources.
5.1 The Database Approach to Data Management
All databases are built essentially the same. A bit is the smallest unit of data. Combined bits
create bytes, a single character. A group of bytes creates a field. A group of fields creates a
record. A group of records creates a file. A group of files creates a database.
Database is a collection of related files containing records on people, places, or things. Prior to
digital databases, business used file cabinets with paper files.
Entity is a generalized category representing person, place, thing on which we store and
maintain information E.g., SUPPLIER, PART
Attributes are specific characteristics of each entity. Examples are:
 SUPPLIER name, address
 PART description, unit price, supplier

1
 Figure 5.1 The Data Hierarchy

A computer system organizes data in a hierarchy that starts with the bit, which represents either a
0 or a 1. Bits can be grouped to form a byte to represent one character, number, or symbol. Bytes
can be grouped to form a field, and related fields can be grouped to form a record. Related
records can be collected to form a file, and related files can be organized into a database.

5.2 Organizing Data in a Relational Database

A relational database stores data in tables. The data are then extracted and combined into
whatever form or format the user needs. The tables are sometimes called files, although that is
actually a misnomer, since you can have multiple tables in one file.
Data in each table are broken down into fields. A field, or column, contains a single attribute for
an entity. A group of fields is stored in a record or tuple (the technical term for record). Figure
5.1 shows the composition of a relational database table.

2
Figure 5.2 A Relational Database Table

A relational database organizes data in the form of two-dimensional tables. Illustrated here is a
table for the entity SUPPLIER showing how it represents the entity and its attributes. Supplier
Number is the key field.
Each record requires a key field, or unique identifier. The best example of this is your student
registration number – there is only one per person. That explains in part why your educational
institution would ask you for your student registration number for identification purposes.
In a relational database, each table contains a primary key, a unique identifier for each record.
To make sure the tables relate to each other, the primary key from one table is stored in a related
table as a foreign key. For instance, in the customer table below the primary key is the unique
customer I D. That primary key is then stored in the order table as the foreign key so that the two
tables have a direct relationship.

3
Figure 6.3 The PART Table

Data for the entity PART have their own separate table. Part_Number is the primary key and
Supplier_Number is the foreign key, enabling users to find related information from the
SUPPLIER table about the supplier for each part.
5.3 Establishing Relationships
Determine the relationships between each data element that you currently have using an entity-
relationship diagram. The data don’t necessarily have to be in a computer for you to consider
the impact. Determine which data elements work best together and how you will organize them
in tables. Break your groups of data into as small units as possible. Even when you think each is
as small as it can get, go back through it again just to make sure. Decide what the key identifier
will be for each record.
There are three possible types of entity relationships:
1. One-to-one: Each record in one table has only one related record in another table. For
example, each person has a single date of birth

4
 2. One-to-many: Each record in one table can relate to multiple records in other tables. For
example, one supplier can supply many parts and many parts can be supplied by a single
supplier.
3. Many-to-many: Multiple records in one table can relate to multiple records in other
tables. For example students take many courses.
If you use a many-to-many relationship you’ll need to create a join table or intersection relation
to bring the tables together in an orderly fashion. That is, you’ll take a single field from one
table, a single field from another table, and create a third table that joins the first two fields
together.
Figure 5.4 A Simple Entity-Relationship Diagram

This diagram shows the relationship between the entities SUPPLIER and PART.
We mentioned before that you want to create the smallest fields possible. You also want to avoid
redundancy among tables. You do not want to have two tables storing a customer’s name. That
makes it more difficult to keep data properly organized and updated. What would happen if you
changed the customer’s name in one table and forgot to change it in the second table?
Minimizing redundancy and increasing the stability and flexibility of databases is called
normalization.
Your goals for creating a good data model are:
 Including all entities and the relationships among them
 Organizing data to minimize redundancy
 Maximizing data accuracy
 Making data easily accessible

5
Whichever relationship type you use, you need to make sure the relationship remains consistent
by enforcing referential integrity. That is, if you create a table that points to another table, you
must add corresponding records to both tables.
Figure 5.5 Sample Order Report

Sample Order Report

The shaded areas show which data came from the SUPPLIER, LINE_ITEM, and ORDER tables.
The database does not maintain data on Extended Price or Order Total because they can be
derived from other data in the tables.
Figure 5.6 Final Database design with sample records

6
The final design of the database for suppliers, parts, and orders has four tables. The LINE_ITEM
table is a join table that eliminates the many-to-many relationship between ORDER and PART.
Figure 5.7 Entity-Relationship Diagram for the Database with Four Tables

This diagram shows the relationship between the SUPPLIER, PART, LINE_ITEM, and ORDER
entities.
This graphic shows an example of an entity-relationship diagram. Reading from right to left, it
shows that one order can include many line Items. Each line item contains only one part. Each
part is supplied by one supplier. Reading from left to right, it shows that one supplier provides
many parts, each part can be ordered in many line items, and each line item belongs to one order.

5.5 Database Management Systems (DBMS)

A database management system (DBMS) is basically a software program such as Microsoft
Access for desktop computing or Oracle Database built for larger computing systems. The
software permits an organization to centralize data, manage them efficiently, and provide easy
access to the stored data by authorized users.
Physical views of data are often different from the logical views of the same data. Physical
views describe how the data are actually arranged on storage media and are important to
programmers who must manipulate the data as they are physically stored in the database.
Logical views describe how end users see and use the data. For instance, assume you store
tablets of paper in your lower-right desk drawer. You store your pencils in the upper-left drawer.
When it comes time to write your request for a pay raise, you pull out the paper and pencil and
put them together on your desktop. It isn’t important to the task at hand where the items were
stored physically; you are concerned with the logical idea of the two items coming together to
help you accomplish the task.
A single human resources database provides many different views of data, depending on the
information requirements of the user. Illustrated here are two possible views, one of interest to a
benefits specialist and one of interest to a member of the company’s payroll department

7
Figure 5.8: Human Resource Database

A single human resources database provides many views of data, depending on the information
requirements of the user. Illustrated here are two possible views, one of interest to a benefits
specialist and one of interest to a member of the company’s payroll department.
This graphic illustrates what is meant by providing different logical views of data. The orange
rectangles represent two different views in an HR database, one for reviewing employee benefits,
the other for accessing payroll records. The students can think of the cylinder as the physical
view, which shows how the data are actually organized and stored on the physical media. The
physical data do not change, but a DBMS can create many different logical views to suit
different needs of users.

5.6 Operations of a Relational DBMS

Use these three basic operations to extract data from a relational database:
 Select: create a subset of records meeting the stated criteria
 Join: combine related tables to provide more information than individual tables
 Project: create a new table from subsets of previous tables
8
Figure 5.9 The Three Basic Operations of a Relational DBMS

The select, join, and project operations enable data from two tables to be combined and only
selected attributes to be displayed.
This graphic illustrates the result from combining the Select, Join, and Project operations to
create a subset of data. The Select operation retrieves just those records in the PART table whose
part number is 137 or 150. The Join operation uses the foreign key of the Supplier underscore
Number provided by the PART table to locate supplier data from the SUPPLIER table for just
those records selected in the Select operation. Finally, the Project operation limits the columns to
be shown to be simply the part number, part name, supplier number, and supplier name (orange
rectangle).
Figure 5.10 Example of a SQL Query

Illustrated here are the SQL statements for a query to select suppliers for parts 137 or 150. They
produce a list with the same results as Figure 5.9.

9
This graphic shows an example SQL statement that would be used to retrieve data from a
database. In this case, the SQL statement is retrieving records from the PART table (Figure 5.9)
whose Part Number is either 137 or 150.

5.6.1 Capabilities of Database Management Systems

A DBMS has three components, all of them important for the long-term success of a system.
Data definition capability. Marketing looks at customer addresses differently from Shipping, so
you must make sure that all database users are speaking the same language. Think of it this way:
marketing is speaking French, production is speaking German, and human resources is speaking
Japanese. They are all saying the same thing, but it’s very difficult for them to understand each
other. Creating the data definition language itself sometimes gets shortchanged. The
programmers who are creating the language sometimes say “Hey, an address is an address, so
what.” That’s when it becomes critical to involve users in the development of the data definition
language.
Data dictionary. Each data element or field should be carefully analyzed when the database is
first built or as elements are later added. Determine what each element will be used for, who will
be the primary user, and how it fits into the overall scheme of things. Then write it all down and
make it easily available to all users. This is one of the most important steps in creating a good
database.
Users and programmers should consult the data dictionary to determine what data elements are
available before they create new ones that are the same or similar to those already in the data
dictionary. This can eliminate data redundancy and inconsistency.

5.7 Querying and Reporting

A data manipulation language is a formal language used to add, delete, change, and extract
data in a database. Data manipulation languages are getting easier to use and more prevalent.
SQL (Structured Query Language) is the most prominent language and is now embedded in
desktop applications such as Microsoft Access.
In addition to SQL, database management systems include report generators. These tools help
users create more structured data displays than simple queries. For instance, if you run a query to

10
determine how many customers on your paper route pay on time, you’ll find out that 15 percent
of them do. A report generator helps you display the results in context to the query you ran.
The biggest problem with databases is the misconception that every data element should be
stored in the same table. In fact, each data element should be analyzed in relation to other data
elements, with the goal of making the tables as small in size as possible. The ideal relational
database will have many small tables, not one big one. On the surface that may seem like extra
work and effort, but by keeping the tables small, they can serve a wider audience because they
are more flexible. This setup is especially helpful in reducing redundancy and increasing the
usefulness of data.
5.8 Non-Relational Databases and Databases in the Cloud
Relational databases will serve your company well if all your data can be neatly tucked into rows
and columns. Unfortunately, much of the data a business wants to access aren’t structured like
that. Data are now stored in text messages, social media postings, maps, and the like. Non-
relational database management systems are better at managing large data set on distributed
computing networks. They can easily be scaled up or down depending on the particular needs of
your business at a particular time.
5.9 Cloud Databases, Distributed Databases and Block Chain
Cloud computing service companies provide a way for you to manage your company’s data
through Internet access using a web browser. At the present time, you may not be able to create a
sophisticated relational database management system but it won’t be long before it’s a standard
service for organizations of all sizes. Pricing for cloud-based database services are predicated
upon:
 Usage – small databases cost less than larger ones
 Volume of data stored
 Number of input-output requests
 Amount of data written to the database
 Amount of data read from the database
Small- and medium-sized businesses can benefit from using cloud-based databases by not having
to maintain the information technology infrastructure needed to establish a local database. Large
businesses can benefit from the services by using it as an adjunct to their onsite database and
moving peak usage to the cloud.

11
A distributed database is simply one that is stored at various locations throughout the Internet
but is made available anywhere.
Block Chain
Blockchain is a secure, shared digital ledger (database) that records information across a network
of computers, rather than storing it in one central location. Data is stored in groups called
"blocks" that are chained together chronologically, making it nearly impossible to alter, cheat, or
hack.
In summary it is:
 Distributed database of transactions
 Operates on a network without central authority
 Maintains a growing list of records called blocks
 Once recorded, blocks cannot be changed
 Reduces cost of processing transactions and enhances security
Figure 5.11 How Blockchain Works

A blockchain system is a distributed database that records transactions in a peer-to-peer network

of computers

12
5.10 Principal tools and technologies for accessing information from databases to improve
business performance and decision making
Corporations and businesses go to great lengths to collect and store information on their
suppliers and customers. What they haven’t done a good job of in the past is fully using the data
to take advantage of new products or markets.
5.10.1 The Challenge of Big Data
Just a bit ago, we talked about how much of the data businesses want to collect, store, process,
and use are no longer sorted neatly and easily into rows, columns, and tables. Email messages,
text messages, tweets, and even output from large mainframe computers that process huge
amounts of data, now contain information companies and managers are looking for. Postings to
Facebook and LinkedIn contain data that can be useful to businesses if they are able to turn it
into useful information.
The term big data is used to describe those kinds of data that cannot be stored and process in
typical database management systems. While the term isn’t meant to describe the quantity of
data, it does reach into the Exabyte and petabyte range. Companies want and need to capture,
store, process, and generate information from big data because it shows patterns in business
transactions and processes that may be useful to executives and managers.
5.10.2 Business Intelligence Technology Infrastructure
Businesses collect millions of pieces of data. Using the right tools, a business can use its data to
develop effective competitive strategies that we discussed in previous chapters. Rather than
guessing about which products or services are your best sellers, business intelligence provides
concrete methods of analyzing exactly what customers want and how best to supply them.
Three benefits of using business intelligence include:
 Capability to a mass information
 Develop knowledge about customers, competitors, and internal operations
 Change decision-making behavior to achieve higher profitability
Many times businesses store data in separate systems even though they’ve made great strides in
migrating everything into one large database. In some cases, the data are structured, semi-
structured, or unstructured. Somehow, all that has to come together at some point using
appropriate tools and technologies. How to do that effectively and efficiently is what we’ll look
at now.

13
5.10.3 Data Warehouses and Data Marts
As organizations want and need more information about their companies, their products, and
their customers, the concept of data warehousing has become very popular. Remember those
islands of information we keep talking about? Unfortunately, too many of them have proliferated
over the years and now companies are trying to rein them in by using data warehousing.
No, data warehouses are not great big buildings with shelves and shelves of bits and bytes stored
on them. They are huge computer files that store old and new data about anything and everything
that a company wants to maintain information on. Data come from a variety of sources, both
internal and external to the organization. They are then stored together in a data warehouse from
which they can be accessed and analyzed to fit the user’s needs.
Since a data warehouse can be cumbersome because of its size and sheer volume of data, a
company can break the information into smaller groups called data marts. It’s easier and
cheaper to sort through data marts that tend to be more focused on a particular subject. It’s still
useful to have a huge data warehouse, though, so that information is available to everyone who
wants or needs it. You can let the user determine how the data will be manipulated and used.
Using data warehouses and data marts correctly can give management a tremendous amount of
information that can be used to trim costs, reduce inventory, put products in the right stores at the
right time, attract new customers, or keep old customers happy.

14
Figure 5.12 Business Intelligence Technology Infrastructure

A contemporary business intelligence technology infrastructure features capabilities and tools to

manage and analyze large quantities and different types of data from multiple sources. Easy-to-
use query and reporting tools for casual business users and more sophisticated analytical toolsets
for power users are included.
Figure 5:12 illustrates the components of a contemporary business intelligence infrastructure. It
shows that the data warehouse extracts data from multiple sources, both internal and external,
including a Hadoop data lake, and transforms it as needed for the business intelligence systems.
An analytic platform has tools for power users, including reporting, OLAP, and data mining, to
extract meaningful information from the data warehouse and Hadoop data lake. A subset of the
data warehouse is collected in a data mart for casual groups of users.
5.10.4 Hadoop
For the kinds of data, we discussed earlier that don’t fit neatly into rows, columns, and tables, a
new technology called Hadoop is better for handling unstructured and semi-structured big data.

15
Hadoop is an open-source software framework that uses distributed parallel processing across a
network of small computers.
The software breaks huge data set problems into smaller sub-sets, sends the sub-sets to the
smaller computers for processing, and then gathers the results back into a data set that is
analyzed. There are two main components of the system:
 Hadoop Distributed File System used for data storage
 MapReduce for high-performance parallel data processing

5.11 Analytical Tools: Relationships, Patterns, Trends

Tools for consolidating, analyzing, and providing access to vast amounts of data to help users
make better business decisions
 Multidimensional data analysis (O L A P)
 Data mining
 Text mining
 Web mining
5.11.1 Online Analytical Processing (O L A P)
• Supports multidimensional data analysis, enabling users to view the same data in
different ways using multiple dimensions
– Each aspect of information—product, pricing, cost, region, or time period—
represents a different dimension
– Example: How many washers was sold in the East region in June compared to the
previous June and sales forecast
• Enables users to obtain online answers to ad hoc questions such as these in a fairly rapid
amount of time

16
Figure 5.13 Multidimensional Data Model

This view shows product versus region. If you rotate the cube 90 degrees, the face that will show
is product versus actual and projected sales. If you rotate the cube 90 degrees again, you will see
region versus actual and projected sales. Other views are possible.
Figure 5.13 illustrates the concept of dimensions as it relates to data. Product is one dimension.
Region is a dimension and Product and Sales are two more dimensions. Altogether, we are trying
to analyze four dimensions. The business question is: In the Eastern region, what are the actual
and projected sales of our products (nuts, bolts, washers, and screws)? Sometimes referred to as a
“data cube,” the graphic can depict this four-dimensional view of the data. More importantly,
when compared to a spreadsheet model of the same data, a graphical data cube is much faster,
easier to understand and visualize the relationships
5.11.2 Data Mining
Data mining technology allows a digital firm to get more information than ever before from its
data. One danger in data mining is the problem of getting information that on the surface may

17
seem meaningful, but when put into context of the organization’s needs, simply doesn’t provide
any useful information.
 Finds hidden patterns and relationships in large datasets
o Example: customer buying patterns
 Infers rules to predict future behavior
These are the five types of information managers can obtain from data mining:
o Associations: occurrences linked to single event
o Sequences: events linked over time
o Classifications: patterns describing a group an item belongs to
o Clustering: discovering as yet unclassified groupings
o Forecasting: uses series of values to forecast future values

5.11.3 Text Mining and Web Mining

Much of the data created that might be useful to businesses is stored not in databases but in text-
based documents. Word files, emails, call center transcripts, and services reports contain
valuable data that managers can use to assess operations and help make better decisions about
the organization. Unfortunately, there has not been an easy way to mine those documents until
recently.
Text mining or text data mining, is a specialized branch of data mining that converts large
volumes of unstructured text such as emails, social media, and documents into structured data to
discover insights, trends, and relationships. By using natural language processing (NLP) and
machine learning, it transforms raw text into analytical formats for pattern recognition.
Text mining tools help scrub text files to find data or to discern patterns and relationships.
Smart companies use sentiment analysis to improve customer interfaces or solve problems they
otherwise wouldn’t have known exist.

Important aspects of text mining include:

 Process: The workflow typically involves collecting text, cleaning/preprocessing (removing
noise, tokenizing), transforming text into structured data, and analyzing it.
 Techniques: Common methods include sentiment analysis, topic modeling, classification,
and entity recognition.
 Purpose: It identifies patterns and relationships within textual data that are not immediately
obvious, assisting in data-driven decision-making.

18
 Applications: Common uses include analyzing customer feedback, social media monitoring,
filtering spam, and searching for information in documents.
Text mining bridges the gap between unstructured text and traditional data mining
techniques, often serving as a pre-processing step for larger data analysis.

Web Mining is a specialized branch of data mining that applies analytical techniques to
automatically discover, extract, and evaluate information from the World Wide Web. It analyzes
web content, hyperlink structures, and server logs to find hidden patterns, improving website
design, personalizing user experiences, and enhancing search engine results.
The text uses the example of marketers using the Google Trends service, which track the
popularity of various words and phrases used in Google search queries, to learn what people are
interested in and what they are interested in buying.
There are three categories of web mining processes:
 Web content mining: extract knowledge from the content of web pages – text, images,
audio, and video
 Web structure mining: data related to the structure of a website – links between
documents
 Web usage mining: user interaction data recorded by web servers – user behavior on a
website
5.12 Databases and the Web
• Many companies use the web to make some internal databases available to customers or
partners
• Typical configuration includes:
– Web server - is software or hardware, or both working together, that stores
website files (HTML, CSS, images, JavaScript) and delivers them to users over
the internet. It uses the HTTP (Hypertext Transfer Protocol) to handle client
requests from browsers, ensuring websites are accessible online.
– Application server - is a software framework and server-side environment that
hosts, executes, and manages applications, delivering business logic to users.
Unlike web servers that primarily deliver static content, application servers
process complex, dynamic data, connecting front-end web servers to back-end
databases.
19
 – Database server (hosting DBMS) - is a dedicated computer system or software
application that stores, manages, and retrieves structured data for client
applications. Operating on a client-server model, it runs a Database Management
System (DBMS) to process queries, ensure data integrity, and handle security. It
is fundamental to web development, enterprise software, and application backend
services.
• Advantages of using the web for database access:
– Ease of use of browser software
– Web interface requires few or no changes to database
– Inexpensive to add web interface to system
Figure 5.14 Linking Internal Databases to the Web

Users access an organization’s internal database through the web, using their desktop PCs or
mobile devices and web browser software.
Figure 5.14 illustrates the way data is passed from a database through to a user with a web
browser.
5.12.1 Data Governance
• Encompasses policies and procedures through which data can be managed as an
organizational resource
• Establishes organization’s rules for sharing, disseminating, acquiring, classifying, and
inventory information.
– Example: Firm information policy that specifies that only selected members of a
particular department can view certain information
Ensuring Data Quality
Data quality measures how well a dataset meets criteria for accuracy, completeness,
consistency, timeliness, validity, and uniqueness, ensuring it is fit for its intended purpose in

20
operations, planning, and decision-making. High-quality data is reliable and accurate, while poor
quality disrupts analytics, erodes trust, and creates risks.
• Data quality assurance especially important with today’s organizations relying so heavily
on data to drive operations and decision making
– But not just a business problem. Also can pose serious problems for individuals
• Data quality problems caused by:
– Redundant and inconsistent data produced by multiple systems
– Data input errors
• Data quality audit - is a systematic, often annual, review of an organization's data to
assess its accuracy, completeness, consistency, and reliability. It involves evaluating data
against predefined standards to identify errors, such as duplicates or missing information,
ensuring data is fit for decision-making, compliance, and operational use.
Data quality audits verify data accuracy in one of three ways:
 Survey entire data files
 Survey samples from data files
 Survey end users about their perceptions of data quality
• Data cleansing - is the essential process of detecting and correcting inaccurate,
incomplete, improperly formatted, or duplicate data within a dataset. It improves data
quality for analysis by fixing errors, filling in missing values, and standardizing formats
to ensure accuracy and consistency.
It’s better for the company or organization to uncover poor quality data than to have customers,
suppliers, or governmental agencies uncover the problems.
Whether a company creates a single data warehouse from scratch or puts a web-front on old,
disparate, disjointed databases, it still needs to ensure data cleansing receives the attention it
should. It’s too expensive, both monetarily and customer-oriented, to leave bad data hanging
around.

21