DTP Data Warehouse Model - Relational Database Tables

Relational Database Tables for the DTP Data Warehouse Model

This page lists the details of the tables in the DTP Data Warehouse Model. It shows the creation script for the table, provides an explanation for each of the columns, and lists any data that is loaded as part of database creation.

DATACLASSES

This table holds the major classification divisions for data. It is preloaded with four classes: Biological, Biochemical, Chemical, Clinical.

DATACLASSES Column Explanation:

DATACLASSNBR - internal database key for this table
DATACLASSNAME - unique name for this data class
DATACLASSDESCIPTION - explanation of what this data class is meant to contain

Creation Script:

create table DATACLASSES 
(
	DATACLASSNBR          NUMBER not null,
	DATACLASSNAME         VARCHAR2(20) not null,
	DATACLASSDESCRIPTION  VARCHAR2(150) not null, 
	constraint DATACLASSES_PK primary key (DATACLASSNBR) 
)
alter table DATACLASSES add constraint DATACLASSES_UK unique (DATACLASSNAME)

Initial Data:

INSERT INTO DATACLASSES VALUES 
	(1,'Biological','Tests performed on intact cells or organisms'); 
INSERT INTO DATACLASSES VALUES 
	(2,'Biochemical','Tests performed in cell free conditions'); 
INSERT INTO DATACLASSES VALUES 
	(3,'Chemical','Data on chemical properties'); 
INSERT INTO DATACLASSES VALUES 
	(4,'Clinical','Data from human clinical trials');

DATATYPES

This table holds value for types of data within each major class of data.

DATATYPES Column Explanation:

DATATYPENBR - internal database key for this table
DATACLASSNBR - internal database key for DATACLASSES entry associated with this DATATYPE record
DATATYPENAME - unique name for this data type. Must be unique across all data types, not just the data types corresponding to a particular data class.
DATATYPEDESCRIPTION - explanation of what this data type is meant to contain

Creation Script:

create table DATATYPES 
(
	DATATYPENBR          NUMBER not null,
	DATACLASSNBR         NUMBER not null,
	DATATYPENAME         VARCHAR2(20) not null,
	DATATYPEDESCRIPTION  VARCHAR2(150) not null, 
	constraint DATATYPES_PK primary key (DATATYPENBR) 
)
alter table DATATYPES add constraint DATATYPES_UC2 unique 
(
	DATATYPENBR,
	DATACLASSNBR
)
alter table DATATYPES 
        add constraint DATATYPES_UK unique (DATATYPENAME)
alter table DATATYPES
	add constraint DATACLASSESDATATYPES foreign key (DATACLASSNBR)
	references DATACLASSES (DATACLASSNBR) ON DELETE CASCADE;

Intital Data:

INSERT INTO DATATYPES VALUES 
	(1,1, 'Cellular', 'Assays done on intact cells'); 
INSERT INTO DATATYPES VALUES 
	(2,1,'Mammalian', 'In vivo tests done in mammals'); 
INSERT INTO DATATYPES VALUES 
	(3,1, 'Yeast', 'Assays done using yeast'); 
INSERT INTO DATATYPES VALUES 
	(4,3,'2D', '2D coordinates for chemical structure'); 
INSERT INTO DATATYPES VALUES 
	(5,3,'3D', '3D coordinates for chemical structure'); 
INSERT INTO DATATYPES VALUES 
	(6,3,'1D','chemical structure connection data'); 
INSERT INTO DATATYPES VALUES 
	(9,3,'Chemical Properties', 'Measured and calculated chemical properties'); 
INSERT INTO DATATYPES VALUES 
	(10,2,'Enzyme','Assays measuring activity or inhibiton of purified enzymes'); 
INSERT INTO DATATYPES VALUES 
	(11,3,'Chemical Name','Trivial and systematic chemical names for compounds');

TESTMASTER

This table allows data to be grouped into particular sets. The level of the grouping is up to the user, but it is intended for data that was generated using the same protocol. TESTSUBSET provides a capability of further grouping of a set of data. The table also allows for distributed data, as it contains information on where the actual data is stored, which may or may not be the same database as the TESTMASTER record.

TESTMASTER Column Explanation:

DATATYPENBR - internal database key for the DATATYPES record associated with this TESTMASTER record
DATACLASSNBR - internal database key for the DATACLASSES record associated with this TESTMASTER record
TESTMASTERNBR - internal database key for this TESTMASTER record
SHORTNAME - unique name for this TESTMASTER record
DESCRIPTION - explanation for this group of data. It is recommended that if TESTCOMPOUNDS or TESTCELLLINES records are used to fully describe the data, that the identification of how the compounds or cell lines are used be part of the description.
DBHOST - host name of the machine on which the data is stored
DATABASE - database instance name that contains the data
DBPORT - port needed to connect to the data
DBTYPE - which database software is being used. As of Nov 2001, oracle and postgres are recognized
RESULTTYPE - an integer that is interpreted as series of binary flags. Each flag indicates whether a particular type of data is used for this set of data, see TESTRESULTS. The bits are:
- bit 0 - (float) measurement field is used
- bit 1 - textresult field is used
- bit 2 - chemical structure number is used
- bit 3 - descriptive code number is used
The number is the sum of 2**(bit number) of all the flags that are set. For example, a data group that includes a float value and a chemical structure would use a value of 5 ( 2**0 + 2**2 ), while a data group that used only the testresult field would use a value of 2 ( 2**1 ).
WEBLINKDOC - a URL that points to a page where the data group is explained
WEBLINKCGI - a URL that points to a CGI or servlet that will respond to a query by sending data back. The specific fields and values that the particular CGI will understand may vary, but for the DTP servers, adding "?searchtype=help" to the end of WEBLINKCGI will always return a full explanation of what fields and values are used by the CGI.

Creation Script:

   
create table TESTMASTER 
(
	DATATYPENBR    NUMBER not null,
	DATACLASSNBR   NUMBER not null,
	TESTMASTERNBR  NUMBER not null,
	SHORTNAME      VARCHAR2(30) not null,
	DESCRIPTION    VARCHAR2(300) not null,
	DBHOST         VARCHAR2(75) not null,
	DATABASE       VARCHAR2(75) not null,
	DBPORT         VARCHAR2(10) null,
	DBTYPE         VARCHAR2(20) not null,
	RESULTTYPE     NUMBER not null,
	WEBLINKDOC     VARCHAR2(75) not null,
	WEBLINKCGI     VARCHAR2(75) not null, 
	constraint TESTMASTER_PK primary key (TESTMASTERNBR)
)
alter table TESTMASTER 
        add constraint TESTMASTER_UK unique (SHORTNAME)
alter table TESTMASTER
	add constraint DATACLASSES_TESTMASTER_FK1 foreign key (DATACLASSNBR)
	references DATACLASSES (DATACLASSNBR); 
alter table TESTMASTER
	add constraint DATATYPESTESTMASTER foreign key (DATATYPENBR)
	references DATATYPES (DATATYPENBR);

No intial Data for TESTMASTER

TESTSUBSET

TESTSUBSET provides for more detailed organiztion of data under a TESTMASTER

TESTSUBSET Column Explanation

TESTSUBSETNBR - internal database key
TESTMASTERNBR - internal database key for the TESTMASTER record associated with theis TESTSUSET record
SHORTNAME - summary name. Must be unique (across all testsubsets or only the testsubsets in a given testmaster?)
DESCRIPTION - more complete text description of this TESTSUBSET
PRIORITY - number that indicates the ordering of TESTSUBSETs within a TESTMASTER. Lower number is higher priority

create table TESTSUBSET 
(
	TESTSUBSETNBR   NUMBER not null,
	TESTMASTERNBR   NUMBER not null,
	SHORTNAME       VARCHAR2(30) not null,
	DESCRIPTION     VARCHAR2(200) not null,
	PRIORITY        NUMBER null, 
	constraint TESTSUBSET_PK primary key (TESTSUBSETNBR)
) 
alter table TESTSUBSET 
        add constraint TESTSUBSET_UK unique (TESTMASTERNBR,SHORTNAME)
alter table TESTSUBSET
	add constraint TESTMASTERTESTSUBSET foreign key (TESTMASTERNBR)
	references TESTMASTER (TESTMASTERNBR) ON DELETE CASCADE;

No Intial Data for TESTSUBSET

UNITS

This holds information on units of measurement.

UNITS Column Explanation

UNITNBR - internal database key
SHORTNAME - summary name. must be unique across all UNITs
HTMLNAME - summary name used in HTML files
DESCRIPTION - more complete text explanation of the particular UNIT

Creation Script:

create table UNITS 
(
	UNITNBR     NUMBER not null,
	SHORTNAME   VARCHAR2(30) not null,
	HTMLNAME    VARCHAR2(40) null,
	DESCRIPTION VARCHAR2(200) not null, 
)
alter table UNITS add constraint UNITS_LK unique (SHORTNAME)

Initial Data:

INSERT INTO UNITS VALUES 
(1,'M','M','Molar'); 
INSERT INTO UNITS VALUES 
(2,'microM','µ M','microMolar'); 
INSERT INTO UNITS VALUES 
(3,'% of cntl','% of cntl','per cent of control'); 
INSERT INTO UNITS VALUES 
(4,'% inhib','% inhib','per cent inhibition'); 
INSERT INTO UNITS VALUES 
(5,'micrograms/ml','µ g/ml','micrograms per milliliter'); 
INSERT INTO UNITS VALUES 
(6,'log10(M)','log₁₀(M)','log base 10 of molar concentration'); 
INSERT INTO UNITS VALUES 
(7,'Daltons','Daltons','Daltons'); 
INSERT INTO UNITS VALUES 
(8,'NA','NA','Units not applicable to this data');
INSERT INTO UNITS VALUES 
(9, 'log10(ug/ml)', 'log₁₀(ug/ml) ','log base 10 of concentration (micrograms/millileters)');
INSERT INTO UNITS VALUES 
(10, 'V', 'volumetric','volumetric');
INSERT INTO UNITS VALUES 
(11, 'log10(V)', 'log₁₀(V)','log base 10 of volumetric concentration');

DISPERSIONS

This table holds information on types of data dispersion measurements.

DISPERSIONS Column Explanation

DISPERSIONNBR - internal database key
SHORTNAME - summary name. Must be unique across all DISPERSIONs
HTMLNAME - name used in HTML files
DESCRIPTION - more complete text explanation of the particular DISPERSION

Creation Script:

create table DISPERSIONS 
(
	DISPERSIONNBR  NUMBER not null,
	SHORTNAME      VARCHAR2(30) not null,
	HTMLNAME       VARCHAR2(25) null,
	DESCRIPTION    VARCHAR2(200) not null, 
	constraint DISPERSIONS_PK primary key (DISPERSIONNBR) 
)
alter table DISPERSIONS add constraint DISPERSIONS_UK unique (SHORTNAME)

Initial Data:

INSERT INTO dispersions VALUES 
(1,'NA','NA','Disperion Not applicable to this data'); 
INSERT INTO dispersions VALUES 
(2,'SD','SD','Standard Deviation'); 
INSERT INTO dispersions VALUES 
(3,'range','range','Difference between highest and lowest value');

ENDPOINTS

This table holds information on the endpoints measured

ENDPOINTS Column Explanation

ENDPOINTNBR - internal database key
SHORTNAME - summary name. Must be unique across all ENDPOINTSs
HTMLNAME - name used in HTML files
DESCRIPTION - more complete text explanation of the particular ENDPOINT

Creation Script:

create table ENDPOINTS 
(
	ENDPOINTNBR   NUMBER not null,
	SHORTNAME     VARCHAR2(30) not null,
	HTMLNAME      VARCHAR2(40) not null,
	DESCRIPTION   VARCHAR2(200) not null, 
	constraint ENDPOINTS_PK primary key (ENDPOINTNBR)
) 
alter table ENDPOINTS add constraint ENDPOINTS_UK unique (SHORTNAME)

Initial Data:

INSERT INTO endpoints VALUES 
(1, 'cell number','cell number','Number of cells at end 
of assay'); 
INSERT INTO endpoints VALUES 
(2, 'GI50','GI₅₀','Concentration required for 
50% inhibition of growth'); 
INSERT INTO endpoints VALUES 
(3, 'TGI','TGI','Concentration required for total 
inhibition of growth'); 
INSERT INTO endpoints VALUES 
(4, 'LC50','LC₅₀','Concentration required for 
50% cell kill'); 
INSERT INTO endpoints VALUES 
(5, 'IC50','IC₅₀','Concentration required for 
50% inhibition'); 
INSERT INTO endpoints VALUES 
(6, 'NA','NA','Enpoint not applicable to this data');
INSERT INTO endpoints VALUES
(7, 'EC50', 'EC₅₀','Concentration required for 
50% protection (Effective Concentration) ');
INSERT INTO endpoints VALUES
(8, 'GI%', 'GI%', 'Growth Inhibition Percentage');

DESCRIPTIVEDATA

This table stores a specific set of text results for a given TESTMASTER. It is intended to allow the possible text results to be restricted to a defined set so that problems associated with parsing different strings and deciding if they represent the same result can be avoided.

DESCRIPTIVEDATA Column Explanation

DESCRIPTIVECODENBR - internal database key
TESTMASTERNBR - internal database key for the TESTMASTER asociated with this set of DESCRIPTIVEDATA. The TESTMASTER record must already be in the database.
SHORTNAME - summary name. Must be unique across all DESCRIPTIVEDATAs with the same TESTMASTER
HTMLNAME - name used in HTML files
DESCRIPTION - more complete text explanation of the particular DESCRIPTIVEDATA

Creation Script:

create table DESCRIPTIVEDATA 
(
	DESCRIPTIVECODENBR  NUMBER not null,
	TESTMASTERNBR       NUMBER not null,
	SHORTNAME           VARCHAR2(30) not null,
	HTMLNAME            VARCHAR2(25) null,
	DESCRIPTION         VARCHAR2(200) not null, 
	constraint DESCRIPTIVEDATA_PK primary key (DESCRIPTIVECODENBR) 
)
alter table DESCRIPTIVEDATA 
        add constraint DESCRIPTIVEDATA_UK unique (TESTMASTERNBR,SHORTNAME)
alter table DESCRIPTIVEDATA
	add constraint TESTMASTERTEXTRESULTS_FK1 foreign key (TESTMASTERNBR)
	references TESTMASTER (TESTMASTERNBR) ON DELETE CASCADE;

No Initial Data

TESTRESULTS

This is the table that holds the actual results. There are four types of data that can be used:

floating point number
free text
restricted text (See DESCRIPTIVEDATA)
chemical structure

All of these or any subset can be used in a single record. The set used is specified in the RESULTTYPE column in TESTMASTER.

TESTRESULTS Column Explanation

TESTRESULTNBR - internal database key
TESTSUBSETNBR - internal database key for the TESTSUBSET record that this result falls under. This record must already exist in the database.
MEASUREMENT - floating point number result
MEASUREMENTUNITNBR - internal database key for the appropriate record in the UNIT table.
NBROFTESTS - number of tests summarized by this result.
DISPERSIONNBR - internal database key for the appropriate record in the DISPERSIONS table.
DISPERSIONVALUE - value of the dispersion measurement
TESTRESULT - free text result
PRIORITY - number to order similar measurements. Lower number is higher priority
ENDPOINT - internal database key for the appropriate record in the ENDPOINTS table.
DESCRIPTIVECODENBR - internal database key for the appropriate record in the DISCRIPTIVEDATA table.
STRUCTURENBR - internal database key for the appropriate record in the STRUCTURES table.

Creation Script:

create table TESTRESULTS 
(
	TESTRESULTNBR       NUMBER not null,
	TESTSUBSETNBR       NUMBER not null,
	MEASUREMENT         NUMBER(30,15) null,
	MEASUREMENTUNITNBR  NUMBER null,
	NBROFTESTS          NUMBER null,
	DISPERSIONNBR       NUMBER null,
	DISPERSIONVALUE     NUMBER(30,15) null,
	TEXTRESULT          VARCHAR2(1000) null,
	PRIORITY            NUMBER null,
	ENDPOINT            NUMBER null,
	DESCRIPTIVECODENBR  NUMBER null, 
	STRUCTURENBR        NUMBER null,
	constraint TESTRESULTS_PK primary key (TESTRESULTNBR)
)
create index TESTRESULTS_NI on TESTRESULTS (TESTSUBSETNBR ASC,TESTRESULTNBR ASC)
alter table TESTRESULTS
	add constraint DESCRIPTIVEDATA_FK1 foreign key (DESCRIPTIVECODENBR)
	references DESCRIPTIVEDATA (DESCRIPTIVECODENBR); 
alter table TESTRESULTS
	add constraint ERRORDISPERSIONSTESTRESULTS foreign key (DISPERSIONNBR)
	references DISPERSIONS (DISPERSIONNBR); 
alter table TESTRESULTS
	add constraint TESTRESULTS_FK1 foreign key (ENDPOINT)
	 references ENDPOINTS (ENDPOINTNBR); 
alter table TESTRESULTS
	add constraint TESTSUBSETTESTRESULTS foreign key (TESTSUBSETNBR)
	references TESTSUBSET (TESTSUBSETNBR) ON DELETE CASCADE; 
alter table TESTRESULTS
	add constraint UNITSTESTRESULTS1 foreign key (MEASUREMENTUNITNBR)
	references UNITS (UNITNBR);

No Initial Data

TESTCELLLINES

This table allows a cell line to be associated with a TESTRESULTS record.

TESTCELLLINES Column Explanation

TESTRESULTNBR - internal database key for associated TESTRESULTS record
CELLLINENBR - internal database key for the cell line
CELLPOSITION - a number that can be used to identify the position in the protocol that this cell line occupies.

Creation Script:

create table TESTCELLLINES 
(
	TESTRESULTNBR  NUMBER not null,
	CELLLINENBR    NUMBER not null,
	CELLPOSITION   NUMBER not null, 
        constraint TESTCELLLINES_PK primary key (TESTRESULTNBR, CELLLINENBR, CELLPOSITION)
)
alter table TESTCELLLINES
	add constraint TESTRESULTSTESTCELLLINES foreign key (TESTRESULTNBR)
	references TESTRESULTS (TESTRESULTNBR) ON DELETE CASCADE;

No Initial Data

CELLLINEIDSYSTEMS

This table lists the cell line identification systems known to the database.

CELLLINEIDSYSTEMS Column Explanation

CELLLINEIDSYSTEMNBR - internal database key for this table
SYSTEMNAME - name of the identification system
IDN1DEFINITION - definition of the IDN1 field in this system. NULL if not used.
IDN2DEFINITION - definition of the IDN2 field in this system. NULL if not used.
IDS1DEFINITION - definition of the IDS1 field in this system. NULL if not used.
IDS2DEFINITION - definition of the IDS2 field in this system. NULL if not used.
SYSTEMDESCRIPTION - description of this system

Creation Script:

create table CELLLINEIDSYSTEMS 
(
	CELLLINEIDSYSTEMNBR  NUMBER not null,
	SYSTEMNAME           VARCHAR2(50) not null,
	IDN1DEFINITION       VARCHAR2(30) null,
	IDN2DEFINITION       VARCHAR2(30) null,
	IDS1DEFINITION       VARCHAR2(30) null,
	IDS2DEFINITION       VARCHAR2(30) null,
	SYSTEMDESCRIPTION    VARCHAR2(100) not null, 
	constraint CELLLINEIDSYSTEMS_PK primary key (CELLLINEIDSYSTEMNBR) 
)
alter table CELLLINEIDSYSTEMS 
	add constraint CELLLINEIDSYSTEMS_UK unique (SYSTEMNAME)

Initial Data:

INSERT INTO CELLLINEIDSYSTEMS VALUES  
(3,'NCI Cancer','Panel Number','Cell Number','Panel Name','Cell Name',
'Panel number/Cell number Panel Name/Cell Name for NCI/DTP cancer cell lines'); 
INSERT INTO CELLLINEIDSYSTEMS VALUES  
(4,'NCI AIDS','Panel Number','Cell Number','Panel Name','Cell Name',
'Panel number/Cell number Panel Name/Cell Name for NCI/DTP AIDS cell lines');

TRANSLATECELLLINENBR

This table makes the association between internal database keys and externally meaningful cell line identifiers.

TRANSLATECELLLINENBR Column Explanation

CELLLINENBR - internal database key for a cell line
CELLLINEIDSYSTEMNBR - internal database key for the associated record in CELLLINEIDSYSTEMS.
IDN1 - value for the IDN1 field
IDN2 - value for the IDN2 field
IDS1 - value for the IDS1 field
IDS2 - value for the IDS2 field

Creation Script:

create table TRANSLATECELLLINENBR 
(
	CELLLINENBR          NUMBER not null,
	CELLLINEIDSYSTEMNBR  NUMBER not null,
	IDN1                 NUMBER null,
	IDN2                 NUMBER null,
	IDS1                 VARCHAR2(30) null,
	IDS2                 VARCHAR2(30) null, 
	constraint TRANSLATECELLLINENBR_PK primary key (CELLLINENBR, CELLLINEIDSYSTEMNBR) 
)
alter table TRANSLATECELLLINENBR 
       add constraint TRANSLATECELLLINENBR_LK unique (CELLLINEIDSYSTEMNBR,IDN1,IDN2,IDS1,IDS2)
alter table TRANSLATECELLLINENBR
       add constraint CELLLINEIDSYSTEMSCELLLINE foreign key (CELLLINEIDSYSTEMNBR)
       references CELLLINEIDSYSTEMS (CELLLINEIDSYSTEMNBR);

No Initial Data

TESTCOMPOUNDS

This table allows a compoun to be associated with a TESTRESULTS record.

TESTCOMPOUNDS Column Explanation

TESTRESULTNBR - internal database key for the TESTRESULT record this compound is associated with.
COMPOUNDNBR - internal database key for hte compound
COMPOUNDPOSITION - a number that can be used to indicate this compound's position in the protocol. For example, a protocol might specify the compound 1 is added at time 0 amd compound 2 is added at 48 hours.
CONCENTRATION - amount of compound used
UNITNBR - internal database key for the UNITS record identifying the unit used for the CONCENTRATION value.

Creation Script:

create table TESTCOMPOUNDS 
(
	TESTRESULTNBR     NUMBER not null,
	COMPOUNDNBR       NUMBER not null,
	COMPOUNDPOSITION  NUMBER not null,
	TESTMASTERNBR     NUMBER not null,
	CONCENTRATION     NUMBER(30,15) null,
	UNITNBR           NUMBER null, 
	constraint TESTCOMPOUNDS_PK primary key (TESTRESULTNBR, COMPOUNDNBR, COMPOUNDPOSITION)
)
create index TESTCOMPOUNDS_NI on TESTCOMPOUNDS (COMPOUNDNBR ASC,TESTMASTERNBR ASC)
alter table TESTCOMPOUNDS
	add constraint TESTRESULTSNBR2 foreign key (TESTRESULTNBR)
	references TESTRESULTS (TESTRESULTNBR) ON DELETE CASCADE; 
alter table TESTCOMPOUNDS
	add constraint UNITSTESTCOMPOUNDS foreign key (UNITNBR)
	references UNITS (UNITNBR);
alter table TESTCOMPOUNDS
	add constraint TESTRESULTSNBR2 foreign key (TESTRESULTNBR)
	references TESTRESULTS (TESTRESULTNBR) ON DELETE CASCADE;

No Initial Data

COMPOUNDIDSYSTEMS

This table lists the compound identification systems know to the database.

COMPOUNDIDSYSTEMS Column Explanation

COMPOUNDIDSYSTEMNR - internal database key for this identification system
SYSTEMNAME - name for this system
IDN1DEFINITION - definition for the IDN1 field
IDS1DEFINITION - definition for the IDS1 field
IDS2DEFINITION - definition for the IDS2 field
IDS3DEFINITION - definition for the IDS3 field
IDS4DEFINITION - definition for the IDS4 field
SYSTEMDESCRIPTION - text description of this identification system.

Creation Script:

create table COMPOUNDIDSYSTEMS 
(
	COMPOUNDIDSYSTEMNBR   NUMBER not null,
	SYSTEMNAME            VARCHAR2(50) not null,
	IDN1DEFINITION        VARCHAR2(30) null,
	IDS1DEFINITION        VARCHAR2(30) null,
	IDS2DEFINITION        VARCHAR2(30) null,
	IDS3DEFINITION        VARCHAR2(30) null,
	IDS4DEFINITION        VARCHAR2(30) null,
	SYSTEMDESCRIPTION     VARCHAR2(100) not null, 
        constraint COMPOUNDIDSYSTEMS_PK primary key (COMPOUNDIDSYSTEMNBR) 
)
alter table COMPOUNDIDSYSTEMS 
add constraint COMPOUNDIDSYSTEMS_UK unique (SYSTEMNAME)

Initial Data:

INSERT INTO COMPOUNDIDSYSTEMS VALUES  
(1,'NSC','NSC number',null,null,null,null,'NSC (with prefix S) from NCI/DTP'); 
INSERT INTO COMPOUNDIDSYSTEMS VALUES  
(2,'CAS','CAS number (integer value)','CAS number (string value)',null,null,null,'CAS Registry Number');
INSERT INTO COMPOUNDIDSYSTEMS VALUES  
(4,'Natural NSCs','NSC','Prefix','Phylum','Family',null,'Natural Compounds (NSC, Prefix, Phylum, Family)');

TRANSLATECOMPOUNDNBR

This table makes the association between internal database keys and externally meaningful compound identifiers.

TRANSLATECOMPOUNDNBR Column Explanation

COMPOUNDNBR - internal database key for a compound
COMPOUNDSYSTEMNBR - internal database key for the COMPOUNDIDSYSTEMS record.
IDN1 - number field.
IDS1 - string field.
IDS2 - string field.
IDS3 - string field.
IDS4 - string field.

Creation Script:

create table TRANSLATECOMPOUNDNBR 
(
	COMPOUNDNBR          NUMBER not null,
	COMPOUNDIDSYSTEMNBR  NUMBER not null,	
	IDN1                 NUMBER null,
	IDS1                 VARCHAR2(30) null,
	IDS2                 VARCHAR2(40) null,
	IDS3                 VARCHAR2(40) null,
	IDS4                 VARCHAR2(30) null, 
	constraint TRANSLATECOMPOUNDNBR_PK primary key (COMPOUNDNBR, COMPOUNDIDSYSTEMNBR) 
)
create index TRANSLATECOMPOUNDNBR_IDN1 on TRANSLATECOMPOUNDNBR (IDN1 ASC)
create index TRANSLATECOMPOUNDNBR_IDS1 on TRANSLATECOMPOUNDNBR (IDS1 ASC)
alter table TRANSLATECOMPOUNDNBR
	add constraint COMPOUNDIDSYSTEMSCOMPOUND foreign key (COMPOUNDIDSYSTEMNBR)
	references COMPOUNDIDSYSTEMS (COMPOUNDIDSYSTEMNBR);

No Initial Data

CHEMICALELEMENTS

THis table holds information about individual elements in the periodic table

CHEMICALELEMENTS Column Explanation

atomic_number - the atomic number of the element, also the internal database key
element_name - name of the element
atomic_symbol - two letter atomic symbol
nat_mass - mass (based on natural abundance)
sanss_code - number code in the SANSS tables in the old DTP/DIS

Creation Script:

CREATE TABLE CHEMICALELEMENTS 
(
	atomic_number     number not null,
	element_name      VARCHAR2(12) not null,
	atomic_symbol     varchar2(2) not null,
	nat_mass          NUMBER not null,
	sanss_code        number not null,
	CONSTRAINT chemelement_pk PRIMARY KEY (atomic_number )
)

Initial Data

INSERT INTO chemicalelements VALUES (0,'Unknown','??',0.0,33);
INSERT INTO chemicalelements VALUES (1,'Hydrogen','H',1.00797,86);
INSERT INTO chemicalelements VALUES (2,'Helium','He',4.0026,84);
INSERT INTO chemicalelements VALUES (3,'Lithium','Li',6.939,28);
INSERT INTO chemicalelements VALUES (4,'Beryllium','Be',9.0122,44);
INSERT INTO chemicalelements VALUES (5,'Boron','B',10.811,14);
INSERT INTO chemicalelements VALUES (6,'Carbon','C',12.01115,1);
INSERT INTO chemicalelements VALUES (7,'Nitrogen','N',14.0067,3);
INSERT INTO chemicalelements VALUES (8,'Oxygen','O',15.9994,2);
INSERT INTO chemicalelements VALUES (9,'Fluorine','F',18.9984,6);
INSERT INTO chemicalelements VALUES (10,'Neon','Ne',20.183,48); 
INSERT INTO chemicalelements VALUES (11,'Sodium','Na',22.9898,30);
INSERT INTO chemicalelements VALUES (12,'Magnesium','Mg',24.312,24);
INSERT INTO chemicalelements VALUES (13,'Aluminum','Al',26.9815,18);
INSERT INTO chemicalelements VALUES (14,'Silicon','Si',28.086,10);
INSERT INTO chemicalelements VALUES (15,'Phosphorus','P',30.9378,7);
INSERT INTO chemicalelements VALUES (16,'Sulfur','S',32.064,4);
INSERT INTO chemicalelements VALUES (17,'Chlorine','Cl',35.453,5);
INSERT INTO chemicalelements VALUES (18,'Argon','Ar',39.948,27);
INSERT INTO chemicalelements VALUES (19,'Potassium','K',39.102,29);
INSERT INTO chemicalelements VALUES (20,'Calcium','Ca',40.08,20);
INSERT INTO chemicalelements VALUES (21,'Scandium','Sc',44.956,88);
INSERT INTO chemicalelements VALUES (22,'Titanium','Ti',47.90,76);
INSERT INTO chemicalelements VALUES (23,'Vanadium','V',50.942,21);
INSERT INTO chemicalelements VALUES (24,'Chromium','Cr',51.996,52);
INSERT INTO chemicalelements VALUES (25,'Manganese','Mn',54.9380,54);
INSERT INTO chemicalelements VALUES (26,'Iron','Fe',55.847,23);
INSERT INTO chemicalelements VALUES (27,'Cobalt','Co',58.9332,32);
INSERT INTO chemicalelements VALUES (28,'Nickle','Ni',58.71,47);
INSERT INTO chemicalelements VALUES (29,'Copper','Cu',63.54,22);
INSERT INTO chemicalelements VALUES (30,'Zinc','Zn',65.37,34);
INSERT INTO chemicalelements VALUES (31,'Gallium','Ga',69.72,90);
INSERT INTO chemicalelements VALUES (32,'Germanium','Ge',72.59,87);
INSERT INTO chemicalelements VALUES (33,'Arsenic','As',74.9216,11);
INSERT INTO chemicalelements VALUES (34,'Selenium','Se',78.96,26);
INSERT INTO chemicalelements VALUES (35,'Bromine','Br',79.909,8);
INSERT INTO chemicalelements VALUES (36,'Krypton','Kr',83.80,61);
INSERT INTO chemicalelements VALUES (37,'Rubidium','Rb',85.47,95);
INSERT INTO chemicalelements VALUES (38,'Strontium','Sr',87.62,83);
INSERT INTO chemicalelements VALUES (39,'Yttrium','Y',88.905,69);
INSERT INTO chemicalelements VALUES (40,'Zirconium','Zr',91.22,66);
INSERT INTO chemicalelements VALUES (41,'Niobium','Nb',92.906,50);
INSERT INTO chemicalelements VALUES (42,'Molybdenum','Mo',95.94,53);
INSERT INTO chemicalelements VALUES (43,'Technetium','Tc',99,79);
INSERT INTO chemicalelements VALUES (44,'Ruthenium','Ru',101.07,91);
INSERT INTO chemicalelements VALUES (45,'Rhodium','Rh',102.905,93);
INSERT INTO chemicalelements VALUES (46,'Palladium','Pd',106.4,102);
INSERT INTO chemicalelements VALUES (47,'Silver','Ag',107.870,15);
INSERT INTO chemicalelements VALUES (48,'Cadmium','Cd',112.40,89);
INSERT INTO chemicalelements VALUES (49,'Indium','In',114.82,64);
INSERT INTO chemicalelements VALUES (50,'Tin','Sn',118.69,17);
INSERT INTO chemicalelements VALUES (51,'Antimony','Sb',121.75,19);
INSERT INTO chemicalelements VALUES (52,'Tellurium','Te',127.60,78);
INSERT INTO chemicalelements VALUES (53,'Iodine','I',126.9044,9);
INSERT INTO chemicalelements VALUES (54,'Xenon','Xe',131.30,70);
INSERT INTO chemicalelements VALUES (55,'Cesium','Cs',132.905,51);
INSERT INTO chemicalelements VALUES (56,'Barium','Ba',137.34,55);
INSERT INTO chemicalelements VALUES (57,'Lanthanum','La',138.91,60);
INSERT INTO chemicalelements VALUES (72,'Halfnium','Hf',178.149,72);
INSERT INTO chemicalelements VALUES (73,'Tantalum','Ta',180.948,81);
INSERT INTO chemicalelements VALUES (74,'Tungsten','W',183.85,35);
INSERT INTO chemicalelements VALUES (75,'Rhenium','Re',186.2,94);
INSERT INTO chemicalelements VALUES (76,'Osmium','Os',190.2,106);
INSERT INTO chemicalelements VALUES (77,'Iridium','Ir',192.2,63);
INSERT INTO chemicalelements VALUES (78,'Platinum','Pt',195.09,98);
INSERT INTO chemicalelements VALUES (79,'Gold','Au',196.967,38);
INSERT INTO chemicalelements VALUES (80,'Mercury','Hg',200.59,12);
INSERT INTO chemicalelements VALUES (81,'Thallium','Tl',204.37,75);
INSERT INTO chemicalelements VALUES (82,'Lead','Pb',207.19,25);
INSERT INTO chemicalelements VALUES (83,'Bismuth','Bi',208.980,16);
INSERT INTO chemicalelements VALUES (84,'Polonium','Po',210,100);
INSERT INTO chemicalelements VALUES (85,'Astatine','At',210,62);
INSERT INTO chemicalelements VALUES (86,'Radon','Rn',222,92);
INSERT INTO chemicalelements VALUES (87,'Francium','Fr',223,103);
INSERT INTO chemicalelements VALUES (88,'Radium','Ra',226,96);
INSERT INTO chemicalelements VALUES (89,'Actinium','Ac',227,13);
INSERT INTO chemicalelements VALUES (58,'Cerium','Ce',140.12,71);
INSERT INTO chemicalelements VALUES (59,'Praseodymium','Pr',140.907,99);
INSERT INTO chemicalelements VALUES (60,'Neodymium','Nd',144.24,49);
INSERT INTO chemicalelements VALUES (61,'Promethium','Pm',147,101);
INSERT INTO chemicalelements VALUES (62,'Samarium','Sm',150.35,85);
INSERT INTO chemicalelements VALUES (63,'Europium','Eu',151.96,39);
INSERT INTO chemicalelements VALUES (64,'Gadolinium','Gd',157.25,31);
INSERT INTO chemicalelements VALUES (65,'Terbium','Tb',158.924,80);
INSERT INTO chemicalelements VALUES (66,'Dysprosium','Dy',162.50,42);
INSERT INTO chemicalelements VALUES (67,'Holmium','Ho',164.930,67);
INSERT INTO chemicalelements VALUES (68,'Erbium','Er',167.26,41);
INSERT INTO chemicalelements VALUES (69,'Thulium','Tm',168.934,74);
INSERT INTO chemicalelements VALUES (70,'Ytterbium','Yb',173.04,68);
INSERT INTO chemicalelements VALUES (71,'Lutetium','Lu',174.97,57);
INSERT INTO chemicalelements VALUES (90,'Thorium','Th',232.038,77);
INSERT INTO chemicalelements VALUES (91,'Protactinium','Pa',231,104);
INSERT INTO chemicalelements VALUES (92,'Uranium','U',238.03,73);
INSERT INTO chemicalelements VALUES (93,'Neptunium','Np',237,45);
INSERT INTO chemicalelements VALUES (94,'Plutonium','Pu',242,97);
INSERT INTO chemicalelements VALUES (95,'Americium','Am',243,36);
INSERT INTO chemicalelements VALUES (96,'Curium','Cm',247,59);
INSERT INTO chemicalelements VALUES (97,'Berkelium','Bk',247,37);
INSERT INTO chemicalelements VALUES (98,'Californium','Cf',249,65);
INSERT INTO chemicalelements VALUES (99,'Einsteinium','Es',254,40);
INSERT INTO chemicalelements VALUES (100,'Fermium','Fm',253,105);
INSERT INTO chemicalelements VALUES (101,'Mendelevium','Md',256,56);
INSERT INTO chemicalelements VALUES (102,'Nobelium','No',253,46);
INSERT INTO chemicalelements VALUES (103,'Lawrencium','Lw',257,0);

COORDINATETYPE

A table to hold values for types of chemical structure coordinates

COORDINATETYPE Column Explanation

coordintype - internal database key
shortname - summary name
longname - more detailed text description

Creation Script:

CREATE TABLE COORDINATETYPE 
(
	coordintype     number not null,
	shortname       VARCHAR2(10) not null,
	longname        VARCHAR2(50) not null,
	CONSTRAINT coordin_key  PRIMARY KEY (coordintype )
)

Initial Data:

INSERT INTO coordinatetype VALUES (0,'No Struct','Null Structure, No coordinates');
INSERT INTO coordinatetype VALUES (1,'Connection','Connection table information only');
INSERT INTO coordinatetype VALUES (2,'2D','2D Coordinates');
INSERT INTO coordinatetype VALUES (3,'3D','3D Coordinates');

STRUCTURES

This table is the main header for chemical structures. A structure can consist of substructures.

STRUCTURES Column Explanation

STRUCTURENBR - internal database key, provides link to TESTRESULTS table.
SUBSTRUCTNBR - substructure number, used to distinguish different parts of a complex structure
COMPOUNDNBR - internal database key, provides link to TRANSLATECOMPOUNDNBR table.
TESTMASTERNBR - internal database key, provides link to the appropriate TESTMASTER record.
TESTSUBSETNBR - internal database key, provides link to the appropriate TESTSUBSET record.
COORDINTYPE - internal database key, provides link to the appropriate COORDINATETYPE record.
NUMATOMS - number of atom records
NUMBONDS - number of bond records
CONVENTION - string that represents the format convention for the structure that was loaded (MDLMol for example)

Creation Script:

create table STRUCTURES 
(
	STRUCTURENBR     NUMBER not null,
	SUBSTRUCTNBR     NUMBER not null,
	COMPOUNDNBR      NUMBER null,
	TESTMASTERNBR    NUMBER null,
	TESTSUBSETNBR    NUMBER null,
	COORDINTYPE      NUMBER null,
	NUMATOMS         NUMBER null,
	NUMBONDS         NUMBER null,
	CONVENTION       VARCHAR2(15) null, 
	constraint STRUC_KEY primary key (STRUCTURENBR, SUBSTRUCTNBR)
)
alter table structures 
	add constraint coordin_struc foreign key (coordintype)
	references coordinatetype (coordintype) ; 
alter table STRUCTURES
	add constraint ATOM_DCODE_FK foreign key (TESTSUBSETNBR)
	references TESTSUBSET (TESTSUBSETNBR); 
alter table STRUCTURES
	add constraint ATOM_TSTMASTER foreign key (TESTMASTERNBR)
	references TESTMASTER (TESTMASTERNBR); 
alter table STRUCTURES
	add constraint COORDIN_STRUC foreign key (COORDINTYPE)
	 references COORDINATETYPE (COORDINTYPE);

No initial Data

ATOMDATA

THis table holds data for an individual atom in a chemical structure

ATOMDATA Column Explanation

STRUCTURENBR - internal database key
SUBSTRUCNBR - internal database key. The STRUCTURENBR, SUBSTRUCTNBR pair must refer to an existing record in STRUCTURES
ATOMIDNUM - internal database key for this atom
ATOMIC_NUMBER - internal database key, provides link to CHEMICALELEMENTS table
XVAL - value for x coordinate
YVAL - value for y coordinate
ZVAL - value for z coordinate
ATOMLABEL - string to indentify this particular atom

Creation Script:

create table ATOMDATA 
(
	STRUCTURENBR   NUMBER not null,
	SUBSTRUCTNBR   NUMBER not null,
	ATOMIDNUM      NUMBER not null,
	ATOMIC_NUMBER  NUMBER null,
	XVAL           NUMBER null,
	YVAL           NUMBER null,
	ZVAL           NUMBER null,
	ATOMLABEL      VARCHAR2(10) null, 
	constraint ATOM_DATA_KEY primary key (STRUCTURENBR, SUBSTRUCTNBR, ATOMIDNUM) 
)
alter table ATOMDATA
	add constraint ATOM_FK foreign key (STRUCTURENBR,SUBSTRUCTNBR)
	references STRUCTURES (STRUCTURENBR,SUBSTRUCTNBR); 
alter table ATOMDATA
	add constraint ATOM_NUMBER_FK foreign key (ATOMIC_NUMBER)
	references CHEMICALELEMENTS (ATOMIC_NUMBER);

No Initial Data

FCHARGEDATA

THis table stores information on formal charges on atoms

FCHARGEDATA Column Explanation

STRUCTURENBR - internal database key
SUBSTRUCNBR - internal database key.
ATOMIDNUM - internal database key. The STRUCTURENBR, SUBSTRUCTNBR, ATOMIDNUM triple must refer to an exisiting record in ATOMDATA
CHARGEID - identifier to allow for multiple charge specifications
FORMALCHARGE - the formal charge on this atom

Creation Script:

create table FCHARGEDATA 
(
	STRUCTURENBR   NUMBER not null,
	SUBSTRUCTNBR   NUMBER not null,
	ATOMIDNUM      NUMBER not null,
	CHARGEID       NUMBER not null,
	FORMALCHARGE   NUMBER null, 
	constraint FCHARGE_DATA_KEY primary key (STRUCTURENBR, SUBSTRUCTNBR, ATOMIDNUM, CHARGEID) 
)
alter table FCHARGEDATA
	add constraint FCHARGE_FK foreign key (STRUCTURENBR,SUBSTRUCTNBR,ATOMIDNUM)
	references ATOMDATA (STRUCTURENBR,SUBSTRUCTNBR,ATOMIDNUM);

No Initial Data

MASSDATA

MASSDATA Column Explanation

STRUCTURENBR - internal database key
SUBSTRUCNBR - internal database key.
ATOMIDNUM - internal database key. The STRUCTURENBR, SUBSTRUCTNBR, ATOMIDNUM triple must refer to an exisiting record in ATOMDATA
ATOMMASS - atomic mass of this atom. If no MASSDATA record exists, the atomic mass is assumed to be the value stored for this element in CHEMICALELEMENTS

Creation Script:

create table MASSDATA 
(
	STRUCTURENBR   NUMBER not null,
	SUBSTRUCTNBR   NUMBER not null,
	ATOMIDNUM      NUMBER not null,
	ATOMMASS       NUMBER null, 
	constraint MASS_DATA_KEY primary key (STRUCTURENBR, SUBSTRUCTNBR, ATOMIDNUM) 
)
alter table MASSDATA
	add constraint MASS_FK foreign key (STRUCTURENBR,SUBSTRUCTNBR,ATOMIDNUM)
	 references ATOMDATA (STRUCTURENBR,SUBSTRUCTNBR,ATOMIDNUM);

No Initial Data

ATOMPARITY

This table stores information on stereochemical descriptors refering to a particular atom.

ATOMPARITY Column Explanation

STRUCTURENBR - internal database key
SUBSTRUCNBR - internal database key.
ATOMIDNUM - internal database key. The STRUCTURENBR, SUBSTRUCTNBR, ATOMIDNUM triple must refer to an exisiting record in ATOMDATA
FLOATVAL - floating point representation
SYMBOLVAL - string representation
PARITYTYPE - parity description convention

Creation Script:

create table ATOMPARITY 
(
	STRUCTURENBR   NUMBER not null,
	SUBSTRUCTNBR   NUMBER not null,
	ATOMIDNUM      NUMBER not null,
	FLOATVAL       NUMBER null,
	SYMBOLVAL      VARCHAR2(10) null,
	PARITYTYPE     VARCHAR2(10) null, 
	constraint ATOMP_KEY primary key (STRUCTURENBR, SUBSTRUCTNBR, ATOMIDNUM)
)
alter table ATOMPARITY
	add constraint ATOMP_FK foreign key (STRUCTURENBR,SUBSTRUCTNBR,ATOMIDNUM)
	 references ATOMDATA (STRUCTURENBR,SUBSTRUCTNBR,ATOMIDNUM);

No Intital Data

BONDDATA

This table holds information on individual bonds in a chemical structure

BONDDATA Column Explanation

STRUCTURENBR - internal database key
SUBSTRUCNBR - internal database key. The STRUCTURENBR, SUBSTRUCTNBR pair must refer to an existing record in STRUCTURES
BONDIDNUM - intenral database key to identify this bond
BONDORDER - string representation of the bond order
BONDLENGTH - lengh of this bond
ATOMID1 - internal database key for first atom in this bond
ATOMID2 - internal database key for second atom in this bond
BONDLABEL - test label for this bond

Creation Script:

create table BONDDATA 
(
	STRUCTURENBR    NUMBER not null,
	SUBSTRUCTNBR    NUMBER not null,
	BONDIDNUM       NUMBER not null,
	BONDORDER       VARCHAR2(10) null,
	BONDLENGTH      NUMBER null,
	ATOMID1         NUMBER null,
	ATOMID2         NUMBER null,
	BONDLABEL       VARCHAR2(10) null, 
	constraint BOND_DATA_KEY primary key (STRUCTURENBR, SUBSTRUCTNBR, BONDIDNUM) 
)
alter table BONDDATA
	add constraint ATOM1_FK foreign key (STRUCTURENBR,SUBSTRUCTNBR,ATOMID1)
	references ATOMDATA (STRUCTURENBR,SUBSTRUCTNBR,ATOMIDNUM); 
alter table BONDDATA
	add constraint ATOM2_FK foreign key (STRUCTURENBR,SUBSTRUCTNBR,ATOMID2)
	references ATOMDATA (STRUCTURENBR,SUBSTRUCTNBR,ATOMIDNUM);

No Initial Data

BONDPARITY

This table honds information on stereochemcial descriptors refering to a particular bond

BONDPARITY Column Explanation

STRUCTURENBR - internal database key
SUBSTRUCNBR - internal database key.
BONDIDNUM - internal database key. The STRUCTURENBR, SUBSTRUCTNBR, BONDIDNUM triple must refer to an exisiting record in BONDDATA
SYMBOLPARITY - string representation of the stereochemical descriptor
PARITYTYPE - convention for this stereochemical descriptor
PARITYVALUE - string value for this descriptor

Creation Script:

create table BONDPARITY 
(
	STRUCTURENBR    NUMBER not null,
	SUBSTRUCTNBR    NUMBER not null,
	BONDIDNUM       NUMBER not null,
	SYMBOLPARITY    VARCHAR2(10) null,
	PARITYTYPE      VARCHAR2(10) null,
	PARITYVALUE     VARCHAR2(10) null, 
	constraint BONDPARITY_KEY primary key (STRUCTURENBR, SUBSTRUCTNBR, BONDIDNUM) 
)
alter table BONDPARITY
	add constraint BONDPARITY_FK foreign key (STRUCTURENBR,SUBSTRUCTNBR,BONDIDNUM)
	references BONDDATA (STRUCTURENBR,SUBSTRUCTNBR,BONDIDNUM);

No Initial Data

Sequences

Sequences used to create new database keys.

create sequence CELLLINEID_SEQ start with   5;
create sequence COMPOUNDID_SEQ start with   5;
create sequence DATACLASS_SEQ start with    5;
create sequence DATATYPE_SEQ start with    12;
create sequence DESCRIPTIVE_SEQ start with  1;
create sequence DISPERSION_SEQ start with   4;
create sequence ENDPOINT_SEQ start with     9;
create sequence STRUCTURENBRSEQ start with  1;
create sequence TESTMASTER_SEQ start with   1; 
create sequence TESTRESULT_SEQ start with   1;
create sequence TESTSUBSET_SEQ start with   1;
create sequence UNIT_SEQ start with  12;