std 07092501.3.1 logicaldatamodeling

7/27/2019 STD 07092501.3.1 LogicalDataModeling

1/20

STD-07092501.3.1

LOGICAL DATA MODEL

STANDARDS & GUIDELINES

Version 3.1

September 25, 2007


2/20

REVISION HISTORY

Version Date Description

1.0 10/02/2003 Created by Charlotte Gregg as new Standards document for ERD

1.3 11/01/2003Entity Relationship Diagram (ERD) Standards, version 1.3 approved by

John Willmott, BIS Bureau Chief.

2.0 10/21/2005

Ownership of document transferred to Enterprise Data & InformationAdministration. Added a paragraph titled Attribute Class Word

Assignment. Added an appendix titled Appendix A Class

Word/Abbreviations. Attribute Name & Entity Name paragraph wasupdated. Revised Entity Name paragraph to address Class Words.

Enhanced discussion in Attribute Name section to address Prime &

Qualifier words. Applied final changes to Class Word table. Conducted

minor editorial changes.

Entity Relationship Diagram (ERD) Standards, version 2.0 approved byJohn Willmott, CIO

8/18/2006

Major rewrite and re-branding of the former ERD Standards to LDM

Standards. Includes reorganization of layout, breaking out into a separatedocument (Logical Data Model Techniques) how to perform certain

procedures with Oracle Designer & incorporating some standards that

were formerly in the SMD standards document, etc. Also includes revisedstandards relative to Primary Key, etc.

4/24/2007 Rewrite to include the roles of Data Administration defined by the ISDM.

5/1/2007Added standards 240, 310, 320, 330 as per Donna Gorton.Removed standard 380 to be relocated in the Physical Data Model

Standards

3.0 6/14/2007

Updated the document with above mentioned changes after approved

Version 2.0. Formatting changes and other additional changes were also

made.

Logical Data Model Standards and Guidelines, version 3.0 approved by

John Willmott, CIO

3.1 9/25/2007Approved standards revised to apply current MRA numbers and to

reference version 2.0.

8/12/2013 LDM Standards & Guidelines Page 2 of 20


3/20

TABLE OF CONTENTS

STD-07092501.3.1..........................................................................................................................1

REVISION HISTORY....................................................................................................................2

TABLE OF CONTENTS................................................................................................................3

INTRODUCTION...........................................................................................................................5

BY LOGICAL DATA MODEL (LDM) WE MEAN... .................................................................5

GRANDFATHER CLAUSE...........................................................................................................5

STANDARDS ................................................................................................................................5

GENERAL STANDARDS..................................................................................................6Modeling Tool Standard # 100................................................................................6

Third Normal Form Standard # 110.........................................................................6

Subject Area Data Standard # 120...........................................................................6

ENTITIES............................................................................................................................6

Entity Name Standard # 204....................................................................................6Entity Short Name Standard # 208..........................................................................7

Entity Plural Standard # 210....................................................................................8

Entity Description Standard # 214...........................................................................9Code Entity Standard # 218...................................................................................10

Synonym Standard # 250.......................................................................................11

ATTRIBUTES ..................................................................................................................11

Atomic Attribute Standard # 300...........................................................................11

Attribute Name Standard # 304.............................................................................12Attribute Class Word Assignment Standard # 308................................................12

Attribute Indicator Standard # 310........................................................................12

Attribute Sequencing Standard # 312....................................................................13

Attribute Format Standard # 316...........................................................................13Attribute Domain Standard # 320..........................................................................14

Primary Unique Identifier Standard # 324.............................................................14

Attribute Comment Standard # 328.......................................................................15Attribute Description Standard # 332....................................................................16

RELATIONSHIPS.............................................................................................................17

Relationship Standard # 400..................................................................................17



4/20

ENTITY RELATIONSHIP DIAGRAM LAYOUT CONSIDERATIONS......................18

Entity Placement Standard # 700...........................................................................18

Relationship Placement Standard # 710................................................................18Diagram Font Standard # 720................................................................................19

APPENDIX I ...............................................................................................................................20REFERENCE / BIBLIOGRAPHY........................................................................20



5/20

INTRODUCTION

Why study Latin?

Perhaps you remember the WHY? title from some of your past text books. Now that we

have your attention. Why should we have a Logical Data Model (LDM) standards document?What is the purpose? The purpose of this document is to provide guidance to those tasked with

preparing a Logical Data Model for their application project. The purpose of this document is to

share with you a best practice the result of many years of work by a great many practitioners.The purpose of this document is NOT to set road-blocks or to force delays of your work.

BY LOGICAL DATA MODEL (LDM) WE MEAN...

By the term LDM we mean the Entity Relationship Diagram (ERD) and all the related

documentation data dictionary, business rules, etc.

GRANDFATHER CLAUSE

Existing legacy applications that are grandfathered with respect to changes in the standards will

be brought into compliance over time as enhancement releases are fielded for a particular

application. Maintenance releases are specifically waived with respect to a standards review.

STANDARDS

These standards are based on the premise that an LDM is created and approved prior totransformation to a Physical Data Model (PDM) for all new application releases as well as for

enhancement releases for existing applications. For easy reference, the individual standards are

numbered and guidelines are in italics.



6/20

GENERAL STANDARDS

Modeling Tool Standard # 100

Conceptual, Logical and Physical Models must be developed using the Oracle Designer tool;

both DEP and external development teams must utilize Oracle Designer and the approved

DEP Designer repository. The use of any other modeling tool/repository is not authorized.

Third Normal Form Standard # 110

The ERD component of the LDM must be in at least third normal form (Boyce-Codd, fourth,and fifth normal form are also acceptable).

Subject Area Data Standard # 120

The established Location Data Standards will be adhered to for geospatial objects.

Adherence to these supplementary standards is mandatory. Models will be sent to

Geographic Information Systems (GIS) for review.

ENTITIES

Entity Name Standard # 204

An entity name must include an Entity Class Word.

Guideline: Refer to See How to Create an Entity and How to Create Business Names

in Oracle Designer Modeling Techniques if you are unfamiliar with Oracle Designer.

An entity name must be a maximum of 30 characters long.

Guideline: The logical model allows a larger name length so that abbreviations can

be avoided for clarity sake in the business model. The PDM however, is limited by

the Oracle thirty character naming constraint so when longer names are used

truncation will occur.

An Entity name must be made up of one to five words and is followed by a class word.

Only singular nouns and modifiers are to be used for an entity name.



7/20

An entity name must contain only alphabetical characters and spaces (cannot contain

numbers).

An entity name must contain hyphens or underscores between the words if more than oneword is present. Hyphens and underscores allow for greater readability while keeping the

characters of field and table names contiguous. This makes selection of these strings

easier and more accurate when working with code. Also, many Oracle operations willrequire that these entity names be placed in quotes if spaces are present a simple selectstatement will not work without accommodation for the spaces.

An entity name must not contain the name of physicalconstructs, such as view, file,

record, row or table.

An entity name must not contain numeric values.

Also applicable in Standard 2.0- see page 4. Please note that the maximum length in

Standard 2.0 is 27.

Entity Short Name Standard # 208

Guideline: An entity short name is also known as an alias. Oracle Designer uses

the short name in the generation of names for constraints, keys, sequences, etc.

Each entity must have an entity short name.

Guideline: See How to create an Entity in Oracle Designer techniques for OracleDesigner Tips.

An entity short name must be a unique combination of up to six letters

An entity short name must not be an Oracle Reserved Word, an Entity Class Word, or an

Attribute Class Word.

The entity short name must be unique among all existing DEP enterprise Oracle shortnames.

Guideline: See the ORADEV.BIS_LIB.SHORT_NAMES table for currently reserved

Short Names.

If the one word entity name is six characters or fewer, use the first three letters of the

name as the short name. If that short name is not unique, add one letter of the worduntil it is unique.

If the one word entity name consists of more than six characters, use the first three

letters of theentity nameas the short name.If that short name is not unique, add one

letter of the word, up to the sixth letter, until it is unique.

If the entity name is more than one word, use the first character of each word up tosix characters.



8/20

In the event that a duplicate entity short name exists after applying the above rules,

suffix the short name with the number 1.

In the event that an entity short name is initially six characters and is not unique,

remove the last character and replace with the number 1. Keep incrementing thisnumber until there is no longer a conflict with an existing short name.

Once a candidate entity short name is established the Application Development Team

must contact Apps Admin who will in turn record (reserve) these short names and

confirm they have been reserved. This process changes the short name from acandidate to reserved status.

A candidate entity short name is subject to change until reserved. Since multiple

Application Development Teams must reserve short names, to prevent problems

during development, Data Administration recommends that you submit yourcandidate short names as soon as possible when they are finalized.

Also applicable in Standard 2.0- see page 4.

Entity Plural Standard # 210

Guideline: The entity plural is used to generate the related PDM table name when

the Database Design Transformer (DDT) utility is used. This property is

automatically defaulted but it is recommended that the default plural name beoverridden in those instances where the plural is nonsensical.

An entity plural is mandatory.

Guideline: See How to Create an Entity in Oracle Designer Modeling Techniques

for Oracle Designer tips.

The entity plural must be the proper plural form of the entity name.

The entity plural must consist of spaces and alphabetical characters (cannot contain

numbers), and underscores or hyphens.

The entity plural must not correspond to an entity or attribute class word or reservedword. In General all rules that apply to entity naming (Standard 200) apply to the entity

plural name.

The entity plural must not differ from the entity name except for the plural suffix.

All entity names that end in an entity class word must not have a plural form in thisproperty but the entity name should be repeated.

The entity plural can not exceed 30 characters in length.




9/20

Entity Description Standard # 214

Each Entity must have a detailed Entity Description which clearly indicates its business

purpose and usage within the context of the enterprise, rather than focusing on a

definition that only has meaning to the application or expert user group.

Guideline: It is assumed that the reader does not have direct knowledge of theapplication. Please see the section How to Write Meaningful Business Description

in Oracle Designer Modeling Techniques for more information and examples. See

How to Create an Entity Description in Oracle Designer Modeling Techniques forOracle Designer tips.

An Entity Description must stand alone. For example, a description would not assume

the reader has access to other documentation.

If additional information on business usage is provided, it must be placed in labeled

sections.Guideline: The section labels that can be used are: Business Rule, or Value List. Forexample:

CH Event Review

Consists of the collection of Clearinghouse members that are required to review or

have an interest in the processing of an object subject to Clearinghouse approval.

Business Rule:

- A least one Clearinghouse Event Item must be established for the ClearinghouseEvent which will be the Environmental Interest Clearinghouse member for which

the data was entered (the source).

- Only Clearinghouse Review ID value of "R" can provide a response.- The CH Event Item, upon initial creation, that has a CH Review ID value of "I" will

have the CH Response value set to a default of "No Response".

Descriptions must be grammatically correct and must not contain misspellings.

Avoid acronyms and abbreviations; if unavoidable, the full acronym and/or abbreviation

expansion must be included in the entity description. The acronym or abbreviation

expansion must be provided if used as part of the entity name.

Circular descriptions will not be approved. Entity descriptions must provide information

about the entity that extends beyond what is implied by the entity name.Guideline: An OBJECT OF INTEREST is an object of interest, would be an example

of a circular description.




10/20

Code Entity Standard # 218

The name of a code entity must end in the Entity Class Word of CODE preceded by a

space.

Code entities will either use a surrogate primary key or create the primary key using theattribute that will store the actual code value.

Guideline: (See the Primary Unique Identifier Standard # 370 and How to Create a

Primary Unique Identifier in the Oracle Designer Modeling Techniques.)

A code entity must have BEGIN DATE and END DATE attributes.

The application Architect/Designer must utilize existing code entities.

A list of the current reusable common entities follows:

COORDINATE METHOD CODE

COORD ACCURACY LEVEL CODE

COUNTY CODE

COUNTRY CODE

DATUM CODE

DISTRICT CODE

LAND TYPE CODE

NAIC CODE

NAIC SIC

OFFICE CODE

OFFICE COUNTY CODE

PHONE AREA EXCHANGE CODE

PROXIMITY CODE

SIC CODE

STATE CODE

UNITED STATES CITY CODE

VERIFICATION STATUS CODE

Some combination of the following attributes must be included in all code entities toprovide the most flexible usage of the valid values.



11/20

Guideline: The examples in Table 1 Code Entity Example & Table 2 Code

Entity Attributes portray what a code entity should look like.

SHORT NAME If a surrogate key is used, this is used to hold a shortenedversion of the full name of the code value.

LONG NAME Used to hold the full name expansion of the code value.

DESCRIPTION Used to hold a description of how this code is used by the

business area and how it may be related to other code values or another code entity.

Entity Name Short Name Entity Plural Table Name

LAND USE CODE LUC LAND USE CODES LAND_USE_CODESTable 1 Code Entity Example

Attribute Name Data Type Maximum Length Optional

LAND USE ID Spaces in field namesmake working with code difficult.

Varchar2or Number

As required No

SHORT NAME Varchar2 As required No

LONG NAME Varchar2 As required No

DESCRIPTION Varchar2 As required No

BEGIN DATE Date No

END DATE Date YesTable 2 Code Entity Attributes


Synonym Standard # 250

The synonym attribute from the Designer palette should not be used.

ATTRIBUTES

Atomic Attribute Standard # 300

Attributes are properties of the owning entity and as such must convey a singular atomic fact

about the entity.



12/20

Guideline: For example, the code Report Frequency could have a value of D for

daily or W for weekly. These are atomic facts and cannot further be broken down.

However a code Type Frequency with a value of DSUM meaning daily summaryestablishes two separate facts, one that it is a daily occurrence and two that it is a

summary report. In reality you have two separate facts, the frequency and the type of

report. As such two separate attributes should be utilized.

Attribute Name Standard # 304

All entities in any ERD must contain at least one attribute.

The attribute name must be made up of one to five words followed by one Attribute Class

Word Suffix used to clarify the attribute if the information cannot be obtained from the

attribute name.

Guideline: For example, an attribute name of PROCESS DATE with a data type of

DATE may be either a date with time or a date without time. To clarify the attributethe name should be PROCESS DATE if it is a date without time and PROCESS TS if

it is a date with time.

An attribute name must not exceed 30 characters.

When an attribute name is only one word, it must not be an Oracle Reserved Word.

The attribute name must be singular and contain no hyphens or underscores.

Also applicable in Standard 2.0- see page 7. Please note that the maximum length in

Standard 2.0 is 27.

Attribute Class Word Assignment Standard # 308

If used, a class word suffix must use the corresponding abbreviation found in the

Attribute Class Word List.

The descriptions and examples provided in the Attribute Class Word List will be utilized

in determining the appropriate class word.

Also applicable in Standard 2.0- see page 8 and Appendix A.

Attribute Indicator Standard # 310

All indicator attributes must have a format of VARCHAR2(1) NOT NULL.

Indicator attributes by nature must have only two states and cannot have a null state, i.e.

0 & 1 or Y & N, etc.



13/20

Guideline: A difference should be noted regarding Code Tables and Indicators.

Traditionally Indicators were meant to be simple Y/N or 1/0 (as in on/off). They

are attributes that need no corresponding Description attributes.

Code (entities/tables, described earlier) must have 2 or more values and alwaysrequire a corresponding Description attribute.

Attribute Sequencing Standard # 312

Guideline: The attribute sequencing standard is based on the premise that the

business community prefers to see attributes in their natural order. During theprocess of the initial transformation this sequencing will be preserved in the PDM.

See Oracle Designer Modeling Techniques.

It is mandatory for Primary Key (PK) Unique Identifier (UID) components to be

displayed first and in the proper hierarchical order so that the transformed PK index has

the proper order for complex primary keys.

Attributes will be sequenced in the order of their natural business usage.

Guideline:

As an example:

PREFIX NAME

FIRST NAME

MIDDLE INITIAL NAME

LAST NAME

SUFFIX NAME

Also applicable in Standard 2.0- see page 8. Please note that the sequencing order

has changed.

Attribute Format Standard # 316

All attributes must be assigned a format with the maximum length and if applicable

the precision and scale specified.

Guideline: Certain business names have defined formats/sizes. If the attribute

represents one of these business names those pre-defined formats, names, attribute

class words and sizes specified in the Standard Data Formats must be utilized. It ispermissible to prefix the required data element name to customize it for the specific

usage.



14/20

The use of ANSI data types of DECIMAL, INTEGER, INT, SMALLINT,

CHARACTER, CHARACTER VARYING, CHAR VARYING, REAL and DOUBLEPRECISION is not allowed.

Guideline: The data type of CHAR while supported by Oracle is not authorized as

an acceptable format type. A data type of VARCHAR2 (n) with the specified length

must be utilized as a substitute for a CHAR. For example:

Not approved - HAZARDOUS MATERIAL IND CHAR(1) NOT NULL

Approved - HAZARDOUS MATERIAL IND VARCHAR2(1) NOT NULL

Attribute Domain Standard # 320

Guideline: In Oracle Designer, Domains are used to provide standardizedcharacteristics for attributes. For example, all attributes assigned to the NAME

domain will be the same format. By using a NAME domain, we could change the

format of all attributes in that domain to a new format, etc.

A domain name must be meaningful; abbreviations should be avoided unless

obvious.

The domain name must be made up of one to five real words.

Use of domains to implement permitted lists of values should only be considered

when the list of allowable values is static, (e.g. days of week, yes or no).

Where applicable, domains must also represent the same units of measure and

business meaning.

When the domain name is only one word, it must not be an Oracle Reserved Word.

A domain name must be singular.


Primary Unique Identifier Standard # 324

All entities must have a primary UID, with the exception of code entities in whichthe usage is optional.

Guideline: See Code Entity Standard # 240 for code entity standards and How toCreate a Primary UID in the Oracle Designer Modeling Techniques for Oracle

Designer tips.

The primary UID must be a surrogate UID with a data type of NUMBER and aprecision of ten.

Oracle sequence generators will be utilized to populate this PK in the physical

schema.

http://www.dep.state.fl.us/resource/dataadmin/docs/ORACLE_Reserved_Words.dochttp://www.dep.state.fl.us/resource/dataadmin/docs/ORACLE_Reserved_Words.doc


15/20

The primary UID attribute name must be the entity name suffixed with the word

KEY

Guideline: Valid examples of entities and their primary key UID attributes are as

follows:Entity PK UID_______

PAYMENT PAYMENT KEY

CONTRACT CONTRACT KEY

FACILITY FACILITY KEY

Also applicable in Standard 2.0- see page 10. Please note that in Standard 2.0 theUID must be suffixed with the word ID not Key.

Attribute Comment Standard # 328

All attributes must have comments. In many cases, the comment can be a duplicateof the attribute description.

Comments are limited to a maximum of 4000 characters in length.

Comments must clearly indicate the attributes meaning within the context of the

enterprise rather than focusing on a comment that only has meaning to the application

expert user group.

The comment must define the business meaning; as such the system analyst/endusers are the best source for a comment that avoids techno-jargon.

Comments must stand alone. A comment would not assume the reader has access to

other documentation.

All comments must assume that the reader does not have direct knowledge of theapplication, that is, they will be enterprise comments rather than narrowly focused

application comments.

Circular comments must be avoided. In other words they should provide information

about the attribute that extends the business definition beyond what is implied by theattribute name.

Guideline: Examples of proper comments can be found below:

COMPLAINANT PHONE NBR

Phone number provided by Complainant.

Business Rule:

Required if RECONTACT REQUESTED is set = 'Y'.



16/20

ERP COMPLAINT KEY

A system generated number that uniquely identifies the business instance or row and

has no business meaning. The surrogate key value is not normally displayed to thebusiness user.

SITE ID

The unique number used to identify a Site for the Project.

Business Rule:

May only be used when Project Type = MITIGATION and the mitigation is OFF-

SITE!

It is in the comment field that you would provide an explanation of the originalsource of the attribute and/or how the attribute will be used. If the attribute is

relevant to only one application, note the application ownership and use.

When you transform your LDM, the attribute description will not be transformed into

a comment for the column in the PDM. This description will be transformed to thedescription and help text fields for the column; however, these fields do not impact

the Data Definition Language (DDL) and therefore will not carry over to the

physical database. It is the attribute comment that will be transformed into a

comment for the column in the PDM.

Also applicable in Standard 2.0- see page 10. Please note that in Standard 2.0 80

characters is the maximum length.

Attribute Description Standard # 332

All attributes must have a description.

Descriptions must clearly indicate the attributes meaning within the context of the

enterprise rather than focusing on a comment that only has meaning to the applicationexpert user group.

The description must define the business meaning.

Descriptions must stand alone. A description would not assume the reader has

access to other documentation.

Circular descriptions must be avoided. In other words they should provideinformation about the attribute that extends the description beyond what is implied by the

attribute name.

Guideline: One technique to improve descriptions is where the attribute represents a

code, provide a limited sample of the domain of code values and their meanings,which may or may not represent valid values.

If additional information on business usage is provided, it must be placed in labeled

sections. The section labels that can be used are: Business Rule, Derivation Rule,

Default Rule, Format Rule and Value List. For example:



17/20

Marital Status Code

The domain values that record for the Individual the condition of being married or

unmarried.

Business Rule

- A marital status value must be specified at data entry.

Value List:

Single

MarriedDivorced

Widowed

Common LawUnspecified

Default Value

Unspecified


RELATIONSHIPS

Relationship Standard # 400

Standard

All relationships must be named.

The relationship phrases must use lower case letters for easy reading.

Relationship phrases must be meaningful.

Both sides of a relationship must be described.

Do not use weak relationship phrases, such as associated with or related to.

Use descriptive phrases that align with the business terminology concerning the entities.

One to one relationships must be carefully reviewed; they may actually be sub-types,

perhaps with different names, attributes or relationships.

Guideline: Relationships that are optional at both ends should also be carefully

reviewed -- most of the time they represent a modeling error.




18/20

ENTITY RELATIONSHIP DIAGRAM LAYOUT CONSIDERATIONS

Entity Placement Standard # 700

The ERD must be readable, including attribute names, relationships, etc.

Guideline:

Place parent entities to the left of or above child entities on the diagram as this will

create a left-to-right, top-to-bottom reading/viewing pattern.

Size entities to reduce clutter and provide clear paths for relationships. Clear pathsimply that they do not cross any other relationships whenever possible.

Size the entities so that all attributes are visible within each entity in the vertical

dimension when printed

Size entities so that all the entity information is visible in the horizontal plane when

printed.

Fill or outline the entities with the appropriate color. See the table below for theappropriate colors.

Entity Type Line or Fill Color

Data An entity that will become a data table. A data entity

contains the applications core attributes. The resulting

transactional table will be one to which the users input and/orretrieve data from on a regular basis.

pale yellow (1st row,

2nd column in

Designer color box)

Code An entity that will become a code table. The table

will contain repeatedly used data values that fall within themanagement of the application administrative team. A code

table often populates the List of Value (LOV) fields in Oracle

forms and is used for data validation. A code entity must benamed using the CODE Entity Class Word, for example

PAY CODE.

cyan

(1st row, 5th column in

Designer color box)

Table 4 Entity Color Codes


Relationship Placement Standard # 710

The following recommendations are offered in order of precedence. Readability is the goal.

Guideline:

Whenever possible, do not cross an entity box.



19/20

Present relationship names in a readable manner.

Avoid crossing relationship lines if possible.

Point crows feet to the right (East) and/or down (South) when possible. In other

words, the three point prongs point to the right and/or down when ever possible.

Minimize the use of bent relationship lines. Straight lines are preferred but bentlines are acceptable to avoid crossing entities and relationship lines.


Diagram Font Standard # 720

The following recommendations are offered regarding type fonts:

Guideline:

Select a font size for all entity names that is at least 12 points, Bold. If you can use a

larger font size, do so.

Select a font size for all attribute names that is at least 10 points. If you can use a

larger font size, do so.

Make the legend font size 24 points and bold.




20/20

APPENDIX I

REFERENCE / BIBLIOGRAPHY

Kramm, Mark A., Graziano, Kent. Oracle Designer: A Template for Developing an EnterpriseStandards Document. Upper Saddle River, NJ: Prentice Hall, 2000

von Halle, Barbara, Fleming, Candace. Handbook of Relational Database Design. Reading,Mass., Addison-Wesley, 1989

Location Data Standards

Oracle Designer Modeling Techniques

Physical Data Model Standards & Procedures

std 07092501.3.1 logicaldatamodeling

Documents