ENTITY RELATIONSHIP DIAGRAMS IN DBMS, Slides of Database Management Systems (DBMS)

Download ENTITY RELATIONSHIP DIAGRAMS IN DBMS and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Database Systems

Lecture

Topic: Entity Relationship Diagrams (ERDs)

Instructor: Lecturer Ayesha Naseer

Learning Objectives

• Know the symbols and vocabulary of the Crow's Foot notation for entity relationship

diagrams.

• Use the cardinality symbols to represent 1 - 1, 1-M, and M-N relationships.
• Compare the Crow's Foot notation to the representation of relational tables.
• Understand important relationship patterns.
• Use generalization hierarchies to represent similar entity types.
• Detect notational errors in an entity relationship diagram.
• Understand the representation of business rules in an entity relationship diagram.

Entity Relationship Diagrams (ERDs) An entity relationship diagram (ERD) shows the relationships of entity sets stored in a database. ERDs have three basic elements: entity types, relationships, and attributes. Entity Type A collection of entities (persons, places, events, or things) of interest represented by a rectangle in an entity relationship diagram. In the Crow's Foot notation as well as most other notations, rectangles denote entity types. Attribute a property of an entity type or relationship. Each attribute has a data type that defines the kind of values and permissible operations on the attribute. An entity type should have a primary key as well as other descriptive attributes. Attributes are shown inside an entity type rectangle. Relationship A named association among entity types. A relationship represents a two-way or bidirectional association among entities. Most relationships involve two distinct entity types. In the Crow's Foot notation, relationship names appear on the line connecting the entity types involved in the relationship.

Relationship Cardinality

 The number of entities that participate in a relationship. In an ERD, the minimum and maximum

cardinalities are specified for both directions of a relationship.

 Following Figure shows a set of courses ({Coursel, Course 2 , Course 3 }), a set of offerings ({Offeringl,

Offering 2 , Offering 3 , Offering 4 }), and connections between the two sets.

 Coursel is related to Offeringl, Offering 2 , and Offering 3 , Course 2 is related to Offering 4 , and Course 3

is not related to any Offering entities. Likewise, Offeringl is related to Coursel, Offering 2 is related to

Coursel, Offering 3 is related to Coursel, and Offering 4 is related to Course 2.

 From this instance diagram, we might conclude that each offering is related to exactly one course. In

the other direction, each course is related to 0 or more offerings.

Crow s Foot Representation of Cardinalities  The Crow's Foot notation uses three symbols to represent cardinalities.  The Crow's Foot symbol (two angled lines and one straight line) denotes many (zero or more) related entities.  In following figure the Crow's Foot symbol near the Offering entity type means that a course can be related to many offerings.  The circle means a cardinality of zero, while a line perpendicular to the relationship line means a cardinality of one.  To depict minimum and maximum cardinalities, the cardinality symbols are placed adjacent to each entity type in a relationship.  The minimum cardinality symbol appears toward the relationship name while the maximum cardinality symbol appears toward the entity type.  In Figure, a course is related to a minimum of zero offerings (circle in the inside position) and a maximum of many offerings (Crow's Foot in the outside position).  Similarly, an offering is related to exactly one (one and only one) course a shown by the single vertical lines in both inside and outside positions.

Classification of Cardinalities

Identification Dependency (Weak Entities and Identifying Relationships) In an ERD, some entity types may not have their own primary key. Entity types without their own primary key must borrow part (or all) of their primary key from other entity types. Entity types that borrow part or their entire primary key are known as weak entities. The relationship(s) that provides components of the primary key is known as an identifying relationship. Thus, an identification dependency involves a weak entity and one or more identifying relationships. Identification dependency occurs because some entities are closely associated with other entities. For example, a room does not have a separate identity from its building because a room is physically contained in a building. You can reference a room only by providing its associated building identifier. In the ERD for buildings and rooms, the Room entity type is identification dependent on the Building entity type in the Contains relationship. A solid relationship line indicates an identifying relationship. For weak entities, the underlined attribute (if present) is part of the primary key, but not the entire primary key. Thus, the primary key of Room is a combination of BldglD and RoomNo.

M-N Relationships with Attributes

Self-Referencing (Unary) Relationships

A self-referencing (unary) relationship involves connections among members of the

same set. Self-referencing relationships are sometimes called reflexive relationships

because they are like a reflection in a mirror.

The following Figure displays two self-referencing relationships involving the Faculty

and Course entity types. Both relationships involve two entity types that are the same

(Faculty for Supervises and Course for PreReqTo).

These relationships depict important concepts in a university database. The Supervises

relationship depicts an organizational chart, while the PreReqTo relationship depicts

course dependencies that can affect a student's course planning.

Associative Entity Types Representing Multiway (M- Way) Relationships

 Some ERD notations support relationships involving more than two entity types

known as The Uses relationship lists suppliers and parts used on projects.

 For example, a relationship instance involving Supplierl, Parti, and Projectl indicates

that Supplierl Supplies Parti on Projectl.

 An M-way relationship involving three entity types is called a ternary relationship. M-

way (multiway) relationships where the M means more than two.

Associative Entity Types Representing Multiway (M- Way) Relationships  Although you cannot directly represent M-way relationships in the Crow's Foot notation, you should understand how to indirectly represent them. You use an associative entity type and a collection of identifying 1 - M relationships to represent an M-way relationship.  In following Figure, three 1 - M relationships link the associative entity type, Uses, to the Part, the Supplier, and the Project entity types. The Uses entity type is associative because its role is to connect other entity types.  Because associative entity types provide a connecting role, they are sometimes given names using active verbs.  In addition, associative entity types are always weak as they must borrow the entire primary key.  For example, the Uses entity type obtains its primary key through the three identifying relationships.

Equivalence between 1-M and M-N Relationships

 To improve your understanding of M-N relationships, you should know an important equivalence for

M-N relationships.

 An M-N relationship can be replaced by an associative entity type and two identifying 1 - M

relationships. The following Figure shows the Enrollsln relationship converted to this 1 - M style.

 In following Figure, two identifying relationships and an associative entity type replace the Enrollsln

relationship. The relationship name (Enrollsln) has been changed to a noun (Enrollment) to follow the

convention of nouns for entity type names.

 The 1 - M style is similar to the representation in a relational database diagram. If you feel more

comfortable with the 1 - M style, then use it. In terms of the ERD, the M-N and 1 - M styles have the

same meaning.

Generalization Hierarchies Generalization hierarchies allow entity types to be related by the level of specialization. The following Figure depicts a generalization hierarchy to classify employees as salaried versus hourly. Both salaried and hourly employees are specialized kinds of employees. The Employee entity type is known as the supertype (or parent). The entity types SalaryEmp and HourlyEmp are known as the subtypes (or children). Because each subtype entity is a supertype entity, the relationship between a subtype and supertype is known as ISA. For example, a salaried employee is an employee. Because the relationship name (ISA) is always the same, it is not shown on the diagram. Inheritance supports sharing between a supertype and its subtypes. Because every subtype entity is also a supertype entity, the attributes of the supertype also apply to all subtypes. For example, every entity of SalaryEmp has an employee number, name, and hiring date because it is also an entity of Employee. Inheritance means that the attributes of a supertype are automatically part of its subtypes. That is, each subtype inherits the attributes of its supertype. For example, the attributes of the SalaryEmp entity type are its direct attribute (EmpSalary) and its inherited attributes from Employee (EmpNo, EmpName, EmpHireDate, etc.). Inherited attributes are not shown in an ERD. Whenever you have a subtype, assume that it inherits the attributes from its supertype.

Disjointness and Completeness Constraints Generalization hierarchies do not show cardinalities because they are always the same. Rather disjointness and completeness constraints can be shown. Disjointness means that subtypes in a generalization hierarchy do not have any entities in common. In following Figure, the generalization hierarchy is disjoint because a security cannot be both a stock and a bond. In contrast, the generalization hierarchy in following figure is not disjoint because teaching assistants can be considered both students and faculty. Thus, the set of students overlaps with the set of faculty. Completeness means that every entity of a supertype must be an entity in one of the subtypes in the generalization hierarchy. The completeness constraint in following Figure means that every security must be either a stock or a bond. Some generalization hierarchies lack both disjointness and completeness constraints. In following Figure, the lack of a disjointness constraint means that some employees can be both salaried and hourly. The lack of a completeness constraint indicates that some employees are not paid by salary or the hour (perhaps by commission).

Summary of Crow's Foot Notation

Representation of Business Rules in an ERD  As you develop an ERD, you should remember that an ERD contains business rules that enforce organizational policies and promote efficient communication among business stakeholders.  An ERD contains important business rules represented as primary keys, relationships, cardinalities, and generalization hierarchies.  Primary keys support entity identification, an important requirement in business communication. Identification dependency involves an entity that depends on other entities for identification, a requirement in some business communication.  Relationships indicate direct connections among units of business communication.  Cardinalities restrict the number of related entities in relationships supporting organizational policies and consistent business communication.  Generalization hierarchies with disjointness and completeness constraints support classification of business entities and organizational policies.  Thus, the elements of an ERD are crucial for enforcement of organizational policies and efficient business communication.

Diagram Rules

Find all consistency errors in following ERD

Example of Rule Violations and Resolutions

Example of Rule Violations and Resolutions The following list explains the violations:

• Consistency rule 6 (weak entity rule) violation: Faculty cannot be a weak entity without at least one identifying relationship.
• Consistency rule 7 (identifying relationship rule) violation: The Has relationship is identifying but neither Offering nor Course is a weak entity.
• Consistency rule 8 (identification dependency cardinality rule) violation: The cardinality of the Registers relationship from Enrollment to Student should be (1, 1) not (0, Many).
• Consistency rule 9 (redundant foreign key rule) violation: The CourseNo attribute in the Offering entity type is redundant with the Has relationship. Because CourseNo is the primary key of Course, it should not be an attribute of Offering to link an Offering to a Course. The Has relationship provides the linkage to Course.

Example of Rule Violations and Resolutions The following list suggests possible corrective actions for diagram errors:

• Consistency rule 6 (weak entity rule) resolution: The problem can be resolved by either adding one or more identifying relationships or by changing the weak entity into a regular entity. In Figure slide 18, the problem is resolved by making Faculty a regular entity. The more common resolution is to add one or more identifying relationships.
• Consistency rule 7 (identifying relationship rule) resolution: The problem can be resolved by adding a weak entity or making the relationship nonidentifying In Figure slide 18, the problem is resolved by making the Has relationship nonidentifying. If there is more than one identifying relationship involving the same entity type, the typical resolution involves designating the common entity type as a weak entity.
• Consistency rule 8 (identification dependency cardinality rule) resolution: The problem can be resolved by changing the weak entity's cardinality to (1,1). Typically, the cardinality of the identifying relationship is reversed. In Figure slide 18, the cardinality of the Registers relationship should be reversed ((1,1) near Student and (0, Many) near Enrollment).
• Consistency rule 9 (redundant foreign key rule) resolution: Normally the problem can be resolved by removing the redundant foreign key. In Figure slide 18, CourseNo should be removed as an attribute of Offering. In some cases, the attribute may not represent a foreign key. If the attribute does not represent a foreign key, it should be renamed instead of removed.

ERD after removing all consistency errors

Closing Thoughts

 This Lecture has explained the notation of entity relationship diagrams as a prerequisite to applying

entity relationship diagrams in the database development process.

 Using the Crow's Foot notation, this lecture described the symbols, important relationship patterns,

and generalization hierarchies.

 The basic symbols are entity types, relationships, attributes, and cardinalities to depict the number of

entities participating in a relationship.

 Four important relationship patterns were described: many-to-many (M-N) relationships with

attributes, associative entity types representing M-way relationships, identifying relationships

providing primary keys to weak entities, and self-referencing (unary) relationships.

 Generalization hierarchies allow classification of entity types to depict similarities among entity types.

 To improve your usage of the Crow's Foot notation, business rule representations, diagram rules, and

comparisons to other notations were presented.

 This lecture presented formal and informal representation of business rules in an entity relationship

diagram to provide an organizational context for entity relationship diagrams.

 The diagram rules involve completeness and consistency requirements. The diagram rules ensure that

an ERD does not contain obvious errors.

 To help you apply the rules, the ER Assistant provides a tool to check the rules on completed ERDs.

Resources

 Chapter 5, Michael V. Mannion., Database Design, Application Development, and

Administration, , Mc Graw Hill Publishers, 3

rd

edition,

 Chapter 7, Avi Silberschatz, Henry F. Korth, S. Sudarshan , Database Systems

Concepts , Mc Graw Hill Publishers, 7

th

edition

 Chapter 3, Elmasri and Navathe. Fundamentals of Database Systems. Addison-

Wesley, 7

th

edition, 2007.