Entity Relationship (E-R) Primer
There are two kinds of tables in database: entity tables and relation tables. Given an E-R diagram, the tables can be directly identified from it.
Entity tables are used to hold information about things in the database. One rule of thumb is that Entity tables should have some kind of unique index which can be referenced by other tables. In addition to the index, Entities have attributes. An example is the Interface table. An interface has attributes for its name and its return type. The LSB also adds attributes to indicate the status of the interface (included, excluded, or undecided), if it is properly documented, etc. Generally, each attribute is represented by a column in the table.
Relation tables are used to hold information about the relationship between two entities. A relation table can be as simple as having two columns, each of which contain the unique index from another table. Relations may also have attributes, which are represented by additional columns.
There a 3 types of relationships that can occur, 1-to-1, 1-to-N, and N-to-N. Each is implemented by a different technique.
- 1-to-1 relationships are implemented by just putting additional columns in an entities table.
- 1-to-N relationships are implemented by having a column in the entity table that represents the N side of the relationship which contains the unique index from the entity table that represents the 1 side of the relationship. This allows multiple rows in the N entity table to refer to the same row in the 1 entity table.
- N-to-N relationships are implemented by using a Relation table. Each row in this table contains the unique indecies from 2 (or more) Entity tables. Each index value can appear multiple times in the relationship table, thus allowing the many to many relation.
The primary keys in all tables are marked green, the unique keys are marked blue. In the composite keys the order of fields is that in which they are declared in description table. Note that now each table has no more that one unique key, so if more than one fields are marked blue for one table then these keys form a composite unique key.
The naming scheme is intended as a usage hint. Tables named FooBar are relation (join) tables expressing a relationship between the components of the name, which are sometimes abbreviated e.g. ArchInt is a join of Architecture and Interface. BaseTypes and VMIBaseTypes tables implement N-to-N relations between ClassInfo and Interface tables. The XxxGroup tables represent groupings of the Xxx table, e.g. the HeaderGroup allows you to group together pieces of a Header
The following tables represent the LSB database schema:
Tables containing information about different LSB elements
Tables containing information about distributions, applications, upstream components, their structure and certification status.
Cache Tables and Stored Procedures
Cache tables are created automatically on the basis of LSB Elements and Community tables (when performing 'make restoreall' operation or using a separate make target called 'cache' - 'make cache'). Some stored procedures are created by these scripts, as well (and actually some cache tables are created by stored procedures, so one can simply invoke appropriate procedures to recreate some of the tables).
Cache tables and are used in the LSB Navigator to speed up different queries concerning both LSB and Community parts of the database.
All cache table names starts with 'cache_'.
Futures Tracker Tables
New LSB Futures Tracker implemented in the LSB Navigator also stores some information in the database.
Triggers are useful for situations when lots of data should be uploaded to the database in order to avoid some routine work. Note, however, that triggers require high privileges (till MySQL 5.1.6, trigger creation requires SUPER privilege), so triggers can be absent in the database (please refer to the SpecDatabaseUsage section for more information).
The list of all DB Schema and Scripts updates released by ISPRAS team with release notes is available at LSB Infrastructure Project portal.