Enhance Entities Relationship (EER)

What is it?

       Enhance or External Entities Relationship like as its name. It is extended from Entities Relationship Diagram (ERD) which is a diagram show the relationship between entities.

What is difference with (Entities Relationship Diagram) ERD?

      There are three differences between EER and ERD. EER introduce three new concept. They are extension which are added to ERD

1. Total mapping constraints
2. Aggregation at the type level
3. Generalizations and classifications

Firstly, this article will focus to Generalization and Classification (super/sub type)

What is super and sub type?

         Subtype entity is a special case of another entity called its supertype.

         Let’s exanimate some example of super/sub type

         

         Ex: A Company has many employees. They are in 3 groups: Engineer, Secretary and Programmer

         

         In this case, we will have Employee group is common group of Engineer, Secretary and Programmer groups. Employee group will keep common attribute(generalization) between Engineer, Secretary and Programmer such as EmployeeID, Employee_Name, Employee_Address, Employee_Phone

         Three groups Engineer, Secretary and Programmer have some difference attributes (specialization).For instance, Engineer has “Degree” attribute, Secretary has “Type_speed” attribute and Programmer has “Programming_language”.

How to show super/sub type in EER

          There is several ways to show super/sub type in EER. It is dependence what kind of ERD you used. This article introduce 2 kind which are popular in drawing ER

1.       UML language

2.       Information Engineering

How to covert super/sub type to physical database?

          It doesn’t have any rule to show super/sub type entity in database. But the accepted solution is creation each sub and super entity a table. Sub type entity tables will get foreign key from super type entity table and make them become primary key of them

In your example, we have 4 tables:

Physical diagram

View super and subtypes

          When you create each table for super and subtype, you can create view for sub by JOIN and use UNION to show the view of super entity

         Example query

JOIN

select * from Employee join Engineer on Employee.Employee_ID = Engineer.Employee_ID

UNION

select * from Employee join Engineer on Employee.Employee_ID = Engineer.Employee_ID

Union

select * from Employee join Secrectary on Employee.Employee_ID = Secrectary.Employee_ID

Union

select * from Employee join Programmer on Employee.Employee_ID = Programmer.Employee_ID

Continue……