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)

Sample Data Domain

This is the sample_domain.sql file that comes with a ChkDB distribution (without the license section).

To create your own data domain, you create a file similar to the one below. It should have a name ending in _domain.sql) 

-- ==============================================================
--  ChkDB  -  Sample Domain -  CHKDB_SAMP_HR
--
--  Define a sample data domain that is an abstract view into
--  a sample Human Resources database.  A sample set of rules 
--  has been written for this domain.
--
--  Before this script is run, the ChkDB tables must exist.
--  Create the ChkDB tables with:   chkdb_tables.sql
--    or run setup_chkdb.sql
--
--  Populating the CHKDB tables with insert statements
--  is fairly portable but lengthy and tedious.
--  We need a nice GUI app that reads and writes this 
--  information from XML files.
--
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
--  Define the tables/views in the domain.
--
--  Note that columns are extra wide in domains in the 
--  hope that they will hold even bad data.
--
--  The create statements are commented out because, in this
--  case, we implement the domain as views into the database
--  to be checked.  See sample_implement.sql
--
--  Alternatively, we could have created the tables below, 
--  and populated them from the database prior to doing checks.
--
--  In any case, whether tables or views, they should look
--  like this:
--
--  create table CHKDB_SAMP_HR_DEPT (
--    DEPT_ID           VARCHAR2(40),
--    DEPT_NAME         VARCHAR2(80)  NOT NULL,
--    MANAGER           VARCHAR2(40),
--    FLOOR             VARCHAR2(20),
--    BUDGET_AMT        NUMBER(15,2),
--    BUDGET_APPRV      NUMBER(15,2),
--    BUDGET_EST        NUMBER(15,2),
--    constraint CHKDB_SAMP_HR_DEPT_PK
--      primary key (DEPT_ID)
--  );
--
--  create table CHKDB_SAMP_HR_GROUP (
--    DEPT_ID           VARCHAR2(40),
--    GROUP_ID          VARCHAR2(40),
--    GROUP_NAME        VARCHAR2(80),
--    ACTIVE_YN         CHAR(1)       NOT NULL,
--    GROUP_DESC        VARCHAR2(200),
--    constraint CHKDB_SAMP_HR_GROUP_PK
--      primary key (DEPT_ID,GROUP_ID)
--  );
--
--  create table CHKDB_SAMP_HR_EMP (
--    EMP_ID            VARCHAR2(40),
--    EMP_NAME          VARCHAR2(80)  NOT NULL,
--    DEPT_ID           VARCHAR2(40),
--    GROUP_ID          VARCHAR2(40),
--    POSITION          VARCHAR2(80),
--    HIRE_DT           DATE,
--    LAST_REVIEW_DT    DATE,
--    PROBATION_END_DT  DATE,
--    VACN_DAYS_TOT     NUMBER(10,2),
--    VACN_DAYS_USED    NUMBER(10,2),
--    constraint CHKDB_SAMP_HR_EMP_PK
--      primary key (EMP_ID)
--  );

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

delete CHKDB_DOMAIN where DATA_DOMAIN = 'CHKDB_SAMP_HR';

insert into CHKDB_DOMAIN (DATA_DOMAIN, 
                          DESCRIPTION) 
  values ('CHKDB_SAMP_HR', 
          'Sample Data Domain for Human Resources');


-- ==============================================================
--  Define the tables/views in the domain in CHKDB_TABLE

delete CHKDB_TABLE where DATA_DOMAIN = 'CHKDB_SAMP_HR';

-- =  =  =  =  =  =  =  =  =  =  =  =  =  =  =  =
-- table CHKDB_SAMP_HR_DEPT

insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME, 
              DESCRIPTION, 
              PRIMARY_KEY_LIST,
              CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN)
  values ('CHKDB_SAMP_HR', 'CHKDB_SAMP_HR_DEPT', 
          'Departments in the company', 
          'DEPT_ID',
          'Y', 'V', 'Y');

-- =  =  =  =  =  =  =  =  =  =  =  =  =  =  =  =
-- table CHKDB_SAMP_HR_GROUP

insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME, 
              DESCRIPTION, 
              PRIMARY_KEY_LIST,
              CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN)
  values ('CHKDB_SAMP_HR', 'CHKDB_SAMP_HR_GROUP', 
          'Groups in a department', 
          'DEPT_ID,GROUP_ID',
          'Y', 'V', 'Y');

-- =  =  =  =  =  =  =  =  =  =  =  =  =  =  =  =
-- table CHKDB_SAMP_HR_EMP

insert into CHKDB_TABLE (DATA_DOMAIN, TABLE_NAME, 
              DESCRIPTION, 
              PRIMARY_KEY_LIST,
              CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN)
  values ('CHKDB_SAMP_HR', 'CHKDB_SAMP_HR_EMP', 
          'Employees in the company', 
          'EMP_ID',
          'Y', 'V', 'Y');

-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
--  Define the other non-checkable tables.
--
--  Non-checkable tables, that are nevertheless involved
--  with ChkDB checking, may be inserted into CHKDB_TABLE:
--    - to record that they are involved with ChkDB checking
--    - to (optionally) delete them when ChkDB is removed
--
--  In this case, when ChkDB is removed, we want to delete
--  the sample 'corporate database' tables.

insert into CHKDB_TABLE (
              DATA_DOMAIN, TABLE_NAME, 
              CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN)
  values ('CHKDB_SAMP_HR', 'CHKDB_SAMP_DEPT', 
          'N', 'T', 'Y');

insert into CHKDB_TABLE (
              DATA_DOMAIN, TABLE_NAME, 
              CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN)
  values ('CHKDB_SAMP_HR', 'CHKDB_SAMP_GROUP', 
          'N', 'T', 'Y');

insert into CHKDB_TABLE (
              DATA_DOMAIN, TABLE_NAME, 
              CHECKABLE_YN, TABLE_VIEW_TV, DROP_ON_REMOVE_YN)
  values ('CHKDB_SAMP_HR', 'CHKDB_SAMP_EMPL', 
          'N', 'T', 'Y');


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