Borehole Database Table Structures

Entity Relationship Diagram:

An entity relationship diagram for the borehole database is available at:

Entity Relationship Diagram

Tables:

• Sample Location
• Excavation Completion
• Fluid Penetration
• Lithology
• Sample Data
• Sample Analysis
• Penetration Test Results

Table Name: sample_location

Description: General information about the site where the subsurface investigation was conducted. Each investigation is assigned a unique name (official_name) based on a batch number, a sequence number within the batch and the USGS code for the 7.5 minute quadrangle that the investigation is located in.

Attribute	Description	Type	L	Units
official_name	Unique sample location name generated by SHMP	char	20
well_name	Sample location name assigned by staff	char	20
data_source	Source of data	char	32
report_no	Report or file number from source	char	10
site_name	Site identification name	char	50
site_id	Site identification code	char	10
operator	Investigator	char	32
job_number	Investigator job number	char	10
longitude	Longitude at ground surface (NAD 27 datum)	double		dec deg
latitude	Latitude at ground surface (NAD 27 datum)	double		dec deg
ref_elevation	Reference elevation	double		ft
total_depth	Total depth of well	double		ft
remarks	Description/comments	char	80

Table Name: excavation_compl

Description: Information about how the well was completed.

Attribute	Description	Type	L	Units
official_name	Unique sample location name generated by SHMP	char	20
well_name	Sample location name assigned by staff	char	20
excav_comp_date	Date (YYYY-MM-DD) drilling/excavation completed	integer
excavation_method	Method fo drilling/excavation	char	32
hole_diameter	Diameter of hole	double		in
casing_type	Type of casing	char	32
casing_inner_diam	Casing inner diameter	double		in
remarks	Description/comments	char	80

Table Name: fluid_pen

Description: Information about the fluid penetrations for a well. Since a well may have more than one fluid contact, the combination of official_name, fluid_name and top_depth must be unique.

Attribute	Description	Type	L	Units
official_name	Unique sample location name generated by SHMP	char	20
well_name	Sample location name assigned by staff	char	20
fluid_name	Name of fluid contact	char	32
top_depth	Depth to top contact	double		ft
bottom_depth	Depth to bottom contact	double		ft
fc_date	Fluid contact date (YYYY-MM-DD)	integer
remarks	Description/comments	char	80

Table Name: lithology

Description: Information about lithologic descriptions and qualifiers as recorded or interpreted from the log. The combination of official_name, lith_type and top_depth must be unique.

Attribute	Description	Type	L	Units
official_name	Unique sample location name generated by SHMP	char	20
well_name	Sample location name assigned by staff	char	20
layer_number	Numeric value assigned to an interval	integer
top_depth	Depth to top of layer	double		ft
bottom_depth	Depth to bottom of layer	double		ft
lith_type	Lithology code	char	6
remarks	Description/comments	char	240

Table Name: sample_data

Description: Basic information about the sample intervals recorded for a log such as the type of sampler and sample equipment. Each sample interval recorded from the log is assigned a sample id and the top and bottom depths as interpreted from the log are recorded. The combination of official_name and sample_id must be unique.

Attribute	Description	Type	L	Units
official_name	Unique sample location name generated by SHMP	char	20
well_name	Sample location name assigned by staff	char	20
sample_id	Sample identification	char	12
top_depth	Depth to top of sample	double		ft
bottom_depth	Depth to bottom of sample	double		ft
lith_type	Lithology code for core sample	char	6
sample_equipment	Equipment used to collect sample	char	32
remarks	remarks Description/comments	char	80

Table Name: sample_analysis

Description: Information that describes the results of any lab tests that were run on the sample intervals. For each record in sample_data there may be zero or more records in sample_analysis, i.e. one sample interval may be analyzed for dry density, moisture content, grain size etc. The combination of official_name, sample_id and parameter_name must be unique.

Attribute	Description	Type	L	Units
official_name	Unique sample location name generated by SHMP	char	20
well_name	Sample location name assigned by staff	char	20
sample_id	Sample identification	char	12
top_depth	Depth to top of sample	double		ft
parameter_name	Parameter measured	char	32
parameter_value	Reported result of the test	double
parameter_units	Reported units of measure	char	8

Table Name: pen_test_results

Description: Information concerning the results of any penetration tests conducted in the boring along with several parameters of the penetration test itself, such as hammer type, hammer mass, etc. The penetration test results are stored as one of three possible types: 1) true SPT tests (conforming to ASTM D1586 standards), 2) SPT "equivalent" tests where the operator has already done some conversion of raw blow counts and 3) raw blow_counts. The combination of official_name and sample_id must be unique.

Attribute	Description	Type	L	Units
official_name	Unique sample location name generated by SHMP	char	20
well_name	Sample location name assigned by staff	char	20
sample_id	Sample identification	char	12
top_depth	Depth to top of sample	double		ft
bottom_depth	Depth to bottom of sample	double		ft
hammer_type	Type of hammer	char	32
hammer_mass	Mass of hammer	real
hammer_fall	Free fall of hammer	real
blow_count	Number of blows per foot	real
spt_equivalent	Equivalent SPT blows/ft	real
nm	Blow count, SPT, ASTM D1586	real
test_date	Date of test (YYYY-MM-DD)	integer
remarks	remarks Description/comments	char	80