Data Models Presentation

http://www.itlibrary.org/index.php?page=Release_Management

http://www.anticlue.net/archives/000738.htm

 

http://www.itlibrary.org/index.php?page=Release_Management

http://itservicemngmt.blogspot.com/2007/07/release-management-quick-reference.html

Naming Objects: General Rules

Structured IT organizations usually adopt their own database design and naming conventions.

I think that this depends largely on the objective and the strategy of the company. 

It is very important that standards and conventions are accepted by the majority of the team otherwise it wouldn’t be a standard at all. 

Standardization is critical to the successful achievement of quality in the system engineering process. The standards defined in this document are intended as a communication tool that allows developers to work together in a cohesive manner. Having established standards:

1. Provides consistency across all projects

2. Eliminates guesswork on the part of new team members as to how things are supposed to be done

3. Improves the maintainability of models and systems over time (i.e. less guesswork)

4. Reduces long-term costs (because systems are easier to maintain)

5. Promotes reusability (because objects within the Designer repository can be easily maintained)

6. Simplify the overall management and administration of the database server

 

These are some standards that I have adopted before to teams I worked  with. I talked with some organizations before who preferred codes for the names for security reasons. These codes will have no meaning to people who can have access to the document outside the organization. But this something that they define for the team.

 

All rules established in this section must be followed when naming objects, unless specifically accepted by individual object descriptions in later sections.

The individual sections contain any additional rules specific to an object and one or more examples to illustrate usage.

Acronyms/Abbreviations

Full words should always be used, unless an acronym can be substituted or an abbreviation is required for length considerations.

Use of Significant Words

Use root words wherever possible. Dropping the suffix (-age, -ence, -ance, -ing, -ant, -ity, -any, -ive, -ary, -ony, -aty, -ory, -ation, -ment, -ed, -tion) will generally leave the root word. Do this only if the remaining root word is meaningful as is.

Words such as who, what, when, or where are not allowed.

The use of articles and prepositions (such as the or of), adjoining words or conjunctions (such as and or or), qualifying words such as new or old, and numbers should be on an exception basis.

Use of Oracle Reserved Words

A name cannot be an Oracle reserved word. See Oracle Documentation for reserved words – Oracle Reserved Words.

Oracle reserved words have a special syntactic meaning to Oracle or the PL/SQL language so should not be used for schema or program object names.

Additionally, the names of SQL built-in functions should not be used for schema or program object names.

Use of Special Characters

Special characters, including brackets, quotation marks, question marks, dollar signs ($), hash signs (#) and slashes are not permitted. Dashes are allowed only in legitimately hyphenated words.

 

Use of Special Characters

Special characters, including brackets, quotation marks, question marks, dollar signs ($), hash signs (#) and slashes are not permitted. Dashes are allowed only in legitimately hyphenated words.

Use of Underscores

When an Oracle object is viewed in the Oracle data dictionary it will be in all uppercase letters regardless of the case that was typed in the CREATE statement. The problem with all uppercase names is that when more than one word exists in a name the words tend to run together.

Underscores will not be used except to separate words (or acronyms) or separate prefixes and suffixes from the base names of schema objects, object parts or PL/SQL elements.

 

1. Server Model Objects

This section describes naming conventions for objects that will normally be encountered or defined in the Design phase of development.

Tables

Table Names

• A table should be descriptive.

• Table names should have a minimum of 4 characters and a maximum of 30.

• A table name must not duplicate the name of another object in the database.

• Module name can be added as prefix to the table name.

• A table representing an entity should have a suffix of TE (example: module_employee_TE).

• A table representing a reference should have a suffix of TR (example: module_inventory_type_TR)..

• A table representing historical data should have a suffix of TS (example: module_sales_transactions_TS)

Table Comments

To ensure that appropriate meta-data is included in the Oracle data dictionary, all tables must have a comment. Comments can be entered through Designer, a DDL SQL COMMENT statement or via a third-party Oracle tool (e.g. Toad). This comment should describe the basic information stored in the table. This will allow us to generate a concise data Dictionary later.

Columns

Column Names

• A column name should be descriptive.

• A column name must be the same as the name of the attribute from which the column was mapped, with the spaces translated to underscores.

• Not more than 30 characters

• The column name must not be a plural.

• Names that are 5 characters or less in length will not be abbreviated.

• If the column is not based on an attribute, then it should be named using the naming standard set forth for attributes with the exception that an underscore is used instead of a space between segments.

• Column names will not be prefixed with the table short name.

• Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name.

• Number of columns within a table can range from 1 to 254.

• Audit capabilities are required in all systems developed. Every entity must contain the following four audit attributes:

• CREATED BY

• DATE_CREATED

• UPDATED BY

• DATE_UPDATED

Column Comments

To ensure that appropriate meta-data is included in the Oracle data dictionary, all columns must have a comment. Comments can be entered through Designer, a DDL SQL COMMENT statement or via a third-party Oracle tool (e.g. Toad). This comment should describe the basic information stored in the column. This will allow us to generate a concise data Dictionary later.

Constraints

• Constraints can be defined for table and column to enforce certain rules.

• Constraint name length should not exceed 32 characters.

• Constraint name should be preceded by the constraint identifier and an underscore followed by the table name where the constraint is associated and n. ‘n’ represents the numeric reference of the constraint in relation to the table. The constraint identifier are the following: (example: PK_CODE0)

 

   

 

 

IDENTIFIER	CONSTRAINT TYPE
NN	Not Null
UN	Unique
PK	Primary Key
FK	Foreign Key
CK	Check

Indexes

• Create index to speed up the retrieval of rows and to enforce uniqueness on values in a column.

• Index names should reflect the table they belong to, their function and any other important data in their name.

• Index name should always be preceded by a prefix ‘aX_’ then the name of the table where the index is associated and n. ‘n’ represents the numeric reference of the index in relation to the table. ‘a’ denotes the type of index. The following are the prefix for the different type of index.

• (example: IX_CUSTOMER_ID0)

 

   

 

 

PREFIX	DESCRIPTION
FX	Foreign key index
PX	Primary key index
UX	Unique key Index
CX	Cluster key index
HX	Hash key index
BX	Bitmap key index
IX	Non unique index

Sequences

• Sequence is a database object that automatically generates a sequence number.

• The naming convention for sequence is <table_name>_SQ. Where <table_name> is the table for which the sequence will provide values to the primary key.

• Additionally, sequences may need to reflect functionality because a single sequence may be used with multiple tables. In this case, the naming convention is <common functionality>_SQ.

Example: Three tables are generated from sub-types of the entity MODEL. They each represent a different type of model but to avoid potential key conflicts, the decision is to use one sequence for all the primary keys because they will all be mapped to a common cross-reference table. So the sequence name is MODEL_ID_SQ since the sequence will generically represent a model identifier.

• Sequence names should have a minimum of 8 characters.

Views

• A virtual table — that is a table that does not physically exist in its own right.

• View name should always have a suffix of ‘_VW’ after the name that describes the information found in the view.

• The naming convention for views is <table name>_<criteria>_VW. Where <table name> is the name of the root (or ‘master’) table the view is based on. The <criteria> qualifier is optional. The qualifier, if used, should give the end users a clear idea of the purpose and contents of the view. Use the <criteria> qualifier if:

• Using the table name alone is not unique.

• The view is based on a join of 2 or more tables.

• The view contains a where clause.

• The view is unusually complex.

• The view is a summary.

Example: EMPLOYEES_VW is a view on the EMPLOYEES table

 

Materialized Views

• The naming convention for materialized views is:

<materialized view name>_<criteria>_MV

Where <materialized view name> is the name of the root (or ‘master’) table the materialized view is based on. The <criteria> qualifier is optional. The qualifier, if used, should give the end users a clear idea of the purpose and contents of the materialized view. Use the <criteria> qualifier if:

• Using the table name alone is not unique

• The materialized view is based on a join of 2 or more tables

• The materialized view contains a where clause

• The materialized view is unusually complex

• The materialized view is a summary

• Materialized view names should have a minimum of 6 characters.

Examples: CUSTOMERS_ACTIVE_MV provides information on only active CUSTOMERS.

EMPLOYEES_DEPARTMENTS_MV is a view joining the EMPLOYEES table to the DEPARTMENTS table.

 

1. Other Database Objects

Database Links

• Database link names should have a maximum of 128 characters.

• The naming convention for database links is:

@<remote database>.<host name>.<domain>

Where <remote database> is the name of the remote database, <host name> is the name of the server where the remote database resides and <domain> is the owner of the database.

• Names of database links can also contain periods (.) and ‘at’ signs (@).

Example: @CLIENT1.LNX01.COMPANY.COM

 

Databases

Export Files

• The Oracle Export utility provides a simple way of extracting the object definitions and table data from an Oracle database and storing them in an Oracle binary-format Export dump file.

• The naming convention for export dump files is:

<sid>_YYMMDD_n.DMP

Where <sid> is the SID of the database which is the target of the export.

Use n if there are multiple dumps.

• A log file using the same naming convention with the suffix LOG should always be generated to accompany any Oracle export.

• On completion of the export, the values of the export parameters used should be listed at the top of the log file for future reference.

Example: PROJECT_040101.DMP

 

Functions

• A function is a named program unit that takes parameter and returns a computed value.

• PL/SQL functions will normally be sub-programs within a PL/SQL package. Function names should not contain verbs that indicate how the function will obtain the value that it returns e.g. GET_STATUS. A function name should indicate what the return value will be and nothing more.

• The naming convention for packaged functions is:

FN_<application_name>_<noun> or FN_<application_name>_<adjective>

Where <noun> or <adjective> indicates what the function will return. <application_name> is the short name of the application to which the function is associated. Function name should always be preceded with ‘FN_’.

Example: FN_TOTAL_UNITS_CHECK is a stand-alone function that returns TRUE if the total unit count is greater than zero.

Instances

• The naming convention for instances is:

<database_name><instance type>

Where <database_name > is the name of the database to which the instance is related. The <instance type> qualifier is identifies the type of system and can take one of the following values:

DEV – Development system

QA – Quality Assurance system

PROD – Production system

• Each database has just one instance.

• Instance names should have a maximum of 9 characters.

Example: COM-DEV

Packages

• Package is a database object that groups logically related PL/SQL types, objects and subprograms.

• The naming convention for packages is:

PKG_<package name>

Where <package name> is the name of the table or other object on which the package operates. Package name should always be preceded with ‘PKG_’.

• Each package will contain procedures and functions that operate on a table or other object.

• Where a package does not operate on data in a particular table or object, a name describing the purpose of the package should be used.

• PL/SQL code should take advantage of the many benefits packages offer over stand-alone procedures and functions:

• Enforced information hiding

• Object-oriented design

• Top-down design

• Object persistence

• Performance improvement

• Package names should have a minimum of 8 characters.

Examples: PKG_VESSELS is a package containing various procedures for the VESSELS table.

PKG_STRINGS is a package that provides functions to handle string manipulations.

Procedures

• A named PL/SQL block which can take parameters and be invoked to perform a specific action.

• PL/SQL procedures will normally be sub-programs within a PL/SQL package.

• The naming convention for packaged procedures is:

SP_<apllication_name>_<noun> and/or SP_<application_name>_<verb>

Where <noun> or <verb> provide a brief explanation of what the procedure does. <application_name> is the short name of the application to which the procedure is associated. Procedure name should always be preceded with ‘SP_’.

• By default, packages used to manipulate data in a single table may be created with 5 packaged procedures that act as methods for all access to that table. The default packaged procedures are:

• select_all – Returns all records

• select_rec – Given the entire primary key and only the primary key returns complete record

• insert_rec – Inserts a record

• update_rec –Given the entire primary key and only the primary key updates a record

• delete_rec - Given the entire primary key and only the primary key deletes a record.

Example: SP_TR_ELAPSED_TIME is a stand-alone procedure that calculates and processes the elapsed time between two events.

Schemas

• A schema is a set of objects owned by a user and as such no additional naming convention is required (see Users).

• Where a set of objects is owned by an application, a ‘Three Schema Security Model’ for database security should be used as follows:

Level 1 Schema – Owns the application data and grants only the bare minimum of needed privileges to a second schema.

Level 2 Schema – Uses privileges granted by the first schema to create the objects needed to access the application data such as Views and Packages.

Level 3 Schema – Uses privileges granted and objects created by the second schema to access the data. Used for application users to connect to the database.

• Application users should never be allowed to connect to the database via a ‘single’ schema that owns all data and objects.

• Application specific schemas should be prefixed with the application short name. (See Applications)

Example: LOC_DATA is a schema that owns the data (tables) for the LOCATOR application (equivalent to Level 1 Schema).

Synonyms

• Synonyms are created as aliases for tables, views, sequences, procedures, functions, packages, materialized views, Java class schema objects, or other synonyms. Synonyms are used for security and convenience to:

• Mask the name and owner of an object

• Provide location transparency for remote objects of a distributed database

• Simplify SQL statements for database users

• The naming convention for synonyms is:

<synonym name>

Where <synonym name> exactly matches the name of the underlying object that the synonym is referencing. An exception to this is where the underlying object has been renamed in which case the synonym needs to be redefined and applications based on the synonym will continue to function without modification.

• Synonyms should have a minimum of 4 characters.

Example: VESSELS is a synonym for the table VESSELS

 

Tablespaces

• A tablespace name should reflect the contents of the tablespace. During a standard Oracle installation, special tablespaces are created and named as follows:

   

   

   

  TABLESPACE	CONTENTS
  TEMP	Sorting, grouping and indexing data
  RBS	Rollback data
  INDX	Indexes associated with data in USER tablespace
  USERS	Miscellaneous user objects and data
  SYSTEM	Data dictionary, including definitions of tables, views and stored procedures
  UNDOTBS01	Undo data

• Tablespace should be names according to function.

• The tablespace name should also indicate whether it contains table data or index data.

• Each file in a tablespace should have a sequential number that relates to when it was created (1 for the first file, 2 for the next and so forth).

• Tablespaces should be named connotatively with 8 or fewer characters.

• The word tablespace (or reminders of) should not be embedded in tablespace names e.g. TSPACE_A

• Partitioned tables should be stored in tablespace that are named for the partition they contain.

• The naming convention for tablespaces is as follows:

<object type> or <application name><object_type><n>

Where <object type> is the contents of the tablespace. This is applicable for TEMP, SYSTEM, USERS, UNDOTBS. <application name> is the short name of the application, <object type> is D (for data) or X (for index). <n> is optional and refers to the partition number. This is significant for partitioned tablespaces.

Example 1: SYSTEM

TEMP

Triggers

• Database trigger is a stored subprogram associated with a table. It automatically forces before or after an INSERT, UPDATE or DELETE statement affecting the table.

• A trigger is a stored PL/SQL block associated with a table, a schema, or database

• The naming convention for triggers is:

T<when><type>_<table name>

Where:

<table name> is the name of the table on which the trigger is based

<when> refers to whether the trigger is executed Before or After an insert, update, or delete. The value of <when> is ‘A’ for after and ‘B’ for before triggers.

<type> is the action (Insert, Update or Delete) performed by the trigger. The value of <type> is ‘I’ for insert, ‘U’ for update and ‘D’ for delete.

• Trigger names should have a minimum of 10 characters.

Examples: TBI_CLIENT_MSTR = A before insert trigger on client_mstr table.

TBIUD_IMPLEMENTATION_DTL = A before insert, update and delete trigger on implementation_dtl table.

 

Procedures

1. To ensure that the database follow the design standards from the initial stages of the Application Development cycle the following will be the responsibility of the Project Team Leader :

• Determine business rules that have to be defined in the database server.

• Create database objects for systems still under the development environment.

• Create scripts that will be used for the creation of database objects such as tables, indexes, stored procedures, triggers. These scripts will be used when the system under development will be transferred to production.

• Determine the approximate volume of production data on every table used by the system.

• Maintain (Grant / Revoke) the object access rights of the different users of their system in the development server.

• Coordinate with the DBA regarding the enrollment of users (developers) of the system. This should be done by filling up a “user enrollment form”.

1. Likewise, to ensure that database design standards are met in the Development and Production servers the following will be the responsibility of the Database Administrator

• Monitor, maintain and administer the Development and Production servers.

• Create the database objects in the production server from scripts.

• Create a separate tablespace in both the Development and Production servers for each system.

• Create login accounts for the different users per system

• Maintain (Grant / Revoke) the access rights of the different users of each system in the development and production servers.

• Establish User Profiles per System

• For standard access rights and easier maintenance of user rights, user profiles should be established.

• Establish a standard login program in granting/revoking access rights of users of the different systems.

 

 

1. Diagramming Guidelines

• Establish standard tool for the company (Jdev/ERWin) and make sure that designers/developers are using the same tool to allow exchange of information

• Diagrams should be exported to graphical outputs like JPEG/GIF especially if there is a need to allow others to view the diagrams at the same time restricting them to edit it.

• Digrams should be versioned and placed in a repository

• Create subject matters and place related tables inside when doing datamodels

 

Test-driven design (TDD) (Beck 2003; Astels 2003), is an evolutionary approach to development which combines test-first development where you write a test before you write just enough production code to fulfill that test and refactoring.

A good approach is to add an xUnit test first, it would then fail until you refactor and implement the functionality. This goes on iteratively and continuously. The benefit is that the business requirements are converted to test cases which will be then use during the QA Testing. This will avoid guessworks on the side of the QA analysts

TDD Details

Leaders of the Enterprise Content Management System have finally decided to allow interoperability across content repositories
see details

Enterprise Content Management vendors EMC Corporation, IBM Corporation, and Microsoft Corporation have announced the publication of Content Management Interoperability Services (CMIS), distributed as a ZIP archive with four prose documents and a collection of schemas, WSDLs, and XML instances. According to the published Introduction, the CMIS standard is intended to “define a domain model and set of bindings, such as Web Service and REST/Atom that can be used by applications to work with one or more Content Management repositories/systems. The CMIS interface is designed to be layered on top of existing Content Management systems and their existing programmatic interfaces. It is not intended to prescribe how specific features should be implemented within those CM systems, nor to exhaustively expose all of the CM system’s capabilities through the CMIS interfaces. Rather, it is intended to define a generic/universal set of capabilities provided by a CM system and a set of services for working with those capabilities…”

CMIS uses Web Services and Web 2.0 interfaces to enable applications to interoperate with multiple Enterprise Content Management (ECM) repositories by different vendors. “The ultimate goal of CMIS is to dramatically reduce the IT burden around multivendor, multirepository content management environments. Currently, customers must spend valuable time and money to create and maintain custom integration code and one-off integrations to get different ECM systems within their organizations to talk to one another…”

CMIS has been in development for two years, culminating in a vendor software interoperability Plugfest in August 2008 in Redmond, WA. Working together since late 2006, the three companies were joined in the creation of the CMIS draft specification by other leading software providers including: Alfresco Software, Open Text, Oracle, and SAP. A final gathering of all seven companies was recently held to validate interoperability of the specification before submission to OASIS.”

Alfresco has now announced the availability of the first Content Management Interoperability Services (CMIS) specification draft implementation from Alfresco Labs. The Draft CMIS Implementation is freely available for download. It offers support for the CMIS REST and Web Services bindings allowing client applications to connect to, navigate, read, and create content against the Alfresco content repository. It also supports the CMIS Query Language providing SQL-like querying of the repository including location, properties, and full-text. A CMIS Test Suite is provided to allow compliance compatibility testing against any CMIS compliant REST Binding.”

An announcement from Open Text reports that Open Text has worked with SAP AG to “create a prototype that uses the CMIS standard to manage content from SAP applications with Open Text Enterprise Library Services… With the new standard, developers can write applications that can work with multiple repositories from different vendors, allowing users to access and organize information stored in different repositories through a single application and interface. Open Text is a member of the group of companies working to develop the standard.”

“The objective of the CMIS standard is to define a common content management web services interface that can be implemented by content repositories and enable interoperability across repositories. These capabilities and interfaces will not match every existing content management system and may require some level of change to existing products, at least in terms of conforming existing interfaces to those defined here. However, it is an explicit goal that CMIS will not require major product changes or significant data model changes like other standards such as JSR 170 have required…”

“The CMIS standard will expose core/common ECM repository capabilities in an intentionally generic way. These will allow for applications to be constructed that can work with content residing in one or more ECM repositories, without having to understand implementation differences between the individual repositories or worrying about interface inconsistencies between the repositories…

While most/all of the capabilities that will be exposed via CMIS generally fall into the core/basic functions of an ECM repository, the goal of this standard is to ensure that ECM applications can be built on top of the CMIS interfaces that enable richer/business critical applications and use cases, like Business Process Management and Electronic Discovery. Because those application use cases have been under consideration through the CMIS design process, CMIS will enable ECM applications to focus on solving business logic problems at the application-level without worrying about the implementations of specific ECM repositories…

“By providing a services-oriented architecture for interacting with an ECM repository, ECM applications can use CMIS to be loosely-coupled to individual repositories, rather than more tightly integrated. This will make it simpler for (a) applications to use CMIS interfaces ‘a la carte’ rather than having to having to invoke the full-set of CMIS interfaces, and (b) allow applications to be built in a Services Oriented Architecture.

According to the published “Overview of Content Management Interoperability Services 1.0,” CMIS “defines four base types of objects that exist within a Repository, where the Repository can define additional Object Types for any of these type of objects. An Object Type specifies the schema of Properties that are allowed or required for the object. (1) Documents represent individual content objects in the repository. A Document may or may not include one content-stream. (2) Folders represent organizational containers in which Documents (or other folders) can be stored.(3) Relationships represent loose relationships between exactly two (2) objects (documents or folders) in the Repository. (4) Policies represent administrative policies that may be applied to objects.”

CMIS “exposes services for:

Discovering Object Type definitions and other Repository information — including which optional capabilities are supported by a particular Repository
Creating, Reading, Updating, and Deleting objects
Filing Documents into zero, one, or more Folders — if the repository supports the optional multi-filing capability
Navigating and traversing the hierarchy of folders in the Repository
Creating versions of Documents and accessing a Document’s version history
Querying a repository to retrieve one or more objects matching user-specified search criteria, including full-text search queries”
Document objects can be versioned, but ‘folder’, ‘relationship’, and ‘policy’ objects are not versioned. All methods for referring/retrieving a Document can specify whether they refer to a specific version of a Document, or should always retrieve the latest version.

A CMIS Repository has the option of supporting multi-filing of Documents into zero, one, or more than one folder concurrently. Folders can never be multi-filed. The Repository’s level of support for multi-filing will be exposed to applications through the Repository service…”

On September 10, 2008, OASIS member companies submitted a proposed charter for a new OASIS Content Management Interoperability Services (CMIS) Technical Committee. Based upon Version 0.5 of the CMIS specification, the TC would “define a domain model including a data model and abstract capabilities for Content Management (CM) and a set of bindings that can be used by applications to work with one or more Content Management Repositories/systems and that can be implemented by content repositories and enable interoperability across repositories.”

Source: OASIS CMIS

 

Tracing is an important technique your team can apply in the struggle to

ensure that you are designing and implementing the right system. The

trick is to implement “just the right amount” of traceability in “just the

right way” so that the risk-to-reward ratio benefit fits your project’s

circumstances. Otherwise, you may find that

Your project suffers from excessive overhead without

commensurate quality improvement.

Or worse, you fail to deliver the requisite quality and reliability

demanded by your project circumstances.

These are both in the class of “very bad things that can happen to your

project,” so it behooves the team to define and implement the right

traceability strategy from the beginning.

See Details from IBM DevWorks –

Traceability Matrix

See the link below to  know when to use Restful Web Services

Restful Web Services

Implementing Restful Web Services using the apache CXF (formerly XFire)

CXF Restful Web Services

Visit the Apache CXF site here;

Craig Mcclanahan, creator of Struts and co-spec lead for JavaServer Faces, has started blogging. In this entry, he gives his current recommendations for the common question: Struts or JSF?

JSF or Struts

CMS on Microsoft SharePoint