UMLS Database Query Diagrams:
How to perform searches in the UMLS history files (MRAUI.RRF and MRCUI.RRF)

This diagram shows how to perform searches in the UMLS history files (MRAUI.RRF and MRCUI.RRF).

As of 2008AB, the atom history file (MRAUI.RRF) contains only information about atoms that moved from one CUI to another when compared in adjacent releases. A search of the AUI value in the AUI1 field of MRAUI.RRF will yield rows indicating movements of that atom from one CUI to another and the release in which that move took place.

The concept history file (MRCUI.RRF) contains information about deletions, merges, and bequeathals of CUIs that are no longer in the UMLS. Every CUI from past UMLS releases should be either in the current MRCONSO.RRF, or in the MRCUI.RRF file. In this way, the current state of any CUI should be knowable.

A search for a CUI value in the CUI1 field will yield information about the history of that CUI.

- A REL = 'DEL' record indicates that the CUI no longer exists (it was deleted) and no suitable mapping for it was found.
- A REL = 'SY' record indicates that the CUI was merged into another CUI (the CUI2). The CUI2 will ALWAYS be a CUI in MRCONSO.RRF.
- A REL in (RB, RN, RO) indicates that a CUI no longer exists, but has been mapped (in a non-synonymous way) to another CUI (the CUI2). The CUI2 will ALWAYS be a CUI in MRCONSO.RRF.

Splits are not explicitly represented in the concept history file as the semantics of this file require that CUI1 is always dead and CUI2 is always alive (or null). Thus, a little work is needed to determine an explicit split. Such changes can be found by identifying atom movements from a CUI that still exists into a CUI that is now considered "new." A new concept can be identified by having an MR attribute value of '00000000'.

Corresponding Oracle Queries:

1. Determine if an atom moved from one concept to another from 2006AD to 2007AA.

SELECT decode(count(*),1,'Y','N') FROM mraui
WHERE aui1 = 'A10861823'
    AND ver = '2007AA'
    AND mapreason = 'move';

2. Find target CUI of a merge from 2006AD to 2007AA. There will be at most exactly one row returned. If no rows are returned, then this CUI was not merged during that editing cycle.

SELECT cui2 as target_cui FROM mrcui
WHERE cui1 = 'C0107699'
    AND ver = '2006AD'
    AND rel = 'SY';

3. Find bequeathal CUIs of a CUI deleted from 2006AD to 2007AA. It is possible that a CUI being deleted was bequeathed to more than one current version CUI.

SELECT cui2 as bequeathal_cui FROM mrcui
WHERE cui1 = 'C0128545'
    AND ver = '2006AD'
    AND rel in ('RO','RB','RN');

4. Determine whether a CUI was "deleted" from 2006AD to 2007AB. This means that the CUI is no longer in the release and was not merged with another concept.

SELECT decode(count(*),1,'Y','N') FROM mrcui
WHERE cui1 = 'C0174735'
    AND ver = '2006AD'
    AND rel != 'SY';

5. Find all cases of pure merges between CUI1 -> CUI2 during 2006AD to 2007AA. A "pure merge" is a case where all of the atoms of CUI1 were moved into CUI2 and CUI1 was deleted.

SELECT cui1, cui2 FROM mrcui
WHERE ver = '2006AD'
    AND rel = 'SY';

6. Find all cases of pure splits from CUI1 -> CUI2 during 2006AD to 2007AA. A "pure split" is a case where some of the atoms of CUI1 were moved into a NEW concept and no atoms from other concepts were moved into that concept. In the case of a pure split, it is possible that entirely new atoms from source updates are also in CUI2.

SELECT DISTINCT a.cui1, a.cui2 FROM mraui a, mrsat b
WHERE ver = '2007AA'
    AND mapreason = 'move'
    AND a.cui2 = b.cui
    AND b.atn = 'MR'
    AND b.atv = '00000000'
    AND cui2 IN
        (SELECT cui2 FROM mraui c
        WHERE c.ver = '2007AA'
            AND c.mapreason = 'move'
        GROUP BY cui2 HAVING count(distinct cui1)=1);

7. Find all cases of atoms that moved from one concept to another that do not involve pure splits or pure merges. Note: for this result some of the CUI1 may be deleted and some may not be.

WITH merges AS
    (SELECT cui1, cui2 FROM mrcui
    WHERE ver = '2006AD'
        AND rel = 'SY'),
    splits AS
    (SELECT DISTINCT a.cui1, a.cui2 FROM mraui a, mrsat b
    WHERE ver = '2007AA'
        AND mapreason = 'move'
        AND a.cui2 = b.cui
        AND b.atn = 'MR'
        AND b.atv = '00000000'
        AND cui2 IN
            (SELECT cui2 FROM mraui c
            WHERE c.ver = '2007AA'
                AND c.mapreason = 'move'
    GROUP BY cui2 HAVING count(distinct cui1) = 1))
SELECT DISTINCT aui1 as aui, cui1, cui2 FROM mraui
WHERE ver = '2007AA'
    AND mapreason = 'move'
    AND (cui1, cui2) IN
        (SELECT cui1, cui2 FROM mraui
        MINUS
            (SELECT cui1, cui2 FROM merges
            UNION
            SELECT cui1, cui2 FROM splits)
    );

Last Reviewed: July 29, 2016