Smithsonian National Museum of Natural History logo

Osteoware: Standardized Skeletal Documentation Software

Osteoware: Standardized Skeletal Documentation Software

Summary Paragraph and SQL Queries

With the click of a few ‘Add comments’ import buttons, you can transfer comments text fields directly from other modules into the Summary Paragraph module. The text may then be copy and pasted to rearrange and compose a summary report for that catkey number. The unlimited text field also has a spellcheck feature that allows you to add terms to build your own custom dictionary.

  • Easily transfer text fields directly from other modules via ‘Add comments’ import buttons.
  • Copy and paste to rearrange and compose a report for that catkey number.
  • Unlimited text field has handy spellcheck feature with customizable dictionary.

The Summary Paragraph module with comments import buttons on the right side that facilitate the transfer of text fields from the modules listed in the buttons. The unlimited text field has spellchecker feature.

Structured Query Language (SQL) Queries

The Summary Paragraph module, and the individual catkey reports it creates, are currently the only way to generate reports through the Osteoware graphic user interface (GUI). In order to efficiently extract other aggregate data from the Structured Query Language (SQL) database you must install the Advantage Data ArchitectTM version 9.1, which is available as a freeware download from Sybase (see the link in the Downloads section), and use the SQL query features.

It is beyond the scope of this website to provide a comprehensive SQL resource, however do not be intimidated. SQL is very easy to learn and is a powerful skill to possess.  The following introductory web resources are available. 

http://www.sqlzoo.net/

http://www.sqlcourse.com/

If you can recommend other good SQL sites, then please post URL's in our forum https://osteoware.si.edu/forum/ under the Database Management and SQL subforum.

In brief, a SQL query is composed of nothing more than Select, From, and Where statements, as seen in the center of this screenshot of the Advantage database manager. The Order By statement (the last line) is not required but will organize the query results for you. More powerful query statements ‘join’ tables together based on the unique identifier (usually the Catkey) so that data may be extracted from two or more tables at once for subsequent meta-analyses.

The Advantage Data Architect displaying the complete list of Osteoware data tables to the left in the Connection Repository, the AGESEX.ADT table open in the background to the right, and the SQL Utility window in the foreground containing a simple query to extract data for females (SUMARSEX=2) over 15 years of age (MinAge >=16).

In the next screenshot we build on the previous simple AGESEX query with a table ‘join’ statement by adding the Where condition a.catkey = c.catkey. The small case ‘a.’ and ‘c.’ before catkey and the Select and Where conditions, are table aliases; they are defined in the From statement: from AGESEX a, Crandeformation c. These small case letters simply replace having to laboriously type in the complete table name in front of each selected data field and where condition. In the simple AGESEX query we did not need table aliases because only one table was involved.

The join query in the example will extract data for females (SUMARSEX=2) over 15 years of age (MinAge >=16) and having posterior cranial modification/deformation (POSTDeformed is not null). In this case, the SQL code ‘is not null’ will find all individuals where data is scored for posterior deformation, in other words the record is not blank. Beware though, a zero ‘0’ in a data record does not qualify as ‘null’, the record must be blank.

You may extract data with pinpoint accuracy by creatively combining join and where statements. For instance, you may extract data for females of a certain age that have posterior cranial deformation and display lambdoid suture ossicles or perhaps cervical vertebrae arthritis. The possibilities are endless and limited only by your imagination and SQL language skills. Remember, you may post any SQL questions here https://osteoware.si.edu/forum/database-management-and-sql-queries.

The Advantage Data Architect displaying the complete list of Osteoware data tables to the left in the Connection Repository, the AGESEX.ADT table open in the background to the right, and the SQL Utility window in the foreground containing a simple query to extract data for females (SUMARSEX=2) over 15 years of age (MinAge >=16).