-- ============================================================================================== -- Author: ALIREZA AHADI -- Create date: AUGUST, THE 8TH, 2016 -- Description: TABLE DESCRIPTOR FOR MIRTAR2GO PREDICTION RESULT ENTITIES AND THE ADDITIONAL DATA -- ============================================================================================== -- --In order to upload the prediction result, gene2mRNA mapping, microRNA expression values and the hipathDB data into the SQL server, first create the required tables by running the CREATE TABLE script, --and then upload the INSERT STATEMENTS script into the SQL server. These scripts are generated by SMSS tools pack version 4.0 under SQL server version 2008 R. -- --Upon successful run of the script file, the following tables are created: -- -- -- -- -- ***ADOPTED FROM HIPATHDB, MORE INFORMATION AVAILABLE AT http://hipathdb.kobic.re.kr*** CREATE TABLE [unified_pathway_relationReference]( /* pathway information [pathwayId] [varchar](50) NOT NULL, /* unique pathway ID on hiPathDB [referenceId] [varchar](max) NULL, /* reference identifier [referenceDbName] [varchar](max) NULL, /* the name of the database where the pathway is [pathwayDbId] [varchar](max) NOT NULL /* database identifier: KEGG, BioCarta, Nci-Nature, Reactome ) -- -- -- -- ***ADOPTED FROM HIPATHDB, MORE INFORMATION AVAILABLE AT http://hipathdb.kobic.re.kr*** CREATE TABLE [unified_pathway_relationPair]( /* node-level information of pathway components [pathwayId] [varchar](max) NULL, /* unique pathway ID on hiPathDB [interactionId] [varchar](max) NULL, /* unique identifier of the interaction between two nodes [interactionType] [varchar](max) NULL, /* type of interaction [nodeA] [varchar](max) NULL, /* head of the interaction [nodeB] [varchar](max) NULL, /* tail of the interaction [controlType] [varchar](max) NULL, /* [pathwaydbId] [int] NULL /* database identifier: KEGG, BioCarta, Nci-Nature, Reactome ) -- -- -- -- ***ADOPTED FROM HIPATHDB, MORE INFORMATION AVAILABLE AT http://hipathdb.kobic.re.kr*** CREATE TABLE [unified_pathway_nameList]( /* [pathwayId] [varchar](300) NULL, /* unique pathway ID on hiPathDB [pathwayName] [varchar](300) NULL, /* the name of the pathway [pathwayDbId] [varchar](50) NULL, /* database identifier: KEGG, BioCarta, Nci-Nature, Reactome [pId] [int] NOT NULL /* ) -- -- -- -- ***ADOPTED FROM HIPATHDB, MORE INFORMATION AVAILABLE AT http://hipathdb.kobic.re.kr*** CREATE TABLE [unified_pathway_abstractNode]( /* [pathwayId] [varchar](300) NULL, /* name of the pathway [superNodeId] [varchar](300) NULL, /* name of the super node [pathwayDbId] [varchar](50) NULL /* database identifier: KEGG, BioCarta, Nci-Nature, Reactome ) -- -- -- -- -- ***ADOPTED FROM HIPATHDB, MORE INFORMATION AVAILABLE AT http://hipathdb.kobic.re.kr*** CREATE TABLE [unified_abstract_node_information]( /* [abstractNodeId] [varchar](430) NULL, /* [nodeName] [varchar](430) NULL, /* [nodeType] [varchar](430) NULL, /* [publicId] [varchar](430) NULL, /* [publicIddbName] [varchar](430) NULL /* ) -- -- -- -- CREATE TABLE [RNAhybridCutOff_valueLess_20]( /* [Target_mRNA_RefSeq] [varchar](50) NULL, /* predicted target mRNA transcript ID [Gene_Name] [varchar](50) NULL, /* name of the predicted target gene [Target_mRNA_Chr] [varchar](50) NULL, /* chromosome on which the gene is located [Target_mRNA_Strand] [varchar](50) NULL, /* chromosome strand on which is the gene is located [Target_mRNA_Start] [int] NULL, /* start coordinate of predicted target mRNA (hg19) [Target_mRNA_Stop] [int] NULL, /* stop coordinate of predicted target mRNA (hg19) [MicroRNA_ID] [varchar](50) NULL, /* miRBase ID for the microRNA [MirNA_Conservation_Family] [varchar](50) NULL, /* miRBase conservation family for the microRNA [CLIP_Start] [int] NULL, /* start coordinate of the AGO interaction on the 3' UTR [CLIP_Stop] [int] NULL, /* stop coordinate of the AGO interaction on the 3' UTR [mRNA_Exon_Start] [int] NULL, /* start coordinate of the exon on which AGO is CLIPing the 3' UTR [mRNA_Exon_Stop] [int] NULL, /* stop coordinate of the exon on which AGO is CLIPing the 3' UTR [Binding_Site_Seq] [varchar](50) NULL, /* a selected 40 nucleotide strand on which the seed match is identified. The seed match starts from the second nucleotide [microRNA_Reversed_Seed] [varchar](50) NULL, /* reverse complementary sequence of the microRNA's seed (6-mer) region [Match_Distance2CLIP_3p] [int] NULL, /* seed match offset to the beginning of the CLIPed site from the 3' UTR [Match_Distance2CLIP_5p] [int] NULL, /* seed match offset to the beginning of the CLIPed site from the 5' UTR [MicroRNA_Seq] [varchar](50) NULL, /* genomic sequence of the microRNA [Hybridization_Energy] [float] NULL, /* the hybridisation energy (minimum free energy) between the microRNA and the 40 nt predicted binding site [Strength_Score] [float] NULL, /* the confidence score [BC1] [varchar](50) NULL, /* cell line flag for BC1 [EF3D_A2] [varchar](50) NULL, /* cell line flag for EF3D [hESC] [varchar](50) NULL, /* cell line flag for human steam cell [HeLa] [varchar](50) NULL, /* cell line flag for HeLa [_293s] [varchar](50) NULL, /* cell line flag for 293s [Human_Lymphoblastic] [varchar](50) NULL, /* cell line flag for lymphoblastic [CCR_Conservation_Score] [float] NULL /* ) -- -- -- -- CREATE TABLE [Result_Ranked]( /* refer to RNAhybridCutOff_valueLess_20 attribute descriptors [Target_mRNA_RefSeq] [varchar](50) NULL, /* [Gene_Name] [varchar](50) NULL, /* [Target_mRNA_Chr] [varchar](50) NULL, /* [Target_mRNA_Strand] [varchar](50) NULL, /* [Target_mRNA_Start] [int] NULL, /* [Target_mRNA_Stop] [int] NULL, /* [MicroRNA_ID] [varchar](50) NULL, /* [MirNA_Conservation_Family] [varchar](50) NULL, /* [CLIP_Start] [int] NULL, /* [CLIP_Stop] [int] NULL, /* [mRNA_Exon_Start] [int] NULL, /* [mRNA_Exon_Stop] [int] NULL, /* [Binding_Site_Seq] [varchar](50) NULL, /* [microRNA_Reversed_Seed] [varchar](50) NULL, /* [Match_Distance2CLIP_3p] [int] NULL, /* [Match_Distance2CLIP_5p] [int] NULL, /* [MicroRNA_Seq] [varchar](50) NULL, /* [Hybridization_Energy] [float] NULL, /* [Strength_Score] [float] NULL, /* [BC1] [varchar](50) NULL, /* [EF3D_A2] [varchar](50) NULL, /* [hESC] [varchar](50) NULL, /* [HeLa] [varchar](50) NULL, /* [_293s] [varchar](50) NULL, /* [Human_Lymphoblastic] [varchar](50) NULL, /* [CCR_Conservation_Score] [float] NULL /* ) -- -- -- -- ***ADOPTED FROM HIPATHDB, MORE INFORMATION AVAILABLE AT http://hipathdb.kobic.re.kr*** CREATE TABLE [Reactome]( /* [Ensembl] [varchar](255) NULL, /* [Id] [varchar](255) NULL, /* [Link] [varchar](255) NULL, /* [Descriptions] [varchar](255) NULL, /* [Tag] [varchar](255) NULL, /* [Species] [varchar](255) NULL /* ) -- -- -- -- CREATE TABLE [Optimistic_RNAhybridCut_off]( /* refer to RNAhybridCutOff_valueLess_20 attribute descriptors [Target_mRNA_RefSeq] [varchar](50) NULL, /* [Gene_Name] [varchar](50) NULL, /* [Target_mRNA_Chr] [varchar](50) NULL, /* [Target_mRNA_Strand] [varchar](50) NULL, /* [Target_mRNA_Start] [int] NULL, /* [Target_mRNA_Stop] [int] NULL, /* [MicroRNA_ID] [varchar](50) NULL, /* [MirNA_Conservation_Family] [varchar](50) NULL, /* [CLIP_Start] [int] NULL, /* [CLIP_Stop] [int] NULL, /* [mRNA_Exon_Start] [int] NULL, /* [mRNA_Exon_Stop] [int] NULL, /* [Binding_Site_Seq] [varchar](50) NULL, /* [microRNA_Reversed_Seed] [varchar](50) NULL, /* [Match_Distance2CLIP_3p] [int] NULL, /* [Match_Distance2CLIP_5p] [int] NULL, /* [MicroRNA_Seq] [varchar](50) NULL, /* [Hybridization_Energy] [float] NULL, /* [Strength_Score] [float] NULL, /* [BC1] [varchar](50) NULL, /* [EF3D_A2] [varchar](50) NULL, /* [hESC] [varchar](50) NULL, /* [HeLa] [varchar](50) NULL, /* [_293s] [varchar](50) NULL, /* [Human_Lymphoblastic] [varchar](50) NULL, /* [CCR_Conservation_Score] [float] NULL /* ) -- -- -- -- -- CREATE TABLE [Optimistic_Predictions]( /* refer to RNAhybridCutOff_valueLess_20 attribute descriptors [Target_mRNA_RefSeq] [varchar](50) NULL, /* [Gene_Name] [varchar](50) NULL, /* [Target_mRNA_Chr] [varchar](50) NULL, /* [Target_mRNA_Strand] [varchar](50) NULL, /* [Target_mRNA_Start] [int] NULL, /* [Target_mRNA_Stop] [int] NULL, /* [MicroRNA_ID] [varchar](50) NULL, /* [MirNA_Conservation_Family] [varchar](50) NULL, /* [CLIP_Start] [int] NULL, /* [CLIP_Stop] [int] NULL, /* [mRNA_Exon_Start] [int] NULL, /* [mRNA_Exon_Stop] [int] NULL, /* [Binding_Site_Seq] [varchar](50) NULL, /* [microRNA_Reversed_Seed] [varchar](50) NULL, /* [Match_Distance2CLIP_3p] [int] NULL, /* [Match_Distance2CLIP_5p] [int] NULL, /* [MicroRNA_Seq] [varchar](50) NULL, /* [Hybridization_Energy] [float] NULL, /* [Strength_Score] [float] NULL, /* [BC1] [varchar](50) NULL, /* [EF3D_A2] [varchar](50) NULL, /* [hESC] [varchar](50) NULL, /* [HeLa] [varchar](50) NULL, /* [_293s] [varchar](50) NULL, /* [Human_Lymphoblastic] [varchar](50) NULL, /* [CCR_Conservation_Score] [float] NULL /* ) -- -- -- -- CREATE TABLE [MicroRNA_Expression]( /* Normalised re-scaled microRNA expression values in different cell lines [MicroRNA_ID] [varchar](50) NULL, /* miRBase ID for the microRNA [_hE] [float] NULL, /* microRNA expression value in hESC [BC_EF_Hu] [float] NULL, /* average microRNA expression value in lymph cells [HE] [float] NULL, /* microRNA expression value in HeLa [_29] [float] NULL /* microRNA expression value in 293s ) -- -- -- -- CREATE TABLE [Gene_ID_Map_Ensembl]( /* The map of gene names to their corresponding Ensembl mRNA transcript IDs and gene IDs [Ensembl_Gene_ID] [varchar](50) NULL, /* gene accession ID on Ensembl [Ensembl_Transcript_ID] [varchar](50) NULL, /* transcript accession ID on Ensembl [Gene_Name] [varchar](50) NULL /* the official name of the gene ) -- -- -- -- CREATE TABLE [Gene_ID_Map]( /* The map of gene names to their corresponding Entrez IDs and RefSqe transcript IDs [Gene_Name] [varchar](100) NULL, /* the official name of the gene [Entrez_ID] [varchar](100) NULL, /* Entrez accession ID for the gene [Target_mRNA_RefSeq] [varchar](100) NULL /* RefSeq transcript ID for the corresponding mRNA ) -- -- -- --