Search



Page tree

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

Open the MySQL Workbench Schema Tab

Open MySQL Workbench and then select the Schema Tab.

You should see something like the image below.

  • If you gave your database a name other than snomedct you will see the name you chose listed as a schema.

Make SNOMED CT the Default Database Schema 

If the name of you SNOMED CT database schema is displayed in bold this means it is the default database.

  • If your SNOMED CT database schema name is not shown in bold, double-click on the schema name. It will become the default and will be displayed in bold.

Tables in the Database

Expand the Tables Item under the SNOMED CT Database Schema name. This will reveal a list of table names.

All the listed table names have an initial prefix followed by an underscore character. The meaning of the prefixes used and the names and content of specific tables with these prefixes are summarized in the table below.

PrefixTables Using this PrefixTable NamesTable Content
full

One table with the full prefix is created for each component.

These tables are named full_[component-type] (e.g. full_concept, full_description, full_relationship)Each of these tables is populated with all the rows from the file (or files) representing this type component in the Full release subfolders.
One table with the full prefix is also created for each reference set type present in the release.The tables are named full_refset_[refset-type] (e.g. full_refset_Simple, full_refset_Language, full_refset_Association).Each of these tables is populated with all the rows from the file (or files) representing reference sets of this type in the Full release subfolders.
snap

One table with the snap prefix is created for each component.These tables are named snap_[component-type] (e.g. snap_concept, snap_description, snap_relationship)Each of these tables is populated with all the rows from the file (or files) representing this type component in the Snapshot release subfolders.
One table with the snap prefix is also created for each reference set type present in the release.The tables are named snap_refset_[refset-type] (e.g. snap_refset_Simple, snap_refset_Language, snap_refset_Association).Each of these tables is populated with all the rows from the file (or files) representing reference sets of this type in the Snapshot release subfolders.
Additional tables with snap prefix are created to represent the transitive closure and proximal primitive relationshipsThe table are named snap_transclose and snap_proximal_primitives.The snap_transclose table is populated with all the rows from the transitive closure files generated during the SnomedRfsMySql import process. The snap_proximal_primitives table is populated with promimal primitive relationships derived by processing the snap_transclose table.
config

Lookup and configuration files used by views and stored procedures.

  • See sections below in Views and Procedures for further information.


config_languageA table linking ISO language codes (e.g. en-US, en-GB, es) to the identifier of the relevant language reference set.
config_settings

A table storing configuration settings that determine:

  1. The language reference set used to select synonyms and fully specified names
  2. The effectiveTime of two configurable retrospective snapshot views (snap1 and snap2)
  3. The effectiveTime range for two configurable delta views (delta1 and delta2)
config_shortcuts

A table linking a short text keys to commonly used concept ids. This is used to facilate constraining searches to concepts within these hierarchies without requiring the query to specify the full SNOMED CT identifier for the concept.

This is currently only used by the procedures snap_search_plus, snap_search1_plus and snap2_search_plus. In future it may also be used to support procedures with a common requirment for

Views

SQL database views are in effect virtual tables. They can be queried in the same way as a table but they do not store data. The data that appears to be stored in a view is in fact defined by a stored query applied to the data stored in one or more tables.

The SNOMED CT import process creates two distinct types of views. Filtered views of a single table and composite views that bring together related data from different tables.

Filtered Table Views

The import process creates six distinct sets of table views. Five of these are applied to every Full release table. The naming conventions and characteristics of each of these filtered views summarized in the table below.

PrefixView NamesView Content
snap1snap1_[component-type] (e.g. snap1_concept, snap1_description)
snap1_refset_[refset-type] (e.g. snap1_refset_Simple)

These table views enable access to retrospective snapshots of the Full release data. The most recent version of every component in the table with an effectiveTime less than or equal to the snapshot date

When the database is imported the snapshot dates are set as follows:

  • snap1 views are set as a snapshot date 6 months before the current release
  • snap2 views are set as a snapshot date 12 months before the current release

These snapshot times can be changed by calling the stored procedure setSnapshotTime(viewNumber, dateTime).

For example, to set the snap1 date to 31 January 2017

  • CALL setSnapshotTime( 1,"20170131");

and to set the snap2 date to 1 May 2016

  • CALL setSnapshotTime( 2, "20160501");
snap2snap2_[component-type] (e.g. snap2_concept, snap2_description)
snap2_refset_[refset-type] (e.g. snap2_refset_Simple)
deltadelta_[component-type] (e.g. delta_concept, delta_description)
delta_refset_[refset-type] (e.g. delta_refset_Simple)
The delta table views enable access to delta views between any two dates. Only rows in the table with an effectiveTime greater than the start time and less that end time will be included in these views.

When the database is imported the delta date ranges are set as follows:

  • delta views are set with a start date 6 months before the current release and an end date matching the current release date (this matches the current Delta release file content).
  • delta1 views start 12 months before the current release with an end date 6 months before the current release date.
  • delta2 views start 18 months before the current release with an end date 12 months before the current release date.

Delta date ranges can be changed by calling the stored procedure setDeltaRange(viewNumber, startDateTime, endDateTime).

For example, to set the delta view range to start on 31 July 2018 and end a year later

  • CALL setDeltaRange(0,"20180731","20190731");

The delta1 and delta2 ranges can also be set in the same way

  • CALL setDeltaRange(1,"20170731","20190731");
  • CALL setDeltaRange(2,"20020131","20070731");
delta1delta1_[component-type] (e.g. delta1_concept, delta1_description)
delta1_refset_[refset-type] (e.g. delta1_refset_Simple)
delta2delta2_[component-type] (e.g. delta2_concept, delta2_description)
delta2_refset_[refset-type] (e.g. delta2_refset_Simple)

An additional table view (with the prefix snapasview) provides a current snapshot view derived from the Full release. This is redundant in this database, because the import process imports the Snapshot release files as well as the Full release files. However, a few snapasview examples are included to provide examples of a views that could be used to avoid the need to import the Snapshot tables. 

Composite Views

The table below summarizes the composite views supported by the database. Many of these composite views have variants that access specific snapshot views. These variants are indicated by the view prefixes snap, snap1 and snap2. Note that the snap variants use the snap tables, while snap1 and snap2 variants use the relevant snapshot table views. Composite views that require access to the transitive closure table can only access the current snapshot (i.e. the snap tables). A few specific composite views are also relevant to to the delta views and these have delta, delta1 and delta2 variants.

Composite ViewPurposeSnap Table and ViewsDelta Views
fsnDisplay of fully specified name for a specified conceptid.All snapshot views-
prefDisplay of preferred synonym for a specified conceptid.All snapshot views-
synDisplay of acceptable synonyms for a specified conceptid.All snapshot views-
synallDisplay of all valid synonyms (preferred and acceptable) for a specified conceptid.All snapshot views-
syn_search_activeAll valid synonyms of active concepts. This is used as the substrate for searches.All snapshot views-
term_search_activeFully specified name and all valid synonyms of active concepts. This can be used as an extended substrate for searches including fully specified names.All snapshot views-
rel_fsnAll relationships with fully specified names returned for sourceid (src_id, src_term), typeid (type_id, type_term) and destinationid (dest_id, dest_term) and relationshipGroup.All snapshot views-
rel_prefAll relationships with preferred synonyms returned for sourceid (src_id, src_term) typeid (type_id, type_term) and destinationid (dest_id, dest_term) and relationshipGroup.All snapshot views-
rel_def_fsnAll defining attribute relationships with fully specified names returned for sourceid (src_id, src_term), typeid (type_id, type_term) and destinationid (dest_id, dest_term) and relationshipGroup.All snapshot views-
rel_def_prefAll defining attribute relationships with preferred synonyms returned for sourceid (src_id, src_term) typeid (type_id, type_term) and destinationid (dest_id, dest_term) and relationshipGroup.All snapshot views-
rel_child_fsnAll direct subtypes of a concept (conceptId) returned using the id and fully specified name (id, term) of the child concept.All snapshot views-
rel_child_prefAll direct subtypes of a concept (conceptId) returned using the id and preferred synonym (id, term) of the child concept.All snapshot views-
rel_parent_fsnAll direct supertypes of a concept (conceptId) returned using the id and fully specified name (id, term) of the parent concept.All snapshot views-
rel_parent_prefAll direct supertypes of a concept (conceptId) returned using the id and preferred synonym (id, term) of the parent concept.All snapshot views-
transclose_prefTransitive closure table view returned with subtype and supertype returned with id and preferred term.Only snap table-
proxprim_prefProximal primitive relationships closure table view returned with subtype and supertype returned with id and preferred term.Only snap table-
inactive_conceptsReturns all inactive concepts in a specified snapshot or delta view. The returned data includes the fully specified name of the concept, the reason for inactivation (from the concept inactivation reference set) and the associations with active concepts shown in the historical association reference sets.All snapshot viewsAll delta views
inactive_descriptionsReturns all inactive descriptions in a specified snapshot or delta view. The returned data includes the fully specified name and active status of the described concept, and the reason for inactivation (from the description inactivation reference set),All snapshot viewsAll delta views

Stored Procedures

ProcedureDescriptionView Prefix Support
snap_SearchPlus(searchWords,filter)

Searches for acceptable synonyms of active concepts using a MySQL fulltext boolean search for the specified word or words. Word prefixed by "+" must be present, words prefixed by "-" but be absent and words with neither prefix will also be searched for but their absence from a term will not prevent a match.

The filter can be used as follows to filter the search:

  • Left blank: no filtering
  • < conceptId : only terms of concepts that are subtypes of the concept identifed by conceptid will be included in the search results.
  • < shortcutTerm : only terms of concepts that are subtypes of the concept identified by looking up the shortcutTerm in the config_shortcuts table will be included in the search results
  • regular-expression : only terms that match the regular expression will be included in the search results
  • !regular-expression : only terms that do NOT match the regular expression will be included in the search results

Examples:

CALL snap_SearchPlus('fundus stomach', '');
CALL snap_SearchPlus('+fundus +stomach','');
CALL snap_SearchPlus('+lung +disease -chronic','');

CALL snap_SearchPlus('appendix','<proc');
CALL snap_SearchPlus('hemoglobin','<lab');
CALL snap_SearchPlus('infection','<19829001');

CALL snap_SearchPlus('+fundus', 'ch');
CALL snap_SearchPlus('fundus','!(eye|oculi|uter)');
CALL snap_SearchPlus('+lung +disease +chronic','oe?dema');

All snapshot views.
snap_ShowLanguages(conceptId, languageCodeA, languageCodeB)

Shows the terms associated with a specified conceptId in two languages specified by the language codes.

Example:

CALL `snap_ShowLanguages`(80146002, 'en-GB','en-US');

All snapshot views.
eclSimple(expression-constraint)

Allows a fairly simple ECL expression to be processed. Maximum of one focus concept constraint optionally refined by up to two attribute value constraints.

Example:

CALL `eclSimple`('<404684003:363698007=<<39057004,116676008=<<415582006');

Only current snapshot
setLanguage(viewNumber, languageCode)

Sets the language reference set that determines the terms to be displayed by composite views with names ending _fsn, _pref, _syn, _synall, term. The language and dialect code is used to specify the language (e.g. en-US, en-GB).

If other values are supported by the SNOMED Edition, these will need to be added to the config_languages to provide the refsetId lookup from the language code.

Example:

CALL `setLanguage`(0, "en-GB")

-
setDeltaRange(viewNumber, startDateTime, endDateTime)

Sets the date range for a specified delta view (viewNumber 0=delta, 1=delta1, 2=delta2)

Examples:

CALL setDeltaRange(0,"20180731","20190731");

CALL setDeltaRange(2,"20020131","20070731");

-
setSnapshotTime(viewNumber, dateTime)

Sets the date on which a specified snapshot view is based (viewNumber 1=snap1, 2=snap2)

Example:

CALL setSnapshotTime( 1,"20170131");

CALL setSnapshotTime( 2,"20120131");

-
resetConfig()Resets the configuration file to the default initial starting snapshot time and delta range.-
showConfig()Displays the configuration table settings for language, snapshot dates and delta ranges.-



Feedback
  • No labels