SE (CIS)

CS-306: DBMS

DATABASE DESIGNING
Database designing is the process of developing database structures from user requirements for data. It
starts with requirement analysis, which identifies user needs (present and future) for data. It then proceeds
by translating these user requirements into first a conceptual, then a physical, database design. The
resulting design must satisfy user needs in terms of completeness, integrity, performance constraints and
other factors.

Designing databases is a complex and difficult process. It requires the commitment and participation of
the entire organization. Also it requires the use of an organized approach or methodology. Until recently,
such methodology did not exist and database design was often a haphazard process. However, a number
of tools and techniques (including computer-assisted design) are now available to facilitate database
design.

STEPS IN DATABASE DESIGN

A. Requirement Formulation and Analysis

B. Conceptual Design
C. Implementation Design
D. Physical Design

A. Requirements Formulation and Analysis

The process of collecting, analyzing and documenting information about the part of the organization that
is to be supported by the database application and using this information to identify the users present and
future data requirements of the new system.

As shown in figure, the major inputs to this process are user information requirements (especially the data
items that are used and the associations between those data items) and processing requirements (report
frequencies, response-time requirements and so on).

The information is gathered for each major user view. A user view defines what is required of a database
application from the perspective of a particular job role (such as Manager or Supervisor) or enterprise
application area (such as marketing, HR etc). A database application may have one or more user views.
The information gathered for each major view (i.e. job role or enterprise application area) includes

A description of the data used or generated

The details of how data is to be used or generated (i.e. transactions performed)
Any additional requirements for the new database application
A purchasing officer’s view of a manufacturing organization includes data about Raw Material and
Supplier. The officer would like to store data such as material ID, description, quantity on hand, unit cost
etc. about the raw material used by the organization for manufacturing the products. They would like to
generate report on monthly raw material usage, amount owed by company to each supplier etc.

Page 1 of 4
 SE (CIS)
CS-306: DBMS

These requirements may be gathered using fact-finding techniques including interviewing, observing,
examining, questionnaires etc.

The database developer might ask following questions from the managers and other staff members during
interviews:-
What is your job description?
What kinds of data do you work with?
What types of reports do you use?
While conducting interviews about user views, we should also collect more general information on the
system requirements. Examples of the types of questions that may be asked about the system include
What transactions run frequently on the database?
When do the critical transactions run?
What type of security do you want for the database?
What type of protection from failures or data loss do you want for the database application?
The above questions help to identify different user views of data, data elements required in the user
views, primary keys, relationships among data elements, operational requirements such as security,
integrity and response time.

Page 2 of 4
 SE (CIS)
CS-306: DBMS

B. Conceptual Design
The purpose of conceptual design is to synthesize the various user views and information requirements
into a conceptual data model independent of all physical considerations. This data model is also called
conceptual schema and may be expressed in one of several forms: an entity-relationship diagram,
semantic data model, normalized relations and so on. The schema developed at this conceptual design
phase provides a detailed overview of the enterprise.

The conceptual data model describes entities, attributes, relationships, candidate keys, primary keys,
different types of constraints etc. It is entirely independent of implementation details such as target
DBMS software, specific data models (e.g. relational), hardware platform or any other physical
considerations.

A fully developed conceptual schema will also indicate the information requirements of the enterprise
identified during the requirements formulation stage. This includes operations such as modifying or
updating data, searching for and retrieving specific data and deleting data. A review of the schema for
meeting information requirements can be made at this stage of conceptual design.

C. Logical Design
The process of constructing a model of the information used in an enterprise based on a specific data
model but independent of a particular DBMS and other physical considerations.

The conceptual data model created in the previous phase is refined and mapped onto a logical data
model. The logical data model is based on the target data model for the database (for example, the
relational data model). In other words, we know that the DBMS is, for example, relational, network,
hierarchical or object oriented. However, we ignore any other aspects of the chosen DBMS and, in
particular, any physical details, such as storage structures or indexes.

Throughout the process of developing a logical data model, the model is tested and validated against the
user’s requirements. The technique of normalization is used to test the correctness of a logical data
model. Normalization ensures that the relations derived from the data model do not display data
redundancy, which can cause update anomalies when implemented.
D. Physical Design
The 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. The goal is to design a database that will
efficiently and securely handle all data processing against it.

In developing the physical database design, we must first identify the target database system. Therefore,
physical design is tailored to a specific DBMS system. There is feedback between physical and logical
design because decisions are taken during physical design for improving performance that may affect
the structure of the logical model.

Page 3 of 4
 SE (CIS)
CS-306: DBMS

In general, the main aim of physical database design is to describe how we intend to physically
implement the logical database design. For the relational model, this involves

Creating a set of relational tables and the constraints on these tables from the information presented
in the logical model.
Identifying the specific storage structures and access methods (indexing and hashing) for the data to
achieve an optimum performance.
Designing security protection for the system.

*****

Page 4 of 4