Entity-Relationship Model for Data Modeling

1. Chapter Five Data Modeling with the Entity-Relationship Model

2. The Data Model • A data model is a plan, or blueprint, for a database design. • It is more generalized & abstract than a database design. • It is easier to change a conceptual data model than it is to change an implemented database design. • Modeling is not easy • You must abstract the important details of an organization.

3. Entity-Relationship Model • The ER model consists of a set of graphical symbols that are used to create conceptual models of databases. • There are several versions • Original E-R model— Chen (1976). • Extended E-R model — Chen model extensions. • Information Engineering (IE)— Martin (1990); it uses “crow’s foot” notation. • IDEF1X— A national standard developed by the National Institute of Standards and Technology. • Unified Modeling Language (UML)— The Object Management Group; it supports object-oriented methodology.

4. Entities • Things that can be identified and that users want to track • Entity class— a collection of entities of a given type • Entity instance— the occurrence of a particular entity • There are usually many instances of an entity in an entity class. • Entities translate into database tables.

5. CUSTOMER:The Entity Class and Two Entity Instances

6. Attributes • Attributes are an entity’s characteristics. • All entity instances of a given entity class have the same attributes, but vary in the values of those attributes. • Shown in Chen data models as ellipses. • In crow’s foot model, they’re listed within a rectangular form.

7. EMPLOYEE:Attributes in Chen

8. EMPLOYEE:Attributes in Crow’s Foot

9. Identifiers • Identifiers are attributes that uniquely identify entity instances. • May be simple or composite. • Identifiers in data models become keys in database implementations: • Entities have identifiers. • Tables (or relations) have keys. • We often use the terms interchangeably.

10. Relationships • Entities are associated with one another in relationships: • Relationship classes: associations among entity classes • Relationship instances: associations among entity instances • A relationship class can involve two or more entity classes. • If two, it’s binary • If three, it’s ternary

11. Binary Relationship

12. Ternary Relationship PRESCRIPTION DOCTOR MEDICINE PATIENT

13. Relationship Cardinality • The maximum cardinality is the maximum number of entity instances that can participate in a relationship. • The minimum cardinality is the minimum number of entity instances that must participate in a relationship.

14. Maximum Cardinality • Maximum cardinality is the maximum number of entity instances that can participate in a relationship. • Three categories: • One-to-One [1:1] • One-to-Many [1:N] • Many-to-Many [N:M]

15. The Three Types ofMaximum Cardinality

16. Minimum Cardinality • Minimum cardinality is the minimum number of entity instances that must participate in a relationship. • Minimums are generally stated as either 0 or 1: • If 0, participation in the relationship by the entity is optional • Ie, no entity instance must participate in the relationship. • If 1, participation in the relationship by the entity is mandatory • Ie, at least one entity instance must participate in the relationship.

17. The Three Types ofMinimum Cardinality

18. Data Modeling Notation

19. Data Modeling Notation:ERwin

20. Data Modeling Notation:N:M and O-M Note that: (1) ERwin cannot indicate true minimum cardinalities on N:M relationships (2) Visio introduces the intersection table instead of using a true N:M model

21. ID-Dependent Entities • An ID-dependent entity is an entity whose identifier includes the identifier of another entity. • The ID-dependent entity is a logical extension of the independent entity: • BUILDING : APARTMENT • PAINTING : PRINT • The minimum cardinality from the ID-dependent entity is always one.

22. ID-Dependent Entities A solid line indicates an identifying relationship

23. Weak Entities • A weak entity is an entity whose existence depends upon another entity. • All ID-Dependent entities are weak. • But not all weak entities are ID-Dependent. • The identifier of the parent does not appear in the identifier of the weak child entity.

24. Weak Entities (Continued) Weak entities must be indicated by an accompanying text box in Erwin – There is no specific notation for a nonidentifying but weak entity relationship A dashed line indicates a nonidentifying relationship

25. ID-Dependent and Weak Entities

26. Subtype Entities • A subtype entity is a special case of a supertype entity: • STUDENT : UNDERGRADUATE or GRADUATE • The supertype contains all common attributes, while the subtypes contain specific attributes. • The supertype may have a discriminator attribute that indicates the subtype.

27. Subtypes with a Discriminator

28. Subtypes: Exclusive or Inclusive • If subtypes are exclusive, one supertype relates to at most one subtype. • If subtypes are inclusive, one supertype can relate to one or more subtypes.

29. Subtypes: Exclusive or Inclusive (Continued)

30. Subtypes: IS-A relationships • Relationships connecting supertypes and subtypes are called IS-A relationships, because a subtype IS A supertype. • The identifer of the supertype and all of its subtypes must be identical, i.e., the identifier of the supertype becomes the identifier of the related subtype(s). • Subtypes are used to avoid value-inappropriate nulls.

31. ERwin Symbol Summary

32. ERwin Symbol Summary (Continued)

33. David M. Kroenke’s Database Processing Fundamentals, Design, and Implementation(10th Edition) End of Presentation: Chapter Five Part One