system analysis and design methods “entity...
TRANSCRIPT
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