University of Salahaddin

PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

College of Engineering
Software & Informatics Engineering Department

Methodology for DB Design

Conceptual , Logical ,Physical database
design

2nd stage

Lecturer:
Hanan Kamal

2018-2019
 Objectives
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

• The purpose of a design methodology.

• Database design has three main phases:
– conceptual, logical, and physical design.
• How to decompose the scope of the design
• into specific views of the enterprise.

2
 Database Design Methodology
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

• A structured approach that uses procedures,

techniques, tools, and documentation aids to
support and facilitate the process of design.
• Three main phases
– Conceptual database design
– Logical database design
– Physical database design

3
 Conceptual Database Design
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

• The process of constructing a model of the

data used in an enterprise, independent of all
physical considerations.

4
 Overview Database Design
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

Methodology
Conceptual database design
• Step 1 Build conceptual data model
1. Identify entity types
2. Identify relationship types
3. Identify and associate attributes with entity or
relationship types
4. Determine attribute domains
5. Determine candidate, primary, and alternate key
attributes

5
 Overview Database Design
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

Methodology
Step 1 Build conceptual data model (continue)
6. Consider use of enhanced modeling concepts
(optional step)
7. Check model for redundancy
8. Validate conceptual model against user
transactions
9. Review conceptual data model with user

6
 Step 1 Build Conceptual Data
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

• To build a conceptual data model of the data

requirements of the enterprise.
– Model comprises entity types, relationship types, attributes and
attribute domains, primary and alternate keys, and integrity
constraints.

• Step 1.1 Identify entity types

– To identify the required entity types.
– For the Staff user views of DreamHome we identify the
following entities:
– Staff PropertyForRent
– PrivateOwner BusinessOwner
– Client Preference
– Lease
7
 Step 1 Build Conceptual Data
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

• Step 1.2 Identify relationship types

– To identify the important relationships that exist
between the entity types.
– Typically, relationships are indicated by verbs or
verbal expressions. For example:
– Staff Manages PropertyForRent
– PrivateOwner Owns PropertyForRent
– PropertyForRent AssociatedWith Lease

8
 Step 1 Build Conceptual Data
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

• Step 1.3 Identify and associate attributes

with entity or relationship types
– To associate attributes with the appropriate entity or
relationship types and document the details of each
attribute.
– Simple/composite attributes
– Single/multi-valued attributes
– Derived attributes
– Potential problems
• AS EXAMPLE SHOWN IN THE NEXT SLIDE

9
 Step 1 Build Conceptual Data
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

• DreamHome attributes for entities For the Staff user views of

DreamHome, we identify and associate attributes with
entities as follows:
• Staff staffNo, name (composite: fName, lName), position, sex,
DOB
• PropertyForRent propertyNo, address (composite: street, city,
postcode), type, rooms, rent
• PrivateOwner ownerNo, name (composite: fName, lName),
address, telNo
• BusinessOwner ownerNo, bName, bType, address, telNo,
contactName
• Client clientNo, name (composite: fName, lName), telNo
• Preference prefType, maxRent
• Lease leaseNo, paymentMethod, deposit (derived as
PropertyForRent.rent*2), depositPaid, rentStart, rentFinish,
duration (derived as rentFinish – rentStart)
10
 Step 1 Build Conceptual Data
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

• Step 1.4 Determine attribute domains

– To determine domains for the attributes in the
data model and document the details of each
domain.
– For example, we may define:
• (staffNo) as being a five-character variable length
string, with the first two characters as letters and
the next one to three characters as digits in the
range 1–999;
• (geneder) of the Staff entity as being either ‘M’ or
‘F’.
11
 Step 1 Build Conceptual Data
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

• Step 1.5 Determine candidate, primary, and

alternate key attributes
– To identify the candidate key(s) for each entity and if
there is more than one candidate key, to choose one to
be the primary key and the others as alternate keys.
• Step 1.6 Consider use of enhanced modeling
concepts (optional step)
– To consider the use of enhanced modeling concepts, such
as specialization / generalization, aggregation, and
composition.

12
 Step 1 Build Conceptual Data Model
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

• Step 1.7 Check model for redundancy

– To check for the presence of any redundancy in the model
and to remove any that does exist.
• Step 1.8 Validate conceptual model against
user transactions
– To ensure that the conceptual model supports the
required transactions.
• Step1.9 Review conceptual data model with
user
– To review the conceptual data model with the user to
ensure that the model is a ‘true’ representation of the
data requirements of the enterprise.
13
 Extract from data dictionary for Staff user views of
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

DreamHome showing description of entities

14
First-cut ER diagram for Staff user
views of DreamHome

15
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com
 Extract from data dictionary for Staff user views of
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

DreamHome showing description of relationships

16
 Extract from data dictionary for Staff user views of
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

DreamHome showing description of attributes

17
DreamHome with primary keys added
ER diagram for Staff user views of

18
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com
 Revised ER diagram for Staff user views of
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

DreamHome with specialization / generalization

19
Example of removing a redundant
relationship called Rents

20
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com
 Using pathways to check that the conceptual
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

model supports the user transactions

21
 Step 2 Build and Validate Logical Data
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

Model
• To translate the conceptual data model into a
logical data model and then to validate this
model to check that it is structurally correct
using normalization and supports the
required transactions.

22
 Step 2 Build and Validate Logical Data
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

Model
• Step 2.1 Derive relations for logical data
model
– To create relations for the logical data model to represent
the entities, relationships, and attributes that have been
identified.

23
Conceptual data model for Staff view
showing all attributes

24
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com
 Step 2.1 Derive relations for logical
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

data model
• Strong entity types
– For each strong entity in the data model, create a
relation that includes all the simple attributes of that
entity.
• 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.

25
 Step 2.1 Derive relations for logical
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

data model
• 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.

26
 Step 2.1 Derive relations for logical
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

data model
• 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

mandatory participation on both sides of 1:1
relationship;

mandatory participation on one side of 1:1
relationship;

optional participation on both sides of 1:1 relationship.

27
 Step 2.1 Derive relations for logical
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

data model
• 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.

28
 Step 2.1 Derive relations for logical
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

data model
• 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.

29
 30
superclass / subclass relationship
Guidelines for representation of
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com
 Representation of superclass / subclass relationship
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

based on participation and disjointness

31
 Step 2.1 Derive relations for logical
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

data model
• 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.

32
 Step 2.1 Derive relations for logical
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

data model
• Complex relationship types
– Create a relation to represent the relationship and include
any attributes that are part of the relationship. Post a copy
of the primary key attribute(s) of the entities that
participate in the complex relationship into the new
relation, to act as foreign keys. Any foreign keys that
represent a ‘many’ relationship (for example, 1..*, 0..*)
generally will also form the primary key of this new
relation, possibly in combination with some of the
attributes of the relationship.
– For example, the ternary Registers relationship

33
 Step 2.1 Derive relations for logical
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

data model
• 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.

34
Summary of how to map entities and
relationships to relations

35
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com
Relations for the Staff user views of
DreamHome

36
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com
 Step 2.2 Validate relations using normalization
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

– To validate the relations in the logical data

model using normalization.

• Step 2.3 Validate relations against user

transactions
– To ensure that the relations in the logical
data model support the required
transactions.

37
 Step 2.4 Check integrity constraints
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

• To check integrity constraints are represented

in the logical data model. This includes
identifying:
• Required data
• Attribute domain constraints
• Multiplicity
• Entity integrity
• Referential integrity
• General constraints

38
 Referential integrity constraints for
relations in Staff user views of
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

DreamHome

39
 Step 2.5 Review logical data model
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

with user
• To review the logical data model with the
users to ensure that they consider the model
to be a true representation of the data
requirements of the enterprise.

40
 Step 2.6 Merge logical data models
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

into global Model (optional step)

• To merge logical data models into a single
global logical data model that represents all
user views of a database.

41
 Step 2.6.1 Merge local logical data
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

models into global model

• To merge local logical data model into a single
global logical data model.

• This activities in this step include:

– Step 2.6.1 Merge local logical data models into
global model
– Step 2.6.2 Validate global logical data model
– Step 2.6.3 Review global logical data model with
users.

42
 Logical v. Physical Database Design
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

• Sources of information for physical design

process includes logical data model and
documentation that describes model.

• Logical database design is concerned with

the what, physical database design is
concerned with the how.

43
 Physical Database Design
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

Process of producing a description of the

implementation of the database on
secondary storage.
It describes the base relations, file
organizations, and indexes used to
achieve efficient access to the data, and
any associated integrity constraints and
security measures.

44
 Overview of Physical Database Design
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

Methodology
• Step 3 Translate logical data model for
target DBMS
– Step 3.1 Design base relations
– Step 3.2 Design representation of derived data
– Step 3.3 Design general constraints

45
 Overview of Physical Database Design
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

Methodology
• Step 4 Design file organizations and
indexes
– Step 4.1 Analyze transactions
– Step 4.2 Choose file organizations
– Step 4.3 Choose indexes
– Step 4.4 Estimate disk space requirements

46
 Overview of Physical Database Design
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

Methodology
• Step 5 Design user views
• Step 6 Design security mechanisms
• Step 7 Consider the introduction of
controlled redundancy
• Step 8 Monitor and tune operational
system

47
 Assignment
PDF Created with deskPDF PDF Creator X - Trial :: http://www.docudesk.com

• 11-2 Deadline title

• 4/3/2019 11:59 PM Report deadline
• 31/3 11:59 PM deadline project

48