Database Storage & Design Appendix C Appendix Getting Started With Mysql Community Server Edition Important You Are Using

Unlock access to all the studying documents.

View Full Document

Appendix C – Getting Started with MySQL 5.7 Community Server Edition

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

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