Documentation of bmrbdata database

(The Dictionary)

The dictionary is a single table in the database, called, imaginatively enough, "dictionary". Each row in the dictionary describes one of the fields of one of the other tables in the database. If you take all the rows of the dictionary that have the same particular value for dbtablemanual, then you have a complete description of that table. (Well, not a complete description exactly. There are some boilerplate fields that are always prepended to all table layouts, and the dictionary does not describe those fields.)

Here is the full description of the fields of the dictionary, and what they mean:

Dictionary Table

field name field type field meaning

originalsf varchar(80,1) This is the name of the saveframe from the template NMR-STAR file that this tag came from.

originalcategory varchar(80,1) This is the value of the Saveframe_Category that this tag came from. The Saveframe_Category labels the format for the saveframe, such that we can be guaranteed that all saveframes with the same Saveframe_Category are in the same format, and represent the same category of real-world data.

originaltag varchar(80,1) This is the name of the tag from the NMR-STAR file that this row of the dictionary is representing. There is a one-to-one mapping of this tag name to the field's name in the database. (dbcolumnmanual is an abbreviation of this originaltag value)

dictionaryseq integer This is a simple number for the purposes of ordering the dictionary's rows. (A relation does not guarantee any order unless you give it a field to use in an ORDER BY clause. That is the purpose of this field.)

dbtableauto varchar(18,1) This is a programmaticly generated suggestion for the table's name in the database. This can be ignored for most people's purposes. Use the value in dbtablemanual instead.

dbcolumnauto varchar(18,1) This is a programmaticly generated suggestion for this column's name in the database. This can be ignored for most people's purposes. Use the value in dbcolumnmanual instead.

dbtype varchar(20,1) This is the type of this field, expressed in SQL syntax. To learn more about SQL syntax for data types, see this page.

dbnullable varchar(8,1) Determines whether or not the field is a nullable field. There are two legal values: "NULLABLE", and empty-string (or string-of-spaces).

enumerationid integer Unused at the moment. Can be ignored for now.

foreigntable varchar(18,1) Unused at the moment. One day this will show the foreign key mapping of this field to a field on some other table, but at the moment we do not want to make use of database-enforced foreign keys yet.

foreigncolumn varchar(18,1) Unused at the moment. One day this will show the foreign key mapping of this field to a field on some other table, but at the moment we do not want to make use of database-enforced foreign keys yet.

indexflag char(1) Either a 'Y' or an 'N'. If 'Y', then that means that there should be a secondary index generated for this field, because we expect to query on it often.

internalflag char(1) Either a 'Y' or an 'N'. If 'Y'. then this is a field that should never have its true value shown to the outside world: The information is for internal BMRB purposes only. (Makes no difference in the schema, but we use it when exporting table data to others.)

dbtablemanual varchar(18,1) This is the manually generated name of the relation on which this field resides. This is the name that is really used for the table. The dbtableauto field simply stores the original name that was suggested programaticly to the domain scientist.

dbcolumnmanual varchar(18,1) This is the manually generated name to be used for the field in the database. This is the name that is *really* used. The dbcolumnauto field simply stores the original name that was suggested programaticly to the domain scientist.
This is intended to be an abbreviation of the originaltag field. The abbreviation was needed because the database can only handle identifiers of 18 characters or less.

loopflag char(1) Either a 'Y' or an 'N'. Determines whether or not this field came from a tag that was inside a loop in the original NMR-STAR file. This is important because loop tables look a little different from non-loop tables.

sequence integer This is a number to sort by when ordering the dictionary. The order of the rows of the dictionary reflects the order of the associated tags as they appeared in the original NMR-STAR template file. This tells us what order to use when generating an NMR-STAR file from the data in the database tables.

Dictionary Table
field name	field type	field meaning
`originalsf`	`varchar(80,1)`	This is the name of the saveframe from the template NMR-STAR file that this tag came from.
`originalcategory`	`varchar(80,1)`	This is the value of the Saveframe_Category that this tag came from. The Saveframe_Category labels the format for the saveframe, such that we can be guaranteed that all saveframes with the same Saveframe_Category are in the same format, and represent the same category of real-world data.
`originaltag`	`varchar(80,1)`	This is the name of the tag from the NMR-STAR file that this row of the dictionary is representing. There is a one-to-one mapping of this tag name to the field's name in the database. (dbcolumnmanual is an abbreviation of this originaltag value)
`dictionaryseq`	`integer`	This is a simple number for the purposes of ordering the dictionary's rows. (A relation does not guarantee any order unless you give it a field to use in an ORDER BY clause. That is the purpose of this field.)
`dbtableauto`	`varchar(18,1)`	This is a programmaticly generated suggestion for the table's name in the database. This can be ignored for most people's purposes. Use the value in dbtablemanual instead.
`dbcolumnauto`	`varchar(18,1)`	This is a programmaticly generated suggestion for this column's name in the database. This can be ignored for most people's purposes. Use the value in dbcolumnmanual instead.
`dbtype`	`varchar(20,1)`	This is the type of this field, expressed in SQL syntax. To learn more about SQL syntax for data types, see this page.
`dbnullable`	`varchar(8,1)`	Determines whether or not the field is a nullable field. There are two legal values: "NULLABLE", and empty-string (or string-of-spaces).
`enumerationid`	`integer`	Unused at the moment. Can be ignored for now.
`foreigntable`	`varchar(18,1)`	Unused at the moment. One day this will show the foreign key mapping of this field to a field on some other table, but at the moment we do not want to make use of database-enforced foreign keys yet.
`foreigncolumn`	`varchar(18,1)`	Unused at the moment. One day this will show the foreign key mapping of this field to a field on some other table, but at the moment we do not want to make use of database-enforced foreign keys yet.
`indexflag`	`char(1)`	Either a 'Y' or an 'N'. If 'Y', then that means that there should be a secondary index generated for this field, because we expect to query on it often.
`internalflag`	`char(1)`	Either a 'Y' or an 'N'. If 'Y'. then this is a field that should never have its true value shown to the outside world: The information is for internal BMRB purposes only. (Makes no difference in the schema, but we use it when exporting table data to others.)
`dbtablemanual`	`varchar(18,1)`	This is the manually generated name of the relation on which this field resides. This is the name that is really used for the table. The dbtableauto field simply stores the original name that was suggested programaticly to the domain scientist.
`dbcolumnmanual`	`varchar(18,1)`	This is the manually generated name to be used for the field in the database. This is the name that is really used. The dbcolumnauto field simply stores the original name that was suggested programaticly to the domain scientist. This is intended to be an abbreviation of the originaltag field. The abbreviation was needed because the database can only handle identifiers of 18 characters or less.
`loopflag`	`char(1)`	Either a 'Y' or an 'N'. Determines whether or not this field came from a tag that was inside a loop in the original NMR-STAR file. This is important because loop tables look a little different from non-loop tables.
`sequence`	`integer`	This is a number to sort by when ordering the dictionary. The order of the rows of the dictionary reflects the order of the associated tags as they appeared in the original NMR-STAR template file. This tells us what order to use when generating an NMR-STAR file from the data in the database tables.

The select: To see a description of all the fields of a single table in the database, you would perform the following SQL select:

	SELECT * FROM dictionary WHERE dbtablemanual = 'thetablename'
	    ORDER BY sequence;

This gives you a description of the fields on that table, (not counting the boilerplate fields that are prepended to all the tables.)

Next topic: The boilerplate columns