Entity-Relationship Model – Part 1
Database Design Process
IT Projects1
2011 2012 2013 2014 2015
SUCCESSFUL 29% 27% 31% 28% 29%
CHALLENGED 49% 56% 50% 55% 52%
FAILED 22% 17% 19% 17% 19%
1
CHAOS report by Standish Group, 2015
IT Projects1
2011 2012 2013 2014 2015
SUCCESSFUL 29% 27% 31% 28% 29%
CHALLENGED 49% 56% 50% 55% 52%
FAILED 22% 17% 19% 17% 19%
There can be many reasons, including:
Customers were not sure about what they wanted,
Requirements were not properly documented,
Improper development methodology was used,
Resources were not sufficient,
There were communication issues,
. . .
1
CHAOS report by Standish Group, 2015
The Tyre Swing Project
As proposed by
the project sponsor
As specified in the
project request
As designed by the
senior analyst
As produced by
the programmers
As installed at the
user’s site
What the user
wanted
Database Design – Four Phases
The database design process has four phases:
1 Requirements Collection and Analysis
2 Conceptual Design
3 Logical Design
4 Physical Design
Phase 1: Requirements Collection and Analysis
Requirements collection and analysis is the process of collecting and
analyzing data requirements of the organization so as to provide database
solutions that fulfill business needs of the organization.
Compilation of data requirements includes:
a description of data used or generated;
details of how data is to be used/generated;
any additional requirements for new database system;
. . .
Phase 2: Conceptual Design
Conceptual design is the process of constructing a conceptual data model
that is
modeled at a high-level of abstraction;
sufficiently simple and often graphical;
used to communicate the requirements of a database with
nontechnical users.
A conceptual data model is built using the information in users’
requirements specification.
Note: The conceptual design is based on the entity-relationship model in
this course.
Phase 3: Logical Design
Logical design is the process of constructing a logical data model (e.g.
relational or object-oriented).
A conceptual data model is translated onto a logical data model, which can
be further refined (e.g., normalisation) to meet the data requirements. For
example,
From: An ER model
To: Relations with their primary and foreign keys, which facilitates SQL
to deal with retrieving, updating and deletion.
Note: The logical design is based on the relational data model in this
course.
Phase 4: Physical Design
Physical design is the process of implementing the logical data model in a
specific database management system (DBMS).
Assume that the logical data model is the relational data model. Then the
physical design is to create relations in a DBMS that involves:
Selecting the files in which to store the relations.
Deciding which indexes should be used to achieve efficient access.
Describing the integrity constraints and security measures.
. . .
The decisions made during the physical design phrase affect the
performance and accessibility of the database.
Note: Details of this topic are out of the scope of our course.
Database Design Process
Data Requirements
Requirements
Collection and
Analysis
Conceptual
Design
Logical
Design
Physical
Design
Functional
Analysis
Functional Requirements
Conceptual Schema
Logical Schema
Internal Schema
Application
Program Design
High-Level Transaction
Specification
Transaction
Implementation
Application Porgrams
Miniworld