Chapter 4 Entity Relationship ER Modeling Database Systems

Chapter 4 Entity Relationship (E-R) Modeling Database Systems: Design, Implementation, and Management Peter Rob & Carlos Coronel

In this chapter, you will learn: 4 How relationships between entities are defined and refined, and how such relationships are incorporated into the database design process 4 How ERD components affect database design and implementation 4 How to interpret the modeling symbols for the four most popular ER modeling tools 4 That real-world database design often requires that you reconcile conflicting goals

The Entity Relationship (E-R) Model 4 ER model forms the basis of an ER diagram 4 ERD represents the conceptual database as viewed by end user 4 Main Components u Entities l l In E-R models an entity refers to the entity set. An entity is represented by a rectangle containing the entity’s name. u Attributes l l 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. u Relationships

Entities 4 Refers to entity set and not to single entity occurrence 4 Corresponds to table and not to row in relational environment 4 In both Chen and Crow’s Foot models, entity is represented by rectangle containing entity’s name 4 Entity name, a noun, is usually written in capital letters

Attributes 4 Characteristics of entities 4 In Chen model, u attributes are represented by ovals and are connected to entity rectangle with a line u Each oval contains the name of attribute it represents 4 In Crow’s Foot model, attributes are written in attribute box below entity rectangle

The Attributes of the STUDENT Entity

Domains 4 Attributes have domain u. Domain is attribute’s set of possible values 4 Attributes may share a domain

Primary Keys 4 Underlined in the ER diagram 4 Key attributes are also underlined in frequently used table structure shorthand 4 Ideally composed of only a single attribute 4 Possible to use a composite key : u Primary key composed of more than one attribute

Composite Primary Keys (continued) • Primary Keys (CLASS_CODE) Another possible Composite Primary Key (CRS_CODE + CLASS_SECTION)

Classes of Attributes 4 A simple attribute cannot be subdivided. u. Examples: Age, Sex, and Marital status 4 A composite attribute can be further subdivided to yield additional attributes. u. Examples: l ADDRESS l PHONE Street, City, State, Zip NUMBER Area code, Exchange number

Classes of Attributes 4 A single-valued attribute can have only a single value. u Examples: l l A person can have only one social security number. A manufactured part can have only one serial number. 4 Multivalued attributes can have many values. u Examples: l l A person may have several college degrees. A household may have several phones with different numbers u Multivalued attributes are shown by a double line connecting to the entity.

Multivalued attributes in an Entity

Resolving Multivalued Attribute Problems 4 Although conceptual model can handle M: N relationships and multivalued attributes, you should not implement them in relational DBMS. 4 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). u Create a new entity composed of the original multivalued attribute’s components ( Figure 4. 5). u

Splitting the Multivalued Attribute into New Attributes

A New Entity Set Composed of Multivalued Attribute’s Components

Derived Attributes 4 A derived attribute u may be calculated (derived) from other attributes u Need u Can not be physically stored within the database be derived by using an algorithm u Example: AGE can be derived from the data of birth and the current date.

Depiction of a Derived Attribute

Derived Attributes (continued)

The Entity Relationship (E-R) Model 4 Relationships u. A relationship is an association between entities. u Relationships are represented by diamond-shaped symbols.

Relationships 4 Association between entities 4 Participants are entities that participate in a relationship 4 Relationships between entities always operate in both directions 4 Relationship can be classified as 1: M 4 Relationship classification is difficult to establish if know only one side of the relationship

Connectivity 4 The term connectivity is used to describe the relationship classification (e. g. , one-to-one, one-to-many, and many-to-many).

Cardinality 4 Cardinality expresses the specific number of entity occurrences associated with one occurrence of the related entity. 4 The minimum and maximum number of entity occurrences

Connectivity and Cardinality in an ERD

Connectivity and Cardinality

Relationship Strength 4 Existence Dependent u If an entity’s existence depends on the existence of one or more other entities, it is said to be existence-dependent. u CLASS is existence-dependent on COURSE (parent entity) u EMPLOYEE claims DEPENDENT— DEPENDENT is existence-dependent on EMPLOYEE 4 Existence independent Entity can exist apart from one or more related entities u Example: u l l 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 Strength 4 Weak (non-identifying) relationship One entity is not existence-independent on another entity u exist if the PK of the related entity doesn’t contain a PK component of the parent entity u l l COURSE( CRS_CODE, …) CLASS( CLASS_CODE, …) 4 Strong (identifying) relationship u exist when the related entities are existent-dependent and the PK of the dependent entity contains a PK component of the parent entity l l COURSE( CRS_CODE, …) CLASS( CRS_CODE, CLASS_SECTION, …)

A Weak Relationship Between COURSE and CLASS

A Strong (Identifying) Relationship Between COURSE and CLASS

Relationship Strength and Weak Entities 4 Weak Entities u A weak entity is an entity that l Is existence-dependent and l Has a primary key that is partially or totally derived from the parent entity in the relationship. u The existence of a weak entity is indicated by a double rectangle. u The weak entity inherits all or part of its primary key from its strong counterpart.

A Weak Entity in an ERD • EMPLOYEE( EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_DOB ) • DEPENDENT( EMP_NUM, DEP_FNAME, DEP_DOB ) Primary Key DEP_NUM

Weak entity in a Strong Relationship Between DEPENDENT and EMPLOYEE ( EMP_NUM ) ( EMP_NUM + DEP_NUM )

Weak entity in a Strong Relationship 4 Weak relationship One entity is not existence-independent on another u PK of related entity doesn’t contain PK component of parent entity u l l COURSE( CRS_CODE, …) CLASS( CLASS_CODE, …) 4 Strong relationship Not Weak entity One entity is existence-dependent on another u PK of related entity contains PK component of parent entity u l l COURSE( CRS_CODE, …) CLASS( CRS_CODE, CLASS_SECTION, …) 4 In any case, CLASS is always existence-dependent on COURSE, whether or not it is defined to be weak.

Relationship Participation 4 Optional participation u One entity occurrence does not require a corresponding entity occurrence in a particular relationship. u An optional entity is shown by a small circle on the side of the optional entity. 4 Mandatory participation u One entity occurrence requires corresponding occurrence in related entity u If no optionality symbol is shown on ERD, it is mandatory