ChkDB |
Open Source Rule-Based Data Checking | ||||||
|
Home Contact Download Releases Open Source License Project Contributing The PL/SQL Using ChkDB Installation Read-Me Rule-Sets ChkDB Tables E-R Diagram
Tiny Sample External Links Data Quality and Data Profiling (a glossary) |
The OIL_GAS_WELL_1 Data DomainAn oil and gas well, including (eventually) its completions, tests, cores, etc. Logs and production may be included, or they may be split into separate domains.
This is ogw1_well_domain.sql (without the license section)...
-- ==============================================================
-- Create and setup the OIL_GAS_WELL_1 data domain.
--
-- The OIL_GAS_WELL_1 domain is a set of tables/views
-- containing data required for data checking in an
-- oil and gas well database.
--
-- This is NOT a design of how well data could/should be
-- stored; it is a design of how stored data can be viewed
-- (or temporarily dumped) so that data checking can be done.
-- Fields are very wide so that they can (hopefully) hold
-- even bad values.
--
-- This is a work in progress. Suggestions and contributions
-- are appreciated.
--
-- Overview
-- Table/View Name Primary Key Columns
-- ------------------------ ---------------------------------
-- CHKDB_OGW1_WELL UWI
-- CHKDB_OGW1_WELL_SPUDDED UWI
-- CHKDB_OGW1_WELL_COMPLETE UWI
-- CHKDB_OGW1_DST UWI, RUN_NUM, TEST_NUM, TEST_UNIQ
-- CHKDB_OGW1_DST_GOOD UWI, RUN_NUM, TEST_NUM, TEST_UNIQ
-- CHKDB_OGW1_DST_PERIOD UWI, RUN_NUM, TEST_NUM, TEST_UNIQ,
-- PERIOD_UNIQ
--
--
-- ==============================================================
-- Different Ways of Using this Data Domain File
--
-- The 'create table' statements can be used either of two ways:
-- - to create tables that are populated and then checked, or,
-- - as guides to creating views into tables in your
-- database that are then checked.
--
-- In this file, tables are defined for basic entities
-- (ex. wells, DSTs) and views are defined for subset entities
-- (ex. completed-wells, good-DSTs). If this file is used
-- as written, the tables must be populated prior to checking.
--
-- Alternatively, you could create CHKDB_OGW1_WELL and
-- CHKDB_OGW1_DST as views into tables in your corporate
-- well database (and no pre-populating would be required).
--
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- CHKDB_OGW1_WELL
-- one row is: an oil or gas well (or observation well, etc.).
create table CHKDB_OGW1_WELL (
UWI VARCHAR2(80),
NUMERIC_ID NUMBER(15,0),
PARENT_UWI VARCHAR2(80),
DATA_SOURCE VARCHAR2(80),
WELL_NAME VARCHAR2(200),
GOVT_WELL_ID VARCHAR2(80),
WELL_CLASS VARCHAR2(80), -- ex. Lahee class
CURRENT_STATUS VARCHAR2(80),
CURRENT_STATUS_DATE DATE,
SPUD_DATE DATE,
FINISHED_DRILLING_DATE DATE,
RIG_RELEASE_DATE DATE,
COMPLETION_DATE DATE,
ABANDONMENT_DATE DATE,
OPERATOR VARCHAR2(80),
COUNTRY VARCHAR2(80),
PROVINCE_STATE VARCHAR2(80),
COUNTY VARCHAR2(80),
DISTRICT VARCHAR2(80),
REGION VARCHAR2(80),
AREA VARCHAR2(80),
PROSPECT VARCHAR2(80),
BOTTOM_HOLE_LATITUDE NUMBER(15,7),
BOTTOM_HOLE_LONGITUDE NUMBER(15,7),
SURFACE_LATITUDE NUMBER(15,7),
SURFACE_LONGITUDE NUMBER(15,7),
FIELD VARCHAR2(80),
POOL VARCHAR2(80),
KB_ELEVATION NUMBER(16,2),
CASING_FLANGE_ELEVATION NUMBER(16,2),
GROUND_ELEVATION NUMBER(16,2),
TOTAL_DEPTH NUMBER(16,2),
TRUE_VERTICAL_DEPTH NUMBER(16,2),
DRILLER_TD NUMBER(16,2),
LOGGER_TD NUMBER(16,2),
WHIPSTOCK_DEPTH NUMBER(16,2),
PLUGBACK_DEPTH NUMBER(16,2),
LEASE_NAME VARCHAR2(200),
LEASE_NUM VARCHAR2(80),
constraint CHKDB_OGW1_WELL_PK
primary key (UWI)
);
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- CHKDB_OGW1_WELL_SPUDDED
-- one row is: a well with a spud-date
create view CHKDB_OGW1_WELL_SPUDDED as
select * from CHKDB_OGW1_WELL
where SPUD_DATE is not null;
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- CHKDB_OGW1_WELL_COMPLETE
-- one row is: a well that has been completed or abandoned
create view CHKDB_OGW1_WELL_COMPLETE as
select * from CHKDB_OGW1_WELL
where COMPLETION_DATE is not null
or ABANDONMENT_DATE is not null;
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- CHKDB_OGW1_DST
-- one row is: a drill stem test for a well.
create table CHKDB_OGW1_DST (
UWI VARCHAR2(80),
RUN_NUM VARCHAR2(80) DEFAULT '1',
TEST_NUM VARCHAR2(80),
TEST_UNIQ VARCHAR2(80) DEFAULT '1',
NUMERIC_ID NUMBER(15,0),
DATA_SOURCE VARCHAR2(80),
REPORT_TEST_NUM VARCHAR2(80),
TEST_DATE DATE,
TEST_STATUS VARCHAR2(80),
GOOD_TEST_YN CHAR(1),
TEST_COMPANY VARCHAR2(80),
DST_TYPE VARCHAR2(80),
TOP_DEPTH NUMBER(16,4),
BOTTOM_DEPTH NUMBER(16,4),
TOP_FORMATION VARCHAR2(200),
BOTTOM_FORMATION VARCHAR2(200),
TD_AT_TEST_TIME NUMBER(16,4),
INIT_HYDROSTATIC_PRESS NUMBER(16,2),
VO1_INIT_PRESS NUMBER(16,2),
VO1_FIN_PRESS NUMBER(16,2),
SI1_FIN_PRESS NUMBER(16,2),
VO2_INIT_PRESS NUMBER(16,2),
VO2_FIN_PRESS NUMBER(16,2),
SI2_FIN_PRESS NUMBER(16,2),
FIN_HYDROSTATIC_PRESS NUMBER(16,2),
SULPHUR_STATUS VARCHAR2(80),
TEMPERATURE NUMBER(12,2),
constraint CHKDB_OGW1_DST_PK
primary key (UWI, RUN_NUM, TEST_NUM, TEST_UNIQ)
);
-- Note: Optional denormalization - period pressures:
-- VO1 = First Valve Open (ie. flowing) period
-- SI1 = First Shutin period
-- VO2 = Last Valve Open (ie. flowing) period
-- SI2 = Last Shutin period
-- Rule sets might use these denormalized columns or
-- they might use the CHKDB_OGW1_DST_PERIOD table (or both)
-- depending on how the database is organized.
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- optional table/view CHKDB_OGW1_DST_LIST
-- one row is: a drill stem test for a well.
--
-- This table may optionally be used for values that
-- cannot be determined in a 'create view' statement.
-- See the section for CHKDB_OGW1_DST_GOOD.
-- create table CHKDB_OGW1_DST_LIST (
-- UWI VARCHAR2(80),
-- RUN_NUM VARCHAR2(80) DEFAULT '1',
-- TEST_NUM VARCHAR2(80),
-- TEST_UNIQ VARCHAR2(80) DEFAULT '1',
-- GOOD_TEST_YN CHAR(1),
-- constraint CHKDB_OGW1_DST_LIST_PK
-- primary key (UWI, RUN_NUM, TEST_NUM, TEST_UNIQ)
-- );
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- CHKDB_OGW1_DST_GOOD
-- one row is: a drill stem test that was not a misrun.
create view CHKDB_OGW1_DST_GOOD as
select * from CHKDB_OGW1_DST
where GOOD_TEST_YN = 'Y';
-- or, alternatively, the where clause, above, could be:
-- where TEST_STATUS = 'GOOD_TEST';
-- or, another alternative using an optional table...
--
-- create view CHKDB_OGW1_DST_GOOD as
-- select * from CHKDB_OGW1_DST a
-- where exists
-- (select 'x' from CHKDB_OGW1_DST_2 b
-- where b.UWI = a.UWI
-- and b.RUN_NUM = a.RUN_NUM
-- and b.TEST_NUM = a.TEST_NUM
-- and b.TEST_UNIQ = a.TEST_UNIQ
-- and b.GOOD_TEST_YN = 'Y');
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- CHKDB_OGW1_DST_PERIOD
-- one row is: a period in a drill stem test for a well.
create table CHKDB_OGW1_DST_PERIOD (
UWI VARCHAR2(80),
RUN_NUM VARCHAR2(80) DEFAULT '1',
TEST_NUM VARCHAR2(80),
TEST_UNIQ VARCHAR2(80) DEFAULT '1',
PERIOD_UNIQ VARCHAR2(80),
PERIOD_SEQ_NUM NUMBER(4),
PERIOD_TYPE VARCHAR2(80),
DURATION_MINUTES NUMBER(4),
INIT_PRESS NUMBER(16,2),
FIN_PRESS NUMBER(16,2),
constraint CHKDB_OGW1_DST_PERIOD_PK
primary key
(UWI, RUN_NUM, TEST_NUM, TEST_UNIQ, PERIOD_UNIQ)
);
-- PERIOD_UNIQ is used in the key in order to handle cases
-- the period sequence is not always determinable.
-- If, in the database being checked, the initial and final
-- hydrostatic pressures are recorded in rows in a period
-- table, corresponding rows can be created in this table.
-- A row for IHP could have a sequence number of 0 (zero)
-- and the row for the FHP could have a sequence number
-- of 9.
-- If the period sequence number is always determinable,
-- it should go into PERIOD_UNIQ as a string and into
-- PERIOD_SEQ_NUM as a number.
-- PERIOD_TYPE values might be:
-- VO=Valve-Open SI=Shutin and maybe IHP and FHP
-- ==============================================================
-- Define the domain in CHKDB_DOMAIN
delete CHKDB_DOMAIN where DATA_DOMAIN = 'OIL_GAS_WELL_1';
insert into CHKDB_DOMAIN (DATA_DOMAIN, DESCRIPTION)
values ('OIL_GAS_WELL_1',
'Tables/Views for checking oil and gas well data');
-- ==============================================================
-- Define the tables/views in the domain in CHKDB_TABLE
delete CHKDB_TABLE where DATA_DOMAIN = 'OIL_GAS_WELL_1';
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
-- Tables in the domain.
-- = = = = = = = = = = = = = = = =
-- table CHKDB_OGW1_WELL
insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME,
DESCRIPTION,
CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN,
PRIMARY_KEY_LIST)
values ('OIL_GAS_WELL_1', 'CHKDB_OGW1_WELL',
'A well - an oil well, gas well, test well, etc.',
'Y', 'T', 'Y',
'UWI');
-- = = = = = = = = = = = = = = = =
-- table CHKDB_OGW1_WELL_SPUDDED
insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME,
DESCRIPTION,
CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN,
PRIMARY_KEY_LIST)
values ('OIL_GAS_WELL_1', 'CHKDB_OGW1_WELL_SPUDDED',
'A well that has been spudded',
'Y', 'V', 'Y',
'UWI');
-- = = = = = = = = = = = = = = = =
-- table CHKDB_OGW1_WELL_COMPLETE
insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME,
DESCRIPTION,
CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN,
PRIMARY_KEY_LIST)
values ('OIL_GAS_WELL_1', 'CHKDB_OGW1_WELL_COMPLETE',
'A well that has been completed or abandoned',
'Y', 'V', 'Y',
'UWI');
-- = = = = = = = = = = = = = = = =
-- table CHKDB_OGW1_DST
insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME,
DESCRIPTION,
CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN,
PRIMARY_KEY_LIST)
values ('OIL_GAS_WELL_1', 'CHKDB_OGW1_DST',
'A drill stem test',
'Y', 'T', 'Y',
'UWI, RUN_NUM, TEST_NUM, TEST_UNIQ');
-- = = = = = = = = = = = = = = = =
-- table CHKDB_OGW1_DST_GOOD
insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME,
DESCRIPTION,
CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN,
PRIMARY_KEY_LIST)
values ('OIL_GAS_WELL_1', 'CHKDB_OGW1_DST_GOOD',
'A drill stem test that is not a misrun',
'Y', 'V', 'Y',
'UWI, RUN_NUM, TEST_NUM, TEST_UNIQ');
-- = = = = = = = = = = = = = = = =
-- table CHKDB_OGW1_DST_PERIOD
insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME,
DESCRIPTION,
CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN,
PRIMARY_KEY_LIST)
values ('OIL_GAS_WELL_1', 'CHKDB_OGW1_DST_PERIOD',
'A flow or shut-in period in a drill stem test',
'Y', 'T', 'Y',
'UWI, RUN_NUM, TEST_NUM, TEST_UNIQ, PERIOD_SEQ_NUM');
-- = = = = = = = = = = = = = = = =
-- table CHKDB_OGW1_DST_2
-- This next one, if it is used, does not need any keys
-- because it is not a checkable table; it is a support
-- table that could be used to create a view into the table
-- of DSTs.
-- insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME,
-- DESCRIPTION,
-- CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN)
-- values ('OIL_GAS_WELL_1', 'CHKDB_OGW1_DST_2',
-- 'A flow or shut-in period in a drill stem test',
-- 'N', 'T', 'Y');
-- ==============================================================
|