Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
© 2018 Pearson Education, Inc. Page 14 of 25
IMPORTANT: If you are using the Microsoft Windows operating system, download and use the
MySQL Installer for Windows! Do not download separate MySQL components for the Windows
OS.
The MySQL Home page is: http://dev.mysql.com/. The MySQL Installer for Windows is found at:
http://dev.mysql.com/downloads/installer/5.7.html
C.28. If you haven’t already done so, work through the steps described in this appendix to
create and populate the WP database.
C.29. Use MySQL and the MySQL Workbench to create and run the following SQL queries in
Chapter 3, starting on page 160.
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
C.30. Use MySQL and the MySQL Workbench to run one or more of the saved SQL queries
you created in question C.29:
C.31. Complete Exercise 3.59 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-AWE-3-1-A.sql.)
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 Selected
SQL Statement in
Connected
Server button
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
Use the script file:
C.32. Complete Exercise 3.60 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-AWE-3-3-E.sql.
C.33. If you have not already done so, import the COMPUTER table from Microsoft Excel into
the MySQL WP database as explained in the text.
C.34. Import the COMPUTER_ASSIGNMENT table from Microsoft Excel into the MySQL WP
database. Column characteristics are shown in Figure C-69 (this table shows the column
characteristics with Microsoft Access 2016 data types). How should this table be linked
to the EMPLOYEE table and the COMPUTER table by foreign keys? Be sure to include
these foreign keys in your final COMPUTER_ASSIGNMENT table structure when you
create it in MySQL.
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
• Double-click the WP database schema from the list in the MySQL for Excel pane, then
click on the Export Excel Data to New Table button. Enter the column characteristics in
the Export Data – COMPUTER_ASSIGNMENT [A1:C31] screen as shown below:
Select each tab in turn representing each attribute, and use these attributes:
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
What referential integrity constraints should we use? For EMPLOYEE, EmployeeNumber is a
surrogate key and is never updated. Further, WP never drops employee records because of
ALTER TABLE COMPUTER_ASSIGNMENT
ADD CONSTRAINT C_A_EMP_FK FOREIGN KEY(EmployeeNumber)
REFERENCES EMPLOYEE(EmployeeNumber)
ON UPDATE NO ACTION
C.35. If you have not already done so, create the WP-User user account and associated
permissions in the SQL Server 2016 WP database as explained in the text.
C.36. Create a user account in the MySQL WP database named WP-Reader. Give this user
SQL Server authentication with the password of WP-Reader+password and with other
password settings to match those shown in Figure C-36. Give WP-Reader only SELECT
object rights to the WP database.
• Login to MySQL workbench and click the Server | Users and Privileges command.
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
© 2018 Pearson Education, Inc. Page 19 of 25
C.37. Create a Microsoft Access 2016 database named WPIS_RO.accdb where RO stands
for “read-only.” This database will be a read-only application for the MySQL WP
A. Open Access, and create a new Blank database named WPIS-RO.accdb. Enable the content. Click
the File command tab, and then click the Options button. The Access Options dialog box is
displayed.
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
2. Link the WPIS_RO.accdb database to the MySQL WP database. When you
create your Machine User Data Source DSN, name the DSN WP_RO, and
use the WP-Reader user account (as detailed in Exercise C.36) for MySQL
authentication.
B. In the Select Data Source dialog box, select the Machine Data Source tab, and click the
C. The User Data Source radio button will be selected in the Create New Data Source
dialog box. Click the Next button. The driver selection dialog box appears. Scroll down
until you find MySQL ODBC 5.3 ANSI Driver, click on it, then click on the Next button. A
confirmation box will appear. Click the Finish button.
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
D. In the Connection Parameters dialog box, enter the data source name WP-RO, the user-
id WP-Reader, the password WP-Reader+password, and select the wp database
schema from the drop-down list. Click OK. Back at the Select Data Source dialog box,
select WP-RO then the OK button.
E. Lastly the Link Tables dialog box appears, allowing us to select the tables we want linked
3. Import all existing tables (including the COMPUTER and
COMPUTER_ASSIGNMENT tables if they have been imported as detailed in
Exercises C.28 and C.29).
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
4. Create a form to show all the data in PROJECT table named WP Projects Form.
5. Create a report to show all the data in the PROJECT table named WP Projects
Report.
With the PROJECT table selected in the Object Navigator pane, use the Create | Report
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
C.38. The database design for the Wallingford Motor Customer Relationship Management
database is shown in Figure AW-5-1. In the MySQL Workbench, create a new MySQL
Database Model named WMCRM-Database-Design. In this model, add an E-R diagram,
and recreate the database design shown in Figure AW-5-1 in this diagram.
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
C.39. The database design for Heather Sweeney Designs is shown in Figure 5-27. In the
MySQL Workbench, create a new MySQL Database Model named HSD-Database-
Design. In this model, add an E-R diagram, and recreate the database design shown in
Figure 5-27 in this diagram.
Appendix C – Getting Started with MySQL 5.7 Community Server Edition
© 2018 Pearson Education, Inc. Page 25 of 25
