Chapter 6 Foundations of Business Intelligence. Databases and Information Management

Chapter 6 Foundations of Business Intelligence: Databases and Information Management What Is a Database? • Database: – Collection of related files containing records on people, places, or things • Entity: – Generalized category representing person, place, thing – E.g., SUPPLIER, PART • Attributes: – Specific characteristics of each entity:  SUPPLIER name, address  PART description, unit price, supplier Databases and Transaction Processing • Firms use the web to make information from their internal databases available to customers and partners. • Middleware and other software make this possible – Web server – Application servers or CG I – Database server • Web interfaces provide familiarity to users and savings over redesigning legacy systems. Figure 6.16 Linking Internal Databases to the Web Relational Databases • Organize data into two-dimensional tables (relations) with columns and rows • One table for each entity: – E.g., (CUSTOMER, SUPPLIER, PART, SALES) – Fields (columns) store data representing an attribute – Rows store data for separate records, or tuples • Key field: uniquely identifies each record • Primary key Establishing Relationships • Entity-relationship diagram – Used to clarify table relationships in a relational database • Referential integrity rules – Ensure that relationships between coupled tables remain consistent – Example of a one-to-many relationship Figure 6.2 A Relational Database Table Figure 6.3 The PART Table Figure 6.5 Sample Purchase Order Report Figure 6.6 The Final Database Design with Sample Records Figure 6.7 Entity-Relationship Diagram for the Database with Four Tables Database Management Systems (DBMS) • Software for creating, storing, organizing, and accessing data from a database • Separates the logical and physical views of the data – Logical view: how end users view data – Physical view: how data are actually structured and organized • Examples: Microsoft Access, DB2, Oracle Database, Microsoft SQ L Server, MySQL Figure 6.8 Human Resources Database with Multiple Views Operations of a Relational DBMS • Select: – Creates a subset of all records meeting stated criteria • Join: – Combines relational tables to present the server with more information than is available from individual tables • Project: – Creates a subset consisting of columns in a table – Permits user to create new tables containing only desired information Figure 6.9 The Three Basic Operations of a Relational DBMS Figure 6.10 Access Data Dictionary Features Figure 6.11 Example of an SQL Query SELECT PART.Part_Number, PART.Part_Name, SUPPLIER.Supplier_Number, SUPPLIER.Supplier_Name FROM PART, SUPPLIER WHERE PART.Suplier_Number = SUPPLIER.Supplier_Number AND Part_Number = 137 OR Part_Number = 150; Figure 6.12 An Access Query Big Data • Massive quantities of unstructured and semi-structured data from Internet and more – 3Vs: Volume, variety, velocity – Petabytes and exabytes • Big datasets offer more patterns and insights than smaller datasets, e.g. – Customer behavior – Weather patterns • Requires new technologies and tools Non-Relational Databases • “NoSQL” • Handle large data sets of data that are not easily organized into tables, columns, and rows • Use more flexible data model – Don’t require extensive structuring • Can manage unstructured data, such as social media and graphics • E.g. Amazon’s Simple D B, MetLife’s Mongo D B Cloud Databases and Distributed Databases • Relational database engines provided by cloud computing services – Pricing based on usage – Appeal to small or medium-sized businesses • Amazon Relational Database Service – Offers MySQL, Microsoft SQL Server, Oracle Database engines • Distributed databases – Stored in multiple physical locations – Google’s Spanner cloud service Blockchain • Distributed database of transactions • Operates on a network without central authority • Maintains a growing list of records called blocks • Once recorded, blocks cannot be changed • Reduces cost of processing transactions and enhances security Figure 6.13 How Blockchain Works Business Intelligence Infrastructure • Array of tools for obtaining useful information from internal and external systems and big data – Data warehouses – Data marts – Hadoop – In-memory computing – Analytical platforms Data Warehouses • Data warehouse: – Database that stores current and historical data that may be of interest to decision makers – Consolidates and standardizes data from many systems, operational and transactional databases – Data can be accessed but not altered • Data mart: – Subset of data warehouses that is highly focused and isolated for a specific population of users Hadoop • Open-source software framework for big data • Breaks data task into sub-problems and distributes the processing to many inexpensive computer processing nodes • Combines result into smaller data set that is easier to analyze • Key services – Hadoop Distributed File System (HDFS) – MapReduce Figure 6.14 Business Intelligence Technology Infrastructure Analytical Tools: Relationships, Patterns, Trends • Once data is gathered, tools are required for consolidating, analyzing, to use insights to improve decision making – Software for database querying and reporting – Multidimensional data analysis (O L AP ) – Data mining Online Analytical Processing (OLAP) • Supports multidimensional data analysis, enabling users to view the same data in different ways using multiple dimensions – Each aspect of information—product, pricing, cost, region, or time period—represents a different dimension – E.g., comparing sales in East in June versus May and July • Enables users to obtain online answers to ad hoc questions such as these in a fairly rapid amount of time Figure 6.15 Multidimensional Data Model Data Mining • Finds hidden patterns and relationships in large databases and infers rules from them to predict future behavior • Types of information obtainable from data mining – Associations: occurrences linked to single event ex: change in system because of Covid – Sequences: events linked over time – Classifications: patterns describing a group an item belongs to – Clustering: discovering as yet unclassified groupings – Forecasting: uses series of values to forecast future values Text Mining • Unstructured data (mostly text files) accounts for 80 percent of an organization’s useful information. • Text mining allows businesses to extract key elements from, discover patterns in, and summarize large unstructured data sets. • Sentiment analysis – Mines online text comments online or in email to measure customer sentiment Web Mining • Discovery and analysis of useful patterns and information from the web – E.g. to understand customer behavior, evaluate website, quantify success of marketing • Content mining – mines content of websites • Structure mining – mines website structural elements, such as links • Usage mining – mines user interaction data gathered by web servers