ERD Lecture Notes PDF

4 Chapter 4 Entity Relationship (ER) Modeling Database Systems: Design, Implementation, and Management, Sixth Edition, Rob and Coronel Database Systems: Design, Implementation, & Management, 6th 1 Edition, Rob & Corone...

4 Chapter 4 Entity Relationship (ER) Modeling Database Systems: Design, Implementation, and Management, Sixth Edition, Rob and Coronel Database Systems: Design, Implementation, & Management, 6th 1 Edition, Rob & Coronel  4 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 Database Systems: Design, Implementation, & Management, 6th 2 Edition, Rob & Coronel  4 The Entity Relationship (ER) Model ER model forms the basis of an ER diagram ERD represents the conceptual database as viewed by end user ERDs depict the ER model’s three main components: – Entities – Attributes – Relationships Database Systems: Design, Implementation, & Management, 6th 3 Edition, Rob & Coronel  4 Entities Refers to the entity set and not to a single entity occurrence Corresponds to a table and not to a row in the relational environment In both the Chen and Crow’s Foot models, an entity is represented by a rectangle containing the entity’s name Entity name, a noun, is usually written in capital letters Database Systems: Design, Implementation, & Management, 6th 4 Edition, Rob & Coronel  4 Attributes Characteristics of entities In Chen model, attributes are represented by ovals and are connected to the entity rectangle with a line Each oval contains the name of the attribute it represents In the Crow’s Foot model, the attributes are simply written in the attribute box below the entity rectangle Database Systems: Design, Implementation, & Management, 6th 5 Edition, Rob & Coronel  4 The Attributes of the STUDENT Entity Database Systems: Design, Implementation, & Management, 6th 6 Edition, Rob & Coronel  4 Domains Attributes have a domain: – The attribute’s set of possible values Attributes may share a domain Database Systems: Design, Implementation, & Management, 6th 7 Edition, Rob & Coronel  4 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 Database Systems: Design, Implementation, & Management, 6th 8 Edition, Rob & Coronel  4 The CLASS Table (Entity) Components and Contents Database Systems: Design, Implementation, & Management, 6th 9 Edition, Rob & Coronel  4 Attributes Composite attribute Simple attribute Single-value attribute Multivalued attributes Database Systems: Design, Implementation, & Management, 6th 10 Edition, Rob & Coronel  4 A Multivalued Attribute in an Entity Database Systems: Design, Implementation, & Management, 6th 11 Edition, Rob & Coronel  4 Resolving Multivalued Attribute Problems Although the conceptual model can handle multivalued attributes, you should not implement them in the relational DBMS – Within original entity, create several new attributes, one for each of the original multivalued attribute’s components Can lead to major structural problems in the table – Create a new entity composed of original multivalued attribute’s components Database Systems: Design, Implementation, & Management, 6th 12 Edition, Rob & Coronel  4 Splitting the Multivalued Attribute into New Attributes Database Systems: Design, Implementation, & Management, 6th 13 Edition, Rob & Coronel  4 Components of the Multivalued Attribute Database Systems: Design, Implementation, & Management, 6th 14 Edition, Rob & Coronel  4 A New Entity Set Composed of a Multivalued Attribute’s Components Database Systems: Design, Implementation, & Management, 6th 15 Edition, Rob & Coronel  4 Derived Attributes Attribute whose value may be calculated (derived) from other attributes Need not be physically stored within the database Can be derived by using an algorithm Database Systems: Design, Implementation, & Management, 6th 16 Edition, Rob & Coronel  4 Depiction of a Derived Attribute Database Systems: Design, Implementation, & Management, 6th 17 Edition, Rob & Coronel  4 Relationships Association between entities Participants: – 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 you only know one side Database Systems: Design, Implementation, & Management, 6th 18 Edition, Rob & Coronel  4 Connectivity and Cardinality Connectivity – Used to describe the relationship classification Cardinality – Expresses the specific number of entity occurrences associated with one occurrence of the related entity Established by very concise statements known as business rules Database Systems: Design, Implementation, & Management, 6th 19 Edition, Rob & Coronel  4 Connectivity and Cardinality in an ERD Database Systems: Design, Implementation, & Management, 6th 20 Edition, Rob & Coronel  4 RELATIONSHIP Strength Existence dependence – Entity’s existence depends on the existence of one or more other entities Existence independence – Entity can exist apart from one or more related entities Weak (non-identifying) relationships – One entity is not existence-independent on another entity Strong (Identifying) Relationships – Related entities are existence-dependent Database Systems: Design, Implementation, & Management, 6th 21 Edition, Rob & Coronel  4 A Weak (Non-Identifying) Relationship Between COURSE and CLASS Database Systems: Design, Implementation, & Management, 6th 22 Edition, Rob & Coronel  4 A Weak Relationship Between COURSE and CLASS Database Systems: Design, Implementation, & Management, 6th 23 Edition, Rob & Coronel  4 Relationship Participation Optional: – One entity occurrence does not require a corresponding entity occurrence in a particular relationship Mandatory: – One entity occurrence requires a corresponding entity occurrence in a particular relationship Database Systems: Design, Implementation, & Management, 6th 24 Edition, Rob & Coronel  A Strong (Identifying) Relationship 4 Between COURSE and CLASS Database Systems: Design, Implementation, & Management, 6th 25 Edition, Rob & Coronel  4 An Optional CLASS Entity in the Relationship PROFESSOR teaches CLASS Database Systems: Design, Implementation, & Management, 6th 26 Edition, Rob & Coronel  4 COURSE and CLASS in a Mandatory Relationship Database Systems: Design, Implementation, & Management, 6th 27 Edition, Rob & Coronel  4 Relationship Strength and Weak Entities Weak entity meets two conditions – Existence-dependent: Cannot exist without entity with which it has a relationship – Has primary key that is partially or totally derived from the parent entity in the relationship Database designer usually determines whether an entity can be described as weak based on the business rules Database Systems: Design, Implementation, & Management, 6th 28 Edition, Rob & Coronel  4 A Weak Entity in an ERD Database Systems: Design, Implementation, & Management, 6th 29 Edition, Rob & Coronel  4 A Weak Entity in a Strong Relationship Database Systems: Design, Implementation, & Management, 6th 30 Edition, Rob & Coronel  4 Relationship Degree Indicates number of associated entities or participants Unary relationship – Association is maintained within a single entity Binary relationship – Two entities are associated Ternary relationship – Three entities are associated Database Systems: Design, Implementation, & Management, 6th 31 Edition, Rob & Coronel  4 Three Types of Relationships Database Systems: Design, Implementation, & Management, 6th 32 Edition, Rob & Coronel  4 The Implementation of a Ternary Relationship Database Systems: Design, Implementation, & Management, 6th 33 Edition, Rob & Coronel  4 Recursive Relationships Relationship can exist between occurrences of the same entity set Naturally found within a unary relationship Database Systems: Design, Implementation, & Management, 6th 34 Edition, Rob & Coronel  4 An ER Representation of Recursive Relationships Database Systems: Design, Implementation, & Management, 6th 35 Edition, Rob & Coronel  4 The 1:1 Recursive Relationship “EMPLOYEE is Married to EMPLOYEE” Database Systems: Design, Implementation, & Management, 6th 36 Edition, Rob & Coronel  4 Implementation of the M:N Recursive “PART Contains PART” Relationship Database Systems: Design, Implementation, & Management, 6th 37 Edition, Rob & Coronel  4 Implementation of the 1:M “EMPLOYEE Manages EMPLOYEE” Recursive Relationship Database Systems: Design, Implementation, & Management, 6th 38 Edition, Rob & Coronel  4 Composite Entities Also known as bridge entities Composed of the primary keys of each of the entities to be connected May also contain additional attributes that play no role in the connective process Database Systems: Design, Implementation, & Management, 6th 39 Edition, Rob & Coronel  4 Converting the M:N Relationship into Two 1:M Relationships Database Systems: Design, Implementation, & Management, 6th 40 Edition, Rob & Coronel  4 The M:N Relationship Between STUDENT and CLASS Database Systems: Design, Implementation, & Management, 6th 41 Edition, Rob & Coronel  4 A Composite Entity in an ERD Database Systems: Design, Implementation, & Management, 6th 42 Edition, Rob & Coronel  4 Entity Supertypes and Subtypes Generalization hierarchy – Depicts a relationship between a higher-level supertype entity and a lower-level subtype entity Supertype entity – Contains shared attributes Subtype entity – Contains unique attributes Database Systems: Design, Implementation, & Management, 6th 43 Edition, Rob & Coronel  4 Nulls Created by Unique Attributes Database Systems: Design, Implementation, & Management, 6th 44 Edition, Rob & Coronel  4 A Specialization Hierarchy Database Systems: Design, Implementation, & Management, 6th 45 Edition, Rob & Coronel  4 Inheritance Enables an entity subtype to inherit the attributes and relationships of the supertype. All entity subtypes inherit their primary key attribute from their supertype. Subtype Discriminator Attribute in the supertype entity that determines to which entity subtype each supertype occurrence is related. For eg: EMP_TYPE Database Systems: Design, Implementation, & Management, 6th 46 Edition, Rob & Coronel  4 The EMPLOYEE/PILOT Supertype/Subtype Relationship Database Systems: Design, Implementation, & Management, 6th 47 Edition, Rob & Coronel  4 Disjoint Subtypes Also known as non-overlapping subtypes – Subtypes that contain a unique subset of the supertype entity set – Each entity instance (row) of the supertype can appear in only one of the disjoint subtypes Supertype and its subtype(s) maintain a 1:1 relationship Database Systems: Design, Implementation, & Management, 6th 48 Edition, Rob & Coronel  4 Overlapping Subtypes Subtypes that contain nonunique subsets of the supertype entity set Each entity instance of the supertype may appear in more than one subtype. For eg: in a university, a person may be an employee or a student or both. Database Systems: Design, Implementation, & Management, 6th 49 Edition, Rob & Coronel  4 A Specialization Hierarchy with Overlapping Subtypes Database Systems: Design, Implementation, & Management, 6th 50 Edition, Rob & Coronel  4 Completeness Constraint Specifies whether each entity supertype occurrence must also be a member of at least one subtype. Partial completeness – Not every supertype occurrence is a member of a subtype (symbol: single horizontal line) Total completeness – Every supertype occurrence must be a member of at least one subtype. (symbol: double horizontal line) Database Systems: Design, Implementation, & Management, 6th 51 Edition, Rob & Coronel  4 Developing an ER Diagram Database design is an iterative rather than a linear or sequential process Iterative process – Based on repetition of processes and procedures Database Systems: Design, Implementation, & Management, 6th 52 Edition, Rob & Coronel  4 A Supertype/Subtype Relationship in an ERD Database Systems: Design, Implementation, & Management, 6th 53 Edition, Rob & Coronel  4 A Comparison of ER Modeling Symbols Database Systems: Design, Implementation, & Management, 6th 54 Edition, Rob & Coronel  4 The Chen Representation of the Invoicing Problem Database Systems: Design, Implementation, & Management, 6th 55 Edition, Rob & Coronel  4 The Crow’s Foot Representation of the Invoicing Problem Database Systems: Design, Implementation, & Management, 6th 56 Edition, Rob & Coronel  4 The Rein85 Representation of the Invoicing Problem Database Systems: Design, Implementation, & Management, 6th 57 Edition, Rob & Coronel  4 The IDEF1X Representation of the Invoicing Problem Database Systems: Design, Implementation, & Management, 6th 58 Edition, Rob & Coronel  4 The Challenge of Database Design: Conflicting Goals Database design must conform to design standards High processing speeds are often a top priority in database design Quest for timely information might be the focus of database design Database Systems: Design, Implementation, & Management, 6th 59 Edition, Rob & Coronel  4 The Completed Tiny College ERD Database Systems: Design, Implementation, & Management, 6th 60 Edition, Rob & Coronel  4 Summary Entity relationship (ER) model – Uses ER diagrams to represent conceptual database as viewed by the end user – Three main components Entities Relationships Attributes – Includes connectivity and cardinality notations Connectivities and cardinalities are based on business rules Database Systems: Design, Implementation, & Management, 6th 61 Edition, Rob & Coronel  4 Summary (continued) ER symbols are used to graphically depict the ER model’s components and relationships ERDs may be based on many different ER models Entities can also be classified as supertypes and subtypes within a generalization hierarchy Database designers are often forced to make design compromises Database Systems: Design, Implementation, & Management, 6th 62 Edition, Rob & Coronel