Creating and Configuring a SNOMED CT Connection

Open MySQL Workbench Application

The following dialog should be displayed.

SQL Practical Guide > A.5 Using MySQL Workbench to Query SNOMED CT > image2019-9-9_10-40-16.png

Create a SNOMEDCT Connection

Click the spanner symbol to the right of the MySQL Connections prompt.

When the dialog below opens:

Select the default MySQL connection
Then click the Duplicate button

SQL Practical Guide > A.5 Using MySQL Workbench to Query SNOMED CT > image2019-9-9_10-53-55.png

With the newly created connection selected:

Change the Connection Name to SNOMEDCT
Enter a Default Schema name as snomedct (i.e. the name of the newly created database)

SQL Practical Guide > A.5 Using MySQL Workbench to Query SNOMED CT > image2019-9-9_11-0-26.png

Click the button.
If the connection is successful you will see the following dialog.

In future when you open MySQL Workbench you will see the option to open the SNOMEDCT connection.

SQL Practical Guide > A.5 Using MySQL Workbench to Query SNOMED CT > image2019-9-9_11-3-2.png

Save Your MySQL Password in Keychain

To avoid future prompts for the MySQL Password you can save the password in the Keychain.

Click the button.
Enter the MySQL password to be stored.

Testing and Using the SNOMED CT Database

Open the SNOMEDCT Connection

Open MySQL Workbench
Select the SNOMED CT Connection

SQL Practical Guide > A.5 Using MySQL Workbench to Query SNOMED CT > image2019-9-9_11-3-2.png

Test the SNOMED CT Database

To test the loaded SNOMED CT database open one of the example queries in the SnomedCtRfsMySql/mysql_examples folder.

Click open SQL button in the toolbar in the toolbar
Find the mysql_examples folder in the SnomedCtRfsMySql subfolder of your home directory.
Select and open one of the queries

SQL Practical Guide > A.5 Using MySQL Workbench to Query SNOMED CT > image2019-9-9_11-21-3.png

The query should be display as shown below

SQL Practical Guide > A.5 Using MySQL Workbench to Query SNOMED CT > image2019-9-9_11-22-58.png

Click the lightning icon to run the query

The results of running the query should be displayed as below

SQL Practical Guide > A.5 Using MySQL Workbench to Query SNOMED CT > image2019-9-9_11-25-54.png

Using the SNOMED CT Database

The SNOMED CT database can be used in different ways:

Running example SQL queries in the SnomedRfsMySql package.
Running queries provided as part of a SNOMED CT E-Learning assignment.
Using example SQL queries as templates for your own queries.
Running SQL queries you have written yourself from scratch.

You can run your SQL queries in MySQL Workbench as described in the previous section. Additionally these queries can be run using the MySQL command line interface or through a MySQL connector for one of the programming languages listed at: https://dev.mysql.com/downloads/.

If you are using example queries as templates for your own queries alway copy the query first so that you do not overwrite the original example query.

If you accidentally overwrite an example query, you can download the SnomedRfsMySql.zip file again and extract the example queries folder.

Managing MySQL Accounts

The "root" user account is typically used for importing the SNOMED CT release files. However, you should create additional MySQL accounts for database users that do not have administrator rights. Ideally the name of these MySQL accounts should match the Mac username of the user as this simplifies call the mysql command line tool and the use of MySQL connectors.

To create user accounts in the MySQL Workbench select the Administration tab (see below) and select Users and Privileges (see below).

Set up the account details and access permissions.

In this example an account with the username newuser account is being created as shown below:
In the schema privileges the newuser is given access to the snomedct database schema:
Within the snomedct database the rights of this newuser are limited to the following actions.