ATF DG Handbook - Version 4.2.docx

DEPARTMENT OF JUSTICE

DATA GOVERNANCE HANDBOOK VERSION 4.2

Department of Justice Bureau of Alcohol, Tobacco, Firearms, and Explosives

Bureau of Alcohol, Tobacco, Firearms, and Explosives Office of Science and Technology / Office of Science and Technology Date Last Updated Mar 11, 2019

Modified by Data Governance Team 99 New York Avenue, N.E. Washington, DC 20002

Change Information Page

Pages Modified By Description

Original

All

Originating document

ii

VERSION 4.2

TABLE OF CONTENTS

Contents

1 PREFACE

This document includes information that may not be disclosed outside the Government and will not be duplicated, used, or disclosed - in whole or in part- for any purpose other than to evaluate this document and/or use this document for ATF assigned projects.

1.1 Purpose

The Bureau of Alcohol, Tobacco, Firearms, and Explosives (ATF) is establishing Data Governance Standards in accordance with the Agile Development Life Cycle to support the development, enhancement, and maintenance of new and existing systems. The purpose of this Data Governance Handbook is to provide the data standards and guideline that is necessary to establish and maintain Bureau’s data storage. There are separate documents explaining ATF Agile Development Life Cycle.

1.2 Scope

This Data Governance (DG) handbook is applicable to all ATF project/contract personnel, assigned to establish or maintain systems used by ATF that contains database.

1.3 Objective

The objective of this Data Governance (DG) handbook is to provide a source document that a technical or non-technical employee can refer to when needing information on how to implement, maintain and/or gather the data management information necessary to support the day-to-day operation/development of ATF systems. This handbook specifically defines ATF Data Governance guideline and standards.

2 DATA GOVERNANCE

2.1 Introduction

Organizations develop policies and implement formal procedures to: maintain data integrity, protect existing data, maintain data quality and ensure data confidentiality. Data life cycle management (DLM) is a policy-based approach to managing the flow of an information system's data throughout its life cycle: from creation and initial storage to the time when it becomes obsolete and decommissioned. However, Data Governance is a subset of Data Management.

The Data Management Association (DAMA) defines Data Governance as “The exercise of authority, control and shared decision making (planning, monitoring and enforcement) over the management of data assets."

Data standards are the rules by which data are described and recorded. In order to share, exchange, and understand data, we (ATF) must standardize the format as well as the meaning. The designated system owner(s) of individual system owns the data and is mainly responsible for data quality. OST is mainly responsible for storing and accessing data in a secured and efficient way for ATF systems. OST in coordination with individual system owners will identify future needs to add/modify Data Standards.

Data Governance (DG) activities are applicable to Systems Development Life Cycle (SLC) processes. They facilitate successful DG for each project under the requirements of the Bureau’s SLC Order. Establishing and enforcing Data Standards are part of Data Governance activity. This handbook defines a detailed set of data standards and guideline that will allow the individual project managers and development team to apply for their individual projects.

At the initial stage, Data Governance Team consists of a small group of personnel from OST. Over time, as the program matures and funding available, Data Governance Team will be expanded. Data Governance Handbook addresses standards and best practice of data service/data access API. However overall Service Oriented Architecture (SOA) governance is out of scope for Data Governance Handbook.

2.2 Benefit of Data Governance

· Easy change management.

· Ensure data alignment.

· Accurate and consistent master data access.

· Enable simple governance.

2.3 Benefit of Data Standardization

· Improved data quality.

· Increased data compatibility.

· Improved consistency and efficiency of data collection.

· Reduced data redundancy.

· Improved data access.

2.4 Data Governance Activities

· Guidelines and Methods for Effective Data Planning.

· Data Access procedures and API standards.

· Data Standards.

· Data Models and Data Dictionary.

· Data Analysis.

· Reference Data and Master Data Management.

· Data Consolidation.

· Design Review.

· Enforcement of Standards.

2.5 Responsibilities

Responsibilities - Data Governance Team:

Some of the Data Governance responsibilities include (but not limited to):

· Develop and maintain all guidelines and standards in DG Handbook to support all Data Governance activities.

· Provides oversight to ensure that the guidelines and standards of the DG handbook is enforced within all application development activities.

· Review database changes.

· Review new or modified data models for individual project.

· Review data dictionary for new/modified data elements.

· Provide data architectural expertise to project team.

· Work with development team to identify reusable data elements.

· Maintain and regulate data service/ data access API libraries.

· Over time, provide and enforce data service/data access API standards.

· Work with individual IPT team to identify and resolve and/or recommend a solution to management for data-related technical issues.

· Work as a group to recommend changes to improve new and/or updated database designs for integrity, integration, maintainability and performance.

Responsibilities - Development Team:

Some of the Development Team responsibilities include (but not limited to):

· Develop all internal procedures and processes in accordance with Data Governance Guidelines and Standards.

· Work with the Data Governance Team and Production DBAs to ensure that production and test database servers are in-synch.

· Work with the Data Governance Team to recommend performance enhancements, and develop and test those enhancements.

· Ensure that the software and hardware forming the database system meet the needs of application users.

· Follow Data Governance Standards guideline when modifying existing database or creating new database.

· Use existing data services (if available) or develop reusable data services (when applicable).

· Create/update data models and create/update data dictionary relevant to the project.

· Use data modeling tool as recommended by Data Governance Team.

· Work to ensure that the physical data model transformation from the logical data model is validated/reconciled.

· Development DBA (as assigned by IPT) should:

· Facilitate data integration among related systems.

· Help define and maintain database security.

· Define control procedures for data backup and recovery.

· Perform regular database backups and recovery when necessary.

· Provide all Data Governance deliverables (data model, data dictionary, data services etc.) to Data Governance Team for review and approval.

Responsibilities – Production Database Administrator (DBA):

Some of the Production DBA responsibilities include (but not limited to):

· Maintain the Production, Staging (if any), and Testing database environment.

· Work with the Development DBAs to ensure that Production and Test database servers are in-synch.

· Ensure that the software and hardware forming the database system meet the needs of the application users.

· Review database/data modification scripts, data conversion plans and data maintenance procedures.

· Work with the Development DBAs to recommend performance enhancements, and implement those enhancements.

· Facilitate data integration among systems.

· Help define and maintain database security.

· Define control procedures for data backup and recovery.

· Perform regular database backups and recovery when necessary.

· Perform regular performance monitoring of the production databases and develop tuning recommendations for OST.

Responsibilities – System Owner Some of the System Owner’s responsibilities include (but not limited to):

· System Owner owns the data of the individual system and therefore responsible for the quality of data.

· Identify data inconsistency and work with OST resolving the issue. There are two part in this effort: a) cleansing the production data, and b) fixing the application to address future data corruption.

· Identify “core” business data elements and coordinate with Data Governance Team so that they are included in Data Standard Handbook.

3 SYTEMS LIFE CYCLE (SLC) MANAGEMENT

The Data Management Handbook provides details of all data management activities applicable to Systems Development Life Cycle (SLC) processes and facilitates successful data management for each project under the requirements of the Bureau’s SLC Order. The handbook defines a common approach to each Enterprise level data management activity and allows the individual project managers to select an appropriate mix of data management activities that are applicable to their individual projects.

For details, please see ATF Systems Life Cycle (SLC) Management Handbook.

3.1 Database Development Life Cycle (DDLC)

The Database Development Life Cycle (DDLC) consists of DM activities that are mapped to the various phases of the SLC activities. This mapping provides a measurable implementation of DM processes and their deliverables.

Application Development Life Cycle

(ADLC)

Database Development Life Cycle

(DDLC)

4 DATA ADMINISTRATION

4.1 Introduction

One of the main responsibilities of Data Governance is to establish policies and guidelines to build and manage repository of Data Governance related artifacts that includes Enterprise Data Modeling and data dictionary. For ATF, currently the choice of data modeling tool is ER Studio Data Architect. Other tools can be considered for special cases; however, the project team and/or developer need prior approval from Data Governance Manager. For example, different modeling tool (pre-approved by Data Governance Team) can be used for XML / non-relational database.

The developer is required to follow the Data Modeling Standards to arrive at the relevant conceptual, logical and physical data models. The developer is also required to use the Data Naming Standards to describe all the data items. Finally, the developer is required to deliver such data models and data dictionary that will satisfy the approved functional requirements of the application, to the Bureau's Data Governance Team (DGT) for review and approval.

Section 6 (Data Naming Standard) addresses the naming conventions for logical objects and physical objects in the design phase and development phase of the software development lifecycle using the CASE tool: ER Studio Data Architect. Logical objects include entities and attributes; physical objects include tables, columns, primary keys, foreign keys, indexes, sequence, views, procedures, functions, triggers, snapshots, packages and package bodies. Detailed Data Naming Standards are described fully in Section 6 (Data Naming Standards).

Data Modeling techniques and standards are described fully in Section 7 (Data Modeling and Standard).

The development team should use existing data model (if available) as a baseline. However if no baseline data model is available at that time, the development team should use reverse engineering to create the baseline model.

4.2 Database Administration

The following section describe, in detail, all the processes, standards and guidelines to manage the database objects and any discussions on the application objects will be outside the scope of this section.

The primary audience for this section is the Database Administrator (DBA) since she/he will be the one who will take actions based on these processes, standards and guidelines while creating, maintaining and administering databases. The DBA also will be the person who will guide the developers in adhering to the expectations set by these processes, standards and guidelines.

4.2.1 Various Database Environments

ATF supports database in multiple environment to make sure smooth transition from development to testing to finally deployment to production. The first is the development database environment where the database objects are developed (DEV). The second database environment is testing (TST) and this will reside on the Bureau’s test server and will be administered by contractors through OST ITSMD. For some projects, there will be a Staging database environment (STG) and this will reside on the Bureau’s staging host and will be administered by OST ITSMD. The last database environment in the SLC path is Production (PROD) and this will reside on the Bureau’s production servers and will be administered by OST ITSMD. The CM Repository Tool will track the path of all database objects delivered by the developers through these stages.

A “cookie cutter” approach should be followed in defining Applications, Users and database Schema so that this entire Database Administration Model can be replicated for each Application/ User/Schema on any of the Bureau’s Database Servers. This approach, therefore, makes the routine chores of the DBAs repeatable and hence, measurable.

4.2.2 Database Architectural Rules

The following are the database architectural rules:

· None of the developers needs a host level connection to database server. However, should there be a need for such an access; the developer needs to contact the Application Development Manager for further details.

· Developers shall NOT have access to any of the production database servers unless OST management approve read-only requests from developer(s).

· The end users shall not have access to the DEV database, unless development database is used for UAT testing for agile development.

· Data Governance Manager should have read access to all database (Development, Testing and Production).

· All authorized database user will logon to DEV using IT Security approved proper authentication process. End-users will be restricted to the navigational capabilities of the applications they are using.

· Reusable View and/or API should be used to access core data from database.

· Conceptually, there are four data domains for each application. They are: DEV data domain, TST data domain, STG data domain (for some projects), PROD data domain.

4.2.3 UserID, Object and File Naming Guideline

Since different users perform different functions within the DEV database, it follows that some users perform functions that are not performed by (or restricted to) other users. So, it becomes natural that these users are put into a “class” system that permits them to perform only those functions they are authorized to do. There are three classes of UserIDs within the DEV database. They are:

All the database objects should be created using the “Data Naming Standards” described in section 6 of this document.

DDL/SQL File naming guideline are described under “Section 6 (Database Design and Naming Standards).

4.3 Data Migration

There are two types of data migration: a) Data migration to new production database as part of deployment; b) Data migration from production to test database for testing purpose. The production DBAs shall not perform any data loads into Test database unless they receive Data Migration Document from the development team.

Data migration to new production database:

The development team should develop and test the data migration script in development environment. The production dba should run the data migration script in testing environment with production database as source; however, the project IPT team should validate the success of data migration. When successfully tested in test environment, the production dba should run the same data migration script in production environment as part of production deployment and IPT team should validate the data migration.

Data migration from production to test database:

During the development phase of a project, the primary developer who owns the development schema usually creates his/her own test data and this data is usually of small volume and is used to test the functionality of the application. ATF approved COTS products can be used for generating sample test data to mimic production data volumes (example: SL*Plus, HP Load Runner, JMeter, Selenium, etc.).

However, during testing phases of the application development life cycle, sometimes it requires more rigorous testing that usually means considerable volume of test data. Filtered/sanitized data (removing/replacing sensitive data) from production database should be extracted for testing environment. Development team in coordination with System Owner will define sensitive data and developer will develop script to extract filtered data. Before any data migration from production, IT Security and Data Governance Team’s (DGT) approval is needed. For exceptional cases, if a section of unfiltered production data is needed for testing, a data waiver need to be approved in advance. Please contact IT Security for “data waiver approval” process.

Document Data migration process:

The data migration process must be manageable, repeatable, restart-able and certifiable. Document the details of data migration process, as it tends to be specific to each application being developed and tested. This document, at the minimum, shall contain the following:

· Target database table names.

· Source file names or source table names.

· A mapping of the target column/field names to their source column/field names along with their field types and field sizes.

· Extract program names and general outline of their logic

· Special handling logic, if applicable, to any of the data fields being migrated such as default values, conversion of nulls, etc.

· Output filenames for transfer.

· Estimated population for each of the extract files and any applicable partitioning scheme to handle oversized extracted files.

· Control file names for handling each of the extracted files, if applicable.

· Definitions of validations (if applicable) at the Load time and detailed descriptions of anticipated exceptions.

Data Migration Script Guideline:

The following guidelines should be followed while loading data into target database:

· All the primary key constraints should be active during the data load. This will expose problems with duplicate keys in the incoming data and may allow development team to redesign and address the issues.

· The foreign key constraints rely on the fact that the columns that are used as foreign keys are “NOT NULL”

· All non-primary indexes should be dropped prior to the load to reduce load time. However, these indexes should be re-created immediately after the completion of individual tables.

4.4 Data Design Review

The database design reviews can be formal or informal. During the whole lifecycle of the project, development team should seek feedback from Data Governance (DG) Team regarding the database architectural design, disk space allocations, object names chosen, choice of primary and foreign keys, additional indexes, etc. Data Governance (DG) team must oversee all designs, upgrades and maintenance deliverables that affect the Bureau’s databases. This handbook establishes the basis for reviewing all such designs (data models, data dictionary, data access APIs, etc.), upgrades and maintenance. No database change should go to production until Data Governance Team approves it. For non-relational database (example XML database), the development team will work with Data Governance Team to identify and establish standards and best practice as these are new to ATF environment.

4.5 Change Management Process

The Bureau, through its SLC, requires that no work can proceed without an approved Change Management Process. For details, please refer to ATF Change Management Process Handbook.

4.6 Database Configuration Management

If a data model does not exist for the application in the CM Repository Tool, then the existing production database schema will be used to reverse-engineer the schema into an ER Studio DA model. Once such a data model is created through the reverse-engineering process, it will be used as a starting point for the maintenance project. In addition, the vendor shall deliver the ER Studio DA Model (.DM1 file) for the entire application, not just for those entities/attributes being affected by the maintenance. The Bureau can classify database development and maintenance into two project scenarios: a) New Development and b) Enhancement of existing application/database.

4.6.1 New Database Development Procedures

· Step I: IPT team in consultation with Data Governance Manager will make a decision on where new database will be residing (server location, database and version)

· Step II: Development team provides initial data model, data dictionary and data flow diagram to Data Governance Team to review.

· Step III: Data Governance Team Approve initial data base design to move forward.

· Step IV: Project Manager assign OST DBA to the project. Development team create ticket for development database

· Step V: Production DBA create the development database in DEV environment.

· Step VI: When ready for test, development team provides updated data model, data dictionary and data flow diagram to Data Governance Team.

· Step VII: When approved by the Data Governance Team, development team create a ticket.

· Step VIII: Production DBA create the test database in TST environment.

· Step IX: While testing, development team may need to modify database to address issues. In that case, development team will work with Data Governance Team (to get approval for the delta change) and Production DBA (to implement delta change in test environment).

· Step X: Once the product is ready to go to production, final approved data artifacts (including final database change script) are saved in the repository.

· Step XI: Production DBA apply the scripts to production environment during the IPT approved timeframe.

If a new database is replacing old database, development team should notify to production DBA and Data Governance team when old database can be decommissioned. For Agile programing, steps VI to XI can be repetitive.

4.6.2 Database Modification/Enhancement Procedures

· Step I: The IPT team in consultation with Data Governance (DG) team decides database changes is required for this project.

· Step II: Project Manager assign OST DBA to the project. Development team create ticket for development database.

· Step III: Production DBA create the development database in DEV environment.

· Step IV: Development team provides modified data model, data dictionary and data flow diagram to Data Governance Team to review.

· Step V: Data Governance Team Approve database design to move forward.

· Step VI: When approved by the Data Governance Team, development team create a ticket.

· Step VII: Production DBA create the test database in TST environment from production database and apply delta change.

· Step VIII: While testing, development team may need to modify database to address issues. In that case, development team will work with Data Governance Team (to get approval for the delta change) and Production DBA (to implement delta change in test environment).

· Step IX: Once the product is ready to go to production, final approved data artifacts should be saved in the repository. Final database change script saved in Repository.

· Step X: Production DBA would apply the scripts to production environment during the IPT approved timeframe.

5 DATABASE SECURITY STANDSARDS AND ENFORCEMENT

This section will be further elaborated in future releases. Currently we are not addressing the security aspect of the database. It’s up to the ISSO to determine detailed data security guidance and regulate accordingly.

For implementation of data auditing and data forensics capability in SQL server database, please see section “6.4.2 Audit Fields and Data Auditing” for details.

5.1 Responsibility

The Bureau’s Automated Information Systems (AIS) Security Program, ATF Order 7500.2, provides the authority to the Bureau’s Information Systems Security Officer (ISSO) to develop, publish and monitor information security policies across all data sources within the Bureau. This AIS Security Program is consistent with Government-wide policies, standards, and procedures issued by the Office of Management and Budget (OMB), the Department of Commerce, the General Services Administration (GSA) and the Office of Personnel Management (OPM).

While the Bureau’s ISSO establishes the Bureau-wide policies and guidelines under the ATF Order 7500.2, the Bureau’s Data Governance Manager assists the Chief, Information Systems Division (OST) in implementing those security policies and guidelines when they relate to data.

Project Team (DM-IPT), develops and implements a common set of database procedures and processes that will satisfy such standards.

5.2 Data Security Measures

The data owner (or the system owner) and the developer will jointly define the various business roles within the given application. Some examples of such roles will be manager, administrator, analyst, clerk, and reviewer. One or more database roles will manage all access to data within a database instance. It is preferred that all applications manage their data security using these database-level roles. Sometimes it may be necessary to rely on application-level security to manage more granular data access by different users but such cases this should be discussed with the OST ITSDD DBA prior to coming to the DM-IPT for design approval.

Developers will then assign object-level grants to these roles and create the following deliverables for the formal design review process:

· A SQL script that will create all the roles defined above. This will be named as xxxxROLE.SQL

· A SQL script that will grant the object-level privileges such as select, insert, update and delete to these roles. This will be named as xxxxGRNT.Sql

· A matrix, in the format of a spread sheet, showing the names of these roles, the names of the database objects such as table, view, sequence, procedure and the object-level privilege assigned to the role on the object. This will be named as xxxxROLE.XLS.

If “best practice” recommendation by database vendor is in conflict with above-mentioned ATF standards, the development team should consult with Data Governance Team to resolve the issue.

5.3 Data Auditing and Data Forensics Capability:

Four Audit fields should be added (create user id, create date, modified user id, modified date) for every data base tables. Please review Common Data Element (Section 7) for standard details of audit fields.

To track the key data that has been changed in MS SQL Server Database, ATF recommend to implement one of the following:

· Enabling Change Data Control (CDC) with default data retention of 60 days

· Temporal Table

They both have strengths and limitations, therefore should be used based on specific business and/or data security requirement.

Change data capture records insert, update, and delete activity that is applied to a SQL Server table. This makes the details of the changes available in an easily consumed relational format.

Temporal tables captures the lifetime of a record based on the physical dates the record was removed or updated.

6 DATABASE DESIGN AND NAMING STANDARDS

The Data Governance Program Office recognizes the cost of resources to retrofit all data objects to these new Data Naming Standards. To ease the burden of such retrofits, these Data Naming Standards will be applied only to NEW or MODIFIED data objects. All new data elements, new database objects (both logical and physical) and new databases are required to follow these Data Naming Standards. A new attribute (or column) for an already existing entity (e.g. table or view) is treated the same as a new attribute for a new entity for determining whether these Data Naming Standards are applicable. This approach will support the application of these Data Naming Standards to both new system development projects as well as those maintenance projects that repair or enhance existing production systems.

For COTS/GOTS products, that uses vendor provided data structure, can be exempt from ATF standards (if it conflicts with ATF standard). However for customized fields, it is expected to follow ATF standards.

6.1 Data Base Creation

The following provide sample template to create oracle database:

CREATE DATABASE mynewdb USER SYS IDENTIFIED BY sys_password USER SYSTEM IDENTIFIED BY system_password LOGFILE GROUP 1 ('/u01/app/oracle/oradata/mynewdb/redo01.log') SIZE 100M, GROUP 2 ('/u01/app/oracle/oradata/mynewdb/redo02.log') SIZE 100M, GROUP 3 ('/u01/app/oracle/oradata/mynewdb/redo03.log') SIZE 100M

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

CHARACTER SET AL32UTF8

NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 500M REUSE SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 500M REUSE DEFAULT TABLESPACE users DATAFILE '/u01/app/oracle/oradata/mynewdb/users01_ts.dbf'

SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01_ts.dbf'

SIZE 500M REUSE

UNDO TABLESPACE undotbs DATAFILE '/u01/app/oracle/oradata/mynewdb/undo01_ts.dbf'

SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

6.2 DDL/SQL File Naming Conventions

By making standard SQL files to perform the creation of different database schema, these files can be transferred to other database servers in their native format and the database Schema can be replicated easily (like a ‘cookie cutter’) when the Dev, TST and PROD schemas reside on different Oracle database instances. The developer shall submit to the DBA or the DBA shall generate the following set of SQL files for each new schema:

The DBA shall create the following files as an extension to the developer’s package:

The above filenames will be functionally duplicated for each schema so that the same filenames shall exist for each schema and will be used as permanent objects in a delivery checklist while creating a new schema in a new environment.

The SQL statements in the above files are sometime inter-dependent and such dependencies should be considered by the DBA while using them. Public Synonyms and the object level grants should be created by the DBA at the time of installing the schema. Grants should be always given to users via the appropriate roles. If the developer expects direct object level privileges assigned to a schema owner, this should be discussed with the DBA prior to creating the schema per the developer’s specifications.

A tablespace is a logical structure where tables, indexes, and clusters are stored. Tablespace Naming Standards begin with the application identifier and ends with:

· ‘_data’, if tablespace holds data

· ‘_index’, if the tablespace holds index data Tablespace name must be unique within a database.

6.3 Database Objects Naming Convention

These database objects, which includes tables, views, sequences, indexes, names constraints, packages and package bodies (procedures and/or functions), should be prefixed by the application acronym followed by an underscore (‘_’). This naming convention makes the object unique across all applications running within the same database instance.

· The developer shall maintain a specific order of creation while creating the various database objects. The tables are first created and then the rest of the database objects are created. Thus the order is:

a. Tables

b. Primary Key Constraints

c. Foreign Key Constraints

d. Additional indexes

e. Views

f. Sequences

g. Triggers

h. Procedures

k. Functions

l. Packages and Package Bodies

m. Public Synonyms

n. Object-level Grants

· Creation of sequences does not depend on the creation of any tables. However, the triggers, procedures, functions, packages and package bodies that are based on the tables and views, may reference these sequences.

Length of Labels:

Database object names should be restricted to no more than thirty (30) characters and include entities, attributes, tables, columns, views, sequence generators, and domains. However, some ER Studio utilities may add a suffix of 4 additional characters for example “_JNL”. For this reason, the recommended length for database object names is 26 characters. The words’ ‘alias’ and ‘short name’ are used to describe a codified label of three (3) to six (6) characters. The word ‘name’ will signify a descriptive label of three (3) to twenty-six (26) characters.

Names should be meaningful, and should accurately describe the object. Applications’ data names for entity and attributes should be used, if possible. The consistent use of abbreviations and standard acronyms will assist in this endeavor. Please Appendix “A” & “B” for list of recommended acronyms.

Acronyms/Abbreviations:

For all objects except attributes, full words should be used unless an abbreviation is required for length considerations.

For Attributes, always use an abbreviation if an appropriate one exists on the ATF Data Element Abbreviation List (Appendix A). In addition, ATF bureau-wide acronyms list can be found in Appendix B of this handbook. This list is periodically updated to address new needs.

If name length considerations mandate abbreviation of words, begin the abbreviation process from the right. If the word has an approved abbreviation, substitute the abbreviation. If the word is not in approved Abbreviation List (Appendix A), please consult with Data Governance Team so that agreed upon abbreviation can be added to the approved list.

Use of Significant Words:

Use root words wherever possible. If a suffix is required to add meaning, abbreviate the suffix as follows:

· ‘age’ as ‘g’

· ‘ence’ and ‘ance’ as ‘nc’

· ‘er’ as ‘r’

· ‘ed’ as ‘d’

· ‘ified’ as ‘fd’

· ‘val’ as ‘vl’

· ‘ity’ as ‘ty’

· ‘mated’ as ‘mtd’

· ‘tic’ as ‘tc’

· ‘ing’, as ‘ng’

· ‘ant’ as ‘nt’

· ‘ive’ as ‘v’

· ‘tion’ as ‘tn’

· ‘ment’ as ‘mnt’

· ‘ed’ as ‘d’

· etc.

Words such as ‘who’, ‘what’, ‘when’, or ‘where’ should be avoided. The use of articles and prepositions (such as ‘the’ or ‘of’), adjoining words or conjunctions (such as ‘and’ or ‘or’), qualifying words such as ‘new’ or ‘old’, and numbers should be avoided but can be used as an exception basis.

Use of Special Characters:

Special characters, including brackets, quotation marks, question marks, and slashes are not permitted. Dashes are allowed only in legitimately hyphenated words. Underscores should not be used except where needed to separate words in physical implementation objects (like tables, columns, and modules). The ER Studio tool will insert underscores, when required, during transformation from logical to physical repository objects.

6.4 General Database Design Considerations:

When naming entities and tables, do not reference old system/application names unless the acronym is actually relevant and adds value to the table name.

API:

Enterprise level API should be used and/or developed (whenever relevant) to access data to/from database. The Data Governance Team is the gatekeeper of all enterprise level API accessing database. Please coordinate with Data Governance Team while using/creating these APIs. Please review DATA ACCESS SERVICE (DAS) / DATA ACCESS API (section 8) for details.

Audit Fields:

Four Audit fields should be added (create user id, create date, modified user id, modified date) for every data base tables. Please review Common Data Element (Section 7) for standard details of audit fields.

Data Dictionary:

Table level and Field Level Data dictionary should be created for all new/modified Tables. The new/modified data dictionary should be provided to Data Governance Team for approval. The dictionary should be provided in a script so that it can be saved in the database.

Data Reusability:

Data should be re-used (wherever applicable) instead of recreating new tables to save duplicate data. Please consult with Data Governance Team for further guidance.

Enterprise Reference Table:

Enterprise Reference Table should be saved/used in/from ATF schema instead of local schema. When new Enterprise Reference Table is identified, please consult with Data Governance Team.

Data Warehouse:

Industry best practice should be used while designing and developing data warehouse. Typically, data is read-only and need to be de-normalized for performance reason.

Partitions:

Partitioning decomposes very large tables and indexes into smaller and more manageable pieces. Each partition is independent object with its own name and optionally its own storage characteristics.

6.5 Tables

Table Level Comment / Data Dictionary:

All database Table should have data dictionary (description about the table). The data dictionary should be saved in database. This data dictionary/comment should describe the basic information stored in the table.

Table Creation:

If the application acronym is ‘xxxx’, then a database table (called “table_one”, for example) should be named as xxxx_table_one. This naming is independent of the name of the schema owner under whose schema the database object is created.

All the database tables should be created explicitly in the users’ DATA tablespace with explicit INITIAL, NEXT and PCTINCREASE storage parameters. The PCTINCREASE parameter shall be set to zero. The INITIAL and NEXT parameters shall be set to be equal.

Table Names:

Table names should be the plural of the Entity names, with the spaces translated to underscores. If the resulting name has more than 26 characters, see Appendix A for a list you can use to shorten the table name. Table name should be descriptive. Table name is prefixed with an application abbreviation followed by underscore (_).

· Example: XXXX_CUSTOMERS

Table Alias:

All tables must have an alias. The alias must conform to the same standard set forth for entity short names. If the table is based on an entity, then the alias must be the same as the entity short name.

Intersection Tables:

After an “intersection”, entity is mapped to a table, both table1 and table2 should be plural. Since the LCS LICENSE entity will become LCS_LICENSES and the LCS PERMIT entity will become LCS_PERMITS, the intersection table name should properly reflect the connected table names.

· Example: LCS_XREF_LICENSES_PERMITS.

Reference Tables:

A reference table is mapped from a reference entity. For reference tables used across multiple systems, the ERD entity should be prefixed with ATF REF (Subject). The Table name becomes ATF_REF_(Subject)S.

· Example: Entity ATF REF STATE (entities are singular) becomes table name ATF_ REF_STATES, which is plural.

For reference tables, which appear to be used by only one system, prefix the system acronym followed by REF and then (Subject).

· Example: Entity SOT REF TAX CODE becomes Table SOT_REF_TAX_CODES.

Cross Reference Tables:

Cross-reference tables are the tables with recursive relationships. A cross-reference table is mapped from a cross-reference entity. If the recursive relationship is to a single table, the cross-reference table will be named: XXXX_XREF_<TABLE NAME>.

· Example: LCS_XREF_LICENSES shows is a recursive table of the table LCS_LICENSES.

6.6 Columns/Fields

All Columns must have a corresponding datatype to indicate the format of the data contained in that column. In additional to datatype, other edits can be associated with columns in database to enforce defined business rules (default values, check constraints, unique constraints, referential integrity, etc.)

6.6.1 Column/Field Level Comment / Data Dictionary:

All significant database Column should have data dictionary (description about the column/data field). The dictionary should be saved in database. This data dictionary/comment should describe the basic information stored in the column/field.

6.6.2 Column Name:

A column name must be the same as the name of the attribute from which the column was mapped, with the spaces translated to underscores. The column name must not be a plural. The column name must use the approved abbreviations and class names in as listed in Appendix A and Appendix B. Column names cannot exceed 30 characters. Column names should not be prefixed with the table short name.

Some recommended practice for Column creation:

· Use descriptive names.

· Replace Camel Casing with underscores (i.e. OrderNumber to ORDER_NUMBER)

· Document the meaning of each Column with ‘COMMENT’ command.

· Group all the Key Columns in the Table or View at the front of the row.

· Make all the non-computing number Columns to VARCHAR2 type (for Oracle) or VARCHAR type (for SQL Server).

· Make all the alphanumeric Columns VARCHAR2 type (for Oracle) or VARCHAR type (for SQL Server).

· Make all the timestamp (date and time) Columns as DATE type (for Oracle) or DATETIME type (for SQL Server).

· Store all the large alphanumeric Columns at the end of the row using VARCHAR2 (for Oracle) or VARCHAR (SQL Server).

· Use the same Column name that means the same across the various Tables and Views and use a consistent column Description for them.

· Use appropriate abbreviations for the Column names. Discuss with the Data Governance Team for any new abbreviation (that is not already approved).

· Avoid using LONG, LONG RAW data types for Oracle and NCHAR, NVARCHAR, BIGINT, TEXT and NTEXT (for SQL Server)

6.6.3 Column Constraints:

All constraint names should be no more than 30 characters long.

6.6.4 Primary Key:

All primary key constraints must be started with application acronym 'XXXX_’, then followed by the table’s short name and ended with the suffix '_PK'.

· Example: For the table XXXX_CUSTOMER with the alias CUST the primary key will be: XXXX_CUST_PK

All primary keys should be created using “alter table ....” syntax and shall use indexes, created in the user’s INDEX tablespace by adding “using index tablespace ...” syntax. Defining a primary key on a table is expected for all tables. Primary key constraints can consists of one or many columns. Any primary key column is automatically set with a NOT NULL status. Avoid using system generated primary key names.

6.6.5 Foreign Key:

Foreign key names should be generated with the following algorithm: <application acronym>_<table alias of the target table>_<table alias of the originating key>_FK>. If there are multiple foreign keys referencing other tables, a number should be added to the string “FK”, such as “_FK2”. Otherwise, only use suffix “_FK”.

· Example: XXXX_CUST_CTRY_FK1 XXXX_CUST_EMP _FK2

All foreign keys should be created using “alter table ....” syntax. The syntax warrants that the table underlying the foreign key should be created first without the foreign key. Avoid using system generated default names.

6.6.6 Check Constraints:

This standard applies to table level check constraints. The naming convention is:

<application acronym>_<table alias>_<what is being checked>_CHK. Where ‘what is being checked’ is either a column name or some abbreviated text to describe the condition being validated.

· Example: The table called XXXX_Competitors (COMPET) has a check constraint on the column END_DATE to ensure it is greater than the start date. The check constraint will be named: XXXX_COMPET_ ENDDATE_CHK

6.6.7 Unique Key Constraints:

As for unique key constraints, the naming convention should be in format of:

<application acronym>_<table alias>_<UID Name>_UK. Where the UID Name is the name specified for a secondary unique identifier on the entity.

· Example: The table called XXXX_ORDERS (ORD) has two unique identifiers named ORD2 and ORD3. The unique key constraints will be named: XXXX_ORD_ORD2_UK; XXXX_ORD_ORD3_UK

6.7 Indexes

Indexes are required for all Primary, Unique and Foreign keys. In general, you should create an index on a column in any of the following situations:

· The column is queried frequently.

· A referential integrity constraint exists on the column.

· A UNIQUE key integrity constraint exists on the column.

All indexes (other than the ones created as part of Primary Key Constraints) should be created separately using “create index ...” syntax explicitly naming the user’s INDEX tablespace as their destination. All columns that participate in the foreign key constraints for the table should be indexed using “create index ...” syntax explicitly naming the user’s INDEX tablespace as their destination. Additional indexes, other than those warranted by the primary key constraints and the foreign key constraints, may be unique or non-unique.

The more indexes, the more overhead is incurred as the table is altered. When rows are inserted or deleted, all indexes on the table must be updated. When a column is updated, all indexes on the column must be updated. The developer should pay attention to the possible database performance issues in over- indexing or under-indexing a database table. It is common that a table may be under-indexed at the beginning, but additional indexes may be added later to improve data access performance.

6.8 Sequence

When creating database sequences, care should be taken to choose or not to choose the “cache” clause. This consideration is based on whether the design intention is not to lose continuity in the sequence numbers being returned from the sequences. Additionally, the developer should review the default values for the various clauses in the “Create sequence … “statement and decide on any appropriate overrides.

Such clauses are:

· increment

· start with

· cycle

· minvalue

· maxvalue

· order

If a sequence is used exclusively to populate the primary key of a table, it will be wise to name that sequence to indicate that fact.

In most cases, sequence names should be created as follows: <table name>_SEQ, where <table name> is the table for which the sequence will provide values to the primary key. In case it is over 30 characters long, use format of <application acronym>_<table alias>_SEQ

· Example: XXXX_CUSTOMERS_SEQ

6.9 Views

A View is a logical representation of a table or combination of tables. You can use view in almost the same way as tables (query, update, insert, delete). View definition must reference base tables only. Do not create nested view, i.e. view which reference other views.

All views must be named as follows: <table name>_<criteria>_V, where the table name is the name of the root (or ‘master’) table the view is based on. The criteria qualifier is optional. The maximum length for a view name will be 26.