Database Systems: Design, Implementation, and Management CHAPTER 3

Entity Relationship (E-R) Modeling

1

Chapter Objectives



Understand basic modeling concepts



Understand the entity relationship model



Learn how to develop an E-R diagram

2

Basic Modeling Concepts

 A

data model

is an abstract representation of the data requirement for business operations and decision making.

 It represents data entities and their characteristics, relationships among entities, and constraints on these relationships.

 The data model facilitates database design.

 It also serves as a communications tool to facilitate the interaction among the designer, the applications programmer, and the end user.

 Entity Relationship (E-R) modeling is popularly used to represent data models. 3

Data Models: Degrees of Data Abstraction

 ANSI/SPARC proposed a three layered architecture for DBMS.

 Conceptual level (community view)  Internal level (DBMS’s view)  External level (user’s view)  A physical level, describing actual physical implementation of data structures and access methods, can be added below the internal level.

 Corresponding data models will vary in their levels of abstraction.

4

Data Models: Degrees of Data Abstraction

5

Figure 3.1 Four Modified (ANSI/SPARC) Data Abstraction Models

Data Models: Conceptual Model



The Conceptual Model

 The conceptual model represents a global view of the organization’s data as viewed by all end-users of an information system.

 It describes all entities and their attributes, the relationships among these entities and the constraints on these relationships.  The conceptual model forms the basis for the conceptual schema - a description of the database structure.

 The conceptual model is independent of both software (DBMS and OS) and hardware.

 The E-R model is the most widely used conceptual model 6

Tiny College Entities

7

Figure 3.2

A Conceptual Schema for Tiny College

8

Figure 3.3

Data Models:Internal Model



The Internal Model

 The internal model adapts the conceptual model to a specific DBMS (e.g., hierarchical, network, and relational).  The internal model is software-dependent but hardware independent.

 Development of the internal model is especially important to hierarchical and network database models because the data access efficiencies in these models can be affected by improper design.

9

Internal Model

Figure 3.4

10

11

Data Models: External Model



The External Model

 The external model is the end user’s/ applications programmer’s view (local view)of the database environment.

 It is concerned about a specific business operation.

 It is implemented through the CREATE VIEW command in SQL.

External Model

Figure 3.5

The External Models for Tiny College

12

Data Models: External Model



Benefits of the external model

 Application program development is simplified because the programmer does not have to be concerned about data not relevant to his/her application.

 Communication with the end-user is simplified.  Identification of data required to support each business unit’s operation is simplified.

 Access control and security can be easily implemented.

13

14

Data Models: Physical Model



The Physical Model

 The physical model operates at the lowest level of abstraction , describing the way data is stored on storage media such as disks or tapes.

 It requires the definition of both the physical storage devices and the access methods required to reach the data within those storage devices.

 The physical model is both software and hardware-dependent .

The Entity Relationship (E-R) Model



E-R Model Components or modules

 Three components: Entities, Attributes, and Relationships.

 Entities  In E-R model an entity refers to the entity set, not an entity occurrence.

  A row in a table corresponds to an entity instance .

An entity is represented by a rectangle containing the entity’s name.

 Example: 15 STUDENT

The Entity Relationship (E-R) Model



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.

 Primary keys are underlined.

16

E-R Diagram - Example

ID NAME MAJOR STUDENT

E-R Diagram of STUDENT Entity with Attributes ID

123456789 123123423 356342112

Name

John McDonald Joe Baxter Jill Austin

Relational Table for Student entity Major

Insy Insy Fina A shorthand form used to describe table structure:

STUDENT (ID, NAME, MAJOR)

17

Attributes



Characteristics of entities



Domain is set of possible values



Primary keys underlined

18

19

Classes 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  Don’t confuse with composite key, where two attributes together form a primary key.

 Example of composite key:  CLASS(Course_ID, Section_Nbr, Class_Time, Prof_Num)

Classes 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.

 A multivalued attribute can have many values.

 Multivalued attributes are shown by a double line connecting to the entity  Examples:  A person may have several college degrees.

 A household may have several phones with different numbers 20

Multivalued Attribute Example

Mod_Code Car_Year Car_ID CAR Car_Color

Car Color can take multiple values for color of body, trim, top, etc

21

Classes of Attributes



Multivalued Attribute in Relational DBMS

 The relational DBMS cannot implement multivalued attributes.

 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. Example:  CAR_COLOR  CAR_TOPCOLOR, CAR_BODYCOLOR, CAR_TRIMCOLOR Create a new entity composed of the original multivalued attribute’s components.

22

Multivalued Attribute Representation

Mod_Code Car_Year TopColor TrimColor Car_ID CAR BodyColor

Car color attribute is replaced by three single valued attributes

Mod_Code Car_Year Car_ID Section Car_ID CAR 1 Has M SEC_COLOR

Multivalued attribute replaced by a new entity

Color 23

Classes of Attributes

 A derived attribute is not physically stored within the database; its value is computed from other attributes.

 It is indicated using a dotted line connecting the attribute with the entity.

 Example: AGE can be derived from DOB and current date.

 Number of years of employment 24 EMP_NUM EMP_DOB EMP_AGE EMPLOYEE

Relationships



Relationship

 A

relationship

is an association between entities.

 Relationships are described as verbs.

 Relationships are represented by diamond-shaped symbols.

 Example: 25 Professor Teaches Class

Types of Relationships

 A relationship’s degree indicates the number of entities that participate in the relationship.

 A

unary relationship

exists when an association is maintained within a single entity. (Recursive relationship)  Example: Employee 26 Manages

Types of Relationships

 A

binary relationship

exists when two entities are associated. (Most common).

 Three entities participate in a

ternary relationship

.

27 Author Writes Contributor Grant Fund Recipient Book

Connectivity

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

28 Professor 1 Teaches M

One-to-Many relationship

M N Author Writes 1

Person

Is married to

Many-to-Many relationship

1

One-to-One relationship

Class Book

Cardinality

  Cardinality expresses the specific number of entity occurrences associated with one occurrence of the related entity.

This is determined by an organization’s business policy.

29 Professor 1 (0,3) Teaches M (1,1)

One-to-Many relationship

Class Student M (1,6) Enrolls in (5,35)

Many-to-Many relationship

N Class

Cardinality



Implications of Cardinality

 A professor listed in the professor table may appear in the Class table as few as zero times or as many as 3 times.

30 Table name: CLASS Cl_Code 10012 Course AC_211 10013 10014 10015 10016 10017 10018 AC_211 AC_211 AC_212 AC_212 CIS-220 CIS-220 10019 10020 10021 10022 10023 10024 CIS-220 CIS-420 QM-261 QM-261 QM-362 QM-362 2 1 2 2 1 2 3 1 1 1 Sec 2 3 1 Time MWF 8:00-8:50 a.m

MWF 9:00-9:50 a.m

TTh 2:30-3:45 p.m

MWF 10:00-10:50 a.m

Th 6:00-8:40 p.m

MWF 9:00-9:50 a.m

MWF 9:00-9:50 a.m

MWF 10:00-10:50 a.m

W 6:30-8:40 p.m

MWF 8:00-8:50 a.m

TTh 1:00-2:15 p.m

MWF 11:00-11:50 a.m

TTh 2:30-3:45 p.m

Room BUS311 BUS200 BUS252 BUS311 BUS252 KLR209 KLR211 KLR209 KLR209 KLR200 KLR200 KLR200 KLR200 ProfNum 105 105 342 301 301 228 114 228 162 114 114 162 162



Professor Table

Cardinality

Table name: Professor ProfNum LName 103 104 105 Donelly Yukon FName Ronald Preston Hefington Arnelle 110 114 Thieu Van Graztevki Gerald 155 160 162 209 228 297 301 Ritula Smith Rob Smith Coronel Jones Osaki Annelise Robert Peter Melanie Cartos Hermine Ismael 335 342 387 Okomoto Smith Smithson Ronald Robert James Init Dept O HIST D B ENG ACCT S B T CHEM CIS MATH ENG K M K CIS CIS CIS CHEM ACCT F A D ENG ACCT ACCT Office AC220 AC402E BUS229 PYR200 KLR302 PYR245 MLL108 KLR203 KLR225 KLR203 PYR356 BUS244 MLL108 BUS345 BUS229 Extn 6783 5561 8665 3412 4436 4440 2248 2359 3421 2359 1145 4683 3997 5514 8665 31

Connectivity and Cardinality in an ERD

32

Existence Dependency

 If an entity’s existence depends on the existence of one or more other entities, it is said to be existence-dependent .

 Example:  CLASS is existence dependent on COURSE - Course code appears as a foreign key in the Class table  The implication of existence dependence is that a Course instance must be created in the Course table before the corresponding Class instances may be added to the Class table.

33

34

Relationship Participation

 The participation is optional if 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.

 Example: Class is optional to Professor Professor is mandatory to Class, so every have a Professor assigned to it.

Class must Professor 1 (0,3) Teaches M (1,1) Class

Relationship Participation

 Relationship participation depends on the business rule of the organization.

35 Course 1 (0,N) Creates M (1,1) Class

Class is optional to Course, Course is mandatory to Class

Course 1 (1,N) Creates M (1,1) Class

Class is mandatory to Course and Course is mandatory to Class

Weak Entity



A

weak entity (non-identifying) is one that  is existence-dependent on another entity and  has a primary key that is partially or totally derived from its strong counterpart.

 A weak entity is indicated by a double rectangle

.

36 E_ID E_Name Employee 1 (0,N) Supports E_ID Dep_Num M (1,1) Dependent Dep_Name

Weak Entity

37

Weak Entity

 Weak entity illustration

Employee Table E_ID E_Name

1001 John 1002 1003 Joe Jill

Dependent Table E_ID

1001 1001

Dep_Num

1 2 1001 1003 1003 3 1 2

Dep_Name

Jack Jeb Jacob Joy Jimmy 38 Is Class a weak entity in the relationship between Class and Course?

Weak (Non Identifying)/Strong (Identifying) Relationship

39 

Weak/Non-identifying: Class is existence dependent but not weak COURSE( CRS_CODE, DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT) CLASS(CLASS_CODE, CRS_CODE, CLASS_SECTION,,CLASS_TIME, …)



Strong /Identifying: Class is existence dependent and weak COURSE( CRS_CODE, DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT) CLASS(CRS_CODE, CLASS_SECTION,CLASS_TIME, …)

Recursive Entities

 A recursive entity is one in which a relationship can exist between occurrences of the same entity set.

 A recursive entity is found within a unary relationship.

 Examples: 1

Employee

1

Emp_Num

345 346 347 348 349 Is married to

Emp_Lname

Ramirez Jones Ramirez Delaney Shapiro

Emp_Fname

James Anne Louise Robert Anton

Spouse

347 349 345 346 40

Recursive Entities

 1:M Recursive relationship

1

Employee

M

Emp_Code 1001 1002 1003 1004 1005 Emp_Lname Craighead Frazier Grzner Mahapatra Sircar Manages Emp_Manager 1005 1005 1005 1005 41

Recursive Entities

 M:N Recursive relationship

COURSE Table Crs_Code

INSY 3302 INSY 3305 INSY 4302 INSY 4308

Description Credit

Bus. Programming 3 Sys Analysis 3 Database Sys Design 3 3

PREREQ Table Crs_Code

INSY 3305 INSY 4302 INSY 4308 INSY 4308

Prereq

INSY 3302 INSY 3302 INSY 3305 INSY 4302

M COURSE N

requires 42

Composite Entities

 A composite entity is often created in the process of replacing a M:N relationship by a pair of 1:M relationships.

 The composite entity serves as a bridge between the related entities.

 Its primary key is composed of the primary keys of the entities it connects.  It may contain additional attributes besides the primary key.

 Example: 43

 Example:

Composite Entities

Student M (1,6) Enrolls in (5,35)

Many-to-Many relationship

N Class C_Id S_Id Name 1 Student (1,6) M (1,1) Enrolls in M 1 (1,1) (5,35) Class S_Id C_Id Grade

A pair of 1:M relationships with a Composite entity

Compare with Figures 3.30 and 3.31, pp. 141, 142 Desc 44

Composite Entity

45

Entity Supertypes and Subtypes

 Supertype-Subtype relationship (aka ISA relationship, or Generalization-Specialization hierarchy) was added to the ER model to create the Enhanced ER model.  A subtype entity inherits the attributes and relationships of the supertype entity and has attributes and relationships unique to itself.

 A subtype entity is created when:  it has some unique attributes that do not apply to the supertype entity  it participates in relationships that do not apply to the supertype entity 46

Entity Supertypes and Subtypes

 Subtype entities could be disjoint (nonoverlapping) or overlapping . Disjoint is aka exclusive and overlapping is aka inclusive membership.

 Membership in one of the subtype entity sets may be mandatory ( Covering ) or optional ( Non covering ) for each member of the supertype entity set.  In a relational model supertype-subtype relationship is modeled as a 1:1 relationship between a supertype entity and its subtype(s).

47

Entity Supertypes and Subtypes



Example: NC E Payment C E Mortgage Loan Personal Loan Car Loan Cash Check Credit Note: The notation used in this diagram is based on that used in Cool Gen E-R diagramming tool.

This notation is different from that used in the book.

48

Entity Supertypes and Subtypes



Example: Employee NC I Administrator Professor NC E Dean Dept. Chair

49

Super type and Subtype

Gs-Overlapping and G-nonOverlapping

50

Comparison of E-R Modeling Symbols

51

Review

Understand basic modeling concepts an abstract representation of the data communications tool Data Models: Degrees of Data Abstraction (level) Conceptual Model Internal Model External Model Physical Mode conceptual model conceptual schema The Entity Relationship (E-R) Model

52

Review

Understand the entity relationship model E-R Model Components Attributes Simple,Composite,Single,Multivalued,Derived Relationship Degree, Connectivity, Cardinality, Relationship Participation: Optional/Mandatory, Existence Dependent (important for the order of creation of tables).

Recursive(1:1,1:M,M:N).

Strong/Weak Entities Recursive, Existence Dependent, Weak, Composite/Bridge Super-type/ Subtype (C/NC, E/I) – as 1:1 relationship in Relational model. Learn how to develop an E-R diagram

53