The goal of the Database Standards is to document new databases and applications in a consistent manner across platforms. These methods and requirements should provide a template for better documentation and make it easier to identify objects during daily maintenance and crisis events.

Below is the list of deliverables and standards for any new database development:

1. The application and database documentation must be listed in the Global Index Notebook.
2. There must be an Entity Relationship diagram drawn with the entity and unique identifiers listed.
3. Each entity must have a definition written in paragraph form.
4. Each table must have a definition that may be identical to the corresponding entity.
5. Each column in a table must have a definition, a descriptor, a size, a domain, and a datatype.
6. The column abbreviations used must be in the Data Management Abbreviation list.
7. There must be a Physical Diagram of the database that includes table and column names.
8. The data stored in a column must correspond to the column definition and domain.
9. The database must be backed up each workday (Monday-Friday).
10. All online updates to data in tables must be done via the use of stored procedures. (see Security Policy below)
11. All customer and developer staff userids for ORACLE and IDMS databases on the OS/390 must be authenticated by RACF.
12. All databases must have separate development, test, and production environments.

The paragraphs below give more detail about how we name entities, tables, and columns. There is additional information on security and backup. If you need JCL for any of the utilities listed below check out the share libraries.

   ORACLE - DB.ORCL.SHRLIB.CNTL
   IDMS - DB.R120.SHRLIB.CNTL

Additional documentation about IDMS entities can be found in DB.R120.TEXT(NAMING). It includes naming standards for segments, dbnames, dictionaries, and DMCLs.

13.1 Record and Table Names

Goal: To have record and table names that are consistent, clear, and descriptive of the data maintained in the record or table. A record or table is a data representation of an entity with an existence in the real world. Buildings, employees, stock items, etc. have an existence, and the university has a need to keep data about each occurrence of those entities. A record or table name should indicate what entity that record or table contains data about.

Process:

• 1.1. Name the table (the word table will be used to mean either record or table) to indicate the entity that is represented by that table. A table containing data about a building should be called Building. A table containing data about an employee should be called Employee. A table containing data about a stock item should be called Stock-Item.
• Sometimes there is a need for more than one table to contain data about an entity. For example, we may have a table containing data about an employee's personnel data, a table with payroll data, and a table with historical data. The table name should be expanded. In the above example, we would have the table names: Employee-Pers, Employee-Pay, and Employee-Hist.
• All table names will be singular. A table name for a table representing buildings will be Building. A table name for a table representing employees will be Employee.
• Because a table may be used by more than one application, there will not be a reference to an application in a table name. An FRS account table should always have the same name no matter what application needs to use the data in that table.
• A column or element name may not be a table name. An element or column represents an attribute of an entity, not the entity itself. Employee name is an attribute of employee. The Employee table would have a column called Employee-Name, or properly abbreviated, EMP-NM.
• In a network IDMS database, the record name will have an IDMS internal record ID appended as a suffix to the record type. The record name for university buildings will be Building-303. These suffix numbers are not necessary in databases defined as relational in IDMS, Sybase or Oracle.

13.2 Data Element and Column Names

Goal: To have data element and table column names that are consistent, clear, and descriptive of the data maintained in the element or column. An element or column should have the same name every place it appears in all databases across all platforms. Customers and programmers should not have to guess what name is used in a specific database on a specific platform. Additionally, standard names will improve productivity, facilitate sharing of programmer resource among groups, and provide coherent table joins.

Process:

• 2.1. Name the column (this will mean element or column) based on what the data is, not how it is used. A column may be used different ways but it should have only one name. Example: Suppose we have an indicator that has two values: 0 means USA, and 1 means foreign or not USA. One program may use the indicator to summarize fields by adding to one counter if the indicator is 0 and to another counter if it is 1. Another program may print only records that are non-USA or value 1. If the column is named for its use, we could have two names for the same column; Summary-Indicator and Print-Indicator. And if there are other uses such as purge, selection etc., there could be many other names for the same column. Furthermore, if a different program does print selection on some other column we would have two columns named Print-Indicator with different values. An appropriate column name could be USA_IND (IND for indicator).
• 2.2. A domain is a of set of values for a column. The domain of the column must be identified, named and included in the column name. Explanation: the set of all Organization Numbers is a domain and thus should be part of the (or possibly the entire) column name, Organization-Number (abbreviated to ORG_NUM). There are times when a domain is so broad it is not useful such as on-hand quantity of a stock item. The values could be from zero to nearly infinity. In this case, the column does not have a domain included in the name, Stock-Item-On-Hand-Quantity (abbreviated STK-ITM-ON-HAND-QTY).
• 2.3. If a domain name appears twice in a table or record, then the role it plays must be identified and added to the column name. Example: an employee works in AIS but is paid by the Cashiers office. The employee table would need to show two organization numbers, possibly called Organization-Number-Works and Organization-Number-Paid. Note this is not in conflict with item #2.1 above. A role differentiates one column from another column when the same domain is represented twice or more in a table. A role does not represent how a column is used. The organization number may still be used for print selection criteria, summary criteria etc. Another example is an Employee table with an employee's person ID and the person ID of the employee's boss; PERS_ID_EMP and PERS_ID_BOSS. Both columns have the same domain.
• 2.4. The column name must be clear without the context of its membership in a table. An employee name should be called Employee-Name (EMP_NM); an organization name called Organization-Name (ORG_NM). Simply calling each column Name is not sufficient. Some software products cannot handle two columns with the same name. Furthermore, two columns called Name would seem to indicate the same domain when in fact, we have two different domains; that is, the entire set of employee names and the entire set of organization names.
• 2.5. Each column must be categorized with a descriptor (class code) identifying the type of data it contains. Initially developed by IBM, these descriptors have now been embraced, with some individual modifications, as nearly an industry wide standard. Normally, the descriptor will be the last component of the column name. There are two exceptions to this location described below in paragraph 2.9.
  
  

  Descriptor	Meaning	Description
  ADDR	Address	Location of a person or building, or a person's email address.
  AMT	Amount	Numeric representation of currency or other amount (e.g. scores, points)
  CD	Code	Alphanumeric code which can be translated or has assigned meaning. Has a distinct set of values (e.g., customer_type_cd). Often used as a primary key on a reference table.
  CNT	Count	Represents a count of a number of occurrences. Typically used in summary tables.
  DT	Date	Represents a date.
  IND	Boolean Indicator	Indicates a logical condition which has only two values (e.g., "Y" or "N", 1 or O). May also contain NULL values. (e.g., return_item_ind)
  ID	Code Identifier	Alphanumeric code used to represent some type of entity. Generally has no built in meaning. Could also the ID reference from an external system. Often used as the primary key for a transactional table. (e.g., sales_order_id)
  NM	Name	Alphanumeric word or phrase that is usually a proper noun (e.g., last_nm)
  NUM	Number	A number which describes an attribute but is not a quantity or the identifier for the table. For example, line item numbers associated with line item tables.
  PCT	Percent	Numeric representation of percent.
  RT	Rate	Numeric values used for factors and multipliers.
  TXT	Text Descriptions	Free-form textual descriptions.

• 2.6. Abbreviations
  • 2.6.1 Abbreviations used at A.I.S. will be created according to the rules defined here, with one exception. Enhancements and interfaces to vendor purchased software can use the vendor's naming standards, rather than these standards.
  • 2.6.2 Abbreviations may be used, but are not required in naming data elements/columns.
  • 2.6.3 When used, the abbreviation or acronym must be in or added to the standard list of acronym & abbreviations. Standard abbreviations and acronyms (widely used, commonly known) can be used even if the word is not normally eligible for abbreviating given the rules below, e.g., months of the year may be abbreviated at the discretion of the designer, as long as they are the standard abbreviations for the months.
  • 2.6.4. Abbreviations and Acronyms are defined based on the following principles:
    • 2.6.4.1 All abbreviations & acronyms will be unique.
    • 2.6.4.2 Abbreviations will be long enough to clearly indicate the unique meaning.
    • 2.6.4.3 One abbreviation will be used for all forms of a single word. Example: Admin = Administrator, Administration, Administrative
    • 2.6.4.4 Try not to create abbreviations that are themselves English words. For example, do not use CLASS as an abbreviation for CLASSIFICATION.
    • 2.6.4.5. It is best to have an abbreviation begin with the same letter as the word being abbreviated. For example, use EXCPT not XCPT for EXCEPTION.
    • 2.6.4.6 No word may be abbreviated to less than 3 characters, unless it is a widely used abbreviation.
    • 2.6.4.7. No word may be its own abbreviation.
    • 2.6.4.8. Words four characters in length should generally not be abbreviated. Existing four character word abbreviations are to be used only when necessary to fit a column name within the length restrictions of a given vendor's database (Oracle, MS SQL, etc.).
  • 2.6.5. A column may have none, some or all of its parts abbreviated. The DBA and developer are to determine how and when abbreviations are to be used for a given application database for new data elements. Once named a data element will be named consistently across all applications.
  • 2.6.6. If a word needs abbreviating, submit a request to the DBA assigned to your project. Data Warehouse staff will also assign abbreviations for data warehouse tables as needed. All abbreviations will be documented by the person who made them (who, date, reason for chosen abbreviation). New abbreviations will be broadcast to all Database Management, and Data Warehouse staff. Initial suggestions for abbreviations will become standards after 3 business days.
  • 2.6.7 In the event that the desired name can not fit within the length restrictions of the database in question, several options exist:
    • 2.6.7.1. Choose a different wording.
    • 2.6.7.2. Eliminate words that are not critical.
  • 2.6.8 Resources for Abbreviations & Acronyms. A resource for finding acronyms is:
    
    http://www.ucc.ie/cgi-bin/uncgi/acronym
    
    Links for viewing abbreviations in use at other universities:
    
    http://www.adpc.purdue.edu/DataMgmt/DA/Stds/abbrev%20A_B.htm
    http://www.oit.umd.edu/cgi-bin/acrolist.cgi
• 2.7. Participation of an element in a record is shown by a suffix. In the case of an IDMS element, the suffix is the IDMS internal record number. For an Oracle table the element will have a table name as a qualifier prefix. Examples:

     IDMS ORG-NUM-469
     Oracle ORG.ORG_NUM

• 2.8 The syntax for an element/column name is as follows: Domain+Descriptor+Role(If applicable)+Suffix(If applicable). The role may be placed before the domain. For example, contact_person_id and person_id_contact are both acceptable. It is not acceptable to split the domain, e.g., person_contact_id.
• 2.9. Exceptions:
  • 2.9.1 If a column exists in two different lengths, such as FRS account number, an integer will be appended to the element name following the descriptor to show the length. Example: ACCT-NUM-6 and ACCT-NUM-10. (Note: This method of naming is used sparingly since a change to the column length could translate to hours of work to replace code in programs.)
  • 2.9.2 If a domain ends with a descriptor, e.g., ORGANIZATION_NAME, you may omit the descriptor, since it is included in the column name.

13.3 Oracle Dataset Names for Oracle nondatabase files

• 3.1. Datasets we created to assist systems with installs:

     DB.ORCL.INSTALL.DATA
     DB.ORCL.INSTALL.CNTL

• 3.2. Datasets with SQL code not requiring modification, SQL templates, etc.:

     DB.ORCL.DBG.DATA

• 3.3. Datasets for SQL code for specific databases:

     DB.ORxn.schm.DATA where x = D for development, T = test, P = production
     n = 1,2,3,etc., schm = 4 character schema owner

• 3.4. Datasets containing database structures exported from an

     AIS Oracle database:
     DB.ORxn.schm.EXPORT.Dyymmdd
     See #3 for naming explanation and
     yy is bytes 3-4 of century, mm = month, dd=day of month

• 3.5. Datasets containing database structures from vendor:

     DB.ORCL.appname.EXP.DATA.Dyymmdd
     DB.ORCL.appname.EXP.DEFN.Dyymmdd
     where appname is the application name such as COEUS30,
     DATA = signifies that export file contains only a data extract,
     DEFN = signifies that export file contains only data definitions

• 3.6. PDS for sharing SQL code with rest of AIS:

     DB.ORCL.SHRLIB.CNTL

• 3.7. PDS for each of us to do our own work:

  DB.int.ORxn.DATA - SQL code
  DB.int.ORxn.CNTL - JCL
  where Int = your 3 character initials
  ORxn (see #3 above for details)

13.4 Oracle dataset naming conventions

Notes:

1. The tablespace name would be sss+TBS+nnn: OSPTBS001.
2. The ddname for tablespace files is the 3rd level of the dataset name.
3. The ddname for nontablespace files is the default name assigned during the Instance setup process.

5. Security

All userids that access databases on the UNIX or the OS/390 servers must be created by the Function Access Control System, FACS. The requests can be made via the WEB at http://www.unc.edu/ais/systems/security/

All customer and developer staff userids that access ORACLE and IDMS databases on the OS/390 server must be authenticated by RACF.

PL/SQL is not required to update Oracle tables when the access is through a proxy user not exposed to customers.

Specific instances covered by this policy would be:

1. Access via server-side datasources, EJBs, and O/R mappers,
2. Access via mainframe batch, and
3. Access via CICS Oracle adapter configured to a proxy user or transaction ID.

The general intent of this policy change is to allow the use of developer productivity tools to generate access to Oracle tables and abstract that access away from customers. This policy does not relieve the developer from writing efficient code. Poorly performing applications may be required to use PL/SQL as the means to gain efficiency.

This policy does not require that all database access use dynamic SQL. Some applications may wish to use procedures, and packages in order to limit update access to their databases. That is entirely acceptable.

6. Backup and Recovery Issues, Suggestions, and AIS methods

Systems does nightly backups of IDMS and ORACLE OS/390 databases. The backups are kept for two weeks worth of backup cycles. (Reminder: Nightly backups are done Monday through Friday. This means that weekend work will not be included in an official backup until Monday night. Therefore, please keep copies of your weekend files after you have finished. This will allow you to recover on Monday midday if a database crashes and must go back to a Friday night backup. )

Data Management keeps five cycles of journals for IDMS CVs and Oracle Instances.

It cannot be stressed enough that it is best to have a backup strategy worked out before you need it.

IDMS files updated through the CV (central version/IDMS engine) have changes logged in the IDMS JOURNALS. If you are unclear about whether you are running through the CV, check MESSAGES in your job on IOF and look for SYSCTL. You should see DB.CV12.SYSCTL (test environment), DB.CV11.SYSCTL (production environment), or DB.CV16.SYSCTL (retrieval query CV) if you are running through the Central Version.

IDMS files can be updated locally provided you keep your own backup files for recovery, you have RACF authority, and you understand which files must be recovered together if there is a problem. Local updating means that the CV (central version) does not journal the changes you make to the files. When a local batch job abends, the IDMS files are left locked. Then you must restore all of the files which must be recovered together to the prior backup and start over.

Oracle has a utility called EXPORT/IMPORT. If you EXPORT a database you can capture the complete database structure, security, and data. Then if you have problems, you can IMPORT the information back to the database with the EXPORT file. These two utilities go together and only read files produced by the utility. (A word of caution: IMPORTs can be very slow. Some sites with large databases do not rely on IMPORTs for regular processing.) You can export from one database and import to another database in another instance. You can import individual tables within a larger EXPORT.

If you need JCL for any of the utilities listed above, check out the share libraries.
   ORACLE - DB.ORCL.SHRLIB.CNTL
   IDMS - DB.R120.SHRLIB.CNTL

7. Standard Instance Environments: Development, Test, and Production

All databases must have separate development, test, and production environments. In-house IDMS applications use DEVDICT, TSTDICT, and PRDDICT. ORACLE Instances on the OS/390 have ORD1 (ORDn), ORT1 (ORTn), and ORP1 (ORPn) which represent three instances. The third letter of the instance name corresponds to D for development, T for test, and P for production.

8. Stored Procedure, Trigger, Function, etc. Setup/Design/Implementation recommendations Including standard return codes, operation, design, coding, and documentation Module Specifications

There are no specific standards for these items, yet. They will be added as development progresses.

9. Recommended Ways to Load Database

If you need JCL for any of the utilities listed below, check out the share libraries.
   ORACLE - DB.ORCL.SHRLIB.CNTL
   IDMS - DB.R120.SHRLIB.CNTL

The IDMS load utility to load an empty database is FASTLOAD. See the IDMS UTILITIES manual for more details.

Oracle has a batch loader called SQL LOADER. Sometimes, it is abbreviated to SQLLDR. It is fast and widely used. Samples of the JCL can be found in DB.ORCL.SHRLIB.CNTL(SQLLDR). A template for the LOADPARM referenced can be found in DB.ORCL.SHRLIB.CNTL(LOADPARM).

If you need to load an Oracle Database from another Oracle Database, see the EXPORT/IMPORT description in #6 above.

10. System Test plan

A System Test plan should be scheduled to allow enough time to test the new database and application before it is put in production. This test will allow customers to use the application before it is production critical. This testing should provide the information the database and programming staff need to know that the security pieces are in place for the customers' access to the database.

11. Oracle Database Design Documentation

Currently (10/27/1999), the in-house Oracle database designs done by Data Management are documented in Embarcadero's ERSTUDIO. It has the capability to create the diagrams needed with textual definition for entities, tables and columns. It can produce the database documentation in an HTML format.