1 / 39

Chapter 4 Entity Relationship (E-R) Modeling

Chapter 4 Entity Relationship (E-R) Modeling. Database Systems: Design, Implementation, and Management Peter Rob & Carlos Coronel. In this chapter, you will learn:.

xiang
Download Presentation

Chapter 4 Entity Relationship (E-R) Modeling

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. Entities • Refers to entity set and not to single entity occurrence • Corresponds to table and not to row in relational environment • In both Chen and Crow’s Foot models, entity is represented by rectangle containing entity’s name • Entity name, a noun, is usually written in capital letters

  5. Attributes • Characteristics of entities • In Chen model, • attributes are represented by ovals and are connected to entity rectangle with a line • Each oval contains the name of attribute it represents • In Crow’s Foot model, attributes are written in attribute box below entity rectangle

  6. The Attributes of the STUDENT Entity

  7. Domains • Attributes have domain • Domain is attribute’s set of possible values • Attributes may share a domain

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

  9. Primary Keys (CLASS_CODE) Another possible Composite Primary Key (CRS_CODE + CLASS_SECTION) Composite Primary Keys (continued)

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

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

  12. Multivalued attributes in an Entity

  13. Resolving Multivalued Attribute Problems • Although conceptual model can handle M:N relationships and multivalued attributes, you should not implement them in relational DBMS. • 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).

  14. Splitting the Multivalued Attribute into New Attributes

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

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

  17. Derived Attributes • 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.

  18. Depiction of a Derived Attribute

  19. Derived Attributes (continued)

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

  21. Relationships • Association between entities • Participantsare entities that participate in a relationship • Relationships between entities always operate in both directions • Relationship can be classified as 1:M • Relationship classification is difficult to establish if know only one side of the relationship

  22. Connectivity • The term connectivity is used to describe the relationship classification (e.g., one-to-one, one-to-many, and many-to-many).

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

  24. Connectivity and Cardinality in an ERD

  25. Connectivity and Cardinality

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

  27. Relationship Strength • Weak (non-identifying) relationship • One entity is notexistence-independent on another entity • exist if the PK of the related entitydoesn’t contain a PK component of the parent entity • COURSE( CRS_CODE, …) • CLASS( CLASS_CODE, …) • Strong (identifying)relationship • exist when the related entities are existent-dependent andthe PK of the dependent entity contains a PK component of the parent entity • COURSE( CRS_CODE, …) • CLASS( CRS_CODE, CLASS_SECTION, …)

  28. A Weak Relationship Between COURSE and CLASS

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

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

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

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

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

  34. Relationship Participation • Optional participation • 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 participation • One entity occurrence requires corresponding occurrence in related entity • If no optionality symbol is shown on ERD, it is mandatory

  35. CLASS is Optional to PROFESSOR • PROFESSOR is Mandatory to CLASS Relationship Participation (continued)

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

  37. CLASS is Optional to COURSE • COURSE is Mandatory to CLASS Relationship Participation (continued)

  38. COURSE and CLASS in a Mandatory Relationship Relationship Participation (continued)

  39. Relationship Participation (continued)

More Related