CASDB

Notes to CAS server DB design

Phase II by Sega

The wordy description of the DB:

1) Our CAS system will store the catalogues. Each catalogue is the collection of different tables. Each catalogue should contain one or more tables.

2) The names of the catalogues should be unique, as well as the the names of tables of the SAME catalogue.

3) The tables of each catalogue should reside in separate schemas (to have different namespaces). The name of the schema should be equal to the catalogue name.

4) All the metadata about the CAS system, its tables, catalogues should reside in separate schema (currently "cas_metadata").

1) The main system table of CAS is the table "catalog_list". That table should reference all the catalogues in the system with their

• unique catalogue id (column "id") (PK)
• name (column "name")
• short info (column "info")
• long description (column "description")

2) The second system table of CAS is the table "table_list". That table should list all the tables in all catalogues. Each record should contain

• the table unique id (column "id") (PK)
• the id of the corresponding catalogue (column "catalog_id") (FK)
• the table name (name)
• short info (column "info")
• long description (column "description")

3) The third system table of CAS is the table attribute_list. That table should list the columns for all data-tables in CAS. Each record of this table should contain

• the unique id of the attribute (column "id")(PK)
• the id of the corresponding table (column "table_id")(FK)
• the name of the attribute (columns "name")
• the unit of the attribute
• the id of the UCD from the table ucd_list (columns "ucd_id")(FK)
• the id of the datatype from the table datatype_list (column "datatype_id")(FK)
• short info (column "info")
• long description (column "description")

4) Next system table is the table with datatypes ("datatype_list"). The purpose of the table is to define the list of the "external" types (from VO specification for example) and (probably) define the mapping between the external and internal types. That table should contain:

• The unique id of the datatype (column "id")(PK)
• The "external" name of the datatype (column "name")
• The internal(Postgres) name of the datatype (column "datatype_internal")
• Datatype info (column "info")

5) Next system table is the table with UCD's ("ucd_list"). The purpose of that table is the list of UCD's together with their normal (wordy) explanation to have the possibility to detect situations like multiple UCD to one physical entity, and just to manage better and separately the UCD part of CAS. That table should contain:

• The unique id of the UCD (column "id") (PK)
• The UCD word (column "name")
• The wordy explanation of the entity (column "info")

Each component of the system also have each own extendable list of properties:

6) The table "catalog_property_list" contain the list of properties which can be set up for the catalog. That table should contain:

• The unique id of the property (column "id")(PK)
• The name of the property (column "name")
• Additional info (column "info")

7) The table listing the properties for all catalogues (table "catalog_property_map"). It should contain:

• The id of the catalogue (column "catalog_id") (FK)
• The id of the property (column "property_id") (FK)
• The value of the property

8) The table "table_property_list" contain the list of properties which can be set up for the tables. That table should contain:

• The unique id of the property (column "id")(PK)
• The name of the property (column "name")
• Additional info (column "info")

9) The table listing the properties for all tables (table "table_property_map"). It should contain:

• The id of the table (column "table_id") (FK)
• The id of the property (column "property_id") (FK)
• The value of the property

10) The table "attribute_property_list" contain the list of properties which can be set up for the attributes. That table should contain:

• The unique id of the property (column "id")(PK)
• The name of the property (column "name")
• Additional info (column "info")

11) The table listing the properties for all attributes (table "attribute_property_map"). It should contain:

• The id of the attribute (column "attribute_id") (FK)
• The id of the property (column "property_id") (FK)
• The value of the property

The graph of the db (by "dbwrench")

The formal SQL initialization of the DB system is available here – http://vo.astronet.ru/cgi-bin/viewvc.cgi/sql/trunk/initdb.sql?root=cas&view=markup

The SQL interface functions is available here http://vo.astronet.ru/cgi-bin/viewvc.cgi/sql/trunk/cas_funcs.sql?root=cas&view=markup