Page 1 of 62
Database Concepts
8th Edition
David M. Kroenke • David J. Auer • Scott L. Vandenberg • Robert C. Yoder
Instructor’s Manual
Prepared by Robert C. Yoder and David J. Auer
CHAPTER EIGHT
BIG DATA, DATA WAREHOUSES, AND
BUSINESS INTELLIGENCE SYSTEMS
Page 2 of 62
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted,
in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior
written permission of the publisher. Printed in the United States of America.
Instructor’s Manual to accompany:
Database Concepts (8th Edition)
David M. Kroenke • David J. Auer • Scott L. Vandenberg • Robert C. Yoder
Chapter Eight –Data Warehouses, Business Intelligence Systems, and Big Data
Page 3 of 62
CHAPTER OBJECTIVES
• Learn the basic concepts of data warehouses and data marts
• Learn the basic concepts of dimensional databases
• Learn the basic concepts of business intelligence (BI) systems
• Learn the basic concepts of Online Analytical Processing (OLAP)
• Learn the basic concepts of virtualization and virtual machines
• Learn the basic concepts of cloud computing
• Learn the basic concepts of Big Data, structured storage, and the MapReduce
process
CHAPTER ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
be reported and corrected in the online DBC e08 Errata document, which will be
available at http://www.pearsonhighered.com/kroenke.
THE ACCESS WORKBENCH
Solutions to the Access Workbench exercises may be found in Solutions to all Sections:
The Access Workbench, which is a separate document within the Instructor’s Manual.
NOTES ON MICROSOFT WINDOWS 10
This book uses the Microsoft Windows 10 operating system as the basis for screenshots
and step-by-step instructions. However, with Windows 10, Microsoft has introduced a
continuous update system that has already resulted in some fundamental differences in
how different versions of Windows 10 look and operate.
For example, in the original version of Microsoft Windows 10, clicking the Windows
Start button (or pressing the Windows key on the keyboard) displayed the menu shown
in Figure 1. In this menu, we need to click the All apps button in order to see the All
apps menu shown in Figure 2.
Chapter Eight –Data Warehouses, Business Intelligence Systems, and Big Data
Page 4 of 62
Figure 1 – Windows 10 Main Menu
Figure 2 – Windows 10 All Apps Menu
The All apps button
The File Explorer button
The File Explorer icon
The All apps menu
Chapter Eight –Data Warehouses, Business Intelligence Systems, and Big Data
Page 5 of 62
Figure 3 – Windows 10 Anniversary Update Main Menu with All Apps Menu Included
Microsoft then released the Windows 10 Anniversary Update (Feature update to
Windows 10, version 1607) (see the blog discussion at
https://blogs.windows.com/windowsexperience/2016/08/02/how-to-get-the-windows–10–
anniversary-update/#K1CZuiw4auiuE9A5.97 ). One of the changes introduced in the
Anniversary Update was a major change to the menu system. Now, as shown in
Figure 3, the All apps menu is immediately available when the Start button is used (or
the keyboard Windows key is pressed).
Therefore, note that the step by step instructions in this book may need to be altered for
your use depending upon which version of Microsoft Windows 10 you or your students
are using!
We recommend that you update Windows 10 to the Windows 10 Anniversary Update
(Feature update to Windows 10, version 1607), and make sure it is patched with all
updates to that version (at a minimum patched to Windows 10 Version 1607 update for
August 23, 2016 (KB3176936), and the Windows 10 Version 1607 cumulative update for
September 29, 2016 (KB3194496). We also recommend using the 32-bit version of
Microsoft Office. This insures that all the examples discussed in this book will function
properly.
The All apps menu
The File Explorer button
The File Explorer icon
Chapter Eight –Data Warehouses, Business Intelligence Systems, and Big Data
Page 6 of 62
TEACHING SUGGESTIONS
• This chapter introduces some advanced topics of database processing using
business intelligence (BI) systems. Each of these topics is only briefly touched upon
in this chapter. There is more information on each of them Database Processing:
Fundamentals, Design, and Implementation (14th Edition), Upper Saddle River:
Prentice Hall (2016).
• The topic of Big Data is a rapidly evolving one, and a lot of new developments may
have occurred by the time this textbook is in use in the classroom. Do some research
on the topics of Big Data, NoSQL, Cassandra, Hadoop, and related topics discussed
in the chapter to make sure you have current information for your class.
• Explain to your students that business Intelligence (BI) systems already have an
important role in business operations, and the importance of this role should only
increase over time. If you know of any local examples, use them to illustrate your
point.
• Ask the students to think about ways in which data can be made more useful to
decision makers. How can data be made more relevant? Think about a continuum
of ways that data can be brought closer—downloading, data warehouses, etc.
• Many firms refer to their data or their information system as a data warehouse.
Discuss the integration required to be considered a warehouse, then explain why
others would not be considered warehouses.
• If every department wants to download data, the management problems become
immense. Data warehousing is an attempt to centralize and specialize the skills and
facilities for bringing data closer to end-users.
• OLAP is becoming important for the analysis of operational data, especially when
examples can be done using the Microsoft Excel PivotTable feature.
• Explain to your students that virtualization technology has enabled cloud computing
to be economical, and cloud computing in turn has contributed to the development of
Big Data. Many workplaces in the near future will be using some form of
virtualization, cloud, and Big Data technologies. Having knowledge of these will be a
career-booster.
ANSWERS TO REVIEW QUESTIONS
8.1 What are BI systems?
8.2 How do BI systems differ from transaction processing systems?
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 7 of 62
8.3 Name and describe the two main categories of BI systems.
8.4 What are the three sources of data for BI systems?
BI systems obtain data in three ways:
8.5 Summarize the problems with operational databases that limit their usefulness for BI
applications.
8.6 What is an ETL system, and what functions does it perform?
8.7 What problems in operational data create the need to clean data before loading the data
into a data warehouse?
The problems that inhibit the usefulness of operational database for BI applications are:
8.8 What does it mean to transform data? Give an example other than the ones used in this
book.
8.9 Why are data warehouses necessary?
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 8 of 62
8.10 Give examples of data warehouse metadata.
8.11 Explain the difference between a data warehouse and a data mart. Give an example
other than the ones used in this book.
Figure 8-6 below reflects this pattern.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 9 of 62
8.12 What is the enterprise data warehouse (EDW) architecture?
8.13 Describe the differences between operational databases and dimensional databases.
The differences are summarized in Figure 8-7:
8.14 What is a star schema?
A star schema is a database design that uses a denormalized design to store historical data. It is
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 10 of 62
8.15 What is a fact table? What type of data are stored in fact tables?
8.16 What is a fact table measure?
8.17 What is a dimension table? What types of data are stored in dimension tables?
8.18 What is a slowly changing dimension?
8.19 Why is the time dimension important in a dimensional model?
8.20 What is a conformed dimension?
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 11 of 62
8.21 What does OLAP stand for?
8.22 What is the distinguishing characteristic of OLAP reports?
8.23 Define measure, dimension, and cube in the context of OLAP reports?
8.24 Give an example, other than ones in this text, of a measure, two dimensions related to
your measure, and a cube.
We will use PropertySales as a measure.
Dimensions would be the attributes in a relational database table, such as Category, Type, etc.
Data as stored in a relational database table would be shown as:
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 12 of 62
Here is an example of a cube:
8.25 What is drill down?
8.26 Explain two ways that the OLAP report in Figure 8-19 differs from that in Figure 8-18.
In Figure 8-19, the user has:
8.27 Define distributed database.
A distributed database is a database that:
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 13 of 62
8.28 Explain one way to partition a database that has three tables: T1, T2, and T3.
Assume we have three database servers: S1, S2, and S3. One way to partition the database is by
putting one table on each server:
8.29 Explain one way to replicate a database that has three tables: T1, T2, and T3.
Assume we have three database servers: S1, S2, and S3. One way to replicate the database is by
putting all three tables on each server:
8.30 Explain what must be done when fully replicating a database but allowing only one
computer to process updates.
If only one computer accepts updates, the copies of the updates must be periodically sent to the
servers holding the other replicas. The challenges are:
8.31 If more than one computer can update a replicated database, what three problems can
occur?
If more than one computer can update a replicated database, then:
8.32 What solution is used to prevent the problems in question 8.31?
8.33 Explain what problems can occur in a distributed database that is partitioned but not
replicated.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 14 of 62
8.34 What organizations should consider using a distributed database?
Replicated, read-only databases present few problems, but distributed databases should only be
used by organizations that have:
8.35 Explain the meaning of the term object persistence.
8.36 In general terms, explain why relational databases are difficult to use for object
persistence.
8.37 What does OODBMS stand for, and what is its purpose?
8.38 According to this chapter, why were OODBMSs not successful?
8.39 What is an object-relational database?
8.40 What is Big Data?
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 15 of 62
8.41 What is the relationship between 1 MB of storage and 1 EB of storage?
8.42 What is the NoSQL movement?
The NoSQL movement is now usually referred to as the Not only SQL movement. It is the
movement from relational Big Data databases to the use of non-relational DBMSs, often known
8.43 What were the first nonrelational data stores to be developed, and who developed it?
8.44 What is Cassandra, and what is the history of the development of Cassandra to its
current state?
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 16 of 62
8.45 As illustrated in Figure 8-22, what is column family database storage and how are such
systems organized? How do column family database storage systems compare to
RDMS systems?
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 17 of 62
A column family database storage system is shown in Figure 8-22. The structured storage
equivalent of a relational DBMS (RDBMS) table has a very different construction. Although
similar terms are used, they do not mean the same thing that they mean in a relational DBMS.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 18 of 62
8.46 Explain MapReduce processing.
8.47 What is Hadoop, and what is the history of the development of Hadoop to its current
state? What are HBase and Pig?
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 19 of 62
Hadoop originated as part of Cassandra, but the Hadoop project has spun off a nonrelational
data store of its own called HBase and a query language named Pig.
8.48 What is virtualization?
8.49 What is a hypervisor, and what is the difference between a type 1 hypervisor and a
type 2 hypervisor?
8.50 What is cloud computing? What major technology enables cloud computing?
8.51 What are the differences between SaaS, PaaS, and IaaS?
These are the three basic ways to rent cloud resources. The simplest is Software As A Service
(SaaS), when a company leases usage of applications software that is typically accessed using a
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 20 of 62
ANSWERS TO EXERCISES
8.52 Based on the discussion of the Heather Sweeney Designs operational database (HSD)
and dimensional database (HSD-DW) in the text, answer the following questions.
A. Using the SQL statements shown in Figures 8-10, create the HSD-DW database
in a DBMS.
B. What transformations of data were made before HSD-DW was loaded with data?
List all the transformations, showing the original format of the HSD data and how
they appear in the HSD-DW database
NOTE: These are the actual transformations, and do not include the same data column
in different tables (for example, LINE_ITEM.Quantity appears as PRODUCT-
SALES.Quantity, but there is no change either in the column name or data type).
C. Write the complete set of SQL statements necessary to load the transformed
data into the HSD-DW database.
See Figure 8-11, that generates the following files. Be sure to run them in this order:
D. Populate the HSD-DW database, using the SQL statements you wrote to answer
part C.
E. Figure 8-24 shows the SQL code to create the SALES_FOR_RFM fact table
shown in Figure 8-15. Using those statements, add the SALES_FOR_RFM table
to your HSD-DW database.