Essentials of Management Information
Systems
Thirteenth Edition
Chapter 6
Foundations of Business
Intelligence: Databases and
Information Management
Copyright © 2019 Pearson Education Ltd.
What is a Database?
• Database:
– Collection of related files containing records on people, places, or
things
– Prior to digital databases, business used file cabinets with paper
files.
• Entity:
– Generalized category representing person, place, thing
– E.g., SUPPLIER, PART
• Attributes:
– Specific characteristics of each entity:
▪ SUPPLIER name, address
▪ PART description, unit price, supplier
Copyright © 2019 Pearson Education Ltd.
Relational Databases
• Organize data into two-dimensional tables (relations) with
columns and rows
• One table for each entity:
– E.g., (CUSTOMER, SUPPLIER, PART, SALES)
– Fields (columns) store data representing an attribute
– Rows store data for separate records, or tuples
• Key field: uniquely identifies each record
• Primary key
•One field in each table
•Cannot be duplicated
•Provides unique identifier for all information in any row
Copyright © 2019 Pearson Education Ltd.
Figure 6.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.
Copyright © 2019 Pearson Education Ltd.
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.
Copyright © 2019 Pearson Education Ltd.
Establishing Relationships (1 of 2)
• Entity-relationship diagram
– Used to clarify table relationships in a relational
database
• Relational database tables may have:
– One-to-one relationship
– One-to-many relationship
– Many-to-many relationship
▪ Requires “join table” or intersection relation that
links the two tables to join information
Copyright © 2019 Pearson Education Ltd.
Figure 6.4 A Simple Entity-Relationship
Diagram
This diagram shows the relationship between the entities
SUPPLIER and PART.
Copyright © 2019 Pearson Education Ltd.
Establishing Relationships (2 of 2)
• Normalization
– Streamlining complex groups of data
– Minimizes redundant data elements
– Minimizes awkward many-to-many relationships
– Increases stability and flexibility
• Referential integrity rules
– Ensure that relationships between coupled tables
remain consistent
•E.g., when one table has a foreign key that points to another table, you
may not add a record to the table with foreign key unless there is a
corresponding record in the linked table.
Copyright © 2019 Pearson Education Ltd.
Figure 6.5 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.
Copyright © 2019 Pearson Education Ltd.
Figure 6.6 The Final Database Design with
Sample Records
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.
Copyright © 2019 Pearson Education Ltd.
Figure 6.7 Entity-Relationship Diagram for
the Database with Four Tables
This diagram shows the relationship between the entities
SUPPLIER, ART, LINE_ITEM, and ORDER.
Copyright © 2019 Pearson Education Ltd.
Database Management Systems (DBMS)
• Software for creating, storing, organizing, and accessing
data from a database
• Separates the logical and physical views of the data
– Logical view: how end users view data
– Physical view: how data are actually structured and
organized
• Examples: Microsoft Access, DB2, Oracle Database,
Microsoft SQL Server, MySQL
Copyright © 2019 Pearson Education Ltd.
Figure 6.8 Human Resources Database
with Multiple Views
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.
Copyright © 2019 Pearson Education Ltd.
Operations of a Relational DBMS
• Select:
– Creates a subset of all records meeting stated criteria
• Join:
– Combines relational tables to present the server with
more information than is available from individual
tables
• Project:
– Creates a subset consisting of columns in a table
– Permits user to create new tables containing only
desired information
Copyright © 2019 Pearson Education Ltd.
Figure 6.9 The Three Basic Operations of a
Relational DBMS
The select, project, and join operations enable data from two
different tables to be combined and only selected attributes to be
displayed.
Copyright © 2019 Pearson Education Ltd.
Capabilities of Database Management
Systems
• Data definition capabilities:
– Specify structure of content of database
• Data dictionary:
– Automated or manual file storing definitions of data
elements and their characteristics
• Querying and reporting:
– Data manipulation language
▪ Structured query language (SQL)
▪ Microsoft Access query-building tools
– Report generation, e.g., Crystal Reports
Copyright © 2019 Pearson Education Ltd.
Figure 6.10 Access Data Dictionary
Features
Microsoft Access has
a rudimentary data
dictionary capability
that displays
information about the
size, format, and other
characteristics of each
field in a database.
Displayed here is the
information
maintained in the
SUPPLIER table. The
small key icon to the
left of
Supplier_Number
indicates that it is a
key field.
Copyright © 2019 Pearson Education Ltd.
Figure 6.11 Example of an SQL Query
SELECT PART.Part_Number, PART.Part_Name,
SUPPLIER.Supplier_Number,
SUPPLIER.Supplier_Name
FROM PART, SUPPLIER
WHERE PART.Suplier_Number = SUPPLIER.Supplier_Number AND
Part_Number = 137 OR Part_Number = 150;
Copyright © 2019 Pearson Education Ltd.
Figure 6.12 An Access 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 6.9.
Copyright © 2019 Pearson Education Ltd.
Non-Relational Databases
•Developed to handle large data sets of data that is not
easily organized into tables, columns, and rows
• “NoSQL”
• Handle large data sets of data that are not easily
organized into tables, columns, and rows
• Use more flexible data model
– Don’t require extensive structuring
• Can manage unstructured data, such as social media
and graphics
• E.g. Amazon’s SimpleDB, MetLife’s MongoDB
Copyright © 2019 Pearson Education Ltd.
Cloud Databases and Distributed
Databases
• Relational database engines provided by cloud
computing services
– Pricing based on usage
– Appeal to small or medium-sized businesses
• Amazon Relational Database Service
– Offers MySQL, Microsoft SQL Server, Oracle
Database engines
• Distributed databases
– Stored in multiple physical locations
– Google’s Spanner cloud service
Copyright © 2019 Pearson Education Ltd.
The Challenge of Big Data
• Massive quantities of unstructured and semi-structured
data from Internet and more
– 3Vs: Volume, variety, velocity
– Petabytes and exabytes
• Big datasets offer more patterns and insights than
smaller datasets, e.g.
– Customer behavior
– Weather patterns
• Requires new technologies and tools
Copyright © 2019 Pearson Education Ltd.
Business Intelligence Infrastructure
• Array of tools for obtaining useful information from
internal and external systems and big data
– Data warehouses
– Data marts
– Hadoop
– In-memory computing
– Analytical platforms
Copyright © 2019 Pearson Education Ltd.
Data Warehouses
• Data warehouse:
– Database that stores current and historical data that
may be of interest to decision makers
– Consolidates and standardizes data from many
systems, operational and transactional databases
– Data can be accessed but not altered
• Data mart:
– Subset of data warehouses that is highly focused and
isolated for a specific population of users
Copyright © 2019 Pearson Education Ltd.
Hadoop
• Open-source software framework for big data
• Breaks data task into sub-problems and distributes the
processing to many inexpensive computer processing
nodes
• Combines result into smaller data set that is easier to
analyze
• Key services
– Hadoop Distributed File System (HDFS)
– MapReduce
Copyright © 2019 Pearson Education Ltd.
In-Memory Computing
• Relies on computer’s main memory (RAM) for data
storage
• Eliminates bottlenecks in retrieving and reading data
• Dramatically shortens query response times
• Enabled by high-speed processors, multicore processing
• Lowers processing costs
Copyright © 2019 Pearson Education Ltd.
Analytic Platforms
• Preconfigured hardware-software systems
• Designed for query processing and analytics
• Use both relational and non-relational technology to
analyze large data sets
• Include in-memory systems, NoSQL DBMS
• E.g. IBM PureData System for Analytics
– Integrated database, server, storage components
• Data lakes
Copyright © 2019 Pearson Education Ltd.
Figure 6.13 Business Intelligence
Technology Infrastructure
Copyright © 2019 Pearson Education Ltd.
Analytical Tools: Relationships, Patterns,
Trends
• Once data is gathered, tools are required for
consolidating, analyzing, to use insights to improve
decision making
– Software for database querying and reporting
– Multidimensional data analysis (OLAP)
– Data mining
Copyright © 2019 Pearson Education Ltd.
Online Analytical Processing (OLAP)
• 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
– E.g., comparing sales in East in June versus May and
July
• Enables users to obtain online answers to ad hoc
questions such as these in a fairly rapid amount of time
Copyright © 2019 Pearson Education Ltd.
Figure 6.14 Multidimensional Data Model
Copyright © 2019 Pearson Education Ltd.
Data Mining
• Finds hidden patterns and relationships in large
databases and infers rules from them to predict future
behavior
• Types of information obtainable from data mining
– Associations: occurrences linked to single event
– Sequences: events linked over time
– Classifications: patterns describing a group an item
belongs to
– Clustering: discovering as yet unclassified groupings
– Forecasting: uses series of values to forecast future
values
Copyright © 2019 Pearson Education Ltd.
Text Mining
• Unstructured data (mostly text files) accounts for 80
percent of an organization’s useful information.
• Text mining allows businesses to extract key elements
from, discover patterns in, and summarize large
unstructured data sets.
• Sentiment analysis
– Mines online text comments online or in email to
measure customer sentiment
Copyright © 2019 Pearson Education Ltd.
Web Mining
• Discovery and analysis of useful patterns and information
from the web
– E.g. to understand customer behavior, evaluate
website, quantify success of marketing
• Content mining – mines content of websites
• Structure mining – mines website structural elements,
such as links
• Usage mining – mines user interaction data gathered by
web servers
Copyright © 2019 Pearson Education Ltd.
Databases and the Web
• Firms use the web to make information from their internal
databases available to customers and partners.
• Middleware and other software make this possible
– Web server
– Application servers or CGI
– Database server
• Web interfaces provide familiarity to users and savings
over redesigning legacy systems.
Copyright © 2019 Pearson Education Ltd.
Figure 6.15 Linking Internal Databases to
the Web
Copyright © 2019 Pearson Education Ltd.
Establishing an Information Policy
• Information policy
– States organization’s rules for organizing, managing,
storing, sharing information
• Data administration
– Responsible for specific policies and procedures
through which data can be managed as a resource
• Database administration
– Database design and management group responsible
for defining and organizing the structure and content
of the database, and maintaining the database.
Copyright © 2019 Pearson Education Ltd.
Ensuring Data Quality
• Poor data quality: major obstacle to successful customer
relationship management
• Data quality problems caused by:
– Redundant and inconsistent data produced by
multiple systems
– Data input errors
• Data quality audit
• Data cleansing
Copyright © 2019 Pearson Education Ltd.