Database systems
kittu_unadu
ExtensionWorkingwithSQl.PDF
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 Documentation to the Products/Features To Be Installed list.
13. Click the Next button. The MySQL Installer checks your computer to make sure all installa- tion requirements are met. If any requirement test fails, a message is displayed. If you have not installed the Microsoft Visual C++ Redistributable Package for Visual Studio 2019 or the Microsoft Visual Studio 2010 Tools for Office as discussed earlier, you will get an error message at this point. If all requirements are met, the MySQL Installer Installation screen is displayed, as shown in Figure A-2(g). This screen summarizes all the products that will be installed.
14. Click the Execute button. The MySQL Installer installs all the selected products, as shown in the Figure A-2(h). The installation status for each product is marked as Complete after it is installed.
FIGURE A-2
Installing MySQL 8.0
The MySQL Installer Select Products and Features screen
Select MySQL Work- bench 8.0—x64 is shown to match the Windows OS installed
Click this button to add your selection to Prod- ucts/Features To Be Installed
The Next button
Select MySQL for Excel 1.3—x86 (32-bit) is shown to match the version of Microsoft Office installed
(d) The MySQL Installer Select Products and Features Screen with Expanded Applications MySQL Installer, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 9 04/07/19 8:16 PM
A-10 Online Extension A Working with MySQL
FIGURE A-2
Installing MySQL 8.0
The Next button
The MySQL Installer Select Products and Features screen
Select Connector/ODBC 8.0—x86 (32-bit) is chosen to match the version of Microsoft Offfice installed
Select Connector/NET 8.0—x86 (32-bit) is shown to match the version of Microsoft Office installed
(e) The MySQL Installer Select Products and Features Screen with Expanded Connectors MySQL Installer, Oracle Corporation.
FIGURE A-2
Installing MySQL 8.0
The MySQL Installer Select Products and Features screen
Select MySQL Documentation 8.0— only the x86 (32-bit) version is available
Click this button to add your selection to Products/Features To Be Installed
The Next button
(f) The MySQL Installer Select Products and Features Screen with Expanded Documentation MySQL Installer, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 10 04/07/19 8:16 PM
Online Extension A Working with MySQL A-11
FIGURE A-2
Installing MySQL 8.0
The Execute button
The MySQL Installer Installation screen
These products will be installed
(g) The MySQL Installer Installation Screen
The MySQL Installer Installation screen
These products have been successfully installed
The Next button
FIGURE A-2
Installing MySQL 8.0
(h) The Completed MySQL Installer Installation Screen
MySQL Installer, Oracle Corporation.
MySQL Installer, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 11 04/07/19 8:16 PM
A-12 Online Extension A Working with MySQL
15. After the product installation for all products is complete, click the Next button. The Product Configuration screen is displayed, as shown in Figure A-2(i). We will now config- ure our installation of MySQL Server 8.0.
16. Click the Next button. The High Availability screen is displayed, as shown in Figure A-2(j). Because we are working with only one MySQL server, we will configure our installation of MySQL as a Standalone MySQL Server with Classic MySQL Replication. Note that it is possi- ble to cluster MySQL servers (i.e., use a group of servers), and you may wish to read more about InnoDB clusters by consulting the appropriate documentation. Be sure the Standalone MySQL Server / Classic MySQL Replication radio button is checked, and then click the Next button.
17. The Type and Networking screen is displayed, as shown in Figure A-2(k). We will configure our installation of MySQL as the default Development Computer with the default TCP/IP options. We will not need to use the advanced configuration options. Click the Next button.
18. The Authentication Method screen is displayed, as shown in Figure A-2(l). Click the Use Legacy Authentication Method (Retain MySQL 5.x Compatibility) radio button. While we would prefer to use the recommended strong password encrytion, we cannot guarantee comparability with Microsoft Office 2019. Click the Next button.
19. Click the Next button. The Accounts and Roles screen is displayed, as shown in Figure A-2(m). We will need to enter a password for the root user account. The root user is the name of the administrator user in MySQL, and this user has complete administra- tion privileges on the MySQL server. Choose a password, then enter it in both the MySQL Root Password and Repeat Password text boxes.
20. In order to add yourself as a MySQL server administrator, click the Add User button. The MySQL User Detail dialog box is displayed, as shown in Figure A-2(n). Enter your name as the Username, and enter a password. Leave the other settings as shown. When you are done, click the OK button to close the MySQL User Detail dialog box. We will need this user for the ODBC discussion later in this extension and for Chapter 7.
21. The Accounts and Roles screen is displayed again, as shown in Figure A-2(o) where you will see yourself added as a user and DB Admin. Click the Next button.
FIGURE A-2
Installing MySQL 8.0
The MySQL Installer Product Configuration screen
These products that will be configured
The Next button
(i) The MySQL Installer Product Configuration Screen MySQL Installer, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 12 04/07/19 8:16 PM
Online Extension A Working with MySQL A-13
The MySQL Installer High Availabilty screen
Select Standalone MySQL Server/Classic MySQL Replication
The Next button
FIGURE A-2
Installing MySQL 8.0
(j) The MySQL Installer High Availability Screen MySQL Installer, Oracle Corporation.
FIGURE A-2
Installing MySQL 8.0
The MySQL Installer Type and Networking screen
Select Development Computer as the configuration type
We will use the default TCP/IP connectivity settings
We do not need to use any advanced configuration settings
The Next button
(k) The MySQL Installer Type and Networking Screen MySQL Installer, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 13 04/07/19 8:16 PM
A-14 Online Extension A Working with MySQL
FIGURE A-2
Installing MySQL 8.0
The MySQL Installer Accounts and Roles screen
The Next button
The Add User button
Reenter the password
Enter a password for the MySQL root user account— the root user is the main MySQL administrator account
(m) The MySQL Installer Accounts and Roles Screen
The MySQL Installer Authentication Method screen
Select Use Legacy Authentication Method (Retain MySQL 5.x Compatibility) as the authentication method
The Next button
FIGURE A-2
Installing MySQL 8.0
(l) The MySQL Installer Authentication Method Screen MySQL Installer, Oracle Corporation.
MySQL Installer, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 14 04/07/19 8:16 PM
Online Extension A Working with MySQL A-15
FIGURE A-2
Installing MySQL 8.0
The MySQL User Account dialog box
Enter the User Name
Reenter the password
Enter a password for the new user name
The OK button
(n) The MySQL Installer MySQL User Account Dialog Box MySQL Installer, Oracle Corporation.
FIGURE A-2
Installing MySQL 8.0
(o) The Completed MySQL Installer Accounts and Roles Screen
The Next button
The MySQL Installer Accounts and Roles Screen
The added MySQL user account for Auer as a DB Admin
MySQL Installer, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 15 04/07/19 8:16 PM
A-16 Online Extension A Working with MySQL
22. The MySQL Installer Windows Service screen is displayed, as shown in Figure A-2(p). We will use the default settings on this screen, configuring MySQL server as a Windows service named MySQL80 that starts automatically when the operating system is started. Click the Next button. ■ NOTE: If you are reinstalling MySQL 8.0, you may have to use a different Windows
service name. We suggest adding the next portion of the version number, for example use MySQL8016 for MySQL version 8.0.16.0.
23. The Apply Configuration screen is displayed, which summarizes the steps that will be run to configure the server, as shown in Figure A-2(q). Click the Execute button to configure the MySQL server.
24. The Apply Configuration screen is displayed with the successfully completed steps indi- cated, as shown in Figure A-2(p). If any step had failed, there would be a message shown on this screen. Click the Finish button.
25. The Product Configuration screen is displayed, but now showing that the configuration is complete in Figure A-2(s). Click the Next button.
26. The Installation Complete screen is displayed, with the Start MySQL Workbench after Setup checkbox checked, as shown in Figure A-2(t). Click the Finish button.
27. The MySQL Installer dialog box closes, and the MySQL Workbench is opened and dis- played as shown in Figure A-3. The MySQL Workbench will be the main MySQL GUI utility we use for our work in MySQL 8.0.
28. Now is a good time to add the MySQL Workbench icon to the Windows Taskbar – this will make it much more convenient to open MySQL Workbench. Right-click the MySQL Workbench icon currently on the Taskbar, then click Pin to taskbar.
29. Click the MySQL Workbench Close [X] button to close MySQL Workbench.
The Next button
The MySQL Installer Windows Service Screen
The MySQL80 Windows service will run as a standard system account
MySQL server 8.0 will be configured as a Windows service
The Windows service name will be MySQL80
MySQL server 8.0 will be automatically started when this computer is started
FIGURE A-2
Installing MySQL 8.0
(p) The MySQL Installer Windows Service Screen MySQL Installer, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 16 04/07/19 8:16 PM
Online Extension A Working with MySQL A-17
FIGURE A-2
Installing MySQL 8.0
The MySQL Installer Apply Configuration screen
The MySQL server has been configured with these steps—if there had been any problems, error messages would appear on the screen
The Finish button
(r) The Successful MySQL Installer Apply Configuration Screen
The Execute button
The MySQL Installer Apply Configuration screen
The MySQL server will be configured with these steps
FIGURE A-2
Installing MySQL 8.0
(q) The MySQL Installer Apply Configuration Screen MySQL Installer, Oracle Corporation.
MySQL Installer, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 17 04/07/19 8:16 PM
A-18 Online Extension A Working with MySQL
FIGURE A-2
Installing MySQL 8.0
The Finish button
The MySQL Installer Installation Complete Screen
The MySQL Workbench will be started when the Finish button is clicked
(t) The MySQL Installer Installation Complete Screen
FIGURE A-2
Installing MySQL 8.0
The MySQL Installer Product Configuration screen is displayed again, but now showing that MySQL Server 8.0.16 in properly configured
The Next button
MySQL Server 8.0.16 showing a status of Configuration complete
(s) The MySQL Installer Product Configuration Screen After Configuration is Complete MySQL Installer, Oracle Corporation.
MySQL Installer, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 18 04/07/19 8:16 PM
Online Extension A Working with MySQL A-19
FIGURE A-3
The MySQL Workbench
The MySQL Workbench Home tab
The MySQL Workbench Close [X] button
The MySQL Connections button—MySQL connections connect MySQL Workbench to MySQL servers
The default local instance MySQL80 connection
The MySQL Workbench icon on the taskbar
How Do I Create a Workspace for the MySQL Workbench Files? Before using the MySQL Workbench, we recommend creating a folder named MySQL Workbench under the Documents folder (or whatever your main data storage area is named). In Windows, this can be done using Windows Explorer, as shown in Figure A-4. In this workspace, create two folders, EER Models (for database designs) and Schemas (for database scripts). In the Schemas folder, create a subfolder for each database project each time you start a new database project.
How Do I Start the MySQL Workbench? To start the MySQL Workbench running in Microsoft Windows 10, click the Windows Start button, then scroll down the apps menu to the MySQL folder, and then click the MySQL Workbench 8.0 CE icon.4 The MySQL Workbench splash screen is displayed, followed by the MySQL Workbench window with the Home page displayed, as shown in Figure A-5.
The MySQL Workbench Home tab is a dashboard allowing us access to MySQL database development, MySQL DBMS administration, and MySQL database de- sign (mislabeled as “Models”), SQL database development and MySQL DBMS
4Alternatively, we recommend that you pin MySQL Workbench 8.0 CE to the Taskbar, as shown in Figure A-4, and start the MySQL Workbench from this icon. The CE designation shows that this is the open source, freely downloadable community edition – there is also a commercial version available that includes additional functionality.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation | Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 19 04/07/19 8:16 PM
A-20 Online Extension A Working with MySQL
FIGURE A-5
The MySQL Workbench Home Tab with the Local instance MySQL80 button
The MySQL Workbench Home tab
The MySQL Connections button—MySQL connections connect MySQL Workbench to MySQL servers
The MySQL Models button— the Models page is used to create database designs as described later
The default local instance MySQL80 connection- connect as the root user
The MySQL Workbench icon on the taskbar
FIGURE A-4
The MySQL Workbench Folder in File Explorer
The Documents folder
Create a folder named MySQL Workbench under the Documents folder
Create a folder named EER-Models under the MySQL Workbench folder
Create a folder named Schemas under the MySQL Workbench folder
Pin the MySQL Workbench icon to the taskbar
File Explorer, Windows 10, Microsoft Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation | Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 20 04/07/19 8:16 PM
Online Extension A Working with MySQL A-21
administration. There are several means of accessing the features provided, and we will only cover some basic ones here.
To connect to a MySQL DBMS engine, we use the Local instance MySQL80.
Connecting to a MySQL DBMS in MySQL Workbench
1. Click the Local instance MySQL80 button. The Connect to MySQL Server dialog box is displayed, as shown in Figure A-6.
2. Type in the password for the username for the connection in the Password text box. In this case, we are using the password we created for the user root.
3. We recommend that you do not save the password in the vault. 4. Click the OK button. The user is logged in, and the MySQL Workbench Connection
window is displayed (with the tab labeled as Local instance MySQL80), as shown in Figure A-7(a). Note the sections of the MySQL Connection shown in Figure A-7(a) in this initial display, including the Navigator window with an Administration tab and a Schemas tab, the toolbar, the Query # (currently 1) window, the SQL Additions window, the Output window, the Information window, and the window control buttons.
5. The Navigator window is where we control our actions in MySQL Workbench. By default, it opens with the Administration tab open. As shown in Figure A-7(a), the Navigator Administration tab includes sections named MANAGEMENT, INSTANCE and PERFORMANCE. We will use the features in the MANAGEMENT section when we discuss database administration in Section 6 of “Working with MySQL.” For now, how- ever, we need to work with MySQL schemas (databases), so click the Navigator Schemas tab to select it.
FIGURE A-6
The Connect to MySQL Server Dialog Box
The Connect to MySQL Server dialog box
Click the OK button
Type the password for the root user in the password textbox
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 21 04/07/19 8:16 PM
A-22 Online Extension A Working with MySQL
The SQL Additions window
The Open/Close Navigator Window button
The Open/Close Output Window button
The Open/Close SQL Additions Window button
The Output Window
The Administration tab
The Navigator Window
The SQL Editor Window
The Schemas tab
FIGURE A-7
The Connection Window and SQL Editor Tab
(a) The Navigator Administration Tab
The SQL Editor Window
The Navigator Window
The sys schema
The Schemas tab
The Output Window
The Information Window
The Schemas window in the Navigator
(b) The Navigator Schema Tab
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 22 04/07/19 8:16 PM
Online Extension A Working with MySQL A-23
SECTION 2 – CREATING AND USING A MySQL DATABASE Now that the MySQL DBMS is installed and we know how to open and close the MySQL Workbench, we can create a new database. We will create a database named Art_Course_ Database to match the Art Course Database we used in Chapters 1 and 2.
While we will discuss SQL statements in detail in Chapter 3 in the book, at this point you need to understand that to create a database, which is called a schema in MySQL, MySQL uses SQL commands, which can be stored in an SQL script. An SQL script is a set of one of more related SQL statements. Scripts, which are named with the *.sql extension, can be stored and reused, and are efficient for processing groups of SQL statements.
How do I Create a MySQL Database? To create a database in MySQL Workbench, start by opening the MySQL Workbench and then opening the Local instance MySQL80, as discussed in the previous section. The MySQL Workbench and the open tabbed connection window named Local Instance MySQL80 in our preferred configuration for working with MySQL databases is shown in Figure A-7(b).
In Figure A-8, we can see some features of the Navigator window and the Connection window toolbar. Note that MySQL Workbench uses long but very descriptive names for the buttons on the MySQL Workbench toolbars. Here are the definitions of some important ones on the Connection window toolbar, followed by a shortened name that we will use in the text to simplify our discussions:
1. The Create a new SQL tab for executing queries button (Create a new SQL tab) – we will use this to open a new SQL editor tab.
2. The Open a SQL script file in a new query tab button (Open an existing SQL script) – we will use this to open an existing SQL script file.
3. The Create a new schema in the connected server button (Create a new schema) – it is used to create a new database (schema).
4. The Reconnect to DBMS button (Reconnect) – it is used to reestablish the connection to a MySQL database server as needed after some actions.
5. The Refresh SCHEMAS button (Refresh SCHEMAS) – it is used to refresh the objects displayed in the Navigator after actions are taken by SQL statements or SQL scripts.
Now we will create the Art Course Database (which name we will rewrite with underscores as Art_Course_Database for use as a MySQL schema name) that we used as our example database in Chapters 1 and 2.
6. In the upper-right hand corner of the Connection window are three buttons that control the display of the Navigator, the SQL Additions window, and the Output window. We will always leave the Navigator displayed, and we will open and close the Output window as needed. However, we will not use the SQL Additions window in this text, so click the Open/Close SQL Additions Window button to close the SQL Additions window.
7. We can adjust the width of the Navigator window (and the Information window at the same time) by dragging the boundary between the Navigator window and the SQL Editor win- dow. Using this method to increase the width of the Navigator window, followed by click- ing the SQL Editor tab, the MySQL Workbench now appears as shown in Figure A-7(b). This is our preferred configuration of the MySQL Workbench tabbed connection window for our work creating and using MySQL databases.
Now that we have successfully installed MySQL 8.0 and opened the MySQL Work- bench, we can close MySQL Workbench. In the next section of “Working with MySQL” we will discuss how to create a database using MySQL Workbench.
Closing MySQL Workbench
1. Click the Close [X] button in the top-right corner of MySQL Workbench.
Z04_KROE8149_09_SE_EXTA.indd 23 04/07/19 8:16 PM
A-24 Online Extension A Working with MySQL
FIGURE A-8
The Navigator Window and Connection Window Toolbar
The Open a SQL script file in a new query tab button
The Create a new SQL tab for executing queries button
The Create a new schema in the connected server button
The Reconnect to DBMS button
The Refresh SCHEMAS button
The SCHEMAS tab
Creating a MySQL Database:
1. Click the Create a new schema button shown in Figure A-8. 2. The new_schema tabbed window is displayed, as shown in Figure A-9. 3. Type the new schema (database) name Art_Course_Database in the Name textbox, as
shown in Figure A-9, and then click the Apply button. The Apply Changes to Object warning dialog box will declare that the schema name will be in lowercase letters as “art_course_database” (MySQL only allows lowercase letters in schema names) as shown in Figure A-10. Click the OK button.
4. To create a new schema, MySQL uses an SQL script, which is a set of one or more SQL commands. At this point, you do not need to understand the details of this process (SQL is discussed in Chapter 3 in the text), but simply be aware of how MySQL is creating the schema. As shown in Figure A-11, an Apply SQL Script to Database dialog box with a Review SQL Script page is displayed so that the user can review the SQL command before it is executed. Click the Apply button.
5. As shown in Figure A-12, the Apply SQL Script to Database dialog box with an Apply SQL Script page is displayed with the results of executing the SQL command. Click the Finish button.
6. The art_course_database - Schema (the renamed new_schema – Schema) SQL Editor tab is displayed again in Figure A-13. Click the X (Close) button on the art_course_database – Schema Schema tab.
7. The art_course_database schema object now appears in the Navigator, as shown in Figure A-14. Note that although we entered the schema name as Art_Course_Database (with initial uppercase letters), MySQL insists on displaying it as art_course_database (all lowercase). If you want to make sure the Object Browser objects are displayed correctly, then click Refresh SCHEMAS button (as shown in Figure A-8) which will refresh the display in the Object Browser.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 24 04/07/19 8:16 PM
Online Extension A Working with MySQL A-25
FIGURE A-9
The new_schema Tab
The new_schema – Schema tab
Type the database (schema) name in the Name textbox
Click the Apply button
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
FIGURE A-10
The Apply Changes to Object Warning Dialog Box
Type Apply Changes to Object warning dialog box
Click the OK button to use the lowercase schema name art_course_database
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 25 04/07/19 8:16 PM
A-26 Online Extension A Working with MySQL
FIGURE A-12
The Apply SQL Script to Database – Applying SQL Script to the Database Dialog Box
Type Apply SQL Script to Database dialog box
The Apply SQL Script page
The SQL statements have been run
Click the Finish button
FIGURE A-11
The Apply SQL Script to Database – Review the SQL Script to be Applied on the Database Dialog Box
The Apply SQL Script to Database dialog box
The Review SQL Script page
The SQL statements to be run—these SQL statements can be edited
Click the Apply button
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 26 04/07/19 8:16 PM
Online Extension A Working with MySQL A-27
FIGURE A-14
The New Database (Schema) Object in the Navigator
The Query {number} tabbed window is available to create and edit SQL statements and scripts
The art_course_database database (schema) object in the Navigator
FIGURE A-13
The art_course_database Schema (Database) in the Navigator
Click the close button on the art_course_database – Schema tab
The art_course_database schema object in the Navigator
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 27 04/07/19 8:16 PM
A-28 Online Extension A Working with MySQL
FIGURE A-15
The Set as Default Schema Command
Right-click the art_course_database schema object to display the shortcut menu, and then click the Set as Default Schema command to make art_course_database the default (active) schema
How Do I Set the Active Database in MySQL? To work with a MySQL database, you must select it as the active database. In MySQL terms, this is called the default schema. To set the default schema, select the name of the database that you want to work with in the Default Schema drop-down list.
Setting the Default Schema:
1. Click the art_course_database schema object in the SCHEMAS section of the Navigator to select it.
2. Right-click the art_course_database schema object to display the shortcut menu, as shown in Figure A-15.
3. In the shortcut menu, click the Set as Default Schema command. 4. The art_course_database schema is set as the active schema (default schema), and the art_
course_database schema object is displayed in bold text in the Navigator to indicate that it is the active schema, as shown in Figure A-16. Figure A-16 also illustrates an alternative technique to set the default schema – the use of the SQL USE {Schema Name} statement. When this statement is run (one way to do this is to click the Execute SQL Statement [under the keyboard cursor] button, which is used to run individual SQL statements), it will set the default schema. The advantage of this method is that the SQL statement can precede and be combined with other SQL statements to ensure that the SQL statements are applied to the correct database.
How Do I Work with SQL Statements and SQL Scripts in MySQL? Because we have already argued that you need to know how to write and use SQL state- ments instead of relying on GUI tools, we come back to simply using SQL as the basis for our work. But we do not want to use a command-line utility, and we are not going to use the GUI tool in GUI mode, so what is left?
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 28 04/07/19 8:16 PM
Online Extension A Working with MySQL A-29
FIGURE A-16
The SQL USE {SchemaName} Statement
The Execute the statement under the keyboard cursor button
The USE art_course_database SQL statement—running this statement will set art_course_database as the default (active) schema
The art_course_database schema is now the default (active) schema as indicated by the schema name displayed in bold
The answer is that the MySQL Workbench provides us with an excellent SQL editing environment. This lets us take advantage of GUI capabilities while still working with text- based SQL statements. We do this by opening and using an SQL Editor window in which to create and edit our SQL statements. Note that when we connect to the MySQL server, the SQL Editor window is opened with a tabbed SQL Query window open by default. Thus, we already have one available. We can open others, as needed, by using the File | New Query Tab command or by clicking the Create a new SQL tab button.
The SQL editing environment in a MySQL Script window will be our tool of choice for editing SQL DDL statements. One advantage of using this SQL Editor is the ability to save and reuse SQL Scripts. For MySQL, SQL scripts are plaintext files labeled with the *.sql file extension. We can save, open, and run (and rerun) SQL scripts.
By default, MySQL will save scripts in the user’s Documents folder. Because this does not separate MySQL files from other data files, we recommend using the folder structure we created earlier in this chapter and shown in Figure A-4 with a MySQL Workbench folder, with separate folders named EER Models and Schemas, and with a separate folder for each database project, such as Art_Course_Database, under it.
As discussed earlier, an SQL script is composed of one or more SQL statements. SQL scripts can include SQL script comments. SQL script comments are lines of text that do not run when the script is executed but are used to document the purpose and contents of the script. Each comment line begins with the characters /* (slash asterisk) and ends with the characters */(asterisk slash).
How Can I Use Existing SQL Scripts? Another advantage of SQL scripts is that we can use scripts written by others. To do this, we simply store the script in an appropriate location on our computer, open it in an SQL script tabbed window, and execute it.
To illustrate this, we will build the Art_Course_Database database to run the example SQL query statements. This will then allow you to run the SQL query shown in Figure 1-29 in an actual database as a preview of the work on SQL you will do in Chapter 3.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 29 04/07/19 8:16 PM
A-30 Online Extension A Working with MySQL
FIGURE A-17
The Open SQL Script Dialog Box
The Open an existing SQL Script button—use this button to open a script in a new query tabbed window instead of the currently open query window
The Open SQL Script dialog box—browse to the DBC-e09-MySQL-Art- Course-Database- Create-Tables.sql file
The Open button
Opening and Running an Existing SQL Script
1. In your Documents/MySQL Workbench/Schemas folder, create a folder named Art_Course_Database.
2. The SQL scripts needed to build the Art_Course_Database database are available at www.pearsonhighered.com/kroenke. Go to the Database Concepts 9/e Companion Web site, and download the Student Data Files in the ZIP archive file named StudentDataFiles .zip to your Downloads folder. After downloading, you will need to extract the files.
3. After you have extracted the files, copy the two files in the Downloads/StudentDataFiles/ Schemas/ArtCourseDB folder to your Documents/MySQL Workbench/Schemas/Art_ Course_Database folder.
4. Click the Open an existing SQL script file button (note that although we prefer the syntax “an SQL,” MySQL itself uses “a SQL”) shown in Figure A-17 to display the Open SQL Script dialog box (alternatively, we can use the File | Open SQLScript menu command to open the dialog box). The advantage of this button is that the script will be opened in a new query tabbed window, not the Query 1 tabbed window that we currently have opened.
5. Browse to the DBC-e09-MySQL-Art-Course-Database-Create-Tables.sql SQL script. 6. Click the Open button. The DBC-e09-MySQL-Art-Course-Database-Create-Tables.sql
SQL script is displayed in a new SQL query tabbed window, as shown in Figure A-18. 7. Click the Execute the selected portion of the script or everything, if there is no
selection button. The SQL script is run, and the Art Course Database tables are created, as shown in Figure A-19. Note that we have resized the Output window, which displays the script actions and their results (if there is an error, the error message will be displayed here).
■ NOTE: The button titles appear when the mouse cursor is placed over them. The MySQL folks seem to be very creative when naming buttons, so that there is no doubt about exactly what the button does!
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 30 04/07/19 8:16 PM
Online Extension A Working with MySQL A-31
FIGURE A-18
The Art_Course_Database Database Create Tables Script
The DBC-e09- MySQL-Art-Course- Database-Create- Tables.sql SQL script
The Execute the selected portion of the script or everything, if there is no selection button—use this button to run the entire script
FIGURE A-19
The Art_Course_Database Database Table Objects in the Navigator
The art_course_database table objects created by the SQL script
The execution of the SQL script is displayed in the Output window
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 31 04/07/19 8:16 PM
A-32 Online Extension A Working with MySQL
FIGURE A-20
Populating the Art_Course_Database Database Tables
The art_course_database table objects have been populated by the SQL script
The execution of the SQL script is displayed in the (resized) Output window
The DBC-e09- MySQL-Art- Course- Database-Insert- Data.sql SQL script
8. Click the Open an existing SQL script file button to display the Open SQL Script dialog box.
9. Browse to the DBC-e09-MySQL-Art-Course-Database-Insert-Data.sql SQL script. 10. Click the Open button. The DBC-e09-MySQL-Art-Course-Database-Insert-Data.sql SQL
script is displayed in a new SQL query tabbed window. 11. Click the Execute the selected portion of the script or everything, if there is no selection
button. The SQL script is run, and the Art Course Database tables are populated with data, as shown in Figure A-20. Note that we have resized the Output window.
12. In the DBC-e09-MySQL-Art-Course-Database-Insert-Data.sql SQL script tabbed window, click the Close [X] button to close this tabbed window.
13. In the DBC-e09-MySQL-Art-Course-Database-Create-Tables.sql SQL script tabbed win- dow, click the Close [X] button to close this tabbed window.
We have now created the art_course_database schema itself, and then created the tables for the database and populated them with data. All by using preexisting SQL scripts. We will now use the art_course_database to run an SQL query on the data. We will use the same query that was used for Figure 1-29, which is:
/* *** SQL-Query-ExtA-01 *** */
SELECT CUSTOMER.CustomerLastName,
CUSTOMER.CustomerFirstName, CUSTOMER.Phone,
COURSE.CourseDate, ENROLLMENT.AmountPaid,
COURSE.Course, COURSE.Fee
FROM CUSTOMER, ENROLLMENT, COURSE
WHERE CUST OMER.CustomerNumber = ENROLLMENT.CustomerNumber
AND COURSE.CourseNumber = ENROLLMENT.CourseNumber;
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 32 04/07/19 8:16 PM
Online Extension A Working with MySQL A-33
After running the SQL query, we will save it as an SQL script for reuse as needed.
Running an SQL Query in MySQL Workbench
1. As shown in Figure A-21, enter the SQL statement for the SQL SELECT statement, and then click the Execute the statement under the keyboard cursor button.
2. The SQL SELECT statement is run, and the query results are displayed in the Result 1 tabbed result grid, as shown at the bottom of Figure A-21. These results confirm that the Art Course Database data was successfully entered into the art_course_database database.
3. As discussed earlier, we can save this query as an SQL script for later use. Click the Save the script to a file button (floppy disk icon) to open the Save SQL Script dialog box as shown in Figure A-22. Browse to the Documents/MySQL Workbench/Schemas/DBC-e09-Art- Course-Database folder, and then save this SQL query as SQL-Query-ExtA-01.sql.
4. When the SQL query is saved, the name of the tabbed SQL Editor window will be changed form Query 1 to SQL-Query-ExtA-01. In the SQL-Query-ExtA-01 tabbed window, click the Close [X] button.
We have now created an SQL script of the query that we can reopen and rerun any time we need to. This is a powerful tool when working with databases. This completes our work in this section of “Working with MySQL.”
Closing MySQL Workbench
1. Click the Close [X] button in the top-right corner of MySQL Workbench.
FIGURE A-21
Querying the Art_Course_Database Tables
The Query 1 tabbed SQL Editor window
The SQL SELECT statement to query the database
The Save the script to a file button
The results of running the query in the Result 1 Result Grid window
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 33 04/07/19 8:16 PM
A-34 Online Extension A Working with MySQL
FIGURE A-22
Saving the SQL Query as an SQL Script
The Save SQL script dialog box
The Save the script to a file button
Type the file name here
The Save button
SECTION 3 – USING SQL IN MySQL Now that we know how to use existing SQL scripts and how to create and save SQL scripts for SQL query statements, we will discuss how to use SQL scripts to do the SQL tasks discussed in Chapter 3. This will include creating and populating database tables of our own, and how to run SQL queries against that data. To illustrate this, we will use the Wall- ingford Motors CRM database, which is the database used in the sections of “Working with Microsoft Access.” This will give us a chance to compare how each of these DBMS Prod- ucts handles various tasks. In this chapter, we will discuss the specific implementation of the WMCRM database in MySQL 8.0, and use that implementation to introduce some MySQL specific topics not covered in Chapter 3. You should work through this section of “Working with MySQL” before you complete Chapter 3 so that you can use the techniques discussed here to create and use the WP database and SQL statements described in Chapter 3.
All database systems, whether relational or non-relational, must accomplish the same tasks. In terms of data handling, it is sometimes said that database systems must be capable of CRUD.
CRUD is an acronym that stands for create, read, update and delete. These are the four actions that all database systems must be able to do with data:
• Create (store) data in the database • Read the data that exists in the database • Update (modify) the data that exists in the database • Delete data that exists in the database
BTW
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 34 04/07/19 8:16 PM
Online Extension A Working with MySQL A-35
Creating the WMCRM Database We discussed how to create a new MySQL database (a schema in MySQL syntax) in Section 1 of “Working with MySQL.” We will use the same steps here.
Opening MySQL Workbench and a Connector Window:
1. Open MySQL Workbench. 2. Click the Local instance MySQL80 connector button. 3. The Connect to MySQL Server dialog box is displayed. Enter the password for the root
user, and then click the OK button. 4. The Local instance MySQL80 connector window is displayed.
Creating the WMCRM Database:
1. Click the Create a new schema button. 2. The new_schema tabbed window is displayed. Type the new schema (database) name
WMCRM in the Name textbox, and then click the Apply button. A warning dialog box will declare that the schema name will be in lowercase letters as “wmcrm” (MySQL only allows lowercase letters in schema names). Click the OK button.
3. The Apply SQL Script to Database – Review the SQL Script to be Applied on the Database dialog box is displayed so that you can review the SQL command before it is executed. The command is correct, so click the Apply button.
4. The Apply SQL Script to Database - Applying SQL Script to the Database dialog box is displayed with the results of executing the SQL command. Click the Finish button.
5. The wmcrm – Schema database (the renamed new_schema) tabbed window is displayed again. Click the Close [X] button on the wmcrm – Schema tab.
6. The wmcrm schema object now appears in the Navigator, as shown in Figure A-23. If you want to make sure the Object Browser objects are displayed correctly, then click Refresh SCHEMAS button which will refresh the display in the Object Browser.
Now we need to set the wmcrm database as the default schema.
Setting the Default Schema:
1. Click the wmcrm schema object in the SCHEMAS section of the Navigator to select it. 2. Right-click the wmcrm schema object to display the shortcut menu. 3. In the shortcut menu, click the Set as Default Schema command. 4. The wmcrm schema is set as the active schema (default schema), and the wmcrm schema
object is displayed in bold text in the Navigator to indicate that it is the active schema.
How do I use SQL Statements to Create Table and Relationship Structures? In Chapter 3, we discussed the SQL statements needed to create tables and the relation- ships between the tables. We will use similar SQL statements to create the needed table and relationship structure for the WMCRM database. We will store these SQL statements as an
Relational database systems accomplish these tasks using Structured Query Language (SQL), as we discuss in detail in Chapter 3 and online Extension B, “Advanced SQL.” The SQL commands for these actions are: • INSERT – Store (create) data • SELECT – Read and query the data • UPDATE – Modify the data • DELETE – Remove data
Z04_KROE8149_09_SE_EXTA.indd 35 04/07/19 8:16 PM
A-36 Online Extension A Working with MySQL
SQL Script, and then run that script to actually create the tables and relationships between them. At the same time, we will create a folder in the MySQL Workbench/Schemas folder to hold the SQL scripts we create.
Creating and Saving the SQL Script to Create the WMCRM Table Structure
1. Because we opened MySQL Workbench itself, the Query 1 SQL Editor tab should be open. If it isn’t (which would be the case if you didn’t close MySQL Workbench at the end of Section 2), then click the Create a new SQL tab for executing queries button to open a tabbed window, as shown in Figure A-24.
2. In the open tabbed SQL Query window, type the SQL comments and the SQL USE wmcrm statement shown in Figure A-24 as the beginning of the SQL Script.
3. Click the Save the Script to a File button. The Save SQL Script dialog box is displayed, as shown in Figure A-25.
4. The Save SQL Script dialog box opens to the Documents/MySQL Workbench/Schemas/ Art-Course-Database folder. Browse up one level to the Documents/MySQL Workbench/ Schemas folder.
5. Click the New folder button in the Save SQL Script dialog box. A new folder object is displayed.
6. Type the folder name WMCRM-Database as the new folder name, and then click the Open folder button two (2) times in the Save SQL Script dialog box. The new folder is created, and we move to that folder.
7. Type the file name WMCRM-Create-Tables in the File name text box of the Save SQL Script dialog box, and then click the Save Button on the Save Script to File dialog box. The script is saved.
The MySQL 8.0 version of the SQL CREATE TABLE statements for the WMCRM database is shown in Figure A-26. The set of tables included here includes all the WMCRM
FIGURE A-23
The wmcrm Database Object in the Navigator
The wmcrm database (schema) object expanded to show the subobjects
The Refresh SCHEMAS button
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 36 04/07/19 8:16 PM
Online Extension A Working with MySQL A-37
FIGURE A-24
The WMCRM Create Tables Script in MySQL Workbench
The Save the Script to a File button
The WMCRM Create Tables SQL script comments
The USE wmcrm SQL statement
FIGURE A-25
Saving the SQL Script
The Save the script to a file button
The Save SQL Script dialog box
Click here to move up one level in the folders
The New folder button
The Save button—this will be an Open button when creating a new folder
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 37 04/07/19 8:16 PM
A-38 Online Extension A Working with MySQL
tables used in sections 1 through 5 of “Working with Microsoft Access.” The database schema (where schema is used in its meaning of a summary of the database structure) is:
VEHICLE (InventoryID, Model, VIN) SALESPERSON (NickName, LastName, FirstName, HireDate, WageRate,
CommissionRate, OfficePhone, EmailAddress, InventoryID) CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP,
EmailAddress, NickName) PHONE_NUMBER (CustomerID, PhoneNumber, PhoneType) CONTACT (ContactID, CustomerID, ContactDate, ContactType, Remarks)
Where
InventoryID in SALESPERSON must exist in InventoryID in VEHICLE NickName in CUSTOMER must exist in NickName in SALESPERSON CustomerID in PHONE_NUMBER must exist in CustomerID in CUSTOMER CustomerID in CONTACT must exist in CustomerID in CUSTOMER
Note that every DBMS product has a set of reserved words, and you should avoid using them as table names or column names. You can find a list of the MySQL 8.0 reserved words in the documentation.5
FIGURE A-26
The SQL Statements to Create the WMCRM Table Structure
CREATE TABLE VEHICLE( InventoryID Int NOT NULL AUTO_INCREMENT, Model Char(25)
(35) NOT NULL,
NickName Char(35) NOT NULL, LastName Char(25) NOT NULL, FirstName Char(25) NOT NULL, HireDate Date NOT NULL,
(5,2)WageRate Numeric NOT NULL DEFAULT 12.50, 5,3CommissionRate Numeric( ) NOT NULL,
OfficePhone Char(12) NOT NULL, EmailAddress Varchar( )100 NOT NULL UNIQUE, InventoryID Int NULL UNIQUE, CONSTRAINT SALESPERSON_PK PRIMARY KEY(NickName),
VEHICLE(InventoryID)REFERENCES ON DELETE CASCADE
CONSTRAINT SALESP_VEH_FK FOREIGN KEY(InventoryID)
VIN Char NOT NULL, CONSTRAINT VEHICLE_PK PRIMARY KEY (InventoryID) );
);
CREATE TABLE SALESPERSON(
5For a complete list of MySQL 8.0 reserved keywords, see the MySQL 8.0 documentation “Keywords and Reserved Words” in Section 9.3.
Z04_KROE8149_09_SE_EXTA.indd 38 04/07/19 8:16 PM
Online Extension A Working with MySQL A-39
MySQL supports surrogate keys, and the surrogate key columns are created using the AUTO_INCREMENT attribute with the primary key. Note that by default AUTO_ INCREMENT starts at 1 and then increments by adding 1 to the previous surrogate key value each time a new key is created. We can change the starting value by using an ALTER TABLE statement, but you cannot change the value of the increment. This somewhat limits the usefulness of AUTO_INCREMENT, but it should be usable in many cases.
In the WMCRM database, we have the following surrogate keys that can be implemented with AUTO_INCREMENT.
InventoryID in VEHICLE Start at 1 Increment by 1 CustomerID in CUSTOMER Start at 1 Increment by 1 ContactID in CONTACT Start at 1 Increment by 1
Note that it is possible to directly insert AUTO_INCREMENT values when inserting data. We will use this feature when inserting CustomerID values because of the missing CustomerID value of 2 in the Microsoft Access version of the WMCRM database. This was caused when we illustrated deleting and reinserting a customer record for Jessica Christman in Section 1 of “Working with Microsoft Access.”
CREATE TABLE CUSTOMER( CustomerID Int NOT NULL AUTO_INCREMENT, LastName Char(25) NOT NULL,
CustomerID Int NOT NULL, PhoneNumber Char(12) NOT NULL, PhoneType Char(25) NULL,
Address Char(35) NULL, City Char(35) NULL, State Char(2) NULL,
(10)ZIP Char NULL, (100)EmailAddress Varchar NULL,
35NickName Char( ) NOT NULL,
CONSTRAINT PHONE_NUM_PK PRIMARY KEY(CustomerID, PhoneNumber),
CUSTOMER(CustomerID)REFERENCES CONSTRAINT PHONE_CUS_FK FOREIGN KEY(CustomerID)
CONSTRAINT CUSTOMER_PK PRIMARY KEY(CustomerID),
SALESPERSON(NickName)REFERENCES ON UPDATE CASCADE
CONSTRAINT CUS_SALESP_FK FOREIGN KEY(NickName)
(25)FirstName Char NOT NULL,
);
ON DELETE CASCADE
);
CREATE TABLE PHONE_NUMBER(
CREATE TABLE CONTACT ( ContactID Int NOT NULL AUTO_INCREMENT, CustomerID Int NOT NULL,
ContactType Char(10) NOT NULL, Remarks Varchar(255) NULL, CONSTRAINT CONTACT_PK PRIMARY KEY(ContactID),
CUSTOMER(CustomerID)REFERENCES CONSTRAINT CONTACT_CUS_FK FOREIGN KEY(CustomerID)
ContactDate Date NOT NULL,
ON DELETE NO ACTION
);
FIGURE A-26 (Continued)
Z04_KROE8149_09_SE_EXTA.indd 39 04/07/19 8:16 PM
A-40 Online Extension A Working with MySQL
FIGURE A-27
The WMCRM-Create-Tables Script in MySQL Workbench
The Execute SQL Script button
The named and saved SQL script in the tabbed script window
Believe it or not, the full name of the button that we have labeled as the Execute SQL Script button is the Execute the selected portion of the script or everything, if there is no selection button, according to the mouse roll-over tool tip. To see this for yourself, move the mouse over the button and leave it there for a few sec- onds. A tool tip window will open with the full name of the button. The full name is very descriptive and accurate but a bit long to be used in our instructions!
BTW
Data types for MySQL 8.0 are shown in Figure 3-5(a). Data types for each table are shown in Sections 1 through 5 of “Working with Microsoft Access” as Microsoft Access data types, and have been converted to the corresponding MySQL 8.0 data types. Similarly, col- umns indicated as required for Microsoft Access are created as NOT NULL in MySQL 8.0.
Completing and Saving the SQL Script to Create the WMCRM Table Structure
1. In the WMCRM-Create-Tables script, type the SQL statements (including the remarks) as shown in Figure A-26. The SQL script now appears as shown in Figure A-27.
2. Click the Save the script to a file button. The updated SQL script is saved. 3. Click the Execute SQL Script button (the thunderbolt icon) to run the entire script.
(Note: The icon next to it will only execute the SQL statement under the cursor.) 4. After the script executes, expand the WMCRM schema object to display the tables created
by the script, as shown in Figure A-28. 5. Click the Close [X] button on the WMCRM-Create-Tables script window tab to close
the window.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 40 04/07/19 8:16 PM
Online Extension A Working with MySQL A-41
FIGURE A-28
The WMCRM-Create-Tables Script Results
The Create a new SQL tab button
The expanded wmcrm schema in the Navigator now show the WMCRM table objects
The Action Output in the Output window shows that the WMCRM tables were successfully created—error messages would appear here
How Do I Use SQL Statements to Insert Data? The use of the SQL INSERT command is discussed in Chapter 3. This discussion includes comments on some of the specific syntax requirements of MySQL, especially when using the AUTO_INREMENT feature. Figure A-29 shows the MySQL 8.0 version of the SQL statements to populate the WMCRM database with data. These statements combine the data shown for the WMCRM database in Sections 1 through 5 of “Working with Microsoft Access.”
Also, let’s remember that (as discussed in Chapter 3) MySQL treats the AUTO_INCRE- MENT value as a missing value, so that your INSERT statement has to list all the column names where data is being inserted except the AUTO_INCREMENT column, or use NULL values as inserted values including a null value for the AUTO_INCREMENT column.
One point of interest is the discontinuity of CustomerID values in CUSTOMER. Note that there is no CustomerID of 2. This is because we used Jessica Christman’s data to illustrate delet- ing a CUSTOMER record and then recreating it. The first instance of Jessica’s data used the CustomerID value of 2. Because surrogate key values cannot be used more than once, when we recreated Jessica’s data her CustomerID became 3. Thus, there is no Customer Id value of 2!
Fortunately, MySQL lets us input an AUTO_INCREMENT value directly. When we do this, the input value becomes the new starting point for the AUTO_INCREMENT val- ues – look at the INSERT statements for the CUSTOMER data in Figure A-29.
Enter the SQL statements to create and save an SQL script named WMCRM-Insert- Data. sql based on Figure A-29, and then execute the script to populate the WMCRM database.
Creating and Saving the SQL Script to Populate the WMCRM Table Structure 1. Click the Create a new SQL tab for executing queries button to open a tabbed window. 2. In the open tabbed SQL Query window, type the SQL comments shown in Figure A-29 as
the beginning of the SQL Script. 3. Click the Save the Script to a File button. The Save SQL Script dialog box is displayed,
and it should already be showing the Documents/MySQL Workbench/Schemas/WMCRM- Database folder. If not, browse to the Documents/MySQL Workbench/Schemas/WMCRM- Database folder.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 41 04/07/19 8:16 PM
A-42 Online Extension A Working with MySQL
/***** CUSTOMER DATA **************************************************************/
/* Note that MySQL allows NULL in the AUTO_INCREMENT field to generate the next value */
/* Note that MySQL allows direct insertion of an AUTO_INCREMENT column value */
INSERT NULL, 'Griffey', 'Ben', '5678 25th NE', 'Seattle', 'WA', '98178',
INTO CUSTOMER VALUES(
'[email protected]', 'Big Bill');
NULL, 'Christman', 'Rob', '4567 47th NW', 'Seattle', 'WA', '98167', '[email protected]', 'Tina');
'[email protected]', 'Billy');
INSERT INTO CUSTOMER VALUES( 3, 'Christman', 'Jessica', '3456 36th SW', 'Seattle', 'WA', '98189',
INSERT INTO CUSTOMER VALUES(
NULL, 'Hayes', 'Judy', '234 Highland Place', 'Edmonds', 'WA', '98210', '[email protected]', 'Tina');
INSERT INTO CUSTOMER VALUES(
FIGURE A-29
The SQL Statements to Populate the WMCRM Tables
/***** VEHICLE DATA *************************************************************/
/* Note that MySQL allows NULL in the AUTO_INCREMENT field to generate the next value */
INSERT
USE
INTO VEHICLE
wmcrm;
VALUES( 'Histandard', 'G19HS123400002' 'Histandard', 'G19HS123400003' 'HiLuxury', 'G19HL234500001' 'HiLuxury', 'G19HL234500002' 'HiLuxury', 'G19HL234500003' 'SUHi', 'G19HU345600001' 'SUHi', 'G19HU345600002' 'SUHi', 'G19HU345600003'
); INSERT INTO VEHICLE VALUES( ); INSERT INTO VEHICLE VALUES( ); INSERT INTO VEHICLE VALUES( ); INSERT INTO VEHICLE VALUES( ); INSERT INTO VEHICLE VALUES( ); INSERT INTO VEHICLE VALUES( ); INSERT INTO VEHICLE VALUES( ); INSERT INTO VEHICLE VALUES
NULL, 'Histandard', 'G19HS123400001'NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL,( );
'HiElectra', 'G19HE456700001’INSERT INTO VEHICLE VALUES NULL,( );
/************************************************************************************
/************************************************************************************
/* */
/* */
/* */
/* */
/* Kroenke, Auer, Vandenberg, and Yoder */
/* Database Concepts (9th Edition) Extension A */
/* Wallingford Motors CRM (WMCRM) Database - Insert Data */
/* These are the MySQL 8.0 SQL code solutions */
*/
*/
/***** SALESPERSON DATA ****************************************************/ INSERT INTO SALESPERSON VALUES(
'Tina', 'Smith', 'Tina', '2012-08-10', '15.50', '.125', '206-287-7010', '[email protected]', 4);
'15.50', '.125', '206-287-7020', '[email protected]', 5);
'12.50', '.120', '206-287-7030', '[email protected]', 7);
INSERT INTO SALESPERSON VALUES( 'Big Bill', 'Jones', 'William', '2012-09-25',
'Billy', 'Jones', 'Bill', '2013-05-17', INSERT INTO SALESPERSON VALUES(
Z04_KROE8149_09_SE_EXTA.indd 42 04/07/19 8:16 PM
Online Extension A Working with MySQL A-43
/***** PHONE_NUMBER DATA **********************************************************/ INSERT INTO PHONE_NUMBER VALUES(
INSERT INTO PHONE_NUMBER VALUES(
INSERT INTO PHONE_NUMBER VALUES(
INSERT INTO PHONE_NUMBER VALUES(
INSERT INTO PHONE_NUMBER VALUES(
INSERT INTO PHONE_NUMBER VALUES(
1, '206-456-2345', 'Home');
1, '206-765-5678', 'Cell');
3, '206-467-3456', NULL);
4, '206-478-4567', NULL);
4, '206-458-9998', 'Fax');
5, '425-354-8765', NULL);
NULL, 1, '2018-07-07', 'Phone', 'General interest in a Gaia.'); INSERT INTO CONTACT VALUES(
NULL, 1, '2018-07-07', 'Email', 'Sent general information.'); INSERT INTO CONTACT VALUES(
NULL, 1, '2018-07-12', 'Phone', 'Set up an appointment.'); INSERT INTO CONTACT VALUES(
NULL, 1, '2018-07-14', 'Meeting', 'Bought a HiStandard.'); INSERT INTO CONTACT VALUES(
NULL, 3, '2018-07-19', 'Phone', 'Interested in a SUHi, set up an appointment.'); INTOINSERT CONTACT VALUES(
NULL, 1, '2018-07-21', 'Email', 'Sent a standard follow-up message.'); INTOINSERT CONTACT VALUES(
NULL, 4, '2018-07-27', 'Phone', 'Interested in a HiStandard, set up an appointment.'); INTOINSERT CONTACT VALUES(
NULL, 3, '2018-07-27', 'Meeting', 'Bought a SUHi.'); INTOINSERT CONTACT VALUES(
/***** CONTACT DATA ***************************************************************/ /* Note that MySQL allows NULL in the AUTO_INCREMEMT field to generate the next value */
INSERT INTO CONTACT VALUES( NULL, 4, '2018-08-02', 'Meeting', 'Talked up to a HiLuxury. Customer bought one.');
NULL, 4, '2018-08-10', 'Email', 'Sent a standard follow-up message.');
INSERT INTO CONTACT VALUES( NULL, 3, '2018-08-03', 'Email', 'Sent a standard follow-up message.');
INSERT INTO CONTACT VALUES(
NULL, 5, '2018-08-15', 'Phone', 'General interest in a Gaia.'); INSERT INTO CONTACT VALUES(
/*************************************************************************************/
4. Type the file name WMCRM-Insert-Data in the File name text box of the Save SQL Script dialog box, and then click the Save Button on the Save Script to File dialog box. The script is saved.
5. In the WMCRM-Insert-Data script, type the SQL statements (including the remarks) as shown in Figure A-29.
6. Click the Save the script to a file button. The updated SQL script is saved. 7. Click the Execute SQL Script button (the thunderbolt icon) to run the entire script.
(Note: The icon next to it will only execute the SQL statement under the cursor.) The results are shown in Figure A-30.
8. After the script executes, leave the WMCRM-Insert-Data tabbed window open for the time being.
FIGURE A-29 (Continued)
Z04_KROE8149_09_SE_EXTA.indd 43 04/07/19 8:16 PM
A-44 Online Extension A Working with MySQL
What Is Transaction COMMIT in MySQL? In the results of running the WMCRM-Insert-Data SQL script that are shown in Figure A-30, you will see callouts to a Commit button, to a Rollback button, and to a Toggle autocommit mode button. In Chapter 6, we will discuss transaction processing in detail. In short, to en- sure database integrity, a transaction is initiated with (in MySQL) an SQL START TRANS- ACTION statement and then either (1) committed to the database with an SQL COMMIT statement if the transaction is successful, or (2) removed entirely from the database with an SQL ROLLBACK statement if there was an error in the transaction processing. Transaction processing statements are used with SQL commands such as INSERT and UPDATE.
MySQL 8.0 enables an autocommit mode (implicit COMMIT mode) by default, where a successful transaction is committed to the database automatically. This is shown in Figure A-30 by the fact that the Toggle autocommit mode button is shown in color, indicat- ing that autocommit mode is enabled. This is the default behavior when we are using the Workbench to modify our data.
However, and as the name of the Toggle autocommit mode button implies, autocommit mode can be disabled by using this button. When MySQL autocommit mode is disabled, the Commit and Rollback buttons will become functional and appear in color rather than grayed out as they are shown in Figure A-30. Thus, we can make changes to our database and undo them in this mode.
Disabling MySQL autocommit mode means that explicit transaction COMMIT actions must be done either by (1) running an SQL COMMIT statement or (2) clicking the Commit button after the transaction is completed.
Having explained all of this, we can close the WMCRM-Insert-Data script.
Closing the SQL Script to Populate the WMCRM Table Structure
1. Click the Close [X] button on the WMCRM-Insert-Data script window tab to close the window.
FIGURE A-30
Populating the WMCRM Database
The SQL statements to populate the WMCRM tables
The Commit button
The Rollback button
The Toggle autocommit mode button
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 44 04/07/19 8:16 PM
Online Extension A Working with MySQL A-45
How Do I Work with SQL Queries in MySQL? Now that we’ve created and populated the WMCRM database, we can run SQL queries against the data. While scripts are good for large sets of SQL commands that need to be run together, most SQL queries are run as single commands. Nonetheless, individual SQL queries are created in the same tabbed window and run the same way as SQL scripts.
There is, in fact, a way to store queries in a MySQL database – create them as SQL views. An SQL view is a named query that is stored in the database, and SQL views are discussed in online Extension B, “Advanced SQL,” and later in this extension in Section B.
However, SQL queries and SQL views are considered to be two different, al- though related, parts of SQL, where SQL views are a special class of SQL queries. Thus, using an SQL script is an efficient way to store and reuse SQL queries.
BTW
Further, groups of SQL queries can be saved and stored in an SQL script. In fact, if we want to save SQL queries in MySQL, we must do it this way, because, unlike Microsoft Access, MySQL has no feature for storing queries in a database.
We have already created and run an SQL query in Section 2 of “Working with MySQL.” Here we will create an SQL script to collect a set of SQL queries for the WMCRM database.
Creating and Saving an SQL Script to Store SQL Queries
1. Click the Create a new SQL tab for executing queries button to open a tabbed window. 2. In the open tabbed SQL Query window, type the following SQL query including the
comments:
/* *** SQL-Query-ExtA-03-01 *** */
SELECT *
FROM CUSTOMER;
3. Click the Save the Script to a File button. The Save SQL Script dialog box is displayed, and it should already be showing the Documents/MySQL Workbench/Schemas/WMCRM-Database folder. If not, browse to the Documents/MySQL Workbench/Schemas/WMCRM-Database folder.
4. Type the file name WMCRM-SQL-Queries-ExtA-Section-03 in the File name text box of the Save SQL Script dialog box, and then click the Save Button on the Save Script to File dialog box. The script is saved.
5. Click the Execute the statement under the keyboard cursor button. The query is run, and the results appear as shown in Figure A-31.
6. In the open tabbed SQL Query window, type the following SQL query, including the comments, after SQL-Query-ExtA-03-01 (leave one blank line between the queries in the SQL script):
/* *** SQL-Query-ExtA-03-02 *** */
SELECT CustomerID, LastName, FirstName
FROM CUSTOMER
WHERE CustomerID > 2
ORDER BY LastName DESC;
Z04_KROE8149_09_SE_EXTA.indd 45 04/07/19 8:16 PM
A-46 Online Extension A Working with MySQL
7. Click the Save the script to a file button. The updated SQL script is saved. 8. Highlight SQL-Query-ExtA-03-02 as shown in Figure A-32, and then click Execute
the statement under the keyboard cursor button. The query is run, and the results appear as shown in Figure A-32. This illustrates how we can select one specific por- tion of an SQL script to be executed instead of the entire script—very useful indeed (except for MySQL’s current choice of the highlighting color which obscures much of the SQL text)! ■ NOTE: As an alternative to highlighting a single SQL statement, you can simply click
anywhere within the SQL statement. This puts the cursor within the SQL statement, or, as MySQL refers to it, puts the SQL statement “under the keyboard cursor,” and allows MySQL to identify the SQL statement to be executed (and thus the name of the button!). However, this will only work for a single SQL statement, so if you want to execute two or more statements at the same time, you must highlight the entire block of SQL statements.
9. In the open tabbed SQL Query window, type the following SQL query, including the com- ments, after SQL-Query-ExtA-03-02 (leave one blank line between the queries in the SQL script):
/* *** SQL-Query-ExtA-03-03 *** */
SELECT LastName, FirstName,
ContactDate, ContactType, Remarks
FROM CUSTOMER, CONTACT
WHERE CUSTOMER.CustomerID = CONTACT.CustomerID
AND CUSTOMER.CustomerID = 3
ORDER BY ContactDate;
FIGURE A-31
The SQL Query and Results in MySQL Workbench
The SQL query statement
The SQL query results in a tabbed Result Grid window—the column widths can be adjusted if necessary to display the complete data items
The Execute the statement under the keyboard cursor button
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 46 04/07/19 8:16 PM
Online Extension A Working with MySQL A-47
10. Click the Save the script to a file button. The updated SQL script is saved. 11. Highlight SQL-Query-ExtA-03-03, and then click Execute the statement under the
keyboard cursor button. The query is run, and the results are displayed. 12. Click the Save the script to a file button to make sure the updated SQL script is saved. 13. Click the Close [X] button on the WMCRM-SQL-Queries-ExtA-Section-03 window tab
to close the window.
Note that the results of the SQL query appear in a tabbed results window in a spreadsheet-style display. You can modify the column widths in the results display by using standard Windows drag-and-drop techniques to help make more data visible. You can run multiple queries at the same time by using multiple results windows. You can open additional SQL Script tabbed windows so that you can work with multiple SQL queries or SQL scripts at the same time.
FIGURE A-32
The Highlighted SQL Query and Results in MySQL Workbench
The SQL query results in a tabbed Result Grid window
The Execute the statement under the keyboard cursor button
The highlighted SQL query statement is the statement that will be run—unfortunately, the highlight color that MySQL uses by default obscures a lot of the text
The folks who work on the MySQL Workbench have no qualms about making major GUI changes in minor version upgrades of the product (it happened while we were working on this edition of Database Concepts). The same functional- ity will be there, but perhaps implemented slightly differently! Be aware and be prepared!
BTW
Additional Documentation for MySQL To get access to MySQL documentation, use the Help | Help Index command for help on MySQL Workbench itself, or go to http://dev.mysql.com/doc for documentation on MySQL 8.0 (and other versions of MySQL).
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 47 04/07/19 8:16 PM
A-48 Online Extension A Working with MySQL
Before proceeding with the rest of the material in this Extension, we recommend that you work through and understand the SQL topics covered in Chapter 3, “Structured Query Language.” After that, we recommend that you read and work through Extension B, “Advanced SQL,” and Section B of “Working with MySQL” which is the section of “Working with MySQL” that covers the MySQL perspective on the “Advanced SQL” material
BTW
Chapter 4 in the text discusses systems analysis and design and then describes how to cre- ate data models. Therefore, we should logically be dealing with data models in this section of “Working with MySQL.” Unfortunately, MySQL Workbench is not good at creating data models because it cannot create true M:N (many-to-many) relationships. In Chapter 4, we show how to create data models using Microsoft Visio 2019.
On the other hand, MySQL Workbench is an excellent tool for creating the database designs discussed in Chapter 5, and in the next section of “Working with MySQL” we will use MySQL Workbench for just that purpose.
In this section of “Working with MySQL” we will deal with another common problem in database development – data in another format that needs to be imported into a database table.
When developing a database to support an application, it is very common to find that some (if not all) of the data needed in the database exists as data in user worksheets (also called spreadsheets). A typical example of this is a Microsoft Excel 2019 worksheet that a user has been maintaining and must now be converted to data stored in the database. If we are really lucky, the worksheet will already be organized like a database table, with appropriate column labels and unique data in each row. And if we are really, really lucky, there will be one or more columns that can be used as the primary key in the new database table. In that case, we can easily import the data into the database. More likely, we will have to modify the worksheet and organize and clean up the data in it before we can import the data. In essence, we are following a procedure known as extract, transform, and load (ETL) that we encountered in Chapter 7 in our discussion of data warehouses.
SECTION 4 – IMPORTING MICROSOFT EXCEL DATA INTO A MySQL DATABASE
The topic of importing Excel data into a database table is discussed in online Extension B, “Advanced SQL.” The techniques for importing Excel data into a Microsoft Access 2019 database are discussed in the Extension A section of “Working with Microsoft Access.” For a discussion of importing Excel data into SQL Server 2017 or Oracle Database XE, see online Chapter 10A and online Chapter 10B respectively in David M. Kroenke, David J. Auer, Scott L. Vanden- berg, and Robert C. Yoder, Database Processing: Fundamentals, Design, and Implementation, 15th edition. Upper Saddle River, NJ: Pearson, 2019.
BTW
Now that we have successfully created, populated, and queried the WMCRM database, we can close the MySQL Workbench.
Closing MySQL Workbench:
1. Click the Close [X] button in the top-right corner of MySQL Workbench.
Z04_KROE8149_09_SE_EXTA.indd 48 04/07/19 8:16 PM
Online Extension A Working with MySQL A-49
As an example, let’s consider the set of Gaia model specifications for the 2019 Gaia line. Wallingford Motors currently stores these in an Excel 2019 worksheet named DBC-e09- WMCRM-2019-Gaia-Specifications.xlsx, and is shown in Figure A-33. Wallingford Motors wants to move this data into the WMCRM database.
However, this worksheet is problematic because it contains more than just the column names and data we will want to import. Therefore, we create an edited version as shown in Figure A-34. This is the version of the data we will import into MySQL 8.0.
FIGURE A-33
The Wallingford Motors Gaia Specifications Workbook
FIGURE A-34
The Revised Wallingford Motors Gaea Specifications Workbook
Excel 2019, Windows 10, Microsoft Corporation.
Excel 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 49 04/07/19 8:16 PM
A-50 Online Extension A Working with MySQL
In ETL terms, we must extract the data we need from the worksheet, transform each set of data into a correctly structured and formatted dataset for the database, and then load (import) the data from the worksheet into the database.
After the data is imported into the database table, we will have to use SQL ALTER TABLE statements to create primary keys, foreign keys, and any other needed constraints. While we will use the SQL ALTER TABLE statement as needed here, a full discussion of the SQL ALTER TABLE statement can be found in Extension B, “Advanced SQL.”
Preparing the Microsoft Excel Data for Import into a Database Table Figure A-34 shows the WM GAIA SPECS worksheet after it has been cleaned up. All ex- traneous rows and columns have been deleted, and only the appropriate Gaia model data (with column headers) remains. This worksheet now looks like a database table, which is a good indication that the data import should work properly.
Importing the Microsoft Excel Data into a MySQL 8.0 Database Table For MySQL, we will create the SPECIFICATIONS table using the MySQL for Excel Add-In. We installed this utility using the MySQL Installer, and when Microsoft Excel is launched, it will then appear on the DATA tab in the Microsoft Excel 2019 ribbon. The MySQL for Excel Add-In does a good job of letting us create a new table, set a primary key, and specify most column characteristics. After the data is in a new table that we will name SPECIFICATIONS_2019, we will add any needed constraints. However, MySQL does not support some common SQL ALTER TABLE features, so we will have to use MySQL specific syntax. See http://dev.mysql.com/doc/refman/8.06/en/alter-table.html.
Importing Microsoft Excel Data into a MySQL 8.0 Database Table
1. Open the 2019-Gaia-Specifcations worksheet in Microsoft Excel 2019, and click the Data tab in the Ribbon. The MySQL for Excel button is displayed, as shown in Figure A-35. Click the MySQL for Excel button to launch the MySQL for Excel pane, as shown in Figure A-36.
2. Open a MySQL connection by double-clicking Local instance MySQL80 and logging into the MySQL 8.0 server.
3. As shown in Figure A-37, click the wmcrm schema name to select it, and then click the Next button.
4. In Microsoft Excel, select (highlight) the entire SPECIFICATIONS_2019 table range! 5. As shown in Figure A-38, click the Export Excel Data to New Table command. 6. The Export Data - SPECIFICATIONS_2019 [A1:E5] dialog box is displayed, as shown in
Figure A-39, labeled with the name of the selected Microsoft Excel sheet and the selected range (SPECIFICATIONS_2019 [A1:E5]).
7. In the Pick a Primary Key section, click the Use existing column radio button, and select ModelNumber as the column to be used as the primary key.
8. Complete the new SPECIFICATIONS_2019 table specifications as shown below—note that you can adjust data types and NULL/NOT NULL (shown as “Allow Empty”) for each column as shown in Figure A-40. Although we are using text data types which would nor- mally be CHAR data types, we will use the selected MySQL VARCHAR data type for text columns and adjust the number of characters to match the following column characteristics. Click the column heading for each and adjust the attribute characteristics as follows:
ModelNumber VarChar(5)
ModelName VarChar(25)
ModelDescription VarChar(50)
EstElectricToGasRatio Decimal(4,2) – Allow Empty (NULL)
EstMPG Decimal(4,0) – Allow Empty (NULL)
Z04_KROE8149_09_SE_EXTA.indd 50 04/07/19 8:16 PM
Online Extension A Working with MySQL A-51
FIGURE A-35
The MySQL for Excel Button
The MySQL for Excel button
The SPECIFICATIONS_2019 worksheet
Excel 2019, Windows 10, Microsoft Corporation | MySQL for Excel, Oracle Corporation.
FIGURE A-36
The MySQL for Excel Pane
The MySQL for Excel button
The MySQL for Excel pane
Double-click the Local instance MySQL80 local connection button to connect to the MySQL server
Excel 2019, Windows 10, Microsoft Corporation | MySQL for Excel, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 51 04/07/19 8:16 PM
A-52 Online Extension A Working with MySQL
FIGURE A-37
Selecting the wmcrm Database Schema
Click the Next button
We are connected to the Local instance MySQL80 connection
Click the wmcrm Schema to select the WMCRM database
Excel 2019, Windows 10, Microsoft Corporation | MySQL for Excel, Oracle Corporation.
FIGURE A-38
Selecting the Excel Data
Select (highlight) the entire range of the SPECIFICATIONS_2019 table in Microsoft Excel
Click the Export Excel Data to New Table command
Excel 2019, Windows 10, Microsoft Corporation | MySQL for Excel, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 52 04/07/19 8:16 PM
Online Extension A Working with MySQL A-53
FIGURE A-39
The Export Data – SPECIFICATIONS_2019 [A1:E5] Dialog Box
The Export Data – SPECIFICATIONS_ 2019 [A1:E5] dialog box
Click the Use existing column radio button and then select ModelNumber as the primary key column
9. The complete Export Data – SPECIFICATIONS_2019 [A1:E5] dialog box is shown in Figure A-40.
10. Click the Export Data button. The new table is created and populated, as shown in the Success dialog box seen in Figure A-41.
11. In the Success dialog box, click the OK button. 12. In the Microsoft Excel MySQL For Excel pane, click the Close button to close MySQL for
Excel. 13. Save the Microsoft Excel workbook. If a dialog box appears warning about macro features
that cannot be saved, ignore it and click the Yes button. 14. Close the Microsoft Excel workbook.
Excel 2019, Windows 10, Microsoft Corporation | MySQL for Excel, Oracle Corporation.
FIGURE A-40
Editing the SPECIFICATIONS Table Specifications
Edit each set of column characteristics as listed in the steps in the text
The Data Type drop-down list
The Allow Empty checkbox
The Export Data button
The Export Data – SPECIFICATIONS_ 2019 [A1:E5] dialog box
Excel 2019, Windows 10, Microsoft Corporation | MySQL for Excel, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 53 04/07/19 8:16 PM
A-54 Online Extension A Working with MySQL
The Microsoft Excel data is now in the WMCRM database. We now move to MySQL Workbench to work with the new table.
Working with the New Table in MySQL Workbench:
1. Open MySQL Workbench, log in if needed, and refresh the wmcrm schema. Once refreshed, the expanded wmcrm schema will show the new SPECIFICATIONS_2019 table. Expand the Tables object and the specifications_2019 table object Columns.
2. We need to inspect the structure of the new specifications_2019 table. Right-click the specifications_2019 table object, click Table Inspector, and then click the Columns tab. The correct column characteristics for the specifications_2019 table are displayed as shown in Figure A-42. If you forgot to set the EstElectricToGasRatio and EstMPG columns to NULL (NULLABLE = YES), we can run an SQL ALTER TABLE command to fix that, as shown later.
3. We need to check the data in the new specifications_2019 table. Right-click the specifications_2019 table object, and then click the Select Rows – Limit 1000 command. The data in the computer table is displayed, as shown in Figure A-43. All of the data is correct.
FIGURE A-41
The Success Dialog Box
The OK button
The Success dialog box
Excel 2019, Windows 10, Microsoft Corporation | MySQL for Excel, Oracle Corporation.
FIGURE A-42
The SPECIFICATIONS Table Characteristics
Select the Columns tab
The wmcrm.specifications_ 2019 tab
Right-click the specifications_2019 table object to display the shortcut menu, and click Table Inspector
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 54 04/07/19 8:16 PM
Online Extension A Working with MySQL A-55
4. If you need to modify the computer table to match the SPECIFICATIONS_2019 column characteristics in Step 8 in the previous set of steps, here is how to set the NULL/NOT NULL setting of EstElectricToGasRatio.
/* *** SQL-ALTER-TABLE-ExtA-03-01 *** */
ALTER TABLE SPECIFICATIONS_2019
MODIFY COLUMN EstElectricToGasRatio DECIMAL(4,2) NULL;
5. Some DBMS products allow us to ensure that columns contain certain data values. For example, we need to ensure that the ModelName column contains only ‘HiStandard’, ‘HiLuxury’, ‘SUHi’, or ‘HiElectra’. Until MySQL server version 8.0.16.0, which we are using, MySQL allowed the command to run, but did not enforce these values. Now we can use the CHECK constraint and be confident it will work as designed. Here is the syntax to set the CHECK CONSTRAINT for the ModelName column:
/* *** SQL-ALTER-TABLE-Ext-03-02 *** */
ALTER TABLE SPECIFICATIONS_2019
ADD CONSTRAINT MODEL_NAME_CHECK CHECK
(ModelName IN ('HiStandard', 'HiLuxury', 'SUHi', 'HiElectra'));
6. The SPECIFICATIONS_2019 table has now been added to the WMCRM database. Close the specifications_2019 tab, the wmcrm.specifications_2019 tab, and the Query 1 tabbed SQL Editor.
7. Click the Close [X] button on MySQL Workbench to close the MySQL Workbench.
To fully integrate the SPECIFICATIONS_2019 table into the WMCRM database, we still need to make at least one foreign key link between SPECIFICATIONS_2019 and at least one other table. We will do this in the Exercises at the end of this extension.
FIGURE A-43
The Final SPECIFICATIONS Table Data
Right-click specifications_2019 to display the shortcut menu, and click Select Rows – Limit 1000
The specifications_2019 tab
The result of the Select Rows – Limit 1000 command—the data is correct and no adjustments are needed
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 55 04/07/19 8:16 PM
A-56 Online Extension A Working with MySQL
SECTION 5 – CREATING A DATABASE DESIGN IN MySQL WORKBENCH As we discuss in Chapters 4 and 5, a data model is a logical or conceptual view of the database. A database design defines the database characteristics that will be implemented as a database in an actual DBMS product. Data models are generic, while database designs are created for a specific DBMS. One of the main differences between a data model and a database design is how N:M relationships are handled. In a data model, N:M relationships exist as N:M non- identifying relationships between two strong entities. In a database design, N:M relationships are broken into two 1:N identifying relationships between three ID-dependent entities.
How Do I Create Database Designs in the MySQL Workbench? The MySQL Workbench has tools for what MySQL describes as database design and modeling. There are two meanings to the term database design:
1. The database design process, which is the creation of the proper structure of database tables, the proper relationships between tables, appropriate data constraints, and other structural components.
2. The database design E-R diagram, which is a visual set of specifications for a specific data- base in a specific DBMS.
When MySQL uses the term database design and modeling, it is referring to the database design process. What this means is that we can use MySQL Workbench to plan a database before we actually implement it. Our planning, or modeling, can include the creation of various objects, including draft SQL scripts and, of particular importance to us, an E-R diagram which MySQL refers to as an EER (Enhanced Entity-Relationship) diagram. This EER diagram is actually a database design E-R diagram. Note that MySQL Workbench cannot create true N:M relationships between two entities in the EER diagrams, which are required to create the data models discussed in Chapter 4. Instead, MySQL Workbench immediately creates two 1:N identifying relationships between three tables (based on the two original entities in the N:M relationship plus a third intersection table as discussed in Chapter 5). We used Microsoft Visio 2019 as our tool to create data models, and now we can use MySQL Workbench models as our tool to create database designs.
Because we have been working with the WMCRM database, it would seem logical to create a database design for the database to illustrate the process. However, we will illus- trate creating database designs in the MySQL Workbench by creating an E-R diagram for the Wedgewood Pacific (WP) database that is used as the basis for the discussion of SQL in Chapter 3.6 This database is also used for the example data model created in Microsoft Visio 2019 in Chapter 4’s Section of “Working with Microsoft Access.”
This will allow users to see how a data model is converted into a database design. It also allows us to illustrate the use of intersection tables when converting M:N relationships (the WMCRM database does not have any M:N relationships). When we are done, we will have a database diagram that will closely resemble the WP database diagram in Figure 5-16 and Figure 5-17.
The WP data model as created in Microsoft Visio 2019 is shown in Figure A-44 (this figure is identical to Figure WA-5-1). Note that we used the Visio primary key icon to indi- cate the identifier for each entity in the data model. Based on Figure 5-1, to convert this data model into a database design, we need to:
6Of course, it could be argued that we really should have created the database design first and then imple- mented that design. In many database courses, the data modeling and database design topics (which we cov- er in Chapters 4 and 5) are taught before using SQL to create the databases (which we cover in Chapter 3). In this case, the database design will follow the actual implementation of the database in the DBMS. We prefer to introduce SQL earlier. There are two reasons for this. First, users who are never involved in creat- ing databases still often use SQL or QBE for querying databases (usually in data warehouses or data marts as discussed in Chapter 7) to gather information. Second, we like to get our students involved with DBMSs, databases, and SQL as early in the course as possible. Either approach works, and your professor will choose the one that he or she prefers.
Z04_KROE8149_09_SE_EXTA.indd 56 04/07/19 8:16 PM
Online Extension A Working with MySQL A-57
• Create a table for each entity in the data model by • Specifying primary keys (surrogate key as appropriate) • Specify column properties to include
• Data type • Null status • Default value (if any) • Data constraints (if any)
• Create relationships by placing foreign keys as needed • Create intersection tables or association tables as needed for N:M relationships
The column characteristics for the WP database tables are shown in Figure A-45. This data will be the basis for our conversion of the WP data model into a WP database design for MySQL.
The MySQL Workbench Home tab and MySQL Connections window are shown in Figure A-46. Click the Models button to display the Models window, as shown in Figure A-47.
How Do I Create a Database Model and E-R Diagram in the MySQL Workbench?
To create a new data model in the MySQL Workbench, you can:
• Use the File | New Model command, or • Click the New Model button on the initial Home page (It looks like a circle with a plus
sign inside it).
FIGURE A-44
The WP Data Model in Visio 2019
Visio 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 57 04/07/19 8:16 PM
A-58 Online Extension A Working with MySQL
FIGURE A-45
The WP Database Table Column Characteristics
Column Name
DEPARTMENT
EMPLOYEE
PROJECT
ASSIGNMENT
Type Key Required Remarks
Column Name Type Key Required Remarks
Column Name Type Key Required Remarks
Column Name Type Key Required Remarks
DepartmentName
DepartmentPhone
BudgetCode
OfficeNumber
Char (35)
Char (12)
Char (35)
Char (15)
Primary Key
No
No
No
Yes
EmployeeNumber Integar Primary Key Yes
FirstName Char (25) No Yes
LastName Char (25) No Yes
Department Char (35) Foreign Key Yes
Position Char (35) No No
Supervisor Integar Foreign Key No
OfficePhone Char (12) No No
EmailAddress Varchar (100) No Yes
Surrogate Key
REF: DEPARTMENT
ProjectID Integer Primary Key Yes Surrogate Key
REF: DEPARTMENT
REF: EmployeeNumber
ProjectName Character (50) No Yes
ProjectID Integer Primary Key, Foreign Key
Yes REF: PROJECT
EmployeeNumber Integer Primary Key, Foreign Key
Yes REF: EMPLOYEE
HoursWorked Decimal (6,2) NoNo
Department Character (35) Foreign Key Yes
MaxHours Decimal (8,2) No Yes
StartDate Date No No
EndDate Date No No
Yes
Yes
Yes
Z04_KROE8149_09_SE_EXTA.indd 58 04/07/19 8:16 PM
Online Extension A Working with MySQL A-59
FIGURE A-46
The MySQL Workbench MySQL Connections Window
The Home tab
The MySQL Connections button and window
The MySQL Workbench Migration Wizard button
The MySQL Models button—click to switch to the Models window
FIGURE A-47
The MySQL Workbench Models Window
The Models window
Click the New Model button to create a new MySQL database design
Right-click on the installed sakila_full model to display the shortcut menu, and then click the Clear List command
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 59 04/07/19 8:16 PM
A-60 Online Extension A Working with MySQL
Once the model is created, entity-relationship (E-R) diagrams, which the MySQL Workbench refers to as EER for the extended entity-relationship model (which, as dis- cussed in Chapter 4, is the correct term, although today the term E-R model always means the EER model), are created within the model. Note that you can create database designs without connecting to a MySQL server.
Creating a New MySQL E-R (EER) Diagram:
1. If you haven’t already done so, create a subfolder c:/Documents/MySQL Workbench folder called EER-Diagrams.
2. Open MySQL Workbench. The MySQL Workbench is displayed as shown in Figure A-46. 3. Click the Model button. The MySQL Workbench Models window is displayed, as shown
on Figure A-47. 4. Right-click the sakila_full model (this is a sample database design provided with MySQL
Workbench) shown in Figure A-47 to display the shortcut menu, then click the Clear List command to remove any existing models from the window. When the MySQL Workbench Clear Model Entry List warning dialog box is displayed, click the Delete button.
5. Click the New Model button. A new SQL Model tabbed window is displayed, as shown in Figure A-48.
6. In the MySQL Model tabbed window, click the File | Save Model As . . . command to display the Save Model dialog box. Browse to the Documents\MySQL Workbench\EER- Diagrams folder, and click the New folder button. Name the new folder WP-Database.
7. In the WP-Database folder, save the new model as WP-Database-Design.mwb (note that *.mwb is the default file extension used by the MySQL Workbench for data models).
8. Double-click the Add Diagram icon in the Model Overview section of the MySQL Model page, as shown in Figure A-48. A new, blank EER Diagram page is displayed, as shown in Figure A-49. Again, note that MySQL uses the acronym EER where we are using E-R.
9. Close the Modeling Additions pane, and then click the Save Model to Current File button (see Figure A-49) to save the WP-Database-Design model with the E-R diagram.
FIGURE A-48
The MySQL Model Tab and Window
The MySQL Model tab and window
The Save model to current file button
The Model Overview section
Double-click the Add Diagram button to open a tabbed blank EER diagram page
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 60 04/07/19 8:16 PM
Online Extension A Working with MySQL A-61
Now that we have a blank E-R diagram work area available, we can build the E-R diagram itself. We start by adding a table to the E-R diagram. We will add the DEPARTMENT table. By looking at Figure A-45 we can see the columns that are used in the DEPARTMENT table.
Creating a Table in the MySQL E-R (EER) Diagram:
1. In the E-R diagram toolbar, click the Place a New Table button as shown in Figure A-50. 2. Move the cursor over the blank E-R diagram area (this is not a drag-and-drop—just move
the cursor). As you do so, notice that table property options are displayed across the top of the design area, as shown in Figure A-50. We do not need to change any of these.
FIGURE A-49
The EER Diagram Tab and Window
The EER Diagram tab and window
The MySQL Model tab is now named WP-Database-Design.wmb
The Save model to current file button
The blank E-R diagram
The Place a New Table button
FIGURE A-50
Placing the Table Object
Note the table property options that appear when the table object is selected
The Place a New Table button
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 61 04/07/19 8:16 PM
A-62 Online Extension A Working with MySQL
3. When the cursor is at the place where you want the table icon, click the left mouse button. A new table object named table1 is created on the E-R Diagram, as shown in Figure A-51.
4. Double-click the table1 object to open the MySQL table editor for table1 as shown in Figure A-52. Note that the table editor has tabs along the bottom of the editor window and that the Columns tab is selected so that the Columns pane is displayed. ■ NOTE: Alternatively, right-click the table object to display a shortcut menu, and then
click Edit {TableName} . . . . 5. In the Columns pane, click the Table Name text box to select it if needed, and then type in
the table name DEPARTMENT. Note that the new table name immediately appears on the table editor tab and the table object in the E-R diagram area, as shown in Figure A-53.
FIGURE A-52
The MySQL Table Editor
The MySQL table editor—the tab displays the table name
Type the table name in the Table Name text box
The Columns tab
FIGURE A-51
The table1 Table Object
The table1 table object—double click to open the MySQL table editor
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 62 04/07/19 8:16 PM
Online Extension A Working with MySQL A-63
FIGURE A-53
The Renamed Table
The renamed table object
The renamed table tab
The table name Department was typed into this text box, followed by pressing the Enter key
Now that we have the DEPARTMENT table created and named, we will create the columns in the table.
As shown in Figure A-54, MySQL uses checkboxes with the following abbreviations and a Default text box for defining column characteristics in the MySQL Table Editor:
• PK Primary Key – Check if this column is the primary key or part of a composite primary key.
• NN NOT NULL – Check if this column must have an inserted value. • UQ UNIQUE – Check if this column must contain a unique value. • B Binary – Check if this column uses only two values, such as 0 and 1 or Yes and No. • UN Unsigned Data Type – Check if this column uses numbers without negative val-
ues and you specifically want to not permit negative numbers where they might not otherwise be allowed. Zero fill (ZF) numbers are automatically checked UN.
• ZF Zero Fill – Check if this column should be automatically filled with zeros. • AI AUTO_INCREMENT – Check if this column is a primary key that should have
sequential surrogate key values.
The default text is used, of course, for specifying the DEFAULT value for a column if one is required. Figure A-57 shows the complete DEPARTMENT table. The key symbol indicates the primary key, and the filled-in, light blue diamonds indicate NOT NULL columns (NULL columns have an empty diamond).
Creating the DEPARTMENT Table Columns in the MySQL E-R (EER) Diagram:
1. In the MySQL Table Editor, double-click the row area in the Column Name column in the Columns area. MySQL Workbench generates a primary key name idDEPART- MENT. Click the PK and NN columns to set the primary key.
2. The correct primary key column name for DEPARTMENT (as shown in Figure A-45) is DepartmentName. Edit the Column Name to read DepartmentName.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 63 04/07/19 8:16 PM
A-64 Online Extension A Working with MySQL
FIGURE A-55
The Datatype Drop-Down List
The Datatype drop-down list—select CHAR( ) from this list (it is not visible in the figure—scroll down the list to find it)
PK indicates a primary key column, and NN indicates a NOT NULL column
The key symbol indicates this column is a primary key column
FIGURE A-54
The MySQL Table Editor Generated Primary Key idDEPARTMENT
The MySQL table editor generated primary key name idDEPARTMENT—it should be DepartmentName
The Datatype is INT—it should be Char(35)
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 64 04/07/19 8:16 PM
Online Extension A Working with MySQL A-65
3. The Datatype for DepartmentName is currently INT, but it should be Char(35). Click the Datatype drop-down list arrow in the Datatype field to display the Datatype drop-down list as shown in Figure A-55. Select the Char() datatype from the list (it is at the bottom of the list and not visible in Figure A-55) and edit the datatype to read Char(35). ■ NOTE: Alternatively, you can just type the correct data type into the Datatype field.
4. There are no other settings that need to be changed for the DepartmentName column. It is already marked as PK (Primary Key) and NN (NOT NULL).
5. Double-click the Column Name field in the blank row immediately below DepartmentName. The completed DepartmentName column row and a new blank col- umn row are displayed as shown in Figure A-56. Note that the correct column settings are now displayed in the DEPARTMENT table object in the E-R diagram. Continue adding new columns and set the datatypes and attributes to complete the DEPARTMENT table as shown in Figure A-57.
6. Click the Save Model to Current File button to save the changes, and then close the MySQL Table Editor by clicking the Close [X] button in the DEPARTMENT – Table tab at the top of the table editor pane.
Figure A-57 shows the complete DEPARTMENT table. The key symbol indicates the primary key, and the filled-in, light blue diamonds indicate NOT NULL columns (NULL columns have an empty diamond).
Now, on your own, build the EMPLOYEE and PROJECT tables, but wait to build the ASSIGNMENT table until we discuss how to create relationships. The process is simi- lar to the process we used to build the DEPARTMENT table, and the results are shown in Figure A-58. Note that the table objects have been resized and rearranged. After you have completed the three tables, close the MySQL Table Editor so your screen looks like Figure A-58. The blue diamonds in each table design indicate that NN is set, the white dia- monds indicate NN is not set. You may find it easier to enter the column name, press tab, then enter the datatype directly for each column, then update the column characteristics boxes separately. To delete a column, right-click it and use Delete Selected.
FIGURE A-56
The Completed DepartmentName Primary Key Column
The completed DepartmentName primary key column row
The new column row—MySQL generates the column name of DEPARTMENTcol and the datatype of VARCHAR(45)
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 65 04/07/19 8:16 PM
A-66 Online Extension A Working with MySQL
FIGURE A-57
The Completed DEPARTMENT Table
All columns are now in the DEPARTMENT table—the blue diamond indicates a NOT NULL column (you may need to manually adjust the size of the DEPARTMENT table object to see all the details)
FIGURE A-58
The Completed DEPARTMENT, EMPLOYEE, and PROJECT Tables
The PROJECT table
The DEPARTMENT table
The EMPLOYEE table
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 66 04/07/19 8:16 PM
Online Extension A Working with MySQL A-67
FIGURE A-59
Relationships in the MySQL Workbench EER Diagram Toolbar
Now that we have created the tables, we need to connect them with relationships. As shown in Figure A-59, the MySQL Workbench has buttons (labeled in IE Crow’s Foot no- tation) to create a variety of relationships:
• 1:1 Non-identifying Relationship – Used between two strong entities. • 1:N Non-identifying Relationship – Used between two strong entities. • 1:1 Identifying Relationship – Used between a strong entity and an ID-dependent weak
entity – see discussion later. • 1:N Identifying Relationship – Used between a strong entity and an ID-dependent
weak entity. • N:M Identifying Relationship – Used between two strong entities – see discussion later. • Place a Relationship Using Existing Columns – See discussion later.
The usage of 1:1 non-identifying, 1:N non-identifying, and 1:N identifying relation- ships are standard and correct. However, by definition an identifying relationship has to be used in a 1:N relationship (see Chapter 4), so the 1:1 identifying relationship does not make sense. Similarly, pure N:M relationships only exist in data models, and they are always nonidentifying relationships between two strong entities, so this symbol uses a dashed line instead of a solid one. However, the MySQL Workbench lets us edit relationships after we’ve created them so we can change any parameter we want to change.
MySQL Workbench uses the term non-identifying relationship, whereas in Da- tabase Concepts we use the term nonidentifying relationship. We have seen the term non identifying relationship used in other contexts. All three terms mean exactly the same thing, and which is used is a matter of style. Since MySQL Workbench uses non-identifying, we will also use that term in this extension for consistency with the MySQL Workbench screenshots, while remaining well aware that we have used nonidentifying in Database Concepts itself.
BTW
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
1:1 Non-identifying Relationship
1:N Non-identifying Relationship
1:1 Identifying Relationship
1:N Identifying Relationship
N:M Identifying Relationship—this should be non-identifying as discussed in the text of this extension
Place a Relationship Using Existing Columns—specify the columns to be used in the relationship
Z04_KROE8149_09_SE_EXTA.indd 67 04/07/19 8:16 PM
A-68 Online Extension A Working with MySQL
Among the buttons that MySQL Workbench provides for creating relationships in the database design, the Place a Relationship Using Existing Columns choice is very useful. Normally, when we create a relationship, the MySQL Workbench automatically adds a for- eign key, even if the column that should be the foreign key is already there! And this rela- tionship usually turns out to be a non-identifying relation, despite the solid line shown on the button. Since both EMPLOYEE and PROJECT contain the column that will be the foreign key – in both cases it is the Department column – we will use this button to create the relationships between the tables currently in the E-R diagram. We will start with the relationship between DEPARTMENT and EMPLOYEE.
Creating a 1:N Nonidentifying Relationship Between Two Tables:
1. Click the Place a Relationship Using Existing Columns button as shown in Figure A-59. 2. MySQL Workbench displays a Foreign Key Columns dialog box instructing us to Pick one
or more columns for the foreign key, as shown in Figure A-60. 3. Click the Department column in EMPLOYEE to select it. 4. Click the Pick Referenced Columns button in the Foreign Key Columns dialog box, as
shown in Figure A-61. 5. The Foreign Key Columns dialog box becomes the Referenced Columns dialog box, in-
structing us to Pick matching columns for the referenced table, as shown in Figure A-62. 6. Click the DepartmentName column in DEPARTMENT. A 1:N nonidentifying relation-
ship is created between DEPARTMENT (parent – the 1 side of the relationship) and EMPLOYEE (child – the N side of the relationship), as shown in Figure A-63. Note the red diamond indicating a foreign key in EMPLOYEE.
7. To see the details of the relationship, right-click the relationship line, then click Edit Relationship in the shortcut menu, and then click the Foreign Key tab at the bottom of the Relationship pane. The relationship parameters are displayed as shown in Figure A-64.
The MySQL Relationship Editor shown in Figure A-64 shows this relationship (it has been named fk_EMPLOYEE_DEPARTMENT by the MySQL Workbench, but we can
FIGURE A-60
The Foreign Key Columns Dialog Box – Pick Columns for Foreign Key
Click the Department column to select it
The Foreign Key Columns dialog box
The Place a Relationship Using Existing Columns button
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 68 04/07/19 8:16 PM
Online Extension A Working with MySQL A-69
FIGURE A-61
EMPLOYEE.Department Selected as the Foreign Key Column
The Department column is selected
The Department column is selected
Click the Pick Referenced Columns button
FIGURE A-62
The Referenced Columns Dialog Box – Pick Referenced Primary Key
Click the DepartmentName column to select it
The Referenced Columns dialog box
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 69 04/07/19 8:16 PM
A-70 Online Extension A Working with MySQL
FIGURE A-64
The Relationship Foreign Key Properties
The Relationship tabbed window
The relationship is non-identifying
DEPARTMENT is mandatory
EMPLOYEE is mandatory
The Foreign Key tab
The relationship is One-to-Many (1:N)
FIGURE A-63
The Completed 1:N Nonidentifying Relationship
The 1:N non-identifying relationship
The red diamond indicates a foreign key column
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 70 04/07/19 8:16 PM
Online Extension A Working with MySQL A-71
change the name if desired), and can control the name in the SQL code when we actu- ally construct the tables and relationships in our DBMS (in the SQL CREATE TABLE EMPLOYEE statement in Figure 3-7 we used a FOREIGN KEY CONSTRAINT phrase to name this corresponding foreign key constraint as EMP_DEPART_FK).
The options on the Foreign Key page shown in Figure A-64 allow us to control all aspects of the relationship. As shown there, the relationship is one-to-many (1:N), non- identifying, with both DEPARTMENT and EMPLOYEE having mandatory participation in the relationship (i.e., minimum cardinality of 1). The non-identifying 1:N parameters are correct, but what about the minimum cardinalities?
Does a DEPARTMENT have to have at least one employee? This is actually a business rule question, but we will assume that the answer for WP is yes, meaning that WP does not allow departments without employees to exist.
Does an EMPLOYEE have to be assigned to a department? Again, this is a business rule question, but the fact that EMPLOYEE.Department is NOT NULL with a DEFAULT value of Human Resources is a good indication that the answer for WP is yes, meaning that WP does not allow employees unassigned to departments to exist. Therefore, the manda- tory parameters are correct, and our relationship is correct as drawn.
Closing the MySQL Relationship Editor:
1. Close the MySQL Relationship Editor by clicking the Close [X] button on the tab.
On your own, create the relationship between DEPARTMENT and PROJECT the same way. We will assume that every PROJECT must have a sponsoring DEPARTMENT but that a DEPARTMENT is not required to have any PROJECTs in the works. Connect the FK Department in the PROJECT table to the PK DepartmentName in the DEPARTMENT ta- ble. Finally, we need to add the recursive relationship between EMPLOYEE.Supervisor and EMPLOYEE.EmployeeNumber. Connect the FK Supervisor in the EMPLOYEE table to the PK EmployeeNumber in the EMPLOYEE table. Note that the participation is optional, so edit the recursive relationship, select the Foreign Key tab, and deselect the Mandatory boxes for both sides. The completed E-R diagram with the additional relationships is shown in Figure A-65.
At this point we still need to build the ASSIGNMENT table and its relationships with EMPLOYEE and PROJECT. However, since ASSIGNMENT is an association table in an associations relationship with EMPLOYEE and PROJECT (association relationships are discussed in Chapter 5) and since association tables are just intersection tables with ad- ditional attributes (again, as discussed in Chapter 5), we will use this as an opportunity to illustrate how MySQL Workbench handles N:M relationships.
As discussed in Chapters 4 and 5, an N:M relationship only exists in a data model (as a non-identifying relationship between two strong entities). In a database design, the N:M re- lationship becomes two 1:N ID-dependent identifying relationships linking the two original tables through a new, third table called an intersection table. However, MySQL Workbench only builds database designs and will automatically create the intersection table with the two 1:N relationships whenever we specify an N:M relationship.
Creating an N:M Nonidentifying Relationship Between Two Tables:
1. Click the N:M Identifying Relationship button, then click the PROJECT table, and then click the EMPLOYEE table.
2. As shown in Figure A-66, the MySQL Workbench creates an intersection table named PROJECT_has_EMPLOYEE and places it in the E-R diagram together with two 1:N ID-dependent identifying relationships between (1) PROJECT_has_EMPLOYEE and EMPLOYEE and (2) PROJECT_has_EMPLOYEE and PROJECT.
We have just demonstrated that the MySQL Workbench can only create database designs, not data models, by showing how MySQL Workbench automatically converts a M:N
Z04_KROE8149_09_SE_EXTA.indd 71 04/07/19 8:16 PM
A-72 Online Extension A Working with MySQL
FIGURE A-66
The PROJECT_has_EMPLOYEE Intersection Table
The PROJECT_has_EMPLOYEE intersection table with two One-to- Many (1:N) ID-dependent identifying relationships
FIGURE A-65
The Completed DEPARTMENT, EMPLOYEE and PROJECT Relationships
This is the recursive non-identifying relationship between Supervisor and EmployeeNumber
N:M Identifying Relationship button—This should be non- identifying as discussed in the text of this extension
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 72 04/07/19 8:16 PM
Online Extension A Working with MySQL A-73
In the figures we note these occurrences with a warning box and a callout that uses the symbol PK/FK to designate any such columns.
What about the relationship cardinalities? We will assume that:
• Not every employee has to be assigned to a project, and one employee can be assigned to many projects.
• A project can be created without any employees initially assigned to it, and one project can have many employees assigned to it.
This means that the maximum cardinalities must be set to optional-many Crow’s Foot notation symbols by editing the relationships.
The final E-R diagram is shown in Figure A-67 and, except for the exact arrangement of the tables and relationships, is the same as the WP database design shown in Figures 5-16 and 5-17.
relationship into two 1:N relationships to an intersection table. Now we simply have to do some editing in the MySQL Table Editor by double-clicking the PROJECT_has_EMPLOYEE table:
• Change the table name to ASSIGNMENT. • Change the primary key attribute names to EmployeeNumber and ProjectID. • Add the HoursWorked attribute as DECIMAL(6,2).
WARNING: Some versions of MySQL Workbench 8.0 have a bug that causes the key symbol for a column that is both a primary key and a foreign key to not be displayed. The symbol is the same key symbol used for the primary key, but it is colored red to a foreign key. The ASSIGNMENT table should appear as:
FIGURE A-67
The Completed E-R Diagram
The E-R diagram using Crow’s Foot notation
PK/FK ProjectID PK/FK EmployeeNumber
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 73 04/07/19 8:16 PM
A-74 Online Extension A Working with MySQL
FIGURE A-68
The MySQL 8.0 Security Model
User Logins Global Permissions
Database Permissions
Table Permissions
Column Permissions
Routine Permissions
Server
Database (Schema)
And that completes our introduction to creating database designs in MySQL Workbench. The next step in the systems analysis and design process would be to implement the database design in an actual MySQL database. In our case, we have already implemented the WP database in Chapter 3, where we used it as the basis for our discussion of SQL.
Closing MySQL Workbench:
1. Save the WP-Database-Design in the MySQL Workbench, and close the EER Diagram window by clicking the Close [X] button in the EER Diagram tab.
2. Close the MySQL Model window by clicking the Close [X] button in the MySQL Model tab. 3. Close the MySQL Workbench by clicking the Close [X] button on the MySQL
Workbench window.
SECTION 6 – DATABASE ADMINISTRATION IN MySQL When you, as a database administrator, create a database in MySQL 8.0, you have full permissions within the DBMS to do whatever you need to with that database. However, in order for other people (or applications) to use that database, you must create appropriate user accounts with logins and appropriate permissions. To protect the databases you have created and the data stored in them, you must backup those databases on a regular basis.
We discussed security in general terms in Chapter 6, and here we will discuss how those general ideas pertain to MySQL. The MySQL system consists of users who are granted permissions on various levels: global, database, table, column, or routine. This can be done by assigning a user to a global (server and all databases) Administrative Role or by granting schema (database) specific schema privileges. Unlike other DBMS products, MySQL does not have groups or roles. The MySQL 8.0 security model is shown in Figure A-68.
Security consists of authentication and authorization. First, the user logs onto the DBMS with his or her password (authentication), and then the logged-in user can do only what he or she has been granted permission to do (authorization). We can create users and grant permissions with SQL statements and/or with the MySQL Workbench. We do this in the same Local instance MYSQL80 tabbed connection window we have been using— we simply need to set the Navigator to display the MANAGEMENT, INSTANCE, and PERFORMANCE tabbed window links by clicking on the Administration tab near the bottom of the Navigator pane to display the MANAGEMENT, INSTANCE, and PER- FORMANCE option menus as shown in Figure A-69. Note the same options are available by using the Server tab in the MySQL Workbench menu bar.
Z04_KROE8149_09_SE_EXTA.indd 74 04/07/19 8:16 PM
Online Extension A Working with MySQL A-75
FIGURE A-69
The MANAGEMENT, INSTANCE and PERFORMANCE Options
The Local instance MySQL80 tab
The Server tab
The Sever commands
The Administration tab
The Navigator MANAGEMENT, INSTANCE, and PERFORMANCE options
How Do I Create User Accounts in MySQL 8.0? A full discussion of database administration and database security is provided in Chapter 6. Here, we will discuss the actual steps taken in a MySQL 8.0 database to create a user account and give it the appropriate permissions to read data from and write data to the WMCRM database.
Creating a WMCRM User Login:
1. Open MySQL Workbench, and connect to the Local instance MySQL80 connection. 2. Close the Output window. 3. Click the Administration tab in the Navigator window to display the MANAGEMENT,
INSTANCE, and PERFORMANCE option menus as shown in Figure A-69. 4. In the Navigator window, click the MANAGEMENT Users and Privileges option (alter-
natively, use the Server | Users and Privileges command). The Administration - Users and Privileges screen is shown in Figure A-70.
5. Select the Add Account button, and enter the new userid WMCRM-User and the password WMCRM-User+password, as shown in Figure A-71(a).
6. Click the Apply button. The WMCRM-User user account is created, as shown in Figure A-71(b).
How Do I Grant Users Permissions in MySQL 8.0? Now we need to grant specific permission to our new user. We could use the SQL GRANT statement to do this, and a full discussion of how to do this is in the MySQL document “GRANT Syntax” at http://dev.mysql.com/doc/refman/8.0/en/grant.html. However, we find it easier to do this in MySQL Workbench.
There are two types of privileges we can grant to a user:
• By using a MySQL administrative role, which assigns predefined sets of privileges to the DBMS itself and thus to every database on that server.
• By using MySQL schema privileges, which assign specific rights to a specific database.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 75 04/07/19 8:16 PM
A-76 Online Extension A Working with MySQL
FIGURE A-70
The MySQL 8.0 Workbench Users and Privileges Screen
The Administration - Users and Privileges tab
The Users and Privileges screen
No user account is selected
The Add Account button
FIGURE A-71
The MySQL 8.0 Workbench Add User Screen
(a) Creating the WMCRM-User User Account
Enter the login name WMCRM-User
The Apply button
Enter and confirm the password WMCRM-User+password
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 76 04/07/19 8:16 PM
Online Extension A Working with MySQL A-77
We can, of course, assign a user a combination of server roles and schema privileges. First, we’ll consider server administrative roles.
Granting MySQL Administrative Roles:
1. During installation of MySQL, we created a user account with the DB Admin role. In our case that user is named Auer, but in your setup you will have used a different user name. In User Accounts, click the user name of the user created during installation that has the DB Admin role (we are using the user named Auer) to select it.
2. Click the Administrative Roles tab to display the server administrative roles available to be assigned to WMCRM-User. The Users and Privileges screen is shown in Figure A-72. Note that by granting the DBA (DB Admin) role, we have in fact given complete control (as indicated by all the other roles and all the global privileges being checked) of the data- base server and all databases on the server to Auer.
3. At this point, we will decide that we do not want to grant WMCRM-User a server adminis- trative role, but would rather grant permissions only to the WMCRM database. Therefore, having considered these roles, we will not assign any of them to WMCRM-User.
The Administrative Roles and Schema Privileges screens use a lot of screen space. You may need to expand the window (maximize) or increase your screen resolution in order to use the complete functionality of the screens.
BTW
FIGURE A-71 (Continued)
The MySQL 8.0 Workbench Add User screen
(b) The New User in User Accounts
The Auer user account was created during the installation of MySQL 8.0 and has DB Admin privileges
The Administrative Roles tab
The WMCRM-User user account has been successfully created, and now needs to be assigned privileges
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 77 04/07/19 8:16 PM
A-78 Online Extension A Working with MySQL
Granting MySQL Schema (Database) Privileges:
1. In User Accounts, click the WMCRM-User user object to select it. 2. Click the Schema Privileges tab to display the schema privileges for WMCRM-User. As
shown in Figure A-73, WMCRM-User currently has no database-specific privileges. 3. Click the Add Entry button to display the New Schema Privilege Definition dialog box, as
shown in Figure A-74. 4. In the New Schema Privilege Definition dialog box, click the Selected schema radio
button, and then select wmcrm from the schema list. 5. In the New Schema Privilege Definition dialog box, click the OK button. The selected
schema is assigned to WMCRM-User, but without any permissions, as shown in Figure A-75. 6. Click the Select “ALL” button. All privileges except the GRANT OPTION are selected,
as shown in Figure A-76. The GRANT OPTION would allow the user to give any assigned permission to another user, and this action should be reserved for database administrators.
7. Click the Apply button. The privileges are now granted, as shown in Figure A-77. 8. Close the Administration - Users and Privileges tab by clicking the Close [X] button on
the tab.
Now we have created the login and assigned permissions for WMCRM-User for the WMCRM database.
How Do I Create Database Backups in MySQL 8.0? As explained in Chapter 6, database files should be backed up periodically. When this is done, it is possible to recover a failed database by restoring it from a prior saved database and applying changes from a log file. MySQL includes command-line utilities for making detailed backups and a backup facility in the MySQL Workbench. Here we will use the MySQL Workbench backup tools. For more information on the MySQL administration
FIGURE A-72
Assigning Administrative Roles in MySQL Workbench
The Auer user account is selected—this user has DBA (DB Admin) privileges
Granting the DBA (DB Admin) role grants every other role as well
Granting the DBA (DB Admin) role grants all global privileges
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 78 04/07/19 8:16 PM
Online Extension A Working with MySQL A-79
FIGURE A-73
Assigning Schema Privileges in MySQL Workbench
The WMCRM-User user account is selected—this user has no privileges
The Schema Privileges tab
Schema Privileges that can be assigned to a user
The Add Entry button
FIGURE A-74
Selecting the Schema
The New Schema Privilege Definition dialog box
The WMCRM-User user is being assigned the schema
Click the Selected schema radio button
Select the wmcrm schema in the drop-down schemas list
Click the OK button
utilities, see the MySQL 8.0 Manual’s Chapter 7, “Backup and Recovery,” at https://dev. mysql.com/doc/refman/8.0/en/backup-and-recovery.html.
We will now make a complete backup of the WMCRM database. Note that MySQL Workbench uses the term data export dump as a synonym for backup. Clicking the Data Ex- port link (under the MANAGEMENT category) displays the Data Export page, as shown in Figure A-78(a). To create a backup, select the wmcrm schema. We have also selected the Dump Stored Procedures and Functions and Dump Triggers options (this will save the entire database and associated code—do this regardless of whether or not your database has user-defined functions, stored procedures, or triggers so you’re in the habit of doing it!).
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 79 04/07/19 8:16 PM
A-80 Online Extension A Working with MySQL
FIGURE A-75
Available Schema Privileges
The WMCRM-User can now be assigned privileges for the wmcrm schema
Schema Privileges are now available to be assigned to WMCRM-User
The Select “ALL” button
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation. FIGURE A-76
Selected Schema Privileges
The WMCRM-User can now has selected privileges for the wmcrm schema
All MySQL schema privileges except the GRANT OPTION are now assigned to WMCRM-User for the wmcrm schema
The Apply button
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 80 04/07/19 8:16 PM
Online Extension A Working with MySQL A-81
FIGURE A-77
Assigned Schema Privileges
The WMCRM-User can now has assigned privileges for the wmcrm schema
Backing Up the WMCRM Database:
1. Click the Data Export Option in the MANAGEMENT section of the Navigator Administration tab to display the Data Export page as shown in Figure A-78(a).
2. Because this is the first backup we have created, we need to create a folder under the MySQL Workbench folder in Documents to hold the backups. Click the Browse For Folder button to display the Browse For Folder dialog box, then browse to the MySQL Workbench folder as shown in Figure A-78(b).
3. Click the Make New Folder button. Create a new folder named Backups under the MySQL Workbench folder, and then press the Enter key.
4. With the new Backups folder now highlighted, click the OK button. The MySQL backup file will now be saved in the Backups folder, as shown in the file path in the Export to Dump Project Folder text box.
5. Click the Start Export button. If you get a message saying “Folder already exists,” then click the Overwrite button to create the backup in the specified folder. The backup (data export) opera- tion is run, and the steps are displayed in the Export Progress tab, as shown in Figure A-78(c).
6. The backup (data export) is complete. Click the Close [X] button on the Administration- Data Export window tab to close the window.
For database recovery in MySQL, we will again use the MySQL Workbench. Having made a backup of the WMCRM database, we can recover it if needed. If the database and log files have been properly backed up, restoring the database is straightforward. However, because we do not need to actually restore the WMCRM database, we will simply note that restoring a database is a straightforward operation and uses the Data Import/Restore link as shown in Figure A-79 and the Start Import button (note that we have used the Browse To Folder button to browse to the MySQL Workbench Backups folder).
You can use backup and restore to transfer a database to another computer or user (such as your professor!). Just do a full backup of the database you want to share to a
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 81 04/07/19 8:16 PM
A-82 Online Extension A Working with MySQL
FIGURE A-78
Database Backup in MySQL Workbench
(a) The Data Export Window
The Data Export window
The wmcrm schema has been selected
The Export to Dump Project Folder radio button is selected—the file path shown is the default
The Browse For Folder button
The Start Export button
The Data Export and Data Import/Restore options are used to manage MySQL backups and restores
(b) The Browse For Folder Dialog Box
The Browse For Folder dialog box
The MySQL Workbench folder
Click the Make New Folder button, then name the new folder Backups, and then press the Enter key.
With the new Backups folder highlighted, click the OK button
backup file as we have done, say, the file MyBackup. Then create a new database on another computer and name it whatever you want. Finally, restore the database using the MyBackup files as just described.
At this point we are done using MySQL Workbench, and we can close MySQL Workbench.
Closing MySQL Workbench:
1. Close the MySQL Workbench by clicking the Close [X] button on the MySQL Workbench window.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 82 04/07/19 8:16 PM
Online Extension A Working with MySQL A-83
FIGURE A-79
Database Restore in MySQL Workbench
The Data Import window
The Browse For Folder button
The Start Import button
The Import from Dump Project Folder radio button is selected—the file path shown is the one to the Backups folder
FIGURE A-78 (Continued)
Database Backup in MySQL Workbench
(c) The Data Export Export Progress Tab
The Close [X] buttom
The backup (data export) is completed, with the steps shown in the Log window below
The Export Progress tab
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 83 04/07/19 8:16 PM
A-84 Online Extension A Working with MySQL
SECTION 7 – BUSINESS INTELLIGENCE SYSTEMS USING MySQL In Chapter 7, we discussed online analytical processing (OLAP) as an example of business intelligence systems (BI) reporting systems. A general discussion of the process was provided in the chapter. Here we will provide a detailed discussion of the process using data stored in a MySQL database.
The example discussed in Chapter 7 is based on a data warehouse for Heather Sweeney Designs. The Heather Sweeney Designs transactional (online transaction processing [OLTP]) database (HSD) is discussed in the Case Problems in Chapter 3, and the Heather Sweeney Designs data warehouse database is discussed in Chapter 7, where a database design (in MySQL Workbench) and data are provided.
In Chapter 7, we created a data warehouse for Heather Sweeney Designs, and the basic star schema table structure for this data warehouse is shown in Figure 7-9, which is reproduced as Figure A-80 here for reference. This structure is modified in Figure 7-15 to include a table for RFM reporting (as discussed in Chapter 7), but we do not need to include that extension here.
Note that in the HSD-DW database the CUSTOMER table uses the same surrogate primary key (CustomerID) as does the operational database, which has an integer value. Also note that we have concatenated LastName and FirstName into a single CustomerName column and are using only the customer’s area code prefix, not the entire phone number. Finally, note that we do not use individual EmailAddress values in the HSD-DW database, only values of EmailDomain, which is not unique. This is a simple way of aggregating data.
Creating the HSD-DW Database We begin by creating the HSD-DW database in MySQL, and then creating and populating the tables. We will then create an SQL view to hold the data needed for the OLAP report. The SQL statements to create the HSD-DW tables are shown in Figure 7-10, which is re- peated here as Figure A-81. Note that because we are using existing data to populate the CUSTOMER table, we do not use AUTO_INCREMENT to create the CustomerID values – they already exist!
We will now use these SQL CREATE TABLE statements to build the HSD-DW data- base in MySQL.
FIGURE A-80
The HSD-DW Star Schema
PK/FK TimeID, PK/FK CustomerID, PK/FK ProductNumber
PRODUCT dimension table
PRODUCT_SALES fact table
CUSTOMER dimension table
TIMELINE dimension table
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 84 04/07/19 8:16 PM
Online Extension A Working with MySQL A-85
FIGURE A-81
The HSD-DW SQL Create Table Statements
CREATE TABLE TIMELINE(
USE hsd_dw;
TimeID Int NOT NULL, Date Date NOT NULL, MonthID Int NOT NULL, MonthText Char(15) NOT NULL, QuarterID Int NOT NULL, QuarterText Char(10) NOT NULL, Year Char(10) NOT NULL, CONSTRAINT TIMELINE_PK PRIMARY KEY(TimeID) );
CREATE TABLE CUSTOMER( CustomerID Int NOT NULL, CustomerName Char(75) NOT NULL, EmailDomain VarChar(100) NOT NULL, PhoneAreaCode Char(6) NOT NULL, City Char(35) NULL, State Char(2) NULL, ZIP Char(10) NULL, CONSTRAINT CUSTOMER_PK
Char(35) Char(25) VarChar(75)
PRIMARY KEY(CustomerID)
NOT NULL, NOT NULL, NOT NULL,
);
CREATE TABLE PRODUCT( ProductNumber ProductType ProductName
PRODUCT_PK PRIMARY KEY(ProductNumber)CONSTRAINT );
CREATE TABLE PRODUCT_SALES( TimeID Int NOT NULL, CustomerID Int NOT NULL, ProductNumber Char(35) NOT NULL, Quantity Int NOT NULL, UnitPrice DECIMAL(9,2) NOT NULL, Total DECIMAL(9,2) NULL, CONSTRAINT PRIMARY KEYPRODUCT_SALES_PK
(TimeID, CustomerID, ProductNumber), CONSTRAINT PS_TIMELINE_FK FOREIGN KEY(TimeID)
REFERENCES TIMELINE(TimeID)
ON UPDATE NO ACTION ON DELETE NO ACTION,
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT PS_CUSTOMER_FK FOREIGN KEY(CustomerID) REFERENCES CUSTOMER(CustomerID)
CONSTRAINT PS_PRODUCT_FK FOREIGN KEY(ProductNumber) REFERENCES PRODUCT(ProductNumber)
ON UPDATE NO ACTION ON DELETE NO ACTION
);
Creating the HSD-DW Database:
1. Open MySQL Workbench, and connect to MySQL 8.0 using the Local instance MySQL80 connection.
2. Close the Output window
Z04_KROE8149_09_SE_EXTA.indd 85 04/07/19 8:16 PM
A-86 Online Extension A Working with MySQL
3. Click the Create a new schema button. 4. The new_schema tabbed window is displayed. Type the new schema (database) name
HSD_DW in the Name textbox, and then click the Apply button. A warning dialog box will declare that the schema name will be in lowercase letters as “hsd_dw” (MySQL only allows lowercase letters in schema names, and disallows the dash [hyphen] character). Click the OK button.
5. The Apply SQL Script to Database – Review the SQL Script to be Applied on the Database dialog box is displayed so that you can review the SQL command before it is executed. The command is correct, so click the Apply button.
6. The Apply SQL Script to Database – Applying SQL Script to the Database dialog box is displayed with the results of executing the SQL command. Click the Finish button.
7. The hsd_dw - Schema database (the renamed new_schema - Schema) dialog box is dis- played again. Click the X (Close) button on the hsd_dw - Schema tab.
8. The hsd_wd schema object now appears in the Navigator. If you want to make sure the Object Browser objects are displayed correctly, then click Refresh SCHEMAS button, which will refresh the display in the Object Browser.
Now we need to set the hsd_dw database as the default schema.
Setting the hsd_dw Schema as the Default Schema:
1. Click the hsd_dw schema object in the SCHEMAS section of the Navigator to select it. 2. Right-click the hsd_dw schema object to display the shortcut menu. 3. In the shortcut menu, click the Set as Default Schema command. 4. The hsd_dw schema is set as the active schema (default schema), and the hsd_dw schema
object is displayed in bold text in the Navigator to indicate that it is the active schema, as shown in Figure A-82.
Now we will create and run an SQL script to create the HSD-DW table structure.
FIGURE A-82
The HSD-DW Schema
The hsd_dw schema has been set as the active (default) schema
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 86 04/07/19 8:16 PM
Online Extension A Working with MySQL A-87
FIGURE A-83
Writing the HSD-DW-Create-Tables SQL Script
The SQL comment statements for the HSD-DW-Create-Tables SQL script
Creating and Saving the SQL Script to Create the HSD-DW Table Structure
1. Click the Create a new SQL tab for executing queries button to open a tabbed SQL Editor window, if there is not one already open.
2. In the open tabbed SQL Editor window, type the SQL comments shown in Figure A-83 as the beginning of the SQL Script.
3. Click the Save the Script to a File button. The Save SQL Script dialog box is displayed. 4. The Save SQL Script dialog box opens to the Documents/MySQL Workbench/
Schemas/Art-Course-Database folder. Browse up one level to the Documents/ MySQL Workbench/Schemas folder.
5. Click the New folder button in the Save SQL Script dialog box. A new folder object is displayed.
6. Type the folder name HSD-DW-Database as the new folder name, and then click the Open folder button in the Save SQL Script dialog box. The new folder is created.
7. Type the file name HSD-DW-Create-Tables in the File name text box of the Save SQL Script dialog box, and then click the Save Button on the Save Script to File dialog box. The script is saved.
Now that we have the SQL script created, we can complete entering the SQL CREATE TABLE statements for the HSD-DW database as shown in Figure A-84, and then run them to build the table and relationship structure of the HSD-DW database.
Completing and Saving the SQL Script to Create the HSD-DW Table Structure
1. In the HSD-DW-Create-Tables script, type the SQL statements (including the remarks) as shown in Figures A-81 and A-84.
2. Click the Save the script to a file button. The updated SQL script is saved. 3. Open the Output window. 4. Click the Execute SQL Script button to run the entire script.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 87 04/07/19 8:16 PM
A-88 Online Extension A Working with MySQL
5. After the script executes, expand the hsd_dw schema object to display the tables created by the script, as shown in Figure A-84.
6. Click the Close [X] button on the HSD-DW-Create-Tables script window tab to close the window.
Figure A-85 shows the MySQL 8.0 version of the SQL statements to populate the HSD- DW database with data, based on the data shown in Figure 7-11. See Chapter 7 for a discus- sion of the data itself.
Enter the SQL statements to create and save an SQL script named HSD-DW-Insert- Data.sql based on Figure A-85, and then execute the script to populate the WP database.
Creating and Saving the SQL Script to Populate the HSD-DW Table Structure
1. If needed, click the Create a new SQL tab for executing queries button to open a tabbed SQL Editor window.
2. In the open tabbed SQL Editor window, type SQL comments similar to those shown in Figure A-85 as the beginning of the SQL Script.
3. Click the Save the Script to a File button. The Save SQL Script dialog box is displayed, and it should already be showing the Documents/MySQL Workbench/Schemas/HSD-DW- Database folder. If needed, browse to the Documents/MySQL Workbench/Schemas/HSD- DW-Database folder.
4. Type the file name HSD-DW-Insert-Data in the File name text box of the Save SQL Script dialog box, and then click the Save Button on the Save Script to File dialog box. The script is saved.
5. In the HSD-DW-Insert-Data script, type the SQL statements (including the remarks) as shown in Figure A-85.
6. Click the Save the script to a file button. The updated SQL script is saved.
FIGURE A-84
The HSD-DW-Create-Tables Script Results
The SQL CREATE TABLE statements
The hsd_dw Tables have been created
The CREATE TABLE actions in the Action Output window
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 88 04/07/19 8:16 PM
Online Extension A Working with MySQL A-89
The next part of our work will use SQL views. SQL views are discussed in online Extension B, “Advanced SQL.” Implementing SQL views in MySQL 8.0 is discussed in detail in Section B – Advanced SQL of this extension. If you haven’t worked through this material on SQL views, you may want to do so before continuing.
BTW
7. Click the Execute SQL Script button to run the entire script. The results are shown in Figure A-86.
8. Click the Close [X] button on the HSD-DW-Insert-Data script window tab to close the window.
FIGURE A-85
The SQL Statements to Populate the HSD-DW Tables
/***** TIMELINE DATA **************************************************************/ INSERT
USE
INTO TIMELINE
hsd_dw;
VALUES( INSERT INTO TIMELINE VALUES( INSERT INTO TIMELINE VALUES( INSERT INTO TIMELINE VALUES( INSERT INTO TIMELINE VALUES( INSERT INTO TIMELINE VALUES( INSERT INTO TIMELINE VALUES( INSERT INTO TIMELINE VALUES( INSERT INTO TIMELINE VALUES
43388, '2018-10-15', 10, 'October', 3, 'Qtr3', 2018); 43398, '2018-10-25', 10, 'October', 3, 'Qtr3', 2018); 43454, '2018-12-20', 12, 'December', 3, 'Qtr3', 2018); 43549, '2019-03-25', 3, 'March', 1, 'Qtr1', 2019); 43551, '2019-03-27', 3, 'March', 1, 'Qtr1', 2019); 43555, '2019-03-31', 3, 'March', 1, 'Qtr1', 2019); 43558, '2019-04-03', 4, 'April', 2, 'Qtr2', 2019); 43563, '2019-04-08', 4, 'April', 2, 'Qtr2', 2019); 43578, '2019-04-23', 4, 'April', 2, 'Qtr2', 2019);(
INSERT INTO TIMELINE VALUES 43592, '2019-05-07', 5, 'May', 2, 'Qtr2', 2019);( INSERT INTO TIMELINE VALUES 43606, '2019-05-21', 5, 'May', 2, 'Qtr2', 2019);( INSERT INTO TIMELINE VALUES 43621, '2019-06-05', 6, 'June', 2, 'Qtr2', 2019);(
/***** CUSTOMER DATA **************************************************************/ INSERT INTO CUSTOMER VALUES(
1,'Jacobs,Nancy', 'somewhere.com', '817', 'Fort Worth', 'TX', '76110');
3,'Able,Ralph', 'somewhere.com', '210', 'San Antonio', 'TX', '78214');
INSERT INTO CUSTOMER VALUES( 2,'Jacobs,Chantel', 'somewhere.com', '817', 'Fort Worth', 'TX', '76112');
INSERT INTO CUSTOMER VALUES(
4,'Baker, Susan', 'elsewhere.com', '210', 'San Antonio', 'TX', '78216'); INSERT INTO CUSTOMER VALUES(
INSERT INTO CUSTOMER VALUES(
7,'George,Sally', 'somewhere.com', '972', 'Dallas', 'TX', '75223');
5,'Eagleton,Sam', elsewhere.com', '210', 'San Antonio', 'TX', '78218'); INSERT INTO CUSTOMER VALUES(
6,'Foxtrot, Kathy', 'somewhere.com', '972', 'Dallas', 'TX', '75220'); INSERT INTO CUSTOMER VALUES(
8,'Hullett, Shawn', 'elsewhere.com', '972, 'Dallas', 'TX', '75224'); INSERT INTO CUSTOMER VALUES(
INSERT INTO CUSTOMER VALUES( 9,'Pearson, Bobbi', 'elsewhere.com', '512', 'Austin', 'TX', '78710');
INSERT INTO CUSTOMER VALUES( 10,'Ranger, Terry', 'somewhere.com', '512', 'Austin', 'TX', '78712');
11,'Tyler, Jenny', 'somewhere.com', '972', 'Dallas', 'TX', '75225'); INSERT INTO CUSTOMER VALUES(
INSERT INTO CUSTOMER VALUES( 12,'Wayne, Joan', 'elsewhere.com', '817', 'Fort Worth', 'TX', '76115');
(Continued)
Z04_KROE8149_09_SE_EXTA.indd 89 04/07/19 8:16 PM
A-90 Online Extension A Working with MySQL
/***** PRODUCT DATA **************************************************************/ INSERT INTO PRODUCT VALUES(
'BK001', 'Book', 'Kitchen Remodeling Basics For Everyone');
'BK003', 'Book', 'Kitchen Remodeling Dallas Style For Everyone');
INSERT INTO PRODUCT VALUES( 'BK002', 'Book', 'Advanced Kitchen Remodeling For Everyone');
INSERT INTO PRODUCT VALUES(
'VB001', 'Video Companion', 'Kitchen Remodeling Basics Video Companion'); INSERT INTO PRODUCT VALUES(
INSERT INTO PRODUCT VALUES(
'VK001', 'Video', 'Kitchen Remodeling Basics');
'VB002', 'Video Companion', 'Advanced Kitchen Remodeling Video Companion'); INSERT INTO PRODUCT VALUES(
'VB003', 'Video Companion', 'Kitchen Remodeling Dallas Style Video Companion'); INSERT INTO PRODUCT VALUES(
'VK002', 'Video', 'Advanced Kitchen Remodeling'); INSERT INTO PRODUCT VALUES(
INSERT INTO PRODUCT VALUES( 'VK003', 'Video', 'Kitchen Remodeling Dallas Style');
INSERT INTO PRODUCT VALUES( 'VK004', 'Video', 'Heather Sweeney Seminar Live in Dallas on 25-Oct-17');
/***** PRODUCT_SALES DATA ****************************************************/ INSERT INTO PRODUCT_SALES VALUES(43388, 3, 'VB001', 1, 7.99, 7.99); INSERT INTO PRODUCT_SALES VALUES(43388, 3, 'VK001', 1, 14.95, 14.95); INSERT INTO PRODUCT_SALES VALUES(43398, 4, 'BK001', 1, 24.95, 24.95); INSERT INTO PRODUCT_SALES VALUES(43398, 4, 'VB001', 1, 7.99, 7.99); INSERT INTO PRODUCT_SALES VALUES(43398, 4, 'VK001', 1, 14.95, 14.95); INSERT INTO PRODUCT_SALES VALUES(43454, 7, 'VK004', 1, 24.95, 24.95); INSERT INTO PRODUCT_SALES VALUES(43549, 4, 'BK002', 1, 24.95, 24.95); INSERT INTO PRODUCT_SALES VALUES(43549, 4, 'VK002', 1, 14.95, 14.95); INSERT INTO PRODUCT_SALES VALUES(43549, 4, 'VK004', 1, 24.95, 24.95); INSERT INTO PRODUCT_SALES VALUES(43551, 6, 'BK002', 1, 24.95, 24.95); INSERT INTO PRODUCT_SALES VALUES(43551, 6, 'VB003', 1, 9.99, 9.99); INSERT INTO PRODUCT_SALES VALUES(43551, 6, 'VK002', 1, 14.95, 14.95); INSERT INTO PRODUCT_SALES VALUES(43551, 6, 'VK003', 1, 19.95, 19.95); INSERT INTO PRODUCT_SALES VALUES(43551, 6, 'VK004', 1, 24.95, 24.95); INSERT INTO PRODUCT_SALES VALUES(43551, 7, 'BK001', 1, 24.95, 24.95); INSERT INTO PRODUCT_SALES VALUES(43551, 7, 'BK002', 1, 24.95, 24.95); INSERT INTO PRODUCT_SALES VALUES(43551, 7, 'VK003', 1, 19.95, 19.95); INSERT INTO PRODUCT_SALES VALUES(43551, 7, 'VK004', 1, 24.95, 24.95);
/*********************************************************************************/
INSERT INTO PRODUCT_SALES VALUES(43592, 9, 'VB002', 1, 7.99, 7.99); INSERT INTO PRODUCT_SALES VALUES(43592, 9, 'VK002', 1, 14.95, 14.95); INSERT INTO PRODUCT_SALES VALUES(43606, 8, 'VB003', 1, 9.99, 9.99); INSERT INTO PRODUCT_SALES VALUES(43606, 8, 'VK003', 1, 19.95, 19.95); INSERT INTO PRODUCT_SALES VALUES(43606, 8, 'VK004', 1, 24.95, 24.95); INSERT INTO PRODUCT_SALES VALUES(43621, 3, 'BK002', 1, 24.95, 24.95); INSERT INTO PRODUCT_SALES VALUES(43621, 3, 'VB001', 1, 7.99, 7.99); INSERT INTO PRODUCT_SALES VALUES(43621, 3, 'VB002', 2, 7.99, 15.98); INSERT INTO PRODUCT_SALES VALUES(43621, 3, 'VK001', 1, 14.95, 14.95); INSERT INTO PRODUCT_SALES VALUES(43621, 3, 'VK002', 2, 14.95, 29.90); INSERT INTO PRODUCT_SALES VALUES(43621, 11, 'VB002', 2, 7.99, 15.98); INSERT INTO PRODUCT_SALES VALUES(43621, 11, 'VK002', 2, 14.95, 29.90); INSERT INTO PRODUCT_SALES VALUES(43621, 12, 'BK002', 1, 24.95, 24.95); INSERT INTO PRODUCT_SALES VALUES(43621, 12, 'VB003', 1, 9.99, 9.99); INSERT INTO PRODUCT_SALES VALUES(43621, 12, 'VK002', 1, 14.95, 14.95); INSERT INTO PRODUCT_SALES VALUES(43621, 12, 'VK003', 1, 19.95, 19.95); INSERT INTO PRODUCT_SALES VALUES(43621, 12, 'VK004', 1, 24.95, 24.95);
FIGURE A-85 (Continued)
The SQL Statements to Populate the HSD-DW Tables
Z04_KROE8149_09_SE_EXTA.indd 90 04/07/19 8:16 PM
Online Extension A Working with MySQL A-91
Our next step is to create the SQL view named HSDDWProductSalesView shown in Chapter 7 on page 472. This SQL view pulls together all the data we will need for our OLAP table (called a Pivot Table) in Microsoft Excel 2019. We will slightly rewrite this SQL statement to use SQL JOIN ON syntax:
/* *** SQL-CREATE-VIEW-ExtA-07-01 *** */
CREATE VIEW HSDDWProductSalesView AS
SELECT C.CustomerID, C.CustomerName, C.City,
P.ProductNumber, P.ProductName,
T.Year, T.QuarterText,
SUM(PS.Quantity) AS TotalQuantity
FROM CUSTOMER C JOIN PRODUCT_SALES PS
ON C.CustomerID = PS.CustomerID
JOIN PRODUCT P
ON P.ProductNumber = PS.ProductNumber
JOIN TIMELINE T
ON T.TimeID = PS.TimeID
GROUP BY C.CustomerID, C.CustomerName, C.City,
P.ProductNumber, P.ProductName,
T.QuarterText, T.Year;
Creating and Saving an SQL View
1. If needed, click the Create a new SQL tab for executing queries button to open a tabbed SQL Editor window.
2. In the open tabbed SQL Editor window, type the SQL comments for SQL-CREATE- VIEW-ExtA-07-01 shown earlier.
FIGURE A-86
Populating the HSD-DW Database
The SQL INSERT statements for the HSD-DW data
The INSERT actions in the Action Output window
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 91 04/07/19 8:16 PM
A-92 Online Extension A Working with MySQL
3. Click the Save the Script to a File button. Save this SQL Query as HSD-DW-Create-View- HSDDWProductSalesView.sql.
4. Click the Execute the statement under the keyboard cursor button to run the SQL com- mand. The results are shown in Figure A-87.
5. Leave the SQL Query window open.
We will now use the SQL view by using it in an SQL query statement. To do so, we will run the SQL query SQL-Query-ExtA-07-01:
/* *** SQL-Query-ExtA-07-01 *** */
SELECT *
FROM HSDDWProductSalesView
ORDER BY CustomerName, Year, QuarterText;
After we obtain the results, we will export the data to an Excel *.csv (comma separated values) file for import into Microsoft Excel 2019.
Using an SQL View and Exporting the Results
1. Close the Output window. 2. In the open tabbed SQL Query HSD-DW-Create-View-HSDDWProductSalesView, type
the SQL statement for SQL Query-ExtA-07-01. 3. Click the Save the Script to a File button to save the query as part of the HSD-DW-
Create-View-HSDDWProductSalesView.sql script. 4. Highlight (or click within) the SQL Query-ExtA-07-01 statement, and then click the
Execute the statement under the keyboard cursor button to run the SQL command. The results are shown in Figure A-88. Note that these results match those shown in Chapter 7 in Figure 7-16, which, of course, is exactly what we want.
FIGURE A-87
Creating the SQL View HSDDWProductSalesView
The SQL CREATE VIEW statement
The CREATE VIEW action in the Action Output window
The SQL view named hsddwproductsalesview has been created
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 92 04/07/19 8:16 PM
Online Extension A Working with MySQL A-93
FIGURE A-88
Results of Using the SQL View HSDDWProductSalesView
The SQL SELECT statement to use the HSDDWProductSalesView
The results of running the SQL statement in the Result Grid window
The Export result set to an external file button is used to export the results in the Result Grid window
5. Click the Export button in the Result Grid. The Export Resultset dialog box appears, with the location in the same place we have been saving our SQL scripts. Type the file name HSD- DW-HSDDWProductSalesView-Results into the File name text box as shown in Figure A-89.
6. Click the Save button to complete the data export. 7. Click the Save the Script to a File button to resave the HSD-DW-Create-View-
HSDDWProductSalesView.sql script to make sure any and all changes have been saved. 8. Click the Close [X] button on the HSD-DW-Create-View-HSDDWProductSalesView
SQL Editor window tab to close the window. 9. Click the Close [X] button on the MySQL Workbench window to close MySQL Workbench.
Creating a Microsoft Excel OLAP Report Because the OLAP report will be in a Microsoft Excel 2019 workbook, we need to create a new workbook to hold the OLAP report, which will be created as a Microsoft Excel PivotTable. Because the HSD-DW-HSDDWProductSalesView-Results.csv file will open in Excel, it is very easy to do – we just open the csv file and save it as a Microsoft Excel 2019 *.xlsx workbook file named HSD-DW-BI.xlsx in the same folder.
Creating the Microsoft Excel 2019 HSD-DW-BI Workbook
1. Start Windows Explorer. 2. Browse to your HSD-DW-Database folder. 3. Double-click HSD-DW-HSDDWProductSalesView-Results.csv to open the file in Excel,
as shown in Figure A-90. 4. Excel makes it very easy to save the file as an *xlsx workbook file – in the POSSIBLE
DATA LOSS warning bar, click the Save As . . . button. The Save As dialog box appears. 5. In the Save As dialog box, type the file name HSD-DW-BI in the File name text box, and
then select Excel Workbook (*.xlsx) from the Save as type drop down list. The completed Save As dialog box appears as shown in Figure A-91.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 93 04/07/19 8:16 PM
A-94 Online Extension A Working with MySQL
FIGURE A-89
The Export Resultset Dialog Box
The Export Resultset dialog box
The Save button
The file name of the exported resultset
The resultset will be saved as a Comma Separated Values (CSV) file
FIGURE A-90
The HSD-DW-HSDDWProductSalesView-Results.csv File in Excel 2019
The data exported from MySQL on a Microsoft Excel worksheet
The Save As… button
The Microsoft Excel worksheet is labeled with the same name as the *.csv file containing the exported data
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Excel 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 94 04/07/19 8:16 PM
Online Extension A Working with MySQL A-95
6. Click the Save button. The file is saved as HSD-DW-BI.xls, and we have the workbook we need with the data we need to create the Excel 2019 Pivot Table as our OLAP table. The workbook is shown in Figure A-92.
7. Adjust the column widths of the HSD-DW-HSDDWProductSalesView-Results worksheet so that all column names and data are visible. The worksheet and workbook now appear as shown in Figure A-93.
8. Click the Save button in the Quick Access Toolbar to save the changes to the workbook. Leave the workbook open.
Alternatively, Microsoft has made it possible to link directly to MySQL 8.0 from Microsoft Excel 2019 itself to obtain the data needed for the report. This technique would use the MySQL Connector/NET 8.0.16 that we installed during our installation of MySQL 8.0. We would start on the Data tab with the Get Data | From Database | From MySQL Database command.
However, exporting a result set to a *.csv file and then converting that file to an Excel workbook *.xlsx file is often much more efficient (and sometimes more reliable).
BTW
FIGURE A-91
The Completed Save As Dialog Box
The Save As dialog box
The file name for the saved Excel workbook
The file will be saved as an Excel Workbook *.XLSX file
Excel 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 95 04/07/19 8:16 PM
A-96 Online Extension A Working with MySQL
FIGURE A-92
The HSD-DW-BI Workbook
The workbook is now an Excel *.XLSX file named HSD-DW-BI
The exported MySQL data converted to a Microsoft Excel 2019 worksheet
The Microsoft Excel worksheet is still labeled with the same name as the original *.csv file containing the exported data
FIGURE A-93
The Adjusted Column Widths for the HSDDWProductSalesView Data
The column widths have been adjusted to display all the data
Excel 2019, Windows 10, Microsoft Corporation.
Excel 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 96 04/07/19 8:16 PM
Online Extension A Working with MySQL A-97
FIGURE A-94
The PivotTable Button
The PivotTable button
Creating the Basic OLAP Report PivotTable
1. In the HSD-DW-BI workbook, click the Insert command tab to display the PivotTable button in the Tables command group, as shown in Figure A-94.
2. Click the PivotTable button. The Create PivotTable dialog box is displayed, as shown in Figure A-95. Note that all the data on the HSD-DW-HSDDWProductSalesView-Results work- sheet has been input as the data source for the PivotTable as the Select a table or range radio button is selected and the Table/Range box contains the range of that data (the range is also outlined with a box of dashed green lines on the worksheet itself). Further, the New Worksheet radio button is selected as the location of the new PivotTable, and this is exactly what we want.
3. Click the OK button in the Create PivotTable dialog box. The new PivotTable is created in a new worksheet named Sheet1. Rename Sheet1 as HSD-DW PivotTable, as shown in Figure A-96.
Structuring the OLAP Report We can now create the structure of the OLAP report. We do this by using the Microsoft Excel PivotTable Fields pane, shown in Figure A-96 and in detail in Figure A-97. To build the structure of the PivotTable, we drag and drop the field objects from the field object list. We drag the measures we want displayed to the VALUES box. We drag the dimension at- tributes we want as column structure to the COLUMNS box, and we drag the dimension attributes we want as row structure to the ROWS box.
For the Heather Sweeney Designs Product Sales PivotTable, we will use TotalQuantity as the measure, so it needs to go in the Values box. The column structure will contain product attributes, in this case only ProductNumber. Finally, the row structure will have customer at- tributes – City first, followed by CustomerName and then Year. This analysis will show us sales in each city for each customer in that city for each year that customer purchased a product.
Excel 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 97 04/07/19 8:16 PM
A-98 Online Extension A Working with MySQL
FIGURE A-95
The Create PivotTable Dialog Box
The Create PivotTable dialog box
All the data in the worksheet will be available for use in the PivotTable
The PivotTable will be created in a new worksheet in the HSD-DW-BI workbook
The OK button
FIGURE A-96
The New PivotTable
The PivotTable Tools command tab
The Save button
The initial PivotTable area
The PivotTable Fields pane—the structure of the PivotTable is built using these tools
Excel 2019, Windows 10, Microsoft Corporation.
Excel 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 98 04/07/19 8:16 PM
Online Extension A Working with MySQL A-99
Creating the OLAP Report’s PivotTable Structure
1. Click and hold the ProductNumber field object, drag it to the Columns box, and drop it there. As shown in Figure A-98, the ProductNumber labels and a Grand Total label (not shown, but to the right of the last ProductNumber label) are added to the worksheet col- umns, the ProductNumber field object in the field objects list is checked and displayed in bold, and the field object ProductNumber is listed in the Columns box.
2. Click and hold the TotalQuantity field object, drag it to the Values box, and drop it there. As shown in Figure A-99, the Sum of TotalQuantity values are added to the worksheet, the TotalQuantity field object in the field objects list is checked and displayed in bold, and the field object Sum of TotalQuantity is listed in the Values box.
3. Click and hold the City field object, drag it to the Rows box, and drop it there. As shown in Figure A-100, the sales of each product and the sum of sales for each of the City row labels is added to the worksheet, the City field object in the field objects list is checked and displayed in bold, and the field object City is listed in the Rows labels box. In addition, the values in the report are starting to show up.
4. Click and hold the CustomerName field object, drag it to the Rows box, and drop it there, below City. As shown in Figure A-101, the sum of the City row labels is divided into customer name, the CustomerName field object in the field objects list is checked and displayed in bold, and the field object CustomerName is listed in the Rows box. In addi- tion, the values in the report are now distributed according to city (City) and customer (CustomerName) within each product.
5. Click and hold the Year field object, drag it to the Rows box, and drop it there, below CustomerName. The order of fields in the Rows box should be: City, CustomerName, and Year. You can adjust the order by clicking on the down-arrow next to each field name and moving up or down as needed. As shown in Figure A-102, the sum of the City row labels is now divided into CustomerName and Year, the Year field in the field objects list is checked and displayed in bold, and the field Year is listed in the Rows box below CustomerName. In addition, the purchase values in the report are now distributed according to city (City), customer in each city (CustomerName), and year of the purchase (Year). We expanded the Year field for Ralph Able in San Antonio in the figure to show date details. Note that the presentation of the PivotTable Figure A-102 is now identical to the PivotTable shown in Figure 7-18. We have achieved our goal of recreating the PivotTable used in Chapter 7, “Data Warehouses, Business Intelligence Systems, and Big Data.”
6. Click the Save button on the Microsoft Excel Quick Access Toolbar to save your work to this point.
FIGURE A-97
The PivotTable Fields Pane
The PivotTable Fields pane
Field objects in this box appear as the PivotTable column structure
Field objects in this box appear in the PivotTable cell structure
Field objects in this box appear as the PivotTable row structure
The Field object list—drag-and- drop these filed objects to one of the four boxes below as needed
Excel 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 99 04/07/19 8:16 PM
A-100 Online Extension A Working with MySQL
FIGURE A-98
The ProductNumber Column Labels
The ProductNumber values as column labels
The ProductNumber field object is checked and displayed in bold
The ProductNumber field object is displayed in the Columns box
FIGURE A-99
The Sum of TotalQuantity Row Label
The PivotTable cell values are now displayed—at this point only the sum of each column is shown
The TotalQuantity field object is checked and displayed in bold
The TotalQuantity field object is displayed in the Values box, and the sum is being calculated
Excel 2019, Windows 10, Microsoft Corporation.
Excel 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 100 04/07/19 8:16 PM
Online Extension A Working with MySQL A-101
FIGURE A-100
The City Row Labels and Values
The City row labels and City row cell values are now displayed
The City field object is checked and displayed in bold
The City field object is displayed in the Rows box
FIGURE A-101
The CustomerName Row Labels and Values
The CustomerName field object is checked and displayed in bold
The CustomerName row labels and row cell values are now displayed
The CustomerName field object is displayed in the Rows box
Excel 2019, Windows 10, Microsoft Corporation.
Excel 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 101 04/07/19 8:16 PM
A-102 Online Extension A Working with MySQL
Modifying an OLAP Report We have finished building our OLAP report. We can modify it as needed by moving the field objects in the PivotTable Field List pane. We can contract and expand various portions of the OLAP report by clicking on the + (plus sign) and – (minus sign) symbols to the left of the row labels. We can also format the OLAP report to make it look the way we want it to.
Formatting the OLAP Report
1. Select cell A1 in the HSD-DW Pivot Table worksheet. Type in the text Heather Sweeney Designs Product Sales Report. Format this text 14 pts and bold.
2. Select cell A3 (labeled Sum of TotalQuantity) in the HSD-DW Pivot Table worksheet, and then click the PivotTable Tools Design command tab to display the Design command groups, as shown in Figure A-103.
3. Click the Banded Columns check box in the PivotTable Style Options command group.
FIGURE A-102
The Year Row Labels and Values
The Year row labels and row cell values are now displayed
The Year field object is checked and displayed in bold
The Year field object is displayed in the Rows box
FIGURE A-103
The Excel PivotTable Tools Design Command Tab
The Design command tab
The Banded Columns checkbox
The PivotTable Styles gallery drop-down arrow
Excel 2019, Windows 10, Microsoft Corporation.
Excel 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 102 04/07/19 8:16 PM
Online Extension A Working with MySQL A-103
4. Click the PivotTable Styles Gallery drop-down arrow button to display the PivotTable Styles Gallery, as shown in Figure A-104.
5. Select the PivotTable style shown in Figure A-104 to format the OLAP report. 6. If desired, adjust the column widths of the columns in the report for consistency and a good
visual appearance. The final, formatted PivotTable OLAP report is shown in Figure A-105. 7. Click the Save button on the Microsoft Excel Quick Access Toolbar to save the final ver-
sion of the worksheet and workbook. 8. Close the HSD-DW-BI.xlsx workbook. 9. Close Microsoft Excel 2019.
FIGURE A-104
The Excel PivotTable Styles Gallery
The PivotTable Styles gallery
The columns in the PivotTable now have borders
Click this PivotTable style to format the OLAP report
FIGURE A-105
The Final PivotTable OLAP Report
Excel 2019, Windows 10, Microsoft Corporation.
Excel 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 103 04/07/19 8:16 PM
A-104 Online Extension A Working with MySQL
SECTION B – ADVANCED SQL IN MySQL In this section, we will cover three topics we discussed in the section of “Working with Microsoft Access” in online Extension B, “Advanced SQL.” These are:
• How to use the SQL ALTER TABLE statement. • How to create and use a query on a recursive relationship. • How to create and use SQL views.
We will continue to use the WMCRM database we used in Section 3 of “Working with MySQL.” At this point, we have created and populated (which means that we have inserted the data into) the CUSTOMER, PHONE_NUMBER, CONTACT, SALESPERSON, and VEHICLE tables and have set the referential integrity constraints between them. And from Section 4, we have the SPECIFICATIONS_2019 table created from data imported from a Microsoft Excel worksheet (if you haven’t completed Section 4 yet, this table will not be in your version of the WMCRM database—don’t worry, it is not used in this section).
Working with the SQL ALTER TABLE Statement in MySQL We introduced the SQL ALTER TABLE statement in Chapter 3, and extended our discus- sion of it in online Extension B, “Advanced SQL.” To add a column in MySQL’s variant of SQL, we use the syntax ALTER TABLE {TableName} ADD {ColumnName} {DataType} {NULL status}. MySQL permits, but does not require, the use of the MySQL COLUMN keyword in the ADD clause, which would then read ADD COLUMN {ColumnName} {DataType} {NULL status}.
To alter an existing column in a table, MySQL uses several keywords to indicate which part of the column definition is being changed. The MySQL CHANGE [COLUMN] keyword (the COLUMN keyword is optional) is used to rename a column and/or to change the column definition. The MySQL MODIFY [COLUMN] keyword (the COLUMN key- word is optional) is used to only change the column definition (and to change the order of the column in the table). The MySQL RENAME COLUMN keyword (the COLUMN keyword is required) is used to change only the column name. Because we will usually be changing a column definition but not renaming it, we will normally be using the MySQL MODIFY [COLUMN] keyword. For more information, see the MySQL 8.0 Reference Manual section 13.1.9 ALTER TABLE Syntax at https://dev.mysql.com/doc/refman/8.0/en/alter-table.html.
Here, we will use it to add a column to the CUSTOMER table that we will need in or- der to create a query on a recursive relationship. At Wallingford Motors, one way the new customers are found is by the recommendation of existing customers. When a sale is made to a customer, that customer is asked to recommend one (and only one) potential customer. If the customer makes such a recommendation, he or she is rewarded with a 3 percent re- duction in the sales price of the car he or she is buying. Note that this recommendation is not a requirement, and not all customers are comfortable giving Wallingford Motors the name of someone they know.
To record this data, we must add a ReferredBy column to the CUSTOMER table. As shown in Figure A-106, the column will be integer data (because CustomerID is integer) and not required (NULL) because not all customers were recommended by other customers.
FIGURE A-106
WMCRM Database Column Characteristics for the CUSTOMER ReferredBy Column
Z04_KROE8149_09_SE_EXTA.indd 104 04/07/19 8:16 PM
Online Extension A Working with MySQL A-105
Because customers can only refer one other person, the data values of ReferredBy must be unique (each CustomerID value can only appear once in the ReferredBy column). Further, this column will have a recursive relationship within the CUSTOMER table because Re- ferredBy will be a foreign key referencing CustomerID. Figure A-107 shows the data for the ReferredBY column that will need to be added after the column is created.
Let’s take this one step at a time. First, we add the NULL column ReferredBy. To do this, we will use the SQL ALTER TABLE statement:
/* *** SQL-ALTER-TABLE-ExtA-B-01 *** */
ALTER TABLE CUSTOMER
ADD COLUMN ReferredBy INTEGER NULL UNIQUE;
Note that MySQL 8.0 SQL syntax permits the use of the COLUMN keyword in the ADD clause. Other DBMS products, such as Microsoft SQL Server, do not use it.
Why did we use the UNIQUE keywork for the ReferredBy column? We did this be- cause this is how we enforce the requirement that values in this column be unique. Further, this constraint is necessary to create a 1:1 relationship instead of a 1:N relationship. We need this because the recursive relationship we will add later is a 1:1 relationship.
After we have created the ReferredBy column, we use SQL UPDATE statements to add new data to the column:
/* *** SQL-UPDATE-ExtA-B-01 *** */
UPDATE CUSTOMER
SET ReferredBy = 3
WHERE CustomerID = 4;
/* *** SQL-UPDATE-ExtA-B-02 *** */
UPDATE CUSTOMER
SET ReferredBy = 1
WHERE CustomerID = 5;
We will again create an SQL script to hold and store our SQL statements. This is a way of backing up our work, allowing us to recreate it quickly if necessary.
Creating and Populating the ReferredBy Column in the CUSTOMER table
1. Open MySQL Workbench. 2. Click the Local instance MySQL80 button. The Connect to MySQL Server dialog box
appears. 3. Enter the password for the root user in the Password text box, and then click the OK but-
ton. The tabbed Local instance MySQL80 window appears.
FIGURE A-107
WMCRM CUSTOMER Table ReferredBy Column Data
Z04_KROE8149_09_SE_EXTA.indd 105 04/07/19 8:16 PM
A-106 Online Extension A Working with MySQL
4. Set the wmcrm schema as the active schema (default schema). 5. Open the Output window. 6. In the Query1 SQL Editor window, type the comments and SQL USE statement shown in
Figure A-108. 7. Click the Save the Script to a File button. The Save SQL Script dialog box is displayed,
and it should already be showing the Documents/MySQL Workbench/Schemas/WMCRM- Database folder. If not, browse to the Documents/MySQL Workbench/Schemas/WMCRM- Database folder.
8. Type the file name WMCRM-Add-ReferredBy-Column in the File name text box of the Save SQL Script dialog box, and then click the Save Button on the Save Script to File dialog box. The script is saved.
9. In the WMCRM-Add-ReferredBy-Column script, type the SQL statement (including the remarks) to add the ReferredBy column as shown earlier in the SQL-ALTER-TABLE- ExtA-B-01 statement.
10. Click the Save the script to a file button. The updated SQL script is saved. 11. Highlight (or click within) the SQL-ALTER-TABLE-ExtA-B-01 statement, and then click
the Execute the statement under the keyboard cursor button (the thunderbolt icon with the I on it) to run the SQL statement.
12. Refresh the wmcrm database schema and expand the CUSTOMER table. The results, with the new ReferredBy column, are shown in Figure A-109.
13. In the WMCRM-Add-ReferredBy-Column script, type the SQL statements (including the remarks) to add the data to the ReferredBy column as shown earlier in the SQL-UPDATE- ExtA-B-01 and SQL-UPDATE-ExtA-B-02 statements.
14. Click the Save the script to a file button. The updated SQL script is saved. 15. Highlight both the SQL-UPDATE-ExtA-B-01 and SQL-UPDATE-ExtA-B-02 statements.
Click the Execute the selected portion of the script or everything, if there is no selection button (the thunderbolt icon) to run the SQL statements.
FIGURE A-108
Creating the WMCRM-Add-ReferredBy-Column SQL Script
The SQL comment statements for the WMCRM-Add-ReferredBy- Column SQL script
The SQL USE {Schema Name} statement
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 106 04/07/19 8:16 PM
Online Extension A Working with MySQL A-107
16. In the WMCRM-Add-ReferredBy-Column script, type the following SQL query statement (including the remarks) to display the data in the ReferredBy column of the CUSTOMER table:
/* *** SQL-Query-ExtA-B-01 *** */
SELECT CustomerID, LastName, FirstName, ReferredBy
FROM CUSTOMER
ORDER BY CustomerID;
17. Click the Save the script to a file button. The updated SQL script is saved. 18. Highlight (or click within) the SQL-Query-ExtA-B-01 statement, and then click the
Execute the statement under the keyboard cursor button (the thunderbolt icon with the I on it) to run the SQL statement. The results, with the completed and populated ReferredBy column, are shown in Figure A-110.
19. Leave the WMCRM-Add-ReferredBy-Column SQL script window open – we will add the referential integrity constraint necessary to create the recursive relationship to it in the next section.
How Do I Create a Recursive Relationship within the CUSTOMER Table in MySQL 8.0? A recursive relationship, as discussed in Chapter 4 and Chapter 5, is a relationship between two columns of the same table. In this case, the ReferredBy column in CUSTOMER will have a recursive relationship with CustomerID in CUSTOMER. We create this relationship
FIGURE A-109
The ReferredBy column in the CUSTOMER table
The ReferredBy column has been created in the CUSTOMER table
The SQL ALTER TABLE ADD COLUMN statement
The ALTER TABLE action in the Action Output window
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 107 04/07/19 8:16 PM
A-108 Online Extension A Working with MySQL
by adding a FOREIGN KEY constraint to the CUSTOMER table by using the following SQL statement:
/* *** SQL-ALTER-TABLE-ExtA-B-02 *** */
ALTER TABLE CUSTOMER
ADD CONSTRAINT REF_BY_CUSTOMER_FK FOREIGN KEY(ReferredBy)
REFERENCES CUSTOMER(CustomerID);
Creating a 1:1 Recursive Relationship Within the CUSTOMER table
1. In the WMCRM-Add-ReferredBy-Column script, type the following SQL statement (including the remarks) to add a referential integrity constraint to the CUSTOMER table:
/* *** SQL-ALTER-TABLE-ExtA-B-02 *** */
ALTER TABLE CUSTOMER
ADD CONSTRAINT REF_BY_CUSTOMER_FK FOREIGN KEY(ReferredBy)
REFERENCES CUSTOMER(CustomerID);
2. Click the Save the script to a file button. The updated SQL script is saved. 3. Highlight (or click within) the SQL-ALTER-TABLE-ExtA-B-02 statement, and then click
the Execute the statement under the keyboard cursor button (the thunderbolt icon with the I on it) to run the SQL statement. The results of a successful action will appear in the Output window, and this will be the only results that are visible at this time.
FIGURE A-110
Results for the SQL-Query-ExtA-B-01 Statement
The SQL-Query-ExtA-B-01 SQL statement
The results for the SQL-Query- ExtA-B-01 SQL statement
The SELECT action in the Action Output window
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 108 04/07/19 8:16 PM
Online Extension A Working with MySQL A-109
How Do I Run a Query on a Recursive Relationship in MySQL 8.0? A query on a recursive relationship, as discussed in Chapter 4 and Chapter 5, is a query between two columns of the same table that have a recursive relationship. The trick is to make it appear as though we are using two separate tables instead of just the one table. We can do this by using two aliases for the same table. To do this, we will use the following SQL statement:
/* *** SQL-Query-ExtA-B-02 *** */
SELECT C.FirstName AS CustomerFirstName,
C.LastName AS CustomerLastName,
RB.FirstName AS ReferredByFirstName,
RB.LastName AS ReferredByLastName
FROM CUSTOMER C JOIN CUSTOMER RB
ON C.ReferredBy = RB.CustomerID
ORDER BY C.CustomerID;
Note how the CUSTOMER table is used twice: first with the alias of C (for CustomerID), and then with an alias of RB (for ReferredBy). Just one table but seen as two by the SQL statement!
Running an SQL Query on a Recursive Relationship
1. In the WMCRM-Add-ReferredBy-Column script, type the following SQL query state- ments (including the remarks) to display the data in the ReferredBy column of the CUSTOMER table:
/* *** SQL-Query-ExtA-B-02 *** */
SELECT C.FirstName AS CustomerFirstName,
C.LastName AS CustomerLastName,
RB.FirstName AS ReferredByFirstName,
RB.LastName AS ReferredByLastName
FROM CUSTOMER C JOIN CUSTOMER RB
ON C.ReferredBy = RB.CustomerID
ORDER BY C.CustomerID;
2. Click the Save the script to a file button. The updated SQL script is saved. 3. Highlight (or click within) the SQL-Query-ExtA-B-02 statement, and then click the
Execute the statement under the keyboard cursor button (the thunderbolt icon with the I on it) to run the SQL statement. The results are shown in Figure A-111.
4. Click the Save the script to a file button to make sure all changes to the SQL script have been saved. The updated SQL script is saved.
5. This completes our work on the SQL ALTER statement and SQL queries on recursive relationships, so click the Close [X] button on the WMCRM-Add-ReferredBy-Column script window to close it but leave MySQL Workbench open.
How Do I Create SQL Views in MySQL 8.0? Now we will discuss using SQL views in MySQL 8.0. SQL views were briefly introduced in Chapter 3, and discussed in detail in online Extension B, “Advanced SQL.” As an enterprise-class DBMS, MySQL 8.0 lets us store SQL views in a database, and this was il- lustrated in Figure B-21.
Z04_KROE8149_09_SE_EXTA.indd 109 04/07/19 8:16 PM
A-110 Online Extension A Working with MySQL
Here we’ll use a view to solve a very real problem in the WMCRM database: the fact that customer’s phone numbers are stored in a separate table (PHONE_NUMBER) from the rest of the customer data (which is in CUSTOMER). We created the PHONE_NUMBER table to resolve a multivalue, multicolumn problem that existed in the original CUSTOMER table.
Now, having fixed the multivalue, multicolumn problem, we are faced with the prob- lem of recombining the data in the two tables when a user wants to see the customer’s phone data together with other customer data at the same time. Of course, this is easy to do with an SQL query if you know SQL:
/* *** SQL-Query-ExtA-B-03 *** */
SELECT FirstName, LastName, EmailAddress,
PhoneNumber AS CustomerPhoneNumber,
PhoneType
FROM CUSTOMER JOIN PHONE_NUMBER
ON CUSTOMER.CustomerID = PHONE_NUMBER.CustomerID
ORDER BY LastName, FirstName;
We can create an SQL view from this query by using the SQL CREATE VIEW state- ment, which requires us to rewrite SQL-Query-ExtA-B-03 to create an SQL view named CustomerPhoneView:
/* *** SQL-CREATE-VIEW-ExtA-B-01 *** */
CREATE VIEW CustomerPhoneView AS
SELECT FirstName, LastName, EmailAddress,
PhoneNumber AS CustomerPhoneNumber,
PhoneType
FROM CUSTOMER JOIN PHONE_NUMBER
ON CUSTOMER.CustomerID = PHONE_NUMBER.CustomerID
ORDER BY LastName, FirstName;
FIGURE A-111
Results for the SQL-Query-ExtA-B-02 Statement
The SQL-Query-ExtA-B-02 SQL statement
The results for the SQL-Query- ExtA-B-02 SQL statement
The SELECT action in the Action Output window
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 110 04/07/19 8:16 PM
Online Extension A Working with MySQL A-111
Note that MySQL allows us to include the ORDER BY clause in the SQL view! This is not typical of SQL CREATE VIEW statements in other DBMS products – there we have to ex- clude the ORDER BY clause for the SQL view, and add it to any query that uses the SQL view.
As usual, it is best to store our code for creating database structures such as views in a script file, so we will create an SQL Script named WMCRM-Create-Views.sql in the MySQL Workbench to store this view and any other views we want to create for the WMCRM database.
Creating an SQL View in MySQL 8.0
1. In MySQL Workbench, click the File | New Query Tab command to open a new SQL Editor tabbed window.
2. In the new SQL Editor window, type the comments shown in Figure A-112. 3. Click the Save the Script to a File button. The Save SQL Script dialog box is displayed,
and it should already be showing the Documents/MySQL Workbench/Schemas/WMCRM- Database folder. If not, browse to the Documents/MySQL Workbench/Schemas/WMCRM- Database folder.
4. Type the file name WMCRM-Create-Views in the File name text box of the Save SQL Script dialog box, and then click the Save Button on the Save Script to File dialog box. The script is saved.
5. In the WMCRM-Create-Views script, type the following SQL CREATE VIEW statement (including the remarks) to create the CustomerPhoneView SQL view:
/* *** SQL-CREATE-VIEW-ExtA-B-01 *** */
CREATE VIEW CustomerPhoneView AS
SELECT FirstName, LastName, EmailAddress,
PhoneNumber AS CustomerPhoneNumber,
PhoneType
FROM CUSTOMER JOIN PHONE_NUMBER
ON CUSTOMER.CustomerID = PHONE_NUMBER.CustomerID
ORDER BY LastName, FirstName;
FIGURE A-112
Creating the WMCRM-Create-Views SQL Script
The SQL comment statements for the WMCRM-Create-Views SQL script
The SQL USE {Schema Name} statement
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 111 04/07/19 8:16 PM
A-112 Online Extension A Working with MySQL
6. Click the Save the script to a file button. The updated SQL script is saved. 7. Highlight (or click within) the SQL-CREATE-VIEW-ExtA-B-01 statement, and then click
the Execute the statement under the keyboard cursor button (the thunderbolt icon with the I on it) to run the SQL statement.
8. Refresh the wmcrm database schema and expand the Views icon and the customerphoneview SQL view icon. The results, with the new CustomerPhoneView, are shown in Figure A-113.
9. To use the CustomerPhoneView SQL view, we reference it by name in an SQL query statement. Type the following SQL query statement into the WMCRM-Create-Views script (this is the MySQL equivalent of the SQL statement SQL-Query-WA-ExtB-05 for Microsoft Access 2019 in Extension B):
/* *** SQL-Query-ExtA-B-04 *** */
SELECT FirstName, LastName, EmailAddress, CustomerPhoneNumber, PhoneType
FROM CustomerPhoneView;
10. Click the Save the script to a file button. The updated SQL script is saved. 11. Highlight (or click within) the SQL-Query-ExtA-B-04 statement, and then click the
Execute the statement under the keyboard cursor button (the thunderbolt icon with the I on it) to run the SQL statement. The results are shown in Figure A-114.
12. Click the Save the script to a file button to make sure all changes to the SQL script have been saved. The updated SQL script is saved.
13. This completes our work on the SQL Views, so click the Close [X] button on the WMCRM-Create-Views script window to close it.
14. Click the Close [X] button on the MySQL Workbench window to close MySQL Workbench.
We now know how to create and use SQL views in MySQL 8.0. We discuss SQL views in depth in online Extension B, “Advanced SQL.” We recommend that you work through the material on SQL views in Extension B. In fact, now would be a good time to work through all of Extension B.
FIGURE A-113
Creating the CustomerPhoneView SQL View
The SQL View named customerphoneview has been created
The SQL CREATE VIEW statement
The CREATE VIEW action in the Action Output window
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 112 04/07/19 8:16 PM
Online Extension A Working with MySQL A-113
While MySQL 8.0 is an excellent enterprise-class DBMS, it does not provide any applica- tion development tools. Microsoft Access 2019 does provide a set of application develop- ment tools such as forms, reports, stored queries, and menu systems (see Extension C, “Working with Microsoft Access – Microsoft Access 2019 Switchboards”). Thus, it would be useful to have a way to use Microsoft Access 2019 as the application development fron- tend for a MySQL 8.0 database. Also, since most companies store their data on a centralized server, being able to use Microsoft Access to connect to remote data can be very helpful for your workplace productivity.
What Is Open Database Connectivity (ODBC)? This is actually very easy to do using Open Database Connectivity (ODBC). Several stan- dard interfaces have been developed for accessing database servers. Every DBMS product has an Application Programming Interface (API). An API is a collection of objects, meth- ods, and properties for executing DBMS functions from program code. Unfortunately, each DBMS has its own API, and APIs vary from one DBMS product to another. To save pro- grammers from having to learn to use many different interfaces, the computer industry has developed standards for database access.
The Open Database Connectivity (ODBC) standard is one such standard that was de- veloped in the early 1990s to provide a DBMS-independent means for processing relational database data. It is a well-established standard and is seeing new prominence as the pre- ferred tool to connect applications and relational databases to the “NoSQL” nonrelational data structures introduced in Chapter 7 and discussed in detail in Extension C, “Advanced Business Intelligence and Big Data.” Because it is a widely known and implemented stan- dard, we will use it here.
FIGURE A-114
Using the CustomerPhoneView SQL View
The results for the SQL-Query- ExtA-B-04 SQL statement
The SQL-Query-ExtA-B-04 SQL statement
The SELECT action in the Action Output window
SECTION C – ADVANCED BI AND BIG DATA
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 113 04/07/19 8:16 PM
A-114 Online Extension A Working with MySQL
The ODBC standard allows programmers to code instructions to various DBMS products using ODBC standard statements. These instructions are passed to an ODBC driver, which translates them into the API of the specific DBMS in use. The driver receives results back from the DBMS and translates those results into a form that is part of the ODBC standard.
ODBC Architecture The basic ODBC architecture in a three-tier Web server envi- ronment (the connections between Web pages and databases is a typical use of ODBC) is shown in Figure A-115. The application program, the ODBC driver manager, and the ODBC DBMS driver (a multiple-tier driver in this case) all reside on the Web server. The DBMS driver sends requests to data sources, which reside on the database serv- er. According to the ODBC standard, a data source is the database and its associated DBMS, operating system, and network platform.
The application issues requests:
• to create a connection with a data source • to issue SQL statements and receive results • to process errors • to start, commit, and roll back transactions
ODBC provides a standard means for each of these requests, and it defines a standard set of error codes and messages.
The driver manager serves as an intermediary between the application and the DBMS drivers. When the application requests a connection, the driver manager determines the type of DBMS that processes a given ODBC data source and loads that driver in memory (if it is not already loaded).
A DBMS driver manager processes ODBC requests and submits specific SQL state- ments to a given type of data source. There is a different driver for each data source type. It is the responsibility of the driver to ensure that standard ODBC commands execute cor- rectly. The driver also converts data source error codes and messages into the ODBC stan- dard codes and messages.
ODBC identifies two types of drivers: single tier and multiple tier. A single-tier driver processes both ODBC calls and SQL statements. A multiple-tier driver processes ODBC calls but passes the SQL requests directly to the database server. Although it may reformat an SQL request to conform to the dialect of a particular data source, it does not process the SQL.
Establishing an ODBC Data Source Name A data source is an ODBC data struc- ture that identifies a database and the DBMS that processes it. The three types of data sources are file, system, and user. A file data source is a file that can be shared among database users. The only requirement is that the users have the same DBMS driver and privilege to access the database. A system data source is local to a single computer. The op- erating system and any user on that system (with proper permissions) can use a system data source. A user data source is available only to the user who created it. Each created data source is given a data source name (DSN) that is used to reference the data source.
FIGURE A-115
ODBC Three-Tier Web Server Architecture
SQL Commands
Web ServerUser Client
ApplicationWeb Browser Driver Manager DBMS Driver
Database Server
DBMS Database
Tier 1 Tier 2 Tier 3
Z04_KROE8149_09_SE_EXTA.indd 114 04/07/19 8:16 PM
Online Extension A Working with MySQL A-115
In general, the best choice for Internet applications is to create a system data source on the Web server. Browser users then access the Web server, which, in turn, uses a system data source to set up a connection with the DBMS and the database. ODBC system data sources can also be used for non-Web applications.
FIGURE A-116
The ODBC Data Sources (32-bit) Icon in the Windows Start Menu
The Windows Administrative Tools folder
The ODBC Data Sources (32-bit) icon button
The Windows 10 Start button
7For a discussion of creating Web sites that connect to databases using an OBDC system data source, see Chapter 11 in David M. Kroenke, David J. Auer, Scott L. Vandenberg, and Robert C. Yoder, Database Processing: Fundamentals, Design, and Implementation, 15th ed. (Upper Saddle River, NJ: Pearson, 2019).
How Do I Create an ODBC System Data Source for MySQL 8.0? To create a system data source in the Windows operating system, you use the ODBC Data Source Administrator.7 In Windows 10, you open the ODBC Data Source Administrator8 by using the Start | Windows Administrative Tools | ODBC Data Sources (32-bit) command, as shown in Figure A-116. Alternatively, you can open the ODBC Data Source Administrator as follows:
1. Open the Windows Control Panel by typing Control Panel in the search bar, and then clicking the Control Panel icon when it is displayed.
2. In the Control Panel window, click System and Security.
8Warning: There are actually multiple versions of the ODBC Data Source Administrator in the 64-bit versions of Windows 10, Windows 8.1, and Windows 7. You must use the correct version depending on whether the DBMS you are using is a 32-bit or 64-bit version. To make matters worse, all the versions use the same file name of odbcad32.exe! Fortunately, in Windows 10 both the 32-bit and the 64-bit versions are easily accessible via the Windows Start Menu. The instructions on this page open the 32-bit version, which works with 32-bit programs (we are using the 32-bit version of Office 2019). If you are using ODBC to connect to a 64-bit version of Mi- crosoft Access 2019, Microsoft SQL Server 2017, Oracle Database XE, or MySQL 8.0, then you may need to use the version of the ODBC Data Sources (64-bit) application available in the Windows Administrative Tools folder in the Windows Start Menu.
Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 115 04/07/19 8:16 PM
A-116 Online Extension A Working with MySQL
3. In the System and Security window, click Administrative Tools. 4. In the Administrative Tools window, right-click the ODBC Data Sources (32-bit) shortcut
icon, and then click Run as administrator. The ODBC Data Source Administrator (32-bit) application is displayed, as shown in Figure A-117(a).
Here is how you create a system data source named WMIS for use with the WMCRM da- tabase on a MySQL 8.0 DBMS:
FIGURE A-117
Creating the New System Data Source
(a) The Create New Data Source Dialog Box
The ODBC Data Source Administrator (32-bit) application
The Create New Data Source dialog box
The MySQL ODBC 8.0 ANSI Driver
The Finish button
The System DSN tab
The Add button
Creating an ODBC System Data Source:
1. In the ODBC Data Source Administrator, click the System DSN tab and then click the Add button to display the Create New Data Source dialog box, as shown in Figure A-117(a).
2. In the Create New Data Source dialog box, we need to connect to MySQL 8.0, so we select the MySQL ODBC 8.0 ANSI Driver, as shown in Windows 10 in Figure A-117(a).
3. Click the Finish button. The MySQL Connector/ODBC Data Source Configuration dia- log box is displayed, as shown in Figure A-117(b).
4. Enter a name for your data source (WMCRM), a description of the use of the data source (ODBC Connection to WMCRM), the user (WMCRM-User), the password (WMCRM- User+password), and the database (wmcrm). Note that we are using the WMCRM-User login that we created in Section 6 of “Working with MySQL,” and that we do not need to provide a Server name (the TCP/IP port number is sufficient).
5. Click the Test button. If the connection parameters are correct, a Text Result dialog box with a “Connection Successful” message will appear, as shown in Figure A-117(c).
ODBC Data Source Administrator (32-bit), Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 116 04/07/19 8:16 PM
Online Extension A Working with MySQL A-117
FIGURE A-117 (Continued)
Creating the New System Data Source
(b) The MySQL Connector/ODBC Data Source Configuration Dialog Box
The MySQL Connector/ ODBC Data Source Configuration dialog box
DO NOT enter a Server name—the TCP/IP Port number is sufficient
Enter the password WMCRM-User+password that we created in Section 6
Enter a name for the data source here
Enter a description of the data source here
Enter the wmcrm database (schema) name
The Test button
The OK button
Enter the user name WMCRM-User that we created in Section 6
(c) The Test Result Dialog Box
The ODBC connection parameters are correct
The Test Result dialog box
The OK button
6. Click the OK button in the Text Result dialog box to close the dialog box 7. Click the OK button on the MySQL Connector/ODBC Data Source Configuration dialog
box. The data source is created as shown in Figure A-117(d). 8. Click the OK button on the ODBC Data Source Administrator (32-bit) application to close it.
How Do I Create an ODBC Connection from Microsoft Access 2019 to a MySQL 8.0 Database? Having created a ODBC system data source, we will now use it to connect between Micro- soft Access 2019 and MySQL 8.0. For our example, we will continue to use the WMCRM
MySQL Community Server 8.0, MySQL Connector/ODBC, Oracle Corporation.
MySQL Community Server 8.0, MySQL Connector/ODBC, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 117 04/07/19 8:16 PM
A-118 Online Extension A Working with MySQL
FIGURE A-117 (Continued)
Creating the New System Data Source
(d) The Completed WMCRM System DSN
The ODBC Data Source Administrator (32-bit) application
The completed WMCRM system data source
The OK button
database that we have been using in MySQL 8.0, and we will create a Microsoft Access 2019 database to act as the application development environment for the WMCRM da- tabase. We will name our Microsoft Access 2019 database as WMIS.accdb (for Walling- ford Motors Information System).
Creating the WMIS.accdb Database:
1. Open Microsoft Access 2019. 2. Click the Blank desktop database icon to open the Blank desktop database dialog box. 3. In the Blank database dialog box, type in the file name WMIS.accdb, and then click the
Create button. ■ NOTE: The WMIS.accdb file will be created in your c:\Documents folder by default,
and that is where we have created the file. If you want to put the file in a different folder, click the Browse button to open the File New Database dialog box, browse to the desired folder, and create the WMIS database there.
4. The new WPIS.accdb database is displayed. 5. Close the open Table1 tabbed window.
Because we will be connecting to a DBMS that uses ANSI standard SQL (ANSI 92) instead of Microsoft ANSI-89 SQL (this topic is discussed in depth at the beginning of Chapter 3), we need to set the Microsoft Access 2019 options for WPIS.accdb to work with ANSI standard SQL.
Setting the Microsoft Access 2019 SQL Setting:
1. Click the File command tab, and then click the Options button. The Access Options dia- log box is displayed.
2. In the Access Options dialog box, click the Object Designers button to display the Object Designers page.
ODBC Data Source Administrator (32-bit), Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 118 04/07/19 8:16 PM
Online Extension A Working with MySQL A-119
3. In the Object Designers page, find the SQL Server Compatible Syntax (ANSI 92) section of the Query Design settings. Click the This database checkbox as shown in Figure A-118. MySQL uses the new standard as well as SQL Server.
4. Click the OK button to save the settings and close the dialog box. 5. A Microsoft Access Information dialog box is displayed, as shown in Figure A-119. 6. Read the information in the dialog box, and then click the OK button. 7. Microsoft Access takes the actions discussed in the information dialog box and then
reopens the WMIS.accdb database with the Security Warning message bar displayed. 8. In the Security Warning message bar, click the Enable Content button.
FIGURE A-118
The SQL Server Compatible Syntax (ANSI 92) Checkbox
The Access Options dialog box
The SQL Server Compatible Syntax (ABSI 92) settings
The Object Designers button
The Query design settings
The This database checkbox
The OK button
FIGURE A-119
The Microsoft Access Information Dialog Box
Access 2019, Windows 10, Microsoft Corporation.
Access 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 119 04/07/19 8:16 PM
A-120 Online Extension A Working with MySQL
The WMIS database is now ready to use. Our next step is to connect to the WP database in the MySQL 8.0 server on your computer. To do this, we will use the WMCRM ODBC system data source we just created. Note that this data source uses the WMCRM-User login we previously created in Section 6 of “Working with MySQL,” and therefore this connection will have the permissions granted to that user.
Linking the Microsoft Access 2019 Database to an External Data Source via ODBC:
1. In the Microsoft Access 2019 WPIS.accdb database, click the External Data command tab, and then click the New Data Source button in the Import & Link commands section.
2. In the drop-down list, click the From Other Sources button and then click the OBDC Database button as shown in Figure A-120. The Get External Data – ODBC Database dialog box appears.
3. In the Get External Data – ODBC Database dialog box, the Select the source and destination of the data page is displayed, as shown in Figure A-121. Click the Link to the data source by creating a linked table radio button also shown in Figure A-121, and then click the OK button.
4. The Select Data Source dialog box is displayed, as shown in Figure A-122. This is the dia- log box that we will use to select the needed OBDC DSN. In the Select New Data Source dialog box, click the Machine Data Source tab, then click the WMCRM data source name to select it, and then click the OK button.
5. The Link Tables dialog box is displayed, as shown in Figure A-123. We need to select all the tables shown in the list, but not select any SQL views shown in the list (there is only one—customerphoneview). You can use the Select All button to select everything in the list, but if you do you will need to deselect any SQL views in the list. After you have se- lected the correct set of tables, click the OK button.
FIGURE A-120
The New Data Source | From Other Sources | ODBC Database Command
The External Data command tab
The From Other Sources button
The ODBC Database button
The New Data Source drop-down list button
The New Data Source drop-down list
Access 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 120 04/07/19 8:16 PM
Online Extension A Working with MySQL A-121
FIGURE A-121
The Get External Data – ODBC Database Dialog Box
The Get External Data - ODBC Database dialog box
The Link to the data source by creating a linked table radio button
The OK button
FIGURE A-122
The Select Data Source Dialog Box
The Select Data Source dialog box
The OK button
The Machine Data Source tab
Select the MySQL Connector/ODBC WMCRM system data source
Access 2019, Windows 10, Microsoft Corporation.
Access 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 121 04/07/19 8:16 PM
A-122 Online Extension A Working with MySQL
FIGURE A-123
The Link Tables Dialog Box
The Link Tables dialog box
The OK button
Do not save the password
Select all the tables in the MySQL WMCRM database, but to not select any SQL views
The Select All button— clicking this button will select everything in the Tables list, including the SQL views—if you do click it, deselect any SQL views in the list
6. The tables in the MySQL database are linked to Microsoft Access, and the linked table objects are displayed in the Tables section of the Navigator pane, as shown in Figure A-124.
7. We can now use the WMIS database to create forms, reports, queries, and other application features that actually use the data in the WMCRM database in MySQL 8.0. To illustrate this, Figure A-125 shows a WMCRM Customer Data Form built based on the linked Customer table. Compare this form to the one in Figure WA-1-36. They are basically the same, although you will see some differences because the form in Figure WA-1-36 was created based on only the first iteration of the CUSTOMER table, and Figure A-125 is based on a more complete and modified database. Nonetheless, they are similar enough to illustrate the point we are making.
8. Click the Save button on the Microsoft Access Quick Access Toolbar to make sure any changes to the WMIS database have been saved, and then close Microsoft Access 2019.
We now have a Microsoft Access 2019 database we can use for application development using data stored in a MySQL 8.0 database running on a MySQL Community Server 8.0 DBMS.
Access 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 122 04/07/19 8:16 PM
Online Extension A Working with MySQL A-123
FIGURE A-124
The Linked WMCRM Tables
The tables in the Table section are all linked tables in the MySQL WRCRM database
FIGURE A-125
The WMCRM Customer Data Form Using the Linked WMCRM Tables
The WMCRM Customer Data Form tabbed document window
The Forms section of the Navigator pane
The WMCRM Customer Data Form object
The New Record button
Access 2019, Windows 10, Microsoft Corporation.
Access 2019, Windows 10, Microsoft Corporation.
Z04_KROE8149_09_SE_EXTA.indd 123 04/07/19 8:16 PM
A-124 Online Extension A Working with MySQL
The purpose of online Extension A, “Working with MySQL,” is to allow you to use MySQL 8.0 as needed for your solutions to the Exercises, Case Questions, and Project Questions in various chapters and online Extensions of Database Concepts, 9th edition. Specifically, Sections 1 and 2 of “Working with MySQL” will prepare you to create and use the Wedge- wood Pacific (WP) database in Chapter 3 so that you can run the SQL queries in the text (see Exercises A.27–A.33). Similarly, each section of “Working with MySQL” will prepare you to do the exercises, case questions, and project questions in the corresponding chapter in the text.
SUMMARY
*.sql extension */ (asterisk slash) /* (slash asterisk) Administrative Role ANSI standard SQL (ANSI 92) Application Programming Interface
(API) authentication authorization autocommit mode backup commit CRUD data export dump data import data model data source data source name (DSN) data warehouse database design database management systems
(DBMSs) default schema extended entity-relationship model Extract, Transform, and Load (ETL) file data source machine data source
Microsoft ANSI-89 SQL multiple-tier driver MySQL CHANGE [COLUMN]
keyword MySQL COLUMN keyword MySQL Community Server MySQL Community Server MySQL Connecter/NET MySQL Connector/ODBC MySQL for Excel MySQL Installer for Windows MySQL MODIFY [COLUMN]
keyword MySQL RENAME COLUMN
keyword MySQL Workbench navigator window non identifying nonidentifying non-identifying non-relational database ODBC architecture ODBC DBMS driver ODBC driver manager online analytical processing (OLAP) Open Database Connectivity
(ODBC)
Pivot Table recovery recursive relationship relational database rollback root schema schema privileges single-tier driver spreadsheet SQL ALTER TABLE statement SQL commands SQL COMMIT statement SQL ROLLBACK statement SQL script SQL script comments SQL scripts SQL START TRANSACTION
statement SQL view system data source systems analysis and design user data source Windows operating system workbook worksheet
KEY TERMS
A.1 What is MySQL? A.2 What is the primary advantage of using MySQL instead of SQL Server 2017? A.3 What are the six MySQL programs that are recommended as a useful set of MySQL
software products?
A.4 What are two purposes of the MySQL Workbench? A.5 What is a MySQL schema?
REVIEW QUESTIONS
Z04_KROE8149_09_SE_EXTA.indd 124 04/07/19 8:16 PM
Online Extension A Working with MySQL A-125
A.6 How do you create a new database in MySQL? A.7 What is a MySQL default schema? How do you specify the default schema? A.8 What is an SQL script? What types of SQL statements and commands can be run
more efficiently as scripts?
A.9 What tool(s) can you use to create an SQL script? A.10 What file extension should you use with MySQL scripts? A.11 How do you create, save, and run an SQL script in MySQL? A.12 How does MySQL use the AUTO_INCREMENT keyword? What limitations are
there on the use of the AUTO_INCREMENT keyword?
A.13 How do you create and run an SQL query in MySQL? A.14 How do you obtain online help when using the MySQL Workbench? Where is the
help information displayed?
A.15 What is the purpose of the MySQL Connector/OBDC? How do you install it, and where does it appear after it is installed?
A.16 How do you import Microsoft Excel 2019 data into a MySQL 8.0 table? A.17 What is a data model? What is a database design? Which one does MySQL
Workbench create? Why can’t MySQL Workbench create the other one?
A.18 How do you create a new model in MySQL Workbench? A.19 How do you create a new EER (E-R) diagram in the MySQL Workbench? A.20 How do you create a table in an EER (E-R) diagram? A.21 How do you create a 1:N non-identifying relationship in an EER (E-R) diagram? A.22 How does the MySQL Workbench handle N:M relationships? A.23 How do you create user accounts in MySQL 8.0? How do you give them appropri-
ate permissions to a specific database?
A.24 Why would you want to create an ODBC connection to link Microsoft Access 2019 database to a MySQL 8.0 Database?
A.25 What is an ODBC DSN? Why is one needed? A.26 How do you create an ODBC connection to link a Microsoft Access 2019 database
to a MySQL 8.0 Database?
EXERCISES
A.27 If you haven’t already done so, download and install MySQL Community Server 8.0, the MySQL Workbench, and other MySQL products as described in Section 1.
A.28 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 to create the WP table structure. D. Creating and running an SQL script named WP-Insert-Datas based on
Figure 3-11 to populate the WP tables.
Z04_KROE8149_09_SE_EXTA.indd 125 04/07/19 8:16 PM
A-126 Online Extension A Working with MySQL
A.29 Use MySQL and the MySQL Workbench to create and run the following SQL queries in Chapter 3:
• SQL-Query-CH03-01 through SQL-Query-CH03-32 • SQL-Query-CH03-35 through SQL-Query-CH03-53 • Save each query as follows:
• Create and run each query in the MySQL Workbench. • After you have run each query, use the File | Save Script As command to save
the query. By default, MySQL saves each file as an *.sql script. Name your queries in numerical sequence, starting with the file name MySQL-SQL- Query-CH03-01.sql.
A.30 Use MySQL and the MySQL Workbench to run one or more of the saved SQL queries you created in exercise A.29:
• At this point, you should have only one SQL Script window open. Open a query with the File | Open SQL Script command.
• Experiment with opening and closing SQL Script windows and running various queries in these windows.
A.31 Complete exercise 3.56 using MySQL. Start each saved query name with MySQL- and use the default *.sql file extension. (The first saved query name should be MySQL-SQL-Query-WAE-3-1-A.sql.)
A.32 Complete exercise 3.57 using MySQL. Start the saved query name with MySQL- and use the default *.sql file extension. Create as many scripts as you need for Parts A through D. The saved query name should be MySQL-SQL-Query-WAE-3-3-E.sql.
A.33 Complete exercise WA.B.4 in online Extension B, “Advanced SQL,” using MySQL. This exercise requires you to import Microsoft Excel 2019 data into three tables in the MySQL WP schema. Create as many SQL scripts as you need to complete this work.
A.34 Create a WP-User user account in the WP database with the password WP- User+password. Make this user a database administrator.
A.35 Create a user account in the WP database named WP-Reader. Give this user the password of WP-Reader+password and with other password settings to match those shown in the text in Figure A-69. Give WP-Reader only SELECT object rights to the WP database.
A.36 Create a Microsoft Access 2019 database named WPIS_RO.accdb where RO stands for “read-only.” This database will be a read-only application for the MySQL WP database, which will allow users to read and query the data in the WP database but not to make any updates to the data or to insert new data. Then:
A. Set the WPIS_RO.accdb database to use SQL Server Compatible Syntax (ANSI 92).
B. Link the WPIS_RO.accdb database to the MySQL WP database. When you create your System Data Source DSN, name the DSN WP_RO, and use the WP-Reader user account (as detailed in Exercise A.35) for MySQL authentication.
C. Import all existing tables (including the COMPUTER and COMPUTER_ ASSIGNMENT tables if they have been imported as detailed in Exercise A.33).
D. Create a form to show all the data in the PROJECT table named WP Projects Form.
E. Create a report to show all the data in the PROJECT table named WP Projects Report.
A.37 The database design for Heather Sweeney Designs is shown in Figure 5-27. In the MySQL Workbench, create a new MySQL model named HSD-Database-Design. In this model, add an EER (E-R) diagram, and recreate the database design shown in Figure 5-27 in this diagram. Use data types based on Figures 5-26 and 5-28.
Z04_KROE8149_09_SE_EXTA.indd 126 04/07/19 8:16 PM
Online Extension A Working with MySQL A-127
A.38 The database design for the Wallingford Motor Customer Relationship Manage- ment database is shown in Figure WA-5-2. In the MySQL Workbench, create a new MySQL model named WMCRM-Database-Design. In this model, add an EER (E-R) diagram, and recreate the database design shown in Figure WA-5-2 in this diagram. Use data types based on those shown in figures in Chapters 1, 2, 4, and 5.
A.39 If you haven’t done so, work through Section 3 of “Working with MySQL” to create the MySQL 8.0 database named WMCRM. Complete the work on the WMCRM database to include all parts of the WMCRM database described.
A.40 If you haven’t done so, work through this Section 4 of “Working with MySQL” to import Microsoft Excel 2019 worksheet data into the WMCRM SPECIFICATIONS_2019 table. At this point, the SPECIFICATIONS_2019 is not linked to any other table(s) in the WMCRM database. What table(s) should be linked to the SPECIFICATIONS_2019 table? Create the necessary relationship(s). Hint: If there is duplicate data after the tables are linked, delete the appropriate columns from the previously existing tables(s), not from SPECIFICATIONS_2019.
A.41 Open the MySQL Database Model named WMCRM-Database-Design that you created in exercise A.34. Save it as a new file named WMCRM-Database-Design- Revision-01. Add the WMCRM SPECIFICATIONS_2019 table and any new relationships to the E-R diagram, and make any other changes to other tables needed to update the WMCRM database design to conform to the WMCRM database after the SPECIFICATIONS_2019 table was added.
A.42 Write SQL statements to accomplish the following tasks, and submit them to MySQL 8.0 via MySQL Workbench. Save your work in an SQL script named WMCRM-ExtA-EX-A-42.sql.
A. Create a table named CUSTOMER_NAMES with the following columns:
Column Name Type NULL status Key CustomerID Int NULL No
CustomerName Char (50) NULL No
MailingAddressName Char (50) NULL No
CustomerLastName Char (25) NULL No
CustomerFirstName Char (25) NULL No
In Extension B, “Advanced SQL,” we discuss user-defined functions in the section on SQL/Persistent Stored Modules (SQL/PSM). As we discuss in Extension B, a user-defined function (also known as a stored function) is a stored set of SQL state- ments that:
• Is called by name from another SQL statement • May have input parameters passed to it by the calling SQL statement • Returns an output value to the SQL statement that called the function
The logical process flow of a user-defined function is illustrated in Figure B-31. SQL/PSM user-defined functions are similar to the SQL built-in functions (COUNT, SUM, AVG, MAX, and MIN) that we discussed and used in Chapter 3, except that, as the name implies, we create them ourselves to perform specific tasks that we need to do.
In Extension B, we used as our example the common problem of needing a name in the format LastName, FirstName (including the comma) in a report when the da- tabase stores the basic data in two fields named FirstName and LastName. To create this combined name, we created a user-defined function named NameConcatenation.
Z04_KROE8149_09_SE_EXTA.indd 127 04/07/19 8:16 PM
A-128 Online Extension A Working with MySQL
B. Create the NameConcatenation user-defined function as described in Extension B, “Advanced SQL.” Note that MySQL uses DELIMITER state- ments as part of the SQL code. In this instance, the term delimiter refers to the symbol or symbols we use at the end of the block of code that creates the user-defined function. First, we change the delimiter to “//”, and after we use it, we change it back to “;”.
C. In Chapter 3 on SQL, we discussed the SQL INSERT statement. This state- ment can be used with a subquery to insert data into one table from another. This is known as a bulk insert. Use the NameConcatenation user-defined func- tion in a bulk insert to populate the CustomerID and CustomerName columns in the CUSTOMER_NAMES table as shown here:
/* *** SQL-INSERT-ExtA-B-01 *** */ INSERT INTO CUSTOMER_NAMES (CustomerID, CustomerName) (SELECT CustomerID, NameConcatenation(FirstName, LastName) FROM CUSTOMER ORDER BY CustomerID);
Note that an SQL subquery can be used in SQL statements beyond just the SQL SELECT statement. Here we used an SQL subquery in an INSERT statement, but we can also use one in UPDATE, DELETE, and MERGE statements, and it is often exactly what we need!
D. Alter the CUSTOMER_NAMES table so that the CustomerID column is the primary key of the table.
Having dealt with the problem of concatenating two separate name values into one, let’s consider the opposite problem: separating a combined name into separate ele- ments. This is a problem that commonly occurs when using data provided to us in a Microsoft Excel worksheet, where the user simply put an entire name into one cell. As an practical example of this, consider the WMCRM CUSTOMER_NAMES CustomerName column we just populated, where CustomerName contains the combined customer name in LastName, FirstName format.
Because a best practice of database design is to decompose data like this into its separate elements, we have the problem of breaking this data into LastName and FirstName. We can use a user-defined function to do this. Note that the delineator or separator between LastName and FirstName in CustomerName is a comma. We can search for the comma using the MySQL built-in character string function LOCATE, which will return the numeric position of the comma. The full syntax of the function is:
LOCATE (ExpressionToFind, ExpressionToSearch [, StartLocation])
In the CUSTOMER_NAMES table, we want to find the comma (“,”) in Cus- tomerName starting at the default location of 1 (character strings in MySQL are counted starting at 1, not 0). Once we have found the comma, we can retrieve the last name by using the MySQL built-in character string function SUBSTRING, which will return a subset of the characters from a character string. The full syntax of the function is:
SUBSTRING (ExpressionToSearch, StartLocation, Length)
In the CUSTOMER_NAMES table, we want to return the subset of Custom- erName starting at 1 and ending one character below the comma ([Value returned by LOCATE] – 1). We put these together into a user-defined function named GetLastNameCommaSeparated as shown in Figure A-126. Note that varName in the function was set to a length of 45 to match the length assigned by the MySQL for Excel import process.
Z04_KROE8149_09_SE_EXTA.indd 128 04/07/19 8:16 PM
Online Extension A Working with MySQL A-129
FIGURE A-126
The SQL Statements for the GetLastNameCommaSeparated User-Defined Function
DELIMITER //
CREATE FUNCTION GetLastNameCommaSeparated
-- These are the input parameters (
varName VARCHAR(45) ) RETURNS VARCHAR(25) DETERMINISTIC BEGIN
-- This is the variable that will hold the value to be returned DECLARE varLastName VARCHAR(25);
-- This is the variable that will hold the position of the comma DECLARE varIndexValue INT;
-- SQL statement to find the comma separator
SET varIndexValue = LOCATE(',', varName);
-- SQL statement to determine last name SET varLastName = SUBSTRING(varName, 1, (varIndexValue - 1));
-- Return the last name RETURN varLastName;
END //
DELIMITER ;
E. Create the GetLastNameCommaSeparated user-defined function in the WMCRM database.
We can now use the GetLastNameCommaSeparated user-defined function to pop- ulate the CustomerLastName column of the CUSTOMER_NAMES table. But be- fore we create the SQL statements to modify the CUSTOMER_NAMES table data, we need to discuss a default setting used in MySQL Workbench that will affect what we are trying to do. By default, MySQL Workbench does not allow the execution of SQL UPDATE and DELETE statements unless these statements have an SQL WHERE clause. This setting is called safe updates. As we learned in our Chapter 3 discussion of SQL DML actions, an UPDATE statement without a WHERE clause will affect every row in the table, and a DELETE statement without a WHERE clause will remove every row in a table. Not a good idea!
To prevent this from inadvertently happening, MySQL simply won’t let you do it. However, when altering the structure of a table, it can be necessary to UPDATE every row in the table. Therefore, before modifying the CUSTOMER_NAMES table data, we need to disable safe updates.
Figure A-126 shows the location of the Edit | Preferences command, which is used to open the MySQL Workbench Preferences dialog box.
Use the Edit | Preferences command to open the MySQL Workbench Preferences dialog box, which appears as shown in Figure A-128. In the MySQL Workbench Preference dialog box, click the SQL Editor tab and then ensure that the Safe Updates (rejects UPDATEs and DELETEs with no restrictions) check box is unchecked, and then click the OK button, as shown in Figure A-127. If it was previously checked, then you will need to click the Reconnect to DBMS but-
Z04_KROE8149_09_SE_EXTA.indd 129 04/07/19 8:16 PM
A-130 Online Extension A Working with MySQL
FIGURE A-128
Allowing UPDATES without an SQL WHERE Clause
The Workbench Preferences dialog box
Click SQL Editor
The OK button
The SQL Editor setting page
Uncheck the Safe Updates (rejects UPDATES and DELETES with no restrictions) check box
FIGURE A-127
The Edit | Preferences Command
Select Edit, then click Preferences to display the Workbench Preferences dialog box
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Z04_KROE8149_09_SE_EXTA.indd 130 04/07/19 8:16 PM
Online Extension A Working with MySQL A-131
Ms. Marcia Wilson owns and operates Marcia’s Dry Cleaning, which is an upscale dry cleaner in a well-to-do suburban neighborhood. Marcia makes her business stand out from the compe- tition by providing superior customer service. She wants to keep track of each of her customers and their orders. Ultimately, she wants to notify them that their clothes are ready via email.
Assume that Marcia has hired you as a database consultant to develop an operational database named MDC having the following four tables:
CUSTOMER (CustomerID, FirstName, LastName, Phone, EmailAddress) INVOICE (InvoiceNumber, CustomerID, DateIn, DateOut, Subtotal, Tax,
TotalAmount)) INVOICE_ITEM (InvoiceNumber, ItemNumber, ServiceID, Quantity,
UnitPrice, ExtendedPrice) SERVICE (ServiceID, ServiceDescription, UnitPrice)
The referential integrity constraints are:
CustomerID in INVOICE must exist in CustomerID in CUSTOMER InvoiceNumber in INVOICE_ITEM must exist in InvoiceNumber in INVOICE ServiceID in INVOICE_ITEM must exist in ServiceID in SERVICE
MARCIA’S DRY CLEANING CASE QUESTIONS
ton shown in Figure A-8 to reset the connection between the MySQL Workbench and the DBMS. You may need to scroll down to see the Safe Updates checkbox.
F. Populate the CUSTOMER_NAMES CustomerLastName column using the following SQL UPDATE statement:
/* *** SQL-UPDATE-ExtA-B-03 *** */ UPDATE CUSTOMER_NAMES SET CustomerLastName = GetLastNameCommaSeparated(CustomerName);
G. Create a user-defined function named GetFirstNameCommaSeparated that takes a comma separated name as input and returns the first name as output.
H. Populate the CUSTOMER_NAMES CustomerFirstName column using the GetFirstNameCommaSeparated user-defined funtion.
I. Write a user-defined function named FirstNameFirst that concatenates the CUSTOMER table LastName and FirstName into a single value named CustomerName and displays, in order, the CustomerFirstName, space, and the CustomerLastName (Hint: Johnson and Ariel would be combined to read Ariel Johnson).
J. Use the FirstNameFirst user-defined function in a bulk update to populate the MailingAddressName column in the CUSTOMER_NAMES table.
K. At this point, we need to reset the safe updates setting back to its default—leaving it disabled is asking for trouble! Repeat the steps discussed earlier, but this time check the Safe Updates (rejects UPDATEs and DELETEs with no restrictions) check box.
L. Set CUSTOMER_NAMES.CustomerID as a foreign key referencing CUSTOMER.CustomerID.
Z04_KROE8149_09_SE_EXTA.indd 131 04/07/19 8:16 PM
A-132 Online Extension A Working with MySQL
FIGURE A-129
Sample Data for the MDC CUSTOMER Table
FirstName LastNameCustomerID Phone EmailAddress
Nikki
Brenda
Bruce
Betsy
George
Kathy
Betsy
Kaccaton
Catnazaro
LeCat
Miller
Miller
Miller
Miller
100
105
110
115
120
125
130
723-543-1233
723-543-2344
723-543-3455
723-654-3211
723-654-4322
723-514-9877
723-514-8766
ServiceDescription UnitPriceServiceID
Men’s Shirt
Dress Shirt
Women’s Shirt
Blouse
Slacks – Men’s
Slacks – Women’s
Skirt
Dress Skirt
Suit – Men’s
Suit – Women’s
Tuxedo
Formal Gown
$1.50
$2.50
$1.50
$3.50
$5.00
$6.00
$5.00
$6.00
$9.00
$8.50
$10.00
$10.00
10
11
15
16
20
25
30
31
40
45
50
60
FIGURE A-130
Sample Data for the MDC SERVICE Table
Assume that CustomerID of CUSTOMER and InvoiceNumber of INVOICE are surrogate keys with values as follows:
CustomerID Start at 100 Increment by 5 InvoiceNumber Start at 2019001 Increment by 1
Note that because CustomerID increments by 5, you cannot use AUTO_INCREMENT to insert the proper CustomerID values. Further, assume that ServiceID is a surrogate key, but not one that automatically increments—the values of ServiceID are assigned by Marcia’s Dry Cleaning management when new services are added at Marcia’s Dry Cleaning.
Data for the CUSTOMER table is shown in Figure A-129, data for the SERVICE table is shown in Figure A-130, data for the INVOICE table is shown in Figure A-131, and data for the INVOICE_ITEM table is shown in Figure A-132.
Z04_KROE8149_09_SE_EXTA.indd 132 04/07/19 8:16 PM
Online Extension A Working with MySQL A-133
A. Specify NULL/NOT NULL constraints for each table column. B. State relationships as implied by foreign keys, and specify the maximum and minimum
cardinality of each relationship. Justify your choices.
C. Illustrate your answers to questions A and B by drawing a database design for the MDC database in MySQL Workbench.
D. Using MySQL 8.0 and the MySQL Workbench, create a database named MDC. E. In the MySQL Workbench folder structure in your My Documents folder, create a
folder named DBC-e09-MDC-Database in the Schemas folder. Use this folder to save and store *.sql scripts containing the SQL statements that you are asked to create in the remaining questions in this section.
Using the MDC database, create an SQL script named MDC-Create-Tables.sql to answer part F.
F. Write CREATE TABLE statements for each of the tables using your answers to parts A–D, as necessary. Set the first value of CustomerID to 100 and increment it by 5 (note that AUTO_INCREMENT cannot be used here). Set the first value of InvoiceNumber to 2019001 and increment it by 1. Use FOREIGN KEY constraints to create appropri- ate referential integrity constraints. Set UPDATE and DELETE behavior in accordance with your referential integrity action design. Set the default value of Quantity to 1. Write a CHECK constraint that SERVICE.UnitPrice be between 1.50 and 10.00.
Using the MDC database, create an SQL script named MDC-Insert-Data.sql to answer part G.
G. Write INSERT statements to insert the data shown in Figures A-129, A-130, A-131, and A-132.
Using the MDC database, create an SQL script named MDC-Create-Views-and-Functions. sql to answer parts H through O.
H. Create a view called OrderSummaryView that contains INVOICE.InvoiceNumber, INVOICE.DateIn, INVOICE.DateOut, INVOICE_ITEM.ItemNumber, INVOICE_ ITEM.ServiceID, and INVOICE_ITEM.ExtendedPrice.
FIGURE A-131
Sample Data for the MDC INVOICE Table
CustomerID DateInInvoiceNumber DateOut SubTotal Tax TotalAmount
$158.50
$25.00
$49.00
$17.50
$12.00
$152.50
$7.00
$140.50
$27.00
100
105
100
115
125
110
110
130
120
04-Oct-19
04-Oct-19
06-Oct-19
06-Oct-19
07-Oct-19
11-Oct-19
11-Oct-19
12-Oct-19
12-Oct-19
2019001
2019002
2019003
2019004
2019005
2019006
2019007
2019008
2019009
06-Oct-19
06-Oct-19
08-Oct-19
08-Oct-19
11-Oct-19
13-Oct-19
13-Oct-19
14-Oct-19
14-Oct-19
$12.52
$1.98
$3.87
$1.38
$0.95
$12.05
$0.55
$11.10
$2.13
$171.02
$26.98
$52.87
$18.88
$12.95
$164.55
$7.55
$151.60
$29.13
Z04_KROE8149_09_SE_EXTA.indd 133 04/07/19 8:16 PM
A-134 Online Extension A Working with MySQL
ItemNumber ServiceIDInvoiceNumber Quantity UnitPrice ExtendedPrice
$3.50
$2.50
$10.00
$5.00
$6.00
$9.00
$2.50
$5.00
$6.00
$2.50
$3.50
$2.50
$3.50
$2.50
$5.00
$6.00
$3.50
$3.50
$2.50
$5.00
$6.00
$9.00
1
2
3
4
5
6
1
1
2
1
1
2
1
2
3
4
1
1
2
3
4
1
16
11
50
20
25
40
11
20
25
11
16
11
16
11
20
25
16
16
11
20
25
40
2019001
2019001
2019001
2019001
2019001
2019001
2019002
2019003
2019003
2019004
2019005
2019005
2019006
2019006
2019006
2019006
2019007
2019008
2019008
2019008
2019008
2019009
2
5
2
10
10
1
10
5
4
7
2
2
5
10
10
10
2
3
12
8
10
3
$7.00
$12.50
$20.00
$50.00
$60.00
$9.00
$25.00
$25.00
$24.00
$17.50
$7.00
$5.00
$17.50
$25.00
$50.00
$60.00
$7.00
$10.50
$30.00
$40.00
$60.00
$27.00
FIGURE A-132
Sample Data for the MDC INVOICE_ITEM Table
I. Create a view called CustomerOrderSummaryView that contains INVOICE .InvoiceNumber, CUSTOMER.FirstName, CUSTOMER.LastName, CUSTOMER .Phone, INVOICE.DateIn, INVOICE.DateOut, INVOICE.SubTotal, INVOICE_ITEM .ItemNumber, INVOICE_ITEM.ServiceID, and INVOICE_ITEM.ExtendedPrice.
J. Create a view called CustomerOrderHistoryView that (1) includes all columns of CustomerOrderSummaryView except INVOICE_ITEM.ItemNumber, INVOICE_ITEM. ExtendedPrice, and INVOICE_ITEM.ServiceID; (2) groups orders by CUSTOMER. LastName, CUSTOMER.FirstName, and INVOICE.InvoiceNumber, in that order; and (3) sums and averages INVOICE_ITEM.ExtendedPrice for each order for each customer. Name the sum as TotalExtendedPrice and the average as AverageExtendedPrice.
K. Create a view called CustomerOrderCheckView that uses CustomerOrderHistoryView and that shows any customers for whom the sum of INVOICE_ITEM
Z04_KROE8149_09_SE_EXTA.indd 134 04/07/19 8:16 PM
Online Extension A Working with MySQL A-135
GARDEN GLORY PROJECT QUESTIONS
The purpose of online Extension A, “Working with MySQL,” is to allow you to use MySQL 8.0 as needed for your solutions to the Garden Glory project questions in each chapter and online Extension of Database Concepts. Specifically, Sections 1 and 2 of “Working with MySQL” will prepare you to create and use the Wedgewood Pacific (WP) database in Chapter 3 so that you can run the SQL queries in the text (see Exercises A.27–A.33), and then create the Garden Glory (GG) database for the Chapter 3 project questions. Similarly, each section of “Working with MySQL” will prepare you to do the Garden Glory project questions in the text.
Note that the material on importing Microsoft Excel 2019 worksheet data into a MySQL database table covered in Section 4 of “Working with MySQL” is intended for use with project question K in online Extension B, “Advanced SQL.” We recommend that you study online Extension B, “Advanced SQL,” immediately after you work through the Chapter 3 material on SQL. Thus, you should work through Section B and Section 4 of “Working with MySQL” in that order, and apply what you learn to the Garden Glory proj- ect questions in “Advanced SQL.”
A. In the Chapter 3 Garden Glory project questions, you created the GARDEN_GLORY database. Document your GARDEN_GLORY database by creating a GARDEN_ GLORY database design in MySQL Workbench.
B. Create a user login named GG-User, with the password GG-User+password. Do not assign an administrative role to this user, but do assign all schema permissions except GRANT to the GARDEN_GLORY database.
C. Create an ODBC system data source name (DSN) named GG to the GARDEN_ GLORY database using the GG-User account.
.TotalExtendedPrice is not equal to INVOICE.SubTotal. Hint: for this data no rows will be displayed.
L. Create and test a user-defined function named LastNameFirst that combines two pa- rameters named FirstName and LastName into a concatenated name field formatted LastName, FirstName (including the comma and space).
M. Create and test a view called CustomerOrderSummaryView that contains the cus- tomer name concatenated and formatted as LastName, FirstName in a field named CustomerName, INVOICE.InvoiceNumber, INVOICE.DateIn, INVOICE.DateOut, and INVOICE.TotalAmount.
N. Create and test a user-defined function named FirstNameFirst that combines two pa- rameters named FirstName and LastName into a concatenated name field formatted FirstName LastName (including the space).
O. Create and test a view called CustomerDataView that contains the customer name concatenated and formatted as FirstName LastName in a field named CustomerName, along with Phone and EmailAddress.
P. Create a user login named MDC-User, with the password MDC-User+password. Do not assign an administrative role to this user, but do assign all schema permissions except GRANT to the MDC database.
Q. Create an ODBC system data source name (DSN) named MDC to the MDC database using the MDC-User account.
R. Create a new Microsoft Access 2019 database named MDCIS.accdb, and link it to the tables in the MySQL MDC database using the MDC ODBC DSN. Create a data input from for the CUSTOMER table named Marcia’s Dry Cleaning Customer Data Form.
Z04_KROE8149_09_SE_EXTA.indd 135 04/07/19 8:16 PM
A-136 Online Extension A Working with MySQL
JAMES RIVER JEWELRY PROJECT QUESTIONS
The purpose of online Extension A, “Working with MySQL,” is to allow you to use MySQL 8.0 as needed for your solutions to the James River Jewelry project questions in each chap- ter and online Extension of Database Concepts. Specifically, Sections 1 and 2 of “Working with MySQL” will prepare you to create and use the Wedgewood Pacific (WP) database in Chapter 3 so that you can run the SQL queries in the text (see Exercises A.27–A.33), and then create the James River Jewelry (JRJ) database for the Chapter 3 project questions. Similarly, each section of “Working with MySQL” will prepare you to do the James River Jewelry project questions in the text.
Note that the material on importing Microsoft Excel 2019 worksheet data into a MySQL database table covered in Section 4 of “Working with MySQL” is intended for use with project question K in online Extension B, “Advanced SQL.” We recommend that you study online Extension B, “Advanced SQL,” immediately after you work through the Chapter 3 material on SQL. Thus, you should work through Section B and Section 4 of “Working with MySQL” in that order, and apply what you learn to the James River Jewelry project ques- tions in “Advanced SQL.”
A. In the Chapter 3 James River Jewelry project questions, you created the JRJ database. Document your JRJ database by creating a JRJ database design in MySQL Workbench.
B. Create a user login named JRJ-User, with the password JRJ-User+password. Do not assign an administrative role to this user, but do assign all schema permissions except GRANT to the JRJ database.
C. Create an ODBC system data source name (DSN) named JRJ to the JRJ database using the JRJ-User account.
D. Create a new Microsoft Access 2019 database named JRJIS.accdb, and link it to the tables in the MySQL JRJ database using the JRJ ODBC DSN. Create a data input form for the CUSTOMER table named James River Jewelry Customer Data Form, and a data input form for the ITEM table named James River Jewelry Item Data Form.
THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS
The purpose of online Extension A, “Working with MySQL,” is to allow you to use MySQL 8.0 as needed for your solutions to The Queen Anne Curiosity Shop project ques- tions in each chapter and online Extension of Database Concepts. Specifically, Sections 1 and 2 of “Working with MySQL” will prepare you to create and use the Wedgewood Pa- cific (WP) database in Chapter 3 so that you can run the SQL queries in the text (see Exercises A.27–A.33), and then create The Queen Anne Curiosity Shop (QACS) database for the Chapter 3 project questions. Similarly, each section of “Working with MySQL” will prepare you to do The Queen Anne Curiosity Shop project questions in the text.
Note that the material on importing Microsoft Excel 2019 worksheet data into a MySQL database table covered in Section 4 of “Working with MySQL” is intended for use with project question N in online Extension B, “Advanced SQL.” We recommend that you study online Extension B, “Advanced SQL,” immediately after you work through the Chapter 3 material on SQL. Thus, you should work through Section B and Section 4 of
D. Create a new Microsoft Access 2019 database named GGIS.accdb, and link it to the tables in the MySQL GARDEN_GLORY database using the GG ODBC DSN. Create a data input form for the OWNER table named Garden Glory Owner Data Form, and a data input form for the EMPLOYEE table named Garden Glory Employee Data Form.
Z04_KROE8149_09_SE_EXTA.indd 136 04/07/19 8:16 PM
Online Extension A Working with MySQL A-137
“Working with MySQL” in that order, and apply what you learn to The Queen Anne Curi- osity Shop project questions in “Advanced SQL.”
A. In the Chapter 3 project questions for The Queen Anne Curiosity Shop, you created the QACS database. Document your QACS database by creating a QACS database design in MySQL Workbench.
B. Create a user login named QACS-User, with the password QACS-User+password. Do not assign an administrative role to this user, but do assign all schema permissions except GRANT to the QACS database.
C. Create an ODBC system data source name (DSN) named QACS to the QACS data- base using the QACS-User account.
D. Create a new Microsoft Access 2019 database named QACSIS.accdb, and link it to the tables in the MySQL QACS database using the QACS ODBC DSN. Create a data input form for the CUSTOMER table named The Queen Anne Curiosity Shop Customer Data Form, and a data input form for the EMPLOYEE table named The Queen Anne Curiosity Shop Employee Data Form.
Z04_KROE8149_09_SE_EXTA.indd 137 04/07/19 8:16 PM
