ER Modeling

Download Report

Transcript ER Modeling

David M. Kroenke’s

Database Processing:

Fundamentals, Design, and Implementation

Chapter Five: Data Modeling with the Entity-Relationship Model

An objective for this presentation…

To be able to represent a description of a real-world situation as an ER Model. “What’s an ER model?”, you say… 5-2

What is modeling?

• Abstracting information about real world phenomenon and developing it into a model that contains essential features of the phenomenon • 3 categories of data modeling: – High-level data model (aka conceptual data model) e.g. ER – Implementation model e.g. Relational – Low-level data model – how data is physically stored use these in file organization or physical database design 5-3

The Data Model

• A

data model

is a plan, or blueprint, for a

database design

• A data model is more generalized and abstract than a database design.

• It is easier to change a data model than it is to change a database design, so it is the appropriate place to work through conceptual database problems.

High-level model

• A collection of primitives that individually and through their relationships represent an acceptable abstraction of the universe of interest.

• E.g. data model for the college.

5-5

4 main concepts in high level modeling

1. Classification 2. Identification 3. Association a. Degree b. Cardinality 4. Generalization/Specialization 5-6

E-R Model

•

Entity-Relationship model

is a set of concepts and graphical symbols that can be used to create conceptual schemas.

• Versions – – – – –

Original E-R model

— Peter Chen (1976).

Extended E-R model

— Extensions to the Chen model.

Information Engineering (IE)

— James Martin (1990); it uses “crow’s foot” notation, is easier to understand and we will use it.

IDEF1X

— A national standard developed by the National Institute of Standards and Technology [see Appendix B]

Unified Modeling Language (UML)

Entities

• Something that can be identified and the users want to track –

Entity class

given type — a collection of entities of a –

Entity instance

particular entity — the occurrence of a • There are usually many instances of an entity in an entity class.

Attributes

•

Attributes

describe an entity’s characteristics.

• All entity instances of a given entity class have the same attributes, but vary in the values of those attributes.

• Originally shown in data models as

ellipses

• Data modeling products today commonly show attributes in

rectangular form

Identifiers

•

Identifiers

are attributes that name, or identify, entity instances.

• • The identifier of an entity instance consists of one or more of the entity’s attributes.

Composite identifiers

more attributes : Identifiers that consist of two or • Identifiers in data models become keys in database designs: – Entities have identifiers.

– Tables (or relations) have keys.

Relationships

• Entities can be associated with one another in

relationships

: –

Relationship classes

: associations among entity classes –

Relationship instances

: associations among entity instances • In the original E-R model, relationships could have attributes but today this is no longer done.

• A relationship class can involve two or more entity classes.

Degree of the Relationship

• The

degree

of the relationship is the number of entity classes in the relationship: – One entity has a

unary relationship

of degree one. (rhs) – Two entities have a

binary relationship

two.

of degree – Three entities have a

ternary relationship

of degree three.

Unary Relationship

Employee Supervises

5-17

Binary Relationship

Ternary Relationship

Entities and Tables

• The principle difference between an entity and a table (relation) is that you can express a relationship between entities without using foreign keys.

• This makes it easier to work with entities in the early design process where the very existence of entities and the relationships between them is uncertain.

Cardinality

• • •

Cardinality

means “count,” and is expressed as a number.

Maximum cardinality

is the maximum number of entity instances that

can

participate in a relationship.

Minimum cardinality

is the minimum number of entity instances that

must

participate in a relationship.

Maximum Cardinality

•

Maximum cardinality

is the maximum number of entity instances that

can

participate in a relationship.

E1 E1 E1 Employee Student

The Three Types of Maximum Cardinality 1 to 1 1 to Many Many to Many 1 to 1 1 to Many

E2 E2 E2 Vehicle Dormitory

Many to Many

Employee Project

5-23

Parent and Child Entities

• In a one-to-many relationship: – The entity on the one side of the relationship is called the

parent entity

or just the

parent

– The entity on the many side of the relationship is called the

child entity

or just the

child

HAS-A Relationships

• The relationships we have been discussing are known as

HAS-A relationships

: – Each entity instance

has a

relationship with another entity instance: • An EMPLOYEE

has one or more

COMPUTERs.

• A COMPUTER

has an

assigned EMPLOYEE.

Minimum Cardinality

•

Minimum cardinality

entity instances that relationship.

is the minimum number of

must

participate in a • Minimums are generally stated as either zero or one: – IF

zero [0]

THEN participation in the relationship by the entity is

optional

, and

entity instance must participate in the relationship.

– IF

one [1]

THEN participation in the relationship by the entity is

mandatory

, and

at least one

entity instance must participate in the relationship.

Indicating Minimum Cardinality

• As shown in the examples in a following slide: –

Minimum cardinality of zero [0]

indicating

optional

oval

participation is indicated by placing next to the optional entity.

–

Minimum cardinality of one [1] mandatory (required)

indicating participation is indicated by placing a

vertical hash mark

next to the required entity.

Reading Minimum Cardinality

• Look toward the entity in question: – IF you see an

oval

THEN that entity is optional (minimum cardinality of zero [0]).

– IF you see a

vertical hash mark

THEN that entity is mandatory (required) (minimum cardinality of one [1]).

E1 E1 E1 Employee Student

The Three Types of Minimum Cardinality

E2 E2 E2 Vehicle Dormitory Employee Project

5-30

Data Modeling Notation

Data Modeling Notation: N:M and O-M Note that: (1) ERwin cannot indicate true minimum cardinalities on N:M relationships (2) Visio introduces the intersection table instead of using a true N:M model DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall 5-34

Weak Entities

(modified rhs) • A weak entity is an entity whose existence depends upon another entity.

• There are two types of weak entities – ID Dependent entity - entity whose identifier includes the identifier of the parent – Non – ID Dependent entity - The identifier of the parent does not appear in the identifier of the weak child entity.

Weak Entities (Continued)

Employee A dashed line indicates a

nonidentifying relationship

Dependent Here the attributes of Employee may be EmployeeNum and Name while Attributes of Dependent may be SSNum, Name, and Kinship. This would Mean that any dependent can be uniquely identified without any information 5-36 From its parent, an instance of Employee.

ID-Dependent Entities

• An

ID-dependent entity

is an entity (child) whose identifier includes the identifier of another entity (parent).

• All ID-Dependent entities are considered weak.

• The ID-dependent entity is a logical extension or sub-unit of the parent: – BUILDING : APARTMENT – PAINTING : PRINT • The minimum cardinality from the ID-dependent entity to the parent is always one.

A solid line indicates an

identifying relationship

ID-Dependent Entities

Subtype Entities

• A

subtype entity supertype entity

: is a special case of a – STUDENT : UNDERGRADUATE or GRADUATE • The supertype contains all common attributes, while the subtypes contain specific attributes.

• The supertype may have a

discriminator

attribute that indicates the subtype.

Subtypes with a Discriminator

Subtypes: Exclusive or Inclusive (Disjoint/Overlapping)

• If subtypes are

exclusive

, one supertype relates to at most one subtype.

– This is also called disjoint. (rhs) • If subtypes are

inclusive

, one supertype can relate to one or more subtypes.

Subtypes: Disjoint or Overlapping

Faculty

Roster Adjunct

5-42

Subtypes: Total / Partial

• If every instance of a parent class must be a member of a subclass  total specialization • Otherwise, instance of parent class does not have to be a member of a subclass  partial specialization 5-43

Subtypes: Total / Partial

Faculty

Two lines indicates Total Specialization (one line indicates Partial specialization) d

Roster djunct

5-44

Subtypes: IS-A relationships

• Relationships connecting supertypes and subtypes are called

IS-A relationships

, because a subtype IS A supertype.

• The identifer of the supertype and all of its subtypes must be identical, i.e., the identifier of the supertype becomes the identifier of the related subtype(s).

David M. Kroenke’s Database Processing Fundamentals, Design, and Implementation (10 th Edition)

End of Presentation: Chapter Five Part One

David M. Kroenke’s

Database Processing:

Fundamentals, Design, and Implementation

Chapter Five: Data Modeling with the Entity-Relationship Model Part Two

ID-Dependent Relationships: The Association Pattern Note the Price column, which has been added.

ID-Dependent Relationships: The Archtype/Instance Pattern • The

archtype/instance pattern

occurs when the ID-dependent child entity is the physical manisfestation (instance) of an abstract or logical parent: – PAINTING : PRINT – CLASS : SECTION – YACHT_DESIGN : YACHT – HOUSE_MODEL: HOUSE DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall 5-61

ID-Dependent Relationships: The Archtype/Instance Pattern Note that these are true ID-dependent relationships - the identifier of the parent appears as part of the composite identifier of the ID dependent child.

ID-Dependent Relationships: The Archtype/Instance Pattern Note the use of weak, but not ID dependent children.

David M. Kroenke’s Database Processing Fundamentals, Design, and Implementation (10 th Edition)

End of Presentation: Chapter Five Part Two

ER Modeling

Transcript ER Modeling

David M. Kroenke’s

Database Processing:

Chapter Five: Data Modeling with the Entity-Relationship Model

An objective for this presentation…

What is modeling?

The Data Model

High-level model

4 main concepts in high level modeling

E-R Model

Entities

Attributes

Identifiers

Relationships

Degree of the Relationship

Unary Relationship

Binary Relationship

Ternary Relationship

Entities and Tables

Cardinality

Maximum Cardinality

Parent and Child Entities

HAS-A Relationships

Minimum Cardinality

Indicating Minimum Cardinality

Reading Minimum Cardinality

Data Modeling Notation

Weak Entities

Weak Entities (Continued)

ID-Dependent Entities

ID-Dependent Entities

Subtype Entities

Subtypes with a Discriminator

Subtypes: Exclusive or Inclusive (Disjoint/Overlapping)

Subtypes: Total / Partial

Subtypes: IS-A relationships

David M. Kroenke’s

Database Processing:

Chapter Five: Data Modeling with the Entity-Relationship Model Part Two

Directory