[Bio] / BioSimScratch / ExcelBasedECTables.sql Repository:
ViewVC logotype

Annotation of /BioSimScratch/ExcelBasedECTables.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.8 - (view) (download)

1 : efrank 1.1 -- ----------------------------------------------------------------------
2 :     drop table ECMetabolite;
3 :     create table ECMetabolite (
4 :     --
5 :     -- Lists all metabolites in the excel spreadsheet based E. coli
6 :     -- reconstruction giving both short names and long names. The
7 :     -- short name is a key
8 :     -- ----------------------------------------------------------------------
9 :     --
10 : efrank 1.8 ShortName varchar2(512) NOT NULL,
11 : efrank 1.1 /* A short name for the metabolite */
12 :     --
13 : efrank 1.8 LongName varchar(512) NOT NULL,
14 : efrank 1.1 /* The full name (in someones universe) */
15 :     --
16 : efrank 1.2 CONSTRAINT ECMetabolite_PK PRIMARY KEY (ShortName, LongName)
17 : efrank 1.1 );
18 :    
19 :     -- ----------------------------------------------------------------------
20 :     drop table ECReactions;
21 :     create table ECReactions (
22 :     --
23 :     -- Defines the reaction numbers (just integers that serve as ids)
24 :     -- and gives the direction of the reaction (forward, backward, both).
25 :     -- Relations will be used to construct the reactions.
26 :     -- ----------------------------------------------------------------------
27 :     --
28 :     RxId Number(6) NOT NULL,
29 :     /* Reaction Id */
30 :     --
31 : efrank 1.2 Direction varchar2(8) NOT NULL,
32 : efrank 1.1 /* need constraint of 'forward' 'backward' 'both' */
33 : efrank 1.2 --
34 :     CONSTRAINT ECReactions_PK PRIMARY KEY ( RxId )
35 : efrank 1.1 );
36 :    
37 :     -- ----------------------------------------------------------------------
38 :     drop table ECEnzymeRole;
39 :     create table ECEnzymeRole (
40 :     --
41 :     -- Gives the name of a catalyst (required), a corresponding
42 :     -- function (EC number), if available, and an EnzymeRoleId.
43 :     -- The latter is just an integer id used to build our relations.
44 :     -- The notion in this table is a catalyst/enzyme in a specific
45 :     -- role. So, if an enzyme servers multiple purposes in a
46 :     -- reconstruction, there will be multiple entries in this table.
47 :     -- ----------------------------------------------------------------------
48 :     --
49 :     EnzymeRoleId number(6) NOT NULL,
50 :     /* a simple integer to serve as key */
51 :     --
52 : efrank 1.8 Function varchar(512),
53 : efrank 1.1 /* EC number in format nn.nn.nn.nn . All 4 must */
54 :     /* be present. Use the literal '--' for missing parts */
55 :     /* The whole Function entry _may_ be null. */
56 :     --
57 : efrank 1.8 EnzymeName varchar(512) NOT NULL,
58 : efrank 1.1 /* Enzyme name */
59 : efrank 1.2 --
60 : efrank 1.3 CONSTRAINT ECEnzymeRole_PK PRIMARY KEY ( EnzymeRoleId )
61 : efrank 1.1 );
62 :    
63 :     -- ----------------------------------------------------------------------
64 :     drop table ECRxInput;
65 :     create table ECRxInput (
66 :     --
67 :     -- A relation that ties reactions to metabolites that are inputs
68 : efrank 1.5 -- to the reaction. The constraint does not work because of
69 :     -- Seed/KEGG
70 : efrank 1.1 -- ----------------------------------------------------------------------
71 :     --
72 :     RxId Number(6) NOT NULL,
73 :     /* Reaction ID that we are binding metabolite to */
74 :     --
75 : efrank 1.7 Stoich float(32) NOT NULL,
76 : efrank 1.1 /* number of molecules of this kind in reaction */
77 :     --
78 : efrank 1.8 ShortName varchar2(512) NOT NULL
79 : efrank 1.2 --
80 : efrank 1.5 -- CONSTRAINT ECRxInput_PK PRIMARY KEY (RxId, ShortName)
81 : efrank 1.1 );
82 :    
83 :     -- ----------------------------------------------------------------------
84 :     drop table ECRxOutput;
85 :     create table ECRxOutput (
86 :     --
87 :     -- A relation that ties reactions to metabolites that are outputs
88 : efrank 1.5 -- to the reaction. The constraint does not work because of
89 :     -- Seed/KEGG
90 : efrank 1.1 -- ----------------------------------------------------------------------
91 :     --
92 :     RxId Number(6) NOT NULL,
93 :     /* Reaction ID that we are binding metabolite to */
94 :     --
95 : efrank 1.7 Stoich float(32) NOT NULL,
96 : efrank 1.1 /* number of molecules of this kind in reaction */
97 :     --
98 : efrank 1.8 ShortName varchar2(512) NOT NULL
99 : efrank 1.2 --
100 : efrank 1.5 -- CONSTRAINT ECRxOutput_PK PRIMARY KEY (RxId, ShortName)
101 : efrank 1.1 );
102 :    
103 :     -- ----------------------------------------------------------------------
104 :     drop table ECCatalyst;
105 :     create table ECCatalyst (
106 :     --
107 :     -- A relation that binds catalysts/enzymes to a specific reaction.
108 :     -- ----------------------------------------------------------------------
109 :     --
110 :     RxId Number(6) NOT NULL,
111 :     /* Reaction ID that we are binding enzyme to */
112 :     --
113 :     EnzymeRoleId number(6) NOT NULL
114 :     /* enzyme to associate */
115 :     );

MCS Webmaster
ViewVC Help
Powered by ViewVC 1.0.3