Relational Model of Databases
As an example of how data can be organized
conceptually, we shall describe the relational data model. In this conceptual
model, the data in the database is viewed as being organized into a series of
relations or tables of data which are associated in ways defined in the data
dictionary. A relation consists of rows of data with columns containing
particular attributes. The term "relational" derives from the
mathematical theory of relations which provides a theoretical framework for
this type of data model. Here, the terms "relation" and data
"table" will be used interchangeably. Table 14-2 defines one possible
relation to record unit cost data associated with particular activities.
Included in the database would be one row (or tuple) for each of the various
items involved in construction or other project activities. The unit cost
information associated with each item is then stored in the form of the
relation defined in Table 5-2.
This entry summarizes the unit costs associated
with construction of 12" thick brick masonry walls, as indicated by the
item DESCRIPTION. The ITEM_CODE is a numerical code identifying a particular
activity. This code might identify general categories as well; in this case, 04.2
refers to general masonry work. ITEM_CODE might be based on the MASTERFORMAT or
other coding scheme. The CREW_CODE entry identifies the standard crew which
would be involved in the activity. The actual composition of the standard crew
would be found in a CREW RELATION under the entry 04.2-3, which is the third
standard crew involved in masonry work (04.2). This ability to point to other
relations reduces the redundancy or duplication of information in the database.
In this case, standard crew number 04.2-3 might be used for numerous masonry
construction tasks, but the definition of this crew need only appear once.
WORK_UNIT, OUTPUT and TIME_UNIT summarize the expected output
for this task with a standard crew and define the standard unit of measurement for
the item. In this case, costs are given per thousand bricks per shift. Finally,
material (MATL_UNIT_COST) and installation (INSTCOSTS) costs are recorded along
with the date (DATEMCOS and DATEICOS) at which the prices were available and
entered in the database. The date of entry is useful to insure that any
inflation in costs can be considered during use of the data.
The data recorded in each row could be obtained by
survey during bid preparations, from past project experience or from commercial
services. For example, the data recorded in the Table 5-2 relation could be
obtained as nationwide averages from commercial sources.
An advantage of the relational database model is
that the number of attributes and rows in each relation can be expanded as
desired. For example, a manager might wish to divide material costs
(MATL_UNIT_COST) into attributes for specific materials such as cement,
aggregate and other ingredients of concrete in the unit cost relation defined
in Table 5-2. As additional items are defined or needed, their associated data
can be entered in the database as another row (or tuple) in the unit cost
relation. Also, new relations can be defined as the need arises. Hence, the
relational model of database organization can be quite flexible in application.
In practice, this is a crucial advantage. Application systems can be expected
to change radically over time, and a flexible system is highly desirable.
With a relational database, it is straightforward
to issue queries for particular data items or to combine data from different
relations. For example, a manager might wish to produce a report of the crew
composition needed on a site to accomplish a given list of tasks. Assembling
this report would require accessing the unit price information to find the
standard crew and then combining information about the construction activity or
item (eg. quantity desired) with crew information. However, to effectively
accomplish this type of manipulation requires the definition of a
"key" in each relation.
In Table 5-2, the ITEMCODE provides a unique
identifier or key for each row. No other row should have the same ITEMCODE in
any one relation. Having a unique key reduces the redundancy of data, since
only one row is included in the database for each activity. It also avoids
error. For example, suppose one queried the database to find the material cost
entered on a particular date. This response might be misleading since more than
one material cost could have been entered on the same date. Similarly, if there
are multiple rows with the same ITEMCODE value, then a query might give
erroneous responses if one of the rows was out of date. Finally, each row has
only a single entry for each attribute.
The ability to combine or separate relations into
new arrangements permits the definition of alternative views or external models
of the information. Since there are usually a number of different users of
databases, this can be very useful. For example, the payroll division of an
organization would normally desire a quite different organization of
information about employees than would a project manager. By explicitly
defining the type and organization of information a particular user group or
application requires, a specific view or subset of the entire database can be
constructed. This organization is illustrated in Fig. 5-1 with the DATA
DICTIONARY serving as a translator between the external data models and the
database management system.
the operations associated with querying and manipulating relations is an
explicit algebraic theory. This algebra defines the various operations that can
be performed on relations, such as union (consisting of all rows belonging to
one or the other of two relations), intersection (consisting of all rows
belonging to both of two relations), minus (consisting of all rows belonging to
one relation and not another), or projection (consisting of a subset of the
attributes from a relation). The algebraic underpinnings of relational
databases permits rigorous definitions and confidence that operations will be
accomplished in the desired fashion.