DBMS- Enhanced- E- R Model, Study notes of Database Management Systems (DBMS)

Download DBMS- Enhanced- E- R Model and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!

MODULE 03D: MODULE 03D:

The Enhanced E-R Model and The Enhanced E-R Model and

Business Rules Business Rules

Specialization Specialization

Top-down design process; we designate subgroupings within an entity set that are distinctive from other entities in the set.  These subgroupings become lower-level entity sets that have attributes or participate in relationships that do not apply to the higher-level entity set.

Specialization Example Specialization Example

Generalization 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 terms specialization and generalization are used interchangeably.

Design Constraints on a Design Constraints on a Specialization/Generalization Specialization/Generalization  (^) Constraint on which entities can be members of a given lower-level entity set.

• (^) condition-defined  E.g. all customers over 65 years are members of senior-citizen entity set; senior-citizen ISA person.
• (^) user-defined  (^) 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 Design Constraints on a Specialization/Generalization Design Constraints on a Specialization/Generalization

Aggregation Aggregation  (^) Consider the ternary relationship workson , which we saw earlier  (^) Suppose we want to record managers for tasks performed by an employee at a branch

Aggregation (Cont.) Aggregation (Cont.)

Relationship sets works-on and manages represent overlapping information

• (^) Every manages relationship corresponds to a works- on relationship
• (^) However, some works-on relationships may not correspond to any manages relationships  (^) So we can’t discard the works-on relationship

Aggregation Aggregation

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-ER Design Decisions E-ER 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.

E-R Diagram for a Banking Enterprise E-R Diagram for a Banking Enterprise

Supertypes and Subtypes Supertypes and Subtypes

 Subtype:Subtype: A subgrouping of the entities in an entity type which has attributes that are distinct from those in other subgroupings  Supertype:Supertype: An generic entity type that has a relationship with one or more subtypes  Inheritance:Inheritance:

• (^) Subtype entities inherit values of all attributes of the supertype
• (^) An instance of a subtype is also an instance of the supertype

Employee supertype with three subtypes All employee subtypes will have emp nbr, name, address, and date-hired Each employee subtype will also have its own attributes

Relationships and Subtypes Relationships and Subtypes

Relationships at the supertype level indicate that all subtypes will participate in the relationship  The instances of a subtype may participate in a relationship unique to that subtype. In this situation, the relationship is shown at the subtype level