- 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
- 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.
- 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.
- 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
- 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