• Home

Database systems

Introduction to Database Systems

Project Description

This project is based on the material in textbook Chapter 3 – Chapter 5, and Appendix A sections 2 – 5. All the references on figures, chapters/sections

The database for Wedgewood Pacific (WP) has been discussed extensively in Chapter 3 as well as in the “Working with Microsoft Access” in Chapter 1 and Chapter 2.

Complete the following four tasks using MySQL (Community Server 8.0 and the Workbench).

Create one document file (Microsoft Word or PDF format) that contains all your SQL scripts (in text format) and clear screenshots (with brief explanation) for all 4 parts.

1. Create the Wedgewood Pacific (WP) database as described in Chapter 3. This will include:

a. Creating the WP schema, and setting it as the default schema.

b. Creating a folder to hold SQL scripts for the WP schema in the C:/Documents/MySQL Workbench/Schemas folder.

c. Creating and running an SQL script named WP-Create-Tables based on Figure 3-7 (page

152) to create the WP table structure.

d. Creating and running an SQL script named WP-Insert-Datas based on Figure 3-11 (pages 159 – 161) to populate the WP tables.

What to turn in?

· Provide screenshots (similar to Figure A-19 and Figure A-20) with a brief explanation to demonstrate that you have completed this task.

2. Write an SQL query to answer the bolded question below based on the WP database that you have created in part 1.

Who are the employees assigned to projects run by the Sales and Marketing Department? The result should be sorted by ProjectID in ascending order, and contain the following information: ProjectID, ProjectName, Department, DepartmentPhone, EmployeeNumber, LastName, FirstName, and OfficePhone.

What to turn in?

· A copy of your SQL script (in text format, not screenshot image);

· A screenshot of the results of running the query.

3. Wedgewood Pacific (WP) has decided to keep track of computers used by the employees. To do so, two new tables are added to the database. The schema for these tables, as related to the existing EMPLOYEE table, along with the referential integrity constraints, are shown in question WA.3.3 (pages 241 – 244). In addition, Figure 3-31, Figure 3-32, Figure 3-33, and Figure 3-34 are the corresponding database column characteristics for the tables and table data.

The schema for these tables is (note that we are purposely excluding the recursive relationship in EMPLOYEE at this time):

The referential integrity constraints:

Write an SQL query to answer the following question:

Who is currently using which computer at WP?

The result should be sorted first by Department and then by employee LastName, and contain the following information: SerialNumber, Make, Model, EmployeeID, LastName, FirstName, Department, and OfficePhone.

What to turn in?

· A copy of your SQL script (in text format, not screenshot image);

· A screenshot of the results of running the query.

4. Using an IE Crow’s Foot E-R diagram, Figure 5-17 (page 365) and Figure A-67 (page A-73) show the database design for the Wedgewood Pacific database (including the recursive relationship for EMPLOYEE) in MySQL Workbench. See Appendix A section 5 (pages A-56 to A-74) for more details.

Use MySQL Workbench to enhance this E-R diagram (Figure 5-17 or Figure A-67) with the COMPUTER and COMPUTER_ASSIGNMENT tables as mentioned in the previous part.

What to turn in?

· A screenshot of the completed E-R diagram.

Database systems

Database Concepts
9th Edition

David M. Kroenke • David J. Auer • Scott L. Vandenberg • Robert C. Yoder

Online Extension A

Working with MySQL

Z04_KROE8149_09_SE_EXTA.indd 1 04/07/19 8:15 PM

Vice President of Courseware Portfolio Management: Andrew Gilfillan
Executive Portfolio Manager: Samantha Lewis
Team Lead, Content Production: Laura Burgess
Content Producer: Faraz Sharique Ali
Portfolio Management Assistant: Bridget Daly
Director of Product Marketing: Brad Parkins
Director of Field Marketing: Jonathan Cottrell
Product Marketing Manager: Heather Taylor
Field Marketing Manager: Bob Nisbet
Product Marketing Assistant: Liz Bennett
Field Marketing Assistant: Derrica Moser
Senior Operations Specialist: Diane Peirano

Senior Art Director: Mary Seiner
Interior and Cover Design: Pearson CSC
Cover Art: Artwork by Donna Auer
Senior Product Model Manager: Eric Hakanson
Manager, Digital Studio: Heather Darby
Course Producer, MyLab MIS: Jaimie Noy
Digital Studio Producer: Tanika Henderson
Full-Service Project Manager: Gowthaman Sadhanandham
Full Service Vendor: Integra Software Service Pvt. Ltd.
Manufacturing Buyer: LSC Communications, Maura Zaldivar-Garcia
Text Printer/Bindery: LSC Communications
Cover Printer: Phoenix Color

Credits and acknowledgments borrowed from other sources and reproduced, with permission, in this textbook appear on the appropriate page
within text.

Microsoft and/or its respective suppliers make no representations about the suitability of the information contained in the documents and
related graphics published as part of the services for any purpose. All such documents and related graphics are provided “as is” without
warranty of any kind. Microsoft and/or its respective suppliers hereby disclaim all warranties and conditions with regard to this information,
including all warranties and conditions of merchantability, whether express, implied or statutory, fitness for a particular purpose, title and
non-infringement. In no event shall Microsoft and/or its respective suppliers be liable for any special, indirect or consequential damages or any
damages whatsoever resulting from loss of use, data or profits, whether in an action of contract, negligence or other tortious action, arising out
of or in connection with the use or performance of information available from the services.

The documents and related graphics contained herein could include technical inaccuracies or typographical errors. Changes are periodically
added to the information herein. Microsoft and/or its respective suppliers may make improvements and/or changes in the product(s) and/or the
program(s) described herein at any time. Partial screen shots may be viewed in full within the software version specified.

Microsoft® Windows®, and Microsoft Office® are registered trademarks of the Microsoft Corporation in the U.S.A. and other countries. This
book is not sponsored or endorsed by or affiliated with the Microsoft Corporation.

MySQL®, the MySQL Command Line Client®, the MySQL Workbench®, and the MySQL Connector/ODBC® are registered trademarks
of Sun Microsystems, Inc./Oracle Corporation. Screenshots and icons reprinted with permission of Oracle Corporation. This book is not
sponsored or endorsed by or affiliated with Oracle Corporation.

Oracle Database XE 2016 by Oracle Corporation. Reprinted with permission.

PHP is copyright The PHP Group 1999–2012, and is used under the terms of the PHP Public License v3.01 available at http://www.php.net/
license/3_01.txt. This book is not sponsored or endorsed by or affiliated with The PHP Group.

ArangoDB is a copyright of ArangoDB GmbH.

Copyright © 2020, 2017, 2015 by Pearson Education, Inc. 221 River Street, Hoboken, NJ 07030. All rights reserved. Manufactured in the
United States of America. This publication is protected by Copyright, and permission should be obtained from the publisher prior to any
prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying,
recording, or likewise.

For information regarding permissions, request forms and the appropriate contacts within the Pearson Education Global Rights & Permissions
Department, please visit www.pearsoned.com/permissions. Ackn-owledgments of third-party content appear on the appropriate page within the text,
which constitutes an extension of this copyright page. Unless otherwise indicated herein, any third-party trademarks that may appear in this work are
the property of their respective owners and any references to third-party trademarks, logos or other trade dress are for demonstrative or descriptive
purposes only. Such references are not intended to imply any sponsorship, endorsement, authorization, or promotion of Pearson’s products by the
owners of such marks, or any relationship between the owner and Pearson Education, Inc. or its affiliates, authors, licensees or distributors.

Library of Congress Cataloging-in-Publication Data

Names: Kroenke, David M., author. | Auer, David J., author. |
Vandenberg, Scott L., author. | Yoder, Robert C., author.

Title: Database concepts/David M. Kroenke, David J. Auer, Western
Washington University, Scott L. Vandenberg, Siena College, Robert C.
Yoder, Siena College.

Description: Ninth edition. | New York, NY : Pearson, [2020] | Includes
index.
Identifiers: LCCN 2018052988 | ISBN 9780135188149 | ISBN 0135188148
Subjects: LCSH: Database management. | Relational databases.
Classification: LCC QA76.9.D3 K736 2020 | DDC 005.74–dc23 LC record available at
https://lccn.loc.gov/2018052988

1 19

ISBN 10: 0-13-518814-8
ISBN 13: 978-0-13-518814-9

Z04_KROE8149_09_SE_EXTA.indd 2 04/07/19 8:15 PM

A-1

EX
TE

N
S
IO

N

■ Learn how to install MySQL Community Server 8.0

■ Learn how to install MySQL Workbench

■ Learn how to install MySQL for Excel

■ Learn how to install the MySQL Connector/ODBC

■ Learn how to install the MySQL Connector/NET

■ Learn how to install MySQL documentation

■ Learn how to create a database in MySQL 8.0

■ Learn how to create and run SQL scripts in MySQL 8.0

■ Learn how to submit SQL commands to create table
structures

■ Learn how to submit SQL commands to insert database data

■ Learn how to submit SQL commands to query a database

■ Learn how to import Microsoft Excel worksheet data into
a database

■ Learn how to create database designs in the MySQL
Workbench

■ Learn how to use the database administrator functions
of MySQL Workbench

■ Learn how to use MySQL database data to create a
Microsoft Excel PivotTable

■ Learn how to use advanced SQL features in MySQL 8.0

■ Learn how to use Microsoft Access as an application
development platform

A Working with MySQL
CHAPTER OBJECTIVES

WHAT IS THE PURPOSE OF THIS EXTENSION?
Until recently, the relational database (defined and discussed in Chapter 1 and Chapter 2)
and its associated database management systems (DBMSs) were the dominant database
systems in computer information systems. Many major DBMS products, including Micro-
soft SQL Server, Oracle Database, IBM DB2, Oracle MySQL, and even Microsoft Access,
are relational databases. In recent years, the non-relational database has become a signifi-
cant player in the field. Used in online apps such as Facebook and Twitter, the importance
of the non-relational database is growing. Non-relational databases, often misidentified as
NoSQL (Not only SQL) databases, are discussed in Chapter 7 and online Extension C,
“Advanced Business Intelligence and Big Data.”

However, relational database DBMS products continue to hold a significant place in
transaction processing and other business systems, and they will not be replaced soon. A
knowledge of relational databases and how to use them will be an important part of your
career in business information systems for some time to come. Therefore, it is important
to study and learn a relational database in depth, and we think that MySQL is an excellent
place to start.

MySQL Community Server is an open source, freely downloadable, enterprise-class
DBMS that has been around for many years. In November 2005, MySQL 5.0 was released,
and, as of this writing, MySQL 8.0 is the generally available (GA) release. In February
2008, Sun Microsystems completed its acquisition of MySQL AB, the company that cre-
ated and owned MySQL. Subsequently, Oracle Corporation acquired Sun Microsystems

Z04_KROE8149_09_SE_EXTA.indd 1 04/07/19 8:16 PM

A-2 Online Extension A Working with MySQL

in a deal that was finalized on January 27, 2010, after European Commission approval on
January 21, 2010 (see http://www.sun.com/third-party/global/oracle/). Thus, the Oracle
Corporation now owns MySQL in addition to its flagship Oracle Database product.1
MySQL, while not having as many features as SQL Server, has become widely used and
very popular as a DBMS supporting Web sites running the Apache Web server. The
MySQL Community Server version and MySQL Workbench graphical user interface
(GUI) utility are free.

Why Should I Learn to Use MySQL?
For the purposes of this book, the most important reason to learn to use MySQL is that
MySQL really handles SQL well. All the SQL commands and keywords in Chapter 3 and
online Extension B, “Advanced SQL,” marked “Does Not Work with Microsoft Access
ANSI-89 SQL” will work with MySQL. There will still be minor variations in some SQL
statements, but this is typical of DBMS products.

What Will This Extension Teach Me?
This extension is designed to get you started creating databases and running SQL commands
and SQL scripts (which are related groups of SQL commands) so that you can use a more
robust SQL environment than provided by Microsoft Access.

What Won’t This Extension Teach Me?
The material in this extension does not go beyond the information necessary to get you
started using MySQL. There are many important MySQL topics not covered here, includ-
ing stored procedures, triggers, and backups and restores. Some of these topics are covered
in online Chapter 10C of David M. Kroenke, David J. Auer, Scott M. Vandenberg, and
Robert C. Yoder Database Processing: Fundamentals, Design, and Implementation, 15th edi-
tion (Upper Saddle River, NJ: Pearson, 2019).

How Should I Use This Extension?
The material in this extension is grouped into sections that correspond to the chapters and
online extensions in Database Concepts, 9th edition. As you read each chapter, you should
read and work through the corresponding section in this extension. Specifically, the sections
of this extension and the chapters in the book are matched as shown in Figure A-1.

The overall structure of this extension is intended to parallel the work done in the sec-
tions of “Working with Microsoft Access” in the book itself. However, a perfect one-to-one
correspondence is not possible, so the sections of “Working with MySQL” present similar
MySQL material and topics in a logical manner.

We recommend that you study online Extension B, “Advanced SQL,” immediately
after Chapter 3. “Advanced SQL” contains a section on importing spreadsheet data into
a database table, and the detailed instructions for doing this in MySQL are in Section 4 of
“Working with MySQL.” Therefore, you should work through Section 4 while studying
that section of “Advanced SQL.” Other topics in “Advanced SQL” are covered in Section B
of “Working with MySQL.”

1For information about Oracle Database Express Edition 11g Release 2, see online Chapter 10B in David
M. Kroenke, David J. Auer, Scott M. Vandenberg, and Robert C. Yoder, Database Processing: Fundamentals,
Design, and Implementation, 15th edition (Upper Saddle River, NJ: Pearson, 2019).

Z04_KROE8149_09_SE_EXTA.indd 2 04/07/19 8:16 PM

Online Extension A Working with MySQL A-3

SECTION 1 – INSTALLING MySQL COMMUNITY SERVER 8.0
In Chapter 1, we introduce some important concepts of database systems. In this section
of Working with MySQL, we will install MySQL Community Server 8.0 and its associated
utilities and documentation. When you have completed this section, you will have a func-
tional MySQL 8.0 installation, and be ready to create and use a database in Section 2.

The MySQL Community Edition is intended for general use and can be downloaded
for free. MySQL runs on the Microsoft Windows operating system, various Linux distribu-
tions, and even Apple’s macOS. The various versions of Microsoft SQL Server (including
the free SQL Server 2017 Developer Edition and Express Edition), like all other Microsoft
products, run only on Microsoft operating systems. If you are not using a Microsoft operat-
ing system, MySQL is an obvious contender for your DBMS of choice. In fact, it is the only
major DBMS product that runs on macOS, which makes it a great teaching environment
because all students will be able to find a version that runs on their operating system!

IMPORTANT: If you are using a non-Windows operating system (e.g., a Linux
distribution or macOS), then you need to download and install at least the first two of the
following five programs listed on the next page. We recommend that you install all five.

IMPORTANT: If you are using a Windows operating system, you should only install
these programs by using the MySQL Installer for Windows, which can be downloaded
from http://dev.mysql.com/downloads/installer. Download the current file named mysql-
installer-community-8.0.##.#.msi (Do not download the mysql-installer-web-community-
8.0.##.# file). We are downloading and using version 8.0.16.0, which is current at the time of
this writing (we used version 8.0.14.0 in the book itself). Do not try to install the programs
separately on a Windows OS computer! If you do so, you may find that some functionality or
connectivity between the programs is missing!

Database Concepts Chapter/Extension Working with MySQL Section
Chapter 1

Getting Started
Section 1

Installing MySQL Community Server 8.0

Chapter 2
The Relational Model

Section 2
Creating and Using a MySQL Database

Chapter 3
Structured Query Language

Section 3
Using SQL in MySQL

Chapter 4
Data Modeling and the Entity-Relationship

Model

Section 4
Importing Microsoft Excel Data

into a MySQL Database

Chapter 5
Database Design

Section 5
Creating a Database Design on MySQL

Workbench

Chapter 6
Database Administration

Section 6
Database Administration in MySQL

Chapter 7
Data Warehouses, Business

Intelligence Systems,
and Big Data

Section 7
Business Intelligence Systems

Using MySQL

Online Extension B
Advanced SQL

Section B
Advanced SQL in MySQL

Online Extension C
Advanced Business Intelligence and Big Data

Section C
Advanced BI and Big Data in MySQL

FIGURE A-1

Corresponding Book Chapters and Sections in Working with MySQL

Z04_KROE8149_09_SE_EXTA.indd 3 04/07/19 8:16 PM

A-4 Online Extension A Working with MySQL

1. MySQL Community Server Edition.2 Download the most current generally available ver-
sion for your operating system. As this extension was written, MySQL Community Server
8.0 was the most recent version generally available. Therefore, we will base this extension
on that version.

2. MySQL Workbench.3 The MySQL Workbench is the graphical administration and SQL
command utility for MySQL. In addition, it can be used for creating database designs as
described in Chapter 5.

3. MySQL Connector/ODBC. This provides the ODBC programs necessary to provide ap-
plication connectivity to a MySQL database, as described in later in this extension, and in
Chapter 7.

4. MySQL Connector/NET. This provides a MySQL driver for Microsoft ADO.NET and is
necessary to provide application connectivity to a MySQL database as described later in
this extension.

5. MySQL for Excel. This provides the Microsoft Excel component needed to export data
in Microsoft Excel into a table in a MySQL database, as described later in this extension.
Before installing MySQL for Excel, you must install Microsoft Visual Studio 2010 Tools
for Office Runtime, as described below.

MySQL and its associated utilities are very easy to install. At this point, install and
configure MySQL Community Server 8.0 and the MySQL workbench by (1) using the
MySQL Installer for Windows if you are using a Microsoft Windows OS (see the detailed
steps below) or (2) downloading and installing the separate programs for non-Windows
OSs. Install MySQL Community Server 8.0 as a Developer Machine when asked during the
installation process. MySQL Workbench will also be installed as part of the process. We
will discuss the MySQL Connector/ODBC, the MySQL Connector/NET, and MySQL
for Excel later in this extension. We will also install the documentation of MySQL as part
of our installation process.

Note that during the installation and configuration of MySQL Community Server, you
will be asked to provide a password for the root user account. Root is the name of the

2This extension, which was finalized after Database Concepts (9th edition) itself went to press, uses MySQL
Community Server 8.0 version 8.0.16.0. The screenshots in Database Concepts itself are based on MySQL
Community Server 8.0 version 8.0.14.0. You may notice some slight variations in the screenshots from the
two versions.

3This extension, which was finalized after Database Concepts (9th edition) itself went to press, uses MySQL
Workbench 8.0.16 CE. The screenshots in Database Concepts Chapter 5 are based on MySQL Workbench
8.0.14 CE. You may notice some slight variations in the screenshots from the two versions.

If you look at the various Web sites for MySQL, you will find that the name of
the MySQL Community Server edition varies from place to place. On the main
Oracle MySQL Web site (see http://www.mysql.com/products), it is referred to as
MySQL Community Edition. On the MySQL Web site that we use to download the
MySQL products (see http://dev.mysql.com/downloads/mysql), it is referred to as
both MySQL Community Edition and MySQL Community Server. On the MySQL
Web site that we use to download the MySQL Installer for Windows (see http://
dev.mysql.com/downloads/installer), it is referred to as just the MySQL Server.

So, what shall we call it? In Database Concepts (9th edition) and all the
accompanying online Extensions, we use the term MySQL Community Server.

BTW

Z04_KROE8149_09_SE_EXTA.indd 4 04/07/19 8:16 PM

Online Extension A Working with MySQL A-5

default MySQL administrator account. Be sure you provide the password when prompted,
and be sure to remember this password! The username Root comes from the UNIX and
Linux operating systems, where it is the name of the default system administrator account.
Be aware that MySQL 8.0 is an enterprise-class DBMS, and it is therefore much more
complex than Microsoft Access. Further, it does not include application development tools,
such as form and report generators.

If you are using a Microsoft Windows operating system, we recommend that you
download the MySQL Installer for Windows, which packages current versions of MySQL
Community Server 8.0, the MySQL Workbench, several MySQL connectors (including the
recommended MySQL Connector/ODBC and Connector/NET), other utilities (such as
MySQL for Excel), samples, examples, and documentation together with an installation
utility that controls which products are actually installed.

The MySQL Installer for Windows can be downloaded from http://dev.mysql.com/
downloads/. There is a separate version of the MySQL Installer for Windows for each major
version of MySQL, and the version we are using here is for MySQL 8.0. Download the current
file named mysql-installer-community-8.0.##.#.msi (Do not download the mysql- installer-web-
community-8.0.##.# file). We are downloading and using version 8.0.16.0, which is current at
the time of this writing. Before running the MySQL Installer for Window, you need to:

1. Install the .Net Framework 3.5. This can be done in Control Panel. Select Programs |
Programs and Features | Turn Windows features on or off to launch the Add Roles and
Features Wizard to install .NET Framework 3.5 in Features.

2. Download and install the latest version of .NET Framework. As this extension is being
written, this .NET Framework 4.8 which is downloadable from https://dotnet.microsoft.
com/download/dotnet-framework/net48. Scroll down to the Advanced downloads section,
and download the offline installer version labeled Runtime in the Run apps – Runtime
column.

3. Download and install the Microsoft Visual C++ Redistributable Package for Visual
Studio 2019 (32-bit or 64-bit version depending upon your operating system) from
https://visualstudio.microsoft.com/downloads/ (look in the Other Tools and Frameworks
section of the Web page). Windows 10 may already come with or have been updated to
the 2017 Redistributable Package.

4. Download and install the Microsoft Visual Studio 2010 Tools for Office (32-bit or 64-bit
version depending upon your operating system) from https://www.microsoft.com/en-
us/download/details.aspx?id=48217. This is needed for the MySQL for Excel utility.

■ NOTE: This application must be installed as part of the MySQL 8.0 installation—see
the details in the Installing MySQL Community Server 8.0 steps below. Also note un-
like the installation of the previous two packages, this installation may not suggest re-
starting your computer. Nonetheless, you should restart it anyway, or the MySQL for
Excel utility may not show up in the list of available MySQL applications during the
MySQL 8.0 installation itself.

5. IMPORTANT! Before installing MySQL, you will need to know whether your version
of Microsoft Access is 32-bit or 64-bit, so that we can install the correct version of the
Connector/ODBC and Connector/NET drivers. To do this:

a. Start Microsoft Access 2019 and double-click the Blank desktop database template
icon. Click the File command tab, and the Account to get the Office Product
Information Screen. Then click the About Access button.

b. The About Microsoft Access 2019 dialog box is displayed, and the version (32-bit
or 64-bit) appears at the end of the very top line. Make a note of which version it
is, and then click the OK button.

c. Close Microsoft Access 2019.
d. Note that when we double-clicked the Blank desktop database icon we created a

Microsoft Access 2019 database named Database1.accdb in the This PC | Documents
folder. We will not use this database, so open File Explorer and delete it. Close File
Explorer when you have done so.

Z04_KROE8149_09_SE_EXTA.indd 5 04/07/19 8:16 PM

A-6 Online Extension A Working with MySQL

The installation instructions below are based on a first-time, clean installation
of MySQL. Your installation of MySQL will install the current versions of these
products, which may have different versions of the products shown here. That is
okay, as you will want to have the most current versions of the software installed.

After you have installed MySQL, you should check regularly for updated
versions of the MySQL release you are using. For systems running a Windows
OS, this functionality is built into the MySQL Installer for Windows, and you
should use this utility to check for updates and then update your MySQL
installation. Note that the MySQL Installer for Windows checks for updates
for all MySQL products, not just MySQL Server.

However, that being said, we have run into problems with the MySQL
Installer successfully and completely performing the update. Therefore, we
recommend that you uninstall MySQL completely and run a new installation of
the new version. In order to uninstall MySQL, you must:

1. Backup any database schemas and data you have in your existing
installation of MySQL. See Section 6 of “Working with MySQL” on
Database Administration for instructions on how to do this (and how to
restore that material after you have reinstalled MySQL 8.0).

2. Uninstall the installed MySQL components using the Windows 10 Con-
trol Panel (type Control Panel into the Windows search text box on the
Task Bar, and then click the icon to launch the program).

3. Remove the folder C:\Program Files\MySQL and any contents of the
folder that remain after uninstalling MySQL (it should be empty).

4. Remove the folder C:\ProgramData\MySQL and any contents of the folder that
remain after uninstalling MySQL (it should be empty). This folder is normally
a hidden folder—use the Windows File Explorer View | Options command to
change settings on hidden folders and files. You may need to restart your
computer before deleting this folder if any files in it are still in use.

For non-Windows OS installations, check the version number on the
appropriate Downloads page. We need to check for updates because these
updates are used in lieu of service packs and patches to make sure your
installation is as secure as possible. There should be no problem installing a
newer version of MySQL over an older version of the same release.

BTW

Installing MySQL Community Server 8.0

1. To start the actual installation process, run the downloaded mysql-installer-community-
8.0.16.0.msi file from your browser window, or open File Explorer and double-click the
file in the Downloads folder. When the User Account Control dialog box for the file
mysql-installer-community-8.0.16.0.msi is displayed, click the Yes button. When the User
Account Control dialog box for the file MySQLInstallerLauncher.exe is displayed, click
the Yes button.

2. The MySQL Installer dialog box opens, and the License Agreement screen is displayed, as
shown in Figure A-2(a). Read the agreement, check the I accept the license terms check-
box, and then click the Next button.

3. The Choosing a Setup Type screen is displayed, as shown in Figure A-2(b). At this point
we need to install the Microsoft Visual Studio 2010 Tools for Office, so click the Cancel
button to cancel the installation of MySQL and close the MySQL Installer.

4. The MySQL Installer Cancel? dialog box is displayed to confirm that you want to cancel
the installation. Click the Yes button.

Z04_KROE8149_09_SE_EXTA.indd 6 04/07/19 8:16 PM

Online Extension A Working with MySQL A-7

FIGURE A-2

Installing MySQL 8.0

The MySQL
Installer dialog box

The MySQL Installer
License Agreement
screen

Check the I accept
the license terms
checkbox

The Next button

(a) The MySQL Installer License Agreement Screen

FIGURE A-2

Installing MySQL 8.0

The MySQL Installer
Choosing a Setup
Type screen

Click the Custom
radio button

The Next button

(b) The MySQL Installer Choosing a Setup Type Screen

MySQL Installer, Oracle Corporation.

MySQL Installer, Oracle Corporation.

Z04_KROE8149_09_SE_EXTA.indd 7 04/07/19 8:16 PM

A-8 Online Extension A Working with MySQL

5. If you have not already done so, download the Microsoft Visual Studio 2010 Tools for
Office from https://www.microsoft.com/en-us/download/details.aspx?id=48217. In you
Downloads folder, right-click the downloaded vstor_redist.exe file, and then click Run as
Administrator in the shortcut menu to start the installation.

6. After the Microsoft Visual Studio 2010 Tools for Office installation is complete, restart
your computer.

7. In your Downloads folder, double-click the mysql-installer-community-8.0.16.0.msi file.
When a User Account Control dialog box for the file MySQLInstallerLauncher.exe is
displayed, click the Yes button.

8. Because you have already accepted the MySQL Installer license agreement in step 2 above,
the Choosing a Setup Type screen is displayed, as shown in Figure A-2(b). Since we want to
install the minimum set of MySQL components, select the Custom radio button, and click
the Next button.

9. The Select Products and Features screen is displayed, as shown in Figure A-2(c). The first
selection will be MySQL Community Server 8.0 itself. Expand the MySQL Servers option
as shown in the figure. Select the version of MySQL Server 8.0 that matches your operat-
ing system (we are using a 64-bit version of Windows 10, so the MySQL Installer shows
us the x64 version – to determine which one you are using, click the Windows Start but-
ton, then click the Settings icon in the Windows menu to open the Windows Settings app
and click System | About), and then click the right-facing arrow button to add MySQL
Server 8.0 to the Products/Features To Be Installed list.

10. Staying on the Select Products and Features screen, expand the Applications options as
shown in Figure A-2(d). Select the version of MySQL Workbench 8.0 that matches your
operating system (we are using a 64-bit version of Windows 10, so the MySQL Installer
shows us the x64 version), and then click the right facing arrow button to add MySQL
Workbench to the Products/Features To Be Installed list. Next select MySQL for Excel,
and add it to the Products/Features To Be Installed list. We will use it later in this extension
to import Microsoft Excel data into a MySQL database.

FIGURE A-2

Installing MySQL 8.0

The MySQL Installer
Select Products and
Features screen

Select MySQL
Server 8.0—choose
the version for your
operating system

Click this button to add
your selection to
Products/Features To
Be Installed

The Next button

(c) The MySQL Installer Select Products and Features Screen
MySQL Installer, Oracle Corporation.

Z04_KROE8149_09_SE_EXTA.indd 8 04/07/19 8:16 PM

Online Extension A Working with MySQL A-9

■ NOTE: If you don’t see the MySQL for Excel option, you have not installed
Microsoft Visual Studio 2010 Tools for Office correctly. Go back to step 3 of these
instructions, and start where it reads “At this point we need to install the Microsoft
Visual Studio 2010 Tools for Office . . .”

11. Staying on the Select Products and Features screen, expand the MySQL Connectors options
as shown in Figure A-2(e). Based on whether you have a 32-bit or 64-bit version of Microsoft
Access 2019 (as you determined earlier in this extension), select the matching versions of
Connector/ODBC 8.0 and Connecter/NET 8.0, and then click the right-facing arrow but-
ton to add Connector/ODBC 8.0 and Connector/NET 8.0 to the Products/Features To Be
Installed list. Select 32-bit ODBC drivers if your version of Microsoft Access is 32-bit, 64-bit
drivers if your Access version is 64-bit. Installing the correct version will avoid having driver
mismatch problems between Windows 10 (64-bit) and Microsoft Access (can be 32-can or 64-
bit). REMINDER: If you are using a Windows operating system, you should only install the
MySQL Connector/ODBC and Connector/NET (and any of the other MySQL Connectors
available from MySQL if you need them) by using the MySQL Installer for Windows.

12. Staying on the Select Products and Features screen, expand the Documentation Options
as shown in Figure A-2(f). Select the MySQL Documentation 8.0 option then the 8.0.16
sub-option (at this time there is only a 32-bit version available – it will also run on a
64-bit operating system), and then click the right-facing arrow button to add MySQL
D

Database systems

Database Concepts
9th Edition

David M. Kroenke • David J. Auer • Scott L. Vandenberg • Robert C. Yoder

Online Extension B

Advanced SQL

Z05_KROE8149_09_SE_EXTB.indd 1 04/07/19 8:23 PM

Vice President of Courseware Portfolio Management: Andrew Gilfillan
Executive Portfolio Manager: Samantha Lewis
Team Lead, Content Production: Laura Burgess
Content Producer: Faraz Sharique Ali
Portfolio Management Assistant: Bridget Daly
Director of Product Marketing: Brad Parkins
Director of Field Marketing: Jonathan Cottrell
Product Marketing Manager: Heather Taylor
Field Marketing Manager: Bob Nisbet
Product Marketing Assistant: Liz Bennett
Field Marketing Assistant: Derrica Moser
Senior Operations Specialist: Diane Peirano

Senior Art Director: Mary Seiner
Interior and Cover Design: Pearson CSC
Cover Art: Artwork by Donna Auer
Senior Product Model Manager: Eric Hakanson
Manager, Digital Studio: Heather Darby
Course Producer, MyLab MIS: Jaimie Noy
Digital Studio Producer: Tanika Henderson
Full-Service Project Manager: Gowthaman Sadhanandham
Full Service Vendor: Integra Software Service Pvt. Ltd.
Manufacturing Buyer: LSC Communications, Maura Zaldivar-Garcia
Text Printer/Bindery: LSC Communications
Cover Printer: Phoenix Color

Credits and acknowledgments borrowed from other sources and reproduced, with permission, in this textbook appear on the appropriate page
within text.

Microsoft and/or its respective suppliers make no representations about the suitability of the information contained in the documents and
related graphics published as part of the services for any purpose. All such documents and related graphics are provided “as is” without
warranty of any kind. Microsoft and/or its respective suppliers hereby disclaim all warranties and conditions with regard to this information,
including all warranties and conditions of merchantability, whether express, implied or statutory, fitness for a particular purpose, title and
non-infringement. In no event shall Microsoft and/or its respective suppliers be liable for any special, indirect or consequential damages or any
damages whatsoever resulting from loss of use, data or profits, whether in an action of contract, negligence or other tortious action, arising out
of or in connection with the use or performance of information available from the services.

The documents and related graphics contained herein could include technical inaccuracies or typographical errors. Changes are periodically
added to the information herein. Microsoft and/or its respective suppliers may make improvements and/or changes in the product(s) and/or the
program(s) described herein at any time. Partial screen shots may be viewed in full within the software version specified.

Microsoft® Windows®, and Microsoft Office® are registered trademarks of the Microsoft Corporation in the U.S.A. and other countries. This
book is not sponsored or endorsed by or affiliated with the Microsoft Corporation.

MySQL®, the MySQL Command Line Client®, the MySQL Workbench®, and the MySQL Connector/ODBC® are registered trademarks
of Sun Microsystems, Inc./Oracle Corporation. Screenshots and icons reprinted with permission of Oracle Corporation. This book is not
sponsored or endorsed by or affiliated with Oracle Corporation.

Oracle Database XE 2016 by Oracle Corporation. Reprinted with permission.

PHP is copyright The PHP Group 1999–2012, and is used under the terms of the PHP Public License v3.01 available at http://www.php.net/
license/3_01.txt. This book is not sponsored or endorsed by or affiliated with The PHP Group.

ArangoDB is a copyright of ArangoDB GmbH.

Copyright © 2020, 2017, 2015 by Pearson Education, Inc. 221 River Street, Hoboken, NJ 07030. All rights reserved. Manufactured in the
United States of America. This publication is protected by Copyright, and permission should be obtained from the publisher prior to any
prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying,
recording, or likewise.

For information regarding permissions, request forms and the appropriate contacts within the Pearson Education Global Rights & Permissions
Department, please visit www.pearsoned.com/permissions. Ackn-owledgments of third-party content appear on the appropriate page within the
text, which constitutes an extension of this copyright page. Unless otherwise indicated herein, any third-party trademarks that may appear in this
work are the property of their respective owners and any references to third-party trademarks, logos or other trade dress are for demonstrative or de-
scriptive purposes only. Such references are not intended to imply any sponsorship, endorsement, authorization, or promotion of Pearson’s products
by the owners of such marks, or any relationship between the owner and Pearson Education, Inc. or its affiliates, authors, licensees or distributors.

Library of Congress Cataloging-in-Publication Data

Names: Kroenke, David M., author. | Auer, David J., author. |
Vandenberg, Scott L., author. | Yoder, Robert C., author.

Title: Database concepts/David M. Kroenke, David J. Auer, Western
Washington University, Scott L. Vandenberg, Siena College, Robert C.
Yoder, Siena College.

Description: Ninth edition. | New York, NY : Pearson, [2020] | Includes
index.
Identifiers: LCCN 2018052988 | ISBN 9780135188149 | ISBN 0135188148
Subjects: LCSH: Database management. | Relational databases.
Classification: LCC QA76.9.D3 K736 2020 | DDC 005.74–dc23 LC record available at
https://lccn.loc.gov/2018052988

1 19

ISBN 10: 0-13-518814-8
ISBN 13: 978-0-13-518814-9

Z05_KROE8149_09_SE_EXTB.indd 2 04/07/19 8:23 PM

B-1

EX
TE

N
S
IO

N

B Advanced SQL
■ To understand reasons for using the SQL ALTER

statement

■ To use the SQL ALTER statement

■ To understand the need for the SQL MERGE statement

■ To use the SQL MERGE statement

■ To understand the need for additional types of SQL
queries

■ To use SQL outer join queries

■ To use SQL correlated subqueries

■ To use SQL queries on recursive relationships

■ To understand the reasons for using SQL set operators

■ To use SQL set operators

■ To understand the reasons for using SQL views

■ To use SQL statements to create and use SQL views

■ To understand SQL/Persistent Stored Modules (SQL/PSM)

■ To use SQL statements to create and use SQL user-
defined functions

■ To introduce the topic of importing Microsoft Excel 2019
data into a database table

■ To introduce the topic of using Microsoft Access 2019 as
an application development platform

CHAPTER OBJECTIVES

WHAT IS THE PURPOSE OF THIS EXTENSION?
In Chapter 3, we discussed SQL in depth. We discussed two basic categories of SQL state-
ments: data definition language (DDL) statements, which are used for creating tables, rela-
tionships, and other structures, and data manipulation language (DML) statements, which
are used for querying and modifying data.

In this extension, which should be studied immediately after Chapter 3, we:

• Describe and illustrate additional uses of the SQL ALTER statement.
• Describe and illustrate the SQL MERGE statement.
• Describe and illustrate SQL outer join queries.
• Describe and illustrate SQL correlated subqueries.
• Describe and illustrate SQL queries on recursive relationships.
• Describe and illustrate SQL set operators.
• Describe and illustrate SQL views, which extend the DML capabilities of SQL.
• Describe and illustrate SQL Persistent Stored Modules (SQL/PSM) and create user-

defined functions.
• Describe importing Microsoft Excel worksheet data into a database table.
• Describe using Microsoft Access as a development platform.

EXTENDING THE WP DATABASE
In Chapter 3, we created and used a database for Wedgewood Pacific named WP. We will
continue to use that database as the basis for our discussion in this extension. Our first step
is to extend the WP database by adding some additional tables. WP manufactures and sell
consumer drones, and currently offers three models: The Alpha III, the Bravo II, and the
Delta IV. While sold through various retailers, these drone models are also available directly
from WP through catalog and Web site sales.

Z05_KROE8149_09_SE_EXTB.indd 1 04/07/19 8:23 PM

B-2 Online Extension B Advanced SQL

FIGURE B-1

WP Database Column Characteristics for the PRODUCTION_ITEM Table

FIGURE B-2

WP Database Column Characteristics for the CATALOG_SKU_20## Tables

WP identifies each drone model with a stock keeping unit (SKU), which is a unique
identifier for each product item that WP sells. WP maintains product data (for both cur-
rent and past products) in a table named PRODUCTION_ITEM, and it maintains data
that identifies what products are or were available in its annual catalogs in tables named
CATALOG_SKU_20##, where ## indicates the year. These tables also record when the
SKU was added to the Web site, and some products may be introduced in a calendar year
after the catalog itself is published. Thus, such a product will be available on the Web site
but not via the catalog. The column characteristics for the PRODUCTION_ITEM table are
shown in Figure B-1, and column characteristics for the CATALOG_SKU_20## tables are
shown in Figure B-2. Data for the PRODUCTION_ITEM Table is shown in Figure B-3, for
the CATALOG_SKU_2017 table in Figure B-4, and for the CATALOG_SKU_2018 table
in Figure B-5.

In schema format, the tables are:

PRODUCTION_ITEM (SKU, SKU_Description, ProductionStartDate,
ProductionEndDate, QuantityOnHand, QuantityInProduction)

CATALOG_SKU_2017(CatalogID, SKU, CatalogDescription, CatalogPage,
DateOnWebsite)

CATALOG_SKU_2018(CatalogID, SKU, CatalogDescription, CatalogPage,
DateOnWebsite)

Z05_KROE8149_09_SE_EXTB.indd 2 04/07/19 8:23 PM

Online Extension B Advanced SQL B-3

FIGURE B-3

WP Database Data for the PRODUCTION_ITEM Table

FIGURE B-4

WP Database Data for the CATALOG_SKU_2017 Table

FIGURE B-5

WP Database Data for the CATALOG_SKU_2018 Table

Z05_KROE8149_09_SE_EXTB.indd 3 04/07/19 8:23 PM

B-4 Online Extension B Advanced SQL

The referential integrity constraints are:

SKU in CATALOG_SKU_2017 must exist in SKU in PRODUCTION_ITEM
SKU in CATALOG_SKU_2018 must exist in SKU in PRODUCTION_ITEM

Note that while these tables have relationships between themselves, they do not currently
have relationships with any of the existing DEPARTMENT, EMPLOYEE, PROJECT, or
ASSIGNMENT tables in the WP database.

Using the SQL CREATE TABLE and SQL INSERT statements that we discussed in
Chapter 3, we can easily add these tables to the WP database and populate them with data.
The SQL CREATE TABLE statements needed to create the tables are shown in Figure B-6,
and the SQL INSERT statements needed to populate the tables are shown in Figure B-7.
However, note that we are intentionally creating the tables without the foreign key con-
straint between CATALOG_SKU_2018 and PRODUCTION_ITEM.

Note that these statements illustrate the use of the MySQL keyword AUTO_INCRE-
MENT to create surrogate keys in MySQL 8.0. Surrogate keys must be handled differently
in Microsoft SQL Server and Oracle Database XE. For a discussion of how to create surro-
gate keys in MySQL 8.0, see online Extension A, “Working with MySQL.” For a discussion
of how to create surrogate keys in Microsoft SQL Server and Oracle Database XE, see our
book Database Processing: Fundamentals, Design, and Implementation.1

1Specifically, see online Chapter 10A, “Managing Databases with SQL Server 2017” and online Chapter
10B, “Managing Databases with Oracle Database” in David M. Kroenke, David J. Auer, Scott L. Vanden-
berg, and Robert C. Yoder, Database Processing: Fundamentals, Design, and Implementation, 15th ed. (Up-
per Saddle River, NJ: Pearson, 2019).

FIGURE B-6

SQL CREATE TABLE Statements to Modify the WP Database

CREATE

USE wp;

TABLE PRODUCTION_ITEM(
SKU Int NOT NULL,
SKU_Description Char(35) NOT NULL,
ProductionStartDate Date NULL,
ProductionEndDate Date NULL,
QuantityOnHand Int NULL,
QuantityInProduction Int NULL,
CONSTRAINT PRODUCTION_ITEM_PK PRIMARYKEY(SKU)
);

CREATE TABLE CATALOG_SKU_2017(
CatalogID Int NOT NULL AUTO_INCREMENT,
SKU Int NOT NULL,
CatalogDescription Varchar(255) NOT NULL,
CatalogPage Int NULL,
DateOnWebSite Date NULL,
CONSTRAINT CATALOG_SKU_2017_PK PRIMARY KEY(CatalogID),
CONSTRAINT CAT17_PROD_ITEM_FK FOREIGN KEY(SKU)

REFERENCES PRODUCTION_ITEM(SKU)
ON UPDATENO ACTION
ON DELETE NO ACTION

);

ALTER TABLE CATALOG_SKU_2017 AUTO_INCREMENT = 20170001;

ALTER TABLE CATALOG_SKU_2018 AUTO_INCREMENT = 20180001;

CREATE TABLE CATALOG_SKU_2018(
CatalogID Int NOT NULL AUTO_INCREMENT,
SKU Int NOT NULL,
CatalogDescription Varchar(255) NOT NULL,
CatalogPage Int NULL,
DateOnWebSite Date NULL,
CONSTRAINT CATALOG_SKU_2018_PK PRIMARY KEY(CatalogID)
);

Z05_KROE8149_09_SE_EXTB.indd 4 04/07/19 8:23 PM

Online Extension B Advanced SQL B-5

FIGURE B-7

SQL INSERT Statements to Populate the New WP Database Tables

/***** PRODUCTION_ITEM DATA *************************************************/

USE wp;

/***** CATALOG_SKU_2017 *****************************************************/

/* Note use of NULL to generate next AUTO_INCREMENT value in MySQL */

/* Note use of NULL to generate next AUTO_INCREMENT value in MySQL */

/***** CATALOG_SKU_2018 *****************************************************/

INSERT INTO PRODUCTION_ITEM VALUES(
170102001, ‘Alpha II, Black’, ‘2016-10-15’, ‘2017-11-30’, 0, 0);

170102005, ‘Alpha II, White’, ‘2016-11-15’, ‘2017-10-31’, 0, 0);

170201001, ‘Bravo I, Black’, ‘2016-12-15’, ‘2017-11-30’, 0, 0);

170201005, ‘Bravo I, White’, ‘2016-12-15’, ‘2017-11-30’, 0, 0);

170303001, ‘Delta III, Black’, ‘2017-01-15’, ‘2017-01-02’, 5, 0);

170303005, ‘Delta III, White’, ‘2017-01-15’, ‘2017-01-02’, 15, 0);

180103001, ‘Alpha III, Black’, ‘2017-11-15’, NULL, 100, 100);

180103005, ‘Alpha III, White’, ‘2017-11-15’, NULL, 100, 0);

180202001, ‘Bravo II, Black’, ‘2017-12-15’, NULL, 200, 100);

180202005, ‘Bravo II, White’, ‘2017-12-15’, NULL, 150, 50);

180304001, ‘Delta IV, Black’, ‘2018-01-15’, NULL, 300, 200);

180304005, ‘Delta IV, White’, ‘2018-01-15’, NULL, 200, 100);

INSERT INTO PRODUCTION_ITEM VALUES(

INSERT INTO PRODUCTION_ITEM VALUES(

INSERT INTO PRODUCTION_ITEM VALUES(

INSERT INTO PRODUCTION_ITEM VALUES(

INSERT INTO PRODUCTION_ITEM VALUES(

INSERT INTO PRODUCTION_ITEM VALUES(

INSERT INTO PRODUCTION_ITEM VALUES(

INSERT INTO PRODUCTION_ITEM VALUES(

INSERT INTO PRODUCTION_ITEM VALUES(

INSERT INTO PRODUCTION_ITEM VALUES(

INSERT INTO PRODUCTION_ITEM VALUES(

INSERT INTO CATALOG_SKU_2017 VALUES(
NULL,170102001, ‘Our low price Alpha II model in black.’, 10, ‘2017-01-01’);

NULL,170102005, ‘Our low price Alpha II model in white.’, 12, ‘2017-01-01’);

NULL,170201001, ‘Our new Bravo I model in black.’, 18, ‘2017-01-01’);

NULL,170201005, ‘Our new Bravo I model in white.’, 20, ‘2017-01-01’);

NULL,170303001, ‘Our high performance Delta III model in black.’, 24, ‘2017-01-01’);

NULL,170303005, ‘Our high performance Delta III model in white.’, 26, ‘2017-01-01’);

NULL,180103001, ‘New, updated Alpha III model in black.’, NULL, ‘2017-12-01’);

NULL,180103005, ‘New, updated Alpha III model in white.’, NULL, ‘2017-12-01’);

INSERT INTO CATALOG_SKU_2017 VALUES(

INSERT INTO CATALOG_SKU_2017 VALUES(

INSERT INTO CATALOG_SKU_2017 VALUES(

INSERT INTO CATALOG_SKU_2017 VALUES(

INSERT INTO CATALOG_SKU_2017 VALUES(

INSERT INTO CATALOG_SKU_2017 VALUES(

INSERT INTO CATALOG_SKU_2017 VALUES(

INSERT INTO CATALOG_SKU_2018 VALUES(
NULL, 180103001, ‘Our low price Alpha III model in black.’, 10, ‘2018-01-01’);
INSERT INTO VALUES
NULL, 180103005, ‘Our low price Alpha III model in white.’, 11, ‘2018-01-01’);
INSERT INTO CATALOG_SKU_2018

CATALOG_SKU_2018

VALUES(

(

NULL, 180202001, ‘Our new Bravo II model in black.’, 18, ‘2018-01-01’);
INSERT INTO CATALOG_SKU_2018 VALUES(
NULL, 180202005, ‘Our new Bravo II model in white.’, 17, ‘2018-01-01’);
INSERT INTO CATALOG_SKU_2018 VALUES(
NULL, 180304001, ‘Our high performance Delta IV model in black.’, 22, ‘2018-01-01’);
INSERT INTO CATALOG_SKU_2018 VALUES(
NULL, 180304005, ‘Our high performance Delta IV model in white.’, 23, ‘2018-01-01’);

Z05_KROE8149_09_SE_EXTB.indd 5 04/07/19 8:23 PM

B-6 Online Extension B Advanced SQL

A MySQL Workbench database design for the WP database is shown in Figure B-8.
Note that there is no relationship between PRODUCTION_ITEM and CATALOG_
SKU_2018. Also note that the relationship between PRODUCTION_ITEM and CATA-
LOG_SKU_2017 is 1:N because, although it does not occur in our current data, an SKU
can be used on more than more catalog page (for example, as part of product bundle).
The data as it actually exists (after the SQL INSERT statements are run) in the PRODUC-
TION_ITEM, CATALOG_SKU_2017, and CATALOG_SKU_2018 tables is shown in
Figure B-9. Our revisions to the WP database are complete, and we will make use of these
new tables in our discussion of advanced SQL features.

PK /FK ProjectID
PK /FK EmployeeNumber

FIGURE B-8

The Revised Database Design for the WP Database

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

FIGURE B-9

Data in the New WP Database Tables

(a) Data in the New PRODUCTION_ITEM Table

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 6 04/07/19 8:23 PM

Online Extension B Advanced SQL B-7

In Chapter 3, we introduced the SQL ALTER TABLE statement (see page 202), which is used
to modify the structure of a table once it has been created in a database. It can be used to add,
modify, and drop columns and constraints, and, very useful for us, to modify the AUTO_IN-
CREMENT starting value in MySQL 8.0. Here we will illustrate common uses of this statement.

Modifying the MySQL AUTO_INCREMENT Starting Value
As discussed in online Extension A, “Working with MySQL,” the MySQL AUTO_
INCREMENT function that is used to create surrogate key values will initially use a starting
value of 1 and an increment of 1. While the increment value cannot be changed, the starting
value can be modified by using an ALTER TABLE statement after the table has been created.

Indeed, we have already used such statements in Figure B-6 to modify the starting val-
ues for CatalogID in the CATALOG_SKU_2017 and CATALOG_SKU_2018 tables. For
the CATALOG_SKU_2017 table, we used the SQL Statement:

/* *** EXAMPLE CODE – THIS STATEMENT HAS ALREADY BEEN RUN *** */

/* *** SQL-ALTER-TABLE-ExtB-01 *** */

ALTER TABLE CATALOG_SKU_2017 AUTO_INCREMENT = 20170001;

This statement sets the first value of the CatalogID to 20170001. For the CATALOG_
SKU_2018 table, we used the SQL Statement:

/* *** EXAMPLE CODE – THIS STATEMENT HAS ALREADY BEEN RUN *** */

/* *** SQL-ALTER-TABLE-ExtB-02 *** */

ALTER TABLE CATALOG_SKU_2018 AUTO_INCREMENT = 20180001;

This statement sets the first value of the CatalogID to 20180001.

USING THE SQL ALTER TABLE STATEMENT

(b) Data in the New CATALOG_SKU_2017 Table

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

(c) Data in the New CATALOG_SKU_2018 Table

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 7 04/07/19 8:23 PM

B-8 Online Extension B Advanced SQL

Both Microsoft SQL Server and Oracle Database allow you to set both the starting
value and the increment value of the surrogate key values. Microsoft Access and
MySQL are both limited by comparison: Microsoft Access only allows starting
values of 1, and both Microsoft Access and MySQL only allow an increment
value of 1.

BTW

Adding a Column to an Existing Table
The SQL ALTER TABLE statement can be used to add a column to an existing table. We
used this feature in Chapter 3’s section of “Working with Microsoft Access” (see pages
227–228), and here we will elaborate on how to do this. The basic syntax is:

/* *** EXAMPLE CODE – DO NOT RUN *** */

/* *** SQL-ALTER-TABLE-ExtB-03 *** */

ALTER TABLE {TableName}

ADD {ColumnName} {DataType} {OptionalColumnConstraints};

Note that the SQL COLUMN keyword is not used in an ADD {COLUMN} clause in the
variants of SQL used in most relational DBMS products. MySQL, however, does allow the
use of the SQL COLUMN keyword as an option but does not require it (see online Exten-
sion A, “Working with MySQL”).

If we are adding a column that allows NULL values, then we can do it with that one
statement. If, however, we are adding a column with a NOT NULL column constraint, then
we must use the following steps:

1. Add the column as column that allows NULL values.
2. Update all table rows with data values in the new column.
3. Modify the column to set the NOT NULL constraint.

For example, suppose that new legislation is passed requiring that any new
drone being offered for sale must be approved by the FAA before going into produc-
tion. The  FAA checks for safety and control compliance with FAA regulations and
specifications.

This means that WP will need to add a column to the PRODUCTION_ITEM table
to store the date that FAA approval was received for each drone model. Further, since this
approval must be received before the drone goes into production, it must be on hand when
the model is added to the table, and is therefore a NOT NULL data constraint. We will as-
sume that WP has such approvals and approval dates for all drone models currently in the
table and add the column as a NOT NULL column.

Adding a NOT NULL Column to the PRODUCTION_ITEM Table

1. Add a new column named ApprovalDate as a NULL column to the PRODUCTION_
ITEM table.

/* *** SQL-ALTER-TABLE-ExtB-04 *** */

ALTER TABLE PRODUCTION_ITEM

ADD ApprovalDate DATE NULL;

Z05_KROE8149_09_SE_EXTB.indd 8 04/07/19 8:23 PM

Online Extension B Advanced SQL B-9

2. Update the current rows in the PRODUCTION_ITEM table with the FAA approval
dates for the existing drone models. The ApprovalDate data we will use is shown in the
UPDATE statements:

/* *** SQL-UPDATE-ExtB-01 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = ‘2016-08-31’

WHERE SKU = 170102001;

/* *** SQL-UPDATE-ExtB-02 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = ‘2016-08-31’

WHERE SKU = 170102005;

/* *** SQL-UPDATE-ExtB-03 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = ‘2016-09-30’

WHERE SKU = 170201001;

/* *** SQL-UPDATE-ExtB-04 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = ‘2016-09-30’

WHERE SKU = 170201005;

/* *** SQL-UPDATE-ExtB-05 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = ‘2016-11-30’

WHERE SKU = 170303001;

/* *** SQL-UPDATE-ExtB-06 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = ‘2016-11-30’

WHERE SKU = 170303005;

/* *** SQL-UPDATE-ExtB-07 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = ‘2017-09-30’

WHERE SKU = 180103001;

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 9 04/07/19 8:23 PM

B-10 Online Extension B Advanced SQL

/* *** SQL-UPDATE-ExtB-08 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = ‘2017-09-30’

WHERE SKU = 180103005;

/* *** SQL-UPDATE-ExtB-09 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = ‘2017-09-30’

WHERE SKU = 180202001;

/* *** SQL-UPDATE-ExtB-10 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = ‘2017-09-30’

WHERE SKU = 180202005;

/* *** SQL-UPDATE-ExtB-11 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = ‘2017-11-30’

WHERE SKU = 180304001;

/* *** SQL-UPDATE-ExtB-12 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = ‘2017-11-30’

WHERE SKU = 180304005;

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

3. Modify the ApprovalDate column to set the NOT NULL column constraint.

/* *** SQL-ALTER-TABLE-ExtB-05 *** */

ALTER TABLE PRODUCTION_ITEM

MODIFY COLUMN ApprovalDate DATE NOT NULL;

Note that the SQL COLUMN keyword is used in a MODIFY COLUMN or an ALTER
COLUMN clause—MySQL and Oracle Database use the MODIFY COLUMN clause,
whereas Microsoft SQL Server uses the ALTER COLUMN clause. Looking at the column
descriptions for the PRODUCTION_ITEM table, as shown in Figure B-10, we can see that
the ApprovalDate column is now set to NOT NULL.

Adding a Table Constraint to an Existing Table
The SQL ALTER TABLE statement can be used to add a table constraint to an existing
table. The basic syntax is:

Z05_KROE8149_09_SE_EXTB.indd 10 04/07/19 8:23 PM

Online Extension B Advanced SQL B-11

/* *** EXAMPLE CODE – DO NOT RUN *** */

/* *** SQL-ALTER-TABLE-ExtB-06 *** */

ALTER TABLE {TableName}

ADD CONSTRAINT {ConstraintName} {TableConstraint};

Note that the SQL CONSTRAINT keyword is used in an ADD CONSTRAINT clause.
A typical use here might be to add an SQL CHECK constraint to a table.2 In our WP

database example, the data value in ApprovalDate in each row in the table must be less than
the data value in ProductionStartDate in the same row (FAA approval must be on hand
before production of a specific drone model is started). We can do that by using the SQL
ALTER TABLE statement:

/* *** SQL-ALTER-TABLE-ExtB-07 *** */

ALTER TABLE PRODUCTION_ITEM

ADD CONSTRAINT CheckProductionDate CHECK

(ApprovalDate < ProductionStartDate);

Note that the SQL CONSTRAINT keyword is used in an ALTER CONSTRAINT clause.
Looking at the DDL tab of the table inspector for the PRODUCTION_ITEM table, as
shown in Figure B-11, we can see that the CheckProductionDate constraint has been added.

2We are using MySQL Community Server 8.0.16.0 in this extension and in Extension A, “Working with
MySQL.” As of MySQL 8.0.16.0, the CHECK constraint implemented in MySQL works correctly. In prior
versions, the SQL statement implementing the CHECK constraint would be parsed correctly, but not actu-
ally implemented. We highly recommend that you install or upgrade to MySQL 8.0.16.0 (or a higher ver-
sion number if available) as soon as possible. See Extension A, “Working with MySQL” for a discussion
of installing and upgrading MySQL. Note that this problem only existed in MySQL—Microsoft Access,
Microsoft SQL Server, and Oracle Database XE all implement the CHECK constraint correctly.

Right-click the production_item
table, then click Table Inspector,
then click the Columns tab

ApprovalDate is “not nullable”—
NOT NULL

The ALTER TABLE statement that
set ApprovalDate to NOT NULL

FIGURE B-10

PRODUCTION_ITEM.ApprovalDate set to NOT NULL

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 11 04/07/19 8:23 PM

B-12 Online Extension B Advanced SQL

Adding a Referential Integrity Constraint to an Existing Table
The SQL ALTER TABLE statement can be used to add a referential integrity constraint to
an existing table. The basic syntax is:

/* *** EXAMPLE CODE – DO NOT RUN *** */

/* *** SQL-ALTER-TABLE-ExtB-08 *** */

ALTER TABLE {TableName}

ADD CONSTRAINT {ConstraintName} FOREIGN KEY({ColumnName})

REFERENCES {TableName}({PrimaryKeyColumn})

{Optional ON UPDATE clause}

{Optional ON DELETE clause};

Note that the SQL CONSTRAINT keyword is used in an ADD CONSTRAINT clause.
In our WP database example, we still need to add a referential integrity constraint to

the CATALOG_SKU_2018 table to establish the relationship between that table and the
PRODUCTION_ITEM table. We can do that by using the SQL ALTER TABLE statement:

/* *** SQL-ALTER-TABLE-ExtB-09 *** */

ALTER TABLE CATALOG_SKU_2018

ADD CONSTRAINT CAT18_PROD_ITEM_FK FOREIGN KEY(SKU)

REFERENCES PRODUCTION_ITEM(SKU)

ON UPDATE NO ACTION

ON DELETE NO ACTION;

Right-click the production_item
table, then click Table Inspector,
then click the DDL tab

The ALTER TABLE statement that
created the CHECK constraint

The DDL tab shows the SQL
CREATE TABLE statement needed
to create the table as it currently
exists—Note that this statement
now contains the CHECK constraint

FIGURE B-11

The PRODUCTION_ITEM CheckProductionDate Constraint

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 12 04/07/19 8:23 PM

Online Extension B Advanced SQL B-13

Note that because SKU, the primary key of PRODUCTION_ITEM, is a surrogate key, it
will never be changed, and therefore no update actions will be necessary. And because WP
wants to retain data in the CATALOG_SKU_20## tables for historical records, we will not
allow deletion of an SKU from the PRODUCTION_ITEM table.

After running the ALTER TABLE statement, the relationship between PRODUC-
TION_ITEM and CATALOG_SKU_2018 can be seen in Figure B-12. Note that the added
ApprovalDate column also appears in the PRODUCTION_ITEM table. Compare data-
base diagram in Figure B-12 to the one in Figure B-8.

Removing a Table Column from an Existing Table
The SQL ALTER TABLE statement can be used to remove a column from a table. Note
that the SQL COLUMN keyword is used in the DROP COLUMN clause. The basic
syntax is:

/* *** EXAMPLE CODE – DO NOT RUN *** */

/* *** SQL-ALTER-TABLE-ExtB-10 *** */

ALTER TABLE {TableName}

DROP COLUMN {ColumnName};

PK /FK ProjectID
PK /FK EmployeeNumber

ApprovalDate column

FIGURE B-12

The Database Design for the WP Database with the Added Relationship

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 13 04/07/19 8:23 PM

B-14 Online Extension B Advanced SQL

Removing a Table Constraint from an Existing Table
The SQL ALTER TABLE statement can be used to remove a table constraint. Note that
the SQL CONSTRAINT keyword is used in the DROP CONSTRAINT clause. The basic
syntax is:

/* *** EXAMPLE CODE – DO NOT RUN *** */

/* *** SQL-ALTER-TABLE-ExtB-11 *** */

ALTER TABLE {TableName}

DROP CONSTRAINT

Database systems

  • page 1
  • page 2
  • page 3
  • page 4
  • page 5
  • page 6
  • page 7
  • page 8
  • page 9
  • page 10
  • page 11
  • page 12
  • page 13
  • page 14
  • page 15
  • page 16
  • page 17
  • page 18
  • page 19
  • page 20
  • page 21
  • page 22
  • page 23
  • page 24
  • page 25
  • page 26
  • page 27
  • page 28
  • page 29
  • page 30
  • page 31
  • page 32
  • page 33
  • page 34
  • page 35
  • page 36
  • page 37
  • page 38
  • page 39
  • page 40
  • page 41
  • page 42
  • page 43
  • page 44
  • page 45
  • page 46
  • page 47
  • page 48
  • page 49
  • page 50
  • page 51
  • page 52
  • page 53
  • page 54
  • page 55
  • page 56
  • page 57
  • page 58
  • page 59
  • page 60
  • page 61
  • page 62
  • page 63
  • page 64
  • page 65
  • page 66
  • page 67
  • page 68
  • page 69
  • page 70
  • page 71
  • page 72
  • page 73
  • page 74
  • page 75
  • page 76
  • page 77
  • page 78
  • page 79
  • page 80
  • page 81
  • page 82
  • page 83
  • page 84
  • page 85
  • page 86
  • page 87
  • page 88
  • page 89
  • page 90
  • page 91
  • page 92
  • page 93
  • page 94
  • page 95
  • page 96
  • page 97
  • page 98
  • page 99
  • page 100
  • page 101
  • page 102
  • page 103
  • page 104
  • page 105
  • page 106
  • page 107
  • page 108
  • page 109
  • page 110
  • page 111
  • page 112
  • page 113
  • page 114
  • page 115
  • page 116
  • page 117
  • page 118
  • page 119
  • page 120
  • page 121
  • page 122
  • page 123
  • page 124
  • page 125
  • page 126
  • page 127
  • page 128
  • page 129
  • page 130
  • page 131
  • page 132
  • page 133
  • page 134
  • page 135
  • page 136
  • page 137
  • page 138
  • page 139
  • page 140
  • page 141
  • page 142
  • page 143
  • page 144
  • page 145
  • page 146
  • page 147
  • page 148
  • page 149
  • page 150
  • page 151
  • page 152
  • page 153
  • page 154
  • page 155
  • page 156
  • page 157
  • page 158
  • page 159
  • page 160
  • page 161
  • page 162
  • page 163
  • page 164
  • page 165
  • page 166
  • page 167
  • page 168
  • page 169
  • page 170
  • page 171
  • page 172
  • page 173
  • page 174
  • page 175
  • page 176
  • page 177
  • page 178
  • page 179
  • page 180
  • page 181
  • page 182
  • page 183
  • page 184
  • page 185
  • page 186
  • page 187
  • page 188
  • page 189
  • page 190
  • page 191
  • page 192
  • page 193
  • page 194
  • page 195
  • page 196
  • page 197
  • page 198
  • page 199
  • page 200
  • page 201
  • page 202
  • page 203
  • page 204
  • page 205
  • page 206
  • page 207
  • page 208
  • page 209
  • page 210
  • page 211
  • page 212
  • page 213
  • page 214
  • page 215
  • page 216
  • page 217
  • page 218
  • page 219
  • page 220
  • page 221
  • page 222
  • page 223
  • page 224
  • page 225
  • page 226
  • page 227
  • page 228
  • page 229
  • page 230
  • page 231
  • page 232
  • page 233
  • page 234
  • page 235
  • page 236
  • page 237
  • page 238
  • page 239
  • page 240
  • page 241
  • page 242
  • page 243
  • page 244
  • page 245
  • page 246
  • page 247
  • page 248
  • page 249
  • page 250
  • page 251
  • page 252
  • page 253
  • page 254
  • page 255
  • page 256
  • page 257
  • page 258
  • page 259
  • page 260
  • page 261
  • page 262
  • page 263
  • page 264
  • page 265
  • page 266
  • page 267
  • page 268
  • page 269
  • page 270
  • page 271
  • page 272
  • page 273
  • page 274
  • page 275
  • page 276
  • page 277
  • page 278
  • page 279
  • page 280
  • page 281
  • page 282
  • page 283
  • page 284
  • page 285
  • page 286
  • page 287
  • page 288
  • page 289
  • page 290
  • page 291
  • page 292
  • page 293
  • page 294
  • page 295
  • page 296
  • page 297
  • page 298
  • page 299
  • page 300
  • page 301
  • page 302
  • page 303
  • page 304
  • page 305
  • page 306
  • page 307
  • page 308
  • page 309
  • page 310
  • page 311
  • page 312
  • page 313
  • page 314
  • page 315
  • page 316
  • page 317
  • page 318
  • page 319
  • page 320
  • page 321
  • page 322
  • page 323
  • page 324
  • page 325
  • page 326
  • page 327
  • page 328
  • page 329
  • page 330
  • page 331
  • page 332
  • page 333
  • page 334
  • page 335
  • page 336
  • page 337
  • page 338
  • page 339
  • page 340
  • page 341
  • page 342
  • page 343
  • page 344
  • page 345
  • page 346
  • page 347
  • page 348
  • page 349
  • page 350
  • page 351
  • page 352
  • page 353
  • page 354
  • page 355
  • page 356
  • page 357
  • page 358
  • page 359
  • page 360
  • page 361
  • page 362
  • page 363
  • page 364
  • page 365
  • page 366
  • page 367
  • page 368
  • page 369
  • page 370
  • page 371
  • page 372
  • page 373
  • page 374
  • page 375
  • page 376
  • page 377
  • page 378
  • page 379
  • page 380
  • page 381
  • page 382
  • page 383
  • page 384
  • page 385
  • page 386
  • page 387
  • page 388
  • page 389
  • page 390
  • page 391
  • page 392
  • page 393
  • page 394
  • page 395
  • page 396
  • page 397
  • page 398
  • page 399
  • page 400
  • page 401
  • page 402
  • page 403
  • page 404
  • page 405
  • page 406
  • page 407
  • page 408
  • page 409
  • page 410
  • page 411
  • page 412
  • page 413
  • page 414
  • page 415
  • page 416
  • page 417
  • page 418
  • page 419
  • page 420
  • page 421
  • page 422
  • page 423
  • page 424
  • page 425
  • page 426
  • page 427
  • page 428
  • page 429
  • page 430
  • page 431
  • page 432
  • page 433
  • page 434
  • page 435
  • page 436
  • page 437
  • page 438
  • page 439
  • page 440
  • page 441
  • page 442
  • page 443
  • page 444
  • page 445
  • page 446
  • page 447
  • page 448
  • page 449
  • page 450
  • page 451
  • page 452
  • page 453
  • page 454
  • page 455
  • page 456
  • page 457
  • page 458
  • page 459
  • page 460
  • page 461
  • page 462
  • page 463
  • page 464
  • page 465
  • page 466
  • page 467
  • page 468
  • page 469
  • page 470
  • page 471
  • page 472
  • page 473
  • page 474
  • page 475
  • page 476
  • page 477
  • page 478
  • page 479
  • page 480
  • page 481
  • page 482
  • page 483
  • page 484
  • page 485
  • page 486
  • page 487
  • page 488
  • page 489
  • page 490
  • page 491
  • page 492
  • page 493
  • page 494
  • page 495
  • page 496
  • page 497
  • page 498
  • page 499
  • page 500
  • page 501
  • page 502
  • page 503
  • page 504
  • page 505
  • page 506
  • page 507
  • page 508
  • page 509
  • page 510
  • page 511
  • page 512
  • page 513
  • page 514
  • page 515
  • page 516
  • page 517
  • page 518
  • page 519
  • page 520
  • page 521
  • page 522
  • page 523
  • page 524
  • page 525
  • page 526
  • page 527
  • page 528
  • page 529
  • page 530
  • page 531
  • page 532
  • page 533
  • page 534
  • page 535
  • page 536
  • page 537
  • page 538
  • page 539
  • page 540
  • page 541
  • page 542
  • page 543
  • page 544
  • page 545
  • page 546
  • page 547
  • page 548
  • page 549
  • page 550
  • page 551
  • page 552

Database systems

Database Concepts
9th Edition

David M. Kroenke • David J. Auer • Scott L. Vandenberg • Robert C. Yoder

Online Extension C

Advanced Business Intelligence and Big Data

Z06_KROE8149_09_SE_EXTC.indd 1 04/07/19 9:05 PM

Vice President of Courseware Portfolio Management: Andrew Gilfillan
Executive Portfolio Manager: Samantha Lewis
Team Lead, Content Production: Laura Burgess
Content Producer: Faraz Sharique Ali
Portfolio Management Assistant: Bridget Daly
Director of Product Marketing: Brad Parkins
Director of Field Marketing: Jonathan Cottrell
Product Marketing Manager: Heather Taylor
Field Marketing Manager: Bob Nisbet
Product Marketing Assistant: Liz Bennett
Field Marketing Assistant: Derrica Moser
Senior Operations Specialist: Diane Peirano

Senior Art Director: Mary Seiner
Interior and Cover Design: Pearson CSC
Cover Art: Artwork by Donna Auer
Senior Product Model Manager: Eric Hakanson
Manager, Digital Studio: Heather Darby
Course Producer, MyLab MIS: Jaimie Noy
Digital Studio Producer: Tanika Henderson
Full-Service Project Manager: Gowthaman Sadhanandham
Full Service Vendor: Integra Software Service Pvt. Ltd.
Manufacturing Buyer: LSC Communications, Maura Zaldivar-Garcia
Text Printer/Bindery: LSC Communications
Cover Printer: Phoenix Color

Credits and acknowledgments borrowed from other sources and reproduced, with permission, in this textbook appear on the appropriate page
within text.

Microsoft and/or its respective suppliers make no representations about the suitability of the information contained in the documents and
related graphics published as part of the services for any purpose. All such documents and related graphics are provided “as is” without
warranty of any kind. Microsoft and/or its respective suppliers hereby disclaim all warranties and conditions with regard to this information,
including all warranties and conditions of merchantability, whether express, implied or statutory, fitness for a particular purpose, title and
non-infringement. In no event shall Microsoft and/or its respective suppliers be liable for any special, indirect or consequential damages or any
damages whatsoever resulting from loss of use, data or profits, whether in an action of contract, negligence or other tortious action, arising out
of or in connection with the use or performance of information available from the services.

The documents and related graphics contained herein could include technical inaccuracies or typographical errors. Changes are periodically
added to the information herein. Microsoft and/or its respective suppliers may make improvements and/or changes in the product(s) and/or the
program(s) described herein at any time. Partial screen shots may be viewed in full within the software version specified.

Microsoft® Windows®, and Microsoft Office® are registered trademarks of the Microsoft Corporation in the U.S.A. and other countries. This
book is not sponsored or endorsed by or affiliated with the Microsoft Corporation.

MySQL®, the MySQL Command Line Client®, the MySQL Workbench®, and the MySQL Connector/ODBC® are registered trademarks
of Sun Microsystems, Inc./Oracle Corporation. Screenshots and icons reprinted with permission of Oracle Corporation. This book is not
sponsored or endorsed by or affiliated with Oracle Corporation.

Oracle Database XE 2016 by Oracle Corporation. Reprinted with permission.

PHP is copyright The PHP Group 1999–2012, and is used under the terms of the PHP Public License v3.01 available at http://www.php.net/
license/3_01.txt. This book is not sponsored or endorsed by or affiliated with The PHP Group.

ArangoDB is a copyright of ArangoDB GmbH.

Copyright © 2020, 2017, 2015 by Pearson Education, Inc. 221 River Street, Hoboken, NJ 07030. All rights reserved. Manufactured in the
United States of America. This publication is protected by Copyright, and permission should be obtained from the publisher prior to any
prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying,
recording, or likewise.

For information regarding permissions, request forms and the appropriate contacts within the Pearson Education Global Rights & Permissions
Department, please visit www.pearsoned.com/permissions. Ackn-owledgments of third-party content appear on the appropriate page within the
text, which constitutes an extension of this copyright page. Unless otherwise indicated herein, any third-party trademarks that may appear in this
work are the property of their respective owners and any references to third-party trademarks, logos or other trade dress are for demonstrative or de-
scriptive purposes only. Such references are not intended to imply any sponsorship, endorsement, authorization, or promotion of Pearson’s products
by the owners of such marks, or any relationship between the owner and Pearson Education, Inc. or its affiliates, authors, licensees or distributors.

Library of Congress Cataloging-in-Publication Data

Names: Kroenke, David M., author. | Auer, David J., author. |
Vandenberg, Scott L., author. | Yoder, Robert C., author.

Title: Database concepts/David M. Kroenke, David J. Auer, Western
Washington University, Scott L. Vandenberg, Siena College, Robert C.
Yoder, Siena College.

Description: Ninth edition. | New York, NY : Pearson, [2020] | Includes
index.
Identifiers: LCCN 2018052988 | ISBN 9780135188149 | ISBN 0135188148
Subjects: LCSH: Database management. | Relational databases.
Classification: LCC QA76.9.D3 K736 2020 | DDC 005.74–dc23 LC record available at
https://lccn.loc.gov/2018052988

1 19

ISBN 10: 0-13-518814-8
ISBN 13: 978-0-13-518814-9

Z06_KROE8149_09_SE_EXTC.indd 2 04/07/19 9:05 PM

C-1

EX
TE

N
S
IO

N
Advanced Business Intelligence
and Big Data

EXTENSION OBJECTIVES

■ Learn the basic concepts of reporting systems

■ Learn the basic concepts of RFM reporting

■ Learn the basic concepts of data mining

■ Learn the basic concepts of market basket analysis

■ Learn the basic concepts of decision trees

■ Learn the basic concepts of Big Data

■ Learn the basic concepts of nonrelational database
management systems

■ Learn about replicated, partitioned data stores for
supporting clusters

■ Understand the importance of XML

■ Understand the role of XSLT in materializing XML
documents

■ Learn the elements of XML documents

■ Understand how to describe and validate XML document
structure using XML Schema

■ Understand how XML can be supported in a DBMS

■ See examples of XPath and XQuery for queries of XML
documents

■ Learn the basic concepts of key-value NoSQL databases

■ Learn the basic concepts of column family NoSQL
databases

■ Learn the basic concepts of graph NoSQL databases

■ Learn some advanced concepts of document NoSQL
databases

■ Learn the basic concepts of the Microsoft Azure cloud
environment for creating and using relational databases

T his extension covers technologies that have largely arisen after relational databases became the most widespread form of data storage. These technologies take advantage of improvements in stor-
age, processing speed, networking, and software advances in order to get
information from and to manage large amounts of data, often in a networked
environment. Two of these technology areas are Business Intelligence (BI) and
Big Data.

BI includes a number of important areas; here we will expand on
reporting systems and data mining—including coverage of RFM analysis,
market basket analysis, and decision trees. Big Data can occur anywhere
and with any data model, but it frequently involves cloud-based hardware/
software and/or nonrelational database management systems. Thus, in this
book, we take Big Data to mean not just large amounts of data, but also the
software and hardware systems that have evolved to make Big Data more
useful. We will explore some of the basic concepts of Big Data, in addition
to the CAP theorem discussed in Chapter 7. We will present a more detailed
discussion of various nonrelational data models used in the realm of Big Data.
Specifically, we will introduce XML as well as the major varieties of so-
called NoSQL database management systems: key-value, document, column
family, and graph.

C

Z06_KROE8149_09_SE_EXTC.indd 1 04/07/19 9:05 PM

C-2 Online Extension C Advanced Business Intelligence and Big Data

REPORTING SYSTEMS

To make some of these concepts more concrete, this extension includes
coverage of using the Microsoft Azure cloud platform to create and manage
a database. In addition, the “Working with Microsoft Access” section of this
extension describes two ways of using Microsoft Access that can be useful in
a Big Data or BI context. Using Access as a frontend to an enterprise DBMS
(in this case, MySQL) allows Access to manipulate more data efficiently than
it is typically capable of; this is clearly helpful in a Big Data scenario. Access
Switchboard forms allow a DBA (or sophisticated end users) to create a single
form that can be used to navigate to all the forms, reports, queries, and so on
in a database. This can make an Access database much easier for BI users
by placing the relevant reports and queries all together on a startup screen.

The purpose of a reporting system is to create meaningful information from disparate data
sources and to deliver that information to the proper users on a timely basis. Unlike data
mining, which uses sophisticated statistical techniques, reporting systems create informa-
tion by using the basic operations of sorting, filtering, grouping, and making simple
calculations.

Reporting systems summarize the current status of business activities and compare that
status with past or predicted future activities. Report delivery is crucial. Reports must be
delivered to the proper users on a timely basis in the appropriate format. For example, re-
ports may be delivered on paper, via a Web browser, or in some other format.

It is easier to understand reporting systems if you are familiar with a typical report, so
let us take a look at a typical reporting problem: RFM analysis.

RFM Analysis
RFM analysis analyzes and ranks customers according to their purchasing patterns. It is a
simple customer classification technique that considers how recently (R) a customer has
ordered, how frequently (F) a customer orders, and how much money (M) the customer
spends per order.

To produce an RFM score, we need only two things: customer data and sales data for
each purchase (including the date of the sale and the total amount of the sale) made by
each customer. If you look at the SALES_FOR_RFM table and its associated CUSTOMER
and TIMELINE dimension tables in Figure C-1, you see that we have exactly those data:
The SALES_FOR_RFM table is the starting point for RFM analysis in the HSD-DW BI
system. Although we will not do it here, RFM analysis can be done using SQL statements
and a table such as SALES_FOR_RFM.1

To calculate an R score, you first sort the customer purchase records by the date of the
most recent (R) purchase—note that only the most recent purchase for each customer is
used in this calculation. In a common form of this analysis, the customers are then divided
into five groups, and a score of 1 to 5 is given to customers in each group. The 20 percent
of the customers having the most recent orders are given an R score of 1, the 20 percent of
the customers having the next most recent orders are given an R score of 2, and so forth,
down to the last 20 percent, who are given an R score of 5.

1For a full discussion of RFM analysis using SQL statements, see David M. Kroenke, David J. Auer, Scott
L. Vandenberg, and Robert C. Yoder, Database Processing: Fundamentals, Design, and Implementation,
15th edition (Upper Saddle River, NJ: Pearson, 2019).

Z06_KROE8149_09_SE_EXTC.indd 2 04/07/19 9:05 PM

Online Extension C Advanced Business Intelligence and Big Data C-3

FIGURE C-1

The HSD-DW Dimensional Database with RFM Data

SALES_FOR_RFM
Fact Table

TIMELINE Dimension
Table

CUSTOMER
Dimension Table

To calculate an F score, you re-sort the customers on the basis of how frequently they
order. As before, the customers are again divided into five groups. The 20 percent of the
customers who order most frequently are given an F score of 1, the next 20 percent most
frequently ordering customers are given a score of 2, and so forth, down to the least fre-
quently ordering customers, who are given an F score of 5.

To calculate an M score, you re-sort the customers according to the average amounts
spent on their orders. The 20 percent who have placed the largest orders on average are
given an M score of 1, the next 20 percent are given an M score of 2, and so forth, down to
the 20 percent who spend the least, who are given an M score of 5.

Figure C-2 shows sample RFM data for Heather Sweeney Designs. (Note that these data
have not been calculated from the sample data and are for illustrative purposes only.) The
first customer, Ralph Able, has a score of {1 1 2}, which means that he has ordered recently
and orders frequently. His M score of 2 indicates, however, that he does not order the most
expensive goods. From these scores, the salespeople can surmise that Ralph is a good cus-
tomer who may be open to purchasing more expensive goods or higher quantities of goods.

FIGURE C-2

The RFM Score Report

Each customer is
ranked for R (recent),
F (frequent), and
M (money)
characteristics—1 is
highest (best) and 5 is
lowest (worst) score

SQL Server 2017, SQL Server Management Studio, Windows 10, Microsoft Corporation

Z06_KROE8149_09_SE_EXTC.indd 3 04/07/19 9:05 PM

C-4 Online Extension C Advanced Business Intelligence and Big Data

Susan Baker is above average in terms of how recently she has shopped and how fre-
quently she shops, but her purchases are average in value. Sally George is truly in the mid-
dle. Based on Jenny Tyler’s scores, she has not ordered in some time, but in the past, when
she did order, she ordered frequently, and her orders were of the highest monetary value.
These data suggest that Jenny may be going to another vendor. Someone from the sales
team should contact her immediately. However, no one on the sales team should be talking
to Chantel Jacobs. She has not ordered for some time, she does not order frequently, and
when she does order, she only buys inexpensive items and not many of them.

Reporting System Components
Figure C-3 shows the major components of a reporting system. Data from disparate data
sources are read and processed. As shown, reporting systems can obtain data from opera-
tional databases, data warehouses, and data marts.

A reporting system maintains a database of reporting metadata. The metadata describe
reports, users, groups, roles, events, and other entities involved in the reporting activity.
The reporting system uses the metadata to prepare and deliver appropriate reports to the
proper users in the correct format on a timely basis. As shown in Figure C-3, reports can be
prepared in a variety of media or formats. Figure C-4 lists report characteristics, which we
describe in more detail next.

Report Types Some reports are static reports. They are prepared once from the un-
derlying data, and they do not change. A report of the past year’s sales, for example, is a
static report. Other reports are dynamic reports—at the time of their creation, the reporting
system reads the latest, most current data and generates the report using those fresh data.
Reports on today’s sales and on current stock prices are dynamic reports.

Query reports are prepared in response to information entered by users. Google web
search is an example of a reporting system that uses query reports: You enter the keywords
you want to search for, and Google’s reporting system searches its database and generates a
response that is custom-built to your query and possibly your location or other factors.
Within a specific organization, such as Heather Sweeney Designs, a query report could be
generated to show current inventory levels. The user would enter item numbers, and the
reporting system would respond with inventory levels of those items.

Report Media As illustrated in Figure C-3 and summarized in Figure C-4, reports
are delivered via many different channels. Some reports are printed on paper or its elec-
tronic equivalents, such as PDF format. Other reports are delivered via Web portals.

FIGURE C-3

Components of a
Reporting System

Reporting
System

Metadata

Paper Report

Web Portal

Digital
Dashboard

E-mail Alert

Users

XML Web
Service

Operational
Database

Reporting
System

Author
Manage
Deliver

and/or

and/or

Data
Warehouse
Database

Data Mart
Database

Z06_KROE8149_09_SE_EXTC.indd 4 04/07/19 9:05 PM

Online Extension C Advanced Business Intelligence and Big Data C-5

An organization might place a sales report on the sales department’s Web portal and a re-
port on customers serviced on the customer service department’s Web portal.

A digital dashboard is an electronic display that is customized for a particular user.
Companies such as Google, MSN, and Yahoo! offer digital dashboard services that you
might have seen or used. Users can define the content they want to see—say, a local
weather forecast, a list of stock prices, and a list of news sources—and the vendor con-
structs a customized display for each user. Such pages are called, for example, myhomemsn.
com and My Yahoo!. Other dashboards are designed specifically for organizations.
Executives at a manufacturing organization, for example, might have a dashboard that
shows up-to-the-minute production and sales activities. These dashboards may be pro-
vided by companies such as IBM or SAP.

Reports can also be delivered via alerts. Users can indicate that they want to be noti-
fied of news and events by email or cell phone. “Smart cell phones” such as the iPhone and
those using the Android operating system are capable of displaying Web pages and can use
digital dashboards.

Finally, reports can be delivered to other information systems, sometimes using XML
or other Web technologies. This style of reporting is particularly useful for interorganiza-
tional information systems, such as supply chain management.

Report Modes The final report characteristic summarized in Figure C-4 is the report
mode. A push report is sent to users based on a predetermined schedule. Users receive the
report without any action on their part. In contrast, users must request a pull report. To
obtain a pull report, a user goes to a Web portal or digital dashboard and clicks a link or
button to cause the reporting system to produce and deliver the report.

Report System Functions
As shown in Figure C-3, report systems serve three functions: report management, report
authoring, and report delivery. Report management consists of defining who receives what
reports, when, and by what means. Most report management systems enable the report
system administrator to define user accounts and user groups and to assign users to one or
more groups. For example, all the salespeople would be assigned to the Sales group, all
upper-level management would be assigned to the Executive group, and so forth. All these
objects and assignments are stored in the reporting system metadata shown in Figure C-3.

Report authoring involves connecting to the required data sources, creating the report
structure, and formatting the report. Reports created using a report authoring system are
then assigned to groups and users. Assigning reports to groups saves the administrator
work; when a report is created, changed, or removed, the administrator need only change
the report assignments of the group, and all the users in the group will inherit the changes.
The report assignment metadata not only includes the user or group and the reports as-
signed but also indicates the format of the report that should be sent to the user, the chan-
nel by which the report will be delivered, and whether the report is to be pushed or pulled.

FIGURE C-4

Report Characteristics

Type Media Mode
Static Paper Push

Dynamic Web portal Pull

Query Digital dashboard

Online analytical
processing (OLAP)

Email/alert

XML Web service
and application
specific

Z06_KROE8149_09_SE_EXTC.indd 5 04/07/19 9:05 PM

C-6 Online Extension C Advanced Business Intelligence and Big Data

If it is to be pushed, the administrator declares whether the report is to be generated on a
regular schedule or as an alert based on a specific event in a database.

The report delivery function of a reporting system pushes reports or allows them to be
pulled based on the report management metadata. Reports can be delivered by hand or via
an email server, a Web portal, XML Web Services, or other program-specific means. The
report delivery system uses the operating system and other program security components
to ensure that only authorized users receive authorized reports, and it also ensures that
push reports are produced at appropriate times.

For query reports, the report delivery system serves as an intermediary between the
user and the report generator. It receives a user query request, such as the item numbers in
an inventory query, passes the query request to the report generator, receives the resulting
report, and delivers the report to the user.

DATA MINING
Instead of the basic calculations, filtering, sorting, and grouping used in reporting applica-
tions, data mining involves the application of sophisticated mathematical and statistical
techniques to find patterns and relationships that can be used to classify data and predict
future outcomes. As shown in Figure C-5, data mining represents the convergence of sev-
eral methodologies. Data mining techniques have emerged from the statistical and mathe-
matics disciplines and from the artificial intelligence and machine-learning communities. In
fact, data mining terminology embraces an odd combination of terms used by these differ-
ent disciplines.

Data mining techniques take advantage of developments for processing enormous da-
tabases that have emerged in the past two decades. Of course, all these data would not have
been generated were it not for fast and inexpensive computers, and without such comput-
ers, results from the new techniques would be impossible to produce in a reasonable
timeframe.

Many data mining techniques are sophisticated and difficult to use. However, such
techniques are valuable to organizations; and some business professionals, especially those
in finance and marketing, have developed expertise in their use. Almost all data mining
techniques require specialized software. Popular data mining products are Enterprise
Miner from SAS Corporation, SPSS Modeler from IBM, and HP Vertica Analytics
Platform from Hewlett-Packard. However, there is a movement to make data mining avail-
able to more users. For example, Oracle offers data mining functionality via the

FIGURE C-5

Convergence of
Disciplines for Data
Mining

Data
Mining Data

Management
Technology

Artificial Intelligence
Machine Learning

Statistics/
Mathematics

Huge
Databases

Cheap Computer
Processing and

Storage

Sophisticated
Marketing, Finance,
and Other Business

Professionals

Z06_KROE8149_09_SE_EXTC.indd 6 04/07/19 9:05 PM

Online Extension C Advanced Business Intelligence and Big Data C-7

“Oracle Advanced Analytics” option, with a GUI interface as part of SQL Developer.
Microsoft offers SQL Server Analysis Services as an add-on to SQL Server; it provides data
mining and other functionality. Data mining techniques fall into two broad categories: un-
supervised and supervised.

Unsupervised Data Mining
When using unsupervised data mining techniques, analysts do not create a model or hy-
pothesis prior to beginning the analysis. Instead, the data mining technique is applied to
the data and results are observed. After the analysis, explanations and hypotheses are cre-
ated to explain the patterns found.

One commonly used unsupervised technique is cluster analysis. With cluster analysis,
statistical techniques are used to identify groups of entities that have similar characteristics.
A common use for cluster analysis is to find customer groups in (1) order data and (2) cus-
tomer demographic data. For example, Heather Sweeney Designs could use cluster analy-
sis to determine which groups of customers are associated with the purchase of specific
products. For example, a cluster analysis could be created using the HSD-DW data table
to create the OLAP reports. In this case, the cluster analysis tool might indicate that there
are different sales patterns for the Dallas area and the non-Dallas area. For example, sales
of specific videos may differ markedly between the two clusters. Market basket analysis is
another form of unsupervised data mining. As this is one of the most common and impor-
tant forms of data mining, we will describe it in more detail next.

Market Basket Analysis Data mining techniques are usually complex. However, mar-
ket basket analysis is a data mining technique that can be readily implemented with pure
SQL (although we will not pursue that implementation in this book). All the major data
mining products have features and functions to perform market basket analysis. Market
basket analysis is also known as association rules.

Suppose that you run a diving shop, and one day you realize that one of your sales-
people is much better than others at up-selling your customers. Any of your sales associates
can fill a customer’s order, but this particular salesperson is especially able to sell customers
items in addition to those for which they ask. One day you ask him how he does it.

“It’s simple,” he says. “I just ask myself, ‘What is the next product they’ll want to buy?’
If someone buys a dive computer, I don’t try to sell her fins. If she’s buying a dive com-
puter, she’s already a diver, and she already has fins. But, look, these dive computer displays
are hard to read. A better mask makes it easier to read the display and get the full benefits
from the dive computer.” Thus, the market basket analysis might include an association
rule that says, “If a customer buys a dive computer, then that customer will also buy a
mask.” Clearly not all customers buying a dive computer will also buy a mask, of course, so
the market basket analysis will need to determine the likelihood that this will occur.

Market basket analysis is a data mining technique for determining such patterns and
rules. A market basket analysis shows the products that customers tend to purchase at the
same time. Several different statistical techniques can be used to generate a market basket
analysis. Here we discuss a technique that involves, as implied earlier, conditional
probabilities.

Figure C-6 shows hypothetical data from 1,000 transactions at a dive shop. The first
row of numbers (shaded blue) under each column is the total number of transactions that
include the product in that column. For example, the 270 near the top of the Mask column
means that 270 of the 1,000 transactions include the purchase of a mask. The 120 under
Dive Computer means that 120 of the 1,000 purchase transactions included a dive
computer.

Note that in this example, every transaction involves one or two items among those
listed in Figure C-6; those transactions with two different items will be counted in two col-
umns of the blue row. Also note that some of the 1,000 transactions do not contain any of
the five products listed in the table (e.g., somebody purchases a wet suit and nothing else).

Z06_KROE8149_09_SE_EXTC.indd 7 04/07/19 9:05 PM

C-8 Online Extension C Advanced Business Intelligence and Big Data

You can use the numbers in the blue row to estimate the probability that a customer
will purchase an item. Because 270 out of 1,000 transactions included a mask, you can esti-
mate the likelihood that a customer will buy a mask to be 270/1,000, or .27. Similarly, the
likelihood of a tank purchase is 200/1,000, or .2, and the likelihood of a fins purchase is
280/1,000, or 0.28.

The next five rows in this table show the occurrences of transactions that involve two
items. For example, the last column indicates that 50 transactions included both a dive
computer and a mask, 30 transactions included a dive computer and a tank, 20 included a
dive computer and fins, 10 included a dive computer and weights, 5 included a dive com-
puter and another dive computer (meaning the customer bought two dive computers), and
5 transactions had a dive computer and no other product. Note the symmetry in the table:
The numbers for (mask, fins) and (fins, mask) are the same, and so on.

These data are interesting, but we can refine the analysis by computing additional fac-
tors. Marketing professionals define support as the probability that two items will be pur-
chased together. From these data, the support for fins and mask is 150 out of 1,000, or .15.
Similarly, the support for dive computer and mask (the combination cited earlier by a sales-
person) is 50/1,000, or .05.

Confidence is defined as the probability of a customer buying one product, given that
he or she is buying another product. The confidence of fins, given that the customer is pur-
chasing a mask, is the number of purchases of fins and masks out of the number of pur-
chases of masks. Thus, in this example, the confidence is 150 out of 270, or .55556. The
confidence that a customer purchases a tank, given that the customer is purchasing fins, is 40
out of 280, or .14286. As another example, the confidence in the rule alluded to by our star
salesperson (“If a customer buys a dive computer, then that customer will also buy a mask”)
is 50>120 = .41667, or roughly 42 percent.

Lift is defined as the ratio of confidence divided by the base probability of an item
purchase. The lift for fins, given a mask, is the probability that a customer buys fins (given
that the customer is purchasing a mask) divided by the overall probability that the cus-
tomer buys fins. If the lift is greater than 1, then the probability of buying fins goes up
when a customer buys a mask; if the lift is less than 1, the probability of buying fins goes
down when a customer buys a mask.

For the data in Figure C-6, the lift for fins, given a mask purchase, is .55556/.28 or 1.98.
This means that when someone purchases a mask, the likelihood he or she will also pur-
chase fins almost doubles. The lift for fins, given a dive computer purchase, is 20/120 (the
confidence of fins, given a dive computer) divided by .28, the probability that someone buys
fins (280 of the 1,000 transactions involved fins). Therefore, 20/120 is .16667, and

FIGURE C-6

A Market Basket
Analysis Example

1,000
Transactions

Mask

Mask

Tank

Tank

Fins

Fins

Weights

Weights

Dive
Computer

270 200 280 130 120

20 20 150 20 50

20 80 40 30 30

150 40 10 60 20

20 30 60 10 10

50 30 20 10 5

1