Choosing a relational database management system that meets your requirements now and in the future is a major decision. Each database management system has its strengths and weaknesses and which is right for you depends upon the requirements of your database (such as number of records, transaction intensity, performance level, back up and security) which operating system you use, your budget, who will administer the database, and who will host the database server.
Data Modeling As Part of Database Design
ATS has expertise managing Oracle, Sybase and MySQL database management systems, among others.
The data model is one part of the conceptual design process. The other
is the function model. The data model focuses on what data should
be stored in the database while the function model deals with how the
data is processed. To put this in the context of the relational database,
the data model is used to design the relational tables. The functional
model is used to design the queries that will access and perform operations
on those tables.
Data modeling is preceeded by planning and analysis. The effort devoted
to this stage is proportional to the scope of the database. The planning
and analysis of a database intended to serve the needs of an enterprise
will require more effort than one intended to serve a small workgroup.
The information needed to build a data model is gathered during the requirments
analysis. Although not formally considered part of the data modeling stage
by some methodologies, in reality the requirements analysis and the ER
diagramming part of the data model are done at the same time.
The goals of the requirements analysis are:
- to determine the data requirements of the database in terms of primitive
- to classify and describe the information about these objects
- to identify and classify the relationships among the objects
- to determine the types of transactions that will be executed on the
database and the interactions between the data and the transactions
- to identify rules governing the integrity of the data
The modeler, or modelers, works with the end users of an organization
to determine the data requirements of the database. Information needed
for the requirements analysis can be gathered in several ways:
- review of existing documents - such documents include existing forms
and reports, written guidelines, job descriptions, personal narratives,
and memoranda. Paper documentation is a good way to become familiar
with the organization or activity you need to model.
- interviews with end users - these can be a combination of individual
or group meetings. Try to keep group sessions to under five or six people.
If possible, try to have everyone with the same function in one meeting.
Use a blackboard, flip charts, or overhead transparencies to record
information gathered from the interviews.
- review of existing automated systems - if the organization already
has an automated system, review the system design specifications and
The requirements analysis is usually done at the same time as the data
modeling. As information is collected, data objects are identified and
classified as either entities, attributes, or relationship; assigned names;
and, defined using terms familiar to the end-users. The objects are then
modeled and analysed using an ER diagram. The diagram can be reviewed
by the modeler and the end-users to determine its completeness and accuracy.
If the model is not correct, it is modified, which sometimes requires
additional information to be collected. The review and edit cycle continues
until the model is certified as correct.
Three points to keep in mind during the requirements analysis are:
- Talk to the end users about their data in "real-world" terms. Users
do not think in terms of entities, attributes, and relationships but
about the actual people, things, and activities they deal with daily.
- Take the time to learn the basics about the organization and its
activities that you want to model. Having an understanding about the
processes will make it easier to build the model.
- End-users typically think about and view data in different ways according
to their function within an organization. Therefore, it is important
to interview the largest number of people that time permits.
Steps In Building the Data Model
While ER model lists and defines the constructs required to build a data
model, there is no standard process for doing so. Some methodologies,
such as IDEFIX, specify a bottom-up development process were the model
is built in stages. Typically, the entities and relationships are modeled
first, followed by key attributes, and then the model is finished by adding
non-key attributes. Other experts argue that in practice, using a phased
approach is impractical because it requires too many meetings with the
end-users. The sequence used for this document are:
- Identification of data objects and relationships
- Drafting the initial ER diagram with entities and relationships
- Refining the ER diagram
- Add key attributes to the diagram
- Adding non-key attributes
- Diagramming Generalization Hierarchies
- Validating the model through normalization
- Adding business and integrity rules to the Model
In practice, model building is not a strict linear process. As noted
above, the requirements analysis and the draft of the initial ER diagram
often occur simultaneously. Refining and validating the diagram may uncover
problems or missing information which require more information gathering
Data modeling must be preceded by planning and analysis. Planning defines
the goals of the database , explains why the goals are important, and
sets out the path by which the goals will be reached. Analysis involves
determining the requirements of the database. This is typically done by
examining existing documentation and interviewing users.
An effective data model completely and accurately represents the data
requirements of the end users. It is simple enough to be understood by
the end user yet detailed enough to be used by a database designer to
build the database. The model eliminates redundant data, it is independent
of any hardware and software constraints, and can be adapted to changing
requirements with a minimum of effort.
Data modeling is a bottom up process. A basic model, representing entities
and relationships, is developed first. Then detail is added to the model
by including information about attributes and business rules.