sTJJUF4V

1-- ----------------------------------------------------------------------
2-- MySQL Migration Toolkit
3-- SQL Create Script
4-- ----------------------------------------------------------------------
5
6SET FOREIGN_KEY_CHECKS = 0;
7SET UNIQUE_CHECKS = 0;
8
9CREATE DATABASE IF NOT EXISTS `swampdb2003`
10  CHARACTER SET latin1 COLLATE latin1_swedish_ci;
11USE `swampdb2003`;
12-- -------------------------------------
13-- Tables
14
15DROP TABLE IF EXISTS `swampdb2003`.`AgencyLookUp`;
16CREATE TABLE `swampdb2003`.`AgencyLookUp` (
17  `AgencyRowID` int(10) unsigned NOT NULL auto_increment,
18  `AgencyCode` VARCHAR(20) NULL,
19  `AgencyDescr` VARCHAR(150) NULL,
20  `PrimaryContact` VARCHAR(50) NULL,
21  `StreetAddress` VARCHAR(50) NULL,
22  `City` VARCHAR(50) NULL,
23  `State` VARCHAR(2) NULL,
24  `Zip` VARCHAR(10) NULL,
25  `Email` VARCHAR(50) NULL,
26  `Telephone` VARCHAR(25) NULL,
27  `Fax` VARCHAR(25) NULL,
28  `WebAddress` VARCHAR(75) NULL,
29  `Active` TINYINT(1) NOT NULL,
30  PRIMARY KEY (`AgencyRowID`),
31  UNIQUE INDEX `AgencyCode` (`AgencyCode`)
32)
33ENGINE = INNODB;
34
35
36DROP TABLE IF EXISTS `swampdb2003`.`AnalyteLookUp`;
37CREATE TABLE `swampdb2003`.`AnalyteLookUp` (
38  `AnalyteRowID` int(10) unsigned NOT NULL auto_increment,
39  `AnalyteCode` SMALLINT(5) NULL,
40  `AnalyteName` VARCHAR(125) NULL,
41  `CASNumber` DOUBLE(15, 5) NULL,
42  `AnalyteDescr` VARCHAR(150) NULL,
43  `Utility1` VARCHAR(25) NULL,
44  `Group1` VARCHAR(30) NULL,
45  `Group2` VARCHAR(30) NULL,
46  `Group3` VARCHAR(30) NULL,
47  `Group4` VARCHAR(30) NULL,
48  `Group5` VARCHAR(30) NULL,
49  `Group6` VARCHAR(30) NULL,
50  `Active` TINYINT(1) NOT NULL,
51  PRIMARY KEY (`AnalyteRowID`),
52  UNIQUE INDEX `AnalyteCode` (`AnalyteCode`),
53  UNIQUE INDEX `AnalyteName` (`AnalyteName`)
54)
55ENGINE = INNODB;
56
57DROP TABLE IF EXISTS `swampdb2003`.`BatchQualLookUp`;
58CREATE TABLE `swampdb2003`.`BatchQualLookUp` (
59  `BatchQualRowID` int(10) unsigned NOT NULL auto_increment,
60  `BatchQualCode` VARCHAR(10) NULL,
61  `BatchQualifier` VARCHAR(150) NULL,
62  `Active` TINYINT(1) NOT NULL,
63  PRIMARY KEY (`BatchQualRowID`),
64  UNIQUE INDEX `BatchQualCode` (`BatchQualCode`)
65)
66ENGINE = INNODB;
67
68DROP TABLE IF EXISTS `swampdb2003`.`BatchValLookUp`;
69CREATE TABLE `swampdb2003`.`BatchValLookUp` (
70  `BatchVerRowID` int(10) unsigned NOT NULL auto_increment,
71  `BatchValCode` VARCHAR(10) NULL,
72  `BatchVerification` VARCHAR(100) NULL,
73  `Active` TINYINT(1) NOT NULL,
74  PRIMARY KEY (`BatchVerRowID`),
75  UNIQUE INDEX `BatchValCode` (`BatchValCode`)
76)
77ENGINE = INNODB;
78
79DROP TABLE IF EXISTS `swampdb2003`.`ComplianceLevelLookUp`;
80CREATE TABLE `swampdb2003`.`ComplianceLevelLookUp` (
81  `ComplianceLevelRowID` int(10) unsigned NOT NULL auto_increment,
82  `ComplianceCode` VARCHAR(5) NULL,
83  `ComplianceDescr` VARCHAR(150) NULL,
84  `Active` TINYINT(1) NOT NULL,
85  PRIMARY KEY (`ComplianceLevelRowID`),
86  UNIQUE INDEX `ComplianceCode` (`ComplianceCode`)
87)
88ENGINE = INNODB;
89
90DROP TABLE IF EXISTS `swampdb2003`.`ConstituentLookUp`;
91CREATE TABLE `swampdb2003`.`ConstituentLookUp` (
92  `ConstituentRowID` int(10) unsigned NOT NULL auto_increment,
93  `ConstituentCode` VARCHAR(20) NULL,
94  `MatrixCode` SMALLINT(5) NULL,
95  `MethodCode` SMALLINT(5) NULL,
96  `AnalyteCode` SMALLINT(5) NULL,
97  `FractionCode` SMALLINT(5) NULL,
98  `UnitCode` SMALLINT(5) NULL,
99  `Active` TINYINT(1) NOT NULL,
100  PRIMARY KEY (`ConstituentRowID`),
101  UNIQUE INDEX `ConstituentCode` (`ConstituentCode`),
102  UNIQUE INDEX `UniqueConstraint` (`MatrixCode`, `MethodCode`, `AnalyteCode`, `FractionCode`, `UnitCode`),
103  CONSTRAINT `FK_AnalyteCode` FOREIGN KEY (`AnalyteCode`) REFERENCES `AnalyteLookUp` (`AnalyteCode`) ON UPDATE CASCADE,
104  CONSTRAINT `FK_MethodCode` FOREIGN KEY (`MethodCode`) REFERENCES `MethodLookUp` (`MethodCode`) ON UPDATE CASCADE,
105  CONSTRAINT `FK_UnitCode` FOREIGN KEY (`UnitCode`) REFERENCES `UnitLookUp` (`UnitCode`) ON UPDATE CASCADE,
106  CONSTRAINT `FK_MatrixCode` FOREIGN KEY (`MatrixCode`) REFERENCES `MatrixLookUp` (`MatrixCode`) ON UPDATE CASCADE,
107  CONSTRAINT `FK_FractionCode` FOREIGN KEY (`FractionCode`) REFERENCES `FractionLookUp` (`FractionCode`) ON UPDATE CASCADE
108)
109ENGINE = INNODB;
110
111DROP TABLE IF EXISTS `swampdb2003`.`Corrections`;
112CREATE TABLE `swampdb2003`.`Corrections` (
113  `CorrectionsRowID` int(10) unsigned NOT NULL auto_increment,
114  `Person` VARCHAR(50) NULL,
115  `AssociatedAgency` VARCHAR(50) NULL,
116  `Date` DATETIME NULL,
117  `TableName` VARCHAR(50) NULL,
118  `FieldName` VARCHAR(30) NULL,
119  `OriginalData` VARCHAR(150) NULL,
120  `ChangedData` VARCHAR(150) NULL,
121  `Global` TINYINT(1) NOT NULL,
122  `CorrectionsComments` VARCHAR(255) NULL,
123  PRIMARY KEY (`CorrectionsRowID`)
124)
125ENGINE = INNODB;
126
127DROP TABLE IF EXISTS `swampdb2003`.`DigestExtractLookUp`;
128CREATE TABLE `swampdb2003`.`DigestExtractLookUp` (
129  `DigestExtractRowID` int(10) unsigned NOT NULL auto_increment,
130  `DigestExtractCode` SMALLINT(5) NULL,
131  `DigestExtractMethod` VARCHAR(50) NULL,
132  `DigestExtractDescr` VARCHAR(150) NULL,
133  `DigestExtractType` VARCHAR(15) NULL,
134  `DigestExtractInstrument` VARCHAR(75) NULL,
135  `MethodOnFile` VARCHAR(50) NULL,
136  `Active` TINYINT(1) NOT NULL,
137  PRIMARY KEY (`DigestExtractRowID`),
138  UNIQUE INDEX `DigestExtractCode` (`DigestExtractCode`),
139  UNIQUE INDEX `DigestExtractMethod` (`DigestExtractMethod`)
140)
141ENGINE = INNODB;
142
143DROP TABLE IF EXISTS `swampdb2003`.`EventTypeLookUp`;
144CREATE TABLE `swampdb2003`.`EventTypeLookUp` (
145  `EventTypeRowID` int(10) unsigned NOT NULL auto_increment,
146  `EventType` VARCHAR(20) NULL,
147  `EventDescr` VARCHAR(150) NULL,
148  `Active` TINYINT(1) NOT NULL,
149  PRIMARY KEY (`EventTypeRowID`),
150  UNIQUE INDEX `EventType` (`EventType`)
151)
152ENGINE = INNODB;
153
154DROP TABLE IF EXISTS `swampdb2003`.`FieldObsResult`;
155CREATE TABLE `swampdb2003`.`FieldObsResult` (
156  `FieldObsResultRowID` int(10) unsigned NOT NULL auto_increment,
157  `SampleRowID` int(10) unsigned NOT NULL,
158  `ConstituentRowID` int(10) unsigned NOT NULL,
159  `TextResult` VARCHAR(80) NULL,
160  `FieldObsResultComments` VARCHAR(150) NULL,
161  PRIMARY KEY (`FieldObsResultRowID`),
162  CONSTRAINT `FK_SampleRowID` FOREIGN KEY (`SampleRowID`) REFERENCES `Sample` (`SampleRowID`) ON UPDATE CASCADE,
163  CONSTRAINT `FK_ConstituentRowID` FOREIGN KEY (`ConstituentRowID`) REFERENCES `ConstituentLookUp` (`ConstituentRowID`) ON UPDATE CASCADE,
164  UNIQUE INDEX `UniqueConstraint` (`SampleRowID`, `ConstituentRowID`)
165)
166ENGINE = INNODB;
167
168DROP TABLE IF EXISTS `swampdb2003`.`FieldObsResult_Entry`;
169CREATE TABLE `swampdb2003`.`FieldObsResult_Entry` (
170  `FieldObsResultRowID` int(10) unsigned NOT NULL auto_increment,
171  `SampleRowID` int(10) unsigned NOT NULL,
172  `ConstituentRowID` int(10) unsigned NOT NULL,
173  `TextResult` VARCHAR(80) NULL,
174  `FieldObsResultComments` VARCHAR(150) NULL,
175  PRIMARY KEY (`FieldObsResultRowID`),
176  UNIQUE INDEX `UniqueConstraint` (`SampleRowID`, `ConstituentRowID`),
177  CONSTRAINT `FK_SampleRowID` FOREIGN KEY (`SampleRowID`) REFERENCES `Sample_Entry` (`SampleRowID`) ON UPDATE CASCADE,
178  CONSTRAINT `FK_ConstituentRowID` FOREIGN KEY (`ConstituentRowID`) REFERENCES `ConstituentLookUp` (`ConstituentRowID`) ON UPDATE CASCADE
179)
180ENGINE = INNODB;
181
182DROP TABLE IF EXISTS `swampdb2003`.`FieldObsVarLookUp`;
183CREATE TABLE `swampdb2003`.`FieldObsVarLookUp` (
184  `FieldObsVarRowId` int(10) unsigned NOT NULL auto_increment,
185  `AnalyteName` VARCHAR(30) NULL,
186  `ValueCode` VARCHAR(50) NULL,
187  `ValueCodeDescr` VARCHAR(100) NULL,
188  `Active` TINYINT(1) NOT NULL,
189  PRIMARY KEY (`FieldObsVarRowId`),
190  UNIQUE INDEX `UniqueConstraint` (`AnalyteName`, `ValueCode`)
191)
192ENGINE = INNODB;
193
194DROP TABLE IF EXISTS `swampdb2003`.`FieldResEquipLookUp`;
195CREATE TABLE `swampdb2003`.`FieldResEquipLookUp` (
196  `FieldResEquipRowID` int(10) unsigned NOT NULL auto_increment,
197  `SampleDevice` VARCHAR(50) NULL,
198  `AnalyteName` VARCHAR(125) NULL,
199  `Active` TINYINT(1) NOT NULL,
200  PRIMARY KEY (`FieldResEquipRowID`),
201  UNIQUE INDEX `UniqueConstraint` (`SampleDevice`, `AnalyteName`),
202  CONSTRAINT `FK_AnalyteName` FOREIGN KEY (`AnalyteName`) REFERENCES `AnalyteLookUp` (`AnalyteName`) ON UPDATE CASCADE,
203  CONSTRAINT `FK_SampleDevice` FOREIGN KEY (`SampleDevice`) REFERENCES `SamplingDeviceLookUp` (`SampleDevice`) ON UPDATE CASCADE
204)
205ENGINE = INNODB;
206
207DROP TABLE IF EXISTS `swampdb2003`.`FieldResult`;
208CREATE TABLE `swampdb2003`.`FieldResult` (
209  `FieldResultRowID` int(10) unsigned NOT NULL auto_increment,
210  `SampleRowID` int(10) unsigned NOT NULL,
211  `ConstituentRowID` int(10) unsigned NOT NULL,
212  `FieldReplicate` SMALLINT(5) NULL,
213  `Result` DOUBLE(15, 5) NULL,
214  `ResQualCode` VARCHAR(10) NULL,
215  `SigFig` SMALLINT(5) NULL,
216  `SamplingDeviceCode` SMALLINT(5) NULL,
217  `QACode` VARCHAR(25) NULL,
218  `ComplianceCode` VARCHAR(5) NULL,
219  `CalibrationDate` DATETIME NULL,
220  `FieldResultComments` VARCHAR(100) NULL,
221  PRIMARY KEY (`FieldResultRowID`),
222  UNIQUE INDEX `UniqueConstraint` (`SampleRowID`, `ConstituentRowID`, `FieldReplicate`, `SamplingDeviceCode`),
223  CONSTRAINT `FK_SampleRowID` FOREIGN KEY (`SampleRowID`) REFERENCES `Sample` (`SampleRowID`) ON UPDATE CASCADE,
224  CONSTRAINT `FK_QACode` FOREIGN KEY (`QACode`) REFERENCES `QALookUp` (`QACode`) ON UPDATE CASCADE,
225  CONSTRAINT `FK_ConstituentRowID` FOREIGN KEY (`ConstituentRowID`) REFERENCES `ConstituentLookUp` (`ConstituentRowID`) ON UPDATE CASCADE,
226  CONSTRAINT `FK_ResQualCode` FOREIGN KEY (`ResQualCode`) REFERENCES `ResQualLookUp` (`ResQualCode`) ON UPDATE CASCADE,
227  CONSTRAINT `FK_ComplianceCode` FOREIGN KEY (`ComplianceCode`) REFERENCES `ComplianceLevelLookUp` (`ComplianceCode`),
228  CONSTRAINT `FK_SamplingDeviceCode` FOREIGN KEY (`SamplingDeviceCode`) REFERENCES `SamplingDeviceLookUp` (`SamplingDeviceCode`) ON UPDATE CASCADE,
229  INDEX `ComplianceCode` (`ComplianceCode`),
230  INDEX `QACode` (`QACode`),
231  INDEX `ResQualCode` (`ResQualCode`),
232  INDEX `SamplingDeviceCode` (`SamplingDeviceCode`)
233)
234ENGINE = INNODB;
235
236DROP TABLE IF EXISTS `swampdb2003`.`FieldResult_Entry`;
237CREATE TABLE `swampdb2003`.`FieldResult_Entry` (
238  `FieldResultRowID` int(10) unsigned NOT NULL auto_increment,
239  `SampleRowID` int(10) unsigned NOT NULL,
240  `ConstituentRowID` int(10) unsigned NOT NULL,
241  `FieldReplicate` SMALLINT(5) NULL,
242  `Result` DOUBLE(15, 5) NULL,
243  `ResQualCode` VARCHAR(10) NULL,
244  `SigFig` SMALLINT(5) NULL,
245  `SamplingDeviceCode` SMALLINT(5) NULL,
246  `QACode` VARCHAR(25) NULL,
247  `ComplianceCode` VARCHAR(5) NULL,
248  `CalibrationDate` DATETIME NULL,
249  `FieldResultComments` VARCHAR(100) NULL,
250  PRIMARY KEY (`FieldResultRowID`),
251  UNIQUE INDEX `UniqueConstraint` (`SampleRowID`, `ConstituentRowID`, `FieldReplicate`, `SamplingDeviceCode`),
252  CONSTRAINT `FK_SampleRowID` FOREIGN KEY (`SampleRowID`) REFERENCES `Sample_Entry` (`SampleRowID`) ON UPDATE CASCADE,
253  CONSTRAINT `FK_QACode` FOREIGN KEY (`QACode`) REFERENCES `QALookUp` (`QACode`) ON UPDATE CASCADE,
254  CONSTRAINT `FK_ConstituentRowID` FOREIGN KEY (`ConstituentRowID`) REFERENCES `ConstituentLookUp` (`ConstituentRowID`) ON UPDATE CASCADE,
255  CONSTRAINT `FK_ResQualCode` FOREIGN KEY (`ResQualCode`) REFERENCES `ResQualLookUp` (`ResQualCode`) ON UPDATE CASCADE,
256  CONSTRAINT `FK_ComplianceCode` FOREIGN KEY (`ComplianceCode`) REFERENCES `ComplianceLevelLookUp` (`ComplianceCode`),
257  CONSTRAINT `FK_SamplingDeviceCode` FOREIGN KEY (`SamplingDeviceCode`) REFERENCES `SamplingDeviceLookUp` (`SamplingDeviceCode`) ON UPDATE CASCADE,
258  INDEX `ComplianceCode` (`ComplianceCode`),
259  INDEX `FieldResult_EntrySampleRowID` (`SampleRowID`),
260  INDEX `QACode` (`QACode`),
261  INDEX `ResQualCode` (`ResQualCode`),
262  INDEX `SamplingDeviceCode` (`SamplingDeviceCode`)
263)
264ENGINE = INNODB;
265
266DROP TABLE IF EXISTS `swampdb2003`.`FractionLookUp`;
267CREATE TABLE `swampdb2003`.`FractionLookUp` (
268  `FractionRowID` int(10) unsigned NOT NULL auto_increment,
269  `FractionCode` SMALLINT(5) NULL,
270  `FractionName` VARCHAR(50) NULL,
271  `FractionDescr` VARCHAR(150) NULL,
272  `Active` TINYINT(1) NOT NULL,
273  PRIMARY KEY (`FractionRowID`),
274  UNIQUE INDEX `FractionCode` (`FractionCode`),
275  UNIQUE INDEX `FractionName` (`FractionName`)
276)
277ENGINE = INNODB;
278
279DROP TABLE IF EXISTS `swampdb2003`.`GPSDeviceLookUp`;
280CREATE TABLE `swampdb2003`.`GPSDeviceLookUp` (
281  `GPSDeviceRowID` int(10) unsigned NOT NULL auto_increment,
282  `GPSDeviceCode` SMALLINT(5) NULL,
283  `GPSDeviceID` VARCHAR(50) NULL,
284  `GPSDescr` VARCHAR(100) NULL,
285  `Active` TINYINT(1) NOT NULL,
286  PRIMARY KEY (`GPSDeviceRowID`),
287  UNIQUE INDEX `GPSDeviceCode` (`GPSDeviceCode`),
288  UNIQUE INDEX `GPSDeviceID` (`GPSDeviceID`)
289)
290ENGINE = INNODB;
291
292DROP TABLE IF EXISTS `swampdb2003`.`LabBatch`;
293CREATE TABLE `swampdb2003`.`LabBatch` (
294  `LabBatchRowID` int(10) unsigned NOT NULL auto_increment,
295  `LabBatch` VARCHAR(35) NULL,
296  `SubmittingAgency` VARCHAR(20) NULL,
297  `AgencyCode` VARCHAR(20) NULL,
298  `BatchQualCode` VARCHAR(10) NULL,
299  `BatchValCode` VARCHAR(10) NULL,
300  `LabBatchComments` LONGTEXT NULL,
301  `Gen_TempField*0` INT(10) NULL,
302  PRIMARY KEY (`LabBatchRowID`),
303  UNIQUE INDEX `LabBatch` (`LabBatch`),
304  CONSTRAINT `FK_BatchValCode` FOREIGN KEY (`BatchValCode`) REFERENCES `BatchValLookUp` (`BatchValCode`) ON UPDATE CASCADE,
305  CONSTRAINT `FK_AgencyCode` FOREIGN KEY (`AgencyCode`) REFERENCES `AgencyLookUp` (`AgencyCode`) ON UPDATE CASCADE,
306  CONSTRAINT `FK_BatchQualCode` FOREIGN KEY (`BatchQualCode`) REFERENCES `BatchQualLookUp` (`BatchQualCode`) ON UPDATE CASCADE,
307  INDEX `AgencyCode` (`AgencyCode`),
308  INDEX `BatchQualCode` (`BatchQualCode`),
309  INDEX `BatchValCode` (`BatchValCode`)
310)
311ENGINE = INNODB;
312
313DROP TABLE IF EXISTS `swampdb2003`.`LabResult`;
314CREATE TABLE `swampdb2003`.`LabResult` (
315  `LabResultRowID` int(10) unsigned NOT NULL auto_increment,
316  `SampleRowID` int(10) unsigned NOT NULL ,
317  `SampleID` VARCHAR(50) NULL,
318  `PrepCode` SMALLINT(5) NULL,
319  `PrepDate` DATETIME NULL,
320  `DigestExtractCode` SMALLINT(5) NULL,
321  `DigestExtractDate` DATETIME NULL,
322  `LabBatch` VARCHAR(35) NULL,
323  `AnalysisDate` DATETIME NULL,
324  `ConstituentRowID` int(10) unsigned NOT NULL,
325  `LabReplicate` SMALLINT(5) NULL,
326  `Basis` VARCHAR(5) NULL,
327  `Result` DOUBLE(15, 5) NULL,
328  `ResQualCode` VARCHAR(10) NULL,
329  `SigFig` SMALLINT(5) NULL,
330  `MDL` DOUBLE(15, 5) NULL,
331  `RL` DOUBLE(15, 5) NULL,
332  `QACode` VARCHAR(25) NULL,
333  `ComplianceCode` VARCHAR(5) NULL,
334  `ExpectedValue` DOUBLE(15, 5) NULL,
335  `LabSampleID` VARCHAR(35) NULL,
336  `LabResultComments` VARCHAR(200) NULL,
337  PRIMARY KEY (`LabResultRowID`),
338  UNIQUE INDEX `UniqueConstraint` (`SampleRowID`, `LabBatch`, `AnalysisDate`, `ConstituentRowID`, `LabReplicate`, `Basis`),
339  CONSTRAINT `FK_PrepCode` FOREIGN KEY (`PrepCode`) REFERENCES `PrepLookUp` (`PrepCode`) ON UPDATE CASCADE,
340  CONSTRAINT `FK_DigestExtractCode` FOREIGN KEY (`DigestExtractCode`) REFERENCES `DigestExtractLookUp` (`DigestExtractCode`) ON UPDATE CASCADE,
341  CONSTRAINT `FK_LabBatch` FOREIGN KEY (`LabBatch`) REFERENCES `LabBatch` (`LabBatch`) ON UPDATE CASCADE,
342  CONSTRAINT `FK_SampleRowID` FOREIGN KEY (`SampleRowID`) REFERENCES `Sample` (`SampleRowID`) ON UPDATE CASCADE,
343  CONSTRAINT `FK_QACode` FOREIGN KEY (`QACode`) REFERENCES `QALookUp` (`QACode`) ON UPDATE CASCADE,
344  CONSTRAINT `FK_ConstituentRowID` FOREIGN KEY (`ConstituentRowID`) REFERENCES `ConstituentLookUp` (`ConstituentRowID`) ON UPDATE CASCADE,
345  CONSTRAINT `FK_ResQualCode` FOREIGN KEY (`ResQualCode`) REFERENCES `ResQualLookUp` (`ResQualCode`) ON UPDATE CASCADE,
346  CONSTRAINT `FK_ComplianceCode` FOREIGN KEY (`ComplianceCode`) REFERENCES `ComplianceLevelLookUp` (`ComplianceCode`),
347  INDEX `ComplianceCode` (`ComplianceCode`),
348  INDEX `DigestExtractCode` (`DigestExtractCode`),
349  INDEX `LabSampleID` (`LabSampleID`),
350  INDEX `PrepCode` (`PrepCode`),
351  INDEX `QACode` (`QACode`),
352  INDEX `ResQualCode` (`ResQualCode`),
353  INDEX `SampleID` (`SampleID`)
354)
355ENGINE = INNODB;
356
357DROP TABLE IF EXISTS `swampdb2003`.`LabResult_Entry`;
358CREATE TABLE `swampdb2003`.`LabResult_Entry` (
359  `LabResultRowID` int(10) unsigned NOT NULL auto_increment,
360  `SampleRowID` int(10) unsigned NOT NULL,
361  `SampleID` VARCHAR(50) NULL,
362  `PrepCode` SMALLINT(5) NULL,
363  `PrepDate` DATETIME NULL,
364  `DigestExtractCode` SMALLINT(5) NULL,
365  `DigestExtractDate` DATETIME NULL,
366  `LabBatch` VARCHAR(35) NULL,
367  `AnalysisDate` DATETIME NULL,
368  `ConstituentRowID` int(10) unsigned NOT NULL,
369  `LabReplicate` SMALLINT(5) NULL,
370  `Basis` VARCHAR(5) NULL,
371  `Result` DOUBLE(15, 5) NULL,
372  `ResQualCode` VARCHAR(10) NULL,
373  `SigFig` SMALLINT(5) NULL,
374  `MDL` DOUBLE(15, 5) NULL,
375  `RL` DOUBLE(15, 5) NULL,
376  `QACode` VARCHAR(25) NULL,
377  `ComplianceCode` VARCHAR(5) NULL,
378  `ExpectedValue` DOUBLE(15, 5) NULL,
379  `LabSampleID` VARCHAR(35) NULL,
380  `LabResultComments` VARCHAR(200) NULL,
381  PRIMARY KEY (`LabResultRowID`),
382  UNIQUE INDEX `UniqueConstraint` (`SampleRowID`, `LabBatch`, `AnalysisDate`, `ConstituentRowID`, `LabReplicate`, `Basis`),
383  CONSTRAINT `FK_PrepCode` FOREIGN KEY (`PrepCode`) REFERENCES `PrepLookUp` (`PrepCode`) ON UPDATE CASCADE,
384  CONSTRAINT `FK_DigestExtractCode` FOREIGN KEY (`DigestExtractCode`) REFERENCES `DigestExtractLookUp` (`DigestExtractCode`) ON UPDATE CASCADE,
385  CONSTRAINT `FK_LabBatch` FOREIGN KEY (`LabBatch`) REFERENCES `LabBatch` (`LabBatch`) ON UPDATE CASCADE,
386  CONSTRAINT `FK_SampleRowID` FOREIGN KEY (`SampleRowID`) REFERENCES `Sample_Entry` (`SampleRowID`) ON UPDATE CASCADE,
387  CONSTRAINT `FK_QACode` FOREIGN KEY (`QACode`) REFERENCES `QALookUp` (`QACode`) ON UPDATE CASCADE,
388  CONSTRAINT `FK_ConstituentRowID` FOREIGN KEY (`ConstituentRowID`) REFERENCES `ConstituentLookUp` (`ConstituentRowID`) ON UPDATE CASCADE,
389  CONSTRAINT `FK_ResQualCode` FOREIGN KEY (`ResQualCode`) REFERENCES `ResQualLookUp` (`ResQualCode`) ON UPDATE CASCADE,
390  CONSTRAINT `FK_ComplianceCode` FOREIGN KEY (`ComplianceCode`) REFERENCES `ComplianceLevelLookUp` (`ComplianceCode`),
391  INDEX `ComplianceCode` (`ComplianceCode`),
392  INDEX `DigestExtractCode` (`DigestExtractCode`),
393  INDEX `LabSampleID` (`LabSampleID`),
394  INDEX `PrepCode` (`PrepCode`),
395  INDEX `QACode` (`QACode`),
396  INDEX `ResQualCode` (`ResQualCode`),
397  INDEX `SampleID` (`SampleID`)
398)
399ENGINE = INNODB;
400
401DROP TABLE IF EXISTS `swampdb2003`.`MatrixLookUp`;
402CREATE TABLE `swampdb2003`.`MatrixLookUp` (
403  `MatrixRowID` int(10) unsigned NOT NULL auto_increment,
404  `MatrixCode` SMALLINT(5) NULL,
405  `MatrixName` VARCHAR(50) NULL,
406  `MatrixDescr` VARCHAR(100) NULL,
407  `Active` TINYINT(1) NOT NULL,
408  PRIMARY KEY (`MatrixRowID`),
409  UNIQUE INDEX `MatrixCode` (`MatrixCode`),
410  UNIQUE INDEX `MatrixName` (`MatrixName`)
411)
412ENGINE = INNODB;
413
414DROP TABLE IF EXISTS `swampdb2003`.`MethodLookUp`;
415CREATE TABLE `swampdb2003`.`MethodLookUp` (
416  `MethodRowID` int(10) unsigned NOT NULL auto_increment,
417  `MethodCode` SMALLINT(5) NULL,
418  `MethodName` VARCHAR(30) NULL,
419  `MethodDescr` VARCHAR(150) NULL,
420  `Type1` VARCHAR(15) NULL,
421  `Type2` VARCHAR(15) NULL,
422  `Type3` VARCHAR(15) NULL,
423  `Instrument` VARCHAR(75) NULL,
424  `MethodOnFile` VARCHAR(50) NULL,
425  `Active` TINYINT(1) NOT NULL,
426  PRIMARY KEY (`MethodRowID`),
427  UNIQUE INDEX `MethodCode` (`MethodCode`),
428  UNIQUE INDEX `MethodName` (`MethodName`)
429)
430ENGINE = INNODB;
431
432DROP TABLE IF EXISTS `swampdb2003`.`MissingValueLookUp`;
433CREATE TABLE `swampdb2003`.`MissingValueLookUp` (
434  `MissingValueRowID` int(10) unsigned NOT NULL auto_increment,
435  `MissingValueCode` VARCHAR(12) NULL,
436  `DataType` VARCHAR(30) NULL,
437  `MissingValueDescr` VARCHAR(100) NULL,
438  `MissingValueComments` VARCHAR(250) NULL,
439  `Active` TINYINT(1) NOT NULL,
440  PRIMARY KEY (`MissingValueRowID`),
441  UNIQUE INDEX `MissingValueCode` (`MissingValueCode`)
442)
443ENGINE = INNODB;
444
445DROP TABLE IF EXISTS `swampdb2003`.`ParentProjectLookUp`;
446CREATE TABLE `swampdb2003`.`ParentProjectLookUp` (
447  `ParentProjectRowID` int(10) unsigned NOT NULL auto_increment,
448  `ParentProjectID` VARCHAR(25) NULL,
449  `ParentProjectName` VARCHAR(150) NULL,
450  `ProjType` VARCHAR(50) NULL,
451  `StartDate` DATETIME NULL,
452  `Duration` VARCHAR(150) NULL,
453  `Purpose` VARCHAR(255) NULL,
454  `ProjLead` VARCHAR(100) NULL,
455  `ProjPhon` VARCHAR(20) NULL,
456  `ProjEmail` VARCHAR(150) NULL,
457  `Address1` VARCHAR(255) NULL,
458  `Address2` VARCHAR(255) NULL,
459  `CityStat` VARCHAR(255) NULL,
460  `ZIP` VARCHAR(15) NULL,
461  `URL` VARCHAR(255) NULL,
462  `Notes` VARCHAR(255) NULL,
463  `OrgNameorID` VARCHAR(255) NULL,
464  `DQOlevel` VARCHAR(255) NULL,
465  `External File Name` VARCHAR(255) NULL,
466  `ParentProjectComments` VARCHAR(250) NULL,
467  PRIMARY KEY (`ParentProjectRowID`),
468  UNIQUE INDEX `ParentProjectID` (`ParentProjectID`),
469  UNIQUE INDEX `ParentProjectName` (`ParentProjectName`),
470  INDEX `OrgNameorID` (`OrgNameorID`)
471)
472ENGINE = INNODB;
473
474DROP TABLE IF EXISTS `swampdb2003`.`PrepLookUp`;
475CREATE TABLE `swampdb2003`.`PrepLookUp` (
476  `PrepRowID` int(10) unsigned NOT NULL auto_increment,
477  `PrepCode` SMALLINT(5) NULL,
478  `Preparation` VARCHAR(30) NULL,
479  `Filtered` TINYINT(1) NOT NULL,
480  `Active` TINYINT(1) NOT NULL,
481  PRIMARY KEY (`PrepRowID`),
482  UNIQUE INDEX `Preparation` (`Preparation`),
483  UNIQUE INDEX `PrepCode` (`PrepCode`)
484)
485ENGINE = INNODB;
486
487DROP TABLE IF EXISTS `swampdb2003`.`ProjectsLookUp`;
488CREATE TABLE `swampdb2003`.`ProjectsLookUp` (
489  `ProjectsRowID` int(10) unsigned NOT NULL auto_increment,
490  `ProjectID` VARCHAR(8) NULL,
491  `AgencyCode` VARCHAR(20) NULL,
492  `ParentProjectID` VARCHAR(25) NULL,
493  `FieldVerCode` VARCHAR(10) NULL,
494  `FieldVerComm` VARCHAR(50) NULL,
495  `QAPPVersion` VARCHAR(100) NULL,
496  `Active` TINYINT(1) NOT NULL,
497  `ProjectsComments` VARCHAR(250) NULL,
498  PRIMARY KEY (`ProjectsRowID`),
499  UNIQUE INDEX `ProjectID` (`ProjectID`),
500  UNIQUE INDEX `UniqueConstraint` (`ProjectID`, `ParentProjectID`),
501  CONSTRAINT `FK_FieldVerCode` FOREIGN KEY (`FieldVerCode`) REFERENCES `BatchValLookUp` (`BatchValCode`) ON UPDATE CASCADE,
502  CONSTRAINT `FK_ParentProjectID` FOREIGN KEY (`ParentProjectID`) REFERENCES `ParentProjectLookUp` (`ParentProjectID`) ON UPDATE CASCADE,
503  INDEX `AgencyCode` (`AgencyCode`),
504  INDEX `FieldVerCode` (`FieldVerCode`)
505)
506ENGINE = INNODB;
507
508DROP TABLE IF EXISTS `swampdb2003`.`QALookUp`;
509CREATE TABLE `swampdb2003`.`QALookUp` (
510  `QARowID` int(10) unsigned NOT NULL auto_increment,
511  `QACode` VARCHAR(25) NULL,
512  `QACodeDescr` VARCHAR(250) NULL,
513  `Type1` VARCHAR(25) NULL,
514  `Type2` VARCHAR(25) NULL,
515  `Type3` VARCHAR(25) NULL,
516  `Active` TINYINT(1) NOT NULL,
517  PRIMARY KEY (`QARowID`),
518  UNIQUE INDEX `QACode` (`QACode`),
519  UNIQUE INDEX `QACodeDescr` (`QACodeDescr`)
520)
521ENGINE = INNODB;
522
523DROP TABLE IF EXISTS `swampdb2003`.`ResQualLookUp`;
524CREATE TABLE `swampdb2003`.`ResQualLookUp` (
525  `ResQualRowID` int(10) unsigned NOT NULL auto_increment,
526  `ResQualCode` VARCHAR(10) NULL,
527  `ResQualifier` VARCHAR(150) NULL,
528  `Type1` VARCHAR(25) NULL,
529  `Type2` VARCHAR(25) NULL,
530  `Type3` VARCHAR(25) NULL,
531  `Active` TINYINT(1) NOT NULL,
532  PRIMARY KEY (`ResQualRowID`),
533  UNIQUE INDEX `ResQualCode` (`ResQualCode`),
534  UNIQUE INDEX `ResQualifier` (`ResQualifier`)
535)
536ENGINE = INNODB;
537
538DROP TABLE IF EXISTS `swampdb2003`.`Sample`;
539CREATE TABLE `swampdb2003`.`Sample` (
540  `SampleRowID` int(10) unsigned NOT NULL auto_increment,
541  `StationCode` VARCHAR(9) NULL,
542  `SampleDate` DATETIME NULL,
543  `SampleTime` DATETIME NULL,
544  `EventType` VARCHAR(20) NULL,
545  `SampleTypeCode` VARCHAR(20) NULL,
546  `SampleReplicate` SMALLINT(5) NULL,
547  `DepthSampleCollection` DOUBLE(15, 5) NULL,
548  `Unit` VARCHAR(2) NULL,
549  `ProjectID` VARCHAR(8) NULL,
550  `SeasonCode` SMALLINT(5) NULL,
551  `AgencyCode` VARCHAR(20) NULL,
552  `StationFailCode` SMALLINT(5) NULL,
553  `SampleComments` VARCHAR(200) NULL,
554  `SampleComplete` TINYINT(1) NOT NULL,
555  `UserName` VARCHAR(20) NULL,
556  PRIMARY KEY (`SampleRowID`),
557  UNIQUE INDEX `UniqueConstraint` (`StationCode`, `SampleDate`, `SampleTime`, `EventType`, `SampleTypeCode`, `SampleReplicate`, `DepthSampleCollection`, `ProjectID`, `AgencyCode`),
558  
559  CONSTRAINT `FK_StationCode` FOREIGN KEY (`StationCode`) REFERENCES `StationLookUp` (`StationCode`) ON UPDATE CASCADE,
560  CONSTRAINT `FK_SeasonCode` FOREIGN KEY (`SeasonCode`) REFERENCES `SeasonLookUp` (`SeasonCode`) ON UPDATE CASCADE,
561  CONSTRAINT `FK_ProjectID` FOREIGN KEY (`ProjectID`) REFERENCES `ProjectsLookUp` (`ProjectID`) ON UPDATE CASCADE,
562  CONSTRAINT `FK_StationFailCode` FOREIGN KEY (`StationFailCode`) REFERENCES `StationFailLookUp` (`StationFailCode`) ON UPDATE CASCADE,
563  CONSTRAINT `FK_SampleTypeCode` FOREIGN KEY (`SampleTypeCode`) REFERENCES `SampleTypeLookUp` (`SampleTypeCode`) ON UPDATE CASCADE,
564  CONSTRAINT `FK_AgencyCode` FOREIGN KEY (`AgencyCode`) REFERENCES `AgencyLookUp` (`AgencyCode`) ON UPDATE CASCADE,
565  CONSTRAINT `FK_EventType` FOREIGN KEY (`EventType`) REFERENCES `EventTypeLookUp` (`EventType`) ON UPDATE CASCADE,
566
567  INDEX `SeasonCode` (`SeasonCode`),
568  INDEX `StationCode` (`StationCode`),
569  INDEX `StationFailCode` (`StationFailCode`)
570)
571ENGINE = INNODB;
572
573DROP TABLE IF EXISTS `swampdb2003`.`Sample_Entry`;
574CREATE TABLE `swampdb2003`.`Sample_Entry` (
575  `SampleRowID` int(10) unsigned NOT NULL auto_increment,
576  `StationCode` VARCHAR(9) NULL,
577  `SampleDate` DATETIME NULL,
578  `SampleTime` DATETIME NULL,
579  `EventType` VARCHAR(20) NULL,
580  `SampleTypeCode` VARCHAR(20) NULL,
581  `SampleReplicate` SMALLINT(5) NULL,
582  `DepthSampleCollection` DOUBLE(15, 5) NULL,
583  `Unit` VARCHAR(2) NULL,
584  `ProjectID` VARCHAR(8) NULL,
585  `SeasonCode` SMALLINT(5) NULL,
586  `AgencyCode` VARCHAR(20) NULL,
587  `StationFailCode` SMALLINT(5) NULL,
588  `SampleComments` VARCHAR(200) NULL,
589  `SampleComplete` TINYINT(1) NOT NULL,
590  `UserName` VARCHAR(20) NULL,
591  PRIMARY KEY (`SampleRowID`),
592  UNIQUE INDEX `UniqueConstraint` (`StationCode`, `SampleDate`, `SampleTime`, `EventType`, `SampleTypeCode`, `SampleReplicate`, `DepthSampleCollection`, `ProjectID`, `AgencyCode`),
593  
594  CONSTRAINT `FK_StationCode` FOREIGN KEY (`StationCode`) REFERENCES `StationLookUp` (`StationCode`) ON UPDATE CASCADE,
595  CONSTRAINT `FK_SeasonCode` FOREIGN KEY (`SeasonCode`) REFERENCES `SeasonLookUp` (`SeasonCode`) ON UPDATE CASCADE,
596  CONSTRAINT `FK_ProjectID` FOREIGN KEY (`ProjectID`) REFERENCES `ProjectsLookUp` (`ProjectID`) ON UPDATE CASCADE,
597  CONSTRAINT `FK_StationFailCode` FOREIGN KEY (`StationFailCode`) REFERENCES `StationFailLookUp` (`StationFailCode`) ON UPDATE CASCADE,
598  CONSTRAINT `FK_SampleTypeCode` FOREIGN KEY (`SampleTypeCode`) REFERENCES `SampleTypeLookUp` (`SampleTypeCode`) ON UPDATE CASCADE,
599  CONSTRAINT `FK_AgencyCode` FOREIGN KEY (`AgencyCode`) REFERENCES `AgencyLookUp` (`AgencyCode`) ON UPDATE CASCADE,
600  CONSTRAINT `FK_EventType` FOREIGN KEY (`EventType`) REFERENCES `EventTypeLookUp` (`EventType`) ON UPDATE CASCADE,
601
602  INDEX `SeasonCode` (`SeasonCode`),
603  INDEX `StationCode` (`StationCode`),
604  INDEX `StationFailCode` (`StationFailCode`)
605)
606ENGINE = INNODB;
607
608DROP TABLE IF EXISTS `swampdb2003`.`SampleDetail`;
609CREATE TABLE `swampdb2003`.`SampleDetail` (
610  `SampleDetailRowID` int(10) unsigned NOT NULL auto_increment,
611  `SampleRowID` int(10) unsigned NOT NULL,
612  `SamplingCrew` VARCHAR(150) NULL,
613  `SamplingDeviceCode` SMALLINT(5) NULL,
614  `StartingBank` VARCHAR(5) NULL,
615  `OccupationMethod` VARCHAR(20) NULL,
616  `SampleLocation` VARCHAR(20) NULL,
617  `PositionWaterColumn` VARCHAR(20) NULL,
618  `Hydromod` VARCHAR(50) NULL,
619  `HydromodLoc` VARCHAR(2) NULL,
620  `StationWaterDepth` DOUBLE(15, 5) NULL,
621  `UnitStationWaterDepth` VARCHAR(10) NULL,
622  `StreamWidth` DOUBLE(15, 5) NULL,
623  `UnitStreamWidth` VARCHAR(20) NULL,
624  `DistanceFromBank` DOUBLE(15, 5) NULL,
625  `UnitDistanceFromBank` VARCHAR(2) NULL,
626  `ActualLatitude` DOUBLE(15, 5) NULL,
627  `ActualLongitude` DOUBLE(15, 5) NULL,
628  `GPSDeviceCode` SMALLINT(5) NULL,
629  `GPSAccuracy` VARCHAR(50) NULL,
630  `UnitAccuracy` VARCHAR(50) NULL,
631  `Datum` VARCHAR(10) NULL,
632  `SampleDetailComments` VARCHAR(150) NULL,
633
634
635
636  PRIMARY KEY (`SampleDetailRowID`),
637  UNIQUE INDEX `SampleRowID` (`SampleRowID`),
638  CONSTRAINT `FK_SampleRowID` FOREIGN KEY (`SampleRowID`) REFERENCES `Sample` (`SampleRowID`) ON UPDATE CASCADE,
639  CONSTRAINT `FK_GPSDeviceCode` FOREIGN KEY (`GPSDeviceCode`) REFERENCES `GPSDeviceLookUp` (`GPSDeviceCode`) ON UPDATE CASCADE,
640  CONSTRAINT `FK_SamplingDeviceCode` FOREIGN KEY (`SamplingDeviceCode`) REFERENCES `SamplingDeviceLookUp` (`SamplingDeviceCode`) ON UPDATE CASCADE,
641  INDEX `GPSDeviceCode` (`GPSDeviceCode`),
642
643  INDEX `SamplingDeviceCode` (`SamplingDeviceCode`)
644)
645ENGINE = INNODB;
646
647DROP TABLE IF EXISTS `swampdb2003`.`SampleDetail_Entry`;
648CREATE TABLE `swampdb2003`.`SampleDetail_Entry` (
649  `SampleDetailRowID` int(10) unsigned NOT NULL auto_increment,
650  `SampleRowID` int(10) unsigned NOT NULL,
651  `SamplingCrew` VARCHAR(150) NULL,
652  `SamplingDeviceCode` SMALLINT(5) NULL,
653  `StartingBank` VARCHAR(5) NULL,
654  `OccupationMethod` VARCHAR(20) NULL,
655  `SampleLocation` VARCHAR(20) NULL,
656  `PositionWaterColumn` VARCHAR(20) NULL,
657  `Hydromod` VARCHAR(50) NULL,
658  `HydromodLoc` VARCHAR(2) NULL,
659  `StationWaterDepth` DOUBLE(15, 5) NULL,
660  `UnitStationWaterDepth` VARCHAR(10) NULL,
661  `StreamWidth` DOUBLE(15, 5) NULL,
662  `UnitStreamWidth` VARCHAR(20) NULL,
663  `DistanceFromBank` DOUBLE(15, 5) NULL,
664  `UnitDistanceFromBank` VARCHAR(2) NULL,
665  `ActualLatitude` DOUBLE(15, 5) NULL,
666  `ActualLongitude` DOUBLE(15, 5) NULL,
667  `GPSDeviceCode` SMALLINT(5) NULL,
668  `GPSAccuracy` VARCHAR(50) NULL,
669  `UnitAccuracy` VARCHAR(50) NULL,
670  `Datum` VARCHAR(10) NULL,
671  `SampleDetailComments` VARCHAR(150) NULL,
672
673
674
675  PRIMARY KEY (`SampleDetailRowID`),
676  UNIQUE INDEX `SampleRowID` (`SampleRowID`),
677  CONSTRAINT `FK_SampleRowID` FOREIGN KEY (`SampleRowID`) REFERENCES `Sample_Entry` (`SampleRowID`) ON UPDATE CASCADE,
678  CONSTRAINT `FK_GPSDeviceCode` FOREIGN KEY (`GPSDeviceCode`) REFERENCES `GPSDeviceLookUp` (`GPSDeviceCode`) ON UPDATE CASCADE,
679  CONSTRAINT `FK_SamplingDeviceCode` FOREIGN KEY (`SamplingDeviceCode`) REFERENCES `SamplingDeviceLookUp` (`SamplingDeviceCode`) ON UPDATE CASCADE,
680  INDEX `GPSDeviceCode` (`GPSDeviceCode`),
681
682  INDEX `SamplingDeviceCode` (`SamplingDeviceCode`)
683)
684ENGINE = INNODB;
685
686DROP TABLE IF EXISTS `swampdb2003`.`SampleTypeLookUp`;
687CREATE TABLE `swampdb2003`.`SampleTypeLookUp` (
688  `SampleTypeRowID` int(10) unsigned NOT NULL auto_increment,
689  `SampleTypeCode` VARCHAR(20) NULL,
690  `SampleTypeDescr` VARCHAR(150) NULL,
691  `EventType1` VARCHAR(20) NULL,
692  `EventType2` VARCHAR(20) NULL,
693  `EventType3` VARCHAR(20) NULL,
694  `EventType4` VARCHAR(20) NULL,
695  `CollectionType` VARCHAR(20) NULL,
696  `Active` TINYINT(1) NOT NULL,
697
698
699
700  PRIMARY KEY (`SampleTypeRowID`),
701  UNIQUE INDEX `SampleTypeCode` (`SampleTypeCode`),
702  UNIQUE INDEX `SampleTypeDescr` (`SampleTypeDescr`)
703
704)
705ENGINE = INNODB;
706
707DROP TABLE IF EXISTS `swampdb2003`.`SamplingDeviceLookUp`;
708CREATE TABLE `swampdb2003`.`SamplingDeviceLookUp` (
709  `SamplingDeviceRowID` int(10) unsigned NOT NULL auto_increment,
710  `SamplingDeviceCode` SMALLINT(5) NULL,
711  `SampleDevice` VARCHAR(50) NULL,
712  `SerialNumber` VARCHAR(30) NULL,
713  `SampleDeviceDescr` VARCHAR(100) NULL,
714  `SamplingMatrix` VARCHAR(50) NULL,
715  `Active` TINYINT(1) NOT NULL,
716
717
718
719  PRIMARY KEY (`SamplingDeviceRowID`),
720  UNIQUE INDEX `SampleDevice` (`SampleDevice`),
721  UNIQUE INDEX `SamplingDeviceCode` (`SamplingDeviceCode`)
722
723)
724ENGINE = INNODB;
725
726DROP TABLE IF EXISTS `swampdb2003`.`SeasonDetailLookUp`;
727CREATE TABLE `swampdb2003`.`SeasonDetailLookUp` (
728  `SeasonRowID` int(10) unsigned NOT NULL auto_increment,
729  `SeasonCode` SMALLINT(5) NULL,
730  `SeasonDescr` VARCHAR(50) NULL,
731  `ProjectID` VARCHAR(8) NULL,
732  `Active` TINYINT(1) NOT NULL,
733
734
735
736  PRIMARY KEY (`SeasonRowID`),
737  UNIQUE INDEX `UniqueConstraint` (`SeasonCode`, `ProjectID`),
738  CONSTRAINT `FK_SeasonCode` FOREIGN KEY (`SeasonCode`) REFERENCES `SeasonLookUp` (`SeasonCode`) ON UPDATE CASCADE,
739
740)
741ENGINE = INNODB;
742
743DROP TABLE IF EXISTS `swampdb2003`.`SeasonLookUp`;
744CREATE TABLE `swampdb2003`.`SeasonLookUp` (
745  `SeasonParentRowID` int(10) unsigned NOT NULL auto_increment,
746  `SeasonCode` SMALLINT(5) NULL,
747  `Season` VARCHAR(50) NULL,
748  `Active` TINYINT(1) NOT NULL,
749
750
751
752  PRIMARY KEY (`SeasonParentRowID`),
753  UNIQUE INDEX `Season` (`Season`),
754  UNIQUE INDEX `SeasonCode` (`SeasonCode`)
755
756)
757ENGINE = INNODB;
758
759DROP TABLE IF EXISTS `swampdb2003`.`StationFailLookUp`;
760CREATE TABLE `swampdb2003`.`StationFailLookUp` (
761  `StationFailRowID` int(10) unsigned NOT NULL auto_increment,
762  `StationFailCode` SMALLINT(5) NULL,
763  `FailureReason` VARCHAR(150) NULL,
764  `Active` TINYINT(1) NOT NULL,
765
766
767
768  PRIMARY KEY (`StationFailRowID`),
769  UNIQUE INDEX `FailureReason` (`FailureReason`),
770  UNIQUE INDEX `StationFailCode` (`StationFailCode`)
771
772)
773ENGINE = INNODB;
774
775DROP TABLE IF EXISTS `swampdb2003`.`StationLookUp`;
776CREATE TABLE `swampdb2003`.`StationLookUp` (
777  `StationsRowID` int(10) unsigned NOT NULL auto_increment,
778  `SWRCBRegion` VARCHAR(5) NULL,
779  `StationCode` VARCHAR(9) NULL,
780  `StationName` VARCHAR(100) NULL,
781  `LocalWatershed` VARCHAR(50) NULL,
782  `StationComments` VARCHAR(255) NULL,
783  `Active` TINYINT(1) NOT NULL,
784  `TargetLat` DOUBLE(15, 5) NULL,
785  `TargetLong` DOUBLE(15, 5) NULL,
786  `Datum` VARCHAR(10) NULL,
787  `GageStationID` VARCHAR(50) NULL,
788  `HydrologicUnit` VARCHAR(50) NULL,
789  `CalWNum221` DOUBLE(15, 5) NULL,
790  `SWRCBNum221` DOUBLE(15, 5) NULL,
791  `HBASA2` DOUBLE(15, 5) NULL,
792  `NationalHydrographyDataReach` DOUBLE(15, 5) NULL,
793  `NHDWaterbody` VARCHAR(50) NULL,
794  `LocalWaterbody` VARCHAR(50) NULL,
795  `WBType` VARCHAR(5) NULL,
796  `Elevation` DOUBLE(15, 5) NULL,
797  `Unit` VARCHAR(2) NULL,
798  `County` VARCHAR(50) NULL,
799  `AlternativeStationCode1` VARCHAR(50) NULL,
800  `AlternativeStationCode2` VARCHAR(50) NULL,
801  `AlternativeStationCode3` VARCHAR(50) NULL,
802  `NHDRiverMile` VARCHAR(50) NULL,
803  `StreamSubsystem` VARCHAR(50) NULL,
804  `AddDate` DATETIME NULL,
805  `StationCodeVer` VARCHAR(50) NULL,
806  `StationCodeVerComm` VARCHAR(255) NULL,
807
808
809
810  PRIMARY KEY (`StationsRowID`),
811  UNIQUE INDEX `StationCode` (`StationCode`),
812  UNIQUE INDEX `StationName` (`StationName`),
813  CONSTRAINT `FK_WBType` FOREIGN KEY (`WBType`) REFERENCES `WBTypeLookUp` (`WBType`) ON UPDATE CASCADE,
814  INDEX `GageStationID` (`GageStationID`)
815
816)
817ENGINE = INNODB;
818
819DROP TABLE IF EXISTS `swampdb2003`.`StatMethLookUp`;
820CREATE TABLE `swampdb2003`.`StatMethLookUp` (
821  `StatMethodRowID` int(10) unsigned NOT NULL auto_increment,
822  `StatMethCode` VARCHAR(10) NULL,
823  `StatMethDescr` VARCHAR(150) NULL,
824  `Active` TINYINT(1) NOT NULL,
825
826
827
828  PRIMARY KEY (`StatMethodRowID`),
829  UNIQUE INDEX `StatMethCode` (`StatMethCode`)
830
831)
832ENGINE = INNODB;
833
834DROP TABLE IF EXISTS `swampdb2003`.`TimePointLookUp`;
835CREATE TABLE `swampdb2003`.`TimePointLookUp` (
836  `TimePointRowID` int(10) unsigned NOT NULL auto_increment,
837  `TimePoint` VARCHAR(10) NULL,
838  `TimePointDescr` VARCHAR(75) NULL,
839  `Active` TINYINT(1) NOT NULL,
840
841
842
843  PRIMARY KEY (`TimePointRowID`),
844  UNIQUE INDEX `TimePoint` (`TimePoint`)
845
846)
847ENGINE = INNODB;
848
849DROP TABLE IF EXISTS `swampdb2003`.`ToxBatch`;
850CREATE TABLE `swampdb2003`.`ToxBatch` (
851  `ToxBatchRowID` int(10) unsigned NOT NULL auto_increment,
852  `ToxBatch` VARCHAR(25) NULL,
853  `SubmittingAgency` VARCHAR(20) NULL,
854  `AgencyCode` VARCHAR(20) NULL,
855  `ToxBatchStartDate` DATETIME NULL,
856  `RefToxBatch` VARCHAR(25) NULL,
857  `OrganismSupplier` VARCHAR(75) NULL,
858  `OrganismAgeAtTestStart` SMALLINT(5) NULL,
859  `UnitsAgeAtStart` VARCHAR(6) NULL,
860  `BatchQualCode` VARCHAR(10) NULL,
861  `BatchValCode` VARCHAR(10) NULL,
862  `ToxBatchComments` LONGTEXT NULL,
863  `Gen_TempField*0` INT(10) NULL,
864
865
866
867  PRIMARY KEY (`ToxBatchRowID`),
868  UNIQUE INDEX `ToxBatch` (`ToxBatch`),
869  CONSTRAINT `FK_BatchValCode` FOREIGN KEY (`BatchValCode`) REFERENCES `BatchValLookUp` (`BatchValCode`) ON UPDATE CASCADE,
870  CONSTRAINT `FK_AgencyCode` FOREIGN KEY (`AgencyCode`) REFERENCES `AgencyLookUp` (`AgencyCode`) ON UPDATE CASCADE,
871  CONSTRAINT `FK_BatchQualCode` FOREIGN KEY (`BatchQualCode`) REFERENCES `BatchQualLookUp` (`BatchQualCode`) ON UPDATE CASCADE,
872  INDEX `AgencyCode` (`AgencyCode`),
873  INDEX `BatchQualCode` (`BatchQualCode`),
874  INDEX `BatchValCode` (`BatchValCode`)
875
876)
877ENGINE = INNODB;
878
879DROP TABLE IF EXISTS `swampdb2003`.`ToxConcLookUp`;
880CREATE TABLE `swampdb2003`.`ToxConcLookUp` (
881  `ToxConcRowID` int(10) unsigned NOT NULL auto_increment,
882  `ToxConc` DOUBLE(15, 5) NULL,
883  `ToxConcDescr` VARCHAR(50) NULL,
884  `Active` TINYINT(1) NOT NULL,
885
886
887
888  PRIMARY KEY (`ToxConcRowID`),
889  UNIQUE INDEX `ToxConc` (`ToxConc`),
890  UNIQUE INDEX `ToxConcRowID` (`ToxConcRowID`)
891
892)
893ENGINE = INNODB;
894
895DROP TABLE IF EXISTS `swampdb2003`.`ToxConstituentLookUp`;
896CREATE TABLE `swampdb2003`.`ToxConstituentLookUp` (
897  `ToxConstituentRowID` int(10) unsigned NOT NULL auto_increment,
898  `ToxConstituentCode` VARCHAR(20) NULL,
899  `MatrixCode` SMALLINT(5) NULL,
900  `MethodCode` SMALLINT(5) NULL,
901  `ToxTestDur` SMALLINT(5) NULL,
902  `UnitCode` SMALLINT(5) NULL,
903  `ToxSpeciesCode` VARCHAR(5) NULL,
904  `Active` TINYINT(1) NOT NULL,
905
906
907
908  PRIMARY KEY (`ToxConstituentRowID`),
909  UNIQUE INDEX `ToxConstituentCode` (`ToxConstituentCode`),
910  UNIQUE INDEX `UniqueConstraint` (`MatrixCode`, `MethodCode`, `ToxTestDur`, `UnitCode`, `ToxSpeciesCode`),
911  CONSTRAINT `FK_MatrixCode` FOREIGN KEY (`MatrixCode`) REFERENCES `MatrixLookUp` (`MatrixCode`) ON UPDATE CASCADE,
912  CONSTRAINT `FK_ToxTestDur` FOREIGN KEY (`ToxTestDur`) REFERENCES `ToxTestDurLookUp` (`ToxTestDur`) ON UPDATE CASCADE,
913  CONSTRAINT `FK_ToxSpeciesCode` FOREIGN KEY (`ToxSpeciesCode`) REFERENCES `ToxSpeciesLookUp` (`ToxSpeciesCode`) ON UPDATE CASCADE,
914  CONSTRAINT `FK_MethodCode` FOREIGN KEY (`MethodCode`) REFERENCES `MethodLookUp` (`MethodCode`) ON UPDATE CASCADE
915
916)
917ENGINE = INNODB;
918
919DROP TABLE IF EXISTS `swampdb2003`.`ToxEffort`;
920CREATE TABLE `swampdb2003`.`ToxEffort` (
921  `ToxEffortRowID` int(10) unsigned NOT NULL auto_increment,
922  `ToxTestRowID` int(10) unsigned NOT NULL,
923  `Dilution` DOUBLE(15, 5) NULL,
924  `ToxConc` DOUBLE(15, 5) NULL,
925  `ToxResConstituentRowID` int(10) unsigned NOT NULL,
926  `ToxEffortComments` VARCHAR(150) NULL,
927
928
929
930  PRIMARY KEY (`ToxEffortRowID`),
931  UNIQUE INDEX `UniqueConstraint` (`ToxTestRowID`, `Dilution`, `ToxConc`, `ToxResConstituentRowID`),
932  CONSTRAINT `FK_ToxTestRowID` FOREIGN KEY (`ToxTestRowID`) REFERENCES `ToxTest` (`ToxTestRowID`) ON UPDATE CASCADE,
933  CONSTRAINT `FK_ToxConc` FOREIGN KEY (`ToxConc`) REFERENCES `ToxConcLookUp` (`ToxConc`) ON UPDATE CASCADE,
934  CONSTRAINT `FK_ToxResConstituentRowID` FOREIGN KEY (`ToxResConstituentRowID`) REFERENCES `ToxResConstituentLookUp` (`ToxResConstituentRowID`) ON UPDATE CASCADE
935)
936ENGINE = INNODB;
937
938DROP TABLE IF EXISTS `swampdb2003`.`ToxEffort_Entry`;
939CREATE TABLE `swampdb2003`.`ToxEffort_Entry` (
940  `ToxEffortRowID` int(10) unsigned NOT NULL auto_increment,
941  `ToxTestRowID` int(10) unsigned NOT NULL,
942  `Dilution` DOUBLE(15, 5) NULL,
943  `ToxConc` DOUBLE(15, 5) NULL,
944  `ToxResConstituentRowID` int(10) unsigned NOT NULL,
945  `ToxEffortComments` VARCHAR(150) NULL,
946
947
948
949  PRIMARY KEY (`ToxEffortRowID`),
950  UNIQUE INDEX `UniqueConstraint` (`ToxTestRowID`, `Dilution`, `ToxConc`, `ToxResConstituentRowID`),
951  CONSTRAINT `FK_` FOREIGN KEY (``) REFERENCES `LookUp` (``) ON UPDATE CASCADE,
952  CONSTRAINT `FK_` FOREIGN KEY (``) REFERENCES `LookUp` (``) ON UPDATE CASCADE,
953  CONSTRAINT `FK_ToxTestRowID` FOREIGN KEY (`ToxTestRowID`) REFERENCES `ToxTest_Entry` (`ToxTestRowID`) ON UPDATE CASCADE,
954  CONSTRAINT `FK_ToxConc` FOREIGN KEY (`ToxConc`) REFERENCES `ToxConcLookUp` (`ToxConc`) ON UPDATE CASCADE,
955  CONSTRAINT `FK_ToxResConstituentRowID` FOREIGN KEY (`ToxResConstituentRowID`) REFERENCES `ToxResConstituentLookUp` (`ToxResConstituentRowID`) ON UPDATE CASCADE
956)
957ENGINE = INNODB;
958
959DROP TABLE IF EXISTS `swampdb2003`.`ToxEffortTypeLookUp`;
960CREATE TABLE `swampdb2003`.`ToxEffortTypeLookUp` (
961  `ToxEffortTypeRowID` int(10) unsigned NOT NULL auto_increment,
962  `ToxEffortType` VARCHAR(20) NULL,
963  `ToxEffortTypeDescr` VARCHAR(75) NULL,
964  `Active` TINYINT(1) NOT NULL,
965
966
967
968  PRIMARY KEY (`ToxEffortTypeRowID`),
969  UNIQUE INDEX `ToxEffortType` (`ToxEffortType`)
970
971)
972ENGINE = INNODB;
973
974DROP TABLE IF EXISTS `swampdb2003`.`ToxEndPointLookUp`;
975CREATE TABLE `swampdb2003`.`ToxEndPointLookUp` (
976  `ToxEndPointRowID` int(10) unsigned NOT NULL auto_increment,
977  `ToxEndPointCode` VARCHAR(15) NULL,
978  `ToxEndPoint` VARCHAR(75) NULL,
979  `ToxEffortType` VARCHAR(20) NULL,
980  `Active` TINYINT(1) NOT NULL,
981
982
983
984  PRIMARY KEY (`ToxEndPointRowID`),
985  UNIQUE INDEX `ToxEndPoint` (`ToxEndPoint`),
986  UNIQUE INDEX `ToxEndPointCode` (`ToxEndPointCode`),
987  CONSTRAINT `FK_ToxEffortType` FOREIGN KEY (`ToxEffortType`) REFERENCES `ToxEffortTypeLookUp` (`ToxEffortType`) ON UPDATE CASCADE
988
989)
990ENGINE = INNODB;
991
992DROP TABLE IF EXISTS `swampdb2003`.`ToxResConstituentLookUp`;
993CREATE TABLE `swampdb2003`.`ToxResConstituentLookUp` (
994  `ToxResConstituentRowID` int(10) unsigned NOT NULL auto_increment,
995  `ToxResConstituentCode` VARCHAR(50) NULL,
996  `ToxEndPointCode` VARCHAR(15) NULL,
997  `WQSource` VARCHAR(25) NULL,
998  `TimePoint` VARCHAR(10) NULL,
999  `TimePointUnit` VARCHAR(5) NULL,
1000  `UnitCode` SMALLINT(5) NULL,
1001  `Active` TINYINT(1) NOT NULL,
1002
1003
1004
1005  PRIMARY KEY (`ToxResConstituentRowID`),
1006  UNIQUE INDEX `ToxResConstituentCode` (`ToxResConstituentCode`),
1007  UNIQUE INDEX `UniqueConstraint` (`ToxEndPointCode`, `WQSource`, `TimePoint`, `TimePointUnit`, `UnitCode`),
1008
1009  CONSTRAINT `FK_TimePoint` FOREIGN KEY (`TimePoint`) REFERENCES `TimePointLookUp` (`TimePoint`) ON UPDATE CASCADE,
1010  CONSTRAINT `FK_ToxEndPointCode` FOREIGN KEY (`ToxEndPointCode`) REFERENCES `ToxEndPointLookUp` (`ToxEndPointCode`) ON UPDATE CASCADE,
1011  CONSTRAINT `FK_UnitCode` FOREIGN KEY (`UnitCode`) REFERENCES `UnitLookUp` (`UnitCode`) ON UPDATE CASCADE
1012)
1013ENGINE = INNODB;
1014
1015DROP TABLE IF EXISTS `swampdb2003`.`ToxResult`;
1016CREATE TABLE `swampdb2003`.`ToxResult` (
1017  `ToxResultsRowID` int(10) unsigned NOT NULL auto_increment,
1018  `ToxEffortRowID` int(10) unsigned NOT NULL,
1019  `ToxReplicate` SMALLINT(5) NULL,
1020  `Result` DOUBLE(15, 5) NULL,
1021  `SigFig` SMALLINT(5) NULL,
1022  `ResQualCode` VARCHAR(10) NULL,
1023  `ToxResultsComments` VARCHAR(150) NULL,
1024
1025
1026
1027  PRIMARY KEY (`ToxResultsRowID`),
1028  UNIQUE INDEX `UniqueConstraint` (`ToxEffortRowID`, `ToxReplicate`),
1029  CONSTRAINT `FK_ResQualCode` FOREIGN KEY (`ResQualCode`) REFERENCES `ResQualLookUp` (`ResQualCode`) ON UPDATE CASCADE,
1030  CONSTRAINT `FK_ToxEffortRowID` FOREIGN KEY (`ToxEffortRowID`) REFERENCES `ToxEffort` (`ToxEffortRowID`) ON UPDATE CASCADE,
1031  INDEX `ResQualCode` (`ResQualCode`)
1032
1033)
1034ENGINE = INNODB;
1035
1036DROP TABLE IF EXISTS `swampdb2003`.`ToxResult_Entry`;
1037CREATE TABLE `swampdb2003`.`ToxResult_Entry` (
1038  `ToxResultsRowID` int(10) unsigned NOT NULL auto_increment,
1039  `ToxEffortRowID` int(10) unsigned NOT NULL,
1040  `ToxReplicate` SMALLINT(5) NULL,
1041  `Result` DOUBLE(15, 5) NULL,
1042  `SigFig` SMALLINT(5) NULL,
1043  `ResQualCode` VARCHAR(10) NULL,
1044  `ToxResultsComments` VARCHAR(150) NULL,
1045
1046
1047
1048  PRIMARY KEY (`ToxResultsRowID`),
1049  UNIQUE INDEX `UniqueConstraint` (`ToxEffortRowID`, `ToxReplicate`),
1050  CONSTRAINT `FK_ResQualCode` FOREIGN KEY (`ResQualCode`) REFERENCES `ResQualLookUp` (`ResQualCode`) ON UPDATE CASCADE,
1051  CONSTRAINT `FK_ToxEffortRowID` FOREIGN KEY (`ToxEffortRowID`) REFERENCES `ToxEffort_Entry` (`ToxEffortRowID`) ON UPDATE CASCADE,
1052  INDEX `ResQualCode` (`ResQualCode`)
1053
1054)
1055ENGINE = INNODB;
1056
1057DROP TABLE IF EXISTS `swampdb2003`.`ToxSigEffectLookUp`;
1058CREATE TABLE `swampdb2003`.`ToxSigEffectLookUp` (
1059  `ToxSigEffectRowID` int(10) unsigned NOT NULL auto_increment,
1060  `ToxSigEffectCode` VARCHAR(10) NULL,
1061  `ToxSigEffectDescr` VARCHAR(225) NULL,
1062  `Active` TINYINT(1) NOT NULL,
1063
1064
1065
1066  PRIMARY KEY (`ToxSigEffectRowID`),
1067  UNIQUE INDEX `ToxSigEffectCode` (`ToxSigEffectCode`)
1068
1069)
1070ENGINE = INNODB;
1071
1072DROP TABLE IF EXISTS `swampdb2003`.`ToxSpeciesLookUp`;
1073CREATE TABLE `swampdb2003`.`ToxSpeciesLookUp` (
1074  `ToxSpeciesRowID` int(10) unsigned NOT NULL auto_increment,
1075  `ToxSpeciesCode` VARCHAR(5) NULL,
1076  `ToxSpeciesName` VARCHAR(100) NULL,
1077  `ToxSpeciesComments` VARCHAR(150) NULL,
1078  `Active` TINYINT(1) NOT NULL,
1079
1080
1081
1082  PRIMARY KEY (`ToxSpeciesRowID`),
1083  UNIQUE INDEX `ToxSpeciesCode` (`ToxSpeciesCode`),
1084  UNIQUE INDEX `ToxSpeciesName` (`ToxSpeciesName`)
1085
1086)
1087ENGINE = INNODB;
1088
1089DROP TABLE IF EXISTS `swampdb2003`.`ToxSum`;
1090CREATE TABLE `swampdb2003`.`ToxSum` (
1091  `ToxSumRowID` int(10) unsigned NOT NULL auto_increment,
1092  `ToxEffortRowId` int(10) unsigned NOT NULL,
1093  `RepCount` INT(10) NULL,
1094  `Mean` DOUBLE(15, 5) NULL,
1095  `StdDev` DOUBLE(15, 5) NULL,
1096  `StatMethCode` VARCHAR(10) NULL,
1097  `Probability` DOUBLE(15, 5) NULL,
1098  `PctControl` DOUBLE(15, 5) NULL,
1099  `EvalThreshold` INT(10) NULL,
1100  `ToxSigEffectCode` VARCHAR(10) NULL,
1101  `ToxSumComments` VARCHAR(150) NULL,
1102
1103
1104
1105  PRIMARY KEY (`ToxSumRowID`),
1106  UNIQUE INDEX `ToxEffortRowId` (`ToxEffortRowId`),
1107  CONSTRAINT `FK_ToxEffortRowId` FOREIGN KEY (`ToxEffortRowId`) REFERENCES `ToxEffort` (`ToxEffortRowId`) ON UPDATE CASCADE,
1108  CONSTRAINT `FK_StatMethCode` FOREIGN KEY (`StatMethCode`) REFERENCES `StatMethLookUp` (`StatMethCode`) ON UPDATE CASCADE,
1109  CONSTRAINT `FK_ToxSigEffectCode` FOREIGN KEY (`ToxSigEffectCode`) REFERENCES `ToxSigEffectLookUp` (`ToxSigEffectCode`) ON UPDATE CASCADE,
1110
1111  INDEX `StatMethCode` (`StatMethCode`),
1112  INDEX `ToxSigEffectCode` (`ToxSigEffectCode`)
1113)
1114ENGINE = INNODB;
1115
1116DROP TABLE IF EXISTS `swampdb2003`.`ToxSum_Entry`;
1117CREATE TABLE `swampdb2003`.`ToxSum_Entry` (
1118  `ToxSumRowID` int(10) unsigned NOT NULL auto_increment,
1119  `ToxEffortRowId` int(10) unsigned NOT NULL,
1120  `RepCount` INT(10) NULL,
1121  `Mean` DOUBLE(15, 5) NULL,
1122  `StdDev` DOUBLE(15, 5) NULL,
1123  `StatMethCode` VARCHAR(10) NULL,
1124  `Probability` DOUBLE(15, 5) NULL,
1125  `PctControl` DOUBLE(15, 5) NULL,
1126  `EvalThreshold` INT(10) NULL,
1127  `ToxSigEffectCode` VARCHAR(10) NULL,
1128  `ToxSumComments` VARCHAR(150) NULL,
1129
1130
1131
1132  PRIMARY KEY (`ToxSumRowID`),
1133  UNIQUE INDEX `ToxEffortRowId` (`ToxEffortRowId`),
1134  CONSTRAINT `FK_ToxEffortRowId` FOREIGN KEY (`ToxEffortRowId`) REFERENCES `ToxEffort_Entry` (`ToxEffortRowId`) ON UPDATE CASCADE,
1135  CONSTRAINT `FK_StatMethCode` FOREIGN KEY (`StatMethCode`) REFERENCES `StatMethLookUp` (`StatMethCode`) ON UPDATE CASCADE,
1136  CONSTRAINT `FK_ToxSigEffectCode` FOREIGN KEY (`ToxSigEffectCode`) REFERENCES `ToxSigEffectLookUp` (`ToxSigEffectCode`) ON UPDATE CASCADE,
1137
1138  INDEX `StatMethCode` (`StatMethCode`),
1139  INDEX `ToxSigEffectCode` (`ToxSigEffectCode`)
1140)
1141ENGINE = INNODB;
1142
1143DROP TABLE IF EXISTS `swampdb2003`.`ToxTest`;
1144CREATE TABLE `swampdb2003`.`ToxTest` (
1145  `ToxTestRowID` int(10) unsigned NOT NULL auto_increment,
1146  `SampleRowID` int(10) unsigned NOT NULL,
1147  `SampleID` VARCHAR(50) NULL,
1148  `LabSampleID` VARCHAR(20) NULL,
1149  `ToxBatch` VARCHAR(25) NULL,
1150  `ToxConstituentRowID` int(10) unsigned NOT NULL,
1151  `QACode` VARCHAR(25) NULL,
1152  `ComplianceCode` VARCHAR(5) NULL,
1153  `ToxTestComments` VARCHAR(150) NULL,
1154
1155
1156
1157  PRIMARY KEY (`ToxTestRowID`),
1158  UNIQUE INDEX `UniqueConstraint` (`SampleRowID`, `ToxBatch`, `ToxConstituentRowID`),
1159  CONSTRAINT `FK_ToxConstituentRowID` FOREIGN KEY (`ToxConstituentRowID`) REFERENCES `ToxConstituentLookUp` (`ToxConstituentRowID`) ON UPDATE CASCADE,
1160  CONSTRAINT `FK_ComplianceCode` FOREIGN KEY (`ComplianceCode`) REFERENCES `ComplianceLevelLookUp` (`ComplianceCode`) ON UPDATE CASCADE,
1161  CONSTRAINT `FK_QACode` FOREIGN KEY (`QACode`) REFERENCES `QALookUp` (`QACode`) ON UPDATE CASCADE,
1162  CONSTRAINT `FK_SampleRowID` FOREIGN KEY (`SampleRowID`) REFERENCES `Sample` (`SampleRowID`) ON UPDATE CASCADE,
1163  CONSTRAINT `FK_ToxBatch` FOREIGN KEY (`ToxBatch`) REFERENCES `ToxBatch` (`ToxBatch`) ON UPDATE CASCADE,
1164  INDEX `ComplianceCode` (`ComplianceCode`),
1165  INDEX `LabSampleID` (`LabSampleID`),
1166  INDEX `QACode` (`QACode`),
1167
1168  INDEX `SampleID` (`SampleID`)
1169)
1170ENGINE = INNODB;
1171
1172DROP TABLE IF EXISTS `swampdb2003`.`ToxTest_Entry`;
1173CREATE TABLE `swampdb2003`.`ToxTest_Entry` (
1174  `ToxTestRowID` int(10) unsigned NOT NULL auto_increment,
1175  `SampleRowID` int(10) unsigned NOT NULL,
1176  `SampleID` VARCHAR(50) NULL,
1177  `LabSampleID` VARCHAR(20) NULL,
1178  `ToxBatch` VARCHAR(25) NULL,
1179  `ToxConstituentRowID` int(10) unsigned NOT NULL,
1180  `QACode` VARCHAR(25) NULL,
1181  `ComplianceCode` VARCHAR(5) NULL,
1182  `ToxTestComments` VARCHAR(150) NULL,
1183
1184
1185
1186  PRIMARY KEY (`ToxTestRowID`),
1187  UNIQUE INDEX `UniqueConstraint` (`SampleRowID`, `ToxBatch`, `ToxConstituentRowID`),
1188  CONSTRAINT `FK_ToxConstituentRowID` FOREIGN KEY (`ToxConstituentRowID`) REFERENCES `ToxConstituentLookUp` (`ToxConstituentRowID`) ON UPDATE CASCADE,
1189  CONSTRAINT `FK_ComplianceCode` FOREIGN KEY (`ComplianceCode`) REFERENCES `ComplianceLevelLookUp` (`ComplianceCode`) ON UPDATE CASCADE,
1190  CONSTRAINT `FK_QACode` FOREIGN KEY (`QACode`) REFERENCES `QALookUp` (`QACode`) ON UPDATE CASCADE,
1191  CONSTRAINT `FK_SampleRowID` FOREIGN KEY (`SampleRowID`) REFERENCES `Sample_Entry` (`SampleRowID`) ON UPDATE CASCADE,
1192  CONSTRAINT `FK_ToxBatch` FOREIGN KEY (`ToxBatch`) REFERENCES `ToxBatch` (`ToxBatch`) ON UPDATE CASCADE,
1193  INDEX `ComplianceCode` (`ComplianceCode`),
1194  INDEX `LabSampleID` (`LabSampleID`),
1195  INDEX `QACode` (`QACode`),
1196
1197  INDEX `SampleID` (`SampleID`)
1198)
1199ENGINE = INNODB;
1200
1201DROP TABLE IF EXISTS `swampdb2003`.`ToxTestDurLookUp`;
1202CREATE TABLE `swampdb2003`.`ToxTestDurLookUp` (
1203  `ToxTestDurRowID` int(10) unsigned NOT NULL auto_increment,
1204  `ToxTestDur` SMALLINT(5) NULL,
1205  `Unit` VARCHAR(5) NULL,
1206  `Active` TINYINT(1) NOT NULL,
1207
1208
1209
1210  PRIMARY KEY (`ToxTestDurRowID`),
1211  UNIQUE INDEX `ToxTestDur` (`ToxTestDur`)
1212
1213)
1214ENGINE = INNODB;
1215
1216DROP TABLE IF EXISTS `swampdb2003`.`UnitLookUp`;
1217CREATE TABLE `swampdb2003`.`UnitLookUp` (
1218  `UnitRowID` int(10) unsigned NOT NULL auto_increment,
1219  `UnitCode` SMALLINT(5) NULL,
1220  `Unit` VARCHAR(20) NULL,
1221  `Active` TINYINT(1) NOT NULL,
1222
1223
1224
1225  PRIMARY KEY (`UnitRowID`),
1226  UNIQUE INDEX `Unit` (`Unit`),
1227  UNIQUE INDEX `UnitCode` (`UnitCode`)
1228
1229)
1230ENGINE = INNODB;
1231
1232DROP TABLE IF EXISTS `swampdb2003`.`UnitsLookUp`;
1233CREATE TABLE `swampdb2003`.`UnitsLookUp` (
1234  `UnitsLookupRowID` int(10) unsigned NOT NULL auto_increment,
1235  `VariableName` VARCHAR(25) NULL,
1236  `FromDate` DATETIME NULL,
1237  `ToDate` DATETIME NULL,
1238  `Units` VARCHAR(50) NULL,
1239  `SignificantDecimal` SMALLINT(5) NULL,
1240  `CodeListName` VARCHAR(50) NULL,
1241
1242
1243
1244  PRIMARY KEY (`UnitsLookupRowID`),
1245  CONSTRAINT `FK_VariableName` FOREIGN KEY (`VariableName`) REFERENCES `VariablesLookUp` (`VariableName`) ON UPDATE CASCADE,
1246  UNIQUE INDEX `VariableName` (`VariableName`),
1247  INDEX `CodeListName` (`CodeListName`)
1248
1249)
1250ENGINE = INNODB;
1251
1252DROP TABLE IF EXISTS `swampdb2003`.`UserPreferences`;
1253CREATE TABLE `swampdb2003`.`UserPreferences` (
1254  `cboSamplingDeviceCode` VARCHAR(50) NULL,
1255  `cboOccupMethod` VARCHAR(50) NULL,
1256  `cboSampleLocation` VARCHAR(50) NULL,
1257  `cboStartingBank` VARCHAR(50) NULL,
1258  `cboPositionWaterColumn` VARCHAR(50) NULL,
1259  `cboUnitsStationWaterDepth` VARCHAR(50) NULL,
1260  `cboUnitsStreamWidth` VARCHAR(50) NULL,
1261  `cboUnitDistanceFromBank` VARCHAR(50) NULL,
1262  `cboSamplingCrew` VARCHAR(50) NULL,
1263  `cboHydromod` VARCHAR(50) NULL,
1264  `cboHydromodLoc` VARCHAR(50) NULL,
1265  `cboDatum` VARCHAR(50) NULL,
1266  `cboGPSDeviceCode` VARCHAR(50) NULL,
1267  `cboUnitAccuracy` VARCHAR(50) NULL,
1268  `txtLatitude` VARCHAR(50) NULL,
1269  `txtLongitude` VARCHAR(50) NULL,
1270  `cboDepthUnits` VARCHAR(50) NULL,
1271  `cboProjectID` VARCHAR(50) NULL,
1272  `cboAgencyCode` VARCHAR(50) NULL,
1273  `cboSeason` VARCHAR(50) NULL,
1274  `cboStationFail` VARCHAR(50) NULL,
1275  `cboResQualCodeFieldResult` VARCHAR(50) NULL,
1276  `cboSamplingDeviceCodeFieldResult` VARCHAR(50) NULL,
1277  `cboQACodeFieldResult` VARCHAR(50) NULL,
1278  `cboResQualCodeLabResult` VARCHAR(50) NULL,
1279  `cboPrepCode` VARCHAR(50) NULL,
1280  `cboDigestExtractCode` VARCHAR(50) NULL,
1281  `cboBasis` VARCHAR(50) NULL,
1282  `cboLabBatch` VARCHAR(50) NULL,
1283  `cboQACodeLabResult` VARCHAR(50) NULL,
1284  `cboToxBatch` VARCHAR(50) NULL,
1285  `cboQACodeToxTest` VARCHAR(50) NULL,
1286  `cboToxConc` VARCHAR(50) NULL,
1287  `txtDilution` VARCHAR(50) NULL,
1288  `cboResQualCodeToxResult` VARCHAR(50) NULL,
1289  `cboStatMethCode` VARCHAR(50) NULL,
1290  `txtMean` VARCHAR(50) NULL,
1291  `txtStdDev` VARCHAR(50) NULL,
1292  `txtProbability` VARCHAR(50) NULL,
1293  `txtPctControl` VARCHAR(50) NULL,
1294  `txtEvalThreshold` VARCHAR(50) NULL,
1295  `cboToxSigEffectCode` VARCHAR(50) NULL,
1296  INDEX `cboAgencyCode` (`cboAgencyCode`),
1297  INDEX `cboDigestExtractCode` (`cboDigestExtractCode`),
1298  INDEX `cboGPSDeviceCode` (`cboGPSDeviceCode`),
1299  INDEX `cboPrepCode` (`cboPrepCode`),
1300  INDEX `cboProjectID` (`cboProjectID`),
1301  INDEX `cboSamplingDeviceCode` (`cboSamplingDeviceCode`),
1302  INDEX `cboStatMethCode` (`cboStatMethCode`),
1303  INDEX `cboToxSigEffectCode` (`cboToxSigEffectCode`)
1304)
1305ENGINE = INNODB;
1306
1307DROP TABLE IF EXISTS `swampdb2003`.`VariableCodesLookUp`;
1308CREATE TABLE `swampdb2003`.`VariableCodesLookUp` (
1309  `VariableCodesLookUpRowID` int(10) unsigned NOT NULL auto_increment,
1310  `CodeListName` VARCHAR(50) NULL,
1311  `ValueCode` VARCHAR(50) NULL,
1312  `CodeDescription` VARCHAR(100) NULL,
1313  `Active` TINYINT(1) NOT NULL,
1314
1315
1316
1317  PRIMARY KEY (`VariableCodesLookUpRowID`),
1318  UNIQUE INDEX `UniqueConstraint` (`CodeListName`, `ValueCode`),
1319  INDEX `CodeDescription` (`CodeDescription`)
1320
1321)
1322ENGINE = INNODB;
1323
1324DROP TABLE IF EXISTS `swampdb2003`.`VariablesLookUp`;
1325CREATE TABLE `swampdb2003`.`VariablesLookUp` (
1326  `VariablesLookUpRowID` int(10) unsigned NOT NULL auto_increment,
1327  `VariableName` VARCHAR(40) NULL,
1328  `Active` TINYINT(1) NOT NULL,
1329  `Description` VARCHAR(250) NULL,
1330  `VariableType` TINYINT(3) UNSIGNED NULL,
1331
1332
1333
1334  PRIMARY KEY (`VariablesLookUpRowID`),
1335  UNIQUE INDEX `VariableName` (`VariableName`)
1336
1337)
1338ENGINE = INNODB;
1339
1340DROP TABLE IF EXISTS `swampdb2003`.`WBTypeLookUp`;
1341CREATE TABLE `swampdb2003`.`WBTypeLookUp` (
1342  `WBTypeRowID` int(10) unsigned NOT NULL auto_increment,
1343  `WBType` VARCHAR(5) NULL,
1344  `WBTypeDescr` VARCHAR(80) NULL,
1345
1346
1347
1348  UNIQUE INDEX `WBType` (`WBType`),
1349  UNIQUE INDEX `WBTypeDescr` (`WBTypeDescr`),
1350  UNIQUE INDEX `WBTypeRowID` (`WBTypeRowID`)
1351
1352)
1353ENGINE = INNODB;
1354
1355
1356SET UNIQUE_CHECKS = 1;
1357SET FOREIGN_KEY_CHECKS = 1;
1358
1359-- ----------------------------------------------------------------------
1360-- EOF