Database Design Process
01.What
are the stages of database system development life cycle?
I.
Database
planning
II.
System
definition
III.
Requirements
collection and analysis
IV.
Database
design
V.
DBMS
selection
VI.
Application
design
VII.
Prototyping
VIII.
Implementation
IX.
Data
conversion and loading
X.
Testing
XI.
Operational
maintenance
02.What
are the main responsibilities of each stage?
I.
Database planning:
§ Management activities that allow stages of database system development
lifecycle to be realized as efficiently and effectively as possible.
§ Must be integrated
with overall IS strategy of the organization.
§ Database planning
should also include development of standards that govern;
ü how data will be
collected
ü how the format
should be specified
ü what necessary
documentation will be needed
ü how design and
implementation should proceed.
II.
System
definition:
§ Describes scope and
boundaries of database system and the major user views.
§ User view defines
what is required of a database system from perspective of:
ü a particular job
role (such as Manager or Supervisor) or enterprise application area (such as
marketing, personnel, or stock control)
III.
Requirements
collection and analysis:
§ Process of collecting and analyzing information about the part of
organization to be supported by the database system, and using this information
to identify users’ requirements of new system.
§ Information is
gathered for each major user view including;
ü a description of
data used or generated
ü details of how data
is to be used/generated
ü any additional
requirements for new database system.
§ Information is
analyzed to identify requirements to be included in new database system.
Described in the requirements specification.
§ Another important
activity is deciding how to manage the requirements for a database system with
multiple user views.
IV.
Database
design:
§ Process of creating a design for a database that will support the
enterprise’s mission statement and mission objectives for the required database
system.
§ Main purposes of
data modeling include;
ü to assist in
understanding the meaning (semantics) of the data
ü to facilitate
communication about the information requirements.
§ Building data model
requires answering questions about entities, relationships, and attributes.
V.
DBMS
selection :
§ Selection of an appropriate DBMS to support the database system.
§ Undertaken at any time prior to logical design provided sufficient
information is available regarding system requirements.
§ Main steps to
selecting a DBMS:
ü define Terms of
Reference of study
ü shortlist two or
three products
ü evaluate products
ü recommend selection
and produce report
VI.
Application
design:
§ Design of user
interface and application programs that use and process the database.
§ Database design and
application design are parallel activities.
§ Includes two
important activities;
ü
transaction design
ü
user interface design
VII.
Prototyping :
§ Building working
model of a database system.
§ Purpose
ü
to identify features of a system that work well, or
are inadequate
ü
to suggest improvements or even new features
ü
to clarify the users’ requirements
ü
to evaluate feasibility of a particular system design
VIII.
Implementation:
§
Physical realization of the
database and application designs.
ü
Use DDL to create database
schemas and empty database files.
ü
Use DDL to create any
specified user views.
ü
Use 3GL or 4GL to create the
application programs. This will include the database transactions implemented
using the DML, possibly embedded in a host programming language.
IX.
Data
conversion and loading:
§ Transferring any existing data into new database and converting any
existing applications to run on new database.
§ Only required when new database system is replacing an old system.
ü DBMS normally has utility that loads existing files into new database.
§ May be possible to convert and use application programs from old system
for use by new system.
X.
Testing:
§ Process of running the database system with intent of finding errors.
§ Use carefully planned test strategies and realistic data.
§ Testing cannot show absence of faults; it can show only that software
faults are present.
§ Demonstrates the database and application programs appear to be
working according to requirements.
§ Should also test usability of system.
§ Evaluation conducted against a usability specification.
XI.
Operational
maintenance:
§ Process of monitoring and
maintaining database system following installation.
§ Monitoring performance of
system.
ü if performance falls, may
require tuning or reorganization of the database.
§ Maintaining and upgrading
database application (when required).
§ Incorporating new
requirements into database application.
03. Explain
the importance of database design stage?
Database design is important because a database should be
catered to fit the needs of the company or business using the database. Not all
databases can function the same way, especially because not all businesses
function in the same way. Additionally, a database needs to be user friendly.
It needs to provide users with a means of getting to and storing their
information with little trouble. It also needs to be secure against outside
attacks.
04. What
are the phases using database design stage?
I.
Conceptual database design :
§ Process of
constructing a model of the data used in an enterprise, independent of all
physical considerations.
§
Data model is
built using the information in users’ requirements specification.
§
Conceptual data
model is source of information for logical design phase.
ü ERD, DD
II.
Logical database design :
§ Process of constructing a model of the data used in an
enterprise based on a specific data model
(E.g.
relational), but independent of a particular DBMS and other physical considerations.
§
Conceptual data
model is refined and mapped on to a logical data model.
ü DB schemas
III.
Physical database design :
§ Process of producing a description of the database
implementation on secondary storage.
§
Describes base
relations, file organizations, and indexes used to achieve efficient access to data.
Also describes any associated integrity constraints and security measures.
§
Tailored to a
specific DBMS system.
05. Explain
usage of ER diagrams and why it is so important for database development?
Developing
a database without an ERD as building a house without a building plan. It might
be doable because you think that simply laying a brick one over another is
enough to build something; however the moment somebody else takes responsibility
over the project there is disaster potential. In my experience you will not
benefit much from ERDs unless you use them in conjunction with CASE tools (Erwin,
MySQL Workbench etc.) which will additionally allow you to perform some really
helpful operations such as forward and reverse engineering. Even without these
functions having a centralized schematic of the complete database is useful because
sometimes the constraints implemented in the database itself are not enough to
tell the full story about the relationships between particular database
entities. Here is an example involving MySQL which, as you might know,
implements several internal storage engines, most notably MyISAM and InnoDB.
There are significant differences between them one of the most important one
being that MyISAM doesn't support constraints. Despite that fact MyISAM is
heavily used for web applications which means that the relational logic needs
to be implemented either through business logic (application code)
or
in another way. The problem is when you forward engineer an ERD with MyISAM
tables
(Entities)
MySQL will silently ignore the constraints set by the ERD and you will end up
with
a
database which doesn't clearly identify the nature of the relationships between
entities. In
other
words after you develop the database layout there is no way for the code
developers
to
implement proper business logic without an ERD.
ER Diagrams Important:
§ If you want to use a
DBMS, you need to be able to represent your data in it.
§ There are many ways to
achieve this.
§ We will discuss one
approach that is traditionally seen as a good and successful one.
§ No matter which approach
is used, one can end up with a good or a bad database design.
§ In a future lecture, we
will discuss objective criteria for discovering and fixing bad design choices.
