chapter 4 entity relationship (e-r) modeling
DESCRIPTION
Chapter 4 Entity Relationship (E-R) Modeling. Database Systems: Design, Implementation, and Management Peter Rob & Carlos Coronel. In this chapter, you will learn:. - PowerPoint PPT PresentationTRANSCRIPT
Chapter 4Entity Relationship (E-R) ModelingChapter 4Entity Relationship (E-R) Modeling
Database Systems: Design, Implementation, and Management
Peter Rob & Carlos Coronel
In this chapter, you will learn: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
The Entity Relationship (E-R) ModelThe Entity Relationship (E-R) Model
ER model forms the basis of an ER diagram
ERD represents the conceptual database as viewed by end user
Main Components Entities
In E-R models an entity refers to the entity set. An entity is represented by a rectangle containing the entity’s
name.
Attributes Attributes are represented by ovals and are connected to the
entity with a line. Each oval contains the name of the attribute it represents. Attributes have a domain -- the attribute’s set of possible values. Attributes may share a domain.
Relationships
EntitiesEntities
Refers to entity set and not to single entity occurrence
Corresponds to table and not to row in relational environment
In both Chen and Crow’s Foot models, entity is represented by rectangle containing entity’s name
Entity name, a noun, is usually written in capital letters
AttributesAttributes
Characteristics of entities
In Chen model, attributes are represented by ovals and are connected to
entity rectangle with a line Each oval contains the name of attribute it represents
In Crow’s Foot model, attributes are written in attribute box below entity rectangle
The Attributes of the STUDENT EntityThe Attributes of the STUDENT Entity
DomainsDomains
Attributes have domain Domain is attribute’s set of possible values
Attributes may share a domain
Primary KeysPrimary 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
Composite Primary Keys (continued)Composite Primary Keys (continued)
• Primary Keys (CLASS_CODE) Another possible Composite Primary Key (CRS_CODE + CLASS_SECTION)
• Primary Keys (CLASS_CODE) Another possible Composite Primary Key (CRS_CODE + CLASS_SECTION)
Classes of AttributesClasses of Attributes
A simple attribute cannot be subdivided.
Examples: Age, Sex, and Marital status
A composite attribute can be further subdivided to yield additional attributes.
Examples: ADDRESS Street, City, State, Zip
PHONE NUMBER Area code, Exchange number
Classes of AttributesClasses of Attributes
A single-valued attribute can have only a single value. Examples:
A person can have only one social security number. A manufactured part can have only one serial number.
Multivalued attributes can have many values. Examples:
A person may have several college degrees. A household may have several phones with different numbers
Multivalued attributes are shown by a double line connecting to the entity.
Multivalued attributes in an Entity
Resolving Multivalued Attribute ProblemsResolving Multivalued Attribute Problems
Although conceptual model can handle M:N relationships and multivalued attributes, you should not implement them in relational DBMS.
Possible courses of action for the designer Within the original entity, create several new attributes, one for
each of the original multivalued attribute’s components ( Figure 4.4).
Create a new entity composed of the original multivalued attribute’s components ( Figure 4.5).
Splitting the Multivalued Attribute into New Attributes
A New Entity Set Composed of Multivalued Attribute’s ComponentsA New Entity Set Composed of Multivalued Attribute’s Components
A New Entity Set Composed of Multivalued Attribute’s ComponentsA New Entity Set Composed of Multivalued Attribute’s Components
A derived attribute may be calculated (derived) from other attributes
Need not be physically stored within the database
Can be derived by using an algorithm
Example: AGE can be derived from the data of birth and the current date.
Derived AttributesDerived Attributes
Depiction of a Derived AttributeDepiction of a Derived Attribute
Derived Attributes (continued)Derived Attributes (continued)
Relationships A relationship is an association between entities. Relationships are represented by diamond-shaped
symbols.
The Entity Relationship (E-R) ModelThe Entity Relationship (E-R) Model
RelationshipsRelationships
Association between entities Participants
are 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 know only one side of the relationship
The term connectivity is used to describe the relationship classification (e.g., one-to-one, one-to-many, and many-to-many).
ConnectivityConnectivity
Cardinality expresses the specific number of entity occurrences associated with one occurrence of the related entity.
The minimum and maximum number of entity occurrences
CardinalityCardinality
Connectivity and Cardinality in an ERDConnectivity and Cardinality in an ERD
Connectivity and CardinalityConnectivity and Cardinality
Existence Dependent If an entity’s existence depends on the existence of one or
more other entities, it is said to be existence-dependent.
CLASS is existence-dependent on COURSE (parent entity)
EMPLOYEE claims DEPENDENT—DEPENDENT is existence-dependent on EMPLOYEE
Existence independent Entity can exist apart from one or more related entities Example:
some of parts are produced “in-house” and other parts are bought from vendors.At least some of the parts are not supplied by a vender.
PART is existence-independent from VENDOR
Relationship StrengthRelationship Strength
Weak (non-identifying) relationship One entity is not existence-independent on another entity exist if the PK of the related entity doesn’t contain a PK
component of the parent entity COURSE( CRS_CODE, …) CLASS( CLASS_CODE, …)
Strong (identifying) relationship exist when the related entities are existent-dependent and
the PK of the dependent entity contains a PK component of the parent entity
COURSE( CRS_CODE, …) CLASS( CRS_CODE, CLASS_SECTION, …)
Relationship StrengthRelationship Strength
A Weak Relationship Between COURSE and CLASSA Weak Relationship Between COURSE and CLASS
A Strong (Identifying) Relationship Between COURSE and CLASSA Strong (Identifying) Relationship Between COURSE and CLASS
Weak Entities
A weak entity is an entity that Is existence-dependent and
Has a primary key that is partially or totally derived from the parent entity in the relationship.
The existence of a weak entity is indicated by a double rectangle.
The weak entity inherits all or part of its primary key from its strong counterpart.
Relationship Strength and Weak EntitiesRelationship Strength and Weak Entities
A Weak Entity in an ERD
• EMPLOYEE( EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_DOB )
• DEPENDENT( EMP_NUM, DEP_NUM, DEP_FNAME, DEP_DOB ) Primary Key
DEP_NUM
Weak entity in a Strong Relationship BetweenDEPENDENT and EMPLOYEE
( EMP_NUM + DEP_NUM )
( EMP_NUM )
Weak relationship One entity is not existence-independent on another PK of related entity doesn’t contain PK component of parent
entity COURSE( CRS_CODE, …) CLASS( CLASS_CODE, …)
Strong relationship One entity is existence-dependent on another PK of related entity contains PK component of parent entity
COURSE( CRS_CODE, …) CLASS( CRS_CODE, CLASS_SECTION, …)
In any case, CLASS is always existence-dependent on COURSE, whether or not it is defined to be weak.
Weak entity in a Strong Relationship Weak entity in a Strong Relationship
Not Weak entity
Optional participation
One entity occurrence does not require a corresponding entity occurrence in a particular relationship.
An optional entity is shown by a small circle on the side of the optional entity.
Mandatory participation
One entity occurrence requires corresponding occurrence in related entity
If no optionality symbol is shown on ERD, it is mandatory
Relationship ParticipationRelationship Participation
Relationship Participation (continued)Relationship Participation (continued)
• CLASS is Optional to PROFESSOR PROFESSOR is Mandatory to CLASS
• CLASS is Optional to COURSE COURSE is Mandatory to CLASS
• COURSE and CLASS in a Mandatory Relationship
Relationship Participation (continued)Relationship Participation (continued)
• CLASS is Optional to COURSE COURSE is Mandatory to CLASS
Relationship Participation (continued)Relationship Participation (continued)
• COURSE and CLASS in a Mandatory Relationship
Relationship Participation (continued)Relationship Participation (continued)