Skip to content

Latest commit

 

History

History
125 lines (113 loc) · 11.6 KB

Centralized Cruise Database - Technical Documentation.md

File metadata and controls

125 lines (113 loc) · 11.6 KB
Raw

Centralized Cruise Database - Technical Documentation

Overview:

The Centralized Cruise Database (CCD) is used to track information about each PIFSC research cruise including activities, regions, etc. to remove the need for each division/program to manage this information. This centralized database is available for all PIFSC database users to reference with their various division data sets.

Resources:

Database Setup:

  • Create separate database schemas for the database (data schema) and each application (application schemas):
  • Execute the role/permissions queries below using a DBA account:

  • Automated Installation

    • The automated database deployments utilize the Database Version Control Module SOP
    • open a command line window and switch the directory to the SQL folder of your working copy of this repository
    • Execute the corresponding deployment script for the given database instance using the "@" syntax (e.g. deploy_dev.sql for the development database instance) and enter the data schema credentials to deploy the database

  • Manual Installation

  • Cruise/reference data can be purged and reloaded for development purposes using refresh_ref_data.sql
  • Grant external schemas permissions to the Centralized Cruise Database
    • Modify the Centralized Cruise Database's grant_external_schema_privs.sql to replace the [EXTERNAL SCHEMA] placeholders with the given schema name and execute using the CEN_CRUISE schema
    • For detailed information on integrating the CCD into a new/existing data system refer to the CCD Data Integration SOP

External Dependencies

  • The Centralized Utilities Database is utilized in multiple CCD objects to perform calculations
    • URL (Git): git@picgitlab.nmfs.local:centralized-data-tools/centralized-utilities.git
    • Database: 1.0 (Git tag: cen_utils_db_v1.0)
  • The PARR Tools Database is integrated into the CCD to provide information about the Cruise Leg data sets and data set packages
    • URL (Git): git@picgitlab.nmfs.local:centralized-data-tools/parr-tools.git
    • Database: 1.3 (Git tag: PIFSC_Data_Set_db_v1.3)
  • The Centralized Authorization System (CAS) is used to perform authentication and authorization for the MOUSS data management application
    • Repository URL: git@picgitlab.nmfs.local:centralized-data-tools/authorization-application-module.git in the "CAS" folder
    • Version: 1.2 (git tag: central_auth_app_db_v1.2)
  • The CCD data schema permissions are listed in CEN_CRUISE_permissions.xlsx

Features:

  • DB Version Control Module (VCM)
    • Repository URL: git@github.com:PIFSC-NMFS-NOAA/PIFSC-DBVersionControlModule.git
    • Version: 1.0 (git tag: db_vers_ctrl_db_v1.0)
  • DB Logging Module (DLM)
    • Repository URL: git@github.com:PIFSC-NMFS-NOAA/PIFSC-DBLoggingModule.git
    • Version: 1.0 (git tag: db_log_db_v1.0)
  • Error Handler Module
    • Repository URL: git@picgitlab.nmfs.local:centralized-data-tools/apex_tools.git in the "Error Handling" folder
    • Version: 1.0 (git tag: APX_Cust_Err_Handler_db_v1.0)
  • Data Validation Module (DVM)
    • Repository URL: git@github.com:PIFSC-NMFS-NOAA/PIFSC-DataValidationModule.git
    • Version: 1.5 (git tag: DVM_db_v1.5)
  • APEX Feedback Form (AFF)
    • Repository URL: git@picgitlab.nmfs.local:centralized-data-tools/apex-feedback-form.git
    • Version: 0.1 (git tag: apex_feedback_form_db_v0.1)
  • Centralized Configuration (CC) project
  • Data history tracking package
    • Version Control Information:
      • URL: svn://badfish.pifsc.gov/Oracle/DSC/trunk/apps/db/dsc/dsc_pkgs
      • Files: dsc_cre_hist_objs_pkg.pks (package specs) and dsc_cre_hist_objs_pkg.pkb (package body)
    • Description: This was developed by the PIFSC Systems Design Team (SDT) to track data changes to a given table over time to facilitate accountability, troubleshooting, etc.

Database Diagram:

Data Flow:

Business Rules:

  • The business rules for the CCD are defined in the Business Rule Documentation and each specific business rule listed in the Business Rule List with a Scope of "Cruise DB" apply to the underlying database and rules with a Scope of "Data QC" apply to the QC criteria used to evaluate Cruise data in the underlying database.

Database Roles:

  • There are multiple database roles defined by the CCD to make permissions management easier
    • Business Rules:
      • Database Administrator Role (CR-DB-018)
      • Database Read/Write Role (CR-DB-019)
      • Database Readonly Role (CR-DB-020)
      • Database CRDMA Role (CR-DB-021)
      • Database Integration Role (CR-DB-022)
    • The database role permissions are listed in CCD_Role_Permissions.xlsx

Custom CCD Oracle Packages:

  • The CDVM was developed to extend the functionality of an existing Data Validation Module (DVM) to implement specific business rules defined for the CCD and associated modules
  • The CCDP was developed to provide functions and stored procedures for the CCD to provide functionality for the database and associated module(s)

Cruise Database Reference Data:

  • Cruise Legs and Cruise Leg Aliases:
    • Cruise Leg Name Alias Documentation
      • There is no limit on the number of cruise leg aliases that can be defined for a given cruise leg
      • The information for the defined cruise leg aliases can be viewed by querying the CEN_CRUISE.CCD_CRUISE_LEG_DELIM_V view
    • Cruise_Leg_DDL_DML_generator.xlsx contains sheets labeled "Cruises", "Cruise Legs", "Cruise Leg Aliases" that defines the cruises (CCD_CRUISES), cruise legs (CCD_CRUISE_LEGS), and cruise leg aliases (CCD_LEG_ALIASES) respectively for each research cruise defined in the Centralized Cruise database. The DML to load this reference data is generated in labeled columns.
      • These DML statements can be exported to a DML file so these values can be easily loaded into a given database schema.
    • An APEX application is available to manage the cruises, cruise legs, and leg aliases

Test Data Set: