1 / 43

Chapter 4

Chapter 4. Entity Relationship (ER) Modeling Database Systems: Design, Implementation, and Management, Seventh Edition, Rob and Coronel. The Entity Relationship (ER) Model. ER model forms the basis of an ER diagram ERD represents conceptual database as viewed by end user

damien
Download Presentation

Chapter 4

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 4 Entity Relationship (ER) Modeling Database Systems: Design, Implementation, and Management, Seventh Edition, Rob and Coronel

  2. The Entity Relationship (ER) Model • ER model forms the basis of an ER diagram • ERD represents conceptual database as viewed by end user • ERDs depict database’s main components: • Entities • Attributes • Relationships Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  3. 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 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  4. Attributes • Characteristics of entities • Sometimes referred to as properties • 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 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  5. Attributes (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  6. Primary Keys • Underlined in the ERD • Key attributes are also underlined in frequently used table structure shorthand Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  7. The Entity Relationship (E-R) Model • Classes of Attributes • A simple attribute cannot 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 • NAME  last, first, Middle Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  8. The Attributes of the STUDENT Entity First Initial Last Name STUDENT STU ID No longer a Figure Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  9. 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. • A student can only have one GPA • Multivalued attributes can have many values. • Examples: • A person may have several college degrees. • A household may have several phones with different numbers • Cars can have multiple colors • Multivalued attributes are shown by a double line connecting to the entity. Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  10. Multivalued Attributes (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  11. The Entity Relationship (E-R) Model • Multivalued Attribute in Relational DBMS • relational DBMS cannot implement multivalued attributes. • Possible courses of action for the designer • 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). Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  12. Resolving Multivalued Attribute Problems (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  13. The Entity Relationship (E-R) Model • A derived attribute is not physically stored within the database; instead, it is derived (calculated) by using an algorithm. • Example: AGE can be derived from the data of birth and the current date. • Could be stored in DB if you understand the trade-off and decide it is worth it Figure 4.6 A Derived Attribute Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  14. Derived Attributes (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  15. The Entity Relationship (E-R) Model • Cardinality • Cardinality expresses the specific number of entity occurrences associated with one occurrence of the related entity. Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel No longer a Figure: Connectivity and Cardinality in an ERD

  16. Connectivity and Cardinality (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  17. Existence Dependence • Existence dependence • Exist in database only when it is associated with another related entity occurrence • Means FK cannot be null • Existence independence • Entity can exist apart from one or more related entities • Sometimes refers to such an entity as strong or regular entity Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  18. Relationship Strength • Weak (non-identifying) relationships • Exists if PK of related entity does not contain PK component of parent entity • (related PK is only the FK) • Strong (Identifying) Relationships • Exists when PK of related entity contains PK component of parent entity Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  19. Weak (Non-Identifying) Relationships Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  20. Weak (Non-Identifying) Relationships (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  21. Strong (Identifying) Relationships Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  22. The Entity Relationship (E-R) Model • Weak Entities • A weak entity is an entity that • Is existence-dependent and • Has a primary key that is partially or totally derived from the parent entity in the relationship. • Hence the weak entity is the dependent entity involved in a strong (identifying) relationship • The existence of a weak entity is indicated by a double rectangle. (in Chen notation) • The weak entity inherits all or part of its primary key from its strong counterpart. Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  23. Weak Entities (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  24. Relationship Participation • Optional participation • One entity occurrence does not require corresponding entity occurrence in particular relationship • Mandatory participation • One entity occurrence requires corresponding entity occurrence in particular relationship Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  25. Relationship Participation (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  26. Relationship Participation (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  27. Relationship Participation (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  28. Relationship Participation (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  29. Relationship Degree • Indicates number of entities or participants associated with a relationship • Unary relationship • Association is maintained within single entity • Binary relationship • Two entities are associated • Ternary relationship • Three entities are associated Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  30. Three Degrees of Relationships Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  31. Relationship Degree (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  32. Relationship Degree (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  33. Recursive Relationships (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  34. Recursive Relationships (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  35. Recursive Relationships (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  36. Recursive Relationships (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  37. Composite Entities • Also known as bridge entities • Composed of primary keys of each of the entities to be connected • May also contain additional attributes that play no role in connective process Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  38. Composite Entities (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  39. Composite Entities (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  40. Developing an ER Diagram • Database design is an iterative rather than a linear or sequential process • begins with a general narrative (problem description) • Iterative process • E-R model is created and presented for review. • process repeated until the end users and designers agree Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  41. Developing an ER Diagram (continued) • Building an ERD usually involves the following activities: • Create detailed narrative of organization’s description of operations • Identify business rules based on description of operations • Identify main entities and relationships from business rules • Develop initial ERD • Identify attributes and primary keys that adequately describe entities • Revise and review ERD Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  42. Database Design Challenges: Conflicting Goals • Database design must conform to design standards – avoidance of redundancy / protection against inconsistency • High processing speeds are often a top priority in database design – may lead to fewer tables than would be ideal design re: above • Quest for timely information might be focus of database design Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  43. End Chapter 4 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

More Related