Unified Medical Language System^® (UMLS^®)

Home > Biomedical Research & Informatics > UMLS > Implementation Resources > Scripts

Semantic Network MySQL Load Script

This document is a guide to loading the UMLS Semantic Network into a MySQL database by using the scripts and files created by MetamorphoSys. To generate the necessary files and scripts, select the "Semantic Network" checkbox on the "Install UMLS" screen. Also, select the "MySQL" option from the "Choose Database Load Scripts" pick-list on the "Install UMLS" screen.

Recommended Software Version: MySQL Server 5.5

Creating a Database

In order to load the Semantic Network into a MySQL database, you will have to either use an existing database or create a new one. Two important considerations when creating a MySQL database are the default character set and collation settings. We recommend using UTF8 and the utf8_unicode_ci collation setting. For example:

CREATE DATABASE IF NOT EXISTS umls CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Configuring and Running the Load Script

Windows

If you are running MetamorphoSys in a Windows environment, in your output directory (after running MetamorphoSys with the "Semantic Network" box checked and the MySQL database selected) you should see a batch file script called "populate_net_mysql_db.bat" This script can be used to load the Semantic Network into a MySQL database. The directory will also contain an SQL script for creating the tables.

Edit the script and configure the four parameters at the top of the script. These are:

set MYSQL_HOME=<path to MYSQL_HOME>
set user=<username>
set password=<password>
set db_name=<db_name>

MYSQL_HOME should be set to the installation directory of your local MySQL server.  This is likely to be something like "C:\Program Files\MySQL\MySQL Server 5.5". Remember to enclose the value in quotes if the directory name contains spaces. After configuring these four parameters, this section of your script may look something like the following:

set MYSQL_HOME="C:\Program Files\MySQL\MySQL Server 5.5"
set user=myuser
set password=p4ssw0rd
set db_name=umls

You are now ready to run the script. To do so, navigate to the directory containing the Semantic Network files using the Windows explorer and double-click on the "populate_net_mysql_db.bat" file you just finished editing.

Here are some things to keep in mind when using this script:

1. The script is written to expect that there is a local MySQL Server installation and that you plan to load the Semantic Network into a database being managed by this local server. Before getting this far, you should have already installed and configured your MySQL Server. For more information on that, you are encouraged to visit http://www.mysql.com.
2. If your database is configured without a password you can leave the password setting blank, but you will have to update the script "populate_net_mysql_db.bat" and remove all references to the "-p%password%" parameter. Thus, a line like this:
   
   %MYSQL_HOME%\bin\mysql -u %user% -p%password% --local-infile=1 %db_name% < mysql_net_tables.sql >> mysql_net.log 2>&1
   
   Must be changed to this
   
   %MYSQL_HOME%\bin\mysql -u %user% --local-infile=1 %db_name% < mysql_net_tables.sql >> mysql_net.log 2>&1
3. The script will automatically create a log file called "mysql_net.log" in the same directory as the script. If you are planning on running the script more than once, you should consider renaming the log file before your second run, otherwise the contents of the first run will be lost.
4. While the script is running, you can monitor the process from within the MySQL command shell by using "mysql> show processlist;"
5. When the load completes, each of the tables will be loaded.

Linux, Macintosh, or Solaris

If you are running MetamorphoSys in a Linux, Macintosh, or Solaris environment, in your output directory (after running MetamorphoSys with the "Semantic Network" box checked and the Mysql database selected) you should see a .sh shell script called "populate_net_mysql_db.sh". This script can be used to load the Semantic Network into a MySQL database. The directory will also contain an SQL script for creating the tables.

Edit the script and configure the four parameters at the top of the script. These are:

MYSQL_HOME=<path to MYSQL_HOME>
user=<username>
password=<password>
db_name=<db_name>

MYSQL_HOME should be set to the installation directory of your local MySQL server. Another way to think of this is that the setting of MYSQL_HOME should contain a bin/ directory that contains the "mysql" command. This may even be something like "/usr". After configuring these four parameters, this section of your script may look something like the following:

MYSQL_HOME=/usr
user=myuser
password=p4ssw0rd
db_name=umls

You are now ready to run the script. To do so, navigate to the directory of your Semantic Network files and invoke the script from the command line. It may be necessary to change the file permissions to make the script executable before it can be started. For example:

% cd <directory with Semantic Network files>
% chmod 775 populate_net_mysql_db.sh
% populate_net_mysql_db.sh &

Here are some things to keep in mind when using this script:

1. The script is written to expect that there is a local MySQL Server installation and that you plan to load the Semantic Network into a database being managed by this local server. Before getting this far, you should have already installed and configured your MySQL Server. For more information on that, you are encouraged to visit http://www.mysql.com.
2. If your database is configured without a password you can leave the password setting blank, but you will have to update the script "populate_net_mysql_db.bat" and remove all references to the "-p%password%" parameter. Thus, a line like this:
   
   $MYSQL_HOME/bin/mysql -u $user -p$password $db_name < mysql_net_tables.sql >> mysql_net.log 2>&1
   
   Must be changed to this
   
   $MYSQL_HOME/bin/mysql -u $user $db_name < mysql_net_tables.sql >> mysql_net.log 2>&1
3. The script will automatically create a log file called "mysql_net.log" in the same directory as the script.  If you are planning on running the script more than once, you should consider renaming the log file before your second run, otherwise the contents of the first run will be lost.
4. While the script is running, you can monitor the process from within the MySQL command shell by using "mysql> show processlist;"
5. When the load completes, each of the tables will be loaded.

Sample Queries

We have provided a number of sample queries to demonstrate how to access certain kinds of data within the Semantic Network.

Semantic Type Queries

Get a semantic type by value.
SELECT * FROM SRDEF
WHERE sty_rl = ? AND rt = 'STY';

Get a semantic type by TUI.
SELECT * FROM SRDEF
WHERE ui = ? AND rt = 'STY';

Get parents of a semantic type by value.
SELECT b.* FROM SRSTR a, SRDEF b
WHERE sty_rl1 = ?
     AND rl = 'isa' AND b.rt = 'STY' AND a.sty_rl2 = b.sty_rl;

Get parents of a semantic type by TUI.
SELECT b.* FROM SRSTR a, SRDEF b, SRDEF c
WHERE c.ui = ?
     AND a.sty_rl1 = c.sty_rl AND rl = 'isa'
     AND b.rt = 'STY' AND a.sty_rl2 = b.sty_rl
     AND c.rt = 'STY';

Get children of a semantic type by value.
SELECT b.* FROM SRSTR a, SRDEF b
WHERE sty_rl2 = ?
     AND rl = 'isa' AND b.rt = 'STY' AND a.sty_rl1 = b.sty_rl;

Get children of a semantic type by TUI.
SELECT b.* FROM SRSTR a, SRDEF b, SRDEF c
WHERE c.ui = ?
     AND a.sty_rl2 = c.sty_rl AND rl = 'isa'
     AND b.rt = 'STY' AND a.sty_rl1 = b.sty_rl
     AND c.rt = 'STY';

Get siblings of a semantic type by value.
SELECT c.* FROM SRSTR a, SRSTR b, SRDEF c
WHERE a.sty_rl1 = ?
     AND a.rl = 'isa' AND b.rl = 'isa'
     AND a.sty_rl2 = b.sty_rl2 AND a.sty_rl1 != b.sty_rl1
     AND c.rt = 'STY' AND c.sty_rl = b.sty_rl1;

Get siblings of a semantic type by TUI.
SELECT c.* FROM SRSTR a, SRSTR b, SRDEF c, SRDEF d
WHERE d.ui = ?
     AND a.rl = 'isa' AND b.rl = 'isa'
     AND a.sty_rl2 = b.sty_rl2 AND a.sty_rl1 != b.sty_rl1
     AND c.rt = 'STY' AND b.sty_rl1 = c.sty_rl
     AND d.rt = 'STY' AND a.sty_rl1 = d.sty_rl;

Get ancestors of a semantic type by value.
SELECT b.* FROM SRSTRE2 a, SRDEF b
WHERE sty1 = ?
     AND rl = 'isa' AND b.rt = 'STY' AND a.sty2 = b.sty_rl
ORDER BY stn_rtn;

Get ancestors of a semantic type by TUI.
SELECT b.* FROM SRSTRE1 a, SRDEF b
WHERE ui1 = ?
     AND b.rt = 'STY' AND a.ui3 = b.ui
     AND a.ui2 IN (SELECT ui FROM srdef WHERE rt= 'RL' and sty_rl = 'isa')
ORDER BY stn_rtn;

Get descendants of a semantic type by value.
SELECT b.* FROM SRSTRE2 a, SRDEF b
WHERE sty2 = ?
     AND rl = 'isa' AND b.rt = 'STY' AND a.sty1 = b.sty_rl
ORDER BY stn_rtn;

Get descendants of a semantic type by TUI.
SELECT b.* FROM SRSTRE1 a, SRDEF b
WHERE ui3 = ?
     AND b.rt = 'STY' AND a.ui1 = b.ui
     AND a.ui2 IN (SELECT ui FROM srdef WHERE rt= 'RL' and sty_rl = 'isa')
ORDER BY stn_rtn;

Get defined relationships for a semantic type by value.
SELECT a.rl, b.ui rl_ui, sty_rl2 sty, c.ui sty_ui FROM SRSTR a, SRDEF b, SRDEF c
WHERE sty_rl1 = ?
     AND ls = 'D' AND a.rl = b.sty_rl AND b.rt = 'RL'
     AND a.sty_rl2 = c.sty_rl AND c.rt = 'STY';

Get defined relationships for a semantic type by TUI.
SELECT a.rl, b.ui rl_ui, sty_rl2 sty, c.ui sty_ui FROM SRSTR a, SRDEF b, SRDEF c, SRDEF d
WHERE d.ui = ?
     AND ls = 'D' AND a.rl = b.sty_rl AND b.rt = 'RL'
     AND a.sty_rl2 = c.sty_rl AND c.rt = 'STY'
     AND a.sty_rl1 = d.sty_rl AND d.rt = 'STY';

Get all relationships for a semantic type by value (including inherited relationships).
SELECT a.rl, b.ui rl_ui, sty2 sty, c.ui sty_ui FROM SRSTRE2 a, SRDEF b, SRDEF c
WHERE sty1 = ?
     AND a.rl = b.sty_rl AND b.rt = 'RL'
     AND a.sty2 = c.sty_rl AND c.rt = 'STY';

Get all relationships for a semantic type by TUI (including inherited relationships).
SELECT a.rl, b.ui rl_ui, sty2 sty, c.ui sty_ui FROM SRSTRE2 a, SRDEF b, SRDEF c, SRDEF d
WHERE d.ui = ?
     AND a.rl = b.sty_rl AND b.rt = 'RL'
     AND a.sty2 = c.sty_rl AND c.rt = 'STY'
     AND a.sty1 = d.sty_rl AND c.rt = 'STY';

Get all relationships from one semantic type to another by value.
SELECT a.rl, b.ui FROM SRSTRE2 a, SRDEF b
WHERE sty1 = ? AND sty2 = ?
     AND a.rl = b.sty_rl AND b.rt = 'RL';

Get all relationships from one semantic type to another by TUI.
SELECT a.rl, b.ui FROM SRSTRE2 a, SRDEF b, SRDEF c, SRDEF d
WHERE c.ui = ? AND d.ui = ?
     AND a.rl = b.sty_rl AND b.rt = 'RL'
     AND a.sty1 = c.sty_rl AND c.rt = 'STY'
     AND a.sty2 = d.sty_rl AND d.rt = 'STY';

Semantic Network Relation Label Queries

NOTE: Semantic Network relation labels only have “isa” relationships to other labels.

Get a Semantic Network relation label by value.
SELECT * FROM SRDEF
WHERE sty_rl = ? AND rt = 'RL';

Get a Semantic Network relation label by TUI.
SELECT * FROM SRDEF
WHERE ui = ? AND rt = 'RL';

Get parents of a Semantic Network relation label by value.
SELECT b.* FROM SRSTR a, SRDEF b
WHERE sty_rl1 = ?
     AND rl = 'isa' AND b.rt = 'RL' AND a.sty_rl2 = b.sty_rl;

Get parents of a Semantic Network relation label by TUI.
SELECT b.* FROM SRSTR a, SRDEF b, SRDEF c
WHERE c.ui = ?
     AND a.sty_rl1 = c.sty_rl AND rl = 'isa'
     AND b.rt = 'RL' AND a.sty_rl2 = b.sty_rl
     AND c.rt = 'RL';

Get children of a Semantic Network relation label by value.
SELECT b.* FROM SRSTR a, SRDEF b
WHERE sty_rl2 = ?
     AND rl = 'isa' AND b.rt = 'RL' AND a.sty_rl1 = b.sty_rl;

Get children of a Semantic Network relation label by TUI.
SELECT b.* FROM SRSTR a, SRDEF b, SRDEF c
WHERE c.ui = ?
     AND a.sty_rl2 = c.sty_rl AND rl = 'isa'
     AND b.rt = 'RL' AND a.sty_rl1 = b.sty_rl
     AND c.rt = 'RL';

Get siblings of a Semantic Network relation label by value.
SELECT c.* FROM SRSTR a, SRSTR b, SRDEF c
WHERE a.sty_rl1 = ?
     AND a.rl = 'isa' AND b.rl = 'isa'
     AND a.sty_rl2 = b.sty_rl2 AND a.sty_rl1 != b.sty_rl1
     AND c.rt = 'RL' AND c.sty_rl = b.sty_rl1;

Get siblings of a Semantic Network relation label by TUI.
SELECT c.* FROM SRSTR a, SRSTR b, SRDEF c, SRDEF d
WHERE d.ui = ?
     AND a.rl = 'isa' AND b.rl = 'isa'
     AND a.sty_rl2 = b.sty_rl2 AND a.sty_rl1 != b.sty_rl1
     AND c.rt = 'RL' AND b.sty_rl1 = c.sty_rl
     AND d.rt = 'RL' AND a.sty_rl1 = d.sty_rl;

Get ancestors of a Semantic Network relation label by value.
SELECT b.* FROM SRSTRE2 a, SRDEF b
WHERE sty1 = ?
     AND rl = 'isa' AND b.rt = 'RL' AND a.sty2 = b.sty_rl
ORDER BY stn_rtn;

Get ancestors of a Semantic Network relation label by TUI.
SELECT b.* FROM SRSTRE1 a, SRDEF b
     WHERE ui1 = ?
     AND b.rt = 'RL' AND a.ui3 = b.ui
     AND a.ui2 IN (SELECT ui FROM srdef WHERE rt= 'RL' and sty_rl = 'isa')
ORDER BY stn_rtn;

Get descendants of a Semantic Network relation label by value.
SELECT b.* FROM SRSTRE2 a, SRDEF b
WHERE sty2 = ?
     AND rl = 'isa' AND b.rt = 'RL' AND a.sty1 = b.sty_rl
ORDER BY stn_rtn;

Get descendants of a Semantic Betwork relation label by TUI.
SELECT b.* FROM SRSTRE1 a, SRDEF b
WHERE ui3 = ?
     AND b.rt = 'RL' AND a.ui1 = b.ui
     AND a.ui2 IN (SELECT ui FROM srdef WHERE rt= 'RL' and sty_rl = 'isa')
ORDER BY stn_rtn;

Copyright, Privacy, Accessibility, Site Map, Viewers and Players
U.S. National Library of Medicine, 8600 Rockville Pike, Bethesda, MD 20894
National Institutes of Health, Health & Human Services
Freedom of Information Act, Contact Us

Last reviewed: 27 April 2012
Last updated: 12 April 2011
First published: 12 April 2011
Metadata| Permanence level: Permanent: Dynamic Content