Databases and Data Warehouses

1. 3 Databasesand Data Warehouses

2. Knowledge Workers Checklist • logical vs. physical organization • databases & software to manage them • relational model concepts • data warehouses & data mining • managing the information resource

3. Organizations do 3 things with Information • process transactions • OLTP • make decisions • OLAP • manage the information • logical vs. physical

4. A Database is: • a collection of information • organized • accessed by logical structure

5. Components of a Database • information itself • in the form of records • data dictionary • primary key for each record • foreign keys that relate files • built-in integrity constraints

6. ADataBase Management System • is software • contains 5 critical components • DBMS engine • data definition subsystem • data manipulation subsystem • application generation subsystem • data administration subsystem

7. DBMS Engine • most important but seldom recognized • separates logical from physical

8. Data Definition • builds data dictionary • builds logical database structure

9. views report generators QBE tools SQL Data Manipulation

10. build forms build data entry screens create transaction processing Application Generation

11. Data Administration Subsystem • backup and recovery • security management • query optimization • reorganization facilities • concurrency control • change management

12. Relational Database Model • most widely used • tables store data • intuitive for humans • entity class –people, places or things • instance – one of an entity • i.e. an employee, a city, a baseball game

13. Book book-code title GW4-5 Blazing Sun BS7-8 Bright Star PQ1-2 Cattle Run PQ1-3 Cattle Skip Customer cus-num name 100 Poulos 114 Simmons 115 Thompson Purchase cus-num book-code date 114 BS7-8 9/16/01 100 GW4-5 9/17/01 114 PQ1-3 9/17/01 114 GW4-5 9/17/01 115 PQ1-2 9/17/01 100 BS7-8 9/18/01 entities? instances? primary keys? foreign keys? An example: Small-Town Book Store

14. Object-Oriented Model • an object is a software module • run by OODB • combines information and procedures • procedures are reusable

15. Data Warehouses and Data Mining • support OLAP • support decision making

16. Data Warehouse • information gathered from many databases • multidimensional • support OLAP, not OLTP

17. Data Mining Tools • query-and-reporting tools • intelligent agents • multidimensional analysis tools • support information discovery

18. Managing Information • determine type of models & databases • CIO serves as overseer • who owns the information? • ethical concerns • maintenance (outsource?)

19. Summary • differences exist between the physical and logical view of information • databases have a significant role in organizations • data dictionaries hold the logical structure of databases • software to run databases is called a database management system • the relational database model is the most widely used • relations store information about an entity class • databases use primary and foreign keys • data warehouses and data mining tools help discover new information to support decision making • types of databases include: centralized, distributed, external, online, knowledge worker and operational • oversight of an organization’s information is often done by a CIO