38) Explain GEN Subtype entity ? How it works?

38) Explain GEN Subtype entity ? How it works?

· When a parent entity type which includes subtypes is transformed, the parent entity type with its subtypes becomes one physical table.  All attributes defined within each subtype become optional columns on the table.   

· A subtype is sometimes promoted to an entity type in the Data Model for performance considerations.  An analysis of the impact of the subtype versus separate entity type should be done early in Analysis to avoid rework in action diagramming, database implementation, and data mapping from other systems.

· A promoted entity has a one-to-one relationship with the original entity with the following characteristics:

-The relationship is optional on the side of the original entity.

-The relationship is mandatory on the side of the promoted entity.

-The relationship is the identifier for the promoted entity.

-The relationship is mutually exclusive with any other relationships from the main (original) entity to other promoted subtypes from the same partitioning

Promoting Subtypes

· Involve your Database Administrator (DBA) in your discussions about whether to promote subtypes.  Your DBA should understand many of the factors to be considered in such a decision, which include such things as saving storage space, expending more processing time, impact on existing work, and the complexity of the proposed implementation.Use of subtypes can increase disk storage space with a consequent effect on I/O.  This is particularly true if the subtype represents a small portion of the occurrences of the parent entity type, as there will be wasted DASD for the empty columns.  The columns will contain either blanks or spaces.  This results in fewer occurrences of a single parent entity type on a data page, which increases I/O because more pages have to be READ to retrieve the data.  This effect may be minimized by the use of compression routines.

· You can use a third party vendor's compression routine (EDITPROC) to reduce the amount of disk space used.  There is some processing overhead associated with this.

· Promote subtypes to high-level entity types when faced with DBMS columns-per-table limitations, or concurrency or storage problems.

· Deciding to implement entity subtypes depends on how sensitive the installation is to wasted disk space versus increased I/O, additional INSERTs and DELETEs to create both parent and subtype rows in one table, and the access patterns for use of subtype predicates with or without parent predicates.  (Access patterns here means the answers to questions of access volume, required response times, and so forth.)Use of a subtype adds rows to a record.  This causes additional INSERTS and DELETEs in the database.  It also means additional INSERT and DELETE statements must be added to the action diagram.

· Not all subtype constraints are enforced by the code generator.  Relationships defined at the subtype level are always optional at the parent entity type level, and are only mandatory if they are against an occurrence of the subtype's type.

· Consider the effects of concurrency and reorganization and recovery administrative tasks when deciding to promote subtypes.

Subtype Views

· Attributes in the subtype can be referenced in views of the entity type or view of the subtype.  If you do not reference subtype attributes, you do not need a subtype view or the classifier.  If you do reference subtype attributes, you should reference the classifier.

· Subtype identifiers are defined to distinguish occurrences of the subtype from one another.  The indexes built for them in the Data Store List are defined as non-unique since the index will be on a table supporting all occurrences of the supertype (parent entity type).  Since identifying predicates must be static and classifying attributes do not have to be, using the classifier to make the subtype identifier unique is usually a poor choice.Put one identifier on the high level entity type (parent entity type).  Add indexes in the Data Store List as needed to enforce uniqueness constraints deemed important enough to warrant the additional index.

· An entity view cannot be matched to an entity subtype view.  View matching, and reuse of process and common action blocks, is more difficult if the same data is sometimes in an entity view and some times in an entity subtype.

· You should not have views of subtype attributes unless you also have views of the classifying value.  Test the classifier to see that the subtype attributes are valid.

· If you build a view of a subtype, the generated SQL SELECT for READs of that view automatically gets the classifying value added to the WHERE clause.