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.

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

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

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.

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.

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.

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.

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.

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.

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.

Ask what additional functions, if any, are performed.

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

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

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

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.

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

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.

A task must be performed within one functional area.

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

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.

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.

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.

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.

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.

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.

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).

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 and request alternate items)
• 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

Security Constraints: Rules where security levels are assigned to data. They can be used as integrity rules, derivation rules, or as schema rules (data dependencies).

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

Data Integrity: Overall accuracy, completeness, and consistency of data which refers to data safety in regard to regulatory compliance and security. The major importance of data integrity is protection against data loss or a data leak. In order to keep data safe from outside forces with malicious intent, you must ensure that internal users are handling data correctly. By implementing the appropriate data validation and error checking sensitive data is never miscategorized or stored incorrectly.

Domain Integrity: A set of acceptable values that a column is allowed to contain. This can include constraints and other measures that limit the format, type, and amount of data entered for processes that ensure the accuracy of each piece of data.

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:

Response

Cyclic processing time requirements

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, or auditor. 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.

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

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

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.

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.

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

Use an informal procedure for identifying relationships by pairing each entity in the local view with all other entities contained in that view.

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.

Determine which relationships are most significant and which are redundant. This can be done only with a detailed understanding of the design perspective under consideration.

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

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

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.

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.

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".

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.

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.

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.

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:

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

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

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

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

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.

State what attributes are used to describe each entity.

Define the perceived access path for each entity.

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

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

Developers must be proficient at data techniques, and data professionals must learn modern development technologies and skills for the purpose of having a better understanding of each other requirements.

One challenge of agile adoption for databases is unlike applications it has a current state that must always be managed to maintain the integrity of the data. However, despite this challenge development teams need to implement the proper tools and processes for automating database development and release tasks. If the entire software stack including the database is evolving incrementally, flexibly, and rapidly the organization could receive a better return on investments and shorter response time for customer requirements.

Multipurpose Column - If a column is being used for several purposes, i.e., used for someone’s birth date as a customer or start date if that person is an employee.

Redundant Data - Redundant data is a serious problem because the same data is stored in numerous places, and causes inconsistencies with the outcome of erroneous information.

Tables with Too Many Columns - When a table has many columns, it indicates that the table lacks cohesion— it is trying to store data from numerous entities. For example the Employee table containing columns to store three different addresses or several phone numbers could require the structure to be normalized by adding "Address" and "Phone " tables.

Table with Too Many Rows - Large tables are suggestive of performance problems because it becomes time- consuming to search a table with thousands of rows. The solution is to split the table vertically by moving some columns into another table.

Smart Columns - A smart column is when columns different positions within the data represent different concepts. For example if the first four digits of the personnel ID indicate the personnel’s home branch, then personnel ID is smart column because you can parse it to discover more granular information.

Fear of Change - "Fear of Change" can stop developers from taking action, lead to "Resistance of Change" and could result in unmanageable systems. For example, the fear of unwanted side effects when cleaning up functioning components.

Data professionals must be knowledgeable with techniques that enable them to work in an evolutionary way. Evolutionary database development techniques are as follows:
• Database Refactoring - Develop an existing database schema a small portion at a time to improve the quality of its design without changing the semantics
• Evolutionary data modeling - Model the data aspects of a system iteratively and incrementally as with other aspects of a system, to ensure that the database schema evolves in step with the application code
• Database Regression Testing - Ensure the database schema works, if it does not roll back the changes
• Configuration Management of Database Artifacts - Database models, database tests, test data, etc. must be managed like the Enterprise Life Cycle documentation
• Developer Sandboxes - When possible, provide separate sandboxes for developers; they need their own working environment where they can modify the portion of the system they are building, and get it working

Make smaller changes to your database because It is easier and less risky to apply, and if something is broken the change can be identified and rolled back faster.

Identify individual refactorings distinctly because refactorings normally build upon each other and you need to ensure they are applied in the correct order and add any dependencies between them.

When in a multi-application environment in which multiple project teams may be applying refactoring to the same database schema, identify which team produced a refactoring i.e., team 1 could have refactorings identification ((ID) 01 - 07). See Version Identification Strategies in Figure 2.5.13-18.

Implement a large change by creating many small increments. For example, when applying a consistent naming strategy throughout your database consider splitting an existing table into two.

Create a database configuration table for identifying the current schema version of the database to enable you to update the schema. For example, if you identify the refactorings using a date/timestamp strategy you must identify the current schema version the same way.

Data processing needs and priorities

Largest percentage of the total business workload

Critical response time requirement

Data access requirements and/or complex logic

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

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

Security requirements

Performance that is measurable and monitored

Aggregate: Operates on a collection of values, but returns a single value.

Ranking: Returns a ranking value for each partitioning row.

Rowset: Returns an object that can be used in a place of table reference in the SQL statement.

Scalar: Operates on a single value and returns a single value.

Exact Numeric Types:

Approximate Numeric: Uses Read and float.

Date and time: Uses DateTime, Date, Datetime2, Dateoffset, Smalldatetime, Time.

Character strings: Uses Char, Varchar, Text.

Unicode character strings: Uses Nchar, Nvarchar, Ntext.

Binary strings: Uses Binary, image and varbinary.

Other data types: Pertain to Cursor, Hierarchyid, sql_variant, Table, Rowversion, Uniqueidentifier, XML, Spatial and Geography.

For additional information see, https://www.sqlshack.com/an-overview-of-sql-server-data-types/.

Use only T-SQL syntax for supporting SQL Server.

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

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

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

Key-value paired databases: Data is stored as key/value pair hash table where each key is unique, and is designed with the capability of handling heavy data loads. Key value stores help the developer to store schema-less data. See example figure below:

Column-Oriented Databases: Allows you to store the columns of data instead of rows, and are normally used to manage data warehouses, business intelligence, and CRM.

Document-Oriented databases: see Table IRM 2.5.13.7.3.

Graph-Oriented Database: A type of database used to represent data in the form of a graph with three components as the following:
• Nodes (or vertices): This is defined as the nouns in the database, they store data about the people, place and things.
• Edges (or relationships): Defined as the verbs in the database, they stored data about the actions that are done between nodes.
• Properties: Used to model the data
• Labels: Used to tag a group of related notes

Data Storage must have very good I/O ability (like SSD) to support MongoDB. Write and read performance of MongoDB has significant dependency on the type of storage disk.

Good query performance: Create indexes to improve query performance; however, be cautions of too many indexes that are not being used because it could result in slower performance.
• Best Strategy for Designing Indexes: Profile a variety of index configurations with data sets similar to the ones you’ll be running in production to see which configurations perform best. for more indexing information see https://www.mongodb.com/docs/manual/applications/indexes.

Use a reasonable amount of RAM: As applicable, select a platform that has more RAM than your working data set size. The working set is the set of data and indexes accessed during normal operations. Being able to keep the working set in memory is very important for cluster performance.

Consider using Sharding to increase the amount of available RAM in a cluster.

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

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.

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.

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.