Er - Entity Relationship Diagram Lecture Notes PDF

Chapter 6: ER – Entity Relationship Diagram Major components of ER diagram Practices 10/16/24 Ashish Kumar 1 ER 1976 proposed by Peter Chen ER diagram is widely used in database design Represent conceptual level of a database system Describe things and their re...

Chapter 6: ER – Entity Relationship Diagram Major components of ER diagram Practices 10/16/24 Ashish Kumar 1 ER 1976 proposed by Peter Chen ER diagram is widely used in database design Represent conceptual level of a database system Describe things and their relationships in high level 10/16/24 Ashish Kumar 2 Basic Concepts Entity set – an abstraction of similar things, e.g. cars, students An entity set contains many entities Attributes: common properties of the entities in a entity sets Relationship – specify the relations among entities from two or more entity sets 10/16/24 Ashish Kumar 3 An Example 10/16/24 Ashish Kumar 4 Relationship A relationship may be thought as a set as well For binary relationship, it enumerates the pairs of entities that relate to each other For example, entity set M = {Mike, Jack, Tom} entity set F = {Mary, Kate}. The relationship set married between M and F may be {,} 10/16/24 Ashish Kumar 5 Relationship Example 10/16/24 Ashish Kumar 7 Attribute of A Relationship Set 10/16/24 Ashish Kumar 8 Relationship The degree of a relationship = the number of entity sets that participate in the relationship Mostly binary relationships Sometimes more Mapping cardinality of a relationship 1 –1 1 – many many – 1 Many-many 10/16/24 Ashish Kumar 9 One-One and One-Many 10/16/24 Ashish Kumar 10 Many-one and many-many 10/16/24 Ashish Kumar 11 1- many 10/16/24 Ashish Kumar 12 Many - 1 10/16/24 Ashish Kumar 13 Many - many 10/16/24 Ashish Kumar 14 Alternative Cardinality Specification 10/16/24 Ashish Kumar 15 Note on Mapping Cardinality Both many and 1 include 0 Meaning some entity may not participate in the relationship 10/16/24 Ashish Kumar 16 Total Participation When we require all entities to participate in the relationship (total participation), we use double lines to specify Every loan has to have at least one customer 10/16/24 Ashish Kumar 17 Self Relationship Sometimes entities in a entity set may relate to other entities in the same set. Thus self relationship Here employees mange some other employees The labels “manger” and “worker” are called roles the self relationship 10/16/24 Ashish Kumar 18 More examples on self- relationship People to people Parent – children Manager – employee Husband – wife Word to word Root – synonym 10/16/24 Ashish Kumar 19 Attributes Both entity sets and relationships can have attributes Attributes may be Composite Multi-valued (double ellipse) Derive (dashed ellipse) 10/16/24 Ashish Kumar 20 Another Example 10/16/24 Ashish Kumar 21 Keys A super key of an entity set is a set of one or more attributes whose values uniquely determine each entity. A candidate key of an entity set is a minimal super key Although several candidate keys may exist, one of the candidate keys is selected to be the primary key. 10/16/24 Ashish Kumar 22 Key Examples Suggest super keys for the following entity? What are the candidate keys? Primary key? author name death birthday description 10/16/24 Ashish Kumar 23 Ternary Relationship 10/16/24 Ashish Kumar 24 Can We Decompose a Ternary Relationship? Some relationships that appear to be non-binary may be better represented using binary relationships E.g. A ternary relationship parents, relating a child to his/her father and mother, is best replaced by two binary relationships, father and mother Using two binary relationships allows partial information (e.g. only mother being know) But there are some relationships that are naturally non- binary E.g. works-on 10/16/24 Ashish Kumar 25 Converting Ternary to binary In general, any non-binary relationship can be represented using binary relationships by creating an artificial entity set. Replace R between entity sets A, B and C by an entity set E, and three relationship sets: 1. RA, relating E and A 2.RB, relating E and B 3. RC, relating E and C Create a special identifying attribute for E Add any attributes of R to E 10/16/24 Ashish Kumar 26 Converting Ternary to binary 10/16/24 Ashish Kumar 27 Weak Entity Set Some entity sets in real world naturally depend on some other entity set They can be uniquely identified only if combined with another entity set Example: section1, section2, … become unique only if you put them into a context, e.g. csce4350 10/16/24 Ashish Kumar 30 Weak Entity Set Notations Double rectangles for weak entity set Double diamond for weak entity relationship Dashed underscore for discriminator 10/16/24 Ashish Kumar 31 Specialization A lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked. A lower-level entity set may have additional attributes and participate in additional relationships 10/16/24 Ashish Kumar 32 10/16/24 Ashish Kumar 33 Specification Disjoint Overlapping Completeness constraint (use double lines) total : an entity must belong to one of the lower-level entity sets partial: an entity need not belong to one of the lower-level entity sets 10/16/24 Ashish Kumar 34 Design Considerations Use of entity sets vs. attributes Whether we want to keep additional information Use of entity sets vs. relationship sets Actions among entities are usually represented by relationships Binary versus n-ary relationship sets N-nary relationships are usually more natural for actions among entity sets Weak entity set vs. strong entity set Generalization 10/16/24 Ashish Kumar 35 Notations 10/16/24 Ashish Kumar 36 Notations 10/16/24 Ashish Kumar 37