Presentation Objectives

1. Presentation Objectives • By the end of this presentation you should know: • The content of the 3 core SNOMED distribution files • How to load the 3 core files into relational tables Help

2. SNOMED CT Core Structure • SNOMED CT Core Files • Principles behind SNOMED CT Core Structure • Three Core tables • Loading the Core Tables into a Database • Microsoft Access • Example SQL Table Definitions/Data Load Help

3. The Principles behind the SNOMED CT Structure • SNOMED is concept based • Each concept represents a unit of meaning • Each concept has one or more language terms that can be used to describe the concept • Every concept has inter-relationships with other concepts that provide logical computer readable definitions

4. The Principles behind the SNOMED CT Structure Kidney Disease • Descriptions Disease of Kidney Renal Disease Concept ID - 90708001 Renal Disorder Nephrosis

5. The Principles behind the SNOMED CT Structure (128606002) Disorder of the urinary system • Hierarchical relationships Is a (90708001) Kidney disease

6. The Principles behind the SNOMED CT Structure • Clinical attributes - logical definitions created through relationships with other concepts (90708001) Kidney disease (64033007) Kidney Has disorder site

7. SNOMED CT Core Files • The following slides show the 3 core files and the data fields that comprise them

8. SNOMED CT Core Distribution Tables Relationships Concepts RelationshipId ConceptId1 Relationship ConceptId2 Characteristic Type Refinability RelationshipGroup ConceptId Concept Status Full Specified Name CTV3ID SNOMEDID IsPrimitive Descriptions DescriptionId Description Status ConceptId Term Initial Capital Status Description Type Language Code

9. SNOMED CT Concepts Table Concepts ConceptId Concept status Fully specified name CTV3ID SNOMEDID IsPrimitive Sample Data

10. SNOMED CT Descriptions Table Descriptions DescriptionId Term ConceptId Description type Description status Initial capital status Language code Sample Data

11. SNOMED CT Relationships Table Relationships RelationshipId ConceptId1 Relationship ConceptId2 Characteristic Type Refinability RelationshipGroup Sample Data

12. Loading the SNOMED CT Files • SNOMED CT core files may be loaded directly into a relational database. • The first row of each table contains the table column names for ease of import • Example - Microsoft Assess Import • Example - SQL table Definition/Data Load

13. More Information SNOMED CT Implementation Guide or contact us www.snomed.org

14. Concept Table Sample Data

15. Description Table Sample Data

16. Relationship Examples (Terms shown for illustrative purposes only)

17. SNOMED CT Concept Identifier • ConceptId is a 64 bit integer code that uniquely identifies each concept in SNOMED CT. This field is the primary key of the concepts file. Concepts Descriptions Partition identifier Relationships Item identifier Concept ID 101291657896189 00 9 Check-digit

18. Partition Identifier - Core Values

19. Partition Identifier - For Extensions

20. SNOMED CT Extension Identifier • Extensions to the SNOMED CT are differentiated from the core through the “partition identifier”. Each extension has a namespace that identifies the author. Partition identifier Namespace identifier SCTID 99999999 0101291 10 9 Extension item identifier Check-digit

21. Check Digit • It is not envisaged that users will routinely type SCTID values, but the check digit is available to check for errors in transcription or communication of the SCTID. • The right most digit of any SCTID is the check digit. • This digit is generated and checked using the “Verhoff check”, which is a Dihedral D5 Check. See the SNOMED CT core specification for information about the Verhoff check-digit and sample program code.

22. Item Identifier - Core Content • The item identifier for core content will be an integer with a value between 100 (3 digits) and 999,999,999,999,999 (15 digits). • Combined with the partition identifier and the check sum, this make the total length of the SCTID somewhere between 6 and 18 digits.

23. Concept Status Flag • “Concept status” is an integer value that specifies the current status of a given concept. The permitted values can be viewed in the table Concept Status Values. • A value of zero indicates that the concepts in current use. • A full documentation can be found in the SNOMED CT Implementation Guide.

24. Concept Status Values

25. Concept Fully Specified Name • The “Fully Specified Name” is a unique string which describes the concept in a way that is intended to be unambiguous. • Note that the fully specified name for a concept also appears in the descriptions table. Although this creates redundancy of data storage, the designers of SNOMED felt it important to include a human readable representation in the concepts table. Concepts Descriptions

26. CTV3ID and SNOMEDID • The CTV3ID and SNOMED ID are alpha-numeric codes that are attached to a concept for historical reasons. SNOMED CT was formed by the merger of SNOMED RT and Clinical Terms Version 3 (formerly know as the Read codes). • These codes will continue to be allocated and stored for the purpose of backward compatibility with the source terminologies.

27. The “Is Primitive” Flag • “IsPrimitive” is a Boolean field (true/false). • It has important application for developers who which to manipulate SNOMED CT concepts using description logic. Primitive concepts can not have children assigned to them automatically based on the clinical attributes of the concept. • For more information on this field, see the SNOMED CT implementation guide.

28. SNOMED CT Description Identifier • DescriptionId is a 64 bit integer code that uniquely identifies each description in SNOMED CT. This field is the primary key of the descriptions file. Partition identifier Item identifier 9 Description ID 101291 01 Check-digit

29. Terms • “Term” is a text string which can be used to describe the associated concept. Each term is not unique within the descriptions table, since the same term may be used to describe more than one concept. For example the term “Fundus” is associated with several different anatomical concepts, so it will appear in multiple rows of the descriptions table. In such cases, the concept FullySpecifiedName should be examined by a user, to differentiate meaning between these concepts.

30. Description Type Values

31. Description Status Values

32. Initial Capitalization Status • This field indicates whether the capitalization of the first character of the term is significant. • If this field has a value of ‘0’ the first character may have it’s case changed according to it’s position in a sentence without changing it’s meaning. • If it is ‘1’, the case of the first character must not be changed, but must be displayed as it appears in the description table.

33. Description Language Code • This field is a string identifying the language, and if appropriate the dialect in which the description is valid. It consists of a code and, optionally, a sub-code. If a sub-code is present it is separated from the code by a dash (“-”). • The code is the ISO635 language code. • The sub-code is a two letter ISO3166 country code, or a string of more than two letters, which is registered with IANA as a sub-code for the language.

34. Description Language Code (Definitions) • ISO639 is the International Standard for “Codes for the representation of languages” • ISO3166 is the International Standard for “Codes for the representation of names of countries” • IANA is the “Internet Assigned Numbers Authority”

35. SNOMED CT Relationship Identifier • RelationshipId is a 64 bit integer code that uniquely identifies each concept in SNOMED CT. This field is the primary key of the concepts file. Partition identifier Item identifier 9 Relationship ID 101291 02 Check-digit

36. Relationship Characteristic Type Values

37. Relationship Refinability Values

38. Relationship - Relationship Group • An integer value that expresses an association between two or more Relationships. • The default relationship group value is zero and this applies to all relationships that have not been stated to be associated with any other Relationships. All Relationships that share the same ConceptId1 and the same non-zero Relationship group value are associated with one another. Any Relationships that share the same ConceptId1 but have different Relationship group values are not associated with one another

39. Loading into Microsoft Access • SNOMED CT tables can be loaded directly into Access via the “File/Get External Data/Import” menu option. • Some important parameters involved in the load process are as follows: - • SNOMED CT files are tab delimited • Field names are in the first row of each file • Primary key is the “Id” field for each table

40. Access Load - Tab Delimited SNOMED CT Files are “tab” delimited

41. Access Load - First Row Has Field Names Field names for each table can be found in the first row.

42. Access Load - Primary Keys

43. Example SQL Table Definitions/Data Load • SNOMED CT tables can be created and loaded using SQL scripts or by entering SQL at the command line. The following slides show illustrative examples. You should consult your own SQL manuals for the exact commands applicable to your relational database management system (RDMS). • Create Concept Table • Create Description Table • Create Relationship Table • Load Table Data

44. Example* SQL Definition for ‘Concept’ Table CREATE TABLE SNOMEDConcept (ConceptId BIGINT UNSIGNED NOT NULL, ConceptStatus TINYINT NOT NULL, FullySpecifiedName TEXT, CTV3ID TINYTEXT, SNOMEDID TINYTEXT NOT NULL, PRIMARY KEY (ConceptId), KEY (SNOMEDID) ); * Example shown uses ‘MySQL’ data types. See your own RDBMS documentation for the correct syntax and data types for the create table command.

45. Example* SQL Definition for ‘Description’ Table CREATE TABLE SNOMEDDescription (DescriptionId BIGINT UNSIGNED NOT NULL ConceptId BIGINT UNSIGNED NOT NULL REFERENCES SNOMEDConcept, DescriptionStatus TINYINT NOT NULL, DescriptionType TINYINT NOT NULL InitialCapitalStatus TINYINT, LanguageCode TINYTEXT, PRIMARY KEY (DescriptionId) ); * Example shown uses ‘MySQL’ data types. See your own RDBMS documentation for the correct syntax and data types for the create table command.

46. Example* SQL Definition for ‘Relationship’ Table CREATE TABLE SNOMEDRelationship (RelationshipId BIGINT UNSIGNED NOT NULL ConceptId1 BIGINT UNSIGNED NOT NULL REFERENCES SNOMEDConcept, RelationshipType BIGINT UNSIGNED NOT NULL REFERENCES SNOMEDConcept, ConceptId2 BIGINT UNSIGNED NOT NULL REFERENCES SNOMEDConcept, CharacteristicType TINYINT Refinability TINYINT, PRIMARY KEY (RelationshipId) KEY (ConceptId1), KEY (ConceptId2) ); * Example shown uses ‘MySQL’ data types. See your own RDBMS documentation for the correct syntax and data types for the CREATE TABLE command.

47. Example* - Load SNOMED CT Table Data LOAD DATA INFILE ‘sct_Concepts_01232002.txt’ INTO TABLE SNOMEDConcept FIELDS TERMINATED BY ‘\t’ INGNORE 1 LINES; > LOAD DATA INFILE ‘sct_Description_01232002.txt’ INTO TABLE SNOMEDDescription FIELDS TERMINATED BY ‘\t’ INGNORE 1 LINES; > LOAD DATA INFILE ‘sct_Rels_01232002.txt’ INTO TABLE SNOMEDRelationships FIELDS TERMINATED BY ‘\t’ INGNORE 1 LINES; * Examples shown use ‘MySQL’ syntax. See your own RDBMS documentation for the correct syntax of the LOAD DATA command