1 / 48

Entity Relationship (E-R) Modeling

Entity Relationship (E-R) Modeling. Learning Objectives. Conceptual model(s) Internal and external models Definition and refinement of relationships between entities during the database design process ERD components and database design and implementation

tiger-glass
Download Presentation

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. Entity Relationship (E-R) Modeling TCSS445A Isabelle Bichindaritz

  2. Learning Objectives • Conceptual model(s) • Internal and external models • Definition and refinement of relationships between entities during the database design process • ERD components and database design and implementation • Interpretation of the modeling symbols for the four most popular E-R modeling tools TCSS445A Isabelle Bichindaritz

  3. Basic Modeling Concepts • Art and science • Good judgment coupled with powerful design tools • Models • “Description or analogy used to visualize something that cannot be directly observed” Webster’s Dictionary • “A model is a representation of the world in simplified terms, it is an abstraction of the real world” • Data Model • Relatively simple representation of complex real-world data structures TCSS445A Isabelle Bichindaritz

  4. Data Models: Degrees of Data Abstraction Figure 3.1 TCSS445A Isabelle Bichindaritz

  5. Degrees of Abstraction • Conceptual • Global view of data from application domain, based on end-users requirements • Basis for identification and description of main data items • ERD used to graphically represent conceptual data model (or class diagram in UML) • Hardware and software (and DBMS) independent • Internal • Representation of database as seen by DBMS • Adapts conceptual model to a specific DBMS • Software dependent TCSS445A Isabelle Bichindaritz

  6. Degrees of Abstraction • External • Users’ views of data environment • Provides subsets of internal view • Makes application program development easier • Facilitates designers’ tasks • Ensures adequacy of conceptual model • Ensures security constraints in design • Physical • Lowest level of abstraction • Software and hardware dependent • Requires definition of physical storage devices and access methods TCSS445A Isabelle Bichindaritz

  7. Degrees of Abstraction • Three main levels of data models: deliverables • Conceptual data model • Project initiation and planning: ERD’s with entities and relationships only • Analysis: ERD’s refined with attributes • Logical data model = Internal + external data model: a set of normalized relations, based on ERD and views/forms design • Physical data model = physical file and database design TCSS445A Isabelle Bichindaritz

  8. Conceptual Data Model Example TCSS445A Isabelle Bichindaritz

  9. Internal / External Data Models Example TCSS445A Isabelle Bichindaritz

  10. The Entity Relationship (E-R) Model • Represents conceptual view • Main Components • Entities • Stands for entity set • Corresponds to entire table, not row • Represented by rectangle • Rows correspond to entity instances or entity occurrences • Attributes • Represented by ovals or in entity • Relationships • Represented by diamonds or just a relationship name TCSS445A Isabelle Bichindaritz

  11. Attributes • Characteristics of entities • Domain is set of possible values ( (true, false), … ) • Primary keys underlined TCSS445A Isabelle Bichindaritz

  12. Simple Cannot be subdivided Age, sex, marital status Composite Can be subdivided into additional attributes Address into street, city, zip Single-valued Can have only a single value Person has one social security number Multi-valued Can have many values Person may have several college degrees Can be represented by a 1-M relationship Derived Can be derived with algorithm Age can be derived from date of birth Attributes TCSS445A Isabelle Bichindaritz

  13. Attributes • Examples:CLASS(CLASS_CODE, CRS_CODE, CLASS_SECTION, CLASS_TIME, CLASS_ROOM, PROF_NUM)CLASS(CRS_CODE, CLASS_SECTION, CLASS_TIME, CLASS_ROOM, PROF_NUM)STUDENT(Student_Id, Student_Name, Address, Phone_Number, Major) TCSS445A Isabelle Bichindaritz

  14. Multivalued Attributes TCSS445A Isabelle Bichindaritz

  15. Multivalued Attributes TCSS445A Isabelle Bichindaritz

  16. Derived Attributes TCSS445A Isabelle Bichindaritz

  17. Relationships • Association between entities • Connected entities are called participants • Operate in both directions • Connectivity describes relationship classification • 1:1, 1:M, M:N • Cardinality • Expresses number of entity occurrences associated with one occurrence of related entity: (1,4), (1,N), … • How many classes does a professor teach ? (1,4) TCSS445A Isabelle Bichindaritz

  18. Connectivity and Cardinality in an ERD Figure 3.12 TCSS445A Isabelle Bichindaritz

  19. Relationship Strength • Existence dependence • Entity’s existence depends on existence of related entities • Existence-independent entities can exist apart from related entities • EMPLOYEE claims DEPENDENT • Weak (non-identifying) • One entity is existence-independent on another • PK of related entity doesn’t contain PK component of parent entity • Strong (identifying) • One entity is existence-dependent on another • PK of related entity contains PK component of parent entity TCSS445A Isabelle Bichindaritz

  20. Weak Relationship IE = Inversion Entity = a non-unique identifier for an entity TCSS445A Isabelle Bichindaritz

  21. Strong Relationship TCSS445A Isabelle Bichindaritz

  22. Relationship Participation • Optional • Entity occurrence does not require a corresponding occurrence in related entity • Shown by drawing a small circle on side of optional entity on ERD • Mandatory • Entity occurrence requires corresponding occurrence in related entity • If no optionality symbol is shown on ERD, it is mandatory TCSS445A Isabelle Bichindaritz

  23. Optional Participation TCSS445A Isabelle Bichindaritz

  24. Weak Entity • Existence-dependent on another entity • Has primary key that is partially or totally derived from parent entity Figure 3.19 TCSS445A Isabelle Bichindaritz

  25. Relationship Degree • Indicates number of associated entities • Unary • Single entity • Recursive • Exists between occurrences of same entity set • Binary • Two entities associated • Ternary • Three entities associated TCSS445A Isabelle Bichindaritz

  26. Three Types of Relationships Figure 3.21 TCSS445A Isabelle Bichindaritz

  27. Composite Entities • Used to ‘bridge’ between M:N relationships • Bridge entities composed of primary keys of each entity needing connection Figure 3.30 TCSS445A Isabelle Bichindaritz

  28. Composite Entities (con’t.) Figure 3.31 TCSS445A Isabelle Bichindaritz

  29. Composite Entities (con’t.) TCSS445A Isabelle Bichindaritz

  30. Entity Supertypes and Subtypes • Generalization hierarchy • Depicts relationships between higher-level supertype and lower-level subtype entities • Supertype has shared attributes • Subtypes have unique attributes • Disjoint relationships • Unique subtypes • Non-overlapping • Indicated with a ‘G’ • Overlapping subtypes use ‘Gs’ Symbol TCSS445A Isabelle Bichindaritz

  31. Generalization Hierarchy with Disjoint Subtypes TCSS445A Isabelle Bichindaritz

  32. Generalization Hierarchy with Overlapping Subtypes Figure 3.35 TCSS445A Isabelle Bichindaritz

  33. Comparison of E-R Modeling Symbols • Alternate styles developed to enable easier use of CASE tools • Chen • Moved conceptual design into practical database design arena • Crow’s Foot • Cannot detail all cardinalities • Rein85 • Similar to Crow’s Foot • Operates at higher level of abstraction • IDEF1X • Derivative of ICAM studies in the late 1970’s • Uses fewer symbols TCSS445A Isabelle Bichindaritz

  34. Comparison of E-R Modeling Symbols Figure 3.36 TCSS445A Isabelle Bichindaritz

  35. Developing an E-R Diagram • Iterative Process • Step1: General narrative of organizational operations developed • Step2: Basic E-R Model graphically depicted and reviewed • Step3: Modifications made to incorporate newly discovered E-R components • Repeat process until designers and users agree E-R Diagram complete TCSS445A Isabelle Bichindaritz

  36. Supertype/Subtype Relationship in an ERD Figure 3.42 TCSS445A Isabelle Bichindaritz

  37. First ERD Segment Established Figure 3.43 TCSS445A Isabelle Bichindaritz

  38. Second and Third ERD Segments Established Figures 3.44 & 3.45 TCSS445A Isabelle Bichindaritz

  39. Fourth and Fifth ERD Segments Established Figures 3.46 & 3.47 TCSS445A Isabelle Bichindaritz

  40. Sixth and Seventh ERD Segments Established Figures 3.48 & 3.49 TCSS445A Isabelle Bichindaritz

  41. Eighth ERD Segment Established Figures 3.50 TCSS445A Isabelle Bichindaritz

  42. Ninth ERD Segment Established Figures 3.51 TCSS445A Isabelle Bichindaritz

  43. Components of E-R Model Table 3.2 TCSS445A Isabelle Bichindaritz

  44. Completed ERD Figure 3.52 TCSS445A Isabelle Bichindaritz

  45. Challenge of Database Design: Conflicting Goals • Database must be designed to conform to design standards • High-speed processing may require design compromises • Quest for timely information may be the focus of database design • Other concerns • Security • Performance • Shared access • Integrity TCSS445A Isabelle Bichindaritz

  46. Burger Inventory Example • The Burger store wants to develop a new inventory system. Analysts have determined that the following data are required to represent the data needed by the inventory system: • An INVOICE includes one or more INVOICE ITEMS, each of which corresponds to an INVENTORY ITEM. Obviously, an INVOICE ITEM cannot exist without an associated INVOICE, and over time, there will be zero to many receipts, or INVOICE ITEMs, for an INVENTORY SYSTEM. TCSS445A Isabelle Bichindaritz

  47. Burger Inventory Example • Each PRODUCT has a RECIPE of INVENTORY ITEMs, containing several RECIPE LINEs. Thus, RECIPE LINE is an associative entity supporting a bill-of-materials type relationship between PRODUCT and INVENTORY ITEM. • A SALE indicates that Burger sells one or more ITEM SALES, each of which corresponds to a PRODUCT. ITEM SALE cannot exist without an associated SALE, and over time there will be zero to many ITEM SALES for a PRODUCT. Note: the following ERD does not represent weak entities,and relationships. Do you see any ? TCSS445A Isabelle Bichindaritz

  48. Burger Inventory Example TCSS445A Isabelle Bichindaritz

More Related