conceptual data modeling, entity relationship diagrams 1

Conceptual Data Modeling, Entity Relationship Diagrams

1

Importance of Conceptual Data Modeling Data rather than processes are more complex in many

modern information systems.

Characteristics of data (structure, properties) are more stable, i.e. less likely to change over time, easier to reach consensus on.

It is shared between many processes, therefore is crucial in the design of databases, ensuring integrity of the data in an information system, efficiency of processing.

2

An Entity

An ENTITY or (Entity Type) is something of interest in the environment (e.g., person, place, object, event, concept), characterized with ATTRIBUTES. Represented in E-R diagram by a rectangle

An ENTITY INSTANCE is a particular occurrence of an entity type – NOT SHOWN ON ERDs.

ENTITY ~ TableATTRIBUTE ~ Column in a tableENTITY INSTANCE ~ Row in a table

3

An Attribute

A discrete data element A characteristic (property) of an entity

CUSTOMERCustomer_NumberLast_NameFirst_NameStreet_AddressCityStateZipPhone

This Customer entity has eight attributes

4

Example: identify Entity, Attributes, Instances

Cust_ID Last_Name First_Name Address City ST Zip

0001 Snerd Mortimer General Delivery Tampa FL 336470002 Fogg Bob 567 Fogg Lane Omaha NE 324050003 Amos Famous 2 Cookie Ct. Miami FL 331330004 Targa Maxine 67 Fast Lane Clinton NJ 200820005 George Scott 56 Neat St. Boulder CO 358820006 Guy Nice 290 Pleasant St. Tampa FL 336410007 Smith Bob 76 Quaker Path Wynn NY 211180009 Smith James 234 Bayview Tampa FL 33641

5

Customer

What Should an Entity Be?

SHOULD BE:An object that is important to businessAn object that will have many instances

in the databaseAn object that will be composed of

multiple attributesSHOULD NOT BE:

Data that is not used by the application, should not be stored in the database.

6

Types of Attributes

Simple vs. Composite Simple - most basic level Composite – decomposable into a group of related attributes

ex: address (street, city, state, zip)

Single Valued vs. Multi Valued – Single - only one value per entity instance (e.g., last name,

date of birth) Mulitvalued- multiple values per entity instance (e.g.,

degrees, clubs, skills)

Stored vs Derived (e.g. DateOfBirth vs Age)

7

8

Attributes notation

Simple

Textbook’s notation

99

An attribute broken into

component parts

Multivaluedan employee can have

more than one skill

Derivedfrom date employed

and current date

Alternative notation

Multivalued attributes are shown as double ellipses

EMPLOYEE

emp-id

nameskill

f_name

l_name

m_name

Multivalued composite

Composite attributes may be shown broken down into their simple components Simple/Single Valued; identifier

10

Example: time stamping

11

Identifier Attributes

Every instance of an entity must be uniquely identified (to unambiguously distinguish them)

Simple identifier consists of one attribute

Composite identifier consists of more than one attribute (e.g., first name, middle name, and last name)

Partial identifier (in weak entities) – attribute that together with some attribute from another entity identifies an instance

Underline identifiers in diagrams

Double underline partial identifiers

12

Identifiers

Value of identifier must be unique for each entity instance

Should not change value over timeGuaranteed to have a valid valueNo intelligent identifiers (e.g. containing locations or

people that might change)Consider substituting single-attribute artificial

identifiers for natural composite identifiersto simplify design and enhance performance

13

Simple vs composite identifiyer

14

Relationships

A relationship is an association between one or more entities

The degree of a relationship indicates the number of entities involved

The cardinality of a relationship describes the number of instances of one entity associated with another entity

15

Cardinality Constraints

0 Optional relationships

0 or one0

0 0 or many

Mandatory relationships

one and only one

one or more

16

Cardinality Constraints - the number of instances of one entity that can or must be associated with each instance of another entity.

Example

17

An employee can be assigned to any number

of projects, or may not be assigned to any at all

A project must be assigned to at least one employee, and may be assigned to

many

Degrees of Relationships: unary, binary, ternary

The number of different entities involved in a relationship

18

More on Relationships

Relationships (many-to-many or one-to-one) 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

19

Figure 3-21a Employees and departments

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

20

Note: a relationship can have attributes of its own

Degrees of Relationships - ternary

A vendor supplies parts to warehouses. The unit cost and delivery method may differ for every warehouse.

21

More on Entities: Strong vs. Weak Entities

Strong entities exist independently of other types of entities has its own unique identifier represented with single-line rectangle

Weak entity dependent on a strong entity…cannot exist on its own Does not have a unique identifier represented with double-line rectangle

Identifying relationship links strong entities to weak entities represented with double line diamond

22

Associative Entities Associative entities provide details of a many-to-many

association. It’s an entity – it has attributes AND it’s a relationship – it links entities together

When should a many-to-many relationship with attributes instead be converted into an associative entity? The associative entity could have meaning independent The associative entity could have meaning independent

of the other entitiesof the other entities The associative entity preferably has a unique identifier, The associative entity preferably has a unique identifier,

and should also have other attributesand should also have other attributes The associative entity may participate in other The associative entity may participate in other

relationships other than the entities of the associated relationships other than the entities of the associated relationshiprelationship

23

Example:many-to-many relationship with attributes vs. associative entity

24

Associative entity is depicted as a rectangle with a diamond inside.

Associative entity – other notation

25

Modeling a ternary relationship as an associative entity

26

27

SUMMARY of basic E-R notation

Alternative ER Model Notation

Entity Attribute

Relationship

28