Chapter 3 Databases PDF

CHAPTER 3 DATABASE SYSTEMS, DATA WAREHOUSES, AND DATA MARTS Hossein BIDGOLI MIS, Chapter 3 ©2011 Course...

CHAPTER 3 DATABASE SYSTEMS, DATA WAREHOUSES, AND DATA MARTS Hossein BIDGOLI MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 1  Chapter 3 Database Systems, Data Warehouses, and Data Marts learning outcomes LO1 Define a database and a database management system. LO2 Explain logical database design and the relational database model. LO3 Define the components of a database management system. LO4 Summarize recent trends in database design and use. LO5 Explain the components and functions of a data warehouse. MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 2  Chapter 3 Database Systems, Data Warehouses, and Data Marts l e a r n i n g o u t c o m e s (cont’d.) LO6 Describe the functions of a data mart. MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 3  Chapter 3 Database Systems, Data Warehouses, and Data Marts Databases Database – Collection of related data that can be stored in a central location or in multiple locations – Usually a group of files File – Group of related records – All files are integrated Record – Group of related fields Data hierarchy MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 4 Exhibit 3.1 Data Hierarchy MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 5  Chapter 3 Database Systems, Data Warehouses, and Data Marts Databases (cont’d.) Critical component of information systems – Any type of analysis that’s done is based on data available in the database Database management system (DBMS) – Creating, storing, maintaining, and accessing database files Advantages over a flat file system MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 6 Exhibit 3.2 Interaction between the User, DBMC, and Database MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 7  Chapter 3 Database Systems, Data Warehouses, and Data Marts Types of Data in a Database Internal data – Collected within organization External data – Sources MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 8  Chapter 3 Database Systems, Data Warehouses, and Data Marts BI in Action: Law Enforcement Business intelligence (BI) – Used in law enforcement as well as in the business world Richmond, Virginia – System generates BI reports that help pinpoint crime patterns – Allocate manpower to days and locations where crime likely to occur MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 9  Chapter 3 Database Systems, Data Warehouses, and Data Marts Methods for Accessing Files Sequential file structure – Records organized and processed in numerical or sequential order – Organized based on a “primary key” – Usually used for backup and archive files Because they need updating only rarely Random access file structure – Records can be accessed in any order – Fast and very effective when a small number of records need to be processed daily or weekly MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 10  Chapter 3 Database Systems, Data Warehouses, and Data Marts Methods for Accessing Files (cont’d.) Indexed sequential access method (ISAM) – Records accessed sequentially or randomly – Depending on the number being accessed Indexed access – Uses an index structure with two parts: Indexed value Pointer to the disk location of the record matching the indexed value MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 11  Chapter 3 Database Systems, Data Warehouses, and Data Marts Logical Database Design Physical view – How data is stored on and retrieved from storage media Logical view – How information appears to users – How it can be organized and retrieved – Can be more than one logical view MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 12  Chapter 3 Database Systems, Data Warehouses, and Data Marts Logical Database Design (cont’d.) Data model – Determines how data is created, represented, organized – Includes: Data structure Operations Integrity rules Hierarchical model – Relationships between records form a treelike structure MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 13 Exhibit 3.3 A Hierarchical Model MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 14  Chapter 3 Database Systems, Data Warehouses, and Data Marts Logical Database Design (cont’d.) Network model – Similar to the hierarchical model – Records are organized differently MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 15 Exhibit 3.4 A Network Model MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 16  Chapter 3 Database Systems, Data Warehouses, and Data Marts The Relational Model Relational model – Uses a two-dimensional table of rows and columns of data Data dictionary – Field name – Field data type – Default value – Validation rule MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 17  Chapter 3 Database Systems, Data Warehouses, and Data Marts The Relational Model (cont’d.) Primary key – Unique identifier Foreign key – Establishes relationships between tables Normalization – Improves database efficiency – Eliminates redundant data – 1NF through 3NF (or 5NF) MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 18  Chapter 3 Database Systems, Data Warehouses, and Data Marts The Relational Model (cont’d.) Data retrieval – Select – Project – Join – Intersection – Union – Difference MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 19  Chapter 3 Database Systems, Data Warehouses, and Data Marts Components of a DBMS Database engine Data definition Data manipulation Application generation Data administration MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 20  Chapter 3 Database Systems, Data Warehouses, and Data Marts Database Engine Heart of DBMS software Responsible for data storage, manipulation, and retrieval Converts logical requests from users into their physical equivalents MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 21  Chapter 3 Database Systems, Data Warehouses, and Data Marts Data Definition Create and maintain the data dictionary Define the structure of files in a database – Adding fields – Deleting fields – Changing field size – Changing data type MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 22  Chapter 3 Database Systems, Data Warehouses, and Data Marts Data Manipulation Add, delete, modify, and retrieve records from a database Query language – Structured Query Language (SQL) Standard fourth-generation query language used by many DBMS packages SELECT statement – Query by example (QBE) Construct statement of query forms Graphical interface MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 23  Chapter 3 Database Systems, Data Warehouses, and Data Marts Application Generation Design elements of an application using a database – Data entry screens – Interactive menus – Interfaces with other programming languages MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 24  Chapter 3 Database Systems, Data Warehouses, and Data Marts Data Administration Used for: – Backup and recovery – Security – Change management Create, read, update, and delete (CRUD) Database administrator (DBA) – Individual or department – Responsibilities MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 25  Chapter 3 Database Systems, Data Warehouses, and Data Marts Recent Trends in Database Design and Use Data-driven Web sites Natural language processing Distributed databases Client/server databases Object-oriented databases MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 26  Chapter 3 Database Systems, Data Warehouses, and Data Marts Data-Driven Web Sites Data-driven Web site – Interface to a database – Retrieves data and allows users to enter data Improves access to information Useful for: – E-commerce sites that need frequent updates – News sites that need regular updating of content – Forums and discussion groups – Subscription services, such as newsletters MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 27  Chapter 3 Database Systems, Data Warehouses, and Data Marts Distributed Databases Distributed database – Data is stored on multiple servers placed throughout an organization Reasons for choosing Approaches for setup – Fragmentation – Replication – Allocation Security issues MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 28  Chapter 3 Database Systems, Data Warehouses, and Data Marts Client/Server Databases Client/server database – Users’ workstations (clients) linked in a local area network (LAN) to share the services of a single server – Server processes data – Returns only records meeting request MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 29  Chapter 3 Database Systems, Data Warehouses, and Data Marts Object-Oriented Databases Object-oriented database – Object consists of attributes and methods Encapsulation – Grouping objects along with their attributes and methods into a class Inheritance – New objects can be created faster and more easily by entering new data in attributes Interaction with an object-oriented database takes places via methods MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 30  Chapter 3 Database Systems, Data Warehouses, and Data Marts Data Warehouses Data warehouse – Collection of data used to support decision-making applications and generate business intelligence Multidimensional data Characteristics – Subject oriented – Integrated – Time variant – Type of data – Purpose MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 31  Chapter 3 Database Systems, Data Warehouses, and Data Marts Input Variety of sources – External – Databases – Transaction files – ERP systems – CRM systems MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 32  Chapter 3 Database Systems, Data Warehouses, and Data Marts ETL Extraction, transformation, and loading (ETL) Extraction – Collecting data from a variety of sources – Converting data into a format that can be used in transformation processing Transformation processing – Make sure data meets the data warehouse’s needs Loading – Process of transferring data to the data warehouse MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 33 Exhibit 3.9 A Data Warehouse Configuration MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 34  Chapter 3 Database Systems, Data Warehouses, and Data Marts Storage Raw data Summary data Metadata MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 35  Chapter 3 Database Systems, Data Warehouses, and Data Marts Output Data warehouse supports different types of analysis – Generates reports for decision making Online analytical processing (OLAP) – Generates business intelligence – Uses multiple sources of information and provides multidimensional analysis – Hypercube – Drill down and drill up MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 36 Exhibit 3.10 Slicing and Dicing Data MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 37  Chapter 3 Database Systems, Data Warehouses, and Data Marts Output (cont’d.) Data-mining analysis – Discover patterns and relationships Reports – Cross-reference segments of an organization’s operations for comparison purposes – Find patterns and trends that can’t be found with databases – Analyze large amounts of historical data quickly MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 38  Chapter 3 Database Systems, Data Warehouses, and Data Marts Data Warehouse Applications at InterContinental Hotels Group (IHG) The new system has increased the company’s query response time from hours to minutes It has generated valuable BI on both its customers and the competition Future plans include the migration of financial data, which will enable IHG to perform side-by- side analyses of operations, marketing, sales, and financial data MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 39  Chapter 3 Database Systems, Data Warehouses, and Data Marts Data Marts Data mart – Smaller version of data warehouse – Used by single department or function Advantages over data warehouses More limited scope than data warehouses MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 40  Chapter 3 Database Systems, Data Warehouses, and Data Marts Summary Databases – Accessing files – Design principles – Components – Recent trends Data warehouses and data marts MIS, Chapter 3 ©2011 Course Technology, a part of Cengage Learning 41