Database Storage & Design Appendix C Database Concepts Edition David Kroenke David Auer Scott Vandenberg Robert Yoder Instructors

Unlock access to all the studying documents.

View Full Document

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

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

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

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

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?

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.