7 Phases of
Database Design
- Define
a Mission Statement and Mission Objectives
The
Mission Statement
Conduct
an interview with the owner or manager or the organization.
Establish the purpose of the database. Start the process by
defining the end result. A well written mission statement is
free of any phrases or sentences that explicitly describe
specific tasks.
Ex. The
purpose of the ABC company database is to maintain the data we
use and provide the information we need to run the day-to-day
affairs of our business.
Sample questions from Pg 85 to help you arrive at your mission
statement.
-
How
would you describe the purpose of your organization to a new
client?
-
What
would you say is the purpose of your organization?
-
What
is the major function of your organization?
-
How
would you describe what your organization does?
-
Will
you define the single most important reason for the
existence of your organization?
-
What
is the main focus of your organization?
The Mission Objectives
Prepare statements that represent
the general tasks to be performed against the data collected in
the database. This determines the subjects that each of the
tables represent. The purpose of a mission objective is to help
define various structures within the database and to help guide
the overall direction of the database’s development. A mission
objective is a declarative sentence that clearly defines a
general task and is free from unnecessary details. If a mission
objective represents more than one general task, it should be
broken into two or more mission objectives. Examples of mission
objectives:
- We need to maintain complete
patient address information
- We need to keep track of all
customer sales
- We need to make sure an
account representative is responsible for no more that
twenty accounts at any given time.
- We need to keep track of
vehicle maintenance
- We need to produce employee
phone directories
Types
of questions to ask:
-
What kind of work do you perform on a daily basis?
- How would you define your
job description?
- What kind of data do you
work with?
-
What types of reports do you generate?
- What types of things do you
keep track of?
- What types of services does
your organization provide?
- How would you describe the
type of work that you do?
- Analyze
the Current Database
- Review the way data is
currently collected and presented.
- Conduct interviews with users
and management to identify how they interact with the
database on a daily basis.
- Prepare a list of fields and
calculations using the information from the analysis and the
interviews.
- Create
the Data Structures
-
Define tables by identifying the
subjects that will be tracked by the database.
-
Assign each table, fields that best characterize its
subject.
-
Establish Primary Key
- Define field specifications
for every field (conduct interviews with users and
management to help identify any specific field
characteristics that may be important to them.
- Modify multi-valued fields so
that each field stores only a single value
- Determine
and Establish Table Relationships
-
Conduct interviews with users and management to identify
relationships, relationship characteristics and establish
relationship level integrity.
- Once relationships are identified, establish the logical
connection for each relationship. Depending on the type of
relationship, use Primary Keys and Foreign Keys or Linking
Tables to connect the tables.
-
Determine the type of participation for each relationship.
-
Determine the degree of participation for each relationship.
-
The type and degree could be obvious or based on specific
business rules.
- Determine
and Define Business Rules
-
Interview users and management to determine specific
limitations and requirements that will be imposed on the
data, data structures, or relationships.
- These specifications or constraints will become the
business rules that serve to establish various levels of
data integrity.
- Determine
and Establish Views
-
Interview users and management to identify
the different ways they look at the data in the database and
develop them as views.
- The views are the queries performed on the data, whether
it’s from one or more tables and fields.
- Review
Data Integrity
-
Review each table and its fields to ensure that it
meets the criteria of a properly designed table and field.
- Review and check field specifications for each field and
check field level integrity.
-
Review the validity of each relationship, confirming the
type of relationship as well as the type or participation
and degree of participation for each table within the
relationship.
- Go over the Business Rules to confirm that the limitations
and/or requirements have been placed on the database.
Ch
5. Conducting Interviews
- Prepare a list of questions to provide a focus and direction
for the interview. Ask open-ended questions.
-
Set up guidelines for interviews with users and management.
- Set a limit of six people or less for each interview.
- Conduct separate interviews for users and management. If
both are required then do so with a specific purpose in
mind.
- If you have several groups to interview then designate a
group leader for each group.
-
Prior to the actual interview, let the participants know
what you’re going to discuss and how the interview will
be conducted.
- Make sure that participants understand that you
appreciate their input to the overall design project.
- Conduct the interview in a well lit room, separated from
noise, with a large table and comfortable chairs.
- Take notes, record the interview or assign someone to do
so.
- Give everyone your equal undivided attention.
- Make sure everyone understands that you are the official
arbitrator if a dispute arises.
- Keep the pace of the interview moving.
- Always maintain control of the interview.
You need to answer the following questions:
What
types of data does the organization use?
How does it use that data?
How does it manage and maintain that data?
The best
way to answer these questions is to analyze the current
database. The goal of the analysis is to determine the types of
data the organization uses and how the data is managed and
maintained. It also serves to identify how the data is viewed
and used. The analysis requires reviewing the ways data is
collected, presented and conducting interviews with users and
management. The information gathered is used to define a
preliminary field list and help determine the tables that should
be included in the initial database structure. One significant
point to remember is not to adopt the current database structure
as the basis for the new database structure. The following are
two types of databases that are typically analyzed during this
part of the database design process.
Data
that is collected, stored, and maintained on paper. The only
reason to analyze this type of database is to identify any
items such as individual pieces of data extracted from forms
that will be turned into fields when incorporated into the new
database.
A legacy
database is any database in existence and in use for five
years or more. (mainframe)
There
are three steps in the analysis process:
-
Reviewing
how data is collected
-
Reviewing
the manner in which information is presented
-
Conducting
interviews with users and management
The
second step in the analysis process is to review any methods
currently used to present information needed by the
organization. This includes:
Review
and identify all the reports generated from the database.
Gather a sample of each one and keep in a separate folder as
in the previous step.
Now that
you have a general idea of how the organization collects and
presents its data, it’s time to interview users and
management to determine how the organization uses its data.
Interviews are useful for the following reasons:
-
They
provide details about the samples you assembled in
reviewing how data is collected and how information is
presented.
-
They
provide information on the way the organization uses its
data.
-
They
are instrumental in defining preliminary field and table
structures.
-
They help to define future information requirements.
Conducting
interviews is a two-part process: the first part involves
speaking with users, and the second with management. Use both
open-ended and closed questions throughout the interview. The
open ended are used to focus on specific subjects and the
closed questions are used to obtain specific details on a
certain subject. Ex. of an open-ended question:
How
would you define the work that you do on a daily basis?
As you
ask each open-ended question, write down the response and
identify any subjects suggested within the response. You can
identify subjects by looking for nouns within the sentences
from the response. Subjects are always represented by nouns
and identify an object (such as a person, place, or thing) or
an event (something that occurs at a given point in time).
Make sure you only look for nouns that specifically represent
an object or event. To ensure that you account for every
subject you need to discuss, mark the nouns with a double
underline as you identify them. Ex. As an account
representative, I’m responsible for ten clients.
After you’ve identified all of the appropriate nouns within
the response, list them on a sheet of paper, this is your list
of subjects or entities. This procedure is known as the
subject identification technique.
Once you’ve
identified the subjects suggested within the response, pick a
particular subject and begin to ask follow-up questions
related to it. The purpose of this line of questioning is to
obtain as much detailed information as possible about the
subject you’ve selected. Once you’ve finished, begin
looking for any details regarding the subject under
discussion. Your objective here it to obtain as many facts
about the subject as possible. Now you’re interested in the
nouns that represent characteristics of the subject – they
describe a particular aspect of the subject. These nouns are
easy to identify within a sentence because they are typically
in singular form. Example Phone Number, Address.
As you identify nouns that represent characteristics within
the response, mark each one with a single underline. As you
identify nouns within the response, list them on a sheet of
paper, this becomes your list of characteristics. Later you
will use this list when you are determining the fields for the
database. Use a separate sheet of paper for the list of
characteristics. Do not list subjects and characteristics on
the same sheet. Now you have a list of characteristics for the
subject under discussion. These characteristics will
subsequently become fields in the database. After you’ve
finished discussing a particular subject, move on to the next
subject on your subjects list and begin the same pattern of
questioning. Start with open-ended questions, identify the
subjects suggested in the responses, ask more specific
questions as the discussion progresses, and identify as many
of the subject’s characteristics as possible. Continue this
process in an orderly manner until you’ve discussed every
subject on your list.
The
first part of the interview process involves conducting user
interviews. These interviews will focus on the following four
issues:
-
The
types of data users are currently using.
-
How
users are currently using their data.
-
The
data-collection sample, report samples, and on-screen
presentation samples
-
The
types of information users need in conjunction with their
daily work.
Your
objective for this part of the interview is to identify the
types of data the users are currently using and how they use
that data in support of the work they do. You’ll use this
information later in the design process to help define field
and table structures. Ask each participant about the work he
or she performs on a daily basis. Have the participant walk
you through the job he/she performs on a daily basis. Start
the discussion with an open-ended question. After all
characteristics have been identified, move on to the next
subject and begin the entire process again.
This
concerns the data-collection samples, report samples, and
on-screen presentation samples. Your objectives are to
identify how the objects represented by the samples are
used, to clarify any aspects of the samples you don’t
understand, and to assign a description to each sample.
Discuss each sample with the participants until the
purpose and use is clearly identified. Based on the reply,
develop a description of each sample. The description
should be succinct yet give a clear indication of the
purpose of the sample and how it is used.
The
final issue to discuss with users concerns their
information requirements. The objectives are to determine
whether individual users receive information based on data
they don’t directly control or maintain, to determine
what types of additional information they need, and to
determine what types of information they can foresee
themselves needing in the future. This information will be
used later to define and verify field and table
structures.
Review
the report samples with the users to find out what data is
used to produce the reports. Determine the origin of the
data so you can identify all the data used by a user.
The
objective is to determine whether the users require
additional information that is not being delivered to them
currently. Have the participants review the reports they
currently receive and ask them if there is any other
information they would like to see in the reports. Then
determine whether the new information represents new
subjects or new characteristics, if it does then identify
each new item and add it to the appropriate list. Have the
participant give a brief statement indicating why the
information is necessary. Determine whether any new
subjects or characteristics are represented in the
additional information. Examine each report and apply the
subject identification technique and the characteristics
identification technique to the comments given by the
participants.
Your
objective is to identify any information that the
participants believe will be necessary for them to receive
as the organization grows.
The
second part of the interview process involves interviewing
management personnel. These interviews will focus on the
following issues:
-
The
types of information managers currently receive.
-
The
types of additional information they need to receive.
-
The
types of information they foresee themselves needing.
-
Their
perception of the business’s overall information
requirements.
Your
objectives are to identify the information that management
receives and determine whether they currently receive any
reports that are not represented in the group of report
samples. Ask each participant about the work they perform and
the responsibilities associated with their position. Ask the
participants if they use any of the report samples you have
and to identify any other subjects on the reports that might
have been overlooked. Ask each participant if they receive any
reports that are not represented in your samples and obtain a
sample. Review the sample with each participant and use the
subject and characteristics identification techniques to
identify the subjects represented by the new reports and their
associated characteristics. Then add the subjects and
characteristics to their respective lists. Attach a
description to each new report and add the new report to your
collection of report samples. Repeat this procedure until you’ve
accounted for every new report.
The
objective is to determine if there is any information that is
needed that is currently missing from the reports they
receive. Steps to follow:
-
Review
the report samples with the participants and ask if there
is any additional information they would like to include
in any of the reports.
-
Have
them note additional information including the reasons why
they believe it’s necessary on the appropriate reports.
Make sure they are attached to the applicable report.
-
Determine
if the information represents new subjects or new
characteristics. If it does, identify each new item and
add it to the appropriate list.
-
Review
the reports and discuss any concerns you have about them
with the participants.
Your
objective is to determine what information management foresees
itself needing in the future.
Review
all new reports and how the information contained in them will
be used. Discuss whether there are any other informational
requirements for the organization.
Now
that you have completed your analysis of the current
database and the interviews with users and management, you
can create a preliminary field list. This list represents
the fundamental data requirements of the organization and
constitutes the core set of fields that will be defined in
the database. You’ll create the preliminary field list
using the following two-step process:
-
Review
and refine the list of characteristics you have
completed.
-
Determine
whether there are any characteristics in the data
collection samples, report samples, and on-screen
presentation samples that need to be added to the
preliminary field list.
The
first step is to review and refine the list of
characteristics you compiled in the analysis and interview
process. Review the list and identify and remove duplicate
characteristics. Look for items in the list with the same
name. When you find one or more duplicates, determine
whether the duplicates represent the same characteristic. If
they do, remove them from the list. Now look for items that
have different names but the same meaning and remove all but
one. There should be a single occurrence of a particular
characteristic. Make sure that each item on your list
represents a characteristic. You can test each item by
asking yourself questions like these:
Can
this word be used to describe something?
Does
this word represent a component or detail or piece of
something in particular?
Does
this word represent a collection of things?
Does
this word represent something that can be broken down into
smaller pieces?
If you
find that an item represents a subject rather than a
characteristic, remove it from the list of characteristics
and add it to the list of subjects. Be sure to identify the
new subjects characteristics and add them to your list of
characteristics.
The
second step involves studying the data-collection samples,
report samples, and on-screen presentation samples you
gathered during the analysis of the current database. Your
objective is to determine whether there are any
characteristics found on the data and report samples that
need to be added to the preliminary field list. To begin
this step, highlight every characteristic you find on each
sample. Then take each characteristic and determine whether
it’s already on the preliminary field list; if it’s on
the list, cross it out on the sample. Next look at the
remaining characteristics and determine whether any of them
has the same meaning as an existing field; if it does, cross
it out on the sample. Finally if there are any highlighted
characteristics left on the samples, add them to the
Preliminary field list.
One
final refinement you need to make to the preliminary field
list before it can be considered complete: every calculated
field must be removed and placed on a separate list. This
new list becomes your calculated field list. A calculated
field is one that stores the result of a mathematical
calculation as its value.
Conduct
brief interviews with users and management to review the
items that appear on the preliminary field list and the
calculated field list. The purpose is to determine whether
there are any fields that have been left out of either list.
Be sure to conduct interviews at this time; the feedback
participants provide is a means of verifying the fields on
both lists.
Each table
is composed of fields which represent the elements that define
or describe the subject of the table.
The
preliminary table list will be used to identify and establish
the tables for the new database. Three procedures are used to
develop this list. The first involves using the preliminary
field list. The second involves using the list of subjects and
the third using the mission objectives.
The
process of defining the tables for the database begins with a
review of the preliminary field list with the objective of
determining what subjects are implied by the items on the
list.
Next,
create a second version of the preliminary table list by
merging the list of subjects (created during the interviews
with users and management) with the first version of the
preliminary table list (compiled by studying the preliminary
field list). Merging the two lists is a three-step process,
which involves resolving duplicate items, items that represent
the same subject and combining the remaining items together
into one list.
Use the
mission objectives to determine whether you have overlooked
any subjects during the previous two procedures. Start with
the first mission objective, and use the subject
identification technique to identify the subjects represented
in that statement. Underline each subject you identify and
then cross-check it against the items on the preliminary table
list. Use the same techniques here that you used in the
previous procedure:
-
If
the item you underlined in a mission objective statement
already appears on the preliminary table list, determine
whether the items represent different subjects. If they
do, assign an appropriate name to each occurrence, and add
them to the preliminary table list. If they are truly
duplicates, cross out the duplicate item on the mission
objective.
-
If
the item underlined in the mission objective statement has
a name that is synonymous with the name of an item on the
preliminary table list and both items represent the same
subject, select the name that best identifies that subject
and use it in the preliminary table list.
-
If the item underlined in the mission objective statement
represents a new subject, add it to the preliminary list.
Repeat
these steps until you’ve worked through all the mission
objectives.
Your
preliminary table list is as complete as it can be. Now you’ll
transform it into a final table list. To do this you’ll need
to add two elements that are not currently on the preliminary
table list: Table Type and Table Description.
We
classify tables by “type” so that we can identify tables
that function in a similar manner. There are four Table Types:
-
Data – This type of table stores data used to supply
information and represents a subject that is important to
the organization.
-
Linking – this type of table is used to establish a link
between two tables in a many-to-many
relationship.
-
Subset
– This type of table contains supplemental fields that are
related to a particular data table and further describe the
subject of that table in a very specific manner.
-
Validation
– This type of table is used to implement data integrity.
The Table
Description is used to provide a clear definition of the subject
represented by the table and to state why the subject is
important to the organization.
Use
these guidelines to refine each table name on the preliminary
table list. This will become your final table list.
Indicate
each table’s type on the final table list. The four
classifications to use are: data, linking, subset, and
validation. When you first create your final table list,
every item on the list is a data table because it represents
a subject that is important to the organization and stores
data that is used to produce information.
Table
descriptions are crucial so everyone understands why each
table exists and why the organization is concerned with
collecting the data for each table. The description must
explicitly define the table and state its importance to the
organization. Both the definition and the explanation of the
table’s importance must be in the description.
In
order to define a good table description for each table,
get the help of users and management to establish the
table’s definition and importance. Conduct the
interviews with both groups at the same time. Your
objective it to get a consensus regarding the description
of each table. Take your notes and compose the
descriptions yourself, then confer with both parties to
make certain that the descriptions are acceptable and
understood by all. When you finish, the final table list
is complete.
In the
next stage of the database design process, you assign fields
to each table on the final table list. The fields you use
are taken from the preliminary field list. Determine which
fields best represent characteristics of the table’s
subject and assign them to that table. If you believe that a
field or set of fields can be used to represent
characteristics of more than one table, then assign them
accordingly. To begin this process take a sheet of legal
paper and lay it in front of you lengthwise from left to
right. Write the name of each table (from the final table
list) across the top of the paper, starting at the left-hand
side; leave enough space between the table names to allow
room for lengthy field names to be listed underneath. Next
assign fields from the preliminary field list to each table.
Start with the first table and determine which fields best
describe or define its subject, then list those fields under
the table name.
Refine
the fields by improving the field names and resolve any
problems that exist with them.
The
best way to identify potentially troublesome fields is to
determine whether they are in accordance with the Elements
of the Ideal Field.
Multipart
fields are difficult to work with because they contain
more than one item of data. To resolve a multipart field,
you need only identify the separate items making up the
field and treat each one as an individual field.
Unlike
a multipart field, whose value represents two or more
separate items, a multi-valued field represents two or
more occurrences of the same value.
To
resolve a multi-valued field, first remove the field from
the table and use it as the basis for a new table. Next
use a field (or set of fields) from the original table to
link the original table with the new table; the connecting
field(s) will appear in both tables. Then assign an
appropriate name, type, and description to the new table
and add the table to the final table list.
Your
objective in this phase is to make sure that the appropriate
fields have been assigned to each table and that each tables
structure is properly defined. This will also reveal whether
any of the tables have anomalies that need to be resolved.
Redundant
data is a value that is repeated in a field as a result of
the fields’ use as a link between two tables, or is the
result of some field or table anomaly.
Duplicate
fields are fields that appear in two or more tables for
any of the following reasons: they are used to link a set
of tables together, they indicate multiple occurrences of
a particular type of value, or they are there as a result
of a perceived need for supplemental information. The only
instance in which duplicate fields are necessary is in the
case of linking two tables together; using duplicate
fields is the only way to associate records from the first
table with records from the second table. In any other
instance, duplicate fields are unnecessary and should be
avoided primarily because they introduce needless,
redundant data.
You
can identify a potentially problematic table structure by
determining whether it is in accordance with the
characteristics of the Ideal Table.
Purge
the database of any unnecessary duplicate fields.
If a
subset table is required, each subset table contains only
those fields that describe a specific version of the subject
represented by the main table. A field from the main table
is also included in order to connect the main table together
with the subset table. After you’ve created the subset
tables, compose a suitable description for each subset table
and add the subset tables to the final table list. Indicate
“Subset” under the Type column on the list.
When
you identify subset tables, you can refine them by
following these steps:
-
Remove all the fields that the subset tables have in
common and place them into a new table; this becomes
your main table.
-
Identify the subject represented by the new main
table; give the table an appropriate name.
-
Make
certain that the subset tables define a specific
version of the subject represented by the main table.
-
Compose a suitable description for the main table and
add that table to the final table list. Indicate the
table type as “Data”.
-
Why
Keys are Important
-
They
ensure that each record in a table can be properly
identified.
-
They
help establish and enforce various types of integrity such
as table level integrity and relationship level integrity.
-
They
are used to establish table relationships.
The next
task is to establish keys for each table in the database. The
function of a key within a table is determined by the key type.
There are four main types of keys: Candidate, Primary, Foreign,
and Non-keys.
The
first type of key you’ll establish for a table is the
Candidate key. Each table must have at least one Candidate
key. It is from the pool of available Candidate keys that a
Primary key for the table is drawn. A Candidate key is a field
or set of fields that uniquely identifies a single instance of
the subject represented by the table. A Candidate key must
conform to all of the following elements:
-
Elements
of a Candidate Key
-
It must uniquely identify each record in the table.
-
It
must contain unique values.
-
It
cannot be null. A null value represents the absence of
a value.
-
It
cannot be a multipart field.
-
It
comprises a minimum number of fields necessary to
define uniqueness. You can use a combination of fields
(viewed as a single unit) to serve as a Candidate key
so long as each field contributes to defining a unique
value.
-
Its
value is not optional in whole or in part.
-
It
must directly identify the value of each field in the
table.
-
Its
value can only be modified in rare or extreme cases.
Establishing
Candidate keys for a table is simple, look for a field or
set of fields that conforms to all the elements of a
Candidate key. It’s possible to define more than one for
a given table. In many cases it’s a good idea to load a
table with sample data as a means of identifying fields
that qualify as Candidate keys. To identify Candidate keys
in your table structures, write the letters “CK” next
to the name of each field you identify as a Candidate key.
If you have a Candidate key composed of two or more
fields, it’s termed a Composite Candidate key.
If
none of the fields in a table, either singularly or as a
set, qualifies as a Candidate key, you can use an Artificial
Candidate key by creating one that conforms to the elements
of a Candidate key and adding it to the table. Once you’ve
established an Artificial Candidate key for your table mark
that field name with a “CK” in the table structure.
There is one other instance in which you may choose to
create an Artificial Candidate key: when the new field you
add would be a stronger, and thus more appropriate,
Candidate key that any of those fields that currently exist
in a particular table.
A
Primary key is the key that officially identifies the table
throughout the database. The Primary key is used to enforce
table-level integrity, to help establish relationships with
other tables, and to accurately identify and refer to a
particular record with the table. Guidelines you can use to
select an appropriate Primary key:
-
If you have a “simple” (single field) Candidate key
and a Composite Candidate key, choose the “simple”
Candidate key.
-
Choose
the Candidate key that uses a field that incorporates part
of the table name within its name. Choose the one that
identifies the subject of the table. The Candidate key
becomes the Primary key.
Now that
the fundamental table definitions are complete, you’ll need to
conduct interviews with users and management to review the work
you’ve done so far. During these interviews you will:
-
Ensure
that the appropriate subjects are represented in the
database.
-
Make certain that the table names and table descriptions are
suitable and meaningful to everyone.
-
Make
certain that the field names are suitable and meaningful to
everyone.
-
Verify that all the appropriate fields are assigned to each
table.
Fields
represent characteristics of the subjects that are important to
the organization. They store the data that is retrieved and then
presented as information. Data integrity is established for the
database by defining Field Specifications for each field in the
database.
-
Why
Field Specifications are Important
-
Field
level integrity is established and enforced as a result of
defining Field Specifications.
-
Defining Field Specifications for each field enhances
overall data integrity.
-
Defining
Field Specifications compels you to acquire a complete
understanding of the nature and purpose of the data in the
database.
-
Field
Specifications are valuable when you implement the dataset
in an RDBMS program.
-
Field-Level
Integrity
A field has field-level integrity after a full set of
Field Specifications has been defined for the field. Field
Specifications help to warrant that:
-
The
identity and purpose of each field is clear, and that all
of the tables in which it appears are properly identified;
-
Field
definitions are consistent throughout the database;
-
The
values of the field are consistent and valid;
-
The
types of modifications, comparisons, and operations that
can be applied to the values in the field are clearly
identified.
-
Anatomy of a Field Specification
Field Specifications comprise a number of elements
that are used to define every attribute of a field. These
elements are divided into three categories: General Elements,
Physical Elements, Logical Elements, and Specification
Information. The items found within each category are:
-
General
Elements
Provide information on the purpose of the field,
the name of the table(s) in which the field appears, and
the pseudonyms the field may assume under certain
circumstances.
-
Field
Name – Is the unique identifier for the field
itself.
-
Label – is an alternate name for the field
that may be used to identify the field in an RDBMS
program.
-
Parent
Table – A field represents a characteristic of a
particular table’s subject. The table that
represents this subject is referred to as the parent
table of the field, and it is the only table in which
the field will appear. (A field can appear in another
table under the following circumstances: when the
field is used to connect the parent table to a subset
table, and when the field is used to help establish a
relationship between the parent table and another
table.)
-
Shared
By – This element is used to list the names of
other tables that share this field.
-
Alias(es)
– An alias is a name that a field assumes under
very rare circumstances.
-
Description
– In the description you provide a complete
interpretation of the field. The single most important
benefit derived from composing a field description is
that it forces you (and everyone in the organization)
to think carefully about the nature of the data that
will be stored in the field.
-
Guidelines
for Composing a Field Description
-
Use
a statement that accurately identifies the field
and clearly states its purpose.
-
Write
a statement that is clear and succinct.
-
Refrain
from restating or rephrasing the field name.
-
Avoid
using technical jargon, acronyms, or
abbreviations.
-
Do
not include implementation-specific information.
-
Do
not make this description statement dependent on
the description of another field.
-
Do
not use examples.
-
Physical
Elements
The Physical Elements category pertains to the
structure of a field.
-
Data
Type – The most common are:
-
Alphanumeric – stores combinations of letters,
numbers, extended characters, or special
characters.
-
Numeric
– stores numbers only; both whole numbers and
real numbers are permitted.
-
Date
– stores any valid date.
-
Time
– stores any valid time.
-
Character
Support
This element is used to indicate the characters
that are permitted to be entered into the field. As a
result, field level integrity is enhanced.
-
Length
– The total number of characters that can be
entered into a particular field is indicated by
this element.
-
Decimal
Places – The number of digits to the right
of the decimal point is indicated by this element.
-
Input
Mask – This element is used to indicate the
manner in which the data should be entered into
the field. Using an input mask will ensure that
values are correctly entered into a field.
-
Display
Format – This element allows you to indicate
how the value of the field should be presented.
-
Logical Elements
This category of elements pertains to the values
of the field. These elements indicate whether the values
should be unique, when they should be entered, whether
they can be edited, the types of comparisons and
operations that can be performed on the values, and the
range of acceptable values that can be entered into the
field. Setting these elements help enforce field level
integrity.
-
Type
of Key – In the Type of Key element you indicate
the role of the values within the field.
-
Uniqueness – This element determines whether
the values of a field should be unique. In the case of
a Primary key field, this element will be set to “Unique”.
Otherwise, this element is commonly set to “Non-Unique”.
-
Required
Value – Whether a user must enter a value into a
given field is indicated by this element. The Required
Value item is typically set to “No” for most of
the fields in the table. However, in the case of a
Primary key field, it must be set to “Yes”. You
may also need to set Required Value to “Yes” for a
field such as CustZip-Code, because a zip code is
required for accurate Post Office handling.
-
Null Support – “Null” represents a
missing or unknown value. The Null Support element
indicates whether null values should be allowed in the
field. Null Support is set to “No Nulls” for a
Primary key field and an Alternate key field. Null
Support must be set to “No Nulls” whenever the
Required Value element of the field is set to “Yes”.
Null Support can be set to “Nulls Allowed” for a
field such as Cust-County.
-
Edit
Rule – This element indicates at what point in
time a value must be entered into the field and
whether that value can be modified. There are four
settings for Edit Rule, and only one may be chosen at
a time.
-
Enter
Now, Edits Allowed
-
Enter
Later, Edit Allowed
-
Enter Now, Edits Not Allowed
-
Enter
Later, Edits Not Allowed
-
Comparisons
Allowed – The comparisons that can be made to a
particular value of this field are indicated by the
Comparisons Allowed element.
-
Operations Allowed – This element indicates
the types of operations that can be performed on the
values in the field. Four types of operation are
allowed: addition, subtraction, multiplication and
division.
-
Values
Entered By – This element indicates how values
are entered into a field. Either the user will enter
each value manually or the values will be entered
automatically by the database application, also known
simply as the “system”.
-
Default
Value – A default value is a value that is used
when an entry is required but not yet available, and
when Nulls are not allowed.
-
Range of Values – The Range of Values element
lets you determine every possible value that can be
entered into a field. A range of values can be
established under three categories:
-
General
– The complete collection of every possible
value for this field.
-
Integrity specific – The collection of
values that is based on the field’s role in
establishing a table relationship.
-
Business
specific - The collection of values that is
the result of a particular business requirement.
-
Specification Information
The elements under this category pertain to the
nature of the field specification as a whole.
-
Specification
Type – A Field Specification falls into one of
three categories:
-
Unique
-
Generic – The only requirements for
creating a generic Field Specification are that
you use a nonspecific field name and that the
settings for all of the elements be as broad and
general as possible.
-
Replica
-
Based on Existing Specification – This
element indicates whether any of the elements in this
specification have drawn their settings from another
field specification. Whenever Specification Type is
set to “Replica,” the Based on Existing
Specification element is set to “Yes”.
-
Source
Specification – This element indicates the name
of the generic Field Specification upon which the
current specification is based.
-
Defining
Field Specifications for Each Field in the Database
First you should establish as many specifications
as you can, and then you should work with the staff on the
remaining specifications and refining them.
A
relationship exists when the tables are connected by a Primary
key and a Foreign key, or are linked together by a linking
table. A relationship is a crucial part of the database because:
A
relationship establishes a connection between a pair of tables
that are logically related to each other in some form or
manner.
It helps
to further refine table structures and minimize redundant
data.
It is
the mechanism that allows data from multiple tables to be
drawn together simultaneously.
Relationship
level integrity is established when a relationship is properly
defined.
Establishing
relationships always involves a three-step procedure:
identifying the relationships that currently exist between
the tables in the database, establishing each relationship
in the appropriate manner, and then setting the proper
characteristics for each relationship.
-
Identifying Existing Relationships
In the first step of the procedure, you’ll identify
the relationships that currently exist between the tables.
Begin this step by taking a particular table, you can choose a
table at random because you’ll repeat this process for every
table anyway – and determine whether it has a relationship
with any of the remaining tables. Assume your working with
these tables:
BUILDINGS
ROOMS CLASSES
STAFF FACULTY
STUDENTS
Say
you’ve decided to start with the CLASSES table. Make up a
two-column list, writing “Classes” in the first column and
writing the names of the remaining tables in the second column.
Now determine whether the CLASSES table bears a relationship with
any of the other tables in the list by using the process of
elimination.
Classes Buildings
Classes Faculty
Classes Rooms
Classes Staff
Classes Students
You’re
only looking for direct relationships. You want to determine the
relationship between a single record in one table to one or more
records in the other table, and vice versa. (Remember that each
record represents a single instance of the subject represented
by the table.) There are two types of questions you can ask:
-
Associative
– General in nature, this type of question can be stated
as follows: “Can a single record in (name of first table)
be associated with one or more records in (name of second
table)?” Ex. Can a single record in CLASSES be associated
with one or more records in BUILDINGS?
-
Contextual
– This type of question contrasts a single instance of the
subject represented by the first table against multiple
instances of the subject represented by the second table.
There are two categories within this type of question:
ownership oriented and action oriented.
Ownership oriented questions are characterized by the use of
words or phrases such as owns, has, is part of, and
contains.
Action oriented questions use action verbs such as make,
visit, place, teach, and attend.
Use the type of question you believe to be the most
appropriate for the pair of tables you’re working with.
Always diagram one-to-many relationships from left to right.
Also note that each table’s Primary key has been added to
the diagram. From now on, make certain that each time you
diagram a table you include its Primary key. Some pairs of
tables will have a direct relationship; other will not. You
want to identify only direct relationships.
Whenever
you establish many-to-many relationships between tables, check
each table within the relationship to determine whether there
are any fields that should be transferred to the linking
table. When in doubt, load all the tables with sample data;
you should be able to see immediately which fields should be
transferred to the linking table.
-
Reviewing the Structure of Each Table
You want to make certain that each table conforms to
the Elements of the Ideal Table:
-
Refining
all Foreign Keys
As you’ve seen, a Primary key from one particular
table becomes a Foreign key in another table when that field
is used to establish a relationship between those two tables.
A Foreign key must conform to a set of elements, just as all
the other keys you’ve worked with so far.
-
Establishing
Relationship Characteristics
The final step in this procedure is to establish the
characteristics of each relationship. These characteristics
indicate what will occur when a record is deleted, the type of
participation each table bears within the relationship, and to
what degree each table participates in the relationship.
Use a
restrict deletion rule as a matter of course; use a cascade
deletion rule very judiciously. Select a pair of related
tables and pose the following question:
“If
a record in [name of main or “one” table] is deleted,
should related records in [name of subordinate or “many”
table] be deleted as well?”
If
the answer to this question is no, you must use a restrict
deletion rule for this relationship; otherwise, the cascade
deletion rule is appropriate.
The
answer to this question greatly depends on how the data is
being used within the database. If you cannot easily provide
an answer, make note of the relationship and continue with the
next table relationship. You’ll revisit these relationships
when you establish Business Rules for the database.
After
you’ve identified the type of deletion rule you want to use
for a relationship, add it to the relationship diagram.
Restrict deletion rules are indicated by an “( R )” and
cascade deletion rules by a “( C )”. Place the designation
under the connection line of the main table or the table on
the “one” side of the relationship. The deletion rule only
applies to a record in the main table or the “one” side of
the relationship. There is no need to worry about deleting
records in the subordinate table or the “many” side of the
relationship because there can be no adverse affects as a
result of doing so.
-
Identifying
the Type of Participation for Each Table
Each table participates within a relationship in a
particular manner. A table’s type of participation
determines whether a record must exist in that table before
a record can be entered into the other table. There are two
types of participation:
-
Mandatory
– There must be at least one record in this
table before you can enter any records into the other
table.
-
Optional
– There is no requirement for any records to
exist in this table before you can enter any records
into the other table.
The
type of participation for most tables is usually determined
later when you’re defining Business Rules. However, it’s
common to establish the type of participation for tables in
relationships where the type of participation for each table
is obvious, is a result of common sense, or is in accordance
with some particular set of standards.
-
Identifying
the Degree of Participation for Each Table
Now you must determine the degree to which each
table will participate within the relationship. This is a
simple matter of identifying the total number of records in
one table that can be related to a single record in the
other table. The degree of participation is symbolized by
two numbers, separated by a comma, and enclosed in
parentheses, such as “(1,8)”. The numbers in this symbol
represent the lower and upper boundaries. When you add this
symbol to a relationship diagram, place it over the
connection line of the appropriate table. Unlimited degree
of participation is represented by the letter N inside the
parenthesis “(0,N)”.
-
Relationship
Level Integrity
Relationship level integrity is a direct result of
properly establishing a table relationship and defining its
characteristics in the proper manner. In establishing
relationship level integrity, you have:
-
Made
certain that the connection between two tables in a
relationship is sound. You have accomplished this by
using a Primary key and a Foreign key to establish a
one-to-one and one-to-many relationship, and a linking
table to establish a many-to-many relationship.
-
Ensured your ability to insert new records into each
table in a meaningful manner. You have made this
possible by properly identifying the type of
participation for each table within the relationship.
-
Ensured
your ability to delete an existing record without
creating adverse affects. This is guaranteed by the
deletion rule defined for the relationship.
-
Established a meaningful limit to the number of records
that can be interrelated within the relationship. You
have made this possible by properly identifying the
degree of participation for each table within the
relationship.
Relationship
level integrity is the third component of overall data
integrity, table level integrity is the first and field
level integrity is the second.
-
What
are Business Rules
A Business Rule is a statement that imposes some form
of constraint on elements within a field specification for a
particular field or on characteristics of a relationship
between a specific pair of tables. Business Rules influence
the selection of data, the construction of relationships, and
the structure of the reports that the database can produce, as
well as issues of security and confidentiality. The following
statement is an example of a typical Business Rule:
“A
Ship Date cannot be prior to an Order Date for any given order”.
This
statement or Business Rule, imposes a constraint on the Range of
Values element of the field specifications for a Ship Date
field. Business Rules depend on the manner in which an
organization perceives and uses its data, the way it functions
or conducts its business.
-
Types
of Business Rules
There are two major types of Business Rules:
database oriented and application oriented. Business Rules
impose some form of constraint and help enforce and maintain
overall data integrity.
-
Database-oriented
Business Rules are those that impose constraints that
can be established within the logical design of the
database.
-
Application-oriented Business Rules are statements that
impose constraints that cannot be established by
modifying a Field Specification or relationship diagram;
they must be established within the physical design of
the database or within the design of a database
application.
-
Categories
of Business Rules
-
Defining
and Establishing Business Rules
Business Rules must be based on the manner in which
your organization perceives and uses its data, which depends
on the way the organization functions or conducts its
business. The best approach to this task is to define and
establish the field-specific Business Rules first, followed
by the relationship-specific Business Rules.
-
Working
with Users and Management
Work with a representative group of users and
management. Work together to define and establish the
Business Rules for the database.
-
Defining and Establishing Field Specific Business
Rules
-
Step
1. Select a Table – Think about the subject
the table represents, then pose these questions:
-
Step
2. Review each field and determine whether there are
any constraints that should be imposed on it. – Pose
this question:
-
Step 3. Define the necessary Business Rules for
the field.
-
Step 4. Establish the rules by modifying the
appropriate Field Specification elements.
-
Step
5. Determine what actions test the rule – The
constraint imposed by a Business Rule is tested when
a user tries to perform one of three actions:
inserting a record into the table or an entry into a
field, deleting a record from the table or a value
within a field, or updating a field’s value.
-
Step 6. Record the rule on the Business Rule
Specification sheet. – The Business Rule
specification sheet provides three advantages:
-
It allows you to document every
database-oriented Business Rule.
-
It allows you to document every
application-oriented Business Rule.
-
It provides a standard method for recording all
Business Rules.
-
The
Business Rule Specification sheet contains the
following items:
-
Statement – This is the text of the
Business Rule itself. It should be clear yet
succinct and should convey the required
constraints without any confusion or ambiguity.
-
Constraint
- This is a brief explanation of how
the constraint applies to the tables and fields.
-
Type
– The type of rule you are defining is
indicated here. The options are field-specific
and relationship-specific.
-
Tested On – Tested when a user tries to
perform one of three actions: inserting,
deleting or updating a record or field.
-
Structures Affected – Here is where you
indicate the name of the field affected by the
Business Rule or the names of the tables
involved in the relationship affected by the
rule.
-
Field
Elements Affected – Indicate the elements
affected by the constraint.
-
Relationship
Characteristics Affected
-
Action
Taken – Here you indicate the
modifications you’ve made to the elements of a
Field Specification or to a relationship
diagram.
-
Defining
and Establishing Relationship Specific Business Rules
-
Step
1. Select a pair of tables that share a
relationship. – Think about what the tables
represent and why they are related and pose the
following questions:
“What kind of information do these tables provide?”
“Why is the relationship between these two tables
important?”
-
Step 2. Review each relationship characteristic
and determine whether a constraint is warranted by
the way the organization functions or conducts its
business.
-
Step 3. Define the necessary Business Rule.
-
Step 4. Establish the rule by modifying the
relationship characteristic.
-
Step 5. Determine what actions will test the
rule. – You can determine this by identifying
when a violation of the rule is most likely to
occur. Use the following questions to help you make
your decision:
“ Are there circumstances under which this rule
will be violated if I enter a new record into this
table?”
“Will this rule be violated if I do not enter a
new record into this table?”
“Will this rule be violated if I delete a record
from this table?”
-
Step
6. Record the rule on the Business Rule
Specification sheet.
-
Validation
Tables
-
What
are Validation Tables
A validation table is a table that holds
data specifically used to implement data integrity.
-
Using Validation Tables to Support Business Rules
Whenever you use a validation table to
enforce a Business Rule, you typically want to test
the rule if a user attempts to insert a new value
into the field or update an existing value within
the field. Complete a Business Rule Specification
sheet for the Business Rules established with a
validation table.
-
Reviewing
the Business Rule Specification Sheets
Examine the Specification sheets and make
certain that the rule has been properly established and
that the appropriate areas on the sheet are clearly
marked.
-
What
are Views
A view is a virtual table that comprises the fields of
one or more tables in the database; it can also include fields
from other views. Views are valuable for the following
reasons:
-
They
can be used to work with data from two or more tables.
-
They
reflect the most up-to-date information.
-
They
can be fitted to the specific needs of an individual or
group of individuals.
-
They
can be used to help enforce data integrity.
-
They
can be used for security or confidentiality purposes.
-
Anatomy
of a View
Views can be based on a single table, multiple tables,
other Views, or a combination of tables and Views, and they
can use the fields from the structures they comprise. The
structures that compose the View are referred to as “base
tables” or “base Views”. There are three categories of
views:
-
Data Views – Are used to
examine and manipulate data from base tables.
-
Aggregate
Views – The purpose of this type of view is to
display information that is the result of aggregating a
particular set of data in a specific manner. The most
common are sum, average, minimum, maximum, and count. Data
cannot be modified in an aggregate View for two reasons:
calculated fields are non-editable, and the data fields
are used for grouping purposes only. This type of View is
best used as the basis of a report or as a means of
providing various types of statistical information.
-
Validation
Views – Are similar to validation tables in that
they are used to implement data integrity.
-
Determining
and Establishing Views
-
Working with Users and Management – consider
the following points to help you identify View
requirements:
-
Review
your notes with the group.
-
Review
the data-entry, report, and presentation samples you
gathered during the early stages of the design
process.
-
Examine
the tables and the subjects they represent.
-
Analyze
the table relationships.
-
Study
the Business Rules.
-
Establishing
Views
Create each View that you’ve identified using
the appropriate tables and fields.
-
Using
Calculated Fields where appropriate
-
Imposing
Criteria to Filter the Data
-
Using
a View Specifications Sheet to Record the View
The View Specification sheet contains the
following items:
-
Name
– Name of the View
-
Type
– Indicate whether the View type is data,
aggregate, or validation.
-
Base
Tables – Indicate the names of the tables
used to construct the View.
-
Calculated
Field Expressions – Record the expressions
for the calculated fields you included in the
View.
-
Filters
– Criteria used to filter the records.
-
Reviewing
the Documentation for Each View – Keep the
following point in mind:
-
Make
certain that the View is properly constructed.
-
Make
certain that the calculated fields you’ve created
are suitable for the View.
-
Make
certain that the filters will retrieve the required
records.
-
Above all, make certain that you have a View diagram
and View Specifications sheet for each View.
-
Why
Should You Review Data Integrity
-
Assembling
the Database Documentation
Assemble a central repository, preferably a set of
binders. The design repository should consist of the following
sets of documents:
-
Final
table list
-
Field Specifications sheets
-
Calculated
field list
-
Table
structure diagrams
-
Relationship diagrams
-
Business
Rule Specifications sheets
-
View
diagrams
-
View
Specifications sheets
Two
additional sets of items you may consider keeping with this
documentation are the notes you compiled during the design
process and the samples you gathered during the analysis stage
of the design process. Each can be kept in a separate appendix
at the end of the documentation.
All of
these items constitute the complete set of documentation for
the logical design of the database. The design documentation
is vital for three reasons:
-
It
provides a complete record of the structure of the
database.
-
It provides a complete set of specifications and
instructions on how the database should be created during
the implementation process.
-
Should
it seem necessary to modify the database structure during
the implementation process, the design documentation can
be used to determine the effects and consequences of any
modifications.
Back
to Top |