chapter 2 © 2013 pearson education, inc. publishing as prentice hall chapter 2: modeling data in...

Chapter 2 © 2013 Pearson Education, Inc. Publishing as Prentice Hall

CHAPTER 2:MODELING DATA IN THE ORGANIZATION

Modern Database Management11th Edition

Jeffrey A. Hoffer, V. Ramesh, Heikki Topi

1


OBJECTIVES Define terms Understand importance of data modeling Write good names and definitions for entities,

relationships, and attributes Distinguish unary, binary, and ternary

relationships Model different types of attributes, entities,

relationships, and cardinalities Draw E-R diagrams for common business

situations Convert many-to-many relationships to

associative entities Model time-dependent data using time stamps

【】 2


E-R MODEL CONSTRUCTS (1)

Entities: Entity instance–object, person, place,

event, concept (often corresponds to a row in a table)

Entity Type–collection of entities (often corresponds to a table)

【 Definition of entities P. 58 】 (format to follow for your HW/proj)

Entity type: UNIVERSITY; entity instances? is there an attribute Student_Last_Name in the

above? is there an attribute Name_of_Department in

the above? Entity type: DEPARTMENT; entity

instances?

3


DISCUSSION

Type of entities–object, person, place, event, concept

Entity Attributesobject Ex: Bottled water

place Ex: State

place Ex: City

people Ex: Student

people Ex: Patient

event Ex: Stud-org-sponsored campus activities

Think: City names in State table? Similarly: UNIV,

COLLEGE, FACULTY, STUDENT

4


E-R MODEL CONSTRUCTS (2)

Attributes: Properties or characteristics of an entity or

relationship type (often corresponds to a field/column in a

table)

5


E-R MODEL CONSTRUCTS (3) Relationships:

Relationship instance–link between entities (entity instances) (corresponds to primary key-foreign key equivalencies in related tables)

【 Relationship type 】– category of relationship…link between entity types

【 Descriptions 】 P. 59 (REQUIRED format for your HW/proj)

(often corresponds to ___/______ among tables)

Try to state a relationship instance? Such as one that is between entity types STUDENT and COURSE?

6


SUMMARY AND COMPARISON OF BASIC TERMS

In Business

In DB Concept

In DB Implementation

Remark

Biz function

Entity type

Entity instance

Attribute

Relationship

*(“num-ber”)

7

Chapter 2 © 2013 Pearson Education, Inc. Publishing as Prentice Hall 8

Sample E-R Diagram (Figure 2-1)

Explanation of entities: P.58; business rules: P.59 – important, and useful!


E-R Diagram Notation – Chen (1976)

9


E-R Diagram: Chen Notation

• Boxes represent entities• Ovals represent attributes• Diamonds represent relationships


Relationship degrees specify number of entity types involved (unary, binary, ternary)

Entity symbols

A special entity that is also a relationship

Relationship symbols

Relationship cardinalities specify # entity instances in the other end of the relationship each entity instance in this end is allowed to associate

Attribute symbols

Basic E-R notation (Figure 2-2)



MODELING THE RULES OF AN ORG P. 60: “what data modeling is all about?” –

To document rules and policies of an organization!

Business rules and policies govern – Creating, {ex: enrollment record} Updating, {ex: units attempted; units

earned} Removing {ex: customer credit card

number}data in an info storage and processing system

Business rules and policies must be described along with

the data to which they are related 12


BUSINESS RULES

Are statements that define or constrain some aspect of the business

Are derived from policies, procedures, events, functions

Assert business structure Control/influence business behavior Are expressed in terms familiar to end users Are automated through DBMS software Relationships in an ERD are defined/derived

from business rules

13


OVERVIEW OF BUSINESS RULES (1)

Are statements that define or constrain some aspect of the business

Are derived from policies, procedures, events, functions

Assert business structure Control/influence business behavior Are expressed in terms familiar to end users Are automated through DBMS software P. 60: “what data modeling is all about?”

14


OVERVIEW OF BUSINESS RULES (2) Are statements that define or constrain some aspect

of the business Syntax of business rules: ENTITY_1 Relationiship_Verb_Phrase number ENTITY_2Where Relationiship_Verb_Phrase = Cardinality_adverb + Relationship_VerbExamples:

ENTITY_1 May/Must Do/Have number ENTITY_2STUDENT may have many ENROLLMENTCAR must be registered to at least one OWNERCUSTOMER must be served by only one EMPLOYEE

Two examples P. 61 – think about more examples of yours?

15


A GOOD BUSINESS RULE IS:(TABLE 2-1, P.62)

Declarative–what, not how Precise–clear, agreed-upon meaning Atomic–one statement Consistent–internally and externally Expressible–structured, natural

language Distinct–non-redundant Business-oriented–understood by

business people Excellent examples: P. 59

16


A GOOD DATA NAME IS:

Related to business, not technical, characteristics

Meaningful and self-documenting Unique Readable Composed of words from an approved list Repeatable - different people would

develop same name Written in standard syntax

Excellent examples: P. 58

17


SALIN’S (1990) SUGGESTION ON DATA NAME

Preparing a definition of the data Removing insignificant words Arranging the words in a meaningful,

repeatable way Assigning a standard abbr for each

word Use qualifiers if needed

18


DATA DEFINITIONS Explanation of a term or fact

Term–word or phrase with specific meaning

Fact–association between two or more terms

Examples: P.64

Guidelines for good data definition A concise description of essential data

meaning Gathered in conjunction with systems

requirements Accompanied by diagrams Achieved by consensus, and iteratively

refined

19


MODELING ENTITIES AND ATTRIBUTES

The E-R model is most used as a tool for communications between database designers and end users during the analysis phase of database development

20


ENTITIES Entity – a person, a place, an

object, an event, or a concept in the user environment about which the organization wishes to

maintain data Entity type – a collection of

entities that share common properties or characteristics TRUCK // STUDENT

Entity instance – A single occurrence of an entity type 2013 Chevy Tahoe // John Smith, ACCT

21

Kinds of entities: P.66


AN ENTITY…

SHOULD BE: An object that will have many instances in

the database An object that will be composed of multiple

attributes An object that has relationship w other

object(s) An object that we are trying to model

SHOULD NOT BE: A user of the database system An output of the database system (e.g., a report)

We use capital for names of entity types

An entity has many instances. Ex: FACULTY has Jeff Zhang, David Liu…

Imp

lem

en

tati

on

?

22


ENTITY TYPE AND ENTITY INSTANCES

23

Instances

Value of an attribute


Inappropriate entities

System user

System output

Figure 2-4 Example of inappropriate entities

Appropriate entities


STRONG VS. WEAK ENTITIES, ANDIDENTIFYING RELATIONSHIPS

Strong entity exists independently of other types of entities has its own unique identifier

identifier underlined with single line Weak entity

dependent on a strong entity (identifying owner)…cannot exist on its own

does not have a unique identifier (only a partial identifier)

entity box and partial identifier have double lines Identifying relationship

links strong entities to weak entities – double line

25

- Uses the identifier of the strong entity as its identifier


Strong entity Weak entity

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


NAMING AND DEFINING ENTITY TYPES

Naming:1) Singular noun2) Specific to the organization3) Abbr can be used; follow same rules 4) Named for result of event, not the process5) Consistent

Defining (more see P71): What entity instances are included and

not included Often includes when an instance is

created/ For some entity types: what history to be

kept27


ATTRIBUTES Attribute–property or

characteristic of an entity or relationship type Every entity instance has its own set of values for

the attributes of the entity (when each attribute has a specific value, …)

Classifications of attributes: Required versus Optional Attributes Simple versus (Composite Attribute) Single-Valued versus {Multivalued

Attribute} Stored versus Derived Attributes Identifier Attributes

28


REQUIRED VS. OPTIONAL ATTRIBUTES

29

Required – must have a value for every entity (or relationship) instance with which it is associated

Optional – may not have a value for every entity (or relationship) instance with which it is associated


SIMPLE VS. COMPOSITE ATTRIBUTES

Composite attribute – An attribute that has meaningful component parts (attributes)

30

The address is broken into component parts

Figure 2-7 A composite attribute

Example?


Figure 2-8 Entity with multivalued attribute (Skill) and derived attribute (Years Employed)

Multivaluedan employee can have more than one skill

DerivedCalculated from date employed and current date

Multi-valued and Derived Attributes

Multivalued – may take on more than one value for a given entity (or relationship) instance

Derived – values can be calculated from related attribute values (not physically stored in the database)


Example:


IDENTIFIERS (KEYS)

Identifier (Key)–an attribute (or combination of attributes) that uniquely identifies individual instances of an entity type

Simple versus Composite Identifier An order line item is identified with…

Candidate Identifier–an attribute that could be a key…satisfies the requirements for being an identifier

32


CRITERIA FOR IDENTIFIERS Choose Identifiers that

Will not be null Unique for each instance in the

entity Will not change in value

Avoid intelligent identifiers (e.g., containing locations or people that might change)

Substitute new, simple keys for long, composite keys 33


Figure 2-9 Simple and composite identifier attributes

The identifier is boldfaced and underlined



NAMING ATTRIBUTES

Name should be a singular noun or noun phrase

Name should be unique Name should follow a standard

format e.g. [Entity type name

{ [ Qualifier ] } ] Class Similar attributes of different

entity types should use the same qualifiers and classes 35


DEFINING ATTRIBUTES

State what the attribute is and possibly why it is important

Make it clear what is and is not included in the attribute’s value

Include aliases in documentation State source of values Specify required vs. optional State min and max number of occurrences

allowed Indicate relationships with other attributes

36


INTRO TO RELATIONSHIP An association representing an

interaction among the instances of one or more entity types – P.75 A relationship has a verb name A name that describes the nature of the

relationship - examples:

STDENT takes COURSE, MANAGER supervises EMPLOYEE, ENGINEER is assigned to PROJECT, PART is used in PRODUCT…

Think about one that involves an organization?

37


MODELING RELATIONSHIPS

Relationship Types vs. Relationship Instances The relationship type is modeled as lines

between entity types the relationship instance is between specific

entity instances Relationships can have attributes

These describe features pertaining to the association between the entities in the relationship

Two entities can have more than one type of relationship between them (multiple relationships)

Associative Entity–combination of relationship and entity (“relationship-turned-entity”) 38


Figure 2-10 Relationship types and instances

a) Relationship type (Completes)

b) Relationship instances


Next slide


Figure 2-11a A relationship with an attribute

Here, the date completed attribute pertains specifically to the employee’s completion of a course…it is an attribute of the relationshipPlease note that the above relationship is a many-to-many. What if it is one-to-many? Can we do the same re “Date-completed”? “Grade”?

41


ASSOCIATIVE ENTITIES An entity–has attributes A relationship–links entities together Associative Entity–combination of relationship

and entity (“relationship-turned-entity”) Whether or not to convert a relationship to an

associative entity type? – four conditions (P 78)1) Many-to-many2) Resulting entity type has independent

meaning, and can be identified3) Has one or more attributes in addition to …4) The associative entity participates in

relationship(s) independent of the entities related in the associated relationship (P.80)

Ternary relationships should ALWAYS be converted to associative entities

42


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 in its own right

Note that the many-to-many cardinality between entities in Figure 2-11a has been replaced by two one-to-many relationships with the associative entity

(Note that min cardinalities are missing above for the associative entity)

P. 79, second paragraph


ONLINE REFERENCES OF E-R DIAGRAM

A concise but fairly complete E-R model quick reference: http://creately.com/blog/diagrams/er-diagra

ms-tutorial/

E-R diagram elements (Chen and crow’s foot), with examples http://

www.conceptdraw.com/solution-park/diagramming-ERD

44

http://creately.com/blog/diagrams/er-diagrams-tutorial/



http://www.conceptdraw.com/solution-park/diagramming-ERD




DEGREE OF RELATIONSHIPS

Degree of a relationship is the number of entity types that participate in it1. Unary Relationship

Between instances of a single entity2. Binary Relationship

Between instances of two entities3. Ternary Relationship

Between instances of three entities

45

Compared w

cardinality (Slide 55~):

# of entity instances associated


Degree of relationships – from Figure 2-2

Entities of two different types related to each other

Entities of three different types related to each other

One entity related to another of the same entity type



Figure 2-12 Examples of relationships of different degrees

a) Unary relationships


Figure 2-12 Examples of relationships of different degrees (cont.)

b) Binary relationships


Figure 2-12 Examples of relationships of different degrees (cont.)

c) Ternary relationship

Note: a relationship can have attributes of its own


Figure 2-13c An associative entity – bill of materials structure

This could just be a relationship with attributes…it’s a judgment call


Figure 2-14 Ternary relationship as an associate entity

Guidelines: PP.82~83


More cases of ternary relationships?

• STUDENT – COURSE – FACULTY?

• PATIENT – TREATMENT – DOCTOR?

Guidelines: PP.82~83


Attributes or entity? Fig 2-15a


Attributes or entity? Fig 2-15b


Attributes or entity? Fig 2-15c


Figure 2-13c An associative entity – bill of materials structure

This could just be a relationship with attributes…it’s a judgment call.



CARDINALITY OF RELATIONSHIPS

Watch out the difference betweenDegree of relationshipAndCardinality!!

# entities involved

# entity INSTANCEs related


CARDINALITY OF RELATIONSHIPS

One-to-One Each entity instance in the relationship

(“A”) will have exactly one related entity (“B”)

One-to-Many An entity instance (in “A”) on one side of

the relationship can have many related entity instances (in “B”), but an entity instance on the other side will have a maximum of one related entity instance

Many-to-Many Entity instances on both sides of the

relationship can have many related ent. Inst. on the other side

58

Must examine from BOTH sides

to determine


CARDINALITY CONSTRAINTS Cardinality Constraints—the number of

instances of one entity (“B”) that can or must be associated with each instance of another entity (“A”) When you view a relationship from one

entity to another: the number of instances of “ending” entity that can or must be associated with each instance of “starting” entity

Minimum Cardinality If zero, then optional If one or more, then mandatory

Maximum Cardinality - The max number 59


FIG 2-16 INTRODUCING CARDINALITY CONSTRAINTS

Use the “starting” & “ending” to think about cardinalities


61

Relationship cardinalities specify - for EACH instance on the “starting entity type,” the # of instances on the “ending entity type” is allowed {“starting” and “ending” here refer to direction of rela.}

Basic E-R notation (Figure 2-2; portion)

Minimum one Minimum zero

Maximum one Maximum many

60


ONE-TO-ONE (FROM MY OWN IS 312 PPT)

One-to-one (1:1) – A relationship between two entities in which an instance of entity A can be related to only one instance of entity B; and an entity B instance can be related to only one instance of entity A

Has (1)Located-in

(1)

TOWN AIRPORT

Chen

InfoEngi

Has

61


ONE-TO-ONE (THINK ABOUT E-R DIAGR)

Optional

63

Mandatory


ONE-TO-MANY

One-to-many (1:M) – A relationship between two entities, in which an instance of entity A (any instance of A) can be related to zero, one, or more instances of entity B; and an instance of entity B can be related to only one instance of entity A

Has (many)

Is-place-by (1)

This is Chen model; Information engineering method for the same scenario see left portion of

next slide63

Class 3 (Week 4) resumes from here


Info Engring (Crow’s foot)

ORDER

CUSTOMER

ITEM

1

M

1

M

COURSE

DEPT

ENROLLMENT

1

M

1

M

Each CUSTOMERcan place

many ORDERs;

Each ORDERis placed byOnly one

CUSTOMER

How many

customers are

there?

Try stating this relationship?

64


ONE-TO-MANY (THINK ABOUT E-R DIAGR)

Optional

66

Mandatory


MANY-TO-MANY

Many-to-many (M:N) – A relationship between two entities in which an instance (any instance) of entity A can be related to zero, one, or more instances of entity B; AND an instance of entity B can be related to zero, one, or more instances of entity A

Orders (many)

Is-ordered-by (many)

Chen

66


MANY-TO-MANY (THINK ABOUT E-R DIAGR)

Optional

68

Mandatory


Figure 2-17 Examples of cardinality constraints

a) Mandatory cardinalities

A patient must have recorded at least one history, and can have many (viewed L to R)

A patient history is recorded for one and only one patient(viewed R to L)

Related slide added


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

b) One optional, one mandatory

An employee can be assigned to any number of projects, or may not be assigned to any at all (viewed L to R)

A project must be assigned to at least one employee, and may be assigned to many (viewed R to L)


Related slide added


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

c) Optional cardinalities

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



Figure 2-18 Cardinality constraints in a ternary relationship


Cardinality Summary (Logic) [Observe!]

Each Aeach B

Can/may related toCan/may related to

___ B,one A:

Option./mand.____ to ____

Each Aeach B

Must related toCan/may related to

___ B,one A:


Each Aeach B


___ B,one A:


Each Aeach B

Must related toCan/may related to

___ B,one A:


Each Aeach B


___ B,many A:


Each Aeach B

must related toCan/may related to

___ B,many A:



Figure 2-19 Simple example of time-stamping

The Price History attribute is both multivalued and composite.

Time stamp – a time value that is associated with a data value, often indicating when some event occurred that affected the data value


Example of time, Fig 2-20a


Example of time, Fig 2-20b


Figure 2-20c E-R diagram with associative entity for product assignment to product line over time

The Assignment associative entity shows the date range of a product’s assignment to a particular product line.

Modeling time-dependent data has become more important due to regulations such as HIPAA and Sarbanes-Oxley.


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

Figure 2-21 Examples of multiple relationships

a) Employees and departmentsRelated

slide added


Figure 2-21 Examples of multiple relationships (cont.)

b) Professors and courses (fixed lower limit constraint)

Here, min cardinality constraint is 2. At least two professors must be qualified to teach each course. Each professor must be qualified to teach at least one course.


Figure 2-22Data model for Pine Valley Furniture Company in Microsoft Visio notation

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


Each Patient [1, LEFT entity]has recorded [2, relationship verb]at least one, up to many [3, cardinality], patient history (records) [4, RIGHT entity]

Each Patient History (record) [a, RIGHT entity]has been recorded [b, relationship verb (I wrote it; not displayed)]For at least one, up to 1 (ONE) [c, cardinality],

patient (records) [d, LEFT entity]

Reiterate Cardinality (using PPT #69 as example)

1 2 3 4

ab

cd


In the eyes of EMPOLYEE,PROJECT is optional many;In the eyes of PROJECT,EMPLOYEE is mandatory many.

After the associative entity is introduced:

1. The “far ends” are ALWAYS _______________________2. In the eyes of EMPLOYEE, ASSIGNMENT is _________3. In the eyes of PROJECT, ASSIGNMENT is ____________

M-M to Associative Entity (PPT #70 as example)

EMPLOYEE PROJECTASSIGNMENT


The “far ends” are Max? Min?

The “near-center ends” are Min? Max?

So one must always ensure that the “far ends” are never _________

Recap of MIN/MAX (last week)


Figure 2-21 Examples of multiple relationships

c) CUSTOMER and COMPANY, in the Web 2.0 age

Relationship 1: CUSTOMER does_business_with COMPANYRelationship 2: CUSTOMER ________________ COMPANY

Extension of

CUSTOMER COMPANY

does_business_with

________?________

chapter 2 © 2013 pearson education, inc. publishing as prentice hall chapter 2: modeling data in...

Documents

prentice hall chapter

pearson education

prentice hall type

entities entity instances

ternary entity

special entity

prentice hall objectives

hwproj entity type