Transcript Chapter 3 - Database Management

Chapter 3
Database
Management
PowerPoint Presentation
Jack Van Deventer
Ward M. Eagen
© 2006 Pearson Education Canada Inc.
3-1
Chapter 3 Objectives
Understand why databases are important to
modern organizations
 Understand how databases work
 Understand how organizations can
maximize their strategic potential with
databases

© 2006 Pearson Education Canada Inc.
3-2
Case: Ontario Fire Marshal




Ontario’s Office of the Fire Marshal (OFM) is
constructing an online database to be shared by the
provinces 500+ fire departments to access
incident-related information re statistics, trends,
and cross-comparisons of the province’s fires
The purpose is to aid decision making, reduce risk,
and bolster local business cases for resources
Fire services are ‘data rich, information poor’
Must be Internet-based and capable of extracting
data from legacy systems from all of the province
© 2006 Pearson Education Canada Inc.
3-3
Database Management for
Strategic Advantage
Database – a collection of related data
organized in a way to facilitate data
searches
 Use databases to:

Create a book
 Track book sales
 Set salaries and wages
 Pay employees

© 2006 Pearson Education Canada Inc.
3-4
Database Management for
Strategic Advantage

The Database Approach: Foundational
Concepts
DBMS – Database Management Systems
 Use a DBMS software to create, store,
organize, and retrieve data from a single
database or several databases
 Example: Microsoft Access, FileMaker Pro

© 2006 Pearson Education Canada Inc.
3-5
Database Management for
Strategic Advantage

Advantages of the Database Approach









Program-data independence
Minimal data redundancy
Improved data consistency
Improved data sharing
Increased productivity of application development
Enforcement of standards
Improved data quality
Improved data accessibility
Reduced program maintenance
© 2006 Pearson Education Canada Inc.
3-6
Brief Case #1: Major Drilling and
Tenrox



Major Drilling Group International Inc. from
Moncton, N.B. is a minor contractor to mining
companies around the world
Their outdated client/server architecture was a
problem architecturally, with poor performance,
and costly maintenance
Montreal firm Tenrox developed a Web-based
solution that reduces data redundancy, enhances
performance and enforces business rules
© 2006 Pearson Education Canada Inc.
3-7
Database Management for
Strategic Advantage
 Effective

Management of Databases
The database administrator (DBA) :
 Works with programmers and analysts
to design and implement the database
 Works with users and managers to
establish database policies
 Implements security features and
establishes database permissions
© 2006 Pearson Education Canada Inc.
3-8
Key Database Activities

Entering and Querying Data
Form
 Structured Query Language (SQL)
 Query by example (QBE)

© 2006 Pearson Education Canada Inc.
3-9
Key Database Activities

Creating Database Reports
Report – a compilation of data that is organized
and produced in printed format
 Report generators

© 2006 Pearson Education Canada Inc.
3-10
Key Database Activities

Database Design
Must be organized
 Few or no redundancies
 Data model – a map of entity relationships
 Keys




Primary key
Combination primary key
Secondary key
© 2006 Pearson Education Canada Inc.
3-11
© 2006 Pearson Education Canada Inc.
3-12
When Things Go Wrong: Royal
Bank and SAS



RBC with SAS implemented software to comply
with anti-terrorism and anti-money laundering
regulations that went into effect Sept. 11, 2001
Formerly, all searches were manual: Now
software compares client data with control lists
from Canadian Federal authorities and positive
results are reported
There is a constant dilemma in conflict between
individual privacy and compliance with the law
© 2006 Pearson Education Canada Inc.
3-13
Accounting and Finance: Gesco





Founded in 1938, Gesco Canada has one hundred
sales reps, sales managers, and sales support staff,
thousands of customers, an extensive product line
over a territory 5600 km across
The gathering and analyzing of data is a crucial
corporate asset in supporting business strategy
Gresco has adopted and online database solution
Field reps log on and input data which is instantly
available and summarized for account managers
Key accounts are designated and forms made
available to field reps online
© 2006 Pearson Education Canada Inc.
3-14
Operations Management: BC
Assessment





BC Assessment launched ‘LinkBC’ in 2003 allowing
municipalities to access property data via the Web
BC spent 2004 expanding its online property database
offering municipalities older records and opening it up to
the private sector
Users can compare information in different locations and
‘slice and dice’ the information any way they choose
Data goes back 4 years but enhancements will extend it to
10
Ontario also offers the data online through MPAC: egovernment is trying to present a single face to business,
constituents, and other departments
© 2006 Pearson Education Canada Inc.
3-15
Brief Case #2: Nexus





Canadian/US border crossing delays are a constant
problem for commuters
Express entry is offered as a service for a fee to
low-risk commuters through dedicated lanes
After security clearance and an interview,
commuters are issued a special RF-enabled card
The RF signal launches the commuter’s file and
visual confirmation completes the process
Now the service is localized but will be tied into a
central database in the future
© 2006 Pearson Education Canada Inc.
3-16
Key Database Activities

Database Associations
One-to-one (teams to stadiums)
 One-to-many (player to team)
 Many-to-many (players to games)

© 2006 Pearson Education Canada Inc.
3-17
Key Database Activities

Entity-Relationship Diagramming (ERD)
Commonly used when designing databases
 One draws entities (tables) as boxes and lines
between entities to show relationships

© 2006 Pearson Education Canada Inc.
3-18
Key Database Activities

The Relational Model of Databases
Entities linked by a common key field
 Records = rows
 Fields = columns
 Other models exist




Hierarchical
Network
Object-oriented model
© 2006 Pearson Education Canada Inc.
3-19
Key Database Activities

Normalization
A technique for making complex databases
more efficient and more easily handled by the
DBMS
 Eliminates data redundancy

© 2006 Pearson Education Canada Inc.
3-20
© 2006 Pearson Education Canada Inc.
3-21
© 2006 Pearson Education Canada Inc.
3-22
Key Database Activities

Data Dictionary

A document that explains each piece of
information in the database




Field name
Data type
• Numeric, text, date/time
• Useful for sorting and allocating storage
Is this field a key field?
Business rules
• Update authority
• Valid data values
© 2006 Pearson Education Canada Inc.
3-23
How Organizations Get the
Most from Their Data

Linking Web Sites to Databases

Example: Amazon
 2.5 million titles
 Managing online data effectively
© 2006 Pearson Education Canada Inc.
3-24
Marketing: Pizza Hut





Pizza Hut uses Teradata Warehouse Miner data
mining software for competitive intelligence
Their data warehouse contains data from POS on
40 million households: 40-50% of NA market
Recovered the cost of licensing, integrating, and
staff training in the first quarter of implementation
The ability to group household segments into
target markets has turned direct mail from a breakeven cost centre into a profit centre
Benefits include minimizing data redundancy,
reduced proprietary data structures and simplified
management
© 2006 Pearson Education Canada Inc.
3-25
How Organizations Get the
Most from Their Data

Data Mining
A method for better understanding data
 Information on customers, products, markets, etc.
 Drill down: from summary to more detailed data
 Sort and extract information
 Trends, correlations, forecasting, statistics

© 2006 Pearson Education Canada Inc.
3-26
How Organizations Get the
Most from Their Data

Data Mining

Online Transaction Processing (OLTP)
 Immediate automated responses to user
requests
 Multiple concurrent transactions
 A big part of interactive Internet e-commerce
© 2006 Pearson Education Canada Inc.
3-27
How Organizations Get the
Most from Their Data

Data Mining

Online Analytical Processing (OLAP)
 Graphical software tools that provide complex
analysis of data stored in a database
 Drills down to deeper levels of consolidation
 Time series and trend analysis
 “What if” and “why” questions
© 2006 Pearson Education Canada Inc.
3-28
How Organizations Get the
Most from Their Data
 Data

Mining
Merging Transaction and Analytical
Processing
 Real-time OLAP diminishes
performance because the database must
be “locked” during execution time
 Solution: replicate transactions on a
second database server
© 2006 Pearson Education Canada Inc.
3-29
How Organizations Get the
Most from Their Data

Data Mining

Merging Transaction and Analytical Processing
 Operational Systems
• Interact with customers and run a business
in real time
• Examples: Order processing, reservation
systems
 Informational Systems
• Support decision making based on stable
point-in-time or historical data
© 2006 Pearson Education Canada Inc.
3-30
Global Perspectives: Transborder
Data Security and Privacy






Cultural issues pose significant challenges for
organizations in the Global Economy
The European Union Data Protection Directive, Oct. 1998
limits how personal data may be used in Europe
The Personal Information Protection and Electronic
Documents Act provides similar restrictions in Canada
U.S. regulations fall short of either in many respects
For example data collected in Europe and Canada may
only be used for the purposes for which they were
collected whereas it is routinely sold in the U.S.
Hopefully, there will be single treaty on privacy developed
© 2006 Pearson Education Canada Inc.
3-31
How Organizations Get the
Most from Their Data

Data Warehousing
Integrating multiple large databases into a
single repository
 Queries, analysis, and processing
 Purpose: Put key business information into
the hands of decision makers
 Cost: Millions

© 2006 Pearson Education Canada Inc.
3-32
How Organizations Get the
Most from Their Data

Data Marts
Instead of one large data warehouse, many
organizations create multiple data marts
 Each contains a subset of the data
 Example: Finance, inventory, personnel
 Each data mart is customized for particular
DSS applications
 Cost: Typically less than $1 million

© 2006 Pearson Education Canada Inc.
3-33
Coming Attractions: Using
Databases to Stop Telemarketers




The US Congress implemented a ‘do not call’
database that makes it illegal for telemarketers to
call those on it
Implemented Oct. 1, 2003, 55 million Americans
registered in the first 5 months
The telemarketing industry is greatly opposed to
such acts as it restricts their market
Canada has no such legislation but is closely
watching the effects of the legislation in the US
and a similar Canadian Law is likely
© 2006 Pearson Education Canada Inc.
3-34
Information Systems:
Newfoundland Health Card





Newfoundland and Labrador are developing a
comprehensive electronic health-care record
$3.6 million was earmarked for building a Unique Personal
Identifier/Client Registry system for the first phase
The federal government added $5.4 million to its
development: it is interested in using it as a template
Working with no real authority, the process requires getting
people involved and consensus-building
The next phase is to build a provincial Pharmacy Network
linking 170 pharmacies and 1000 physicians to reduce
adverse reactions to medication
© 2006 Pearson Education Canada Inc.
3-35
Final Case: RBC Analytics




Analytics are used to making decisions based on the
‘slicing and dicing’ of data into business intelligence
Examples include using analytic tools to ‘troll’ through a
data warehouse to detect buying patterns, to examine the
effectiveness of your operations, or to measure alignment
of IT with business goals
RBC uses analytics to accurately segment and serve
diverse groups more profitably
A study by the technological research firm IDC showed
that the average ROI from analytics was 431% and the
market for analytic software will reach US $4.8 billion by
2007
© 2006 Pearson Education Canada Inc.
3-36