1 / 30

Database Systems: Design, Implementation, and Management Tenth Edition

Database Systems: Design, Implementation, and Management Tenth Edition. Chapter 4 Entity Relationship (ER) Modeling. Connectivity and Cardinality. Connectivity Describes the relationship classification Cardinality

zody
Download Presentation

Database Systems: Design, Implementation, and Management Tenth Edition

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. Database Systems: Design, Implementation, and ManagementTenth Edition Chapter 4 Entity Relationship (ER) Modeling

  2. Connectivity and Cardinality • Connectivity • Describes the relationship classification • Cardinality • Expresses minimum and maximum number of entity occurrences associated with one occurrence of related entity • Established by very concise statements known as business rules Database Systems, 10th Edition

  3. Database Systems, 10th Edition

  4. Derived Attributes • Derived attribute: value may be calculated from other attributes • Need not be physically stored within database Database Systems, 10th Edition

  5. Database Systems, 10th Edition

  6. Existence Dependence • Existence dependence • Entity exists in database only when it is associated with another related entity occurrence • Existence independence • Entity can exist apart from one or more related entities • Sometimes such an entity is referred to as a strong or regular entity Database Systems, 10th Edition

  7. Relationship Strength • Weak (non-identifying) relationships • Exists if PK of related entity does not contain PK component of parent entity • Strong (identifying) relationships • Exists when PK of related entity contains PK component of parent entity Database Systems, 10th Edition

  8. Database Systems, 10th Edition

  9. Database Systems, 10th Edition

  10. Weak Entities • Weak entity meets two conditions • Existence-dependent • Primary key partially or totally derived from parent entity in relationship • Database designer determines whether an entity is weak based on business rules Database Systems, 10th Edition

  11. Database Systems, 10th Edition

  12. Database Systems, 10th Edition

  13. Recursive Relationships • Relationship can exist between occurrences of the same entity set Database Systems, 10th Edition

  14. Recursive Relationships • Relationship can exist between occurrences of the same entity set Database Systems, 10th Edition

  15. Recursive Relationships Database Systems, 10th Edition

  16. Database Systems, 10th Edition

  17. 1:1 Recursive Relationship Implementation • EMPLOYEE_V1 table can yield anomalies if Anne Jones divorces Anton Shapiro • Two records must be updated, if only one is updated then we have inconsistent data • Nulls for those employees not married to other employees • MARRIED_V1 eliminates nulls for employees not married to other employees but duplicate values are still possible (345,347) and (347,345) • Third method requires collection of marriage date. Unless sending out congratulations each year, the information is unnecessary • Theoretically possible to have more than two people in a marriage Database Systems, 10th Edition

  18. Developing an ER Diagram • Database design is an iterative process • 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, 10th Edition

  19. Database Systems, 10th Edition

  20. Database Systems, 10th Edition

  21. Database Systems, 10th Edition

  22. Database Systems, 10th Edition

  23. Database Systems, 10th Edition

  24. Database Systems, 10th Edition

  25. Database Systems, 10th Edition

  26. Database Systems, 10th Edition

  27. Database Systems, 10th Edition

  28. Database Systems, 10th Edition

  29. Design Challenges • Must conform to design standards • Minimize redundancies and avoid nulls when possible • Processing Speed • High processing speeds are a top priority in today’s online world • Combine two tables into one which may be less elegant but faster data retrieval • Include derived attributes instead of computing them as needed • Information Requirements • Complex information requirements might require additional entities and attributes within the design • May be worth sacrificing “clean” design and speed to ensure maximum information generation Database Systems, 10th Edition

  30. Summary (cont’d.) • Connectivities and cardinalities are based on business rules • M:N relationship is valid at conceptual level • Must be mapped to a set of 1:M relationships • ERDs may be based on many different ERMs • UML class diagrams are used to represent the static data structures in a data model • Database designers are often forced to make design compromises Database Systems, 10th Edition

More Related