First, Check the Uniqueness of the Primary Keys

Appendix C:

Logical Checks

C3

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check that there is a corresponding Admin record (in n1).

Since there is an Admin record, we can check that for each tree,

there is at least one stocking standards reference number in

cb or cc, for BC and Alberta installations, respectively.

Check that there is at least one harvest reference number (in c5).

Check that at least one corresponding c4 record exists.

Check that there is at least one site prep reference number (in c6).

Since there is a site prep ref. no., we can check that MPAPOS is

not null if and only if the area was mechanically prepared

(i.e. N3.TECH_PREP = 'ME').

Check that artificially regenerated trees have a link to the

Art. Regen. History table (N5), and that naturally regenerated

trees do not. Also, for artificially regenerated trees, check

that the species given in the tree record matches the species

given in the Art. Regen. History table.

C4

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding c3 record exists.

Since a corresponding c3 record exists, we can check that a

competition description exists if and only if c3.RMT_CD = 'Y'

and c4.VIGOUR not in ('0','1').

Check a complicated relationship between C4 and CA:

First, the Conditions:

(1) C4.VIGOUR in ( '0', '1' )

(2) C4.HT >= 200

(3) C3.ORIGIN in ( 'S', 'NI', 'SD' ) AND C3.YR = C4.YR

(4) ( N5.SOP = 'SU' AND N5.STKAGE = '1.0+0.0'

AND N5.YRARCOMP = C4.YR ) AND ( C4.DMG1 > 'R'

AND C4.DMG2 > 'R' AND C4.DMG3 > 'R'

AND C4.DMG4 > 'R' AND C4.DMG5 > 'R'

AND C4.DMG6 > 'R' )

Now, the rules:

If none of (1), (2), (3), or (4) is true, then there must exist

at least one record in CA that relates to this C4 and has

CA.INCYEAR = CA.YR (indicating a current year leader measurement).

However, if at least one of (1), (2), or (4) is true, then there

must not exist any records in CA that relate to this C4 and have

CA.INCYEAR = CA.YR (indicating a current year leader measurement).

Check that no earlier C4 record for this tree has VIGOUR = 1,

and that if this record has VIGOUR = 1, then some earlier C4

record must exist with VIGOUR in ( 2, 3 ).

C5

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding n2 record exists.

Check that a corresponding c3 record exists.

C6

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding n3 record exists.

Check that a corresponding c3 record exists.

C8

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding n5 record exists.

Check that a corresponding c3 record exists.

C9

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding n6 record exists.

Check that a corresponding c3 record exists.

CA

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that at least one corresponding c3 record exists.

Check that at least one corresponding c4 record exists.

CB

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding ne record exists.

Check that a corresponding c3 record exists.

CC

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding nf record exists.

Check that a corresponding c3 record exists.

D1

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that at least one corresponding d2 record exists.

D2

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding d1 record exists.

D3

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding d1 record exists.

Check that a corresponding d2 record exists.

E0

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that an e2 record referring to this record exists.

E2

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding e0 record exists.

Check that an e3 record referring to this record exists.

E3

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding e2 record exists.

Check that an e4 record referring to this record exists.

E4

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding e3 record exists.

Check that a corresponding soil pit record exists (in s4).

G0

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that at least one corresponding g1 record exists.

G1

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that the corresponding g0 record exists.

H1

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding d2 record exists.

Check that no corresponding i1 records exist.

Check that no corresponding m1 records exist.

I0

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that at least one corresponding i1 record exists.

I1

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that the corresponding i0 record exists.

Check that no corresponding h1 records exist.

Check that no corresponding m1 records exist.

If the tree number is greater than or equal to 'S901',

then the site index tree must not also be a stand

description tree. Otherwise, the site index tree must

also be a stand description tree.

L0

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

L1

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that at least one corresponding l5 record exists.

L3

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding ne record exists.

Check that each species field is null if and only if *all* of its

corresponding well-spaced numbers (in l4) are null.

L4

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding l3 record exists.

Since a corresponding l3 record exists, check that if a well-spaced

number is non-null, then its corresponding species field in l3 is

also non-null. (If more than one l3 record matches, then don't

do any checking, since there is a problem in the l3 table.)

L5

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that the corresponding l1 record exists.

L6

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that the corresponding lc record exists.

L8

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

L9

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that the corresponding l8 record exists.

LA

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that the corresponding l9 record exists.

LB

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that the corresponding la record exists.

LC

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that at least one corresponding l6 record exists.

LD

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that all LE records for the same grid number

have the same standards ref. number.

Check (in LE) that for every existing combination of

TM/CP/BLK/INS/YR/EVENGRID/<species-type:CONIF/DECID>

there is exactly one LE record with SELCROP = 'S'.

Check that the total number of valid plots (sum of NUMVALID in

table LF) is 18.

Check (in LE) that for grid points on D strata and containing

a deciduous species, there is exactly one Capped Acceptable

Deciduous Density value. On non-D strata or those with only

coniferous species, there must be no Capped Acc. Decid. Dens.

LE

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that there is a survey measurement date (ld) record.

Check that a corresponding Survey Standards (nf) record exists.

LF

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that there is a survey measurement date (ld) record.

LG

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that there is a survey measurement date (ld) record.

M0

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that at least one corresponding m1 record exists.

M1

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that the corresponding m0 record exists.

Check that no corresponding h1 records exist.

Check that no corresponding i1 records exist.

If the tree number is greater than or equal to 'S901',

then the multistory tree must not also be a stand

description tree. Otherwise, the multistory tree must

also be a stand description tree.

N1

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

For BC installations, check that a corresponding nc record

exists.

For Alberta installations, check that a corresponding nd record

exists.

N2

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

N3

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

N5

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

N6

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

N9

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

NA

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

NB

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

NC

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check that a corresponding n1 record exists.

Since a matching n1 record exists, check that its PROVince is 'BC'

ND

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check that a corresponding n1 record exists.

Since a matching n1 record exists, check that its PROVince is 'AB'

NE

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check that there is a corresponding Admin record (in n1).

Since there is an Admin record, we can check that is

for a BC installation.

NF

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check that there is a corresponding Admin record (in n1).

Since there is an Admin record, we can check that is

for an AB installation.

NG

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

P2

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding c4 record exists.

Check that if the Competition Description for a given tree in a

given year is missing (i.e. VEG_COVER or MODAL_HT equals -1), then

no related p3 or p4 child records exist.

P3

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding p2 record exists.

If an exemption from the Vegetation Description list is not requested

(i.e. VEGDESCEXEMPT = 0), then check as follows:

If the quadrant is not compromised and a Veg. Description exists,

then check that the species appears in either the Veg Description

or in the list of veg species that would not appear in a Veg desc.

Note:

For Competition Descriptions in gridpoints that are adjacent to

gridpoints in other quadrants, species given in those adjacent

quadrants' Veg. Descriptions are also considered valid.

Otherwise, it has been requested that the species be exempted from

having to appear in the Vegetation Description (i.e.

VEGDESCEXEMPT = -1), so check as follows:

Check that the exemption is allowed. Exemptions are only allowed

when either: (1) the quadrant is compromised; (2) the Vegetation

Description was not done; (3) the gridpoint is adjacent to a

compromised quadrant; OR (4) the gridpoint is adjacent to the

exterior of the installation.

P4

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding p2 record exists.

Check that the given LAYER is not represented in the list of Top 5

Competitors.

Q0

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Q1

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding q0 record exists.

R0

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that at least one corresponding r1 record (for pre-harvest) or

r2 record (for post-harvest) exists.

R1

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

R2

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

S4

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that at least one corresponding s5 record exists.

Check that a corresponding s9 record exists.

S5

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.

Check the existence of the obligation number.

Check that a corresponding s4 record exists.

S6

First, check the uniqueness of the primary keys.

Now, check the records in this data reference one-by-one.