1 / 23

Data Management Data Warehouses

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

jenna
Download Presentation

Data Management Data Warehouses

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    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

More Related