Documentation of bmrbdata database

(The Boilerplate Fields)

(Before continuing, if you do not know SQL syntax, please refer to a small bit of SQL syntax before reading this page.)

Each table in the BMRB database can be described by taking all the rows in the following select, and then prepending the boilerplate described on this page:

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

There are two kinds of tables in the BMRB database: free-tag tables, and loop tables. Loop tables are tables for which the dictionary entries of the fields of that table have their loopflags set to 'Y'. These two kinds of tables have slightly different boilerplate fields prepended to them.

Free-Tag tables

The Free-tag tables have the following fields prepended to every table:

Boilerplate for free-tag tables
field name	field type	field meaning
`saveframeid`	INTEGER PRIMARY KEY UNIQUE	This is a unique number that identifies this row of this table. Note that all saveframeid's across the whole BMRB database come from the same 'pool' of numbers, so that this number is not only guaranteed unique on this table, but also across all tables in the database.
`originalblock`	varchar(80,0)	Tells which datablock the data in this row came from. There is a one-to-one mapping bewteen NMR-STAR files and datablock names, so this also tells which NMR-STAR file this row came from.
`originalframe`	varchar(80,0)	Tells which saveframe in the datablock this row came from. This is not the same as saveframe_category, because there can be several instances of a saveframe_category in a single datablock, each with its own saveframe name. Also, the originalframes are ONLY guaranteed unique within a single datablock. So to guarantee a unique reference to a saveframe, it is required to combine originalframe with originalblock into one key.

(Indecentally, originalblock and originalframe are combinded together into a secondary index on the table.)

Loop tables

The boilerplate columns for Loop tables look just like the ones for free tag tables (above), with one added column, and the saveframeID no longer has a unique constraint on it. Read the descriptions, though, because the saveframeID column has a slightly different meaning than it did for free tag tables:

Boilerplate for loop-tag tables
field name	field type	field meaning
`saveframeid`	INTEGER	This is a number that identifies the free-tags row that is the 'parent' of this row of the loop table. Since there can be multiple loop rows for a single saveframe (row of a free-tags table), this is not a unique number on loop tables. To figure out which free-tags table is the one containing the parent of this loop row, the dictionary can be used, as described below this table.
`originalblock`	varchar(80,0)	Tells which datablock the data in this row came from. There is a one-to-one mapping bewteen NMR-STAR files and datablock names, so this also tells which NMR-STAR file this row came from.
`originalframe`	varchar(80,0)	Tells which saveframe in the datablock this row came from. This is not the same as saveframe_category, because there can be several instances of a saveframe_category in a single datablock, each with its own saveframe name. Also, the originalframes are ONLY guaranteed unique within a single datablock. So to guarantee a unique reference to a saveframe, it is required to combine originalframe with originalblock into one key.
`loopseq`	integer	A field to keep track of the order of the rows of the loop so that it will be remembered.

	To figure out which free-tags table a loop table is inside of,
	try this query:

	('thistable' is the loop table we are looking at)
	SELECT DISTINCT dbtablemanual
	FROM   dictionary
	WHERE  loopflag = 'N'
	AND    originalcategory = SELECT DISTINCT originalcategory
	                          FROM   dictionary
				  WHERE  dbtablemanual = 'thistable';

So, now let's go to a pair of examples that show how this works...

Next Topic: examples