Database Modeling and
Design
CONCEPTUAL AND LOGICAL DESIGN
MTI – Manajemen Data
Relational Database Design Methodology
Conceptual database design
◦Step 1 Build conceptual data model
◦ Step 1.1 Identify entitiy types
◦ Step 1.2 Identify relationship types
◦ Step 1.3 Identify and associate attributes with entity or
relationship types
◦ Step 1.4 Determine attribute domain
◦ Step 1.5 Determine candidate, primary, and alternate key
◦ Step 1.6 Consider use of enhanced modelling concepts
◦ Step 1.7 Check model for redundancy
◦ Step 1.8 Validate conceptual data model against user
transactions
◦ Step 1.9 Review conceptual data model with user
2
ER diagram of Branch user views of
DreamHome
© Pearson Education Limited 2010 3
Concepts of the ER Model
4
Entity types
¡Group of objects with same properties, identified by
enterprise as having an independent existence:
÷Physical: Staff, Property, Customer, Part, Supplier, Product
÷Conceptual: Sale, Course, Inspection.
Relationship types
¡Set of meaningful associations among entity types.
¡Degree of a Relationship
÷Number of participating entities in relationship: Unary, Binary,
Ternary, Quaternary
Attributes
¡Property of an entity or a relationship type.
© Pearson Education Limited 2010 4
5
ER diagram of Branch Has Staff relationship
© Pearson Education Limited 2010 6
Unary, Binary, and Ternary Relationship
© Pearson Education Limited 2010 7
Multiple Relationship and
Relationship with Attributes
8
© Pearson Education Limited 2010 8
Attributes
Simple Attribute
¡ Attribute composed of a single component with an independent
existence.
Composite Attribute
¡ Attribute composed of multiple components, each with an
independent existence.
Multi-valued Attribute
¡ Attribute that holds multiple values for each occurrence of an entity
type.
Derived Attribute
¡ Attribute that represents a value that is derivable from value of a
related attribute, or set of attributes, not necessarily in the same
entity type.
© Pearson Education Limited 2010 9
Keys
Candidate Key
◦Minimal set of attributes that uniquely identifies each
occurrence of an entity type.
Primary Key
◦Candidate key selected to uniquely identify each
occurrence of an entity type.
Composite Key
◦A candidate key that consists of two or more attributes.
10
© Pearson Education Limited 2010
ER diagram of Staff and Branch entities and
their attributes
11
© Pearson Education Limited 2010
Entity Type
Strong Entity Type
◦ Entity type that is not existence-dependent on some other entity type.
Weak Entity Type
◦ Entity type that is existence-dependent on some other entity type.
◦ Dependent entities as rectangles with rounded corners.
© Pearson Education Limited 2010 12
Structural Constraints
Main type of constraint on relationships is called multiplicity.
Represents policies (called business rules) established by
user or company.
Multiplicity is made up of two types of restrictions on
relationships: cardinality and participation.
Cardinality
¡ Describes maximum number of possible relationship occurrences for
an entity participating in a given relationship type.
Participation
¡ Determines whether all or only some entity occurrences participate in
a relationship.
© Pearson Education Limited 2010 13
Structural Constraints
The most common degree for relationships is binary.
Binary relationships are generally referred to as being:
◦one-to-one (1:1)
◦one-to-many (1:*)
◦many-to-many (*:*)
© Pearson Education Limited 2010 14
Multiplicity of Staff Manages Branch (1:1)
relationship
© Pearson Education Limited 2010 15
Multiplicity of Staff Oversees PropertyForRent (1:*)
relationship type
© Pearson Education Limited 2010 16
Multiplicity of Newspaper Advertises
PropertyForRent (*:*) relationship
© Pearson Education Limited 2010 17
Multiplicity as cardinality and participation
constraints
© Pearson Education Limited 2010 18
Enhanced Entity Relationsip
Modelling
Limitations of basic concepts of the ER model
and requirements to represent more complex
applications using additional data modeling
concepts.
Most useful additional data modeling concept of
Enhanced ER (EER) model is called
specialization/generalization.
A diagrammatic technique for displaying
specialization/generalization in an EER diagram
using UML.
© Pearson Education Limited 2010 19
The Enhanced Entity-Relationship Model
Since 1980s there has been an increase in
emergence of new database applications with
more demanding requirements.
Basic concepts of ER modeling are not sufficient
to represent requirements of newer, more
complex applications.
Response is development of additional ‘semantic’
modeling concepts.
© Pearson Education Limited 2010 20
The Enhanced Entity-Relationship Model
Semantic concepts are incorporated into the
original ER model and called the Enhanced
Entity-Relationship (EER) model.
Examples of additional concept of EER model is
called specialization / generalization.
© Pearson Education Limited 2010 21
Specialization / Generalization
Superclass
◦ Anentity type that includes one or more distinct
subgroupings of its occurrences.
Subclass
◦ A distinctsubgrouping of occurrences of an
entity type.
© Pearson Education Limited 2010 22
Specialization / Generalization
Superclass/subclass relationship is
one-to-one (1:1).
Superclass may contain overlapping
or distinct subclasses.
Not all members of a superclass need
be a member of a subclass.
© Pearson Education Limited 2010 23
Specialization / Generalization
Attribute Inheritance
◦ An entity in a subclass represents same ‘real
world’ object as in superclass, and may possess
subclass-specific attributes, as well as those
associated with the superclass.
© Pearson Education Limited 2010 24
Specialization / Generalization
Specialization
◦ Processof maximizing differences between
members of an entity by identifying their
distinguishing characteristics.
Generalization
◦ Process of minimizing differences between
entities by identifying their common
characteristics.
© Pearson Education Limited 2010 25
AllStaff relation holding details of all staff
© Pearson Education Limited 2010 26
Specialization/generalization of Staff entity into
subclasses representing job roles
© Pearson Education Limited 2010 27
Specialization/generalization of Staff entity into job
roles and contracts of employment
© Pearson Education Limited 2010 28
EER diagram with shared subclass and subclass
with its own subclass
© Pearson Education Limited 2010 29
Constraints on Specialization / Generalization
Two constraints that may apply to a
specialization/generalization:
◦participation constraints
◦disjoint constraints.
Participation constraint
◦Determines whether every member in superclass
must participate as a member of a subclass.
◦May be mandatory or optional.
© Pearson Education Limited 2010 30
Constraints on Specialization / Generalization
Disjoint constraint
◦Describes relationship between members of the subclasses
and indicates whether member of a superclass can be a
member of one, or more than one, subclass.
◦May be disjoint or nondisjoint.
© Pearson Education Limited 2010 31
Constraints on Specialization / Generalization
There are four categories of constraints of
specialization and generalization:
◦mandatory and disjoint
◦optional and disjoint
◦mandatory and nondisjoint
◦optional and nondisjoint.
© Pearson Education Limited 2010 32
DreamHome worked example - Staff Superclass with Supervisor and
Manager subclasses
© Pearson Education Limited 2010 33
DreamHome worked example - Owner Superclass with
PrivateOwner and BusinessOwner subclasses
© Pearson Education Limited 2010 34
DreamHome worked example - Person superclass with Staff,
PrivateOwner, and Client subclasses
© Pearson Education Limited 2010 35
Database Design Methodology
Logical database design for the relational
model
◦Step 2 Build logical data model
◦ Step 2.1 Derive relations for logical data model
◦ Step 2.2 Validate relations using normalization
◦ Step 2.3 Validate relations against user transactions
◦ Step 2.4 Check integrity constraints
◦ Step 2.5 Review logical data model with user
◦ Step 2.6 Merge logical data models into global model (optional)
◦ Step 2.7 Check for future growth
36
Mapping from E-R Diagram
to Relations
(1) Strong entity types
◦ For each strong entity in the data model, create a relation that
includes all the simple attributes of that entity. For composite
attributes, include only the constituent simple attributes.
(2) Weak entity types
◦ For each weak entity in the data model, create a relation that
includes all the simple attributes of that entity. The primary key
of a weak entity is partially or fully derived from each owner
entity and so the identification of the primary key of a weak
entity cannot be made until after all the relationships with the
owner entities have been mapped.
© Pearson Education Limited 2010 37
Step 2.1 Derive relations
for logical data model
(3) One-to-many (1:*) binary relationship types
◦ For each 1:* binary relationship, the entity on the ‘one side’ of the
relationship is designated as the parent entity and the entity on the
‘many side’ is designated as the child entity. To represent this
relationship, post a copy of the primary key attribute(s) of parent entity
into the relation representing the child entity, to act as a foreign key.
© Pearson Education Limited 2010 38
Step 2.1 Derive relations
for logical data model
(4) One-to-one (1:1) binary relationship types
◦ Creating relations to represent a 1:1 relationship is more complex as the
cardinality cannot be used to identify the parent and child entities in a
relationship. Instead, the participation constraints are used to decide
whether it is best to represent the relationship by combining the
entities involved into one relation or by creating two relations and
posting a copy of the primary key from one relation to the other.
◦ Consider the following
◦ (a) mandatory participation on both sides of 1:1 relationship;
◦ (b) mandatory participation on one side of 1:1 relationship;
◦ (c) optional participation on both sides of 1:1 relationship.
© Pearson Education Limited 2010 39
Step 2.1 Derive relations
for logical data model
(a) Mandatory participation on both sides of 1:1
relationship
◦ Combine entities involved into one relation and choose one of the primary keys
of original entities to be primary key of the new relation, while the other (if one
exists) is used as an alternate key.
(b) Mandatory participation on one side of a 1:1
relationship
◦ Identify parent and child entities using participation constraints. Entity with
optional participation in relationship is designated as parent entity, and entity
with mandatory participation is designated as child entity. A copy of primary key
of the parent entity is placed in the relation representing the child entity. If the
relationship has one or more attributes, these attributes should follow the
posting of the primary key to the child relation.
© Pearson Education Limited 2010 40
Step 2.1 Derive relations
for logical data model
(c) Optional participation on both sides of a 1:1
relationship
◦ In this case, the designation of the parent and child entities is arbitrary
unless we can find out more about the relationship that can help a
decision to be made one way or the other.
© Pearson Education Limited 2010 41
Step 2.1 Derive relations
for logical data model
(5) One-to-one (1:1) recursive relationships
◦ For a 1:1 recursive relationship, follow the rules for participation as
described above for a 1:1 relationship.
◦ mandatory participation on both sides, represent the recursive
relationship as a single relation with two copies of the primary
key.
◦ mandatory participation on only one side, option to create a
single relation with two copies of the primary key, or to create a
new relation to represent the relationship. The new relation
would only have two attributes, both copies of the primary key.
As before, the copies of the primary keys act as foreign keys
and have to be renamed to indicate the purpose of each in the
relation.
◦ optional participation on both sides, again create a new
relation as described above.
© Pearson Education Limited 2010 42
Step 2.1 Derive relations
for logical data model
(6) Superclass/subclass relationship types
◦ Identify superclass entity as parent entity and subclass entity as the
child entity. There are various options on how to represent such a
relationship as one or more relations.
◦ The selection of the most appropriate option is dependent on a number
of factors such as the disjointness and participation constraints on the
superclass/subclass relationship, whether the subclasses are involved in
distinct relationships, and the number of participants in the
superclass/subclass relationship.
© Pearson Education Limited 2010 43
Guidelines for representation of
superclass / subclass relationship
Other options:
© Pearson Education Limited 2010 44
Guidelines for representation of
superclass / subclass relationship
Other options for mapping:
Option A. General solution, applicable for all cases. Multiple
relations-Superclass and subclasses.
Option B. Multiple relations-Subclass relations only.
Option C. Single relation with one type attribute.
Option D. Single relation with multiple type attributes.
In what case are they applicable?
45
Conceptual data model for Staff view showing all
attributes
© Pearson Education Limited 46
2010
Representation of superclass / subclass relationship
based on participation & disjointness
© Pearson Education Limited 2010
47
Step 2.1 Derive relations
for logical data model
(7) Many-to-many (*:*) binary relationship
types
◦ Create a relation to represent the relationship and include any
attributes that are part of the relationship. We post a copy of the
primary key attribute(s) of the entities that participate in the
relationship into the new relation, to act as foreign keys. These foreign
keys will also form the primary key of the new relation, possibly in
combination with some of the attributes of the relationship.
(8) Multi-valued attributes
◦ Create a new relation to represent multi-valued attribute and include
primary key of entity in new relation, to act as a foreign key. Unless the
multi-valued attribute is itself an alternate key of the entity, the primary
key of the new relation is the combination of the multi-valued attribute
and the primary key of the entity.
© Pearson Education Limited 2010 48
Summary of how to map entities and relationships
to relations
© Pearson Education Limited 2010 49
Relations that represent the global logical data
model for DreamHome
© Pearson Education Limited 2010
50
Global relation diagram for
DreamHome
© Pearson Education Limited 2010 51
Referential integrity constraints
© Pearson Education Limited 2010
52
Domain
§ Domain is the complete set of possible values
that an attribute can be assigned.
§ A domain provides a means of standardizing
the characteristics of the attributes.
§ For example, the domain Date, which contains all
possible valid dates, can be assigned to any date
attribute in a logical data model or date
columns/fields in a physical data model, such as:
EmployeeHireDate, OrderEntryDate,
ClaimSubmitDate, CourseStartDate
53
Domain
Domains can be defined in different ways.
§ Data Type: Domains that specify the standard types of data one can have in an
attribute assigned to that domain. For example, Integer, Character(30), and Date
are all data type domains.
§ Data Format: Domains that use patterns including templates and masks, such as
are found in postal codes and phone numbers, and character limitations
(alphanumeric only, alphanumeric with certain special characters allowed, etc.) to
define valid values.
§ List: Domains that contain a finite set of values. These are familiar to many people
from functionality like dropdown lists. For example, the list domain for
OrderStatusCode can restrict values to only{Open, Shipped, Closed, Returned}.
§ Range: Domains that allow all values of the same data type that are between one
or more minimum and/or maximum values. Some ranges can be open-ended. For
example, OrderDeliveryDate must be between OrderDate and three months in the
future.
§ Rule-based: Domains defined by the rules that values must comply with in order
to be valid. These include rules comparing values to calculated values or other
attribute values in a relation or set. For example, ItemPrice must be greater than
ItemCost.
54
