Transcript DATABASES

DATABASES
&
DATA WAREHOUSES
S.12
Databases
DATABASES
&
DATA WAREHOUSES
Access to a Gold Mine of Information
“The real value will come from getting the right information to the
right people, at the right time, and giving those people the tools to find
the meaning in it.”
Declaration of Integration
Abbie Lundberg
editor-in-chief CIO
A.1c
Aawwww, Rubbish!
Database
u “Business intelligence tools are only as good as the data that goes
into them; faulty data leads to ill-informed decisions. The
ramifications range from ticked-off customers, to misled investors, to
testy regulators.”
► “Executives can face jail time under Sarbanes-Oxley if they don’t
have financial data in order.”
Information Week:
Business Innovation
Powered by Technology
► “Our marketing effectiveness leads to our sales effectiveness,
which leads to our service effectiveness. Data quality is the key
to success. If you don’t have quality data, that whole chain breaks
down.”
Chuck Scoggins,
VP Marketing,
Hilton Hotels
A.1c
Database
Aawwww, Rubbish!
u “The ultimate goal of data quality improvement is to
catch errors the point of entry or, even better, to prevent
errors from occurring at all.”
Philip Russom
Senior Manager of Research
Data Warehousing Institute
u “Reliable data starts at the beginning…. As long as you
are fixing things on the back end, you are not correcting
the problem.”
Rick Whiting
Information Week
A-2a
Database
ACCESS PROJECTS
u “What’s really great about databases and DBMSs is that the
concepts are the same whether you’re using a personal DBMSs
package or a large mainframe DBMS package.”
u “...once you learn database and DBMS concepts while using a
personal DBMS package (like MS ACCESS), you can apply your
knowledge directly to business environments where other DBMS
packages may be used.”
Haag.p94
u Team Projects use ACCESS to demonstrate fundamental concepts:
VData Definition: tables (fields, properties, keys)
Project A
VApplication Generators: forms (controls)
Projects B, C, D
VData Manipulation: sorting , filters & queries
Projects E, F
A-2b
Database
Use Access as an Alternative….
u “Access gives them a robust database engine that does
not lock into a specific technology.”
1.3
u “Your job is to get work done, not build computer
programs. Access provides an easy way to get started
…with a user interface that will be familiar to anyone who
has used Microsoft Office.”
2.3
u Access is inexpensive, and most people catch on
quickly….Your company probably owns several copies of
Access.
2.4-5
u Access is a workable solution.
Stonyfield Farms & CSS
3.3
TechRepublic
Access
Project A
cradling
tables, properties
relevance
Access
creating
Projects E, F
analysis filter, sort, query
Access
capturing
reliability Projects B, C, D
forms & controls
conveying
access
DBMS processes
data (reliability) info
(relevance) knowledge
(access & analysis)
communicating
access
cradling
shared
information
&
decentralized
computing
A-12
Select Access Projects
Choice: order CIO.1 presentations (Rx
Tentative
dates
930
T
5 APRIL
Data Definition
(cradling : relevance)
B: Application Generators
(capturing : reliably)
C: Application Generators
(capturing : reliably)
TH
CIO Team
Access Project
A:
D: Application Generators
(capturing : reliably)
7 APRIL E:
Data Manipulation
(creating : analysis)
F:
Data Manipulation
(creating : analysis)
Build)
1100
200
A-4a
ACCESS Projects: Structure & Process
DATA for
Project is in Chapter folder
Application
&
Analysis
(ALL 3)
CONCEPTS &
TEMPLATES from
PowerPoint & handouts
copy to flash-drive
from P:
after select projects
PROCEDURES from
MS step-by-step handout
Synthesis & Evaluation
of DBMS procedures: outline of
text integrating concepts & screens
►Should do“Print Screens”
into PPT as do project
Hard limit:
20 minutes.
# slides depends
on content & use.
Application
&
Analysis
Presentation focusing on
Analysis, Synthesis & Evaluation
contributes to Comprehension
Text in MS chapters
illustrates how to
apply, synthesize &
evaluate data
procedures &
concepts
CIO case
See samples
of prior
presentations
ASSIGNMENT: Work with team on ACCESS project.
u
Emphasize course concepts to
explain why the procedures are
important in:
H
meeting the MIS challenge
H
performing the 5Cs,
H
processing data into information
then knowledge,
H
data sharing and decentralized
computing that support decision
making and innovation
H
building CIS (TPS), MIS
(dashboards), DSS, WSS
H
creating information
partnerships, virtual – learning
organizations that are timeless &
locationless
u This requires reviewing
Information Age, IT Systems,
IT Strategies PowerPoints
and using those concepts,
frameworks and ideas to
analyze and evaluate the
ACCESS procedures.
u These exercises are essential
to the synthesis of concepts,
frameworks and ideas into a
new whole that changes the
brain real learning (Bloom
Hierarchy of Learning
Objectives; WSJ 19Jan07
“Thinking Can Change the
Brain”)
10
Seinfeld car rental:
databases support all 5Cs
take reservation
hold reservation
availability of cars
presentation
capture
forms
cradle
tables
create
query
communicate & convey
report
Cluster diagram:
access performs 5Cs &
supports IT systems
Project D: Data - Reliability
***
Uncheck “read only box” in properties (right click on file for properties), then Apply, OK
***
Security Warning: Enable content
*** Handout
1.
Access 2010: Chapter 6 “Maintain Data Integrity” (with notes)
2.
GardenCompany06 GardenCompany06 - AfterRestrictingDataUsingValidationRules
*** Context : pp. 143
6.5 Restricting Data to Values in Lists
 pp.167 - 171
6.6 Restricting Data to Values in Other Tables
 pp.172 - 176
 At step 17, refer to procedures at step 4 on page 173.
Do
these
pages
Some projects have
optional sections: for
example Project F
section 2.4 is optional
Project F: Knowledge – Access & Analysis
***
Uncheck “read only box” in properties (right click on file for properties), then Apply, OK
***
Security Warning: Enable content
*** Handout
1.
2.
Access 2010: Chapter 8 “Create Queries”
GardenCompany08 database
*** Context : p.209
8.2 Creating Queries Manually (Required)
 pp.216 - 220
Do
these
pages
8.3 Using Queries to Summarize Data (Optional)
 pp.221 - 224
8.5 Using Queries to Update Records (Required)
 pp. 231 – 234
8.6 Using Queries to Delete Records (Required)
 pp. 235 – 238
Some projects
have optional
sections
1. Right click the file,
then click
properties
2. Uncheck
“Read Only”
4. click “OK”
3. click “Apply”
select:
enable content
DATABASES
&
DATA WAREHOUSES
DATABASES
&
DATA WAREHOUSES
perform
(5Cs)
so meet
MIS challenge
Access to a Gold Mine of Information
“The real value will come from getting the right information to the
right people, at the right time, and giving those people the tools to find
the meaning in it.”
Declaration of Integration
Abbie Lundberg
CIO 1Dec2002
DB-2.a
D
A
T
A
B
A
S
E
S
&
D
A
T
A
W
A
R
E
H
O
U
S
E
S
Tools to perform
data into information
so meet the
5Cs that process
and knowledge
MIS challenge
capture
cradle
data
journal
DBMS
(ACCESS)
software that allows
KW to perform
these tasks: 5Cs
derived from a data
warehouse by KW using
queries & reports to convey
information & create
knowledge with DSS (Excel)
DATA MART
analyses:
cash flow
create
information
ledger
capture & cradle
data about basic
objects & events
(journal) with
TPS / CIS using
forms & tables
queries & reports used to create
& communicate information from
operational database to a special
form of database (ledger) that
supports decision making with
MIS & EIS
radiatingting
intelligence
Why M4th
Will Rock
Your World!
Bus Week
“A generation ago, quants turned
finance upside down. Now they’re
mapping out ad campaigns
(Victoria’s Secret) building new
businesses from mountains of
personal data (using databases).” 54.1
“Math wizzes are the new business elite. They’re turning
personal data, trends and online content into math,
crunching the numbers and discovering new efficiencies
and ways to market. Before long they’ll have a
mathematical model of you (Victoria’s Secret).” 6.1
What IS Victoria’s Secret?
“Databases enable e-tailers “ not only to track individuals’
purchases closely but also to analyze broad buying patterns - - by
gender, location and credit-card type, at least, and even internetNot address is given…(to) create powerful
service provider, if an e-mail
this!
profiles of shoppers and their buying habits” WSJ 1.5
DATABASES
“This wealth of data can be used to target marketing efforts...WSJ 1.5
Victoria Secret mails several catalogues a year, featuring largely the
same merchandise but with different covers and product photos.
Then it gauges which generates the most sales.” WSJ 3.1-4
Data Mining:
Why Math Will
Rock Your World!
Mapping out ad
campaigns!
What IS Victoria’s Secret?
“…are useful for answering specific questions about shoppers’
behavior on a site.  Do they abandon their shopping carts when
they get to the shipping charges?”
DATABASES
“… Do they quit the site if they have to browse through too many
screens to find what they want?  Will they spend more if you show
a shirt after they buy a pair of pants, but not if you show a jacket?”
WSJ 2.4
Data Mining:
Why Math Will
Rock Your World!
Building a mathematical
model of you!
Why M4th
Will Rock
Your World!
Bus Week
“Statisitics and probability …will
become core skills for business people
as we grapple with challenges involving
large data sets. Winners will know how
to use statistics.”
Databases
Mission Critical
(Big Data: Love
it or Leave WSJ)
“Statistics and probability …will come in handy whether
you’re building financial models at Goldman Sachs or
marketing plans at Ford.”
RRA: building
financial model to find
new efficiencies to
markets
DB-12.b
What are Databases & Data Warehouses?
Data Mart
RRA

convey
information
&
create
knowledge
finance - accounting example
Tactical, aimed at
meeting an
immediate need Extractive
Petroleum
BUSINESS
Industry
ANALYTICS
SIC 1311
E(RRS)
 R2 (ΔP)
efficiencies
.25
in capital market
resource allocation
E(HC)  R2 (ΔP)
Math Will .00
Rock You
OLAP (queries, sorting,
filtering) selectively extracts /
organizes relevant data for
analysis like ledger,
strategic but unfinished concept
SEC EDGAR
whatis.com

create
&
cradle
information
NEW
EFFICIENCIES
TO MARKETS
S&P Compustat
market
accounting
DATABASE
DATABASE
OLTP: JOURNALIZING BASIC EVENTS
BUSINESS SYSTEMS

CIS
(TPS)
capture
&
cradle
data
DATABASES
&
DATA WAREHOUSES
SL.8
DB-3.a
How is a relational database organized?
Logical Structure
(d e c i s i o n s)
Physical Structure
IT
Columns
Characteristics
Rows
Observations
DBMS
DBMS (Access) is
software bridge between
user’s logical requests and
physical structure so more
easily get to their work
performing the 5Cs
whatis.com
Access
BS&A
Table
Why Math
Will Rock
Your World
Bus. Week
p.62
Figure 3.4
DB-4.a
Logical Structure of
a Relational Database
is composed of files (tables)
Part file (table)
Facility file (table)
Figure 3.4
DB-4.a
Logical Structure of
Fields in columns
represent
characteristics
a Relational Database
is composed of files
Part file (table)
Facility file (table)
Figure 3.4
DB-4.a
Logical Structure of
Fields in columns
represent
characteristics
a Relational Database
is composed of files
Part file (table)
Records
(observations)
in rows
entered by
forms
Primary keys
define
records &
relationships
within a file
Facility file (table)
Access
Project A
cradling
tables, properties
relevance
Access
creating
Projects E, F
analysis filter, sort, query
Access
capturing
reliability Projects B, C, D
forms & controls
conveying
access
DBMS process
data (reliability)
information (relevance)
knowledge (access & analysis)
Access Projects
demonstrate
the DBMS
subsystems
of a relational
database
communicating
access
cradling
shared
information

decentralized
computing

radiating
intelligence
DB-13
Database
What benefits / costs are related to
databases & data warehouses?
u POTENTIAL BENEFITS
V improved data integrity (reliability: forms, controls,
validation rules)
V increased user productivity (input: forms;
output: queries/reports)
V increased security
V reduced data redundancy (capture once, use many)
u POTENTIAL COSTS
V development
V hardware
V software
training
maintenance
DB-14
Data Warehouse
What Are the Critical Success Factors
for Databases & Data Warehouses?
Expectations are communicated to the users
User involvement is ensured throughout the project
Project has a good sponsor, from the business side not IT
Project team has the right skill set (CSS : KPMG)
A realistic schedule including all necessary tasks
Tools have been chosen to match the needs of the users ()
Users are trained in the way they plan to use the warehouse
DataWarehousing.com
Next topic
Systems
Development
Life
Cycle
Assignments:
T: submit SDLC slides 1-3
► continue teamwork on Access
project / presentation
Critical
Success
Factors