1 / 38

Chapter 3 and Module C

Chapter 3 and Module C. DATABASES AND DATA WAREHOUSES Supporting the Analytics-Driven Organization. Opening Case: Did You Know CDs Come from Dead Dinosaurs?. In 2010, more than half of all music was in digital form; physical music will never again be the norm. INTRODUCTION.

urban
Download Presentation

Chapter 3 and Module C

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. Chapter 3 and Module C DATABASES AND DATA WAREHOUSES Supporting the Analytics-Driven Organization

  2. Opening Case: Did You Know CDs Come from Dead Dinosaurs? In 2010, more than half of all music was in digital form; physical music will never again be the norm

  3. INTRODUCTION • Business intelligence (BI) • Knowledge about your customers, competitors, business partners, competitive environment, and internal operations to make effective, important, and strategic business decisions • Analytics • Fact-based decision-making • Integrated use of IT and statistical techniques to create BI

  4. Data Processing • IT tools help process information to create business intelligence according to… • OLTP • OLAP

  5. Data Processing • Online transaction processing (OLTP) • The gathering and processing transaction information, and updating existing information to reflect the transaction • Databases support OLTP • Operational database – databases that support OLTP • Online analytical processing (OLAP) • The manipulation of information to support decision making • Databases can support some OLAP • Data warehouses only support OLAP, not OLTP • Data warehouses are special forms of databases that support decision making and help build BI

  6. OLTP, OLAP, and Business Intelligence

  7. THE RELATIONAL DATABASE MODEL • There are many types of databases • The relational database model is the most popular • Relational database

  8. Database Characteristics Collections of information Created with logical structures Include logical ties within the information Include built-in integrity constraints

  9. 1. Database – Collection of Information

  10. 2. Database – Logical Structure • Character • Field • Record • File (Table) • Database • Data Warehouse

  11. Logical Structure: Character • Character • Field • Record • File (Table) • Database • Data Warehouse

  12. Logical Structure: Field • Character • Field • Record • File (Table) • Database • Data Warehouse

  13. Logical Structure: Record • Character • Field • Record • File (Table) • Database • Data Warehouse

  14. Logical Structure: File • Character • Field • Record • File (Table) • Database • Data Warehouse

  15. Logical Structure: Database • Character • Field • Record • File (Table) • Database • Data Warehouse

  16. Database – Physical Structure • Bits • Bytes • Words

  17. Databases – Created with Logical Structures • Databases have many tables • In databases, the row number is irrelevant; not true in spreadsheet software • In databases, column names are very important. Column names are created in the data dictionary

  18. Database – Created with Logical Structures • Data dictionary – contains the logical structure for the information in a database Before you can enter information into a database, you must define the data dictionary for all the tables and their fields. For example, when you create the Truck table, you must specify that it will have three pieces of information and that Date of Purchase is a field in Date format.

  19. 3. Databases – With Logical Ties Within the Information • Logical ties must exist between the tables or files in a database • Logical ties are created with primary and foreign keys • Primary key (PK) • Composite primary key (CPK) • Foreign key (FK)

  20. Database – Logical Ties within the Information Customer Number is the primary key for Customer and appears in Order as a foreign key

  21. Logical Ties – Keys • A PK and a FK do not have to have the same name. • If a record can be uniquely identified with only one PK, then the file should only have one. • A PK is required (or CPKs) for each file. • A FK may or may not exist for each file. • All CPKs do not have to be FKs.

  22. 4. Databases – Built-In Integrity Constraints • Integrity constraints – rules that help ensure the quality of the information • Examples • Primary keys must be unique • Foreign keys must be present • Sales price cannot be negative • Phone number must have area code

  23. Steps in Developing a Database • Step 1: Define Entity Classes (tables) and Primary Keys • Step 2: Defining Relationships Among Entity Classes • ERD (entity relationship diagram) • Normalization: (1) eliminate M:M; (2) fields must depend on PK; (3) no derived fields • Step 3: Defining Information For Each Relation • Step 4: Use A Data Definition Language To Create Your Database

  24. DATABASE MANAGEMENT SYSTEM TOOLS

  25. 5 Components of a DBMS • DBMS engine • Data definition subsystem • Data manipulation subsystem • Views • Report generators • QBE tools • SQL • Application generation subsystem • Data administration subsystem

  26. View • View – allows you to see the contents of a database file, make changes, and query it to find information

  27. Report Generator • Report generator – helps you quickly define formats of reports and what information you want to see in a report

  28. Query-by-Example Tool • QBE tool – helps you graphically design the answer to a question

  29. Structured Query Language SQL – standardized fourth-generation query language found in most DBMSs Sentence-structure equivalent to QBE Mostly used by IT professionals Non-procedural language, which makes it different from other programming languages

  30. DATA WAREHOUSES AND DATA MINING • Data warehouses support OLAP and decision making • Data warehouses do not support OLTP • Data warehouse • Data mart • Data-mining

  31. Data Warehouse Example

  32. Data Mart Example

  33. Data-Mining Tools

  34. Data Warehouse Considerations Do you really need one, or does your database environment support all your functions? Do all employees need a big data warehouse or a smaller data mart? How up-to-date must the information be? What data-mining tools do you need?

  35. INFORMATION OWNERSHIP • Information is a resource you must manage and organize to help the organization meet its goals and objectives • You need to consider • Strategic management support • Sharing information with responsibility • Information cleanliness

  36. Strategic Management Support • Data administration – function that plans for, oversees the development of, and monitors the information resource • Database administration – function responsible for the more technical and operational aspects of managing organizational information

  37. Sharing Information Everyone can share – while not consuming – information But someone must “own” it by accepting responsibility for its quality and accuracy

  38. Information Cleanliness Related to ownership and responsibility for quality and accuracy No duplicate information No redundant records with slightly different data, such as the spelling of a customer name GIGO – if you have garbage information you get garbage information for decision making

More Related