• 2.5.13  Database Design Techniques and Deliverables
    • 2.5.13.1  Program Scope and Objectives
      • 2.5.13.1.1  Background
      • 2.5.13.1.2  Authority
      • 2.5.13.1.3  Roles and Responsibilities
      • 2.5.13.1.4  Program Management and Review
      • 2.5.13.1.5  Program Controls
      • 2.5.13.1.6  Acronyms/Terms/Definitions
      • 2.5.13.1.7  Related Resources
    • 2.5.13.2  Internal Revenue Service (IRS) Relational Database Design Guidance
    • 2.5.13.3  Data Modeling Overview
    • 2.5.13.4  Database Design Overview
      • 2.5.13.4.1  Types of Database Models
      • 2.5.13.4.2  Business Analysis Best Practices
        • 2.5.13.4.2.1  Identify Mission, Functions and Operations
        • 2.5.13.4.2.2  Identify Tasks Performed and Data Usage
        • 2.5.13.4.2.3  Identify Task/Data Relationships
        • 2.5.13.4.2.4  Develop a List of Constraints
        • 2.5.13.4.2.5  Develop a List of Potential Future Changes
      • 2.5.13.4.3  Data Modeling Design
        • 2.5.13.4.3.1  Identify Local Views of the Data
        • 2.5.13.4.3.2  Formulate Entities/Entity Modeling
        • 2.5.13.4.3.3  Specify Relationships
        • 2.5.13.4.3.4  Add Descriptive Attributes
        • 2.5.13.4.3.5  Consolidate Local Views and Design Perspectives
        • 2.5.13.4.3.6  Present Data Model
        • 2.5.13.4.3.7  Verify Data Model
    • 2.5.13.5  Physical Database Design
      • 2.5.13.5.1  Determine the User's Requirements
      • 2.5.13.5.2  Determine the Processing Environment
      • 2.5.13.5.3  Select DBMS Software
      • 2.5.13.5.4  Design the Physical Placement of Data
      • 2.5.13.5.5  Perform Sizing of Data
      • 2.5.13.5.6  Consider Security and Recovery
    • 2.5.13.6  Deliverables
      • 2.5.13.6.1  Decision Analysis and Description Forms
      • 2.5.13.6.2  Task Analysis and Description Forms
      • 2.5.13.6.3  Task/Data Element Usage Matrix
      • 2.5.13.6.4  Data Models
      • 2.5.13.6.5  Entity-Attribute Lists
      • 2.5.13.6.6  Data Definition Lists
      • 2.5.13.6.7  Physical Database Specifications Document
        • 2.5.13.6.7.1  Physical Database Names
        • 2.5.13.6.7.2  Data Structure/Sizing
        • 2.5.13.6.7.3  Data Placement
    • 2.5.13.7  Database Management System Software Supported by the IRS
      • 2.5.13.7.1  IBM Enterprise Database 2 Universal Database (DB2 UDB) Overview
        • 2.5.13.7.1.1  DB2 Physical Objects
        • 2.5.13.7.1.2  DB2 Performance Standards and Guidelines
          • 2.5.13.7.1.2.1  IRS DB2 Tables - Designing for Performance
      • 2.5.13.7.2  SQL Server Overview
        • 2.5.13.7.2.1  SQL Server and T-SQL Design Best Practices
      • 2.5.13.7.3  MySQL Overview
      • 2.5.13.7.4  Oracle Database Design Overview
        • 2.5.13.7.4.1  Oracle - Design for Performance Best Practices
        • 2.5.13.7.4.2  Relational Database Design Rules and SQL Coding Standards
      • 2.5.13.7.5  PostgreSQL Overview
    • 2.5.13.8  Big Data Models and No Structured Query Language (NoSQL) Databases Overview
      • 2.5.13.8.1  Enterprise Database Design
    • 2.5.13.9  Database Security Design
      • 2.5.13.9.1  Database Design Security Best Practices
    • 2.5.13.10  IRS Extensible Markup Language (XML) Overview
      • 2.5.13.10.1  IRS Extensible Markup Language (XML) Naming and Design Rules
    • Exhibit  2.5.13-1  Guidelines for Decision Analysis and Description Forms
    • Exhibit  2.5.13-2  Guidelines for Task Analysis and Description Forms
    • Exhibit  2.5.13-3  Sample Task/Data Element Matrix
    • Exhibit  2.5.13-4  Guidelines for Constructing Data Structure Diagrams (DSD)
    • Exhibit  2.5.13-5  Relationship between Two Entities-Classes
    • Exhibit  2.5.13-6  Explanation of Hierarchical Structure (One-to-Many) and (Many-to-Many) Relationships
    • Exhibit  2.5.13-7  Guidelines for Constructing Entity Relationship Diagrams (ERD)
    • Exhibit  2.5.13-8  Sample Data Definition List
    • Exhibit  2.5.13-9  Summary of Access Methods
    • Exhibit  2.5.13-10  Acronyms and Terms
    • Exhibit  2.5.13-11  Terms/Definitions
    • Exhibit  2.5.13-12  IRS Enterprise Life Cycle (ELC) Data Model Compliance Standards
    • Exhibit  2.5.13-13  Relational Database Design Constancy Rules - Types of Data Storing in Table
    • Exhibit  2.5.13-14  FIPS 127-2 Database Construct Sizing

Part 2. Information Technology

Chapter 5. Systems Development

Section 13. Database Design Techniques and Deliverables

2.5.13 Database Design Techniques and Deliverables

Manual Transmittal

September 30, 2020

Purpose

(1) This transmits revised IRM 2.5.13, Systems Development, Database Design Techniques and Deliverables.

Background

This IRM 2.5.13 transmits the revised standards, guidelines, and other controls for documenting database systems. This IRM describes techniques for analyzing, designing, and modeling Internal Revenue Service (IRS) databases.

Material Changes

(1) Material Changes, Changed the Policy Owner from "Terrance H. Lutes, Chief Information Technology Services" to "Nancy Seiger, Acting Chief Information Officer"

(2) 2.5.13.1, Removed Introduction and Added "Program Scope and Objectives"

(3) 2.5.13.1.1, Combined Purpose with Added "Background"

(4) 2.5.13.1.2, Combine Scope with "Program Scope and Objectives" and Added "Authority"

(5) 2.5.13.1.3, Removed "Organization and Content" and added "Roles and Responsibilities"

(6) 2.5.13.1.4, Added Program Management and Review

(7) 2.5.13.1.5, Added Program Controls

(8) 2.5.13.1.6, Added Acronyms/Terms/Definitions

(9) 2.5.13.1.7, Added Related Resources

(10) 2.5.13.2, Added Internal Revenue Service (IRS) Relational Database Design Guidance

(11) 2.5.13.3, Added Data Modeling Overview and modified grammar

(12) 2.5.13.4, Added Database Design Overview

(13) Changed "Logical Database Design" to "Database Design" , and realigned all "Physical Design" ," Logical Design" under Database design content under "Database Design"

(14) 2.5.13.4.1, Added Types of Database Models

(15) 2.5.13.4.2, Business Analysis - Changed the grammar, punctuation, and restructured the paragraphs for more concise information

(16) 2.5.13.4.2.1, Identify Mission, Functions and Operations - Changed the grammar, punctuation, and restructured the paragraphs for more concise information

(17) 2.5.13.4.2.2, Identify Tasks Performed and Data Usage - Changed the grammar, punctuation, and restructured the paragraphs for more concise information

(18) 2.5.13.4.2.3, Identify Task/Data Relationships - Changed the grammar and punctuation and restructured all information to paragraph two, and moved the table from paragraph three to two

(19) 2.5.13.4.2.4 (1), Included a paragraph for a description of constraints

(20) 2.5.13.4.2.4 (2), Added a list of IRS constraints

(21) 2.5.13.4.2.4 (4), Modified the sentence for more clarity

(22) 2.5.13.4.2.4 (9), Modified the sentence for more clarity by removing repetitive words

(23) 2.5.13.4.2.5, Modified punctuation

(24) 2.5.13.4.3, Changed the subsection title from "Data Modeling" to "Data Modeling Design" because this subsection speaks about database design

(25) 2.5.13.4.3 (5) (6), Rewrote the paragraphs to be more concise, and provide better readability

(26) 2.5.13.4.3.1, Restructured all paragraphs for information to be more concise, and provide better readability

(27) 2.5.13.4.3.2, Added Figure 2.5.13-4 with table, and modified paragraphs for better readability

(28) 2.5.13.4.3.3 (7), Modified the grammar, punctuation, and restructured the paragraph sentences into bullets for more concise information

(29) 2.5.13.4.3.4 (1), Restructured the first paragraph into subparagraph alphalist

(30) 2.5.13.4.3.5 (1- 6), Restructured all paragraphs for information to be more concise, and provide better readability

(31) 2.5.13.4.3.6, Modified grammar and punctuation

(32) 2.5.13.4.3.7 (8), Modified the verbiage for more clarity

(33) 2.5.13.5.4 (1), Modified the verbiage in the first sentence for more clarity

(34) 2.5.13.5.5, Included Exhibit 2.5.13-14

(35) 2.5.13.6.3, Changed verbiage for more clarity

(36) 2.5.13.6.7.1 (5), Added a paragraph to include reference for IRM 2.152.3

(37) 2.5.13.6.7.2, Corrected grammar

(38) 2.5.13.7, Added Database Management System Software Supported by the IRS

(39) 2.5.13.7.1, Added Enterprise Database 2 Universal Database (DB2 UDB) Overview

(40) 2.5.16.7.1.1(1), Added paragraph and table for DB2 Physical Objects

(41) 2.5.13.7.2, Added SQL Server Overview

(42) 2.5.13.7.2.1, Added SQL Server and T-SQL Design Best Practices

(43) 2.5.13.7.3, Added MySQL Database Overview

(44) 2.5.13.7.4, Added Oracle Database Overview

(45) 2.5.13.7.4.1, Oracle - Design for Performance Best Practices

(46) 2.5.13.7.4.2, Relational Database Design Rules and SQL Coding Standards

(47) 2.5.13.7.5, Added PostgreSQL Overview

(48) 2.5.13.8, Added Big Data Models and No Structured Query Language (NoSQL) Databases Overview

(49) 2.5.13.8.1, Added Enterprise Database Design

(50) 2.5.13.9, Added Database Security Design

(51) 2.5.13.9.1, Added Database Design Security Best Practices

(52) 2.5.13.10, Added IRS Extensible Markup Language (XML) Overview

(53) 2.5.13.10.1, Added IRS Extensible Markup Language (XML) Naming and Design Rules

(54) Figure 2.5.13-1, Added a Descriptor

(55) Figure 2.5.13-2, Added a Descriptor

(56) Figure 2.5.13-3, Added a Descriptor

(57) Figure 2.5.13-4, Added a Descriptor

(58) Figure 2.5.13-5, Added a Descriptor

(59) Figure 2.5.13-6, Added a Descriptor

(60) Figure 2.5.13-7, Added a Descriptor

(61) Figure 2.5.13-8, Added a Descriptor

(62) Figure 2.5.13-9, Added a Descriptor

(63) Figure 2.5.13-10, Added a Descriptor

(64) Figure 2.5.13-11, Added a Descriptor

(65) Figure 2.5.13-12, Added a table for Logical Design Compared with Physical Design

(66) Figure 2.5.13-13, Added a table for Example of User Requirements and Consequences

(67) Figure 2.5.13-14, Added a table for DB2 Unique Physical Objects

(68) Figure 2.5.13-15, Added a table for IRS DB2 Standards

(69) Figure 2.5.13-16, Added a table for IRS DB2 Free Space Standards

(70) Figure 2.5.13-17, Added a Descriptor

(71) Added Exhibit 2.5.13-5, Relationship between Two Entities-Classes

(72) Added Exhibit 2.5.13-6, Explanation of Hierarchical Structure (One-to-Many) and (Many-to-Many) Relationship

(73) Added Exhibit 2.5.13-12, IRS Enterprise Life Cycle (ELC) Data Model Compliance Standards

(74) Added Exhibit 2.5.13-13, Relational Database Design Constancy Rules - Types of Data Storing in Table

(75) Added Exhibit 2.5.13-14, FIPS 127-2 Database Construct Sizing

Effect on Other Documents

This IRM supersedes IRM 2.5.13 dated 04-01-2005, and supplements IRM 2.5.1 Information Technology, System Development .

Audience

The audience intended for this transmittal is personnel responsible for engineering, developing, or maintaining Agency software systems identified in the Enterprise Architecture. This engineering, development, and maintenance include work performed by government employees as well as contractors, and the mandates in this manual apply to all IRS production, development, and test database systems.

Effective Date

(09-30-2020)

Nancy Sieger
Acting Chief Information Officer

2.5.13.1 (09-30-2020)

Program Scope and Objectives

  1. Scope : This IRM establishes standards, guidelines, and other controls for documenting database systems developed for the Internal Revenue Service. This manual describes techniques for analyzing, designing, and modeling databases. This development includes that work performed by government employees as well as contractors.

  2. Objectives : The main objectives of designing databases for the IRS is to produce logical and physical design models of the proposed new database system. The database design is the organization of data according to a database model and techniques pertaining to the following:

    1. Analysis

    2. Design

    3. Description

    4. Specification of data designed for automated business data processing using models to enhance communication between developers and customers

  3. Database (DB) Architect : The DB Architect is associated with the System Development Life Cycle (SDLC) process, and must define detailed database designs by determining tables, indexes views, constraints, triggers, stored procedures tablespaces or storage parameters, what data, and how the data elements interrelate. However, the Architect is not involved in the daily operations of the system once it is deployed.

  4. Purpose : To establish quality database designs with the outcome resulting in good consistency of data, elimination of data redundancy, efficient execution of queries, and produce robust, high performance applications.

  5. Audience : The target audience for this manual is: government employee and contractor data architects/analyst, database designer/administrators, developers, Information Technology engineers, and IT managers.

  6. Policy Owner : The Application Development (AD) Associate, Chief Information Officer (ACIO), establishes all Information Technology internal controls for this IRM.

  7. Program Owner : The AD, Technical Integration Office (TIO), Application Standards and Quality (ASQ) is the internal organization that is responsible for the administration, procedures, and updates related to this program. The TIO, Application Standards and Quality branch works closely with key stakeholders from Enterprise Operations (EOps), Data Management Services & Support (DMSS) when updating IRS policies, and industry standards related to all database initiatives.

  8. Primary Stakeholders :

    • Application Development (AD)

    • Enterprise Operations (EOps) Data Management Services & Support (DMSS)

    • Information Technology, Cyber Security

  9. Program Goals : Performing appropriate database design procedures are critical to meet the IRS databases' target-state for scalability, security, performance, and reliability requirements.

2.5.13.1.1 (09-30-2020)

Background

  1. During 1960, Charles W. Bachman designed the first Integrated Database System, and launched the Database Task Group. This Task Group was responsible for the design and standardization of a language called Common Business Oriented Language (COBOL) which was presented as a standard in 1971, and is also known as the CODASYL approach. During 1973, Michael Stonebraker and Eugene Wong from University of Berkeley California researched relational database systems naming the project INGRES (Interactive Graphics and Retrieval System), and successfully proved a relational model is efficient and functional.

  2. INGRES worked with a query language known as QUEL; however, in 1974 IBM developed SQL which was more advanced. SQL evolved with ANSI and OSI standards in 1986 and 1987 and replaced QUEL as the more functional query language.

2.5.13.1.2 (09-30-2020)

Authority

  1. The Clinger-Cohen Act of 1996

  2. 21st Century Integrated Digital Experience Act (IDEA), December 2018

  3. Federal Information Security Management Act of 2014, FISMA 2014

  4. Government Accountability Office (GAO)

  5. Government Performance Results Act (GPRA)

  6. Administrator of the Government Service Administration (GSA)

  7. Office of Management and Budget

  8. Presidential American Technology Council, 2017

  9. Treasury Inspector General Tax Administration (TIGTA)

  10. International Standard ISO -11179 (ISO/IEC 11179)

2.5.13.1.3 (09-30-2020)

Roles and Responsibilities

  1. Information Technology (IT), Cybersecurity : Cybersecurity manages the IRS IT Security program in accordance with the Federal Information Security Management Act with the goal of delivering effective and professional customer service to business units, and support functions within the IRS. These procedures are done as the following:

    1. Provide valid risk mitigated solutions to security inquisitions.

    2. Respond to incidents quickly and effectively in order to eliminate risks/threats.

    3. Ensure all IT security policies and procedures are actively developed, and updated.

    4. Provide security advice to IRS constituents, and proactively monitor IRS robust security program for any required modifications or enhancements.

  2. Director, Enterprise Operations (EOps), Data Management Services & Support (DMSS) : Directs and oversees reliable database and storage operations by pioneering improvements in data services. Other responsibilities are as follows:

    • Plan, build, operate, and maintain the IRS's data management technologies/processes

    • Ensure level 2 and 3 support is administered to address customer database requirements

  3. Applications Development (AD) : AD is responsible for building, testing, delivering, and maintaining integrated information applications systems, e.g., software solutions, to support modernized systems and production environment to achieve the mission and objectives of the Service. Additional, AD is responsible for the following:
    • AD works in partnership with customers to improve the quality of and deliver changes to IRS information systems products and services
    • Establishes and maintains rigorous contract and fiscal management, oversight, quality assurance, and program risk management processes to ensure that strategic plans and priorities are being met
    • Maintains the effectiveness and enhance the integration of IRS installed base production systems and infrastructure while modernizing core business systems and infrastructure
    • Provides quality assessment/assurance of deliverables and processes. Application Development's (AD) chain of command and responsibilities include:

    1. Commissioner : Oversees and provides overall strategic direction for the IRS. The Commissioner's and Deputy Commissioner's focus is for the IRS's services programs, enforcement, operations support, and organizations. Additionally, the Commissioner's vision is to enhance services to the nation's taxpayers, balancing appropriate enforcement of the nation's tax laws while respecting taxpayers' rights.

    2. Deputy Commissioner, Operation Support (DCOS) : Oversees the operations of Agency-Wide Shared Services: Chief Financial Officer, Human Capital Office, Information Technology, Planning Programming and Audit Oversight and Privacy, and Governmental Liaison and Disclosure.

    3. Chief Information Officer (CIO) : The CIO leads Information Technology, and advises the Commissioner on Information Technology matters, manages all IRS IT resources, and is responsible for delivering and maintaining modernized information systems throughout the IRS. Assisting the Chief Technology Officer (CTO) is the Deputy Chief Information Officer for Operations.

    4. Application Development (AD) Associate Chief Information Officer (ACIO) : The AD ACIO reports directly to the CIO; oversees and ensures the quality of: building, unit testing, delivering, and maintaining integrated enterprise-wide applications systems to support modernized and legacy systems in the production environment to achieve the mission of the Service. AD works in partnership with customers to improve the quality, and deliver changes to IRS information systems products and services.

    5. Deputy AD Associate CIO (ACIO) : The Deputy AD ACIO reports directly to the AD ACIO, and is responsible for:
      • Leading strategic priorities to enable the AD Vision, IT Technology Roadmap and the IRS future state
      • Executive planning and management of the development organization which ensures all filing season programs are developed, tested, and delivered on-time and within budget

  4. AD has the following Domains:
    • Compliance Domain
    • Corporate Data Domain
    • Customer Service Domain
    • Data Delivery Service (DDS) Domain
    • Delivery Management; Quality Assurance (DMQA) Domain
    • Identity & Access Management (IAM) Organization Domain
    • Internal Management Domain
    • Submission Processing Domain
    • Technical Integration Organization (TIO) Domain

  5. Director, Compliance : Provides executive direction for a wide suite of Compliance focused applications, and oversee the IT Software Development organization to ensure the quality of production ready applications. Directs and oversees an unified cross-divisional approach to compliance strategies needing collaboration pertaining to the following:

    1. Abusive tax avoidance transactions needing a coordinated response

    2. Cross-divisional technical issues

    3. Emerging issues

    4. Service-wide operational procedures

  6. Director, AD Corporate Data : Directs and oversees the provisioning of authoritative databases, refund identification, notice generation, and reporting.

  7. Director, Customer Service : Directs and oversees Customer Service Support for the IT Enterprise Service Desk ensuring quality employee to customer relationship.

  8. Director, Data Delivery Services : Directs, oversees, and ensures the quality of data with a repeatable process in a scalable environment. The enterprise data strategy is to transform DDS into a data centric organization dedicated to deliver Data as a Service (DaaS) through:

    • Innovation - new methods, discoveries

    • Motivate - incent and enable individuals

    • Renovation - streamline or automate

  9. Director, Delivery Management; Quality Assurance (DMQA) : Executes the mission of DMQA by ensuring AD has a coordinated, cross-domain, and cross-organizational approach to delivering AD systems and software applications. Additionally, the DMQA Director reports to the AD ACIO, and chairs the AD Risk Review Board.

  10. Director, Identity & Access Management (IAM) : Provides oversight and direction for continual secure online interaction by verifying and establishing an individual's identity before providing access to taxpayer information "identity proofing", while staying compliant within federal security requirements.

  11. Director, Internal Management : Provides oversight for the builds, tests, deliveries, refund identification, notice generation, and reporting.

  12. Director, Submission Processing : Provides oversight to an organization of over 17,000 employees comprised of: a headquarters staff responsible for developing program policies and procedures, and five Wage& Investment processing centers. Additionally, the Director, Submission Processing is responsible for processing over 500 million individual and business tax returns through both electronic and paper methods.

  13. Director, Technical Integration : Provides strategic technical organization oversight ensuring applicable guidance, collaboration, consolidation of technical integration issues and quality assurance for the Applications Development portfolio.

2.5.13.1.4 (09-30-2020)

Program Management and Review

  1. Program Reports : The IRS Enterprise Architecture Office (EAO) work in alliance with all IRS organizations to provide guidance for Information Technology application design patterns for standard solutions. This EAO information assist IT managers to plan, and architect all IT solutions, and manage investments in business and technology programs. EA facilitates the EA Review cycle and ELC Compliance Review process to ensure all reports/artifacts relating to the Enterprise Life Cycle are in compliance.

  2. Program Effectiveness : The IRS Enterprise Data Management Office (EDMO) implements, updates, and distributes data standards and guidelines to IRS executive leadership.

2.5.13.1.5 (09-30-2020)

Program Controls

  1. The IRS Enterprise Data Management Office (EDMO) must review all conceptual, logical and physical data models using the standards-based checklists which mandates the level of detail necessary for all data model.

2.5.13.1.6 (09-30-2020)

Acronyms/Terms/Definitions

  1. For Acronyms and Terms, see Exhibit 2.5.13-10

  2. For Terms and Definitions, see Exhibit 2.5.13-11

2.5.13.1.7 (09-30-2020)

Related Resources

  1. The resources below provide information for system development documentation standards from National Institute of Standards and Technology (NIST), IRS IRMs, and the IRS organizations' SharePoint repository sites/Uniform Resource Locators (URLs).

    • IRM 2.5.1 System Development, is the overarching IRM for the System Development program within the IRS

    • IRM 10.8.21 IRS Information Technology (IT) Security Database Security Policy

    • IRM 10.5.1 Privacy Compliance and Assurance (PCA) Program

    • IRM 10.8.1 Information Technology, Policy and Guidance

    • IRM 10.8.6 Application Security and Development

    • NIST SP 800-53, Security and Privacy Controls for Federal Information Systems and Organization

    • NIST SP 800-122, Guide to Protecting the Confidentiality of Personally Identifiable Information (PII)

    • NIST SP 800-64, Security Considerations for System Development Life Cycle: Information Security, Revision 2 October 2008

    • NIST SP 1500, Big Data Interoperability Framework: Volume 4, Security and Privacy

    • NIST Big Data Program: NIST Big Data Public Working Group, https://bigdata.nist.gov/home.php

    • IRS Enterprise Data Standards and Guidelines (EDSG), see IRM 2.5.13.2

    • Online Oracle Database Development Guide see, https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/toc.htm

    • DB2 see, https://www.tutorialspoint.com/db2/db2_introduction.htm

    • DB2 Design, see https://www.ibm.com/developerworks/data/library/techarticle/dm-0408whitlark/index.html

    • Khang, Alex. Relational Database Professional Handbook: Design Rules and SQL Coding Conventions Guidelines, ISBN-13 979-8612037262. February 10, 2020

    • SQL Server, see https://www.sqlservertutorial.net/getting-started/what-is-sql-server/

    • PostgreSQL see, https://www.postgresql.org/docs/9.5/tutorial-arch.html

2.5.13.2 (09-30-2020)

Internal Revenue Service (IRS) Relational Database Design Guidance

  1. Enterprise Data Standards and Guidelines (EDSG) provides standards and rules for the development and modification of the names, definitions, and other metadata for classes, attributes, and data models. pertain to the Modernized Environment (ME) data elements located in IRS' modernization system data dictionaries, data models, and Current Production Environment (CPE) data elements.

  2. ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡≡ ≡

  3. See Figure 2.5.13-1 for illustration of the Design Rules of Relational Database.

2.5.13.3 (09-30-2020)

Data Modeling Overview

  1. Data modeling is the process of creating a data model for the data to be stored in a database. This data model emphasizes on what data is needed, and how it must be organized instead of what operations need to be completed on the data. It is also a conceptual representation of data objects, the associations between different data objects and the rules. The primary goal of using data models are the following:

    1. Ensure that all data objects required by the database are correctly represented

    2. Data model structures must define the relational tables, primary and foreign keys, and stored procedures

    3. Data models must depict the base data which is used by database developers to create a physical database

    4. Data models must be able to identify missing, and redundant data

  2. Quality and easily understood data models allow cheaper/faster upgrades, and maintenance for IT infrastructures.

  3. Data models and supporting descriptions are the tools used in database design. These tools become the deliverables that result from applying database design. There are two primary objectives for developing of these deliverables. The first objective is to produce documentation that describes a customer's perspective of data, and the relationships among this data. The second objective is to produce documentation that describes the customer organization's environment, operations, and data needs. In accomplishing these objectives, the following deliverables will result:

    • Decision Analysis and Description Forms

    • Task Analysis and Description Forms

    • Task/Data Element Usage Matrix

    • Data Models

    • Entity-Attribute Lists

    • Data Definition Lists

    • Physical Database Specifications Document

  4. The Data model classes and attributes must be displayed at an adequate level of detail to ensure they are understood. The tables below display the Engineering Data Dictionary (EDD) mandatory metadata:

    1. Each attribute name must end with an approved generic element/class word.

    2. Any existing attribute that does not have one of the approved generic elements/class words at the end of its name must be renamed and redefined, see table IRM # 2.5.13.3 #.

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    "≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡"
    ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

  5. Consider a database approach if one or more of the following conditions exist in the user environment:

    • A multiple number of applications are to be supported by the system

    • A multiple number of processes or activities use a multiple number of data sources

    • A multiple number of data sources are used in the reports produced

    • The data, from the data definitions, are known to be in existing database(s)

    • The development effort is to enhance the capabilities of an existing database

  6. A thorough analysis of the current environment must be concluded to clarify any alternatives that may be preferable to DBMS implementation.

  7. As soon as database development is approved by leadership, then undertake the activities of logical database analysis and design. After the logical schema and sub-schemas are completed they are translated into their physical counterparts. Then the physical sub-schemas are supplied as part of the data specifications for program design.

  8. The logical design encompasses a DBMS-independent view of data, and that physical design results in a specification for the database structure, as it is to be physically stored.

  9. Implementation Design: Is the design step between the logical and physical design that produces a schema, and processed a DBMS.

  10. Do not limit database development considerations to providing random access, or ad hoc query capabilities for the system.

2.5.13.4 (09-30-2020)

Database Design Overview

  1. A database design is the organization of data according to the database model. The designer determines what data must be stored, and how the data elements interrelate. The Database Life Cycle (DBLC) defines the five stages for creating a database as the following:

    1. Requirements analysis

    2. Logical Design

    3. Physical Design

    4. Implementation

    5. Monitoring

    6. Modifications/Redesign

    7. Maintenance

  2. Logical database design is the process of determining how to arrange the attributes of the entities in a business environment into database structures such as tables of a relational database.

  3. To develop a logical database, analyze the business needs of the organization that the database would support, how the operations relate to each other, and what data is required in business operations. After this analysis, model the data.

  4. Modeling involves studying data usage, and grouping data elements into logical units so that a task supported by one or more organizational units is independent of support provided for other tasks. Exhibit 2.5.13-9 provides the terms, and descriptions for logical database design.

  5. Providing each task with its own data groups allow changes in data requirements of one task to be minimally impacted on data provided for another task. When data is managed as a synthesis, data redundancy is minimized, and data consistency among tasks and activities is improved. Figure 2.5.13-1 graphically expresses this point.

  6. The IRS Information Technology, Enterprise Operations, Enterprise Data Management Organization (EDMO) establishes and disseminates standards for conceptual, logical, and physical data modeling.

  7. Logical database design comprises two methods to derive a logical database design. The first method is used to analyze the business performed by an organization. Following this analysis, the second method is used to model the data that supports the business. These methods are:

    1. Business Analysis

    2. Data Modeling

  8. For IRS Enterprise Life Cycle (ELC) Data Model Compliance Standards, see Exhibit 2.5.13-12

2.5.13.4.1 (09-30-2020)

Types of Database Models

  1. There are five standard types of database models:

    • Hierarchical : Data is organized into a tree-line-structure, where the hierarchy starts from the Root data, and expands like a tree, adding parent to child nodes.

    • Network : This database model is used to map many -to-many data relationships, and becomes more connected as more relationships are created. This is an extension of the Hierarchical model, and was the most popular before the Relational Model was implemented

    • Relational : Data is organized into two-dimensional tables, and the connection is maintained through a common field. The structure of data in the relational model is tables. The tables are also known as relations in Relational Model.

    • Entity-Relationship : Entity-Relationships are created by dividing the object into entities, and its features into attributes. E-R Models are sketched to represent the relationships as image forms for easier comprehension, and are used during design.

    • Object-Oriented Database (OODB) : A combination of an Object-Oriented database model and a Relational database model that supports objects, classes, inheritance etc. The goal of this model is to close the gap between relational databases, and the Object-Oriented practices used in many programming languages, e.g., C++, C#, and Java. Databases that represent data in the form of objects and classes. Object-Oriented databases have the same principles of Object-Oriented Programming (OOP) which is the combination model features of (concurrency, transaction, and recovery).

    • Object-Oriented Database Model (OODBM) : Similar principles to an Object-Oriented programming language. An Object-Oriented database management system is a hybrid application that uses a mixture of Object-Oriented and Relational Database platform to process data. See Figure 2.5.13-3

      Figure 2.5.13-3

      Object-Oriented Database Model (OODBM)

      Example of OODBM
      Object-Oriented Programming + Relational Database Features = Object Oriented Database Model

2.5.13.4.2 (09-30-2020)

Business Analysis Best Practices

  1. Business Analysis - Business analysis enables an enterprise to articulate the needs, justification for change, and to design and describe solutions that deliver value. Business Analysis is also a method for analyzing, and understanding a customer's enterprise business needs. The Business Analyst recommends relevant solutions by defining, documenting, and managing requirements while working with stakeholders to establish the requirements. In applying this method, the objectives are to:

    • Must have obtain a clear understanding of an organization's objectives, and how it performs its mission

    • Identify specific requirements that must be reflected in the database that involves decision analysis and task analysis

    • Allow the analyst to focus on the information requirements, and how they are related after identifying each decision and task

    • Gain an understanding of the requirements, not as a critique of the operations

    • Identify stated data needs, and various indicators such as: organizational structure, environmental policies, interaction between functions, etc., which may indicate additional data requirements

    • Define the scope of the database, and the environment that the database will support

    • Identify any constraints for the database operation

    • Define the Acceptance Criteria for projects and user stories

    • Produce documentation that presents a valid picture of the organization's operation

  2. Analyze Documentation - Prior to applying this method, acquire and study the following documentation:

    • A high-level data flow diagram (DFD) depicting the major applications to be supported, and the major data sources and outputs of these applications.

    • Detailed DFDs depict the functions, tasks performed, and a list of the documents, files, and informal references (e.g., memos, verbal communications, etc.) used to perform each function.

  3. Business Analysis eight (8) steps :

    1. Create a Business Case with the following steps :
      • Identify and validate the mission, core values, functions, and operations,
      • Identify the scope and primary business/project objectives
      • Use business analysis techniques such as Strength, Weakness, Opportunities, and Threats (SWOT) analysis
      • Identify tasks performed and data usage in the current and target environment
      • Identify all task/data relationships.
      • Develop a thorough list of constraints.
      • Create a cost-benefit analysis and/or Activity-Based Costing

    2. Create a Stakeholder register with the following steps :
      • Obtain valid information about the stakeholders
      • Identify who makes project decisions, and signs-off project documentation
      • Identify who are the product owners for the Enterprise Life Cycle phases.
      • Identify where the stakeholder works, and who they report to in the agency
      • Identify the stakeholder(s)' role in the project, and their level of influence

    3. Create a Business Analysis Plan with the following steps :
      • Define the resources and tasks associated with the requirements
      • Use business analysis tools such as a RACI matrix, this is a table that displays who are the responsible, accountable, consulted, and informed stakeholders

    4. Elaborate on business objectives that were defined in the business case, and create the specific requirements:
      • Perform an assessment of the business objectives, and current business process creating the as-is (current state), and to-be (target state)
      • Focus information on the elements: infrastructure (technology), process, business related data, impacted business domain(s), goals, and measures

    5. Identify options that are the best solutions for project objectives :
      • Assist the development team with providing more information about similar situations in the agency with other developers, and their practices, as documented in i.e., (lessons learned or playbooks)
      • Use business analysis tools, e.g., Work Breakdown Structure (WBS), Technology Capability assessment, and business or IT staff interviews
      • Use other tools such as Cause and Effect analysis

    6. ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
      ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
      ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
      ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
      ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

    7. Expand on product requirements :
      • Once the high-level scope of the product has been defined, the person performing the Business Analyst functions must focus on the detailed product requirements before actual development begins
      • Use business analysis techniques such as use cases, story boards, prototypes, and/or wireframes
      • Document both functional and nonfunctional requirements

    8. Support the Project Manager and/or Scrum Master and development team :
      • Support the programmers by constantly reviewing their deliverables to ensure they are in-line with objectives in the business case
      • Facilitate interviews with the Project Manager or Scrum Master, and based on their feedback and project team, facilitate and/or update requirements documentation
      • As needed, engage with quality control analysts (DMQA) to ensure that requirements are properly tested

  4. Gap Analysis - Perform this analysis by comparing the identified current state with the desired outcomes to assess if there are any business process gaps that prevents the customer and stakeholders from achieving their business needs.

  5. ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

2.5.13.4.2.1 (09-30-2020)

Identify Mission, Functions and Operations

  1. Identify the mission, functions and operations of the organizational element that the database is to support. The purpose of this step is to define the scope of the potential database's current and future needs, and develop a reference point for further analysis. This step covers all relevant functional areas and be developed separately from any single application design effort.

  2. In examining an organizational element, which may range in size from a branch to an entire organization, the following may provide sources of information:

    • Business Impact Analysis (BIA) :This is the organization's BIA best source of information. This plan may vary widely in content, but must articulate the organization's current and future state, a discussion of each system's scope, and definitions of the dependencies between major systems (both automated and manual) and groups of data. With this information, it is possible to determine which functional areas must be included within the scope of the new design.

    • Scope Responsibility : If the BIA is not available, or does exist, but does not contain diagrams of systems and data dependencies, use it to determine the scope. In this case, persons within the relevant functional areas must be interviewed to determine how they relate to the rest of the organization.

    • Interviews : Additional interviews can be conducted for newly identified areas to ascertain the extent to which they share data with the application(s) under design.

    • Requests for Information Services (RIS) : Potential sources of information for the , mission, functional statements, internal revenue manuals, and senior staff interviews.

    • Future State : Future changes to the organization must be considered when defining the scope of the design effort, i.e., any major changes in operating policy, regulations, etc. Each potential change must be identified, and further defined to determine whether it could change the definition, usage, or relationships of the data. Where a change could affect the database in the future, the design scope must be expanded to consider the effects of this change.

  3. Construct a high-level DFD to graphically depict the boundaries after determining the scope of the database.

2.5.13.4.2.2 (09-30-2020)

Identify Tasks Performed and Data Usage

  1. Identify the tasks performed in each of the functions and operations. The purpose is to identify tasks performed in each function of the organizational element that the database would support and to identify the data usage or "data needs" of these tasks. The functions and their related tasks can be divided into two categories: operational and control/planning.

  2. Decompose each function into the lowest levels of work that require, on a repetitive basis, unique sets of data. Work at this level is considered a "task", a unique unit of work consisting of a set of steps performed in sequence. All these steps are directed toward a common goal and use and/or create a common set of data.

  3. Once a task has been defined, decompose it into subtasks. This decomposition must occur if one or more of the following conditions exist:

    • Separation-Of-Duty : This is more than one person is needed to carry out the task, and each of them is required to have a different skill and/or carries out his/her part independently.

    • Levels of Authorization : The following are different levels of authorization:

    • Different people authorize different parts of the task

    • Different frequencies or durations apply to different parts of the task

    • Input documents are not used uniformly within the task

    • Totally different documents are used for different parts of the task

    • Many different operations are carried out within the task

    • Different primitive operations which each have separate input/output requirements

  4. When a sub-task has been defined, ensure it is limited to that task, and does not span two or more tasks, or it cannot be considered a subtask.

  5. Collect all information in a precise manner using interviews and documentation techniques. This approach is important when identifying operational functions because they provide the basic input to the database design process. These functions, and their associated tasks must be identified first. Therefore, begin by identifying the organizational areas within the scope of the design effort which perform the functions essential to conducting business. Once these functional areas have been determined, the persons to be interviewed can be specified. The recommended steps are as follows:

    1. Determine the key individuals within these areas, and send out questionnaires requesting: job titles of persons within their areas of responsibility; functions performed in each job; and a brief statement of the objective(s) of each job.

    2. Develop a document showing job title, functions performed, and the objectives of these functions after receiving the results of the questionnaire.

    3. Review and classify each job as either operational or control and planning.

    4. Contact the supervisor of each job which is identified as "operational" and ask to select one, preferably two, persons performing that job who can be interviewed.

    5. Conduct the operational interviews. Keep the following three objectives in mind: identify each operational function; identify the data associated with each of these functions; and identify the implicit and explicit rules determining when and how each function occurs.

  6. When conducting operational interviews, accomplish the following steps during the interviews:

    1. Begin by having each interviewee describe, in detail, the functions and tasks that are performed on a daily or potentially daily basis. Document these major actions, decisions, and interfaces on task analysis and decision analysis forms. See Exhibits 2.5.13-1 and 2.5.13-2. These actions, decisions, and interfaces must also be reflected on a detailed data flow diagram. This documentation can subsequently be used to verify that all operational functions and their sequence are correct. Repeat this same procedure for functions that occur weekly, monthly, quarterly, and annually.

    2. As the functions, tasks, and other activities are defined, determine the documents, files and informal references (memos, verbal communications, etc.) used to perform them and indicate these in a separate numbered list. A task/document usage matrix may also be used specifying a task's inputs and outputs in terms of documents.

    3. Once the person interviewed agrees to the contents of the documentation, discuss more specifically each action, decision, and interface point to determine what specific documents or references are required. Then request a copy of each document that has been discussed.

    4. Finally, identify the data elements used or created on each document, and compile a list of these elements including their definitions and lengths. See Exhibit 2.5.13-6 any data elements that are not included in the dictionary must be entered.

  7. The second type of information required for conceptual database development involves the organization's control and planning functions and their related data needs. An in-depth investigation of the organization's explicit and implicit operating policies is necessary. Such information can be obtained through interviews with management. Since the nature of the information collected will vary according to the organization and persons involved, there is no rigid format in which the interview must be documented. However, in order to minimize the possibility of losing or missing information, it is recommended that there be two interviewers who could alternate posing questions and taking notes.

  8. Conduct interviews for control and planning functions with persons whose responsibilities include defining the goals and objectives of the organization, formulating strategies to achieve these goals, and managing plans to implement these strategies; and with those persons directly responsible for the performance of one or more operating areas. The objective of these interviews is to gain, where appropriate, an overall understanding of:

    • The basic components of the organization, and how they interact with one another

    • The external environment that affects the organization directly or indirectly (i.e., Congressional directives, Treasury policies, etc.)

    • Explicit or implicit operating policies that determine how the mission is performed; some of these may be identified when discussing the internal and external environment

    • Information used currently, or required to plan organizational activities, and measure and control performance

    • Obtain examples if available of any action items

    • Changes that are forecast that may affect the organization

  9. The following are steps for conducting control and planning interviews:

    1. Present the designer's perception of functions and operations and seek confirmation and clarification, i.e., clarify which are main functions, support functions, and sub-functions or tasks.

    2. Ask what additional functions, if any, are performed.

    3. Ask what monitoring functions are performed and what critical indicators are used to trigger intervention.

    4. Ask what planning functions are performed and what data is used for planning purposes.

    5. Express appreciation by thanking the person interviewed for his/her time.

    6. If any new data elements are defined during the interviews, ensure they are incorporated in the Enterprise Data Dictionary (EDD) so that they may be properly cross-referenced.

2.5.13.4.2.3 (09-30-2020)

Identify Task/Data Relationships

  1. The process of defining task/data relationships begins with analyzing the documentation developed during the interviews. A task/data relationship is defined as the unique relationship created between data items when they are used to perform a specific task. The process includes the following:

    1. Collect information about data usage, and identify task/data relationships.

    2. Identify functions and tasks as either operational, control, and/or planning, and their data usage, for the task/data relationships. It is critical that these relationships be carefully and thoughtfully defined.

  2. Identify a series of unique tasks, and the following rules must be applied:

    1. A task must be performed within one functional area.

    2. Each task must consist of a set of serially performed steps (or serially positioned symbols on a DFD).

    3. If a decision point occurs, and one path of the decision involves a new action in effect, then the current task ends and a new one begins.

    4. Each step within a single task must be performed within a period as stated during interviews with customers. If a significant amount of time can elapse between two steps, more than one task must be defined.

    5. Each step within the task must use the same set of data. However, if new data is created in one step of the task and used in the next step, they may be considered as the same set of data.

    6. After all the data flows, and other documentation have been analyzed and assigned to tasks, compare the tasks for each duplicate interview to determine if the same ones were defined - This is assuming that two persons with the same job title were interviewed in each relevant area. When conflicts are found, compare the two sets of documentation to determine if one is more detailed

    7. If the DFDs, etc., appear to be the same and differ only on levels of detail, choose the one that best defines a complete unit of work.

    8. If real functional differences are found, review the documents (and notes) associated with each. Sometimes people with similar titles perform different functions due to their seniority, or competence. When major differences are found, separate any unique tasks and add them to the list.

    9. If differences are found and it is difficult to determine why they exist, request that the appropriate supervisor review the task definitions developed during the interviews. (However, do not include any portions of the interviews that are confidential).

    10. Once any conflicting definitions have been resolved, task/data relationships specifically documented. Because it is likely that redundant tasks have been defined, arrange the documentation already produced by department or area. This method increases the likelihood that redundant tasks will be identified. It is suggested that the documentation of task/data element relationships begin with a table such as the one shown in Figure 2.5.13-2. The documentation must:
      • Numerically identify each task
      • Briefly define each task by a verb-object type command (e.g., fill out error report, request alternate items, etc.)
      • Classify tasks as operational or control/planning Identify the frequency and average volume for each task
      • Relate each task to a specific functional area
      • Construct a task/data element matrix to specify each task's inputs and outputs in terms of data elements

    Figure 2.5.13-4

    Task/Data Element Relationships

    Task # Task Definition Type Frequency Average Volume Department Data Elements
    1 Examine order request Operational Daily 500 Order Entry 410, 200, 201 - 225
    ... ... ... ... ... ... ...
    ... ... ... ... ... ... ...

2.5.13.4.2.4 (09-30-2020)

Develop a List of Constraints

  1. Constraints are a very important feature in a relational model based on attributes or tables. Constraints are the rules associated with a database schema that can be implicit (implied), or explicit (fully understood). The purpose of developing a list of all implicit and explicit constraints is to provide information for the physical database designer to use in determining operational considerations, e.g., access restrictions, interfaces to other packages, and recovery capabilities. Constraints provide a mechanism for ensuring the data conforms to guidelines specified by the Database Administrator (DBA), and force the Database Management System DBMS to check that data meets the semantics.

  2. Develop a list of all implicit and explicit constraints such as:

    1. Security

    2. Data Integrity

    3. Domain Integrity

    4. Enterprise Constraints (Semantic Constraint): An enterprise constraint are additional rules specified by users or database administrators, and can be based on multiple tables. For example:
      • One class can have a maximum of 30 students
      • One instructor can guide a maximum of four classes per semester
      • An employee cannot take part in more than five projects

    5. Referential Integrity : Referential Integrity requires that a foreign key must have a matching primary key, or it must be null. This constraint is specified between two tables (parent and child), and maintains correspondence between rows in these tables. See examples of referential integrity constraint in the Organization/Employee table below:

      Referential Integrity Constraint

      Referential Integrity Constraint
      Organization( OrgID , OrgName)
      Employee( EmpID , OrgID, EmpDate)

    6. Response

    7. Cyclic processing time requirements

  3. Document constraints using either a tabular, or a memo format. Examples of items to be considered are:

    • Access and processing cycle time requirements

    • Data security needs

    • Special display or calculation requirements

    • Special equipment utilization

2.5.13.4.2.5 (09-30-2020)

Develop a List of Potential Future Changes

  1. Develop a list of potential future changes, and the way in which they may affect operations. The purpose of this step is to include in the database design considerations that may affect operations in the future. Consider future changes to include anything that may affect the scope of the organization, present operating policies, or the relationship of the organization to the external environment. When reviewing the interviews to identify changes, highlight anything that implies change, and the effect(s) of that change.

  2. For more information, see steps for Business Analysis in subsection IRM 2.5.13.4.2 .

2.5.13.4.3 (09-30-2020)

Data Modeling Design

  1. Data modeling is a technique that involves the analysis of data usage and the modeling the relationships among entities. These relationships are modeled independent of any hardware or software system. The objective of logical design depicts user perspectives of data relationships and information needs.

  2. All IRS modernizations systems' conceptual, logical, and physical data models must be represented using the object model notation of the Unified Modeling Language (UML).

  3. The various approaches to logical database design involve two major design methodologies-entity analysis and attributes .

  4. The primary tool used is the data relationship diagram. This type of diagram is used to facilitate agreement between the designer, and users on the specific data relationships and convey those relationships to the physical database designer. It is a graphic representation of data relationships. The format used must be either the data structure diagram or entity-relationship diagram. See Exhibits 2.5.13-4 and 2.5.13-5.

  5. Simplify the modeling process by partitioning the model into the following four design perspectives:

    • Organizational Perspective : Reflects senior and middle management's view of the organization's information requirements, and how the organization operates.

    • Application Perspective : Represents the processing that must be performed to meet organizational goals, i.e., reports, updates, etc.

    • Information Perspective : Depicts the generic information relationships necessary to support decision-making, and long-term information requirements. It is represented by user ad hoc queries, long-range information plans, and general management requirements.

    • Event Perspective : Pertains to time and scheduling requirements. It represents when things happen, e.g., frequency of reports.

  6. There are two general rules that provide the foundation for design perspectives:

    • The design perspectives are modeled by three types of constructs:
      ✓ Entities : Number of tables needed for database
      ✓ Attributes : Facts necessary to describe each table
      ✓ Relationships : How are the tables linked together

    • In the design perspective, each component of information must be represented by only one of these constructs

  7. An entity refers to an object about which information is collected, e.g., a person, place, thing, or event. A relationship is an association between the occurrences of two or more entities. An attribute is a property of an entity, that is, characteristic about the entity, e.g., size, color, name, age, etc.

  8. Data usage is dynamic, and involves not only values, but relationships. Data must be divided into logical groups before being molded into whatever structures are appropriate, entity relationship diagrams, data structure diagrams, etc. After the completion of logical modeling the designer can determine if the database approach is practical , if not take an alternative path as soon as possible to save vital resources.

  9. Data modeling involves the following steps:

    1. Identify local views of the data

    2. Formulate entities/Entity Modeling

    3. Specify relationships

    4. Add descriptive attributes

    5. Consolidate local views and design perspectives

    6. Verify the data model

2.5.13.4.3.1 (09-30-2020)

Identify Local Views of the Data

  1. Develop local views of data for the following:

    • Organization

    • Application

    • Information

    • Event Design-Perspectives

  2. For each of the functions, activities, and tasks identified, there is a "sub-perspective" or local view of the data. Normally there will be several local views of the data depending on the perspective. These views correspond to self-contained areas of data that are related to functional areas. The selection of a local view will depend on the perspective, and the size of the functional area. Factors which must be considered in formulating local views include a manageable scope, and minimum dependence, or interaction with, other views.

  3. The primary vehicles for determining local views are: the task/data element matrices, and the task analysis and description forms constructed during logical database analysis. See Exhibits 2.5.13-2 and 2.5.13-3.

2.5.13.4.3.2 (09-30-2020)

Formulate Entities/Entity Modeling

  1. Entity modeling is a technique used to describe data in terms of entities, attributes, and relationships. For each local view, formulate the entities that are required to capture the necessary information about that view.

  2. At this point the designer is confronted with two major considerations as follows:

    1. The existence of multiple entity instances must be addressed by using the concept of "type" or "role". For example, the population of the entity EMPLOYEE can be categorized into employees of "type": computer systems analyst, secretary, auditor, etc. The generalization of these types into the generic entity EMPLOYEE will be considered in the next stage of conceptual design where user views are consolidated.

    2. Use of the entity construct - Information can be modeled as either an "entity" , "attribute" , or "relationship" . See Figure 2.5.13-5 example of how the designer must be guided by rules

      Figure 2.5.13-5

      Design Process Rules

      Design Process: the designer must be guided by rules Rules Example
      1) Two employees are married can be modeled using the entity MARRIAGE First - Use the construct that seems most natural
      2) The relationship IS-MARRIED-TO, or the attribute CURRENT-SPOUSE Second - Avoid redundancy - in the use of modeling constructs; use only one construct to model a piece of information.

    3. If this method later proves to be wrong, it must be factored out in subsequent design steps.

  3. The second consideration deals with the use of the entity construct itself. See Figure 2.5.13-5 "Design Process Rules" as an example.

  4. One rule of thumb, "magic number seven, plus or minus two", this technique has been successfully used to restrict the number of entities identified so that a local view can be properly represented. This states that the number of facts (information clusters) that a person can manage at one time is about seven plus, or minus two. When this is applied to the database design process, the maximum number of entities contained in a local view must not be more than nine, but closer to six or seven. If this restriction cannot be met, the scope of the local view is too large.

  5. Selection and assignment of an "entity name" : The entity name is important when views are consolidated because that next stage deals with homonyms and synonyms. If the name given to an entity does not clearly distinguish that entity, the integration and consolidation process will carry this distortion even further.

  6. For more, guidance on naming data elements see, IRM 2.152.3 Data Engineering, Naming Data Elements(s)/Object(s).

  7. Identify attributes for each entity: A collection of attributes may be used as the basis for formulating entities. The significant attribute is the identifier (or primary key) that uniquely distinguishes the individual entity instances (occurrences),e.g., employee number. This entity identifier is composed of one or more attributes whose value set is unique. This is also important later in the consolidation phase because the identifying attribute values are in a one-to-one correspondence with the entity instances. Therefore, two entities with the same identifiers could be redundant. However, this will depend on their descriptive attributes, and the degree of generalization.

2.5.13.4.3.3 (09-30-2020)

Specify Relationships

  1. Identify relationships between the entities. In this step, additional information is added to the local view by forming associations among the entity instances. There are several types of relationships that can exist between entities. These include:

    • Optional relationships

    • Mandatory relationships

    • Exclusive relationships

    • Contingent relationships

    • Conditional relationships

  2. In an optional relationship the existence of either entity in the relationship is not dependent on that relationship. For example, there are two entities, OFFICE and EMPLOYEE. Although an office may be occupied by an employee, they can exist independently.

  3. In a mandatory relationship, the existence of both entities is dependent on that relationship.

  4. An exclusive relationship is a relationship of three entities where one is considered the prime entity that can be related to either one of the other entities, but not both.

  5. In a contingent relationship the existence of one of the entities in the relationship is dependent on that relationship. A VEHICLE is made from many PARTS.

  6. A conditional relationship is a special case of the contingent relationship. When it occurs the arrow must be labeled with the condition of existence.

  7. Relationships can exist in several forms. The associations can be one-to-one (1:1), one-to-many, or many-to-many (N:N), the descriptions are the following:

    1. One-to-One association : Depicted as a single-headed arrow, and indicates that the relationship involves only one logical record, entity or entity class of each type.

    2. One-to-Many association : Depicted as a double-headed arrow, and documents the fact that a single entity, entity class or logical record of one type can be related to more than one of another type.

    3. Many-to-Many association : Depicted as a double-headed arrow in both directions.

  8. An informal procedure for identifying relationships is to pair each entity in the local view with all other entities contained in that view. Then for each pair, ask if a meaningful question can be proposed involving both entities or if both entities may be used in the same transaction. If the answer is yes to either question, determine the type of relationship that is needed to form the association. Next, determine which relationships are most significant and which are redundant. Of course, this can be done only with a detailed understanding of the design perspective under consideration.

2.5.13.4.3.4 (09-30-2020)

Add Descriptive Attributes

  1. Add descriptive attributes; attributes can be divided into two classes:

    1. Classes that identify entity instances : Included when the entities were formulated.

    2. Classes that provide the descriptive properties of entities : Examples of descriptive attributes are: color, size, location, date, name, and amount.

  2. In this step of local view modeling, the descriptive attributes are added to the previously defined entities. Only single-valued attributes are allowed for the description of an entity.

2.5.13.4.3.5 (09-30-2020)

Consolidate Local Views and Design Perspectives

  1. Consolidate Local Views and Design perspectives : Consolidation of the local views into a single information' structure is the major effort in the logical database design. It is here that the separate views and applications are unified into a potential database. Three underlying concepts that form the basis for consolidating design perspectives; these concepts are:

    1. Identity : Identity is a concept which refers to synonymous elements when two or more elements are identical, or have an identity relationship, if they are synonyms. Although the identity concept is simple, the determination of synonyms is not. Pertaining to inadequate data representation methods, the knowledge of data semantics is limited. Typically, an in-depth understanding of the user environments is required to determine if synonyms exist. Determining whether similar definitions may be resolved to identical definitions, or if one of the other element relationships really applies, requires a clear and detailed understanding of user functions and data needs.

    2. Aggregation : Aggregation is a concept in which a relation between elements is considered to become another higher-level element. For example, "EMPLOYEE" may be thought of as an aggregation of "NAME" , "SSN" , and "ADDRESS" . Many aggregations are easy to identify since the major data models incorporate syntax that can represent aggregations.

    3. Generalization : Generalization is a concept in which a group of similar elements is thought of as a single generic element by suppressing the differences between them. For example, the entity "EMPLOYEE" may be thought of as a generalization of "FACTORY-WORKER", "OFFICE-WORKER", and "EXECUTIVE". An instance of any of these three types is also an instance of the generalized "EMPLOYEE". Care must be taken not to confuse it with aggregation. An analogy for aggregation is parts making up a "whole", and generalization is the "whole".

  2. Since aggregation and generalization are similar in structure and application, one element may participate in both aggregation and generalization relationships.

  3. Inferences can be drawn about the aggregation dimension from the generalization dimension, and vice versa, e.g., it can be inferred that each instance of "EXECUTIVE" is also an aggregation of Name, SSN, and Address. See Figure 2.5.13-8.

  4. There are three consolidation types; these types may be combined in various ways to construct any type of relationship between objects (elements) in different user views. By combining consolidation types, powerful and complex relationships can be represented. Most semantic relationships are represented by some combination, and are listed as the following:

    • Identity Consolidation : Two objects may be semantically identical with the additional option of having identical names. Homonyms must be guarded against as well as similar, but not identical objects. Similarity is best expressed using aggregation and generalization as a check on the consistency of the consolidation, and user views. If an object from User view is found to be identical to an object from User second view, neither of these objects can participate further in any other identity consolidations between these two views. This is true because each object is assumed to be unique within the context of its own local user view.

    • Aggregation Consolidation : This may occur in two forms; the difference depends on whether one of the users has specified the aggregated "whole" object. An example of the simpler form is where User 1 has specified a number of objects without making any consolidation type relationships between them, e.g., an inventory view of "HANDLEBARS" , "WHEELS" , "SEATS" , and "FRAMES" . However, User 2 has specified an object, "BICYCLE" , which is an aggregation of User is objects. The conceptually more difficult version of aggregation occurs when both users have specified some or all of the parts of an unmentioned "whole". As an example, when separate inventory functions are maintained for basic, non-variable parts (FRAMES, WHEELS) and for parts that may be substituted by customer request (SEATS, HANDLEBARS). This type of aggregation is more difficult to recognize since neither user has defined a BICYCLE object.

    • Generalization Consolidation : This may also occur in two forms; the difference lies in whether either of the users has specified the generalized or generic object.

  5. The consolidation process comprises four steps:

    1. Select Perspectives : First, confirm the sequence of consolidation by following the order of design perspectives. Since this order is general, check it against the objectives of the database being designed. For example, if you are designing a database for a process-oriented organization, you might consider the key perspectives to be the application and event perspectives and therefore begin the process with these.

    2. Order Local Views within each Perspective : Once the design perspectives have been ordered, focus the consolidation process on local views within each perspective. Several views comprise the perspective chosen, and this second step orders these views for the consolidation process. The order must correspond to each local view's importance with respect to specific design objectives for the database.

    3. Consolidate Local Views within each Perspective : This step is the heart of the consolidation process. For simplicity and convenience, use binary consolidation, i.e., integrating only two user views at a time. This avoids the confusion of trying to consolidate too many views. The order of consolidation is determined by the previous step where the local views within a perspective have been placed in order. The process proceeds as follows:

    1. Take the top two views in the perspective being considered, and consolidate these using the basic consolidation principles.

    2. Using the binary approach, merge the next local view with the previously consolidated local views. Continue this process until the last view is merged.

    3. After the consolidation process is completed for the first design perspective.

    4. The next design perspective is introduced, and this process continues until all perspectives are integrated.

  6. Resolve Conflicts : Conflicts can arise in the consolidation process primarily because of the number of people involved, and the lack of semantic power in our modeling constructs. They may also be caused by incomplete or erroneous specification of requirements. The majority of these conflicts are dealt with in the consolidation step using the rules previously discussed, any remaining conflicts that have to be dealt with by designer decisions are taken care of in this step. When a design decision is made, it is important to "backtrack" to the point in the consolidation process where these constructs were entered into the design. At this point the implications of the design decision are considered, and their effects on the consolidation process.

2.5.13.4.3.6 (09-30-2020)

Present Data Model

  1. The purpose of this step is to present the data model. Use data relationship diagrams to document local views and their consolidation. These must take the form of an entity-relationship diagram, or a data structure diagram. See Exhibits 2.5.13-4 and 2.5.13-5.

  2. Use these rules when constructing either of these diagrams:

    • Each entity and relationship must be clearly labeled

    • Each entity must be related to at least one other entity

    • Show attributes only if they uniquely identify the entity, or are a common access path to the entity

    • Limit entities and relationships for a given activity to a single page

    • Identify the name of the activity supported at the top of the page

    • If a data relationship diagram for an activity needs to span more than one page, use the same off-page connectors as used in DFDs

2.5.13.4.3.7 (09-30-2020)

Verify Data Model

  1. Verify the data model. The purpose of this step is to verify the accuracy of the data model and obtain user concurrence on the proposed database design.

  2. The process of developing the information structure involves summarizing, and interpreting large amounts of data concerning how different parts of an organization create and/or use that data.

  3. The design process is highly structured; therefore, take caution to not miss relationships and/or expressed them incorrectly.

  4. The development of the information structure is the only mechanism that defines explicitly how different parts of an organization use, and manage data. Anticipate that management, with new knowledge shall require some changes. Because of this possibility, it is necessary to provide management with an understanding of the data relationships shown in the information structure, and how these relationships affect the way in which the organization performs, or can perform, its mission. Each relationship in the design and each relationship excluded from the design must be identified and expressed in very clear statements that can be reviewed and approved by management. Following management's review, the design will, if necessary, be adjusted to reflect its decisions.

  5. The verification process is separated into two parts, self-analysis and user review. In self-analysis, the analyst must insure that:

    • All entities have been fully defined for each function and activity identified

    • All entities have at least one relation to another entity

    • All attributes have been associated with their respective entities

    • All data elements have been defined in the Enterprise Data Dictionary

    • All processes in the data flow diagram can be supported by the database when the respective data inputs and outputs are automated

    • All previously identified potential changes have been assessed for their impact on the database and necessary adjustments to the database have been determined

  6. To obtain user concurrence on the design of the database, perform the following steps, in the form of a walk-through, to interpret the information structure for the user:

    1. State what each entity is dependent upon (i.e., if an arrow points to it). Example: All "ORDERS" must be from "CUSTOMERS" with established accounts.

    2. State what attributes are used to describe each entity.

    3. Define the perceived access path for each entity.

    4. Define the implications of each arrow (i.e., one-to-one, one-to-many etc.).

    5. Define what information cannot exist if an occurrence of an entity is removed from the database.

  7. Give the user an opportunity to comment on any perceived discrepancies in the actual operations or usage. If changes need to be made, then give the user the opportunity to review the full design at the completion of the changes.

  8. After all changes have been have been updated for both the relationship diagram and the data definitions, obtain user concurrence on the design specifications.

2.5.13.5 (09-30-2020)

Physical Database Design

  1. The boundary between logical and physical database design is difficult to assess because of the lack of standard terminology. However, there seems to be general agreement that logical design encompasses a DBMS-independent view of data and that physical design results in a specification for the database structure, as it will be physically stored. The design step between these two that produces a schema that can be processed by a DBMS can be called implementation design. The DBMS-independent schema developed during logical design is one of the major inputs. Refinements to the database structure that occur during this design phase are developed from the viewpoint of satisfying DBMS-dependent constraints as well as the more general constraints specified in the user requirements.

  2. The major objective of implementation design is to produce a schema that satisfies the full range of user requirements and that can be processed by a DBMS. These extend from integrity and consistency constraints to the ability to efficiently handle any projected growth in the size and/or complexity of the database. However, these must be considerable interaction with the application program design activities that are going on simultaneously with database design. Analyze high-level program specifications and program design guidance supplied to correspond to the proposed database structure.

  3. The usefulness of these guidelines is directly related to the where one is in a development life cycle, and the level of expertise of a developer. This document assumes the reader is familiar with database concepts and terminology since designers will most likely be database administrators or senior computer specialists.

  4. The criterion for physical design is determined by evaluating requirements e.g., operational efficiency, response time, system constraints and security concerns. This physical design layout must be routinely adjusted to improve the system operation, while maintaining the user's logical view of data. The physical structuring, or design will often be quite different from the user's perception of how the data is stored.

  5. The following steps provide general guidance for physical database design. Since much of the effort will depend on the availability of data and resources, the sequence of these steps is flexible:

    1. Determine user requirements

    2. Determine processing environment

    3. Select database management system software

    4. Design physical placement of data

    5. Perform sizing of data

    6. Use database security controls based on IRM 10.8.21" Information Technology (IT) Security, Database Security Policy" .

    7. Implement or use the recover procedures based on NIST SP 800-34 "Contingency Planning Guide for Federal Information Systems and policy" and IRM 10.8.60"IT Security, IT Service Continuity Management (ITSCM) Policy" .

  6. See Figure 2.5.13-12 for comparison of logical and physical design:

    Figure 2.5.13-12

    Logical Design Compared with Physical Design

    Logical Design Physical Design
    Entity Table
    Relationship Foreign Key
    Attribute Column
    Unique Identifier Primary Key

  7. ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡≡ ≡ ≡

2.5.13.5.1 (09-30-2020)

Determine the User's Requirements

  1. User(s)' requirements are critical factors that add value to a product, service or environment. Fulfilling user requirements is a process of engaging users to understand their problems, process, goals and preferences. The following are user requirement examples:

    • Accessibility : User requirement of 99.99% availability

    • Accuracy : Data initiates must be correct

    • Capacity : Storage of Big data

    • Compatibility : Product or service must be compatible with other services and/or products

    • Convenience :

    • Comfort : Physical design that is easier to understand, or better for vision, i.e., 508 compliance

    • Durability : A design must have stress testing to eliminate breakage

    • Efficiency : The processes' desired outcome for resource constraints

    • Features : Preferred methods for serving needs

    • Functions : Functional requirements the user needs to perform work, e.g., a system may be required to calculate and print budgets.

    • Integrated Services : Automatic availability of diverse multiple devices

    • Performance : At a minimum a set of performance requirements must be documented as the following:
      ✓ Maximum response time to be experienced for each user-computer interaction. Response time is measured from the time the user starts their action until feedback is received from the computer.
      ✓ Throughput required, and the time it will take place, e.g., the requirement for one program could be for it to run twice a day at a specific time.
      ✓ The size and time of maximum-throughput periods
      ✓ Response time that is minimally acceptable the rest of the time. Response time degradations can cause users to think the system is down

    • Readability : Users prefer information, and visual data that is concise and easily understood

    • Refinement (Schema) : Checking tables for redundancies and anomalies

    • Reliability : Product must perform consistently

    • Risk : Requirement to reduce risk' e.g., being able to roll-back the database, or obtain a backup

    • Trainable : User interfaces must be user-friendly

    • Stability : the system environment must be stable, and not susceptible to crashing.

    • Visual Appeal - Users strongly prefer products, services, and environments that are aesthetically appealing

  2. Decision making, and the impact of approving user requirements without having all the facts could have negative consequence; therefore, performing an analysis is necessary before acting. Figure 2.5.13-13 displays each requirement, and an example of an associated result:

    Figure 2.5.13-13

    Example of User Requirements and Consequences

    Requirements Consequences
    Retrieval time decreases with a simple database structure To meet the logical design requirements, it may be necessary to implement a more complex multilevel structure.
    Ease of recovery increases with a simple structure Data relationships may require more complex mechanisms
    Increased pointer or index requirements, hardware cost is increased if information is spread over many storage devices Data clustering and compacting degrade performance
    Privacy requirements may require stringent security, e.g., encryption, or data segmentation These procedures decrease performance in terms of update, and retrieval time
    Active files, that are accessed in real time, and need high-speed devices The result is increased cost

2.5.13.5.2 (09-30-2020)

Determine the Processing Environment

  1. To determine the primary type of processing, the designer must have a framework of physical requirements. Three environments will be discussed; however, these are only guidelines because for some systems this information will not be applicable for one general set of requirements. These considerations will often conflict with the user's requirements or security needs, thus forcing the designer to make decisions regarding priority.

  2. Normally this environment requires fast response time, and multiple run units will actively share DBMS facilities. In order to meet response time specifications, cost may increase due to the necessity for additional system resources. Recovery may be critical in such a volatile environment, and whenever possible, use a simple structure. In a CODASYL (network) structure, this time specification would translate into reduced data levels, number of network relationships, number of sets and size of set occurrences. In a high-volume processing environment requests are most frequently random in nature requiring small amounts of information transfer; thus affecting page and buffering considerations

  3. Low-volume systems generally process more data per request, indicating run units may remain in the system longer. There is the likelihood of more sequential requests and reports, and response time is probably not the critical issue. Resources may be more limited in this environment, implying smaller buffers, and perhaps fewer peripherals. With the possibility of fewer resources, those resources may need to be more highly utilized. On-line recovery techniques may be unavailable since the resource requirements are costly. Although the number of transactions is low in this environment, the probability of multiple simultaneous run units accessing the same data may be high.

  4. When a batch environment is indicated, the designer is left with maximum flexibility since the requirement is reasonable turnaround time and effective use of resources. Because of job scheduling options, concurrency problems can be controlled. Recovery tends to be less critical and will be determined by such factors as file volatility, the time necessary to rerun update programs, and the availability of input data. For example, if the input data is readily available, the update programs short and processing 85 % retrieval; the choice may be made to avoid the overhead of maintaining an on-line recovery file.

2.5.13.5.3 (09-30-2020)

Select DBMS Software

  1. The DBMS must first physically support the logical design requirement that is, based on the logical data model, the package must support the required hierarchical, network or relational structure. Early stages of analysis must provide enough information to determine this basic structure. From a physical database design point of view, an analysis must then be made as to how effectively the DBMS can handle the organizational and environmental considerations. If the proposed package fails to provide adequate support of the requirements, the project manager, and supervisor(s) must be notified. The notification must include the specific point(s) of failure, anticipated impact(s), and any suggestions or alternatives for alleviating the failure(s).

2.5.13.5.4 (09-30-2020)

Design the Physical Placement of Data

  1. Designing the placement of data involves selecting the physical storage, and access methods as well as secondary and multiple key implementation techniques. DBMS packages vary as to the options offered. The use of vendor documentation, providing specific software handling details, will be necessary to complete this process. Exhibit 2.3.13-8 provides a summary of access methods and uses.

2.5.13.5.5 (09-30-2020)

Perform Sizing of Data

  1. Obtain the specifics of sizing from vendor documentation as each DBMS handles space requirements in a different manner. Consider sizing in conjunction with designing the placement of data. Once data records, files, and other DBMS specifics have been sized according to a proposed design, a decision may be made, because of the space allocation involved, to change the design. Data compaction techniques may be considered at this point. Flexibility to make changes and reevaluate trade-offs during this entire procedure is of critical importance.

  2. For an example, see Exhibit 2.5.13-14

2.5.13.5.6 (09-30-2020)

Consider Security and Recovery

  1. The DBMS selected must have the options necessary to implement security and recovery requirements from IRM 10.8.21 IT, Security, Database Security Policy, and IRM 10.8.32 IBM Mainframe System Security Requirements. Implementation of these considerations will often cause trade-offs in other design areas.

2.5.13.6 (09-30-2020)

Deliverables

  1. The deliverables for database design are the following:

    1. Decision Analysis and Description Forms

    2. Task Analysis and Description Forms

    3. Task/Data Element Usage Matrix

    4. Data Models

    5. Entity-Attribute Lists

    6. Data Definition Lists

    7. Physical Data Base Specifications Document

2.5.13.6.1 (09-30-2020)

Decision Analysis and Description Forms

  1. Decision Analysis and Description Forms must identity such items as type of decision, the decision maker, and the nature of the decision. Exhibit 2.5.13-1 provides guidelines and shows a sample form.

2.5.13.6.2 (09-30-2020)

Task Analysis and Description Forms

  1. Task Analysis and Description Forms must include the name of the task, its description (overview), the people/departments involved, and subtasks and their relationships. Exhibit 2.5.13-2 shows a sample form and provides guidelines.

2.5.13.6.3 (09-30-2020)

Task/Data Element Usage Matrix

  1. A task/data element usage matrix relates each data element to one or more tasks, see Exhibit 2.5.13-3 for a sample matrix.

2.5.13.6.4 (09-30-2020)

Data Models

  1. Data relationship diagrams depict the relationships between entities. These are tools that provide one way of logically showing how data within an organization is related. They must be models using conventions for either data structure diagrams, or entity relationship diagrams. Exhibits 2.5.13-4 and 2.5.13-5 provide samples of these diagrams, and more detailed guidelines. The term "entity" refers to an object representing a-person, place, thing event about which information is collected. When constructing either of these diagrams it is recommended that the entities be limited to those of fundamental importance to the organization.

2.5.13.6.5 (09-30-2020)

Entity-Attribute Lists

  1. Entity-attribute relation lists may be derived from the Enterprise Engineering Data Dictionary listings.

2.5.13.6.6 (09-30-2020)

Data Definition Lists

  1. Data definition lists may be derived from Enterprise Engineering Data Dictionary listings. Exhibit 2.5.13-6 provides a sample.

2.5.13.6.7 (09-30-2020)

Physical Database Specifications Document

  1. The objective of this subsection is to state the required content for Physical Database Specifications. Duplication of effort can be eliminated if there are existing documents available containing physical specifications. Use the following resources for developing documentation:

    • DBMS Documentation : For example, a listing of the scheme from a CODASYL DBMS will provide details as data names, sizing, placement, and access methods.

    • Data Dictionary Listing : Provides certain physical specifications, e.g., data format and length.

    • Project Documentation : All documentation submitted as Physical Database Specifications must be organized in a macro to micro manner, or global to specific. Hence, begin at the schema level, moving to subschema, indices, data elements, etc. The objective is to organize documentation in a manner that is clear, and easy for the user to read.

    • Adhere to the standards and guidelines

2.5.13.6.7.1 (09-30-2020)

Physical Database Names

  1. Where appropriate in the documentation, identify names of physical database items. Specifically, the items will be all those defined to the DBMS software, such as:

    • Attribute Name

    • Subject Area Name

    • Super Class or Class

    • Schema

    • Subschema

    • Set

    • Record

    • Field

    • Key

    • Index Names

    • Relationship Name

  2. Be atomic, and represent only one concept.

  3. Be unique, avoid adding a name like that of another.

  4. Be concise, using minimal words as possible.

  5. Table and column names must be sized to fit the requirements of the target RDBMS. Different database products permit different lengths.

  6. ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

  7. Where data naming standards are applicable, these standards shall be met to the extent possible with the DBMS software. For example, if the DBMS does not permit hyphens in naming, an exception would be made to the standard "all words in a name must be separated with a hyphen".

  8. For more guidance on data naming see, IRM 2.152.3 IT Data Engineering, Naming Data Elements/Object.

  9. For definition of all terms listed, see Exhibit 2.5.13-11

2.5.13.6.7.2 (09-30-2020)

Data Structure/Sizing

  1. Identification of data elements, associations within and between record types as well as sizing requirements are identified and documented during the logical database design process. The physical representation of data structures will vary from the logical, however, since the physically stored data must adhere to specific DBMS characteristics. As applicable to the DBMS, the following structures must be documented:

    • Records

    • Blocks/Pages

    • Primary Key (PK) attributes

    • Files

  2. Describe the physical record layout. In this description, include the data fields, embedded pointers, spare data fields, and database management system overhead (flags, codes, etc.). Besides data record types, document any other record types such as index records. If records are handled as physical blocks or pages, provide the following:

    • Calculations determining block/page size

    • Total number of blocks/pages allocated

  3. Describe the strategy for determining block/page sizes.

  4. Specify the amount of space allocated for each database file. This must be consistent with the total record, and block/page sizing documentation described above.

2.5.13.6.7.3 (09-30-2020)

Data Placement

  1. For each record type:

    • State the storage and access method used

    • Describe the storage and assess method

    • Where applicable, identify the physical data location (track, cylinder)

  2. When using an algorithm access method:

    • Provide the primary record key to be used by the algorithm

    • Describe the algorithm used, including the number, and size of randomized address spaces available to the algorithm

    • Give the packing density, and the strategy for its determination

  3. When using an index sequential access method:

    • Provide the primary record key

    • State indexing strategy/levels

    • State initial load strategy

  4. When using chains access method:

    • Provide the access path to the record type (i.e., Is this primary access of detail record though the master record, or is this a chain of secondary keys?)

    • List the pointer options used (i.e., forward, backward, owner, etc.)

    • Indicate whether the chain is scattered or stored contiguously with the master

  5. When an index access method is used:

    • Identify keys used to index a record type

2.5.13.7 (09-30-2020)

Database Management System Software Supported by the IRS

  1. The IRS supports various database management software which is installed on both client-server application systems, and legacy application systems with features for:

    1. Data storage

    2. Data backup and recovery

    3. Data presentation and reporting

    4. Data security management

    5. Database communication

    6. Multi-user access Control

  2. The IRS has seven common types of database management systems:

    1. Document databases

    2. E-R model databases

    3. Graph databases

    4. Hierarchical databases

    5. Object-oriented databases

    6. Relational databases

    7. NoSQL databases

  3. Database software is classified into six sub-types:

    1. Analytical Databases : Allows users to pull data from a variety of databases, and examine them for the purpose of quantifying, or assessing performance of the business environment.

    2. Data Warehouse Databases : Allows users to pull key data from a variety of databases, and store it in a central location for reporting, or other purposes.

    3. Distributed Databases : Pertains to centralized database management systems that controls information stored in a variety of locations including cloud, or network servers.

    4. End-user Databases : Stores information that is used primarily by one person, e.g., spreadsheets.

    5. External Databases : Compiles information that must be accessed by a variety of users via the Internet.

    6. Operational Databases : Allows the user to modify data in real-time.

  4. The top enterprise database management system software that is currently approved, and supported by the IRS are the following:

    1. IBM Database 2 Universal Database (DB2)

    2. IBM DB2 for z/OS

    3. Microsoft SQL Server Enterprise

    4. MongoDB (NoSQL)

    5. MySQL database service

    6. PL/SQL (Oracle) database

    7. PL/pgSQL (PostgreSQL) database

    8. SAS

    9. Transact-SQL database

    Note:

    This list changes as agency requirements are modified.

2.5.13.7.1 (09-30-2020)

IBM Enterprise Database 2 Universal Database (DB2 UDB) Overview

  1. DB2 is a Relational database Management System product of IBM which was release during 1996 for distributed platforms, and designed to store, analyze and retrieve data. The Universal Database (UDB) DB2 Server can run on any operating systems such as Linux, UNIX, and Windows. During 2016, IBM released DB2 11.1 with enhanced analytics, increased availability, reliability, and included more security for business applications.

2.5.13.7.1.1 (09-30-2020)

DB2 Physical Objects

  1. The Enterprise Data Standards and Guidelines Office (EDSG) developed twelve (12) DB2 unique DBMS objects standards and guidelines. A data element entered is not correct, or complete unless there are entries in all mandatory fields. See Figure # # 2.5.13-14 # below:

    Figure 2.5.13-14

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

    Note:

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

    Note:

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

    Note:

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

    Note:

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

    Note:

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

    Note:

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

    Note:

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

  2. ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

2.5.13.7.1.2 (09-30-2020)

DB2 Performance Standards and Guidelines

  1. During the initial DB2 environment design process of creating a strategy for quality performance, the focus must be on DB2 database, applications, and/or transactions that have the most importance workload. If the performance of the applications accessing the database is addressed after development, it will be more difficult, and time-consuming to make the modifications to obtain adequate response-times.

  2. When designing for performance, objectives must be realistic when determining what applications have the most vital workloads, and be in-line with the organization's expectations based on the following characteristics:

    1. Data processing needs and priorities

    2. Largest percentage of the total business workload

    3. Critical response time requirement

    4. Data access requirements and/or complex logic

    5. Using vast amounts of resources (CPU, memory, Input/Output)

    6. Direct interface with customer/end users via web in contrast with applications that are internal to IRS

    7. Security requirements

    8. Performance that is measurable and monitored

2.5.13.7.1.2.1 (09-30-2020)

IRS DB2 Tables - Designing for Performance

  1. This subsection specifically addresses the best organization of database tables, columns, and optimum index definitions for enterprise DB2 performance established by the EDMO Database Administration team. The goal is to ensure high quality, and efficient DBMS throughout the agency. See Figure # # 2.5.13-15 # for standards:

    Figure 2.5.13-15

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

    Figure 2.5.13-16

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡≡ ≡ ≡≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡≡ ≡ ≡≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡"≡ ≡ ≡ ≡ ≡" ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡"≡ ≡ ≡ ≡ ≡ ≡" ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡"≡ ≡ ≡ ≡ ≡ ≡" ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡"≡ ≡ ≡ ≡ ≡ ≡" ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡"≡ ≡ ≡ ≡ ≡" ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡"≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡" ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
    ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

    Note:

    Important: Using the wrong data type for a column can mislead the DB2 optimizer. Cardinality could escalate, and the DB2 optimizer might not make the correct decision when determining the path to the data.

2.5.13.7.2 (09-30-2020)

SQL Server Overview

  1. SQL Server is a relational database management system, or RDBMS, developed by Microsoft. SQL Server is built on top of SQL, a standard programming language for interacting with the relational databases. Transact-SQL (T-SQL) is the main query language for SQL Server, and contains various extensions of standard SQL, i.e., local variable, control statements, built-in functions, bulk insert, and options on the DELETE and UPDATE statements. T-SQL is proprietary while SQL is open format.

  2. As of 2016, SQL Server 2017 became available, and runs on both Windows and Linux environments.

2.5.13.7.2.1 (09-30-2020)

SQL Server and T-SQL Design Best Practices

  1. This subsection describes practices that database designers and database development team must follow to create SQL, or SQL Based code that meets the code standards and conventions in SQL Server Database and/or T-SQL.

  2. The following rule is when creating a query in a relational database:

    1. Use only T-SQL syntax for supporting SQL Server.

    2. Use built-in and user-defined functions in SQL Server instead of third party functions for the reason of portability, and ease of use.

    3. Use End comment text which states a change in content with a period.

    4. Add a new line, if the length of comment text is greater than 100 characters.

    • T-SQL Select Command: Do not use "*" in your SELECT queries instead of specifying name of columns

  3. ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

2.5.13.7.3 (09-30-2020)

MySQL Overview

  1. MySQL was purchased by Sun Microsystems in 2008, and is open source with premium add-ons. MySQL is used for website, and as a back-end database solution for enterprise applications.

  2. During 2019 MySQL 8, included features such as NoSQL document store for Big Data. This includes better sorting, support for partial updated crash-safe DDL sentences, and JSON extended syntax.

2.5.13.7.4 (09-30-2020)

Oracle Database Design Overview

  1. Oracle database (Oracle DB) is a relational database management system (RDBMS) from the Oracle Corporation which was originally developed during 1977. This system has a relation database framework where data objects may be directly accessed by users, or application front end via SQL. Oracle is a fully scalable relational database architecture, and is used by global enterprises.

  2. The key to database and application performance is design, not tuning.

  3. Oracle SOAP (Simple Object Access Protocol) interfaces support (Atomicity, Consistency, Isolation, and Durability) ACID compliant database transactions which guarantee transactions are processed reliably.

  4. ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

2.5.13.7.4.1 (09-30-2020)

Oracle - Design for Performance Best Practices

  1. Design for Performance: When the design is poor the data model is not efficient. A great start to an efficient design is well-defined performance goals and metrics, and a good benchmarking strategy; therefore, design goals for developers must be the following steps:

    1. Design quality Data Models for Optimal Performance: This is very important — perform frequent queries by doing the following:
      • Analyze the data requirements of the application.
      • Implement the database design for the application.
      • Maintain the database and database application by monitoring the system.
      • Schedule the maintenance periods, and inform users when it will begin.
      • Fix any bugs, apply patches and release upgrades.

    2. Tools for Performance : Use performance tools for reporting runtime information about the application as the following:
      • DBMS_APPLICATION_INFO Package : Use this package with the SQL Trace facility and Oracle Trace, and to record the names of executing module, or transactions in the database.
      • SQL Trace Facility (SQL_TRACE) : For best results, use this tool with "TKPROF" formats the trace file contents in a readable file. The EXPLAIN PLAN statement show the execution plans chosen by the optimizer, and the execution plan for the specified SQL statement if it were executed in the current state.
      • EXPLAIN PLAN Statement : This statement stores the execution plan for the statement in a plan table after a SQL statement is run, and the optimizer generates several execution plans. The plan table contains optimization information such as the cost, and cardinality of each operation, accessed partitions, and distribution method of join inputs.

    3. Testing for Performance : Use the following guidelines for testing an application performance:
      • Automatic Database Diagnostic Monitor (ADDM) : ADDM is used for design validation, and determines where database performance problems might exist, and recommends corrections.
      • SQL Tuning Advisor : SQL Tuning Advisor which is also used for design validation, e.g., if ADDM finds high-load SQL statements SQL Tuning Advisor can analyze the statements, and provide tuning recommendations.
      • Test a Single User Performance First : Start testing in a single user mode first; therefore, if an acceptable performance is not achieved with low loads, then multiple user cannot obtain acceptable performance.

    4. Design for Scalability : Use appropriate application development techniques, i.e., bind variables, and Oracle Database architecture features like shared server connections, clustering, partitioning, and parallel operations.

2.5.13.7.4.2 (09-30-2020)

Relational Database Design Rules and SQL Coding Standards

  1. This section is for enterprise database design, regulation of linked databases, e.g., COMMON databases, MASTER databases, and Transaction databases. See Exhibit 2.5.13-13

2.5.13.7.5 (09-30-2020)

PostgreSQL Overview

  1. PostgreSQL is authorized for IRS use, and is an open source developed object relational database management system (ORDBMS) based on POSTGRES, version 4.2. It was created at the University of California, at Berkeley Computer Science Department. PostgreSQL is the descendant of this Berkeley code, and offers features as follows:

    • Complex queries

    • Foreign keys

    • Triggers

    • Updatable views

    • Transactional integrity

    • Multiversion concurrency control

  2. PostgreSQL runs on Windows, Linux, FreeBSD, and UNIX Solaris operating systems, and includes features like Oracle and IBM DB2. PostgreSQL supports high concurrent traffic loads and full ACID compliance for transactions. PostgreSQL 11.1 was released in November 2018.

2.5.13.8 (09-30-2020)

Big Data Models and No Structured Query Language (NoSQL) Databases Overview

  1. Relational databases that store data in a fixed, tabular format is only enough for small to intermediate-scale database applications.

  2. Big data normally refers to online transaction processing of high volumes of data or digital information such as: Terabytes (TB) = 1,000 GB, Petabyte (PB) = 1,000 TB, Exabyte (EB) = 1,000 PB, Zettabyte (ZB) = 1,000 EB, Yottabyte (YB) = 1,000 ZB, and of low-density unstructured data. Since Big Data implies enormous storage it would be costly, and unpractical when using relational databases. Hence, NoSQL (non-relational databases) like "MongoDB" which is document-oriented is currently used by IRS Information Technology systems for big-data analytics and data warehousing.

  3. NoSQL databases does not use tables, nor the links between tables in order to store and organize information. This database is critical for large volumes of quickly changing data applications and real-time web applications because of its ability to manage agility challenges, and provide highly resilient scalability on the fly. There are four major types of NoSQL databases:

    1. Column Database : Data is stored in a columnar format.

    2. Graph Database : This database is arranged in the form of a graph with the elements connected using the relations between them.

    3. Key-value database : This database is organized as key value pairs that only appear once, e.g., Couchbase and ArangoDB.

    4. Document Database : Document databases store data in documents comparable to JavaScript Object Notation (JSON) objects, but can also use XML, text, or Binary Large Object (BLOB).


    5. •
      •
      •

  4. The benefits of using NoSQL databases are:

    1. Flexible for developers to manipulate, and map to objects in their code

    2. The schema dynamically adapts to change, and is self-designing — it does not need to be pre-defined in the database

    3. Document databases like MongoDB use JSON format so rules pertaining to document structure can be imposed to store data

2.5.13.8.1 (09-30-2020)

Enterprise Database Design

  1. An enterprise Database Management System (DBMS) is used by enterprises/large organization to manage large collection of data.

2.5.13.9 (09-30-2020)

Database Security Design

  1. All organizations must work on a continual basis protecting their databases that have sensitive data by identifying and remedying security vulnerabilities and exploits. In addition to doing monitoring and security assessments, ensure results are analyzed and properly audited for demonstrating compliance with federal security regulations: IRM 10.8.21 Information Technology (IT) Security, Database Security Policy, OWASP, and NIST 800-53A industry standards.

2.5.13.9.1 (09-30-2020)

Database Design Security Best Practices

  1. This subsection is guidance for ensuring adequate security controls for all databases storing sensitive or protected data.

  2. For databases the following Federal Information Processing Standard (FIPS) government standards must be followed:

    1. FIPS 127-2: Identifies and describes the recommended construct sizes, see Exhibit 2.5.13-14

    2. FIPS 140-2: Identifies the cryptographic security requirements, and is designed to protect data at rest, and in transit over the network. FIPS have several levels ranging from (1 - lowest) to (4 - highest).

2.5.13.10 (09-30-2020)

IRS Extensible Markup Language (XML) Overview

  1. As of January 2003, the IRS has approved XML as the recommended syntax specification in more than 20+ applications. The Enterprise Data Management Organization (EDMO) is the program lead, and collaborates daily with organizations using projects associated with this language by establishing policy and standards. The IRS XML Community of Practice (xmlCoP) was launched to facilitate, and review the necessary changes XML policies, standards and practices.

  2. Extensible Markup Language (XML) is used to define documents with a standard format that can be read by an XML compatible application. XML can be used with HTML pages, but is not a markup language. It is a "metalanguage - information used to describe language" that can be used to create markup languages for particular applications.

  3. Technical experts can use XML to create a database of information without having a real database. XML is commonly used in web applications, and other programs, e.g., Arbortext which is a XML compatible application used to create/update IRS Internal Revenue Manuals (IRMs).

  4. The XML Industry Standards are as follows:

    1. The World Wide Web Consortium (W3C) is family of XML standards.

    2. Electronic Business is using eXtensible Markup Language (ebXML).

    3. Universal Business Language (UBL): UBL is an implementation of ebXML, ebXML originally an Oasis standard is now an ISO standard (ISO 15000-5), and focuses on the design of reusable components.

2.5.13.10.1 (09-30-2020)

IRS Extensible Markup Language (XML) Naming and Design Rules

  1. EDMO is responsible for the IRS XML naming and design rules, and the relevant standardized XML components or products after use. The naming and design rules for constructing and naming XML components or products pertain to:

    1. Attributes

    2. Data Types

    3. Elements

    4. Namespaces

    5. Schema

  2. For all definitions, see Exhibit 2.5.13-11

  3. The main objective of the XML naming, and design rules is to maintain uniformity, and interoperability across the IRS, and its data exchange partners. This naming and design rules established by EDMO apply to all XML data exchange between services, reusable system components, and system to system interfaces.

  4. ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡≡ ≡ ≡

Exhibit 2.5.13-4

Guidelines for Constructing Data Structure Diagrams (DSD)

≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

Note:

≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

This is an Image: 36192024.gif

Depiction of two Entity Classes

Please click here for the text description of the image.

Hierarchical Network Structure

Hierarchical Network Diagram
Data structure diagrams can represent hierarchical structure, network structure, or a combination of these structures. The following diagram depicts a hierarchical structure.

This is an Image: 36192026.gif

Depiction of a Hierarchical Structure

Please click here for the text description of the image.

Explanation of Hierarchical Structure Above

Explanation of Hierarchical Structure 1
1. This depicts that an employee works several projects, and that each project must have only one name and one completion date.
2. Hierarchical structures are distinguished from other relationships because every node must have only one parent node, except the root, which has no parent.
3. The descendants of a node are called children

Exhibit 2.5.13-5

Relationship between Two Entities-Classes

Two Entity Classes
1. These symbols do not indicate how many individual entities constitute each entity class. Relationships depict cardinality, e.g., (One to One), (One to Many), (Many to Many), etc. The following depicts a relationship between entity classes.
2. This relationship indicates that each Department comprises (One to Many) Employee

This is an Image: 36192025.gif

Illustration of between Two Entities-Classes Relationship

Please click here for the text description of the image.

Exhibit 2.5.13-6

Explanation of Hierarchical Structure (One-to-Many) and (Many-to-Many) Relationships

Explanation of Hierarchical Structure 2
1. This network structure allows a logical record, entity, or entity class to have more than one parent.
2.
• The following examples show a one-to-many and a many-to-many relationship:
✓ The first example shows that a student has only one advisor and one major, but an advisor counsels many students and many students choose a particular academic major.
✓ The second example shows that a student has many classes, and that a class comprises many students.

This is an Image: 36192027.gif

Illustration of (One-to-Many) and (Many-to-Many) Relationships

Please click here for the text description of the image.

Exhibit 2.5.13-7

Guidelines for Constructing Entity Relationship Diagrams (ERD)

≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

This is an Image: 36192028.gif

Illustration of Relationship between two Entities

Please click here for the text description of the image.

≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

This is an Image: 36192029.gif

Illustration Diagram of Subtype Dependence with Three Products

Please click here for the text description of the image.

≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

This is an Image: 36192030.gif

Illustration Diagram of Existence Dependence

Please click here for the text description of the image.

≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

This is an Image: 36192031.gif

Illustration Diagram of Identifier Dependence

Please click here for the text description of the image.

≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

This is an Image: 36192032.gif

Illustration Diagram of Precedence Dependence

Please click here for the text description of the image.

≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

This is an Image: 36192033.gif

Illustration of Relationship to one or more entity types

Please click here for the text description of the image.

Exhibit 2.5.13-8

Sample Data Definition List

Sample Data Definition List
Name Definition Length
(Name of data element) (Definition of data element) (Length of data element)
... ... ...
... ... ...

Exhibit 2.5.13-10

Acronyms and Terms

Acronyms Terms
ACID Atomicity, Consistency, Isolation, Durability
BLOB Binary Large Object
CASE Computer Aided Software Engineering
CODASYL Conference on Data Systems Languages
CLOB Character Large Object
CSS Cascading Style Sheet
DBA Database Administrator
DBMS Database Management System
DBRM Database Request Module
DDL Data Description (Definition) Language
DSD Data Structure Diagram
DSN Data Set Name
EDD Engineering Data Dictionary
EDSG Enterprise Data Standards and Guidelines
ERD Entity Relationship Diagram
HTML Hypertext Markup Language
JSON Java Script Object Notation
LOB Large Object
ORDBMS Object Relational Database Management System
PPDM Project Physical Data Model
SOAP Simple Object Access Protocol
SQL Sequel Language
UML Unified Modeling Language
XML Extensible Markup Language
W3C World Wide Web Consortium
WSDL Web Services Description Language
WSDL (NDR) WSDL Naming and Design Rules

Exhibit 2.5.13-11

Terms/Definitions

Terms Definitions
ACID A set of properties for database transactions that guarantee validity in the event of errors, power failures, or other adverse system issues.
Algorithm A process or set of rules to be followed in calculations or other problem-solving operations.
Attribute A data item that comprises a single piece of information about an entity. It usually cannot be broken down into parts that have meanings of their own. Attributes can be divided into two classes--those that identify entity instances (occurrences) and those that provide the descriptive properties of an entity.
Bachman Diagram Another name for a data structure diagram. It is named for Charles Bachman who invented it.
Bucket An area of storage containing one or more records referred to by the same address.
Buffer Storage used to temporarily hold data being transferred from one device to another.
Block Usually a combination of two or more records which are read and written jointly by one machine instruction.
Branches The relationship between the records in a tree (hierarchical) data structure.
Business Rule Obtained from users when gathering requirement, and are used to determine cardinality
Cardinality Expresses the minimum and maximum number of entity occurrences associated with one occurrence of a related entity.
Cascading Style Sheet CSS is normally the standard way to define the presentation of HTML pages. CSS controls the layout of the page features such as the color, font, size, and the complete layout and is considered more efficient than HTML.
Chain A list of data items strung together by means of a pointer.
Entity Integrity Requires that every table have a primary key; neither the primary key, nor any part of it, can contain null values.
CODASYL A network data model developed by the Conference on Data System Languages, Database Task Group.
Compaction Reduces the number of bits In data without effecting the informational content.
Conceptual Design Description as part of the design process of how a new product will work and meet its performance requirements.
Conceptual Model The overall logical structure of a database (often referred to as conceptual schema) which is independent of any software or data storage structure.
Concurrency In data base systems, refers to the number of run units actively sharing the DBMS facilities.
Data Item The smallest unit of data that has meaning in describing information, the smallest unit of named data.
Database Administrator (DBA) One or more individuals, possibly aided by a staff, who manage an organization's database resource.
Database A collection of interrelated data stored together with controlled redundancy to serve one or more applications; the data are stored so that they are independent of the programs which use them.
Database Design The process of developing a database structure from user requirements.
Data Description (Definition) Language (DDL) A language for describing data. In some cases, software uses DDL only for logical data, or only for physical data, or both.
Database Design Methodology A collection of techniques and tools employed within an organizational framework that can be applied consistently to successive database development projects.
Database Management System (DBMS) The collection of software required for using a database, which presents multiple views of the data to the users and programmers.
Data Independence The property of being able to change the overall logical or physical structure of the data without changing an application program view of that data
Data Model A logical representation of the data structure that forms a database. It should represent the inherent properties of the data independent of software, hardware or machine performance implications. These representations are independent of the class of software that will be used for implementation.
Database link A database link is a pointer that defines a one-way communication path from an Oracle database server to another database server. A database link connection allows local users to access data on a remote database.
Dimension A dimension is a schema object that defines hierarchical relationships between pairs of columns or column sets. A hierarchical relationship is a functional dependency from one level of a hierarchy to the next level in the hierarchy. A dimension is a container of logical relationships between columns and does not have any data storage assigned to it.
Encryption The process of encoding/decoding when transferring data to and from the data base.
Entity A person, place, thing or concept that interests an organization. An entity is something about which data is stored. An entity has various attributes which can be recorded, e.g., COLOR, SIZE, etc.
Hierarchical A tree structure where some records are subordinate to others.
Identifying Relationship The primary key contains the foreign key; indicated in an ERD by the solid line.
Implementation Design A database design activity that involves transforming and refining a conceptual schema into a schema that can be implemented through a database management system.
Key The data item which is used to address or identify a record.
Logical Database Design A description of the structure of logical relationships among the data elements of the system being designed.
Network A structure in which a detail record can have more than one master record.
Orphan Record A record whose foreign key value is not found in the corresponding entity (the entity where the primary key is located)
Overflow An area of placement assigned to a record which for some reason cannot be stored in its home address (i.e. logically assigned address).
Materialized view A materialized view is a database object that provides indirect access to table data by storing the results of a query in a separate schema object. A materialized view contains the rows resulting from a query against one or more base tables, views, and/or other materialized views.
Package Arrangement or collection of procedures and functions into logical groupings.
Packing Density The number of records stored in a bucket compared to the number that could be stored.
Physical Database A database in the form in which it is stored on the storage media, including pointers or other means of interconnecting it. Multiple logical databases may be derived from one or more physical databases.
Procedure A particular course or mode of action, or a subfunction.
Relational Pertaining to a database in normalized, two-dimensional flat form. The DBMS recombines data elements giving different relations or greater flexibility.
Real Time Application or processing in which response to input is fast enough to affect subsequent Input (i.e. terminal dialogues on interactive systems).
Response Time Total time between an instruction being given to access particular data, and that data being available (seek time + read or write time).
Schema A map of the overall logical structure of the database covering all data item and record types. The global logical view of the data.
Servicewide Data Dictionary The authoritative source for the Service's standard data names, definitions and codes.
Sets In CODASYL, refers to a collection of record types e.g., an owner type if defined with one of its member types.
Sequence Sequence numbers are Oracle integers of up to 38 digits defined in the database. The sequence generator provides a unique sequential series of numbers.
Simulation To represent the functioning of one system by another.
Simple Object Access Protocol A messaging protocol for exchanging structured information via web services in a computer network.
Sub-schema A map of a programmer's view of data used. It is derived from the schema.
Task The lowest level of work that requires, on a repetitive basis, a unique set of data. A unique unit of work consisting of a set of sequential steps all directed toward a common goal and all using and/or creating a common set of data.
Transfer Time Time taken to move data between a direct access device and the central processor.
Volatile File A file with high rate of additions and deletions.
Tree Structure A hierarchy of groups of data such as: 1) The highest level in the hierarchy has only one group called a "root".
2) All groups except the root are related to one, and only one group on a higher level than themselves.
World Wide Web Consortium The primary international standards organization for the World Wide Web which was founded in 1994.

Exhibit 2.5.13-12

IRS Enterprise Life Cycle (ELC) Data Model Compliance Standards

≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

  • ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

  • ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

  • ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

  • ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

  • ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

  • ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

Exhibit 2.5.13-13

Relational Database Design Constancy Rules - Types of Data Storing in Table

≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡"≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡" ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡"≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡" ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡"≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡" ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡
≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡ ≡

Exhibit 2.5.13-14

FIPS 127-2 Database Construct Sizing

Sizing for Database Constructs FIPS Example of Oracle Compliance Notes
1. Length of an identifier (in bytes) 18 30

Note:

1. - The numbers of SET clauses in an UPDATE.
2. Length of CHARACTER datatype (in bytes) 240 2000

Note:

2. - The FIPS PUB defines the length of a collection of columns to be the sum of: twice the number of columns, the length of each character column in bytes, decimal precision plus 1 of each exact numeric column, binary precision divided by 4 plus 1 of each approximate numeric column.
3. Decimal precision of NUMERIC datatype 15 38

Note:

3 - The Oracle limit for the maximum row length is based on the maximum length of a row containing a LONG value of length 2 gigabytes and 999 VARCHAR2 values, each of length 4000 bytes: 2(254) + 231 + (999(4000)).
4. Decimal precision of DECIMAL datatype 15 38

Note:

4 - The Oracle limit for a UNIQUE key is half the size of an Oracle data block (specified by the initialization parameter DB_BLOCK_SIZE) minus some overhead.
5. Decimal precision of SMALLINT datatype 4 38

Note:

5 - Oracle places no limit on the number of columns in a GROUP BY clause or the number of sort specifications in an ORDER BY clause. However, the sum of the sizes of all the expressions in either a GROUP BY clause, or an ORDER BY clause is limited to the size of an Oracle data block (specified by the initialization parameter DB_BLOCK_SIZE) minus some overhead.
6. Binary precision of FLOAT datatype 20 126

Note:

6 - The Oracle limit for the number of cursors simultaneously opened is specified by the initialization parameter OPEN_CURSORS. The maximum value of this parameter depends on the memory available on your operating system, and exceeds 100 in all cases.
7. Binary precision of REAL datatype 20 63 Left blank intentionally
8. Binary precision of DOUBLE PRECISION datatype 30 126 Left blank intentionally
9. Columns in a table 100 1000 Left blank intentionally
10. Values in an INSERT statement 100 1000 Left blank intentionally
11. SET clauses in an UPDATE statement (see Note 1) 20 1000 Left blank intentionally
13. Length of a row (see Note 2 and Note 3) 2,000 2,000,000 Left blank intentionally
14. Columns in a UNIQUE constraint 6 32 Left blank intentionally
15 Length of a UNIQUE constraint (see Note 2) 120 (see Note 4) Left blank intentionally
16 Length of a foreign key column list (see Note 2) 120 (see Note 4) Left blank intentionally
17. Columns in a GROUP BY clause 6 255 (see Note 5) Left blank intentionally
18. Length of GROUP BY column list 120 (see Note 5) Left blank intentionally
19. Columns in a referential integrity constraint 6 32 Left blank intentionally
20. Tables referenced in a SQL statement 15 No limit Left blank intentionally
21. Cursors simultaneously open 10 (see Note 6) Left blank intentionally
22. Items in a select list 100 1000 Left blank intentionally