Chapter 2

Conceptual Modeling
Entity Relationship Diagrams

Objectives
Definition of terms Importance of data modeling Write good names and definitions for entities, relationships, l ti hi and d attributes tt ib t Distinguish unary, binary, and ternary relationships Model different types of attributes attributes, entities entities, relationships, and cardinalities E R diagrams for common business situations Draw E-R Convert many-to-many relationships to associative entities Model time-dependent data using time stamps
2

Business Rules
Statements that define or constrain some aspect of the business Assert business structure Control/influence business behavior Are expressed in terms familiar to end users Govern how data are stored and handled. Our DB App will (hopefully) automate business rules
3

E/R Modeling
The E/R model is used p to construct a conceptual data model a representation of the structure and constraints of a database and is the technology independent.

E-R E R Modeling
An E-R model is based on: Entities Relationships between entities Attributes of entities and relationships E-R diagram ag graphical p representation p of an E-R model

Sample E-R Diagram (Figure 2-1)

Basic E-R notation (Figure 2-2)

Entity symbols
A special entity y that is also a relationship
Relationship degrees specify number of entity types involved

Attribute symbols

Relationship symbols

Relationship cardinalities specify how many of each h entity type is allowed

7

Modeling Business Rules

Business rules
Statements that define the business. Expressed in natural language for end users Expressed in data models for system developers

A Good G dB Business i R Rule l is: i (Table 2-1)

Declarative what, not how Precise clear, , agreed-upon g p meaning g Atomic one statement Consistent internally and externally Expressible structured, structured natural language Distinct non-redundant Business-oriented understood by business people

A Good Data Name is:

Related R l t d to t business, b i not t technical t h i l Meaningful and self-documenting Unique Readable Composed of words from an approved list R Repeatable t bl Written in standard syntax
9

Modeling Entities and Attributes

10

Entities
Things in the real world, physical or not: person, place object, place, object event, event concept

Entity Type
collection of entities that share properties or characteristics Entity type is always SINGULAR Student Represented by a rectangle

Entity Instance
each of the instances of an entity type
11

What Should an Entity Be?

SHOULD BE:
An object that we are trying to model An object that will have many instances in the database

SHOULD NOT BE:

A user of the database system An output of the database system (e.g. a report)

12

Figure 2-4 Example of inappropriate entities System output

System user

Inappropriate pp p entities

Appropriate entities

13

Attributes
Attribute - p property p y or characteristic of an entity y type that is of interest to the organization. Classifications of attributes:
Required versus Optional Attributes Simple versus Composite Attribute Single-Valued versus Multi-valued Attribute Stored versus Derived Attributes Identifier Attributes

14

Figure 2-7 A composite attribute An attribute broken into component parts

Figure 2-8 Entity with multivalued attribute (Skill) and derived attribute (Years Employed)
Multivalued an employee can have more than one skill

Derived from date employed and current date

15

Identifiers (Keys) ( y)
Identifier (Key) - An attribute (or combination of attributes) ib ) that h uniquely i l id identifies ifi individual i di id l instances i of an entity type Simple Key versus Composite Key Candidate Key an attribute that could be a keysatisfies y the requirements q for being g a key y

16

Criteria for Selecting g Identifiers

Will not change g in value over the life of each instance of the entity type. Will not be NULL. No intelligent identifiers (containing e.g. locations or people that might change) Substitute new, simple (e.g., surrogate attribute) tt ib t ) keys k for f long, l composite it keys k (e.g., entity type of Game: Game# instead of f Home_Team H T and d Visitor_Team) Vi it T )
17

Figure 2-9 Simple and composite identifier attributes

The id ifi i identifier is boldfaced and underlined

18

Practice: Person
Produce an E-R diagram for the following situation: We have one entity, called Person, with the following attributes: tt ib t ID (the (th identifier); id tifi ) Name, N which hi h is i composed d of one or more given names and one or more family names; ; one or more aliases; ; an address (composed ( p of street, city, state, zip); date of birth; and age, which can be calculated from the date of birth.

19

Relationships p
Association between two or more entities Represented by connecting lines

20

Figure 2-10 Relationship types and instances

a) Relationship type (Completes)

b) Relationship instances

21

Relationships p
Degree Cardinality constraints

22

Degree of relationships from Figure 2-2

One entity related to another th of f the same entity y type yp

Entities of two different types related to each other

Entities of three h different d ff types related to each other

23

Fig. 2-12: (b) Binary relationships

24

Interpreting Cardinalities

25

Interpreting Cardinalities

26

Cardinality Constraints
Cardinality y Constraints - the number of instances of one entity that can or must be associated with each instance of another th entity. tit Minimum Cardinality
If zero, then th optional ti l If one or more, then mandatory

Maximum Cardinality
The maximum number

27

Quick Check
So now you try it it. Add cardinality constraints to the following diagram
A person is national of zero or more countries A country has one or more people.

28

Fig. 2-16: Introducing cardinality constraint (a) Basic relationship

(a) Relationship with cardinality constraints

29

Figure 2-17 Examples of cardinality constraints

a) Mandatory cardinalities

A patient history is recorded for one and only one patient

A patient must have recorded at least one history history, and can have many
30

Figure 2-17 Examples of cardinality constraints (cont.)

b) One optional, one mandatory

31

Maximum Cardinalities
Looking at the MAXIMUM cardinality on BOTH sides, , we classify y relationships p as:
1:1

1:M

M:N

32

Practice: Products
We have two kinds of entities: Products and Categories. g For each p product we keep p its id (identifier), name, price, wholesale price, and profit margin, which is calculated from the price and the wholesale h l l price. i For F each h category t we keep k its it id (identifier) and its name. Each product belongs to zero or more categories g and each category g y can have zero or more products.

33

Practice : CD
Let us model a CD with three entities: CD, PERSON and SONG SONG.
A CD has a number, which is its identifier, and a title A person has an ID and a name, divided into first, last A song has an ID, a title and a length We keep track of which person is a songs author. A person can author many songs and a song has exactly one author author. We keep track of which people perform on a CD. Zero or more people can perform on a CD, and people can perform on zero or more CDs. CD We keep track of which songs are included on a CD. One or more songs are included on a CD, and a song is included in zero or more CDs.
34

Strong g vs.Weak Entities

Strong entity
Exists independently of other types of entities Has its own unique identifier Identifier underlined with single-line

Weak entity
Dependent p on a strong g entity y cannot exist on its own Does not have a unique identifier (only a partial identifier) Partial identifier underlined with double-line E i box Entity b has h double d bl line li

Identifying relationship
links strong entities to weak entities
35

Figure 2-5 Example of a weak identity and its identifying relationship

Strong entity

Weak entity
36

Weak Entity Strong Entity

Strong entity

Now a N strong entity (unique ID)

37

Practice: Course Sections

Model the courses using two entities: COURSE and SECTION Each course has a number and a title, SECTION. title and may have zero or more sections. Each section has a number and the term offered, which is composed of semester and year.

38

Practice: Weak Entities

Give another example of a weak entity type.

39

Relationships with Attributes

40

Figure 2-11a A binary relationship with an attribute Attribute on a relationship (Link Attribute/Associative)

Here, the date completed attribute pertains specifically to the employees employee s completion of a course courseit it is an attribute of the relationship
41

Practice: Relationships with Attributes

#8, page 103. The figure below shows a grade report that is mailed to students at the end of each semester. Prepare an ERD reflecting the data contained in the grade report. Assume that each course is taught by one instructor.

42

Practice: Relationships with Attributes

43

Homework Assignment g
Exercise #15(a,g), page #104 Exercise #17, #17 page #106 Exercise #25, page #107

44

Associative Entities

45

Associative Entities
One of the hardest concepts in E-R modeling An associative entity is a relationship transformed into an entity y represents p an Each instance of an associative entity instance of the relationship Needed to represent ternary relationships, and for cases when h we need d to t convert t a relationship l ti hi into i t an entity, to relate it to other entities.

46

Associative Entities - Example

Previous model: a database of courses taken by employees. For each employee we keep its SSN (identifier), name and birth date, date and for each course we keep its id and title. title We also keep the date the employee completed that course.

S Suppose that h we also l want to record di information f i about b the h institutions issuing the certificates. HOW???
47

Figure 2-11b An associative entity (CERTIFICATE)

Associative entity is like a relationship with an attribute, but it is also considered to be an entity y in its own right g Note that the many-to-many cardinality between entities in Figure 2-11a 2 11a has been replaced by two one-to-many one to many relationships with the associative entity
48

Fill in the missing cardinalities. di li i

Issues

INSTITUTION Institution ID Institution Address ( (City, y State, Zip)

49

Fig. 2-11: (b) An associative entity (CERTIFICATE)

o Employee p y _ID Course_ID

What is an alternative to assign the PK?

50

Associative Entities
An entity - has attributes A relationship - links entities together When should a relationship with attributes instead be an associative entity y?
All relationships for the associative entity should be many The associative entity could have meaning independent of the other entities The associative entity preferably has a unique identifier, and should also have other attributes The associative entity may participate in other relationships other than the entities of the associated relationship

51

Homework Assignment g
Exercise #15(f), page #104

52

Ternary Relationships

53

Ternary Relationships
Relationships of degree 3 Associates three entities at the same time Can't we just live with binary relationships?

54

Ternary Relationship - Example

Say we wanted to keep track of which Person got which Degrees from which University.
PERSON got t UNIVERSITY

DEGREE

Can this be modeled with binary relationships?

55

Ternary Relationship - Example

Yes, , with the help p of an associative entity y
PERSON
DIPLOMA

UNIVERSITY

DEGREE

Ternary relationship got is now an associative entity, DIPLOMA. Cardinality constraints?

56

Figure 2-12 c) Example of a ternary relationship

Note: a relationship can have attributes of its own

57

Fig. 2-14: Ternary relationships as an associative entity

As a general rule, ternary relationships should be converted to associative entities.

58

Fig. 2-18: Cardinality constraints in a ternary relationship

Wh t are the What th business b i rules? l ?

59

Practice: Ternary Relationships

Give another example of a ternary relationship relationship.

60

Homework Assignment
Exercise #15(b) #15(b), page #104 Exercise #18, page 106

61

Unary Relationships

62

Unary Relationships
Relationships of degree 1 Also known as recursive relationships Two or more entities in the relationship are of the same type Example: we want to represent when an p y supervises p another employee. p y employee

63

Example: Supervises
Example: we want to represent when an employee supervises another employee. We W could ld start with i h something hi lik like this: hi
Supervises

SUPERVISOR

SUPERVISEE

64

Example: Supervises
But supervisors can have their own supervisors Both supervisor and supervisee are employees, so we need a recursive relationship relationship, with roles
Supervises Supervisee
EMPLOYEE

Supervisor

65

Example: Supervises
And And, of course course, add cardinalities Should always define roles even more important when cardinalities are different
Supervises

EMPLOYEE

What are the business rules?

66

V
Supervisor

Supervisee

Fig. 2-12 (a) Unary relationships

Practice: Add missing g cardinalities.

67

Figure 2-17 Examples of cardinality constraints (cont.)

c) Optional cardinalities

Ap person is married to at most one other person, or may not be married at all

68

Example: Bill Of Materials

Whole Has Components Part

Part-Whole is also a recursive relationship:

V
> Whole Part Has Components
Q Quantity i

ITEM

69

Fig. 2 2-13: 13: (b) Two ITEM bill bill-of-materials of materials instances

70

Fig. 2-13 (c ) : An associative entity - bill of materials structure

This could just be a relationship with attributesits a judgment call

71

Practice: Unary Relationships

Give another example p of a unary y relationship. p

72

Homework Assignment
Exercise #10, ,p page g #103.

73

Attributes or Entity?
Sometimes y you will wonder whether to represent p data as an attribute or an entity. This is a common dilemma. Let us look at a few situations.

74

Figure 2-15a and 2-15b Multivalued attributes can be represented as relationships/Associative Entity

simple

composite

75

Attributes or Entity?
So when SHOULD an attribute be linked to an entity type via a relationship?
Attribute refers to a concept in the data model Multiple entity instances share the same attribute

Example on next page

76

Fig. 2-15: Using relationships and entities to link related attribute (c) ( ) Composite p attribute of data shared with other entity y types yp

77

M lti l Relationships Multiple R l ti hi

M More than th one relationship l ti hi between b t the same entity types (Fig. 2-21)

78

Fig. 2-21: Examples of multiple relationships ( ) Employees (a) E l and d departments d t t

Entities can be related to one another in more than one way.

79

Fig. 2-21: (b) Professors and courses (fixed upon constraint)

A New N Business B i R Rule: l An instructor who is scheduled to teach a course must be qualified to teach that course??

??
Here, minimum cardinality constraint is 2, whats for?

80

Homework Assignment
Exercise #11, ,p page g #103 Exercise #15(d,i), page #105 Exercise #18, page #106 Exercise #20, page #106 Exercise #23, #23 page #107 Exercise #26, page #108

81

Modeling Time Time-Dependent Dependent Data

Figure g 2-19. Simple p example p of time-stamping p g

This attribute is both multivalued and composite.

82

Fig. 2-20: (a) E-R diagram not recognizing product reassignment

Fig. 2-20: (b) E-R diagram recognizing product reassignment

83

Fig. 2-20: (a) E-R diagram not recognizing product reassignment

In the middle of year, due to a reorganization of the sales function some products are reassigned to different product lines

PRODUCT LINE

How t H to reflect fl t product d t li line changed over time?

Sales Assigned $50,000 $ , $40,000

Product Product-Line P P B A

(out of $50,000)

PRODUCT

Placed

ORDER
84

Fig. 2-20: Pine Valley Furniture product database

Solution: adding a new relationship of Sales_for_product_line

(b) E-R diagram recognizing product reassignment

85

Fig. 2-20: Pine Valley Furniture product database

(c) ( ) E-R diagram g with associative entity y for p product assignment g to p product line over time

86

Th Entity The E i Relationship R l i hi (E-R) (E R) Model M d l

Congratulation !! You have just learned one of the most important modeling concept (E-R) for developing the data base systems.

87

Recognizing Different ERD Notations

88

Fig. 2-22: Microsoft Visio Notation for Pine Valley Furniture E-R diagram

Different modeling software tools may have different notation f th for the same constructs

89

Fig. 2-22: E-R diagram for Pine Valley Furniture Company

90