The Enhanced ER (EER) Model

CLO2: Develop conceptual modeling concepts and notations using Entity-Relationship or UML diagrams to analyze and design complex database applications. (Cognitive Skill) Chapter objectives:



Define 3 new concepts:



Sub-classes/Super-classes



Specialization/Generalization



Categories (UNION types)

We learned that databases are useful but need to be carefully designed.

We learned how to design conceptual models using Entity Relationship diagrams We will extend ER diagrams to support some new features.

ER Model Concepts



Entities and Attributes



Entity Types 

Strong Entity



Weak Entity Types



Various Types of Attributes 

Simple, Composite, Multi-valued, Derived, Key Attributes



Relationships and Relationship Types



Constraints on relationships



Roles and Attributes in Relationship Types

W H Y E N H A N C E D E R M O D E L ?

The traditional ER Model concepts were difficult to use for database modelling of complex data. Hence some improvements or enhancements were made to the existing ER Model to make it able to handle the complex applications better.

EER is a high-level data model that incorporates the extensions to the original ER model.

An entity type may have additional meaningful subgroupings of its entities meaningful to the organization  Example: EMPLOYEE may be further grouped into:

 SECRETARY, ENGINEER, TECHNICIAN, ...

 Based on the EMPLOYEE’s Job

 MANAGER

 EMPLOYEEs who are managers (the role they play)

 SALARIED_EMPLOYEE, HOURLY_EMPLOYEE

 Based on the EMPLOYEE’s method of pay

EER diagrams extend ER diagrams to represent these additional subgroupings, called subclasses or subtypes

A member of the superclass can be optionally included as a member of any number of its subclasses Examples:



A salaried employee who is also an engineer belongs to the two subclasses:

 ENGINEER, and  SALARIED_EMPLOYEE



A salaried employee who is also an engineering manager belongs to the three subclasses:

 MANAGER,  ENGINEER, and  SALARIED_EMPLOYEE

An entity that is member of a subclass inherits:



All attributes of the entity as a member of the superclass



All relationships of the entity as a member of the superclass

Example:



In the previous slide, SECRETARY (as well as TECHNICIAN and ENGINEER) inherit the attributes Name, SSN, ..., from EMPLOYEE



Every SECRETARY entity will have values for the inherited attributes

There are two reasons for including superclass/subclass relationship in data model:



Certain attributes may apply to some but not to all entities of the superclass. Thus, a subclass is defined to group the entities to which these attributes apply.



Some relationship types can only allow certain sub-types to participate.

Generalization is the reverse of the specialization process Several classes with common features are generalized into a superclass; 

original classes become its subclasses

Example: CAR, TRUCK generalized into VEHICLE; 

both CAR, TRUCK become subclasses of the superclass VEHICLE.



We can view {CAR, TRUCK} as a specialization of VEHICLE



Alternatively, we can view VEHICLE as a generalization of CAR and TRUCK

If all subclasses in a specialization have membership condition on same attribute of the superclass, specialization is called an attribute-defined specialization 

Attribute is called the defining attribute of the specialization or Discriminator.

Example: Job Type is the defining attribute of the specialization

{SECRETARY,

TECHNICIAN,

ENGINEER} of EMPLOYEE

CONSTRAINTS ON SPECIALIZATION AND GENERALIZATION (3) Completeness (Exhaustiveness) Constraint:



Total

specifies that every entity in the superclass must be a

member of some subclass in the specialization/generalization



The same as total participation constraint in ER

double line



Shown in EER diagrams by a



Partial allows an entity not to belong to any of the subclasses



Shown in EER diagrams by a single line

TOTAL PARTICIPATION

P A R T I A L P A R T I C I P A T I O N

The requirements for this UNIVERSITY database are the following:

1. The database keeps track of three types of persons: employees, alumni, and students. A person can belong to one, two, or all three of these types. Each person has a name, SSN, sex, address, and birth date.
2. Every employee has a salary, and there are three types of employees: faculty, staff, and student assistants. Each employee belongs to exactly one of these types. For each alumnus, a record of the degree or degrees that he or she earned at the university is kept, including the name of the degree, the year granted, and the major department. Each student has a major

The requirements for this UNIVERSITY database are the following:

1. Each faculty has a rank, whereas each staff member has a staff position. Student assistants are classified further as either research assistants or teaching assistants, and the percent of time that they work is recorded in the database. Research assistants have their research project stored, whereas teaching assistants have the current course they work on.
2. Students are further classified as either graduate or undergraduate, with the specific attributes degree program (M.S., Ph.D.,

M.B.A., and so on) for graduate students and class (freshman, sophomore, and so

The super-classes of a category may have different key attributes, as demonstrated by the OWNER category, or they may have the same key attribute, as demonstrated by the REGISTERED_VEHICLE category.

Notice that if a category is total (not partial), it may be represented alternatively as a total specialization (or a total generalization). In this case, the choice of which representation to use is subjective.

e

If the two classes represent the same type of entities and share numerous attributes, including the same key attributes, specialization/generalization is preferred; otherwise, categorization (union type) is more appropriate.

Consider a University Database System used at PSU, which stores information about the:

 Student Club identified by ClubName, President, and NumofMembers.

 Sports team identified by the TeamName, Coach, and the SportsName.

 Department identified by the DeptName and OfficeNum.

 A Sponsor of an event is identified by SponsorID. A sponsor may be a team, a department, or a

club. Each sponsor entity instance is a member of one of these superclasses, so Sponsor is a subclass of Team, Dept, Club. Not every Club, Team, or Dept must be a sponsor.

 A sponsor holds one or many CampusWide Events. However, an event must be organized by one

sponsor. CampusWide Events are identified by EventName, Date and time. CampusWideEvents can be of two types: Concerts and Fair. A Concert has a performer, and a Fair can also be described by Theme and Charity.

Draw an ER Diagram for the above scenario, showing cardinality and participation for all

 Super-classes and sub-classes (Generalization and specialization)  Categories or Union types  Aggregation All of these concepts improve the expressive power of ER diagrams and enable us to design very complex database applications.

Next, we will see how to create logical designs and dive into the relational model.

1. Form a team of 3 or 4
2. Find a relational database management system that you want to use and start preparing by:
3. Installing the DBMS somewhere.
4. Making sure the installation is working.
5. Letting me know which system you will be using.
6. Make a team decision on the technology you will use.
7. Start thinking about the data and domain you will explore. I can provide some ideas if you need.