1 / 76

Understanding Entity Relationship Modeling in Databases

Explore the principles of Entity-Relationship (E-R) modeling including entities, attributes, relationships, key components, and cardinality in database design. Learn about primary keys, classes of attributes, multivalued attributes, derived attributes, and relationship strengths.

clarkr
Download Presentation

Understanding Entity Relationship Modeling in Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 4Entity Relationship (E-R) Modeling Database Systems: Design, Implementation, and Management Peter Rob & Carlos Coronel

  2. In this chapter, you will learn: • How relationships between entities are defined and refined, and how such relationships are incorporated into the database design process • How ERD components affect database design and implementation • How to interpret the modeling symbols for the four most popular ER modeling tools • That real-world database design often requires that you reconcile conflicting goals

  3. The Entity Relationship (E-R) Model • ER model forms the basis of an ER diagram • ERD represents the conceptual database as viewed by end user • Main Components • Entities • In E-R models an entity refers to the entity set. • An entity is represented by a rectangle containing the entity’s name. • Attributes • Attributes are represented by ovals and are connected to the entity with a line. • Each oval contains the name of the attribute it represents. • Attributes have a domain -- the attribute’s set of possible values. • Attributes may share a domain. • Relationships

  4. The Attributes of the STUDENT Entity

  5. Primary Keys • Underlined in the ER diagram • Key attributes are also underlined in frequently used table structure shorthand • Ideally composed of only a single attribute • Possible to use a composite key: • Primary key composed of more than one attribute

  6. The CLASS Table (Entity) Components and Contents

  7. The Entity Relationship (E-R) Model • Classes of Attributes • A simple attributecannot be subdivided. • Examples: Age, Sex, and Marital status • A composite attribute can be further subdivided to yield additional attributes. • Examples: • ADDRESS Street, City, State, Zip • PHONE NUMBER  Area code, Exchange number

  8. The Entity Relationship (E-R) Model • Classes of Attributes • A single-valued attribute can have only a single value. • Examples: • A person can have only one social security number. • A manufactured part can have only one serial number. • Multivalued attributes can have many values. • Examples: • A person may have several college degrees. • A household may have several phones with different numbers • Multivalued attributes are shown by a double line connecting to the entity.

  9. Multivalued attributes in an Entity

  10. The Entity Relationship (E-R) Model • Multivalued Attribute in Relational DBMS • The relational DBMS cannot implement multivalued attributes. • Possible courses of action for the designer • Within the original entity, create several new attributes, one for each of the original multivalued attribute’s components ( Figure 4.4). • Create a new entity composed of the original multivalued attribute’s components ( Figure 4.5).

  11. Splitting the Multivalued Attribute into New Attributes

  12. A New Entity SetComposed of Multivalued Attribute’s Components

  13. The Entity Relationship (E-R) Model • A derived attribute • may be calculated (derived) from other attributes • Need not be physically stored within the database • Can be derived by using an algorithm • Example: AGE can be derived from the data of birth and the current date.

  14. Depiction of a Derived Attribute

  15. The Entity Relationship (E-R) Model • Relationships • A relationship is an association between entities. • Relationships are represented by diamond-shaped symbols.

  16. The Entity Relationship (E-R) Model • Connectivity • The term connectivity is used to describe the relationship classification (e.g., one-to-one, one-to-many, and many-to-many).

  17. The Entity Relationship (E-R) Model • Cardinality • Cardinality expresses the specific number of entity occurrences associated with one occurrence of the related entity. • The minimum and maximum number of entity occurrences

  18. Connectivity and Cardinality in an ERD

  19. Relationship Strength • Existence Dependent • If an entity’s existencedepends onthe existence of one or more other entities, it is said to be existence-dependent. • CLASS is existence-dependent on COURSE (parent entity) • EMPLOYEE claims DEPENDENT—DEPENDENT is existence-dependent on EMPLOYEE • Existence independent • Entity can exist apart from one or more related entities • Example: • some of parts are produced “in-house” and other parts are bought from vendors.At least some of the parts are not supplied by a vender. • PART is existence-independent from VENDOR

  20. Relationship Strength • Weak (non-identifying) relationship • One entity is notexistence-independent on another entity • PK of related entitydoesn’t contain PK component of parent entity • COURSE( CRS_CODE, …) • CLASS( CLASS_CODE, …) • Strong (identifying)relationship • One entity is existence-dependent on another • PK of related entity contains PK component of parent entity • COURSE( CRS_CODE, …) • CLASS( CRS_CODE, CLASS_SECTION, …)

  21. A Weak Relationship Between COURSE and CLASS

  22. A Strong (Identifying) Relationship Between COURSE and CLASS

  23. The Entity Relationship (E-R) Model • Relationship Participation • Optional • The participation is optional if one entity occurrence does not require a corresponding entity occurrence in a particular relationship. • An optional entity is shown by a small circle on the side of the optional entity. • Mandatory • Entity occurrence requires corresponding occurrence in related entity • If no optionality symbol is shown on ERD, it is mandatory

  24. CLASS is Optional to COURSE • COURSE is Mandatory to CLASS • COURSE and CLASS in a Mandatory Relationship

  25. Relationship Strength and Weak Entities • Weak Entities • A weak entity is an entity that • Is existence-dependent and • Has a primary key that is partially or totallyderived from the parent entity in the relationship. • The existence of a weak entity is indicated by a double rectangle. • The weak entity inheritsall or part of its primary key from its strong counterpart.

  26. A Weak Entity in an ERD • EMPLOYEE( EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_DOB ) • DEPENDENT(EMP_NUM, DEP_NUM, DEP_FNAME, DEP_DOB )Primary Key DEP_NUM

  27. Weak entity in a Strong Relationship Between DEPENDENT and EMPLOYEE ( EMP_NUM ) ( EMP_NUM + DEP_NUM )

  28. Weak entity in a Strong Relationship • Weakrelationship • One entity is not existence-independent on another • PK of related entity doesn’t contain PK component of parent entity • COURSE( CRS_CODE, …) • CLASS( CLASS_CODE, …) • Strongrelationship • One entity is existence-dependent on another • PK of related entity contains PK component of parent entity • COURSE( CRS_CODE, …) • CLASS( CRS_CODE, CLASS_SECTION, …) • In any case, CLASS is always existence-dependent on COURSE, whether or not it is defined to be weak. Not Weak entity

  29. Relationship Degree • A relationship’s degree indicates the number of associated entities or participants. • A unary relationship exists when an association is maintained within a single entity. • A binary relationship exists when two entities are associated. • A ternary relationship exists when three entities are associated.

  30. The Implementation of a Ternary Relationship • 若 FUND(FUND_ID,FUND_NAME)，透過 CFR仍能將所有必要的關係聯繫起來，但 FUND(FUND_ID,FUND_NAME,CONTRIB_ID,FUND_AMOUNT)亦有意義，例如：F2 接受 C3 $10,000 的捐款，還未撥給任何 RECIPIENT Researchers

  31. The Entity Relationship (E-R) Model • Recursive relationship • A recursive relationship is one in which a relationship can exist between occurrences of the same entity set. • A recursive entity is found within a unary relationship.

  32. 1:1 Recursive relationship • EMPLOYEE is married to EMPLOYEE

  33. 1:M Recursive relationshipPART contains PART • each part is used to create only onerotor assembly • C130 = 4× AA21-6 + 2× AB-121 + …

  34. M:N Recursive relationship • PART Contains PART • A part_ can be used to create several different kinds of other parts • A part_ is itself composed of many parts. PART PART PART contains contains PART PART COMPONENT

  35. M:N Recursive relationship • COURSE Requires COURSE

  36. Implementation of the 1:M “EMPLOYEE Manages EMPLOYEE” Recursive Relationship

  37. The Entity Relationship (E-R) Model • Composite Entities • A composite entityis composed of the primary keys of each of the entities to be connected. • The composite entity serves as a bridge between the related entities. • The composite entity may contain additional attributes.

  38. The Entity Relationship (E-R) Model • Composite Entities

  39. Converting the M:N Relationship Into Two 1:M Relationships

  40. At the start of registration • A class may exist even though it contains no students at all • A student has not yet signed up for any classes.

  41. A Composite Entity in the ERD

  42. The Entity Relationship (E-R) Model • Entity Supertypes and Subtypes • Describing the different types of employees within a single entity would be awkward at best. • Example : Aviation business ( Figure 4.27)the special pilot characteristics (EMP_LICENCE, EMP_RATING, EMP_MED_TYPE) would cause a large number of nulls for other employees who are not pilots.

  43. Nulls Created by Unique Attributes

  44. The Entity Relationship (E-R) Model • Generalization hierarchy • Depicts relationships between higher-level supertype and lower-level subtype entities. • Supertype contains the shared attributes • Subtype contains the unique attributes. • A subtype entityinheritsits attributes and its relationshipsfrom the supertype entity.

  45. A Generalization Hierarchy • Disjoint relationships are indicated by G

  46. The Entity Relationship (E-R) Model • Disjoint Supertypes • Also known as non-overlapping subtypes • Subtypes that contain a subset of the supertype entity set • Each entity instance (row) of the supertype can appear in only one of the disjoint subtypes. • The supertype and its subtype(s) maintain a 1:1 relationship.

  47. The EMPLOYEE/PILOT Supertype/Subtype Relationship

  48. A Generalization Hierarchy with Overlapping Subtypes • Overlapping relationships are indicated by Gs

  49. A Comparison of ER Modeling Symbols

More Related