Bài giảng Database systems - Entity-relationship model
Problems with ER Models (2)
Fan Trap
Where a model represents a relationship between
entity types, but pathway between certain entity
occurrences is ambiguous.
Usually: two or more 1:N relationships fan out from
the same entity.
Chasm Trap
Where a model suggests the existence of a
relationship between entity types, but pathway does
not exist between certain entity occurrences.
Usually: optional participation.
73 trang |
Chia sẻ: vutrong32 | Lượt xem: 1486 | Lượt tải: 0
Bạn đang xem trước 20 trang tài liệu Bài giảng Database systems - Entity-relationship model, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
DATABASE SYSTEMS
Nguyen Ngoc Thien An
ENTITY-RELATIONSHIP
MODEL
Spring 2014
Contents
Database Design Process
What is ER Model? And Why?
A Sample Database Application
ER Model Concepts
ER Diagram and Naming Conventions
Alternative Diagrammatic Notations
Problems with ER Models
Reading Suggestion: [1] Chapter 7
2
3
Database Design Process (1)
4
Two main activities:
Database design.
Application design.
Focus in this chapter on database design.
To design the conceptual schema for a database
application.
Application design focuses on the programs
and interfaces that access the database.
Generally considered part of software
engineering.
Database Design Process (2)
5
Requirements collection and analysis
Database designers interview prospective database users
to understand and document data requirements.
Result:
Data requirements.
Functional requirements of the application.
Conceptual design
Create a conceptual schema for the database using a
high-level conceptual data model.
Conceptual schema
Description of data requirements.
Includes detailed descriptions of the entity types, relationships,
and constraints.
Independent of storage and implementation details.
Database Design Process (3)
6
Logical design (data model mapping)
The conceptual schema is transformed from high-
level data model into implementation data model.
The result is a database schema in the
implementation data model of DBMS.
Physical design phase
Internal storage structures, file organizations,
indexes, access paths, and physical design
parameters for the database files are specified.
Contents
Database Design Process
What is ER Model? And Why?
A Sample Database Application
ER Model Concepts
ER Diagram and Naming Conventions
Alternative Diagrammatic Notations
Problems with ER Models
7
What is ER Model?
8
Entity-Relationship (ER) model
A popular high-level conceptual data model.
A logical organisation of data within a database
system.
ER model technique is based on relational data
model.
ER diagrams (ERD)
Diagrammatic notation associated with the ER
model.
Why use ER data modelling?
9
User requirements can be specified formally and
unambiguously.
The conceptual data model is independent of any
particular DBMS.
It does not involve any physical or implemental
details.
It can be easily understood by ordinary users.
It provides an effective bridge between informal
user requirements and logical database design
and implementation.
Contents
Database Design Process
What is ER Model? And Why?
A Sample Database Application
ER Model Concepts
ER Diagram and Naming Conventions
Alternative Diagrammatic Notations
Problems with ER Models
10
A Sample Database Application (1)
11
The COMPANY database
Keep track of employees, departments, and projects.
Company is organized into DEPARTMENTs.
Each department: a unique name, a unique number, a
particular employee who manages it.
Keep track of the start date when that employee began
managing the department.
A department may have several locations.
Department controls a number of PROJECTs.
Each project: a unique name, a unique number, and a
single location.
A Sample Database Application (2)
12
The COMPANY database
Each EMPLOYEE
Has name, Social Security number, address, salary, sex
(gender), birth date.
Be assigned to one department.
May work on several projects, which are not necessarily
controlled by the same department.
Keep track of the current number of hours per week that an
employee works on each project.
Keep track of the direct supervisor of each employee (who is
another employee).
Keep track of the DEPENDENTs of each employee.
Each dependent’s first name, sex, birth date, and relation-ship
to the employee.
A Sample Database Application (3)
13
Contents
Database Design Process
What is ER Model? And Why?
A Sample Database Application
ER Model Concepts
ER Diagram and Naming Conventions
Alternative Diagrammatic Notations
Problems with ER Models
14
Contents - ER Model Concepts
15
Entities & Attributes
Types of Attributes
NULL Values
Entity Types & Entity Sets
Key or Uniqueness Constraint
Relationships & Relationship Types
Relationship Degree
Recursive Relationships
Constraints on Binary Relationship Types
Min-max Notation
Attributes of Relationship Types
Weak Entity Types
Exercise: University Database
Relationship Types of Degree Higher than Two
How ER model describes data?
16
Data in
ER
model
Entities
Attributes
Relationships
Entities & Attributes (1)
17
Entity is a specific object or a thing in the mini-world with
independent existence.
E.g.: the EMPLOYEE John Smith, the Research DEPARTMENT,
the ProductX PROJECT, etc.
Attributes are properties used to describe an entity.
E.g.: an EMPLOYEE entity may have a Name, SSN, Address,
Sex, BirthDate.
A specific entity will have a value for each of its attributes.
E.g.: a specific employee entity may have Name = 'John Smith',
SSN = '123456789', Address = '731, Fondren, Houston, TX', Sex
= 'M' , BirthDate = '09-JAN-55'.
Each attribute has a value set (or domain of values)
associated with that attribute to specify set of values that may
be assigned to it for each individual entity.
E.g.: integer, string, subrange, enumerated type, etc.
Entities & Attributes (2)
18
Two entities (EMPLOYEE e1 and COMPANY c1) and their attributes
Types of Attributes (1)
19
Simple (atomic) vs. Composite attributes
Simple attribute: Each entity has a single atomic value for the
attribute.
E.g.: SSN; Sex
Composite attribute: The attribute may be composed of several
components.
E.g.: Address (Apt#, House#, Street, City, State, ZipCode, Country);
Name (FirstName, MiddleName, LastName)
Composition may form a hierarchy where some components are
themselves composite.
Single-valued vs. Multi-valued attributes
Multi-valued attribute: An entity may have multiple values for that
attribute.
E.g.: Color of a CAR or PreviousDegrees of a STUDENT
Denoted as {Colors} or {PreviousDegrees}
Types of Attributes (2)
20
Types of Attributes (3)
21
Stored vs. Derived attributes
Derived attribute: represents a value that is derivable
from value of a related attribute, or set of attributes,
not necessarily in the same entity type.
E.g.: GPA of a STUDENT
Complex attributes
Composite and multi-valued attributes may be nested
arbitrarily to any number of levels although this is rare.
E.g.: PreviousDegrees of a STUDENT is a composite multi-
valued attribute denoted by {PreviousDegrees(College,
Year, Degree, Field)}.
NULL Values
22
NULL values are used for:
Not applicable: In some cases, a particular entity
may not have an applicable value for an attribute.
E.g.: a College_degrees attribute applies only to
people with college degrees.
Unknown:
Missing: It is known that the attribute value exists but
is missing.
Not known: It is not known whether the attribute value
exists.
COMPANY Database
23
Entity Types & Entity Sets (1)
24
Entities with the same basic attributes are
grouped or typed into an entity type.
E.g.: the EMPLOYEE entity type or the PROJECT
entity type.
The collection of all entities of a particular entity
type in the database at any point in time is called
an entity set.
The entity set is usually referred to using the
same name as the entity type.
E.g.: EMPLOYEE refers to both a type of entity as
well as the current set of all employee entities in the
database.
Entity Types & Entity Sets (2)
25
Key or Uniqueness Constraint (1)
26
An entity type has one or more key attributes
whose values are distinct for each individual entity
in entity set.
E.g.: SSN of EMPLOYEE
A key attribute may be composite.
E.g.: VehicleTagNumber is a key of the CAR entity
type with components (Number, State).
An entity type may have more than one key
attribute.
E.g.: the STUDENT entity type may have two keys (in
university context): Citizen ID and Student ID.
Key or Uniqueness Constraint (2)
27
Entity Type
CAR with two
keys and a
corresponding
Entity Set
COMPANY Database
28
Initial Conceptual Design of
the COMPANY Database
Relationships & Relationship Types (1)
29
A relationship relates two or more distinct entities
with a specific meaning.
E.g.: EMPLOYEE John Smith works on the ProductX
PROJECT; EMPLOYEE Franklin Wong manages the
Research DEPARTMENT.
Relationships of the same type are grouped or
typed into a relationship type.
E.g.: the WORKS_FOR relationship type in which
EMPLOYEEs & DEPARTMENTs participate; the
MANAGES relationship type in which EMPLOYEEs &
DEPARTMENTs participate.
Relationships & Relationship Types (2)
30
Relationship type R among n entity types E1,
E2, ..., En
Defines a set of associations - or a relationship
set - among entities from these entity types.
Relationship instances ri
Each ri associates n individual entities (e1, e2, ...,
en).
Each entity ej in ri is a member of entity set Ej.
Relationship Degree (1)
31
The degree of a relationship type
The number of participating entity types.
Binary (degree 2), ternary (degree 3), n-ary
(degree n)
More than one relationship type can exist with
the same participating entity types.
E.g.: MANAGES and WORKS_FOR are distinct
relationships between EMPLOYEE and
DEPARTMENT, but with different meanings and
different relationship instances.
Relationship Degree (2)
32
Relationship instances of a binary relationship type
Relationship Degree (3)
33
Relationship instances of a ternary relationship type
Recursive Relationships (1)
34
Recursive relationships
Same entity type participates more than once in a
relationship type in different roles.
Must specify the role that a participating entity
plays in each relationship instance.
E.g.: SUPERVISION relationships between
EMPLOYEE (in role of supervisor or boss) and
(another) EMPLOYEE (in role of subordinate or
worker).
Recursive Relationships (2)
35
COMPANY Database
36
Constraints on
Binary Relationship Types (1)
37
Structural constraints: one way to express semantics of
relationship
Cardinality ratio
Membership class
Cardinality Ratios (functionality) (for a binary relationship)
Specifies the maximum number of relationship instances that an
entity can participate in.
one-to-one (1:1)
E.g.: A 1:1 binary relationship is MANAGES which relates a department
entity to the employee who manages that department. This represents
the miniworld constraints that an employee can manage only one
department and that a department has only one manager.
one-to-many (1:M) or many-to-one (M:1)
many-to-many (M:N)
Constraints on
Binary Relationship Types (2)
38
1:N or N:1 Relationship
e1
r1
e2
r2
e3
r3
e4
r4
e5
r5
e6
r6
e7
r7
d1
d2
d3
EMPLOYEE WORKS_FOR DEPARTMENT
Constraints on
Binary Relationship Types (3)
39
M:N Relationship
e1
r1
e2
r2
e3
r3
e4
r4
e5
r5
e6
r6
e7
r7
p1
p2
p3
EMPLOYEE
WORKS_ON
PROJECT
r8
r9
Constraints on
Binary Relationship Types (4)
40
Membership class (participation constraint):
Mandatory (total participation): every instance of
a participating entity type must participate in the
relationship (double line).
E.g.: ATTEND relationship between STUDENTS and
COURSE.
Optional (partial participatiaon): not every
instance of a participating entity type must
participate in the relationship (single line).
E.g.: OFFER relationship between SCHOOL and
MODULE is optional for SCHOOL but mandatory for
MODULE.
COMPANY Database
41
Min-max Notation (1)
42
Min-max notation for relationship types:
Specify structural constraints on relationships.
Replaces cardinality ratio (1:1, 1:N, M:N) and
single/double line notation for participation
constraints.
Associate a pair of integer numbers (min, max)
with each participation of an entity type E in a
relationship type R, where 0 ≤ min ≤ max and
max ≥ 1.
Min-max Notation (2)
43
A department has exactly one manager and an employee
can manage at most one department.
An employee can work for exactly one department but a
department must have at least 4 employees.
Employee DepartmentWorks_for
(1,1) (4,n)
Employee DepartmentManages
(0,1) (1,1)
44
Attributes of Relationship Types
45
A relationship type can have attributes.
E.g.: HoursPerWeek of WORKS_ON
Attributes of 1:1 or 1:N relationship types can
be migrated to one entity type.
For 1:N relationship types:
Relationship attribute can be migrated only to
entity type on N-side of relationship.
For M:N relationship types:
Must be specified as relationship attributes.
COMPANY Database
46
Weak Entity Types (1)
47
Weak entity types
Do not have key attributes of their own.
Identified by being related to specific entities from another
entity type.
Identifying relationship: A weak entity type must
participate in an identifying relationship type with an
owner or identifying entity type.
A weak entity type normally has a partial key, which
is the attribute that can uniquely identify weak entities
that are related to the same owner entity.
If a weak entity has no a partial key, a composite attribute
of all the weak entity’s attributes will be the partial key.
Weak Entity Types (2)
48
A weak entity is identified by the combination of:
Its partial key.
The particular entities which the weak entity is related to in
the identifying relationship.
E.g.: Suppose that a DEPENDENT entity is identified by
the dependent’s first name (unique wrt. each EMPLOYEE),
and the specific EMPLOYEE that the dependent is related
to. DEPENDENT is a weak entity type with EMPLOYEE
as its identifying entity type via the identifying relationship
type DEPENDENT_OF.
A weak entity type always has a total participation
constraint in its identifying relationship type.
COMPANY Database
49
Exercise: University Database
50
The university database maintains records of its departments,
lecturers, course modules, and students.
The requirements are summarised as follows:
The university consists of departments. Each department has a
unique name and some other descriptive attributes
A department must also have a number of lecturers, one of which
is the head of department.
All lecturers have different names (we assume so anyway). They
must teach one or more modules. A lecturer can only belong to
one department.
Modules are offered by departments and taught by lecturers.
They must also be attended by at least 10 students. Each
module has a unique module number.
Students must enrol for a number of modules. Each student is
given a unique student number.
Relationship Types of Degree
Higher than Two (1)
54
one relationship
type of degree n
several
relationship types
of smaller degrees
Relationship Types of Degree
Higher than Two (2)
55
In general, an n-ary relationship is not equivalent
to n binary relationships. Make decisions based
on the semantics or meaning of the particular
situation being represented.
May include the n-ary relationship plus one or more of
the smaller degree relationships if necessary.
Two notations for specifying structural constraints
on n-ary relationships specify different constraints:
Based on the cardinality ratio notation.
Based on the (min, max) notation.
Both should be used if it is important to fully specify
structural constraints.
Relationship Types of Degree
Higher than Two (3)
56
Some database design tools permit only binary
relationships. Two ways:
Way 1:
The n-ary relationship must be represented as a weak
entity type.
With n identifying relationships.
No partial key.
Way 2:
Represent n-ary relationship as a regular entity type.
Introducing an artificial or surrogate key.
Reading suggestion: [1] 7.9
Contents
Database Design Process
What is ER Model? And Why?
A Sample Database Application
ER Model Concepts
ER Diagram and Naming Conventions
Alternative Diagrammatic Notations
Problems with ER Models
58
Naming Conventions
An ER model can be expressed in the form of
the ER diagram (ERD).
Proper naming of schema constructs
Choose names that convey meanings attached to
different constructs in schema.
Nouns give rise to entity type names.
Verbs indicate names of relationship types.
Choose binary relationship names to make ER
diagram readable from left to right and from top to
bottom.
Summary of the Notation for ERD
COMPANY Database
61
Contents
Database Design Process
What is ER Model? And Why?
A Sample Database Application
ER Model Concepts
ER Diagram and Naming Conventions
Alternative Diagrammatic Notations
Problems with ER Models
62
Alternative Diagrammatic Notations
63
Current use (in this class):
Chen notation
Some others:
Crow’s Feet notation
UML (Unified Modeling Language): Rational Rose
64
Symbols for entity type/class,
attribute and relationship
Displaying attributes
Displaying
cardinality ratios
Various (min,
max) notations
Notations for displaying
specialization/generalization
UML Class Diagrams (1)
65
UML methodology
Used extensively in software design.
Many types of diagrams for various software design
purposes.
UML class diagrams
Entity in ER corresponds to an object in UML.
Class includes three sections:
Top section gives the class name.
Middle section includes the attributes.
Last section includes operations that can be applied to
individual objects.
Associations: relationship types.
Relationship instances: links.
UML Class Diagrams (2)
66
UML class diagrams:
Binary association
Represented as a line connecting participating classes.
May optionally have a name.
Link attribute
Placed in a box connected to the association’s line by a
dashed line.
Multiplicities: min..max, asterisk (*) indicates no maximum
limit on participation.
Types of relationships: association and aggregation.
Distinguish between unidirectional and idirectional
associations.
Model weak entities using qualified association.
The COMPANY conceptual schema in
UML class diagram notation
Contents
Database Design Process
What is ER Model? And Why?
A Sample Database Application
ER Model Concepts
ER Diagram and Naming Conventions
Alternative Diagrammatic Notations
Problems with ER Models
68
Problems with ER Models (1)
69
Problems may arise when designing a
conceptual data model called connection
traps.
Often due to a misinterpretation of the
meaning of certain relationships.
Two main types of connection traps are called
fan traps and chasm traps.
Problems with ER Models (2)
70
Fan Trap
Where a model represents a relationship between
entity types, but pathway between certain entity
occurrences is ambiguous.
Usually: two or more 1:N relationships fan out from
the same entity.
Chasm Trap
Where a model suggests the existence of a
relationship between entity types, but pathway does
not exist between certain entity occurrences.
Usually: optional participation.
An Example of a Fan Trap
71
At which branch office does staff number SG37 work?
Staff DivisionWorks_for
(1,1) (1,n)
Operates Branch
(1,n) (1,1)
SG37 r1
SA9 r2
SL21 r3
D1
Staff
entities
Division
entities
Branch
entities
r4
r6
B003
B007
B005
D2
r5
Restructuring ER Model to
Remove Fan Trap
72
SG37 works at branch B003
D1
r1
D2
r2
B005r3
B003
Division
entities
Branch
entities
Staff
entities
r4
r6
SG37
SA9
SL21
B007 r5
Division BranchOperates
(1,n) (1,n)
Has Staff
(1,1) (1,1)
Restructuring the model
resolves the fan trap
An Example of a Chasm Trap
73
At which branch office is property PA14 available?
Branch StaffHas
(1,n) (0,n)
Oversees
Property
ForRent
(1,1) (0,1)
B003 r1
B007 r2
B005 r3
SG37
SL21
Branch
entities
Staff
entities
PropertyForRent
entities
r4
r5
PG36
PA14
PL94
SA9
Restructuring ER Model to
Remove Chasm Trap (1)
74
Branch StaffHas
(1,n) (0,n)
Oversees
Property
ForRent
(1,1) (0,1)
Adding the Offers relationship
resolves the chasm trap
Offers
(1,n) (1,1)
Restructuring ER Model to
Remove Chasm Trap (2)
75
B003 r1
B007 r2
B005 r3
SG37
SL21
Branch
entities
Staff
entities
PropertyForRent
entities
r4
r5
PG36
PA14
PL94
SA9
r6
r7
r8
Q & A
76
Các file đính kèm theo tài liệu này:
- 2_entity_relationship_model_508.pdf