III.

Database Modeling Building Blocks

This chapter introduces the building blocks of the relational database model by discussing and
explaining all the various parts and pieces making up a relational database model. For example, a table is
probably the most important piece in the puzzle of the relational database model, where fields or fields in
tables are perhaps of less significance but still essential to the semantics of the model as a whole.

1. Information, Data and Data Integrity

Information refers to knowledge or the way in which knowledge is communicated. Values in a
database are made up of data, which is essentially information. Validity is determined by the integrity of
data. The integrity of data is the correct form of data. The following list leads off the definitions of basic
terms and concepts:
 The concept of information—Information is knowledge or the communication of knowledge.
Knowledge is accumulated and derived by processes including those of experience, events, or
static information (such as a set of statistical values). In computer jargon, information is data that
is stored in a database, processed by programs, or even transmitted over a network such as the
Internet (between multiple users).
 The concept of data—Data is composed of unique, specifically formatted items of information.
Unique data item values are stored in slots in a database, processed as individual values by
coded programs, and transmitted across networks of wires, or even communicated with
electromagnetic signals to and from satellites (all over the world, and beyond).
 The concept of a computer program—Programs are sets of precise instructions, used to
manipulate and process changes to a database.
 The concept of a datatype—Datatypes comprise the forms data can take, such as numbers,
dates, strings, and others.
 The concept of data integrity—The integrity of data is the validity of data. Possible compromises
to data integrity include human error at data entry, network transmission errors, software bugs
and virus infections, hardware malfunction, disk errors, and natural disasters. Countering
compromises to data integrity is mostly a pre-emptive process, rather than a re-active process. In
other words, the best solution is to attempt to prevent data integrity loss. The most significant
prevention mechanisms are database backups (regularly), computer security (in all forms), and
properly designed interfaces restricting how data is entered by data entry users. Solving the
problem after the fact often utilizes something called a parity check (such as when transmitting
over a network), which is simply a check of something, of itself.

2. Understanding the Basics of Tables

In data model theory, a table is a bucket into which data is poured. The idea of the relational
database model and normalization is that data in a specific table is directly associated with all other items
in that same table—that would be each field as exaggerated in Figure 3-1, pictured as the horizontal
dimension.

Figure 3-1: Table fields express the metadata (horizontal) dimension.

Figure 3-2: Table records duplicate the set of fields into the tuples or data (vertical) dimension.
 Records are repeated over and over again in the vertical dimension, duplicating field structures
from the horizontal dimension, as exaggerated in Figure 3-2.
A table is effectively a structure containing fields across it in one dimension defining the structure
of records repeatedly added to that table. In other words, all records in the same tables have the same
field structure applied to them. Figure 3-3 shows a picture demonstrating a pile of books on the left,
passed through a table structure represented by the miniature ERD in the center, resulting in the
structured data set on the right, duplicated as the table records from Figure 3-2.

Figure 3-3: Raw data has structure applied to create structured data.

Tables contain fields and records. Fields apply structure to records, whereas records duplicate
field structure an indefinite number of times.

2.1. Records, Rows, and Tuples

The terms record, row, and tuple all mean the same thing. They are terms used to describe a
record in a table. Figure 3-4 shows the structure of fields applied to each record entry in a table.
There is really nothing to understand other than that a table can have multiple fields, whereas that set
of fields can have many records created in that table, and data can subsequently be accessed
according to the field structure of the table, record by record.

Figure 3 -4: Records repeat table field structure.

 2.2. Fields, Columns and Attributes
The terms field, column, and attribute all mean the same thing. They are all terms used to
describe a field in a table. A field applies structure and definition to a chunk of data within each
repeated record. Data is not actually repeated on every record, but the structure of fields is applied to
each record. So, data on each record can be different, both for the record as a whole, and for each
field value. Note the use of the term “can be” rather than “is,” implying that there can be duplication
across both fields and records, depending on requirements and constraints. A constraint constrains
(restricts) a value. For example, in Figure 3-5 the second box showing NOT NULL for the first three
fields specifies that the ISBN, PUBLISHER_ID, and PUBLICATION_ID fields can never contain NULL
values in any record.

Figure 3-5: The vertical structure of a table showing fields, constraints and datatypes.

Datatypes
There are many different types of datatypes, which vary often more in name than anything
else with respect to different database engines. This section describes all different variations of
datatypes, but without targeting any specific vendor database engine.

Data types can be divided into three separate sections:

 Simple datatypes—These are datatypes applying a pattern or value limitation on a single
value such as a number.
 Complex datatypes—These include any datatypes bridging the gap between object and
relational databases, including items such as binary objects and collection arrays. Specifics
on complex datatypes are not strictly necessary for this topic as they are more object-
oriented than relational in nature.
 Specialized datatypes—These are present in more advanced relational databases catering to
inherently structured data such as XML documents, spatial data, multimedia objects and
even dynamically definable datatypes.

3. Planning
Too many database projects have been abandoned because of a lack of planning, poor planning,
and even a failure to plan. The planning phase of a database is used to ensure that a database will
actually solve a business objective. Planning a database should include a feasibility study to ensure that
the cost of implementing a database solution is actually worth the efforts. It would be a shame to
implement a database system that later proves too expensive. Time should be spent on evaluating the
appropriate personnel to maintain the database project once the database is live. A database is useless
without the appropriate people to support the database system. An understanding of their strengths,
weaknesses, and synergy is necessary. Make sure that the business rules can be accommodated by the
database system. A company should never have to make changes to work with a business system;
however, the reverse should be explored. If a company currently has a database solution, an
understanding of how the old systems data will be migrated to the new database solution needs to be
understood. Users of the new database system should not be expected to reenter the years of data entry
in the new project. Finally collecting all the personnel who will interact directly and indirectly with the
database system is needed to ensure that the new database project objectives will be met.

Discovery
The first step in the planning phase in database design is the discovery phase. The discovery
phase is used to find facts about the company needs, employee expectations, current systems, current
practices, employee concerns, and documentation. There are a number of techniques used to today to
discover facts about an organization, but we will focus on the four most common.
 Interviewing
The Interviewing technique is the most practiced in database planning. There are many
interviewing approaches however. There is the one, on, one approach, where you meet with every
member of an organization who will be involved in using the database. There is also the group approach
where you can field questions and answers based on the departments where each employee works.
Then there is an enterprise interview approach where you will generally meet with middle management
from each department that will use the new database.

 One, on, One Interview

Each strategy has its advantages. For instances, the one on one approach allows you to
really hear what each user has to say about his or her thoughts on the new database. During this
process, you will really get a sense as to who is really supportive of the changes based on the
users' open and candid responses to questions you may express. You will discover during the
process those members who do not like change. Sometimes, these people will even try to derail
your efforts to implement a database. You can circumvent their attempts and instead empower
them with making some of the bigger decisions for their group. The major disadvantage to the
one, on, one process is that the process tends to be time consuming and costly.
 Group Interview
The group interview process is much less expensive because you will spend less time
interviewing however, you do not always hear everyone's input because of the competition for
attention from the interviewer. The competition among those interviewed to get their thoughts
expressed tends to make the group interview process chaotic.
 Enterprise Interview
The enterprise interview process, like group interviews tends to be less expensive and a little
more formal. Those involved in the enterprise interview process act as representatives for their
department. The representative would usually be a department manager, or someone who has
intimate knowledge of the business process in his or her respective department.

Interviewing is an important aspect of the planning phase of database design. Whatever

approach you decide to take, make sure that your objectives as a database developer are met by getting
answers to the questions you will have. You may find that you will go back and forth with different group
levels during the design phase, usually for clarification, or to make changes. In any event, the more you
can accomplish during the interview process, the less time you will spend seeking clarification.

Examining Documents
Another important discovery technique is to examine the company's existing documentation. The
company uses the existing documentation to make decisions. What is on the document that employees
need to make decisions? How are the documents filed? How are the documents retrieved? All of this is
important in the planning phase of database design. Not only do you want to see the user documents, but
you want the documentation on the existing system, such as the data dictionary, flow charts, and user
manuals, as well.

Observations
When articulating needs become too difficult, consider another discovery strategy, observation.
Take the time to watch how an employee, or a group of employees, operates during a specified business
process. A major disadvantage to this process is that users may behave differently if they know they are
being watched. Another major disadvantage is the timing in which you have decided to make
observations. There is no point observing the data entry process of student schedules during the first few
weeks of a semester.

Research
In helping the development process, a lot can be gained by obtaining information from other
resources outside the company walls. There are credible bulletin boards, textbooks on the specific model,
journals on the industry you are modeling, and user groups.

Examining the Existing System

Examining the existing system can illustrate to the database developers the problems that the
user is experiencing, live. There is nothing better than a real life experience to gain a perspective on a
situation. However, there is a great danger in evaluating an existing system. The major disadvantage in
examining an existing electronic system is that it can negatively influence your decisions in making the
new system.
It is very simple to take an old model and put a new face over it. It is very simple to take an older
DBMS and upgrade it to a newer more popular DBMS. The objective of the discovery phase is to
determine how you can improve the business process, not duplicate it. The only advantage to reviewing
the existing system is to ensure the user that you will not take away an interface or a specific process that
the users do enjoy. In order to make sure that the learning curve is as flat as possible, you can get copies
of the systems reports and screen captures to make sure that your new design is relatively similar.
4. Conceptual Design Phase
The second step in the planning phase of a database is the Conceptual Database Design. The
conceptual approach will include the discussion of the entities, attributes, and relationships between
entities, and the validity of data. In this section, we will discuss the important topics in the conceptual
design phase in order to show a full understanding of the user needs. These topics will include describing
the database without actually giving consideration to the DBMS. In this section, we will detail the topics
related to the conceptual design phase.

Identify Views (Modules)

In the database industry, a view has multiple meanings. As a view relates to design methods, a
view is a category, or a module, within an organization. For example, a manufacturing company will have
many departments in order to facilitate the many activities to meet the company's objectives. Each
department, in this case, would be considered a view. Each department that will use this new software
will need to be accounted for in order to develop its portion of the database project correctly. After
meeting with the IT director and president, it was determined that they needed a system to manage their
customer service operations, inventory warehouse, marketing efforts, orders, and sales tracking.
The views in this example will be the following departments: Customer Service, Marketing, Order
Entry, Sales, and Inventory. The departments who will be involved in using the database represent each
view. Since these views are involved in using the DBMS, they will be needed to help to develop the
subject(s) that will be found in each view. Each of these views may have many subjects to manage, so
that brings us to our next step identifying subjects.

Identify Subjects
The next consideration of the conceptual design phase is to identify the subjects. Every database
project will have a subject. More than likely, your database project will have multiple subjects, related
based on common information supplied by mutual attributes. One manner in determining subjects is
based on the discovery forms detailed in the previous section; the other is through interviewing clients
and having discussions about their objectives about the future database. The business that you are
evaluating may have multiple subjects per view. For example, the Orders view will have a number of
subjects such as; Order, Products, Customers, and Sales Representatives. In some cases, a subject may
be found in multiple views. For example, the Customers information would not only be important to those
individuals satisfying customer orders, but also to those individuals whose responsibility it is to handle
customer satisfaction. Further, the subject Sales Representatives are not only important to the order
takers but to those personnel who will manage the Sales department.
During the subject identification stage, you will identify them by nouns. For example, after a discussion
with the customer service manager, you determine that they continuously make references to the
"customer complaint form". This noun is the base subject for the customer service view.

Identify Relationships
Once the modules and entities have been identified, you can concern yourself with the
relationship between each entity. In this stage, you want to make sure that every entity is related to at
least one other entity. Generally, you would use nouns to identify the modules and tables, but with
identifying relationships, you would look for verbs when you are interviewing users. A user would tell you,
for example, that a customer is assigned a sales representative, a department has employees, or a
building has classrooms. The most common way to express relationships among entities is to do so
visually. The most common and standard method is to create an Entity Relation (ER) Diagram, which is
used to graphically represent the structural design of a database project. More detail on ER diagrams will
be discussed later in this chapter.

Identify Attributes
Once you have identified all of the entities in the database project, you will then want to list every
category of data that will be found in your project. Do not concern yourself with which entity the attribute
belongs. Just list every category of information that you have gathered. You may find a number of
problems.

 Duplicate field names

The first one is a duplication of field names. Consider a higher education database project.
You may encounter the First Name field almost a dozen times, depending on the scope of the
database. There are a great many subjects that will have a First Name field. Obviously, the
Student entity will have a First Name field, but how about the Faculty, Staff, Alumni, Vendors,
Speakers, Deans, etc… each one of the subjects will have a First Name field. In order to make
your database more understandable, it is important that field names are never duplicated unless
there is an association with another entity. A common practice is to prefix or qualify duplicate field
names with the entity's abbreviation, for example, studFirstName to represent the first names of
students, empFirstName for employees, and facFirstName for the faculty.
  Calculated fields
Now consider calculated fields. A calculated field is created because of the value(s) of other
attributes. It is not uncommon to want to know immediately the age of a student, or the length of
service in years an employee has, or the number of publications an author has in total. I can tell
you that it is never a good idea to store calculated fields in the basic structure of a database. The
nature of calculated fields are that they depend on values in other fields. That means you can
supply that calculated information to the users who want the information when they need it. But it
should not be part of the database structure.

 Atomic fields
Finally, atomic attributes are fields that store one piece of data. A field called Address should
not store 21Main Street, Wyandanch, New York 11798 as an example. The problem is if you
wanted a list of all customer from a specific state, or zip, the records could not be retrieved. As an
alternative, you will create atomic fields where you would have a field called Street that stores 21
Main Street, City that store Wyandanch, State that stores New York, and Zip that stores 11798.

 Determine Valid Data

Once you have identified all of the attributes and have taken the time to avoid some of the
pitfalls that I have outlined, you can now consider data validation. You want to discuss with your
clients the appropriate data for your records at this stage. For example, one client wanted to
uniquely identify doctors based on numeric and text combination. So in creating the field for
doctors, I established the field would be ###@@@.
Also consider the gender category. What would be the appropriate gender description? In
most cases, you would restrict a user to entering either M for male or F for female. For more
examples, consider a field called OrderExpectedDate, which is used to determine when the
customer expects to receive the products. One would conclude that the order should be after the
date when the order was taken. So a validation rule would look as follows: OrderExpectedDate =
DateOrdered + 3, giving the company three working days to accommodate the order.

 Determine Keys
At this stage of the conceptual design phase, we want to determine the keys for each entity.
A Primary Key (PK) is used to uniquely identify a record within an entity. A PK can be a single
column or multiple attributes. Any field that could potentially uniquely identify a record is known as
a Candidate Key (CK).
Consider a table used to store the college faculty. You may have a field called FacultyID,
which could automatically generate values to a record, and that same entity could have field
called SocSecNo. Both fields could uniquely identify a specific faculty, but you will have to choose
one to be the primary key. In this case, the SocSecNo field is now called an Alternate Key (AK).
An AK is an attribute or a set of attributes that could identify a record, but is not used as the PK. If
you need more than one attribute to make a PK, this is known as a Composite Key (CK).
Consider, for example, a student registering for a course. You will probably have a table called
StudentCourse. In order to prevent a student from registering for the same class more than once,
we use the student's ID and the course ID as a primary key. Also consider a collection of authors
and textbooks. As you know, more than one author can write a textbook. In order to credit the
authors with their related texts, you will need to create an author ID field, and a book ID field, and
make them both the primary key.

 Review Design with Users

Remember, that we are only in the conceptual design phase. This is the time to articulate
your understanding of the enterprise needs by stating all the factors of the design, conceptually.
Review with the high-level users, all of the high-level issues such as the modules and views
within the project. With the actual users of your database project, you can discuss lower-level
issues such as the attributes, relationships, and keys.

Entity-Relationship Modeling
With smaller database projects, you can probably get away with designing a solution without the
aid of modeling. However, with larger scaled projects, you will appreciate the help a graphic display an
Entity Relation (ER) diagram can offer. An ER Diagram is used to graphically represent your database to
display its structure and relations. In order to create an ER Diagram, you must understand the basic
notations involved.
The purpose of this section is to show the common graphic notations in order to accurately
display the design of a database project. There are many CASE tools available that one could use to
create such as diagram. The two more common players in the market are SmartDraw, Microsoft Visio;
even Microsoft Word.
Strategies
There are a number of ways to represent the structure of a database. One strategy is to use the
Database Design Language (DBDL). This strategy consists of your listing the table, with attributes, and
identifying the Primary, Alternate, Secondary, and Foreign Keys. Below is an example illustration based
on Books and Authors.

tblBook(BookID, ISBN, BookName, UnitPrice, Pages, PublisherID)

AK ISBN
SK BookName
FK PublisherID ® tblPublisher

In the above example, the name of the relation is tblBook. The primary key is denoted by the
underlined attribute in the list of attributes. Below the list of attributes, you will find the Alternate Key (AK),
Secondary Key (SK), and Foreign Key (FK), respectively. A popular strategy is the ER Diagram. In this
strategy there are two different formats a developer can take. One is to list each item in objects such as
bubbles, diamonds and rectangles. Another strategy is the use of standard rectangles to illustrate each
entity drawing lines known as crow's feet to illustrate relationships.

Entity
An Entity as described earlier is a subject. You use an entity to categorize the subjects
you want to capture in a database. More specifically, an entity could be a person, place, thing, an
event or activity, or even a concept. You notate an entity by using the entity object in a CASE
tool.
Lets consider a Book database project where the subjects are books, authors, and
publishers. The first entity display I will draw will be for books.
Note that the text found in the entity graph is in an uppercase format. It is the industry
standard to case the entity descriptions in an uppercase format. The book example is considered
a strong entity. A strong entity is a subject that is independent of all other subjects. For example,
you do not need a subject Author or Publisher to have a book. A weak entity, on the other hand,
is a subject that cannot be independent of other entities. For example, the entity Type is used to
identify if the book has a hardcover, or is spiral bound, etc. The entity Type relies on the entity
Book in order for it to exist. Without the Book entity, the Type entity would be useless. A weak
entity is denoted by an entity graph that is similar to the standard except that it appears to be one
entity superimposed within another.

Attributes
The major property within an entity is known as an attribute. An attribute is a category,
field, or even described as a column. A simple attribute is used to record the areas of interest for
a given entity. The graphic display used to identity an attribute is the ellipses.

An area of interest Book entity is the Title of the book. Below is a graphic notation.

There are however different types of attributes within an entity. For example, the primary
key is treated differently from other attributes. This would be known as the key attribute. You
notate the primary key in an attribute the same as the previous except the attribute is underlined.
The ISBN would be an example attribute that would be the primary key for the Book entity. Below
is an example illustration.

Another attribute type is known as the composite attribute. A composite attribute is

used to display a general listing of attributes that could be displayed into smaller attributes. For
example, the attributes Name could be decomposed to First, Middle, Last, Prefix, and Suffix.
Another example would be points of contact. There are number of ways to communicate with an
individual. For example, you can use the phone, fax, or e-mail, all of which are points of contact.
Another example would be an address. An address is a composite attribute because it too, like
name and points of contact, is really a category of decomposed attributes. Address attributes
would be street1, street2, city, state, and zip code. Below is an example illustration.

You will want to display attributes that may contain multiple values. A multi-valued
attribute is one that can contain more than one value. For example, a book could be on more than
one subject. This book, for example, covers database concepts, SQL, Visual Basic, Access, and
HTML. Below is an example illustration of a multi-valued attribute.

Some attributes of interest are not documented; they are determined by values found in
the current entity or other entities within the database project. These are known as derived or
calculated attributes. For example, in order to derive your employee's age, you would take the
employee's date of birth and subtract it from today's date, and divide that from 365 days in a year
to return the employee's age in years. Derived attributes look similar to the simple attribute
notation except the derived attributes are perforated.