Database Design
Planning
Webster's dictionary uses the following
phrases to define the term design:
?
"To prepare the preliminary
plans or sketch for"
?
"To intend for a definite
purpose"
?
"The combination of details
or features of something constructed"
?
"To plan and fashion
artistically or skillfully"
?
"Adaptation of means to a
preconceived end"
"To prepare the
preliminary plans or sketch for" implies that there is more work in database
design than what is obvious on the surface. Before the actual design of the
database occurs, there is much planning involved.
The "definite purpose"
of the database should be clearly defined.
Many "details and
features" are involved during the design of any database.
Once the purpose of the
database has been established, the design team should study all of the details
and features that comprise the business.
These details and
features, once gathered and often "sketched," are eventually formatted into a
database structure using a predetermined database model.
During the actual
design of the database, these details and features are "fashioned artistically
and skillfully" into a database model, which will most likely be modified
numerous times before the design process is complete.
"Adaptation of means to
a preconceived end" is an excellent phrase used to describe the activities of
database designers in many situations.
The designers must be
able to adapt the phases and tasks in database design to roll with the changes
and meet the customer's needs. Often, the designers find that the customer's
needs for a database are refined throughout the design process, or even changed
drastically.
The following bullets
represent actions that should be taken during the design planning process:
?
Defining the mission
statement
?
Defining design
objectives
?
Devising a work
plan
?
Setting milestones and
deliverables
?
Establishing project
deadlines
?
Assigning
tasks
Mission
Statement and Design Objectives
The following questions
can be raised to help define the mission statement:
?
What is the purpose of the
database?
?
Who will use the
database?
?
What type of database will
this be?
?
What models and
methodologies will be used?
?
Is this a new
database?
?
Will this database be molded
after a legacy database?
?
Will the database need to be
modified in the near future?
?
Will there be multiple
databases?
?
How will the customer access
the database?
Following is an example of a mission statement and design objectives for a class management and student registration database system:
Mission statement - An automated business system is needed to
manage and track student class registrations, class schedules, instructors, and
instructor availability.
Design
objectives:
?
The
manual process of managing the training program will be
automated.
?
Internal users will be accessing the
database.
?
The
relational model will be used.
?
An
automated design (AD) tool will be used to design the
database.
?
Currently no database is in place to meet
the present needs (no legacy database).
?
The
database might need to be modified as the training company grows and more data
storage requirements are established.
?
This
will be a single database that will reside in one
location.
?
The
users will implement a form interface to query and make changes to data in the
database.
?
Instructors should be able to query the
database and check the status of classes they teach.
?
In
the future, a portion of the database can be made available online so that
students can query the status of classes or programs for which they are
scheduled.
Milestones
A milestone is a significant point in the
database design process. The most common events in the design process that are
associated with milestones are the completion of the different design phases.
Following are common
milestones:
?
All business requirements
are gathered.
?
A work plan is
devised.
?
Entities and attributes are
established.
?
The logical design of the
database is complete.
?
The physical design of the
database is complete.
?
The database is
tested.
?
The database is implemented
into production.
Establishing
a Design Team
The design team might
consist of one or many individuals.
The responsibility to
design a database might fall into the hands of only one individual for smaller
companies.
It is possible for only
one individual to comprise the entire IT department for a small company.
For larger companies
with larger projects, many individuals will most likely be
involved.
The following figure illustrates an
example design team and work distribution:
Planning
Logical and Physical Modeling
The two basic types of data modeling are
as follows:
?
Logical
modeling
?
Physical
modeling
Logical modeling?deals with gathering business
requirements and converting those requirements into a model.
The logical model
revolves around the needs of the business, not the database, although the needs
of the business are used to establish the needs of the
database.
Logical modeling
involves gathering information about business processes, business entities
(categories of data), and organizational units.
After this information
is gathered, diagrams and reports are produced including entity relationship
diagrams, business process diagrams, and eventually process flow diagrams.
Typical deliverables of
logical modeling include:
?
Entity relationship
diagrams
?
Business process
diagrams
?
User feedback
documentation
Physical modeling involves the actual design of a database
according to the requirements that were established during logical modeling.
Physical modeling deals
with the conversion of the logical, or business model,
into a relational database model.
When physical modeling
occurs, objects are being defined at the schema level.
During physical
modeling, objects such as tables and columns are created based on entities and
attributes that were defined during logical modeling.
Constraints are also
defined, including primary keys, foreign keys, other unique keys, and check
constraints.
Views can be created
from database tables to summarize data or to simply provide the user with
another perspective of certain data.
Other objects such as
indexes and snapshots can also be defined during physical modeling. Physical
modeling is when all the pieces come together to complete the process of
defining a database for a business.
Physical modeling is
database software specific, meaning that the objects defined during physical
modeling can vary depending on the relational database software being used.
Typical deliverables of
physical modeling include:
?
Server model diagrams
?
User feedback
documentation
?
Database design
documentation
Automated
Design Tools
Automated design tools
(formerly known as CASE tools)?are graphical user interface (GUI)
applications that are used to aid in the design of a database or database
application.
Automated design tools
are used to automate and expedite the task of designing a business system.
These automated tools
store information about business requirements and assist in the overall design
and generation of a business system.
Automated design (AD)
tools are vendor-specific.
All AD tools have one
goal in common - to assist the developer in assuring that a complete database
model or database application is designed in a timely
fashion.
Some of the most robust
features of many AD tools include the following:
?
The capability to capture
business and user needs
?
The capability to model
business processes
?
The capability to model the
flow of data in an organization
?
The capability to model
entities and their relationships
?
The capability to generate
DDL to create database objects
?
Full life cycle database
support
?
Business process
reengineering/reverse engineering
?
Database and application
version control
?
Generation of reports for
documentation and user-feedback sessions