CS计算机代考程序代写 database ER Entity-Relationship Model – Part 3

Entity-Relationship Model – Part 3

Enhanced Modeling Concepts

Enhanced Entity-Relationship (EER) Model

The basic modelling concepts are only sufficient for some database
applications.

To reflect data properties and constraints more precisely, a number of
enhanced ER models (EERs) were proposed.

Each EER model includes all the basic modeling concepts of the ER model
we discussed before.

We will further discuss the following concepts in EERs:

Subclass/superclass

Specialisation/generalisation

Constraints on specialisation/generalisation

Subclass and Superclass

Subclass of an entity type: subgrouping of entities.

In many cases subclasses need to be represented explicitly because
of their application significance.

Superclass/subclass, Supertype/subtype and Class/subclass are different
names for the same concept.

Subclass inherits attributes and relationships of superclass.

Subclass can have additional attributes and relationships.

This type of relationship between subclass and superclass is often
described as an ISA relationship type.

Specialisation and Generalisation

Specialization is the process of defining a set of subclasses of an entity
type (top-down).

Defined on distinguishing features of entities in the superclass, e.g.,
based on the job type of each employee:

DepartmentEmployee Works_for

DepartmentEmployee Works_for
M

DepartmentEmployee Works_for

DepartmentEmployee Works_for

N

M 1

1 1

Employee

AddressName

SalarySSN

DepartmentEmployee Works_for

Employee

Name

SalarySSN

Supervise

Supervisor Subordinate

Address

Employee

Name

SalarySSN

has

Address

ChildCname Age

ProjectSupplier Supply

Part

Quantity Pid

Part_no

Sname

DepartmentEmployee Works_for
(1,1) (1,N)

Weak

Entity
Entity

Identifying

Relationship
Relationship

Attribute
Multivalued

Attribute

Key

Attribute

Key

Attribute

Employee

AddressName

SalarySSN

ISA

Secretary Technician Engineer

TypeLevel

Grade

Secretary

Address
Name Salary

SSN Level Engineer

Address
Name Salary

SSN Type

Technician

Address
Name Salary

SSN Grade
Employee

AddressName

SalarySSN

ISA

Secretary Technician Engineer

TypeLevel

Grade

Employee

AddressName

SalarySSN

Specialisation and Generalisation

Generalization is a reverse process of specialization (bottom-up).
Common features of entities in subclasses may be generalized into
single superclass (including primary key).

DepartmentEmployee Works_for

DepartmentEmployee Works_for
M

DepartmentEmployee Works_for

DepartmentEmployee Works_for

N

M 1

1 1

Employee

AddressName

SalarySSN

DepartmentEmployee Works_for

Employee

Name

SalarySSN

Supervise

Supervisor Subordinate

Address

Employee

Name

SalarySSN

has

Address

ChildCname Age

ProjectSupplier Supply

Part

Quantity Pid

Part_no

Sname

DepartmentEmployee Works_for
(1,1) (1,N)

Weak

Entity
Entity

Identifying

Relationship
Relationship

Attribute
Multivalued

Attribute

Key

Attribute

Key

Attribute

Employee

AddressName

SalarySSN

ISA

Secretary Technician Engineer

TypeLevel

Grade

Secretary

Address
Name Salary

SSN Level Engineer

Address
Name Salary

SSN Type

Technician

Address
Name Salary

SSN Grade
Employee

AddressName

SalarySSN

ISA

Secretary Technician Engineer

TypeLevel

Grade

Constraints on Specialisation and Generalisation

Disjointness constraint

Specifies that the subclasses of the specialization must be disjoint.
If not constrained, then entities in the subclasses may overlap.

DepartmentEmployee Works_for

DepartmentEmployee Works_for
M

DepartmentEmployee Works_for

DepartmentEmployee Works_for

N

M 1

1 1

Employee

AddressName

SalarySSN

DepartmentEmployee Works_for

Employee

Name

SalarySSN

Supervise

Supervisor Subordinate

Address

Employee

Name

SalarySSN

has

Address

ChildCname Age

ProjectSupplier Supply

Part

Quantity Pid

Part_no

Sname

DepartmentEmployee Works_for
(1,1) (1,N)

Weak

Entity
Entity

Identifying

Relationship
Relationship

Attribute
Multivalued

Attribute

Key

Attribute

Key

Attribute

Employee

AddressName

SalarySSN

ISA

Secretary Technician Engineer

TypeLevel

Grade

Secretary

Address
Name Salary

SSN Level Engineer

Address
Name Salary

SSN Type

Technician

Address
Name Salary

SSN Grade
Employee

AddressName

SalarySSN

ISA

Secretary Technician Engineer

TypeLevel

Grade

Employee

AddressName

SalarySSN

ISA

d

Disjoint

o

Overlap

(default)

d

Employee

o

Employee

Total

d

Employee

o

Employee

Partial

(default)

ISA d

Disjoint

o

Overlap

(default)

Total
Partial

(default)

CourseLecturer Teaches
M 1

Department

Employee
Works_for

M 1

Location

Has

M
1

Constraints on Specialisation and Generalisation

Completeness constraint

total – every entity in the superclass must be a member of at least one
subclass.

partial – an entity may not belong to any of the subclasses.

DepartmentEmployee Works_for

DepartmentEmployee Works_for
M

DepartmentEmployee Works_for

DepartmentEmployee Works_for

N

M 1

1 1

Employee

AddressName

SalarySSN

DepartmentEmployee Works_for

Employee

Name

SalarySSN

Supervise

Supervisor Subordinate

Address

Employee

Name

SalarySSN

has

Address

ChildCname Age

ProjectSupplier Supply

Part

Quantity Pid

Part_no

Sname

DepartmentEmployee Works_for
(1,1) (1,N)

Weak

Entity
Entity

Identifying

Relationship
Relationship

Attribute
Multivalued

Attribute

Key

Attribute

Key

Attribute

Employee

AddressName

SalarySSN

ISA

Secretary Technician Engineer

TypeLevel

Grade

Secretary

Address
Name Salary

SSN Level Engineer

Address
Name Salary

SSN Type

Technician

Address
Name Salary

SSN Grade
Employee

AddressName

SalarySSN

ISA

Secretary Technician Engineer

TypeLevel

Grade

Employee

AddressName

SalarySSN

ISA

d

Disjointness

o

Overlapping

(default)

d

Employee

o

Employee

Total

d

Employee

o

Employee

Partial

(default)

Design Choices for the EER Model

Specializations and generalisation can be defined to make the conceptual
model accurate.

If the subclasses has few specific attributes and no specific relationships,
then

can be merged into the superclass,

replace with one or more type attributes specifying the subclass that
each entity belongs to.

Choices of disjoint/overlapping and total/partial constraints are driven by
rules in the miniworld being modeled.

Informal Method for Constructing an ER or EER Model

Draw an ER or EER diagram to represent the following design:

(1) Identify the entity types (including weak entity types)

(2) Identify the relationship types (including ISA and identifying
relationship types)

(3) Identify the attributes of entity and relationship types (and their
underlying domains)

(4) Identify a primary key for each entity type

(5) Classify each binary relationship type identified in step 2 (i.e.
one-to-one, many-to-one or many-to-many)

(6) Determine the participation constraints for each entity type in each
binary relationship type

(7) Determine the disjointness and completeness constraints for each ISA

Summary of Notation for ER and EER Diagrams

DepartmentEmployee Works_for

DepartmentEmployee Works_for
M

DepartmentEmployee Works_for

DepartmentEmployee Works_for

N

M 1

1 1

Employee

AddressName

SalarySSN

DepartmentEmployee Works_for

Employee

Name

SalarySSN

Supervise

Supervisor Subordinate

Address

Employee

Name

SalarySSN

has

Address

ChildCname Age

ProjectSupplier Supply

Part

Quantity Pid

Part_no

Sname

DepartmentEmployee Works_for
(1,1) (1,N)

Weak

Entity
Entity

Identifying

Relationship
Relationship

Attribute
Multivalued

Attribute

Key

Attribute

Partial Key

Attribute

Employee

AddressName

SalarySSN

ISA

Secretary Technician Engineer

TypeLevel

Grade

Secretary

Address
Name Salary

SSN Level Engineer

Address
Name Salary

SSN Type

Technician

Address
Name Salary

SSN Grade
Employee

AddressName

SalarySSN

ISA

Secretary Technician Engineer

TypeLevel

Grade

Employee

AddressName

SalarySSN

ISA

d

Disjoint

o

Overlap

(default)

d

Employee

o

Employee

Total

d

Employee

o

Employee

Partial

(default)

ISA d

Disjoint

o

Overlap

(default)

Total
Partial

(default)

CourseLecturer Teaches
M 1

Department

Employee
Works_for

M 1

Location

Has

M
1

Entity-Relationship Model