From The Linux Foundation
Jump to: navigation, search

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.

Table Descriptions

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

Architecture ClassInfo Command CommandAttribute Constant
ConstantAttribute CxxApi DynamicEntries ElfSections Header
HeaderGroup Interface InterfaceAttribute InterpretedLanguage InterpretedLanguageModule
InterfaceVote IntStd LibGroup Library LibraryAttribute
LSBVersion Module Parameter RpmTag SectionTypes
SourceBinary SourceCode SourceHeader SourceShadow StandardSource
Standard SubModule TestCaseSource TestSuite Type
TypeMember TypeMemberExtras TypeType Version Vtable
Relation Tables
AbiApi AbiMacro ArchClass ArchConst ArchDE
ArchES ArchInt ArchLib ArchType BaseTypes
ClassVtab CmdStd LGInt ModSMod SModCmd
SModLib SModStd SourceQualifier TestCmd TestInt

LSB Elements Schema and Details...

Community Tables

Tables containing information about distributions, applications, upstream components, their structure and certification status.

Entity Tables
AppCategory AppInfo AppInterpreter Application ApprovedLibrary
ApprovedCommand CompatSymbol CompFile CompInfo CompLDpath
Component CompProvides CompRequires Distribution DistrVendor
JavaBaseClass JavaClass JavaInterface RawClass RawCommand
RawDynamicEntries RawILModule RawInterface RawLibSoname RawLibrary
RawVtable RILMBuiltin RLibLink WeakSymbol
Relation Tables
AppLib AppJInt AppRILM AppRInt AppShippedLib
CompJInt CompRILM RLibDeps RLibRClass RLibRInt

Community Tables Schema and Details...

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_'.

Stored Procedures and Cache Tables Schema and Details...

Futures Tracker Tables

New LSB Futures Tracker implemented in the LSB Navigator also stores some information in the database.

Futures Tracker Tables Schema and Details...


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).

Triggers Details...


The list of all DB Schema and Scripts updates released by ISPRAS team with release notes is available at LSB Infrastructure Project portal.