ER-model

Participation Constraints

• Total participation (indicated by double line): every entity in the entity set participates in at least one relationship in the relationship set

Ø E.g. participation of loan in borrower is total

Ø every loan must have a customer associated to it via borrower

• Partial participation: some entities may not participate in any relationship in the relationship set

Weak Entities

• An entity set that does not have a primary key is referred to as a weak entity set.
• The existence of a weak entity set depends on the existence of a identifying entity set

Ø it must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set

• The discriminator (or partial key) of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity set.
• The primary key of a weak entity set : Primary key of the strong entity set + the weak entity set’s discriminator.

Class Hierarchies

• Specialization : Top-down design process; we designate subgroupings within an entity set that are distinctive from other entities in the set.
• These sub groupings become lower-level entity sets that have attributes or participate in relationships that do not apply to the higher-level entity set.
• Attribute inheritance – a lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked.
• Generalization : A bottom-up design process – combine a number of entity sets that share the same features into a higher-level entity set.
• Specialization and generalization are simple inversions of each other; they are represented in an E-R diagram in the same way.

Specialization

Specialization/Generalization

• The ISA relationship also referred to as superclass – subclass relationship
• Constraint on whether or not entities may belong to more than one lower-level entity set within a single generalization.

Ø Disjoint : an entity can belong to only one lower-level entity set. Noted in E-R diagram by writing disjoint next to the ISA triangle

Ø Overlapping : an entity can belong to more than one lower-level entity set

• Completeness constraint -- specifies whether or not an entity in the higher-level entity set must belong to at least one of the lower-level entity sets within a generalization.

Ø Total : an entity must belong to one of the lower-level entity sets

Ø Partial: an entity need not belong to one of the lower-level entity sets

Aggregation

• Suppose we want to record managers for tasks performed by an employee at a branch

• Relationship sets works-on and manages represent overlapping information

• Eliminate this redundancy via aggregation

Ø Treat relationship as an abstract entity

Ø Allows relationships between relationships

Ø Abstraction of relationship into new entity

• • Without introducing redundancy, the following diagram represents:

Ø An employee works on a particular job at a particular branch

Ø An employee, branch, job combination may have an associated manager

E-R Design Decisions

• The use of an attribute or entity set to represent an object.
• Whether a real-world concept is best expressed by an entity set or a relationship set.
• The use of a ternary relationship versus a pair of binary relationships.
• The use of a strong or weak entity set.
• The use of specialization/generalization – contributes to modularity in the design.
• The use of aggregation – can treat the aggregate entity set as a single unit without concern for the details of its internal structure.

Entity Vs Attribute

• Should ‘Address’ be an attribute of ‘Employees’ or an entity connected to ‘Employees’ through a relationship
• Depends upon the usage of the data and the data semantics:

Ø If we have several addresses per employee, address must be an entity

Ø If the structure (city, state, etc) are important, address must be modeled as an entity

Entity Vs Relationship

• The diagram is valid if the manager gets a separate discretionary budget for each dept.
• What if the manager gets a discretionary budget that covers all managed departments?

Ø Redundancy : dbudget stored for each dept. manage by the manager

Ø Misleading : suggests that dbudget associated with dept-mgr combination

Binary Vs Ternary relationship

• This ER-diagram models a situation in which an employee can own several policies, each policy can be owned by several Employees, and each dependent can be covered by several policies
• Suppose we have the following additional constraints :

Ø A policy cannot be owned jointly by two or more employees

Ø Every policy must be owned by some employee

Ø Dependents is a weak entity set, and each dependent entity is uniquely identified by the combination pname+policyid

Aggregation Vs Ternary relationship