Français  Contact Us  Help  Search  Canada Site  DFO Home  Regions  Science  About MEDS Go to.... ---------- Argo AZMP Biological Currents Contaminants Drifting Buoys GTSPP ICES JCOMM NAFO Ocean Profiles Offshore Oil& Gas Remote Sensing RNODC Services Site Map SOOPIP Thermo-   salinographs TWL Waves WEBCIS Archive WEBCIS Search WOCE  Services  National  International  Data&Products

Rules and Server Procedures

Table Primary and Foreign Key Constraints

TWL_STATION - Primary Key = station_number

ALTERNATE_STATION_NAME
- Foreign Key =  station_number -> references TWL_STATION

DATUM_OF_ELEVATIONS
- Foreign Key =  station_number -> references TWL_STATION

TWL_STATION_SKETCH
- Foreign Key =  station_number -> references TWL_STATION

STATION_MONUMENT - Primary Key = station_number, benchmark_number
- Foreign Key =  station_number -> references TWL_STATION

BENCHMARK_ELEVATION
- Foreign Key =  station_number, benchmark_number -> references STATION_MONUMENT

NRCAN_ELEVATION.
- Foreign Key =  station_number, benchmark_number -> references STATION_MONUMENT

Rules by Constraint or Procedure (triggers)

1. A Station must exist for any information about the station or benchmarks of the station to also exist.

A TWL_STATION must exist (with a unique station_number) before any other information about the station or benchmarks in the station may be entered. If a station is deleted from TWL_STATION, the delete will cascade to all records in all tables where their station_number is the station_number of the deleted record in TWL_STATION.

2. A Benchmark must exist for any information explicitly about benchmarks to also exist.

A STATION_MONUMENT must exist (with a unique combination of station_number and benchmark_number) before any information is added to related records in the BENCHMARK_ELEVATION and/or NRCAN_ELEVATION tables. If a STATION_MONUMENT record is deleted, the delete will cascade to the related records in these other two tables.

3. Single ACTIVE record rule for DATUM_OF_ELEVATIONS, TWL_STATION_SKETCH, BENCHMARK_ELEVATION.

Only one record per group of equal station_number or station_number/benchmark_number in any of these tables may have status=ACTIVE, the others for that group are given the status = HISTORICAL. At present the choice of which record at a station or benchmark in one of these tables, to be set to ACTIVE is left to the person entering the data. And when one record is set to ACTIVE manually, all others in the group are set to HISTORICAL automatically (by a trigger).

Not sure we want to do this- Note: This function should be changed. If it can be said that only the latest entry in the database will be the ACTIVE one, then the trigger function could handle this easily. If this is the case, then the ACTIVE entries could be entered into the parent table, automatically writing only the historical records into the above tables by a trigger function. This makes application development (edits and computations) much easier to write.

4. Data entry and update Date/Time stamp rule for all records of all tables.

On creation of any record, the system date/time is recorded on that record in DBE_Time and DBU_Time. When a record is updated (edited), the system date is recorded in  DBU_Time. Triggers on the server handle this, and over ride any attempt to manually set the data/time stamps by a user application.

5. Position Rule

The two tables: TWL_STATION and STATION_MONUMENT each have a similar set of column varriables to describe the latitude and longitude position of the object in question ( guage or benchmark monument). The latitude and longitude record the position in decimal degrees (+ve North, +ve East) and are useful to search and plotting and validation applications. The other variables record whole degrees, whole minutes, and decimal seconds with either E /W or N/S. A trigger controls the transformation from one kind of entry to the other. Users typically enter data in the long hand version, whereas the trigger transforms the entry to a single number latitude or longitude. At present entries may be made in either of the fields, and transformed by the trigger in either direction. It is recommended that this function be expanded to allow users to enter data in any format - decimal degrees; whole degrees and decimal minutes ; whole degrees and whole minutes and decimal seconds.

6. Secure Privileges Rule on all records of all tables.

This rule is not immplemented at present. The rule is to give write and edit privileges on a station and all related information about that station, to one user account. The user account is protected by a username/password. Oracle typically can set privileges on tables or columns of tables, but not on specific records. It seems possible to write a trigger to enforce a degree of account security on a record, if that record is labeled with a user's account name in the field account of each table. Read privelegs will be set for all to read all of the data in the database.

7. Valid Entries by Check Constraint

Check constraints have been set on several columns of several of the tables. These are typical Oracle and SQL techniques. Check constraints are a list of valid entries allowable for the relevant column. There is no mechanism in Oracle or in the Benchmark model to define what is actually meant by these values of a check constraint, or rules for the decision of which to choose. See the rule 3 above for an example use of a check constraint. The check list applied to the province column of  TWL_STATION is a good example of a simple mechanism to ensure correct spelling of the provincial postal abreviation.

8. Valid values and combinations of values

Entry validation is important. Some columns could have functions which check entries as values within a range. Also, some columns should be cross compared during entry to make sure that valid combinations of entries are enforced. An example is in the BENCHMARK_ELEVATION table. Here, the hyd_corr and hyd_corr_epoch entries make sence only if the datum_name is set to 'IGLD85'.