Entity subtypes. Supertype. Example. Advantages and disadvantages of using subtypes of entities
Before studying this topic, it is recommended to familiarize yourself with the following topic:
- 1. What is an entity subtype? What are entity subtypes used for? Example
- 2. What is an entity supertype?
- 3. How can entity subtypes be related together? Example
- 4. What are the disadvantages of using subtypes of entities in database developing?
- 5. What are the advantages of using entity subtypes when designing databases?
- 6. What type of relationship is established between a supertype and a subtype?
- Related topics
1. What is an entity subtype? What are entity subtypes used for? Example
Entity subtypes are entered into the ER model in order to reduce the total number of attributes of each entity.
Each entity has a set of unique attributes. However, the attributes of different entities can be repeated. Therefore, it is necessary to develop the ER-model so that the number of repeated attributes in different entities was minimal or reduced to zero. Repeating attributes carries redundancy in the database. The size of the database becomes unreasonably large, so this problem needs to be fixed. Entity subtypes are used to solve this problem.
The idea of using an entity subtype is that a supertype is allocated for the entire diverse set of entities, which contains information common to all types of entities. Details (subtleties) of each type of entity are taken out separately in several specialized subtypes.
Suppose you need to develop a database of employees of the educational institution. In an educational institution there are 3 entities, each of which represents a professional group of employees:
- the entity Administration;
- the entity Teacher;
- the entity Support staff.
If for each entity to describe its own set of attributes, then you will notice that some attributes in different entities will be repeated. The following attributes are common to all entities:
- Identification number.
You can also highlight some unique attributes:
- the entity type “Administration” has the administrative rate, the name of the position held, etc.;
- the type of entity “Teacher” has the number of hours read, the rate per 1 hour, category, etc.;
- the entity type “Support personnel” has a staff rate, a weekend or working day ratio (if the employee worked on weekends), etc.
To solve the problem of avoiding data repeatability, changes are made to the ER-model, as shown in Figure 1, namely:
- the “Employee” supertype of entity is introduced. This supertype contains common attributes for all types (subtypes) of entities;
- entity subtypes “Administration”, “Teacher”, “Support staff” are introduced. Each of the entity subtypes has its own unique attributes.
Figure 1. Supertype and subtypes in the diagram
2. What is an entity supertype?
An entity supertype is an entity type in which only common attributes are implemented (described) for entity subtypes that use this supertype.
3. How can entity subtypes be related together? Example
Entity subtypes may or may not intersect. If a subtype of one entity may be suitable for a subtype of another entity, then this means that these subtypes of entities intersect each other. Otherwise subtypes of entities do not intersect.
Example. In an educational institution, an employee who holds an administrative position (subtype Administration) may be a teacher. That is, the entity subtype Administration intersects with the entity subtype Teacher.
However, an employee from the entity subtype “Support staff” cannot be a teacher. Therefore, the entity subtype “Support staff” does not intersect with the Teacher subtype.
In view of the above, the image of the supertype and subtypes in the diagram has the form, as shown in Figure 2.
Figure 2. The supertype and entity subtypes with intersection between entity subtypes
In Figure 2, between the subtypes Administration and Teacher is a text Gs. This means that subtypes can intersect. Between the Teacher and the Support Personnel subtypes is a symbol ‘G’. This means that entity subtypes do not intersect.
4. What are the disadvantages of using subtypes of entities in database developing?
Each supertype and subtype of entities is implemented by a corresponding table. Table fields are attributes of this type of entity.
The disadvantages of using subtypes of entities are that the overall development of the database is complicated. The difficulties are as follows:
- the difficulty in organizing the interaction between the master and detailed tables for relational databases;
- in the subtype tables, you must additionally enter the primary keys for linking to the supertype table;
- SQL queries become more complex because you need to process data in related tables;
- difficulty in providing secure of user access to table columns.
5. What are the advantages of using entity subtypes when designing databases?
Advantages of using subtypes of entities:
- avoid data redundancy, as each subtype contains only unique information. This in turn leads to a reduction in the size of the database itself;
- simplifying the setting of restrictions for any attribute that corresponds to a column of a table that implements a particular subtype of an entity;
- reduction of errors in programming operations on a table that corresponds to a specific subtype of an entity;
- flexibility in modifying the database structure. You do not need to modify the supertype table if you add / change the structure of the subtype table. This, in turn, leads to the fact that there is no need to redo the entire database management program.
6. What type of relationship is established between a supertype and a subtype?
Between the supertype and subtype set relation “one to one” or 1:1.
- The concept of ER-model. The concept of entity. Attributes. Attribute type
- ER-model. The concept of relationship. The relationship capacity. Types of relationships. Examples