system analysis and design methods “entity...

1

System Analysis

And Design Methods

“ENTITY RELATIONSHIP DIAGRAM”

(ERD)

Prof. Ali Khaleghi

Eng. Hadi Haedar

2

PROJECT 1

DATABASE ARCHITECTURE Overview

External level – concerned with the way individual users

see the data

Conceptual level – can be regarded as a community user view a

formal description of data of interest to the organization

Internal level – concerned with the way in which the data is

actually stored

3

Creating an Entity Relationship Diagram (ERD) and associated data dictionary to represent the reality

and capture business data requirements

PROJECT 1

Conceptual Design

Logical Design

Physical Design

Transforming ERD to relational model:tables, keys (constraints), etc.

Creating the database and other supporting structures based on a specific DBMS

3 Level Database DesignOverview

4

StructuredAnalysis

analysis model tools

General

OO Analysis

Use-cases

Use-case diagrams

Activity diagrams

Swimlane diagramsData object diagrams

ERD diagrams

Data flow diagrams

Process specifications

(Process narrative)

Class diagrams

Packages

CRC cards

Sequence Diagrams

These tools are not

specific to either

structured analysis or

OO analysis.

Overview

5

StructuredAnalysis

Data object diagrams

ERD diagrams

Data flow diagrams

Process specifications

(Process narrative)

StructuredAnalysis

Structured Analysis:Modeling Tools:

Models data elements

• Attributes

• Relationships

Models processes that

transform data

Overview

6

Entity-Relationship Diagram

• Proposed by Dr. Peter Chen in 1970s

• A conceptual model

• A pictorial representation of the information that can be captured by a database

• Allows database professionals to describe an overall design concisely yet accurately

• Major elements :

i. Entity (with attributes and identifier)

ii. Relationship

* Most of Entity-Relationship Diagram can be easily transformed into the relational schema

7

An Example Of Entity-Relationship DiagramERD

8

An Example Of Entity-Relationship DiagramERD

Entity :

• دانشجو

• استاد

• گروه درسی

• درس

Attributes :

• نام دانشجو

• شماره دانشجوئي

• نام استاد

• مدرک

• تاريخ تولد

• نيمسال

• شماره گروه

• شماره درس

• نام درس

• تعداد واحد

• شماره درس

Relationship :

• ارائه

• دارد

9ERD

10

11

i.EntityERD

• It is a real world item / concept that can exist on it’s own

• It may be an object with physical existence ( person, house) or it may be

an object with conceptual existence (company ,job, university course)

• Entity class (entity set) is a structural description of things that share

common attributes

• Entity instance is the occurrence of a particular entity

12

Entity setERD (Entity)

• Collection of entities of a particular entity type in a database at

any point of time is called entity set

• An entity set is a set of entities with common attributes

• Entity set is usually referred to by same name as the entity type

• Entity type is represented as rectangle enclosing the type name

which is singular noun.

Entity type

13

Entity type (Attribute)ERD (Entity)

• defines collection of entities that have same attribute

• Describes an entity class

• All entity instances of a given entity class have the same attributes,

but vary in the values

• Entity type in a database is defined by it’s name and attribute.

• Entity instance is a single occurrence of an entity type.

14

Weak entity and strong entityERD (Entity)

• Entity type that doesn’t have a key attribute on it’s own

2. weak entity

• Regular entity types that have key value is called strong entities

1. strong entity

15

2.Weak entityERD (Entity)

• Entity belonging to weak entity type is identified by being related to specific entities

from another entity type in combination with one of their attribute value

• The relationship that connects owner entity type to weak entity is called Identifying

relationship.

• The weak entities are also called as child entity type or subordinate entity type

• can sometimes be represented as complex attributes (composite and multivalued

attributes)

16

2.Weak entityERD (Entity)

• Weak entity type normally has partial key(descriminator)

• Weak entities have always a total participating constraint because they cannot be

identified without an owner entity , But converse is not true

o Partial key are set of attributes that can uniquely identify weak entities that are

related to some owner entity

o Partial key attribute is denoted with underlined or dotted line

▪ Example : Driver license entity can’t exist without person entity

though it has it’s own key (license number)

=> There is total participation but not weak entity

17

2.Weak entityERD (Entity)

دانشجوکمک هزينه وابستگان

شماره دانشجو شماره دانشجويي شماره وابسته

18

AttributeERD (Entity)

• Attributes are the properties that describe the entities

• Attribute names are enclosed by ovals and connected to their entities by

single line

• Set of attribute values of a given attribute is the value set or domain

19

AttributeERD (Entity)

SIMPLE ATTRIBUTE

COMPOSITE ATTRIBUTE

• Can be divided in to smaller

subparts which represent

more basic attributes with

independent meaning

• Even form hierarchy

• Value of the composite

• attribute is the composition

of the constituent simple

attributes

Example: Address

• Cannot be split in to further

attributes(indivisible)

• Also known as Atomic

attribute

Example: Ssn(Social Security

Number)

20

AttributeERD (Entity)

SINGLE VALUED ATTRIBUTE

• Attributes having single value for particular entity.

• Example : Age

MULTI VALUED ATTRIBUTE

• Attribute having set of values

• Denoted by double circled oval

• Example: Phone-number, College-degree

DERIVED ATTRIBUTE

• Attribute values are derived from another attribute.

• Denoted by dotted oval

• Example: Age

STORED ATTRIBUTE

• Attributes from which the values of other attributes are derived

• Example: Bdate

21

Entity and AttributeERD (Entity)

• Identifies an entity instance

• The value of the identifier attribute is unique for each entity instance

➢ Identifier

22

Iidentifier attribute or key attributeERD (Entity)

• Data item that allows us to uniquely identify individual occurrences or an entity type.

Key

• Attribute or set of attributes that uniquely identify a tuple.

Superkey

• Minimal super key with the property of irredusability and uniqueness

Candidate key

• An entity type may have one or more possible candidate keys, the one which is selected as primary key.

Primary key

• candidate key that consisting of two or more attributes Composite key

• An attribute or set of attribute that matches the candidate key or other or same relation

Foreign key

23

key attributesERD (Entity)

• An attribute , or a group of attributes, that assumes a unique value for each

entity instance. It is sometimes called an identifier.

24

key attributesERD (Entity)

➢ Simple key attribute

➢ Composite key attribute

25

ExampleERD (Entity)

26

ExampleERD (Entity)

Multi valued

Primary-key

stored

Composite-hierarchy

derived

27

Other Attribute TypesERD (Entity)

• An attribute that can be further divided into more attributes

o Example: Name, Address, etc

➢ Composite attribute

• An attribute that allow multiple values

o Example: skills, phone numbers, etc

➢ Multi-Value Attribute

• Attributes that can be calculated (derived) from other attributes

o Example: age, total, interest, due date, etc.

➢ Derived attribute

*Unlike the relational model, these attribute are allowed in conceptual models

28ERD (Entity)

• Composite and multi valued attribute can also be nested

arbitrarily to form complex key

Complex attribute

29ERD (Entity)

➢ Composite attributes are useful to model situations in which user refers to

the composite attribute as a unit but other times refer specifically to it’s

components.

➢ If the composite attribute is to be referenced only as a whole then there

is no need to represent it in to component attributes.

• Example: if there is no need to refer to the individual components of an

address ( ZIP, code, street etc) then the whole address can be designated

as a simple attribute.

Complex and composite attribute

30

Criteria for selecting identifiersERD (Entity)

❖ Choose identifier that will not change in value over the life of each

instance of the entity type

❖ Choose identifier that is guaranteed to have valid values and will

not be null (or unknown). If composite, make sure all parts will have

valid values

❖ Avoid the use of intelligent identifiers whose structure indicates

classifications, locations or people that might change. e.g. the first

two digits of an identifier may indicate a warehouse location, but

such codes are often changed as conditions change, which renders

them invalid.

* Some entities have more than one candidate identifier

31

ii.RelationshipERD

• When attribute of an entity refers to another entity type there exists relationship

• Describes how many entity instance can be in the relationship

• The relationship is often denoted by diamond symbol and are usually verbs.

Example: Relationship ‘WORKS-FOR’ between EMPLOYEE and DEPARTMENT associates

each employee with the department for he works.

32

Relationship features ERD (Relationship)

1. Cardinality: Entity instance’s participation count

2. Degree of relationship: How many entities are involved in a relationship?

3. Participation constraint: participate of an entity in relationship

33

1.CardinalityERD (Relationship features )

➢ Describes how many entity instance can be in the relationship

Describes the maximum number of entity instances that participate in a relationship

A. Maximum cardinality (type of relationship)

Describes the minimum number of entity instances that must participate in a relationship

B. Minimum cardinality

1.A.1 : One-to-one

1.A.2 : One-to-many

1.A.3 : Many-to-many

34

1.A.1 : One-to-One (1:1) ERD (Relationship features .Cardinality : Maximum cardinality )

A single entity instance in one entity class is related to a single entity instance in

another entity class

• ERD Notation (Crow’s foot)

*A governor governs (only) one state; a state has (only) one governor

Governor StateGoverns /

Has

35

1.A.2 : One-to-Many (1:N)ERD (Relationship features .Cardinality : Maximum cardinality )

A single entity instance in one entity class (parent) is related to multiple entity

instances in another entity class (child)

• ERD Notation (Crow’s foot)

*A book is published by (only) one publisher; a publisher can publish many (multiple) books

books PublishersPublish /

Published

by

36

1.A.3 : Many-to-Many (N:N)ERD (Relationship features .Cardinality : Maximum cardinality )

Each entity instance in one entity class is related to multiple entity instances in

another entity class; and vice versa.

• ERD Notation (Crow’s foot)

* A book can be written by many (multiple) authors; an author can write many (multiple) books

AuthorsWrite /

Written bybooks

37

Example of CardinalityERD (Relationship features .Cardinality : Maximum cardinality )

➢ One to one

➢ one to many

➢ Many to many

1 1

N 1

NM

38

1.B : Minimum cardinalityERD (Relationship features .Cardinality : Minimum cardinality)

➢ Minimum cardinality describes the minimum number of instances that must

participate in a relationship for any one instance

➢ Minimums are generally stated as either zero or one:

• 0 (optional): participation in the relationship by the entity is optional.

• 1 (mandatory): participation in the relationship by the entity is

mandatory.

39

1.B : Minimum cardinalityERD (Relationship features .Cardinality : Minimum cardinality)

• ERD Notation (Crow’s foot)

ProgrammersWrite /

Written byCertificates 0 1

One to many maximum

cardinality:

a programmer can have many

certificates; a certificate is issued

to only one programmer.

A certificate is optional in the relationship

(optional for a programmer) ; or a

programmer may not have any

certificates.

A programmer instance is required in the

relationship (a programmer is mandatory for a

certificate); or a certificate has to be issued to

someone.

40

Relationship Modeling ConsiderationsERD

I. Multiple relationships

II. Transitive relationship

III. Attributes of relationships

IV. Promoting relationship to entity

41

I. Multiple relationships ERD (Relationship Modeling Considerations )

• Multiple relationships can exists between entities, as long as they are

independent or different

StudentFaculty

Supervise

Teach

Advise

42

II. Transitive relationship ERD (Relationship Modeling Considerations )

• Entities can be related indirectly by two relationship.

• A relationship is redundant if it can be completely represented by alternate transitive relationships

Can Department and Student be

related indirectly through these

two relationships?

Is this relationship

redundant?

43

III. Attributes of relationships ERD (Relationship Modeling Considerations)

• A relationship can have attributes

COURSECompletesEMPLOYEE

Employee_Name Date_Completed Course_ID Course_TitleEmployee_ID

44

III. Attributes of relationships ERD (Relationship Modeling Considerations)

45

III. Attributes of relationships (part 1)

ERD (Relationship Modeling Considerations)

➢ Attributes of 1:1 or 1:N relationship can be migrated to one of the participating entity

types.

• each EMPLOYEE MANAGES is a 1:1 relationship

• every DEPARTMENT /EMPLOYEE entity participate in

atmost one relationship instance

• value of the Start-date can be determined separately

either by participating DEPARTMENT entity or

participating EMPLOYEE entity

• Example : Start-date attributes of MANAGES can be attribute of either

DEPARTMENT or EMPLOYEE though conceptually it belongs to manages

46

III. Attributes of relationships (part 2)

ERD (Relationship Modeling Considerations)

➢ For 1:N relationship a relationship attribute can be migrated only to entity type on N-

side of relationship

• Example : Start-date attribute here can added only to employee

47

III. Attributes of relationships (part 3)

ERD (Relationship Modeling Considerations)

➢ For M:N relationship types some attribute are determined by the combination of the

participating entities, not by a single enitity.

➢ Such attribute must be specified as the relationship attributes

• Example : No.of hours an employee works on is department is determent is

determined by the EMPLOYEE-PROJECT combination.

48

IV. relationship to entityERD (Relationship Modeling Considerations)

• Relationships can be modeled as entities, particularly when they have attributes

Notice the change of

cardinality notations

(direction)

49

2. Degree Of relationshipERD (Relationship features )

2.1 Unary (one entity type involved in a relationship / the same entity

participates more than once in different roles )

2.2 Binary (two entity types involved in a relationship )

2.3 Ternary (three entity types involved in a relationship )

2.4 N’ary (more than 3 entity types involved in a relationship )

most common

➢Describes the number of entities involved in a relationship

50

2.1 : Unary relationshipERD (Relationship features . Degree Of relationship)

o A customer can refer multiple other customers, and it’s optional for them

to refer other customers (he/she does not have to refer anyone).

o A customer can be referred by only one other customer, and his/her

referee is optional (he/she does not have to be referred by anyone).

➢ only 1 entity required in this relationship

• Example

Refer /

Referred

Custome

r

0

0

51

2.1 : Example of Unary relationship ERD (Relationship features. Degree Of relationship )

52

2.2 : Example of Binary relationship ERD (Relationship features. Degree Of relationship )

53

2.3 : Ternary (three entities) ERD (Relationship features . Degree Of relationship)

o A technician uses a notebook in a project

o Notebook and project as a combination always stay together

o Any of these 3 entities has to participate the relationship

➢ 3 entities are required in this relationship

• Example

User-

notebook

Technician

1

Project

Notebook

1

1

54

2.4 : N’ary (four entities)ERD (Relationship features . Degree Of relationship)

o A physician operates on a patient, with certain

nurses and supplies participating in this operation

at the same time

➢More than 3 entities are required in this relationship

(four entities)

• ExampleoperatesPatient Nurses

Supplies

Physician

55

3. Participation constraintERD (Relationship features)

➢ Participation constraint:

• Total (double line in diagram)

• Partial (existence dependencies / single line in diagram )

➢ Specifies if existence of an entity depends on it being related to another entity via

relationship

➢ Specifies minimum number of relationship instances each entity can participate in

➢ This is called minimum cardinality constraint

56

3. Example of Participation constraintERD (Relationship features)

• if company policy says that every employee must work for the department then

participation of employee in work-for is total.

• Every entity in total set of employee must be related to a department via WORKS-FOR

* The participation of A is total (mandatory) if every entity of A must participate in at least

one relationship in R. Otherwise, the participation of A is partial (optional).

57

3. Example of Participation constraintERD (Relationship features)

58

Alternative notationsERD (Relationship features . Participation constraint )

➢ associating pair of integer (min,max) with each participation of entity type in a relationship

type (structural constraints)

0<=min<=max and max>=1

• Min>0 == total participation

• Min=0 == partial participation

59

Crow’s foot Notation SummaryERD

60

Refining ER Diagrams (part 1)

ERD

• Concept may be first modeled as an attribute and then refined in to a relationship because it is

determined that an attribute is reference to another attribute

• A pair of such attribute that are inverses of one another are refined to as binary relationship

61

Refining ER Diagrams (part 2)

ERD

• An attribute that exists in several entity types may be elevated or promoted to an independent

entity

62

Refining ER Diagrams (part 3.1)

ERD

• Choosing between binary and ternary relationship : including the ternary relationship plus

one or more of the binary relationship if they represent different meaning and all are needed

by the application

( i ) ( ii )

63

Refining ER Diagrams (part 3.2)

ERD

• Choosing between binary and ternary relationship : ternary relationship supply can be

represented as the weak entity type with three identifying relationship

( iii )( ii )

64

Refining ER Diagrams (part 4)

ERD

• Dealing with ternary relationship : replacing the ternary relations hip with an entity type and

a set of binary relationships

65

Refining ER Diagrams (part 5)

ERD

• redundant relationship : Entities can be related indirectly by two relationships

* A relationship is redundant if it can be

completely represented by alternate transitive

relationships

66

Refining ER Diagrams (part 6.1)

ERD

• Confirming optionality and cardinality of relationship : using an “entity set diagram" to

show entity examples graphically

1.Use the diagram to show all possible

relationship scenarios.

2.Go back to the requirements specification and

check to see if they are allowed.

3.If not, then put a cross through the forbidden

relationships

• This allows you to show the cardinality and

optionality of the relationship

67

Refining ER Diagrams (part 6.2)

ERD

• Confirming optionality and cardinality of relationship

68

Refining ER Diagrams (part 7)

ERD

Dividing the relationship parameters : To check we have the correct parameters (sometimes

also known as the degree) of a relationship, ask two questions:

1. One course is studied by how many students?

➢ Answer = `zero or more‘ => the degree at the `student' end

• The answer `zero or more' needs to be split into two parts.

• The `more' part means that the cardinality is `many'.

• The `zero' part means that the relationship is `optional'.

• If the answer was `one or more', then the relationship would be `mandatory'.

69

CONSTRUCTING AN ER MODEL ERD

1) Identify entities

2) Remove duplicate entities

3) List the attributes of each entity

4) Mark the primary keys

5) Define the relationships

6) Describe the cardinality and optionality of the relationships

7) Remove redundant relationships