Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Database Concepts
8th Edition
David M. Kroenke • David J. Auer • Scott L. Vandenberg • Robert C. Yoder
Instructor’s Manual
Prepared by Robert C. Yoder and David J. Auer
Appendix C
Getting Started with MySQL 5.7 Community Server
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted,
in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior
written permission of the publisher. Printed in the United States of America.
Instructor’s Manual to accompany:
Database Concepts (8th Edition)
David M. Kroenke • David J. Auer • Scott L. Vandenberg • Robert C. Yoder
Appendix A - Getting Started with Microsoft SQL Server 2016
CHAPTER OBJECTIVES
• Learn how to install MySQL 5.7 Community Server
• Learn how to install MySQL Workbench
• Learn how to install the MySQL ODBC/Connector
• Learn how to create a database in MySQL 5.7 Community Server Edition
• 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 use Microsoft Access as an application development platform
• Learn how to create database designs in the MySQL Workbench version 6.3.8
CHAPTER ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
be reported and corrected in the online DBC e08 Errata document, which will be
available at http://www.pearsonhighered.com/kroenke.
THE ACCESS WORKBENCH
Solutions to the Access Workbench exercises may be found in Solutions to all Sections:
The Access Workbench, which is a separate document within the Instructor’s Manual.
There is no section of The Access Workbench associated with this appendix.
NOTES ON MICROSOFT WINDOWS 10
This book uses the Microsoft Windows 10 operating system as the basis for screenshots
and step-by-step instructions. However, with Windows 10, Microsoft has introduced a
continuous update system that has already resulted in some fundamental differences in
how different versions of Windows 10 look and operate.
For example, in the original version of Microsoft Windows 10, clicking the Windows
Start button (or pressing the Windows key on the keyboard) displayed the menu shown
in Figure 1. In this menu, we need to click the All apps button in order to see the All
apps menu shown in Figure 2.
Appendix A - Getting Started with Microsoft SQL Server 2016
© 2018 Pearson Education, Inc. Page 4 of 25
Figure 1 – Windows 10 Main Menu
Figure 2 – Windows 10 All Apps Menu
The All apps button
The File Explorer button
The File Explorer icon
The All apps menu
Appendix A - Getting Started with Microsoft SQL Server 2016
Figure 3 – Windows 10 Anniversary Update Main Menu with All Apps Menu Included
Microsoft then released the Windows 10 Anniversary Update (Feature update to
Windows 10, version 1607) (see the blog discussion at
https://blogs.windows.com/windowsexperience/2016/08/02/how-to-get-the-windows-10-
anniversary-update/#K1CZuiw4auiuE9A5.97 ). One of the changes introduced in the
Anniversary Update was a major change to the menu system. Now, as shown in
Figure 3, the All apps menu is immediately available when the Start button is used (or
the keyboard Windows key is pressed).
Therefore, note that the step by step instructions in this book may need to be altered for
your use depending upon which version of Microsoft Windows 10 you or your students
are using!
We recommend that you update Windows 10 to the Windows 10 Anniversary Update
(Feature update to Windows 10, version 1607), and make sure it is patched with all
updates to that version (at a minimum patched to Windows 10 Version 1607 update for
August 23, 2016 (KB3176936), and the Windows 10 Version 1607 cumulative update for
September 29, 2016 (KB3194496). We also recommend using the 32-bit version of
Microsoft Office. This insures that all the examples discussed in this book will function
properly.
The All apps menu
The File Explorer button
The File Explorer icon
Appendix A - Getting Started with Microsoft SQL Server 2016
TEACHING SUGGESTIONS
• When you are using MySQL 5.7, the best text editor to use is the text editor built into
the MySQL Workbench. Take some time to show you students how to use it.
• Make sure that your students work through Appendix C in conjunction with other
material presented in the text by working through Appendix C in the following
sequence:
❖ Before starting Chapter 3 on SQL, install MySQL 5.7 and the MySQL Workbench
by working through this appendix up to and including “How Do I Create a
Workspace for the MySQL Workbench Files?”
❖ When studying the Chapter 3 sections on how to create and populate database
tables, work up to and including “SQL Statements to Insert Database Data.” Both
Chapter 3 and this appendix use the same WP database, and this work will show
you how to create your own copy of the WP database.
❖ When studying the Chapter 3 sections on how to use SQL Data Manipulation
Language (DML) and SQL Data Definition Language (DDL), work through the
section named “How Do I Work with SQL Queries in MySQL?” Both Chapter 3
and this appendix use the same WP database, and you can run the SQL
Statements shown in Chapter 3 yourself, and see the results.
❖ When studying the Appendix E material on SQL Views, you can run the SQL
Statements shown in Appendix E yourself and see the results.
❖ When studying the Appendix E material on SQL Persistent Stored Modules
(SQL/PSM), you can run the SQL Statements shown in Appendix E yourself and
see the results.
❖ Work through the section “How Do I Import Microsoft Excel Data into a MySQL
5.7 Database Table?” in this appendix to understand how to import Microsoft
Excel data in a database table.
❖ Work up to and through the section “How Do I Create an ODBC Connection from
Microsoft Access 2016 to a MySQL 5.7 Database?” in this appendix to
understand how to use Microsoft Access as a development environment for a
MySQL Server database.
❖ When studying Chapter 5 on database designs, work through the remaining
sections starting with “How Do I Create Database Designs in the MySQL
Workbench”.
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
ANSWERS TO REVIEW QUESTIONS
C.1. What is MySQL?
C.2. What is the primary advantage of using MySQL instead of SQL Server 2016?
C.3. What are the four MySQL programs that are recommended as a useful set of MySQL
software products?
1. MySQL 5.7 Server Community Server (download the appropriate version for your
2. The MySQL Workbench (as of this writing, version 6.3.9) is the graphical SQL utility for
MySQL. MySQL is a text command line-oriented program, and the MySQL Workbench
4. MySQL for Excel provides an easy way to import data from Excel into a MySQL database
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
C.4. What are two purposes of the MySQL Workbench?
C.5. What is a MySQL schema?
C.6. How do you create a new database in MySQL?
Creating a MySQL database:
C.7. What is a MySQL default schema? How do you specify the default schema?
Setting the Default Schema:
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
© 2018 Pearson Education, Inc. Page 9 of 25
4. The wp schema is set as the active schema (default schema), and the wp schema object
is displayed in bold text in the Navigator pane to indicate that it is the active schema.
C.8. What is an SQL script? What types of SQL statements and commands can be run more
efficiently as scripts?
C.9. What tool(s) can be used to create an SQL script?
C.10. What file extension should you use with MySQL scripts?
C.11. How do you create, save, and run an SQL script in MySQL?
To open a script in MySQL Workbench, use the File | Open SQL Script . . . menu command.
C.12. How does MySQL use the AUTO_INCREMENT keyword? What limitations are there on
the use of the AUTO_INCREMENT keyword?
C.13. How do you create and run an SQL query in MySQL?
SQL Queries are entered into the SQL Editor SQL Query tabbed window (which, as the name
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
By the way, there is also an Execute the statement under the keyboard cursor button, which
allows you to run just one command in a script at a time. See the screenshot below.
C.14. How do you obtain online help when using the MySQL Workbench? Where is the help
information displayed?
C.15. What is the purpose of the MySQL Connector/OBDC? How do you install it, and where
does it appear after it is installed?
The Execute the
statement under
the keyboard
cursor button
This is the selected
query and only it
will be run when
you click the
Execute the
statement under
the keyboard
cursor button
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
© 2018 Pearson Education, Inc. Page 11 of 25
The MySQL Connector/OBDC installs MySQL ODBC support for using Microsoft Access as an
application development platform for MySQL, and with Web sites as described in Chapter 7. Use
the MySQL installer or download the MySQL Connector/ODBC from the MySQL Website at
http://dev.mysql.com/downloads/connector/odbc/, and run the installation routine. As soon as
the installation is complete, the MySQL ODBC 5.3.7 Driver will be available in the ODBC Data
Source Administrator. Be sure to use the correct driver for the version of Microsoft Access you
are using (32- or 64-bit).
C.16. How do you import Microsoft Excel 2016 data into a MySQL 5.7 table?
C.17. How do you create user accounts in MySQL 5.7? How do you give them appropriate
permissions to a specific database?
Login to MySQL workbench and click the Server | Users and Privileges command. Select the
C.18. Why would you want to create an ODBC connection to link a Microsoft Access 2016 to a
MySQL 5.7 Database?
There are two major reasons. In many work environments, people will be connecting to a
C.19. What is an ODBC DSN? Why is one needed?
A DSN is a Data Source Name. It is used to connect Access to a specific MySQL schema with a
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
C.20. How do you create an ODBC connection to link a Microsoft Access 2016 database to a
MySQL 5.7 Database?
Open Access, and select a new or existing database that we want to link the MySQL tables to.
In the Select Data Source dialog box, select the Machine Data Source tab, and click the New
button. You may get a warning message but just click OK. The User Data Source radio button
C.21. The MySQL Workbench creates “database models.” What is a data model? What is a
database design? Which does MySQL Workbench create?
A data model is a logical or conceptual view of the database. A database design defines the
database characteristics that will be implemented in the actual database. As we discuss in
C.22. How do you create a database model in MySQL Workbench?
To create a new data model in the MySQL Workbench, you can:
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
C.23. How do you create a new E-R diagram in the MySQL Workbench?
C.24. How do you create a table in an E-R diagram?
Creating a Table in the MySQL E-R (EER) Diagram:
C.25. How do you create a 1:N non-identifying relationship in an E-R diagram?
Creating a 1:N Non-identifying Relationship Between Two Tables:
1. Click the Place a Relationship Using Existing Columns.
2. MySQL Workbench displays a Foreign Key Columns dialog box instructing us to Pick one
C.26. How does the MySQL Workbench handle N:M relationships?
ANSWERS TO EXERCISES
C.27. If you haven’t already done so, download and install MySQL 5.7 Community Server
Edition and the MySQL Workbench. Use the default settings for each installation. When
you are asked for a password for the Root user account, create one.
