Transcript tia11e_ch11_ppt_author_revised

Technology
in Action
Alan Evans • Kendall Martin
Mary Anne Poatsy
Eleventh Edition
Copyright © 2015 Pearson Education, Inc.
Technology in Action
Chapter 11
Behind the Scenes:
Databases and Information Systems
Copyright © 2015 Pearson Education, Inc.
Chapter Topics
• Database Basics
– Database Building Blocks
– Database Types
– Database Functions
• How Businesses Use Databases
– Database Warehousing and Storage
– Business Intelligence Systems
– Data Mining
Copyright © 2015 Pearson Education, Inc.
2
Database Basics
• Database Building Blocks
• Database Types
• Database Functions
Copyright © 2015 Pearson Education, Inc.
Database Building Blocks
• A database is an organized collection of
related data, which can be
– Stored
– Sorted
– Queried
• Databases make data more meaningful
and useful
• Databases turn data into information
Copyright © 2015 Pearson Education, Inc.
4
Database Building Blocks
Databases Versus Lists
• Databases are not needed for managing
all types of data
– Lists are adequate for simple tasks
– Other applications have tools to create lists
• Word
• Excel
Copyright © 2015 Pearson Education, Inc.
5
Database Building Blocks
Databases Versus Lists
• When a list is not sufficient for organizing
data
– Complex information
– When multiple people need to access
information
Copyright © 2015 Pearson Education, Inc.
6
Database Building Blocks
Databases Versus Lists
• Lists can lead to inconsistencies
– Repetition of data
– Errors from inconsistently entered or updated
data
• Data redundancy - unnecessary extra
copies of data
• Lists have other problems
Copyright © 2015 Pearson Education, Inc.
7
Database Building Blocks
Databases Versus Lists
Copyright © 2015 Pearson Education, Inc.
8
Database Building Blocks
Advantages of Using Databases
• Databases make our lives easier
• Databases can manage large amounts of
data efficiently
• Databases make information sharing
possible
• Databases promote data integrity
Copyright © 2015 Pearson Education, Inc.
9
Database Building Blocks
Advantages of Using Databases
Copyright © 2015 Pearson Education, Inc.
10
Database Building Blocks
Disadvantages of Using Databases
• Disadvantages of databases
– Can be more time consuming and expensive
to set up and administer
– Care is needed to insure they function as
intended
– Administrator is responsible for designing,
constructing, and maintaining databases
Copyright © 2015 Pearson Education, Inc.
11
Database Building Blocks
Database Management Systems
• How databases are created
– Database management system (DBMS) is
designed to capture and analyze data
– Four main operations of a DBMS
• Creating databases and entering data
• Viewing (or browsing) and sorting data
• Querying (extracting) data
• Outputting data
Copyright © 2015 Pearson Education, Inc.
12
Database Building Blocks
Database Terminology
• How data is stored in a database
All the records representing
student information is a
table in this database
The city column represents
one field in this database
Julio’s
contact
data is
one
record
Copyright © 2015 Pearson Education, Inc.
13
Database Building Blocks
Planning and Creating the Database
• How to create a database with a DBMS
– Describe the data to be captured
– Data dictionary & Metadata
Copyright © 2015 Pearson Education, Inc.
14
Database Building Blocks
Planning and Creating the Database
• How data tables are created
– Step 1: Input unique field names
– Step 2: Define the data type
– Step 3: Set a maximum field size
– Step 4: Set a default value if necessary
– Repeat for each field in the table
Copyright © 2015 Pearson Education, Inc.
15
Database Building Blocks
Planning and Creating the Database
• How to know what fields are needed
– Careful planning is required
– Field should describe a unique piece of data
• Do not combine two pieces of data
• Allows for better filtering and sorting
• Fields can be added or deleted
Copyright © 2015 Pearson Education, Inc.
16
Database Building Blocks
Planning and Creating the Database
• Rules for establishing field names
– Must be unique within a table
– Do not use spaces
•
Use underscores or CamelCase can be used
•
Use caption field to display spaces
– Creating a data dictionary will help you plan
Copyright © 2015 Pearson Education, Inc.
17
Database Building Blocks
Planning and Creating the Database
• Determining what type of data can be
entered in a field
– Data type indicates what type of data can
be stored in the fields
– Prevents wrong type of data from being
entered
Copyright © 2015 Pearson Education, Inc.
18
Database Building Blocks
Planning and Creating the Database
Copyright © 2015 Pearson Education, Inc.
19
Database Building Blocks
Planning and Creating the Database
• Amount of data which can be entered into
a field
– Field size determines maximum number of
characters
– Tailor field size to match maximum length of
data
– Truncated data can result from field size set
too small
Copyright © 2015 Pearson Education, Inc.
20
Database Building Blocks
Using Primary Keys
• Having the same values in the same table
– It is possible for fields to have the same
values
– Each record must have a unique value, the
primary key
– Primary keys make it impossible to duplicate
records
Copyright © 2015 Pearson Education, Inc.
21
Database Building Blocks
Using Primary Keys
• A good primary key
– Must be unique
– Doesn’t have to represent something
– AutoNumber data type (Microsoft Access) is
often used
Copyright © 2015 Pearson Education, Inc.
22
Database Types
• Three major types of databases are in use
– Relational
– Object-oriented
– Multidimensional
• Relational databases
have the largest market share
• Multidimensional databases are growing
Copyright © 2015 Pearson Education, Inc.
23
Database Types
Relational Databases
• Relational database
– Organizes data in table format
– Logically groups similar data into a relation
(a table that contains related data)
– Links data between tables through
relationships on common keys
Copyright © 2015 Pearson Education, Inc.
24
Database Types
Relational Databases
Common field in one table (primary key) linked to
common field (foreign key) in second table
Need to keep data in related tables synchronized
Copyright © 2015 Pearson Education, Inc.
25
Database Types
Relational Databases
• Relational database
– Referential integrity: Each value in the
foreign table has a corresponding value in
the primary table
– Prevents orphan records
Copyright © 2015 Pearson Education, Inc.
26
Database Types
Relational Databases
• Different types of relationships in relational
databases
– One-to-many
– One-to-one
– Many-to-many
Copyright © 2015 Pearson Education, Inc.
27
Database Types
Object-Oriented Databases
• Store data in objects rather than tables
• Contain methods for processing or
manipulating data
• Can store more types of data than
relational databases
• Can access data faster
Copyright © 2015 Pearson Education, Inc.
28
Database Types
Object-Oriented Databases
• Unstructured data include audio clips,
video clips, pictures, and extremely large
documents
– Binary large object (BLOB) is unstructured data
encoded in binary form
– Becoming more popular because of variety of
data
– Costly to convert data but can provide many
advantages
Copyright © 2015 Pearson Education, Inc.
29
Database Types
Multidimensional Databases
• Stored data can be analyzed from different
perspectives (dimensions)
• Relational database has only two
dimensions (fields and records)
• Multidimensional databases organize in
cube format
Copyright © 2015 Pearson Education, Inc.
30
Database Types
Multidimensional Databases
• Data cube
– Measure attribute:
Main type of data
that cube is tracking
– Feature attributes:
Describe measure
attribute in
meaningful ways
Copyright © 2015 Pearson Education, Inc.
31
Database Types
Multidimensional Databases
• Advantages of multidimensional
databases
– Customized to provide information to variety
of users
– Process data faster
• Critical for larger databases
• Especially when accessed via the Internet
Copyright © 2015 Pearson Education, Inc.
32
Database Functions
• Functions
– Populate the database by creating records
– Allow users to extract subsets of data from
the database
– Output the data in a meaningful and
presentable format
Copyright © 2015 Pearson Education, Inc.
33
Database Functions
Inputting Data
• How to get data into the database
– Can be directly keyed into the database
– Can import from other files
• Saves time
• Reduces data error
• Must match the format of the database exactly
• Filters are often applied
• Nonconforming data is flagged
Copyright © 2015 Pearson Education, Inc.
34
Database Functions
Inputting Data
• How to make manual entry into a
database more efficient
– Input forms used to control data input
– Each field has a label
– Data is inputted into the blank boxes
– Existing data can be changed through forms
– Ensures that correct record is changed
Copyright © 2015 Pearson Education, Inc.
35
Database Functions
Inputting Data
Copyright © 2015 Pearson Education, Inc.
36
Database Functions
Data Validation
• Ensuring that only valid data is entered
– Process of ensuring that data meets
specified guidelines
– Rule is defined in data dictionary
– Specified in field properties for each field
– Violations result in error message with
suggested action
– Common validation rules
Copyright © 2015 Pearson Education, Inc.
37
Database Functions
Data Validation
Copyright © 2015 Pearson Education, Inc.
38
Database Functions
Data Validation
Data entered that falls
outside the range of
acceptable values
No value entered in a
required field
Copyright © 2015 Pearson Education, Inc.
39
Database Functions
Viewing and Sorting Data
• Viewing the data in a database
– Displaying all data at one time is an option
– Many times data is viewed one record at a
time
– Forms display individual records
• From only one table
• From related tables
Copyright © 2015 Pearson Education, Inc.
40
Database Functions
Viewing and Sorting Data
• Reordering records in a database
– Easy to sort data in ascending or descending
order
• Highlight a column
• Click Ascending or Descending on the Ribbon
Copyright © 2015 Pearson Education, Inc.
41
Database Functions
Viewing and Sorting Data
View records
by browsing
Sort records
by field name
Copyright © 2015 Pearson Education, Inc.
42
Database Functions
Extracting or Querying Data
• Displaying a subset of data
– Use a filter or create a query
– Query language
– Structured Query Language (SQL) is the
most popular
– Modern systems provide wizards for creating
queries
Copyright © 2015 Pearson Education, Inc.
43
Database Functions
Extracting or Querying Data
Copyright © 2015 Pearson Education, Inc.
44
Database Functions
Outputting Data
• Retrieving data from of a database
– Most common output is viewable or printable
report
– Can generate reports from data in tables or
from queries
– Adjustments can be made to the report
Copyright © 2015 Pearson Education, Inc.
45
Database Functions
Outputting Data
• Transferring data from a database to
another software application
– Exporting: Putting data into format that
another application can understand
– Data can be imported and exported as well
as converted to and from other formats
Copyright © 2015 Pearson Education, Inc.
46
Check Your Understanding
1. What is a database, and why is using one
beneficial?
• A database is a collection of related data that can be stored,
sorted, organized, and queried. Many actions we do every day
(use an ATM, shop online, make an airplane reservation)
generate data that needs to be stored, managed, and used by
others.
• By creating an organized structure for data, databases make
data more meaningful and therefore more useful. Databases
effectively turn data into information.
Copyright © 2015 Pearson Education, Inc.
47
Check Your Understanding
2. What do database management systems
do?
• Databases are created and managed using a database management
system (DBMS). A DBMS is specially designed application software
(such as Oracle Database or Microsoft Access) that interacts with the
user, other applications, and the database to capture and analyze
data.
• The four main operations of a DBMS are as follows:
1. Creating databases and entering data
2. Viewing (or browsing) and sorting data
3. Querying (extracting) data
4. Outputting data
Copyright © 2015 Pearson Education, Inc.
48
Check Your Understanding
3. What components make up a database?
• Understanding how databases store information requires knowing the
unique terminology developed to describe databases. Databases have
three main components:
1. Fields: A database stores each category of information in a field.
Fields are displayed in columns. Each field is identified by a field
name, which is a way of describing the information in the field.
2. Records: A group of related fields is called a record.
3. Tables (Files): A group of related records is called a table (or file).
Tables are usually organized by a common subject.
Copyright © 2015 Pearson Education, Inc.
49
Check Your Understanding
4. What types of databases are there?
• The three major types of databases currently in use are relational,
object-oriented, and multidimensional.
• Relational databases operate by organizing data into various tables
based on logical groupings. In relational databases, a link between
tables that defines how the data is related is referred to as a
relationship.
• An object-oriented database stores data in objects rather than in
tables. Objects contain not only data but also methods for
processing or manipulating that data.
• A multidimensional database stores data that can be analyzed from
different perspectives, called dimensions. This distinguishes it from
a relational database, which stores data in tables that have only two
dimensions (fields and records).
Copyright © 2015 Pearson Education, Inc.
50
Check Your Understanding
5. What are the functions of a relational database?
How do relational databases organize and
manipulate data?
Input (populate) data directly by keying in, or by importing from
other sources. Input forms control how new data is entered. Data
validation rules help prevent improper data from being entered.
Data is viewed/displayed through forms showing individual
records, or through tables displaying all related records
Queries display a subset of data that match certain criteria.
Reports used to output data. Data can be aggregated and sorted.
Data can be outputted to other applications to be further
manipulated.
Copyright © 2015 Pearson Education, Inc.
51
How Businesses Use
Databases
• Database Warehousing and Storage
• Business Intelligence Systems
• Data Mining
Copyright © 2015 Pearson Education, Inc.
Data Warehousing and Storage
• Problems arise when
– Organization gets much larger
– Data is stored in separate databases
• Benefits of accessing data from all
databases are being recognized
• Data warehouses and data marts are large
storage repositories
Copyright © 2015 Pearson Education, Inc.
53
Data Warehousing and Storage
Data Warehouses
• Large-scale collection of
data
• Contains and organizes
data in one place
• Data comes from multiple
databases
• Consolidate information
from various systems
• Present enterprise-wide
view of operations
Copyright © 2015 Pearson Education, Inc.
54
Data Warehousing and Storage
Data Warehouses
• Data is organized as in a normal database
– Data is organized by subject
– Focus is on one specific aspect of an
operation
– Can contain information from multiple
databases
Copyright © 2015 Pearson Education, Inc.
55
Data Warehousing and Storage
Data Warehouses
• Does not capture data from only one time
period
• Populated with data
– Internal sources
– External sources
– Clickstream data
Copyright © 2015 Pearson Education, Inc.
56
Data Warehousing and Storage
Data Staging
• Fitting source data into the warehouse
– Data must be “staged” before being entered
into a data warehouse
– Many programs and procedures might be
needed to extract and reformat data
– Nature and complexity of data determines
complexity of process
Copyright © 2015 Pearson Education, Inc.
57
Data Warehousing and Storage
Data Staging
Copyright © 2015 Pearson Education, Inc.
58
Data Warehousing and Storage
Data Marts
• How to handle smaller amounts of data
– Data mart: Small slices of data warehouse
– Analyze related group of data separated
from the main body
– Pertains to single component of business
– Vital because different data can be extracted
and reformatted
– Can be stored in specialized data marts
Copyright © 2015 Pearson Education, Inc.
59
Business Intelligence Systems
• Software-based solution to gather and
analyze information
– Delivers up-to-the-minute data
• Integral because they store the functional
information
– All perform similar functions
Copyright © 2015 Pearson Education, Inc.
60
Business Intelligence Systems
Copyright © 2015 Pearson Education, Inc.
61
Business Intelligence Systems
Office Support Systems
• What an office support system
accomplishes
– Office support system (OSS) is designed to
improve communications and assist
employees in accomplishing tasks
Copyright © 2015 Pearson Education, Inc.
62
Business Intelligence Systems
Transaction-Processing Systems
• Keeps track of
everyday business
activities
• Early computers
hosted TPSs
Copyright © 2015 Pearson Education, Inc.
63
Business Intelligence Systems
Transaction-Processing Systems
• Batch processing
– Data is accumulated then several
transactions are processed at once
– Appropriate for activities that are not time
sensitive
– Often more efficient
Copyright © 2015 Pearson Education, Inc.
64
Business Intelligence Systems
Transaction-Processing Systems
• When TPS transactions are processed
– Most transactions are processed in real-time
– Real-time processing is when the database
is updated while the transaction is taking
place
– Online transaction processing (OLTP)
ensures that data in the TPS is current
Copyright © 2015 Pearson Education, Inc.
65
Business Intelligence Systems
Management Information Systems
• Management information system (MIS)
– Provides timely and accurate information
– Enables managers to make critical decisions
– Directs outgrowth of TPSs
– Powerful if organized and outputted in useful
form
– Often included as a feature of TPSs
• Can generate various reports
Copyright © 2015 Pearson Education, Inc.
66
Business Intelligence Systems
Decision Support Systems
• Decision Support System (DSS)
– Another type of business intelligence system
– Designed to help managers develop
solutions for specific problems
– Uses information from databases and data
warehouses
– Users can add own insights and experiences
to solution
Copyright © 2015 Pearson Education, Inc.
67
Business Intelligence Systems
Decision Support Systems
• How DSSs get data
Copyright © 2015 Pearson Education, Inc.
68
Business Intelligence Systems
Decision Support Systems
• What function a model management
system performs
– Assists in building management models
– Analysis tool provides view of a particular
business situation
– Aids in decision making
– Used to analyze data to create additional
models
Copyright © 2015 Pearson Education, Inc.
69
Business Intelligence Systems
Decision Support Systems
• How a knowledge-based system helps in
the decision-making process
– Fuzzy logic enables the interjection of
experiential learning into the equation by
considering probabilities
• Enables a system to be more flexible
• Consider a wider range of possibilities
Copyright © 2015 Pearson Education, Inc.
70
Business Intelligence Systems
Enterprise Resource Planning Systems
• What an Enterprise Resource Planning
(ERP) system does
– Accumulates relevant information
– Makes information available
– Uses a common database
Copyright © 2015 Pearson Education, Inc.
71
Data Mining
• Data mining
– Process by which great amounts of data are
analyzed and investigated
– Objective is to spot significant patterns and
trends that would not be obvious
Copyright © 2015 Pearson Education, Inc.
72
Data Mining
• Why businesses mine their data
– Understand customers better
– Effective marketing by concentrating efforts
– Data is classified, then cluster analysis
allows managers to determine trends
• How businesses mine their data
– Classification, Estimation, Affinity grouping,
Clustering, Description and Visualization
Copyright © 2015 Pearson Education, Inc.
73
Data Mining
Copyright © 2015 Pearson Education, Inc.
74
Check Your Understanding
6. What are data warehouses and data
marts, and how are they used?
• A data warehouse is a large-scale collection of data that
contains and organizes in one place all the data from an
organization’s multiple databases.
• Small slices of the data warehouse, each called a data mart,
are often created so that companies can analyze a related set
of data that are grouped together and separated out from the
main body of data in the data warehouse.
Copyright © 2015 Pearson Education, Inc.
75
Check Your Understanding
7. What is a business intelligence system,
and what types of business intelligence
systems are used by decision makers?
• An information system is a software-based solution used to gather and
analyze information. Databases, data warehouses, and data marts are
integral parts of information systems because they store the information
that makes information systems functional.
• All information systems perform similar functions, including acquiring data,
processing that data into information, storing the data, and providing the
user with a number of output options with which to make the information
meaningful and useful.
• Office Support System, Transaction Processing Systems, Management
Information Systems, Decision Support Systems, and Enterprise Resource
Planning Systems are types of business intelligence systems.
Copyright © 2015 Pearson Education, Inc.
76
Check Your Understanding
8. What is data mining, and how does it work?
Data mining is the process by which great amounts of data are analyzed
and investigated. Data mining enables managers to sift through data in
several ways. Each technique produces different information on which
managers can then base their decisions.
• Classification: Before mining, managers define data classes they think
will be helpful in spotting trends.
• Estimation: Estimation enables managers to assign a value to data based
on some criterion.
• Affinity grouping (or association rules): When mining data, managers can
determine which data goes together.
• Clustering: Clustering involves organizing data into similar subgroups, or
clusters. It’s different from classification in that there are no predefined
classes.
• Description and visualization: Often, the purpose of data mining is to
describe data so that managers can interpret it in new and different ways.
Copyright © 2015 Pearson Education, Inc.
77
All rights reserved. No part of this publication may be reproduced, stored in a
retrieval system, or transmitted, in any form or by any means, electronic,
mechanical, photocopying, recording, or otherwise, without the prior written
permission of the publisher. Printed in the United States of America.
Copyright © 2015 Pearson Education, Inc.
78555555555