Spring 2017

Bojana Bislimovska

CS 338

The Enhanced Entity-Relationship (EER)

Model

Major research

• EER model overview

• Subclasses, superclasses and inheritance

• Specialization and generalization

• Modeling of UNION types using categories

• EER model example

• EER design choices

Outline

Major research

Extension of the ER model

• Design more accurate database schemas

• Address more complex application requirements

• Concepts: inheritance, subclasses, superclasses, specialization, generalization

EER Model

Major research

• Subclass (subtype) of an entity type

e.g. Different EMPLOYEE types (Company DB)

Can have its own attributes and relationship types

• Superclass (supertype) over the subclasses

e.g. EMPLOYEE is a supertype over its subclassess

• An entity type can be a subclass for more than one superclass

• Superclass/subclass relationship

• Type inheritance

Subclass inherits all attributes and relationships from the superclass

• Multiple inheritance

Subclass inherits from more than one superclass

Subclasses, Superclasses and Inheritance

• Process of defining a set of subclasses of an entity type

Specialization

Major research

Generalizes differences of several entity types into a single superclass

• Inverse of specialization

• Common attributes belong to the superclass

Generalization

Major research

• Membership definition constraint

Predicate-defined - condition on some attribute value of a superclass

• Attribute-defined - membership condition on the same attribute

User-defined – specified individually for each entity

• Disjointness constraint – subclasses must be disjoint sets Overlapping - an entity can belong to more than one subclass

• Completeness constraint Total specialization – every entity of the superclass must be a

member of at least one subclass

• Superclass derived by generalization is always total

Partial specialization - an entity does not have to belong to any of the subclasses

• Disjointness and completeness are independent

Constraints on Specialization and Generalization

Major research

• The DB keeps track of three types of persons: employees, alumni, and

students. A person can belong to one, two or all three types. Each person has a name, SSN, sex, address, and birth date.

• 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 university degree(s) she earned is kept, including degree name, year and major department. Each student has a major department.

• Each faculty has a rank, whereas each staff member has a staff position. Student assistants can either be research assistants or teaching assistants and the percent of time they work is recorded in the DB. Research assistants have their research project, whereas teaching assistants have the current course they work on.

• Students can be either graduate or undergraduate, with specific attributes degree program (M.S., Ph.D.,...) for graduate students and class for undergraduates.

Example: UNIVERSITY DB

Major research

• Specialization hierarchy - a subclass participates in exactly one

class/subclass hierarchy (has only one parent)

• Specialization lattice - a subclass can participate in more than one class/subclass relationship

Specialization and Generalization Hierarchies and Lattices

Major research

• Top-down conceptual refinement

Applying specialization: start from an entity type and then define subclasses

• Bottom-up conceptual synthesis

Applying generalization: start from multiple entity types and then define superclasses

Refining Conceptual Schemas

Major research

• UNION type (category): a subclass that is a subset of the

UNION of entities from distinct entity types

• UNION type has two or more superclasses from distinct entity types

• Total type - union of all entities in its superclasses

• Partial type - subset of the union

• Some modeling methods do not have union types

Modeling of UNION Types Using Categories

Major research

UNION Types Example

• For ea h perso , the DB ai tai s i for atio o the perso ’s a e, ssn, address, sex and birth date. Two types of persons can be identified: faculty and students. Each faculty has a rank, office, phone and salary. A faculty member can be related to several academic departments. A specific attribute of student is class. Each student is also related to one of her major and minor departments (if known), to the course sections she is currently attending and the completed courses. The DB keeps track of the grade a student received for a course section.

• Graduate student is a subclass of student based on the value of the predicate class. Each graduate student can have several degrees. Each graduate student has an advisor and a thesis committee, if one exists.

• Department has name, telephone and office number, and is related to the faculty member who is its chair and to the college to which it belongs. Each college has name, offi e u er, a d the dea ’s a e.

University DB Requirements

• Course has a course number, name and description. Several sections of each course are offered. Each section has a unique section number and year and quarter in which the section is offered. Each section has an instructor, if that instructor is in the DB. Some sections may be offered in the current quarter of the current year.

• All faculty members and some graduate students can teach several sections of a course. Grant keeps track of research grants and contracts awarded to the university. Each grant has a grant title, number, awarding agency and starting date. A grant has one principal investigator and it can have several researchers that it supports. For each researcher, the DB keeps track of the start and end date of the support, and percentage of time spent on the project.

University DB Requirements

Major research

EER Model Example: University DB

Major research

• Trade-off between model accuracy and model cluttering

• Subclasses with few specific attributes, and no specific relationships can be merged into the superclass

• Union types should be avoided => use specialization/generalization

• Choice of constraints is driven by the rules in the miniworld

• If requirements have no particular constraints, the default are overlapping and partial constraints

EER Design Choices