chapter 2 entity-relationship model 2.1 introductionintroduction 2.2 basic conceptsbasic concepts...
TRANSCRIPT
Chapter 2 Entity-Relationship Model
2.1 Introduction 2.2 Basic concepts 2.3 mapping constrains 2.4 keys 2.5 Entity Relationship Diagram 2.6 Weak Entity Sets 2.7 Extended E-R Features 2.8 Reduction of an E-R Schema to Table
2.1 Introduction
We can characterize the overall approach to the semantic modeling problem in terms of the following four steps:
First, identify a set of semantic concepts. ① we might agree that the world is made up of entities.
② we might go further and agree that entities can usefully be classified into entity types.
③ we might go still further and agree that every entity has a special property that severs to identify that entity.
④ we might go further again and agree that any entity can be related to other entities by means of relationships.
2.1 Introduction
Next we try to devise a set of corresponding symbolic objects that can be used to represent the foregoing semantic concepts.
We also devise a set of formal, general integrity rules to go along with those formal objects.
Finally, we also develop a set of formals operators for manipulating those formal objects.
2.2 Basic Concepts
There are three basic notions that the E-R data model employs: entity sets, relationship sets, and attributes.
1.Entity Sets An entity is a “thing ” or “object” in the real world that is
distinguishable from all other objects. An entity has a set of properties, and the values for some set of properties may uniquely identify an entity, an entity may be concrete or it may be abstract.
An entity set is a set of entities of the same type that share the same properties, or attributes.
Entity set do not need to be disjoint. An entity is represented by a set of attributes. For each attribute,
there is a set of permitted values, called the domain, or value set, of that attribute.
2.2 Basic Concepts
An attribute, as used in the E-R model, can be characterized by the following attribute types:
1)simple and composite attributes
the simple attributes are not divided into subparts
composite attributes can be divided into subparts
customer-name
first-name middle-initial last-name
customer-address
street statecity postal-code
street-number street-name apartment-number
Example:
Figure 2.2 composite attributes customer-name and customer-address
2.2 Basic Concepts
2)single-valued and multivalued attributes the attributes that have a single value for a particular entity is called single-valued attributes. an attribute that has a set of values for a specific entity is called multivalued attributes. Example: phone-number, dependent-name3)null attributes A null value is used when an entity does not have a value for an attribute. an attribute value is unknown : missing (name) or not known(apartment-number)4)derived attribute the value for this type of attribute can be derived from the values of other related attributes or entities. age=date–date-of-birth
2.2 Basic Concepts
2.Relationship Sets A relationship is an association among several entities. A relationship set is a set of relationships of the same type. A relationship set is a mathematical relation on n≥2 entity sets. If E1,E2,……En are entity sets, then a relationship set R is a subset of
{(e1,e2,……en) ︱ e1 E1, e2 E2…… en En}∈ ∈ ∈ The entities involved in a given relationship are said to be participants in that relationship. The number of participants in a given relationship is called the degree of that relationship.
335-57-7991 Adams Spring Pittsfield
321-12-3123 Jones Main Harrison
019-28-3746 Smith North Rye
677-89-9001 Hayes Main Harrison
555-55-5555 Jackson Dupont Woodside
244-66-8800 Curry North Rye
963-96-3969 Williams Nassau Princeton
L-23 1000
L-17 1000
L-15 1500
L-14 1500
L-19 500
L-11 900
L-16 1300
customer loan
Figure 2.3 Relationship set borrower
2.3 Mapping constrains
An E-R enterprise schema may define certain constraints to which the contents of a database must conform.
1.Mapping cardinalities mapping cardinalities, of cardinality ratios, express the number
of entities to which another entity can be associated via a relationship set.
For a binary relationship set R between entity sets A and B, the mapping cardinality must be one of the following:
1)one to one : an entity in A is associated with at most one entity in B, and entity in B is associated with at most one entity in A.
2)one to many: an entity in A is associated with any number of entities in B. An entity in B, however, can be associated with at most one entity in A.
2.3 Mapping constrains
3)many to one: An entity in A is associated with at most one entity in B. An entity in B, however, can be associated with any number of entities in A.
4)many to many: An entity in A is associated with any number of entities in B, and an entity in B is associated with any number of entities in A.
a2
a3
a4
a1
b2
b3
b4
b1
A B
One to one
b2
b3
b4
b5
b1
a2
a3
a1
A B
One to many
b2
b3
b1a2
a3
a4
a5
a1
A B
Many to one
a2
a3
a4
a1
b2
b3
b4
b1
A B
Many to many
2. Existence Dependencies
If the existence of entity x depends on the existence of entity y, then x is said to be existence dependent on y. Operationally, if y is deleted, so is x. Entity y is said to be a dominant entity, and x is said to be a subordinate entity.
The participation of an entity set E in a relationship set R is said to be total if every entity in E participates in at least one relationship in R. If only some entities in E participate in relationship in R, the participation of entity set E in relationship R is said to be partial.
2.3 Mapping constrains
2.4 key
A key allows us to identify a set of attributes that suffice to distinguish entities from each other. (relationships)
1. Entity Sets
superkey : superkey is a set of one or more attributes, that, taken collectively, allows us to identify uniquely an entity in the entity set.
candidate keys: minimal superkeys
primary key: a candidate key that is chosen
by the database designer as the principal means
of identifying entities within an entity set.
superkey
candidate keys
primary key
2.4 key
2. Relationship Sets Let R be a relationship set involving entity sets E1,E2,……En. Let
primary-key(Ei) denote the set of attributes that forms the primary key for entity set Ei.
The composition of the primary key for a relationship set depends on the structure of the attributes associated with the relationship set R.
If the relationship set R has no attributes associated with it, then the set of attributes
primary-key(E1) primary-key(E∪ 2) …… primary-key(E∪ ∪ n) describes an individual relationship in set R.
2.4 key
If the relationship set R has attributes a1,a2,……an associated with if, then the set of attributes.primary-key(E1) primary-key(E∪ 2) …… primary-key(E∪ ∪ n) {a1,a2,……an }∪ describes an individual relationship in set R.
In both of the above cases, the set of attributes. primary-key(E1) primary-key(E∪ 2) …… primary-key(E∪ ∪ n)
from a superkey for the relationship set.
The structure of the primary key for the relationship set depends on the mapping cardinality of the relationship set.
2.5 Entity Relationship Diagram
A E-R diagram consists of the following major components: 1. Rectangles, which represent entity sets 2. Ellipses, which represent attributes 3. Diamonds, which represent relationship sets 4. Lines, which link attributes to entity sets and entity sets to
relationship sets 5. Double ellipses, which represent multivalued attributes 6. Dashed ellipses, which denote derived attributes 7. Double links, which indicate total participation of an entity i
n a relationship set
2.5 Entity Relationship Diagram
many to many
borrower loancustomer 1 M
borrower loancustomer M 1
borrower loancustomer M M
borrower loan
amountcustomer-city
loan-number
customer
customer-name
customer-id
customer-street
one to many
many to one
one to one
depositor account
balancecustomer-city
account-number
customer
customer-name
customer-id
customer-street
2.5 Entity Relationship Diagram
access-date
We have the access-date descriptive attribute attached to the relationship set depositor to specify the most recent date on which a customer accessed that account.
Figure 2.10
E-R diagram with an attribute attached to a relationship set
2.5 Entity Relationship Diagram
name
date-of-birth
street-name
customer
first-name
customer-id
street
last-name
middle-initial
address
city
state
zip-code
phone-number
street-number apartment-number
age
multivalued attributes
derived attributes
composite
attributes
Example:
2.5 Entity Relationship Diagram
works-for
telephone-number
employee
employee-name
employee-id
manager
worker
Figure2.12 E-R diagram with role indicators
role
2.5 Entity Relationship Diagram
works-on branch
branch-citycity
branch-name
employee
employee-name
employee-id
street
assets
job
leveltitle
Figure2.13 E-R diagram with a ternary relationship
2.5 Entity Relationship Diagram
borrower loan
amountcustomer-city
loan-number
customer
customer-name
customer-id
customer-street
total participation
Figure2.14 Total participation of an entity set in a relationship set
borrower loan
amountcustomer-city
loan-number
customer
customer-name
customer-id
customer-street
2.5 Entity Relationship Diagram
0..* 1..1
Figure2.15 Cardinality limits on relationship sets
l..h: l minimum cardinality h maximum cardinality
A minimum value of 1 : total participation
A maximum value of 1 : the entity participates in at most one relationship
A maximum value * : no limit 1..* =total participation
2.6 Weak Entity Sets
An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set.
primary key of payment = loan-number + payment-numberprimary key of loan discriminator
payment-number
paymentloan-paymentloan
loan-number amount
M1
payment-date payment-amount
discriminator
doubly outlined diamond
doubly outlined box
weak entity set
strong entity set
total participation
Example:(E-R) partly
A manufacturing company will wish to record information about :the projects it has on hand;the parts used in those projects;the suppliers who supply those parts;the employees who work on those projects;the departments which employees belong to; the employee’s dependent .
The employee comprise those information: the number of the employee, employee’s name, (include first name ... ) and the salary.
The supplier comprise those information: the number of the supplier, the supplier’s name, supplier’s address(include status and city)
A employee(manager,worker) must belong to a department and may take part in more than one projects
A manager is responsible for more than one projects but a project should assign to one manger
A supplier provide a certain quantity parts to projects.
M
MPROJ-WORK
QTY
PROJ-MANAGER
PROJECTSUPP
PART_PROJ
SUPP_PART
PART
PART_STRUCTURE
QTY
SUPPLIER
CITYSTATUSSNAMES#
M M
M
M
M
M
M M
M
1
DEPENDENT
EMP_DEP
DEPT-EMP
EMPLOYEE
DEPARTMENT
SALARY
EMP#
ENAME
FIRST MI LAST
M
1
1
total participation
many to many
one to many
weak entity set
composite
attributes
2.7 Extended E-R Features
1. Specialization An entity set may include subgroupings of entities that are distinct in some way from othe
r entities in the set. The process of designating subgroupings within an entity set is specialization.
Specialization emphasizes differences among entities within the set
2. Generalization This commonality can be expressed by generalization, which is a containment relationshi
p that exists between a higher-level entity set and one or more lower -level entity sets. generalization emphasizes commonalties among entity sets
ISA
employee is a person
hours-worked
officer
hours-worked
customer
citystreetname
employee
person
credit-ratingsalary
office-number
station-number
ISA
ISA
teller secretary
specialization
generalization
officer is a employee
Figure2.17 Specialization and generalization
2.7 Extended E-R Features
Total(double line) partial(line)
disjoint (d) overlapping(o)
employee
ISA
customer
person
savings-account
ISA
checking-account
account
disjointoverlapping
partial participation total participation
2.7 Extended E-R Features
3. Attribute Inheritance A crucial property of the higher-and lower-level entities created b
y specialization and generalization is attribute inheritance. The attributes of the higher-level entity sets are said to be inherited by the lower-level entity sets.
If an entity set is a lower-level entity set in more than one ISA relationship, the resulting structure is said to be a lattice.
A higher-level entity set with attributes and relationships that apply to all of its lower-level entity sets.
Lower-level entity sets with distinctive features that apply only within a particular lower-level entity set.
2.7 Extended E-R Features
4. Aggregation One limitation of the E-R modal is that it is not possible to express relationships among relationships.
works-on branchemployee
job
manages
manager
redundant relationships
Figure2.18 E-R diagram with redundant relationships
works-on branchemployee
job
manages
manager
2.7 Extended E-R Features
Aggregation is an abstraction through which relationship are treated as higher-level entities.
aggregationhigher-level
entities
Figure2.19 E-R diagram with aggregation
2.8 Reduction of an E-R Schema to Tables
A database that conforms to an E-R database schema can be represented by a collection of tables. 1. Tabular Representation of Strong Entity Sets
In general, if we have a table of n columns. We denote the Cartesian product of D1,D2……Dn by D1×D2 ×…… ×Dn-1 ×Dn.
1. Tabular Representation of Strong Entity Sets
telephone-number
employee
employee-name
employee-id employee-name
employee-id
telephone-number
employee
2.8 Reduction of an E-R Schema to Tables
2. Tabular Representation of Weak Entity Sets
Let A be a weak entity set with attributes a1,a2……an. Let B be the strong entity set on which A is dependent. Let the primary key of B consist of attributes b1,b2……bn. {a1,a2……am} {b∪ 1,b2……bn}
payment-number
paymentloan-paymentloan
loan-number amount
M1
payment-date payment-amount
loan-number payment-number
payment-date
payment-amount
payment
3. Tabular Representation of Relationship Sets
2.8 Reduction of an E-R Schema to Tables
depositor account
balancecustomer-city
account-number
customer
customer-name
customer-id
customer-street
access-date
customer-id account-number access-datedepositor
2.8 Reduction of an E-R Schema to Tables
⑴ Redundancy of Tables In general, the table for the relationship set linking a weak entity set to its corresponding strong entity set is redundant and does not need to be present in a tabular representation of an E-R diagram.
payment-number
paymentloan-paymentloan
loan-number amount
M1
payment-date payment-amount
loan-number
payment-number
payment-date
payment-amount
loan-number
payment-number
redundancy
loan-payment payment
⑵ Combination of Tables (1 to many)
2.8 Reduction of an E-R Schema to Tables
account-branch branch
assetsbranch-name
account
account-number branch-citybalance
account-number balance account-number branch-name
account-number balance branch-name
accountaccount-branch
combination
2.8 Reduction of an E-R Schema to Tables
4. Composite Attributes
customer-street
customer
customer-city
customer-id
customer-address
customer-name
customer-id customer-name customer-street customer-city
customer
2.8 Reduction of an E-R Schema to Tables
5. Multivalued Attributes
New tables should be created for multivalued attributes.
For a multivalued attributes M, we create a table T with a column C that corresponds to M and columns corresponding to the primary key of the entity set or relationship set of which M is an attribute.
employee-id
employee
…
dependent-name…
employee-id d-name
dependent-name
2.8 Reduction of an E-R Schema to Tables
6. Tabular Representation of Generalization
account-number
savings-account
overdraft-amount
account
interest-rate
balanceISA
checking-account
2.8 Reduction of an E-R Schema to Tables
6. Tabular Representation of Generalization 1、 Create a table for the higher-level entity set. For each lower-
level entity set, create a table that includes a column for each of the attributes of that entity set plus a column for each attribute of the primary key of the higher-level entity set.
account-number balance account
account-number interest-rate
savings-account checking-account
account-number overdraft-amount
2.8 Reduction of an E-R Schema to Tables
2、 If the generalization is disjoint and complete then we should not create a table for the higher-level entity set. Instead, for each lower-level entity set, create a table that includes a column for each of the attributes of that entity set plus a column for each attribute of the higher-level entity set.
account-number balance interest-ratesavings-account
account-number balance overdraft -amountchecking-account
2.8 Reduction of an E-R Schema to Tables
7. Tabular Representation of Aggregation
borrower loancustomer
loan-numbercustomer-name
loan-officer
employee
employee-numbercustomer-name
loan-number
employee-number
loan-officer
Exercises:
P72: 2.1 2.3 2.5 2.6 2.9 2.12