1 / 63

Chapter 3

Chapter 3. Databases and Data Warehouses. STUDENT LEARNING OUTCOMES. Describe business intelligence and its role in an organization. Differentiate between databases and data warehouses with respect to their focus on OLTP and OLAP.

jodie
Download Presentation

Chapter 3

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 Databases and Data Warehouses

  2. STUDENT LEARNING OUTCOMES • Describe business intelligence and its role in an organization. • Differentiate between databases and data warehouses with respect to their focus on OLTP and OLAP. • List and describe the key characteristics of a relational database.

  3. STUDENT LEARNING OUTCOMES • Define the five software components of a database management system. • List and describe the key characteristics of a data warehouse. • Define the four major types of data-mining tools in a data warehouse environment. • List key considerations in information ownership in an organization.

  4. Can Companies Keep Your Personal Information Secure and Private? • Databases and data warehouses are organizational repositories of information • Much of the information is personal • It must be secure • If hackers get your personal information, you can suffer from identity theft

  5. Can Companies Keep Your Personal Information Secure and Private? • Top-10 incidents of personal information loss by organizations • Could affect over 53 million people • CardSystems lost information on 40 million customers • Many others

  6. Can Companies Keep Your Personal Information Secure and Private? • Have you been a victim of identity theft? • What happened? • What did you do to recover? • How long did it take?

  7. INTRODUCTION • Businesses need business intelligence (BI) • Business intelligence – knowledge about your customers, competitors, business partners, environment, and internal operations • Enables effective decision making • Information on steroids

  8. INTRODUCTION • IT tools help process information to create business intelligence according to… • OLTP (online transaction processing) • OLAP (online analytical processing)

  9. INTRODUCTION • OLTP – gathering and processing transaction information and updating existing information to reflect transaction • Databases support OLTP • Operational database – database that supports OLTP

  10. INTRODUCTION • OLAP – manipulation of information to support decision making • Databases can help some • Data warehouses support only OLAP, not OLTP • Data warehouses – special forms of databases that support decision making

  11. INTRODUCTION

  12. INTRODUCTION • This chapter – database and data warehouse concepts • Along with some privacy and security considerations

  13. RELATIONAL DATABASE MODEL • Database – logical collection of information you organize and access according to the logical structure of the information • Relational database – uses a series of two-dimensional tables or files to store information in the form of a database

  14. Databases Are… • Collections of information • Created with logical structures • With logical ties within the information • With built-in integrity constraints

  15. Databases – Collections of Information • Databases have many tables • Solomon Enterprises as a concrete provider. Tables include: • Order • Customer • Concrete Type • Employee • Truck

  16. Databases – Collections of Information

  17. Databases – Created with Logical Structures • In databases, row numbers are irrelevant • In databases, columns have logical names such as Order Date and Customer Name • Data dictionary – contains the logical structure of the information in a database

  18. Databases – Logical Ties within the Information • Logical ties must exist between the tables • Logical ties are created with primary and foreign keys • Primary key – field (or group of fields in some cases) that uniquely describe each record

  19. Databases – Logical Ties within the Information • Foreign key – primary key of one file that appears in another file • Foreign keys help create relationships among tables • Table = file = relation (don’t confuse yourself)

  20. Databases – Logical Ties within the Information

  21. Databases – Built-in Integrity Constraints • Integrity constraint – rule that helps ensure the quality of information • Examples • Primary keys must be unique • Foreign keys cannot be blank • Sales price cannot be negative • Phone numbers must have an area code

  22. DBMS TOOLS • Database management system (DBMS) – helps you specify the logical organization for a database and access and use the information within a database • Word processing software = document • Spreadsheet software = workbook • DBMS software = database

  23. DBMS TOOLS • 5 software components • DBMS engine • Data definition subsystem • Data manipulation subsystem • Application generation subsystem • Data administration subsystem

  24. DBMS TOOLS

  25. DBMS Engine • DBMS engine – accepts logical requests, converts them into their physical equivalent, and accesses the database and data dictionary • DBMS engine separates the logical from the physical

  26. DBMS Engine • Physical view – how information is arranged, stored, and accessed on a storage device • Logical view – how you (knowledge worker) need to arrange and access information • Databases – you work only with logical views

  27. Data Definition Subsystem • Data definition subsystem – helps you create and maintain the data dictionary and define the structure of the files in a database • Must create data dictionary for a database before entering any information

  28. Data Manipulation Subsystem • Data manipulation subsystem – helps you add, change, and delete information • Primary interface between you and a database • Views • Report generators • QBE tools • SQL

  29. Views • View – allows you to see the contents of a database file • Similar to a spreadsheet view • Make changes • Sort • Query

  30. Views

  31. Report Generators • Report generator – helps you quickly define formats of reports and what information you want to see in a report • Save report formats to use later • Uses a wizard interface

  32. Report Generators Specify the layout of the report Specify the fields you want in a report

  33. Report Generators

  34. QBE Tools • Query-by-example (QBE) tool – helps you graphically design the answer to a question • “What driver most often delivers concrete to Triple A Homes?”

  35. QBE Tools

  36. SQL • Structured query language (SQL) – standardized fourth-generation language found in most DBMSs • Performs same task as QBE • Uses sentence structure instead • Mostly used by IT people

  37. Application Generation Subsystem • Application generation subsystem – contains facilities to help you develop transaction-intensive applications • Data entry screens (called forms in Access) • Programming languages • Mostly used by IT people

  38. Data Administration Subsystem • Data administration subsystem – helps you manage the overall database environment • Backup and recovery • Security management • Query optimization • Concurrency control • Change management

  39. Data Administration Subsystem • Backup and recovery • Periodically back up information • Recover a database after a failure • Security management • Who has access to what information • Who can perform CRUD tasks on information

  40. Data Administration Subsystem • Query optimization • Restructure physical view to optimize response times to queries • Concurrency control • What happens if two people simultaneously try to change the same information?

  41. Data Administration Subsystem • Change management • What is the effect of structural changes to a database? • What if you add a new column? • What happens if you delete a column? • What happens if you change a column’s attributes?

  42. DATA WAREHOUSES & DATA MINING • Data warehouses support OLAP and decision making • Data warehouses do not support OLTP • Data-mining tools are tools for working with data warehouse information • DBMS software = database • Data-mining tools = data warehouse

  43. What Is a Data Warehouse? • Data warehouse – logical collection of information – gathered from operational databases – used to create business intelligence that supports business analysis activities and decision-making tasks

  44. What Is a Data Warehouse?

  45. What Is a Data Warehouse? • Multidimensional • Rows and columns • Also layers • Many times called hypercubes • What are the dimensions in Figure 3.8 on page 97?

  46. What Are Data-Mining Tools? • Data-mining tools – software tools that you use to query information in a data warehouse • Query-and-reporting tools • Intelligent agents • Multidimensional analysis tools • Statistical tools

  47. What Are Data-Mining Tools?

  48. Query-and-Reporting Tools • Query-and-reporting tools – similar to QBE tools, SQL, and report generators in the typical database environment • Also similar to pivot tables in Excel

  49. Intelligent Agents • Use various AI tools such as neural networks and fuzzy logic to form the basis for “information discovery” and building BI • Help you find hidden patterns in information • Chapter 4 focuses on these

  50. Multidimensional Analysis Tools • Multidimensional analysis (MDA) tools – slice-and-dice techniques that allow you to view multidimensional information from different perspectives • Bring new layers to the front • Reorganize rows and columns

More Related