230 likes | 452 Views
Game Plan. IntroductionWhy use a relational database?Database management systemsData warehousesData miningData marts. 2. A. Why use a relational database?. 1. A database sounds great, but why don't we just store all our data in one big table in an Excel spreadsheet?Example: Can you foresee an
E N D
1. Data Management & Data Warehouses MIS 320
Kraig Pencil
Fall 2010 1
2. Game Plan Introduction
Why use a relational database?
Database management systems
Data warehouses
Data mining
Data marts 2
3. A. Why use a relational database? 1. A database sounds great, but why don’t we just store all our data in one big table in an Excel spreadsheet?
Example: Can you foresee any hassles or potential difficulties associated with entering/storing order information in the following Excel table? 3
4. A. Why use a relational database? 1. Why don’t we just store our data in one spreadsheet table? (cont.)
Potential problems
May have “redundant” data entry
Potential for data entry errors (different/wrong phone numbers)
Updates can be a hassle/inefficient (e.g., change phone no)
Solution
“Normalize” the data …
? Break up the table into a set of linked tables in a data base (instead of having one spreadsheet)
See example 4
5. Example: Normalized Tables(and the advantages of a database) 5
6. Example: Non-Normalized Data Table for an Auto Shop (Rainer & Turban, Fig 4.6)
7. B. Database Management Systems 1. What is a “database management system” (DBMS)?
SW that allows one to create, store, organize, manage, and use data
Example of a DBMS?
2. Key components
Data Definition subsystem
Data Manipulation subsystem
Application Generation subsystem
Data Administration subsystem
DBMS engine
7
8. DBMS Components 8
9. B. Database Management Systems 3. Examples of DBMS components in Access
Data Definition subsystem
Data dictionary (“Design view” for a table)
Data Manipulation subsystem: Move, change, and “ask questions”
View of a table (“Datasheet view” for a table)
Query-by-example (QBE) tool
Structured query language (SQL)
Application Generation subsystem: the “front end”
Design of forms and reports
Data Administration subsystem
Optimize query performance
Security settings with password 9
10. B. Database Management Systems 4. What aspects of data need to be specified?
Lots of aspects!!!
Recall table creation in MS Access (Tutorials 1 & 2)
Common data properties
Data “type” (number, text, date, etc.)
Description
Field size
Required/not required
Etc.
An important reference for a database system:
? Data dictionary
Stores information about the data in a database 10
11. Access Example:
12. Access Example: Data Manipulation Subsystem (Low Stock Products query)
13. Access Example: Application Generation Subsystem (Employer Information Form)
14. Access Example: Data Administration (Performance Analysis for a Database)
15. B. Database Management Systems (cont.) 5. DBMS: Example products
You are very likely to work with – and possibly help develop a database– using one or more of the following:
Small-Midsize DBMS: Microsoft Access, dBase, Paradox
Mid-to-Large DBMS: Microsoft SQL Server, Oracle, DB2, Informix, IMS 15
16. C. Data Warehouses Business problem:
Difficult for larger organizations to analyze organizational data from multiple sources
Solution: Data warehouse
Gather/integrate information from existing operational databases into a “warehouse”
? Create “Business Intelligence” system
See next figure 16
17. Create a Data Warehouse from Operational Databases 17
18. C. Data Warehouses (cont.) 3. Data warehouse features
Designed to support business decision making
Not transactions!
Supports OLAP
On-line Analytical Processing
Crosses functional boundaries of an organization
Can be very large
Note: Warehouse is “read only”
Why?
Can be a significant strategic resource for a company
Can yield a high ROI
Examples
??? 18
19. C. Data Warehouses (cont.) Implementation issues
People may be reluctant to share information
“ETL” process is not easy
Extraction, transformation, load
Expensive 19
20. D. Data Mining Provides a means to extract patterns and relationships from large amount of data (e.g., a data warehouse)
Mining analogy
Sift through raw dirt/rock to find something of value
Large volumes of data are sifted in an attempt to find something worthwhile
Example: market basket analysis
Identify products that may be attractive to a customer
See next slide: Amazon.com buyer suggestions 20
22. D. Data mining (cont.) 4. Identify previously unknown patterns
e.g., What are characteristics of customers likely to default on a bank loan?
e.g., Suppose you discovered that beer and diapers*were often found in the same purchase?
“Market basket analysis”
What could you do with that information to improve sales of one, the other or both?
*This is a common example, not an actual case. 22
23. E. Data Marts 5. Data marts
Warehouses can be overwhelming/difficult to implement …
? Some organizations create “data marts”
A subset of a data warehouse
Simpler, scaled-down version
Focuses on/Integrates a specific area (e.g., Sales department)
Provides useful decision making tools
23
24. Data Marts: Subsets of Data Warehouse 24