7k.

at: _

. a T»
a w

u
p
w.

.4:
K3
. a.

.. 7 \ ¢
i. 2.934
5.5..

L

,L. .. h
wk?
’Nfl‘fi ,
13:... 13's?“

.5 a; u.
.. x.

 

1:. 5
lififi‘.

I!“ .

I [(5
.9.

En E}-
.I >

 

4.011.! a!
v9,I.

 

THESIS
ZI

IIIIIIIIIIIIIIIIIIIIIIIIII IIIIII IIIIIIIIIIIIIIII

3101766 8025

 

LIBRARY

Michigan State
University

 

 

 

This is to certify that the

thesis entitled “q r) A n
DATII ASE MHN/‘GErIEII/Ts >573 mp b I, ,1
FOR CONéTRIACTIL‘II' PROJECT €057 4U) '1‘7/ {)5
DATA P1400555

presented by

Hoymwa SHI

has been accepted towards fulfillment
of the requirements for .

mast-Q Y degree in B“; /£”j (Ofl5i’KCé1‘0IL
Management

 

 

0%M4/7LW

Major professor

 

Date IQ. l0.98'

0-7639 MS U is an Affirmative Action/Equal Opportunity Institution

PLACE IN RETURN BOX to remove this checkout from your record.
TO AVOID FINES return on or before date due.
MAY BE RECAULD with earlier due date if requeSted.

 

DATE DUE | DATE DUE

DATE DUE

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1m cJCIRCIDdaDUImu

DATABASE MANAGEMENT SYSTEM MODELING FOR
CONSTRUCTION PROJECT COST AND TIME DATA PROCESS
By

Hongying Shi

A THESIS
Submitted to
Michigan State University

in partial fulfillment of the requirements
for the degree of

MASTER OF SCIENCE

Department of Agricultural Engineering

1998

ABSTRACT

DATABASE MANAGEMENT SYSTEM MODELING
FOR CONSTRUCTION PROJECT TIME AND COST DATA PROCESS

By

Hongying Shi

In order to successfully manage a construction project, cost and time controls are two
important project management functions and their related information usually draws
more concern from project managers. A database management system can be designed to
collect, store and manage these data throughout the construction process to satisfy
information needs for decision making.

The critical part of designing a database management system is the abstraction of the
conceptual data model from reality. The primary objective of this research was to develop
a formal conceptual database model for construction project cost and time related data.
This conceptual model was developed using the entity-relationship database modeling
method. A construction company's business process was analyzed to identify the project
construction processes and events. Data generated in these processes was recorded
around these events with what (resources) and who (agents) are involved. A prototyping
database structure based on this conceptual model was implemented into the MS Access
environment. Two case studies were conducted to evaluate the model application in

construction practice.

Copyn'ght by
HONGYING SH]
1998

To my Parents and Sister,

And to my Uncle Dr. Shun Ku

iv

ACKNOWLEDGMENTS

I would like to express my deep gratitude and appreciation to my advisor, Dr. Thomas
Burkhardt, for his constant support and guidance during the course of this study.

My sincere thanks to the advisory committee, Dr. Guido L. Geerts (Accounting) and
Professor Timothy Mrozowski (Building Construction Management) for their continuing
advice and encouragement.

The help and suggestion from Mr. Mike Schuchaskie and Mr. Bill Jacobitz of Clark
Construction Company during my case study, are greatly appreciated.

I would like to thank the faculty, staff and students of Building Construction
Management program and the Department of Agricultural Engineering for providing a
challenging learning environment. Special thanks to Mr. Aluel S. Go for his help and
support.

Thanks to my parents and my entire family for their guidance and love. Deep
appreciation goes to my uncle, Dr. Shun Ku for his continuous support and

encouragement.

TABLE OF CONTENTS

LIST OF TABLES ......................................................................... 10
LIST OF FIGURES ........................................................................ 12
ABBREVIATIONS ......................................................................... 13
CHAPTER 1
OVERVIEW AND INTRODUCTION .................................................. 1
1.1 Overview ................................................................................................... . 1
1.2 Problem area ............................................................................................... 3
1.3 Research objective .................................................................................... 9
1.4 Research methodology .............................................................................. . 11
1.4.1 Literature review ............................................................... 11
1.4.2 Integration of cost and time control ......................................... 12
1.4.3 Conceptual model of project database management system
development ........................................................................... 12
1.4.4 A prototyping database structure implemented in relational database
software ................................................................................ 13
1.5 Expected output .......................................................................................... 13
1.6 Organization of the thesis ............................................................ 14
CHAPTER 2
LITERATURE REVIEW ....... . ....................... . ................................... 16
2.1 Cost and schedule control system integration .................................... 16
2.1.1 Cost/Schedule integration problems and methods ........................ 16
2.1.2 CPM/COST: An integrated approach ....................................... 17
2.1.3 Project breakdown model for integrated cost and time control .......... 18
2.2 Information management and relational database integrated in construction
management ........................................................................... 22
2.2.1 COMBINE2 (Computer Models for the Building Industry in Europe)
- Subproject building HVAC component database ............................... 22
2.2.2 Integrated database systems .................................................. 24
2.2.3 Integrating safety and health performance into
construction CPM .................................................................... 24
2.2.4 An entity relationship (E-R) model of construction
project bid data ........................................................................ 25
2.2.5 Related work in project control area: Design of construction industry
information management systems ................................................... 26
2.3 Chapter summary ..................................................................... 27

vi

CHAPTER 3

SYSTEMS APPROACH OF DATABASE DESIGN .................................. 31
3.1 Computer-based information system and database ............................... 32
3.1.1 Computer-based information system overview ............................ 32
3.1.2 Database system ............................................................... 33
3.1.2.1 File system in information processing ............................... 33
3.1.2.2 Database management system ........................................ 34
3.1.2.3 Entity relationship database modeling ............................... 35
3.2 Information system development life cycle ....................................... 38
3.3 Database development process ..................................................... 41
3.3.1 Two approaches of database design ......................................... 41
3.3.1.1 Process-driven design ................................................... 42
3.3.1.2 Data-driven design ...................................................... 42
3.3.1.3 Comparison of data-driven and process-driven
approach ................................................................. 43
3.3.2 Database design process ...................................................... 44
3.3.2.1 Information requirements analysis ........................................ 44
3.3.2.2 View modeling and integration ............................................ 45
3.3.2.3 Logic design .................................................................. 46
3.3.2.4 Physical design ............................................................... 47
3.4 Chapter summary ..................................................................... 48
CHAPTER 4
INFORMATION REQUIREMENTS ANALYSIS AND INTEGRATED
PROJECT COST SYSTEM ............................ ............................... 50
4.1 Project management system overview ...................................................... . 51
4.2 Project cost system ............................................................................... 52
4.2.1 Work breakdown structure (WBS) and data capture units ............... 53
4.2.1.1 Work breakdown structure ............................................. 53
4.2.1.2 Cost coding and data capture unit .................................... 54
4.2.2 Cost Plan ........................................................................ 59
4.2.2.1 Schedule .................................................................. 59
4.2.2.2 Estimate .................................................................. 60
4.2.3 Cost control ..................................................................... 60
4.2.3.1 Project job cost accounting ............................................ 60
4.2.3.2 Source documents for data recording ................................ 64
4.3 Information requirements from form analysis .................................... 64
4.3.1 Progress sheet .................................................................. 65
4.3.2 Manhour time card ............................................................ 66
4.3.3 Material requisition slip ...................................................... 68
4.3.4 Equipment time card .......................................................... 69
4.3.5 Subcontract costs .............................................................. 71
4.4 Chapter summary ..................................................................... 73

vii

CHAPTER 5

REA DATABASE MODEL FOR PROJECT COST AND TIME DATA ....... 74
5.1 A construction company and value chain ................................................... 75
5.1.1 Value chain concept ........................................................... 75
5.1.2 A construction company's value chain ...................................... 76
5.2 REA concept for integrated cost system redesign ................................ 79
5.3 REA view for project cost and time data 81
5.3.1 REA template to model identified events .................................. 82
5.3.1.1 Material actual usage ................................................... 82
5.3.1.2 Labor actual usage ...................................................... 83
5.3.1.3 Equipment actual usage ................................................ 84
5.3.1.4 Subcontract ............................................................... 85
5.3.1.5 Actual work item in progress aggregation ........................... 86
5.3.1.6 Actual work item in progress and work item budget ............... 87
5.3.1.7 Work item budget and its components: material, labor and
equipment .......................................................................... 88
5.3.2 Overall conceptual model .................................................... 90
5.4 Logic design in MS Access 94
5.4.1 Table creation ................................................................... 94
5.4.2 Database operation ............................................................ 95
5.4.3 Weekly labor and progress report generation ............................... 96
5.5 Chapter summary ....................................................................................... 99
CHAPTER 6
CASE STUDIES ......................................................................... 100
6.1 Institutional project ..................................................................................... 100
6.1.1 Cost system in this project .................................................... 101
6.1.2 Analysis of the cost system of this project ................................. 102
6.1.3 Recommendation: database system for cost data .......................... 103
6.1.3.1 System changes ......................................................... 103
6.1.3.2 Evaluation of benefits from these changes .......................... 105
6.2 Case two: A prison facility .......................................................... 105
6.2.1 The project cost system analysis ............................................. 106
6.2.2 Possible changes for the cost system ....................................... 107
6.2.3 Evaluation of benefits from these changes ................................. 108
6.3 Feed back from the project manager .............................................. 109
6.4 Chapter summary .. .................................................................................... 110

viii

CHAPTER 7

SUMMARY, CONCLUSION AND FUTURE RESEARCH ........................ 111
7.1 Summary ................................................................................................... . 111
7.2 Conclusions ............................................................................................... . 113
7.2.1 Conclusions about time related data integrated in cost system .......... 113
7.2.2 Conceptual database model development related conclusions .......... 114

7.2.3 Conclusions related to perform information-processing
function of the project cost system in a database environment .......... 115
7.3 Future research .. ...................................................................................... . 115
APPENDICES ................................................................................. 117
Appendix A Concepts of Data Flow Diagram (DFD) 118
Appendix B CSI Master Format — Broadscope Section Titles .................... 120
Appendix C Data Table Properties ..................................................... 125
LIST OF REFERENCES .................................................................... 127

LIST OF TABLES

Table 4.1 Job progress sheet ..................................................................... 66
Table 4.2 Manhour time card .................................................................... 67
Table 4.3 Data areas and their items derived from manhour time card .................... 67
Table 4.4 Material requisition slip .............................................................. 68
Table 4.5 Data areas and their items derived from material requisition slip .............. 69
Table 4.6 Data Equipment time card ........................................................... 70
Table 4.7 Data areas and their items derived from equipment time card .................. 69
Table 4.8 Subcontractor progress payment claim ............................................. 72

Table 4.9 Data areas and their items derived from subcontractor progress payment

claim .................................................................................. 71
Table 5.1 Entities and their attributes .......................................................... 93
Table 5.2 Example of weekly labor and progress report .................................... 97
Table 5.3 Actual work item in progress data table ........................................... 97
Table 5.4 Actual labor usage data table ........................................................ 97
Table 5.5 Workers’ information data table .................................................... 98
Table 5.6 Craft type data table ................................................................. 98

Table 5.7 Table for the relationship between labor type entity and work item
budget entity ............................................................................ 98

Table 5.8 Work item budget data table ......................................................... 98

Table Al The diagrammatic representation of dataflow diagramming (DFD)
Concepts ............................................................................... 1 18

Table C.1 Properties of Entity Data Table in Prototype Database ........................... 125

xi

LIST OF FIGURES

Figure 1.1 Conceptual database model abstraction and information

processing ........................................................................... . 8
Figure 2.1 Work element model ................................................................ 20
Figure 2.2 Work element matrix table .......................................................... 20
Figure 3.1 Example of ER diagram ............................................................. 38
Figure 3.2 The information system development life cycle ................................. 39
Figure 3.3 Project cost and time data model development process ......................... 49
Figure 5.1 The generic value chain ............................................................. 76
Figure 5.2 Business process in a construction company ..................................... 78
Figure 5.3 A simplified REA example ......................................................... 81
Figure 5.4 Simplified REA pattern of material actual usage ................................. 83
Figure 5.5 Simplified REA pattern of labor actual usage .................................... 84
Figure 5.6 Simplified REA pattern of equipment ............................................ 84
Figure 5.7 Simplified REA pattern for Subcontract .......................................... 86
Figure 5.8 Actual work item in progress aggregation ......................................... 87
Figure 5.9 ER model for work item in progress and budget ................................. 88
Figure 5.10 Relationship of resource cost and work item budget ........................... 89
Figure 5.11 Conceptual database model for project cost and time data ..................... 91
Figure 6.1 Conceptual database model applied for case project 1 .......................... 104
Figure A.l Subcontractor Progress Payment Process DFD .................................. 119

xii

LIST OF ABBREVIATIONS

A/E/C: Architecture/Engineering/Construction

CAD: Computer-Aided-Design

CBS: Cost Breakdown Structure

CM: Construction Management

COMBINE: Computer Models for the Building Industry in Europe
CPM: Critical Path Method

CSI: Construction Specifications Institute

DBMS: Database Management System

DFD: Data Flow Diagrams

ER: Entity Relationship

HVAC: Heating, Ventilating, and Air Conditioning

IS: Information System

OBS: Organization Breakdown Structure

QBE: Query by Example

REA: Resource-Event-Agent

STEP: the international STandard for the Exchange of Product data
SQL: Structured Query Language

WBS: Work Breakdown Structure

xiii

Chapter 1

Overview and Introduction

1.1 Overview

Construction projects are time-consuming undertakings and each project usually
involves much more investment of money than a typical manufactured product. The
development of a project normally consists of several phases including planning and
definition, design, procurement and construction (Clough and Sears, 1991). Management
of a construction project is one of the most challenging management assignments in the
industrial world. In a short period of time after contract award, the field manager must
finalize the preliminary project plans developed during the pre-bid or proposal phase,
order materials and construction equipment, erect construction support facilities, and
recruit and mobilize a construction force (CII, Project Control, 1987). Of course, the
primary objective during the construction process is completing the project on time and
within the budget while meeting established quality requirements and other
specifications. To achieve this goal, a well-designed project management system must be
implemented. Construction project management, applied to a given project, is a process
to monitor the field operations according to the plan, find the deviations, and take timely
corrective action. The construction process starts with the onset of construction. At this
point, a comprehensive construction budget and a detailed time schedule of operations
have been prepared and serve as the base plan for project control. After the project has
been started, the reporting system provides progress information to monitor and measure

the actual construction process. Deviations from the plan are identified and management

action is needed to revise it. Project control is established this way (Clough and Sears,
1991). During the construction process, many data are generated. One of the important
parts during the control process is to collect, store, and manage data throughout the
construction process to draw progress information used in the monitoring and reporting
system in a timely fashion. In the meantime this information can be used as a basis for
planning future projects.

A well-designed information system should be applied as part of an efficient project
management system. The information system is usually composed of people, computer
hardware, software programs, and database(s). It provides a mechanism for data
collection, storage, and retrieval. It also facilitates the transformation of data into
information and the management of both data and information. Information plays a key
role in construction project management. For a construction project to be well managed,
data generated during the project construction process must be readily available. As an
important component of an information system, database technology is an important tool
for managing the enormous amount of data generated throughout the project construction
process, and is one of the areas that can be improved to benefit project management. The
term database refers to a shared, integrated computer structure that houses a collection of
data (Rob and Come], 1997). Database management systems (DBMS) are programs to
manage the database. Through an efficient DBMS, data from the project at hand is
available for management on a timely basis and also available for further project use.

Cost and time management is the key functions in a project management system,
since they are the two major criteria to measure the success of a project. Cost and time-

related information has usually drawn more attention from project managers than other

issues. Project cost and time management are based on cost and time plans developed for
the project and on an information system that will provide data for comparing planned
performance with actual performance (Clough and Sears, 1991). So the major concern of
a database management system (DBMS) in this research is for the collection, storage,
management and retrieval of the cost and time-related data to facilitate project cost and
time management.

Computer and information technologies have been employed in many business
processes and have made previously impossible things, possible. The fast development of
CPU speed, the memory capacity and hard disk space has physically improved the
database management ability in PC based computers. The construction industry can also
benefit much from this technology improvement. During the construction process large
amounts of data are generated, a well-designed computer-based project database
management system can provides a central data storage pool and help to achieve

automatic information processing and retrieval.

1.2 Problem Area

Construction project management begins from a project plan, which sets up the
budget and target for operation. After initiating the construction, the project-controlling
phase begins. Successful cost and time control are key parts for project success. Effective
control is dependent on a good information system. This system provides feedback
information to measure, evaluate, and report job progress, comparing it with the planned
performance. Information must be timely with little delay between field work and

management review of performance. This timely transfer of information gives the project

manager a chance to evaluate alternatives and take corrective action while an opportunity
still exists to rectify problem areas (Clough and Sears, 1991). This need for rapid and
efficient information transfer raises the requirement of an efficient information system for
timely retrieval of useful information for management. As essential components of an
information system, database provides the basic structure and pool to collect, store and
retrieve data into information. The problem is how to design and implement such a
database management system (DBMS) for construction project data, especially cost and
time data.

Traditionally, a construction project cost system is designed to control the
construction costs within the established control budget and to develop information for
estimating future work. Cost data are stored and processed in the job cost system. Along
with the construction progress, time data are entered in the schedule system to update the
preset schedule. Each type of data collection has traditionally become independent from
the other and maintained separately. Managers are dependent on information from both
systems, which reduces the efficiency of obtaining meaningful information and making
prompt and fully informed decisions (Abudayyeh and Rasdorf, 1991b). Sometimes they
are misdirected by different types of information related to one object. A DBMS for time
and cost control functions must be able to collect, manage, and use both types of data in
an integral and timely way. When the time data can be stored and managed in the cost
system, the integration of both the functions in one system can be realized. Basic to a
project management information system is identifying specific packages of work referred
to as work items (Adrian, 1979). These work items serve as a means of integrating the

project time management function in the cost system as an integrated system. So the first

problem to be solved in building a project DBMS for cost and time data is to define the
work items as the basic data storage units. There are many ways to define specific work
items for a given project. The broader the scale of a work item and the fewer work items
you have for a project, the less accuracy will be provided for determining the project's
work performance. The way a project manager defines the work items also reflects the
detail level in the operation that will be planned, monitored and evaluated. A well-known
breakdown structure is the Master Format published by the Construction Specification
Institute (CSI). It also provides a standard for numbering and classifying sections to
organize both estimates and schedules by the same numbering system, which can then be
used for the control budget and schedule (Barrie and Paulson, 1992). Some researchers
have proposed to use work packages which are based on the work breakdown structure to
divide a project into manageable work packages that have well-defined scopes of work.
Each work package represents the actual tasks that will be used in the project's network
and is suggested as a control account against which both cost and time data are acquired
and accumulated (Rasdorf and Abudayyeh, 1991a). This means that work must be
scheduled and estimated in accordance with the control account in the plan stage. In the
operation phase, data will be collected and stored in these accounts for project
monitoring, reporting and controlling. The work package and CSI code system can be
integrated to provide the basic numbering data storage unit for the modeling of project
DBMS in this research.

The availability of DBMS makes it possible to deal with more sophisticated uses of
the project data resource, if the DBMS is designed to make use of that availability. The

data structures and the extent of the relationships among them play a powerful role in

determining how effective a DBMS is. Therefore, design of a database management
system is very crucial. Only a well-designed database can facilitate data management and
become a valuable information generator. Researchers have focused on the process of
designing databases to support numerous complex applications. The database
development begins from the procedure to determine the information needs of the end
users. Then system designers use a conceptual model to derive a formal representation of
the information elements obtained in the requirement survey. This conceptual model is
transferred into a computational DBMS logic model suitable for implementation in the
computerized environment. Finally, the model is applied to a specific hardware and
software environment. In the development process, conceptual design for a conceptual
database model is very import. It is emphasis on an understanding of the structural
properties of data that is independent of implementation details (Batini, et. al., 1992). It
represents the domain reality in an abstraction level. Using conceptual models, one can
build a description of reality that is easy to understand and interpret (Batini, et. al., 1992).
In the case of project construction, as shown in Figure 1.1. Labor, material and equipment
are resources that make a project. Superintendents are involved in the construction
process to be in charge of the process. Parts of the project work are subcontracted to
subcontractors. This typical picture of project construction is illustrated in the upper part
of Figure 1.1. All these components and activities have related cost and/or time data. In
the middle of Figure 1.1 a conceptual database model represents this picture and
summarizes the data in an abstraction level. When data are presented in their logic view

in the conceptual model they can be applied in a computerized environment for

information processing to get the demand information for managers. The process of data
abstraction from reality to computerized information processing is shown in Figure 1.1.
As a conceptual database model is critical in database design, the problem here is
how to develop such a model. Model development requires the use of tools. The Entity-
Relationship (ER) model method is widely used in the relational database environment,
which is also the database environment used in this research. Several conferences have
been organized on the applications of the ER model to database design and to software
design in general. In 1988 American National Standards Institute chose the ER model as
the standard model for Information Resource Dictionary Systems (Batini, et. al., 1992).
In this model data elements are organized around the basic notions of entities,
relationships and attributes. More explanation about this model will be given in chapter 3.
These notions can be furthered by the "real-world" objects that will represent the system.
In an accounting information system these would include: economic resources (such as
raw material) controlled by an enterprise, economic events (such as issues and purchases
of raw material) affecting the resources, and economic agents (such as foremen and
buyers) participating in these events (McCarthy, Rockwell and Arrnitage, 1994). This
Resource-Event-Agent (REA) representation and relations consist of the REA template in
the relational database environment. REA was introduced by McCarthy(1982) as a
domain-specific theory for the design of accounting information systems, and its use has
gradually been expanded to the modeling of economic phenomena in general (Geerts and

McCarthy, 1994). This concept will be explained more in chapter 5.

 

 

  

Subcontractors

Material . Reality: Project Construction
I

 

 

 

 

Mntractors l I Equipment

 

Conceptual Database Model

   

Stored Database and

Computer Process Information

Figure 1.1 Conceptual database model abstraction
and information processing

Database and information system design also requires a good understanding of the
business process of its domain. The value chain concept provides a systematic method for
examining all activities for which a company is responsible and how they interact. Then
it is viewed as an input and output process in which resources are entered as inputs and
some value added-outputs are produced through the production process. A typical
manufacturing company might include product and service flow through (1) research and
development; (2) engineering; (3) production and manufacturing; (4) marketing; (5) sales
and distribution and (6) service. Each step can be considered as a collection of activities
that takes one or more kinds of input and creates an output that is of value to the customer
(Geerts and McCarthy, 1994). From a construction company point of view, the project
construction phase can be considered as the production and manufacturing cycle. Labor,
material and equipment are inputs to the process. Through the construction activities, a
new facility is the output that will be passed on to the customer (owner). The value chain
concept and REA template can be used to analyze business processes where economic
exchanges are evaluated from a database perspective. These concepts will be applied in

this research to analyze the construction business in a database environment.

1.3 Research Objectives

The primary objective of this research is to develop a formal conceptual database
model for construction project cost and time related data.

The purpose of this model is to present data elements of a domain system in an
organized and abstract representation via standard modeling. The domain system, which

is the part of reality being represented in a database system, is the integrated project cost

system. An integrated system means that data processed here are not just cost data, but
some time related data for schedule control are also considered here. Based on this model
a database management system (DBMS) can be built to facilitate project information
processing. This research explores database design development issues and highlights

' conceptual database modeling.

The specific objectives of the research are:

(1) To review the issue of integrating time related data to the cost system and to
establish a framework which defines basic control items for data storage and
control purposes. After time data have been collected and stored in the cost
system, this system becomes an integrated cost system which can perform some
time control functions;

(2) To investigate issues about information systems and the database development
life cycle. The database development process for the integrated cost system is
outlined;

(3) To develop a conceptual data model for the integrated cost system. This
conceptual model represents a project integrated cost system in a database view.
It can also be transferred into a logical model tailored to specific DBMS
software.

(4) To build a prototyping database structure in MS Access. These examples will
demonstrate how data can be processed in the database system that is built from
the conceptual model to prove the concepts explained in the model.

A database reflects an efficient way of storing, accessing, and managing end user

data. A theoretical database development process is needed to produce an efficient

10

database. Conceptual design is the most critical phase of database design within this
process (Batini, et.al., 1992). Pursuing the above objectives will place emphasis on the
phase that includes information requirement analysis and conceptual model development.
The fulfillment of these objectives will give the author some specific knowledge of
database development for construction project data. It will also help integrating
information system concepts in the construction field to accomplish effective project
management.

The domain projects that are studied in this research will be projects that vary from
one million to one hundred million—dollar values. When a job value is in this range, a
detailed, complex reporting and information system is required to serve project
management needs. Then the cost of implementing this system can be justified by the
value of management data that it provides. A simpler and less elaborate information

system is sufficient for smaller and simpler projects.

1.4 Research Methodology

1.4.1 Literature Review

The first step of this research is a comprehensive review of literature. This review
includes the areas related to construction project management, integration of cost and
time control, information system and database management system design, ER data
modeling, relational database software study, value chain and REA business process

analysis.

11

1.4.2 Integration of Cost and Time Control

The next phase is to study the project construction process and the project
management system focusing on the cost and time control subsystems. Information
requirements and flow in project cost system will be studied. Data elements and their
characteristics will be discovered and classified from analyzing documents in this system.
The issue of how to integrate the time control function into the cost system will be raised.

Basic data storage units for project cost and time data will be identified.

1.4.3 Conceptual Model of Project Database Management System Development

A conceptual model of project database management system will be developed. This
model will be a formal representation of the cost and time related data elements generated
in the construction process. There are several research tasks involved in developing the
model. The value chain analysis tool will be used to study the business process of the
construction company. Inputs and outputs during the project construction cycle will be
analyzed. The information requirements of a project manager for cost and time
management will be examined. The data elements related to this requirement will be
organized in terms of entities, relationships, and attributes. The REA pattern will be used
_ to as a template to represent the entities and relationships that are summarized from the
real-world objects. Using this method, a conceptual model of the project database system
will be developed. This model will provide a logical view of the data that meet the

information requirements of the project manager.

12

1.4.4 A Prototyping Database Structure Implemented in Relational Database
Software

In this phase, the author will build sample data tables based on the conceptual model
in a relational database software environment. Microsoft Access1 will be selected as the
implementation software. The conceptual model will be transferred to be a particular MS
Access relational database model. Data tables will be developed. Some sample data will
be entered into Access to show how information can be derived from the database by

Structured Query Language (SQL) tool.

1.5 Expected Output

This research study is intended to integrate relational database management
technology with construction project management systems. This research will focus on
using ER modeling tools and REA templates to develop a conceptual model for the '
design of a DBMS which will store and manage project time and cost data to facilitate
effective cost and time control. It will follow the system approach of the DBMS
development process. The DBMS will be built upon the integrated cost and time control
systems. By determining the information requirements of project managers and
information flow in this system, data elements will be organized in an R(resource) -
E(event) - A(agent) template and the relationships among entities will be defined. The
DBMS conceptual model will be generated using the ER model. This conceptual model

should not be limited to a particular database software application. Based on this model, a

 

1 Trade names are used in this paper solely to provide specific information. Mention of a
product name does not constitute an endorsement of the product by the author to the
exclusion of other products not mentioned.

13

project DBMS can be developed. This DBMS will store, manage and retrieve the project
cost and time data. In this research, data tables will be built base on this model in
Microsoft Access. Sample data will be input to show information processing procedure in

DBMS.

1.6 Organization of the Thesis:
This thesis is organized as follows:

0 Chapter 2 provides the research and literature review. It describes the existing
research work and defines the difference of this research and the goal.

0 Chapter 3 outlines the development process for the project database model. It
introduces some basic concepts in information and database systems and provides the
overview of their development process. The specific model development process for
the project cost and time data is described.

0 Chapter 4 presents the information requirements analysis in the domain system. It
analyzes objectives and functions of project management. The basic storage unit for
cost and time data will be defined. Through the common data unit, some time control
function is integrated in the project cost system. The information requirements and
flow in the cost system are examined. Data elements will be identified and grouped
based on the natural logic relationships among them.

0 Chapter 5 focuses on the conceptual model development and explanation. To design
the cost information system in a database view, the business process of a construction
company is examined into cycles and events by value chain analysis. Business events

trigger the information process. In an ER modeling environment, REA organizes

l4

resource and agent involved around the event to provide an information process
model template. A prototyping database structure is built in MS Access to implement
the conceptual model.

Chapter 6 is the case study. Through the case study, the proposed database view
integrated cost system will be compared with the real practice of construction
company. The proposed system will be used to try to solve some problems of the
reality.

Chapter 7 completes the thesis by discussing the conclusion and the future research in

this area.

15

Chapter 2

Literature Review

This chapter describes the existing research and literature in the topics of project cost
and schedule control system integration, and database modeling in construction

management.

2.1 Cost and Schedule Control System Integration

Traditionally project cost and schedule control systems have been used
independently. Both a Cost Breakdown Structure (CBS) and a Work Breakdown
Structure (WBS) are used in the planning and controlling of construction. The CBS is a
breakdown of all cost elements within a project, both direct and indirect. It's used in the
estimating process and the cost control system (Construction Industry Institute, 1987).
The WBS is a breakdown of the project into tasks which progress is tracked. Based on a
WBS the project schedule is generated to serve as the baseline for time control. Since
these two systems are closely related, research was conducted focusing on the integration

of cost and schedule control systems in order to achieve efficient project management.

2.1.1 Cost/Schedule Integration Problems and Methods

The problem of integration between money and time is a problem of compatibility,
because the cost items are associated with the physical elements of the project. The
SChedule, on the other hand, is a plan of what is to be done and how, and in what

sequence it will proceed. These two types of data are sometimes incompatible and the

16

relationship between them can be: one to one; one to many; many to many (Navon,
1994). On the one hand, a given schedule item may relate to only one cost item. On the
other hand, and more often, a cost item is related to more than one schedule item and
those schedule items can be associated with more than one cost item. Methods to solve
the compatibility problem in this research included manually specifying the resources
associated with each activity; dividing each cost item equally, or proportionally, among
the activities with which it is associated; and assigning each cost item's resources to the
appropriate activity automatically by storing each activity's resource information in an

embedded database (Navon, 1996).

2.1.2 CPM/COST: An Integrated Approach

In "CPM/COST: An Integrated Approach," Sears defined an activity as a unit of work
which takes place in a specific location and has a finite time duration and a cost account
is related to functional items of work such as forrnwork, excavation, and site drainage. He
summarized six ways that cost accounts were matched to activities. It included: (1) one-
to-one relationships of activities to cost accounts, with distribution being uniform; (2)
two or more activities to one cost account with a uniform distribution; (3) two or more
cost accounts to one activity with a uniform distribution; (4) any combination of 2 or 3
above; (5) any predefined non-uniform relationship between activities and cost accounts;
(6) any discontinuous or undefined relationship through field evaluation of progress.

The simplest form of CPM/COST integration is that of an activity which involves
only one cost account and where the cost account is unique to that activity. Because of

the requirement of a large number of cost accounts and the bookkeeping burden, a more

17

beneficial approach is to allow numerous activities to makeup one cost account. Each
actual resource cost was recorded under a single cost account. The important point was
that the activity cost can be forecast for cash flows and cost projections since the
relationships of activity and cost account have been established, and the actual cost for
each cost account is recorded for historical purposes. It is also possible for one activity to
have more than one cost account.

Information such as estimated and actual quantities and costs was stored in
CPM/COST files. The format of these files was that of a variable length computer record,
which stored data including estimated quantity of work, estimated unit costs for labor,
equipment, materials, and subcontracts, as well as actual costs to date, actual quantity,
and activity number. Records were organized by the cost account numbers. This file was
used to produce reports for cost status, cost-forecast, progress cash forecasting, and time-

cost tradeoff (Sears, 1981)

2.1.3 Project Breakdown Model for Integrated Cost and Time Control

To improve the effectiveness of cost and schedule control, researchers are concerned
with the quality, integrity, and timeliness of data that flow through these control systems.
A number of project breakdown models have been proposed to integrate cost- and
schedule—control functions.

In Rasdorf and Abudayyeh's research, They summarized several research in the t0pic
of breakdown project to small unit to integrate cost and time data (Rasdorf and

Abudayyeh, 1991a). The first one is a mapping mechanism between the CBS and the

18

WBS, which the cost in a given cost account from CBS is allocated to one or more
activities (tasks) that relate to that account in WBS by percent allocation.

Noticing that a cost account may relate to one or more activities, and at the same time
an activity may relate to one or more cost accounts, some researchers proposed using a
work-elements concept for integration. A work element was a control account defined by
a matrix of work packages from the WBS and cost accounts from the CBS, as shown in
Figure 2.1. A work element provides a link between the WBS and the CBS, where a cost
account may relate to one or more activities from the WBS, and at the same time an
activity may relate to one or more cost accounts from CBS. Examples are shown in the
work element matrix table in Figure 2.2. The 30cm walls on the first floor are a work
element from the integration of the first floor walls activity and 30 cm walls cost account.
In this model the authors recognized the need for a common denominator for acquiring

and maintaining data for effective project control.

19

 

CBS

Prrliject

 

BEBE!

Project

 

 

Earthwork Foundations Superstructure

 

Floorl Floor2 Floor3 Floor4

 

 

 

 

l
l

Formwork Rebar Pouring
I
Foundations Columns Walls Beams&Slabs
l

 

Site Work Concrete
I

 

 

 

 

Figure 2.1 Work element model

(Source: Rasdorf

and Abudayyeh, 1991a)

'5' (I) (1)—CI)-
<>
"éo n O"

 

 

 

 

 

 

 

 

Activities
First Second Third Fourth
Floor Floor Floor Floor
3 30cm X X X X
5 Walls
§ 250m X x
< Walls
g 200m X
0 Walls

 

 

 

 

 

X: work element

Figure 2.2 Work element matrix table

(Source: Rasdorf

and Abudayyeh, 1991a)

20

In Rasdorf and Abudayyeh's research, they also described another approach
developed by Ibbs and Kim (Rasdorf and Abudayyeh, 1991a). Ibbs and Kim (Kim, 1989)
defined a new element called the basic construction operation required by a design object
(BOD), which is the lowest-level construction task needed to build a specific design
object. Thus, a BOD is an object that provides a linking mechanism between a design
object and its corresponding construction operation control functions, such as estimating
and scheduling. A BOD links to three dimensions: a work package on the WBS, a cost
account on the CBS, and a design object on a drawing. For example, the following five
BODs can be generated for an 20-cm (8-inch) concrete wall object of area A on the
fourth floor: "fabricate formwork", "erect formwor ", "place rebars", "pour concrete",
and "strip formwork". Each BOD is then linked to one cost account on the CBS and one
work package on the WBS. Relevant cost accounts for this example may include "labor
cost for 8—in. walls formwork fabrication", "labor cost for 8-in. walls formwork erection",
"labor cost for 8-in walls formwork stripping", and others for material and labor costs of
placing rebars and pouring concrete. Relevant work packages may include "formwork for
8-inch wall of area A — fourth floor", "rebar work for 8-in. wall of area A — fourth
floor", and "concrete work for 8-in. wall of area A — fourth floor", and "concrete work
for 8-in. wall of area A — fourth floor". This BOD approach ignored data acquisition
support since each BOD is defined at such a refined level that it brings the difficulty of
data acquisition.

Summarized from their work described above, Rasdorf and Abudayyeh proposed a
work-package model based on a project WBS, where a package on the WBS may exist at

a higher level than the actual activity level and serve as a basis for project control

21

functions. Another breakdown structure, the contractor's organization breakdown
structure (OBS), was also recognized by the work-packaging model. This structure
assigns organizational functional responsibilities to each control account established at

the desired level of detail on the WBS (Rasdorf and Abudayyeh,l99la).

2.2 Information Management and Relational Database Integrated in
Construction Management

Information technology (IT) has played a big role in today's business operations. It
has also drawn the attention of researchers in the construction industry (Abudayyeh,
1991, 1993; Betts, 1993; Gibson, 1992; Kartan, 1997; Rasdorf, 1991, 1992; Talonpoika,
at. e1. 1995). From construction process modeling to database concept integration,
researchers have made efforts to use IT to better represent and communicate project
information to achieve the goals of rapid production of hi gh-quality design, fast and cost

effective construction of facility and effective facility management.

2.2.1 COMBINE2 (Computer Models for the Building Industry in Europe) -
Subproject Building HVAC Component Database

COMBINE is a research project focused on developing an operational computer-
based Integrated Building Design System (IBDS). It began with the development of new
methods designed to enhance data-sharing between a number of design applications in
the fields of energy efficiency and building services engineering. This project will enable
better and more efficient building designs, especially from energy conservation, building

quality, and heating and ventilation perspectives. COMBINE2 was the second phase of

22

the COMBINE project. In the COMBINE2 project, the developments concentrated on
establishing a data structure and tools for managing the information exchange in a
building design team. New data exchange methods were developed based on the
international STandard for the Exchange of Product data (STEP) and have been tested.
The objective was to access the architecture CAD, the HVAC-CAD, several thermal
simulation tools, the daylight simulation tool, the costing tool, and two component
databases with the STEP data exchange link to each component and to the common
design database. In the building HVAC component database subproject, the HVAC
component database prototype was implemented. It had a more generic database
structure, which was able to store and manage any kind of component data and integrate
these databases to building design systems. Researchers developed the HV AC component
data model and implemented it with the Access 1.0 relational database. The browser of
the HV AC database was programmed with the Microsoft Visual C-H-. The browser
allows a user to browse, create, edit, delete, search, and export component data. It
enabled links-between any components to different kinds of documents such as text,
graphics and CAD. Selection of one of these links opened the document with the original
software used for making the document. The objective of this project was to build a
generic component data management and storage structures for integration of different
manufacturer's and supplier's databases to facilitate building design practices

(T alonpoika, at. e1. 1995).

23

2.2.2 Integrated Database Systems

In his research, Gibson (1992) declared that there was a need in the construction
industry for more efficient methods of transferring data between various computer
systems and/or project or corporate entities. Data-transfer strategies were defined falling
into two categories, external and internal. External strategies involve improving data flow
by performing translations or format modifications on the data to permit transfer to some
dissimilar hardware or software environment. Internal data transfer strategies involve the
use of central databases that can be accessed by a wide range of project or corporate
users. This researcher proposed an Integrated Database System (IDS) concept to
effectively combine graphical Computer-Aided-Design (CAD) data and traditional
nongraphical data that are utilized for estimating, scheduling, materials management, cost
accounting, and other project-management functions. This research focused on
integrating a 3-D CAD system and relational database systems. Data were captured
during the design phase and were used for different departments within the construction
firm. The data were captured from 3-D model components, which were converted from a
2-D CAD drawing. Takeoff information was generated electronically and used for
material requisitioning, purchasing, expediting, and inventory management (Gibson,

1992).

2.2.3 Integrating Safety and Health Performance into Construction CPM
Kartan (1997) developed a database management system that solicited project-
specific data from the user and provided, as output, applicable safety and health standards

and recommendations. Then this database was integrated into a Critical Path Method

24

(CPM) project scheduling process. This safety database was developed using a relational
database management system (SuperBase) and hypermedia techniquesz. Combining the
capabilities of database with hypermedia techniques provides alternative means of
presenting information. This database was linked to scheduling software such as
Primavera Project Planner, making it possible to automatically transfer safety information
to specific project activities in a CPM schedule. Hypermedia techniques permit dynamic
linkage of separate text or graphic files or other forms of computer data and provide a
different view of safety information. This integrated database/CPM safety system

presented an effective tool to improve project safety control (Kartan, 1997).

2.2.4 An Entity Relationship (E-R) Model of Construction Project Bid Data

Betts (1993) developed a conceptual database model for project bid data. A database
was implemented using a relational database management system and data from a recent
tender. A primary result of the work was an entity-relationship model for construction
tender data. Another result of using a database was the measured improvements in the
flexibility of information retrieval. The range of information included in this research was
that which a building contractor uses and produces while preparing a tender. This
researcher concluded that relational databases offer obvious and significant
improvements in the flexibility with which construction project information can be

retrieved and presented. The conceptual database is appropriate to the dynamic and

 

2 Hypermedia: Technique used in multimedia presentations that allows the user to quickly
move among screens to related subject areas, such as pictures, sounds, animation, or
maps in computerized encyclopedias (Shelly, et. al., 1996).

25

diverse data represented by a construction project tender and that a database in this area

can result in a worthwhile information system (Betts, 1993).”

2.2.5 Related Work in Project Control Area: Design of Construction Industry
Information Management Systems

Abudayyeh and Rasdorf have made a research effort focusing on building an efficient
project information management system. Their research identified work packages on the
project Work Breakdown Structure (WBS) combined with a contractor's Organization
Breakdown Structure (OBS) as the basic structure to establish a common-denominator
control account. Organizational functional responsibilities were assigned to each control
account established at the desired level of detail on the WBS. A schedule and budget
were developed based on work packages. Each account was a depository of control data
that describe the work involved in it.

This research also proposed use of automating data acquisition to improve the quality
of data by eliminating error-prone tasks and processes subject to human errors. Bar-
coding technology was used as an automated data-acquisition tool for identifying and
tracking a variety of information on a construction job site. Bar-coded data items are
provided to the foreman on a sheet. The data sheets were scanned by a hand-held scanner,
and these data were directly entered into the computer.

The process of designing and developing an information management system in this
research was considered to be similar to the development of engineering systems. The
process consisted of four steps including problem definition, conceptual modeling,

computational modeling, and computer modeling. Step one involved identifying the

26

required data items, and describing the behavior of the engineering system with respect to
the methods and mechanisms used in acquiring, storing, and processing data. It was
accomplished by analyzing the work packaging model and the data-collection forms
developed by R. S. Means Company. In step two, the Nijissen information analysis
methodology (N 1AM)3 was used to formally represent the problem formalized by step
one to get the conceptual model. Then the conceptual database model was transformed
into a relational data model. The normalization process to reduce data redundancies was
applied in this step. Finally, the relational data model was mapped onto a relational
database management system.

Their conceptual data model was transformed into data tables. Data about budgets,
definition of WBS and OBS, resource and time consumption are stored in these data
tables. Relational tables that deal with the creation of historical data for future planning

are also created in this database. (Rasdorf and Abudayyeh, 1991a, 1991b, 1992, 1993)

2.3 Chapter Summary

Time and cost management are two important functions within construction project
management. Researchers worked on the integration of these two functions to achieve
more effective management. It's important to know the information reflecting these two
aspects related to one measurable unit of the project breakdown su'ucture. Researchers
analyzed the two sets of project breakdown approaches, cost breakdown structure (CBS)

and work breakdown structure (WBS). They identified the integration problems and the

 

3 NIAM is a natural approach to semantic modeling that produces a conceptual data-base
schema design that is independent of, and can be mapped to, any data-base model.
Semantic modeling involves identifying a set of semantic concepts that can be used to
formally model the real world and to represent the meanings of data (Rasdorf, 1992).

27

relationships between cost accounts based on a CBS and schedule activities based on a
WBS. A work-packaging model was proposed to integrate the CBS and WBS. In this
thesis research, the author will follow this approach to establish the basic units for data
management within a database model. Also, a CSI Master format numbering system will
be integrated in the project breakdown approach in this research.

Database management systems have been applied in the construction field in various
areas. At the bidding stage of projects, a database system improves information
availability and flexibility in retrieving information (Betts, 1993). From the project plan
point of view, an integrated database system where data were taken directly from a 3-D
CAD system facilitated the data transfer to different applications of planning such as
estimating, scheduling, material purchasing (Gibson, 1992). The integration of a safety
and health database with CPM scheduling can effectively improve project safety planning
and control (Kartan, 1997). From the designer's point of view, a more generic database,
which is able to store, and manage any kinds of Heating, Ventilating, and Air
Conditioning (HV AC) component data, can facilitate the designer's practice (COMBINE,
1995). Structured systems analysis and ER modeling for the database design was used in
the contractor's tendering system (Betts, 1993), but not in the area of project management
systems pertaining to cost and time control systems. Abudayyeh and Rasdorf used the
engineering system development approach to design a project information system. They
see the construction project as an example of an engineering system. In this system they
modeled project material, labor, equipment usage and time consuming by using NIAM

data modeling methodology (Rasdorf and Abudayyeh, 1991a, 1991b, 1992, 1993).

28

The subject of this research is conceptual database modeling based on the project cost
system, which is also integrated with some time control functions. The basic idea here is
to perform an information-processing function in the integrated cost system through a
centralized database. Files, documents and procedures for information processing in the
cost system will be redesigned into a database environment. The database system will be
built via a database conceptual model. Conceptual models are tools for representing
reality at a high level of abstraction (Batini, et. al., 1992). So it is important to abstract the
description of all data components and their logical structure of the integrated cost system
in a conceptual database model. Besides resource and time consuming, all the cost
components such as subcontracting expense need to be included in this data model. No
such database model which covered all the cost components has been found in this
literature review.

A cost system is a subsystem of a project management system as well as one process
within the construction company's business processes. From the literature review no
research regarding analysis of construction companies' business process for database
management system design has been found. In this research, value chain and Resource-
Event-Agent (REA) concepts will be used as tools in a business process analysis for the
information system and database design. These two concepts are developed in the general
business management field and will be explained in more details in chapter 5. They have
never been applied in the construction management field in analyzing business process of
a construction company for database system design. The Entity-Relationship modeling
method will be used as the database modeling tool because its readability and simplicity

format is well recognized, and more available in both computer science and information

29

system literature. In this research, a system approach of the information system and
database development process established by both information systems and computer
science researchers will be used to describe the development process of the conceptual
model. It is described in the next chapter to outline the development process of a database

model.

30

Chapter 3

Systems Approach of Database Design

Since database technology began to replace file systems in the mid-1960s, database
design has become a subject studied by researchers both in the computer science and
information systems disciplines. Computer people tend to be concerned with developing
better ways of representing, storing, and processing data by machines. Information
researchers are more concerned about applying computer-based information systems to
the organization to improve productivity and achieve efficient management. They
emphasize the understanding of the structural properties of data that are independent of
implementation details. They study the data modeling aspects of database management
systems, that is, the ways in which data can be organized so they represent the real—world
domain of interest as closely as possible and yet they are still processed by computer. The
systems approach was developed by information researchers for database design and
implementation. This approach will be used in this research for the database modeling of
the project cost and time data. An explanation of this approach will be given in this
chapter to outline the conceptual database model development process for project cost
and time data. System analysis and model development will be presented in the following

chapters.

31

3.1 Computer-based Information System and Database

3.1.1 Computer-based Information System Overview

A system is a group of interrelated components working together toward a common
goal by accepting inputs and producing outputs in an organized transformation process.
An information system (IS) is a set of peOple, procedures, and resources that collects,
transforms, and disseminates information in an organization (O'Brien, 1994). The IS of
an enterprise consists of a collection of activities that regulate the sharing and distribution
of information and the storage of data that are relevant to the management of the
enterprise (Batini, et. al., 1992). Its basic purpose is to make information available for the
right person in the organization. Computer-based information systems use a variety of
computer systems to accomplish the information processing activities. Its basic
components include people, computer hardware, software and data resources (O'Brien,
1994).

People in the IS can be grouped into two different categories: IS end users and IS
specialists. End users are people who use the system in their daily work to produce
information for the business functional operations. IS specialists are people who develop
and maintain the system. They can be categorized into system analyst, computer
programmer, and system administrator. An information system is designed to support the
realization and management of the organizational processes and activities. It builds upon
the business system which has the demand for information. System analysts begin to
analyze the object system from the user view and abstract it to get a logic model of the
system which represents the function of the system in the way a computer can process.

Programmers code the programs for information processing based on output from system

32

analysts. System administrators will take care of system maintenance after the
implementation.

Information systems could be operated manually before the computer age. People
used pen and paper to collect, store, and process data generated during the business
operation to produce information for management needs. Since computers entered into
the daily life of business, no IS can function well without computer hardware and
software. Hardware identifies all the system's physical devices. It includes computers
(microcomputer, rninicomputer, or mainframe) and all their peripherals, which are the
physical devices that control computer inputs and outputs. Peripherals also include any
electronic devices that are used to connect two or more computers, thereby producing a
computer network. Software refers to the collection of programs that instruct the machine
how to work, and procedures that instruct people how to work with computers.

Data are raw facts about physical phenomena or business transactions. Within the
computer-based information system, people use hardware (machines, media, and
network), and software (programs and procedures), to perform inputs, processing,
outputs, storage, and control activities that convert data resources into information
products. Data, programs and procedures work together performing business functions

and activities to produce meaningful information for management.

3.1.2 Database System
3.1.2.1 File System in Information Processing
To generate relevant information efficiently, quick access to the data from which the

required information is produced is needed. Data management, which focuses on data

33

collection, storage, and retrieval, constitutes a core activity for any organization (Rob and
Coronel, 1997). Prior to the use of database management systems (DBMS), data were
kept in computer files on physically large disk drives or magnetic tapes. File processing
systems were used, in which data are organized, stored, and processed in independent
files of data records. For many years, the orientation of information systems had been file
processing (O'Brien, 1994). For instance, in the construction management (CM) field,
project estimates and schedules are kept in estimating and scheduling files. Labor,
material, and equipment usage data are kept in labor and equipment time cards, material
purchase orders and material requisition slips. The quantities of work performed are kept
in progress sheets. Data were stored in these independent data files. The project updating
process consisted of using separate computer programs that updated these independent
data files and used them to produce the documents and reports required by each separated
user application. The independent data file system has limitations in efficiency and
effectiveness for end user applications due to their several major problems. Data
duplication in separated files causes problems in updating and maintenance. Lack of data
integration requires special computer programs to be written to retrieve data from each
independent file. Data dependence requires all data access programs to be changed when

any of the file's data characteristics are changed.

3.1.2.2 Database Management System
The concepts of database and database management were developed to solve the
problems of file processing systems. In the database management approach, files are

consolidated into a common pool of records available to many different application

34

programs. A database is an integrated collection of logically related records and files in a
computer system (O'Brien, 1994). A database represents a change in the way end user
data are stored, accessed, and managed. A database management system (DBMS) is a
collection of programs that manages the database structure and controls access to the data
stored in the database (Rob and Coronel, 1997). A DBMS serves as a software interface
between users and databases. This system helps users easily access the records in a
database, and allows the user to create, maintain, and report the data. In the CM field, if
data about a project schedule, estimate, resource usage, and work progress are stored in
one database, information-processing tasks will be performed in a more efficient and

effective way.

3.1.2.3 Entity Relationship Database Modeling

A conceptual database model is an important concept in a database system. It
describes the structure of a database in a conceptual schema, independent of the particular
DBMS. A typical database model shows all data and their relationships. If the models are
not logically sound, the database designs derived from them will not deliver the database
system's promise of effective information draw from an efficient database (Rob and
Coronel, 1997).

An Entity-relationship (ER)4 approach for conceptual database modeling was
introduced in 1976 and has become more and more popular; several conferences have

been organized on the applications of an ER model to database design and to software

 

4 For more about ER model concepts please refer to Rob and Coronel, 1997; Teby, 1994
and Batini, Ceri, Navathe, 1992.

35

design in general (Batini, et. al., 1992). The basic concepts provided by an ER model are

entities, relationships, and attributes.

Entities represent classes of real-world objects. They are the principal data objects
about which information is to be collected (T eorey, 1994). A particular occurrence of
an entity is called an entity instance. An entity is represented by a rectangle
containing the entity's name in an ER model (Figure 3.1).

Relationships represent real-world associations among one or more entities, and as
such, have no physical or conceptual existence other than that which is inherited from
their entity associations (T eorey, 1994). Relationships are represented by diamond-
shaped symbols (Figure 3.1). A particular occurrence of a relationship is called a
relationship instance or, relationship occurrence.

Attributes represent elementary properties of entities or relationships (Batini, et. al.,
1992). There are two types of attributes: identifiers and descriptors. An identifier (or
key) is used to uniquely determine an instance of an entity; a descriptor (or nonkey
attribute) is used to specify a non-unique characteristic of a particular entity instance.
They are usually represented by a line and a cycle connecting to the entity box or
relationship diamond symbol in ER diagram.

An example of an ER diagram is shown in Figure 3.1. Relationships in entity-

relationship models are described in terms of degree, connectibity, and cardinality.

Degree of a relationship indicates the number of entities associated in a relationship
(T eorey, 1994). A unary relationship exists when an association is maintained within
a single entity. A binary relationship exists when two entities are associated. A

ternary relationship exists when three entities are associated.

36

0 Connectivity is used to describe the relationship classification. It includes one-to-one,
one—to-many and many-to-many relationships between entities (Rob and Coronel,
1997).

- Cardinality expresses the specific number of entity occurrences associated with one
occurrence of the related entity (Rob and Coronel, 1997). It describes the number of
instances within an entity that are related through a relationship. Cardinality is
represented in the ER model by numbers in the parentheses.

These concepts are illustrated below in Figure 3.1. Material and actual material usage
are two entities. Material no., material description and budget unit cost are attributes of
material entity. Charge no., date, and quantity are attributes for actual material usage.
Material no. and Charge no. are key attributes for Material entity and Actual Material
Usage entity. Their relationship is "issue to" which means material is issued to a project
as material actual usage. It's a binary relationship and has one-to-many connectivity.
Cardinality of this relationship is shown in two parentheses. On the material side, 0
means one kind of material can still be in storage and not be used in the project; n means
material can be issued many times to be used in the project. On the actual material usage
side, the two ones mean that each record of the material usage can only record one kind

of material.

37

(O, 11) (1,1)

 

 

 

 

 

 

 

 

 

 

 

Material . Actual
Material Usage
0 Mt] No Charge No.
. . 0
Mt]. Description Date
0 .
Quantity
I.) Budget unit cost

Figure 3.1 Example of ER diagram

3.2 Information System Development Life Cycle:

The information system development life cycle represents the history (life cycle) of
an information system. Database design takes place within the scope of an information
system life cycle. It's difficult to separate database design from the system development
cycle. The process of creating an information system is known as systems development
(Rob and Coronel, 1997). Applying this systems approach, an information system's
development cycle can be summarized as a step-by-step process, with major stages
including: (1) investigation, (2) analysis, (3) detailed systems design, (4) implementation,

and (5) maintenance.

38

Phase

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Tasks
T
System 0 Determine the problems and opportunities

—> Investigation 0 Plan for new system

i . Feasibility study
__) System 0 Existing system evaluation

Analysis 0 Functional requirements

l Data design

P) 1833:?!“ 0 Process design
gn Interface design

I 0 Hardware, software installation
9 mififition 0 Database creation

- 0 Programs coding, testing, & debugging

l 0 System documentation, end user training
—> Wiggles 0 Monitoring, evaluating & modifying

1

 

 

Figure 3.2 The information system development life cycle
Source: Modified from Coronel, 1997

As shown in figure 3.2, several tasks are involved in each phase. The items in the
right column represent the tasks which need to be completed to accomplish each phase.
In the first step, system investigation is an overview of the company and its objectives.
System analysts examine the existing systems to find out the problems and opportunities.
The requirement of a new information system is planned to improve the business

functionality and to contribute to the achievement of the company's objectives. Then a

39

feasibility study is conducted. It determines the technical aspects of hardware and
software requirements, costs, benefits, and feasibility of the proposed system. Problems
defined during the planning phase are examined in greater details during the system
analysis phase. The existing system is also studied to determine how the system activities
of input, processing, output, storage, and control are accomplished. All data flow and
current processes are described and documented, using systems analysis tools such as
data flow diagrams (DFD). More explanation about DFD is given in section 3.3.1.1 and
in appendix A. The result of system analysis should be a better understanding of the
system's functional areas, actual and potential problems, and opportunities. Functional
requirements of a new system are developed, including user interface requirements,
processing requirements, and data storage requirements. Based on the functional
requirements, a systems design specifies how the system will accomplish these
objectives. The data design activity focuses on the design of the database structure and
the files to be used by a proposed information system. System analysts use a
methodology such as entity relationship (ER) diagrams to draw a conceptual data model
for the proposed database. Process design focuses on the design of data transformation
and processes within the database environment. A user interface design focuses on
building the interactions between end users and computer systems. Designers concentrate
on input-output methods and the conversion of data and information between human-
readable and machine-readable forms.

During the implementation phase, the hardware, database management system
(DBMS) software, and application programs are installed. The data model is applied into

a particular DBMS environment, and the actual database is created. Process and interface

I

programs are coded, tested, and debugged until the system is ready to be delivered. After
testing is concluded, the final documentation is reviewed and printed, and end users are
trained. The system is full in operation at the end of this phase.

Once the system is fully implemented and operated by end users, the maintenance
function begins. Maintenance is the monitoring, evaluating, and modifying of operational

information systems to make improvements.

3.3 Database Development Process:

Within the large picture of an information system the database is also subject to a
development process. Research has been committed to this area. Researchers in both
computer science and information systems have focused attention on the process of
designing databases capable of supporting numerous complex applications. They have
noticed the need to design and use conceptual models in the early stage as part of a well-

developed design methodology for the entire database development process.

3.3.1 Two Approaches of Database Design

Much has been written about database design methods, and basically there are two
methodology approaches: one is data—driven, one is process-driven (Maciaszek, 1990;
Raymond, 1987; Nijssen, 1989; 011e, et a1, 1986). These two approaches also reflect the

design philosophy of information system design.

41

3.3.1.1 Process-driven Design

The process-driven design is concerned with how the processes or activities are
performed in the application area. It focuses on applications rather than data. The
processes (business functions) are described first. The direction of data flow, which may
physically be contained in a letter, an invoice, or a report passing between processes and
other components, is also made clear. Data storage locations in the whole process are also
considered. Often, a complex process is refined into several subprocesses. A data flow
diagram (DFD) is usually used as a graphic-diagramming tool to represent the logical
view of the flow of data in a system without specifying the physical devices. Appendix A
shows the symbols used in DFD and a typical DFD. DFDs are usually drawn in
increasing levels of detail, starting with a summary hi gh-level view and proceeding to
more detailed lower level views. Data elements from the data flows and data storage
locations are documented in a data dictionary. The initial database structures are derived

from data flow and data storage locations in the flow.

3.3.1.2 Data-driven Design

The data-driven approach focuses on what kinds of data are stored in the database, as
well as what constraints and relationships between data are applied. The principal design
task is to build a data representation or model of the objective system. The entire focus is
placed on data and their properties. Once the data are identified and structured, data
capture and storage procedures are elaborated so that the resulting database can be. kept
up—to~date. Finally, data access and processing procedures can be designed on demand to

allow decision makers to obtain information in various forms (reports, terminal displays,

42

graphics) and analyze this information (costing, scheduling, performance evaluation)
(Raymond, 1987). ER modeling is a widely accepted technique in the design of

conceptual data base models, which is also to be used in this research.

3.3.1.3 Comparison of Data-driven and Process-driven Approach

Comparing these two approaches, a mixed design environment is sometimes
necessary. If the design starts with processes and derives initial database structures from
the processes, then the method is process-driven. Alternatively, if the design starts with
the determination of data structures that satisfy some desirable properties and refines such
structures once the processes are specified, then the approach is data-driven (Maciaszek,
1990). The data-driven approach builds the data structure on the basis of the general
semantics of information used in the business. If user requirements and business
processes were significantly changed, the redesign of a database derived by the process-
driven methodology is likely to be required earlier than in the case of the database
derived in a data-driven fashion. The database modeling and design for project cost and
time data are considered to be a data-driven approach since the design starts with the
determination of the data requirements, and concern about the data content in a
conceptual database model. The database in which the project data are stored and
managed is the center of the project information system. Data processing programs and
procedures are applied to this database. The data-driven approach is also chosen in this
thesis research because data can be represented in a more generic model without

specifying the particular processes.

43

3.3.2 Database Design Process

Although different terminology is used, the database design process for research
efforts can be outlined and summarized as four separate but dependent phases: (1)
information requirements analysis, (2) conceptual design as view modeling and
integration, (3) logic design, and (4) physical design (Ceri, 1983; McCarthy, et al., 1994;
Rob and Coronel, 1997; Teorey, 1994). In the design of a database for project cost and
time data, the four phases can also be applied as a generic rule. These four phases will be

described in the following sections.

3.3.2.1 Information Requirements Analysis

Information requirements analysis can be simply defined as a procedure which
attempts to determine the present and future information needs of the user community
(McCarthy, at. e1., 1994). In this phase, objectives of the database system are identified.
The scope, which is defined as the extent of the proposed database based on operational
requirements,.is determined. The existing information system is analyzed to find the end
user data views and identify data and their characteristics. The designer examines the
input/output forms and files in order to discover the data characteristics. Business rules
that act upon the data are also reviewed. In this research, a project cost system is analyzed
to define the function and operation mics. Common data storage units for cost and time
data are constructed to provide information for some schedule control functions in the
cost system. Data elements are discovered and classified by analyzing documents, forms,

record formats within the existing system.

3.3.2.2 View Modeling and Integration

The purpose of conceptual design is to describe the information content of the
database rather than the storage structures that will be required to manage this
information (Batini, at. e1., 1992). In this phase, information elements obtained from the
first phase are transformed into a formal representation using standard modeling
methodology. By definition from Batini, a data model is a collection of concepts that can
be used to describe a set of data and operations to manipulate the data. When a data
model describes a set of concepts from a given reality, it is a conceptual data model.
When used in database design, it is a conceptual database model. This model embodies a
clear understanding of the business and its functional areas. Also, it is software- and
hardware-independent.

Conceptual database model development is accomplished using conceptual modeling
techniques, particularly the entity-relationship (ER) model. A basic task here is to
construct a graphic diagram in which raw data from the business operations are captured
in a logical way. Compared with other modeling methods, ER is the most popular and
standard data model, which can be found in most available literature both in computer
science and information system fields. Its simplicity and readability feature also helps
easy communication between database designer and end user. In this research, an ER
model will be used as the modeling methodology for the project cost and time data
conceptual model development. This model presents data in the integrated project cost
system at an abstraction level, and it provides a central storage mechanism for data. It
also reflects a new way to perform information process functions in the integrated cost

system.

45

When the information process in a project cost system is redesigned, value chain
analysis provides a way to examine all activities that a construction company performs. It
decomposes the business process into cycles and events. These events trigger the
information process. REA which represents Resource-Entity-Agent, was first introduced
as a framework to present the accounting information system in a database view
(McCarthy, 1982). In a database environment REA serves as a template to describe the
event information by identifying what happened (event), what resources are involved,
and who (agent) performed the roles. The REA concepts applied here in the model
development process help to identify and implement the business roles in the model.

When the database design is large, and more than one person and one function area
are involved, multiple views of data and relationships result. Integration is a concept
consolidating these views into a single global view to eliminate redundancy and
inconsistency from the model. View integration requires the use of ER semantic tools
such as identification of synonyms, aggregation, and generalization. An example can be
two possible views of a material/equipment database to be merged into a single global
view based on common data about work items in process which consume material and

equipment.

3.3.2.3 Logic Design

Logic design encompasses activities which translate a DBMS-independent conceptual
model into the internal model for a selected database management system (DBMS); such
as DBASE, ORACLE, MS Access. For the relational DBMS, the logical design includes

the design of tables, views, and access authorities. In short, the logical design translates

46

the software-independent conceptual model into a software-dependent model by defining
the appropriate domain definitions, the required tables, and the necessary access
restrictions (Rob and Coronel, 1997). Other output from this phase would include
physical design specifications, program design directions, and guidelines for the database
administrative staff regarding the database security, recovery, consistency and
performance.

In this research, a prototyping database structure including sample tables and queries
based on the conceptual model were set up. But issues regarding the database
administration were not considered since the research purpose here is to analyze the
construction project system and construction business to develop a conceptual data model
for project cost and time data, which is the blueprint of the information generator for

project managers.

3.3.2.4 Physical Design

Physical design is the process of selecting the data storage and data access
characteristics of a database. It decides the location of the data in the storage devices and
the performance of the system. It is not only software-dependent but also hardware-
dependent. Several issues are considered here, including data storage in a single platform
or in a client/server platform, data distribution in different locations, and networking
configurations. Database interface design, application programming for data retrieval,
and integration with other software application are other tasks involved here.

Physical design in the database development process is described as a very technical

job, more typical in the mini and mainframe world than of the personal computer (PC).

47

Even in the more complex mini and mainframe computer environments, modern database
software has assumed much of the burden of the physical portion of the design and its
implementation (Rob and Coronel, 1997). The hardware environment for the prototyping
database structure is the IBM PC. Since this research emphasizes database modeling,
most issues in the physical design part are not included here.

Figure 3.3 summarizes the development process of a project cost and time database
model. The left column shows step-by-step phases in database design process. In the
middle column, tasks that need to be taken in each phase are described. Particular
methods used in this research for project data modeling are illustrated in the right

column.

3.4 Chapter Summary

This chapter provides an overview of the concepts of databases and information
systems and their development life cycle. The emphasis of this chapter is on the
description of the database development process. The project cost and time database
design is considered following in the same process. The purpose is to delineate the step-
by-step process of the project cost and time data model development. The following

chapters will follow the outline here to develop the model.

48

Database design process Tasks Methods used m

 

project data modeling
Information Objicme & SCOPC . Cost and schedule
. . - Busmess model .
Requirements Analysrs development control system analysrs

 

 

 

0 Define data & ° Form analysis
business rules

 

 

 

 

 

 

 

 

 

 

 

View Modeling 0 Conceptual data _
& Integration model development ER model mg .
Value chain analysrs
REA template
. 0 Translate the 0 Prototyping database
Implementation conceptual model into structure setup in MS
Design definitions for tables, Access
and views.
. 0 Define storage . Sample MS Access tables
Physrcal structures, location and applied in IBM PC
Design access paths hardware environment

 

 

0 Interface programming

Figure 3.3 Project cost and time data model
development process

49

Chapter 4

Information Requirements Analysis
in an Integrated Project Cost System

This thesis explores database-modeling issues for project cost and time related data
generated during construction. The purpose is to redesign the data storage system for cost
control and time control to improve the information retrieval for project management.
This research focuses on the information requirements during the project construction of
a typical construction company. This sample company is fully responsible for all
perspectives of the construction. Contractual relationships can be varied, and this
company can be a general contractor which performs parts of the project by its own
forces and subcontracts the other parts. In an alternative way, the company can serve as a
construction management firm to coordinate subcontractors. This database system will
provide an organized and efficient means of measuring, collecting, storing, and
processing data reflecting the progress and status of operations on the project with respect
to cost, resources, and schedule. The conceptual database model represents the data and
operation rules of the project cost system at a high level abstraction. It must embody a
clear understanding of the business and its functional areas. So information requirements
analysis is the important first step to build the conceptual model.

This chapter analyzes the project management system to identify the information
requirements for cost control and time control functions. It starts from a project
management overview. Project management is the process and system applied to
construction projects to accomplish the project effectively. There are several functional

areas within the project management system including cost, schedule, quality, and safety.

50

The emphases of this chapter are to examine information requirements and to discover
the data element characteristics within the integrated cost system in which time related
data are also considered. Basic data storage units for cost and time data will be proposed
for cost and schedule control integration. Data elements and their characteristics will be
identified by analyzing source documents and the traditional information process in a cost

system.

4.1 Project Management System Overview

A construction project is usually a one-time job upon contract award to finalize the
design under budget and on schedule while meeting the quality specifications. The
primary purpose of project construction is to deliver the project according to the design
under budget and within a specified time frame. Project management refers to how best
to implement the construction process which would include proper scheduling,
coordination, and controlled flow of materials and equipment to the job site (Levy, 1994).

Project management usually includes time management, cost engineering, quality
control, resource and safety management. It is implemented by a planning and control
system. The plan provides the baseline and standards against which performance is
measured. The control system monitors the project status and determines where the
project is with respect to the plan, and initiates corrective action if there is a significant
discrepancy. So the project management system is a repeated process of planning,
operation, feedback, and controlling. Before construction, a plan is set according to the
design, the company's historical information, and the project's external factors. When the

operations get underway, the plans become reference standards. As operations continue,

51

external factors such as weather, design changes, labor disputes, and material delays may
cause the course of operations to differ from the plan. Data generated in the operations
are processed to provide information for management about work in place, time, money,
or resources expended. This information processing system includes collection, storage,
analysis, and reporting. Decision-makers use the information adjusted by outside
condition to modify the plan and take action for managing and controlling the operations.
This is a feedback control system, and it operates continuously throughout the life of a
project.

Project cost control is one of the key responsibilities for all members of the project
team, and it provides the analytical methods and procedures for monitoring, analyzing,
forecasting, and controlling costs on a construction project. It also can be expanded to aid
in performing several project management functions, especially the schedule control

system.

4.2 Project Cost System

Controlling the project costs is a concern of every participant in a project. The
objective is to keep project costs within budget and provide information for estimating
the cost of future work. A functioning and reliable cost system plays a vital role in proper
management of a construction project (Clough and Sears, 1991). It is a process of
planning, operation, monitoring, information feedback, and taking actions. Planning
establishes standards of performance. As the work proceeds in the field, cost accounting
methods are applied to determine the actual costs of production. Data are processed into

information to provide the basis for management decisions that are essential to get an

52

action leading to improvements in performance. Resources and production information
are also derived for future estimating.

Traditionally, data are recorded in different source documents such as time cards, and
purchase orders; and are generalized in the job cost accounting system. A database
provides a more efficient way for information processing. A database model outlines the
blueprint upon which to build a project database. In order to develop a database model for
a project cost system, the basic data storage unit needs to be considered. To the simpler
and smaller construction projects, field data can be collected and stored into one unit —
the project itself. Usually, a project needs to be broken down into small measurable units
for planning, controlling and managing data. When time-related data and cost-related
data are collected into the same unit, the cost system is integrated with the time control

system.

4.2.1 Work Breakdown Structure (WBS) and Data Capture Units

4.2.1.1 Work Breakdown Structure

A WBS is a tree hierarchy of the required work to be performed in accomplishing the
end project. Through this hierarchy, a construction project is divided into succeedingly
lower levels of detail, and the bottom level has measurable units in which tasks can be
identified. This management technique is widely used in construction project
management since construction products are totally different from other manufactured
products. Most factory products are produced in clear, visible units such as computers,
cars or lamps, and management emphasizes planning, monitoring and controlling the

production process based on these measurable units. On the contrary, each construction

53

product is usually too broad to be a measurable unit. Each construction project takes
considerable time to complete and is unique as to its design and required construction
methods. A WBS serves as the analysis tool breaking the project into the level where
work can be managed by providing a formal structure which identifies all products and
relates all of the work effort required to meet project objectives (Department of Energy,
1981). The WBS is structured in accordance with the way work will be performed and
reflects the way in which project costs and data will be summarized (Department of
Energy, 1979).

In the project cost and time database, the hierarchy of a project provides a structure
for project data summary and collection. During the planning stage, a time schedule and
cost estimate are developed according to the project WBS. As work progresses on tasks
and resources are employed, management control is applied. Actual project data are
captured based on the planning format and then summarized to provide successive levels
of management with the appropriate report on current and predicted status of the work for

which they are responsible.

4.2.1.2 Cost Coding and Data Capture Unit

Cost coding provides the basic framework upon which a cost system is built. It's a
numbering system of the cost items within a project. Cost items are coded in the cost
coding system which serves as the basic data capture unit in the cost system. A project is
decomposed into units where costs can be calculated and collected. It is also referred to
as a cost breakdown structure (CBS). Actually, a CBS is also one kind of WBS, but it has

a different emphasis and approach for project breakdown into activities for a schedule. A

54

schedule WBS focuses on a breakdown of the project into activities that need to be
performed and progress is tracked. A CBS emphases the elements of a project for
calculating costs. In separated cost and schedule systems, cost and schedule data are
calculated, collected, and maintained separately in cost items and activities. But if these
two kinds of data are managed in one database, the basic data storage unit needs to be
established.

Sometimes the cost items and activities are not compatible. If an estimator has
prepared an estimate for a building project by functional elements, it may be a formidable
task to determine the estimated quantities and costs for each activity. For example, the
functional element superstructure includes such items as concrete formwork, structural
steel, precast concrete, and miscellaneous iron. If only a total cost figure is given for the
functional element, it will be difficult to distribute this cost over activities. To solve this
problem, estimating and scheduling must follow the same WBS structure at the beginning
stage. Then the consideration is to identify specific packages of work referred to as work
packages for cost and schedule control and data collection. These work packages are at a
higher level than the activity and cost items in the WBS. The work-package creates a
unified view of project data by merging the WBS and CBS. It accomplishes the need for
a common denominator in the hierarchy for acquiring and maintaining data for effective
project control (Rasdorf and Abudayyeh, 1992). Each work package is assigned to one
cost account. All aspects of the contractor's management control systems come together
at the cost account including budgets, estimates, schedules, work assignments, cost
collection, progress assessment, problem identification, and corrective actions

(Department of energy, 1981).

55

The Master Format, shown in Appendix B, published by the Construction
Specification Institute is a standard for numbering and classifying sections of a project.
Combining this numbering system with project work packages can provide a standard
numbering project breakdown system where on the bottom level project data can be
collected and recorded. The number for each work item will have 9 digits. First the
project is divided according to the 16 divisions of the Master Format. The second level is
the subsections under each division as shown in Appendix B. These two levels will take
the first five digits. Then the project is continued to be broken down by locations. The
next two digits are for numbering the locations. The next level of the project breakdown
is the work packages named as work items divided according to project elements. Two-
digit number will be used to code this level. .

A sample building project was broken down by this method as an example shown in
Figure 4.1. This is actually a numbering project WBS based on the master format. The
project was broken down and each level was numbered according to the master format.
The first level is in accordance with sections of master format, and the second level is
subsections. Then the project was divided by the locations (floors) and function elements
(slab and beam, walls and columns). The numbers in each level are coded according to
Master Format numbering system. As shown in the example, the code for the cast-in-
place concrete wall work item on floor one is 0330001002. This coding system provides
a numbering system for the project breakdown units as well as their identification in the
computerized information processing environment. Work items which serve as cost

control accounts and basic data capture units are at the bottom level. Each item may

include several activities and different kinds of cost resources. In the example of cast-in-

56

place concrete wall item on the first floor, it includes "pour concrete", "finish concrete",
and "cure concrete" activities, and all cost resources associated with these activities such
as concrete material, labor and equipment. When cost and time data can be captured in
the same unit, the cost system is integrated with the schedule control system. In the
integrated cost system, scheduling plan and estimating budget are required to be done

according to these preset work item units in the early stage of project planning.

57

 

 

WBS system for data storage I

 

 

 

Pro'ect
02000 03000 4000 5000 15000 16000
I I I I I I
SiteWork Concrete Masonry Metals Mechanical Electrical
03100 I 03200 03300
| | l
FormWork Rebar Cast in place Concrete

0330001 0330002 I 0330003 0330004

I | I I

Foundation Floorl Floor 2 Floor 3

 

033000201 033000202 033000203
I I

Slab & Beam Columns

 

 

 

Work item - basic data capture unit
Activities: Pour concrete
Finish concrete
Cure concrete
Costs: concrete material, labor,
and equipment

 

 

Figure 4.1 WBS numbering system of data storage units

58

4.2.2 Cost Plan

For control of project costs, there must be a cost plan against which cost performance
is measured. The cost plan may be considered to consist of two major divisions: schedule
and estimate. Since the cost system here is the cost/time integrated system, the schedule

is considered as one part of the cost plan.

4.2.2.1 Schedule

Usually a construction project has a fixed time frame, in which project managers need
to manipulate large numbers of operations to complete the project. The planning
techniques of the Critical Path Method (CPM) draw all operations of the project in a
network diagram based on job logic and restraints. Once the network has been developed,
the time element is introduced into the planning diagram to form a work schedule. This
project schedule is a projected timetable of construction operations that will serve as the
principal guideline for project execution (Clough and Sears, 1991). Schedule
development involves a series of steps. The major steps include identifying activities,
developing the logic relationships, determining a time duration for each activity, and
calculating critical path time. The scheduling progress begins by dividing the project into
several different detail levels according to project WBS. Basic schedule activities are
identified and then the project time data of these activities will be generated. These major
data items include planned duration, starting time, finishing time, and float time. They
reflect the planned project time information. These schedule activities and their
associated time data can be summarized to the higher level units which become work

items for data storage.

59

4.2.2.2 Estimate

A project estimate establishes the cost projection and control baseline for
construction. Along with the design-construction processes, development of estimates
proceeds from a conceptual view to more detailed information. A conceptual estimate is a
prediction of the total anticipated cost to the owner. An estimate generated in the detailed
design stage is an aid to predicting the project bid price. The most detailed estimate is
prepared by the general contractor. In the integrated cost/time control system, work items
are the basic units on which quantity take off and prices are based. The contractor uses
this estimate in preparing a bid. Once the bid has been accepted, the estimate serves as a
budget which sets realistic goals for company personnel to accomplish. Quantities,
manhours, unit costs, direct dollars, and other resource expenditures to perform the work
are defined by the unit of work items. Major data items in the budget are quantity,

productivity, resource budget usage and unit costs.

4.2.3 Cost Control

4.2.3.1 Project Job Cost Accounting

The primary objective of a project cost system is to keep the construction costs of the
project within the established control budget. As the work proceeds in the field, cost
accounting methods are applied to determine the actual costs of production. The costs as
they actually occur are compared with the budget. This is the way to find problems and
take actions. Project job cost accounting is the key ingredient in the project cost system.
It provides the basic data required for both cost control and estimating (Clough and Sears,

1991). It performs the function of information processing.

A project job cost system is the set of forms, procedures, and communication
channels used to document project information, process it, and then structure it for
project management decisions (Adrian, 1979). It's part of an accounting system, but
differs substantially from financial accounting. Financial accounting is concerned with
tracking accounts receivable, accounts payable, generating payroll, and maintaining good
bookkeeping. Its structure is in accordance with accepted rules and procedures of
accounting and outside requirements. Its objective is, in great part, preparing a financial
statement. Job cost accounting belongs to managerial accounting, which is more
concerned about cost control and how to allocate costs to the specific product produced.
Cost accounting involves the continuous determination of productivity and cost data, the
analysis of this information, and the presentation of results in summary form. As part of
the accounting system, a job-cost ledger is kept to record usage of labor, material,
equipment and other resources, and to record indirect cost expenditures.

Construction cost accounting is concerned not only with costs, but also with man-
hours, equipment-hours, and the amounts of work accomplished (Clough and Sears,
1991). These data are collected and analyzed for cost control and estimating. Figure 4.2
illustrates the data collection and information process procedures in a job costing system.
In the first rounded rectangle, the project estimating process sets up the budget baseline
for field construction. During the construction process shown in the top dotted line
rectangle, field data are collected through a set of forms and procedures of a job cost
system. Direct labor, direct material and other direct costs are identified and posted to
job—cost ledgers. The source document for assessing a project's labor cost is the timecard.

The material purchase invoices are the source documents for identifying material costs.

61

Besides cost data, quantity of work performed by work item is recorded periodically in
progress sheets to reflect the project progress. Then these field data are processed in a
cost and productivity analysis and compared with the budget. Information generated from
this process is summarized to provide management reports for control and feedback for
future estimating. These analysis and feedback processes are shown by the other two
rounded rectangles and arrows in Figure 4.2. In the mean time, cost data are collected

from the job cost ledger to the expense accounts in the financial accounting system.

62

 

[ Project Estimate ](

 

Productivity and other data
feedback to estimating database

 

 

Management
Reports

 

 

 

Pro ect Construction and field data collection

 

 

 

 

 

 

 

 

 

 

 

A

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Labor Material Other Direct Progress
Timecards Invoices Expenses Sheet Y
A B C D Cost & Productivity
I____ \ Analysis
Financial
Job-cost Ledger Accounting System
Debit Credit Income Balance Sheet
Statement
~—>A [Liabilities
B Revenue
C *‘J Assets
) Expenses
I5.
Net Worth _) orth

 

 

 

Figure 4.2 Data collection and information processes
in a job cost system

63

4.2.3.2 Source Documents for Data Recording

As mentioned above in section 4.3.3.1, job cost accounting has the set of forms and
procedures to document project data. Management reports are produced from these
documents to give information feedback to project managers for their decisions. Job cost
accounting serves as a data recording and information processing system in the project
system. No matter whether the information process within this system is manual or
partially computerized by some computer software, it is still a file system. Data are
entered into a series of forms and then stored in forms, reports and other documents. Data
requirements are expressed with a variety of formats. To design a database system based
on this file system, data requirements need to be examined from these documents. These
basic documents include progress sheets, manhour time cards, warehouse requisition
slips, equipment timecards, and subcontract progress payment claim sheets. These
documents are forms which are predefined paper modules used to collect data, and to
exchange data among users (Batini, et. al., 1992). So the data requirement analysis begins

from the form analysis to identify and classify data elements.

4.3 Information Requirements from Form Analysis

The first task in form analysis is to understand the structure and meaning of each
form. Information expressed in forms is obtained by subdividing forms into areas. An
area is simply a portion of the form that deals with data items closely related to one
another (Batini, et. al., 1992). Each form is partitioned into pieces of similar complexity.
Data elements are discovered from forms and classified by areas. Entities in the ER

model can be abstracted from these areas. So form analysis represents the first step

toward a conceptual model for database design. Analysis of the basic forms in a cost
system is presented in the following sections. Data elements in each example form are
grouped into areas. These areas are used to derive abstraction level entities for the ER

model.

4.3.1 Progress Sheet

Progress is one of the most important components of successful cost control. A
project that runs ahead of schedule may be over budget and lacking in quality. On the
other hand, where costs run below the estimate the project may be behind schedule. Only
when the progress on a project is on schedule and within the estimated cost for the work
done is the project performing satisfactorily. Progress information is periodically
collected from the site and recorded in the progress sheet. Table 4.1 shows a typical
progress sheet used by a construction company. In this sheet, project name and recording
data are presented in the title part. Quantity of work performed is recorded daily in each
work item. work item descriptions are under the column titled by work item. The work
item codes are presented in the second row. This progress sheet provides data on a daily

basis to document actual work which has been performed.

65

 

NEWFOUNDLAND CONSTRUCTION CO.LTD.

 

 

PROJECT: Mgel Vamu_ard DATE: W
DAILY PROGRESS SHEET
Quantity of work performed by work item code
Work Item
BB 03100 BB 03200 BB 08300 Remarks

 

Formwork (mg) 83.4

 

Rebar (tonnes) 1.58

 

 

 

 

Concrete (m3) 459]

 

 

 

 

 

 

 

Table 4.1 Job progress sheet
(Source: Ahuja, 1980)

4.3.2 Manhour time card

Labor cost forms a major portion of the total cost for many projects and is one of the
most sensitive costs to manage. A day-today reporting system provides a measure of
performance to compare with the estimate. One of the basic reports in the traditional file
system is the time card. A typical time card is shown in Table 4.2. The time card is used
for a foreman to report his manpower allocation in a standard uncomplicated format.
This format uses predefined numbered work items. Manhours are allocated to each work
item daily. Manhour time cards, together with the progress sheets, can provide
information about productivity analysis, unit labor cost, and manhour status. The time
card contains three areas which contain closely related data items to describe similar
information. The first area is a group of data items about actual labor usage. The second
one is an area about labor information including employee number, name, craft, and

hourly wage rate. The other one is an area indicating the information about the

66

superintendent who is in charge of these workers. These areas and their correspond data

items are shown in Table 4.3.

Name: Motel Vanguard MANHOUR TIME CARD Date: 1980 02 04
# m Report # M
Page # 1

Work item / hours
Employee Hours Pay Amount
(5)

Name

R. Jones 92.

 

Table 4.2 Manhour time card
(Source: Ahuja, 1980)

 

Table 4.3 Data areas and their items derived from manhour time card

67

4.3.3 Material Requisition Slip

Material cost is one of the most important elements making up the total project cost.
A material requisition slip as indicated in Table 4.4 is filled out by the superintendent, or
an authorized individual, before the material is used in the project. Through this slip,
material is issued to the project and recorded according to work items. This form contains
three areas grouping related data items. The first area is information about material. The
second area is about material actual usage. The third one is the data area about the
superintendent who is authorized to receive the material items. These data areas and the

group of data items in each area are summarized in Table 4.5.

 

NEWFOUNDLAND CONSTRUCTION CO. LTD.
MATERIAL REQUISITION SLIP
Req. No. 1432

STOREKEEPER: Please Furnish Mooney Construction with the following:
CHARGE NO. fl; DATE: 1980 02 21

WORK ITEM: BA031 10 Dept. Construction

Quantity Articles Stock No. Unit Cost Amount
200 Timber 5cm x 10cm x 3m 201 $1.50 $300.00
300 Timber 2.50m x 15cm x 6m 468 $2.00 $600.00

ENTERED ON ENTER ON STORE KEEPER RECIPIENT
Ledger Stock Ledger
293 154 R. Gorman L. Vivian F

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

T
o

‘L . y ‘I'
Actual Material Material Superintendent
usage data area

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Table 4.4 Material requisition slip
(Source: Ahuja, 1980)

68

 

Area Data items recorded

 

Material Material stock no., Unit cost, Description

 

Actual material usage Charge No., Quantity, Date

 

Superintendent Recipient name

 

 

 

 

Table 4.5 Data areas and their items derived from material requisition slip

4.3.4 Equipment Time Card

Equipment usage in a project usually depends on the type of project and the methods
of construction that are adopted. The information required on the use of equipment is in a
format very similar to that required for manpower. An equipment time card is the basic
form to record data. It normally contains machine description and/or number, hourly
rates, total dollars, and special conditions (breakdown or idle). Actual working hours are
classified by work items. An example of a typical equipment time card is shown in Table
4.6. Three groups of data items can be identified from this form. One is equipment
information, the other one is equipment actual usage by work items. And the third one is

the project information. These areas and the data items are summarized in Table 4.7.

 

 

 

 

 

 

Area Data item
Project Project No., Project name
Equipment Machine no., Machine name, Standard rate, Overtime rate,
Idle/Repair rate
Actual equipment Actual hours, Date, Actual cost
usage

 

Table 4.7 Data areas and their items derived from equipment time card

69

 

 

 

Project general
information

 

 

i

l

 

 

Equipment
information

 

 

A

 

I
1

WEST AMERICA CONSTRUCTION COMPANY, INC.

WEEKLY EQUIPMENT TIME CARD

Project: Mountaintown Warehouse
Week Ending: Aug. 23, 1980 Project No.: 83WH04I

-----------------------------------------------

--.-.4

Machine: CAT977
E Machine No.: lg Rate ST $84.80/hr.‘

I

l

!
m

5 Idle/Repair: aim/hr. or $69.20/hr.§

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Table 4.6 Equipment time card
(Source: Modified by Barrie and Paulson, 1992)

70

Standard Work item Total Hours
Day Time(ST) Total Weather &
Over F10- SOl- ST. OT. Repair Idle Cost Comments
Time(OT) 02220 02222 - =1=
ST : 8 I" 8
Mon. OT : 678.4 Clear, 85
ST E 6 6 2
Tue. OT : 573.6 Clear, 80
I
ST E 8 8
Wed. OT I 67 8.4 Cloudy. 70
ST : 8 8 E
I .
Thu. OT . 678.4 I 75, Light
E I wrnd
ST : 8 I
. I
Frr. OT E 2 816.8 I 80, clear
Sat. : E
Sun. OT 5 E
' E
Total Hours E 22 18 38 2 2 iPrepared by:
Total Cost : 1865.6 1495 42 3425.6 3'0 P “15°“
l I
l
I
Y
Equipment
Actual usage

 

4.3.5 Subcontract costs

Subcontracts cost— similar to manhours, materials, and equipment cost — form a
varying percentage of project costs, depending on the type and complexity of the
construction and the prime contractor's preferences. Subcontractors' progress payments,
subject to approval of the contractor's authorized field person, are recorded and
maintained to control expenditure spending for subcontractors. An example of a
subcontractor's progress payment claim is shown in Table 4.8. Three groups of data items
can be classified as areas. The first area is subcontract information. The second one is
data items about change orders for the subcontractor. The third area is information about

the subcontractor's progress payment. Table 4.9 shows these areas and their data items.

 

Area Data items

 

Subcontractor Subcontractor name, Work description, Contractor price

 

Sub's progress Claim no., Date, For period, Amount, Work description
payment

 

 

Sub's change order Change order no., date, change amount

 

 

Table 4.9 Data areas and their items derived from
subcontractor progress payment claim

71

 

.- ----- ‘> information payment

 

 

Subcontract Sub's progress

 

 

 

 

 

 

A

 

AWE-T114--
L-EB-QG-BES-S- Bartram-gum NO. 4

JobName: swan conslnlction : Date: 19800817
.Eetiogd-f - 1980 .0-1-01 .19-19800815 -

 

 

 

 

' Description of Work: Foundation I

Subcontractor: Mooney Conaruetion

 

 

_l

 

Original Change Orders New Contract Value of Work Completed to
Contract Price to Date Price Date

 

 

 

$69,000.00 $7,000.00] $76,000.00| $59,000.00

--------------
flange Orderl I Net W '1'
No. Date Increase Decrease Contract Price

   

 

 

 

419 Jan. 22 $4,500.00 $73,500.00
420 Feb. 2 $11,000.00 $84,500.00
421 Mar. 1 $8,500.00| $76,000.00
TOTAL $15,500.00 $8,500.00 $76,000.00I E-I

 

 

 

 

 

 

 

 

 

i3"'rsis1=ayrriém‘naoord [
Previous [This Period fio Date |
Total Amount Claimed $50,000.00 $10,000.00 $60,000.00
Hold Back (10%) $5,000.00E $1,000.00] $6,000.00l
Net P Due $45,000.00 $9,000.00 $54,000.00

 

8%me
Verified and Approved by. Graham Nosewortm '

 

 

 

 

i !—
Sub's progress V
payment

 

 

Sub change order
information

 

 

 

 

 

 

Table 4.8 Subcontractor progress payment claim
(Source: Ahuja, 1980)

72

From the above analysis, data element groups are classified from forms as areas.
These areas reflect an abstraction level of data elements. They could serve as candidates

to be used as entities in an ER model diagram.

4.4 Chapter Summary

In this chapter, a project cost system was analyzed. Numbering data storage units
based on work packages derived from a project WBS and master format was proposed.
Each work item served as a data capture unit to collect both cost data and time related
data. Through the common data capture unit, the cost system was extended to perform
some schedule control functions. Data collection and information process in a cost
system were analyzed. Then information requirements in this integrated system were
examined from form analysis for the database design. Data elements in each form were
identified and classified by dividing form structure into areas. This classification of areas
as data element groups established the approach toward the abstraction of entities for the
conceptual database model development. In the next chapter, the conceptual database

model for project cost and time data is formalized.

73

Chapter 5

REA Database Model for Project Cost and Time Data

Information requirements in a project integrated cost system was analyzed in Chapter
4. Work items were coded according to a project WBS and proposed as cost and time
data storage units. These units also provide an integrated point for time data to store in a
cost system. After data elements are identified and classified in an integrated cost system,
the next step is database model development for cost and time data. In this chapter, the
database model is developed and explained.

Entity-Relationship (ER) is used as the database modeling method to develop the
conceptual model for project data. It organizes data elements identified in chapter 4 in
terms of entity, relationship and attribute. It is critical to fully understand the business
process of a company in order to evaluate and reengineer an information system. First, a
construction company's activities are examined from the value-chain point of view.
Project construction is viewed as a conversion cycle in which resources are converted
into goods and services for sale. Within each cycle, business events trigger the need for
an information process to record, store, analyze and retrieve event data. A database of the
project data is built to satisfy this need. In the database environment, REA as a
generalized entity-relationship representation, which consists of sets of resources, events,
and agents and which serves as a template in the modeling process. From a business
event as the beginning point, an REA pattern considers what (resource) and who (agent)
are involved in the event. It identifies and represents the essential data characteristics of

business processes and events in a set of events, resources and agents within a database

74

environment. After the database model has been developed, sample tables and queries in
an MS Access DBMS are built to reflect the idea of a database view integrated cost

system.

5.1 A Construction Company and Value Chain

5.1.1 Value Chain Concept

The Value chain concept was first introduced by Porter, 1985. It provides a
systematic way of examining all activities that a firm performs and how they interact in
analyzing the sources of the company's competitive advantage. Later this concept has
been extended to be used in business reengineering for Architecture/Engineering]
Construction (A/E/C) industry and information system analysis (Choi, 1994; Hollander,
et. al., 1993). In Porter's original definition "every firm is a collection of activities that are
performed to design, produce, market, deliver, and support its product." All these
activities are represented using a generic value chain, shown in Figure 5.1. These
activities are divided into two categories, primary activities and support activities.
Within each category, activities are further classified into different groups. Primary
activities are directly related to product production and sale. They can be grouped further
into groups such as operations, logistics, marketing and sales and service for operations.
The other category includes activities that do not directly contribute to product
production and sale but they are also one important part of a firm's daily business to
provide support functions. They can be separated into firm infrastructure, human resource

management, technology development, and procurement groups. After all activities are

75

classified within the generic value chain, they can be examined into details to find the

added value to the products and thus its value margin to the firm.

 

FIRM INFRASTRUCTURE

 

HUMAN RESOURCE MANAGEMENT \\

 

 

SUPPORT ACTIVITIES
A

 

 

TECHNOLOGY DEVELOPMENT \\
t PROCUREMENT
INBOUND OPERATIONS OUTBOUND MARKETING
LOGISTICS LOGISTICS & SALES SERVICE

 

 

 

 

 

 

PRIMARY ACTIVITIES

Figure 5.1 The generic value chain
(Source: Porter, 1985)

5.1.2 A Construction Company's Value Chain

In the later extension (Hollander, et. al., 1993), the value chain concept has been used
in business process analysis for information system design. The business processes define
how an organization creates value. They are the activities associated with providing
goods and services to customers. Regardless of the type of goods or services provided,
each organization has three types of business processes:

1. Acquisition/payment process — acquiring, maintaining, and paying for resources

needed by the organization to provide goods and services.

76

2. Conversion process — converting the acquired resources into goods and services

for customers.

3. Sales/collection process — delivering goods and services to customers and

collecting payments (Hollander, at. e1., 1996).

Furthermore, a business process is divided into a discrete, related series of business
events. When business events are executed, they trigger the need for an information
process to record business event data.

Business, in general, has been decomposed into acquisition/payment, conversion, and
sales/collection processes. When applied to the construction industry, the basic process
will be similar, but the events within each process and the methods used to convert
resources into goods and services for customers differ significantly. The goods and
services delivered in the construction industry are facilities, such as buildings, roads,
power plants, airports, and dams. The generic value chain process of a construction
company is shown in Figure 5.2. Three business processes of a construction company are
shown in the circles. Besides acquiring human resources, financing, property, and plants,
a construction company will also acquire labor, material, equipment and subcontract
services. These acquired services and resources are inputs in the construction process.
Each construction project is a conversion process for one or a group of facilities. Project
management in construction is the process to organize, plan, control and deliver a facility.
The sale and collection process in a construction company is completed by the way of
period progress payments during the construction. Outside the company, customers and
suppliers are also involved in the company's business process. In the construction

industry, these outside participants are facility owners, suppliers and subcontractors.

77

Suppliers and subcontractors participate in the company's business by providing
resources and services and by getting paid. Owners are involved in the process by
receiving their facility and paying for it. The arrows in Figure 5.2 illustrate money,
resources, and products flowing through the company's process and its outside

participants.

. Suppliers/ Facility
l Subcontractors l Owner

 

 

 

 

 

 

 

 

 

 

 

Equipment, Payment for Process Facility

Materials, Labor goods and payment for construction

Construction Services services facility in progress
CONSTRUCTION COMPANY

   

 
  

 

 

 
    
   
 

Acquire, Labo ) Pro'ec t > Pro
subcontract, r, J . Facility gress
and a for matenal, construction , Payment

P Y equipment, construction
& subs m progress
& complete

acquired

 

 

 

Figure 5.2 Business process in a construction company
(Modified from Hollander, et. al., 1996)

Information processes are shaped by an organization's business and management
processes (Hollander, et. al., 1996). A value chain provides a way to decompose an
organization into processes and events. Each event triggers a recording process. Record
maintenance creates the need for a repository of data that documents and describes an
organization's business processes, including the events, people and resources associated

with the processes. This data repository which is a collection of data, is used to generate

78

output for information customers. For example, subcontracting parts of the project's work
is an example of a business event; recording data about the subcontract is an information
process. Installation of the formwork of the first floor wall is an example of event; while
recording the actual data about start and finish times for this installation is an information
process.

A value chain analyzes the business processes within an organization into separated
cycles (processes) and down to events. It provides a business model on which a database
system can be built. Data generated in the business process are identified and recorded

and then associated with the events analyzed from the value chain.

5.2 REA Concept for Integrated Cost System Redesign

As described in Chapter 4, a job cost accounting system records and processes project
cost data. It is part of a traditional accounting information system serving the cost control
function, and providing information for future project planning. In the process of design
of an accounting information system integrating with computer database technology,
REA framework as an 'occurrence template' was developed using database modeling for
accounting transactions (Geerts and McCarthy, 1991). When referring to accounting as
the language of business, REA provides an alternative model language to organize
business data. It organizes entities and relationships of the data model into a template of
three elements that are resource, event and agent.

0 Economic resources are defined to be objects that are scarce and have utility and

are under the control of an enterprise.

79

0 Economic events are defined as a class of phenomena which reflect changes in
scarce means (economic resources) resulting from production, exchange,
consumption, and distribution.

0 Economic agents include persons and agencies who participate in the economic
events of an enterprise or who are responsible for subordinates' participation.
(McCarthy, 1982)

Stock-flow relationships connect the economic resource and events. Resource stock
flows to economic events and is consumed there. In the mean time, events are controlled
by agents. A duality relationship links events when one event is increasing the resources
of an enterprise and the other is a corresponding event involved with resource decrement
(McCarthy, 1982). A typical example to reflect the duality relationship concept is the
relationship between "purchase" and "cash disbursement". "Purchase" fills the increment
role which increases a company's raw material, supplied parts and other resources. "Cash
disbursement" decreases the company's money resource. These relationships and the
resource-event-agent (REA) elements are shown in a simplified REA template in Figure
5.3. The symbols used here have the same meaning as they do in an ER model. Examples
are given for resource, event, and agent in the entity rectangles. It is a simplified REA
pattern because duality relationship is not illustrated in this pattern. Duality relationship
in the conversion cycle of project construction is presented by the relationship between
"actual resource usage" and "actual work item in progress" events. Actual resource usage
reflects the decrement of resource owned by “a company. Actual work item in progress is

an event representing actual work done in the field. What the work has done in a project

80

is the increment of the construction product (project), which can be viewed as a
company's resource also.

After the data elements concerned within the integrated cost system are identified,
development of a data model would continue by trying to characterize those items in
semantic terms (that is, in terms of entities and relationships). With the REA as a
template, this characterization process would proceed to identify economic events,
resources, agents, and their data attributes. An REA provides a template to help system
designers think about the information process of a company. Events are considered as
critical information elements and as base elements of the conceptual schema (McCarthy,
1982). Then, what and who are involved in this event are considered as resource and
agent. Data generated during a business process are recorded around an event, resource

and agent template.

 

Economic Resource

 

 

 

Material, labor, etc.

 

 

 

Economic Event Economic Agent

‘ control
Actual mtl. usag£_ Superintendent

 

 

Stock flow

 

 

 

 

 

Figure 5.3 A simplified REA example

5.3 REA View for Project Cost and Time Data
What is proposed here is the database view of an integrated cost system.

Traditionally, cost data are entered and recorded in a job cost accounting system.

81

Information that project managers need is derived from cost accounting forms and
procedures. Through carefully defined work items, time related data could also be
recorded in cost accounting forms. When this system is redesigned in a database view, it
reflects a new way to record, manage, and retrieve data. Data are mapped in a conceptual
data model to derive the logical structure of the cost system. In the mean time this model
can be further processed into a computer storage structure of database management
systems. The list of data elements concerned with processing of information required in
an integrated cost system have been identified in chapter 4. The conceptual database
model is developed here by using the entity relationship (ER) modeling method. It
focuses on description of the information content of a database rather than storage
structures in a computer. REA provides a template method of discovering an enterprise's
business process and events and organizing them into a database environment. Then, the
ER model is built around a major business event. Several events are identified such as
actual work items in progress, actual labor usage, actual material usage, actual machine

usage and subcontracts.

5.3.1 REA Template to Model Identified Events

5.3.1.1 Material Actual Usage

The REA pattern of material actual usage records actual material use in a project.
Material, described by the material entity, is issued to actual usage entity. The
superintendent is in charge of the material actual usage. A simplified REA pattern with
resource (material) flowing to the actual usage event is shown in Figure 5.4. The

superintendent is involved as an agent to be in charge of material actual usage. The

82

cardinality in each relationship which is Shown in the parentheses in Figure 5.4 is
explained as follows. One kind of material can be used many times in the same project. In
the mean time, there is only one kind of material contained in one actual material usage
event. One superintendent can issue material usage to the project many times. Actual
material usage must be authorized by a superintendent.

(0,11) (1,1)

 

 

Material . Actual
Material Usage

 

 

 

 

 

 

 

 

Superintendent

 

 

 

Figure 5.4 Simplified REA pattern of material actual usage

5.3.1.2 Labor Actual Usage

Labor is viewed as an economic resource in the REA template here since it is under
control of the company and consumed to the project through labor hours. Labor is not
viewed as an agent because it is not responsible for a subordinate's participation. The

labor usage process is similar as the pattern of material usage. It is shown in Figure 5.5.

83

 

Labor

 

 

 

 
    

 

Actual
Labor Usage

 

 

 

 

 

Superintendent

 

 

Figure 5 .5 Simplified REA pattern of labor actual usage

5.3.1.3 Equipment Actual Usage

The process of equipment usage in a construction project is similar to labor usage.

Equipment actual usage is controlled by operators. 80 operators are involved in this

process as agents. As a resource, direct consumption of equipment is calculated by

equipment time spent on the project. The simplified REA pattern for equipment is shown

in Figure 5.6.

 

Equipment

 

 

 

 

 

Actual
equipment Usage

(1.1)
Control
(on)

Operators

 

 

 

 

 

 

 

Figure 5.6 Simplified REA pattern of equipment

5.3.1.4 Subcontract

Subcontracts usually form a varying percentage of project work, depending on the
type and complexity of the construction and the prime contractor's preferences. So it's
also an important event in the project construction process. But the difference is that it is
not performed by the company's own forces. It represents the company's outsource
process. As an outsource event, subcontractors are external agents involved in this
process. The resource here is the progress payments as the money resource flows out to
pay for this service. The part of work items of a project done by subcontractors can be
seen as the in flow resource that the company gets through the subcontracting event. In
the database modeling process, if an entity's existence depends on the existence of one or
more other entities, it is said to be existence-dependent (Rob and Coronel, 1997). This
concept explains the relationship between subcontracts and sub's change orders.
According to the data requirement analysis, data elements about subcontractor's change
orders need to be classified in one group to provide information for management during
the construction period. This group of data elements can be identified as the change order
entity. A subcontractor's change orders refer to its related subcontract. They exist only
when the subcontract exists so the subcontractor's change order entity is a dependent

entity of a subcontract entity. The subcontractor part of the data model is shown below in

figure 5.7.

85

 

Work Item
Budget

 

 

 

(0.n)

Done by

(0.11)

 

Subcontractor

 

 

 

(0.11)

(1.1)
Sub

 

 

Contract

 

 

 

 

 

Progress
Payment

 

 

 

 

Sub's Change
Order

 

 

 

    
 

Supplement by

Figure 5.7 Simplified REA pattern for Subcontract

5.3.1.5 Actual Work Item in Progress Aggregation

An abstraction is a mental process that is used to select some characteristics and

properties of a set of objects and to exclude other irrelevant characteristics. Aggregation

is one type of abstraction concept used in database modeling. It defines a new class from

a set of other classes that represent its component parts (Batini, et. al., 1992).

Aggregation is described as a "part-Of" relationship; for example, wheel, pedal, and

handlebar are all parts of a bicycle. The aggregation concept is illustrated by the relation

between the actual work item in progress and actual labor, material, and equipment

usage. Each work item may have material, labor, and equipment actual usage. Labor,

86

material, and equipment usage are component parts of each work item. This relationship
is shown in Figure 5.8. As explained in section 5.2, the relationship between these
resources usage entities and the work item in progress entity also reflects the duality
concept in an REA pattem.DWhile resources are decreased by consumption in the project,
(construction product), a facility is built which is an increment of another kind of

resource in the company (products).

 

 

 

 

 

 

 

Actual Work
Item in Progress
(0.11) ’ (0.11)
(On)
has has
(1,1) (1.1) (1.1)
Actual Actual Actual
Material Usage Labor Usage Equipment Usage

 

 

 

 

 

 

 

 

Figure 5.8 Actual work item in progress aggregation

5.3.1.6 Actual Work Item in Progress and Work Item Budget

The entity of an actual work item in progress is for recording work performed in the
field. No matter whether these items have been finished or are still in progress, they
should correspond to the budget of each item. So there is a relationship between work in
progress and its budget. This relationship reflects the items in a project that are performed
by a general contractor's own forces. It is modeled by an actual work item in progress and
a work item budget with a one-to-many relationship Shown in Figure 5.9. It shows each
budget work item that is needed to be done in the field and that actual work item in

progress data can be recorded several times corresponding to one budget work item.

87

 

 

Work Item
Budget

Actual Work .
Item in Progress

 

 

 

 

 

 

 

Figure 5.9 ER model for Work item in progress and budget

5.3.1.7 Work Item Budget and Its Components: Material, Labor and Equipment

Material, labor and equipment are basic cost components of each work item. So
relationships exist among labor, material, and work item budgets. Each work item may
contain several kinds of material or may consist of only labor and equipment without
material consumption. Each kind of material may be used in one work item or in many
work items. A many-to-many relationship exists between material and work item budget
entities. This relationship has it own attribute to record quantity of each material type for
each work item. During the logic design phase, this relationship needs to be transferred
into a data table to store this information.

Each worker's personal information such as name and address are recorded as
resources in a labor entity. These workers can be further classified according to different
craft types. A labor type entity is required for labor type information such as hourly rate,
worker availability and number of each craft. Each work item may include several types
of crafts to become a crew. For example, placing concrete may include one foreman, one
concrete mason, four laborers, one operator, and one carpenter. So the relationship
between work item and labor type represents a labor resource component in each work

item. This is a many-to-many relationship since each labor type may be included in many

88

work items and each work item may include several kinds Of craft. This many-to-many
relationship also has its own attribute such as budget hours and number of workers in
each craft for each work item. So this relationship must be transferred into a data table
when logic design proceeds. Data for equipment cost of each work item are modeled in
the same way. Machines are classified into different types and data for machine type are
recorded in a machine type entity. Equipment cost information for each work item is
represented by the many-to-many relationship between equipment type, and work item
budget. The relationships between work item and the corresponding labor, material, and

equipment cost are illustrated in Figure 5.10.

(M) (1,1)
Equipment -
Type . Equipment

 

 

 

 

 

 

 

 

 

 

(0.0)
¢ (0,1,) ( La)
(0.0)
Work Item Material
Budget

 

 

 

 

 

 

a (Om)

‘0’“) (an) (1.1)
IAbOI' ‘ Labor
Type

Figure 5.10 Relationship of resource cost and work item budget

 

 

 

 

 

 

 

 

89

5.3.2 Overall Conceptual Model

From the analysis of the different project components, an overall conceptual database
model can be derived. Figure 5.11 illustrates the entities and structure of an ER model for
project cost and time data. The model is derived by connecting logic relations which were
explained above in Figure 5.4 to Figure 5.10. This ER model records actual expenses
based on work items. Actual work progress and time are also recorded in work items in
progress with actual start times. Work items for completed work also have finish times.
Information for resources such as employee name and unit cost is described in material,
labor and equipment entities. Workers and machines can also be classified into different
types such as labor crafts and machine types to record data elements related to type such
as budget rates for different crafts. When resources are spent in a project, the
consumption is recorded in actual usage. Material is measured by actual quantity used.
Labor and equipment are calculated by hours Spent. Then the usage of different kinds of
resources is aggregated to the actual work item in a progress entity. The superintendent
and operators are involved in the construction process as responsible persons controlling
events. Each work item has its budget information recorded in a work item budget entity.
After work items have been performed in the field, actual cost and time consumed need
to be compared with the budget. This relationship is modeled by a one-to-many
relationship between actual work items in progress and work item budget entities.

A project can be separated into two parts, one is performed by a general contractor's
own forces, and the other part is subcontracted out. An actual work item in progress .
reflects the work performed by a contractor's own forces. When parts of the project are

subcontracted, subcontractors are involved as agents to perform parts of the project work.

90

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

   

 

 

 

 

 

 

 

 

 

  

 

 

 

 

  
   

 

 

 

 

   

 

 

 

 

 

 

 

 

 

  

 

 

 

 

 

 

 

 

   

 

 

 

 

 

 

 

 

 

 

 

 

 

(0,n) Subcontractor Progress Sub's Change
Payment Order
has
(0.1!) Has (1,1) (1,1)
(Ln) (0 n) (1 1) Suppl
_ ’ (1.1) .
Material Actual (0J1)
Issue ,
Material Usage
Sub (0,n)
Contract
In charge (0 n) (0,n)
- (0,n) Acma] Work (I l) (0,11) WBOI': him
Superintendent Item in Projress ' u ge
Done by
(0,n) (0,n) (0,n) (0,n)
Supervrse 0 ¢ (0,11‘) ’
1,1
(1’1) (1’1) ( ) Equipment
—— A as Eqfitgal Ty”
‘ c uip sage
Labor 6 Labor Usage
(on) (1,1)
Equipment ,
(0.11)
Labor (091])
Type
(1.0)
Project

 

 

 

 

 

 

 

Figure 5.11 Conceptual database model for project cost and time data

91

Periodic progress payments to subcontractors are the money resource out flow as another
important expense in exchange for a service. Subcontractor's change orders are the
supplement part to each subcontract to reflect new changes of a project due to the
complex situation during construction. These relationships are illustrated in the
subcontract part of the ER model. General project information such as project title,
location, start date and total price is recorded in the project entity. Each work item
reflects one small piece of the project. One project consists of all work items aggregated
together.

The conceptual database model represents an abstract picture of an integrated cost
system. Project direct expenses are recorded and compared with the budget. Actual cost
data are stored and can be used in a future estimate. In addition, time related data are also
described in the budget and actual work item entities to provide work progress
information for schedule measure and control. Each entity in Figure 5.11 has its attributes
to describe the data characteristics of the entity. All entities in Figure 5.11 and their
attributes are listed in Table 5.1. The first attribute in each entity is the key attribute to

identify every instance in the entity.

92

 

Entity Name

Attributes

 

Project

Project no., Name, Start date, Finish date, Owner,
Original contract price, Total value, Location,

 

Work Item Budget

Work item no., Description, Unit, Quantity, Start time,
Finish time, Duration, Material cost, Labor cost,
Eqripment cost, Total cost

 

Relationship between
Material and Work
item budget

Material budget no., Quantity, Material cost

 

Relationship between
Equipment type and
Work item budget

Equipment budget use no., Budget hours,
Standard rate, Overtime rate

 

Relationship between

Labor budget no., Budget labor hours,

 

 

 

 

 

 

 

 

 

 

 

 

 

Labor type and Work Budget labor rate,
item budget
Actual Work item in Work item work in progress no., Actual quantity,
progress Actual start date, Actual finish date
Material Material stock no., Description, Budget unit cost, Unit
Labor Employee ld., Name, Address, Phone no.
Labor Type Labor type no., Description, Available number, Budget
rate
Equipment Machine ld., Machine name
Equipment Type Equipment type no., Description, Standard rate,
Overtime rate
Subcontract Subcontract no., Contract price,
Subcontractor Subcontractor ld., Description, Contact person, Address,
Phone no.
Sub Progress Progress payment no., Period from, To Date,
Payment Description, Amount
Sub Change Order SubChange order no., Date, Change amount
Operator Employee no., Name, Wage rate
Superintendent Employee no., Name, Start in charge date,

 

Finish in chage date,

 

Table 5.1 Entities and their attributes

93

 

5.4 Logic Design in MS Access

Logic design translates the conceptual model into a particular database management
system (DBMS). In a relational database environment, tables are created using Structured
Query Language (SQL). This language is composed of about thirty. commands, and is
designed to work with any application that requires the manipulation of data stored in a
relational database (Rob and Coronel, 1997). By using SQL, table Structures can be
created within the designated database. Most DBMS now use query by example (QBE)
interfaces that allow the attribute names to be typed into a template and the attribute's
data type to be selected from pick lists. MS Access supports this kind of template to

create tables, queries, and forms.

5.4.1 Table Creation

In this research, all entities represented in a database conceptual model have been
implemented into MS Access tables. There are two ways of applying the relationships in
the conceptual model to a DBMS. In a one-to-many relationship, the primary key in the
one-Side entity can be posted to the many-side entity to become a foreign key attribute. In
a one-to-one relationship, the primary key of either Side entity can be added to the other
side entity as a foreign key attribute. Besides a foreign key attribute posting, an additional
table can be created to represent the relationships between entities. Primary key attributes
are the key attribute from both side entities in a binary relationship. Relationships being
transferred into a data table are required when a many-to-many relationship exists or
when the relationship has its own attributes such as the relationships among work item

budget and its resources as explained in section 5.3.1.7. In this Access implementation,

94

each of the relationships is implemented by creating a table. So the entities and
relationships in the conceptual database model in chapter four are become tables in MS
Access. Some data table structures of this database are Shown in Appendix C. These data
tables are built to store cost and time data during a project construction process. They
consist of a relational database. Information presented in reports and other formats is
draw from these tables by computer procedures. Some sample data are entered into these
tables as shown in table 5.3 to table 5.8. These data are from the sample highway bridge

project in Clough and Sears' book "Construction Project Management, Second Edition."

5.4.2 Database Operation

The useful power of a database system is that data are logically related and stored in a
single data repository. Therefore, they are easy to store, access and manage to produce
information by using automatic computer procedures. Based on data tables, some
operations such as query and index can be created to manipulate data. A query is a
question regarding an information requirement to which a database can provide answers.
An index refers to sorting the data in an entity in order. The query capability of relational
DBMS by SQL provides a powerful and flexible way to manage data. This capability of
data manipulation provides a more flexible way to retrieve information. Different kinds
of reports can be produced dependent on how SQL queries are written. Besides query,
index and other utility functions provided by a DBMS, application programs could also

be written accessing the database to produce useful information.

95

5.4.3 Weekly Labor and Progress Report Generation

In this research, after the database conceptual model has been transferred into an MS
Access database, a weekly progress and labor cost report is generated as an example to
illustrate the SQL query procedure. In the project construction process, labor costs and
productivity usually attract much concern from project managers. A weekly labor cost
report is produced throughout the construction to help monitor labor expenses. Work
item's start date and finish date are also drawn from the database to include in this report.
These time related data are provided to help project managers in monitoring project
progress and controlling the schedule. Data used for this report are stored in several MS
Access tables including actual work item in progress, actual labor usage, workers’
information, craft type and work item budget tables. These tables are shown in table 5.3
to table 5.8. The sample data are from the sample highway bridge project in Clough and
Sears' book "Construction Project Management, Second Edition."

Although data are stored in different tables, they are related to each other by sharing a
common entity characteristic, a foreign key attribute. These logic relationships link them
together to become a central pool of data. Query is the tool to draw data from the pool to
meet the information needs. Based on this principle, a labor expense and progress report
is produced to reflect the labor cost and progress statue by using queries and the report
utility in MS Access. Its format is shown in Table 5.2. Basically, it compares budget data
with actual data and makes forecasts based on work performed. Because of lirrrited data,
only one work item's cost and progress status are produced here. Due to the same reason,
actual labor usage per day is assumed the same for six days as a week. The work is

scheduled to be finished in seven working days. This example Shows the possible project

96

information-processing procedures in a database built upon the conceptual model

discussed in chapter 4.

WEEKLY LABOR And PROGRESS REPORT

 

 

 

 

 

 

Project. Highway Bridge Project No 7908-05

Project Ending: July 21 Prepared By. RH C.
Work Item No Description Unit Budget Qty Actual Qty Labor Dt Cost
03157 Abutment forms sf 1,810.00 1448 $1,909.50

To Date Budget Lb. To Date Lb To Date Projected

Labor Cost Unit Cost Unit Cost Smsf L088 38mg! LOSS
$1,498.08 1.05497 1.03459 $29.52 $35.90
Budget Start Budget Actual Actual

Dae Finish Date Start Date Finish Date ...............
“#19179 7127f” 7I15f79 7I21f79

 

 

Table 5.2 Example of weekly labor and progress report

 

 

 

 

Work Item Actual Quantity Actual Actual
WIP No Start Date Finish Date
A03157-1 1448 7/15/79 7/21/79

 

 

 

 

Table 5.3 Actual work item in progress data table

 

 

 

 

 

Labor Usage No Date Actual Hrs Actual Rate
ALO3157-1 7/20/‘79 8.00 $1 1.4
ALO3157-2 7/20/79 8.00 $1 1.4
ALO3157-3 7/20/79 8.00 $8.41

 

 

 

 

 

Table 5.4 Actual labor usage data table

97

 

 

 

 

 

 

 

 

 

 

Employee No Name Labor Type No (foreign ksy)
132 Kraus, W.R. 0001
143 Whelan, MD 0003
176 Green, T.T 0002
221 Pierce, IL. 0001
248 Cordova, J .E. 0003
319 Johnson, S.V. 0003
417 Armstrong, RD. 0002

 

 

 

 

Table 5.5 Workers’ information data table

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Labor Type No Budget Lbr. Rate Craft
001 $8.41 Labor
002 $12.60 Foreman
003 $1 1.53 Concrete
004 $8.41 Labors
005 $11.10 Operator
006 $1 1.40 Carpenter

Table 5.6 Craft type data table

Labor Type No Work Item Budget No Budget Labor Hrs.
001 0331 l 5.00
002 03157 227.00
002 0331 l 20.00
004 0331 1 5.00
005 0331 l 5.00
006 0331 l 5.00

 

 

 

 

 

Table 5.7 Data table for the relationship between
Labor Type Entity and Work Item budget entity

 

 

 

Work Iteml Description Qty Unit Unit St. Fn. lDuration Mtl. Labor Equip. Total
No. price date date cost cost Cost cost
03157 Abutment 1810 Sf 1.92 7/19 7/27 7 145 1909 660 3482

forms .
03311 Concrete, 60 cy 44.41 7/12 7/13 1 2236 200 148 2664

footings

place

 

 

 

 

 

 

 

 

 

 

 

 

Table 5.8 Work item budget data table

98

 

5.5 Chapter Summary

A database conceptual model was developed by using the ER data modeling method.
It was derived both from basic concepts involved in the construction process and
applications of business information-process analysis techniques. A construction business
follows the general business value chain cycles, and project construction is identified as
the product conversion process with its own characteristics. The REA concept provides a
template for business information systems modeling in the relational database
environment. It also fits into the process for construction project data model
development. In this chapter, sample table Structures based on the conceptual model were
also applied in MS Access. Information abstraction procedures from database were
explained and illustrated. In the next chapter, two case studies will be described to
observe the project cost system in actual construction projects and to evaluate the

conceptual model with actual construction practices.

99

Chapter 6

Case Studies

In Chapter 5, a conceptual database model was developed. This model Shows the data
content and its logic structure of an integrated project cost system. Data table structures
based on this conceptual model were set up in the MS Access database management
system. Labor and progress reports were produced as an example to illustrate the
information generation process in a database system and to prove the REA concepts
applied in the conceptual database model. In this chapter, case studies will be discussed
to evaluate the conceptual database model for actual construction projects to find out how
information requirements in a cost system can be satisfied by a database as a central data
storage and management facility instead of traditional independent file systems. Two
projects were used as case studies: one is an institutional construction project and the
other one is a prison facility. The cost systems and schedule control functions used in
these projects will be analyzed. Through this analysis, some problems that can be solved
by applying the database model will be found. The possible changes will be proposed and

benefits will be discussed.

6.1 Case One: an Institutional Project

Case one is a new high school construction project. The total project construction
value is $31 million, including classroom, auditorium and gymnasium. The construction
company was involved in this project since the design phase to help the owner in design,

document and price estimating preparation. The company was assigned the contract

100

through negotiation. Then the company is fully responsible for the whole construction
process. After the company got the contract, all work was subcontracted out. The
company organizes all participants from mobilization to completion, and coordinates

design and construction.

6.1.1 Cost System in this Project

The project has been divided into bid packs and subcontracted out based on these bid
packs. The total project was divided into bid packs according to the project work
breakdown structure (WBS). An estimate for each bid pack was developed as a basis to
compare bid prices submitted by subcontractors and also as the budget to control cost
during construction. Some estimates for bid packs were prepared from takeoff of
blueprints, while some were calculated by the total quantity of the particular work such as
square footage of the site work area. The pricing was based on the company's own
historical cost data for the same type of work and supplemented by the market prices
quoted from subcontractors specialized in a particular kind of work and the local price
information known by the estimator. The company's historical data are maintained by
cost per square foot of each section according to different type of projects.

The company solicited subcontractors' bids and selected the sub contractor for each
bid pack. The major concern of cost control in this project is controlling the
subcontractor's costs. The project manager is also the cost control and schedule control
person in this project. During the construction process, a spreadsheet is maintained for
cost control functions. All the cost data were entered into Spreadsheets. The purpose of

this spreadsheet is to compare budget with actual cost and to record change orders in

101

order to monitor costs for preventing overrun. The basic cost items for data collection are
bid packs. These bid packs were developed according to the project WBS by major
process such as site work, concrete, plumbing, and landscape. Each bid pack was
assigned to one subcontractor. Major data items in this spreadsheet include original
budget, subcontract amount, submittal for changes, approved change orders and amount,
and actual payment to subcontractors. The project manager recorded and traced each
transaction about income (approved change orders) and expenses (payment) to maintain a
margin between budget and actual cost. This margin needs to be a positive figure in order
to keep a profit. Reports about the cost status are generated monthly.

A project schedule was developed according to different subcontractors' work
sections as milestones for time control. This schedule was developed and maintained
using Primavera Project Planner (P3). It is maintained separately from the cost system.

The progress of the project is monitored according to this schedule.

6.1.2 Analysis of the Cost System of this Project

Since all work of this project is performed by subcontractors, the company's primary
job is to organize the subcontractors' work and maintain their costs within budget. The
original budget and expenditure of each bid pack were recorded. After construction was
started, changes of the original contract are important items of concern for the project
manager. These changes are from several sources including the need to change building
design, and field changes from subcontractors. These change orders are submitted to the
architects for approval. Before approval, they are recorded in the suspending item in the

spreadsheet. If they are approved, they will be listed with the original budget to derive the

102

total amount for controlling the expenditures. So all items including budget, changes,
expenses, savings and losses are listed in a spreadsheet. When reports are required, the
project manager needs to go through all of the listed data manually to draw information
for the report. This process would require much time of the project manager.

The cost system used in this project is not integrated with schedule control functions.
No time related data is collected in their cost system. Schedule monitoring and updating

are performed in P3.

6.1.3 Recommendation: Database System for Cost Data

The previous two sections described and analyzed the cost and schedule systems used
in the first case study project. In this section, some possible changes and benefits by
applying the conceptual database model developed in chapter 5 to build a database
management system (DBMS) for performing information processes for cost and schedule

control will be discussed.

6.1.3.1 System Changes

There will be two changes proposed here as follows:

(1) Using a database to organize, store and manage cost data which is now handled
by the spreadsheets. This database can be built based on the conceptual database
model of this research. Since all work of this case project is performed by
subcontractors, the company does not need to trace the cost to the material, labor,

and equipment levels. The database will only include the subcontract part of the

103

database model and will be easy to develop and maintain. The conceptual
database model applied in this project is illustrated in Figure 6.1.

(2) Using basic cost control units the same as the activities for schedule development.
In the existing cost system, bid packs are the smallest units for cost control. A
schedule is developed in P3 having its own basic units for time control. Here, bid
packs are proposed to be divided according to the CSI master format breakdown
structure. In the same time, schedule activities are derived from the same
Structure. The bid packs can serve at the milestone level in schedule control. Time
data such as start date, finish data and duration can be summarized for each bid

pack and recorded into the cost system.

 

 

 

 

 

 

 

 

 

 

 

 

  

 

Subcontractor Progress Sub's Change
Payment Order
( l . 1)
Supplement by
(0m)
Contract (O’n)

 

 

 

@ (0.11)

 

 

 

(Ln) (1.1) BdP ks
Project /has\ 1 ac
V

 

 

 

 

 

 

 

Figure 6.1 Conceptual database model
applied for case project one

104

6.1.3.2 Evaluation of Benefits from these Changes

The following benefits are observed from the changes proposed in section 6.1.3.1.

0 Benefit in report generation: When cost data are all listed in a spreadsheet, the
project manager needs to manually go over all data periodically to draw the
necessary data and retype them for period reports. If data are organized in a
database, these period reports can be generated automatically by preset queries.
This is not difficult to implement in MS Access. It is a one-time setup but can
benefit and save time through the construction period. Also, the project manager
can have more choices in the report generation. They can organize and manipulate
data by using SQL queries and report utility to produce desired information in
different reports.

- Benefit for future planning: After the cost data are stored in the database instead
of several independent spreadsheets, the necessary data for future planning can be
derived by queries. Then these data are stored in an easy to use format instead of
hiding in different Spreadsheet files.

0 Benefit of connection between schedule and cost control: When time data are
entered into the database, they can be presented with the cost data in the same

sheet by queries to provide more information about project status.

6.2 Case Two: A Prison Facility
This case project is building a prison facility with four buildings plus seven housing

units. The total project value is $49 million. The general contractor (GC) was employed

105

with a lump-sum contract. The GC is responsible for all aspects of the project
construction and reports to the owner and architects. The concrete work is performed by

the GC's own workforce. Other work is subcontracted to specialty contractors.

6.2.1 The Project Cost System Analysis
Before construction, a project estimate was developed as a budget for cost control.
This estimate was developed from the takeoff of the blue prints. One project manager in
this project is in charge of the field construction, progress control, cost control, and is
also the estimator. So the cost items in this estimated are mainly based on his experience.
He selected the detailed units that he wants to allocate costs for control. The pricing is
based on the company's historical data and the estimator's experience about market
information. After the construction started, Spreadsheets are used to record and maintain
cost data. The data items in spreadsheets include estimate of costs, actual costs per day,
projected costs and variance. For the GC'S own labor expenses, cost numbers for each of
the above data items are allocated to each labor type of each cost item every day. These
numbers come from the labor time cards subrrritted from the foreman. For the material
expenses of the GC, cost is allocated to each building. Since equipment used by the GC is
rented, the cost is only traced to the project level. Subcontract cost of this project is traced
to each subcontractor.
Spreadsheets are used as the major computer tool to handle the cost data-processing
functions in this project. When information is required, the project manager needs to find
it manually from all data items in different Spreadsheets. It is not an efficient way of data

processing.

106

The project schedule is generated and maintained in Primavera Project Planner (P3).
A person is assigned to be in charge of the project schedule. There are two schedules
maintained in this project. One is a cost loaded schedule developed by a main office
person. Each activity in this schedule has a budget cost assigned to it. The activities are
updated with percentage completion when the construction is under way. The actual cost
of each activity is calculated by the budgeted cost times percentage of completion. The
purpose of this schedule is mainly to generate reports to the owner and architects. Since
this schedule was created by the main office person, it is not very detailed and not
suitable for all field construction circumstances. Another schedule was created by the
project scheduler for the project time control. The problem is that activities in each
schedule are not same. The project progress is recorded in the second schedule. These
data need to be summarized and transferred to the cost loaded schedule to report to the
owner and architects every period. Another problem is that the actual costs associated
with each activity in the cost loaded schedule do not reflect the real expenses of
construction. It is the budgeted cost for work performed. So for the GC, the relationship

between actual costs and actual work progress is still not built.

6.2.2 Possible Changes for the Cost System

The cost and schedule systems in project two were described and analyzed in section
6.2.1. Some problems were observed from this analysis. In this section, a database
environment based on the conceptual database model developed in this research is

proposed as changes in this project for information processing.

107

When data are maintained in Spreadsheets, it is very time consuming to update and
does not allow automatic manipulation of data to produce necessary information. The
project manager needs to look through all spreadsheets manually to find the required
information. When data are Stored in a database, different operations of data can be made
for information processing. This database can be built in any selected DBMS software
based on the conceptual database model of this research.

When this database is used, a common Storage unit for cost and time is required. It
requires developing the estimate and schedule in accordance with the CSI Master Format
numbering project work breakdown system. Then time and progress data can be
summarized from the schedule system and stored in this database. Progress information
of a work item associated with its cost can be produced from this database. This
arrangement can solve the two-schedule problem. The cost loaded schedule does not need
to be maintained since the progress and cost information can be drawn from this

database.

6.2.3 Evaluation of Benefits from these Changes

The possible benefits from the changes are as follows:

0 Benefit in information processing: When data are collected and operated in a
database, the necessary information can be produced by SQL queries. These
queries can be set one time ahead and used many times when the information is
needed. This strategy will save time and reduce errors when information is
processed manually. In the mean time, different types of reports can be produced

for desired information through the database operation. After project completion,

108

data for future planning can also be summarized from the database as historical
information of the company.

0 Benefits in schedule system: When the same project breakdown structure is used
for scheduling and estimating, the connection between time data and cost data can
be built. The progress data can be calculated and summarized to the cost database.
The progress and cost information can be generated from this database to present
in one report for the owner. The problem of maintaining two schedules can be

solved this way.

6.3 Feed Back from a Project Manager

The information about using a database to manage data was considered useful by the
project manager for the first case study project. The professional accepted the idea of
using a database to record and retrieve project data. It is considered to be an efficient way
to organize large amounts of data in a project compared with Spreadsheets. The project
manager realized that a database allows data manipulation, which a spreadsheet can not
do. He agreed that by using a database it is easy to sort data, draw Specific information,
and produce reports for different needs. He agreed that a database is very useful in cost
data maintenance and information processing for construction projects. With the author's
explanation, he also understood the need of a database model upon which to build the
database.

The project manager also pointed out the need for training on using database software
and the requirements of easy to use DBMS software. The author suggested windows

based MS Access to use in construction projects because of its user-friendly interface.

109

6.4 Chapter Summary

The case studies were aimed at examining construction practice in cost control and
the integration of time data in the cost system. The conceptual database model for project
cost and time data was evaluated in the practice. Some possible changes based on this
model were proposed. Benefits from these changes were analyzed to solve some real

problems for information processes of a construction project.

110

Chapter 7

Summary, Conclusion and Future Research

7.1 Summary

This thesis centered on applying a database management system (DBMS) in the
construction management field to improve the information processes of construction
projects. The implementation of a DBMS requires a conceptual database model to
represent the data contents and their logic relationships abstracted from real construction
projects. The cost system of a construction company within the project management
system is the major concern in this research. Since there exists a close relationship
between cost control and time control, time-related data was proposed to be entered and
managed in the cost system in order to integrate some time control functions in the same
system. This was accomplished by using work items as the basic Storage units for cost
and time data. These work items are generated based on the project work breakdown
structure (WBS) according to Construction Specifications Institute (CSI) Master Format
system. The job costing performs the information processing function in a cost system. It
is a series of forms, documents, procedures, and communication channels to record
project data, process it, and structure it in particular formats for project management. This
research proposed a design of this traditional information processing system into a
database environment. Conceptual database modeling is the critical phase in a database
system design. The objective of this research is to develop a conceptual database model
for the project cost and time data. Based on this model a database can be built to achieve

efficient information processing to facilitate project management. This model was

111

developed by using entity-relationship (ER) database modeling method. In the meantime,
the value chain concept and Resource-Event-Agent (REA) pattern were used in the
business process analysis for the design of database. Two projects were used as case
studies to evaluate the conceptual database model in the construction practice.

This research Started from a literature review. It covered the topics about the
integration of project cost control and time control functions and the database system
application in the Architecture/Engineering/Construction industry. No database model
covered all the cost components in a project cost system, which includes material, labor,
equipment, and subcontractors' expenses, has been found in this literature review.
Analysis of a construction company's business process for database management systems
design also has not been found in the previous research. So this research was intended to
provide a comprehensive conceptual database model to present the entire picture of a
project cost system. In the meantime, a construction company's business process was
analyzed to help the database model development.

A system approach for the database management system development was introduced
after the literature review. The development process for the conceptual database model of
the project cost and time data was described. Followed by this process, an information
requirement analysis was conducted next. Work items as the basic data storage units were
proposed to integrate time related data in the cost system. Data elements were identified
and abstracted into data groups via a form analysis of the forms and documents used for
the information process in the cost system.

The value chain concepts were used to examine all activities a construction company

performs to find out the events that trigger the information process. In an entity-

112

relationship database environment, an REA pattern was used to record data around those
business events with their related resources and participants. A conceptual database
model was developed using the ER modeling tool. This database model was also
transferred into an MS Access DBMS, and a data table structure was set up. The
prototype of information process using structure query language (SQL) was illustrated by
producing period labor and progress reports as an example.

Two projects were used as case studies, and the current cost system in these projects
was analyzed. The conceptual database model was evaluated in construction practice to
find out some possible changes based on the conceptual database model. The possible

benefits that can solve some problems in the practice were discussed.

7.2 Conclusions

From the thesis research work it can be concluded that the proposed conceptual
database model for the project cost and time data is a feasible model. This model
represents the data content and their logic relationships in an integrated project cost
system in an abstraction level. Through this research, the following conclusions can be

derived.

7.2.1 Conclusions about Time Related Data Integrated in Cost System

Cost data are usually recorded around cost items broken down from the project by a
cost person. On the other hand, time data are measured by activities for schedule control.
In order to record time data in a cost system, common data storage units need to be built.

A work packaging approach is a feasible approach that can be used as a guideline to

113

construct the data units. In this research, work packages in the project work breakdown

structure are named as work items. These work items are coded according to CSI Master

Format. Cost estimates and time schedules need to be developed based on the same work

breakdown structure in order to build a connection between cost data and time data.

Planned and actual information needs to be recorded by the same data units, work items.

7 .2.2 Conceptual Database Model Development Related Conclusions

The system approach for information and database system design is very helpful
in the database system design for project data. It outlines the step-by-Step process
of a database system implementation.

The entity-relationship model was found to be very suitable in the design of
database systems in the construction field. Its format simplicity makes it easy to
understand and communicate with end users, construction persons. When the
conceptual database model is understandable by them with some instruction, the
quality of the model can be improved so that the database based on this model
will be more effective.

The value chain concept and REA pattern can also be used to analyze a
construction company's business processes and represent these processes in a
database system. The REA pattern recognizes business events, its associated
agents and consumed resources in an entity-relationship database model. In the
mean time, the REA pattern can be combined with other database modeling
concepts such as aggregation, and entity dependency to derive a conceptual

model.

114

7.2.3 Conclusions Related to Perform Information-processing Function of the
Project Cost System in a Database Environment
Based on the conceptual database model, a database can be built to perform
information-processing functions. When the necessary data are stored in a database,
information processing can be performed more effectively. Queries can be used to satisfy
the information requirement questions. Different types of reports can also be generated
automatically using SQL. MS Access can be used as a DBMS software application for

construction project data since it is very user-friendly.

7.3 Future Research

The following are directions suggested for future research:

0 This thesis research for the conceptual database model was developed using ER
modeling in a relational database environment. AS an alternative approach, object-
oriented (00) database modeling could be used to model the project cost and time
data in an object-oriented database environment. Some features of 00 technology
can be applied. For instance, data are not only represented in the database but also
can have associated programs as methods to perform some desired tasks.

0 The database model only covered the cost system within the conversion process
of a construction company, although some time related data were proposed to be
stored in the database to perform some schedule control functions. The scope of

the database model can be expanded to cover all processes of a construction

115

company. This Strategy will provide the foundation to build a central database of a
construction company to manage all data of its business processes.

0 Some application programs can be written on top of the database which is built
from the conceptual data model developed in this research. These programs can
perform more information process functions or serve as the interface between the

database and other construction software applications.

This thesis explored issues about a database system development and its
implementation in the construction management field. The focus is the conceptual
database modeling. The conceptual database model developed here is an attempt to
facilitate the project information process by taking advantages of the fast improvement of

the computer and information technology.

116

APPENDICES

117

Appendix A

Concepts of Data Flow Diagram (DFD)

The data flow diagram supports the concepts of process, dataflow, data store, and

interface. These concepts are presented by graphic representation shown in Figure A].

Processes are represented by rectangles with rounded comers, dataflow by directed lines

(arrows), data stores by three-sided rectangles, and interfaces by rectangles with right

angled comers. Using these concepts, the designer builds a functional schema of the

business process.

 

 

 

 

 

Concept Diagrammatic Representation
Process: a process represents an activity within the
information system In particular, a process can :3
generate, use, manipulate, or destroy information.
Dataflow: a dataflow is an exchange of information
between processes. )
Data store: a data store is a repository of
information. Temporary files, look-up tables, paper
forms, electronic forms, and permanent records may L___

all be represented as data stores.

 

 

 

Interface: an interface is an external user of the
information system, who may be the originator
and/or the receiver of dataflows or data stores.

 

 

 

 

 

 

Table A1 The diagrammatic representation
of dataflow diagramming (DFD) concepts
(Source: Batini, et. al., 1992)

In Figure A2, a DFD example applying in construction management field is shown.

This DFD illustrates the process of subcontractor's application for progress payment. A

progress payment is applied and stored in the progress payment data store. Data about

application_amount is flow to the process check amount due. When the application is '

118

 

approved, approved_amount is sent to pay to subcontractor process and store in a file to
record approved amount of each subcontractor. After the pay to process, check is sent to

subcontractor ending this whole process.

 

 

 

 

 

 

 

Progress Payment
Applicaltrons Subcontractors
requested_amount T

i Check
1
Check 1 Approved_amount Pay to
Amount Due J subcontractor

 

 

 

Approved_amount

 

Progress Payment
file

 

 

Figure A.1 Subcontractor Progress Payment Process DFD

119

 

Appendix B
CSI Master Format — Broadscope Section Titles

This appendix contains the Master Format for construction classifications, as
published by the Construction Specifications Institute (CSI), 601 Madison Street,
Alexandria, Virginia 22314-1791, in conjunction with Construction Specifications
Canada (CSC). It is applicable to organizing Specifications sections, drawings, cost
codes, materials information, office correspondence and other aspects of design,

procurement and construction. (Source: Barrie and Paulson, 1992)

120

BIDDING REQUIREMENTS, CONTRACT
FORMS, AND CONDITIONS OF THE
CONTRACT

00010 Pre-Bid Information
001001nstructions to bidders

00200 Information Available to Bidders
00300 Bid Forms

00400 Supplements to Bid Forms

00500 agreement forms

00600 Bonds and Certificates

00700 General Conditions

00800 Supplementary Conditions

00900 Addenda

SPECIFICATIONS

Division 1 — General Requirements

01010 Summary of Work

01020 Allowances

01025 Measurement and Payment

01030 Altemates/Alternatives

01035 Modification Procedures

01040 Coordination

01050 Field Engineering

01060 Regulatory Requirements

01070 Identification Systems

01090 References

01100 Special Project Requires

01200 Project Meetings

01300 Submittals

01400 Quality Control

01500 Construction Facilities and Temporary
Controls

01600 material and Equipment

01700 Contract Closeout

01800 Maintenance

Division 2 — Sitework

02010 Subsurface Investigation
02050 Demolition

02100 Site Preparation

02140 Dewatering

02150 Shoring and Underpinning
02160 Excavation Support Systems
02170 Cofferdams

02200 Earthwork

02300 Tunneling

02350 Piles and Caissons

02450 Railroad Work

02480 Marine Work

02500 Paving and Surfacing
02600 Utility Piping Materials
02660 Water Distribution

02680 /fuel and Steam Distribution
02700 Sewerage and Drainage
02760 Restoration of Underground Pipe
02770Ponds and Reservoirs

02780 Power and Communications
02800 Site Improvements

02900 Landscaping

Division 3 — Concrete

03100 Concrete Formwork

03200 Concrete Reinforcement

03250 Concrete Accessories

03 300 Cast-in-Place Concrete

03370 Concrete Curing

03400 Precast Concrete

03500 Cementitious Decks and Toppings
03600 Grout

03700 Concrete Restoration and Cleaning
03800 Mass Concrete

Division 4 — Masonry

04100 Mortar and Masonry Grout

04150 Masonry Accessories

04200 Unit Masonry

04400 Stone

04500 Masonry Restoration and Cleaning
04550 Refractories

04560 Corrosion Resistant masonry
04700 Simulated Masonry

Division 5 — Metals

05010 Metal Materials

05030 Metal Coatings

05050 Metal Fastening

05100 Structural Metal Framing
05200 Metal Joists

05300 Metal Decking

05400 Cold Formed Metal Framing
05500 Metal Fabrications

05580 Sheet Metal fabrication

121

Division 5 — Metals (Continue)
05700 Ornamental Metal

05800 Expansion Control

05900 Hydraulic Structures

Division 6— Wood and Plastics
06050 Fasteners and Adhesives
06100 Rough Carpentry

06130 Heavy Timber Construction
06150 Wood and Metal Systerrrs
06170 Prefabricated Structural Wood
06200 Finish Carpentry

06300 Wood Treatment

06400 architectural woodwork
06500 Structural Plastics

06600 Plastic fabrications

06650 Solid Polymer Fabrications

Division 7 — Thermal and Moisture
Protection

07100 Waterproofing

07150 Dampproofing

07180 Water Repellents

07190 Vapor Retarders

07195 Air Barriers

07200 Insulation

07240 Exterior Insulation and Finish Systems
07250 FirestOpping

07300 Shingles and Roofing Riles
07400 Manufactured Roofing and Siding
07500 membrane Roofing

07570 Traffic Coatings

07600 Flashing and Sheet Metal

07700 Roof Specialties and Accessories
07800 Skylights

07900 Joint Sealers

Division 8 — Doors and Windows
08100 Metal Doors and Frames
08200 Wood and Plastic Doors
08250 Door Opening Assemblies
08300 Special Doors

08400 Entrances and Storefronts
08500 Metal Windows

08600 Wood and Plastic Windows
08650 Special Windows

08700 Hardware

08800 Glazing

08900 Glazed Curtain Walls

Division 9 —— Finishes

09100 Metal Support Systems
09200 Lath and Plaster
09250 Gypsum Board

09300 Tile

09400 Terrazzo

09450 Stone Facing

09500 Acoustical Treatment
09540 Special Wall Surfaces
09545 Special Ceiling Surfaces
09550 Wood Flooring

09600 Stone Flooring

09630 Unit Masonry Flooring
09650 Resilient Flooring
09680 Carpet

09700 Special Flooring
09780 Floor Treatment

09800 Special Coatings
09900 Painting

09950 Wall Coverings

Division 10— Specialties

10100 Visual Display Boards

10150 Compartments and Cubicles

10200 Louvers and Vents

10240 grilles and Screens

10250 Service Wall Systems

10260 Wall and Comer Guards

10270 Access Flooring

10290 Pest Control

10300 Fireplaces and Stoves

10340 Manufactured Exterior Specialties

10350 Flagpoles

10400 Identifying Devices

10450 Pedestrian Control Devices

10500 Lockers

10520 Fire Protection Specialties

10530 Protective Covers

10550 Postal Specialties

10600 Partitions

10650 Operable Partitions

10670 Storage Shelving

10700 Exterior Protection Devices for
Openings

10750 Telephone Specialties

10800 Toilet and Bath Accessories

10880 Scales

10900 Wardrobe and Closet Specialties

122

Division 11 — Equipment

11010 Maintenance Equipment

11020 Security and Vault Equipment

11030 Teller and Service Equipment

11040 Ecclesiastical Equipment

11050 Library Equipment

11060 Theater and Stage Equipment

11070 Instrumental Equipment

11080 Registration Equipment

11090 Checkroom Equipment

11100 Mercantile Equipment

11110 Commercial Laundry and Dry Cleaning
Equipment

11120 Vending Equipment

11130 Audio-Visual Equipment

11140 Vehicle Service Equipment

11150 Parking Control Equipment

11160 Loading Dock Equipment

11170 Solid Waste Handling Equipment

11190 Detention Equipment

11200 Water Supply and Treatment Equipment

11280 Hydraulic Gates and Valves

11300 Fluid Waste Treatment and Disposal
Equipment

11400 Food Service Equipment

11450 Residential Equipment

11460 Unit Kitchens

11470 Darkroom Equipment

11480 Athletic, Recreational, and Therapeutic
Equipment

11500 Industrial and Process Equipment

11600 Laboratory Equipment

11650 Planetarium Equipment

11660 Observatory Equipment

11680 Office Equipment

11700 Medical Equipment

11780 Mortuary Equipment

11850 Navigation Equipment

11870 Agricultural Equipment

Division 12 — Furnishings
12050 Fabrics

1 2 100 Artwork

12300 Manufactured Casework
12500 Window Treatment

12600 Furniture and Accessories
12670 Rugs and Mats

12700 Multiple Seating

12800 Interior Plants and Planters

Division 13 — Special Construction

13010 Air Supported Structures

13020 Integrated Assembles

13030 Special Purpose Rooms

13080 Sound, Vibration, and Seismic
Control

13100 Nuclear Reactors

13120 Pre-Engineered Structures

13150 Aquatic Facilities

13175 Ice Rinks

13180 Site Constructed Incinerators

13185 kennels and Animal shelters

13200 Liquid and Gas Storage Tanks

13220 Filter Underdrains and Media

13230 Digester Covers and Appurtenances

13240 Oxygenation Systems

13260 Sludge Conditioning Systems

13300 Utility Control Systems

13400 Industrial and Process Control
Systems

13500 Recording Instrumentation

13600 Solar Energy Systems

13700 Wind Energy Systems

13750 Cogeneration Systems

13800 Building Automation Systems

13900 Fire Suppression and Supervisory
Systems

13950 Special Security Construction

Division 14— conveying Systems
14100 Dumbwaiters

14200 Elevators

14300 Escalators and Moving Walks
14400 Lifts

14500 Material Handling Systems
14600 Hoists and Cranes

14700 Turntables

14800 Scaffolding

14900 Transportation Systems

Division 15 — Mechanical

15050 Basic mechanical Materials and
Methods

15250 Mechanical Insulation

15300 Fire Protection

15400 Plumbing

15500 Heating, Ventilating, and Air
Conditioning

15550 Heat generation

123

Division 15 — Mechanical (Continue)
15650 Refrigeration

15750 Heat Transfer

15850 Air Handling

15880 Air Distribution

15950 Controls

15990 Testing, Adjusting, and Balancing

124

Division 16 — Electrical

16050 Basic Electrical materials and
Methods

16200 power Generation - Built-Up
Systems

16300 Medium Voltage Distribution

16400 Service and Distribution

16500 Lighting

16600 Special Systems

16700 Communications

16850 Electric Resistance Heating

16900 Controls

16950 Testing

Appendix C

Data Table Properties

Table C.1 Properties of Entity Data Table in Prototype Database

 

 

Table Name Column Data Type Size
Actual Equipment Charge N 0 Text 30
Equipment Usage Date Date/Time -
Actual Hours Number Integer
Actual Cost Number Integer
Actual Labor Usage Labor Usage No Text 30
Date Data/Time -
Actual Hrs Number Integer
Actual Rate Number Integer
Actual Charge No Text Integer
Material Usage Date Date/Time -
Quantity Number Integer
Actual Unit Cost Number Integer
Actual Work Item Work Item WIP No Text 30
In Progress Actual Quantity Number Integer
Actual Start Date Data/Time -
Actual Finish Date Date/'1‘ ime -
Equipment Machine N 0 Text 30
Machine Name Text 30
Standard Rate Number Integer
Overtime Rate Number Integer
Workers’ Employee No Text 30
Information Name Text 40
Address Text 30
Phone N 0 Text 15
Craft Type Labor Type No Text 30
Craft Text 30
Available Number Number Integer
Budget Rate Number Integer
Material Material N 0 Text 30
Description Text 50
Budget Unit Cost Number Integer
Unit Text 10

 

125

 

 

Table Column Type Size
Project General Project No Text 30
Information Project Name Text 40
Location Text 40
Start Date Date/Time -
Finish Date Date/Time -
Owner Text 40
Original Contract Value Number Integer
Total Value Number Integer
Sub Change Order SubChange Order No Text 30
Date Date/'1‘ ime -
Change Amount Number Integer
Sub Contract Subcontract No Text 30
Contract Price Number Integer
Sub Progress Progress Pmt No Text 30
Payment Period From DatefI‘ime -
To Date Date/Time -
Description Text 50
Amount Number Integer
Subcontractor Sub No Text 30
Description Text 40
Contact Person Text 30
Address Text 50
Phone Number Text 20
Work Item Work Item Budget No Text 30
Budget Description Text 40
Qty Number Integer
Unit Text 10
Unit Price Currency -
Start Date Date/Time -
Finish Date Date/Time -
Duration Number -
Matl Cost Number -
Labor Dt Cost Currency -
Labor InDt Cost Currency -
Equipment Cost Currency -
Total Cost Number Integer

 

126

LIST OF REFERENCES

127

LIST OF REFERENCES

Abudayyeh, Osama Y. and Rasdorf, William J ., (1991). "Design of Construction Industry
Information Management Systems." Journal of construction Engineering and
Management, ASCE, Vol. 117, 698-715

Abudayyeh, Osama Y. and Rasdorf, William J., (1993). "Prototype Integrated Cost and
Schdule Control System." Journal of Computing in Civil Engineering, ASCE, Vol. 7,
181-198

Adams, Teresa M. (1993). "Knowledge Representation and Processing in Relational
Database." Journal of Computing in Civil Engineering, ASCE, Vol. 7, N02, 238-254

Adrian, James J., (1979). Construction Accounting Financial, Managerial, Auditing, and
Tax, Reston Publishing Company, Inc.

Barrie, Donald S. and Paulson, Boyd C., (1992). Professional Construction Management:
Including CM, Design-Construct, and General Contracting, McGraw-Hill, Inc. Third
Edition .

Betts, Martin, (1993) "An Entity Relationship Model of Construction Project Data."
Proceedings of the 5th International Conference: Computing in Civil and Building
Engineering, Vol. 1, 670-677

I Carr, Robert 1., ( 1993) "Cost, Schedule, and Time Variances and Integration." Journal of
Construction Engineering and management", Vol. 119, 245-265

Ceri, Stefano, (1983). Methodology and Tools for Data Base Design, North-Holland

Publishing company

128

Choi, K. C. (1994). "A Value Chain Model to Support Business Process Reengineering in
Turnkey contracting Firm. " Proceedings of the First Congress held in conjunction
with A/E/C Systems '94.Vol. 2, p1575-1582

Clough, Richard H. and Sears, Glenn A., (1991) Construction Project Management, John
Wiley & Sons, Inc., Third Edition

Construction Industry Institute Cost/Schedule Controls Task Force, (1987) Project
Control for Construction, The construction Industry Institute, the University of Texas
at Austin

Department of Energy, (1979) Cost & Schedule Control Systems Criteria for Contract
Performance Measurement, Summary Description,

Gibson, George E. Jr., Bell, lansford C., (1992) "Integrated Data-Base Systems." Journal
of construction Engineering and Management, Vol. 118, No. 1, 50-59

Geerts, Guido L. and McCarthy, William E., (1994) Using Object Templates from the
REA Accounting Model to Engineer Business Process and Tasks

Hollander, Anita Sawyer, Denna, Eric L., Cherrington, J. Owen, (1996) Accounting,
Information Technology, and Business Solutions Richard D. Irwin, a Times Mirror
Higher Education Group, Inc., Company

Kim, J. J. (1989). "An object-oriented database management system approach to improve
construction project planning and control," thesis presented to the University of
Illinois, at Urbana, III.

Kim, J ae-J un and Ibbs, C. William, (1992). "Comparing Object-Oriented and Relational
data models for project control." Journal of Computing in Civil Engineering, ASCE,

Vol. 6, N03, 348-369

129

 

Kartam, Nabil A. "Integrating Safety and Health Performance into Construction CPM."
Journal of Construction Engineering and Management, ASCE, Vol. 123, No.2, 121-
126

McCarthy, William E., "The REA Accounting Model: A Generalized Framework for
Accounting Systems in a Shared Data Environment," The Accounting Review,
Vol.LVII, No. 3, July 1982, 554-578

McCarthy, William E., Rockwell, Stephen R. and Arnritage, Howard, "A Structured
Methodology for the Design of Accounting Transaction System in a Shared Data
Environment." To be published, Michigan State University, 1994

Maciaszek, L. A., (1990) "Database Design and Implementation. " Prentice Hall

Navon, Ronie, (1994) "Cost-Schedule Integration for Cash-Flow Forecasting."
Proceedings, 1'" Congress on Computing in Civil Engineering, ASCE, New York,
N.Y., 1536-1539.

Navon, Ronie, (1996) "Company-Level Cash-Flow Management." Journal of
Construction Engineering and Management, ASCE, Vol. 122, No.1, 22-29

Nijssen, G.M., Halpin, T.A. (1989). Conceptual Schema and Relational Databases
Design, Prentice Hall

O'Brien James A., (1994). Introduction to Information Systems, Richard D. Irwin, Inc.

Olle, T.W., Sol, H. G., and Verrijn-stuart, A. A. (1986). Information Systems Design
Methodologies: Improving the Practice, Elsevier science Publisher B.V.

Perry, J arnes T. and Schneider, Gary P., (1998). Building Accounting Systems using

Access 7.0 & Windows 95, South-Westem College Publishing

130

Porte, Michael E., (1985). Competitive Advantage, Creating and Sustaining Superior
Performance. The Free Press

Raymond, Louis, (1987). "Information Systems Design for Project Management: A Data
Modeling Approach." Project Management Journal, Volume XVIII, N umber4, P94-
99

Rasdorf, William J. and Abudayyeh, Osama Y., (1991). "Cost and Schedule-Control
Integration: Issues and Needs." Journal of Construction Engineering and
management, ASCE, Vol. 117, 486-502.

Rasdorf, William J ., and Abudayyeh, Osama Y., (1992). "NIAM Conceptual Database
Design Construction Management." Journal of Computing in Civil Engineering,
ASCE, Vol. 6, 41-62

Rob, Peter and Coronel, Carlos, (1997). Database System - design, implementation,
management, Course Technology

Sears, Glenn, (1981). "CPM/COST: An Integrated Approach. " Journal of the
Construction Division, ASCE, Vol. 107, 227-239

Shelly, Gary B., Cashman, Thomas J ., Waggoner, Gloria A., Waggoner, William C.,
Using Computers A Gateway to Information World Wide Web Edition, Boyd & fraser
publishing company

Spainhour, Lisa K. and Rasdorf, William J., (1996). "Entity-Relationship Modeling of
composite Materials Data." Journal of computing in Civil Engineering, ASCE, Vol.

10, No. 3, 226-235

131

Talonpoika, Raine, Karstila, Kari, Lassila, Kenneth, and Pallas, Jukka COMBINE, (1995)
Computer Model for the Building Industry in Europe. [Online] Available
http://erg.ucd.ie/combine.htrrrl

Teorey, Toby J ., (1994). Database Modeling & Design: The Fundamental Principles,

Morgan Kaufmann Publishers, Inc.

132