Database Systems

Chapter 2: Database Design

Session 1:
Entity Relationship Model

1
 Outline

1 Database Design Process

2 Entity Relationship Model

3 Constructing an Entity Relationship Model

4 The Enhanced Entity Relationship Model

2
Database Design Process

3
 Requirements Collection and
Analysis
là giai on s b thit k c s d liu.
qIs a preliminary stage to database design.
qThe process of collecting and analyzing information
about the part of the organization that is to be
supported by the database system, and using this
information to identify the requirements for the new
system.
qThe outcome of this phase is a user requirements
specification.
Quá trình thu thp và phân tích thông tin v b phn ca t chc s c h tr bi h thng c s d liu và s dng
thông tin này xác nh các yêu cu i vi h thng mi.

Kt qu ca giai on này là mt c t yêu cu ngi dùng

 High level design
Thit k c s d liu khái nim: là quá trình xây dng mô hình d liu t thông tin thu thp c trong
giai on phân tích yêu cu.
qConceptual database design: is the process of
constructing a data model from the information
collected in the requirements analysis phase.
§ It is independent of any particular DBMS at high level
design. Nó c lp vi bt k DBMS c th nào thit k
cp cao
§ It construct an entity relationship model (ERM)
Nó xây dng mô hình quan h thc th (ERM)
§ ERM includes entities that are represented in the
database, attributes of the entities, the relationships
among the entities, and constraints on the entities and
relationships.
ERM bao gm các thc th c biu din trong c s
d liu, các thuc tính ca các thc th, các mi quan h
gia các thc th và các ràng buc i vi các thc th và
 Low level design
qThe process of moving from an abstract data model
to the implementation of the database proceeds in
two final design phases:
§ Logical design
§ Physical design

Quá trình chuyn t mô hình d liu tru tng sang

trin khai c s d liu tin hành theo hai giai
on thit k cui cùng:
Thit k logic
Thit k vt lí
 Low level design
Chúng ta phi chn mt DBMS trin khai thit
k c s d liu ca mình và ánh x mô hình d liu khái nim
qLogical design: cp cao trong giai on trc lên mô hình d liu logic
ca DBMS ã chn (quan h, mng, phân cp, hng i tng)
§ We must choose a DBMS to implement our database
design, and map the high level conceptual data model in the
previous phase onto a logical data model of the chosen
DBMS (relational, network, hierarchical, object-oriented)
§ We will only consider relational DBMS
§ Convert an ER diagram from the conceptual model into a
relational schemas in normal form using Normalization.
• Normalization is used to check the entity relationship model
and help eliminate redundancy and other anomalies in the
database.
Chuyn i s ER t mô hình khái nim thành
lc quan h dng bình thng bng Chun hóa.
Chun hóa c s dng kim tra mô hình mi quan h thc th và
giúp loi b s d tha và các im bt thng khác trong c
s d liu.
 Low level design
qPhysical design:
§ Describes how the database is to be implemented:
• Creating a set of relational tables and the constraints on
these tables from the information presented in the logical
data model;
• Identifying the specific storage structures and access
methods for the data to achieve an optimum
performance for the database system;
• Designing security protection for the system.
Thit k vt lí:
Mô t cách trin khai c s d liu:
• To mt tp hp các bng quan h và các ràng buc trên
các bng này t thông tin c trình bày trong mô hình d
liu logic;
• Xác nh cu trúc lu tr c th và phng pháp
truy cp d liu t c hiu sut
ti u cho h thng c s d liu;
Thit k bo v an ninh cho h thng.
 Entity Relationship Model
JERM là mt mô hình d liu cp cao c s dng phát trin thit k khái nim ca c s d liu.

qERM is a high level data model used for developing

the conceptual design of the database.
qThe ER model also has an associated diagrammatic
representation, the ER diagram, which can express
the overall logical structure of a database graphically
qMany various notations used with ERD—the original
Chen notation and the newer Crow’s Foot and UML
notations.
§ The Chen notation favors conceptual modeling.
§ The Crow’s Foot notation favors a more implementation-oriented
approach
§ The UML notation can be used for both conceptual and
implementation modeling.
9
 Entity Relationship Model
qERM has 3 basic concepts: JERM có 3 khái nim c bn:
§ Entity Thc th
• Thuc tính
§ Attributes
§ Relationships

10
 Entity
qAn entity is a person, a place, an object, an event, or a
concept in the user environment about which the
organization wishes to maintain data.
qAn entity has a noun name.
Example:
• Person: Student, teacher, employee, author,..
• Place: store, classroom, warehouse,…
• Object: Movie, product, car, book,..
• Event: Sale, Registration, Reservation
• Concept: Account, Course

11
 Entity
qAn entity type (or entity set) is a collection of
entities that share the common properties, or
characteristics.
§Example: the entity type student might represent the set
of all students in the university.
qAn entity instance is a single occurrence of an entity
type.
§ For example, there is one EMPLOYEE entity type in most
organizations, but there may be hundreds (or even thousands)
of instances of this entity type stored in the database.

12
 Entity
qExample: Entity type versus Entity instance

An employee type Employee with 2 instances

13
 Entity
qIn an E-R diagram, the entity name is placed inside
the box representing the entity type
qPrimary key is underlined.

14
 Entity
Có hai loi thc th; loi thc th yu và mnh.
qThere are two types of entities; weak and strong
entity types.
§A strong entity type is one that exists independently of
other entity types.
• Instances of a strong entity type always have a unique
characteristic (called an identifier)—that is, an attribute or a
combination of attributes that uniquely distinguish
each occurrence of that entity.
§A weak entity type is an entity type whose existence
depends on some other entity type
• The entity type on which the weak entity type depends is
called the identifying owner
• A weak entity type does not typically have its own identifier
15
 Entity
qExample: Weak entity in ERD
§Carries relationship is the identifying relationship (indicated
by the double line)
§The attribute Dependent Name serves as a partial identifier
(indicated by double underline)

16
 Attribute
Thuc tính là thuc tính hoc c im ca mt loi

qAttributes are properties or characteristics of an

entity type.
qAn attribute has a noun name
§ Example:
• Student: Student ID, Student Name, Home Address, Phone
Number, Major
• Automobile: Vehicle ID, Color, Weight
• Employee: Employee ID, Employee Name, Skill
qIn E-R diagrams, we represent an attribute by
placing its name in the entity it describes.
qAttributes may also associated with relationships.

17
 Attribute Các thuc tính có mt min là tp hp các giá
tr có th có cho mt thuc tính nht nh hoc min là
tp hp các giá tr c phép cho mi thuc tín

qAttributes have a domain which is the set of

possible values for a given attribute, or domain is
the set of permitted values for each attribute
§ Example:
• for the gender attribute consists of only two possibilities: M
or F
• for grade is 0 to 10

18
 Identifier Attribute
qAttribute types:
§ Identifier Attribute
§ Required and Optional attribute
§ Simple and Composite attribute
§ Single-valued and Multivalued attribute
§ Stored and Derived attribute
Các loi thuc tính:
Thuc tính nh danh
Thuc tính bt buc và tùy chn
Thuc tính n gin và hn hp
Thuc tính n giá tr và a giá tr
Thuc tính c lu tr và có ngun gc

19
 Mã nh danh (Khóa)-mt thuc tính (hoc t hp các
thuc tính) xác nh duy nht các phiên bn riêng
Identifier Attribute l ca mt loi thc th. Ngha là, không có
hai trng hp nào ca loi thc th có th có
cùng giá tr cho thuc tính nh danh.

qIdentifier (Key)–an attribute (or combination of

attributes) that uniquely identifies individual
instances of an entity type. That is, no two
instances of the entity type may have the same
value for the identifier attribute.
§ Ex: Student ID is an identifier of Student entity type.
qCandidate Identifier–an attribute that could be a
key…satisfies the requirements for being an
identifier Mã nh danh ng viên-mt thuc tính có th làkhóa...áp ng các yêu cu
tr thành mã nh danh
§ Student Name is not a candidate identifier, because
many students may potentially have the same name.

20
 Identifier Attribute

qSimple versus Composite Identifier

§ A composite identifier is an identifier that consists of a
composite attribute.

The identifier is boldfaced

and underlined

21
 Identifier Attribute

qSome entities may have more than one candidate

identifier. The following criteria for selecting
identifiers:
§ Will not change in value
§ Will not be null
§ No intelligent identifiers (e.g., containing locations or
people that might change)
§ Substitute new, simple keys for long, composite keys
Mt s thc th có th có nhiu hn mt s
nhn dng ng viên. Các tiêu chí sau chn s
nhn dng:
S không thay i v giá tr
S không có giá tr
Không có s nhn dng thông minh (ví d: cha v trí hoc
ngi có th thay i)
Thay th các phím mi, n gin bng các phím tng hp, dài
22
 Null values
Trong mt s trng hp, thc th c th có th không có
giá tr áp dng cho mt thuc tính. Null cng có th
ch nh rng mt giá tr thuc tính không xác nh.

qIn some cases, particular entity may not have an

applicable value for an attribute. Null can also
designate that an attribute value is unknown.
qTwo cases:
1. The attribute value is missing. Giá tr thuc tính b thiu.
§ Ex: Height attribute is NULL.
2. It is not known whether the attribute value exist.
§ Ex: College degree – some people may have it and
some not
Không bit liu giá tr thuc tính có tn ti hay không.

23
 Simple & Composite Attribute
qRequired attribute is an attribute that must have
a value in it. Thuc tính bt buc là thuc tính phi có giá
tr trong ó.

qAn optional attribute that may not have a value

in it and can be left blank. Mt thuc tính tùy chn có th không có giá tr
trong ó và có th trng.
qExample:

24
 Simple & Composite Attribute
qSimple Attribute
§Attribute that have not been divided into subparts
§ Simple (Atomic) attributes
• Ex: Age, City, Postal Code
qComposite Attributes
§Can be divided into smaller subparts, which
represent more basic attributes with independent
meaning.
• Ex: Name can broken down into component attributes:
first_name, middle_initial, last_name

25
 Simple & Composite Attributes
q Composite attributes
§ Example: name, address
composite name address
attributes

first_name middle_initial last_name street city state postal_code

component
attributes
street_number street_name apartment_number

§ The decision about whether to subdivide an attribute

into its component parts depends on whether users will
need to refer to those individual components, and hence,
they have organizational meaning.
§ Most drawing tools do not have a notation for composite
attributes, so you simply list all the component parts
26
 Simple & Composite Attributes
q Composite attributes
§ Most drawing tools do not have a notation for composite
attributes, so you simply list all the component parts

27
 Single valued & Multivalued
Attributes
q Single Valued Attributes
§ Can only have one value for the particular entity.
§ Ex: A student can have only one ID number
q Multivalued Attributes
§ Attributes that can have many values
§ Ex: A student can have more than one phone number, one
skill
qMultivalued and composite are different concepts.
§ A multivalued attribute, may occur multiple times
for each employee
§ Composite attributes, each of which occurs once for each
employee, but which have component, more atomic
attributes
28
 Single valued & Multivalued
Attributes
qMultivalued Attributes
§ Other E-R diagramming tools may use an asterisk (*)
after the attribute name, or
you may have to use supplemental documentation to
specify a multivalued attribute

29
 Stored & Derived Attributes
qStored Attributes
§ The date_of_birth is the stored attribute
qDerived attributes
§ Can be computed from other attributes
§ Example: age is derived from date_of_birth.
§ Age is called derived attribute

30
 Example
q E-R diagram with identifier, multivalued, and
derived attributes

31
 Relationship
q A relationship is an association among several
entities
qA relationship set is a set of relationships of the
same type.
qA relationship has a verb phrase name
qTwo entities can have more than one type of
relationship between them (multiple relationships)

32
 Relationship
q Relationship type and relationship instance
Kiu quan h và th hin quan h

§ A relationship type is a meaningful association between

(or among) entity types
• The relationship type is modeled as lines between entity
types
§ A relationship instance is an association between entity
instances

33
 Example Relationship type and
Relationship instances
a) Relationship type

b) Relationship instances

34
 Relationship
qAttributes on relationship
§ Relationships can have attributes which describe features
pertaining to the association between the entities in the
relationship
§ Example: Date Completed is a property of the relationship
Completes, rather than a property of either Employee or
Course entity.

35
 Relationship
qAssociative Entity
§ Associative entity is an entity type that associates
the instances of one or more entity types and contains
attributes that are peculiar to the relationship between
those entity instances.
§ Associative entities are sometimes referred to as gerunds,
because the relationship name (a verb) is usually converted
to an entity name that is a noun.

36
 Relationship
qAssociative Entity
§ Example: An associative entity Certificate

• Note that there are no relationship names on the lines

between an associative entity and a strong entity
§ An associative entity Certificate using Visio

37
 Mc ca mt tp hp mi quan h
Degree of a Relationship set
q The degree of the relationship set is the number of
entity types that participate in a relationship.
§ Unary relationship, are also called Recursive relationship,
which exists when association is maintained within a single
entity (degree 1)
§ Binary relationship: exists when two entities are associated
(degree 2)
• Most relationship sets in a database system are binary.
§ Ternary relationship: exists when three entities are associated
(degree 3)
qRelationships between more than three-entity types
are rare.
38
 Degree of a Relationship set

One entity Entities of two

related to different types
another of the related to each other Entities of three
same entity type different types related
to each other
39
 Cardinality constraints
q Cardinality constraint, or cardinality ratios, express
the number of entities to which another entity can be
associated via a relationship type.
qMapping cardinalities are most useful in describing
binary relationship set
qFor a binary relationship set the mapping cardinality
must be one of the following types:
§ One to one
§ One to many
§ Many to many

40
 Cardinality constraints
One to one

An entity in A is associated with at most one entity in B, and

an entity in B is associated with at most one entity in A.
41
 Cardinality constraints
One to many

An entity in A is associated with any number (zero or more) of

entities in B. An entity in B, however, can be associated with at
most one entity in A.
42
 Cardinality constraints
Many to many

An entity in A is associated with any number (zero or more) of

entities in B, and an entity in B is associated with any number
(zero or more) of entities in A.
43
 Cardinality constraints
qMinimum Cardinality
§ is the minimum number of instances of entity B that
may be associated with each instance of entity A
qMaximum Cardinality
§ is the maximum number of instances of entity B that
may be associated with each instance of entity A
qEx:

44
 Example of Cardinality
constraints
qOne-to-One relationship
§ one department chair—a professor—can chair only one
department, and one department can have only one
department chair.

qOne-to-Many relationship
§ Each painting is painted by one and only one painter,
but each painter could have many paintings.

45
 Example of Mapping
Cardinalities
qMany-to-Many relationship
§ Each CLASS can have many STUDENTs, and each
STUDENT can take many CLASSES.

46
 Optional and Mandatory
Participation
qParticipation determines whether all or only some
entity occurrences participate in a relationship.
qParticipation in a relationship can be optional (or
Partial) or mandatory (or Total) participation.
§Mandatory participation:
• all entity occurrences are involved in a particular relationship.
• Minimum cardinality is one
§Optional participation:
• only some entities may not participate in a particular
relationship.
• Minimum cardinality is zero

47
 Optional and Mandatory
Participation
qEx1:

A patient history is A patient must have recorded

recorded for one and at least one history, and can
only one patient have many

48
 Optional and Mandatory
Participation
qEx2:

A project must be An employee can be assigned to

assigned to at least one any number of projects, or may
employee, and may be not be assigned to any at all
assigned to many

49
 Optional and Mandatory
Participation
qEx2:
A person is
married to at most
one other person,
or may not be
married at all

50
 Optional and Mandatory
Participation
qAnother examples
§An optional CLASS entity in the relationship “PROFESSOR
teaches CLASS”:

§CLASS is optional to COURSE

§COURSE and CLASS in a mandatory relationship:

51
 Optional and Mandatory
Participation
qExample: Identifying relationship, cardinality
between entities:
4. Customer and Order
5. Product and Category
6. Employee and Dependent
7. Employee and Department
8. Instructor and Class
 Multiple Relationships
Between Entity Types
qThere may be more than one relationship between
the same entity types in a given organization
qEx: Employees and departments
§ One relationship associates employees with the department
in which they work.
§ The second relationship associates each department with the
employee who manages that department.

53
 Weak Entity Thc th yu VKU

qA weak entity set is an entity which is existence-

dependent; that is, it cannot exist without the
entity with which it has a relationship.
qAn entity set that is not a weak entity set is
termed a strong entity set.
qThe weak entity has a primary key that is partially
or totally derived from the parent entity (or strong
entity) in the relationship.

54
 Weak Entity
qExample

55
 Constructing an ER model
Before beginning to draw the ER model, read the
requirements specification carefully.
1. Identify entities
§ List all entity types (These are the object of interest in the
system)
§ Remove duplicate entities (Also do not include the system
as an entity type)
2. List the attributes of each entity
3. Mark the primary keys
§ Which attributes uniquely identify instances of that entity
type?
§ This may not be possible for some weak entities.
56
 Constructing an ER model
4. Define the relationships
§ Examine each entity type to see its relationship to the
others.
5. Describe the cardinality and optionality of the
relationships
§ Examine the constraints between participating entities.
6. Remove redundant relationships
§ Examine the ER model for redundant relationships.
qER modelling is iterative, so expect to draw several
versions. Note that there is no one right answer to the
problem, but some solutions are better than others!

57
 Exercises
Ex1: Company organized into DEPARTMENT.
q An university has several departments. Each department employs
many employees, but each employee works in one department
only.
qAn employee can supervise many other employees, but an
employee may have only one supervisor.
qEach department offers many courses. A course can be a pre-
requisite of many other courses, but a course may have only one
pre-requisite.
 Exercises
Ex2: Company organized into DEPARTMENT.
q Each department has unique name and a particular employee who
manages the department. Start date for the manager is recorded.
Department may have several locations.
q A department controls a number of PROJECT. Projects have a
unique name, number and a single location.
q Company’s EMPLOYEE name, ssno, address, salary, sex and birth
date are recorded. An employee is assigned to one department, but
may work for several projects (not necessarily controlled by her
dept). Number of hours/week an employee works on each project is
recorded; The immediate supervisor for the employee.
qEmployee’s DEPENDENT are tracked for health insurance purposes
(dependent name, birthdate, relationship to employee).
 The Enhanced Entity
Mô hình quan h
thc th nâng cao

Relationship Model (EERM)

qThe term enhanced entity relationship (EER) model
(or the Extended entity relationship model) is used to
identify the model that has resulted from extending the
original E-R model with these new modeling constructs.
q A Diagram using this model is called an EER diagram
(EERD)
qThe most important modeling construct incorporated
in the EER model is supertype/subtype relationships
discriminator

60
 The Enhanced Entity
Relationship Model (EERM)
qAn entity supertype is a generic entity type that is
related to one or more entity subtypes.
qThe entity supertype contains common
characteristics, and the entity subtypes each contain
their own unique characteristics.

61
 Supertype/Subtype
relationship
qAn example: Suppose that an organization has three
basic types of employees: hourly employees, salaried
employees, and contract consultants.
§ Hourly employees Employee Number, Employee Name,
Address, Date Hired, Hourly Rate
§ Salaried employees Employee Number, Employee Name,
Address, Date Hired, Annual Salary, Stock Option
§ Contract consultants Employee Number, Employee Name,
Address, Date Hired, Contract Number, Billing Rate

62
Supertype/Subtype
relationship
All employee subtypes will
have emp_number, name,
address, and date hired

Each employee subtype

will also have its own
attributes

63
 Supertype/Subtype
relationship
qThe property of inheritance enables an entity subtype
to inherit the attributes and relationships of the
supertype.
qRelationships at the supertype level indicate that all
subtypes will participate in the relationship.
qThe instances of a subtype may participate in a
relationship unique to that subtype. In this situation,
the relationship is shown at the subtype level.

64
Example: Supertype/subtype relationships
in a hospital

Both outpatients and resident

patients are cared for by a
responsible physician
Only resident patients are
assigned to a bed

65
 Generalization and
Specialization chuyên bit hóa
Tng quát hóa và

qGeneralization:
§ The process of defining a more general entity type from a
set of more specialized entity types.
§ BOTTOM-UP process
§ Based on grouping common characteristics and relationships
of the subtypes
qSpecialization:
§ The process of defining one or more subtypes of the
supertype and forming supertype/subtype relationships.
§ TOP-DOWN process
§ Based on grouping unique characteristics and relationships
of the subtypes
66
 Example of generalization

a) Three entity types: CAR, TRUCK, and MOTORCYCLE

All these types of vehicles have common attributes

67
 Example of generalization(cont.)
b) Generalization to VEHICLE supertype

So we put the
shared attributes
in a supertype

Note: no subtype for motorcycle, since it has no unique attributes

68
 Example of specialization
a) Entity type PART

Only applies to
manufactured parts

Applies only to purchased parts

69
 Example of specialization (cont.)
b) Specialization to MANUFACTURED PART and PURCHASED PART

Created 2 subtypes

Note: multivalued attribute was replaced by an associative entity relationship

to another entity
70
 Completeness Constraint

qThe completeness constraint specifies whether each

entity supertype occurrence must also be a member of
at least one subtype.
qThe completeness constraint can be partial or total.
§ Partial completeness
• Symbolized by a circle over a single line
• Some supertype occurrences are not members
§ Total completeness
• Symbolized by a circle over a double line
• Every supertype occurrence must be member of at least one
subtype

71
Examples of completeness constraints
a) Total specialization rule

A patient must be
either an outpatient or
a resident patient

72
 Examples of completeness constraints (cont.)
b) Partial specialization rule

A vehicle
could be a
car, a truck,
or neither

73
 Subtype Discriminator
qAn attribute in supertype entity whose value
determines the target supertype(s)
qDefault comparison condition for subtype
discriminator attribute is equality comparison
qSubtype discriminator may be based on other
comparison condition

74
 Disjoint and Overlapping
Constraints
qDisjoint subtypes
§ Also called nonoverlapping subtypes
§ Subtypes that contain unique subset of supertype entity
set, in other words, each entity instance of the
supertype can appear in only one of the subtypes
§ In an ERD, disjoint subtypes are indicated by the letter d
inside the category shape
qOverlapping subtypes
§ Subtypes that contain nonunique subsets of supertype
entity set, that is, each entity instance of the supertype
may appear in more than one subtype.
§ In an ERD, illustrates overlapping subtypes with the
letter o inside the category shape.
75
Example: a subtype discriminator
(disjoint rule)

Employee_Type is called
the subtype discriminator
Example: Subtype discriminator (overlap rule)

77
 Example: Subtype discriminator
(overlap rule)
qA new attribute named Part Type has been added
to PART. Part Type is a composite attribute with
components Manufactured? and Purchased?
qEach of these attributes is a Boolean variable (i.e.,
it takes on only the values yes, “Y,” and no, “N”).
When a new instance is added to PART, these
components are coded as follows:

78
Example of supertype/subtype hierarchy

79
 Entity Clusters
qEER diagrams are difficult to read when there
are too many entities and relationships
=>Solution: Group entities and relationships into
entity clusters
qEntity cluster: Set of one or more entity types
and associated relationships grouped into a
single abstract entity type

80
Possible entity
clusters for Pine
Valley Furniture
in Microsoft
Visio

Related
groups of
entities
could
become
clusters
81
 EER diagram of PVF entity clusters

More readable,
isn’t it?

82
Manufacturing entity cluster

Detail for a single cluster

83
 Exercise
qThe database keeps track of three types of persons:
employees, alumni, and students. A person can belong
to one, two, or all three of these types. Each person
has a name, SSN, sex, address, and birth date.
qEvery employee has a salary, and there are three
types of employees: faculty, staff, and student
assistants. Each employee belongs to exactly one of
these types. For each alumnus, a record of the degree
or degrees that he or she earned at the university is
kept, including the name of the degree, the year
granted, and the major department. Each student has a
major department.
84
 Exercise
qEach faculty has a rank, whereas each staff member
has a staff position. Student assistants are classified
further as either research assistants or teaching
assistants, and the percent of time that they work is
recorded in the database. Research assistants have
their research project stored, whereas teaching
assistants have the current course they work on.
qStudents are further classified as either graduate or
undergraduate, with the specific attributes degree
program (M.S., Ph.D., M.B.A., and so on) for graduate
students and class (freshman, sophomore, and so on)
for under- graduates.
85
86