Additional File 2 This file provides the BioWarehouse schema as an SQL definition that can be converted to either Oracle or MySQL format using a tool provided within the BioWarehouse distribution. @- This file is preprocessed by translate-schema.lisp. @- NOTE: @- comments do not appear in preprocessor output file. @- Changes from oracle schema in constructing generic template: @- Change '--SD' to '-- SD' since MySQL didnt like the former syntax. @- Prefixed foreign key constraints with FKEY macro, so it can expand to '--' @- for MySQL until foreign keys are worked out in the schema. @- Added TABLETYPE macro to end of each table (MySQL - "TYPE=INNODB") @- Different table types enable transactions, foreign key support, etc. @- The schema template file uses macro symbols (those beginning with @) @- to specify DBMS-dependent syntax. @- @- Abstract data types: @- @wid: a Warehouse Identifier (a number that is unique throughout the Warehouse) @- @int16, @int32, @int64: signed integral types using 16, 32, and 64 bits resp. @- @real32, @real64: signed floating point types using 32, and 64 bits resp. @- @number: any number for which exact precision is preserved. @- @varchar(nnn): a varying-length string of length <= nnn. @- @string10, @string16, @string32: a varying-length string @- of length <= 2047, 65535, 4294967295 resp. @- @byte32: binary data of length <= 4294967295 bytes. @- @datetime: a date and/or a time. @- @- Other macro symbols: @- @widsequence: Defines a SEQUENCE used to generate WIDs. @- @initialwid: Defines starting value of the above SEQUENCE. @- @index-tablespace: Defines a TABLESPACE in which an INDEX resides. @- @using-index: Defines a TABLESPACE in which an INDEX referenced in a CONSTRAINT resides. @- @tabletype: Specifies a TABLE type (MySQL supports several). @- @autoinc: Specifies the AUTO-INCREMENT property for a numerical row. @- @index10: Marks INDEXes that use long strings. @- The schema template file is subject to certain conventions, for the use of @- our HTML documentation generator: @- o Comment lines directly above a create table are assumed to be @- a description of that table; @- o Comments on the same line (or on a following line with no SQL @- code before the start comment `--') are assumed to describe @- the column of the table; @- o Constraints must be within, and at the end, of a create table @- statement, not following it; @- o Create index statements must follow the table they refer to; @- o For de-normalized tables, to allow the documentation tool to @- pick up the reference, insert a standard syntax constraint @- line with the special comment start `-- SD ' (SD = SchemaDoc). @- These will be ignored by the database, but interpreted by the @- Schemadoc tool. @- ----------------------------------------------------------------------------- @- Tables that are used to define the Warehouse contents and its current state. @- ----------------------------------------------------------------------------- -- Describes this version of the BioSPICE Warehouse. -- This table contains exactly one row. -- CREATE TABLE Warehouse ( Version @number NOT NULL, -- Numbered version of the warehouse schema that is loaded LoadDate @datetime NOT NULL, -- Date that this schema was loaded MaxSpecialWID @wid NOT NULL, -- Maximum value allowed for SpecialWIDTable.PreviousWID / specialWID sequence Description @string10 -- Free-form text as desired by the warehouse maintainers ) @tabletype; INSERT INTO Warehouse (Version, LoadDate, MaxSpecialWID) VALUES (3.0, @now, 999); -- Warehouse ID (WID) allocation -- -- Generate a unlimited sequence of unique numbers to do the bookkeeping for -- Warehouse ID (WID) numbers, which serve as keys for all Warehouse entities. -- WIDs 0 and 1 are reserved and not allocated. -- @widsequence -- To allow for compatibility with RDBMS systems that do not support -- sequences, this table may be used to store the last used WID. -- MySQL dataset loaders use the following protocol for WID assignment: -- DELETE FROM WIDTable; -- INSERT INTO WIDTable VALUES (); -- SELECT last_insert_id(); -- It may also be used in RDBMS systems that do not support auto-increment -- of a counter; however additional protocol like table locking may be -- needed to assure the integrity of WIDs during concurrent assignment. -- This table contains exactly one row at any given time. -- CREATE TABLE WIDTable ( PreviousWID @wid @autoinc NOT NULL, -- Last-used Warehouse identifier. -- CONSTRAINT PK_WIDTable PRIMARY KEY (PreviousWID) @using-index ) @tabletype; INSERT INTO WIDTable (PreviousWID) values (@maxspecialwid); -- Generate a finite sequence of WIDs for use as special Warehouse entities, -- including DataSet WIDs. These are small integers. -- @specialwidsequence -- Special WIDs are low-valued WIDs, assigned to globally and frequently used -- WIDs within a dataset, such as its DataSetWID. They are both more -- mnemonic, and potentially more efficient to store. Warehouse.MaxSpecialWID -- delimits special and regular WIDs. -- Analogous to WIDTable, this table is used to allocate special WIDs. -- MySQL dataset loaders use the following protocol for WID assignment: -- DELETE FROM SpecialWIDTable; -- INSERT INTO SpecialWIDTable VALUES (); -- SELECT last_insert_id(); -- This table contains exactly one row at any given time. -- CREATE TABLE SpecialWIDTable ( PreviousWID @wid @autoinc NOT NULL, -- Last-used special WID CONSTRAINT PK_SpecialWIDTable PRIMARY KEY (PreviousWID) @using-index ) @tabletype; INSERT INTO SpecialWIDTable (PreviousWID) values (1); -- This table defines enumerated types used within the warehouse. Essentially, -- enumerated types correspond to small controlled vocabularies used within -- one or more warehouse tables. For example, the warehouse table Feature has -- a column called Type that defines the type of a feature within an amino-acid -- sequence. Examples types might be PHOS-RESIDUE and GLY-RESIDUE, corresponding -- to residues in a protein that are phosphorylated and glycosylated, respectively. -- Those two enumerated types would be entered into two rows of this Enumeration -- table. Note that in some cases the values actually stored in the warehouse -- table are numbers, which are stored in the Value field of the Enumeration table -- as the string form of that number. -- CREATE TABLE Enumeration ( TableName @varchar(50) NOT NULL, -- Table this enumeration is used in ColumnName @varchar(50) NOT NULL, -- Column this enumeration is used in Value @varchar(50) NOT NULL, -- The enumeration value Meaning @string10 -- A comment describing its meaning ) @tabletype; -- Table whose rows define each dataset currently loaded into the warehouse. -- CREATE TABLE DataSet ( WID @wid NOT NULL, -- Warehouse identifier for this entry. Name @varchar(255) NOT NULL, -- Name of the database. Version @varchar(50), -- Version of the database described by this warehouse dataset ReleaseDate @varchar(50), -- Date that the original database was first released. LoadDate @datetime NOT NULL, -- Date that the load of this data set into the warehouse began. ChangeDate @datetime, -- Date of the last change to this dataset, or the date the load -- completed if no changes have occurred. NULL means that the -- is currently loading, or a fatal error occurred on the load. -- NOTE: This feature is not supported by most loaders until version 4.0. HomeURL @varchar(255), -- Web address of the home page of the original database. QueryURL @varchar(255), -- A URL at which we can retrieve objects in this database via the WWW -- by substituting the unique ID of the object we wish to retrieve for the -- string "%s" in the query URL. An example URL is: -- http://database.university.edu/get-object/5050%s -- CONSTRAINT PK_DataSet1 PRIMARY KEY (WID) @using-index ) @tabletype; -- Defines metadata describing a warehouse object. -- Every warehouse object (that is, anything with a WID column) must have an associated Entry row. -- CREATE TABLE Entry ( OtherWID @wid NOT NULL, -- Warehouse ID of the entity InsertDate @datetime NOT NULL, -- Date entity was stored in the warehouse CreationDate @datetime, -- Date entity was created in its source dataset ModifiedDate @datetime, -- Date that the entry was last modified, either -- in the warehouse or in the source dataset LoadError CHAR(1) NOT NULL, -- 'T' if an error (eg. parse error on some field of the entry) -- occured during loading of this entry into the warehouse, else 'F'. LineNumber @int32, -- Either line number or entry number from dataset source file ErrorMessage @string10, DatasetWID @wid NOT NULL @fkeycomma -- The dataset the entity is from -- @FKEY CONSTRAINT FK_Entry FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE -- SD CONSTRAINT FK_ENTRY1 FOREIGN KEY (OtherWID) REFERENCES Chemical(WID) -- SD CONSTRAINT FK_ENTRY2 FOREIGN KEY (OtherWID) REFERENCES Element(WID) -- SD CONSTRAINT FK_ENTRY3 FOREIGN KEY (OtherWID) REFERENCES Reaction(WID) -- SD CONSTRAINT FK_ENTRY4 FOREIGN KEY (OtherWID) REFERENCES Protein(WID) -- SD CONSTRAINT FK_ENTRY5 FOREIGN KEY (OtherWID) REFERENCES Feature(WID) -- SD CONSTRAINT FK_ENTRY6 FOREIGN KEY (OtherWID) REFERENCES Function(WID) -- SD CONSTRAINT FK_ENTRY7 FOREIGN KEY (OtherWID) REFERENCES EnzymaticReaction(WID) -- SD CONSTRAINT FK_ENTRY8 FOREIGN KEY (OtherWID) REFERENCES Gene(WID) -- SD CONSTRAINT FK_ENTRY9 FOREIGN KEY (OtherWID) REFERENCES BioSource(WID) -- SD CONSTRAINT FK_ENTRY10 FOREIGN KEY (OtherWID) REFERENCES Pathway(WID) -- SD CONSTRAINT FK_ENTRY11 FOREIGN KEY (OtherWID) REFERENCES NucleicAcid(WID) -- SD CONSTRAINT FK_ENTRY12 FOREIGN KEY (OtherWID) REFERENCES Term(WID) -- SD CONSTRAINT FK_ENTRY13 FOREIGN KEY (OtherWID) REFERENCES Support(WID) -- SD CONSTRAINT FK_ENTRY14 FOREIGN KEY (OtherWID) REFERENCES Citation(WID) -- SD CONSTRAINT FK_ENTRY15 FOREIGN KEY (OtherWID) REFERENCES Division(WID) -- SD CONSTRAINT FK_ENTRY16 FOREIGN KEY (OtherWID) REFERENCES GeneticCode(WID) -- SD CONSTRAINT FK_ENTRY17 FOREIGN KEY (OtherWID) REFERENCES Taxon(WID) ) @tabletype; @- ----------------------------------------------------------------------------- @- Tables that contain universally- or widely-accepted scientific data. @- These tables are independent of any dataset. @- ----------------------------------------------------------------------------- -- The periodic table of elements -- CREATE TABLE Element ( WID @wid NOT NULL, -- WID of this element Name @varchar(15) NOT NULL, -- Long name (e.g. 'Oxygen') ElementSymbol @varchar(2) NOT NULL, -- Symbol (e.g. 'O') AtomicWeight @real32 NOT NULL, -- Weighted average weight (e.g. 16.x) AtomicNumber @int16 NOT NULL, -- Number of protons -- CONSTRAINT PK_Element PRIMARY KEY (WID) @using-index ) @tabletype; -- Defines one or more valences for each element. -- CREATE TABLE Valence ( OtherWID @wid NOT NULL, -- WID of the element Valence @int16 NOT NULL @fkeycomma -- Number of valence electrons -- @FKEY CONSTRAINT FK_Valence FOREIGN KEY (OtherWID) REFERENCES Element(WID) ON DELETE CASCADE ) @tabletype; @- ----------------------------------------------------------------------------- @- Object tables. These represent the principal objects in the Warehouse. @- All object tables have a WID column that is globally unique identifier, @- and a DataSetWID column that refers to the DataSet row of the dataset that @- loaded the object. @- ----------------------------------------------------------------------------- -- Defines small molecular weight chemical compounds. -- CREATE TABLE Chemical ( WID @wid NOT NULL, -- Warehouse idetification number for this chemical Name @varchar(255) NOT NULL, -- Common name of the chemical BeilsteinName @varchar(50), -- The Beilstein name of this chemical SystematicName @varchar(255), -- The systematic name for this chemical CAS @varchar(50), -- The Chemical Abstract Service number Charge @int16, -- Charge of the chemical EmpiricalFormula @varchar(50), -- The empirical formula of this chemical -- Format: { ElementSymbol Integer }+ -- E.g. 'H2O1', 'C2H6O1', 'Na1Cl1'. MolecularWeightCalc @real32, -- Molecular weight calculated from chemical formula. -- Units: Daltons. MolecularWeightExp @real32, -- Molecular Weight determined through experimentation. Units: Daltons. OctH2OPartitionCoeff @varchar(50), -- Octanol-water-partition coefficient PKA1 @real32, -- First ionization potential PKA2 @real32, -- Second ionization potential PKA3 @real32, -- Third ionization potential WaterSolubility CHAR(1), -- 'T' if soluble in water, else 'F' Smiles @varchar(255), -- Ascii representation of the chemical structure -- Simplified Molecular Input Line Entry System -- http://www.epa.gov/medatwrk/databases/smiles.html DataSetWID @wid NOT NULL, -- Reference to the data set from which the entity came from -- @FKEY CONSTRAINT FK_Chemical1 FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_Chemical1 PRIMARY KEY (WID) @using-index ) @tabletype; CREATE INDEX CHEMICAL_DWID_NAME ON Chemical(DataSetWID, Name) @index-tablespace; CREATE INDEX CHEMICAL_NAME ON Chemical(Name) @index-tablespace; -- Defines a chemical reaction. The reaction could be enzyme catalyzed -- or occur spontaneously. The reaction could involve small molecules, macromolecules, -- or a combination of the two. Every reaction will be stored in the warehouse in -- a given direction, for example, every reaction that has an assigned EC number is -- written in a direction assigned by the enzyme commission. In physiological settings, -- the reaction could occur in the direction the reaction is stored, the reverse direction, -- or both directions. -- -- Restrictions: Reaction only stores fully qualified EC numbers, e.g., NOT of the form "X.Y.Z.-" CREATE TABLE Reaction ( WID @wid NOT NULL, -- Warehouse ID for this entiry DeltaG @varchar(50), -- Delta G subzero prime value for this reaction ECNumber @varchar(50), -- Official enzyme-commission-number. ECNumberProposed @varchar(50), -- Proposed enzyme-commission-number Spontaneous CHAR(1), -- 'T' if the reaction occurs spontaneously, else 'F' DataSetWID @wid NOT NULL, -- WID of the dataset the reaction is from -- @FKEY CONSTRAINT FK_Reaction FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_Reaction PRIMARY KEY (WID) @using-index ) @tabletype; CREATE INDEX REACTION_DWID ON Reaction(DataSetWID) @index-tablespace; -- Defines a specific protein, that is, a real-world protein which was either purified and isolated, -- OR is reasonably inferred by genomic analysis or other means, such as enzymological characterization. -- The protein could be a monomer or a multimer. In the latter case, a sequence would not be stored for such a record. -- CREATE TABLE Protein ( WID @wid NOT NULL, -- The warehouse ID of this protein -- Name @varchar(1000), -- Common name of the protein, if it exists Name @string10, -- Common name of the protein, if it exists AASequence @string32, -- Amino-acid sequence for this protein, if available Length @int32, -- Length of the amino-acid sequence for this protein, if available -- This value is not necessarily exact, as can be modified by LengthApproximate LengthApproximate @varchar(10), -- Indicates that the Length is an approximate. It could be 'gt' for -- greater than, 'lt' for less than and 'ne' to indicate that it is not -- equal. This is a controlled vocabulary. Charge @int16, -- Charge of the chemical Fragment CHAR(1), -- 'T' if protein is a fragment, else 'F' MolecularWeightCalc @real32, -- Molecular weight calculated from sequence. -- Units: kiloDaltons. MolecularWeightExp @real32, -- Molecular Weight determined through experimentation. -- Units: kiloDaltons. PICalc @varchar(50), -- Isoelectric point (pI) calculated from its sequence. PIExp @varchar(50), -- Isoelectric point (pI) determined through experimentation. DataSetWID @wid NOT NULL, -- WID of the dataset the protein is from -- @FKEY CONSTRAINT FK_Protein FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_Protein PRIMARY KEY (WID) @using-index ) @tabletype; CREATE INDEX PROTEIN_DWID ON Protein(DataSetWID) @index-tablespace; -- Features define regions or points of interest on the protein sequence or -- nucleic acid sequence specified by SequenceWID. -- Features are not used to describe objects such as Genes, BioSources, etc, -- because attributes of those objects are described in their own tables. -- Exceptions: -- 1. Since pseudogenes are not entered in the Gene table, they are listed as sequence features in Feature -- CREATE TABLE Feature ( WID @wid NOT NULL, -- Warehouse identifier of this feature. Description @varchar(255), -- Textual description of this feature. Type @varchar(50), -- Type of feature. These type values come from the source dataset and -- are not necessarily enumerated. Example: "Promoter CYZ". Class @varchar(50), -- Class of feature. Assigns our typology for types of features, or qualifiers -- associated with features. These are our own enumerated type values, to allow us -- to classify features without losing the original (author-provided) type values -- stored in Type. Example: Class=Promoter. SequenceType CHAR(1) NOT NULL, -- Enumeration (see also SequenceWID) that indicates whether the sequence -- is protein or nucleic and how the sequence (if available) is represented: -- If 'P', feature resides on a protein. -- If 'S' or 'N', feature resides on a nucleic acid. SequenceWID @wid, -- References the Protein or Subsequence containing the sequence on which we -- are defining a feature: -- SequenceType of 'S' implies SequenceWID is nonNULL and references a Subsequence- -- sequence = Subsequence.Sequence (i.e., it is stored explicitly), -- SequenceType of 'N' implies SequenceWID (if nonNULL) references a NucleicAcid- -- sequence is the substring Subsequence.Sequence[StartPosition : EndPosition] -- where Subsequence is the full Subsequence of the nucleic acid. -- SequenceType of 'P' implies SequenceWID (if nonNULL) references a Protein- -- sequence is the substring Protein.AASequence[StartPosition : EndPosition]. StartPosition @int32, -- Start position of the feature within the NucleicAcid or Protein sequence. EndPosition @int32, -- End position of the feature within the NucleicAcid or Protein sequence. StartPositionApproximate @varchar(10), -- Indicates that the Start position of the coding region is an approximate value -- It could be 'gt' for greater than, 'lt' for less than and 'ne' to indicate that it is not -- equal. This is a controlled vocabulary. EndPositionApproximate @varchar(10), -- Indicates that the End position of the coding region is an approximate value. -- It could be 'gt' for greater than, 'lt' for less than and 'ne' to indicate that it is not -- equal. This is a controlled vocabulary. ExperimentalSupport CHAR(1), -- 'T' if the feature is supported by experimental evidence, else 'F' ComputationalSupport CHAR(1), -- 'T' if the feature is supported by computational evidence, else 'F' DataSetWID @wid NOT NULL, -- Reference to the data set from which the entity came from -- @FKEY CONSTRAINT FK_Feature FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_Feature PRIMARY KEY (WID) @using-index -- SD CONSTRAINT FK_Feature1 FOREIGN KEY (SequenceWID) REFERENCES Subsequence(WID) -- SD CONSTRAINT FK_Feature2 FOREIGN KEY (SequenceWID) REFERENCES NucleicAcid(WID) -- SD CONSTRAINT FK_Feature3 FOREIGN KEY (SequenceWID) REFERENCES Protein(WID) ) @tabletype; -- Describes the non-enzymatic function(s) of proteins. That is, -- this table should not be used to describe the enzymatic function of a protein whose enzymatic -- function is described using the EnzymaticReaction table. Function names are stored as -- strings with no particular format or interpretation. -- CREATE TABLE Function ( WID @wid NOT NULL, -- Warehouse identifier for this function. Name @varchar(255), -- Name of this function. DataSetWID @wid NOT NULL, -- Data set the entity came from -- @FKEY CONSTRAINT FK_Function FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_Function PRIMARY KEY (WID) @using-index ) @tabletype; -- Defines an association between a reaction and an enzyme that catalyzes -- that reaction (ProteinWID). In the case where we are defining an -- association between a subunit of a larger enzyme complex, and a -- reaction catalyzed by that subunit only when the subunit is part of -- that larger complex, ComplexWID specifies that larger complex (assumes -- that complex is in the warehouse). -- This table requires a WID as it is referenced in multiple linking tables -- to associate alternate compounds, cofactors, etc., to allow for multiple -- valued attributes of the enzymatic reaction. -- CREATE TABLE EnzymaticReaction ( WID @wid NOT NULL, -- WID of this enzymatic reaction ReactionWID @wid NOT NULL, -- WID of the catalyzed reaction ProteinWID @wid NOT NULL, -- WID of the enzyme ComplexWID @wid, -- WID of protein of which ProteinWID -- must be a subunit of for this reaction ReactionDirection @varchar(30), -- Reaction Direction Enumeration DataSetWID @wid NOT NULL, -- WID of the dataset the reaction is from -- @FKEY CONSTRAINT FK_EnzymaticReaction1 FOREIGN KEY (ReactionWID) REFERENCES Reaction(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_EnzymaticReaction2 FOREIGN KEY (ProteinWID) REFERENCES Protein(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_EnzymaticReaction3 FOREIGN KEY (ComplexWID) REFERENCES Protein(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_EnzymaticReaction4 FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_EnzymaticReaction PRIMARY KEY (WID) @using-index ) @tabletype; CREATE INDEX ER_DATASETWID ON EnzymaticReaction(DataSetWID) @index-tablespace; -- Defines the genetic codes with a name, translation table and a start codon for each code. -- See http://anatomy.med.unsw.edu.au/CBL/embryo/DNA/Genetic_Codes.htm for more information. -- Genbank ID is stored as DBID.XID, where DBID.OtherWID = GeneticCode.WID. -- CREATE TABLE GeneticCode ( WID @wid NOT NULL, -- Warehouse ID for this GeneticCode. NCBIID @varchar(2), -- 1- or 2-digit ID assigned to this code. See URL above. Name @varchar(100), -- Genetic code name. TranslationTable @varchar(64), -- Translation table for this genetic code. See URL above for format. StartCodon @varchar(64), -- Start Codon for this genetic code. See URL above for format DataSetWID @wid NOT NULL, -- Reference to the data set from which the entity came from. @FKEY CONSTRAINT FK_GeneticCode FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_GeneticCode PRIMARY KEY (WID) @using-index ) @tabletype; -- Defines a GenBank Division, used in a ***taxonomic sense***. Contains the name of the division and a three letter code. -- modified 5/16/04 by YP to reflect our inferrence that there are two different definitions -- used by NCBI for the term "Division": either as a data organization -- (e.g., the data files associated with the CON division) or with a taxonomic usage of the term (e.g., the BCT division). -- According to this inferrence, there will never be a CON division in the Division table. -- CREATE TABLE Division ( WID @wid NOT NULL, -- Warehouse ID for this Division. Code @varchar(10), -- Genbank Division code (3 characters) which is an abbreviation for the GenBank Name. Name @varchar(100), -- Division name. DataSetWID @wid NOT NULL, -- Reference to the data set from which the entity came from. @FKEY CONSTRAINT FK_Division FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_Division PRIMARY KEY (WID) @using-index ) @tabletype; -- Defines a taxon, which could be a species, genus, family or some other rank. -- These ranks are controlled vocabulary and are stored in the Enumeration table. -- The taxon table associates a taxon with its parent, gives the name, division code and the -- genetic code information for the taxon. -- CREATE TABLE Taxon ( WID @wid NOT NULL, -- Warehouse ID for this Taxon ParentWID @wid, -- Warehouse ID of the parent of this taxon Name @varchar(100), -- Taxonomic Name of this taxon Rank @varchar(100), -- Rank of this taxon (kingdom, superkingdom ..) DivisionWID @wid, -- Warehouse ID of the division this taxon belongs to. InheritedDivision CHAR(1), -- 'T' if division is inherited from parent, else 'F' GencodeWID @wid, -- Warehouse ID of the genetic code for this taxon. InheritedGencode CHAR(1), -- 'T' if gencode is inherited from parent, else 'F' MCGencodeWID @wid, -- Warehouse ID of the mitochondrial genetic code for this taxon. InheritedMCGencode CHAR(1), -- 'T' if the mitochondrial gencode is inherited from parent, else 'F' DataSetWID @wid NOT NULL, -- Reference to the data set from which the entity came from @FKEY CONSTRAINT FK_Taxon FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_Taxon_Division FOREIGN KEY (DivisionWID) REFERENCES Division(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_Taxon_GeneticCode FOREIGN KEY (GencodeWID) REFERENCES GeneticCode(WID) ON DELETE CASCADE, CONSTRAINT PK_Taxon PRIMARY KEY (WID) @using-index ) @tabletype; -- Defines the biological source of an entry in the Protein, NucleicAcid or Gene tables ("the object"), -- as well as other tables eventually storing experimental data related to e.g., microarray or proteomic -- experiments. For example, BioSource might be used to specify the organism, organ, and tissue in -- which gene expression experiments were performed. -- DEPENDENCIES: -- 1. As BioSource references Taxon.WID to specify the species, the NCBI taxonomy dataset must have ALREADY been loaded -- prior to populating BioSource. -- 2. BioSource stores subtype information in BioSubtype via the BioSourceWIDBioSubtypeWID linking table @- Created by YP 4/27/04 -- CREATE TABLE BioSource ( WID @wid NOT NULL, -- Warehouse identifier for biological source. TaxonWID @wid, -- Warehouse GUID identifying WID of Taxon entry for the species. Always -- references species level or higher. Name @varchar(200), -- Informal name assigned to this source. -- Ex. (CMR): 'Escherichia coli K12-MG1655' Strain @varchar(50), -- Strain of organism from which object is derived, if applicable. This is a -- subspecies taxonomic classification not supported by Taxon because it is not -- a rigorous concept. -- Strain is defined as a population of homogeneous organisms possessing a set of -- defined characteristics. In bacteriology, the set of descendants that retains -- the characteristics of the ancestor. Members of a strain that subsequently -- differ from the original isolate are regarded as belonging either to a substrain -- of the original strain, or to a new strain. Organ @varchar(50), -- Organ of organism from which object is derived, if applicable. -- Organ is defined as any part of the body exercising a specific function, such as -- respiration, secretion, or digestion, if applicable. Organelle @varchar(50), -- Organelle of organism from which object is derived, if applicable. Tissue @varchar(50), -- Tissue of organism from which object is derived, if applicable. Can be used in -- conjunction with Organ. -- Tissue is defined as a collection of similar cells and the intercellular substances -- surrounding them. Multiple tissues can be part of an Organ. CellType @varchar(50), -- Cell type of organism from which object is derived, if applicable. -- Cell type is defined as the type of an individual cell, e.g., "myoblast". This is a -- cellular-level classification. CellLine @varchar(50), -- Cell line from which object is derived, if applicable -- Cell line usually pertains to an immortalized version of a given cell type DevelopmentStage @varchar(50), -- Stage of development associated with the object, if applicable Sex @varchar(15), -- Sex of the organism from which object is derived, if applicable -- Enumerated. Values=Male/Female/Hermaphrodite/Asexual/NULL (NULL here means not provided by dataset) DataSetWID @wid NOT NULL, -- The dataset this entity came from or is associated with -- @FKEY CONSTRAINT FK_BioSource1 FOREIGN KEY (TaxonWID) REFERENCES Taxon(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_BioSource2 FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_BioSource PRIMARY KEY (WID) @using-index ) @tabletype; -- Describes idiosyncratic sub-species descriptions such as "serotype", "variety", etc. -- These can be many to one (1 BioSource - many BioSubtypes) linked via BioSourceWIDBioSubtypeWID @- Created by YP 4/27/04 @- Added Version 3.0 -- CREATE TABLE BioSubtype ( WID @wid NOT NULL, -- Warehouse GUID for this BioSubtype Type @varchar(15), -- Type of subtype, e.g., "serotype" Value @varchar(50) NOT NULL, -- Value of the subtype, e.g., "O157" DataSetWID @wid NOT NULL, -- The dataset this entity came from or is associated with -- @FKEY CONSTRAINT FK_BioSubtype2 FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_BioSubtype PRIMARY KEY (WID) @using-index ) @tabletype; -- Defines a specific nucleic acid molecule, such as DNA or of RNA. -- Entries in this table correspond to a real-world DNA or RNA molecules -- which at one point were purified and isolated ("something you can point at"). A row in this table -- can define a complete molecule, a fragment of a molecule, or a molecule that has been partially -- sequenced in different regions. -- This table will be used in several ways: (1) to associate a sequence with an entire -- replicon, or a region of a replicon, when the sequence of that replicon is known; (2) to associate -- a DNA sequence with a single gene; (3) to define a DNA or RNA molecule that has not been -- sequenced; (4) to define RNA molecules such as tRNAs. -- -- Features on a NucleicAcid molecule (such as promoters or binding sites) can be defined using -- the Feature table. -- -- The Subsequence table contains zero or more full or partial sequences contents of this molecule. -- If one represents the full sequence of this molecule, Subsequence.FullSequence = 'T' where -- Subsequence.NucleicAcidWID references this molecule. In this case, NucleicAcid.FullySequenced = 'T' as well. -- @- TODO: Could add Unsequenced flag -- @- Updated extensively Version 3.0 CREATE TABLE NucleicAcid ( WID @wid NOT NULL, -- Warehouse identifier for this NucleicAcid. Name @varchar(200), -- Name or description of this molecule. -- Ex: (CMR) 'Chromosome II Brucella melitensis 16M'. Type @varchar(30) NOT NULL, -- Enumeration: 'DNA' 'RNA' or 'na' Class @varchar(30) NOT NULL, -- Enumeration: describes the molecule as it exists in the organism; stores values such as RNA subtype ("pre-RNA", "mRNA"), replicon type (e.g., "chromosome", "plasmid") Topology @varchar(30), -- Enumeration: 'circular', 'linear' or 'other'. Strandedness @varchar(30), -- Enumeration indicating whether Nucleic Acid is single stranded, double stranded or mixed stranded. SequenceDerivation @varchar(30), -- Enumeration describing how the sequence was generated -- (e.g., from single clone, from assembly of sequences -- from other sequence entries, collection of different clones, etc). Fragment CHAR(1), -- 'T' if this is a fragment of a molecule, -- 'F' if this NucleicAcid describes an entire molecule. FullySequenced CHAR(1), -- 'T' if the molecule is completely -- sequenced within this dataset, else 'F'. MoleculeLength @int32, -- The length of the molecule, in nucleotides. This value can be an -- approximation. Even if this value is known to be exact, -- it may not necessarily be identical to that of TotalLength, as the molecule -- This value is calculated if it is not explicitely stated in the source -- dataset MoleculeLengthApproximate @varchar(10), -- Enumeration; specifies whether MoleculeLength stores an approximate value: -- 'gt' for greater than, -- 'lt' for less than, or -- 'ne' for not equal. CumulativeLength @int32, -- The cumulative number of nucleotides for Subsequences referenced by this NucleicAcid entry, -- whether contiguous or not. This value is a summation of the number of nucleotides for these Subsequences. -- If the molecule is completely sequenced, this value should be identical -- to that of MoleculeLength; in this case, both fields are populated. CumulativeLengthApproximate @varchar(10), -- Enumeration; specifies whether TotalLength approximates actual total length: -- 'gt' for greater than, -- 'lt' for less than, or -- 'ne' for not equal. GeneticCodeWID @wid, -- References the genetic code of this molecule. BioSourceWID @wid, -- References the biological source of this molecule. DataSetWID @wid NOT NULL, -- References the data set from which the entity came from -- @FKEY CONSTRAINT FK_NucleicAcid1 FOREIGN KEY (GeneticCodeWID) REFERENCES GeneticCode(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_NucleicAcid2 FOREIGN KEY (BioSourceWID) REFERENCES BioSource(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_NucleicAcid3 FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_NucleicAcid PRIMARY KEY (WID) @using-index ) @tabletype; -- Contains a contiguous sequence of a nucleic acid molecule. Multiple subsequences can make up a given NucleicAcid entry. -- Contains either the full or partial nucleotide sequence of the nucleic acid molecule stored in NucleicAcid. -- Todo: Currently the schema has no way of indicating an approximate position -- of this Subsequence on the NucleicAcid as a whole. @- Ordering/map issues avoided for this release as too complex @- Added Version 3.0 -- CREATE TABLE Subsequence ( WID @wid NOT NULL, -- Warehouse identifier for this Subsequence. NucleicAcidWID @wid NOT NULL, -- References the containing nucleic acid molecule. FullSequence CHAR(1), -- 'T' if this is the complete sequence of the nucleic acid molecule -- referenced by NucleicAcidWID, else 'F'. Sequence @string32, -- Nucleotide sequence of this Subsequence. Length @int32, -- Number of nucleotides (and characters) in Sequence. This value -- is calculated if it is not explicitely stated in the source -- dataset LengthApproximate @varchar(10), -- Enumeration; indicates that Length approximates actual Sequence length: -- 'gt' for greater than, -- 'lt' for less than, or -- 'ne' for not equal. PercentGC @real32, -- Percentage of Sequence nucleotides that are either guanine or cytosine. -- This value is calculated if it is not explicitely stated in the source -- dataset Version @varchar(30), -- Dataset-specific information to indicate the version of this Subsequence. DataSetWID @wid NOT NULL, -- Reference to the data set from which the entity came from -- @FKEY CONSTRAINT FK_Subsequence1 FOREIGN KEY (NucleicAcidWID) REFERENCES NucleicAcid(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_Subsequence2 FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_Subsequence PRIMARY KEY (WID) @using-index ) @tabletype; -- Defines a notion of gene that is limited to procaryotic aspects of -- genes. Later versions of the warehouse will expand this definition to include -- eukaryotic aspects of genes. Separate tables define associations between a gene and -- (1) its biological source(s), -- (2) its protein product(s), if any, and -- (3) its RNA product(s), if any. -- CodingRegionStart is the position of the first nucleotide in the start codon; -- CodingRegionEnd is the position of the last nucleotide in the codon preceding -- the stop codon. @- Modified for 3.0 -- CREATE TABLE Gene ( WID @wid NOT NULL, -- Warehouse identifier this gene. Name @varchar(255), -- Name of the gene. NucleicAcidWID @wid, -- Reference to the NucleicAcid molecule (replicon) this gene resides upon. SubsequenceWID @wid, -- Reference to the Subsequence containing the nucleotide sequence of this Gene. @- Added 3.0 Type @varchar(100), -- Describes the type of molecule which is known to be *ultimately* produced by this gene -- enumerated values (polypeptide, pre-mRNA, rRNA, tRNA, etc) GenomeID @varchar(30), -- Unique ID assigned to this gene, such as by a genome project CodingRegionStart @int32, -- Base position of start of coding region. Start is always less than End, -- except for genes that wrap around the origin of a circular chromosome. CodingRegionEnd @int32, -- Base position of end of coding region. CodingRegionStartApproximate @varchar(10), -- Indicates that the Start position of the coding region is an approximate value. -- It could be 'gt' for greater than, 'lt' for less than and 'ne' to indicate that it is not -- equal. This is a controlled vocabulary. CodingRegionEndApproximate @varchar(10), -- Indicates that the End position of the coding region is an approximate value. -- It could be 'gt' for greater than, 'lt' for less than and 'ne' to indicate that it is not -- equal. This is a controlled vocabulary. Direction @varchar(25), -- Direction of transcription as defined in the enumeration table Interrupted CHAR(1), -- 'T' if the gene is interrupted, else 'F'. DataSetWID @wid NOT NULL, -- Reference to the data set from which the entity came from -- @FKEY CONSTRAINT FK_Gene1 FOREIGN KEY (NucleicAcidWID) REFERENCES NucleicAcid(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_Gene2 FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_Gene PRIMARY KEY (WID) @using-index ) @tabletype; CREATE INDEX GENE_DATASETWID ON Gene(DATASETWID) @index-tablespace; -- Pathways are graphs of reactions, grouped together according to a -- higher biological function they perform. Some pathways may be -- "template", "reference", "model" or "sum of organisms" pathways. Those -- pathways do not contain an BioSourceWID reference, and have the type -- field set to "R" (Reference/Model/Theoretical). For real organisms, -- the type is "O". -- CREATE TABLE Pathway ( WID @wid NOT NULL, -- Warehouse ID for this pathway Name @varchar(255) NOT NULL, -- Name of the pathway Type CHAR(1) NOT NULL, -- Type of pathway (see comment above) BioSourceWID @wid, -- BioSource this pathway occurs in DataSetWID @wid NOT NULL, -- WID of the dataset the pathway is from -- @FKEY CONSTRAINT FK_Pathway1 FOREIGN KEY (BioSourceWID) REFERENCES BioSource(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_Pathway2 FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_Pathway PRIMARY KEY (WID) @using-index ) @tabletype; @- Does KEGG loader use these indexes? CREATE INDEX PATHWAY_BSWID_WID_DWID ON Pathway(BioSourceWID, WID, DataSetWID)@index-tablespace; CREATE INDEX PATHWAY_TYPE_WID_DWID ON Pathway(TYPE, WID, DataSetWID)@index-tablespace ; CREATE INDEX PATHWAY_DWID ON Pathway(DataSetWID) @index-tablespace; -- A term is a controlled vocabulary term used within a particular dataset. -- E.g. the keyword attached to proteins in SwissProt. Terms can be arranged -- in a hierarchy (see the TermWIDParentWID table). -- CREATE TABLE Term ( WID @wid NOT NULL, -- Warehouse identifier for this term Name @varchar(100) NOT NULL, -- Name for the term DataSetWID @wid NOT NULL, -- WID of the dataset the pathway is from -- CONSTRAINT PK_Term PRIMARY KEY (WID) @using-index ) @tabletype; -- Defines a parameterized computation that has been performed on objects in the warehouse. -- TODO: Parameters are specified in the Parameter table. -- CREATE TABLE Computation ( WID @wid NOT NULL, -- WID of this computation Name @varchar(50) NOT NULL, -- Name of the algorithm, procedure, or program, e.g. 'BLAST' Description @string16, -- Description of the computation performed DataSetWID @wid NOT NULL, -- @FKEY CONSTRAINT FK_Computation FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_Computation PRIMARY KEY (WID) @using-index ) @tabletype; -- Defines a literature citation. -- Typically a citation is associated with one or more Warehouse objects via the CitationWIDOtherWID table. -- CREATE TABLE Citation ( WID @wid NOT NULL, -- WID of this citation Citation @string10 NOT NULL, -- Line of text containing the citation PMID @number, -- Pubmed ID of the citation, if known DataSetWID @wid NOT NULL, -- @FKEY CONSTRAINT FK_Citation FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_Citation PRIMARY KEY (WID) @using-index ) @tabletype; -- Describes point of contact information for an individual and/or an organization. -- CREATE TABLE PointOfContact ( WID @wid NOT NULL, -- Warehouse identifier for this entity Organization @varchar(100), -- Affiliated organization (company, university, institute) Department @varchar(100), -- Research group, lab, academic dept., etc., of organization Person @varchar(100), -- Name of person to contact: 'Lastname, Firstname' Email @varchar(50), -- E-mail address Phone @varchar(50), -- Telephone number Fax @varchar(50), -- Fax number URL @varchar(100), -- Web address of this contact Address @varchar(255), -- Postal address -- CONSTRAINT PK_POC PRIMARY KEY (WID) @using-index ) @tabletype; -- Defines an association between a warehouse entity and an external representation or depiction -- of that entity in a well-defined format. -- Can be used to store the contents itself, or to indicate that an archive is present at a URL. -- CREATE TABLE Archive ( WID @wid NOT NULL, -- Warehouse identifier for this entity OtherWID @wid NOT NULL, -- WID of entity archive is associated with Format @varchar(10) NOT NULL, -- Storage/compression format used eg. 'zip' 'tar' 'tgz' 'jpeg' Contents @byte32, -- Archive contents URL @string10, -- Web address the archive is found at, ideally a specific file ToolName @varchar(50), -- The name of the tool or software kit the archive is used by -- eg. 'MIAMExpress' DataSetWID @wid NOT NULL, -- DataSet.WID this entity is associated with -- CONSTRAINT PK_ARCHIVE PRIMARY KEY (WID) @using-index -- SD CONSTRAINT FK_Archive1 FOREIGN KEY (ExperimentWID) REFERENCES Experiment(WID) ) @tabletype; -- Defines an experiment. Provides a context for associating experimental data with it. -- Allows tree-structured experiments consisting of heterogeneous subexperiments, -- subexperiments corresponding to time-series observations, -- and repeated trials of identical experiments. -- For a hierarchical experiment, data should be associated with the Experiment -- at the appropriate level. For example, if data reflects results from averaging -- numerous identically conducted trials, that data should be associated with the -- Experiment representing the group of these trials. -- A Comment table entry may be created to contain discussion of results, etc. -- A DBID table entry may be created if the (sub)experiment has a unique name within its DataSet. -- SynonymTable table entries may be created to associate names with the (sub)experiment. -- If published, a reference may be created in Citation table. -- CREATE TABLE Experiment ( WID @wid NOT NULL, -- Warehouse identifier for this entity Type @varchar(50) NOT NULL, -- Mnemonic for the general type of experiment, e.g. 'microarray' ContactWID @wid, -- PointOfContact.WID for submitter/investigator ArchiveWID @wid, -- Archive.WID of external rep'n of experiment and its data. StartDate @datetime, -- Date+time experiment was started EndDate @datetime, -- Date+time experiment was completed Description @string16, -- Description of this experiment GroupWID @wid, -- Experiment.WID defining the parent group of this subexperiment GroupType @varchar(50), -- Enumeration indicating type of any and all direct subexperiments -- Exs: 'replicate' 'variant' 'step' 'time-series' GroupSize @int32 NOT NULL, -- Number of child experiments loaded GroupIndex @int32, -- Counter value identifying this experiment in a sequence or set -- of subexperiments (eg. time-series). NULL if GroupWID is NULL. TimePoint @int32, -- Time offset indicating when associated observations in a -- time-series experiment were conducted TimeUnit @varchar(20), -- Enumeration for units of TimePoint DataSetWID @wid NOT NULL, -- DataSet.WID this entity is associated with -- @FKEY CONSTRAINT FK_Experiment2 FOREIGN KEY (GroupWID) REFERENCES Experiment(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_Experiment3 FOREIGN KEY (ContactWID) REFERENCES PointOfContact(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_Experiment4 FOREIGN KEY (ArchiveWID) REFERENCES Archive(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_Experiment5 FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_Experiment PRIMARY KEY (WID) @using-index ) @tabletype; -- Specifies a relationship between one data entity and an experiment in which it was -- recorded as an observation or used in some other fashion. -- CREATE TABLE ExperimentData ( WID @wid NOT NULL, -- Warehouse identifier for this entity ExperimentWID @wid NOT NULL, -- Experiment entity Data @string32 NOT NULL, -- Data associated with experiment Role @varchar(50) NOT NULL, -- Describes how data is used in experiment -- ex: 'green intensity' Kind CHAR(1) NOT NULL, -- Characterizes relationship of data to experiment: -- 'O' = observation, 'C' = computed from observation(s), -- 'P' = parameter, 'M' = metadata describing other data DateProduced @datetime, -- When the observation was taken, the computation was performed, etc. DataSetWID @wid NOT NULL, -- The data set this entity came from or is associated with -- @FKEY CONSTRAINT FK_ExpData1 FOREIGN KEY (ExperimentWID) REFERENCES Experiment(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_ExpData2 FOREIGN KEY (DataSetWID) REFERENCES DataSet(WID) ON DELETE CASCADE, CONSTRAINT PK_ExpData PRIMARY KEY (WID) @using-index ) @tabletype; -- Describes the source and strength of evidence for a particular fact in the warehouse. -- E.g. use to record that there are two sources of support for the function of a given -- protein, one computational and one experimental. This table has a WID for use in the -- CitationWIDOtherWID table. -- CREATE TABLE Support ( WID @wid NOT NULL, -- WID of this support OtherWID @wid NOT NULL, -- WID of supported entity, e.g. Protein Function Type @varchar(10), -- reference to an enumeration type Confidence @real32, -- 0 < confidence <= 1. DataSetWID @wid NOT NULL, -- The data set this entity came from or is associated with -- CONSTRAINT CK_Support CHECK (Confidence > 0 AND Confidence <= 1), CONSTRAINT PK_Support PRIMARY KEY (WID) @using-index -- SD CONSTRAINT FK_Support1 FOREIGN KEY (OtherWID) REFERENCES Chemical(WID) -- SD CONSTRAINT FK_Support2 FOREIGN KEY (OtherWID) REFERENCES Element(WID) -- SD CONSTRAINT FK_Support3 FOREIGN KEY (OtherWID) REFERENCES Reaction(WID) -- SD CONSTRAINT FK_Support4 FOREIGN KEY (OtherWID) REFERENCES Protein(WID) -- SD CONSTRAINT FK_Support5 FOREIGN KEY (OtherWID) REFERENCES Feature(WID) -- SD CONSTRAINT FK_Support6 FOREIGN KEY (OtherWID) REFERENCES Function(WID) -- SD CONSTRAINT FK_Support7 FOREIGN KEY (OtherWID) REFERENCES EnzymaticReaction(WID) -- SD CONSTRAINT FK_Support8 FOREIGN KEY (OtherWID) REFERENCES Gene(WID) -- SD CONSTRAINT FK_Support9 FOREIGN KEY (OtherWID) REFERENCES BioSource(WID) -- SD CONSTRAINT FK_Support10 FOREIGN KEY (OtherWID) REFERENCES Pathway(WID) -- SD CONSTRAINT FK_Support11 FOREIGN KEY (OtherWID) REFERENCES NucleicAcid(WID) -- SD CONSTRAINT FK_Support14 FOREIGN KEY (OtherWID) REFERENCES Citation(WID) ) @tabletype; @- ----------------------------------------------------------------------------- @- Tables that implement arbitrary relations among Warehouse objects. @- Naming convention is to use WID as a column name for any WID @- referencing an object from
. @- Tables are listed in alphabetical order. @- ----------------------------------------------------------------------------- -- The tables ChemicalAtom and ChemicalBond define the chemical bond structure of a chemical, the -- charge on the constituent atoms, and encodes a two or three dimensional representation of the -- structure. It is implicit that ChemicalBonds are symmetric. -- Atoms that are chiral centers are have a non-zero StereoParity field. Values for this field are -- defined in the Enumeration table, and are taken from MDL Molfile format defined in -- http://www.mdli.com/downloads/ctfile/ctfile_subs.html . That specification is confusing because -- it appears to define two redundant ways (marking atoms and marking bonds) of defining stereo -- configurations. Our theory is that the redundancy exists to allow different drawings of stereo -- configurations, and that for simply capturing a configuration, setting the StereoParity field -- of an atom is sufficient. Also, setting the BondStereo field of a bond could simplify drawing -- of chemical structures, and can allow different drawings of the same stereo configurations, -- which can be desirable. But these two fields can be interpreted independently of one another. -- See the appendix of the MDL specification for details on interpreting stereo configurations. -- -- E.g. H2O could be encoded as: -- ChemicalAtom (wid, 1, 'H', 0) -- ChemicalAtom (wid, 2, 'O', 0) -- ChemicalAtom (wid, 3, 'H', 0) -- ChemicalBond (wid, 1, 2, 1) -- ChemicalBond (wid, 2, 3, 1) -- CREATE TABLE ChemicalAtom ( ChemicalWID @wid NOT NULL, -- WID of the chemical AtomIndex @int16 NOT NULL, -- Order of the atoms within the structure, -- starting from 1 Atom @varchar(2) NOT NULL, -- Element symbol of the atom Charge @int16 NOT NULL, -- Charge on the atom X @number, -- X,Y,Z display coordinates for a Y @number, -- drawing of the chemical structure Z @number, StereoParity @number, -- Defines parity of an atom that is a chiral center. -- @FKEY CONSTRAINT FK_ChemicalAtom FOREIGN KEY (ChemicalWID) REFERENCES Chemical(WID) ON DELETE CASCADE, CONSTRAINT UN_ChemicalAtom UNIQUE (ChemicalWID, AtomIndex) @using-index ) @tabletype; -- The tables ChemicalAtom and ChemicalBond define the chemical bond structure of a chemical, the charge on -- the constituent atoms, and encodes a two or three dimensional representation of the structure. -- It is implicit that ChemicalBonds are symmetric. BondTypes and BondStereo are defined in the -- Enumeration table, and are taken from MDL Molfile format defined in -- http://www.mdli.com/downloads/ctfile/ctfile_subs.html . That specification is confusing. See -- documentation of ChemicalAtom table for more information. -- E.g. H2O could be encoded as: -- ChemicalAtom (wid, 1, 'H', 0) -- ChemicalAtom (wid, 2, 'O', 0) -- ChemicalAtom (wid, 3, 'H', 0) -- ChemicalBond (wid, 1, 2, 1) -- ChemicalBond (wid, 2, 3, 1) -- CREATE TABLE ChemicalBond ( ChemicalWID @wid NOT NULL, -- WID of the chemical Atom1Index @int16 NOT NULL, -- Index of first atom bonded (from ChemicalAtom) Atom2Index @int16 NOT NULL, -- Index of second atom bonded BondType @int16 NOT NULL, -- Type of bond enumeration, e.g., 1=Single, 2=Double, 3=Triple, 4=Aromatic. BondStereo @number @fkeycomma -- Stereo information about bond, e.g., 0 = Not stereo. -- @FKEY CONSTRAINT FK_ChemicalBond FOREIGN KEY (ChemicalWID) REFERENCES Chemical(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_ChemicalBond2 FOREIGN KEY (ChemicalWID, Atom1Index) REFERENCES ChemicalAtom(ChemicalWID, AtomIndex) ON DELETE CASCADE, @FKEY CONSTRAINT FK_ChemicalBond3 FOREIGN KEY (ChemicalWID, Atom2Index) REFERENCES ChemicalAtom(ChemicalWID, AtomIndex) ON DELETE CASCADE ) @tabletype; -- Cofactors are chemicals that are required for the enzyme to catalyze -- the reaction, but are left unchanged by the reaction. If multiple -- cofactors are listed for a reaction, this is interpreted as a disjunction. -- This table also encodes prosthetic groups. -- CREATE TABLE EnzReactionCofactor ( EnzymaticReactionWID @wid NOT NULL, -- EnzymaticReaction.WID of the Enzymatic Reaction ChemicalWID @wid NOT NULL, -- Chemical.WID of the compound Prosthetic CHAR(1) @fkeycomma -- 'T' if covalently/tightly bound to enzyme, else 'F' -- @FKEY CONSTRAINT FK_EnzReactionCofactor1 FOREIGN KEY (EnzymaticReactionWID) REFERENCES EnzymaticReaction(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_EnzReactionCofactor2 FOREIGN KEY (ChemicalWID) REFERENCES Chemical(WID) ON DELETE CASCADE ) @tabletype; -- Identifies a compound that is either an alternate substrate or an alternate cofactor -- to a primary compound present in an enzymatic reaction. -- CREATE TABLE EnzReactionAltCompound ( EnzymaticReactionWID @wid NOT NULL, -- EnzymaticReaction.WID of the Enzymatic Reaction PrimaryWID @wid NOT NULL, -- Chemical.WID of primary compound AlternativeWID @wid NOT NULL, -- Chemical.WID of alternate compound Cofactor CHAR(1) @fkeycomma -- 'T' if this alternative is a cofactor, 'F' if it is a substrate -- @FKEY CONSTRAINT FK_ERAC1 FOREIGN KEY (EnzymaticReactionWID) REFERENCES EnzymaticReaction(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_ERAC2 FOREIGN KEY (PrimaryWID) REFERENCES Chemical(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_ERAC3 FOREIGN KEY (AlternativeWID) REFERENCES Chemical(WID) ON DELETE CASCADE ) @tabletype; -- Associates an enzymatic reaction to the compounds that act as -- inhibitors and activators for the reaction. -- The mechanism of action is encoded in the Mechanism column. -- CREATE TABLE EnzReactionInhibitorActivator ( EnzymaticReactionWID @wid NOT NULL, -- EnzymaticReaction.WID of the Enzymatic Reaction CompoundWID @wid NOT NULL, -- Chemical.WID or Protein.WID of the compound InhibitOrActivate CHAR(1), -- 'A' = Activate, 'I' = Inhibit Mechanism CHAR(1), -- Mechanism of action: -- 'A' - Allosteric, -- 'C' - Competitive, -- 'I' - Irreversible, -- 'N' - Nonallosteric and noncompetitive, -- 'U' - Unknown -- (this enumeration is stored in the Enumeration table). PhysioRelevant CHAR(1) @fkeycomma -- 'T' if physiologically relevant, else 'F' -- @FKEY CONSTRAINT FK_EnzReactionIA1 FOREIGN KEY (EnzymaticReactionWID) REFERENCES EnzymaticReaction(WID) ON DELETE CASCADE -- SD CONSTRAINT FK_EnzReactionIA2 FOREIGN KEY (CompoundWID) REFERENCES Chemical(WID) -- SD CONSTRAINT FK_EnzReactionIA3 FOREIGN KEY (CompoundWID) REFERENCES Protein(WID) ) @tabletype; -- This table allows us to state that two pathways are neighbors in -- a biologically significant sense, because they share a substrate -- in common. This link between two pathways is represented by the WIDs of the -- interacting pathways, and of the chemical compound that they share. -- CREATE TABLE PathwayLink ( Pathway1WID @wid NOT NULL, -- WID of one of the linked pathways Pathway2WID @wid NOT NULL, -- WID of the other linked pathways ChemicalWID @wid NOT NULL @fkeycomma -- WID of the linking chemical -- @FKEY CONSTRAINT FK_PathwayLink1 FOREIGN KEY (Pathway1WID) REFERENCES Pathway(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_PathwayLink2 FOREIGN KEY (Pathway2WID) REFERENCES Pathway(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_PathwayLink3 FOREIGN KEY (ChemicalWID) REFERENCES Chemical(WID) ON DELETE CASCADE ) @tabletype; -- A pathway is defined as a set of reaction pairs. For each pair of -- reactions R1 and R2, R1 directly precedes R2 in the pathway. -- Some reactions in a pathway may be considered -- hypothetical, probably because the presence of the enzyme has not been -- demonstrated. -- CREATE TABLE PathwayReaction ( PathwayWID @wid NOT NULL, -- WID of a pathway ReactionWID @wid NOT NULL, -- WID of a reaction, R2, in the pathway PriorReactionWID @wid, -- WID, R1, of a predecessor reaction of R2 Hypothetical CHAR(1) NOT NULL @fkeycomma -- 'F' if R2 proven to exist, 'T' otherwise. -- @FKEY CONSTRAINT FK_PathwayReaction1 FOREIGN KEY (PathwayWID) REFERENCES Pathway(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_PathwayReaction2 FOREIGN KEY (ReactionWID) REFERENCES Reaction(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_PathwayReaction3 FOREIGN KEY (PriorReactionWID) REFERENCES Reaction(WID) ON DELETE CASCADE ) @tabletype; CREATE INDEX PR_PATHWID_REACTIONWID ON PathwayReaction(PathwayWID, ReactionWID)@index-tablespace; -- Associates a reaction with a chemical product of that reaction. -- CREATE TABLE Product ( ReactionWID @wid NOT NULL, -- Warehouse identifier of the reaction that this reactant is part of. OtherWID @wid NOT NULL, -- Reference to the chemical or protein that is the reactant. Coefficient @int16 NOT NULL @fkeycomma -- The number of molecules of this reactant used in the reaction. -- @FKEY CONSTRAINT FK_Product FOREIGN KEY (ReactionWID) REFERENCES Reaction(WID) ON DELETE CASCADE -- SD CONSTRAINT FK_Product1 FOREIGN KEY (OtherWID) REFERENCES Chemical(WID) -- SD CONSTRAINT FK_Product2 FOREIGN KEY (OtherWID) REFERENCES Protein(WID) ) @tabletype; -- Associates a reaction with a chemical that is consumed by the reaction. -- CREATE TABLE Reactant ( ReactionWID @wid NOT NULL, -- Warehouse identifier of the reaction that this reactant is part of OtherWID @wid NOT NULL, -- Reference to the chemical or protein that is the reactant. Coefficient @int16 NOT NULL @fkeycomma -- The amount of this reactant used in the reation -- @FKEY CONSTRAINT FK_Reactant FOREIGN KEY (ReactionWID) REFERENCES Reaction(WID) ON DELETE CASCADE -- SD CONSTRAINT FK_Reactant1 FOREIGN KEY (OtherWID) REFERENCES Chemical(WID) -- SD CONSTRAINT FK_Reactant2 FOREIGN KEY (OtherWID) REFERENCES Protein(WID) ) @tabletype; -- Records a result of a computation of the degree of match between two sequences. -- Sequences are either both Proteins or both Subsequences. -- CREATE TABLE SequenceMatch ( QueryWID @wid NOT NULL, -- WID of the Protein or Subsequence being matched MatchWID @wid NOT NULL, -- WID of the Protein or Subsequence matched against QueryWID ComputationWID @wid NOT NULL, -- WID of the Computation that describes the procedure used to determine the match EValue @real64, -- Roughly, the log-probability of this match occurring; see http://www.ncbi.nlm.nih.gov/BLAST/tutorial/Altschul-1.html PValue @real64, -- 1 - e^(-E), where E is EValue PercentIdentical @real32, -- Percentage of residues in the two molecules that are identical PercentSimilar @real32, -- Percentage of residues in the two molecules that are similar Rank @int16, -- Ordinal rank of Query molecule with other matches (1 is best) Length @int32, -- The length of the match between the two molecules QueryStart @int32, -- Start of the matching region in Query QueryEnd @int32, -- End of the matching region in Query MatchStart @int32, -- Start of the matching region in Match MatchEnd @int32 @fkeycomma -- End of the matching region in Match -- @FKEY CONSTRAINT FK_SequenceMatch FOREIGN KEY (ComputationWID) REFERENCES Computation(WID) ON DELETE CASCADE -- SD CONSTRAINT FK_SequenceMatch1 FOREIGN KEY (QueryWID) REFERENCES Protein(WID) -- SD CONSTRAINT FK_SequenceMatch2 FOREIGN KEY (MatchWID) REFERENCES Protein(WID) -- SD CONSTRAINT FK_SequenceMatch3 FOREIGN KEY (QueryWID) REFERENCES Subsequence(WID) -- SD CONSTRAINT FK_SequenceMatch4 FOREIGN KEY (MatchWID) REFERENCES Subsequence(WID) ) @tabletype; -- Specifies that Subunit is a subunit of Complex. These subunit relationships could be -- described in multiple levels. For example, this table can be used to describe multimeric proteins, -- or ribosomes. -- CREATE TABLE Subunit ( ComplexWID @wid NOT NULL, -- Reference to the parent Protein SubunitWID @wid NOT NULL, -- Reference to the child protein inside the parent Protein Coefficient @int16 @fkeycomma -- Number of copies of the subunit within the complex -- @FKEY CONSTRAINT FK_Subunit1 FOREIGN KEY (ComplexWID) REFERENCES Protein(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_Subunit2 FOREIGN KEY (SubunitWID) REFERENCES Protein(WID) ON DELETE CASCADE ) @tabletype; -- Pathways may be arranged in a hierarchy, i.e. containment within one or -- more superpathways, as an abstraction mechanism. -- CREATE TABLE SuperPathway ( SubPathwayWID @wid NOT NULL, -- WID of sub-pathway SuperPathwayWID @wid NOT NULL @fkeycomma -- WID of super-pathway -- @FKEY CONSTRAINT FK_SuperPathway1 FOREIGN KEY (SubPathwayWID) REFERENCES Pathway(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_SuperPathway2 FOREIGN KEY (SuperPathwayWID) REFERENCES Pathway(WID) ON DELETE CASCADE ) @tabletype; @- ----------------------------------------------------------------------------- @- Tables that reference an untyped WID (one that could be from one of several object tables). @- Naming convention is to use OtherWID as a column name for the untyped WID. @- Tables are listed in alphabetical order. @- ----------------------------------------------------------------------------- -- Link from citations to the entities described in that citation. Enables one citation to -- provide support for multiple entries in the warehouse. -- CREATE TABLE CitationWIDOtherWID ( OtherWID @wid NOT NULL, CitationWID @wid NOT NULL @fkeycomma -- @FKEY CONSTRAINT FK_CitationWIDOtherWID FOREIGN KEY (CitationWID) REFERENCES Citation(WID) ON DELETE CASCADE -- SD CONSTRAINT FK_CWOW_Chemical FOREIGN KEY (OtherWID) REFERENCES Chemical(WID) -- SD CONSTRAINT FK_CWOW_Reaction FOREIGN KEY (OtherWID) REFERENCES Reaction(WID) -- SD CONSTRAINT FK_CWOW_Protein FOREIGN KEY (OtherWID) REFERENCES Protein(WID) -- SD CONSTRAINT FK_CWOW_Feature FOREIGN KEY (OtherWID) REFERENCES Feature(WID) -- SD CONSTRAINT FK_CWOW_Function FOREIGN KEY (OtherWID) REFERENCES Function(WID) -- SD CONSTRAINT FK_CWOW_EnzymaticReaction FOREIGN KEY (OtherWID) REFERENCES EnzymaticReaction(WID) -- SD CONSTRAINT FK_CWOW_GeneticCode FOREIGN KEY (OtherWID) REFERENCES GeneticCode(WID) -- SD CONSTRAINT FK_CWOW_Division FOREIGN KEY (OtherWID) REFERENCES Division(WID) -- SD CONSTRAINT FK_CWOW_Taxon FOREIGN KEY (OtherWID) REFERENCES Taxon(WID) -- SD CONSTRAINT FK_CWOW_BioSource FOREIGN KEY (OtherWID) REFERENCES BioSource(WID) -- SD CONSTRAINT FK_CWOW_BioSubtype FOREIGN KEY (OtherWID) REFERENCES BioSubtype(WID) -- SD CONSTRAINT FK_CWOW_NucleicAcid FOREIGN KEY (OtherWID) REFERENCES NucleicAcid(WID) -- SD CONSTRAINT FK_CWOW_Subsequence FOREIGN KEY (OtherWID) REFERENCES Subsequence(WID) -- SD CONSTRAINT FK_CWOW_Gene FOREIGN KEY (OtherWID) REFERENCES Gene(WID) -- SD CONSTRAINT FK_CWOW_Pathway FOREIGN KEY (OtherWID) REFERENCES Pathway(WID) -- SD CONSTRAINT FK_CWOW_Term FOREIGN KEY (OtherWID) REFERENCES Term(WID) -- SD CONSTRAINT FK_CWOW_Computation FOREIGN KEY (OtherWID) REFERENCES Computation(WID) -- SD CONSTRAINT FK_CWOW_Citation FOREIGN KEY (OtherWID) REFERENCES Citation(WID) -- SD CONSTRAINT FK_CWOW_PointOfContact FOREIGN KEY (OtherWID) REFERENCES PointOfContact(WID) -- SD CONSTRAINT FK_CWOW_Archive FOREIGN KEY (OtherWID) REFERENCES Archive(WID) -- SD CONSTRAINT FK_CWOW_Experiment FOREIGN KEY (OtherWID) REFERENCES Experiment(WID) -- SD CONSTRAINT FK_CWOW_ExperimentData FOREIGN KEY (OtherWID) REFERENCES ExperimentData(WID) -- SD CONSTRAINT FK_CWOW_Support FOREIGN KEY (OtherWID) REFERENCES Support(WID) ) @tabletype; -- This table allows for arbitrary association of (possibly lengthy) comments -- with any object in the warehouse. -- See the Description table for a more specific type of comment. -- CREATE TABLE CommentTable ( OtherWID @wid NOT NULL, -- Warehouse id of an object Comm @string32 -- Comment about the object -- SD CONSTRAINT FK_COMMENT_Chemical FOREIGN KEY (OtherWID) REFERENCES Chemical(WID) -- SD CONSTRAINT FK_COMMENT_Reaction FOREIGN KEY (OtherWID) REFERENCES Reaction(WID) -- SD CONSTRAINT FK_COMMENT_Protein FOREIGN KEY (OtherWID) REFERENCES Protein(WID) -- SD CONSTRAINT FK_COMMENT_Feature FOREIGN KEY (OtherWID) REFERENCES Feature(WID) -- SD CONSTRAINT FK_COMMENT_Function FOREIGN KEY (OtherWID) REFERENCES Function(WID) -- SD CONSTRAINT FK_COMMENT_EnzymaticReaction FOREIGN KEY (OtherWID) REFERENCES EnzymaticReaction(WID) -- SD CONSTRAINT FK_COMMENT_GeneticCode FOREIGN KEY (OtherWID) REFERENCES GeneticCode(WID) -- SD CONSTRAINT FK_COMMENT_Division FOREIGN KEY (OtherWID) REFERENCES Division(WID) -- SD CONSTRAINT FK_COMMENT_Taxon FOREIGN KEY (OtherWID) REFERENCES Taxon(WID) -- SD CONSTRAINT FK_COMMENT_BioSource FOREIGN KEY (OtherWID) REFERENCES BioSource(WID) -- SD CONSTRAINT FK_COMMENT_BioSubtype FOREIGN KEY (OtherWID) REFERENCES BioSubtype(WID) -- SD CONSTRAINT FK_COMMENT_NucleicAcid FOREIGN KEY (OtherWID) REFERENCES NucleicAcid(WID) -- SD CONSTRAINT FK_COMMENT_Subsequence FOREIGN KEY (OtherWID) REFERENCES Subsequence(WID) -- SD CONSTRAINT FK_COMMENT_Gene FOREIGN KEY (OtherWID) REFERENCES Gene(WID) -- SD CONSTRAINT FK_COMMENT_Pathway FOREIGN KEY (OtherWID) REFERENCES Pathway(WID) -- SD CONSTRAINT FK_COMMENT_Term FOREIGN KEY (OtherWID) REFERENCES Term(WID) -- SD CONSTRAINT FK_COMMENT_Computation FOREIGN KEY (OtherWID) REFERENCES Computation(WID) -- SD CONSTRAINT FK_COMMENT_Citation FOREIGN KEY (OtherWID) REFERENCES Citation(WID) -- SD CONSTRAINT FK_COMMENT_PointOfContact FOREIGN KEY (OtherWID) REFERENCES PointOfContact(WID) -- SD CONSTRAINT FK_COMMENT_Archive FOREIGN KEY (OtherWID) REFERENCES Archive(WID) -- SD CONSTRAINT FK_COMMENT_Experiment FOREIGN KEY (OtherWID) REFERENCES Experiment(WID) -- SD CONSTRAINT FK_COMMENT_ExperimentData FOREIGN KEY (OtherWID) REFERENCES ExperimentData(WID) -- SD CONSTRAINT FK_COMMENT_Support FOREIGN KEY (OtherWID) REFERENCES Support(WID) ) @tabletype; -- This table is used to define (i) links between databases; (ii) links between objects from the same dataset. -- For case (i): -- A row in this table defines a link between an object in the warehouse -- (OtherWID) and an entry in another dataset that may or may not be loaded -- into the warehouse. If it is not loaded, DatabaseName -- (and XID, Type, and Version if available) will be nonnull. -- For case (ii): -- A row in this table defines a link between an object in the warehouse -- (OtherWID) and an object in the same dataset for which the dataset specifies -- an association between them not otherwise represented within the warehouse. -- Such associations are currently (5/10/04) made between a gene described in one sequence (the root Bioseq) and -- another GenBank entry (the reference Bisoseq) which is being referenced and describes the gene independently -- of the first Bioseq. In such a case, CrossReference.OtherWID=Gene.WID. -- In either case: if CrossWID is nonnull, the object linked to is -- loaded into the warehouse; if it is null, it may or may not be loaded. -- Note that if a loader does not know whether a referenced entry will be loaded, -- it is free to fill in CrossWID at a later time. @- 3.0 Changed DatasetName column to DatabaseName @- 3.0 Added Type, Version @- 3.0 Replaced DatasetWID with CrossWID, made XID nullable -- CREATE TABLE CrossReference ( OtherWID @wid NOT NULL, -- Warehouse object we are linking FROM CrossWID @wid, -- Nonnull when the object we are linking TO resides in the warehouse XID @varchar(50), -- Unique id used in referenced database of object we are linking TO Type @varchar(20), -- Enumeration: Describes the types of identifiers stored in -- XID for datasets whose entries are identified with IDs -- of different types, such as 'GUID' or 'Accession'. -- See Enumeration table for further details. Version @varchar(10), -- Further qualifies the values of identifiers stored in XID by -- storing the version number of the identifier for datasets that -- support multiple instances of the same object. For example, -- NCBI Accession Numbers are associated with a version number, -- which are REQUIRED for complete identification (w/o such, -- different records can have the same accession number, albeit -- with different version numbers) DatabaseName @varchar(255) -- The object we are linking TO resides in an external database of this name -- SD CONSTRAINT FK_XREF_OTHER_Chemical FOREIGN KEY (OtherWID) REFERENCES Chemical(WID) -- SD CONSTRAINT FK_XREF_OTHER_Reaction FOREIGN KEY (OtherWID) REFERENCES Reaction(WID) -- SD CONSTRAINT FK_XREF_OTHER_Protein FOREIGN KEY (OtherWID) REFERENCES Protein(WID) -- SD CONSTRAINT FK_XREF_OTHER_Feature FOREIGN KEY (OtherWID) REFERENCES Feature(WID) -- SD CONSTRAINT FK_XREF_OTHER_Function FOREIGN KEY (OtherWID) REFERENCES Function(WID) -- SD CONSTRAINT FK_XREF_OTHER_EnzymaticReaction FOREIGN KEY (OtherWID) REFERENCES EnzymaticReaction(WID) -- SD CONSTRAINT FK_XREF_OTHER_GeneticCode FOREIGN KEY (OtherWID) REFERENCES GeneticCode(WID) -- SD CONSTRAINT FK_XREF_OTHER_Division FOREIGN KEY (OtherWID) REFERENCES Division(WID) -- SD CONSTRAINT FK_XREF_OTHER_Taxon FOREIGN KEY (OtherWID) REFERENCES Taxon(WID) -- SD CONSTRAINT FK_XREF_OTHER_BioSource FOREIGN KEY (OtherWID) REFERENCES BioSource(WID) -- SD CONSTRAINT FK_XREF_OTHER_BioSubtype FOREIGN KEY (OtherWID) REFERENCES BioSubtype(WID) -- SD CONSTRAINT FK_XREF_OTHER_NucleicAcid FOREIGN KEY (OtherWID) REFERENCES NucleicAcid(WID) -- SD CONSTRAINT FK_XREF_OTHER_Subsequence FOREIGN KEY (OtherWID) REFERENCES Subsequence(WID) -- SD CONSTRAINT FK_XREF_OTHER_Gene FOREIGN KEY (OtherWID) REFERENCES Gene(WID) -- SD CONSTRAINT FK_XREF_OTHER_Pathway FOREIGN KEY (OtherWID) REFERENCES Pathway(WID) -- SD CONSTRAINT FK_XREF_OTHER_Term FOREIGN KEY (OtherWID) REFERENCES Term(WID) -- SD CONSTRAINT FK_XREF_OTHER_Computation FOREIGN KEY (OtherWID) REFERENCES Computation(WID) -- SD CONSTRAINT FK_XREF_OTHER_Citation FOREIGN KEY (OtherWID) REFERENCES Citation(WID) -- SD CONSTRAINT FK_XREF_OTHER_PointOfContact FOREIGN KEY (OtherWID) REFERENCES PointOfContact(WID) -- SD CONSTRAINT FK_XREF_OTHER_Archive FOREIGN KEY (OtherWID) REFERENCES Archive(WID) -- SD CONSTRAINT FK_XREF_OTHER_Experiment FOREIGN KEY (OtherWID) REFERENCES Experiment(WID) -- SD CONSTRAINT FK_XREF_OTHER_ExperimentData FOREIGN KEY (OtherWID) REFERENCES ExperimentData(WID) -- SD CONSTRAINT FK_XREF_OTHER_Support FOREIGN KEY (OtherWID) REFERENCES Support(WID) -- SD CONSTRAINT FK_XREF_CROSS_Chemical FOREIGN KEY (CrossWID) REFERENCES Chemical(WID) -- SD CONSTRAINT FK_XREF_CROSS_Reaction FOREIGN KEY (CrossWID) REFERENCES Reaction(WID) -- SD CONSTRAINT FK_XREF_CROSS_Protein FOREIGN KEY (CrossWID) REFERENCES Protein(WID) -- SD CONSTRAINT FK_XREF_CROSS_Feature FOREIGN KEY (CrossWID) REFERENCES Feature(WID) -- SD CONSTRAINT FK_XREF_CROSS_Function FOREIGN KEY (CrossWID) REFERENCES Function(WID) -- SD CONSTRAINT FK_XREF_CROSS_EnzymaticReaction FOREIGN KEY (CrossWID) REFERENCES EnzymaticReaction(WID) -- SD CONSTRAINT FK_XREF_CROSS_GeneticCode FOREIGN KEY (CrossWID) REFERENCES GeneticCode(WID) -- SD CONSTRAINT FK_XREF_CROSS_Division FOREIGN KEY (CrossWID) REFERENCES Division(WID) -- SD CONSTRAINT FK_XREF_CROSS_Taxon FOREIGN KEY (CrossWID) REFERENCES Taxon(WID) -- SD CONSTRAINT FK_XREF_CROSS_BioSource FOREIGN KEY (CrossWID) REFERENCES BioSource(WID) -- SD CONSTRAINT FK_XREF_CROSS_BioSubtype FOREIGN KEY (CrossWID) REFERENCES BioSubtype(WID) -- SD CONSTRAINT FK_XREF_CROSS_NucleicAcid FOREIGN KEY (CrossWID) REFERENCES NucleicAcid(WID) -- SD CONSTRAINT FK_XREF_CROSS_Subsequence FOREIGN KEY (CrossWID) REFERENCES Subsequence(WID) -- SD CONSTRAINT FK_XREF_CROSS_Gene FOREIGN KEY (CrossWID) REFERENCES Gene(WID) -- SD CONSTRAINT FK_XREF_CROSS_Pathway FOREIGN KEY (CrossWID) REFERENCES Pathway(WID) -- SD CONSTRAINT FK_XREF_CROSS_Term FOREIGN KEY (CrossWID) REFERENCES Term(WID) -- SD CONSTRAINT FK_XREF_CROSS_Computation FOREIGN KEY (CrossWID) REFERENCES Computation(WID) -- SD CONSTRAINT FK_XREF_CROSS_Citation FOREIGN KEY (CrossWID) REFERENCES Citation(WID) -- SD CONSTRAINT FK_XREF_CROSS_PointOfContact FOREIGN KEY (CrossWID) REFERENCES PointOfContact(WID) -- SD CONSTRAINT FK_XREF_CROSS_Archive FOREIGN KEY (CrossWID) REFERENCES Archive(WID) -- SD CONSTRAINT FK_XREF_CROSS_Experiment FOREIGN KEY (CrossWID) REFERENCES Experiment(WID) -- SD CONSTRAINT FK_XREF_CROSS_ExperimentData FOREIGN KEY (CrossWID) REFERENCES ExperimentData(WID) -- SD CONSTRAINT FK_XREF_CROSS_Support FOREIGN KEY (CrossWID) REFERENCES Support(WID) ) @tabletype; -- This table contains a textual description of a warehouse object. -- An object will not have more than one description, and the description text -- will typically define or otherwise characterize the object. -- See the CommentTable table for a more general type of comment. @- @string10 chosen for length so that Comm can be indexed. -- CREATE TABLE Description ( OtherWID @wid NOT NULL, -- Warehouse id of an object TableName @varchar(30) NOT NULL, -- Name of Warehouse table containing OtherWID Comm @string10 -- Description of the object -- SD CONSTRAINT FK_DESCRIP_Chemical FOREIGN KEY (OtherWID) REFERENCES Chemical(WID) -- SD CONSTRAINT FK_DESCRIP_Reaction FOREIGN KEY (OtherWID) REFERENCES Reaction(WID) -- SD CONSTRAINT FK_DESCRIP_Protein FOREIGN KEY (OtherWID) REFERENCES Protein(WID) -- SD CONSTRAINT FK_DESCRIP_Feature FOREIGN KEY (OtherWID) REFERENCES Feature(WID) -- SD CONSTRAINT FK_DESCRIP_Function FOREIGN KEY (OtherWID) REFERENCES Function(WID) -- SD CONSTRAINT FK_DESCRIP_EnzymaticReaction FOREIGN KEY (OtherWID) REFERENCES EnzymaticReaction(WID) -- SD CONSTRAINT FK_DESCRIP_GeneticCode FOREIGN KEY (OtherWID) REFERENCES GeneticCode(WID) -- SD CONSTRAINT FK_DESCRIP_Division FOREIGN KEY (OtherWID) REFERENCES Division(WID) -- SD CONSTRAINT FK_DESCRIP_Taxon FOREIGN KEY (OtherWID) REFERENCES Taxon(WID) -- SD CONSTRAINT FK_DESCRIP_BioSource FOREIGN KEY (OtherWID) REFERENCES BioSource(WID) -- SD CONSTRAINT FK_DESCRIP_BioSubtype FOREIGN KEY (OtherWID) REFERENCES BioSubtype(WID) -- SD CONSTRAINT FK_DESCRIP_NucleicAcid FOREIGN KEY (OtherWID) REFERENCES NucleicAcid(WID) -- SD CONSTRAINT FK_DESCRIP_Subsequence FOREIGN KEY (OtherWID) REFERENCES Subsequence(WID) -- SD CONSTRAINT FK_DESCRIP_Gene FOREIGN KEY (OtherWID) REFERENCES Gene(WID) -- SD CONSTRAINT FK_DESCRIP_Pathway FOREIGN KEY (OtherWID) REFERENCES Pathway(WID) -- SD CONSTRAINT FK_DESCRIP_Term FOREIGN KEY (OtherWID) REFERENCES Term(WID) -- SD CONSTRAINT FK_DESCRIP_Computation FOREIGN KEY (OtherWID) REFERENCES Computation(WID) -- SD CONSTRAINT FK_DESCRIP_Citation FOREIGN KEY (OtherWID) REFERENCES Citation(WID) -- SD CONSTRAINT FK_DESCRIP_PointOfContact FOREIGN KEY (OtherWID) REFERENCES PointOfContact(WID) -- SD CONSTRAINT FK_DESCRIP_Archive FOREIGN KEY (OtherWID) REFERENCES Archive(WID) -- SD CONSTRAINT FK_DESCRIP_Experiment FOREIGN KEY (OtherWID) REFERENCES Experiment(WID) -- SD CONSTRAINT FK_DESCRIP_ExperimentData FOREIGN KEY (OtherWID) REFERENCES ExperimentData(WID) -- SD CONSTRAINT FK_DESCRIP_Support FOREIGN KEY (OtherWID) REFERENCES Support(WID) ) @tabletype; -- Associates a warehouse entity with the identifier(s) used for that entity in its -- source dataset. For example, if a protein is loaded from SwissProt into the -- warehouse, this table can be used to store the SwissProt accession numbers for -- the protein. @- 3.0 Added Type, Version -- CREATE TABLE DBID ( OtherWID @wid NOT NULL, -- Reference to the warehouse entity uniquely identified by this entry XID @varchar(50) NOT NULL, -- Unique identifier used in dataset of entity Type @varchar(20), -- Enumeration: Describes the types of identifiers stored in -- XID for datasets whose entries are identified with IDs -- of different types, such as 'GUID' or 'Accession'. -- See Enumeration table for further details. Version @varchar(10) -- Further qualifies the values of identifiers stored in XID by -- storing the version number of the identifier for datasets that -- support multiple instances of the same object. For example, -- NCBI Accession Numbers are associated with a version number, -- which are REQUIRED for complete identification (w/o such, -- different records can have the same accession number, albeit -- with different version numbers) -- SD CONSTRAINT FK_DBID_Chemical FOREIGN KEY (OtherWID) REFERENCES Chemical(WID) -- SD CONSTRAINT FK_DBID_Reaction FOREIGN KEY (OtherWID) REFERENCES Reaction(WID) -- SD CONSTRAINT FK_DBID_Protein FOREIGN KEY (OtherWID) REFERENCES Protein(WID) -- SD CONSTRAINT FK_DBID_Feature FOREIGN KEY (OtherWID) REFERENCES Feature(WID) -- SD CONSTRAINT FK_DBID_Function FOREIGN KEY (OtherWID) REFERENCES Function(WID) -- SD CONSTRAINT FK_DBID_EnzymaticReaction FOREIGN KEY (OtherWID) REFERENCES EnzymaticReaction(WID) -- SD CONSTRAINT FK_DBID_GeneticCode FOREIGN KEY (OtherWID) REFERENCES GeneticCode(WID) -- SD CONSTRAINT FK_DBID_Division FOREIGN KEY (OtherWID) REFERENCES Division(WID) -- SD CONSTRAINT FK_DBID_Taxon FOREIGN KEY (OtherWID) REFERENCES Taxon(WID) -- SD CONSTRAINT FK_DBID_BioSource FOREIGN KEY (OtherWID) REFERENCES BioSource(WID) -- SD CONSTRAINT FK_DBID_BioSubtype FOREIGN KEY (OtherWID) REFERENCES BioSubtype(WID) -- SD CONSTRAINT FK_DBID_NucleicAcid FOREIGN KEY (OtherWID) REFERENCES NucleicAcid(WID) -- SD CONSTRAINT FK_DBID_Subsequence FOREIGN KEY (OtherWID) REFERENCES Subsequence(WID) -- SD CONSTRAINT FK_DBID_Gene FOREIGN KEY (OtherWID) REFERENCES Gene(WID) -- SD CONSTRAINT FK_DBID_Pathway FOREIGN KEY (OtherWID) REFERENCES Pathway(WID) -- SD CONSTRAINT FK_DBID_Term FOREIGN KEY (OtherWID) REFERENCES Term(WID) -- SD CONSTRAINT FK_DBID_Computation FOREIGN KEY (OtherWID) REFERENCES Computation(WID) -- SD CONSTRAINT FK_DBID_Citation FOREIGN KEY (OtherWID) REFERENCES Citation(WID) -- SD CONSTRAINT FK_DBID_PointOfContact FOREIGN KEY (OtherWID) REFERENCES PointOfContact(WID) -- SD CONSTRAINT FK_DBID_Archive FOREIGN KEY (OtherWID) REFERENCES Archive(WID) -- SD CONSTRAINT FK_DBID_Experiment FOREIGN KEY (OtherWID) REFERENCES Experiment(WID) -- SD CONSTRAINT FK_DBID_ExperimentData FOREIGN KEY (OtherWID) REFERENCES ExperimentData(WID) -- SD CONSTRAINT FK_DBID_Support FOREIGN KEY (OtherWID) REFERENCES Support(WID) ) @tabletype; CREATE INDEX DBID_XID_OTHERWID ON DBID(XID, OTHERWID) @index-tablespace ; CREATE INDEX DBID_OTHERWID ON DBID(OTHERWID) @index-tablespace ; -- Defines one or more synonyms for a warehouse entity such as a protein, a -- gene, a small molecule, or a pathway. -- CREATE TABLE SynonymTable ( OtherWID @wid NOT NULL, -- Warehouse ID of an entity Syn @varchar(255) NOT NULL -- Another name for the entity -- SD CONSTRAINT FK_SYNONYM_Chemical FOREIGN KEY (OtherWID) REFERENCES Chemical(WID) -- SD CONSTRAINT FK_SYNONYM_Reaction FOREIGN KEY (OtherWID) REFERENCES Reaction(WID) -- SD CONSTRAINT FK_SYNONYM_Protein FOREIGN KEY (OtherWID) REFERENCES Protein(WID) -- SD CONSTRAINT FK_SYNONYM_Feature FOREIGN KEY (OtherWID) REFERENCES Feature(WID) -- SD CONSTRAINT FK_SYNONYM_Function FOREIGN KEY (OtherWID) REFERENCES Function(WID) -- SD CONSTRAINT FK_SYNONYM_EnzymaticReaction FOREIGN KEY (OtherWID) REFERENCES EnzymaticReaction(WID) -- SD CONSTRAINT FK_SYNONYM_GeneticCode FOREIGN KEY (OtherWID) REFERENCES GeneticCode(WID) -- SD CONSTRAINT FK_SYNONYM_Division FOREIGN KEY (OtherWID) REFERENCES Division(WID) -- SD CONSTRAINT FK_SYNONYM_Taxon FOREIGN KEY (OtherWID) REFERENCES Taxon(WID) -- SD CONSTRAINT FK_SYNONYM_BioSource FOREIGN KEY (OtherWID) REFERENCES BioSource(WID) -- SD CONSTRAINT FK_SYNONYM_BioSubtype FOREIGN KEY (OtherWID) REFERENCES BioSubtype(WID) -- SD CONSTRAINT FK_SYNONYM_NucleicAcid FOREIGN KEY (OtherWID) REFERENCES NucleicAcid(WID) -- SD CONSTRAINT FK_SYNONYM_Subsequence FOREIGN KEY (OtherWID) REFERENCES Subsequence(WID) -- SD CONSTRAINT FK_SYNONYM_Gene FOREIGN KEY (OtherWID) REFERENCES Gene(WID) -- SD CONSTRAINT FK_SYNONYM_Pathway FOREIGN KEY (OtherWID) REFERENCES Pathway(WID) -- SD CONSTRAINT FK_SYNONYM_Term FOREIGN KEY (OtherWID) REFERENCES Term(WID) -- SD CONSTRAINT FK_SYNONYM_Computation FOREIGN KEY (OtherWID) REFERENCES Computation(WID) -- SD CONSTRAINT FK_SYNONYM_Citation FOREIGN KEY (OtherWID) REFERENCES Citation(WID) -- SD CONSTRAINT FK_SYNONYM_PointOfContact FOREIGN KEY (OtherWID) REFERENCES PointOfContact(WID) -- SD CONSTRAINT FK_SYNONYM_Archive FOREIGN KEY (OtherWID) REFERENCES Archive(WID) -- SD CONSTRAINT FK_SYNONYM_Experiment FOREIGN KEY (OtherWID) REFERENCES Experiment(WID) -- SD CONSTRAINT FK_SYNONYM_ExperimentData FOREIGN KEY (OtherWID) REFERENCES ExperimentData(WID) -- SD CONSTRAINT FK_SYNONYM_Support FOREIGN KEY (OtherWID) REFERENCES Support(WID) ) @tabletype; CREATE INDEX SYNONYM_OTHERWID_SYN ON SynonymTable( OTHERWID , SYN) @index-tablespace; -- This table captures meta-data that does not have an interpretation in -- the warehouse semantics, but is `hints' to various tools that may -- operate on, or build pictorial representations of, core elements of the -- schema. For example, it may give hints to a particular pathway viewing -- tool on how to layout a pathway. -- CREATE TABLE ToolAdvice ( OtherWID @wid NOT NULL, -- The entity to which this advice applies ToolName @varchar(50) NOT NULL, -- The name of the tool Advice @string32 -- The advice -- SD CONSTRAINT FK_TOOL_Chemical FOREIGN KEY (OtherWID) REFERENCES Chemical(WID) -- SD CONSTRAINT FK_TOOL_Reaction FOREIGN KEY (OtherWID) REFERENCES Reaction(WID) -- SD CONSTRAINT FK_TOOL_Protein FOREIGN KEY (OtherWID) REFERENCES Protein(WID) -- SD CONSTRAINT FK_TOOL_Feature FOREIGN KEY (OtherWID) REFERENCES Feature(WID) -- SD CONSTRAINT FK_TOOL_Function FOREIGN KEY (OtherWID) REFERENCES Function(WID) -- SD CONSTRAINT FK_TOOL_EnzymaticReaction FOREIGN KEY (OtherWID) REFERENCES EnzymaticReaction(WID) -- SD CONSTRAINT FK_TOOL_GeneticCode FOREIGN KEY (OtherWID) REFERENCES GeneticCode(WID) -- SD CONSTRAINT FK_TOOL_Division FOREIGN KEY (OtherWID) REFERENCES Division(WID) -- SD CONSTRAINT FK_TOOL_Taxon FOREIGN KEY (OtherWID) REFERENCES Taxon(WID) -- SD CONSTRAINT FK_TOOL_BioSource FOREIGN KEY (OtherWID) REFERENCES BioSource(WID) -- SD CONSTRAINT FK_TOOL_BioSubtype FOREIGN KEY (OtherWID) REFERENCES BioSubtype(WID) -- SD CONSTRAINT FK_TOOL_NucleicAcid FOREIGN KEY (OtherWID) REFERENCES NucleicAcid(WID) -- SD CONSTRAINT FK_TOOL_Subsequence FOREIGN KEY (OtherWID) REFERENCES Subsequence(WID) -- SD CONSTRAINT FK_TOOL_Gene FOREIGN KEY (OtherWID) REFERENCES Gene(WID) -- SD CONSTRAINT FK_TOOL_Pathway FOREIGN KEY (OtherWID) REFERENCES Pathway(WID) -- SD CONSTRAINT FK_TOOL_Term FOREIGN KEY (OtherWID) REFERENCES Term(WID) -- SD CONSTRAINT FK_TOOL_Computation FOREIGN KEY (OtherWID) REFERENCES Computation(WID) -- SD CONSTRAINT FK_TOOL_Citation FOREIGN KEY (OtherWID) REFERENCES Citation(WID) -- SD CONSTRAINT FK_TOOL_PointOfContact FOREIGN KEY (OtherWID) REFERENCES PointOfContact(WID) -- SD CONSTRAINT FK_TOOL_Archive FOREIGN KEY (OtherWID) REFERENCES Archive(WID) -- SD CONSTRAINT FK_TOOL_Experiment FOREIGN KEY (OtherWID) REFERENCES Experiment(WID) -- SD CONSTRAINT FK_TOOL_ExperimentData FOREIGN KEY (OtherWID) REFERENCES ExperimentData(WID) -- SD CONSTRAINT FK_TOOL_Support FOREIGN KEY (OtherWID) REFERENCES Support(WID) ) @tabletype; @- ----------------------------------------------------------------------------- @- Tables that (only) link WIDs from two object tables. @- These tables implement many-many relations between Warehouse objects. @- Tables are listed in alphabetical order. @- Naming convention is WIDWID @- ----------------------------------------------------------------------------- -- Linking table which links BioSource and BioSubtype @- Created by YP 4/27/04 @- Added Version 3.0 -- CREATE TABLE BioSourceWIDBioSubtypeWID ( BioSourceWID @wid NOT NULL, -- Links to the BioSource associated with this object. BioSubtypeWID @wid NOT NULL @fkeycomma -- Links to the BioSubtype associated with this object. -- @FKEY CONSTRAINT FK_BioSourceWIDBioSubtypeWID1 FOREIGN KEY (BioSourceWID) REFERENCES BioSource(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_BioSourceWIDBioSubtypeWID2 FOREIGN KEY (BioSubtypeWID) REFERENCES BioSubtype(WID) ON DELETE CASCADE ) @tabletype; -- Associates a gene with those species, strains, tissues, etc, from which it was derived. -- CREATE TABLE BioSourceWIDGeneWID ( BioSourceWID @wid NOT NULL, -- Reference to the BioSource that has this gene. GeneWID @wid NOT NULL @fkeycomma -- Reference to the Gene that is present in the organism. -- @FKEY CONSTRAINT FK_BioSourceWIDGeneWID1 FOREIGN KEY (BioSourceWID) REFERENCES BioSource(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_BioSourceWIDGeneWID2 FOREIGN KEY (GeneWID) REFERENCES Gene(WID) ON DELETE CASCADE ) @tabletype; -- Associates a protein with those species, strains, tissues, etc, from which it was derived. -- CREATE TABLE BioSourceWIDProteinWID ( BioSourceWID @wid NOT NULL, -- Reference to the BioSource that has this protein. ProteinWID @wid NOT NULL @fkeycomma -- Reference to the Protein that is present in the organism. -- @FKEY CONSTRAINT FK_BioSourceWIDProteinWID1 FOREIGN KEY (BioSourceWID) REFERENCES BioSource(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_BioSourceWIDProteinWID2 FOREIGN KEY (ProteinWID) REFERENCES Protein(WID) ON DELETE CASCADE ) @tabletype; -- Associates a gene with its nucleic acid product(s). -- Note that the nucleic acid (ie. replicon) containing the gene is referenced by Gene.NucleicAcidWID, -- not in this table. -- CREATE TABLE GeneWIDNucleicAcidWID ( GeneWID @wid NOT NULL, -- Reference to the Gene that produces this nucleic acid. NucleicAcidWID @wid NOT NULL @fkeycomma -- Reference to the nucleic acid that has this gene. -- @FKEY CONSTRAINT FK_GeneWIDNucleicAcidWID1 FOREIGN KEY (GeneWID) REFERENCES Gene(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_GeneWIDNucleicAcidWID2 FOREIGN KEY (NucleicAcidWID) REFERENCES NucleicAcid(WID) ON DELETE CASCADE ) @tabletype; -- Associates a gene with its protein product(s). -- CREATE TABLE GeneWIDProteinWID ( GeneWID @wid NOT NULL, -- Reference to the Gene that produces this protein. ProteinWID @wid NOT NULL @fkeycomma -- Reference to the protein that has this gene. -- @FKEY CONSTRAINT FK_GeneWIDProteinWID1 FOREIGN KEY (GeneWID) REFERENCES Gene(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_GeneWIDProteinWID2 FOREIGN KEY (ProteinWID) REFERENCES Protein(WID) ON DELETE CASCADE ) @tabletype; -- Associates a non-enzymatic function with a protein that has that function. -- CREATE TABLE ProteinWIDFunctionWID ( ProteinWID @wid NOT NULL, -- Reference to the protein that has this function. FunctionWID @wid NOT NULL @fkeycomma -- Reference to the function for this protein. -- @FKEY CONSTRAINT FK_ProteinWIDFunctionWID2 FOREIGN KEY (ProteinWID) REFERENCES Protein(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_ProteinWIDFunctionWID3 FOREIGN KEY (FunctionWID) REFERENCES Function(WID) ON DELETE CASCADE ) @tabletype; -- Defines a parent/child relationship between two terms. -- CREATE TABLE TermWIDParentWID ( TermWID @wid NOT NULL, -- Reference to a term. ParentWID @wid NOT NULL @fkeycomma -- Reference to a parent/owner of the term. -- @FKEY CONSTRAINT FK_TermWIDParentWID1 FOREIGN KEY (TermWID) REFERENCES Term(WID) ON DELETE CASCADE, @FKEY CONSTRAINT FK_TermWIDParentWID2 FOREIGN KEY (ParentWID) REFERENCES Term(WID) ON DELETE CASCADE ) @tabletype; commit;