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
Database
Data Domain
Implementation
Rule-Set
Do Checks
Queries
Results

External Links
Data Quality and
Data Profiling
(a glossary)

The OIL_GAS_SEIS_INV Data Domain

Seismic inventory data (aka. seismic metadata) - lines, information-items, physical-items, data-stores.

Managed by Brian Marshall
Organization none
Contact bmarshal@agt.net

This is ogsi_seisinv_domain.sql (without the license section)... 


-- ==============================================================
--  Create and setup the  OIL_GAS_SEIS_INV  data domain
--  for checking Oil & Gas Seismic Inventory data.
--
--  The OIL_GAS_SEIS_INV domain is a set of tables/views
--  containing data for checking purposes
--
--  This is NOT a design of how seismic inventory 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) store even bad values.
--
--  An implementation of this domain will only use the
--  tables and columns required for the checking to be
--  done.  The domain supports many ways of representing
--  seismic inventory data.
--
--  This is a work in progress.  Suggestions are appreciated.
--
--                    Overview
--  Table/View Name          Primary Key Columns
--  ----------------------   ---------------------------------
--
--         table for the fundamental entity
--  CHKDB_OGSI_INFO_ITEM     INFO_ITEM_ID
--
--         tables for basic entities that may exist
--  CHKDB_OGSI_SEIS_LINE     SEIS_LINE_ID
--  CHKDB_OGSI_PHYS_ITEM     PHYS_ITEM_ID
--  CHKDB_OGSI_DATA_STORE    STORE_ID
--
--         tables for many-to-many relationships
--  CHKDB_OGSI_INFO_LINE     INFO_ITEM_ID, SEIS_LINE_ID,
--                             INFO_LINE_UNIQ
--  CHKDB_OGSI_INFO_PHYS     INFO_ITEM_ID, PHYS_ITEM_ID,
--                             INFO_PHYS_UNIQ
--  CHKDB_OGSI_PHYS_STORE    PHYS_ITEM_ID, STORE_ID,
--                             PHYS_STORE_UNIQ
--
-- ==============================================================
--  Many different ways...
--
--  This domain supports a variety of ways of representing
--  seismic inventory data.  A database to be checked is
--  mapped into the CHKDB_OGSI tables in some particular way;
--  tables and columns for other ways are not populated.
--
--  The relationship between an info-item and a seismic line
--  can be stored as:
--    - a key into CHKDB_OGSI_SEIS_LINE in a row in
--        CHKDB_OGSI_INFO_LINE
--    - line attributes in a row in CHKDB_OGSI_INFO_LINE
--    - line attributes in CHKDB_OGSI_INFO_ITEM
--
--  The location of a physical item is specified in a STORE_ID
--  column, either:
--    - in CHKDB_OGSI_PHYS_STORE rows for the item, or,
--    - in CHKDB_OGSI_PHYS_ITEM rows (if only one location)
--
--  The CHKDB_OGSI_PHYS_STORE table, can contain multiple rows
--  for a physical item - a sequence of STORE_IDs which define
--  a history of the location of the item.
--
--  If used, CHKDB_OGSI_DATA_STORE contains information about
--  locations in/at which physical items may be stored; its
--  primary key is STORE_ID.  Data-stores may nest - a box
--  on a shelf in a room in a building.
--
--  CHKDB_OGSI_DATA_STORE.LOCATION can be used two ways:
--    - an alternate name or description for the data-store
--    - the location of the data-store
--  In the second case, a single CHKDB_OGSI_DATA_STORE row
--  is storing two levels of location information (ex. a row
--  for a box and LOCATION contains a room number).
--
--  A box may be a implemented as a data-store or as an
--  attribute in CHKDB_OGSI_PHYS_STORE or CHKDB_OGSI_PHYS_ITEM.
--
--
-- ==============================================================
--  Setup the tables/views of the data domain.
--
--  The 'create table' statements can be used:
--    - to create tables that are populated and then checked
--    - as guides to creating views into tables in your
--      database that are then checked.
--
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
--  CHKDB_OGSI_INFO_ITEM
--  one row is: a set of data associated with a seismic
--    line: ex. a SEG-Y file, a field dataset, section JPG
--
--  Two ways of identifying the seismic line are supported:
--     SEIS_LINE_ID and LINE_NAME+LINE_KEY_2.
--
--  The 'common line-attribute columns' also exist
--  in CHKDB_OGSI_SEIS_LINE and CHKDB_OGSI_INFO_LINE.
--  In this table, they are either:
--     - denormalized information about the line
--     - information about a subset of the line

create table CHKDB_OGSI_INFO_ITEM (
  INFO_ITEM_ID               VARCHAR2(80),
  ACTIVE_YN                  CHAR(1),
  EFFECTIVE_DATE             DATE,
  EXPIRY_DATE                DATE,
  NUMERIC_ID                 NUMBER(15,0),
  SEIS_LINE_ID               VARCHAR2(80),
  LINE_NAME                  VARCHAR2(80),
  LINE_KEY_2                 VARCHAR2(80),
  ITEM_TYPE                  VARCHAR2(80),
  ITEM_FORMAT                VARCHAR2(80),
  DATA_VERSION               VARCHAR2(80),
  SAMPLE_LENGTH_MS           NUMBER(10,4),
  SAMPLES_PER_RECORD         NUMBER(10,0),
  RECORD_LENGTH_MS           NUMBER(12,4),
  PROCESS_DATE               VARCHAR2(40),
  PROCESSED_BY               VARCHAR2(80),
  CREATE_START_DATE          DATE,
  CREATE_END_DATE            DATE,
  ITEM_BYTES                 NUMBER(15,0),
  ITEM_CATEGORY              VARCHAR2(80),
  ITEM_SUBCATEGORY           VARCHAR2(80),
    -- common line-attribute columns...
  COUNTRY                    VARCHAR2(80),
  PROV_STATE                 VARCHAR2(80),
  COUNTY                     VARCHAR2(80),
  DISTRICT                   VARCHAR2(80),
  REGION                     VARCHAR2(80),
  AREA                       VARCHAR2(80),
  PROSPECT                   VARCHAR2(80),
  SURVEY                     VARCHAR2(80),
  LINE_LENGTH                NUMBER(16,2),
  LINE_LENGTH_UNITS          VARCHAR2(20),
  FIRST_SHOT_POINT           VARCHAR2(20),
  LAST_SHOT_POINT            VARCHAR2(20),
  FIRST_REC_NUM              VARCHAR2(20),
  LAST_REC_NUM               VARCHAR2(20),
  FIRST_NLINE_NUM            NUMBER(10,0),
  LAST_NLINE_NUM             NUMBER(10,0),
  FIRST_XLINE_NUM            NUMBER(10,0),
  LAST_XLINE_NUM             NUMBER(10,0),
  MIN_LATITUDE               NUMBER(15,7),
  MAX_LATITUDE               NUMBER(15,7),
  MIN_LONGITUDE              NUMBER(15,7),
  MAX_LONGITUDE              NUMBER(15,7),
  constraint CHKDB_OGSI_INFO_ITEM_PK
    primary key (INFO_ITEM_ID)
);

-- NUMERIC_ID: a numeric surrogate key and/or foreign key
-- ITEM_TYPE:  ex: "STACK DATA", "SECTION", "OB", etc.
-- ITEM_FORMAT: ex: "SEGB", "SEGY", "JPG", "TEXT", etc.
-- DATA_VERSION: ex: "FIELD DATA", "RAW STACK", "FILT MIG"

-- ITEM_CATEGORY and ITEM_SUBCATEGORY might be used by
-- rules developed for a particular implementation.

-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
--  CHKDB_OGSI_SEIS_LINE
--  one row is: a seismic line - a line defined by a set
--    of geographic points at which seismic sources and/or
--    receivers were located.
--
--  The set of 'common line-attribute columns' also exist
--  in CHKDB_OGSI_INFO_ITEM and CHKDB_OGSI_INFO_LINE:
--     - denormalized information about the line, or,
--     - information about a subset of the line

create table CHKDB_OGSI_SEIS_LINE (
  SEIS_LINE_ID               VARCHAR2(80),
  ACTIVE_YN                  CHAR(1),
  EFFECTIVE_DATE             DATE,
  EXPIRY_DATE                DATE,
  NUMERIC_ID                 NUMBER(15,0),
  LINE_NAME                  VARCHAR2(80),
  LINE_KEY_2                 VARCHAR2(80),
  IS_3D_YN                   CHAR(1),
  PARENT_SEIS_LINE_ID        VARCHAR2(80),
  PARENT_LINE_NAME           VARCHAR2(80),
  PARENT_LINE_KEY_2          VARCHAR2(80),
    -- common line-attribute columns...
  COUNTRY                    VARCHAR2(80),
  PROV_STATE                 VARCHAR2(80),
  COUNTY                     VARCHAR2(80),
  DISTRICT                   VARCHAR2(80),
  REGION                     VARCHAR2(80),
  AREA                       VARCHAR2(80),
  PROSPECT                   VARCHAR2(80),
  SURVEY                     VARCHAR2(80),
  LINE_LENGTH                NUMBER(16,2),
  LINE_LENGTH_UNITS          VARCHAR2(40),
  FIRST_SHOT_POINT           VARCHAR2(40),
  LAST_SHOT_POINT            VARCHAR2(40),
  FIRST_REC_NUM              VARCHAR2(40),
  LAST_REC_NUM               VARCHAR2(40),
  FIRST_NLINE_NUM            NUMBER(10,0),
  LAST_NLINE_NUM             NUMBER(10,0),
  FIRST_XLINE_NUM            NUMBER(10,0),
  LAST_XLINE_NUM             NUMBER(10,0),
  MIN_LATITUDE               NUMBER(15,7),
  MAX_LATITUDE               NUMBER(15,7),
  MIN_LONGITUDE              NUMBER(15,7),
  MAX_LONGITUDE              NUMBER(15,7),
  constraint CHKDB_OGSI_SEIS_LINE_PK
    primary key (SEIS_LINE_ID)
);

--  NUMERIC_ID: a numeric surrogate key and/or foreign key

--  A line may have a parent-line if it is a reshoot or
--  subset of that parent-line.

--  LINE_KEY_2 (if used) is the data that, along with the
--  line-name, uniquely identify a line.

-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
--  CHKDB_OGSI_PHYS_ITEM
--  one row is: a thing in/on which a CHKDB_OGSI_INFO_ITEM
--    may be recorded.

create table CHKDB_OGSI_PHYS_ITEM (
  PHYS_ITEM_ID               VARCHAR2(80),
  ACTIVE_YN                  CHAR(1),
  EFFECTIVE_DATE             DATE,
  EXPIRY_DATE                DATE,
  NUMERIC_ID                 NUMBER(15,0),
  ITEM_TYPE                  VARCHAR2(80),
  ITEM_SIZE                  VARCHAR2(40),
  ITEM_SIZE_UNITS            VARCHAR2(40),
  ITEM_DENSITY               VARCHAR2(40),
  ITEM_NAME                  VARCHAR2(80),
  LABEL1                     VARCHAR2(80),
  LABEL2                     VARCHAR2(80),
  LABEL3                     VARCHAR2(80),
  PHYS_ITEM_STATUS           VARCHAR2(40),
  STORE_ID                   VARCHAR2(80),
  BOX_ID                     VARCHAR2(80),
  CREATE_DATE                DATE,
  constraint CHKDB_OGSI_PHYS_ITEM_PK
    primary key (PHYS_ITEM_ID)
);

-- NUMERIC_ID: a numeric surrogate key and/or foreign key
-- ITEM_TYPE ex: "3590", "CD", "LOCAL_DISK", "HR_DATABASE"
--   (implies an encoding and addressing scheme)

-- ITEM_NAME: what you ask for if you want this item
--  ex: "CD12345" (where the item is a CD)
--  ex: "C:" (where the item is the local disk)

-- LABEL1, LABEL2, LABEL3: optional, application specific
--  ex: LABEL1=Client's barcode, LABEL2=legacy system's
--        reel-id, LABEL3=storage facility's barcode

-- STORE_ID can contain the current location of the item.
-- BOX_ID can identify the containing box (which may be a
--   STORE_ID or an attribute of the item or storage)

--  Seismic data on disk - what is the physical item?
--    - If many info-items can be in a file,
--         the file could be the physical item.
--    - Alternatively, the directory, disk, server
--         or 'the network' could be the physical item.

-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
--  CHKDB_OGSI_DATA_STORE
--  one row is: a container or location in/at which one
--     or more physical items can be stored.
--
--  Data-stores may nest - ex: a Tape (a physical item)
--  may be in a Box on a Shelf in a Room in a Building.

create table CHKDB_OGSI_DATA_STORE (
  STORE_ID                   VARCHAR2(80),
  ACTIVE_YN                  CHAR(1),
  EFFECTIVE_DATE             DATE,
  EXPIRY_DATE                DATE,
  NUMERIC_ID                 NUMBER(15,0),
  CONTAINED_BY_STORE_ID      VARCHAR2(80),
  DATA_STORE_TYPE            VARCHAR2(80),
  DATA_STORE_STATUS          VARCHAR2(80),
  LOCATION                   VARCHAR2(80),
  STORE_NAME                 VARCHAR2(80),
  STORE_NAME2                VARCHAR2(80),
  STORE_BARCODE              VARCHAR2(80),
  STORE_DESC                 VARCHAR2(400),
  constraint CHKDB_OGSI_DATA_STORE_PK
    primary key (STORE_ID)
);

-- NUMERIC_ID: a numeric surrogate key and/or foreign key

--  DATA_STORE_STATUS: current status of location - ex:
--     in-use, closed, destroyed

--  STORE_NAME, STORE_NAME2, STORE_BARCODE can
--  contain alternate ways of identifying location.

--  LOCATION can contain either:
--    - an alternate name or description of this data-store
--    - the location of this data-store

-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
--  CHKDB_OGSI_INFO_LINE
--  one row is: an instance of an info-item being related
--    to (possibly a subset of) a seismic line.

--  The 'common line-attribute columns' also exist
--  in CHKDB_OGSI_SEIS_LINE and CHKDB_OGSI_INFO_ITEM.
--  In this table, they are either:
--     - denormalized information about the line
--     - information about a subset of the line

create table CHKDB_OGSI_INFO_LINE (
  INFO_ITEM_ID               VARCHAR2(80),
  SEIS_LINE_ID               VARCHAR2(80),
  INFO_LINE_UNIQ             VARCHAR2(80),
  ACTIVE_YN                  CHAR(1),
  EFFECTIVE_DATE             DATE,
  EXPIRY_DATE                DATE,
    -- common line-attribute columns...
  COUNTRY                    VARCHAR2(80),
  PROV_STATE                 VARCHAR2(80),
  COUNTY                     VARCHAR2(80),
  DISTRICT                   VARCHAR2(80),
  REGION                     VARCHAR2(80),
  AREA                       VARCHAR2(80),
  PROSPECT                   VARCHAR2(80),
  SURVEY                     VARCHAR2(80),
  LINE_LENGTH                NUMBER(16,2),
  LINE_LENGTH_UNITS          VARCHAR2(20),
  FIRST_SHOT_POINT           VARCHAR2(20),
  LAST_SHOT_POINT            VARCHAR2(20),
  FIRST_REC_NUM              VARCHAR2(20),
  LAST_REC_NUM               VARCHAR2(20),
  FIRST_NLINE_NUM            NUMBER(10,0),
  LAST_NLINE_NUM             NUMBER(10,0),
  FIRST_XLINE_NUM            NUMBER(10,0),
  LAST_XLINE_NUM             NUMBER(10,0),
  MIN_LATITUDE               NUMBER(15,7),
  MAX_LATITUDE               NUMBER(15,7),
  MIN_LONGITUDE              NUMBER(15,7),
  MAX_LONGITUDE              NUMBER(15,7),
  constraint CHKDB_OGSI_INFO_LINE_PK
    primary key (INFO_ITEM_ID, SEIS_LINE_ID, INFO_LINE_UNIQ)
);

--  INFO_LINE_UNIQ is used to make rows unique considering
--  that there can be a many-to-many relationship between
--  info-items and seismic-lines.

-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
--  CHKDB_OGSI_INFO_PHYS
--  one row is: an instance of an info-item being recorded
--    on/in a physical-item.

create table CHKDB_OGSI_INFO_PHYS (
  INFO_ITEM_ID               VARCHAR2(80),
  PHYS_ITEM_ID               VARCHAR2(80),
  INFO_PHYS_UNIQ             VARCHAR2(80),
  ACTIVE_YN                  CHAR(1),
  EFFECTIVE_DATE             DATE,
  EXPIRY_DATE                DATE,
  STORE_ID                   VARCHAR2(80),
  STORE_NAME                 VARCHAR2(200),
  STORE_POS                  NUMBER(20,0),
  STORE_OFFSET               NUMBER(20,0),
  constraint CHKDB_OGSI_INFO_PHYS_PK
    primary key (INFO_ITEM_ID, PHYS_ITEM_ID, INFO_PHYS_UNIQ)
);

--  INFO_PHYS_UNIQ is used to make rows unique considering
--  that there can be a many-to-many relationship between
--  info-items and physical-items.  If a sequence number
--  can be used, it can be stored in this column as a
--  string and in STORE_POS as a number.

--   Optional columns - use depends on physical item type:
-- STORE_NAME:   ex: a filename on a disk or CD
-- STORE_POS:    ex: a file position on a tape
-- STORE_OFFSET: ex: bytes offset from start of tape

-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
--  CHKDB_OGSI_PHYS_STORE
--  one row is: an instance of a physical item being stored
--    in/at a data-store (a container or location).

--  There is a many-to-many relationship between physical
--  items and data-stores.  However, an item can only exist
--  in/at one data-store at any one time; multiple rows for
--  one item describe the item being in/at a sequence of
--  data-stores.

create table CHKDB_OGSI_PHYS_STORE (
  PHYS_ITEM_ID               VARCHAR2(80),
  STORE_ID                   VARCHAR2(80),
  PHYS_STORE_UNIQ            VARCHAR2(80),
  ACTIVE_YN                  CHAR(1),
  EFFECTIVE_DATE             DATE,
  EXPIRY_DATE                DATE,
  BOX_ID                     VARCHAR2(80),
  constraint CHKDB_OGSI_PHYS_STORE_PK
    primary key (PHYS_ITEM_ID, STORE_ID, PHYS_STORE_UNIQ)
);

-- BOX_ID can identify the containing box (which may be a
--   STORE_ID or an attribute of the storage)


-- ==============================================================
--  Define the domain in CHKDB_DOMAIN

delete CHKDB_DOMAIN where DATA_DOMAIN = 'OIL_GAS_SEIS_INV';

insert into CHKDB_DOMAIN (DATA_DOMAIN, DESCRIPTION)
  values ('OIL_GAS_SEIS_INV',
 'Tables/Views for checking seismic inventory data (metadata)');


-- ==============================================================
--  Define the tables/views in the domain in CHKDB_TABLE
--    and the table keys in CHKDB_KEY and CHKDB_KEY_COLUMN

delete CHKDB_TABLE where DATA_DOMAIN = 'OIL_GAS_SEIS_INV';

-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
--  Tables in the domain.

-- =  =  =  =  =  =  =  =  =  =  =  =  =  =  =  =
-- table CHKDB_OGSI_INFO_ITEM

insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME,
              DESCRIPTION,
              PRIMARY_KEY_LIST,
              CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN)
  values ('OIL_GAS_SEIS_INV', 'CHKDB_OGSI_INFO_ITEM',
          'Data about (some part of) a seismic line',
          'INFO_ITEM_ID',
          'Y', 'T', 'Y');

-- =  =  =  =  =  =  =  =  =  =  =  =  =  =  =  =
-- table CHKDB_OGSI_SEIS_LINE

insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME,
              DESCRIPTION,
              PRIMARY_KEY_LIST,
              CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN)
  values ('OIL_GAS_SEIS_INV', 'CHKDB_OGSI_SEIS_LINE',
          'A seismic line',
          'SEIS_LINE_ID',
          'Y', 'T', 'Y');

-- =  =  =  =  =  =  =  =  =  =  =  =  =  =  =  =
-- table CHKDB_OGSI_PHYS_ITEM

insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME,
              DESCRIPTION,
              PRIMARY_KEY_LIST,
              CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN)
  values ('OIL_GAS_SEIS_INV', 'CHKDB_OGSI_PHYS_ITEM',
  'Media/facility on/in which information items are stored',
          'PHYS_ITEM_ID',
          'Y', 'T', 'Y');

-- =  =  =  =  =  =  =  =  =  =  =  =  =  =  =  =
-- table CHKDB_OGSI_DATA_STORE

insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME,
              DESCRIPTION,
              PRIMARY_KEY_LIST,
              CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN)
  values ('OIL_GAS_SEIS_INV', 'CHKDB_OGSI_DATA_STORE',
          'Location at which physical items can be stored',
          'STORE_ID',
          'Y', 'T', 'Y');

-- =  =  =  =  =  =  =  =  =  =  =  =  =  =  =  =
-- table CHKDB_OGSI_INFO_LINE

insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME,
              DESCRIPTION,
              PRIMARY_KEY_LIST,
              CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN)
  values ('OIL_GAS_SEIS_INV', 'CHKDB_OGSI_INFO_LINE',
          'Information items for seismic lines',
          'INFO_ITEM_ID,SEIS_LINE_ID,INFO_LINE_UNIQ',
          'Y', 'T', 'Y');

-- =  =  =  =  =  =  =  =  =  =  =  =  =  =  =  =
-- table CHKDB_OGSI_INFO_PHYS

insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME,
              DESCRIPTION,
              PRIMARY_KEY_LIST,
              CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN)
  values ('OIL_GAS_SEIS_INV', 'CHKDB_OGSI_INFO_PHYS',
  'Information items on physical items (ex. tapes)',
          'INFO_ITEM_ID,PHYS_ITEM_ID,INFO_PHYS_UNIQ',
          'Y', 'T', 'Y');

-- =  =  =  =  =  =  =  =  =  =  =  =  =  =  =  =
-- table CHKDB_OGSI_PHYS_STORE

insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME,
              DESCRIPTION,
              PRIMARY_KEY_LIST,
              CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN)
  values ('OIL_GAS_SEIS_INV', 'CHKDB_OGSI_PHYS_STORE',
  'Physical items (ex. tapes) in/at data-stores',
          'PHYS_ITEM_ID,STORE_ID,PHYS_STORE_UNIQ',
          'Y', 'T', 'Y');

-- ==============================================================