Author: saqibkhan

The Enhanced Entity-Relationship (EER) model is used to get more advanced features into database design, and help database designers map real-world scenarios more effectively. There are two core concepts in the EER Model – specialization and generalization – that help refine or abstract the entities and help designers create schemas that are both flexible and intuitive.

In this chapter, we will throw some light on these two core concepts, specialization and generalization, and highlight how they are different from each other, using practical examples for a better understanding.

What is Specialization in the Extended ER Model?

Specialization focuses on dividing a general entity type, or superclass, into smaller, more specific categories called subclasses. This process is quite useful when entities in the superclass have distinct characteristics or relationships, and the relationships only apply to certain members.

Specialization is like zooming in on a larger picture to highlight its finer details. Specialization takes a broad category and splits it into meaningful subcategories based on distinguishing attributes or roles. For example, from the EMPLOYEE entity type, we can define subclasses such as SECRETARY, ENGINEER, and TECHNICIAN.

Types of Specialization

Specialization could be either attribute-defined or user-defined –

• Attribute-Defined Specialization − In this type of specialization, the membership in subclasses is determined by the value of a specific attribute in the superclass. Like consider the attribute Job_type in EMPLOYEE. If Job_type = “Technician”, then the employee is part of the TECHNICIAN subclass.
• User-Defined Specialization − In this type, the subclass membership is manually assigned by users. For example, managers might decide which EMPLOYEES belong to a specific training group.

Examples of Specialization

Let’s consider the company database. EMPLOYEE is specialized into subclasses, such as:

• SECRETARY with an attribute Typing_speed
• ENGINEER with an attribute Eng_type
• TECHNICIAN with attributes like Tgrade

These subclasses allow the database to store data more efficiently by associating unique attributes with only the relevant groups.

Constraints on Specialization

While working on specializations, we face some constraints. Specialization constraints could be –

• Disjoint − Entities can belong to only one subclass. Like, an EMPLOYEE cannot simultaneously be a TECHNICIAN and an ENGINEER.
• Overlapping − Entities can belong to multiple subclasses. For instance, a salaried engineer could belong to both SALARIED_EMPLOYEE and ENGINEER.

Specializations may also be:

• Total − Every entity in the superclass must belong to at least one subclass. For example, all EMPLOYEES are either HOURLY_EMPLOYEES or SALARIED_EMPLOYEES.
• Partial − Some entities may not belong to any subclass. For example, not every EMPLOYEE is a SECRETARY, ENGINEER, or TECHNICIAN.

What is Generalization in the Extended ER Model?

Generalization combines multiple entity types into a single, broader entity type by identifying shared characteristics. Generalization is like stepping back to see the bigger picture. It suppresses the differences among entities and emphasizes what they have in common.

For instance, in a vehicle database, the CAR and TRUCK share attributes like Vehicle_id and License_plate_no. These can be generalized into a VEHICLE superclass.

Process of Generalization

Generalization is simple. All that is needed is to identify common attributes or relationships among two or more entity types. So, we need to define a new superclass that captures these shared features, representing the original entity types as subclasses of the new superclass.

In the previous transportation database example, we have seen the following –

Entity Types − CAR and TRUCK

• CAR has attributes like No_of_passengers.
• TRUCK has attributes like Tonnage and No_of_axles.

Here, the VEHICLE includes shared attributes such as Vehicle_id and License_plate_no.

This approach avoids redundancy by grouping shared data at the superclass level while preserving unique characteristics in the subclasses.

Combining Specialization and Generalization

Specialization and generalization are applied within the same database design. These two processes are not mutually exclusive; they are complementary.

Consider the university database. Let’s see how specialization and generalization are applied here –

• Specialization − The PERSON entity type is specialized into STUDENT, EMPLOYEE, and ALUMNUS. EMPLOYEE is further divided into FACULTY, STAFF, and STUDENT_ASSISTANT.
• Generalization − FACULTY and STAFF are generalized into EMPLOYEE.

By combining these processes, designers can make a more structured and accurate representation of the data.

Diagrammatic Representation of Specialization and Generalization

In EER diagrams, we visualize specialization and generalization clearly.

Specialization − The superclass connects to subclasses through a circle. The lines indicating subclass relationships. Subclass-specific attributes, such as Typing_speed for SECRETARY, are attached to the respective subclass.

Generalization − The subclasses connect to the generalized superclass. It highlights shared attributes. For instance:

• In a specialization diagram, EMPLOYEE connects to subclasses like ENGINEER and SECRETARY.
• In a generalization diagram, CAR and TRUCK converge into a VEHICLE superclass.

Differences between Specialization and Generalization

Specialization focuses on highlighting the differences between entities. It breaks down a superclass into specific subclasses. For example: EMPLOYEE → SECRETARY, ENGINEER.

Generalization emphasizes the commonalities between entities. It combines specific subclasses into a generalized superclass. For example: CAR, TRUCK → VEHICLE.

Real-World Applications of Specialization and Generalization

The concepts of specialization and generalization are widely used in various domains:

Example 1: Company Database

• Specialization − EMPLOYEE entity type is specialized into categories like SALARIED_EMPLOYEE and HOURLY_EMPLOYEE.
• Generalization − TEMP_WORKER and PERMANENT_WORKER are generalized into EMPLOYEE.

Example 2: Vehicle Registration

• Specialization − VEHICLE entity type is specialized into PASSENGER_CAR and COMMERCIAL_VEHICLE.
• Generalization − CAR and TRUCK are generalized into VEHICLE.

Advantages of Specialization and Generalization

Following are the advantages of using specialization and generalization features in the Extended ER Model –

• Data Organization − Ensures logical grouping of attributes and relationships, making the database easier to manage.
• Flexibility − Accommodates both unique and shared characteristics of entities.
• Efficiency − Reduces redundancy by storing shared attributes in a superclass.
• Real-World Representation − Mirrors how entities are structured in real life.

In traditional ER diagrams, we use the entities and their relationships with rectangles and diamonds, respectively. The Enhanced Entity-Relationship (EER) model extends the traditional ER model to support more complex database designs. In EER, we have new concepts such as subclasses, superclasses, and inheritance that are particularly useful in representing real-world entities and their relationships in a structured and hierarchical way. These useful features make EER models suitable for applications like as engineering design, telecommunications, and complex organizational systems.

In this chapter, we will understand in detail the concepts of subclasses, superclasses, inheritance, and their associated processes of specialization and generalization with examples for a better understanding.

Subclasses and Superclasses

A superclass shows a broad category of entities, however its subclasses define more specific groupings within that category. The superclass-subclass relationship helps us model hierarchical data.

For example, consider the EMPLOYEE entity type in a company’s database. It is a broad category that might include subclasses such as:

• SECRETARY − Employees who handle administrative tasks.
• TECHNICIAN − Employees who provide technical support or services.
• ENGINEER − Employees who design and develop systems or products.

The EMPLOYEE entity type is like the superclass, it has the shared characteristics of all employees. The specific roles like ENGINEER or SECRETARY are the subclasses.

The Class / Subclass Relationship

The connection between a superclass and its subclasses is known as the class-subclass relationship. This relationship shows that entities in a subclass are also members of the superclass.

For example, a TECHNICIAN entity is an EMPLOYEE with additional characteristics specific to the TECHNICIAN role. Similarly, a SECRETARY is an EMPLOYEE but with unique attributes, such as Typing_speed. This relationship is often described using an “is-a” relationship:

• A TECHNICIAN is an
• A SECRETARY is an

EER diagrams illustrate these relationships clearly –

• Like ER diagrams, rectangles represent entities (both superclasses and subclasses).
• Circles denote specialization or generalization processes.
• Subset symbols point from subclasses back to their superclass.

For example, the EMPLOYEE superclass connects to its subclasses through a circle. Specific attributes, like Typing_speed for SECRETARY, are attached to the respective subclass rectangle.

Inheritance in Superclass-Subclass Relationship

Inheritance is one of the key features of the superclass-subclass relationship. Inheritance ensures that entities in a subclass retain the attributes and relationships of their superclass. It is used to enables reuse and prevents redundancy.

Attribute Inheritance

Entities in a subclass inherit −

• Superclass Attributes − For instance, all EMPLOYEES might have attributes such as Name, SSN, and Address. These are inherited by subclasses like ENGINEER and TECHNICIAN.
• Subclass-Specific Attributes − Each subclass has its unique attributes. For example –
  • SECRETARY might have Typing_speed.
  • TECHNICIAN might have Tgrade.

This hierarchical organization helps ensure that common characteristics remain at the superclass level while allowing subclasses to specialize further.

Relationship Inheritance

Subclasses also inherit the relationships of their superclasses. For example, If EMPLOYEE participates in a MANAGES relationship with a PROJECT, all subclasses of EMPLOYEE inherit this relationship.

This flexibility is useful for subclasses to focus on unique relationships while sharing inherited ones.

Specialization: Defining Subclasses

Specialization is the process of dividing a superclass into meaningful subclasses based on specific characteristics. This process is used in designing database schema that reflects real-world distinctions.

Types of Specialization

Predicate-Defined Specialization − Subclass membership can be found in conditions on a superclass attribute. Like Subclasses of EMPLOYEE might be defined based on the attribute named “Job_type”:

• SECRETARY if Job_type = “Secretary”.
• TECHNICIAN if Job_type = “Technician”.

User-Defined Specialization − Membership in subclasses is manually assigned by users. This is not determined by predefined conditions.

Overlapping vs. Disjoint Specialization

Specializations can be:

• Disjoint − Entities belong to at most one subclass. For example, An EMPLOYEE can be either HOURLY_EMPLOYEE or SALARIED_EMPLOYEE, but not both.
• Overlapping − Entities may belong to multiple subclasses. For example: A SALARIED_EMPLOYEE might also be a MANAGER.

Total vs. Partial Specialization

• Total Specialization − Every entity in the superclass must belong to at least one subclass. Like every EMPLOYEE must be either HOURLY_EMPLOYEE or SALARIED_EMPLOYEE.
• Partial Specialization − Some entities in the superclass do not belong to any subclass. Like some EMPLOYEES might not fit into SECRETARY, ENGINEER, or TECHNICIAN subclasses.

These entities have been represented in the following figure –

Generalization: Abstracting Superclasses

Generalization is the reverse process of specialization. Generalization identifies common attributes or relationships among multiple entity types and abstracts them into a single superclass.

For example, consider the following entity types:

• CAR: Attributes include License_plate_no and Vehicle_id.
• TRUCK: Attributes include Tonnage and No_of_axles.

These entities share common attributes like License_plate_no and Vehicle_id. Through generalization, these common attributes can be combined into a VEHICLE superclass, with CAR and TRUCK as its subclasses.

Combining Specialization and Generalization

In real-world examples, database design is often used in a mix of specialization and generalization –

• Specialization refines entity types by breaking them into specific subgroups.
• Generalization abstracts multiple entity types into broader categories.

For instance, in a university database:

• Specialization might divide PERSON into EMPLOYEE, STUDENT, and ALUMNUS.
• Generalization might combine FACULTY and STAFF into an EMPLOYEE superclass.

Example: University Database

Now let us consider a university database to showcase the practical application of these concepts –

• Superclass − PERSON, with attributes like Name, SSN, Birth_date, and Address.
• Subclasses −
  • EMPLOYEE − Specialized into FACULTY, STAFF, and STUDENT_ASSISTANT.
  • STUDENT − Specialized into GRADUATE_STUDENT and UNDERGRADUATE_STUDENT.

Shared Subclasses and Lattices

Sometimes a subclass belongs to multiple superclasses, forming a shared subclass. For example, STUDENT_ASSISTANT is a subclass of both STUDENT and EMPLOYEE. This creates a lattice structure, where entities inherit attributes from multiple paths.

For example:

• A GRADUATE_STUDENT inherits attributes from STUDENT and PERSON.
• A STUDENT_ASSISTANT inherits from both STUDENT and EMPLOYEE.

Advantages of Subclasses, Superclasses, and Inheritance

Following are the advantages of using subclasses, superclasses, and inheritance in Enhanced ER diagrams –

• Efficient Data Representation − Common attributes are stored in the superclass, reducing redundancy.
• Flexibility − Subclasses can add specific details while retaining shared attributes and relationships.
• Real-World Alignment − The hierarchical structure mirrors real-world scenarios, simplifying database design and usage.

The ER model is used to visually represent the process of designing a database. There are several components in an ER diagram. We can extend this ER model to some extent to make them more versatile.

The Enhanced Entity-Relationship (EER) model extends the basic ER model, by adding advanced concepts to represent more complex data structures. This improvement is particularly valuable for modeling databases in fields that demand greater precision. For example engineering, telecommunications, and geographic information systems.

In this chapter, we will have a look at the main elements of the EER model. In addition, we will touch upon the basics of subclasses, superclasses, inheritance, and specialization. We will cover them in greater detail in the subsequent chapters of this tutorial.

Basics of the Enhanced ER Model

The ER model offers foundational tools for creating database schemas. This is useful in many business and industrial applications. But, as database applications evolved, so did the need for more sophisticated modelling tools. That could be represented through complex relationships and data constraints with higher accuracy. The EER model can meet these needs by adding new features such as subclasses, superclasses, inheritance, and union types.

Subclasses and Superclasses

The EER Model uses concepts such as subclasses and superclasses which are normally used in OOPs programming. A superclass represents a general entity type, while a subclass is a more specific grouping of that entity.

For instance, consider the EMPLOYEE entity type. Within a company, the EMPLOYEE category could include distinct groups such as SECRETARY, ENGINEER, TECHNICIAN, and MANAGER. Now each of these subgroups forms a subclass of EMPLOYEE.

Example Diagram − The EER diagram uses a circle to connect subclasses and their superclass. Lines link these entities, illustrating the relationship. In this case, EMPLOYEE is the superclass, while SECRETARY, ENGINEER, and TECHNICIAN are subclasses.

Here, the relationship between a superclass and its subclasses is often described as an “IS-A” relationship. This type of relationship means that any instance of a subclass is inherently also an instance of the superclass. Like a SECRETARY entity is also an EMPLOYEE entity in the database. This relationship indicates that a subclass can access all attributes and relationships defined in its superclass.

Inheritance of Attributes and Relationships

Inheritance is a popular feature from OOPs programming that allows the entities in a subclass to inherit attributes and relationships from their superclass. For instance, the EMPLOYEE entity type could have common attributes called Name, Ssn, and Address. The SECRETARY subclass may inherit these attributes while adding its own, such as Typing_speed.

Inheritance applies to relationships as well. If the EMPLOYEE entity participates in a PROJECT relationship, the SECRETARY subclass will inherit this participation, ensuring a consistent data structure.

Specialization and Generalization in EER Model

Specialization and generalization are two related processes in the EER model that refine how entities are categorized.

Specialization in EER Model

Specialization shows how to define subsets of an entity type based on distinct attributes or characteristics. In our example, we could create subclasses of EMPLOYEE like HOURLY_EMPLOYEE and SALARIED_EMPLOYEE based on the payment method. This can be done with Pay_scale attribute used for relevant subclass.

So, the subclasses created through specialization may have specific attributes and relationships that do not apply to the superclass.

Generalization in EER Model

Generalization is the opposite of specialization. It combines similar entity types into a broader superclass by identifying shared characteristics. For example, entity types CAR and TRUCK with attributes like License_plate_no and Price could be generalized into a superclass called VEHICLE.

The entity-specific attributes, such as No_of_axles for TRUCK and Max_speed for CAR, would be retained in their respective subclasses while inheriting common attributes from VEHICLE.

Constraints in Specialization and Generalization

Specialization and generalization can include constraints that define how entities belong to subclasses. The two main constraints are explained below –

• Disjointness Constraint − The disjointness constraint indicates that an entity can be a member of only one subclass. If subclasses are disjoint, the EMPLOYEE cannot be both a SECRETARY and an ENGINEER. This is represented in diagrams by a “d” in the circle connecting subclasses to the superclass. Take a look at the figure below.
• Completeness Constraint − The completeness constraint is used if an entity must belong to at least one subclass (total specialization) or if it can exist without being in any subclass (partial specialization). There could be a double line between the superclass and the circle to denote a total specialization, while a single line indicates a partial specialization.

Example − Take a look at the following figure. In Figure 8.1, the specialization {HOURLY_EMPLOYEE, SALARIED_EMPLOYEE} is disjoint and total. Every EMPLOYEE must be either hourly or salaried, but not both.

Using Specialization and Generalization in EER Diagrams

In practice, database designers often use specialization to start with a general entity type and create specific subtypes to handle particular attributes or relationships. Conversely, they may use generalization to combine existing entity types when commonalities are found.

Advanced Features: Union Types and Categories

Sometimes, a subclass may need to inherit from more than one superclass. This type of structure is known as a category or union type. This is used when an entity type shares attributes across multiple parent classes. For instance, an OWNER of a vehicle in a registration database might be a PERSON, a BANK, or a COMPANY. The OWNER subclass would be connected to all these superclasses. In the above figure, we are using the “∪” symbol to represent union relationships.

Conclusion

In this chapter, we learned the Enhanced ER model that extends the traditional ER model by incorporating subclasses, superclasses, type inheritance, and specialization or generalization processes. We also reviewed how these features enable more precise and flexible database schemas. We also touched upon constraints such as disjointness and completeness and looked at union types for complex relationships.

Designing a database becomes easier when we use a graphical representation of the entire database. One of the best tools for this is the Entity-Relationship Diagram (ERD). It helps capture important details about entities, their relationships, and any constraints within a business environment.

In this chapter, we’ll learn how to design an ERD using a real-world example of a COMPANY database. We’ll break down each step, explain the types of entities and relationships, and make the process easy to understand.

Company Database Tables Design

We have the following tables in the COMPANY database −

EMPLOYEE

DEPARTMENT

DEPT_LOCATIONS

PROJECT

WORKS_ON

DEPENDENT

Basics of ER Diagram

To understand the example. Let us see the foundational concepts of an ERD: There are several components.

• Entities: Represent objects or items within a system, such as EMPLOYEE, DEPARTMENT, and PROJECT.
• Attributes: Characteristics that define an entity. For example, an EMPLOYEE may have attributes like Name, Ssn, Address, and Salary.
• Relationships: Connect entities and signify how they interact. For example, an EMPLOYEE may WORKS_FOR a DEPARTMENT.

Visual Representation in ER Diagrams

We use the following shapes for visual representation in ER diagrams −

• Rectangles for entities
• Diamonds for relationships
• Ovals for attributes
• Double ovals for multivalued attributes
• Double Rectangle: Weak Entity
• Double Diamond: Weak Relationship

Real-World Example: COMPANY Database ER Diagram

The COMPANY database shows a complex business structure. Here is how we create an ERD for it, incorporating key entities and relationships:

Identifying the Core Entities

The main entities in the COMPANY database are highlighted below −

EMPLOYEE − Represents staff members with attributes such as Name, Ssn, Salary, and Bdate.

DEPARTMENT − The organizational unit with attributes like Name and Number.

PROJECT − Tasks managed by departments with attributes including Name and Number.

DEPENDENT − Individuals dependent on an employee with attributes like Name, Relationship, and Birth_date.

Defining the Key Relationships

Here are the main relationships connecting these entities:

• MANAGES − A relationship between EMPLOYEE and DEPARTMENT. Each department has one manager, but an employee may or may not manage a department.
• WORKS_FOR − A relationship between DEPARTMENT and EMPLOYEE. Each department has many employees, and each employee must be assigned to a department.
• CONTROLS − A relationship where a DEPARTMENT oversees one or more PROJECTS. A project is always managed by one department.
• SUPERVISION − A relationship where one EMPLOYEE supervises others.
• WORKS_ON − A relationship, showing that employees can work on multiple projects and vice versa. The Hours attribute is linked here to indicate time spent.
• DEPENDENTS_OF − A relationship between EMPLOYEE and DEPENDENT, highlighting that an employee may have one or more dependents.

Structuring the ER Diagram

Let us now understand the step-by-step process of creating the ER diagram:

• Draw the Entities − Create rectangles labelled EMPLOYEE, DEPARTMENT, PROJECT, and DEPENDENT. Connect ovals with key attributes like Name, Ssn, and Salary to the EMPLOYEE. Link attributes like Number to DEPARTMENT. We have seen the entity in the previous figures.
• Define the Relationships − Use diamonds to represent relationships like WORKS_FOR, MANAGES, etc. Connect these diamonds to relevant entities using straight lines. Use single lines for partial participation and double lines for total participation.

Example Breakdown: The following figure depicts the COMPANY ER Diagram −

• MANAGES shows that each DEPARTMENT must have a manager (total participation for DEPARTMENT). However, an EMPLOYEE may not manage any department (partial participation).
• WORKS_ON connects EMPLOYEE and PROJECT, indicating an M relationship with the attribute Hours. It is describing the duration an employee works on a project.
• DEPENDENTS_OF illustrates EMPLOYEE and DEPENDENT with a 1
• ratio, showing that while employees might not have dependents (partial participation), each DEPENDENT must have an associated EMPLOYEE (total participation).

Special Considerations in Drawing the ER Diagram

Make a note of the following special considerations while drawing an ER diagram:

• Weak Entities and Identifying Relationships: In the ERD, we can see DEPENDENT is a weak entity. It is identified by its connection to EMPLOYEE. It cannot exist independently and is shown in a double rectangle. We can also notice that the DEPENDENTS_OF relationship is an identifying relationship, shown in a double diamond.
• Redundancy and Refinement: Here the attributes should be consolidated into relationships when necessary. For example, Manager and Manager_start_date from DEPARTMENT were moved to the MANAGES relationship to reduce redundancy.
• Composite and Multivalued Attributes: We can also see the attributes like Name for EMPLOYEE may be composite (consisting of Fname, Minit, Lname). Multivalued attributes like Locations for DEPARTMENT are shown using double ovals.
• Role Names − Add role names to relationships when the same entity participates more than once, such as SUPERVISION, where one EMPLOYEE acts as the supervisor and another as the supervisee.

Conclusion

In this chapter, we learned how to design an ER diagram step by step using a real-world example the COMPANY database. We identified the main entities, defined the relationships between them, marked participation and cardinality, and used special elements like weak entities and composite attributes to add clarity. This structured approach helps in designing ER diagrams for visualizing data models clearly for effective database design.