Open Collections

UBC Theses and Dissertations

UBC Theses Logo

UBC Theses and Dissertations

Use of information for problem resolution on construction projects Shahid, Syed Mohammad 1996

You don't seem to have a PDF reader installed, try download the pdf

Item Metadata

Download

Media
[if-you-see-this-DO-NOT-CLICK]
ubc_1996-0278.pdf [ 13.89MB ]
Metadata
JSON: 1.0050343.json
JSON-LD: 1.0050343+ld.json
RDF/XML (Pretty): 1.0050343.xml
RDF/JSON: 1.0050343+rdf.json
Turtle: 1.0050343+rdf-turtle.txt
N-Triples: 1.0050343+rdf-ntriples.txt
Original Record: 1.0050343 +original-record.json
Full Text
1.0050343.txt
Citation
1.0050343.ris

Full Text

USE OF INFORMATION FOR P R O B L E M RESOLUTION ON CONSTRUCTION PROJECTS by SYED MOHAMMAD SHAHTD B. Tech. (Honors), Indian Institute of Technology, India, 1980 A THESIS SUBMITTED IN PARTIAL FULFILLMENT OF THE REQUIREMENTS FOR THE DEGREE OF MASTER OF APPLIED SCIENCE in THE FACULTY OF GRADUATE STUDIES Department of Civil Engineering We accept this thesis as conforming to the^fequired standard  THE UNIVERSITY OF BRITISH COLUMBIA April, 1996 ©SyedM. Shahid, 1996  In  presenting  degree freely  at  this  the  thesis  in  partial  University  of  British  available for  copying  of  department publication  this or of  reference  thesis by  this  for  his  scholarly  for  her  of  CIV"-  *  financial  A/<S/*J  The University of British Columbia Vancouver, Canada  Date  DE-6 (2/88)  AS*U  '2,  I further  purposes  the  requirements  gain  that  agree  may  representatives.  permission.  Department  of  C o l u m b i a , I agree  and study.  or  thesis  fulfilment  It  shall not  be is  that  the  permission  granted  allowed  an  advanced  Library shall make  by  understood be  for  for  the that  without  it  extensive  head  of  my  copying  or  my  written  11  ABSTRACT  If construction managers are to carry out their responsibilities in the areas of construction and its support services, they need ready access to the right information [Lock 92]. Timely access to the right data, properly processed for decision making, can provide a competitive edge. And keeping track of information flow on a construction job site is a vital task that has a direct bearing on the timely and successful completion of a construction project [Rasdorf & Herbert 88]. Traditionally, in the construction industry data is collected at the departmental level, but there is very little exchange of information between the departments. Much less between companies. As a result, there is a great deal of duplication of effort and inconsistencies occur in datafromdifferent sources. The goal of this thesis is to analyze the information needs of project personnel and to develop a computer-based Project-Management Information Control System (PMICS) to enhance the problem-solving and information management abilities of construction-site management personnel. The use of the latest development in computer technology will be emphasized to manage all the information in such a way as to allow quick access to the right information at the right time and shared by various disciplines or parties. The PMICS builds upon the understanding of the functions and information needs of various personnel at construction projects, and the different documents used by them. The thesis consists of five steps: (1) an analysis of related literature and the current state-of-practice relating to project information; (2) the establishment of a matrix of document types and project  Ill  personnel; (3) the definition of personnel roles and information needs; (4) the establishment of the purpose, contents, and personnel involved in the preparation of different documents; and, (5) the development of a means for tracking project information. The system is developed for the PC platform and runs under Microsoft Windows™ operating environment using Microsoft Access and Excel software. The system developed in this thesis will help manage the production of information, will improve and expedite information, and will relieve technical personnel of administrative and clerical duties.  iv  TABLE OF CONTENTS Abstract  ii  Table of contents  iv  List of Tables  x  List of Figures  xii  Acknowledgment  xvi  Dedication  xvii  1.  INTRODUCTION  1  1.1  Introduction  1  1.2  Goals  1  1.3  Objectives  4  1.4  Research Methodology  5  1.4.1  Literature Review  5  1.4.2  System Development  6  1.5  1.6  Overview of Information Control Problems  6  1.5.1  Information Management and Construction Projects  7  1.5.2  Data Sources  8  1.5.3  Information Reference Sources  8  1.5.4  Information Users and Routing of Information  10  1.5.5  Information Generator  11  Overview of Databases and Database Management Systems  11  1.6.1  Database Concepts  11  1.6.2  Top-Down Planning versus Bottom-Up Design  13  1.6.3  Database Terminology  14  1.6.4  Data Modeling  16  1.6.5  DBMS versus Data Models  18  1.6.6 Relational Databases Terminology  19  1.6.7 Normalization  21  1.6.8  Repeating Groups  23  1.6.9  Normalization Examples  25  1.6.10 Data Dictionary  30  1.7  Focus of the Thesis  30  1.8  Overview of the Thesis  31  LITERATURE REVIEW  32  2.1  Introduction  32  2.2  Documents, Records and Reports in the Construction Industry  32  2.2.1  Document Classifications  32  2.2.2  Project Documentation  33  2.2.3  Reporting Hierarchy  35  2.2.4  Routing of Submittals  37  2.2.5  Computerized Daily Site Reporting  38  2.2.6  Computerized Change Management  38  2.2.7  Claims Analysis  39  2.2.8  Photo Records  40  2.3  2.4  Information and Construction Projects  40  2.3.1  Construction Personnel Role and Information Needs  40  2.3.2  Traditional Information Flow Framework  42  2.3.3  Information Flow Requirements  43  Information and Information Systems  44  2.4.1  Information Definition  44  2.4.2  Management Information and the Construction Industry  45  2.4.3  Current Information Problems  48  vi  2.5  3.  2.4.4  Management Information Control Systems  49  2.4.5  Computer-Based Information Systems  51  2.4.6  Information or Problem Tracking  51  2.4.7  Information Model  53  2.4.8  Entity-Relationship (E/R) Model  54  2.4.9  Structural Data Model  55  2.4.10 Users Interface  59  Related Work  60  2.5.1  Expedition  60  2.5.2  Resident Management System (RMS)  61  2.5.3  Centex-Rooney's Jobsite Program  61  2.5.4  MULTROL  62  2.6  Matrix as an Analytical Tool  62  2.7  Summary  64  FRAMEWORK  65  3.1  Introduction  65  3.2  Framework for PMICS Development  65  3.2.1  Planning Phase  67  3.2.2  Requirements Formulation Phase....  69  3.2.3  System Design Phase  72  3.2.4  Implementation Phase  75  3.3  PMICS - Project Management Information Control System  77  3.3.1  Objectives of PMICS  77  3.3.2  Planning for PMICS  77  3.3.3  Requirements of PMICS  78  3.3.4  System Design of PMICS  78  vii  4.  SYSTEM ANALYSIS AND DESIGN 4.1  79  General Description  79  4.1.1 Description of Users  80  4.1.2 Background for the Application  82  4.1.3 Information Model Building  83  4.1.3.1 Modeling the Construction Project Environment  84  4.1.3.2 Modeling the Documenting/Reporting Process  84  4.1.3.3 Data Entry and Updating  87  4.1.4 Objectives and Scope of the PMICS  87  4.1.5 Selected User Views  88  4.2  Database Design Overview  96  4.3  File Organization Schemes  97  4.3.1  Core Entity - the specifications  97  4.3.2  Other Basic Entities  100  4.4  System Views  102  4.4.1 View 1: Bill of Quantities  104  4.4.2 View 2: Bid Summary Sheet  108  4.4.3 View 3: Change Orders Tracking Log  114  4.4.4 View 4: Correspondence Tracking Log  121  4.4.5 View 5: Daily Site Report (Daily Report)  127  4.4.6 View 6: Defective Work Notifications Tracking Log  133  4.4.7 View 7: Materials Stored Tracking Log  137  4.4.8 View 8: Monthly Progress Report  142  4.4.9 View 9: Photographs Tracking Log  147  4.4.10 View 10: Punch Lists  152  4.4.11 View 11: Requests for Information (RFI) Tracking Log  156  4.4.12 View 12: Shop Drawing Submittals Tracking Log  161  4.4.13 View 13: Spare Parts Tracking Log  167  4.4.14 Integrated View  171  viii  4.5  4.6  Structural Data Model  173  4.5.1 View 1: Bill of Quantities  176  4.5.2 View 2: Bid Summary Sheet  178  4.5.3 View 3: Change Orders Tracking Log  179  4.5.4 View 4: Correspondence Tracking Log  181  4.5.5 View 5: Daily Site Report  183  4.5.6 View 6: Defective Work Notifications Tracking Log  184  4.5.7 View 7: Materials Stored Tracking Log  187  4.5.8 View 8: Monthly Progress Report  189  4.5.9 View 9: Photographs Tracking Log  190  4.5.10 View 10: Punch Lists  192  4.5.11 View 11: Requests for Information (RFI) Tracking Log  194  4.5.12 View 12: Shop Drawing Submittals Tracking Log  196  4.5.13 View 13: Spare Parts Tracking Log  198  4.5.14 Integrated Structural Model  200  Entity and Relationship Attributes  201  4.6.1 Entities  202  4.6.2 Relationships  206  4.6.3 Entity Cross-Reference Matrix  206  4.7  Data Dictionary  208  4.8  Data Volume and Usage  208  4.9  4.8.1 Assumptions  209  4.8.2 Entity Cardinality  210  4.8.3 Expected Entity Size  211  Suggestions for Implementation Environment  212  4.9.1 System Hardware and Software Options  212  4.9.2 Sources of Data Input  213  4.9.3 System Data Output  215  ix  4.10  Physical Implementation  217  4.10.1 Microsoft Access Terminology  217  4.10.2 Prototype Development  220  4.10.2.1 Developing Tables 4.10.2.2 Setting Default Relationships between Tables 4.10.2.3 Designing Queries 4.10.2.4 Creating Forms 4.10.2.5 Producing Reports 4.10.2.6 Creating Applications with Macros  5.  CONCLUSIONS  221 221 221 ...222 222 223  224  5.1  Thesis Review  224  5.2  Benefits and Applications of P M I C S  226  5.3  Experiences and Observations  5.4  Contributions  228  5.5  Extensions and Future Research  229  Bibliography  227  230  Appendices: A.  Matrix M - l Construction Personnel Vs Functions  236  B.  Work Breakdown Structure  241  C.  Data Dictionary  243  D.  Default Relationships  255  E.  Form Printouts  261  F.  Reports  268  X  List of Tables Table 4.1  General Key Terms, Abbreviations and Symbols  103  Table 4.2  Key Terms of Bill of Quantities  107  Table 4.3  Key Terms for Bid Summary Sheet  113  Table 4.4  Key Terms for Change Orders Tracking Log  119  Table 4.5  Key Terms for Correspondence Tracking Log  126  Table 4.6  Key Terms for Daily Site Report  131  Table 4.7  Key Terms for DWN Tracking Log  136  Table 4.8  Key Terms for Materials Stored Tracking Log  141  Table 4.9  Key Terms for Monthly Progress Report  146  Table 4.10  Key Terms for Photographs Tracking Log  150  Table 4.11  Key Terms for Punch Lists  Table 4.12  Key Terms for RFI Tracking Log  Table 4.13  Key Terms for Shop Drawing Submittals Tracking Log  166  Table 4.14  Key Terms for Spare Parts Tracking Log  170  Table 4.15  Entity Cross-Reference Matrix  207  Table 4.16  Entity Cardinality  210  Table 4.17  Expected Entity Sizes  211  Table C. 1  Data Element List for Bids  244  Table C.2  Data Element List for Change Orders  244  Table C.3  Data Element List for Company  245  Table C.4  Data Element List for Correspondence  245  Table C.5  Data Element List for Daily Events  246  Table C.6  Data Element List for Daily Weather (Weather)  247  ....155 160  xi  Table C.7  Data Element List for Days  247  Table C. 8  Data Element List for Defective Work Notice (DWN)  247  Table C.9  Data Element List for Materials Stored  248  Table C. 10  Data Element List for Monthly Progress  248  Table C. 11  Data Element List for Participant Type  248  Table C. 12  Data Element List for People  249  Table C. 13  Data Element List for Photograph  249  Table C. 14  Data Element List for Photo Type  250  Table C. 15  Data Element List for Project ItemDetails  250  Table C. 16  Data Element List for Project TradeDetails  250  Table C. 17  Data Element List for Project  251  Table C. 18  Data Element List for Punch Lists  251  Table C. 19  Data Element List for RFI  252  Table C.20  Data Element List for Shop Drawing  252  Table C.21  Data Element List for Spare Parts  253  Table C.22  Data Element List for Specs Section  253  Table C.23  Data Element List for Storage Location  243  Table C.24  Data Element List for Trades  254  Table C.25  Data Element List for WBS  254  Table C.26  Space Estimate for Tables  254  Table D. 1  Default Relationships  255  Table D.2  Queries and Join Tables  -257  :  xii  List of Figures Figure 1.1  Information Transaction in a Project Decision  9  Figure 1.2  Top-Down Planning versus Bottom-Up Design  14  Figure 1.3  Relational Database Example  20  Figure 1.4  Steps in Normalization  23  Figure 2.1  Contract Reporting Hierarchy  36  Figure 2.2  Routing of Contractor Submittals  37  Figure 2.3  Decision makers, information, and decision making in traditional framework  42  Figure 2.4  Entity-Relationship Data Model Examples  55  Figure 2.5  Structural Data Model Examples  56  Figure 3.1  Proposed System Development Framework  66  Figure 3.2  Phase I - Planning  68  Figure 3.3  Phase II - Requirements Formulation  71  Figure 3.4  Phase III - System Design  73  Figure 3.5  Phase IV - Implementation Phase  76  Figure 4.1  ABC Company Organization Chart  81  Figure 4.2  Participants/Information-Flow Diagram  85  Figure 4.3  Data/Information-Flo wchart  86  Figure 4.4  Personnel/Functions Matrix (M-2)  91  Figure 4.5  Personnel/InfoNeeds Matrix (M-3)  92  Figure 4.6  Document Type/Information Contents Matrix (M-4)  94  Figure 4.7  Hierarchy of TradeAVBS/Specifications  99  Figure 4.8  Specs Sections/Subcontractor Relationships  99  Figure 4.9  View-1 Bill of Quantities  105  Figure 4.10  E/R Diagram for Bill of Quantities  108  Figure 4.11  View-2 Bid Summary Sheet  110  Figure 4.12  E/R Diagram for Bid Summary Sheet  114  Figure 4.13  View-3 Change Orders Tracking Log  116  Figure 4.14  E/R Diagram for Change Orders Tracking Log  121  Figure 4.15a  View-4a Correspondence In Tracking Log  123  Figure 4.15b  View-4b Correspondence Out Tracking Log  124  Figure 4.16  E/R Diagram for Correspondence Tracking Log  127  Figure 4.17  View-5 Daily Site Report  128  Figure 4.18  E/R Diagram for Daily Site Report  132  Figure 4.19  View-6 Defective Work Notifications Tracking Log  134  Figure 4.20  E/R Diagram for Defective Work Notifications Tracking Log....137  Figure 4.21  View-7 Materials Stored Tracking Log  139  Figure 4.22  E/R Diagram for Materials Stored Tracking Log  142  Figure 4.23  View-8 Monthly Progress Report  144  Figure 4.24  E/R Diagram for Monthly Progress Report  147  Figure 4.25  View-9 Photographs Tracking Log  149  Figure 4.26  E/R Diagram for Photographs Tracking Log  151  Figure 4.27  View-10 Punch Lists  153  Figure 4.28  E/R Diagram for Punch Lists  156  Figure 4.29  View-11 Requests for Information (RFI) Tracking Log.....  158  Figure 4.30  E/R Diagram for RFI Tracking Log  161  Figure 4.31  View-12 Shop Drawing Submittals Tracking Log  ,163  Figure 4.32  E/R Diagram for Shop Drawing Submittals Tracking Log  167  xiv  Figure 4.33  View-13 Spare Parts Tracking Log  168  Figure 4.34  E/R Diagram for Spare Parts Tracking Log  171  Figure 4.35a E/R Diagram for Integrated View (Part A)  172  Figure 4.35b E/R Diagram for Integrated View (Part B)  173  Figure 4.36  Structural Model Diagram for Bill of Quantities  177  Figure 4.37  Structural Model Diagram for Bid Summary Sheet  179  Figure 4.38  Structural Model Diagram for Change Orders Tracking Log  181  Figure 4.39  Structural Model Diagram for Correspondence Tracking Log.... 182  Figure 4.40  Structural Model Diagram for Daily Report  184  Figure 4.41  Structural Model Diagram for DWN Tracking log  186  Figure 4.42  Structural Model Diagram for Materials Stored Tracking Log. ..188  Figure 4.43  Structural Model Diagram for Monthly Progress Report  190  Figure 4.44  Structural Model Diagram for Photographs Tracking Log  192  Figure 4.45  Structural Model Diagram for Punch Lists  194  Figure 4.46  Structural Model Diagram for RFI Tracking Log  195  Figure 4.47  Structural Model Diagram for Shop Drawing Submittals Tracking Log  197  Figure 4.48  Structural Model Diagram for Spare Parts Tracking Log  199  Figure 4.49  Integrated Structural Model Diagram  200  Figure 4.50  System Data Organization Diagram  216  Figure A. 1  Personnel/Functions Matrix (M-l)  237  Figure B. 1  Work Breakdown Structure  242  Figure E. 1  Data Entry Form: Correspondence Registry  262  Figure E.2  Data Entry Form: Daily Site Activity  263  Figure E.3  View Form: Bill of Quantities  264  Figure E.4  View Form: Shop Drawing Submittals Log  265  XV  Figure E.5  Switchboard Form: Main Switchboard.  266  Figure E.6  Switchboard Form: Print Reports  267  Figure F. 1  Bid Summary Sheet  269  Figure F.2  Bill of Quantities  270  Figure F.3  Change Orders Status Reports  271  Figure F.4  Company Listings  272  Figure F. 5  Daily Site Reports  273  Figure F.6  Weekly Activity Reports  274  Figure F.7  Monthly Progress Reports  275  Figure F.8  Punch Lists  276  Figure F.9  Requests for Information Reports  277  Figure F. 10  Shop Drawing Submittals Due Lists  278  Figure F. 11  Shop Drawing Submittals Status reports  279  xvi  Acknowledgments  I would like to thank my supervisor, Dr. Thomas M. Froese, for his guidance and encouragement throughout the preparation of this thesis. His dedication and patience are much appreciated. I would also like to thank Dr. Alan D. Russell for sharing his knowledge and literature on the topic of contract control software. I wish to extend my appreciation and thanks to Dr. Thomas M. Froese, Dr. Alan D. Russell and Dr. William F. Caselton for reviewing this thesis and for their constructive comments. Finally, warm and sincere thanks go to my wife Farhana for her financial supports and allowing me to concentrate on this work — And my daughter Kainat for her patience.  Dedication  This thesis is joyfully dedicated to — mother Saleha, —wife Farhana, and —daughter Kainat.  1  Chapter 1 INTRODUCTION  1.1  Introduction  This chapter introduces the basic problem of information management in the construction industry and gives an overview of the information control problem. The goals and focus of this thesis are explained, as is the research methodology. Finally, an overview of the thesis is given.  1.2  Goals  Accurate information is an absolute necessity for sound decisions. In the absence of timely decisions and actions, a construction project may face a number of problems. The problems may include excess cost and time, claims, safety concerns, etc. Contractors have become increasingly concerned with claims, their associated costs, the poor cost recovery prospects, and the time taken up by the process. Therefore, it is in the interest of every contractor and owner to avoid and resolve the problems at an early stage.  As technology progresses, construction projects are becoming increasingly complex. Construction engineers and managers are being bombarded by information at the site from  2  all directions. Unfortunately, the job-site office environment often does not lend itself to proper handling of all the day-to-day paperwork generated on most construction jobs. Proper maintenance of daily project information is crucial to successful project management. The more complete and informative the records, the more effective they will be in resolving any problems that may arise. To ensure safe and efficient operations at the project site, it will be necessary to develop and adopt a project-management information control system.  [Tidwell 92] states that there are three main areas of any project execution: Coordination, Communications, and Control. Coordination is the act of carefully planning what needs to be done to complete a project. Communications is keeping in touch with all the project participants or constituents. The third area is Control. Control is making sure that all our well-made plans are carried out. This thesis treats Control as one of the important areas of construction project execution.  Construction is going through a change in the routine approach to the execution of work. Managing a highly variable process such as construction involves the rapid processing of huge volumes of information in order to properly monitor and control project progress. The need has long existed for a resource to streamline the job of the construction professional. With advances in computer technology, computers have become a central component of information systems. A number of studies have demonstrated the potential benefits of computer-based information systems for construction projects ([Barnes 93],  3  [Chamberlain 91], [Krone 93], [Paulson Jr. 95], [Russell 93], [Tidwell & Leckington 94], [Tokar 90], [Wager 87]). Benefits claimed includes the following: rapid data processing, accuracy, quick retrieval of important data, maximizing project management efficiency, decision-making  support,  document  reduction,  responsibility  tracking, historical  documentation, etc.  [Walker & Hughes 87] suggest that a changing project environment requires project organization structures to be responsive and dynamic. They used linear responsibility analysis technique to develop case studies of project organization structures of private industrial projects, and they looked at organizational systems rather than data systems. They looked at people, the key players making strategic decisions, and at patterns of roles, responsibilities and relationships, that combine to form an organizational structure for a project. In his papers, ([Tenah 81], [Tenah 83], and [Tenah 86]), Tenah has also addressed the issue of management information organization and routing, and construction personnel role and information needs. These issues are also treated in this thesis.  A review of literature revealed several studies on project information system, but much of the literature deals with sophisticated and expensive systems. Small and medium-sized construction companies may not afford such systems. [Lock 92] states that in spite of all the great  advances  in computer and telecommunication  technology,  information  processing and retrieval systems are associated with significant cost, depending on the accuracy, timeliness, and sophistication of the data systems requirements. He further  4  suggests that it is not surprising that with its potential for great cost saving and increased business effectiveness on one side, and substantial capital investment and operating cost on the other side, systematic information management has become an increasingly important part of overall organizational control.  With these things in mind, the primary goal of this thesis is to develop a projectmanagement information control system suitable for small and medium-sized construction companies, and capable of tracking down problems. Specifically, the use of personal computer based software has been used. The secondary aim is to introduce a set of matrices for organizational structures and documents for a construction site including key decision makers; their roles, responsibilities and relationships; and their information needs. This thesis will also outline a number of information tracking logs typical for a construction project.  1.3  Objectives  The objectives of this thesis are following:  •  The first objective is to collect and review literature to determine how different documents are used by construction-site management personnel for solving problems, pursuing claims, providing instruction to site personnel, etc.  5  •  The second objective is to develop a computer-based project-management information control system to enhance the problem solving and information management abilities of site management personnel. The use of the latest development in the computer technology will be emphasized to manage all the information in such a way to allow quick access to the right information at the right time, and to allow it to be shared by various disciplines and parties involved in the project.  1.4  Research Methodology  For this thesis, the following research methodology has been adopted:  1.4.1  Literature Review  The first stage of research involved a thorough literature search of several fields of study, such as: documentation, organization, personnel roles, claims, risk, multimedia, integrated information management, economics and construction management, and civil engineering computing. This literature search has aided in the understanding of documents and their purpose, construction personnel role and responsibilities, the latest advances in integrated information technology, etc. The search has also aided in the development of the ideas and focus of this research, e.g. the analysis of results of literature review leads to the production of a framework for construction documentation, particularly a breakdown of  6  documents, a breakdown of participants, a document/participant matrix, and common data items across documents, etc.  1.4.2  System Development  The next step in the research involved the development of an information system based on the literature search and findings. It was found that much of the literature to date on project control dealt with sophisticated methods for information management which, in my opinion, will be expensive for a small contractor. Because of the diversity of the system options, cost can only be discussed in generalities.  So, from a small contractor's  perspective, a PC based software solution to manage project information is considered to be appropriate.  1.5  Overview of Information Control Problems  This section introduces and examines a number of the issues central to information control problems and puts forward some key concepts essential to the assurance of quality and value in the development of information systems.  7  1.5.1  Information Management and Construction Projects  Several papers have pointed out the shortcomings of traditional tools to provide information for construction project control ([Sanvido & Paulson 92], [Tokar 90], [Liu et al 94b]). These tools have been cost and progress reports comparing expended cost to the budget, and actual progress against scheduled activities, which evolved from accounting practices and project planning techniques. These reports focus only on one aspect of the process, namely the end product—they rarely identify the causes of problem when a problem occurs. Further, present day projects are complex in nature due to the involvement of a large number of individuals, firms, and organizations, and above all, more complex building systems. Hence, information required from management systems has increased.  For a contractor, the management of project information begins the day the decision is made to bid on a contract and continues well after the close-out phase [Kangari 95]. The construction industry is information intensive. It needs accurate, reliable, and timely information regarding legal requirements, building codes and standards, manufacturer's specification, site-specific data, and past projects, etc.  8  1.5.2  Data Sources  In a construction industry, data comes from numerous sources, including contract documents and agreements, project manuals, the master schedule, building codes, test data, survey reports, bid tabulation forms, estimates, cost records, quantity take-offs, value engineering studies, requests for proposal, requests for information, change orders, field reports, meeting minutes, RFI (requests for information) logs, shop-drawing submittal logs, and so forth.  [Tokar 90] defines the following two types of input data (with different input requirements): objective data is information that can be directly taken from the document, such as author, recipient and date; subjective data requires decision making and some level of expertise regarding the ultimate requirements of the system.  1.5.3  Information Reference Sources  [Leslie & McKay 93] states that a project decision will come from three major sources (Figure 1.1). They are: the personal knowledge and experience of the decision-maker or peer, reference sources (research, trade literature), and the current project description (an amalgamation of earlier project decisions).  9  P R O J E C T DATABASE  Figure 1.1  Information Transaction in a Project Decision (Source: [Leslie & McKay 93], p 102)  [Couzen et al 93] have discussed information sources under the following two general classifications:  •  Location of the information source: This refers to whether the information source is located inside or outside the organization.  •  Medium of information transmission: This refers to five basic media: mail, telephone, unscheduled meeting, scheduled meeting and tours.  10  1.5.4 Information Users and Routine of Information  The term "information users" refers to who needs the information, or who information is sent to. In this category the users are: individual managers, departments within the organization, other project participants, etc. Control information spans across functional disciplines; for example, materials will be dealt with by estimators, buyers, planners, site managers, and surveyors. A matrix showing personnel and their information needs is one way to examine the users of the information systems.  [Peters 8 4 ] suggests routing of information to the right people by assigning tasks and responsibilities through a project responsibility matrix. In a project responsibility matrix, all functional specialists who have a role to play occupy a position in the matrix where their responsibilities are well defined. He writes that such matrix will also be a framework of an information system in which appropriate information is channeled to the right people in the project.  [Mintzberg 7 3 ] suggests the information collected by executives and senior managers is used in the following four ways: ( 1 ) to disseminate it to others; ( 2 ) to develop value positions for the firm; ( 3 ) to identify business problems and opportunities; and ( 4 ) to develop mental images—models of how the organization and its environment function.  11  1.5.5 Information Generator  As mentioned earlier, a construction project involves a number of individuals, disciplines or departments within the organization, and other participants. Each one is a generator of information within the scope of his responsibilities. Again, the same matrix as discussed in section 1.5.4 will help identify these sources.  1.6  Overview of Databases and Database Management Systems  A database can be seen as an attempt to overcome some of the limitations imposed by conventional filing systems, such as uncontrolled redundancy, inconsistency, difficult data sharing, and modification inflexibility. It is a database management which allows users to create and maintain a database system. This section presents an overview of some of the concepts used in the database system design.  1.6.1  Database Concepts  [Burger & Halpin 77] states that the database concept provides a suitable environment for a sophisticated user-oriented project information system. Data used and generated at the project level is stored and maintained in a highly integrated structure instead of separate application files. The approach permits organization of data in hierarchical, network or relational system. [Tenah 83] states that the planning, designing, and controlling of  12  information and information systems rely on an extensive database of high quality to be a useful tool in solving the complex problems of today and of the future. [Bengtsson & Bjornsson 87] advocates computerized data-base systems for timely and easy retrieval of data, as the traditional methods for data gathering, such as stop-watch studies and random observations, are time-consuming and often expensive.  [Bowler 94] points out the  importance of a relational database management program (RDBMS) in the engineering office. He discusses the use of database applications in the following areas: technical, project management, business development and administrative applications.  [Townsend 92] states that a database systematically stores information for retrieval or analysis, and describes two types of databases that dominate small computing, namely: flat-file and relational databases. In a flat-file all the data or information are entered in one large file, and accessed individually. This type of database works well for small amounts of data, and includes some duplicated data. Flat-file databases are typically easier to use and less expensive than relational databases. These systems are usually for single users. A relational database breaks its data into logically grouped parcels or files, stores information in tables or forms, and avoids duplication of data. The information in different tables can be related through links established by a column or field. A relational database management systems (RDBMS) can be utilized from simple to very complex database requirements.  13  A database can be seen as an attempt to overcome some of the limitations imposed by conventional filing systems pamford & Curran 91]. It is the database management which allows the user to create and maintain a database system. The database is a physical implementation of an information or data model. Entity relationship (ER) diagrams are one means of formally expressing a data model.  [Hamilton 91] states that by using a relational database, record management process can be made easier. For instance, tracking down information about progress and location of shop drawings within a firm; listing of present and past projects; correspondence, calculations, telephone memorandums, etc.  [Lock 93] states that the foundation of any MIS (Management Information System) is its data bank which contains the raw data that will be accessed by the MIS user, either directly or indirectly via analytical method or models. For this purpose a Database Management System (DBMS) is needed which permits simultaneous access to the many different data banks that exists throughout the company.  1.6.2  Top-Down Planning versus Bottom-Up Design  [McFadden & Hoffer 88, p67-68] state that both top-down planning and bottom-up designs are required for the development of corporate databases. As shown in Figure 1.2, the top-down planning starts with basic organizational goals and objectives, including  14  functions, processes, activities, entities, etc., and then develops an information model followed by databases. Whereas in bottom-up design, design begins with user views of data followed by normalization to develop detailed data model. In database design, the data models are checked against the information model to ensure that they are complete and accurate.  Top-Down •<  Business system planning  Physical (distributed) data bases  Information model  Data models  Proposed data bases  User views  V Bottom-Up  \J  Figure 1.2  1.6.3  Top-Down Planning versus Bottom-Up Design (source: [McFadden & Hoffer 88], p68)  Database Terminology  Some of the terms used to describe data and its presentation, as defined by [Atre 80] and [Bull 90], are as follows:  15  An enterprise is any kind of organization. For example, a bank, a university, a company, or a hospital. A single object is called an entity about which information is recorded. For example, person, place, thing, event, or concept. Each individual entity must be unique so that it is distinguished from others. Properties of objects included in data entities are called attributes. For example, a house can be described by its size, color, age, and surroundings. A data value is the actual data or information contained in each attribute. The values taken by the attributes can be quantitative, qualitative, or descriptive, depending on how the attributes describe an entity. For example, account type (checking), account number (1234567), and account balance (20.53).  A key attribute of an entity has the property that identifies the data values of other attributes of the same entity. Key attributes can also be called entity identifier. A data record is a collection of values taken by related attributes. The data records are stored on some medium. For example, a piece of paper, a human brain, a memory of a computer, or a computer disk. A data file is a collection of data records. For example, a manager's report.  A relationship is the association of an entity with other entities. A relationship exists between two entities if they share one or more common attributes. A n entity does not exist in isolation, but is associated with other entities by means of a relationship. If relationships are considered logically, there will be, at the most, five possible types of relationship. They are as follows: one-to-one, one-to-many, many-to-one, many-to-many and none-to-none.  16  In a one-to-many relationship, the entity at the one end is called the master entity and that at the many end is called the child or detail entity.  The view of data of an individual user is called user's view. Each user's view will be just a small subset of the entire data within the database. The actual data which falls within each user's view at any particular moment is known as an instance of the view. Whenever a data value is changed, added to a new record or deleted from an old record, the user's view stays the same but the instance changes.  A sub-schema or external schema is the description of the data contents of each view. Each sub-schema will represent one local data model. During the requirements analysis, a number of documents, screen layouts and reports are obtained, produced and designed which form a part of the proposed computer system. There will be one sub-schema for each document, and it will contain all the data which is necessary to support the document. A schema or global data model is the description of the entire database.  1.6.4  Data Modeling  The first step in designing a database is to determine which objects to represent within the database and which of the objects' properties to include. This process is called data modeling. Several authors have explained the data modeling in details ([Atre 80], [Bamford & Curran 91], [Jennings & Person 93] and [McFadden & Hoffer 88]). The  17  process of generating a working database involves the creation of the following three separate models: the conceptual model, the logical model and the physical model.  The conceptual model is based on the perception of an organizational area or a problem which is being examined. The term can be applied to an informal model which can consist of reports in use, documentation produced by fact finding, and so on. The term can also be applied to a more formal model which shows entities and their relationships but is independent of any particular database management systems (DBMS). The conceptual model gives an overall view of the flow of the data in the organization.  The logical model is derived from the conceptual model and is a formal representation describing data items and their structural relationships. The conceptual model is translated into a data model compatible with the chosen DBMS. It is possible that the relationship between entities as reflected in the conceptual model are not implementable with the chosen DBMS. The various DBMSs impose a number of constraints and rules for implementation to the physical database. For example, some D B M S insists that only two relationships need be used on the data model: one-to-one and one-to-many. Many-tomany relationships must be resolved into two  one-to-many  relationships by the  introduction of a third link entity. In such situations, a modification to the conceptual model is made to reflect these constraints. The version of the conceptual model that can be presented to the D B M S is called a logical model. The logical model is either a relational, a hierarchical, or a network data model.  18  The final stage of data modeling is to translate the logical schema into the physical database. The physical model is the actual implementation of the database consisting of the data tables or files with supporting software. The translation process of the logical schema to a physical model is performed by the D B M S software.  1.6.5  DBMS versus Data Models  [Atre 90], [Date 90] and [McFadden & Hoffer 88] describe the different data models and their related database management systems software. This section is limited to the description of the different data models.  The data models in use as the underlying structures for database management systems are the following: relational, hierarchical and network. The main difference between the three data models is the way they represent the relationships of entities. All the three approaches have been as the basis for commercial-scale data base management systems.  In a relational model, the entities and their relationships are presented with twodimensional tables. The relationships are also called entities. Every table represents an entity and is made up of rows and columns. The relationships between the entities are of the one-to-one and the one-to-many type. C.J. Date, one of the earliest contributors to relational-database theory, defines a relational database as "a database that is perceived by  19  its users as a collection of tables (and nothing but tables)" (1990, 112). This concept is fundamental to a relational database because it allows for data independence, the isolation of data from the mechanism the D B M S uses to access that data.  A hierarchical data model is made up of a hierarchy of entity types, and is presented as a set of tree structures. The chief elements are segment and the parent-child relationships. A segment is equivalent to the node of a tree structure. Segments are grouped into segment types, and each segment type has a name. A parent-child relationship is a one-to-many relationship between two segment types. The parent type is at the one end and the child type is at the many end.  A network model presents its data as a series of records linked one to another within their relationships. The word set is used to denote relationship between entities. The relationships are of many-to-many and one-to-many type. The relationships between record types in any one area are described by set types. The owner record type is at one end of the relationship and the member record type as at the many end.  1.6.6  Relational Databases Terminology  Some of the common terms of the relational databases, as explained in [Bull 90] and [MS Access 92], are presented in the following paragraphs. The example is based on the table shown in Figure 1.3.  20  A relational database is organized as a set of tables of data called relations (tables or files). Each table has a name and comprises a number of tuples (rows or records) and attributes (columns or fields). The domain of an attribute is the set of the possible values of an attribute for it. For example, the domain for the attribute Employee Number in Employee relation is all integers of 4 digits. The identifying attributes of an entity are called the primary keys. For the relation the primary key is Employee Number.  A table has the following properties: each relation has a name; no two tuples can be the same; the order of tuples is not important; each attribute must have a unique name; the order of attribute is significant; at the intersection of any attribute and tuple there is a single data value; the values in any one column are from the same domain; each tuple is uniquely identified by its primary key made up of one or more attributes; the primary key must not be null.  Employee Number  Employee Name  Age  Employee Address  1010  Green, W.  45  205 West Ave  1245  Smith, G.  36  540 High Street  1345  Brown, P.  29  999 North Ave  1459  Wise, M .  42  987 Curve Street  1514  Wilson, K .  32  123 Long Road  t  attributes  Relation: Employee  Figure 1.3  Relational Database Example  tuples 6-  21  The number of columns in a table is a measure of the degree of relation. A relation of degree 2 is called a pair. A relation of degree n is called an n-tuple. The above relation is of degree 4 that is, a 4-tuple. The number of tuples in a table is a measure of the cardinality of the relation. The Employee relation shown above has a cardinality of 5. A relation can be described by a schema consisting of the name of the relation, the attributes enclosed in brackets, and the primary key underlined. For example:  Employee (Employee Number. Employee Name, Age, Employee Address)  If each tuple is identified by a composite key made up of more than one attribute, then all the identifying attributes will be underlined. A relation which has no repeating groups, as discussed in section 1.6.8, is known as a flat file. A relational database can be implemented fairly easily by means of a flat file.  1.6.7 Normalization  [Bamford & Curran 91], [Date 90], [McFadden & Hoffer 88], and [Rasdorf & Herbert 88] have described the normalization process in a database design as follows:  The technique of normalization is used to transfer conceptual data model into a form acceptable to relational database. It is a method used by relational data model designers to  22  design a good data-base schema by creating data-base tables that eliminate redundancies in the stored data and protect the data-base from insertion and deletion anomalies (problems), thus preserving its correctness and its integrity. This way, when changes are made in a well-designed database, errors will not occur and meaning will not be lost. Thus normalization is the process of decomposing large tables into smaller ones that are free from anomalies. The general aim of normalization is to produce a set of entities which will support the users' needs and which will conform to the requirements of the database.  The standard steps of the normalization process are shown in Figure 1.4. The first step is to remove any repeating groups from the relation. When this step has been taken, the data is said to be in the first normal form (INF). The second step is to ensure that, if the relation has a composite key then any data attribute in the relation should be functionally dependent upon the whole key and not just a part of the key. A data attribute which is functionally dependent upon just a part of a composite key is removed to form a new relation. When this step has been taken, the data is said to be in the second normal form (2NF). The third step is to remove any attributes which are functionally dependent upon other attributes, and to put these in a separate relation. When this step has been taken, the data is said to be in the third normal form (3NF).  It is claimed that relations in the third normal form are sufficient for most practical data base design problems. There is some academic discussion about Boyce/Codd normal form (BCNF), fourth normal form (4NF) andfifthnormal form (5NF).  23  User Views  Unnormalized relations  Normalized relations (INF)  remove repeating groups  remove partial dependencies  Second normal form relations (2NF) remove transitive dependencies Third normal form relations (3NF)  Figure 1.4  Steps in Normalization  (source: [McFadden & Hoffer 88], p249)  1.6.8  Repeating Groups  The concept of repeating groups is explained through the following examples. It is assumed that a construction organization has several projects, and each project has several trades. A relation for a project can be expressed by a schema in the following form:  Project (ProjectlD. Project Name, Address)  24  The above expression shows the name of the relation and the attributes which it contains. The primary key which identifies the tuple is underlined and the attributes are enclosed in brackets. As discussed in the section 1.6.6 that at the intersection of any tuple and any attribute there is a single data value. However, when investigating the real world data, one may find that certain pieces of data are repeated. That is, within one entry of the table, a certain attribute may have multiple data values. For example, if it is decided to maintain details of budget of the trades. Then the relation Project can be written as:  Project (ProjectlD. Project Name, Address, (TradelD, Trade Name, Budget))  The innermost bracket of 'TradelD, Trade Name, Budget' is known as a repeating group. In some cases repeating groups may be present within repeating groups. For example, a bidder may bid for several trades on a project, then the relation Project can be written as:  Project (ProjectlD. Project Name, Address, (TradelD, Trade Name, Budget, (BidderTD, Bidder Name, Bid Amount)))  The relation Project contains two sets of repeating attributes (attributes within inner brackets), where there will be several sets of (ProjectlD, TradelD, BidderTD, Bid Amount). For a given occurrence of ProjectlD there will be many TradelD, and for a  25  given ProjectID and TradelD there will be many BidderlD. If a relation contains a set of repeating attributes, the relation is said to be unnormalized.  1.6.9  Normalization Examples  The normalization process is explained through the following examples. The users' view or the sub-schema for the Bid Summary Sheet (BSS), as discussed in section 4.5.2, is expressed as follows:  BSS (ProjectID. Project Name, (TradelD, Trade Name, Budget, StartDate, EndDate, Bid Due Date, Lowest Bid*, (BidderlD, Bidder Name, Bidder Contact, Bidder Phone, Bid Pkg. Sent, Bid Received,  Bid Amount,  Difference*, File Ref. no.*)))  The entity BSS contains two sets of repeating attributes (attributes within inner brackets). For a given occurrence of ProjectID there will be many TradelD, and for a given ProjectID and TradefD there will be many BidderlD. As discussed in section 1.6.8, if a relation contains a set of repeating attributes, the relation is said to be unnormalized. The remainder of the section deals with the ways in which repeating groups can be removed, and the normalization process. The entity BSS is normalized as follows:  26  •  First normal form (INF): To normalize the above relation to (INF), the  repeating groups are removed and the entity identifier of the original entity is made an attribute of the new entity, resulting in the following relations:  Project-1 (ProjectlD. Project Name) Trade (ProjectlD. TradelD. Trade Name, Budget, StartDate, EndDate, Bid Due Date, (BidderlD, Bidder Name, Bidder Contact, Bidder Phone, Bid Pkg. Sent, Bid Received, Bid Amount))  The entity Trade uses both TradelD and ProjectlD to identify an occurrence of the entity, as it is assumed that each ProjectlD is unique but the same trade may be present on several projects. To complete INF, the entity Trade is converted into INF by removing repeating groups as follows:  Trade-1 fProjectlD. TradelD. Trade Name, Budget, StartDate, EndDate, Bid Due Date) Bids-1 fProjectlD. TradelD. BidderTD. Bidder Name, Bidder Contact, Bidder Phone, Bid Pkg. Sent, Bid Received, Bid Amount)  Thus the original entity BSS has now been normalized into three new entities, Project-1, Trade-1, and Bids-1.  27  •  Second normal form (2NF):  Only entities which have more than one  identifying attributes are involved in the transformation from first to second normal forms. As entity Project-1 has a single identifying attribute, its 2 N F is same as INF. Trade-1 has two identifying attributes. Their relationships with other attributes is explained as follows:  Trade-1 (ProjectID. TradelD. Trade Name, Budget, StartDate, EndDate, Bid Due Date)  It is obvious that only 'Trade Name' depends on TradelD, where as the attributes Budget, StartDate, EndDate and Bid Due Date depend on both TradelD and ProjectID, as all these attributes vary for a trade from project to project. So, Trade-1 becomes as follows:  Trade-2 (TradelD. Trade Name) TradeDetails-2 (ProiectlD. TradelD. Budget, StartDate, EndDate, Bid Due Date)  Similarly, the functional dependencies of Bids-1 are explained as follows: Bids-1 (ProiectlD. TradelD. BidderlD. Bidder Name, Bidder Contact, Bidder Phone, Bid Pkg. Sent, Bid Received, Bid Amount)  It is found that the attributes Bidder Name, Bidder Contact and Bidder Phone depend only on the identifying attribute BidderlD, but the attributes Bid Pkg. Sent, Bid Received, and Bid Amount depend on all the three identifying attributes (BidderlD, TradelD, ProjectID)  28  because, as it is assumed, a bidder may submit bids for several trades and projects at the same time. Based on these assumptions, Bids-1 becomes as follows:  Bids-2 (ProjectlD. TradelD. BidderlD. Bid Pkg. Sent, Bid Received, Bid Amount) Bidder-2 (BidderlD. Bidder Name, Bidder Contact, Bidder Phone)  So, for 2NF there are five entities as follows: Project-2 (ProjectlD. Project Name) Trade-2 (TradelD. Trade Name) TradeDetails-2 (ProjectlD. TradelD. Budget, StartDate, EndDate, Bid Due Date) Bids-2 (ProjectlD. TradelD. BidderlD. Bid Pkg. Sent, Bid Received, Bid Amount) Bidder-2 (BidderlD. Bidder Name, Bidder Contact, Bidder Phone)  •  Third normal form (3NF): All the abovefiverelations are in 3NF, as there are  no transitive dependencies. However, if it is decided to specify the type of a bidder such as Subcontractor, Supplier. Again, in Bidder-2, the attribute Bidder Contact is the name of a responsible manager of the bidding organization. The attribute Bidder Contact can be linked to a new entity People. Thus, the entity Bidder-2 would be written as follows:  Bidder-2 (BidderlD. Bidder Name, Bidder Phone, People ID, Last Name, First Name, Type ID, Participant Type)  29  Now, the entity Bidder-2 has new attributes of People ID (responsible manager's ID), Last Name (manager's last name), First Name (manager's first name), Type ID (participant type ID) and Participant Type (name of participant type). These new attributes are unaffected by previous two stages of normalization. On considering the functional dependencies in the relation above, it is noticed that the attribute People ID uniquely defines the Last Name and First Name. Similarly, the attribute Type ID defines the Participant Type. So to transform Bidder-2 into 3NF, the new relations are as follows:  Bidder-3 (BidderlD. Bidder Name, Bidder Phone, People ID, Type ID) People-3 (People ID. Last Name, First Name) Participant Type-3 (Type ID. Participant Type)  As each of the above relations is in 3NF, so the normalization steps are complete. Therefore, the set of 3NF relations for the Bid Summary Sheet are as follows:  Project (ProjectID. Project Name) Trade (TradelD. Trade Name) TradeDetails (ProjectID. TradelD. Budget, StartDate, EndDate, Bid Due Date) Bidder (BidderlD. Bidder Name, Bidder Phone, People ID, Type JD) Bids (ProjectID. TradelD. BidderlD. Bid Pkg. Sent, Bid Received, Bid Amount) People (People ID. Last Name, First Name) Participant Type (Type ID. Participant Type)  30  1.6.10 Data Dictionary  Much of the information concerning the data is specified by means of a data dictionary [Bull 90]. Data dictionary is a set of system tables that contain the data definitions of the database objects. It is independent of any database management system (DBMS). It is an important feature of a D B M S and acts as a general reference tool describing all the data held on the database. There will be a data dictionary for each file (table), with one entry in the data dictionary for each piece of data on the file.  1.7  Focus of the Thesis  The main focus of this thesis is analyzing and managing project information, particularly as a means of tracking down problems on construction projects. It builds on database software called Microsoft Access, and spreadsheet software called Microsoft Excel, where all the relevant information will be well documented and shared by all the parties involved. The system adopts the most commonly used tracking logs and other information sheets on construction sites.  31  1.8  Overview of the Thesis  Chapter 2 outlines the literature review which is relevant to this thesis. Chapter 3 presents the framework upon which this work is based and describes its steps. Chapter 4 describes the system analysis and design. Chapter 4 also presents entity-relationship diagrams and structural model diagrams of all the user views in the system, including entities and relationship  attributes,  data volume  and usage,  suggestions for  implementation  environment, and physical implementation. The last section of Chapter 4, section 4.10, deals with the prototype development. Chapter 5 presents a summary of the thesis, discusses its application, describes contributions and conclusions, outlines deficiencies in the model, and gives recommendation for future work.  Appendix-A contains matrix (M-l) of construction personnel versus functions. AppendixB contains work breakdown structure. Appendix-C contains data dictionary. Appendix-D contains the lists of default relationships of tables. Appendix-E contains printouts of the forms. Appendix F presents reports and summaries.  32  Chapter 2 LITERATURE REVIEW  2.1  Introduction  A number of authors have highlighted the importance of information systems for the construction industry. This section reviews some of the literature which is relevant to this thesis. Itfirstlooks at documents, records and reports in the construction industry; then reviews issues of information and construction management, information and information systems, and related works; andfinallyexplores the use of matrix as an analytical tool.  2.2  Documents, Records and Reports in the Construction Industry  2.2.1 Document Classifications  [Sargent 93] classifies engineering documents as follows:  •  Item Document: an item document is an individual piece of information which is managed as a single unit, such as a letter, an order or a particular revision of an engineering drawing. An item document is never revised. Its contents arefixedwhen it  33  comes into existence and never change. It is filed and archived with a unique reference number which, similarly, never changes.  •  Compound Document: A compound document is a collection o f information possibly from several sources, which appears to be a single document to the reader, especially when it is printed and bound as a single report. It is created from many items, updated semi-automatically as the items which they quote are revised by other people in the organization. A compound document can be issued and filed for future reference as a single item document, in which case its contents become frozen at the moment it is issued. A compound document is merely a snapshot o f the current status o f some set o f information: it usually accesses databases to quote the most recent revision o f some engineering product, automatically quoting the appropriate item document. Examples include progress reports (quoting directly from company databases), change notices (quoting from engineering drawings), field reports, failure notices and so on.  2.2.2  Project Documentation  During the construction process a variety o f documents are generated, depending to some extent on the construction management  procedures followed  by the organization  [Ganeshan et al 94]. Typical documents include submittals (shop drawings, test reports, manufacturers' data, etc.), correspondence, requests for information, change orders, and inspection reports (entered as text, voice or video recordings). Additionally, there may be  34  other documents generated as part of the project control process such as daily reports and photographs.  [Kangari 95] focuses on arbitrators' perception of the effects  of poor project  documentation on the arbitration process, and their advice to construction professionals to help them properly record project information before a dispute arises. He presents results of a survey questionnaire collected from arbitrators from seven different US cities. The paper also outlines recommendations made by the arbitrators to a hypothetical client on record keeping, organizing, storing, and securing project documentation and day-to-day job information. The important documents recommended include the following:  •  Detailed daily reports outlining work force levels, trade reports, job progress, work descriptions, inspections, equipment used, material deliveries, weather, unusual conditions, and other factors.  •  Jobsite log maintained at site and faxed to home office every day.  •  Progress photos taken at least weekly and always of critical items, dated, signed and filed.  •  Chronological files of all job activities, e.g., delivery tickets, field orders, and payment requisitions.  35  •  All correspondence maintained and answered promptly to avoid problems later.  •  Requests for information, change orders,fieldsdirections, shop-drawing submittal log, bid tabulation forms, meeting minutes, estimates, cost records, quantity takeoffs, and so on.  2.2.3  Reporting Hierarchy  In order to achieve reporting that allows management by exception, data tractability, and timely response to actual or potential problems, [Gilbreath 83] recommends a structured system of periodic reporting as depicted in Figure 2.1. The documents presented therein show a recommended arrangement of (1) source documents; (2) contractual tracking and compliance documents; (3) detailed contract reports covering schedule, claims, change orders, and backcharges; all leading to a (4) summary report for higher management.  36  Monthly contract cost report Integration of information  Data traceability  Contract formation schedule report  Contract formation schedule  CHZr-rj-rJ-i  Project schedule  Summary management reporting  Change request and claim log  Change order summary  Backcharge summary  Change order log  Backcharge log  Detailed contract reports  Tracking aids 4  1  Contract letters and claims  Contract letters and claims  Contract letters and claims Source documents 4  Progress payments  Figure 2.1  forecasts  Contract Reporting Hierarchy (Source: [Gilbreath 83], p 252)  37  2.2.4 Routing of Submittals  [Fisk 93] describes the general procedure of handling submittals (as shown in Figure 2.2) in order to avoid excuses for loss or delay in a submittal or transmittal among architect/engineer (A/E), general contractor, and subcontractors. Such submittals include: shop drawings, samples, certificates, or other similar items.  Subcontractors  Out  General contractor  In  In  -Out—>Resident project representative Routing procedures for submittals being returned to the contractor  Routing procedures for submittals being received by the A/E A/E project manager  Design dept reviewers  Figure 2.2  Routing of Contractor Submittals (Source: [Fisk 93], section 17-1)  38  2.2.5  Computerized Daily Site Reporting  [Russell 93] describes a computerized approach for collecting and processing site information that builds on the traditional superintendent's daily site report, and claims benefit for getting current status of a project; faster response time in dealing with problems; integration of site reporting; project planning/scheduling functions; speedier updating; help in dealing with claims; and documentation of experience in a form useful for future projects.  [Rasdorf & Abudayyeh 92] quotes an example of a Daily Time Sheet and Report to describe  the  principles and concepts of N I A M  (Nijssen's Information Analysis  Methodology) modeling methodology for a relational database model.  2.2.6  Computerized Change Management  The inevitable changes on construction projects requires an administrative process, which can be streamlined with computer applications. [Krone 93] discusses the benefits of managing construction change orders with computers over manual methods. The automated method avoids management disorder by maintaining records, providing a clear and timely reminder of who is responsible for the next step in the change procedure, and providing standardized reports.  39  [Leymeister et al 93] describes a computer application for analyzing and managing change order work at several projects. They illustrate how a well planned database application on a personal computer (PC) proved extremely useful and versatile in processing the voluminous amount of data involved. They also discuss several types of databases which were developed for specific functions needed to analyze the data submitted for each of the contractor's claims. The types of databases used were: labor and equipment database, material database, subcontractor database, labor database, and linked labor and equipment database.  2.2.7  Claims Analysis  One of the problems associated with conducting a claims analysis is the meticulous sorting of project documentation to ascertain pertinent delay information. [Mazerolle & Alkass 93] propose a database management system in a project control process to store information on each delay when it occurs. They further outline the advantage of such system, such as: keeping track of delay information; easy retrieval; and assistance in ascertaining the type of delay, indicating which party is liable, and what actions should be taken.  40  2.2.8  Photo Records  [Hiroshi & Nobuoh 93] describes a filing system of construction pictures and its integration with a database. Here the proposed system is claimed to have the capability of supplying construction information through joint operation with existing databases which can perform multi-phase retrieval and index transaction.  [Maher 78] suggests that the statement, "A picture is worth a thousand words", be given practical effect by using photograph to help solve the problems occasioned by time-delays in construction projects. Acceptance of a photographic record, properly formed and used to present evidence in time delay situations, would be one method of saving time and money.  2.3  Information and Construction Projects  2.3.1  Construction Personnel Role and Information Needs  [Lock 93] states that the type of information required by an organization depends largely on its business and its people, and it is this organizational environment that determines what information must be provided, how it is to be organized, formatted, and accessed to be useful for aiding decision-making and for the general management process.  41  [Sanvido & Paulson 92] state that as people progress from lower levels in a site organization to higher levels, they use different information, skills, and knowledge to make new decisions. [Sanvido 88] developed a model representing the functions performed by each level in a hierarchical organization. He also defined the feedback and information required to support the control and planning functions.  [Tenah 86] acknowledges that there is a lack of information in the construction industry regarding functions, responsibilities, and information needs of construction personnel, whereas a manager or supervisor cannot perform his/her functions efficiently without proper information on which to base decisions. He further discusses how the information required by the construction personnel is organized into reports, the contents of the reports, the purpose(s) they serve, and the frequency at which they are issued. In the same paper, he presents results of a field study conducted to determine the information needs of projects and their functional managers. In his concluding remarks he says that, since management functions and management information needs are inextricably linked, there is the need to redefine personnel functions, responsibilities and information needs after revising each organizational structure.  In any project management system, the people who make up that system should be aware of their precise role. The project responsibility  matrix is a means of achieving this. In the  project responsibility matrix all functional specialists who have a role to play occupy a position in the matrix. [Tenah 86] and [Deatherage 64] describe some of the roles and  42  information needs of key construction personnel. As part of this thesis, Appendix-A presents matrices of construction personnel versus functions and construction personnel versus information needs.  2.3.2  Traditional Information Flow Framework  [Goldhaber et al 77] illustrate a traditional framework of information flow in a construction industry (Figure 2.3). In such a system, it is assumed that all parties and levels of management have the same information and that all must come to the same conclusion. The same reports are generated and distributed to all levels of management. These assumptions do not reflect the actual conditions and information needs of the decision makers. Different decisions are made at different levels. Unless meaningful and timely information is generated, there is no justification of an information system.  Top management Information  Middle management  Project management  Project staff Computer  Figure 2.3  1  System staff  Network systems  Decision makers, information, and decision making in a traditional framework (Source: [Goldhaber et al 77], p 116)  43  2.3.3  Information Flow Requirements  For an organization to function properly, it is essential to encourage information to flow freely in all directions, i.e., upward, downward, and laterally [Tenah 83]. The flow of information is a necessity in order to help construction personnel carry out their duties more efficiently and effectively. This allows managers to learn projects and their problems simultaneously so that decisions and instructions can be made quickly. Reports are the key function of the information system. It must be determined who should get what and when, and not to flood the user with more data than he or she needs.  Keeping in mind the importance of information flow and providing the right information, this thesis will also focus on the different sources of information and documents used at construction sites; their purpose, origin, destination, contents, and frequency. As part of this thesis, a matrix has been developed showing the inter-relationship between project information and documents (Appendix-A).  44  2.4  Information and Information Systems  2.4.1  Information Definition  The concept of information is related to facts, data and knowledge. A fact is something that has happened in the real world and that can be verified [Barton 85]. Data are facts obtained through empirical research or observation. Knowledge is facts or data represented in some way (e.g., reports, lists, letters, etc.) and stored for future reference. Information represents data or knowledge evaluated for specific use. Consequently, facts or data are processed to provide meaningful information.  [Murdick & Ross 75] defines information as "the behavior-initiating stimuli between sender and receiver and in the form of signs that are coded representations of data". [Tenah 83] states that the information is a behavior-affected data. Data differ from information in a sense that data are considered signs, usually recorded observations, that do not affect the behavior of men or machines. However, data may become information if behavior becomes affected. For example, the database for computer systems consists of masses of such signs that are not affecting behavior. Until the data are properly organized for a manager so that he/she reacts to them, they are not information. Thus management information is not just the forms and reports produced. It includes all the data and intelligence—cost, financial, schedule, trend forecast, and control of a particular project or job, as well as the organization as a whole.  45  [Aoki et al 93] define information not only as measurement data, but also as expertise and construction data collected from previous projects.  2.4.2 Management Information and the Construction Industry  Several authors have emphasized the importance of accurate and timely information for construction project management, e.g. status of project resources [Rasdorf & Herbert 88], labor and equipment costs [Pierce 88], building product information [Coker 85], overseas projects [Fanous & Samara 94], and project control [Liu et al 94a].  [Fischer et al 94] stress that project managers must devote a significant portion of their attention to the management of information, i.e., ensuring that information is available in a consistent and accurate form and in a timely manner. While giving a background of information systems [Riley & Sabet 94] explain that a construction project involves a large number of individuals, firms, and organizations and develops a considerable amount of information; it is the way in which this information flows between the participants in the project that has a considerable impact on the success of the project. [Fanous & Samara 94] and [Pierce 88] describe that the successful management of engineering and construction projects requires coordination, and control of many diverse activities performed by specialists assigned to the project. Successful coordination and control of these activities require the project manager and his staff to have access to accurate and  46  current information regarding all facets of project activities. It is also essential that the specialists in each area of the project have the information they need from other disciplines in order to perform their own work effectively and maximize their contribution to the project.  In their paper, [Couzen et al 9 3 ] explain that the day-to-day running and strategic planning of any organizations relies upon effective and efficient  decision making by the  organization's executive and senior management, and that the information forms the basis for such strategic decision making.  [Vanegas 9 4 ] writes that during the construction phase of a building project, the different members of the project team, namely, owner, designer, and contractors (including subcontractors and suppliers), need to communicate with each other; exchange technical and management data and information; analyze and comment on these information to resolve issues and make decisions; and, when necessary, negotiate to reach agreements among all parties. Thus, the quality of the total project team decision-making and implementation process is a direct result of the availability and reliability of information.  While introducing information technology, [Vanier et al 9 3 ] describe the importance of information in the construction industry. They state that the construction industry is information intensive, and it needs accurate, reliable, and timely information, ranging from legal requirements, building codes and standards, through scientific  research, to  47  manufacturers' product information, and including, finally, site-specific data and past project information. They further state that the need of information is becoming more urgent and more critical as the projects are becoming more complex, and the time frame for decision-making is becoming shorter. They write that there are many reasons for these changes, including increasingly complex projects, the debilitating cost of long term project, and shifting demographic and client requirements. Further, they quote from (Mackinder et al 1982) that the task of managing the vast quantity and variety of information in a professional and timely manner represents a very considerable investment on the part of the construction industry around the world.  Information has always been an important ingredient of construction management, which relies on relevant data for effective management in all its operations [Lock 93]. What is new, however, is the increased pressure on managers to process information more effectively  and to integrate them with schedules, finance, market, contractor, and  operational data.  [Bhandari 78] writes that, increasingly, we depend on meaningful and relevant information for the growth and health of our endeavors, and for the smooth functioning of our institutions. Information is one of the few resources not in danger of exhaustion on the shrinking planet. It is unique because the supply is limitless. One of the primary reasons for information having proved to be such a dynamic resource is the fact that there exists a remarkable technological capacity for dealing with it rapidly and effectively.  48  [Burger & Halpin 77] explain that the super-projects pose new and complex logistical and management problems for project managers. The information at the project level is enormous and traditional methods of information handling are not adequate to meet the needs of new management in this environment. Managers need help in controlling information flow at the project level between the project personnel and the participating groups. Hence, new tools are required for the management of information on large construction projects.  In recent years the construction industry has started to take advantage of new developments  in information technology,  particularly in the field of construction  management.  2.4.3  Current Information Problems  [Evt 92] states that the key features of the typical information-handling problems encountered in construction projects are the following:  •  Each project tends to be unique. A lot of information is specific to the project at hand.  49  •  Common information exists between different projects. Duplication of effort occurs for firms involved in different projects. The knowledge from one project is typically used over and over again by reassignment of the key personnel.  •  Information requirements are very user dependent. Different users need different types of information about a building project.  •  Information availability is time dependent. The level of detail of queries varies as the construction project proceeds from planning to design, and to construction and operation stages.  A study, conducted by [Carter 87] on information transmission systems on many construction projects in U . K , shows a considerable duplication of information flow and storage. The study highlights a number of shortcomings of the existing systems, including many files and documents containing information on more than one topic, time-consuming transmittal systems, etc.  2.4.4  Management Information Control Systems  [Kangari  95]  defines  management  information-control systems  (MICS)  from  a  construction manager's perspective as a process of documenting transactions (project activity), communicating, and maintaining information by a consistent and ordered  50  method. He further states that while one use of such systems is the production of reports, the best use is the retrieval of specific, current, and accurate facts necessary for making management decisions. Kangari outlines the types of data controlled by MECS as follows:  •  Raw data: basic information that furnishes  factual  support for technical  information; e.g., building codes, test data, and topographical surveys.  •  Fundamental documents: written material establishing essential criteria for the project; e.g., contract documents and agreements, project manuals, and master schedules.  •  Transaction documents: documents which have as their fundamental purpose the documentation of a specific project activity; e.g., requests for proposal, requests for information, change orders, field reports, and meeting minutes.  •  Transaction files: the method by which transactions are recorded through their progression with the project; e.g., RFI log, shop-drawing/submittal log, and bid tabulation forms.  •  Technical products: documented results of a technical or analytical effort on the project; e.g., estimates, cost records, quantity take-off, as-built schedules, and value engineering studies.  51  2.4.5 Computer-Based Information Systems  The purpose of a computer-based information system for engineers is to integrate the collection, processing, and transmission of information so that engineering professionals can gain more systematic insight into the operations and functions they are managing [Lock 93]. This will minimize guess work and isolated problem solving in favor of systematic integrated problem solving. It also significantly reduces the labor cost for generating and manipulating the data for necessary documentation, reporting, ordering or just record keeping.  The computerized information system's primary function is to improve project managers' efficiency in retrieving project information from existing records [Tokar 90].  2.4.6 Information or Problem Tracking  Keeping track of information flow on a construction job site is a vital task that has a direct bearing on the timely completion of a building project [Rasdorf & Herbert 88]. The major challenge of today's project manager is tracking vast amount of details [Pierce 88]. Recording and tracking the primary categories of information: subcontractors' bidding, change orders, work progress, progress payment, labor and equipment cost, etc. are the  52  direct concern of the project manager or anyone else who is involved in controlling the construction process.  Responsibility tracking is a function of information systems that holds great potential for improving project management [Tokar 90]. The inclusion of a subjective coding field called 'responsibility" can identify the responsible person and express a time frame for action. The data base can then be sorted and selected to produce action lists for management personnel.  The information looses its reliability, accuracy, and value when the manager receives too much or scanty information ([Bhandari 78], [Tenah 81]). The purpose of an information system is to provide the project manager with a record of each piece of data, including where they are at any time in the process [Pierce 88]. In this way, the project manager can follow up on those items that are not progressing through the process quickly enough, and take action to ensure timely delivery of material to the job. The system should also allow the project manager to find out which parties are consistently holding up the process. In the event of a claim for extension of time or other compensation, the project manager can use the tracking records to make a case concerning other causes of delays to the job.  53  2.4.7  Information M o d e l  The design of a computer-based information system for the provision and interchange of information within a project implies that the real-life project can be modeled in a way that allows functionality in the computer system [Riley & Sabet 94]. This information model should cover the facts about all the products and processes needed to construct these products. But the design of such an information system represents a major task. This is due to the substantial amount and different types of information generated within a project, the variety of project type, the considerable amount of different experts involved, the vast and growing number of building materials, the complicated links and process involved in the project, variation of national and regional standards, diversity of client requirements, etc.  [Froese 93] defines an information model as a collection of information about a specific real-life object, and states that the information model should fulfill the following requirements:  •  General usefulness: many different application will use the models for performing different functions.  •  Richness: the model must capture significant levels of detail.  54 •  Comprehensiveness: the scope must include a wide breadth of project information.  •  Flexibility: flexibility is required both in what information can be represented (e.g., numeric parameters, graphical descriptions, logical relationships, etc.) and in how the information can be used.  [Aouad et al 93] describe the major approaches to information modeling in the construction industry, and they are: data modeling; activity modeling; and product modeling.  2.4.8  Entity-Relationship (E/R) Model  [Date 90] claims that the entity/relationship (E/R) approach is one of the best and most widely used approaches for modeling conceptual information structures. He quotes the definitions of entities, relationships, and attributes from Chen (22.10). A n entity is defined as 'a thing which can be distinctly identified". A relationship is identified as 'an association among entities". Entities and relationships have properties (also known as attributes).  Entity-relationship diagrams are one means of formally expressing a data model [Bamford & Curran 91]. Basically, the entity-relationship data model augments a basic network model by introducing a special symbol, the diamond, to explicitly indicate each  55  relationship [McFadden & Hoffer 88]. Figure 2.4 illustrates the E / R data model. Each diamond represents a relationship type; this diamond exists for both 1:M and M : N (oneto-many and many-to-many) relationships between entities (rectangles). Attributes are associated with entities and represented with an ellipse.  Figure 2.4  2.4.9  Entity-Relationship Data Model Examples  Structural Data Model  Choosing a good data model to represent design data and processes is a major step towards the development of an integrated system [Law & Scarponcini 91]. A data model is a collection of well-defined concepts that help the database designer to consider and express the static properties (such as objects, attributes and relationships) and the dynamic properties (such as operations and their relationships) of data intensive applications. In addition to enhancing the database design process, a data model needs to provide the integrity rules to ensure consistency among the entities.  56  The ultimate objective of the semantic modeling activity is to make database systems some what more intelligent [Date 90]. The goal in selecting a semantic data model is to represent directly in an easy form as many of the objects and their relationships of interest as possible. The structural data model that is used is an extension of the relational model [Law & Scarponcini 91]. Relations are used to capture the data about objects and their parts. The structural data model augments the relational model by capturing the knowledge about the constraints and dependencies among the relations in the database.  The primitives of the semantic data model are the relations (which roughly correspond to entities in the E / R model) and the connections formalizing relationships among the relations. There are three basic types of connections, namely ownership, reference, and subset connections [Law & Scarponcini 91] (Figure 2.5).  Relationship  Symbol  Insert/delete Constraints  Ownership  Emp.  — *  Skills  skills need employee/skills goes with employee  Reference  Emp.  >—  Deot.  employee needs department/ department stays  Subset  Emp.  2 Salesman  salesman must be employee/salesman goes with employee  Figure 2.5  Structural Data Model Examples  57  In the words of [Law & Scarponcini 91], the structural data model can be explained as follows:  The connection between two relations R i and R X i and X  2  2  is defined over a subset of their attributes  with common domains. An ownership connection between an owner relation R i  and an owned relation R  2  describes the dependency of the multiple owned tuples on a  single owner tuple. The ownership connection implies that the owned tuples are specifically related to and dependent on a single owner tuple. As an example, a building structure may consist of structural elements, floor, foundation, roof, space, etc. The components exist if the building exists. This connection type specifies the following constraints:  •  Every tuple in R  •  Deletion of an owning tuple in R i requires deletion of all tuples connected to  2  must be connected to an owning tuple in R i .  that tuple in R . 2  •  Modification of X i in an owning tuple of R i requires either propagating the  modification to attributes X  2  of all tuples in R  2  or deleting those tuples.  A reference connection between a primary (referencing) relation R i (referenced) relation R  2  and a foreign  describes the dependency of multiple primary tuples on the same  58  foreign tuple. As an example, an architectural space, which may be an office, elevator, opening, etc., locates on a floor. The floor can not be removed without first removing the spaces defined on that floor. This connection type specifies the following constraints:  •  Every tuple in R i must either be connected to a reference tuple in R  2  or have  null values for its attributes X i .  •  Deletion of a tuple in R  2  requires either deletion of its referencing tuples in R i ,  assignment of null values to attributes X  x  of all the referencing tuples in R i , or  assignment of new valid values to attributes X i of all referencing tuples corresponding to an existing tuple in R . 2  •  Modification of X  2  in a referenced tuple R  2  requires either propagating the  modification to attributes X i of all referencing tuples in R i , assigning null values to attributes X i of all referencing tuples in R i , or deleting those tuples.  A subset connection between a general relation R i and a subset relation R  2  links general  classes to their subclasses and describes the dependency of a single tuple in a subset on a single general tuple. For example, a structural element can either be a column, a beam, a wall, or a slab. Furthermore, a beam can be generalized to be either a main girder or a joist (secondary beam). Deleting a specific instance in the generic class of structural element  59  must delete the corresponding instance existing in the subclass. The subset connection specifies the following constraints:  •  Every tuple in R  •  Deletion of a tuple in R i requires deletion of the connected tuple in R .  •  Modification of X i in a tuple of R i requires either propagating the modification  2  must be connected to one tuple in R i .  2  to attributes X  2  of its connected tuple in R  2  or deleting the tuple in R i .  2.4.10 Users Interface  [Townsend 92] defines different interface terms as follows:  •  Interface:  The way that users communicate with a computer system. In a  database, the interface consists of elements such as menus, forms, tables, reports, and queries.  •  Graphical User Interface (GUI):  A design in which a computer interacts with  a user by means of graphical menus and symbols rather than a command language. Microsoft Windows, the Apple Macintosh, and GeoWorks are examples of graphical user interface.  60  •  Programming Interface: A user interface based on a programming language especially suited to database applications. The first PC database packages to appear were based on programming languages.  •  Spreadsheet Interface: This is one of the world's most popular user interfaces.  •  Form-Oriented Interface: A n approach to database design that uses paper forms as a model for building tables. This type of interface keeps programming to a minimum, and employs some of the gadgets found in the Windows operating system like fields, menus, buttons, drop down menus, and scroll bars.  2.5  Related Work  2.5.1  Expedition  One contract control software for engineering & construction, Expedition Version 4.2 by Primavera Systems, Inc., is worth mentioning since it is developed specifically for the construction industry. Expedition can help to accomplish every task, from reviewing a submittal to making notes from a telephone call, or double-checking approvals on a change order. It helps to file, track or retrieve any contract information. Appendix-F  61  contains the output reports produced by the prototype of the system, PMICS, which are comparable to the samples shown in the Expedition literature [Expedition 95].  2.5.2  Resident Management System (RMS)  RMS is a D B M S developed by the U.S. Army Corps of Engineers (included in the Related Work listing [Ganeshan et al 94]) to support the 3-phase inspection process used by the Corps to administer construction projects. In this system, features of work are divided into related work activities. The contract requirements (e.g., shop drawings, test results, etc.) for each activity are maintained with that activity. It also helps to write and track modifications, manage contract finances, process progress payments, and contract correspondence.  2.5.3  Centex-Roonev's Jobsite Program  Centex-Rooney's Jobsite Program [Barnes 93] is a custom-designed proprietary program developed by Centex-Rooney of Florida. This system came into being from a long-felt need on the owner's part to have better control over the status of shop-drawing submittals, change orders, materials flow, requests for information, and the administrative status of subcontractors and suppliers.  62  2.5.4  MULTROL  M U L T R O L , a multimedia project control and documentation system [Liu et al 94], was developed for the PC platform and runs under Microsoft Windows™ operating environment. This system uses a graphical user interface for all operations, creating a userfriendly environment for construction personnel. The retrieval of project information is further assisted by user definable queries to support various needs of construction management. This system allows the storage and retrieval of project information in the format of text, image, video, and sound.  2.6  Matrix as an Analytical Tool  One of the initial tasks of an information systems design is the requirements analysis. Such task includes study of the environment, users of information and their information needs, and sources of information. To deal with these problems, some sort of analytical tool is required. The tool may be descriptive, illustrative or graphical.  [Tenah 84] presents a method of preparing and routing management reports which allows managers to receive information and take action simultaneously, information to flow in all directions, and managers to think ahead and be prepared for major commitments. The method  used  is  a  graphical reporting  structure  in  a  form  of  Management  Recipients/Summary Reports matrix. This matrix shows the report(s) each manager or  63  supervisor receives to perform his role. [Lock 92] describes document/distribution matrix as a useful tool for information and documents flow. [Peters 84] recommends to use a project/responsibility matrix to define the responsibilities of projects participants.  [Benyon 90] describes a data/process matrix to verify data and process for information modeling. [Barton 85] proposes various tools for a systems design, applied to the construction industry, investigation in order to obtain a model of problem identification. These tools include hierarchy business models, personnel/functional matrices, and data flow diagrams. The personnel/functional matrix, as contained in the literature, indicates the relevant staff involvement.  [Cleveland & King 83] present some of the graphical analytical tools, and the tools are the following: tree diagram, matrix and lists. A tree diagram is a model hierarchy which is useful in organizing project entities, and is used to track deliverables remaining. It is suggested to use a matrix model to collect information concerning roles of different individuals. A matrix model is useful when two or three entities are related. A list is merely an account of items and is similar to a check list.  64  2.7  Summary  The literature review has identified several issues concerning information in the construction industry. Timely and accurate information is a necessity for the successful completion of a construction project. Too little information is of no use. Too much information often gets no attention. Before designing an information system for the construction industry, it is useful to identify the information needs of active project team members. Unfortunately, this area has received little attention by researchers in the construction industry. It is also revealed that information follows functions. It is discovered that one way of finding out the information needs of construction personnel is to adopt a matrix approach. A number of matrix models can be developed for this purpose, namely: personnel versus functions; personnel versus information needs; and information versus documents. It is also identified that, for managing information in a construction environment, a database approach is the most effective and desirable. This approach can reduce redundancy of data and can improve the effectiveness of an information system. This literature review also aided in the development of objectives, scope, and a framework for the development of a construction information system. Several technologies pertaining to database management systems (DBMS) have been identified. In brief, the system description in the following chapters derive from the outcome of the literature search.  65  Chapter 3 FRAMEWORK  3.1  Introduction  This chapter presents a framework for the development of the Project-Management Information Control Systems (PMICS). Although no single methodology or standard approach is used for this purpose, the techniques presented are representative of those proposed and suggested by various researchers and authors.  3.2  Framework for PMICS Development  As stated in the previous chapter, in order to provide an organization with an effective information system, a holistic approach toward data and information is required. A model (Figure 3.1) has been developed as a framework for PMICS development for this thesis. This model is based on the following: 'Proposed Conceptual Integrated System' model [Sadri & Kangari 93], 'Design and Implementation of a CTM Information System' description [Chadha et al 94], 'Data Base Planning' description [McFadden & Hoffer], and 'Designing Information System' outlines [Barton 85]. The basic idea behind this framework is to provide a platform for the proposed information system design and its implementation.  66  General planning & information requirements  Processing requirements Database management system characteristics  Phase 1 Planning  Phase 2 >j Requirements Formulation Requirement specifications Phase 3 System Design Information structure  Enterprise Data Model  Phase 4 Implementation Logical database structure (DBMS processible) and prototype system  Figure 3.1  Hardware/ software operating system characteristics  Proposed System Development Framework  67  The development of a potential database application for information control within a construction project environment requires a thorough understanding of all the data sources and their uses. Figure 3.1 is a graphical representation of the proposed system development framework. The model includes four phases: Planning, Requirements Formulation, System Design, and Implementation. Each phase includes different steps and procedure, and identifies its final product.  3.2.1  Planning Phase  The first phase of database system development is planning. Planning is essential if the benefits of data resource management are to be utilized. The following steps, shown in Figure 3.2, will be followed to provide necessary information for the next phase.  •  Business Environment and Strategy Definition: involves the development of a model or definition reflecting the strategic plans of the organization, business environment, business planning, and critical issues. Business environment means internal and external environments in which the organization now exists and will exist over the strategic planning horizon.  •  Existing Information System Assessment: involves assessment of the existing information systems of the organization based on data generating sources, data  Business Environment & Strategy Definition *  Existing Information System Assessment  Information Needs Report Development  Information Model Building  Data Distribution Plan Development  •  Information System Plan Development  ^ To ^ Requirements Formulation v Phase y  Figure 3.2  Phase I - Planning  69  classification and processing procedure, and the information generated as the users requirements.  •  Information Needs Report Development: involves identification of the type and attributes of information needed based on the functional requirements. It is not related to the organizational structure, but based on the need report.  •  Information Model Building: involves in the development of an information architecture which identifies the functions, processes, and activities for the organization. This model shows the relationships between important business entities.  •  Data Distribution Plan Development: involves development of a plan of data distribution or transfer within the  organization.  The home  office  of the  organization may be away from the construction job site.  •  Information System Plan Development: includes the overview of the system design. Design must support the goals of each business area and satisfy user requirements.  3.2.2  Requirements Formulation Phase  The purpose of requirements formulation and analysis is to identify and describe the data  70  that are required by the organization. It defines the purpose of the database, the scope of information to be contained in it, and the desired functionality. The following steps, shown in Figure 3.3, will be followed to provide the necessary information for the next phase.  •  Database Scope Definition: involves the definition of goals and objectives of the database for the organization. This requires establishing a scope for the database in terms of what business areas or functions are to be addressed.  •  User Views Identification: involves identifying user views by reviewing tasks that are performed or decisions that are made by users and by reviewing the data required for these tasks and decisions. A user view is a description of a user's view of data in a database.  •  Data Source Identification: involves an analysis of data classification (type and format), data generator and user of data and their relationship. A n information flow chart for the organization is drawn. This step may be combined with conceptual model design steps.  •  Reporting Requirements: involves listing the printed reports and their appearance that the system must produce, some of which may be modeled on existing reports and lists.  Database Scope Definition  User Views Identification  ± Data Source Identification  Reporting Requirements  Processing Requirements  ±  :  Data Dictionary Building ± Data Volume & Usage Identification  Figure 3.3  Phase II - Requirements Formulation  72  •  Processing Requirements: involves  identifying the various processing functions,  e.g., calculations, posting totals, transfer of information, archiving and deleting data, etc.  •  Data Dictionary Building:  involves defining and describing in detail each data  item type that appears in a user view. It is created by building a table containing all thefieldsto be used, and showing thefieldname, type, length, and description. It describes the objects and their functions in the system. Development of a data dictionary helps in creating standards in the system.  •  Data  Volume  and  Usage Identification:  involves collecting information  concerning data volume and usage patterns, including future growth. Collecting these data is another step in requirement analysis, however, it is best performed after the conceptual modeling is completed.  3.2.3  System Design Phase  This phase involves the transformation of the requirements set by the two previous phases into a system specification and design of an interface with the end user. The following steps, shown in Figure 3.4, will be followed in this phase.  73  File Organization Scheme Development: involves developing an organization scheme for construction documents.  Information generated  during the  construction process, such as specifications, estimates, drawings, submittals, change orders, correspondence, etc., need to be organized according to some standard code or in a similar fashion.  From / Requirements V Formulation \ ^ Phase  File Organization Scheme Development  User Views Definition  Conceptual Data Model Development  Technology Identification  To Implementation Phase  Figure 3.4  Phase III - System Design  74  User Views Definition:  involves reviewing and defining in detail all the  information or data required as output before starting conceptual data modeling. All the main entities to be tracked in this application are listed, including the character of each entity and the required system output.  Conceptual Data Model Development: involves identifying data items and their relationships. The conceptual design of a database starts with the definition of the requirements and produces a conceptual schema of the data. This is the most important aspect of the database design because it describes the organization and scope of the information to be stored in the implemented database and is independent  of  any  particular Database  Management  System  (DBMS)  implementation. A conceptual model helps describe the entities to be tracked, their relationships, and the business rules that govern those entities. The steps in conceptual design include data modeling (using Entity-Relationship methodology), view integration, conceptual schema development (semantic data methodology), design review, and logical access mapping.  Technology Identification: involves studying and identifying available technology and the incorporation of future technology into the system. At this stage the most suitable hardware and software for the proposed system is identified.  75  3.2.4  Implementation Phase  This phase is concerned with mapping the conceptual model to a logical database structure. This is the final phase of the system development. The important issue here is to make sure that the implementation follows design specifications and user requirements as identified in previous phases. The following steps, shown in Figure 3.5, will be followed in this phase.  •  System Hardware  and Software Selection: involves  selecting  the system  hardware and software based on the previous step of technology identification. The selection of hardware will be based on two principles: first, the project participants move from one construction project to another, and second, the construction job site is the most important place to use the system. The selection of software will be based on the choice of easy-to-use data managers and compatibility for personal computers (PC's) running Windows version 3.1 or higher. The user interface will be developed with the standard Windows user interface.  •  Prototype System Development: involves developing a working model of the finished application that fulfills most of the system's requirements. The purpose of prototype is to demonstrate the idea of using a PMICS for information handling  76  during the construction phase of a project. It will be simple but will realistically demonstrate all database functions.  At this stage, all the forms, reports, menus,  and queries will be designed, with frequent user-views evaluation. Following the results of evaluations of the forms and reports, the database will be refined, as required.  Performance Evaluation: involves monitoring the performance of the system. The system performance for typical queries will be estimated.  System Hardware/ Software Selection  Prototype System Development  Performance Evaluation  Figure 3.5  Phase TV - Implementation Phase  77  3.3  PMICS - Project Management Information Control System  An important part of this research is a prototype system. This section outlines the phased development methodology. This methodology is the result of methodologies present in the literature. The prototype system development and implementation involves almost all the steps mentioned in section 3.2. However, the system analysis and design (Chapter 4) does not completely follow the same sequence as outlined in section 3.2.  3.3.1  Objectives of PMICS  The main objective of this program of research is the development of a prototype information control system for a construction firm. The prototype focuses on the support of the important information requirements of one particular aspect of a construction organization, i.e., information control. The PMICS also serves, however, as a template for similar development on a wider scale, rather than focusing on the information during the construction phase only.  3.3.2  Planning for PMICS  This research uses, as a case study, an information tracking database application for an imaginary construction organization, A B C Construction Company. Although the example  78  is hypothetical, it does contain many of the elements of a real construction environment. Section 4.1.1 describes the business environment and strategy. Section 4.1.2 includes the description of organization's existing information system, its information needs, and data distribution plan. Section 4.1.3 presents information flow-diagrams.  3.3.3  Requirements of P M I C S  Section 4.1.4 describes the database application scope. Section 4.1.5 presents the selected user views for the system. Section 4.1.2 describes the reporting requirements. Section 4.4 contains data source identification and processing requirements, in addition to conceptual data modeling. The data dictionary is listed in Appendix-C.  3.3.4  System Design of P M I C S  Section 4.3 defines the file and document organization schemes. Section 4.4 describes thirteen user views. Section 4.4 through 4.9 deals with the conceptual data modeling. Section 4.10 presents the implementation and prototype development.  79  Chapter 4 SYSTEM ANALYSIS AND DESIGN  4.1  General Description  Construction projects progress through various life-cycle stages, for example: tender, award of contract, pre-construction, construction, and commissioning. In this thesis, the emphasis is on the pre-construction and construction stages.  After a construction contract has been executed, all the bids from suppliers and subcontractors are reviewed so that contracts can be awarded and other commitments firmed up to start the flow of labor and materials to the job site. During these stages, the project manager needs information to make important decisions. This information may include: bid summaries, subcontractor interview forms, subcontracts, purchase orders, and various tracking logs (submittal, change order, payment, field reports, site photograph, stored materials, punch lists, correspondence, etc.).  Given the bulk of information associated with construction projects, a formal organization of the information is essential so as to avoid chaos. Effectively managing this bulk of information to ensure its availability and accuracy is an important managerial task. Virtually all medium and large-size construction firms have computer-based organization  80  of cost accounts and other data. With the advancement in computer technology as well as in data base management systems (DBMS), it is possible to develop a computerized database for even small organizations and projects.  4.1.1  Description of Users  This system is intended to be used by managers of the fictious A B C Construction Company, a small to medium-sized construction company. Although this company is a hypothetical, it does contain many of the elements of a real construction company. The company works as a general contractor on most of its ongoing projects and employs a number of trade subcontractors.  An organizational chart for A B C Construction Company is shown in Figure 4.1. This company employs a number of managers to manage projects. Each manager is assigned specified functions and responsibilities. Every manager prepares or receives a number of periodic reports, including progress reports, daily reports, cost reports, project control reports, etc. Currently, the company has computers to do accounting, word processing, and spreadsheet procedures at their home office as well as at all project sites. Some of the information is maintained by individual managers or staff, either manually or using software less suited to database management.  81  General Manager  Chief Engineer  Personnel Manager  Finance Manager  Chief Accountant  Construction Manager  Procurement Manager  Chief Estimator  Project Manager Project Engineer  Planning/Sch. Engineer  Purchasing Agent  Cost Engineer  Estimator  Superintendent Subcontractor  Foreman  Field Office Engineer  Foreman  Figure 4.1  Foreman  Field Engineer  Foreman  A B C Company Organization Chart  Accountant  82  4.1.2  Background for the Application  As the number of projects is constantly growing, the company needs a project-information tracking system capable of tracking information from project sites. The project management staff normally prepare all the reports and tracking logs. Information is gathered by the project's office engineer and entered into various tracking logs, on paper or in the computer. The project manager receives information on printed forms from various sections or sites. There is a lot of duplication. The company wants to consolidate all of its project-information, eliminate duplicate information, and do more frequent expediting of work.  The most important and frequently used reports or tracking logs are the following:  •  Bill-of-Quantities,  •  Bid Summary Sheets,  •  Change Orders Tracking Log,  •  Correspondence Tracking Log,  •  Daily Site Reports,  •  Defective Work Notifications Tracking Log,  •  Materials Stored Tracking Log,  •  Monthly Progress Reports,  •  Photographs Tracking Log,  83  •  Punch Lists,  •  Requests for Information Tracking Log,  •  Shop Drawing Submittals Tracking Log, and  •  Spare Parts Tracking Log.  Most of the above are prepared on computer (spreadsheets). As some of the information is repeated on more than one report, the idea is to integrate these information into a PMICS in order to allow the user not only to have quick access to information, but also to transfer the information to all project team members and at the same time reduce duplication. As far as possible, the appearance of reports will remain the same as that of the existing one (as shown in Appendix-B). As most of the job-sites are geographically away from the home-office, it is decided to have information distributed to managers through modem, hard copy, or printed reports.  4.1.3  Information Model Building  The information in construction environments is complex and involves processes and functions.  Models are therefore  needed  to  numerous  logically break these  environments into manageable pieces. Although the environments can be seen from various view points in the model, this section describes the modeling of construction environment from an information flow standpoint.  84  4.1.3.1  Modeling the Construction Project Environment  Figure 4.2  shows a participants/information-fiow diagram. After  establishing  the  information needs, it is necessary to model the construction environment and its information flow.  On most construction projects, the common parties who participate in the construction process are the following: project management team, contractor's home-office, owner, architects/engineers (A/E), subcontractors, suppliers, and equipment rental companies.  4.1.3.2  Modeling the Documenting/Reporting Process  The internal/external information attributes are discussed in terms of the three principal groups identified by [Skitmore 89]: (a) project related; (b) organization related (internal); and (c) market related (external). Figure 4.3 shows the data flowchart of information with the relationships established internally and externally to the program. In this figure, the circles represent job-site program activity, the rectangles denote interactive data sources, the single arrows denote the data flow, and the double-sided arrow denotes data-out as reports and logs, and data flow into the system..  85  Legends: 1 2 3 4 5 6 7 8  project information home-office information information for head-office checks/progress statement supervision inquiry/submittals confirmation/inspection payment request  Figure 4.2  9 10 11 12 13 14 15  payment quotations/sub information instruction supplier quotations requisitions to supplier plant quotations requisitions to equipment rental companies  Participants/Information-Flow Diagram  86  Bill of Quantities User View 1 Bid Summary Sheets User View 2 Change Orders Log User View 3 Correspondence Log User View 4 Daily Site Reports User View 5 Defective Work Log User View 6  Data-in Modem to Home-office  Materials Stored Log User View 7  —*• Data-out  Monthly Reports User View 8  Setup done by Project Manager/ Project Engineer  RFILog User View 9  Entry done by Field Office Engineer/ Secretary  Photographs Log User View 10 Punch Lists User View 11 Shop Drawing Log User View 12 Spare Parts Log User View 13  Figure 4.3  Data/Information-Flowchart  87  4.1.3.3  Data Entry and Updating  Most of the data from the pre-construction phase do not require any updating. However, other data need daily or periodical updating. On a daily basis, the system receives information from all project team members. Each report generated by the team members is accumulated and entered into the system, and is shared with all users as soon as it is generated.  As many default values as possible must be used to minimize the amount of information that has to be recorded by field personnel on daily basis [Russell 93].  4.1.4  Objectives and Scope of the PMICS  The PMICS is intended to be a user-friendly application that is easy to access by the different project team members. Form-oriented user interface is desired. The basic objectives are as follows:  •  to understand the information needs of a construction project team;  •  to develop data flow models of a construction project;  •  to develop an information model for the selected user views;  •  to design and implement the database and application/user interfaces; and  •  to provide specifications/requirements for the future systems.  88  Other objectives and scopes are as follows:  •  to bring the project information management function more accessible.  •  to increase the frequency and efficiency of reports.  •  to gain more and timely picture of project information.  •  to eliminate redundancy and standardize the format used for the various reports.  •  to reduce the amount of time spent recording, preparing, and posting reports.  •  to track the status of project and its various activities.  4.1.5 Selected User Views  As described in section 3.2.2, requirements analysis is the process of identifying and documenting what data the users require in the data base to meet present and future information needs. Traditionally, there are two approaches for requirements analysis: process-oriented approach which focuses on data flows and process, and data-oriented approach which focuses on the data that must be included in the database to satisfy user requirements [McFadden & Hoffer 88]. The latter approach, i.e. data-oriented approach, is applied for the proposed application. The principal tools are user view analysis, data definition and description, and normalization.  89  A user view is defined as a subset of data required by a particular user to make a decision or carry out some action [McFadden & Hoffer 88]. User views are identified by reviewing tasks that are performed or decisions that are made by users and by reviewing the data required for these tasks and decisions. Existing reports, tracking logs, files, documents, and displays are the important sources of information of user views.  The literature review has identified functions and information needs of different construction personnel. Findings are depicted in Matrix M - l (Figure A - l , Appendix-A). This matrix covers all the possible functions of construction personnel during construction phase of a project, including general management, management,  engineering,  estimating,  accounting/financing, personnel  planning, project  management,  construction  management, site management, procurement, and safety.  The matrix M - l is short listed to produce a construction personnel versus functions matrix M-2 (Figure 4.4). The matrix M-2 focuses on the project management functions. The matrix M-2 is used as a basis for the development of the third matrix M-3, construction personnel versus information needs (Figure 4.5). The next task is to identify documents which contain these information. The fourth matrix M-4, document type versus information content (Figure 4.6), serves the purpose. Based on Matrix M-4, several user views are identified. However, only thirteen distinct views will be used in the system development. The sample documents of the various user views (tracking logs, reports, and  90  lists) are depicted in the respective sections (section 4.4.1 to 4.4.13). The selected views are as follows:  •  Bill of Quantities,  •  Bid Summary Sheet,  •  Change Orders Tracking Log,  •  Correspondence Tracking Log,  •  Daily Site Report,  •  Defective Work Notifications Tracking Log,  •  Materials Stored Tracking Log,  •  Monthly Progress Report,  •  Photographs Tracking Log,  •  Punch Lists,  •  Requests for Information (RFI) Tracking Log,  •  Shop Drawing Submittals Tracking Log, and  •  Spare Parts Tracking Log.  91  Superintendents  Foremen  D  D  D  E A A A  E A  H  Field Engineer  Field Office Engineer  Purchasing Agent  II  Accountant  & E I c c  Estimator / QS  R=review & analyze  V 03 r. o  Cost Engineer  E=execution H=handle  Project Engineer  D=data provisions  Procurement Manager Project Manager  C=control responsibility  Construction Manager Chief Engineer  Leqends: A=advisory/assist  Responsibility  PERSONNEL/ FUNCTIO NS MATRIX M-2)  o_ uj  Function/Activity Engineering project's engineering & design operations field and office engineering liaison change orders, work orders, claims design, drafting & shop drawing drawing, papers and submittals  A C A C  C,H H H C,H H  E E A  Estimating C,A H C C H  project's estimating quantity survey/estimate from drawings subcontract estimate  D D,E  E E E  Planning project's planning and scheduling project's schedule for resources delivery schedules detail schedules  Quantity  R  A  C,A H H C H  c  c  Surveying C C  work measurement & payment estimates valuations preparation cost /value preparation final accounts agreements  Construction  D  E E E  c C  E  H H  E E E E  H  Management  construction operations management construction methods development subcontractor selection and negotiation procurement of resources building production supervision architect/client liaison  C,H  c  C,A C,H C C,H  H  D A  D  H A H A  Procurement C,A A C,A A A C,A A A C  quotations requests/receipts technical assistance to subs/suppliers negotiation with subs/suppliers procurement contracts/specs approval project's purchasing/expediting/inspection transportation & routes arrangements materials order materials chase  Project  Site  D  D  c  C,A A  c R R  H H E H H E H  c  Management  project completion project's progress/potential problem reports project's progress & status reports progress photographs  D D  C,H C,R C,R C  A H,R H E H  C  A  A  E E  Management  field work, field survey, layout, etc. subcontractors' progress check punching lists completion subcontractors organization & coordination  Figure 4.4  C,R R  Personnel/Functions Matrix (M-2)  92  X  X  X  X  X  Procurement Manager  Project Manager X  X  X  X  X  X  X  X  X  Foremen  Purchasing Agent X  X  Superintendents  Accountant X  X  Field Engineer  Estimator / QS  X  X  X  Field Office Engines  Cost Engineer  X  X  X  ng Engineer  X  X  X  Chief Engineer-  Planning/Scheduli  X  X  Project Engineer  X  X  Construction Manager  Infoneed By  P E R S O N N E L / N F O N E E DS M A T R X (M-3)  X  X  X  X  X  X  X  X  Information Needs  Estimating 1 Quantity Surveying work item lists bill of quantities (item & quantity) item cost cost summary budget for a trade start and end dates of trades sub-bid due dates subcontractors/suppliers directory bid receipt dates bid amounts (individual bidder/trade) bid comparison or summary bidder ranking lists selected subcontractors/suppliers lists progress measurements pay estimate number & date monthly work progress quantity of materials stored at sites value of materials stored at sites monthly valuation and report subcontract estimate  X  X  X  X  X X  X  X  X  X X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X X  X  X  X  X  X  X X  X  X  X  X  X  X  Planning approval time for submittals delivery time of an item time impact by change orders revised completion date of project  X X X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  Engineering  shop drawing lists shop drawing submission due dates shop-drawing subcontractor/supplier status of shop drawings lists of approved shop drawings shop drawing approval delay time requests for information (RFI) lists RFI initiator's name contents of RFI date of RFI status of RFI response time of RFI RFI responding person outstanding RFI change order lists change order details subcontractor affected by change order change order value change order contract time impact status of change orders revised total contract price revised completion date  Figure 4.5  X  X X X  X  X  X  X  X X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X X X  X  X X  X X  X  X  X  X  X  X X  X  X X  X  X  X X  X  X  X X  Personnel/InfoNeeds Matrix (M-3) ... contd...  X  93  ... contd...  Figure 4.5  X  91  CD CU  C  w c  CD TD  c  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X X  X  X  X  X  X  X  X  X  X  X  X  X X  X  X  X  X  X  X  X  X  X  X  X  X X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X X  Foremen  LU  Superinte  O  Field Eng  nginee  <  Field Offic  c  c  CD  CO  Accountai  ng Engins  CD CD  Estimator  c 'cn  Cost Engi  IO  Project M:  CD  <u OJ  Project Er  c  Procurem Manager  Chief Eng  Construct Manager  Infonee Information Needs Procurement suppliers directory requirement dates of materials sample/approval requirements approval time for submittals delivery time of materials materials affected by change orders status of materials on job site quantity of materials required quantity of materials stored supplier of materials lists of spare parts required details of spare parts quantity of spare parts required & delivered quantity of spare parts balance location of spare parts stored supplier of spare parts Project Management daily work progress equipment in use and idle on job site daily labor force on job site daily weather condition visitors on job site materials requirements lists of site photograph negative/roll number of photograph date and location of photograph purpose of photograph Site Management lists of defective work notice (DWN) details of defective work notice subcontractor responsible for DWN type of defects/rejection of DWN value of DWN punch lists and details Hern lists subcontractor responsible for punch list status of punch lists Administrative owners contact & phone architects/engineers contact & phone subcontractors contact & phone suppliers contact & phone correspondence in & out list contents of a correspondence origins of correspondence dates of correspondence file-location of correspondence  UI co  Planning/  <u  <u _c  CO c o •D  •  Purchasir  M-3)  P E R S O N N E L / NFON E E DS MATRIX  X  X  X  X  X  X  X  X X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  X  Personnel/InfoNeeds Matrix (M-3)  94  Company Directory  Spare Parts TL  Shop Drawing TL  Requests for Info TL  Punch Lists TL  Photographs TL  Monthly Prog. Report  Materials Stored TL  Defective Work TL  Daily Site Report  Correspondence TL  Change Orders TL  Bid Summary Sheets  Bill of Quantities  Document Type  D O C U M E N T T Y P E / INFOR MATION C O N T E N T S IV ATRIX (M -4)  Information Contents Estimating 1 Quantity  Surveying  work item lists bill of quantities (item & quantity) item cost cost summary budget for a trade start and end dates of trades sub-bid due dates subcontractors/suppliers directory bid receipt dates bid amounts (individual bidder/trade) bid comparison or summary bidder ranking lists selected subcontractors/suppliers lists progress measurements pay estimate number & date monthly progress estimates quantity of materials stored at sites value of materials stored at sites monthly valuation and report subcontract estimate  X X X X X X X X X X X X X X X X X  X  X  X X X  Planning approval time for submittals delivery time of an item time impact by change orders revised completion date of project  X X X X  Engineering shop drawing lists shop drawing submission due dates shop-drawing subcontractor/supplier status of shop drawings lists of approved shop drawings shop drawing approval delay time requests for information (RFI) lists RFI initiator's name contents & dates of RFI status ans response time of RFI RFI responding person outstanding RFIs change order lists and details subcontractor affected by change order change order value change order contract time impact status of change orders revised total contract price revised completion date  Figure 4.6  X X X X X X X X X X X X X X X X X X X  Document Type/Information Contents Matrix (M-4) ... contd...  95  Company Directory  Spare Parts TL  Shop Drawing TL  Requests for Info TL  Punch Lists TL  Photographs TL  Monthly Prog. Report  Materials Stored TL  Defective Work TL  Daily Site Report  Correspondence TL  Change Orders TL  Bid Summary Sheets  Bill of Quantities  Document Type  D O C U M E N T T Y P E / INFOR VIATION C O N T E N T S M ATRIX (M -4)  information Contents Procurement suppliers directory requirement date of materials sample/approval requirements approval time for submittals delivery time of materials materials affected by change orders status of materials on job site quantity of materials required quantity of materials stored supplier of materials lists of spare parts required details of spare parts quantity of spare parts required/delivered quantity of spare parts balance location of spare parts stored supplier of spare parts Project Management daily work progress equipment in use and idle on job site daily labor force on job site daily weather condition visitors on job site materials requirements lists of site photograph negative/roll number of photograph date of photograph location of photograph purpose of photograph Site Management lists of defective work notice (DWN) details of defective work notice subcontractor responsible for DWN type of defects/rejection of DWN value of DWN punch lists and details item lists sub responsible for punch list status of punch lists Administrative owners contact & phone arch./engineers contact & phone subcontractors contact & phone suppliers contact & phone correspondence in & out list contents of a correspondence origins & dates of correspondence file-location of correspondence  ... contd...  Figure 4.6  X X X X X X X  X X  X  X  X X X X X X X  X X X X X X X X X X X  X X X X X X X X  X X X X X X X X  Document Type/Info Contents M a t r i x  (M-4)  96  4.2  Database Design Overview  A s discussed in section 1.6.4, the first step in designing a database is to do data modeling. Furthermore, a data modeling process involves the creation o f the following three separate models: the conceptual model, the logical model and the physical model.  The conceptual data model may be expressed in several different ways, for example: an entity or table relationship model, a data structure diagram, or a semantic data model. Sections 4.3, 4.4 and 4.5 present the conceptual model development for the database o f the proposed system. Section 4.3 deals with the basic files and their organization schemes. Section 4.4  covers  the  description o f user  views, identification o f information  requirements,  identification o f entities and their attributes  including primary keys,  description o f relationships between entities, and presentation  o f entity-relationship  diagrams. Section 4.5 presents the normalized relations and structural model diagram.  The logical modeling is carried out in sections 4.6 and 4.7. Section 4.6 presents entities and relationships, their attributes based on the conceptual models developed in sections 4.4 and 4.5, and entity cross-reference matrix. Section 4.7 defines a data dictionary.  The translation process, from the logical model to a physical model w i l l be performed by the D B M S software. Section 4.8 assesses the space required for the database system.  97  Section 4.9 and section 4.10 are concerned with the implementation and prototype development.  4.3  File Organization Schemes  This section identifies some of the important and basic entities required for database of the proposed system, and highlights their organization schemes in the database. One of the most significant basic entities, as identified during the requirements analysis, is the specifications. Most of the transactions are referred to a particular specifications section. A specifications is defined as a part of the contract documents contained in the project manual consisting of technical descriptions of material, equipment, construction systems, standards, and workmanship [Jones 91]. Other basic entities are the following: work breakdown structure (WBS), project, subcontractor or company, employee or people, and trade. These basic entities are discussed as follows.  4.3.1  Core Entity - the specifications  A database of specifications related to construction industry will be required for most of the items. Classification schemes such as MASTERFORMAT are widely accepted (though not universally) in the AEC industry [Froese 93]. Project specifications are organized according to CSI Masterformat in North America [Ganeshan et al 94]. Masterformat also provides aframeworkfor organizing all construction documents.  98  For the proposed PMICS, most of the files and documents are tied to a specific CSI Masterformat section number which is called a 'Specs Section' for simplicity. Estimating items are tied to work breakdown structure (WBS) of the A S P E (American Society of Professional Estimators) classification hierarchy, which is parallel to the CSI Masterformat classification hierarchy. A WBS is defined as a list or chart of work items. A work item or item of work is defined as a portion of work that can be observed, identified, and separated for purposes of estimating, cost accounting, and management [Collier 9 4 ] .  The reasons for using WBS over specs section for estimating purposes are as follows [Stewart & Stewart 86]: a specs section may not go far enough down into the estimate details; a specs section number may not apply to the estimate at hand; a WBS is short and covers almost all work items of small to medium projects. A project is defined as a construction undertaking of the work of a construction contract as a whole or a part.  As discussed above, a work item constitutes a WBS. Each work item is assigned with a unique WBS number. Work items usually involve the work of only one trade. A trade may be defined as a division of work having work items of same field. For example, plumbing, H V A C , electrical. A trade may have one or more work items. Thus a WBS is tied to a trade. A WBS may cover one or many specs section, or in another words, a specs section may appear on one or many WBS. This way a specs section is tied to a WBS. The hierarchy of a trade, WBS and specs section is shown in Figure 4 . 7 .  99  Trade  WBS WBS WBS WBS  •  j  1  Specs Section  Figure 4.7  Specs Section  ' i Specs Section  WBS WBS  ri Specs Section  Hierarchy of Trade/WBS/Specifications  A subcontractor may work on one or more trade areas; a trade is associated with one or more WBS; a WBS is related to one or more specs sections, thus all subcontractors are tied to the specs sections. Figure 4.8 explains the scenario.  Subcontractor  Specs Section K  Figure 4.8  Specs Sections/Subcontractor Relationships  Under the older control systems, when RFI, shop drawing, change order, defective work notification, etc., came in, they had to be checked manually to determine the responsible subcontractor. Now, by citing a specs section, related subcontractor will be highlighted. Similarly, for materials or spare parts, related suppliers will be highlighted.  100  4.3.2  Other Basic Entities  As discussed earlier, other basic entities include the following: project, subcontractor or company, employee or people, and trade. A brief description of these entities are given as follows.  A construction company can have several projects running, or completed. The database of projects becomes an essential component of the information to identify different projects. Some  times,  it  is  required  to  get  information  about  similar  project  type,  architects/engineers on previous projects, responsible project manager, or projects on a particular location. To accommodate all these information under one entity, project, the attributes will include the following: project identification number, name, location, type (building, bridge, highways, etc.), start date, scheduled completion date, actual completion date, contract value in dollars, architects/engineers, owner, supervisor (company's project manager), and phone and fax numbers.  A database of possible subcontractors or suppliers for construction projects might be required by the cost estimator to identify subcontractors to ask to bid on parts of a project. Appropriate subcontractors or suppliers appearing in the database could be contacted to prepare bids for specific projects. Other possible contacts for business developments are the following: owners, architects, engineers and other agencies. Even pn  101  a running project, there will be many participants associated with the project. In order to have a global look, information about the above mentioned project participants will be organized in an entity, called company. The entity company will include the following attributes: company identification number, name, address, phone and fax numbers, type (subcontractor, supplier, architects, engineers, owner, etc.), size (small, medium, big), and name of the contact persons. In order to identify the type of a company or the nature of a participant on a project, a separate entity is required. This will be organized into an entity, called Participant Type.  A database of company's employee will be required for personnel requirements, assignment to a project, or other related information. Similarly, a database of people associated with other organizations will be required in order to contact them for important matters. A common entity, people, is assigned to accommodate information about each person. The attributes of entity people will include the following: people identification number, name (last name and first name), address, phone and fax numbers, title, and company identification number.  A database of trades for a project will be required for information about a trade. The information required may include the following: work items included, start and end dates, estimated and actual cost, and budget. These are organized in an entity, called project trade-details. This entity will also help in identifying a subcontractor of a trade on a project.  102  4.4  System Views  This section describes the selected views as listed in section 4.1.5, identifies typical queries for each view, determines entities and their attributes referenced in each view, determines the primary key(s) for each view, defines general key terms for each view, identifies the entities and relationships that exist, explains the entities and their relationships, presents entity-relationship (E/R) diagrams for each view, and finally presents E / R diagram for the integrated view.  Reference sources for this section include the following: [Tidwell 95], [Fisk 93], [Seeley 93], [O'Brien 90], [Levy 94], [Callahan & Bramble 83], [Goldhaber et al 77], [Stewart & Stewart 86] for description of views; [McFadden & Hoffer 88], [Date 90], [El-Bibany & Froese 89], [Townsend 92], [Liskin 93], [Access 92], [Turk 95] for data modeling and system design.  The meaning of repeating groups (set/sets of attributes within inner brackets) of a relation has been explained in section 1.6.8. The concepts of data modeling and E / R diagrams have been introduced in section 1.6.4 and 2.4.8, respectively. Definitions of some of the general terms used throughout the system views are shown in Table 4.1.  103  Table 4.1  General Key Terms, Abbreviations and Symbols  Key Terms  Definitions  Architects/Engineers Client/Owner Company Contractor  or A / E , an individual or firm offering professional services. the agency or individual requiring the construction project. any enterprise or organization included in the database. the person or private organization responsible for the site construction work (ABC Construction Company, in this system). company identification number of the supervising A / E . name of contact person of the A / E . official name of the A / E . phone number of the A / E . file reference number (as in the office filing system), short for item of work, or work item. nature of job performed by a company participating in a project. an individual included in the database or employed by a company the total construction of which the work performed under the contract documents may be the whole or a part. project identification number. official name of the project. a set of projects and companies. details of items included in a project. a set of projects and trades. details of trades included in a project. CSI Masterformat specification section. serial or identification number of a specs section. a person or an organization who has a contract with the contractor to perform a portion of the work at the site. company identification number of a subcontractor. company name of the subcontractor. name of contact person of the subcontractor. phone number of the subcontractor. a collection of items relating to one division or discipline of work on a project, e.g., H V A C , plumbing, electrical, etc. identification number of a trade, name of the trade. Work Breakdown Structure, a list or chart of work items used for estimating, costing and management purposes, identification number of a WBS. computed attribute, one-to-one relationship, one-to-many relationship. many-to-man\  EngineerJD Engr Contact Engr Name Engr Phone File Ref. no. Item Participant Type People Project ProjectlD Project Name Proj-company Project ItemDetails Proj-trade Project TradeDetails Specs Section SpecsID Subcontractor Sub ID Sub Name Sub Contact Sub Phone Trade TradelD Trade Name WBS  WBSJD *  104  4.4.1  View 1: Bill of Quantities  A Bill of Quantities (BoQ) is a schedule which gives brief descriptions and quantities of all the items of work involved. The quantities of work are computed from contract drawings and associated documents. A BoQ is prepared in accordance with a standard method of measurement giving the quantities of each item of work to be executed. The contractor enters a unit rate against each item of work and the extended totals are added to give the tender total. Whether it is a lump sum contract or a bill of quantities contract, a contractor needs a BoQ to build up his tender. In the former case, the BoQ is prepared by the contractor, and in the latter case, the BoQ is provided by the owner. A BoQ is similar to a Bid Breakdown which is used to determine the value of work performed each month on lump-sum contracts.  The Bill of Quantities (Figure 4.9) is useful in quoting for a job, estimating the cost of developing a new site, subcontracting out, producing a site budget and cash flow forecast, preparing periodic progress payment request, providing information to enable accurate cost control during the site construction, etc.  105  o  Ite  E  0.  o o o in r— 69  69  o o o o" o ^—  o o o Iff  Uni:  TRAC  ct> u  o o o o" o  o o o in CN T—  69  o o o in 00 69  o o o cf o  O O O m" CN CN 69 69  o o o o o m o m o 6 9 CM ^— 69 69 in CN &9  o o o in  o o m of •<*• i n 6 9 69  o o o of 69  O m o O CO o o 69 m in 69 69  o o o •«r x— 69  O in CO 69  O O O *r  o o o co" T— CO 69 69  o o o in GO 69  o o m CN w 69  o o o in 00 CO T— 69 69 o o o  o o o m m co m o CO 6 9 6 9 6 9 o 69 •<r oo 69  O O o in CO  o o o  o o o •<*•" i n m TCO — 69 69  O O o o" a> ^ ~  69  o o o  O O o in m CO T— 6 9 x— _ l </> 69  OTA  ••-»  z o o  .t: C 3  co CO E E c E CO E ca CD CO E E E CO CO CO CO cr o cr c r at CU a> c r c r c r _i _i 3 U CO -«-» in _ i in in in in _ j _ i _ i _ i  T  >>  _  o o T—  o o  o o o o o in CN  CO O T  o  O O O O o m •<r i n o CN T —  T—  T—  x—  3  o  cn  T3  ^ 8  o o  CO LU  t  I-  < o  ID m LU CO  Q-  o  0  9~  x UJ  a.  1  c o  co  >  in CD  X  UJ  c  LU LU —J -3  oo  cn a. 0-  :w  E cu £  o  >z n a.  co OQ  co l i co<* 8  _  e l o o Q  0)  _ V C31 *-  w  CO o  ' CO  — y ._ D) P O x : cu JO 3 08 3 D) p- ^ co XI CD O ^ O C 5 cE o C 3 C J 3 \£ a, i g E CT CD CO Q CD co m co O m ° > OCCO l O T - N C O ^ i nQ i. DX S i Cl LOU T - N C O t m i Q O M CO g o  t  o  cn c oo X3 C D  *-  o "a  CO Q  CO o c CO o o  <2  8 g  o  CL  §o  E  «  i "  oo OQ  CL Q .  '  II  5  ao  c  I  S2 8  106  This view provides bill of quantities information to the Construction Manager, Chief Estimator, Procurement Manager, Project Manager, Project Engineer, Planning and Scheduling Engineer, Cost Engineer, Estimator, Field Engineer, Superintendent, and Foremen.  The following queries are typical examples for this view of the data:  Construction Manager, Chief Estimator, Project Manager, Project Engineer, Estimator, and Accountant: •  Find the quantity of an item. The output should have the project id, project name, WBS id, and item description, quantity and unit.  •  Find the item cost and cost summary. The output should have the project id, project name, WBS id, item description and cost, subtotal for a division of work or trade, and grand total.  Procurement Manager and Cost Engineer: •  Find the quantity, cost summary, and budget of an item. The output should have the project id, project name, WBS id, item (description, cost, budget, and markup), subtotal for a division of work or trade, and grand total.  107  Planning  and Scheduling Engineer, Field  Engineer, Superintendent, and  Foreman: •  Find the quantity of an item. The output should have the project id, project name, WBS id, and item description, quantity and unit.  It is assumed that only one table is maintained for the Bill of Quantities of all the projects or sites executed by this organization. Also, a particular work item is identified by both ProjectlD and W B S I D .  The Bill of Quantities (BoQ) is expressed in the following relations: BoQ (ProjectlD, Project Name, ( W B S J D , Item Description, Quantity, Unit, Unit Price, Total Item Cost*, File Ref. no.*, (TradelD, Trade Name)))  Table 4.2  Key Terms of Bill of Quantities  Key Terms  Definitions  Item Description Quantity Unit Unit Price Total Item Cost  description of a work item as in WBS quantity of a work item unit of measurement of a work item price per unit of measurement = Quantity x Unit Price x % markup  The entities and relationships that apply to this view are shown in Figure 4.10. This figure is in accordance with the typical queries of the user view as discussed earlier in this section. Project ItemDetails (PID) is central to this data model. A PID can be identified by both ProjectlD and WBS_ID together (Table C.15, Appendix C). A PID is related to a  108  single project and a single WBS. A project may appear on many PID. There may be many projects. A WBS may appear on many PID. A WBS is related to a single trade. A trade will appear on many WBS. A project may have many trades. Bill of Quantities (BoQ) is just an extension (or subset) of the PID. A BoQ will have the following foreign keys: ProjectlD, WBS_ID and TradelD.  Trade  Figure 4.10 E/R Diagram for Bill of Quantities  4.4.2  View 2: Bid Summary Sheet  A bid or quotation is defined as an offer to do construction work for payment, the acceptance of which constitutes a contract between the contractor who made the bid (the bidder) and the owner who accepted it [Collier 94]. When a contract is awarded to a company, the project bidding documents and details provide the basic budget information  109  for a trade. A trade budget may be defined as the stipulated highest acceptable sub-bid price for a particular trade subcontract.  A sub-bid (here after referred as bid) is defined as an offer to a contractor to do a trade work for payment, the acceptance  of which constitutes a contract between the  subcontractor who made the bid (the bidder) and the contractor who accepted it. A bidder is not a subcontractor or supplier until a contract exists between him and the main contractor. Several bidders bid for various work packages or trades on a construction project. Before beginning any contract negotiation, all the bid information has to be organized and collated.  A Bid Summary Sheet (Figure 4.11) is useful as a collection point for information about bids submitted, bids requested and bids received on each of the trades of a project. Estimates submitted by bidders can be logged and tracked, or recorded and stored for later review. The purpose of this view is to provide a means of comparing one quotation with another to ensure sameness or to highlight differences.  This view provides bidding information to the Project Manager, Procurement Manager, and Estimator. This information relates to the bid submitted by a bidder for a trade package.  UJ UJ X  to  <  3  g  CQ  LU  CO Z>  o X  LU  1» _  or  <  5  i—  O x <  o CD cn  -  "o o' CL rx  O o <D cu  »  L:  L:  J O  c  CO  ro ° > o  >>N & .t: s_ CO  iS °  u c ^.  cu "G cu co  °  ° UJ O  T -  MO  ^  If) CD N  Q  — I  LU  h- CQ  <o or => CD  155,500 158,900 157,800  9,500 6,100 7,200  Bid Difference Amount $ $  o o o  ~>  c c c c CO CO CO CQ  T -  i-  —> —>  —3 ~i  C Inn QC  Iff r>-  5,000 6,500 3,600 10,500  U) CD S  70,000 68,500 71,400 64,500  10-Jan-95 10-Jan-95 10-Jan-95  UJ LU  5-Jan-95 5-Jan-95 5-Jan-95 5-Jan-95  O O O  5-Jan-95 5-Jan-95 5-Jan-95  10-Jan-95 10-Jan-95 10-Jan-95  Bid Rcving Date  \—  9999 6543 5432 4321  Difference  888 9999 987 6543 876 5432  Bid Due Date  CD  Bid Amount $  ACME Awal Engineering Super Aircond. Co.  Phone Number  r-  Bid Rcving Date  o,uuu  10,000 6,400 6,000  Bidder Name  Q  888 987 876 765  Qj  o o  Ground Contracting Excavation Contracor Smith Earthwork Co. Excavation & Hauling  D _i D CD or =>  O  <  Bid Due Date  Oi => O Q  125,000 128,600 129,000 127,000  Serial No.  h- CD  Q  Phone Number  hllj LU (M (O ^  Bidder Name  LU ^  = CO  Serial No.  Q < CO  14- Jan-95 15- Jan-95 14- Jan-95 15- Jan-95  O  o o o  15-Jan-95 15-Jan-95 15-Jan-95 15-Jan-95  .9.  9999 6543 5432 4321  — CO  888 987 876 765  Difference $  < or 9  CD  Bid Amount $  Q  Bid Rcving Date  iii UJ  Globe Electricals Polar Electric Co. Power & Cable Co. Light & Power Cont  o zz  Bid Due Date  r—  Phone Number  X CO  Bidder Name  < °> io  Serial No.  110  o o o o o o o o O N * 0) CM CO" h-* CD" CO"  o  . in oo io in io in  0)0)0)0)  ( N CN t N CN  in in uS in  ~>  CM CM CM CN  in in in in  CO CO CO CO —3 —t  0)  JC CO  cu  cr  ca  CN o v in CD N  CN n ^ in co s  E £ Ui ~u  m  CM  5  CJ  Ill  The following queries are typical for this view of the data:  Procurement Manager: •  Find the quotations and bidding from subcontractors and suppliers for different trades. The output should have the project name, trade or package name, bidder name, bid due date, bid package sent date, bid receiving date, and the bid amount.  •  Compare the quotations with the budget. The output should have the project name, trade name, its budget and difference between budget and bid amount, including the scope of work.  •  Provide a bidder ranking list or find the most competitive bidders to negotiate with. The output should have the bidder name, name of contact person, phone number, and fax number.  Project Manager: •  List the selected subcontractors and suppliers. The output should have the project name, trade name, subcontractor name, subcontract amount, and budget.  112  •  Find the start and completion dates for a trade in order to call the selected subcontractor to start the work. The output should have the project name, trade name, start and end dates, and selected subcontractor including contact names and phone numbers.  Estimator: •  Compare the items and their quantities quoted for. The output should have the project name, trade name, bidder name, bid details and bid amount.  It is assumed that the organization has several ongoing projects, each project has several trades for which bidding is sought, and that a bidder is bidding on more than one trade. Also, it is assumed that a particular bid is identified by a combination of ProjectID, TradelD and SublD.  The user view for the Bid Summary Sheet (BSS) is expressed as the following relations: BSS (ProjectID, Project Name, (TradelD, Trade Name, Budget, StartDate, EndDate, Bid Due Date, Lowest Bid*, (BidderlD, Bidder Name, (Bidder Type), (Bidder Contact), Bidder Phone, Bid Pkg. Sent, Bid Received, Bid Amount, Remarks, Difference*, File Ref. no.*)))  113  Table 4.3  Key Terms for Bid Summary Sheet  Key Terms  Definitions  Budget StartDate EndDate Bid Bid Due Date Lowest Bid Bidder BidderlD Bidder Contact Bid Pkg. Sent Bid Received Bid Amount Difference  total planned cost of work items included in a trade package start date of a trade activities completion date of a trade activities a proposal to do all or a part of the work for a stipulated sum date established for the receipt of bids for a trade the lowest bid that complies with the bidding requirements the company that submits a bid company identification number of a bidder contact or responsible person of a bidder date bid package sent to a bidder date bid received from a bidder bid amount quoted by a bidder difference between budget for a trade and the bid amount  The entities and relationships that apply to this view are shown in Figure 4.12. This figure is in accordance with the typical queries of the user view as discussed earlier in this section. Project TradeDetails (PTD) is central to this data model. A PTD can be identified by both ProjectID and TradelD together (Table C.16, Appendix C). A P T D may appear on many BoQ. A B o Q is related to a single P T D and a single project. A project may appear on many BoQ. A project may have many trades. A trade may be on many projects. A proj-trade is related to a single PTD.  A proj-trade may be related to a single bid. A bid can come from many bidders and a bidder can submit many bids. Thus, a bid can only be identified by all the following three together: ProjectID, TradelD and BidderlD (Table C . l , Appendix C). A bidder is related to a single participant type. A participant type may appear on many bidders. Each bid will appear on the bid summary sheet. A bid summary is related to only one Proj-trade but may  114  include many proj-trade-bidder. Only one winning bidder is identified for a proj-trade. Wining bidder is added to the respective proj-trade in P T D as a subcontractor.  Figure 4.12 E/R Diagram for Bid Summary Sheet  4.4.3  View 3: Change Orders Tracking Log  A change order is a written agreement to modify, add to, or otherwise alter the work from that of the approved contract plans and specifications. It is the only legal means available  115  to change the contract provisions after the award of the contract. Although it may be initiated by either party, it is usually prepared by the owner and submitted to the contractor, signed by the contractor, and submitted to the owner for acceptance.  Most construction projects go through contract modifications or changes during their construction phase. Sometimes, change orders are the primary cause of disputes and claims because they: modify the original contracts and record changes in the contract price and the project schedule. Hence, it is a necessity to have a means of monitoring and tracking the change orders affecting the contract cost and completion time.  A Change Order Tracking Log (Figure 4.13) is useful for logging and tracking change orders. The log is also invaluable as an effective means of locating and retrieving change order documents from files.  This view provides change order information to the Project Manager, Project Engineer, Construction Manger, Procurement Manager, Planning and Scheduling Engineer, Estimator, Accountant, Superintendent, and Foreman. This information relates to the current status of a change order and its details including description, approved change order amount, time impact, revised contract price, and revised completion date.  116  T— CN CN CO  6 d 6 ci z z z z 5  5  5  2  o o o u  o o o o .E .E .E .E  H H J! Ji ra ro ro co o n  1  in co  m  "V  io  5 5  Q-  I L S 5 <  £ o ci £ LL LL 5  <  LL LL. 5  <  c6 c6 CD J2  co co  a ^  UI  o  _l  en c  tn n  a «a S s  I 6 d> i (N O  «  RI  > IS u. u_ Q  o  (A 5  <  U-  V  CO  T3  fl> CO  II"  c  8888 8888 o  5 IO m CN I*" (CJ W » W V» S  a>  5  888§ 8 8 8° o> Co in r<N r*-" co" vt v* Vi  If o o ci  o o"  CO LO in  o  e> o  LO  o o d  dddd 8 88 S in co" w» « » CN"  111 o o d  vt  o o o o o o o o  #  CO d  5  in"  —I  o  5 s  2  o  115  W DC UJ  a or o  § 11  its IS  UJ  O z < X  o  JS  9  O  L;  o  !• !R "  o o  a: a: a. o. <  o a>  o £ o .g ° £ re co «  *° I I S  e O O .N "c <D > to ? 5o S 5 « o < o: o  *-  CN CN CO  2  5  d d d d z z z z  c O  IJsJ 13  B 3 tj  1 111  o o u at  i i  2  B  CO  ~  E CD CD C '5 •C C O <D  5  5  o o o o in in in oi co co  !. u.4 S 5. • d> 5  <  w 3 CO  117  The following queries are typical examples for this view of the data:  Project Manager, Project Engineer, Construction Manger, and Procurement Manager: •  Find the work items and trade subcontractors affected by a change order. The output should have the project id, change order number, description, status, trade name, and subcontractor id, name, contact name and phone number.  •  Find the revised contract price and completion date. The output should have the project id, change order number, change order cost, original contract price, total authorized change order price, revised contract price, original completion time, contract time impact in days, and revised completion date.  Planning and Scheduling Engineer: •  Find the contract time impact in days affected by a change order, and the revised completion date. The output should have the project id, change order number, status, original completion time, contract time impact in days, and revised completion date.  Estimator, and Accountant: •  Find the change order amount and revised contract price and completion date. The output should have the project id, change order number, status, change  118  order cost, original contract price, total authorized change order price, revised contract price, original completion time, contract time impact in days, and revised completion date.  Superintendent, and •  Foreman:  Find the status of a change order and the responsible subcontractor. The output should have the project id, change order number, description, status, trade name, and subcontractor id, name, contact name and phone number.  It is assumed that only one log is maintained to record change orders on all the projects or sites executed by this organization. Also, it is assumed that the CMR# (Contract Modification Request Number) is a unique number for a project, and that a particular Change Order is identified by both ProjectlD and CMR# .  The Change Order Tracking Log (COTL) is expressed as the following relations: C O T L (ProjectlD, Project Name, (EngineerlD, Engr Name, (Engr Contact), Engr Phone), OCBP, A C O * , RCP*, CO%C*, O C C D , C D A*, R C C D * , (CMR#, Initiation Date, Current Status, Description, EsitmatValue, EngrEstimate, Final Value, Time Impact, D-RfAE, D-StS , D-BfS, D-RtAE, D-FS, C M R Days*, Disposition, File Ref. no.*, (SpecsID, Specs Section, ( W B S J D , Item Description, (TradelD, Trade Name, (SubID, Sub Name, Sub Contact, Sub Phone))))))  119  Table 4.4  K e y Terms for Change Orders T r a c k i n g L o g  Key Terms  Definitions  OCBP ACO RCP CO%C OCCD CDA RCCD CMR CMR# Initiation Date Description EstimatValue EngrEstimate Final Value Time Impact D-RfAE D-StS D-BfS D-RtAE D-FS C M R Days  Original contract bid price in $ authorized change orders in $ revised contract price in $ change order as % of the contract bid price original contract completion date contract days added revised contract completion date Contract Modification Request Contract Modification Request number date C M R initiated/introduced brief description of C M R contractor's estimate of C M R engineer's estimate of C M R final settlement value contract time impact in days date C M R received from architect/engineer (A/E) date sent to subcontractor date back from subcontractor date returned to A / E date for final settlement total time taken in change order process in days  The entities and relationships that apply to this view are shown in Figure 4.14. This figure is in accordance with the typical queries of the user view as discussed earlier in this section. Change Order is the principal entity of this data model. A change orders log lists each change order. A change order can be identified by both ProjectlD and CMR# (Table C.2, Appendix C). A change order will be referred to a single specs section. A specs section may appear on many change orders.  120  A specs section is related to a single WBS. A WBS will appear on many specs sections. A WBS is related to a single trade. A trade will appear on many WBS. A proj-trade is related to a single project and a single trade. A project may appear on many proj-trade, as could a trade. A P T D is related to a single proj-trade and a single company (subcontractor/supplier). There may be many projects. A project may have many participating companies.  A company can work on many projects. A company may appear on many people. A people is related to only one company. A company may appear on many PTD. A PTD is identified by both ProjectlD and TradelD (Table C.16, Appendix C), so does a proj-trade. Thus, a proj-trade identifies a trade subcontractor or supplier for further actions. A projcompany is related to a single project and a single company. A project may appear on many proj-company, as could a company. It helps in identifying the A / E . People is related to a single company. A company may appear on many people. It helps in identifying the contact person of the A / E .  121  Company  employs. Proj-Company  participates^ \^in Project TradeDetails  People  Trade  Project  receives^  has  Change Orders  refers to  N  logs  is in  Proj-Trade \*—<^ joins  Specs Section L  Change Orders Log  Figure 4.14 E/R Diagram for Change Orders Tracking Log  4.4.4 View 4: Correspondence Tracking Log  Most construction projects produce a large amount of paper work. Correspondence is one of the principal methods of communication between site and external organizations such as owner, engineer, subcontractors, and suppliers. It is always useful to record and log all important information of incoming as well as outgoing correspondence for future retrieval.  122  A Correspondence Tracking Log (Figures 4.15a and 4.15b) is useful in keeping track of all incoming and outgoing papers. It is used to record any kind of correspondence such as letters, memorandums, interoffice communications, reports, invoices, etc. The log is also invaluable as an effective means of locating and retrieving correspondence from files in case it is needed for substantiation or negotiation.  This view provides incoming .and outgoing correspondence information to construction managers. This information relates to correspondence such as letters to or from project participants, change order packages, submittal records, as-built drawings, specification interpretations, finding of  facts,  records  of  negotiations,  minutes  of  meeting,  memorandums, inter-office communications, reports, invoices, etc.  The following queries are typical examples for this view of the data:  •  Find a specific item of correspondence. The output should have the project id, correspondence number, correspondence  date,  date received,  reference  number, from company id, to company id, keyword or phrase. The output should be able to be sorted on the basis of any of the above attributes.  •  Find the contents of a correspondence. The output should have the project id, correspondence number, reference number, description/notes.  123  Q-  < < c H I Q.  »  oi S  .§ 1 3 » o o bi oi o  • sa ^  1118 •El O (A "D  c  CO »- .  o> .E = _j>  I *  5  !  *5  I-  ;  iiSJ  5 " ^  1 § £ Is •c ^ 8.« -g M 8.1 S 8 1 5  ra cn £ S jfj •£ £ •o ° ra E £ £ 0) oi ts .= .E ^ ^ o CD ra ra c C CL. CL .E -S « O o. Q .ra-S 3 3 c E E (A  (A  CP  a  CO  E EE E  _l _l _l  >. — I 01  0  II  CO  £ 2 .H o 5 o. <o. a. a. 3 M  3 «  E E  Pt 9 P P  J2  t: ii t: i: o °  _l  3  O U-  _l  - J  —i  - J  wo £ w Q 03 g woo CC f| t3 CO gSofcoawcc o 5 g<tgQggogg  is8  D_D_ — XQ_Q_ — — Q_Q_ Q.Q.OOQ.Q.OOQ.D. C0COUJ<c/]COllJLUt0C/)  r§ n  I" 2  _J  O  o z  _S"5  LU  g  (I  •  I I  g°  0.  Q_ O O  CC  >>N O >~ CO  <  <-> X <  o w 35 o o cu cu  E"  2"  et ct  CD o CD CO CD . E J= O) c  c  O  c o LU O  CD  o  fc  "cO  , _  08-Jan-95 08-Jan-95 09-Jan-95 10-Jan-95 11- Jan-95 15-Jan-95 15-Jan-9S 15- Jan-95 16-Jan-95 16-Jan-95  LU O  LU  t  01  0  CC z  1.1 2.1 50.1 41.2 1.2 1.3 51.1 42.1  CO LU  cu c CO 'ro c o  • E ti . S E  i  05-Jan-95 05- Jan-95 06- Jan-95 06-Jan-95 08-Jan-95 10-Jan-95 10-Jan-95 11- Jan-95 12- Jan-95 12-Jan-95  1  o CD o  0  oi  clggggggggg  40.1  LU Q  CD tn  |  a> . i -  Date Received  O  CQ UJ CO  Corr. in No.  o =3  Q -  Date of Corresp.  o u. o  CD  O Q.  cJ  o lit c  LU  ti '  u n o 5 o CO C C 3 O < < LU LU O Q  1  <<  cu  o c CU •o c o a.  2 k> o  o  >  .2 6  ft.  i l l  o C8  t 5 cu  o  o  c  IS  (0  i*i*f£OcKo;£f£c£a: L y ^ W W w w W W w i i J  >•  O)  o —I  i - i M O V i n c B N o o o i O ' - c N O ^ i n c D N i i o o i o  ra  10  •«* 2! 3  11  124  o -i  O)  _c .fc.fc.fc.fc  IS o  « Q < 5  2 cu  9= 5 o 1 « g < cc o g S  •a  £LU £ g < K co 5 § et o °.  o c  0>  5 |  a  c o a  cc cc °=O  CC CC CC EC  Q. Q_ 3 W  0 . 0 . — — a.  LU — UJ Ul — zz\ I — — LU LU  0 O  Q. 0. 3 CO  ^ X —  O O 0 . 0- o Z LU CC 3  3  Z  o Z  </>  a. a. a. 3  3  < co co m m w co  £  L_ o  o t  cu  •S *  > JQ  m  z  • T~ O at o  t  O O  O UJ  o LU  a o QL  CO 111 CC  cc o o  o _c  3 OQ LU W  c .c  u  ±i  >  -  cu t>  o o a> a>  CU CO  CL CL  O  c o LU O  w-  d  . |  -  1  E g _ - .3 E P I g g g g g g g x g 9  <<<<<<<<<< rp fp m m ffl ffl  o>0)wC7>o>o>a>Cipcncn c c c c c c c c c c (onnmnnnnion  CD  o >< <  5  ffl rfl rn  cu < 5 "o> § LU O  I >O"  E 2 o u_  cu  LU CC  w  «  w C  ci) CD  z> 0 1  r-  E  CU O  cc  z  CN  N  P) r  f-  i - N n v i n i o N o o O ' - i N n t ^ i D s c o o i o  125  •  Find the originator or receiver name. The output should have the project id, correspondence number, reference number, from people id, to people id.  •  Find the original date and receiving date. The output should have the project id, correspondence number and date, reference number, receiving date.  •  Locate a filed correspondence or submittal. The output should have the project id, correspondence number, reference number, file reference no.  To simplify the presentation it is assumed that the organization has several ongoing projects, and only one Correspondence Tracking Log is maintained to record all incoming or outgoing correspondence. Also, it assumed that the Corr# (correspondence identifier) is a unique serial number of correspondence for a project (including both incoming or outgoing). A particular correspondence is identified by both ProjectID and Corr# .  The user view for the Correspondence Tracking Log (CTL) is expressed as follows: C T L (ProjectID, Project Name, (Corr#, Referenced CorrDate, Date Sent, DateRcvd, Follow up, (From People, To People, (From Company, To Company, (From Participant Type, To Participant Type))), Keyword 1, Keyword2, Notes, File Ref. no.*))  126  Table 4.5  Key Terms for Correspondence Tracking Log  Key Terms  Definitions  Corr# Reference#  project's correspondence entry serial number reference number of a correspondence (i.e. sender's reference number as appears on the correspondence) original date of a correspondence date of mailing date correspondence received follow up required? type of the project participant, e.g., engineers, supplier first keyword describing the subject second keyword describing the subject  CorrDate Date Sent DateRcvd Follow-up Participant Type Keyword 1 Keyword2  The entities and relationships that apply to this view are shown in Figure 4.16. This figure is in accordance with the typical queries of the user view as discussed earlier in this section. Correspondence is the principal entity of this data model. A correspondence log lists each correspondence. A correspondence is identified by both ProjectlD and Corr# (Table C.4, Appendix C).  There may be many projects. A correspondence can come from many People and vise versa. A people is related to only one company. A company may appear on many people. A company is related to a single participant type. A participant type may appear on many companies. These help in identifying the people, company and participant type.  127  F i g u r e 4.16  E / R D i a g r a m for  Correspondence T r a c k i n g L o g  4.4.5  V i e w 5: D a i l y Site Report (Daily Report)  Almost every construction project has a Daily Report. A daily report is an important part of site communications. It is primarily a summary of information and the principal method of conveying information on site matters to home office, the owner and other parties.  A daily report (Figure 4.17) traditionally records the number of equipment working, the names of the subcontractor working, amount of labor present for both the general contractor and his subcontractors, and a description of the work accomplished during the day, weather conditions, visitors to the site, problems encountered during the day, additional men, equipment, or materials necessary for near future work, and requests to the home office. This document becomes a part of the permanent file and is a most useful source of information in the event of a dispute.  128  DAILY SITE REPORT Project: Project*  Report No.  CITY WAREHOUSE BUILDING 1 Weather Temp. Wind Humidity  Owner: City Warehouse Engineer: XYZ Engineering, Inc. Contractor: ABC Contracting  AVERAGE Non-manual  Name of Contractor  15-Jan-95 M T W T F S S  Sunny 0-5 Still Dry  Cloudy 5-15 Moder. Moder.  FIELD FORCE Manual  ABC Contracting Awal Engineering Ready Mix Supply Smith Steel  5 10 12 8  Total Force Name  9:30 AM 11:00 AM  Ferguson, R. Brannon, W.  Rain 15-20 High Humid  Remarks  Supervision & Q.C. HVAC & Ducts Forms & Concrete Steel Erection  35 VISITORS Representing  Time  12  Date: Day:  Remarks Jobsite visitation Jobsite visitation  XYZ XYZ  EQUIPMENT AT THE SITE:  CONSTRUCTION ACTIVITIES:  MATERIAL NEEDS:  Prepared By:  Signature:  Figure 4.17  View-5 Daily Site Report  Snowy 20-30  129  This view provides project managers information about daily work progress, equipment in use and idle, personnel at the site, material needs, visitors and weather conditions on that day. This information relates to the reports of the field engineer or superintendent.  The following queries are typical examples for this view of the data:  •  Find the details of subcontractors force account (number of people working on the job-site). The output should have the project id, date and day, name of subcontractors and their field labor force.  •  Find the details of work progress and equipment. The output should have the project id, date and day, equipment in use, idle equipment, work in progress, new work activities, and work completed.  •  Find the details of weather conditions. The output should have the project id, date and day, and weather conditions, including temperature, humidity, and wind.  •  Find the names of visitors and their purpose of visit. The output should have the project id, date and day, name of visitors, name of representing organization, time of visits, and purpose of visits.  130  •  Prepare and print the daily report. The output should have all the above attributes, e.g., the project id, project name, date and day, engineer and owner name, weather conditions, listing of job-site visitors, recording of field force, reporting of work progress, recording of delivered and stored materials, and safety related matters.  It is assumed that only one system is used to input Daily Report data of all the projects or sites executed by this organization. A particular Daily Report is identified by both ProjectID and Date .  The Daily Site Report (DSR) may be expressed as the following relations: DSR (ProjectID. Project Name, (EngineerTD, Engr Name, (PeoplelD, People Name), (TypeTD, Participant Type)), (Date, (Day ID, Day), (Weather (sunny, cloudy, rainy, snowy), Temperature, Wind (still, moderate, high), Humidity (dry, mod, high)), (Prepared by ID, Prepared by), (FFC1, FFC2, FFC3, FFC4, NMFF1, NMFF2, NMFF3, NMFF4, MFF1, MFF2, MFF3, MFF4, Jobl, Job2, Job3, Job4), VisitTimel,  VisitTime2, Companyl,  (Daily Report#, Visitorl, Visitor2, Company2, Purposel, Purpose2,  EquipUse, Equipldle, Activities, New Act, Act Compl, Material Needs))  131  Table 4.6  Key Terms for Daily Site Report  Key Terms  Definitions  Date Report# Prepared by ID Day ID FFC1 field FFC2 field FFC3 field FFC4 field NMFF1 NMFF2 NMFF3 NMFF4 MFF1 MFF2 MFF3 MFF4 lob 1 Job2 Job3 Job4 Visitor 1 Visitor2 VisitTimel VisitTime2 Companyl Company2 Purpose 1 Purpose2 EquipUse Equipldle Activities New Act Act Compl Material Needs  date of a report project's serial number of a report PeoplelD of the person making report same as Day ID in Day table force contractor-1 C o I D force contractor-2 C o I D force contractor-3 CoJLD force contractor-4 C o I D non-manual field force of FFC1 non-manual field force of FFC2 non-manual field force of FFC3 non-manual field force of FFC4 manual field force of FFC 1 manual field force of FFC2 manual field force of FFC3 manual field force of FFC4 j ob/trade name of FFC 1 job/trade name of FFC2 job/trade name of FFC3 job/trade name of FFC4 name of visitor-1 name of visitor-2 time of visit of visitor-1 time of visit of visitor-2 name of the visitor-l's company name of the visitor-2's company purpose of the visitor-1' s visit purpose of the visitor-2's visit equipment in use at the project equipment idle at the project activities in progress at the site new activities started at the site activities completed at the site future material needs  The entities and relationships that apply to this view are shown in Figure 4.18. This figure is in accordance with the typical queries of the user view as discussed earlier in this  132  section. Daily report is central to this data model. A daily report combines daily events and daily weather. A daily report can be identified by both ProjectlD and Date together, and as could a daily events (Table C.5, Appendix C) and daily weather (Table C.6, Appendix C).  Both the daily events and daily weather relate to a single project. There may be many projects. A project may have many participating companies. A company can work on many projects. A company may appear on many people. A people is related to only one company. A company is related to a single participant type. A participant type may appear on many companies. These help in identifying people, company and participant type.  People Participant Type  Figure 4.18 E/R Diagram for Daily Site Report  133  4.4.6  View 6: Defective Work Notifications (DWN) Tracking Log  A Defective Work Notice is a written notice of deficiency and a formal demand for corrective action by the contractor. The term Defective Work refers to work that does not meet the contract requirements. These notices are received from time to time during the course of any project. A work may be rejected by the engineer or owner's representative on the basis of non-complying work, material, or tests of material.  A Defective Work Notification Tracking Log (Figure 4.19) is helpful in tracking down the defective-work notices received and the subsequent related actions taken for correction. As any non-complying work is subject to removal and reconstruction, corrective action, or acceptance by the owner upon consideration of a price discount, this log is very useful in tracking down rejected work and its nature of rejection,  corrective action needed, and  estimated value.  This view provides information about defective-work notices or non-compliance work notice to the Project Manager, Project Engineer, Field Engineer, and Superintendent. This information relates to the notices issued by architect/engineer of a project, and their corrective action needed.  134  c 0) E E o O  r  S  o  O  2  r  i  r  O  L  O  W  N  C  O  r  O  )  u^inioinin^^LnLjQLr) CnnCflCnCnwCAwwO)  •fi 1.1 O  S <»  O CJ Q/  c  c c c c=  c c c c c c c c c c LUUJUJIIJLJUJUJLUUJUJ 3  ^- <0  $ IS § .EEs ra *: >  •c-  CM  ^ -  T -  CM CN  3  3  3  3  3  3  < N  g S S5 U) un IA uj Ul inO c~ co •*»• co o  o o  o  LLI LLI  ra cc Q  o  3  3  3  3  3  3  3  rZc^ciiAS^'n^^S CN CO  T—  o. E E o  zt o  c  5 o  o i< o H-  *  T3 O  -a  i  LU > H O LU LL. LU Q  8 S  o o =  °> °" S  C»o TJo £o C  •5 2>3  5> D) S E .E  o x LLI CC  1$  <  H  (1) .E £ g UJ O  o  T-  L: o t i n LLI O  •*  s i *  Dl o CO o o) .9OQ-U5Q."c0LLJcj  O O c 0  O  E  II  1LU W  O  to S O o> S o .5 n i- TJ "O _L <B TJ X> .3- « o c  ai si  O 3 z  111 a |  33  c — o c 3 - 2  K> ° " —•s 0)  o  -  1 §  o o o o o o o o o o O N O O O O O d J O J) cNco^coin^i'^r^-T-'*cocoiocoinincNeNcoh. 0 0 - - 0 0 . - 0 0 0 0 inioininininintotnin c»o)cncno)0)0)0)u)0) 3  3  3  3  3  c6cNiouSci« x- CN CO  l A < i  ^l£  135  The following queries are typical examples for this view of the data:  Project Manager, Project Engineer: •  Find the details of non-compliance notices. The output should have the project id, notice number, description and type of rejection, notice date, receiving date, estimated value, subcontractor id, and correction status.  Field Engineer and Superintendent: •  Find the list of outstanding rejected work and its status for field inspection. The output should have the project id, notice number, area or location, subcontractor id, inspection date, and status.  It is assumed that only one log is maintained to record Defective Work Notifications of all the projects or sites executed by this organization. Also, it assumed that the DWN# (Defective Work Notification number) is a unique serial number of Defective Work Notifications for a project, and that a particular Defective Work Notification is identified by both ProjectlD and DWN# .  The Defective Work Notifications Tracking Log (DWNTL) may be expressed as follows: D W N T L (ProjectlD. Project Name, (EngineerlD, Engr Name, (Engr Contact), Engr Phone), (DWN#, NoticeDate, Description, DateRcvd, EstimatValue, Date Reinspected, Reinspected By, D-NoCR, TDfC*, Comments, File Ref.  136  no.*, (SpecsDD, Specs Section, ( W B S I D , Item Description, (TradelD, Trade Name, (SubID, Sub Name, Sub Contact, Sub Phone))))))  Table 4.7  K e y Terms for D W N T r a c k i n g L o g  Key Terms  Definitions  DWN# NoticeDate DateRcvd EstimatValue DateReinsp D-NoCR TDfC  project's D W N entry serial number D W N issue date date D W N received estimated value of the D W N item date the D W N item reinspected date no-objection certificate received total days for correction  U U A IU h U W J L U J U U U A If k U t ^ i^  The entities and relationships that apply to this view are shown in Figure 4.20. This figure is in accordance with the typical queries of the user view as discussed earlier in this section. Defective work notification (DWN) is the principal entity of this data model. A defective work notifications log lists each D W N . A D W N can be identified by both ProjectlD and DWN# (Table C.8, Appendix C). A D W N will be referred to a single specs section. A specs section may appear on many DWN.  As the Figure 4.20 is similar to the Figure 4.14., the relationships of entities specs section, WBS, trade, proj-trade, project, PTD, company and people are the same as explained in the last paragraph of section 4.2.3.  137  Figure 4.20 E/R Diagram for Defective Work Notifications Tracking Log  4.4.7 View 7; Materials Stored Tracking Log  This view describes materials delivered to the site but not yet incorporated. Most construction  projects  have  construction  materials  stored  at  site or at  another  predetermined locations. Information about the availability of materials is always helpful in order for construction to proceed smoothly. Some contracts even allow for partial or full  138  payment of materials stored. When costly items requiring long lead times to fabricate are delivered to the site, it is often customary to ask for partial payment for such items while they are stored at the site. The remaining value is then paid only after they have finally been installed in the work.  A Materials Stored Tracking Log (Figure 4.21) is useful in tracking down stored materials, their location and value.  This view provides the Project Manager, Project Engineer, Purchasing Agent, Estimator, and Superintendent information about materials delivered but not yet incorporated into the work.  The following queries are typical examples for this view of the data:  Project Manager, Project Engineer, Purchasing Agent, and Superintendent: •  Find the status of materials on the site. To get materials if required. The output should have the project id, material description, supplier id, supplier name, supplier phone number, stored location, original quantity stored, quantity installed to-date, location installed, quantity currently stored.  139  z> z S) 2 ui s m i<5 < / >  o  Q UI UI  o  o o LO LO CO CO Tt of Tf LO CM CM  o  1| z o  o  </>  o iu  </»  < ^2  88888888853 in o_ CN o_ o o> i - " T~ cj) r--~ oo" CN  CO CN CO  T -  T -  o o o O O O O L O O C co" co" cn  tf^  &t O O O  o o o o o o o O L O L O O O l f i O O O O m O O - ? co" co" T T " to" co" co" co"  8888888  i i i  o  o  o  o  o  o  o  STOI  Q UI  STO  tn o s Lu tt tt  (N CO ^ft" (/^  o < o  a  o . p. i n CN 0) o>  -r-*  Q UI  > — i  s s UI to zO a: 3 U o  Q  LO LO LO LO LO LO LO LO LO Cn CO f4 C)) L4  i- 2 Ui  ra LU Q  cMc^c^cococo^fcoTrTr  > -— i <s < in Q  ^^T-CMCNCMCOCSCOCO  Q_ LU  O)  o _i  c  !Z o  5  2. ra ra 2  CB CO ~ _ p p o CO C CO CO ^ o o 2 J? co <» ra u u 2 V w •o_ O O O O * o  ra ra  c  fp w w  a £ra•= S ra $ w ;  .  »  £. ra \_ ra m n  0 tS O " .•2 * 5 i n » CO CO O 111 CO Z LL CO  o o o  0.  3  I-  LU  co CO _l  <  0LU  1  <> / g  CO CD o O C C <D CD U . LL  « " O -ti CO  CD >  to O O C CO LL.  CM  2  3 O)  o  O  8  o CO CD o) ra _ S 2 o 2 w  £ o  to w ra cu «  U-  z  IQ LU  2g  o ra  a. z. s 3 -tt 2  o 5;  H •O  J2> cn .£ c  m  co  CD  ID  CO  0  1 LU a: < ¥  *  i— CD  U  LU  O  L:  LJ °  a) co ° c> o5  .. co tj i_ co co  o o g cn g CD CD JE c o o" o" O LU O k_  i  Q. Q_  UI  O  140  Estimator: •  Find the value of material stored and installed. The output should have the project id, material description, stored location, original quantity stored, original value stored, % installed, installed quantity to-date, amount installed to-date, location installed, quantity currently stored, amount currently stored.  It is assumed that only one tracking log is maintained to record materials of all the projects or sites executed by this organization, and a supplier is the same as the subcontractor of that trade. Also, it assumed that the Mat# (material number) is a unique serial number of material stored at a project, and that a particular stored-material is identified by both ProjectlD and Mat#.  The user view of Materials Stored Tracking Log (MSTL) in expressed as follows: M S T L (ProjectlD. Project Name, (EngineerlD, Engr Name, (Engr Contact), Engr Phone), (Mat#, Item Description, (Loc_ID, Location Stored), LocInstalled, Original Value, Pay Est#, Pay Est Date, % Installed, Amount Consumed To-date*, Amount Currently Stored*, File Ref. no.*, (SpecsID, Specs Section, ( W B S J D , Item Description, (TradelD, Trade Name, (SupplierlD, Supplier Name, Supplier Contact, Supplier Phone)))  141  T a b l e 4.8  K e y Terms for Materials Stored T r a c k i n g L o g  Key Terms  Definitions  Mat# Item Description Location Stored Loc-Installed Original Value Pay Est# Pay Est Date % Installed  project's materials stored entry serial number description of the materials stored as in the WBS location where the materials stored location where the materials installed total value of the materials when initially stored monthly payment estimate number monthly payment estimate date estimated % quantity installed to-date  The entities and relationships that apply to this view are shown in Figure 4.22. This figure is in accordance with the typical queries of the user view as discussed earlier in this section. Materials stored list (MS) is the principal entity of this data model. A materials stored log lists each material which is stored. An MS can be identified by both ProjectID and Mat# (Table C.9, Appendix C). A n M S is related to a single storage location. A storage location may appear on many M S L . A n M S will be referred to a single specs section. A specs section may appear on many MS.  As the Figure 4.22 is similar to the Figure 4.14., the relationships of entities specs section, WBS, trade, proj-trade, project, PTD, company and people are the same as explained in the last paragraph of section 4.2.3.  142  Company  employs:  is in Proj-Company U  People  Project TradeDetails  prepares"  Project  Materials Stored  has  refers tcr  Proj-Trade  joins  Specs Section L  stores  logs  Trade  ^corresponds to WBS  relates ti  Storage Location Materials Stored Log  Figure 4.22 E/R Diagram for Materials Stored Tracking Log  4.4.8 View 8: Monthly Progress Report  A contractor is required to submit to the engineer a monthly progress report or a monthly statement showing the estimated quantity of work done up to the end of each month for the progress payment. It is a very important part of the contractor's operation since it provides the cash flow to progress through the job smoothly.  143  A Monthly Progress Report (Figure 4.23) is a means of itemizing work completed to-date and extending the amount during the current pay period. This report includes all the items on the Bill of Quantities and further columns to show the current month's progress (quantity or percentage, and earnings), and to-date progress (quantity or percentage, and earnings). This view also provides total monthly value of work done for a particular trade for an interim payment to subcontractors.  This view provides monthly and up-to-date progress information to the Construction Manager, Project Manager, Project Engineer, Estimator, Planning and Scheduling Engineer, and Field Engineer.  The following queries are typical examples for this view of the data:  Construction Manager, Project Manager, Project Engineer: •  Find the progress of work. The output should have the project id, pay estimate number and date, work done and earnings (for the month and to-date).  Planning and Scheduling Engineer: •  Find the work remaining for updating the monthly schedule. The output should have the project id, pay estimate number and date, and work done (to-date).  o o oo uoooooo oo oo o o o o o c N o m o o ) " o" io"  8 CO v/  r-"  g" r-~ of  CN CN 00 CD Cft v / vy V 7 v 7 v /  o o a in co r~CN •tf T- CN  T-"  i-  o IO CN  o" CN  Cft Vi  Vi  UJ  oopinpoop  CD^CNCNCNCOCNCN  O O O If)  8SS88SS8  CD  O N N O O M S CCOO O IO 10 S Q Vi CN T " T - T I Vi  <ft  </>  <ft  <ft  CN T~ T - CN  o  CN  Vi Vi  <ft <  Vi  ui  oinmooininp CNCN-r-CNi-CNf-CN  o o o o o o o o o o o o o o o o o o O O Q Q Q O O O O O O O O Q OoO oO oO o o o No c 5o g5N o T fo i n ui c »o > -o^ io_ Do c_o o i_ n in cocDion o" ufViin" in" Vi Vi Vi Vi  O N • — T -  Vi  o  (ft  Vi  o O O Q in o in (ft M T -  o  888 o" If) o r» <ft  O O O I O If) If) co co ' - eft eft  i i  • in  UI  (ft (ft o_ o_ o S co S  (ft <ft  CN Vi  T -  §. E w w w w w w -J -i - i -i  c/) c/3 E E c  E (/) : i cr o cr • —i —i o in in —i 3  «- o o o  O O O •>-  o co  ° 88 £  r-  «-  CN  o  z  D _j  m in to Z>  o  ID  I LU  CO CO  CM  < >-  JO m  CO , _ CO o  o H  UJ  co  .§.  *i-  O LU o LU  - 3 —>  o o cra: a. a.  CO  LU  ai  <U  a  CO  M co  in CL LU  fe c  * 5 -8 5 8 § 8  CO  c ii  jo  2 3 <* 3 « o ^ o 2  §  =5  O co m co o j i c w g l i - N l O ^ i n i O N C O O l O ' - t M n ^ l D I O S O  a.  >. z is a.  145  Estimator, Field Engineer, Accountant: •  Determine the monthly progress. The output should have the project id, pay estimate number, pay estimate date, itemized work with total quantity, unit, unit price, work done and earnings (for the month and to-date), work done for a trade (for the month and to-date).  It is assumed that only one progress payment tracking log is maintained to record monthly progress of all the projects or sites executed by this organization. Also, it assumed that the Pay Est# (Payment Estimate number) is a unique serial number of the Monthly Progress reports for a project, and that a particular monthly progress is identified by both ProjectlD and Pay Est#.  The user view for Monthly Progress Report (MPR) is expressed as the following relations: M P R (ProjectlD. Project Name, (EngineerlD, Engr Name, (Engr Contact), Engr Phone), (Pay Est#, PayEst Date, This Qntty, This Earnings*, To-date Qntty, To-date Earnings*, (WBS_ID, Item Description, Quantity, Unit, Unit Price, Item Total*, (TradelD, Trade Name, (SubID, Sub Name, Sub Contact)))))  146  Table 4.9  Key Terms for Monthly Progress Report  Key Terms  Definitions  Pay Est# PayEst Date Item Description Quantity Unit Unit Price Item Total This Qntty This Earnings To-date Qntty To-date Earnings  project's sequential payment estimate number date of the payment estimate item description as in WBS total quantity of the item measurement unit of the item as in WBS unit price of the item in $ Quantity x Unit Price percentage of the work item done for the month Quantity x Unit Price x This Qntty percentage of the work item done to-date Quantity x Unit Price x To-date Qntty.  The entities and relationships that apply to this view are shown in Figure 4.24. This figure is in accordance with the purpose of the user view as discussed earlier in this section. Monthly progress is central to this data model. A monthly progress can be identified by both ProjectlD and Pay Est# together (Table C I O , Appendix C). A monthly progress report is an extension of a monthly progress. A monthly progress is related to a single project and a single BoQ. A project may appear on many monthly progress, as could a BoQ. There may be many projects. A BoQ is just an extension of PTD. A PID is related to a single WBS. A WBS may appear on many PID. A WBS is related to a single trade. A trade will appear on many WBS. A proj-trade is related to a single project and a single trade. A project may appear on many proj-trade, as could a trade. A proj-trade is related to a single PTD. Thus a proj-trade identifies a trade subcontractor for a progress payment.  Figure 4.24 E/R Diagram for Monthly Progress Report  4.4.9 View 9; Photographs Tracking Log  Photographs of job progress or construction details are a very valuable part of the project documentation. Some specifications  require a general contractor to take progress  photographs on a regular basis. This leads to a considerable amount of work in photograph and negative-handling alone. Even if it is not a project requirement, many contractors take job photographs for a variety of reasons, for example: to record the  148  uncovering of unusual conditions; to act as a further substantiation of a change-order request; to record a complex construction process to show compliance with the contract documents; to record the condition of materials, and environments; etc. The type of photographs are related to their purpose: progress photographs,  public relation  photographs, time-lapse photographs, claims-exhibit photographs, etc.  A Photographs Tracking Log (Figure 4.25) is a useful means for tracking down photographs by either roll number, negative number, facility or area, date, or keywords. This log is maintained as a permanent record of all construction photographs taken. It is an orderly means of photograph indexing and retrieval for any purpose. In addition, it establishes the date and sequence of photographs intended for claims exhibits.  This view provides project's photographs information to the Project Manager, Project Engineer, Planning and Scheduling Engineer, and Field Engineer.  The following queries are typical examples for this view of the data:  •  Find the details of a photograph. The output should have the project id, photo number, roll number, negative number, facility/location, date taken, time of day, name of photographer, direction of camera, position of photographer, photo type, keywords, and caption.  149  o  2 _ O  CO  ti 1  o>  <M  O  a.  -o E co 6 .£ o co o o X f XI  3  CZOA (  E « -c fe §•  CO _o JO CO O h l l L O l O U 3  0-  TJ  o  g  > > s  o -J  fl> -i  .9 O  t ? j Q : LU < w mo < > o ,2 < LU o LU cc o  r - O Q S O Q S  O)  o _l |  Q.  o  9 9  _i  LU — .  E  <S  <  —' o  LU  CO O  o o CO o o cn  LU LU 0. LU 5 <  5 <  5 0-  o o o  tn tn in io in in m 0) o> o> o> cn o> o> c c c c c c c (0  Q  «  CO 10 ffi CQ CO (0  "7 ~* "° "7 "? ~ LO LTS ci o S oS? a>~?Q CN <N CN CN CN CN CO  i- c O) O  o 2  s *  X  o  0. Q.  2a E  re  |  |  o _i  2 o _  o o  2 O t-  o  X Q_  o  CD " o CD_ to  LU  CD . i J= (5 ° > c  <  -2  C > LU > > N  O O O  O  <  ~  O  wo  T  _  » o  CD CD CL  >- CQ  X  CL  CD  2 8  L L CO L L L L  5 5 2 5 5 S < < < < < < CN TT O) T- TT IO  co O D)Z  LU CO Z)  CD "t> CD CO c  c  > O) < <= ° O H I O  a  E a> o o a. cn• a> > CM  "  C  6 E o 0. o  8  e  c  h  Z> CO  I  5  5 c c c ^ •o g b o s g -g <0 u- cc L L O u. co  LU  CD  c o  O  S  <<<< o O o  s 3  o o  CO IO CD CO  T  LO CO  £ 6 o T - M O ^ K l l D S t O d l O  o o  d  £  z  O)  Lu  150  It is assumed that only one tracking log is maintained to record photographs of all the projects or sites executed by this organization. Also, it assumed that the Photo# (photograph number) is a unique serial number of photographs for a project, and that a particular photograph is identified by both ProjectID and Photo#.  The user view for Photo Tracking Log (PTL) is expressed as the following relations: P T L (ProjectID. Project Name, (EngineerlD, Engr Name, (Engr Contact), Engr Phone), (Photo#, Roll#, Negative#, Date Taken, Time of day, (Photogr ID, Photogr Name), Camera direction, Photogr position, Facility/Location, Keyword 1, Keyword2, Caption, File Ref. no.*, (Photo TypelD, Photo Type)))  Table 4.10  Key Terms for Photographs Tracking Log  Key Terms  Definitions  Photo# Roll# Negative# Date Taken Time of day Photogr ID Camera direction Facility/Location Photgr Position Keyword 1  project's photograph entry serial number serial number of the film roll negative-number in the film roll date of photograph time of the day when the photograph taken PeoplelD of the photographer direction of camera while taking photograph name of the location photo taken for position of the photographer first keyword describing the photograph second keyword describing the photograph caption or title of the photograph purpose of a photograph  Keyword2  Caption  151  The entities and relationships that apply to this view are shown in Figure 4.26. This figure is in accordance with the purpose of the user view as discussed earlier in this section. Photograph is the principal entity of this data model. A photographs log lists each photographs taken on a project. A photograph is related to a single project and a single photo type. A project may appear on many photographs, as could a photo type. There will be many photo types. There may be many projects. A photograph can be identified by both ProjectID and Photo# together (Table C.13, Appendix C). A proj-company is related to a single project and a single company. A project may appear on many proj-company, as could a company. It helps in identifying the A / E . People is related to a single company. A company may appear on many people. It helps in identifying the contact person of A / E .  People L  Company  Project  JProj-Company  i  Photographs *  Photo Type  Photographs Log  Figure 4.26 E/R Diagram for Photographs Tracking Log  152  4.4.10 View 10: Punch Lists  A Punch List is a list o f items, usually minor, prepared by the owner or engineer, that must be completed by the contractor before a project is finally accepted. In other words, it is a guide list during finishing stages o f a contract to indicate all w o r k or corrective action remaining before acceptance o f the work. The quantity o f punch list items may vary from project to project. F r o m a contractor's standpoint, the punch list is the final doorway to project completion and acceptance. In order to ensure prompt completion o f this critical close-out requirement, a definitive method o f recording, tracking and reporting punch list items is a necessity o f the project.  A Punch List (Figure B - 1 0 , Appendix-B) is a useful means o f recording, tracking and reporting punch list items. It contains the date each punch list item originated, an accurate description o f the work to be done, the person generating the item, an estimated dollar value o f the work, the date each punch list item was rechecked, and the person rechecking the item.  This view provides punch list information to the Project Manager, Project Engineer, Field Engineer, and Superintendent.  153  o v8  0)<1)(U(UVII)(U<U(1)<1) <Dw<D(UQJ<D<D<D<l><l) C C C C C C _C C C C Q)D)0)O)D)D)GO)D)O) c c c c c c c c c c UJIlJUJLULUUJLULUUJLLl <I 7 >I0 0I > 0I ) 0t ) 0 I) 0 I) 0 )I 0 )I0 )I > * > t > t > i > i > s > . > < . > * : > . I  I  I  I  I  I  I  I  I  I  (0(000000000000000  ( 0 ( 0 ( 1 ) 1 0 ( 0 ( 0 ( 0 1 0 ( 0 ( 0 uSiAc6cDcir>-rii>-c6oi  o II  o -g II II CD O U J •o cu co TJ  "8 Q  § TJ  u m d i v v n i i u t t v a i QJCOCOCUCUCUCUCUCUCU c c c c c c c c c c. cocococncocococococo c c c c c c c c c c uJUJUJuJLJLlJLLJUJUJLLJ ifioinLnLnLOinininir) C)cn0)O>c7)O)O)c>o)C) 0.0.0.0.0.0.0-0.0.0. <<<<<<<<<:<: ySiAi/SuSuSinuSioioLo N N N N N f M C M C M C M N  J2 •5 u o j= £  (0  o c  3 0.  c D .O c C CO) - -C c <= ca c £ •= o ~ o * o C o u CO CO 5 UJ ui c p o — O a> CJ cu •o "ra O -5 o O -2  >  55  (N. CN  8  S 8 a; 8 •§) -2 .8 -2 8  8|  O < < O < O  <<  5  a>  •<*  2  8  3  s  ca °CJJ CD • CO . £ •= .Si 2 CO _ E -*2 & - =  8  2.  TJ CU "a o c -C g)a> o 0) t CD J = ° E c § 2g  8* o  ^  1 §% 8 I  co z> o  TO CD 3 O r £ to > y £  X LU  rr <  3 X  o z 0_  § 8  o  LU  £  dl 5 OJ 03 c h c c o UJ U N O > CO  If  o o . c? 'c?  I—  CL CL  JE O  c  0  LU O  OIUUJIOUIO11151  .88 08 8080808 0. 80. 80.08 0 0  OJ co  < u  k_  1 <"  <<<<<<<<<<  o x <  o  3  g o o ca g o o S ^ "2 E D) Q. J 9- 2 cu 0 ^ 0 — CO CO C m co +-* o o c JB J3 co S co J= = a) 3 a; C CO c D ra CO c O W O = co TJ* ra n .cp U < CO  .8  m  ro  0) %  154  The following queries are typical examples for this view of the data:  Project Manager and Project Engineer: •  Find the status of remaining work items, and the name of the responsible subcontractors. The output should have the project id, item description, status, Engineer Name, Engineer Contact, Engineer phone, subcontractor name, contact name, and phone number.  Field Engineer and Superintendent: •  Find the remaining work items and their details. The output should have the project id, item description, facility/area, date identified, date completed, date checked, subcontractor name, contact name, and phone number.  It is assumed that only one tracking log is maintained to record punch list items of all the projects or sites executed by this organization. Also, it assumed that the PL# (punch list number) is a unique serial number of Punch List Item for a project, and that a particular Punch List item is identified by both ProjectlD and PL# .  The Punch List (PL) may be expressed in relational form as follows: PL (ProjectlD. Project Name, (EngineerlD, Engr Name, (Engr Contact), Engr Phone), (PL#, Punch List Item, Facility/Area, Date Identified, Identified By, Date Completed, Date Checked, Checked By, File Ref. no.*, (SpecsID, Specs  155  Section, ( W B S J D , Item Description, (TradelD, Trade Name, (SubID, Sub Name, Sub Contact, Sub Phone))))  Table 4.11  Key Terms for Punch Lists  Key Terms  Definitions  PL# Punch List Item Facility/Area Date Identified Date Completed Date Checked Checked By  project's punch list entry serial number item description of the punch list facility or area of the punch list item date noticed or identified by the A / E date the item completed for re-inspection date rechecked or inspected by the A / E PeoplelD of the inspector  The entities and relationships that apply to this view are shown in Figure 4.28. This figure is in accordance with the typical queries of the user view as discussed earlier in this section. Punch lists is the principal entity of this data model. A punch lists log enters each punch list item. A punch list can be identified by both ProjectlD and PL# (Table C.18, Appendix C). A punch list item is related to a single project and a single specs section. A project may appear on many punch list, as could a specs section.  As the Figure 4.28 is similar to the Figure 4.14., the relationships of entities specs section, WBS, trade, proj-trade, project, PTD, company and people are the same as explained in the last paragraph of section 4.2.3.  Figure 4.28 E/R Diagram for Punch Lists  4.4.11 View 11; Requests for Information (RFI) Tracking Log  During the bidding and construction phases, a number of questions arise regarding interpretation of those portions of the plans and specifications that are unclear, vague, or in conflict with the contract documents. These field questions are often termed Requests for Information (RFI) or Request for Clarification (RFC). In most construction contracts,  157  the engineers are obliged to provide answers to the questions in a reasonable amount of time. Sometimes, late clarification is a basis for claims. Hence a system is required for filing, locating and retrieving both questions and answers throughout the life of a project.  An RFI Tracking Log (Figure 4.29) is a means of logging and tracking field clarification questions, critical items and their dates. This log is used when questions arise from subcontractors and require answers from the general contractor or from the design consultants, or when the general contractor needs to obtain information or clarification from a subcontractor or owner/architect/engineer.  This view enables the Project Manager, Chief Engineer, and Project Engineer to control information about RFI or clarification from different project participants.  The following queries are typical examples for this view of the data:  •  Retrieve a particular RFI. The output should have the project id, initiation date, initiator id, and RFI# and description.  •  Find details of a particular RFI. The output should have the project id, initiation date, initiator i d , , RFI# and description, related specs section and drawing number, dates of transaction, response, comments.  response by, and  158  oooocnocnoocoocoTI- I- Q Q. 3 3 2 2 2 3 3 3 2 2 2 < < < o o < < < o o o  V  IS Q  Loincnioii2y2toioLnLnLO CqCoCnCnCaOlcnCflCnOlCn 1 1 1 1 1 1 1 1 1 1 • c c c c c c c c c c c  A  B  B  B  B  B  B  B  B  B  B  I  B  9O C —  C^SSSSPSL^CNTI-CO ^ - I - ^ ^ ^ N C S N N C M C M  "8  cninuiinininioinisinin a)c^czocno)cAcj>a)cnc7)C7) c c c c c c c c c c c cacacaracocacocococaca  S > 15 ra <U  m  a:  cn o _i c !2 o  cocoiAcocnciciiAcN^co ^ ^ ^ T - T - C M C M C M C M C M N  • 2 S  m i n i n i n i n u i i n i n u i i n m oicncnoicncflcpcxcncncf) c c c c c c c c c c c cococococacocacocacocti —3 —3 —3 —3 3 5 3 3 3 3 ~^3 i^uScAciiicicNCNcori  c o CQ  E  CD  ^1  <u  o  %2  _l  C Q.  tu "5. « E  CD Z  c^03 c  z  8 a c a  cr  ]? 8  "22 -acoIH r\ cn co J  "ffi  a  LL  £3 3  •—  2 £lc! :  £  co LU  a LU tt  CN CO CN CJ>  c '5 CQ CD to 3 O JZ  cu  o• * J 3*3  u o CU c u  "o "o CL CL  eI  0  5  u.  CO  c  J2 3  TJ  H  C to ± CO co Q o o o JS J2 $ o i O > Q Q Q O O  cn O cn co to * : 0) t»  r -  or o  C TJ 't5 o JS  ^8  o or o  1 8812  o  4)  co  «> "ffi  <  c  ?v2  ±66 2  0  a> >  cn CN  cn  iZ  o _c c u h •c c c £cora co O  03 CU C +3 CO *bZ 3 <U CO O «  O X ^  S  L; CU O CU CO . £ £=  c o LU O  o o o «c u  •2  CS  CQ  CS  Sa  T -  O O O O O O O O O O C ) ctJCBcatSfScocafatstoco E E E J J | E E J J | lololflmlototo'to'to'co'ti) UJUJUJLULiJLUllJLlJUJLULlJ cncncnmcncoraCnOiCnCn 1 1 1 i 1 1 1 1 • • 1 c c c c c c c c c c c COCOCOCOCOtOtStOfOCOfO cfSifScAciSicicNCMtAri T - N l O ^ i n t O M D  0 ) O t - C N C 0 T r i 0 C D I ^ 0 0 0 ) O x - C N  159  •  Find the total response time. The output should have the project id, initiation date, RFI# and description, response date, and response time.  •  List the outstanding RFIs. The output should have the project id, RFI#, initiation date, and response date.  •  Find the contact numbers of engineer and subcontractor. The output should have the project id, RFI#, engineer name, engineer contact, engineer phone, sub name, sub contact, and sub phone number.  It is assumed that only one tracking log is maintained to record RFIs of all the projects or sites executed by this organization. Also, it assumed that the RFI# (requests for information number) is a unique serial number of RFI for a project, and that a particular RFI item is identified by both ProjectID and RFI# .  The user view for the RFI Tracking Log (RFITL) is expressed as the following relations: RFITL (ProjectID. Project Name, (EngineerlD, Engr Name, (Engr Contact), Engr Phone), (RFI#, Initiation Date, (Initiated By, (Initiator ID, (Initiator Type))), Drawing*, RFI Description, D-RfS, D-StAE, D-RBfAE, D-SBtS, (Response by), Total Days*, Comments, File Ref. no.*))  160  Table 4.12  Key Terms for R F I Tracking Log  Key Terms  Definitions  RFI# Initiated By Initiator ID Initiator Type Initiation Date Drawing* RFI Description D-RfS D-StAE D-RBfAE D-SBtS Response By Total Days  project's RFI entry serial number PeoplelD of the initiator Company identification number of the initiator Participant Type of the initiator company date of RFI initiation drawing number for reference short description of the RFI date received from subcontractor date sent to architect/engineer (A/E) date returned back from A / E date sent back to subcontractor PeoplelD of the person who responded the RFI total time taken for a response in days  The entities and relationships that apply to this view are shown in Figure 4.30. This figure is in accordance with the typical queries of the user view as discussed earlier in this section. Requests for Information (RFI) is the principal entity of this data model. A n RFI log lists each RFI. A n RFI can be identified by both ProjectID and RFI# (Table C.19, Appendix C). An RFI is related to a single project and a single people. There may be many projects. A project may appear on many RFI, as could a people.  A people is related to a single company. A company may appear on many people. A company is related to a single participant type. A participant type may appear on many company. These help to identify initiator company and participant type. Proj-company is related to a single project and a single company. A project may appear on many projcompany, as could a company. These help in identifying the A / E and their contact persons.  Figure 4.30 E/R Diagram for Requests for Information (RFI) Tracking Log  4.4.12 View 12: Shop Drawing Submittals Tracking Log  Submittals are an ordinary part of just about every construction project. The contractor and his subcontractors submit shop drawings, samples, certificates, test results, etc., for review by the owner or engineer. These submittals contain detailed information concerning relationships, quantities, construction methods, locations, sizes, and other information about equipment or materials to be incorporated into the construction project. A review of the specifications establishes the procedures for shop drawing or any other submissions.  162  Contractors often base equipment/material orders on timely review and return of the shopdrawing submittals. Most construction contracts require the engineer to review, comment on, and return shop-drawing submittals to the contractor within a certain time frame. Any shop drawings that are returned late can be a basis for a claim for time extension or have other impacts on the project. It is also important to note when a particular submittal is due from the subcontractors and suppliers, so that they can be reminded.  A shop drawing submittals log is a documentation of all shop drawing transmittals received from the subcontractors or sent to the engineer, and a record of the action taken in each case.  A Shop Drawing Submittals Tracking Log (Figure 4.31) is a means of logging, tracking, and following up on submitted shop drawings. Not only does such a tracking log allow a project manager to keep track of what drawings have been received; it also shows where the drawings have been sent and how long they have been there.  This view provides shop drawing submittals information to the Project Manager, Project Engineer, Purchasing Agent, and Superintendent. This information relates to the status of a shop drawing submittal which is submitted by a subcontractor or supplier and reviewed by the architect/engineer.  163  S  C  f I  |i 1°  r~ co to  Copies back to Sub  ill  a  i CN  -^r  CN  T CO  16-Jan-95 19-Jan-95 19-Jan-95  ro c 0) (U > Q- CO I—  •6 8  CD CO CD  15-Jan-95 18-Jan-95 18-Jan-95  <  08-Jan-95 10-Jan-95 10-Jan-95  CO  Submittal/Re view Status Date Date Sent Back from back to Engineer Sub/Supp  Jp CQ O -  Date Sent to Engineer  a  bmittals Tracking Log  £  ; °o (J c (0  c (0  to to  c  c  C  c  ^^3 C  c  to ro ro to g  OS O  O  Q  O <  o  3  T  IT)  13  o  A  .a  -I  e> z o 2  2 m Z) (0 0  z  1 Q Q. 0 1  CO  3 CQ LU CO  o X LLI  CC  <  Q  CO CN  r-  r—  O  O O  LU LU  O O CC cc Q_ Q_  i i  ro co CN <  .. *  15 3 (0  O  O  i  164  The following queries are typical examples for this view of the data:  Project Manager and Project Engineer: •  Find shop-drawing due for submission. The output should have the project id, shop-drawing number, keyword description, scheduled date of submission, trade name, sub id, name, contact name and phone number.  •  Find the details of a particular shop-drawing. The output should have the project id, shop-drawing number, keyword description, scheduled submission date, specs section, submission and review dates, disposition, and comments.  •  Find the status of a shop drawing approval. The output should have the project id, shop-drawing number, dates of submission and reviews,  disposition,  comments, engineer name, contact and phone number.  •  Find the time-lapse for shop-drawing review. The output should have the project id, drawing number, submission and approval dates, and total days to process.  165  Purchasing Agent and Superintendent: •  Find the status of a shop drawing. The output should have the project id, shopdrawing number, dates of submission and reviews, disposition, and comments.  It is assumed that only one tracking log is maintained to record Shop Drawing Submittals of all the projects or sites executed by this organization. Also, it assumed that the Shop Drwg# (shop drawing number) is a unique number (computed through query expression) for a project, and that a particular Shop-Drawing Submittal is identified by both ProjectID and Shop Drwg#.  The Shop Drawing Submittals Tracking Log (SDSTL) is expressed as follows: SDSTL (ProjectID. Project Name, (EngineerlD, Engr Name, (Engr Contact), Engr Phone), (Shop  Drwg#, Scheduled Date*, Description, Disposition,  D-RfS, D-StAE-I, D-StAE-R, D-StAE-F, D-RBfAE, D-SBtS, C-RfS, C StAE, C-RBfAE, C-SBtS, TDP*, Comments, File Ref. no.*, ( W B S J D , (TradelD, Trade Name, (Sub ID, Sub Name, Sub Contact, Sub Phone)))))  166  Table 4.13  Key Terms for Shop Drawing Submittals Tracking Log  Key Terms  Definitions  Shop Drwg# Scheduled Date Description Disposition D-RfS D-StAE-I D-StAE-R D-StAE-F D-RBfAE D-SBtS C-RfS C-StAE C-RBfAE C-SBtS TDP  shop drawing number scheduled date of shop-drawing submission keyword or title of the shop drawing disposition or comments date received from subcontractor date sent to architect/engineer (A/E), initial date sent to A / E , re-submission date sent to A / E , final date returned back from A / E date sent back to subcontractor copies received from subcontractor copies sent to A / E copies received back from A / E copies sent back to subcontractor total days to pro  The entities and relationships that apply to this view are shown in Figure 4.32. This figure is in accordance with the typical queries of the user view as discussed earlier in this section. Shop drawing submittals (SDS) is central to this data model. A SDS can be identified by both ProjectID and W B S T D together (Table C.20, Appendix C), as could a PID (Table C.14, Appendix C). A SDS is related to a single PID, and vise versa. A shop drawing log lists each SDS.  As part of the Figure 4.32 is similar to the Figure 4.14., the relationships of entities specs section, WBS, trade, proj-trade, project, P T D , company and people are the same as explained in the last paragraph of section 4.2.3.  167  Figure 4.32 E/R Diagram for Shop Drawing Submittals Tracking Log  4.4.13 View 13: Spare Parts Tracking Log  Some construction contracts require installation of different types of equipment and sometimes also require a large inventory of spare parts to be handed over to the owner at the end of the contract. A record of spare parts becomes an important part of a project. A Spare Parts Tracking Log (Figure 4.33) is a means of tracking down spare parts that have or have not been delivered to the owner.  168  5) 55 &  CO Q-  8) 8) 05 55  •tt -a =tt -tt  E E E . co c  c:  c  ST 5T ST cr  3 • E E E  .  . ±i ±i c  a- d ci d cr cr cr O o c to c  O ' T-  cr cr  O Q CN r  tO W tO C C 3 3 i i O CN CN C N C N i < i C O f—  C D « U ) W W W t f l C » « O ) C 0  o  o  o  o  o  2 o o o o  o  < D Q ) C U C U C U C U < U C L ) O O C L )  1  CD Z  O O to I-  < CL  LU W  0 1 UJ  cc  I O  T-  LU  < CL 00  o  o  . . m o  S.2  cu OJ  o"o  a!  CL  O  UJ  O  _  rj  —  s  w  ss  —  Li  CJ F O  '1% i  CO ! "C  OJ CO  ra <j3  88S  i N C O C O ^ I Q C N C O < J 5 T - C N I J O C D  irir^cocococoQ^^^in O O O O O O O - « - - " - - > - T -  169  This view provides spare parts information to the Project Manger, Procurement Manager, Purchasing Agent, and Superintendents. This information relates to the listing of various spare parts to be delivered to the owner and their status.  The following queries are typical examples for this view of the data:  Project Manger and Procurement Manager: •  Find the status of spare parts delivery. The output should have the project id, spare part number, spare part description, equipment description, quantity required, quantity delivered, balance quantity to be delivered, supplier id, contact and phone number, and Owner/engineer contact and phone number.  Purchasing Agent and Superintendent: •  Find the list of spare parts to prepare a purchase order. The output should have the project id, spare part number, spare part description, description, specs section,  equipment  quantity required, quantity delivered, balance  quantity to delivered, location stored, supplier id, contact and phone number.  It is assumed that only one tracking log is maintained to record the Spare Parts of all the projects or sites executed by this organization. Also, it assumed that the SP# (spare part number) is a unique serial number of Spare Part for a project, and that a particular Spare Part item is identified by both ProjectlD and SP#.  170  The user view of Spare Parts Log (SPL) is expressed as the following relations: SPL (ProjectlD. Project Name, (EngineerlD, Engr Name, (Engr Contact), Engr Phone), (SP#, SP Description, E q Description, Qntty Required, Qntty Dlvd, Unit, ( L o c f D , Location Stored), Status*, File Ref no.*, (SpecsTD, Specs Section, (WBSJLD, (TradelD, Trade Name, (SupplierK), Supplier Name, Supplier Contact, Supplier Phone))))))  Table 4.14  Key Terms for Spare Parts Tracking Log  Key Terms  Definitions  SP# SP Description E q Description Qntty Required Qntty Dlvd Unit Location Stored Status  project's spare parts entry serial number description of the spare parts which equipment the spare part is for total quantity required to be delivered actual quantity delivered to-date unit of the spare parts storage location of the spare parts balance spare parts to be delivered  The entities and relationships that apply to this view are shown in Figure 4.34. This figure is in accordance with the typical queries of the user view as discussed earlier in this section. Spare Parts (SP) is the principal entity of this data model. A spare parts log lists each spare part which is stored. A SP can be identified by both ProjectlD and SP# (Table C.21, Appendix C). A SP is related to a single storage location. A storage location may appear on many SP. A SP will be referred to a single specs section. A specs section may appear on many SP.  171  As the Figure 4.34 is similar to the Figure 4.14., the relationships of entities specs section, WBS, trade, proj-trade, project, PTD, company and people are the same as explained in the last paragraph of section 4.2.3.  Company  employs.  is in Proj-Company  People is part  participates^ \^in Project TradeDetails  identifies* app fror Trade  Project  prepares^ Spare Parts List  has " N — J Proj-Trade  refers tcr  joins  Specs Section k stores  logs  ^corresponds to WBS  relates ti  Storage Location Spare Parts Log  Figure 4.34 E/R Diagram for Spare Farts Tracking Log 4.4.14 Integrated View The E / R diagram of the integrated view is shown in Figures 4.35a and 4.35b.  172  Project  has  involve!  Company  semployj  Bids  ibmiti  People  .bids or  Project-Trade  . enters ^details Project TradeDetails  Bid Summary Sheet  identifies*  Winning Bids/ Subcontractors  joins Trade  Shop Drawing Log Change Order Log Defective Work Log Punch List Log Materials Stored Log Storage Location Spare Parts Log Monthly Progress Report  Figure 4.35a E/R Diagram for Integrated View (Part A)  173  Daily Site Report  Requests for Information Log Participant Type  Correspondence Log  Legends: —•  one to many relationship one to one relationship User View  Figure 4.35b E/R Diagram for Integrated View (Part B)  174  4.5  Structural Data Model  The conceptual models of the database for each user view is presented in the previous section. The conceptual model, so far presented, are in the form of relations and entityrelationship (E/R) diagrams. This section continues with the conceptual modeling, and includes normalization and development of structural data models.  The main purpose of using the tool of E/R diagrams is to show the pictorial views of the entities, their relationships and degree of relationships; and the S D M diagrams is to show database structures (tables) joined by table relationships. Figure 2.5 is reproduced, below, from section 2.4.9, for a reference in this section.  Relationship  Insert/delete Constraints  Symbol  Ownership  Emp. 1—X{  Reference  Emp. >  Subset  Emp.  Skills I 1 Dept  —3 Salesman  n e e  d employee/skills goes with employee  employee needs department/ department stays salesman must be employee/salesman goes with employee  The basic difference between an entity-relationship model (ERM) and S D M is the data models they represent. Although, both the models are regarded as semantic or meaning associated with data, and can be used as the basis for a systematic database design methodology.  175  The main differences between E R M and S D M can be explained as follows:  •  E R M emphasizes  the relationships between entities.  S D M formalizes the  relationships between entities.  •  E R M is expressed as a degree of relationship between entities (1:1, 1:M, or M:l). S D M is expressed as a connection between entities (ownership, reference, or subset).  •  E R M distinguishes between entities and relationships. S D M does not make any major distinctions between entities and relationships; a relationship is regarded as an entity.  •  E R M does not provide constraints and integrity details precisely. S D M provides constraints and the integrity rules.  •  E R M provides a map of the information system, and also acts like a process model. In SDM, relations are used to capture data about objects and their parts.  •  E R M is more flexible. It allows to create entities for M : M relationships, and uses normalized relations.  176  •  E/R diagrams for an organization may become voluminous. S D M diagrams require less space.  The following sections, 4.5.1 to 4.5.13, present the normalized relations in third normal form (3NF) or local data model and structural data model diagram for each user view. Section 4.5.14 presents the structural data model diagram for an integrated (user) view. The underlined attributes are prime keys.  E a c h sub-section is divided into three parts. The first part presents a set o f entities or relations derived from the relations, as expressed in sections 4.4.1 to 4.4.13, for each user view. E a c h entity is assigned a unique name. The second part presents the entities in their third normal form (3NF). Normalization process is based on the normalization example as described in section 1.6.9. The part shows a structural data model ( S D M ) diagram.  4.5.1  V i e w 1: B i l l of Quantities  The initial relations derived from the entity-relationship model, as shown in section 4.4.1, are as follows: Project (ProjectID. Project Name)  177  Bill of Quantities (ProjectlD. WBS ID. Item Description, Quantity, Unit, Unit Price) Project ItemDetails (ProjectlD. WBS ID. TradelD, Trade Name)  The local data model or normalized relations for the above view are as follows: Project (ProjectlD. Project Name) WBS (WBS ID. Item Description, Unit, TradelD) Trade (TradelD. Trade Name)  Project ItemDetails (ProjectlD. WBS ID. Quantity, Unit Price)  The system control derived from the semantics required by the users is shown in Fig. 4.36.  Project  Project ItemDetails  ^7 Trade  Figure 4.36  WBS  Bill of Quantities  Structural Model Diagram for Bill of Quantities  178  4.5.2  View 2: Bid Summary Sheet  The initial relations derived from the entity-relationship model, as shown in section 4.4.2, are as follows: Project (ProjectID. Project Name) Project  TradeDetails (ProjectID. TradelD. Trade Name, Budget,  StartDate,  EndDate, B i d Due Date) Bids (ProjectID. TradelD. B i d d e r l D . Bidder Name, Bidder Phone, B i d P k g . Sent, B i d Received, B i d Amount, Remarks) Bidder Type (ProjectID. TradelD. B i d d e r l D . Bidder Type) Bidder Contact (ProiectlD. TradelD. B i d d e r l D . Bidder Contact (=PeopleID))  The local data model or normalized relations for the above view are as follows: Project (ProjectID. Project Name) Trade (TradelD. Trade Name) Project TradeDetails (ProjectID. TradelD. Budget, StartDate, EndDate, B i d D u e Date, SubID (Co_ID), Subcontract Amount) Bids (ProjectID. TradelD. B i d d e r l D (=Co_TD), B i d P k g . Sent, B i d Received, B i d Amount, Remarks) Bidder (BidderlD. Bidder Name, TypeTD, PeoplelD, Phone), or Company (Co I D . C o N a m e , TypelD, Contact (=PeopleID), Phone) People (PeoplelD. Name, C o _ I D )  179  Participant Type (TypelD. Participant Type)  The system control derived from the semantics required by the users is shown in Fig. 4.37.  r  Project TradeDetails  People  ><  A.  Company  1 Trade  Project  > Participant Type  N<  Bids  K  I r B i d Summary Sheet  Figure 4.37  4.5.3  Structural Model Diagram for Bid Summary Sheet  View 3: Change Orders Tracking Log  The initial relations derived from the entity-relationship model, as shown i n section are as follows: Project (ProjectlD. Project Name, O C B P , O C C D ) ProjectEngr (ProjectlD. EngineerfD, Engr Name, Engr Phone) EngrContact (EngineerlD. Engr Contact (=PeopleID))  4.4.3,  180  Change Order (ProjectID. CMR#. Initiation Date, Current Status, Description, EsitmatValue, EngrEstimate, Final Value, Time Impact, D-RfAE, D-StS, D-BfS, D-RtAE, D-FS, Disposition) CMRspecs (ProiectlD. CMR#. SpecsID, Specs Section) CMRitem (ProjectID, CMR#. SpecsID, W B S I D ) CMRtrade (ProiectlD. CMR#, SpecsID, WBS_ID, TradelD, Trade Name) CMRsub (ProjectID. CMR#. SpecsID, W B S I D , TradelD, SublD, Sub Name, Sub Contact, Sub Phone)  The local data model or normalized relations for the above view are as follows: Project (ProjectID. Project Name, E n g i n e e r © (=Co_ID), OCBP, OCCD) Change Order (ProjectID. CMR#. SpecsID, Initiation Date, Current Status, Description, EsitmatValue, EngrEstimate, Final Value, Time Impact, D RfAE, D-StS , D-BfS, D-RtAE, D-FS, Disposition) Specs Section (SpecsID. Specs Section, W B S I D ) WBS (WBS ID. Item Description, TradelD) Trade (TradelD. Trade Name) Project TradeDetails (ProjectID. TradelD. SublD (=Co_ID)) Company  (Co_ID.  Co_Name,  TypelD,  (=PeopleID), Phone) People (PeoplelD. Name, C o J D ) Participant Type (TypelD. Participant Type)  Contact 1  (=PeopleID),  Contact2  181  The system control derived from the semantics required by the users is shown in Fig. 4.38.  I  People  Project TradeDetails —  A-  Company  ix:  Project  Participant Type  Change Order  Trade  WBS  _UJ Change Order Log  Figure 4.38  4.5.4  Specs Section  Structural Model Diagram for Change Orders Tracking Log  View 4: Correspondence Tracking Log  The initial relations derived from the entity-relationship model, as shown in section 4.4.4, are as follows: Project (ProjectlD. Project Name, Address) Correspondence (ProjectlD. Cprr#, Referenced CorrDate, Date Sent, DateRcvd, Follow up, Keyword 1, Keyword2, Notes) From/toPeople (ProjectlD. Corr#. From People (=ID), To People (=ID))  182  From/toCompany (ProjectlD. Corr#. F r o m Company (=TD), T o Company (=ID)) From/toType (ProjectlD. Corr#. F r o m Type (=ID), T o Type (=)D))  The local data model or normalized relations for the above view are as follows: Project (ProjectlD. Project Name) Correspondence  (ProjectlD. Corr#. F r o m People  (=PeopleTD),  To  People  (=PeopleID), Reference.^, CorrDate, Date Sent, Date R c v d , K e y w o r d One, K e y w o r d T w o , Follow up, Notes) People (PeoplelD. Name, C o _ I D , Phone) Company  (Co TP.  Co_Name,  TypelD,  Contact l(=PeopleTD),  Contact2  (=PeopleID), Phone) Participant Type (TypelD. Participant Type)  The system control derived from the semantics required by the users is shown in Fig.  Company  Participant Type  Project  ~rn—  A  People  Correspondence  Correspondence L o g  Figure 4.39  Structural Model Diagram for Correspondence Tracking Log  4.39.  183  4.5.5  V i e w 5: Daily Site Report  The initial relations derived from the entity-relationship model, as shown in section 4.4.5, are as follows: Project (ProjectID. Project Name) ProjectEngr (ProjectID. EngineerlD, Engr Name, Engr Phone) EngrContact (ProjectID. EngineerlD, Engr Contact (=PeoplelD), Contact Name) EngrType (ProjectID. EngineerlD, T y p e l D , Participant Type) ReportDay (ProjectID. Date. D a y I D , Day) Weather (ProjectID. Date. Temperature, Sunny (yes/no), Cloudy (yes/no), Rainy (yes/no), Snowy (yes/no), Still W i n d (yes/no), Moderate W i n d (yes/no), H i g h Wind (yes/no), Humid-dry (yes/no), Humid-mod (yes/no), Humidhigh (yes/no)) Daily Events (ProjectID. Date. Daily Report#, V i s i t o r l , Visitor2, V i s i t T i m e l , VisitTime2,  C o m p a n y l , Company2, Purposel,  Purpose2,  EquipUse,  Equipldle, Activities, N e w Act, A c t Compl, Material Needs) DailySub (ProjectID. Date. F F C 1 , F F C 2 , F F C 3 , F F C 4 , N M F F 1 , N M F F 2 , N M F F 3 , N M F F 4 , M F F 1 , M F F 2 , M F F 3 , M F F 4 , J o b l , Job2, Job3, Job4) DailyReporter (ProjectID. Date. Prepared by I D , Prepared by)  The local data model or normalized relations for the above view are as follows: Project (ProjectID. Project Name, EngineerlD (=Co_ID), O w n e r l D (=Co_ID))  184  Weather  (ProjectlD. Date. D a y I D , Temperature,  Sunny (yes/no),  Cloudy  (yes/no), Rainy (yes/no), Snowy (yes/no), Still W i n d (yes/no), Moderate W i n d (yes/no), H i g h W i n d (yes/no), Humid-dry (yes/no), Humid-mod (yes/no), Humid-high (yes/no)) Day (Day I D . D a y Name) Daily Events (ProjectlD. Date. D a y I D , Daily Report#, Prepared by I D , F F C 1 , FFC2, FFC3, FFC4), N M F F 1 , N M F F 2 , N M F F 3 , N M F F 4 , M F F 1 , M F F 2 , M F F 3 , M F F 4 , J o b l , Job2, Job3, Job4, V i s i t o r l , Visitor2, V i s i t T i m e l , VisitTime2,  C o m p a n y l , Company2, P u r p o s e l ,  Purpose2, EquipUse,  Equipldle, Activities, N e w Act, A c t Compl, Material Needs) People (PeoplelD. Name, C o I D , Phone) Company ( C o I D . C o N a m e , TypelD, Contact 1 (=PeopleID), Phone) Participant Type (TypelD. Participant Type)  The system control derived from the semantics required by the users is shown in Fig. 4.40.  People  I  r  Daily Events  _A_  Daily Site Report  Company  Participant Type  Figure 4.40  Tn Daily Weather  N  Structural Model Diagram for Daily Site Report  Day  185  4.5.6  V i e w 6: Defective W o r k Notifications T r a c k i n g log  The initial relations derived from the entity-relationship model, as shown in section  4.4.6,  are as follows: Project (ProjectID. Project Name) ProjectEngr (ProjectID. EngineerlD, Engr Name, Engr Phone) EngrContact (EngineerlD. Engr Contact (=PeopleID)) Defective  Work  (ProjectID.  DWN#.  NoticeDate,  Description,  DateRcvd,  EstimatValue, Date Reinspected, Reinspected B y , D - N o C R , Comments) DWNspecs (ProiectlD. D W N # . SpecsID, Specs Section) D W N i t e m (ProiectlD. D W N # . SpecsID, W B S I D ) D W N t r a d e (ProjectID. D W N # . SpecsID, W B S _ 1 D , TradelD, Trade Name) D W N s u b (ProjectID. DWNtf. SpecsID, W B S I D , TradelD, SublD, Sub Name, Sub Contact, Sub Phone)  The local data model or normalized relations for the above view are as follows: Project (ProjectID. Project Name, EngineerlD (=Co_ID) DefectiveWork (ProjectID. DWNtf. SpecsID, Notice Date, Description, Date Rcvd,  EstimatValue,  Date  Reinspected,  Comments) Specs Section (SpecsID. Specs Section, W B S I D ) W B S ( W B S I D . Item Description, TradelD)  Reinspected  By,  D-NoCR,  186  Trade (TradelD. Trade Name) Project TradeDetails (ProjectlD. TradelD. SubID (=Co_ID)) Company  (CoJTD.  CoName,  TypelD,  Contact 1  (=PeopleID),  Contact2  (=PeopleID), Phone) People (PeoplelD. Name, C o I D ) Participant Type (TypelD. Participant Type)  The system control derived from the semantics required by the users is shown in Fig. 4.41.  People  r  Project TradeDetails sr~  Company  r  Participant Type  V  Project  Defective W o r k LJ  D W N Log  Figure 4.41  Trade  T  WBS  1  Specs Section  Structural Model Diagram for DWN Tracking log  187  4.5.7  V i e w 7: M a t e r i a l s Stored T r a c k i n g L o g  The initial relations derived from the entity-relationship model, as shown in section  4.4.7,  are as follows: Project (ProjectID. Project Name) ProjectEngr (ProjectID. EngineerlD, Engr Name, Engr Phone) EngrContact (EngineerlD. Engr Contact (=PeopleID)) MaterialsStored  (ProjectID. Mat#.  Item Description, Loc-Installed,  Original  Value, Pay Est#, Pay Est Date, % Installed) MatStorage (ProjectID. Mat#, L o c I D , Location Stored) MatSpecs (ProjectID. Mat#, SpecsID, Specs Section) M a t l t e m (ProjectID. Mat#, SpecsID, W B S J D ) MatTrade (ProjectID. Mat#. SpecsID, W B S I D , TradelD, Trade Name) MatSub (ProjectID. Mat#, SpecsID, W B S I D , TradelD, SublD, Supplier Name, Sub Contact, Sub Phone)  The local data model or normalized relations for the above view are as follows: Project (ProjectID. Project Name, EngineerlD (=Co_ID) MaterialsStored (ProjectID. Mat#, SpecsID, L o c _ I D , Item Description, L o c Installed, Original Value, Pay Est#, Pay Est Date, % Installed) Storage Location ( L o c I D . Location name, Address) Specs Section (SpecsID. Specs Section, W B S J L D )  188  WBS (WBS ID. Item Description, TradelD) Trade (TradelD. Trade Name) Project TradeDetails (ProjectlD. TradelD. SubID (=Co_K>)) Company  (Co ID.  CoName,  TypelD,  Contactl  (=PeopleID),  Contact2  (=PeopleID), Phone) People (PeoplelD. Name, Co_ID) Participant Type (TypelD. Participant Type)  The system control derived from the semantics required by the users is shown in Fig. 4.42.  Project TradeDetails  People  —V -ed  Company  I  Participant Type  Project  Materials Stored  —V Storage Location  Figure 4.42  WBS  Trade  Specs Section  UJ Materials Stored Log  Structural Model Diagram for Materials Stored Tracking Log  189  4.5.8  View 8: Monthly Progress Report  The initial relations derived from the entity-relationship model, as shown in section 4.4.8, are as follows: Project (ProjectlD. Project Name) ProjectEngr (ProjectlD. EngineerlD, Engr Name, Engr Phone) EngrContact (EngineerlD. Engr Contact (=PeopleID)) Monthly Progress (ProjectlD. Pay Est#. PayEst Date, This Qntty, To-date Qntty) Progressltem (ProjectlD. Pay Est#. W B S I D , Item Description, Quantity, Unit, Unit Price) ProgressTrade (PjcjectJD, Pav Est#. WBSJD, TradelD, Trade Name) ProgressSub (ProjectlD, Pav Est#. W B S I D , TradelD, SubID, Sub Name, Sub Contact, Sub Phone)  The local data model or normalized relations for the above view are as follows: Project (ProjectlD. Project Name, EngineerlD (=Co_ID) Monthly Progress (ProjectlD. Pay Est#. WBS_ID, PayEst Date, This Qntty, Todate Qntty) Project ItemDetails (ProjectlD. WBS ID. Quantity, Unit Price) WBS (WBS ID. Item Description, Unit, TradelD) Trade (TradelD. Trade Name) Project TradeDetails (ProiectLD. TradelD. SubID (=Co_ID))  190  Company  (CoID.  CoJSTame,  TypelD,  Contact 1  (=PeopleID),  Contact2  (=PeopleID), Phone) People fPeoplelD. Name, C o I D ) Participant Type (TypelD. Participant Type)  The system control derived from the semantics required by the users is shown in Fig. 4.43.  I  People  A.  Company  Project TradeDetails  ^  Trade  Project  —v— Participant Type  Project ItemDetails b >r<  A WBS  UJ  Monthly Progress Report  Figure 4.43  4.5.9  Structural Model Diagram for Monthly Progress Report  View 9: Photographs Tracking Log  The initial relations derived from the entity-relationship model, as shown in section 4.4.9, are as follows: Project (ProjectID. Project Name)  191  ProjectEngr (ProjectID. EngineerlD, Engr Name, Engr Phone) EngrContact (EngineerlD. Engr Contact (=PeopleID)) Photographs (ProjectID. Photo#. Roll#, Negative#, Date Taken, Time of day, Camera  direction,  Photogr  position,  Facility/Location,  Keyword 1,  Keyword2, Caption) Photographer (ProjectID. Photo#. Photogr ID (=PeopleID), Photogr Name) PhotoType (ProjectID. Photo#. Photo TypelD, Photo Type)  The local data model or normalized relations for the above view are as follows: Project (ProjectID. Project Name, EngineerlD (=Co_ID)) Photograph (ProiectlD. Photo#, Photo TypelD, Photgr ID (=PeopleID), Roll#, Negatived, Date Taken, Time of day, Camera direction, Photogr position, Facility/Location, Keyword 1, Keyword2, Caption) Company  (CoID.  CoJSTame,  TypelD,  (=PeopleID), Phone) People (PeoplelD. Name, C o I D ) Participant Type (TypelD. Participant Type) Photo Type (Photo TypelD. Photo Type)  Contact 1  (=PeopleID),  Contact2  192  The system control derived from the semantics required by the users is shown in Fig. 4.44.  Photograph Log  People  I  Ac-  company  Participant Type  Figure 4.44  Project  Photo Type  «H" Photograph  Structural Model Diagram for Photographs Tracking Log  4.5.10 View 10: Punch Lists  The initial relations derived from the entity-relationship model, as shown in section 4.4.10, are as follows: Project (ProjectlD, Project Name) ProjectEngr (ProjectlD. EngineerlD, Engr Name, Engr Phone) EngrContact (EngineerlD. Engr Contact (=PeopleID)) Punch Lists (ProjectlD. PL#. Punch List Item, Facility/Area, Date Identified, Identified By, Date Completed, Date Checked, Checked By) PLspecs (ProjectlD. PL#, SpecsID, Specs Section) PLitem (ProjectlD. PL#. SpecsID, W B S I D )  193  PLtrade (ProjectID. PL#. SpecsID, WBS_ID, TradelD, Trade Name) PLsub (ProjectID. PL#. SpecsID, WBS_ID, TradelD, SubTD, Sub Name, Sub Contact, Sub Phone)  The local data model or normalized relations for the above view are as follows: Project (ProjectID. Project Name, EngineerlD (=Co_ID) Punch Lists (ProjectID, PL#. SpecsID, PunchList Items, Facility/Area, Date Identified, Identified By, Date Completed, Date Checked, Checked By) Specs Section (SpecsID. Specs Section, WBSJLD) WBS (WBS ID. Item Description, TradelD) Trade (TradelD. Trade Name) Project TradeDetails (ProiectlD. TradelD. SublD (=Co_ID)) Company  (Co ID.  CoJSfame,  TypelD,  (=PeopleID), Phone) People (PeoplelD. Name, C o I D ) Participant Type (TypelD. Participant Type)  Contactl  (=PeopleID),  Contact2  194  The system control derived from the semantics required by the users is shown in Fig. 4.45.  People  Project TradeDetails  IX  —sr~  Company  Project  T  Participant Type  Punch Lists  Punch Lists Log  Figure 4.45  Trade  A_  WBS  I Specs Section  Structural Model Diagram for Punch Lists  4.5.11 View 11: Requests for Information (RFT) Tracking Log  The initial relations derived from the entity-relationship model, as shown in section 4.4.11, are as follows: Project (ProjectlD. Project Name) ProjectEngr (ProjectlD. EngineerlD, Engr Name, Engr Phone) EngrContact (EngineerlD. Engr Contact (=PeoplefD)) RFI (ProjectlD. RFI#, Initiation Date, Drawing#, RFI Description, D-RfS, D StAE, D-RBfAE, D-SBtS) RFIpeople (ProjectlD. RFI#, Initiated By (=PeopleID), Name)  195  RFIcompany (ProiectlD. KFI#. Initiated B y (=PeopleID), Initiator I D (=Co_ID)) RFIcompanyType (ProjectID. RFI#. Initiated B y (=Co_ID), Initiator Type) RFIresponse (ProjectID. RFI#. Response by (=PeopleID))  The local data model or normalized relations for the above view are as follows: Project (ProjectID. Project Name, EngineerlD (=Co_ID) RFI  (ProiectlD. RFI#.  Initiated  By(=PeopleID), Response  by(=PeopleID),  Initiation Date, Drawing#, R F I Description, D - R f S , D - S t A E , D - R B f A E , D-SBtS) Company  (CoID.  Co_Name,  TypelD,  Contact 1  (=PeopleID),  Contact2  (=PeopleID), Phone) People (PeoplelD. Name, C o _ I D ) Participant Type (TypelD. Participant Type)  The system control derived from the semantics required by the users is shown in Fig. 4.46.  - ^ R e q u e s t s for Information (RFI)  People  n  Company  ^  Participant Type  Figure 4.46  Projects  RFI Log  Structural Model Diagram for RFI Tracking Log  196  4.5.12 View 12: Shop Drawing Submittals Tracking Log  The initial relations derived from the entity-relationship model, as shown in section 4.4.12, are as follows: Project (ProjectlD. Project Name) ProjectEngr (ProjectlD. EngineerlD, Engr Name, Engr Phone) EngrContact (EngineerlD. Engr Contact (=PeopleID)) Shop Drawing (ProjectlD. Shop Drwg#. Description, Disposition, D-RfS, D StAE-I, D-StAE-R, D-StAE-F, D-RBfAE, D-SBtS, C-RfS, C-StAE, C RBfAE, C-SBtS, Comments) SDitem (ProjectlD. Shop Drwg#. WBS_ID, Shop Drawing (yes/no)) SDtrade (ProiectlD. Shop Drwg#. W B S I D , TradelD, Trade Name) SDsub (ProjectlD. Shop Drwg#. W B S I D , TradelD, SubID, Sub Name, Sub Contact, Sub Phone)  The local data model or normalized relations for the above view are as follows: Project (ProjectlD. Project Name, EngineerlD (=Co_ID) Shop Drawing (ProjectlD. Shop Drwg#. W B S I D , Description, Disposition, D RfS, D-StAE-I, D-StAE-R, D-StAE-F, D-RBfAE, D-SBtS, C-RfS, C StAE, C-RBfAE, C-SBtS, Comments) WBS (WBS ID. Item Description, TradelD)  197  Trade (TradelD. Trade Name) Project ItemDetails (ProjectlD. WBS ID. Shop Drawing (yes/no)) Project TradeDetails (ProjectlD. TradelD. SublD (=Co_ID)) Company  (CoID.  Co_Name,  TypelD,  Contact 1  (=PeopleID),  Contact2  (=PeopleID), Phone) People (PeoplelD. Name, C o I D ) Participant Type (TypelD. Participant Type)  The system control derived from the semantics required by the users is shown in Fig. 4.47.  Project TradeDetails  V~ ^  Project  Trade  Shop Drawing  Hi.  Shop Drawing Log  WBS  Figure 4.47 Structural Model Diagram for Shop Drawing Submittals Tracking Log  198  4.5.13 View 13: Spare Parts Tracking Log  The initial relations derived from the entity-relationship model, as shown in section 4.4.13, are as follows: Project (ProjectlD. Project Name) ProjectEngr (ProjectlD. EngineerlD, Engr Name, Engr Phone) EngrContact (EngineerlD. Engr Contact (=PeopleID)) Spare Parts (ProjectlD. SP#, SP Description, E q Description, Qntty Required, Qntty D l v d , Unit) SPstorage (ProjectlD. SP#, L o c _ I D , Location Stored) SPspecs (ProiectlD. SP#, SpecsID, Specs Section) SPitem (ProiectlD. SP#, SpecsID, W B S J D ) SPtrade (ProiectlD. SP#, SpecsID, W B S _ I D , TradelD, Trade Name) SPsub (ProjectlD. S P J , SpecsID, W B S _ I D , TradelD, SubID, Supplier Name, Sub Contact, Sub Phone)  The local data model or normalized relations for the above view are as follows: Project (ProjectlD. Project Name, EngineerlD (=Co_ID) Spare Parts (ProjectlD. SP#, SpecsID, L o c I D , SP Description, E q Description, Qntty Required, Qntty D l v d , Unit, Status) Specs Section (SpecsID. Specs Section, W B S I D ) W B S ( W B S I D . Item Description, TradelD)  199  Trade (TradelD. Trade Name) Project TradeDetails (ProjectID. TradelD. SublD (=Co_ID))  (CoJLD. Co_Name, TypelD,  Company  Contact 1  (=PeopleID)  ]  Contact2  (=PeopleID), Phone) People (PeoplelD. Name, C o I D ) Participant Type (TypelD. Participant Type) Storage Location (Loc ID. Location name, Address)  The system control derived from the semantics required by the users is shown in Fig. 4.48.  Project TradeDetails  People  —v  XT A_ Company  -<d  I  Project  Trade  WBS  _A_  Participant Type  Spare Parts  ^_  Specs Section  V Storage Location  Figure 4.48  Spare Parts Log  Structural Model Diagram for Spare Parts Tracking Log  200  4.5.14 Integrated Structural Model  The integrated structural model for all the above thirteen views, combined and simplified, is shown in Figure 4.49.  WBS  Specs Section Storage Location  Bill of Quantities  Tn  A_  A_  -A Spare Parts  Materials Stored  —5K—  .A.  DefectiveWork  -A-  Project ItemDetails  .A  ShopDrawing  PunchLists  A_  —  Monthly Progress  Change Order  PROJECT  v— Daily Weather  Correspondence  I  v— Photograph  —V  Project TradeDetails  v  v  RFI  V  People \ Photo Type  Figure 4.49  Participant Type  Integrated Structural Model Diagram  201  4.6  Entity and Relationship Attributes  This section deals with the translation of the conceptual model to a logical model. Earlier in Chapter 1, section 1.6.4 explains the data modeling stages and the data model environment of the commercial database management systems (DBMS). Although the D B M S is not a factor in designing a conceptual model, but designing a logical model is dependent on the D B M S to be used. Considering a D B M S based on a relational data model, such as Microsoft Access, this section will help in the translation of a conceptual model to a logical relational model.  This section lists the attributes associated with each entity and relationship as illustrated in Figures 4.20 to 4.32., which in fact is a global data model of the system. These relations are the results of normalization procedures carried out in section 4.5. A n attribute, or a number of attributes from these relations will represent the primary key(s). The primary keys for a relation are underlined. Additional attributes have been added to some of the entities and relationships (Company, People, Project, Specs  Section and Project  ItemDetails) to fulfill the data information requirements, as discussed in section 4.3. These new attributes are unaffected by all the normalization stages. That is, they are not a repeating group (INF), attributes of entity with more than one identifying attribute (2NF) or functionally dependent on the non-identifying attributes of an entity. They are in their third normal form (3NF).  202  As discussed in section 1.6.6 (Relational Database Terminology), the relational logical model uses two-dimensional tables to present the database. The entities and relationships, as presented in sections 4.6.1 and 4.6.2, will be termed as tables in section 4.7 onwards. When these relations will be mapped into physical models in a real D B M S environment, the tables will have the same title as that of the relations presented in this section. The attributes of these relations will be termed as fields.  The local data models of sections 4.4.4 to 4.4.13 have been combined and consolidated to arrive at the following global data model. For convenience sake, each of these tables is still presented in the relations form instead of a tabular form.  4.6.1  Entities  Bids nProjectlD. TradelD. BidderlD (=Co_ID), Bid Pkg. sent, Bid Received, Bid Amount, Remarks)  Change Order ((ProjectID. CMR#. SpecsID, Initiation Date, Current Status, Description, EsitmatValue, EngrEstimate, Final Value, Time Impact, D RfAE, D-StS, D-BfS, D-RtAE, D-FS, Disposition)  203  Company  (Co ID.  CoJSfame,  TypelD,  Contact 1  (=PeopleID),  Contact2  (=PeopleID), Address, City, Province, Postal Code, Phone, Fax, Size)  Correspondence  (ProjectID.  Corr#. From People  (=PeopleID),  To People  (=PeopleID), Referenced CorrDate, Date Sent, Date Rcvd, Keyword 1, Keyword2, Follow up, Notes)  Daily Events (ProiectlD. Date. Day ID, Daily Report#, Prepared by ID, FFC1, FFC2, FFC3, FFC4), NMFF1, NMFF2, NMFF3, NMFF4, MFF1, MFF2, MFF3, MFF4, Jobl, Job2, Job3, Job4, Visitorl, Visitor2, VisitTimel, VisitTime2,  Companyl, Company2, Purposel,  Purpose2, EquipUse,  Equipldle, Activities, New Act, Act Compl, Material Needs)  Daily Weather (ProjectID. Date. Day ID, Temperature, Sunny (yes/no), Cloudy (yes/no), Rainy (yes/no), Snowy (yes/no), Still Wind (yes/no), Moderate Wind (yes/no), High Wind (yes/no), Humid-dry (yes/no), Humid-mod (yes/no), Humid-high (yes/no)) Day (Day ID. Day Name)  DefectiveWork (ProjectID. DWN#, SpecsID, Notice Date, Description, Date Rcvd,  EsitmatValue, Date  Comments)  Reinspected,  Reinspected  By,  D-NoCR,  204  Materials Stored (ProjectlD. Mat#. SpecsID, L o c I D , Item Description, L o c Installed, Original Value, Pay Est#, Pay Est Date, % Installed)  Monthly Progress (ProjectlD. Pav Est#. W B S I D , PayEst Date, This Qntty, T o date Qntty)  Participant Type (TypelD. Participant Type)  People (PeoplelD. Last Name, First Name, C o _ I D , Title, Address, City, Province, Postal Code, Phone, Fax)  Photograph (ProjectlD. Photo#. Photo T y p e l D , Photgr I D (=PeopleID), Roll#, Negative#, Date Taken, Time o f Day, Camera Direction, Photogr Position, Facility/GLocation, Keyword 1, Keyword2, Caption)  Photo Type (Photo TypelD. Photo Type)  Project (ProjectlD. Project Name, Project Type, EngineerlD (=Co_ID), O w n e r l D (=Co_K>), ManagerlD (=PeopleID), O C B P , O C C D , StartDate, EndDate, Address, Phone, Fax)  205  Punch Lists (ProjectlD. PL#. SpecsID, PunchList Items, Facility/Area, Date Identified, Identified By, Date Completed, Date Checked, Checked By)  RFI (ProjectlD. RFI#, Initiated By (=PeopleID), Response by (=PeopleID), Initiation Date, Drawing#, RFI Description, D-RfS, D-StAE, D-RBfAE, D-SBtS, Comments)  Shop Drawing (ProjectlD. Shop Drwg#. W B S I D , Description, Disposition, D RfS, D-StAE-I, D-StAE-R, D-StAE-F, D-RBfAE, D-SBtS, C-RfS, C StAE, C-RBfAE, C-SBtS, Comments)  Spare Parts (ProjectlD. SP#. SpecsID, Loc_ID, SP Description, E q Description, Qntty Required, Qntty Dlvd, Unit, Status)  Specs Section (SpecsID.  WBSID,  Specs Section,  Division Title)  Storage Location ( L o c I D . Location Name, Address)  Trade (TradelD. Trade Name, Description)  WBS (WBS ID. Item Description, TradelD, Unit)  Section Title, Division,  206  4.6.2  Relationships  The common relationships, in third normal form, from sections 4.4.1 to 4.4.13, are the following: Project ItemDetails (PjrojectiD, W B S I D . Quantity, Unit Price, % Markup, Delivery/Action Date, Delivery Days, Approval Days,  ShopDrawing  (Yes/No), MatSample (Yes/No), Catalog (Yes/No), M i s c . (Yes/No), M i s c Submittals, As-Built Drawing (Yes/No))  Project TradeDetails (ProjectlD. TradelD. Budget, StartDate, EndDate, B i d D u e Date, S u b l D ( C o I D ) , Subcontract Amount) Proi-Companv(ProjecfID. C o I D ) Proj-Trade (ProjectlD. TradelD)  4.6.3  Entity Cross-Reference Matrix  A s a preliminary to preparing the integrated structural data model in the preceding section, as shown in Figure 4.49, it is useful to record the entities and their relationships on an entity cross-reference matrix. This is a table in which each entity w i l l appear as a rowheading and each basic entity, as described in section 4.3, will appear as a column-heading. A t the intersection of the row and the column, any of the letters O, R , or S (corresponding  207  to ownership, reference, or subset) will be placed i f there is a structural relationship between the entity at the head o f the column and the entity at the head o f the row.  The cross-reference matrix is shown in Table 4.15. The column-headings show E l to E 1 0 , as an abbreviation for each entity. The abbreviated headings are as follows: E l is for company, E 2 for Day, E 3 for participant type, E 4 for people, E 5 for photo type, E 6 for project, E 7 for specs section, E 8 for storage location, E 9 for trade, and E 1 0 for W B S .  Table 4.15  Entity Cross-Reference Matrix  Bids Change Order Company Correspondence Daily Events Daily Weather  El R  E2  E3  R R  R R  E4  E5  R R R  E8  E9 R  E10  R  O  0 O  0 0  Monthly Progress  Photo Type Project Punch Lists RFI Shop Drawing  E7  0  Day DefectiveWork Materials Stored Participant Type People Photograph  E6 O O  R R  R R  R R R  R  0  R R  O O  0 0  Spare Parts Specs Section Storage Location  R R R  R R  Trade R  WBS Project ItemDetails Project TradeDetails  R  0 0  R R  208  4.7  Data Dictionary  A data dictionary is defined as a set of system tables that contain the data definitions of database objects [Jennings 93]. Much of the information concerning the data is specified by means of a data dictionary. It is an important feature of D B M S and acts as a general reference tool describing all the data held on the database.  Appendix C contains the data dictionary of the proposed system. It provides information about each attribute (field) as described in sections 4.6.1 and 4.6.2. Typically, each entry in the data dictionary includes the following:  4.8  •  the name of the field,  •  the data type,  •  the length or width of the field,  •  indexing requirements, and  •  brief description of the field.  Data Volume and Usage  As discussed in section 1.6.6, the number of tuples (rows) in a table is a measure of cardinality of a relation. This section outlines the assumptions employed to evaluate cardinality of entities, and evaluates the maximum cardinality and the expected entity sizes.  209  4.8.1  Assumptions  Based on the requirements analysis, the cardinalities of entities in the preceding section will be governed by the following assumptions:  •  duration of a project will be one to two years.  •  records will be kept for two years.  •  there will be approximately 3 projects per year.  •  there will be approximately 10 companies involved per project.  •  there will be approximately 10 trades per project.  •  there will be approximately 20 employees per project.  •  there will be approximately 100 Bill of Items per project.  •  there will be approximately 10x3 =30 bidders per project.  •  there will be approximately 100 change orders per project.  •  there will be approximately 150 correspondence per month per project.  •  there will be approximately one daily report per day per project.  •  there will be approximately 100 defective work notifications per project.  •  there will be approximately 50 materials stored items per project.  •  there will be approximately two storage locations per project.  •  there will be approximately one monthly progress report per month per project.  •  there will be approximately 30 photographs per month per project.  210  4.8.2  •  there will be approximately 100 punch list items per project.  •  there will be approximately 100 requests for information (RFI) per project.  •  there will be approximately 30 shop drawings per project.  •  there will be approximately 50 spare part items per project.  Entity Cardinality  Table 4.16 shows the entities and their maximum cardinalities.  Table 4.16  Entity Cardinality  Entity  Max. Cardinality  Projects  6  Companies  60  Employees  120  Specs Sections  250  Project Trade  60  Project Items  600  Bidders  180  Change Orders  600  Correspondence  10,800  Daily Reports  2,190  Defective W o r k Notices  600  Materials Stored  300  Storage Locations  12  Monthly Project Reports  72  Photographs  2,160  Punch Lists  600  Requests for Information  600  Shop Drawings  180  Spare Parts  300  211  4.8.3  Expected E n t i t y Size  Table 4.17 evaluates the expected entity size in terms of bytes. Record size is the sum of field length of all thefieldsin afile,as recorded in the data dictionary in Appendix-C. As shown in the total column, the total storage requirement is approximately one megabyte.  Table 4.17 No.  1 2 3 4 5 6 7 8 9 10 11 12 13  Expected E n t i t y Sizes File Name  Frequency  Bill of Quantities Monthly Bid Summary Sheet 1/project Change Orders Log 100/project Correspondence Log 5/day/project Daily Site Report 1/day/project DWN Log 100/project Materials Stored Log Monthly Monthly Progress Monthly Photograph Log 3 0/month/proj ect Punch List 100/project RFI Log 100/project 30/prqject Shop Drawing Log Spare Parts Log 50/project  N o . of  R e c o r d Size  T o t a l Size  Records  (bytes)  (bytes)  72 180 600 10,800 2,190 600 72 72 2,160 600 600 180 300  150 360 510 450 1,100 520 500 300 400 500 500 540 670 Total  10,800 64,800 306,000 4,860,000 2,409,000 312,000 36,000 21,600 864,000 300,000 300,000 97,200 201,000 9,782,400  212  4.9  Suggestions for Implementation Environment  This section describes suggestions and recommendations for implementation environment of the proposed system.  4.9.1  System Hardware and Software Options  Technology is changing, and there is always a tendency to want what is perceived as the newest or the best. But the criteria for evaluating project management system hardware and software should be based on what is important for the company [Tidwell 92]. So, the selection of hardware is based on the following needs of the company: speed of a personal computer (PC) for processing and updating data, size of the hard disk for storage of data, R A M (random access memory) for file sizes and new software requirements (database, spreadsheets, and graphics capabilities), expandability of computer system, type of printer for faster printouts, and costs. Anticipating that other project management applications will be used on the same PC, it is suggested that the PC be an IBM-compatible with an 80386 or higher processor (486/66 is recommended); 2 M B (megabyte) of R A M (4 M B or more is recommended); 240 M B or higher hard drive; E G A or V G A monitor ( V G A is recommended); compatible mouse; and a laser printer (2 M B memory or higher). The PC system will have MS-DOS® version 3.1 or later, and Microsoft Windows™ version 3.0 or later.  213  It is further recommended that a commercial database management software package, such as Microsoft Access, be used because this application does not require programming. Microsoft access is designed around database objects that enable to do most of the work without programming. However, it also allows programming to create custom functions.  4.9.2 Sources of Data Input  The Microsoft Access macro will be used to automate the data editing and viewing operations, as shown in Figure 4.50, as well as to aid the process of database maintenance. As per the company's requirements, the data input to the system will be through the following documents:  •  Project and storage location data will be obtained from existing company project description reports.  •  Employee and company data will be obtained from existing company address book.  •  Specs Section data will be obtained from any literature outlining CSI (Construction Specifications Institute) Masterformat, such as [O'Brien 90].  •  Bill of Quantity (BoQ) data will be obtained from the project's work item details (estimated or extracted from contract drawings) and WBS (work  214  breakdown structure). WBS will be based on either CSI Masterformat or A S P E (American Society of Professional Estimators) format. Bid data will be obtained from the bid documents as submitted by bidders. Change order and defective work notification data will be obtained from the respective documents and the project log book. Correspondence data will be obtained from company correspondence log books. Daily site report data will be obtained from daily events and weather reports from job sites. Monthly progress report and materials stored data will be obtained from BoQ and progress measurement reportsfromjob sites. Photograph data will be obtained from photo log ofjob sites. Punch  list  data  will  be  obtained  from  punch  lists  forwarded  by  architects/engineers (A/E) Requests for Information (RFI) data will be obtained from the RFI log of job sites and home office. Shop drawing data will be obtained from project's specification, schedule, and shop drawing submittals log from job sites. Spare parts data will be obtained from project's specifications and contracts, and spare part recordsfromjob sites.  215  4.9.3  System Data Output  Figure 4.50 illustrates a data output organization. The system output will be on screen (forms) as well as on printout reports. Data output on screen will be form-oriented and the user interface will employ some o f the gadgets o f the window operating system like fields, menus, buttons, dropdown menus, scroll bars, etc.  A s required by the company, the following forms and reports w i l l be created:  •  Bill-of-Quantities,  •  B i d Summary Sheets,  •  Change Orders Tracking L o g ,  •  Correspondence Tracking L o g ,  •  Daily Site Reports,  •  Defective W o r k Notifications Tracking L o g ,  •  Materials Stored Tracking L o g ,  •  Monthly Progress Reports,  •  Photographs Tracking L o g ,  •  Punch Lists,  •  Requests for Information Tracking L o g ,  •  Shop Drawing Submittals Tracking L o g , and  •  Spare Parts Tracking L o g .  r  SYSTEM DATA ORGANIZATION  Data Input  Collect Input Data ,  Data Output  Edit Database  Output to Screen  Output to Printer  Printouts  r View Table  Figure 4.50  >  View Query  View Form  View Report  System Data Organization Diagram  217  4.10 Physical Implementation  This section describes the physical development of the system. Section 4.10.1 presents the Microsoft Access terminology, and section 4.10.2 presents the prototype development.  4.10.1 Microsoft Access Terminology  Some of the key terms, as used in Microsoft Access, are defined as follows:  •  Microsoft Access: As mentioned earlier, Microsoft Access is a relational  database management system (RDBMS) for Microsoft Windows™.  •  Table: A table is a Microsoft Access object that stores data in rows (records)  and columns (fields). The data is usually about a particular subject such as project or people. All data in a table describe the subject of the table. The entities and relationships, as listed in section 4.6, will be termed as tables.  •  Query: A query is a Microsoft Access object that asks a question or defines a  set of criteria about data from tables. The data that answers the questions can be from one table or from more than one table. A query brings requested questions together. A number of selected tables, the tables which contain the desired data,  218  are added to the Query window. The Query window is a graphical query-byexample (QBE) tool. The tables in a query are joined by a line that connects one of the fields of a table. The join  line tells Microsoft Access how the data in the tables  are related. A Q B E grid is used to accomplish a variety of tasks in a database, such as the following:  •  •  Combining and displaying records from related tables.  •  Producing columns of calculated data from existing fields in a table.  •  Retrieving subsets of records that match specific selection criteria.  •  Updating fields of data according to updated expressions.  Form: A form is a Microsoft Access object that is used to enter, change and  view records of data. It is used to display data, from one or more tables and/or queries, on the screen or in print using a custom layout. A well-designed form provides a familiar, consistent, and reliable visual tool for performing a variety of database tasks, such as the following:  •  Viewing data one record at a time.  •  Viewing existing records in a table.  •  Entering new records into the table, often in a format designed to resemble a familiar paper form.  •  Printing individual records as forms.  219  •  Report: A report is a Microsoft Access object that is used to print records in a  custom layout. A report can be used to group records and show totals for groups and grand totals for the entire report. A report is the ultimate presentation from a database. A report often provides summaries of data, listings of records, and information gathered together in meaningful groups and subgroups.  •  Macro: A macro automatically carries out a task for a user. Each task that a  Microsoft Access is asked to perform is called an action. Microsoft Access provides a list of actions to select from to create a macro. When a macro is run, Microsoft Access carries out actions in the sequence they are listed, using the specified objects or data. Macros can automate routine or repetitive tasks, such as entering, viewing and printing data.  •  Field: A field is a column of data contained in a table. The attributes of the  entities and relationships, as described in section 4.6, will become fields of a particular table.  •  Type: The term 'type' used in tables of Appendix-D denotes the degree of  relationships between the tables. For example, one-to-one (1-1), one-to-many (1M), etc.  220 •  Enforce: The term 'enforce' used in tables of Appendix-D shows the  referential integrity requirements. This column shows 'yes' if referential integrity is required. Referential integrity are the rules that govern the relationships between primary keys and foreign keys of a tables within a relational database and determine data consistency. Referential integrity requires that the values of every foreign key in every table be matched by the value of a primary key in another table. The rules help ensure that a data is related in a valid way, and that the data is not accidentally deleted.  •  Default Relationships: Because Microsoft Access is a relational database, data  can be used from more than one table at a time. If a database contains tables with related data, the data can be related in queries, forms and reports. It is always useful to create a default relationship between the related data of two tables to allow the DBMS to automatically associate data from different tables.  4.10.2 Prototype Development  This section discusses the various steps followed to produce a prototype of the system. The steps followed were in the following sequence: developing tables, setting default relationships, designing queries, creating forms and subforms, producing reports, and creating applications with macros. The following sections present a short description of each of the steps as discussed above.  221  4.10.2.1 Developing Tables  All the twenty five (25) tables, as listed on the left column of Table 4.15, were included in prototype database. Each table was developed by adding all the fields and their data types, as listed in the data dictionary (Appendix-C). Setting of primary key(s) for each table was performed, simultaneously, during the course of individual table development. Roughly, it took ten man-hours to create all the tables.  4.10.2.2 Setting Default Relationships between Tables  As the purpose of the database is to create queries, forms, and reports that successfully retrieve data from more than one table — setting of default relationships between table became a priority over other database actions. Default relationships between tables, as listed in Table D - l (Appendix-D), were set before designing queries, forms and reports. It took approximately four hours to set default relationships.  4.10.2.3 Designing Queries  After considering carefully, a list of thirty one (31) queries was developed for the database. Each query was intended for a particular form, subform or report. Fields containing data required to appear on a form or report were selected, and their tables were  222  identified. A set of tables joined by their relationships, as listed in Table D-2 (AppendixD), were used to create Q B E grids. A slightly different naming convention, than that of a table, was used for each query in order to differentiate them while designing a form or report. Roughly, it took 10 man-hours each for designing and creating all the queries.  4.10.2.4 Creating Forms  Three different types of forms were created for each user view. The first type of forms was intended for data-entry/editing a table, the second type was for viewing information on screen, and the third one was for using as a subform in the design of a form. For the prototype, the following forms were created: twenty five data-entry forms (one for each table), thirteen on-screen view forms (one for each user view), and thirteen subforms (one for each user view). Some of the other forms created are the following: startup, data-entry form  switchboard, data-table  view  switchboard, form  view  switchboard, main  switchboard, and print-report switchboard. All together, fifty seven (57) forms were created and designed. Some of the forms are depicted in Appendix-E. It took approximately forty man-hours to create and design all the forms.  4.10.2.5 Producing Reports  As mentioned earlier, a report is the ultimate presentation for the database. On construction projects, it is often required to distribute a report to various people. In the  223  prototype, the intended reports axe the same as those of the user views as listed in section 4.9.3, and some of the reports as displayed in the Expedition literature [Expedition 95]. Each report provides summaries of data and information as contained in its respective user view. Fourteen (14) reports were created and designed (thirteen for different user views, and one for company listings). Some of the reports are depicted in Appendix-F. It took approximately twenty man-hours to create and design all the reports.  4.10.2.6 Creating Applications with Macros  In order to produce a form-view or report, a number of actions would be required. As the PMICS contains quite a large number of user views, automation of actions was considered a necessity. Macros were used to create a database application, such as opening and closing a form, printing a report, exiting the Microsoft Access window. The following macros (6 nos.) were created for the prototype: AutoExec, DataEntry form switchboard, Form  switchboard,  Main  switchboard,  PrintReport switchboard,  and TableData  switchboard. It took approximately four man-hours to create all the macros.  224  Chapter 5 CONCLUSIONS  5.1  Thesis Review  The first objective of this work was to collect and review literature to determine the use and purpose of different documents on construction projects. The desired result was to identify documents which contain information that are used by construction personnel for solving problems, pursuing claims, providing instructions to site personnel, etc.  The second objective of the work was to develop a data model based on the above findings for a computer-based project-management information control system (PMICS). The expected use of the system was to handle all the problem solving and management information quickly and efficiently.  The first part of the research was based on the common condition of methodologies for designing databases for construction industry, as proposed by [Scarponcini 89], that  information follows function. The functions executed by these by construction personnel dictate the information that these personnel need and provide. The functions must be understood before the information can be identified and efficiently modeled.  225  A matrix approach was adopted to determine the information needs and the documents that contain them. A set of four matrices was developed for this purpose. The first matrix, personnel versus functions (M-l), was developed wherein each component of the functions of construction site personnel was defined. This matrix included all the generic functions of various departments on a construction project, e.g., general management, financial/accounting, engineering, estimating, planning, construction management, project management and site management. The matrix M - l was short listed to produce a matrix, construction personnel versus functions (M-2), containing functions related to project and site management. The matrix M-2 was used as a basis for the development of the third matrix, construction personnel versus information needs (M-3). The fourth matrix, document type versus information content (M-4), was then developed to identify the documents containing information that identified in M-3. Section 4.1.5 lists the documents identified as user views.  A framework was developed for designing the information system. Chapter 3 describes the framework. Based on the information requirements, each of the user view was presented as a relation. A conceptual data model was developed for each relation of the user views. The tools used for data modeling were entity-relationship (E/R) diagram and structural data model (SDM) diagram. A global data model, one each for E / R and S D M , was developed by integrating all the individual models. These models helped in identifying the various entities, including their attributes, and their relationships. A data dictionary was  226  developed in order to define all the attributes, and to use for future references and amendments.  After the project information environment had been modeled and understood, a prototype design was started. The global model was mapped to a relational database model. The entities became tables, and attributes their fields. The global model also helped in establishing default relationships between tables to improve query performance. Indexes were identified to simplify and speed up the queries. The database was implemented on a commercial relational database management systems (RDBMS), called Microsoft Access.  After the database was implemented, interfaces to the users were developed. Like other RDBMS, Microsoft Access allows multiple views on the data. This property was exploited in generating these interfaces. The interfaces consisted of graphical front ends (forms) for the users to manipulate/query data. All the interfaces were given a similar look and feel.  5.2  PMICS  Benefits and Applications of PMICS  is a prototype project-management information control system aimed at  supporting the information control requirements of the project managers. The following benefits can be achieved from the PMICS:  227  •  PMICS can provide the project managers with a project information storage and retrieval database to facilitate the task of job site information control management.  •  PMICS can provide the project managers with an expanded means of problem identification and tracking which includes transaction management.  •  PMICS can generate a wide variety of periodical reports, e.g., daily site report, monthly progress report, outstanding shop-drawing report, etc.  •  PMICS can be used for the purpose of claims analysis, e.g., by calculating time impacts on change order approval, shop-drawing approval, etc.  •  5.3  PMICS is a user friendly and easy to use system.  Experiences and Observations  The development  of PMICS prototype raised several issues and posed problems  concerning the development of a system which would work in the real world. Although only a small number of construction-site documents were considered and only the project management related information was captured, the analysis turned out to be fairly large. It currently consists of thirteen user screen views and an equal number of printout reports. Some of the experiences and observations are given as following:  228  •  A global conceptual data would be very useful for an integrated information system. Two global conceptual models, one each for entity-relationship and structural data model, were developed as depicted in Figures 4.19 and 4.33.  •  Sufficient analysis and design in the early stages is very important for smooth implementation.  •  Sometimes it is difficult to update the design during implementation, pointing to the need for an integrated data dictionary.  •  5.4  Prototyping is very efficient in pointing out deficiencies in the design.  Contributions  The contributions of this research work include the following:  •  Exhaustive literature  search and survey  of  information management  for  construction projects.  •  Development of various matrices, namely, personnel versus functions, personnel versus information needs, and document type versus information contents.  229  •  Development of global data models for documents in the construction industry.  •  Development of methodologies for designing a system that might be applicable to the development of such a system within most organizations.  •  5.5  And last, but not the least, the PMICS itself.  Extensions and Future Research  In terms of future development and research work, the next logical steps would be to redefine and expand the prototype to serve as a template for a more general information system for construction organizations. Other research work may include the following: to redefine the information needs for the whole organization, and to integrate PMICS with the information systems of all the project participants.  More remote extensions include filing of construction photographs linked with PMICS database, and using pen-based computer to enter daily and site report data.  230  BIBLIOGRAPHY Literatures [Aoki et al 93] Aoki, T., Kimura, T., Momozaki, K., Osaka, H . , and Suzuki, A . (1993). Information Integrated Construction QIC). Proc. Computing in Civil Engineering, A S C E . pp. 145-52. [Barnes 93] Barnes, Wilson C. (1993). Microcomputers in Management of Construction Operations. J. of Construction Engineering and Management, June. pp. 403-412. [Bengtsson & Bjornsson 87] Bengtsson, Sten, and Bjornsson, Hans C. (1987). Production Data Capturing. Managing Construction Worldwide, Vol. One, Lansley, Peter R., et al (Eds.), E . & F.N. Spon, London, pp. 426-436. [Bhandari 78] Bhandari, Narinder (1978). Interaction of Information Flow with C M  Systems. J. of the Construction Division, ASCE, September, pp. 261-267. [Bjork et al 93] Bjork, B.C., Huovila, P., and Hutt, S. (1993). Integrated Construction Project Document Management (ICPDM). Advanced Technologies, Elsevier Science Publishers B.V. pp. 135-45. [Bowler 94] Bowler, Charles E . (1994). Database use in the Engineering Office. Proc.  Computing in Civil Engineering, ASCE. pp. 1874-79. [Burger & Halpin 77] Burger, Amadeus M . , and Halpin, Daniel W. (1977). Database Method for Complex Project Control. J. of the Construction Division, September, pp. 453-463. [Carter 87] Carter, D.J. (1987). The Use of Structured Information System In Building Contract Administration Managing Construction Worldwide, Vol. One, Lansley, Peter R., et al (Eds.), E . & F.N. Spon, London, pp. 437-447. [Chamberlain 91] Chamberlain, Elliot A . (1991). Graphics/Database Integration for Engineers. Proc. Computing in Civil Engineering, A S C E . pp. 159-69. [Coker 85] Coker, G. Biilent (1985). Information System For Building Products. J. of  Construction Engineering and Management, December, pp. 411-425.  [Couzen et al 93] Couzen, A . , Thorpe, A., and Skitmore, M . (1993). Executive Information System for Construction Contract Bidding Decisions. Management of Information Technology for Construction, K.S. Mathur et al (Eds.), World Scientific Publishing Co., Singapore, pp. 149-165.  231  [El-Bibany & Froese 89] El-Bibany, Hossam, and Froese, Thomas (1989). Inventory Control System for a Construction Contractor. Unpublished report. [Expedition 95] Expedition Brochure, Version 4.2 (1995). Contract Control Software for Engineering & Construction. Primavera Systems, Inc. Bala Cynwyd, PA., U S A . [Fanous & Samara 94] Fanous, Gamil F., and Samara, Mufid F. (1994). Management Information System Application on a multi-Million Overseas Project. Proc. Computing in  Civil Engineering, ASCE. pp. 2157-2174.  [Fischer et al 94] Fischer, M . , Froese, T., and Phan, D. (1994). How do Integration and Data Models add Value to a Project. Computing in Civil Engineering, pp. 992-997. [Froese 93] Froese, T. (1993). Project Modeling and Data Standards for A E C , Proceedings of the Fifth International Conference (V-ICCCBE). Computing in Civil and.  Building Engineering, Anaheim, Cal, USA, pp. 1730-37.  [Ganeshan et al 94] Ganeshan, R., Kim, S., Liu, L . , and Stumpf, A . (1994). A Multimedia System for Organizing Construction Documents. Proc. Computing in Civil Engineering, A S C E . pp. 1381-88. [Hamilton 91] Hamilton, Dennis O. (1991). Records Management in Engineering Firms. J.  ofManagement in Engineering, October, pp. 346-355. [Hiroshi & Nobuoh 93] Hiroshi, N . , and Nobuoh, H . (1993). Filing of Construction Photos Linked with Database. Proc. Computing in Civil Engineering, A S C E . pp. 718-21. [Kangari 95] Kangari, Roozbeh (1995). Construction Documentation in Arbitration. J. of  Construction Engineering and Management, June. ASCE. pp. 201-208. [Krone 93] Krone, Stephen J. (1993). Containing Construction Change with Computers.  Proc. Computing in Civil Engineering, ASCE. pp. 1762-69.  [Law & Scarponcini 91] Law, K . H . , and Scarponcini, P. (1991). A View Object Approach for Managing Design/Built Information. Proc. Computing in Civil Engineering, A S C E . pp. 192-201. [Leslie & McKay 93] Leslie, H G , and McKay, D G (19930. Towards an Information and Decision-Support System for the Building Industry. Management of Information Technology for Construction, K.S. Mathur et al (Eds.), World Scientific Publishing Co., Singapore, pp. 101-111. [Liu et al 94a] Liu, L . Y . , Stumpf, A . L . , and Kim, S.S. (1994). Applying Multimedia Technology to Project Control. Proc. Computing in Civil Engineering, A S C E . pp. 608613. [Liu et al 94b] Liu, L . Y . , Stumpf, A . L . , Kim, S.S., and Zbinden, F . M . (1994). Capturing As-built Project Information for Facility Management. Proc. Computing in Civil Engineering, A S C E . pp. 614-21.  232  [Maher 78] Maher, Richard P. (1978). Photographic Record and Time Delays. J. of the  Construction Division, ASCE, September, pp. 341-349.  [Mazerolle & Alkass 93] Mazerolle, M . , and Alkass, S. (1993). An Integrated System to Facilitate the Analysis of Construction Claims. Proc. Computing in Civil Engineering, A S C E . pp. 1509-17. [Paulson Jr. 95] Paulson Jr., Boyd C. (1995). Computer and Construction—Midcareer Reflections. J. of Construction Engineering and Management, ASCE, June. pp. 197-200. [Rasdorf & Herbert 88] Rasdorf, W.J., and Herbert, M.J. (1988). CTMS: A Construction Information Management System Proc. Computing in Civil Engineering, pp.3 3-45. [Rasdorf & Abudayyeh 92] Rasdorf, W.J., and Abudayyeh, Osama Y . (1992). N I A M Conceptual Data-base Design In Construction Management J. of Computing In Civil Engineering, A S C E , January, pp. 41-62. [Raymond 95] Raymond, Louis (1987). Information Systems Design For Project Management: A Data Modeling Approach. Project Management Journal. Vol. XVIII, September, pp. 94-99. [Riley & Sabet 94] Riley, Michael J., and Sabet, Hamid H.R. (1994). Building Product Model, A First Brick in Computer Integrated Construction. Proc. Computing in Civil  Engineering, ASCE. pp. 767-777.  [Russell 93] Russell, Alan D. (1993). Computerized Daily Site Reporting. J. of  Construction Engineering andManagement, A S C E , June. pp. 385-401.  [Sadri & Kangari 93] Sadri, S.L., and Kangari, R. (1993). Construction Information Management. Proc. Computing in Civil Engineering, A S C E . pp. 1754-61. [Sanvido 88] Sanvido, V . E . (1988). A Conceptual Construction Process Model. J. of  Construction Engineering and Management, pp. 294-310.  [Sanvido & Paulson 92] Sanvido, V . E . , and Paulson, B . C . (1992). Site-Level Construction Information System. J. of Construction Engineering and Management, December, pp. 701-15. [Tenah 81] Tenah, Kwaku A. (1981). Management Information Organization and Flow in the Construction Organization. Canadian Society of Civil Engineers (CSCE) Conference, at Fredericton, N.B. on May 26 &27, 1981. pp. 633-649. [Tenah 84] Tenah, Kwaku A.(1984). Management Information Organization and Routing.  J. of Construction Engineering and Management, AS CE, March. pl01-118.  [Tenah 86] Tenah, Kwaku A. (1986). Construction Personnel Role and Information Needs. J. of Construction Engineering, March, pp. 33-48.  233  [Tokar 90] Tokar, Michael D. (1990). Utilizing On-Site Computer-Based Information System. Excellence in the Constructed Project, Proceeding of Construction Congress 1990, Canadian Society of Civil Engineers, pp. 272-277. [Vanegas 94] Vanegas, Jorge A. (1994). Strengthening Design/Construction Interface Using Electronic Imaging, Document Management and Work Flow Technologies. Proc.  Computing in Civil Engineering, ASCE. pp. 600-607.  [Vanier et al 93] Vanier, DJ, Mellon, BS, Thomas, R, and Worling, JL. (1993). Management of Construction Information Technology. Management of Information Technology for Construction, K.S. Mathur et al (Eds.), World Scientific Publishing Co., Singapore, pp. 75-84. [Walker & Hughes 87] Walker, A, and Hughes, W.P. (1987). A Project managed by a multidisciplinary practice: a system-based case study. Construction Management &  Economics, pp. 123-140.  BOOKS [Access 92] Microsoft Access User's Guide (1992). Microsoft Corporation, USA. [Atre 80] Atre, S. (1980). Database: Structured and Management. John Wiley & Sons, New York.  Techniques for Design, Performance,  [Bamford & Curran 91] Bamford, Carl, and Curran, Paul (1991). Database Management Systems. Data Structures, Files and Databases, Macmillan Education Ltd., London, U.K. Ch. 9, pp. 155-160. [Barton 85] Barton, Paul (1985). Information Systems - An Overview. Information Systems in Construction Management, edited by Paul Barton, Batsford Academic and Educational, London. [Benyon 90] Benyon, David (1990). Models in the Information Systems. Information and Data Modelling. Blackwell Scientific Publications, Oxford, U.K. Ch. 4, pp. 49-74. [Bull 90] Bull, Malcom (1990). Oxford, UK.  Students' Guide to Databases. Heinemann Newnes,  [Callahan & Bramble 83] Callahan, M . T . , and Bramble, B . B . (1983). Production of Documents. Discovery in Construction Litigation, Ch. 2. The Michie Company, Virginia, pp.17-45.  234  [Collier 94] Collier, Keith (1994). York. pp. 385-409.  Managing Construction. Delmar Publishers Inc., New  [Cleveland & King 83] Cleveland, David I., and King, William, R. (1983). Project Management Handbook. VanNostrand Reinhold Company, New York. Ch. 6 & 13. [Date 90] Date, C.J. (1990). An introduction to Database Systems. Vol. 1, 5th ed. Addison-Wesley Publishing Company, Inc., Reading, M A . Ch 22. [Deatherage 64] Deatherage, G . E . (1964). Contractor Organization and Management. Construction Company Organization and Management, McGraw-Hill Book Company, New York. Chapter 2. [Fisk 93] Fisk, Edward R. (1993). Inc. New Jersey.  Resident Engineer's Field Manual. PTR Prentice-Hall,  [Gilbreath 83] Gilbreath, Robert D. (1983). Contract Reporting.  Contracts. John Wiley & Sons, New York. Ch-19.  Managing Construction  [Goldhaber et al 77] Goldhaber, Stanley, Jha, Chandra K . , and Macedo, Manuel C. Jr. (1977). Project Management Information System (PMIS). Construction Management Principles and Practices, John Wiley & Sons, New York. pp. 67-188. [Jennings & Person 93] Jennings, Roger, and Person, Ron (1993).  Using Access for  Windows. Que Corporation, Carmel, In. [Jones 91] Jones, Fredric H . (1991). Publications, Inc., Los Altos, California. [Levy 94] Levy, Sidney M . (1994). Inc. New York.  A Concise Dictionary of Construction, Crisp  Project Management in Construction. McGraw-Hill,  [Liskin 93] Liskin, Miriam (1993). Help! Microsoft Access. Ziff-Davis Press, CA. [Lock 92] Lock, Dennis (1992). Information Management-1 & Engineering Management, Ch. 20 & 27.  2. Handbook of  [McFadden & Hoffer 88] McFadden, Fred R., and Hoffer, Jeffery A . 1988. Database Management. Ch. 6 to Ch. 9. The Benjamin/Cummings Publishing Company, Inc., California. [Mintzberg 73] Mintzberg, H . (1973). The nature of Managerial Work, Harper and Row, New York.  235  [Murdick & Ross 75] Murdick, R.G., and Ross, J.E. (1975). Information Systems for Modern Management. Prentice-Hall, Inc., New Jersey, pp. 436-465. [O'Brien 90] O'Brien, James J. (1990). Reinhold, New York. Third Edition.  Construction Inspection Handbook. VanNostrand  [Peters 84] Peters, Glen (1984). Construction Project Management Computers, The Architectural Press, London.  Using Small  [Pierce 88] Pierce, David R. (1988). Project Planning & Control for Construction, R.S. Means Company, Inc. Kingston, Ma. [Skitmore 89] Skitmore, R . M . (1989). Scientific and Technical.. [Stewart  & Stewart  86]  Contract Bidding in Construction. Longman  Stewart, Rodney  D., and Stewart,  Ann L . (1986).  Microestimatingfor Civil Engineers. McGraw-Hill Book Company, New York, ch 2 & 6. [Tidwell 92] Tidwell, Mike C. (1992). Microcomputer Application for Projects. McGraw Hill, Inc. New York. [Townsend 92] Townsend, James J. (1992). Carmel, IN U.S.A. Chapters 1-4.  Field Construction  Introduction to Databases. Que Corporation,  236  APPENDIX A  Matrix This appendix contains matrix M - l (personnel versus functions) only. Other matrices (M2, M-3 and M-4) are depicted in Chapter 4. Matrix M - l is produced through a exhaustive literature search. The main purpose of producing this matrix is to identify the construction documents which contain information required by construction personnel. The matrix M - l presents functions of various construction personnel. The functions included in the matrix are in the following areas: general management,  financial/accounting, engineering,  estimating,  project  planning,  construction  management,  management  and  site  management. This matrix has helped to produce matrices M-2, M-3 and M-4. Matrix M-2 (Figure 4.4) is a short-listed version of matrix M - l , and shows only project and site management functions. Matrix M-3 (Figure 4.5) presents information needs of various construction personnel. Matrix M-4 (Figure 4.6) identifies the construction documents which contain information as listed in matrix M-3. Matrix M - l runs through page number 237 to 240.  237  jopejiuoaqns uaiusjoj  siuapuaiuuadns  jssujBug p|9y bau|6u3 soiyo PI !J S  •jpdns |Bjeuao  )ue)uno33V  SD / Jtnewiisg  jssuiBug )soo JSSUjBU^ B U!inp9H3g/Buiuue|d  jaamBug loafcud  or  111  J G B E U E L M |9UUOSJ3cj  o"  )U3iuajnooJd J01BUJ|IS3 j 9 | l | 0  o o z  33  HI  O  or UJ  13IMQ lssy jsBeueiAJ uoi)onj(suoo  jssmBug jaiiio )ue(unooov jamo jsBeuei/M souem j LU  J866UEIAJ | E J 8 U 8 0  u" UI  luapjsaid  cc  AjHiqisuodsay  ii cc  TO  ° i Q.  1  £  |  i  c  O u  V) CD 'ra  i  LU CD o o  o  8  °3  X <I>  CD 08  I  » .".  o. E i o 1  •S .1 s in  </>(/)>, -S > ro f»< p c  •a' O  I  Q. ™  CD  II <  or LU z UJ  (0 T J CO  CD  JD _Q  1  '  co .! C O  ! i  a <  1 CEO  CO -j CO  o o o <  B.  CD  z o  c E  B in o o  I  "5 B  c O. ; "ro -  , 'o c .: co  >  TJ  < z  <  8  LU  a J  1  I  cn i  c I c 'c 'i ro •fc> 08 ;  1 J  -SHE  1 B  in m c o  JO CO  ra  Ol | i  Q <  e? C  CD  a  238  jopejiuooqns UGLU8J0-J  sluapuaiuuadns  ui  bsu|6ug eoyjo Pl !d 8  •IjpdnS |EJ8U8Q )us6v Bujseqojnd lueiunooov jesujBug AjajES SD / JOIBLUHS3  2  Q.  jgsu|6ug  ISOQ  jesUjBug "IT" uiinponos/Bumueid J88iij6ug patoJd  0£  or d  o  O je6euEiM isuuosjsd J8tieue|/\|  z  o o z  o; x cc o o i  iamo  joieunjsg J88U;BU3 I8JU3 )ssy jetJBUEifti  cc  uoipnjtsuoo J88U|6U3 J8ILO  LL )UB;unooov jsmo UJ  O  J86BUB1AI soueuy J86euey\| |Bjauao  V) DC UJ Q.  luapjsajd  A}!i!q|suodsay II  f  *  CO  XI (0 "5> -c c* .-  £  I£  oa  5 oi II ll  O  o  <0 CO c  |  p CA CD  1E  c CD  I  _  CO  >5f"3  ->» p c  TJ  8 ra  TJ  g)  i  CD  a ° N H co >• -2 > ra  in  2  'P  UJ  .S2 c  J8 TJ CD  (A CO  1  IT  "P| o x:  •s  El  CD a ;  a § CD TJ  CO TJ CD -C U co  ll CD  239  jopeJiuooqns U8UI8JOJ s|U8pua|uu8dns jssujBug p|sy  ipdns lejauao ;ue6v BuisEipjnd lueiunooov jeeujBug Aiajes  d LU of  jssuiBug ISOQ uiinpsgos/Buiuueid i • J88ui6ug pafojd  BC o"  jsBeueyy lasfojd jaBEUB^J |8UUOSJ8d JOBBUEI^ tueiuejnoojd  w z  o  jeemBug laiLip tssy jeBBUEinj uoi)onj|suoo  H O  ZZ) UL ZJ UJ  o K UJ Q.  O  -  I  Si  jaaujBug jaigo  d  )UB)unooov J8IM0 jsBeueyy SOUBUI-J  I*  jeBeuey^ I&I8U8Q |U8p!S8Jd  Ajinqisuodsay II cc  f *  c o  XI Cfl io J=  '«  £ §  ra  C o CO  ii 8 $ II  II  O UJ CO (A 'to <8 to >, > ra  "it  TJ ra > ra "O co  , CO  '8.  I § Or  ••§  a CD c TJ C ra ,  CO  ra CD  111  E CO ra > g.Jjl to o 8J  I  1  2* o 5  C  I  If  CD  co o QJ  I*  c o  C o  8 to CO c  a c '» ra  c o u a • o CD > CD ZJ to  8j  CT 9 ^  240  jopaiiuooqns S)U8pU3)UM8dnS jaaujBug p | s y beuiBug 93IUQ P|8!d  ipdns |ejsu8Q  o  lusBv Biuseupjnd  (UB)unooov J 8 s u i 6 u 3 AJOJBS SD/J01BIUIIS3 jaauiBug ( S O Q J88UjbU3 B unnpsups/Buiuueid CC I  X or  o  JGBEUBVM paloJd  o  O  CC  O  d  jaBeueyu, isuuosjsd jeBeueiq  0}  o o z LL LU Z  z o </)  JO;BLUI)S3 J S J U O  JG8UIBU3  )ssy  jsBi beuBjM uoiprutsuoQ jeeinBug  jamo  lUBiunoooy iaiqo jeBeueyu eoueuy  jaBBUB/J |eJ8U80  a: LU o.  o  o  [uaLuajnooJd  |U8p|S8Jd Ajlliqisuodsay II  f  *  1.  X) cc CO -c  8  c  c  o  s  <2  ra c  3  s  1n  o8 UJ8 io "5  i-SI  8Jl  8.x  > "> U <  c o  c a c  CO  I Si  3  5  8.  i CA  •§ i  32  '-8 3  Hi  CD  I  3 CO  IT?  1  1  co  9'  id  1  i i  si  SI  .•el  CO m ofl • • i l l CO TJ CO  21  2  Si  .sr  1  •8  o CD a  co o3  241  APPENDIX B Work Breakdown Structure This appendix lists a work breakdown structure (or work element structure) developed and promoted by the American Society of Professional Estimators (ASPE). Figure B.l shows the 17-division construction estimating classification system recommended by the ASPE (source: [Stewart & Stewart 86], p36). The Construction Specifications Institute (CSI) recommends a 16-division work breakout (used in the prototype as specs sections) that closely parallels that of the ASPE at the division level but deviates substantially thereafter. The ASPE breakout contains divisional classifications, which are subdivided into discipline classifications, which are, in turn, subdivided into definitive classifications. As the list of CSI breakout is very long, it is not presented in this thesis.  6.00 W O O D & PLASTIC DISCIPLINES 6.10 ROUGH CARPENTRY 6.11 Light Framing 6.13 Heavy Timber Framing 6.15 Trestle Framing 6.17 Laminated framing 6.20 FINISH CARPENTRY  1.00 G E N E R A L DISCIPLINES 1.10 BUILDING 1.20 TRANSPORTATION 1.30 INDUSTRIAL 1.40 HEAVY/UTILITIES 1.50 POWER 2.00 SITE W O R K DISCIPLINES 2.10 SITE PREPARATION - TYPE I (DEMOLITION) 2.11 Mechanical Demolition 2.13 Explosive Demolition 2.15 Salvaging 2.20 SITE PREPARATION - TYPE II (EARTHWORK) 2.21 Site Grading 2.22 Rock Excavation 2.23 Trench Excavation 2.24 Hauling 2.26 Finish Grading 2.27 Soil Stabilization 2.29 Dewatering 2.30 SITE PREPARATION - TYPE III (PILES, CAISSONS & SHORING) 2.31 Pile Foundations 2.35 Pier Foundations 2.37 Drilled Caissons 2.38 Sheet Piling & Cribbing 2.39 Underpinning 2.40 SITE DEVELOPMENT - TYPE I (UTILITIES) 2.41 Permanent Site Drainage 2.43 Water Utilities 2.44 Sanitary Utilities 2.45 Gas Utilities 2.46 Oil Utilities 2.47 Electrical Power Utilities 2.48 Telecommunication Utilities 2.50 SITE DEVELOPMENT - TYPE II (PAVING, SPECIALTIES, LANDSCAPE) 2.51 Curb and Gutter Specialist 2.52 Mudjacking Specialist 2.53 Bituminous Paving 2.54 Concrete Paving 2.55 Fencing 2.56 Site Specialties 2.57 Irrigation 2.58 Landscaping 2.60 RAILROAD WORK 2.70 MARINE WORK 2.80 TUNNELING  7.00 T H E R M A L & MOISTURE DISC. 7.10 WATERPROOFING & DAMP. 7.20 INSULATION 7.30 ROOFING 7.31 Shingle & Tile 7.35 Membrane 7.40 ARCHITEC. SHEET M E T A L 7.50 SKYLIGHTS 7.60 CAULKING & SEALANT 8.00 D O O R & WINDOW DISCIPLINE 8.10 HOLLOW M E T A L 8.20 WOOD & PLASTIC DOORS 8.30 SPECIAL DOORS 8.40 STOREFRONT & GLASS 8.41 Storefront & Curtain Wall 8.45 Glass & Glazing only 8.50 FINISH HARDWARE 9.00 FINISH DISCIPLINES 9.10FINISHES-TYPE I 9.11 Lath & Plaster 9.13 Metal & Stud Framing 9.14Drywall 9.15 Acoustical System 9.17 Special Coatings 9.19 Painting & Wall Covering 9.20 FINISHES - TYPE II 9.21 Ceramic & Quarry Tile 9.23 Terrazzo 9.25 Resilient Flooring 9.27 Carpeting 9.29 Special Flooring 10.00 SPECIALTY DISCIPLINES 10.10 BUILDING SPECIALTIES 10.20 P A R T m O N SYSTEMS 11.00 EQUIPMENT DISCIPLINES 12.00 FURNISHING DISCIPLINES 13.00 SPECIAL CONSTRUCTION DISC.  3.00 C O N C R E T E DISCIPLINE 3.10 CAST-IN-PLACE 3.20 REINFORCING STEEL 3.30 PRECAST 3.40 SPECIALIZED DECKS & FINISHES  14.00 C O N V E Y I N G S Y S T E M DISC. 14.10 ELEVATORS & ESCALATORS 14.20 HOISTS & CRANES 14.30 MATERIAL CONVEYING SYSTEM  4.00 M A S O N R Y DISCIPLINE 4.10 BRICK & BLOCK MASONRY 4.20 STONE MASONRY  15.00 M E C H A N I C A L DISCIPLINES 15.10 PLUMBING 15.20 PIPING 15.30 H V A C 15.40 FIRE PROTECTION  5.00 M E T A L DISCIPLINES 5.10 STRUCTURAL (FAB & ERECT) 5.11 Fabrication only 5.15 Erection only 5.20 MISCELLANEOUS ((FAB & ERECT) 5.30 ORNAMENTAL ((FAB & ERECT) 5.40 DECKING & SIDING 5.50 PRE-ENGINEERED BUILDING  16.00 E L E C T R I C A L DISCIPLINES 16.10 RESIDENTIAL 16.20 COMMERCIALTND. 16.30 POWER TRANSMISSION 16.40 COMMUNICATION SYSTEMS  Figure B . l  17.00 E N V I R O N M E N T A L DISCIPLINES  Work Breakdown Structure  243  APPENDIX C Data Dictionary This appendix contains the tables describing the attributes of each entities and relationships as presented in Section 4.6. Each attribute contains the following categories of information:fieldname, data type, length, index and comments. Field name is same as the attribute name. Data type is the type of data thefieldwill store, e.g., text, number, date/time, yes/no, currency. The number data-type is further categorized into byte, integer, long integer, single and double. Length is the storage space required for the values stored in thefield.Index shows the settings required for the indexed property. Comments shows a brief description of the field. The term 'data element list' is used as a title for all the tables in this appendix. The following abbreviations and legends have been used through out the tables: DOK = Duplicate OK; NOD = No Duplicate; Long = Long integer; Boldfieldname(s) = keyfield(s).  244  Table C. 1  Data Element List for Bids  Field Name  Data Type  Length  Index  Comments  ProjectID TradeDD BidderlD  Long Integer Long Date/Time Date/Time Currency Text  4 2 4 8 8 8 50  Yes (DOK) Yes (DOK) Yes (DOK) No No No No  same as ProjectID in Project table same as TradelD in Trades table same as CoJLD in Company table date bid package sent to a bidder date bid received from a bidder bid amount quoted by a bidder special comments or remarks  Bid Pkg Sent Bid Received Bid Amount Remarks  Table C.2  Data Element List for Change Orders  Field Name  Data Type  Length Index  Comments  ProjectID CMR#  SpecsID  Long Integer Integer  4 2 2  Yes (DOK) Yes (DOK) No  Initiation Date Current Status Description EstimatValue EngrEstimate Final Value Time Impact D-RfAE D-StS D-BfS D-RtAE D-FS Disposition  Date/Time Text Text Currency Currency Currency Byte Date/Time Date/Time Date/Time Date/Time Date/Time Text  8 20 50 8 8 8 1 8 8 8 8 8 50  No No No No No No No No No No No No No  same as ProjectID in Project table Contract Modification Request no. same as SpecsID in Specs Section table date the CMR initiated/introduced current status of the CMR brief description of the CMR contractor's estimate of the CMR engineer's estimate of the CMR final settlement value contract time impact (CTI) in days date CMR received from the A/E date CMR sent to subcontractor date backfromthe subcontractor date returned to the A/E date of final settlement disposition or comments  245  Table C.3  Data Element List for Company  Field Name  Data Type  Length  Index  Comments  Co ID Co_Name TypelD Contact 1 Contact2 Address City Province Postal Code Phone Fax Size  Counter Text Integer Long Long Text Text Text Text Text Text Text  4 40 2 4 4 30 20 20 7 20 20 6  Yes (NOD) Yes (DOK) Yes (DOK) No No No Yes (DOK) No No No No No  company identification number name of the company same as TypelD in Participant Type PeoplelD of primary contact person PeoplelD of second contact person address of the company name of the city province abbreviated name postal code of the area company's phone number company's fax number company size: big, medium or small  Table C.4  Data Element List for Correspond ence  Field Name  Data Type  Length  Index  Comments  ProjectID Corr#  Long Integer  4 2  Yes (DOK) No  From People To People Reference# Corr Date Date Sent DateRcvd Follow-up Keyword 1 Keyword2 Notes  Long Long Text Date/Time Date/Time Date/Time Yes/No Text Text Text  4 4 20 8 8 8 1/8 20 20 75  No No Yes Yes No No Yes Yes Yes No  same as ProjectlD in Project table project's correspondence entry serial number PeoplelD of the sender PeoplelD of the addressee reference no. on a correspondence original date of a correspondence date of mailing date correspondence received follow up required? first keyword describing the subject 2nd keyword describing the subject notes or comments  (DOK) (DOK)  (DOK) (DOK) (DOK)  246  Table C.5  Data Element List for Daily Events  Field Name  Data Type  Length  Index  Comments  ProjectID Date Day ID Daily Report# Prepared by ID FFC1 FFC2 FFC3 FFC4 NMFF1 NMFF2 NMFF3 NMFF4 MFF1 MFF2 MFF3 MFF4 Jobl Job2 Job3 Job4 Visitor 1 Visitor2 VisitTimel VisitTime2 Companyl Company2 Purpose 1 Purpose2 EquipUse Equipldle Activities New Act Act Compl Material Needs  Long Date/Time Integer Integer Long Long Long Long Long Byte Byte Byte Byte Byte Byte Byte Byte Text Text Text Text Text Text Date/Time Date/Time Text Text Text Text Memo Memo Memo Memo Memo Memo  4 8 2 2 4 4 4 4 4 1 1 1 1 1 1 1 1 20 20 20 20 30 30 8 8 30 30 30 30 N/A N/A N/A N/A N/A N/A  Yes (DOK) Yes (DOK) No Yes (DOK) No No No No No No No No No No No No No No No No No No No No No No No No No  same as ProjectID in Project table date of a report same as Day ID in Day table project's serial number of a report PeoplelD of person making report field force contractor-1 C o I D field force contractor-2 C o I D field force contractor-3 C o I D field force contractor-4 Co_ID non-manual field force of FFC1 non-manual field force of FFC2 non-manual field force of FFC3 non-manual field force of FFC4 manual field force of FFC1 manual field force of FFC2 manual field force of FFC3 manual field force of FFC4 job/trade name of FFC1 job/trade name of FFC2 job/trade name of FFC3 job/trade name of FFC4 name of visitor-1 name of visitor-2 time of visit of visitor-1 time of visit of visitor-2 name of the visitor-l's company name of the visitor-2's company purpose of the visitor-l'S visit purpose of the visitor-2'S visit equipment in use at the project equipment idle at the project activities in progress at the site New activities started at the site activities completed at the site future material needs  247  Table C.6  Data Element List for Daily Weather (Weather)  Field Name  Data Type  Length  Index  Comments  ProjectTD Date  Long Date/Time Integer Integer Yes/No Yes/No Yes/No Yes/No Yes/No Yes/No Yes/No Yes/No Yes/No Yes/No  4 8 2 2 1/8 1/8 1/8 1/8 1/8 1/8 1/8 1/8 1/8 1/8  Yes (DOK) Yes (DOK) No No No No No No No No No No No No  same as ProjectlD in Project table date of a report same as Day ID in Day table recorded average temperature sunny weather? cloudy weather? rainy weather? snowy weather? wind is still? wind is moderate? wind is high? humidity: dry? humidity: moderate? humidity: high?  Day ID Temperature Sunny Cloudy Rainy Snowy Still Wind Moderate Wind High Wind Humid-dry Humid-mod Humid-high  Table C.7  Data Element List for Days  Field Name  Data Type  Length  Index  Comments  Day TD  Integer Text  2  Yes (DOK) No  day's identity number name of the day  Day Name  Table C.8  9  Data Element List for Defective Work Notice (DWN)  Field Name  Data Type  Length  Index  Comments  ProjectTD DWN#  Long Integer Integer Date/Time Text Date/Time Currency Date/Time Long Date/Time Text  4 2 2 8 50 8 8 8 4 8 50  Yes (DOK) Yes (DOK) No No No No No No No No No  same as ProjectlD in Project table project's D W N entry serial number same as SpecsID in Specs Section D W N issue date brief description of the D W N date the D W N received estimated value of the D W N item date the D W N item re-inspected name of the inspector date no-objection received special comments, if any  SpecsID NoticeDate Description DateRcvd EstimatValue DateReinsp Reinspected by D-NoCR Comments  248  Table C.9  Data Element List for Materials Stored  Field Name  Data Type  Length  Index  Comments  ProjectTD Mat# SpecsID  Long Integer Long  4 2 4  Yes (DOK) Yes (DOK) No  LocJLD  Long  4  No  ItemDescription Loc-Installed Original Value % Installed  Text Text Currency Single  50 30 8 4  No No No No  PayEst# Pay Est Date  Byte Date/Time  1 8  No No  same as ProjectlD in Project table project's materials serial number same as SpecsID in Specs Section table same as Loc_ID in Storage Location table item description of the materials location of materials installed original value of materials stored estimated % quantity installed todate payment estimate number monthly payment estimate date  Table C.10  Data Element List for Monthly Progress  Field Name  Data Type  Length  Index  Comments  ProjectTD Pay Est#  Long Byte Double Date/Time Single Single  4 1 8 8 4 4  Yes (DOK) Yes (DOK) No Yes (DOK) No No  same as ProjectlD in Project table project's pay estimate serial number same as W B S I D in WBS table date of payment estimate % of the work item done this month % of the work item done to-date  WBSJD PayEst Date This Qntty Todate Qntty  Table C . l l  Data Element List for Participant Type  Field Name  Data Type  Length  Index  Comments  TypeTD  Integer Text  2 15  Yes (NOD) No  identity number of participant type name of the participant type  Participant Type  249  Table C.12  Data Element List for People  Field Name  Data Type  Length  Index  Comments  PeopleTD Last Name First Name Co ID Title Address City Province Postal Code Phone Fax  Counter Text Text Long Text Text Text Text Text Text Text  4 20 10 4 20 30 20 5 7 20 20  Yes (NOD) Yes (DOK) No Yes (DOK) No No No No No No No  people identification number last name of the people last name of the people same as C o I D in Company table title of the person in the company address of the people name of the city province abbreviated name postal code of the area phone number including area code fax number including area code  Table C . 13  Data Element List for Photograph  Field Name  Data Type  Length  Index  Comments  ProjectTD  Long  4  Yes (DOK)  Integer Photo# Long Photo TypelD Long Photgr ID Integer Roll# Integer Negative# Date Taken Date/Time Time of Day Date/Time Camera Direction Text Photgr Position Text Facility/Location Text  2 4 4 2 2 8 8 25 25 20  Yes (DOK) Yes (DOK) No No No No No No No Yes (DOK)  Keyword 1  Text  20  Yes (DOK)  Keyword2  Text  20  Yes (DOK)  Caption  Text  30  No  same as ProjectlD in Project table project's photograph serial no. same as in Photo Type table PeoplelD of the photographer serial number of the film roll negative-number in the film roll date of photography day time when photo taken direction of camera when taking position of the photographer name of location where photo taken first keyword describing the photo second keyword describing the photo caption or title of the photograph  250  Table C.14  Data Element List for Photo Type  Field Name  Data Type  Length  Index  Comments  Photo TypelD  Integer Text  2 15  Yes (NOD) No  identity number of photo type name of the photo type  Photo Type  Table C.15  Data Element List for Project ItemDetails  Field Name  Data Type Length Index  ProjectID  Long  4  WBSJD  Quantity Unit Price % Markup Delivery Date Delivery days  Double Single Currency Single Date/Time Integer  8 4 8 4 8 2  Approval Days  Integer  2  ShopDrawing MatSample Catalog Misc Misc Submittals As-built Drawing  Yes/No Yes/No Yes/No Yes/No Text Yes/No  1/8 1/8 1/8 1/8 20 1/8  Table C.16  Comments  Yes (DOK) same as ProjectID in Project table Yes (DOK) same as WBSID in WBS table No quantity of the WBS item No unit cost of the item No % markup to be added Yes (DOK) scheduled delivery or action date No number of days required for delivery No number of days required for approval Yes (DOK) shop drawing required? No materials sample required? No catalog or brochure required? No other submittals required? No calculation method statement etc. as-built drawing required? No  Data Element List for Project TradeDetails  Field Name  Data Type  Length Index  Comments  ProjectTD TradelD  Long Integer Currency Date/Time Date/Time Date/Time Long Currency  4 2 8 8 8 8 4 8  same as ProjectlD in Project table same as TradelD in Trades table estimated budget for the trade start date of the trade activities end date of the trade activities bid due date from bidders CoK) of the trade subcontractor subcontract amount  Budget StartDate EndDate Bid Due Date SublD Sub Amount  Yes (DOK) Yes (DOK) No Yes (DOK) No Yes (DOK) No No  251  Table C.17  Data Element List for Project  Field Name  Data Type  Length  Index  ProjectTD Project Name Project Type EngineerlD OwnerlD ManagerlD  Counter Text Text Long Long Long  4 30 10 4 4 4  OCBP OCCD Start Date End Date Address Phone Fax  Currency Date/Time Date/Time Date/Time Text Text Text  8 8 8 8 50 20 20  Yes (NOD) project identification number Yes (DOK) official name of the project Yes (DOK) type of project (building, bridge, etc.) No Co_ID of the architects/engineers Co ID of the owner No No PeoplelD of contractor's project manager No original contract bid price Yes (DOK) original contract completion date Yes (DOK) start date of the project No actual completion date No address or location of the project No site office phone number No site office fax number  Table C.18  Comments  Data Element List for Punch Lists  Field Name  Data Type  Length  Index  Comments  ProjectTD PL# SpecsID  Long Integer Integer  4 2 2  Yes (DOK) Yes (DOK) No  Punch List Item Facility/Area Date Identified Identified by Date Completed Date Checked Checked by  Text Text Date/Time Long Date/Time Date/Time Long  50 20 8 4 8 8 4  No No No No No No No  same as ProjectlD in Project table project's punch list serial number same as SpecsID in Specs Section table item description of the punch list location of the punch list item date identified by the engineer PeoplelD of the engineer date punch list item completed date rechecked by the engineer PeoplelD of the engineer  252  Table C.19  Data Element List for R F I  Field Name  Data Type  Length  Index  Comments  ProjectID RFT# Initiated By Initiation Date Drawing# RFI Description D-StAE D-RBfAE D-SBtS Response By  Long Integer Long Date/Time Text Text Date/Time Date/Time Date/Time Long  4 2 4 8 10 50 8 8 8 4  Yes (DOK) Yes (DOK) No No No No No No No No  Comments  Text  50  No  same as ProjectID in Project table project's RFI entry serial number PeoplelD of the initiator date of RFI initiation drawing number, for reference short description of the RFI date sent to the architect/engineer date returned back from the A / E date sent back to subcontractor PeoplelD of the person who responded comments or remarks  Table C.20  Data Element List for Shop Drawing  Field Name  Data Type  Length  Index  Comments  ProjectID ShopDrwg# WBSJD Description Disposition D-RfS D-StAE-I D-StAE-R D-StAE-F D-RBfAE D-SBtS C-RfS C-StAE C-RBfAE C-SBtS Comments  Long Integer Double Text Text Date/Time Date/Time Date/Time Date/Time Date/Time Date/Time Byte Byte Byte Byte Text  4 2 8 40 10 8 8 8 8 8 8 1 1 1 1 50  Yes (DOK) Yes (DOK) No No No No No No No No No No No No No No  same as ProjectID in Project table shop drawing number same as W B S I D in WBS table keyword or title of the shop drawing disposition or comments date received from the subcontractor date sent to A / E (initial) date sent to A / E (resubmission), if any date sent to A / E (Final), if any date received back from the A / E date sent back to the subcontractor copies received from the subcont. copies sent to the A / E copies received back from the A / E copies sent back to subcontractor comments or remarks  253  Table C.21  Data Element List for Spare Parts  Field Name  Data Type  Length  Index  Comments  ProjectTD SP#  Long Integer  4 2  Yes (DOK) No  Specs ID LocJD  Integer Long  2 4  No No  SP Description Eq Description Qntty Required  Text Text Integer  40 40 2  No No No  Qntty Dlvd Unit Status  Integer Text Text  2 4 50  No No No  same as ProjectlD in Project table project's spare parts entry serial number same as Specs ID in Specs Section same as L o c I D in Storage Location table description of the spare parts equipment the spare part is for total quantity required to be delivered actual quantity delivered to-date unit of the spare parts balance spare parts to be delivered  Table C.22  Data Element List for Specs Section  Field Name  Data Type  Length  Index  Comments  SpecsID  Integer  2  Yes (NOD)  4 5 50 12 40  No Yes (NOD) No No No  identity number of a specification section same as WBS ID in WBS table CSI Masterformat section number CSI Masterformat section title CSI Masterformat specs division CSI Masterformat Division Title  WBSID Long Specs Section Text Section Title Text Division Text Division Title Text  Table C.23  Data Element List for Storage Location  Field Name  Data Type  Length  Index  Comments  LocID  Counter Text Text  4 30 50  Yes (NOD) No No  identity number of storage location name of the storage location address of the storage location  Location Name Address  254  Table C.24  Data Element List for Trades  Field Name  Data Type Length Index  Integer Trade Name Text Description Memo TradeTD  Table C.25  2 25 N/A  Comments  YesfNOD) identity number of construction trade Yes (NOD) name of the trade No short description or scope of the trade  Data Element List for WBS  Field Name  Data Type Length Index  WBS ID  Double  Item Description Text TradelD Integer Unit Text  Table C.26 View No.  1 2 3 4 5 6 7 8 9 10 11 12 13  8 45 2 5  Comments  Yes (NOD) identity number of Work Breakdown Structure element No WBS item description No same as TradelD in Trade table No unit of the WBS item  Space Estimate for Tables View Name  Frequency  Bill of Quantities Monthly Bid Summary Sheet 1/project 5/month Change Orders Log Correspondence Log 5/day Daily Site Report 1/day 5/month DWN Log Materials Stored Log Monthly Monthly Monthly Progress Photograph Log 20/month Punch List 100/project 100/project RFI Log Shop Drawing Log 30/project Spare Parts Log 50/project  No. of Records  24 1 120 3600 730 120 24 24 480 100 100 30 50  Record Size (bytes)  150 360 510 450 1100 520 500 300 400 500 500 540 670 Total  Total Size (bytes)  3,600 360 61,200 1,620,000 803,000 62,400 12,000 7,200 192,000 50,000 50,000 16,200 33,500 2,911,460  255  APPENDIX D Default Relationships Table D-l presents the default relationships of table in the database of the system. Primary table denotes the one-end of the one-to-many (1-M) relationship. The related table denotes the many-end of the relationship. The fields column contains the attributes as described in section 4.6 for a particular entities or relationships (here referred to primary table or related table). The type column contains the relationships between entities as shows in Sections 4.4.1 to 4.4.13. The enforce column shows if referential integrity is required or not. Table D-2 can be used as a list of tables required for a query, and as a guide lines for joining line between two tables and setting their relationships. The bold-faced titles are the name of the different queries to be used in the database.  Table D . l  Default Relationships  Primary Table  Related Table  Company Company Company Company Company Company Company Company Company Day Day Participant Type People People People  Bids Daily Events Daily Events Daily Events Daily Events People Project Proj-Company Project TradeDetails Daily Events Daily Weather Company Company Company Correspondence  Fields  CoJD/BidderlD Co ID/FFC1 Co ID/FFC2 Co_ID/FFC3 Co_ID /FFC4 Co_ID CoID/Engineer Co_ID Co_ID/SubID Day ID Day ID TypelD PeoplelD/Contactl PeopleID/Contact2 PeopleTD/From  Type  Enforce  1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M  Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes  256  Primary Table  Related Table  People People People People People People Photo Type Project Project Project Project Project Project Project Project Project Project Project Project Project Project Project Project Project Specs Section Specs Section Specs Section Specs Section Specs Section Storage Location Storage Location Trade Trade Trade Trade WBS WBS WBS WBS  Correspondence Daily Events Photograph Project RFI RFI Photograph Bids Change Order Correspondence Daily Events Daily Weather Defective Work Materials Stored Monthly Progress Photograph Project ItemDetails Proj-Company Proj-Trade Project TradeDetails Punch List RFI Shop Drawing Spare Parts Change Order Defective Work Materials Stored Punch Lists Spare Parts Materials Stored Spare Parts Bids Proj-Trade Project TradeDetails WBS Monthly Progress Project ItemDetails Shop Drawing Specs Section  Fields PeoplelD/To PeoplelD/Prep by ID PeoplelD/Photgr ID PeoplelD/ManagerlD PeoplelD/Initiated By PeoplelD/ResponseBy Photo TypelD ProjectlD ProjectlD ProjectlD ProjectlD ProjectlD ProjectlD ProjectlD ProjectlD ProjectlD ProjectlD Project Project Project ProjectlD ProjectlD ProjectlD ProjectlD SpecsID SpecsID SpecsID SpecsID SpecsID LocID LocJD TradelD TradelD TradelD TradelD WBS ID WBS ID WBSJD WBS ID  Type  Enforce  1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M  Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes  Yes  257  Table D.2  Queries and Join Tables  Primary Table  Related Table  Fields  Type  Enforce  BidSummary Company Company Project Project Trade Trade  Bids Project TradeDetails Bids Project TradeDetails Bids Project TradeDetails  Co_ID/BidderID Co_ID/SubID ProjectID ProjectID TradelD TradelD  1-M 1-M 1-M 1-M 1-M 1-M  Yes Yes Yes Yes Yes Yes  Bill of Quantities Project Trade WBS  Project ItemDetails WBS Project ItemDetails  ProjectID TradelD WBSJD  1-M 1-M 1-M  Yes Yes Yes  Change Orders Company Company Company People People Project Project Specs Section Trade WBS  People Project Project TradeDetails Company Company Change Order Project TradeDetails Change Order Project TradeDetails Specs Section  CoJD CoJD/EngineerlD CoJD/SuMD PeoplelD/Contactl PeopleID/Contact2 ProjectID ProjectID SpecsID TradelD WBSJD  1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M  Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes  Correspondence Company Participant Type People People People People Project  People Company Company Company Correspondence Correspondence Correspondence  CoJD TypelD PeoplelD/ Contact 1 PeoplelD/ Contact2 PeopleTD/From People PeoplelD/To People ProjectID  1-M 1-M 1-M 1-M 1-M 1-M 1-M  Yes Yes Yes Yes Yes Yes Yes  Daily Report Company Company Company Company Company Company Company  People Project Project Daily Events Daily Events Daily Events Daily Events  CoJD CoJD/EngineerlD Co JD/OwnerJD CoJD/FFCl Co ID/FFC2 CoJD/FFC3 Co ID/FFC4  1-M 1-M 1-M 1-M 1-M 1-M 1-M  Yes Yes Yes Yes Yes Yes Yes  258  Primary Table  Related Table  Fields  Type  Enforce  Day People Project Project  Weather Daily Events Daily Events Weather  Day ID PeoplelD/Prep by ID ProjectlD ProjectlD  1-M 1-M 1-M 1-M  Yes Yes Yes Yes  People Project TradeDetails Project Company Company Defective Work Project TradeDetails Defective Work Project TradeDetails Specs Section  CoJD CoJD/SublD Co_ID/EngineerID PeoplelD/Contactl PeopleID/Contact2 ProjectlD ProjectlD SpecsID TradelD WBSJD  1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M  Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes  People Project Project TradeDetails Company Company Materials Stored Project TradeDetails Materials Stored Materials Stored Project TradeDetails Specs Section  CoID CoID/EngineerlD CoJD/SublD PeoplelD/Contactl PeopleID/Contact2 ProjectlD ProjectlD SpecsID LocJLD TradelD WBS ID  1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M  Yes Yes Yes Yes Yes Yes Yes Yes  People Project Project TradeDetails Company Company  Co_ID CoID/EngineerlD Co_JD/SubID PeoplelD/Contactl PeopleID/Contact2 ProjectlD ProjectlD ProjectlD TradelD  1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M  Yes Yes 1-M Yes Yes Yes Yes Yes Yes Yes Yes  Defective Work Company Company Company People People Project Project Specs Section Trade WBS  Materials Stored Company Company Company People People Project Project Specs Section Storage Location Trade WBS  Yes Yes Yes  Monthly Progres Company Company Company People People Project Project Project Trade WBS WBS  Monthly Progress Project ItemDetails Project TradeDetails Project TradeDetails Monthly Progress Project ItemDetails  WBS ID WBS ID  259 Primary Table  Related Table  Fields  Type  Enforce  People Project Company Company Photograph Photograph Photograph  Co_ID Co_ID/Engineer PeoplelD/Contactl  1-M 1-M 1-M 1-M 1-M 1-M 1-M  Yes Yes Yes Yes Yes Yes Yes  People Project TradeDetails Project Company Company Punch Lists Project TradeDetails Punch Lists Project TradeDetails Specs Section  CoJD CoID/SublD CoJD/EngineerlD PeoplelD/Contactl ProjectID ProjectID SpecsID TradelD WBSID  1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M  Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes  People Project Company Company Company RFI RFI RFI  CoJD CoJD/EngineerlD TypelD PeoplelD/Contactl PeoplelD /Contact2 PeoplelD/Initiated By PeoplelD/Response by ProjectID  1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M  Yes Yes Yes Yes Yes Yes Yes Yes  People Project TradeDetails Project Company Company Company Shop Drawing Project ItemDetails Project TradeDetails WBS Project TradeDetails WBS  CoJD CoJD/SubID CoJD/EngineerlD TypelD PeoplelD/Contactl  1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M  Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes  Photographs  Company Company People People People Photo Type Project  PeopleID/Contact2  PeoplelD/Photgr ID Photo TypelD ProjectID  Punch Lists  Company Company Company People People Project Project Specs Section Trade WBS  PeopleID/Contact2  RFIs  Company Company Participant Type People People People People Project Shop Drawing  Company Company Company Participant Type People People Project Project Project Specs Section Trade Trade  PeopleJX)/Contact2  ProjectID ProjectID ProjectID SpecsID TradelD TradelD  260  P r i m a r y Table  Related Table  Fields  Type  Enforce  WBS WBS  Project ItemDetails Shop Drawing  WBSJD WBSID  1-M 1-M  Yes Yes  People Project Project TradeDetails Company Company Company Project TradeDetails Spare Parts Spare Parts Spare Parts Project TradeDetails Specs Section  CoID CoID/EngineerlD Co_ID/SublT> TypelD PeoplelD/Contactl  1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M 1-M  Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes  Spare Parts  Company Company Company Participant Type People People Project Project Specs Section Storage Location Trade WBS  PeopleID/Contact2  ProjectlD ProjectlD SpecsID LocID TradelD WBS ID  261  APPENDIX E Form Printouts This appendix presents some of the form views as they appear on the screen. As stated in section 4.10.2.4,fiftyseven forms were created for the prototype. This appendix contains only six of them, as rest of the forms are more or less similar to these forms in their respective categories. The form categories are the following: data entry form, view form, and switchboard form. This appendix includes the following figures: Figure E. 1 Data Entry Form: Correspondence Registry. Figure E.2  Data Entry Form: Daily Site Activity.  Figure E.3  View Form: Bill of Quantities.  Figure E.4  View Form: Shop Drawing Submittals Log.  Figure E.5  Switchboard Form: Main Switchboard.  Figure E. 6  Switchboard Form: Print Reports.  requ©stir<g or 1?  E.l  Data Entry Form: Correspondence Registry  263  Daily Site Events Report Date:  1 1  ProjectlD: Daily Report*: Temperature:  Humidity: |moderat |j2 Non-manual  Average Field Force  Job Title  Manual  10 [layout, excavation  Main Contractor ID:  underground drainage  1st Subcontractor:  11  2nd Subcontractor:  26|]26  'ormwortc grade beam  3rd Subcontractor:  13 '3  electrical conduit  VISITORS Visitor 1  Time  Name  L_ 0:00 AMI K.N Smith  Purpose  Representing Owner  to see work progress  Visitor 2 Equipment In Use: • self loader. 1 - skip loader, 1 - backhoe, 1 - water tank, 1- yd dump truck Equipment Idle: 1 - tower crane Activities: ABC: continued site layout, clearing, grabbing and minor excavation. Drain Fast: laid underground drainage pipes. Super Ready: erected formwork for grade beam. Elect.Cont.: roughed in conduit thru grade beam. . New Activities: grade beam formwork. Activities Completed: Material Needs: masonary concrete hollow blocks -1000 nos., polythene sheets -10 rolls.  E.2  Data E n t r y F o r m : Daily Site Activity  264  Bill of Quantities  Rotum to r-oims Swietibord  City ^w&mm& BuHAi Description-  Jtem#  I Unit-1 Unit Price:  I S  2.10- Derrof*icr :  i S7.5jQ.aO  2.30 ^llns works  ZgUUm. 3.3C;^ecasT Concr 4 ip^3rttjk&8,ock J< 5 4 0 - p e s k i n g & Sic  M  l  If  -  jjjjffl,jTO,QP  ip PQ-Liafr'r-g  E.3  V i e w F o r m : B i l l of Quantities  :  265  Saium toformsSwitcJiborod  Shop Drawing Submittals Tracking Log Engineer: XYZ Engineering, inc.  ProjectlD: Project Name: |aty Warehouse Building  | Contact!: Allan Woo Contocf2: I Lam Wonder Phone: (604)555-2211  WBS ID;  ShoD Drawina*:  2.10 l-SD-2.1 2.30 l-SD-2.3 2.40 l-SD-2,4 2,50 l-SD-2.5 3.30 l-SD-3.3 5.10 l-SD-5.1 5.40 l-SD-5.4 7.30 l-SD-7.3 7.50 l-SD-7.5 8.10 l-SD-8.1 8.20 l-SD-8.2 9.11 l-SD-9,11 9.13 l-SD-9.13 9.14 l-SD-9.14 9.15 l-SD-9.15 9,21 l-SD-9.21 9.27 l-SD-9.27 14.10 l-SD-14.1 15.10 l-SD-15.1 15.30 l-SD-15.3 15.40 l-SD-15.4 16,10 l-SD-16,1 16.20 l-SD-16.2  E.4  Scheduled Date:  Status:  j |  • Kevword DescriDtion:  plan & location for detonator 22-Jan-95 Approved 11 -Feb-95Make correction not plan and section of piles 14-Feb-95 Submit specified item rletnils plan and section 06-Mar-95 Aooroved lavout plan and sections 14-Feb-95 Ac-Droved 14-Feb-95 Submit specified item plan and elevation details plan and elevation details 14-Feb-95 Approved plan and sections 16-Feb-96 Approved plan and sections 02-Feb-96 Approved 25-Mar-96 Revise and resubmit plan and elevation details plan and elevation details 30-Mar-96 New lavout details 1 l-Apr-96Due plan and sections Ol-Apr-96 Due 04-Apr-96 Due plan and sections Ol-Apr-96 Due plan and elevation details Ol-Apr-96 Due lavout details Ol-Mav-96 Due sections and details 02-Mav-96 Due plan and sections 10-Apr-96 Due plan and sections 20-Apr-96 Due details 27-Apr-96 Due plan and sections ll-Mav-96 Due conduit lavout plan and detail ll-Mav-96 Due  View Form: Shop Drawing Submittals Log  D-Rfj  25-Jai 11-Fel 14-Fel 06-Mc 13-Fei 14-Fei 14-Fel 14-Fel 02-Fet 25-Mc 01-Ac  266  AE-BEE-CEE Contracting  Database Window  j  j  Exit Microsoft Access ~j  MAIN SWITCH BOARD  E.5  Switchboard Form: Main Switchboard  267  Information and Reports Weekly Reports  Company List  ShopDrwg Due  ShopDrwg Status  Pacts and issues :  Monthly Progress  Punch LIB!  - -  RFI Log  OPEN Reports Print Preview  E.6  Switchboard F o r m : Print Reports Switchboard  •  268  APPENDIX F Reports Appendix F contains some of the reports/summaries/listings printouts that the prototype of PMICS can produce. The PMICS can be used as an administrative tool box, choosing what is needed from this system to perform specific tasks. It can be used to manage changes, to track submittals and other information, to produce reports and summaries, and so on. All the reports presented in this appendix are comparable to the reports displayed in the Expedition literature [Expedition 95}. This appendix includes the following figures:  Figure F. 1 Bid Summary Sheet. Figure F.2  Bill of Quantities.  Figure F.3  Change Orders Status Reports.  Figure F.4 Company Listings. Figure F. 5  Daily Site Reports.  Figure F.6  Weekly Activity Reports.  Figure F.7  Monthly Progress Reports.  Figure F. 8 Punch Lists. Figure F.9  Requests for Information Reports.  Figure F. 10 Shop Drawing Submittals Due Lists. Figure F. 11 Shop Drawing Submittals Status reports.  269  Contractor:  PMICS  Ae Bee Cee Contracting  Print Date:  4/10/96  Project #:  1  Proj. Manager: John Cook Engineer:  XYZ Engineering, Inc.  Bid Summary Sheet  Project Name:  City Warehouse Building Page #:  by Trade or Package  Trade:  Bid Due Date  Contact Phone  Bidder Name  Bid Received  Bid Amount  Budget  Difference  $135,500  $157,500  $22,000  Concrete  ABC Concrete  PaulNandi  4/13/95  (604) 666-423  4/13/95  Quick Concrete Supply  Tahir Ahmad (604) 555-668  4/13/95  Abdul Kareem  4/13/95  Super Readymix Co.  3/25/95 Remarks:  Concrete Pouring & Contr Comil Dey (604) 777-445  3/25/95 Remarks:  3/25/95 Remarks:  (604) 444-222  Trade:  Package Sent on  1  3/25/95 Remarks:  4/13/95  Price includes formwork, rebar, placing and curing. 4/13/95  $134,000  $157,500  $23,500  Price includes formwork, rebar, placing and curing. 4/12/95  $135,000  $157,500  $22,500  Price includes formwork, rebar, placing and curing. 4/13/95  $133,500  $157,500  $24,000  Price includes formwork, rebar, placing and curing.  Doors & Windows  City Doors & Windows  Grade One Doors & Win  Nigel Ho (604) 555-668  4/23/95  Mike Noble  4/23/95  Remarks:  (604) 666-332  Insta Doors, Inc.  Laloo Tipnis  4/3/95  4/3/95 Remarks:  4/23/95  (604) 555-112  Figure F.1  4/3/95 Remarks:  4/23/95  $33,100  $42,000  $8,900  $42,000  $9,100  Price includes labor, materials and installation only. 4/23/95  $32,900  Price includes labor, materials and installation. Scaffolding not included. 4/24/95  $34,000  Price includes labor, materials and installation only.  Bid Summary Sheet  $42,000  $8,000  270  Contractor:  PMICS  Ae Bee Cee Contracting  Proj. Manager: John Cook Engineer:  XYZ Engineering, Inc.  Project Name:  Bill of Quantities  Print Date:  4/11/96  Project #:  1  City Warehouse Building Page #:  by Project ltem#/WBS-ID Description  Quantity  Unit  Unit Price  1 Item Total  2.10  Demolition  1  L.S.  $7,500  $7,500  2.20  Earthwork  10,000  cum  $20  $200,000  2.30  Piling works  1  L.S.  $25,000  $25,000  2.40  Utilities  1  L.S.  $10,000  $10,000  2.50  Paving/Landscaping  1  L.S.  $7,500  $7,500  3.10  Cast-in-Place Concrete  450  cum  $350  $157,500  3.30  Precast Concrete  900  sqm  $150  $135,000  4.10  Brick & Block Masonary  5,000  sqm  $40  $200,000  5.10  Structural Metal  1,500  tons  $45  $67,500  5.40  Decking & Siding  1,050  sqm  $30  $31,500  7.10  Waterproofing & Dampproofing  1 . L.S.  $5,000  $5,000  7.20  Insulation  1  L.S.  $5,000  $5,000  7.30  Roofing  1,000  sqm  $45  $45,000  7.50  Skylights  20  ea  $1,000  $20,000  8.10  Hollow Metal Doors & Windows  70  ea  $300  $21,000  8.20  Wood & Plastic Doors & Windows  70  ea  $300  $21,000  9.11  Lath & Plaster Finish  9.13  Metal Stud Framing  9.14  1,500  sqm  $35  $52,500  500  sqm  $30  $15,000  Drywall  2,500  sqm  $20  $50,000  9.15  Acoustical Ceiling System  1,350  sqm  $24  $32,400  9.19  Painting & Wallcovering  1,100  sqm  $70  $77,000  9.21  Ceramic & Quality tiles  1,000  sqm  $35  $35,000  9.27  Carpeting  1,500  sqm  $55  $82,500  14.10  Elevators & Escalators  1  L.S.  $40,000  $40,000  15.10  Plumbing  1  L.S.  $30,000  $30,000  15.30  HVAC  1  LS.  $50,000  $50,000  15.40  Fire Protection  1  L.S.  $25,000  $25,000  16.10  Electrical System  1  LS.  $20,000  $20,000  16.20  Lighting  1  LS.  $20,000  $20,000  Page Total:  $1,487,900  F i g u r e F.2  Bill o f Q u a n t i t i e s  271  Contractor:  PMICS  Ae Bee Cee Contracting  Proj. Manager: John Cook Engineer:  XYZ Engineering, Inc.  Requests and Changes Log  Project Name:  Initiated  Trade: 5  To Vendor  Value Vendor  XYZ Engineer  $1,675  Ae Bee Cee Co  4  XYZ Engineer  $0  Ae Bee Cee Co  8  Ae Bee Cee  $550  Globe Electrical  Ae Bee Cee  $2,000  Globe Electrical  6  Ae Bee Cee  $1,200  Aeros Mechanic  26-Jul-95  03-Sep-95 Not incorporated  04-Aug-95  CO No. 3  l8-Sep-95  CO No. 7  07-Sep-95  CO No. 5  15-Sep-95  CO No. 6  28-Sep-95  CO No. 9  Incorporated into CO No. 3  Incorporated into CO No.7  27-Aug-95 XYZ Engineerin 28-Aug-95  Ae Bee Cee  $1,250  Aeros Mechanic  Ae Bee Cee  $1,250  Aeros Mechanic  Incorporated into CO No. 5  02-Sep-95 XYZ Engineerin 02-Sep-95 Incorporated into CO No. 6  15-Sep-95 XYZ Engineerin 16-Sep-95 Incorporated into CO No. 9  Masonary XYZ Engineer  $0  Ae Bee Cee Co  15-Aug-95 XYZ Engineerin 15-Aug-95  Comments: 11  CO No. 4  HVAC  Comments:  Trade:  15-Aug-95  Not incorporated  06-Sep-95 XYZ Engineerin 07-Sep-95  Comments: 12  Current Status  Incorporated into CO No. 4  25-Jul-95 XYZ Engineerin  Comments: 9  Date Approved  1  Electrical  Comments:  Trade:  Date  22-Aug-95 XYZ Engineerin 23-Aug-95  Comments: 10  Vendor  02-Aug-95 XYZ Engineerin 03-Aug-95  Comments:  Trade:  1  Concrete  Comments: 7  Project*: Page #: Responded Date  4/10/96  City Warehouse Building  by Trade or Package  CMR#  Print Date:  XYZ Engineer  $1,550  Ae Bee Cee Co  Figure F.3  Time & material  To be done time & material  11-Sep-95 XYZ Engineerin 12-Sep-95  Comments:  29-Aug-95  25-Sep-95  Incorporated into CO No. 8  Change Orders Status Report  CO No. 8  272  Contractor:  PMICS  Ae Bee Cee Contracting  Project*:  Proj. Manager: John Cook Print Date:  Project Name: Company Listing With Contacts  4/10/96 Title  Company:  ABC Concrete  PaulNandi  Technical Manager 303 Green Ave.  Ratan Mahi  Asst. Manager  Company:  Ae Bee Cee Contracting  Allan Maxwell  Construction Mana 123 Green Avenue  Dick Wise  Chief Engineer  Company:  Aeros Mechanical Contracting Participant Type:  Subcontractor  AnuBo  Engineering Mana  Vancouver  B.C.  Safdar Alam  Site Representativ  Company:  City Warehouse  Participant Type:  Owner  Ajay Peter  Warehouse Manag 123 Dead End Street Vancouver  B.C.  Andrew Wael  Asst. Manager  Company:  Civil Contracting Co.  Don Holland  Sales Agent  Brian Whisker  Sales Agent  Company:  Constructive Steel  Lotte Green  Business Manager 404 Alps Street  Saad Cho  Asst. Manger  Company:  Globe Electricals, Inc.  MahmoodHassa Manger  Adress  City  Participant Type: Vancouver  Participant Type:  707 Plum Street  Vancouver  Participant Type:  879 Nariman Point  Vancouver  Participant Type: Vancouver  Participant Type:  555 Avon Street  Allan Jo  Sales Agent  Company:  Grade One Doors & Windows  Mike Noble  Business Manager 333 Fonton Avenue  Mike Noble  Business Manager  Vancouver  Participant Type:  Figure F.4  Vancouver  City Warehouse Building P  Contacts  Province Postal Code  1  A  9  E  #  1  Phone  Fax  (604)666-423  (604)666-4235  (604)666-223  604)666-2234  V6N 4X8  (604)666-224  (604)666-2245  V6M 5W9  (604)888-556  604)888-5567  (604)444-123  (604)444-1233  (604)666-599  (604)666-5996  (604)888-665  (604)888-6656  (604)666-332  (604)666-3323  Subcontractor B.C.  V6A3K3  Home-office B.C.  V5R 4W8  Subcontractor B.C.  V6B 3B3  Subcontractor B.C.  V5T6M7  Subcontractor B.C.  V6A4M3  Subcontractor B.C.  V5R 6H3  Company Listing  273  Daily Site Report Project #:  1  Page #:  PMICS  11  Daily Report#:  1  Date:  16-Jan-95  Day:  Monday  Weather:  sunny  Temperature:  11  Project Name: City Warehouse Building Engineer:  XYZ Engineering, Inc.  Owner:  City Warehouse  Contractor:  Ae Bee Cee Contracting  Proj. Manager: John Cook  Wind:  low  Humidity:  moderate  A V E R A G E FIELD FORCE Remarks / Job Type  Non-manual  Manual  Ae Bee Cee Contracting  2  10  Drain Fast Co.  4  5  underground drainage  Name of Contractor  layout, excavation  Super Readymix Co.  3  6  formwork grade beam  Electrical Contracting Est.  2  5  electrical conduit  11  26  = 37  Total Force:  VISITORS Time  Visitors'Name  Representing  10:00 AM  K.N.Smith  Owner  Remarks / Purpose to see work progress  Equipments in Use 1 - self loader, 1 - skip loader, 1 - backhoe, 1 - water tank, 1- yd dump truck Equipments Idle 1 - tower crane Construction Activities A B C : continued site layout, clearing, grabbing and minor excavation. Drain Fast: laid underground drainage pipes. Super Ready: erected formwork for grade beam. Elect.Cont.: roughed in conduit thru grade beam.  New Activities grade beam formwork. Activities Completed  Material Needs masonary concrete hollow blocks - 1000 nos., polythene sheets - 1 0 rolls.  Prepared By:  Allan Maxwell  Figure F.5  Signature:  Daily Site Report  274  PMICS  Ae Bee Cee Contracting  Contractor:  Proj. Manager John Cook Engineer:  XYZ Engineering, Inc.  Owner:  City Warehouse  Report #  Temp c  11  sunny  Project Name:  4/10/96  Project #:  1  City Warehouse Building Page #:  Wind  Humidity  Contractors on site Ae Bee Cee Contracting  Monday  16-Jan-95 11  Sky  Weekly Site Activity  Print Date:  low  moderate Drain Fast Co. Super Readymix Co.  1  Construction Activities ABC: continued site layout, clearing, grabbing and minor excavation. Drain Fast: laid underground drainage pipes. Super Ready: erected formwork for grade beam. Elect.Cont.: roughed in conduit thru grade beam.  Electrical Contracting Est.  12  Ae Bee Cee Contracting  Tuesday  17-Jan-95  sunny  13  low  moderate Drain Fast Co. Super Readymix Co.  ABC: continued site layout, clearing, grabbing and minor excavation. Drain Fast: alligned and tested underground drainage pipes. Super Ready: placed rebar for grade beam. Elect.Cont.: continued roughing in conduit thru grade beam.  Electrical Contracting Est.  13  Ae Bee Cee Contracting  Wednesday  18-Jan-95  snowy  4  high  low  Drain Fast Co. Super Readymix Co.  ABC: continued site layout & suevey, and minor excavation. Drain Fast: backfilled and compacted, underground drainage pipes. Super Ready: continued rebar and formwork for grade beam. Elect.Cont.: dug hole for earthen pit.  Electrical Contracting Est.  14  Ae Bee Cee Contracting  Thursday  19-Jan-95  cloudy  3  high  moderate Drain Fast Co. Super Readymix Co.  ABC: continued site survey, clearing, grabbing and minor excavation. Drain Fast: dug hole and poured bilnding concrete for drainage manholes. Super Ready: closed formwork for grade beam. Elect.Cont.: checked conduit thai grade beam.  Civil Contracting Co.  15  Ae Bee Cee Contracting  Friday  20-Jan-95 4  sunny  moderat  low  Drain Fast Co. Super Readymix Co.  ABC: continued site survey and minor excavation. Drain Fast: erected manhole and fixed underground pipe accessories. Super Ready: poured concrete for grade beam. CCC: started masonary work for site office.  Civil Contracting Co.  16  Ae Bee Cee Contracting  Saturday  21-Jan-95 7  rainy  low  moderate Drain Fast Co. Super Readymix Co.  ABC: checked concrete quality. Drain Fast: continued manhole erection and underground pipe fixing. Super Ready: continued concrete pouring for grade beam. CCC: continued masonary work for site office.  Civil Contracting Co.  17  Ae Bee Cee Contracting  Sunday  22-Jan-95 6  cloudy  high  No work done today.  moderate Ae Bee Cee Contracting Ae Bee Cee Contracting Ae Bee Cee Contracting  Figure F.6  Weekly Activity Report  275  Contractor:  PMICS  Ae Bee Cee Contracting  Proj. Manager: John Cook Engineer:  XYZ Engineering, Inc.  Monthly Progress Report p ject Name: ro  Print Date:  4/11/96  Project #:  1  City Warehouse Building  by Project ltem# Pay Est#: 2.10  Quantity Unit  Description 1  PayEst Date:  Unit Price  Item Total  $7,500  $7,500  1  Demolition  % Progress:  10,000  Earthwork  $20  1  Piling works  $25,000  1  Utilities  $10,000  1  Paving/Landscaping  $7,500  Cast-in-Place Concrete  450  $350  900  Precast Concrete  $150  Brick & Block Masonary  5,000  $40  1,500  Structural Metal  $45  1,050  Decking & Siding  $30  Waterproofing & Dampproofing  1  $5,000  1  Insulation  $5,000  1,000  Roofing  $45  20  Skylights  $1,000  Hollow Metal Doors & Windows  70  $300  Wood & Plastic Doors & Windo  70 ea  Figure F.7  $21,000 % Progress:  ea 8.20  $20,000 % Progress:  ea 8.10  $45,000 % Progress:  sqm 7.50  $5,000 % Progress:  LS. 7.30  $5,000 % Progress:  LS. 7.20  $31,500 % Progress:  sqm 7.10  $67,500 % Progress:  tons 5.40  $200,000 % Progress:  sqm 5.10  $135,000 % Progress:  sqm 4.10  $157,500 % Progress:  cum 3.30  $7,500 % Progress:  LS. 3.10  $10,000 % Progress:  L.S. 2.50  $25,000 % Progress:  L.S. 2.40  $200,000 % Progress:  cum 2.30  This Amount  Tod ate Amount  15-Feb-95  L.S. 2.20  1  Page #:  $300  $21,000 % Progress:  $24,750 20 $66,000 15 $8,250 15 $2,200 10 $12,375 10 $51,976 15 $29,700 10 $44,000 10 $14,850 10 $10,396 15 $1,100 10 $1,100 10 $14,850 15 $4,400 10 $6,930 15 $4,620 10  Monthly Progress Report  $24,750 20 $66,000 15 $8,250 15 $2,200 10 $12,375 10 $51,976 15 $29,700 10 $44,000 10 $14,850 10 $10,396 15 $1,100 10 $1,100 10 $14,850 15 $4,400 10 $6,930 15 $4,620 10  276  Contractor:  PMICS  Ae Bee Cee Contracting  Proj. Manager: John Cook Engineer:  Project Name:  XYZ Engineering, Inc.  Print Date:  4/10/96  Project #:  1  City Warehouse Building Page #:  Punch List by Contractor Item#  Status  Trade  Contact: New  Company:  Approved  Company:  1 Checked  Company:  or  Dick Wise  Phone:  31-Mar-96 Admin Bldg  (604)666-223  Clean debris from around building  Anu Bo  or  SafdarAlam  Phone:  (604)666-224  Balance HVAC system  11-Mar-96 Admin Bldg  HVAC  Don Holland  or  Brian Whisker  30-Mar-96 OP Bldg  Utilities  Phone:  28-Mar-96  (604)444-123  Clean out storm inlet box  $400  Globe Electricals, Inc. Mahmood Hassa  or  Phone:  Allan Jo  (604) 888-665  Approved  Electrical  19-Mar-96 OP Bldg  Light in control room not operating correctly  New  Electrical  30-Mar-96 Admin Bldg  Tighten electrical outlet wall plates  New  Electrical  01-Apr-96 OP Bldg  Submit load test reports  $1,500  $250  $1,000  Aeros Mechanical Contracting Contact:  New  $2,500  Civil Contracting Co.  Contact:  Company:  $450  Aeros Mechanical Contracting  Contact: New  Allan Maxwell  General Constru  Contact:  2  Location  Date  Value  Ae Bee Cee Contracting  Company:  5  Description  HVAC  Anu Bo  or  SafdarAlam  02-Apr-96 Level 1  Figure F.8  Phone:  (604)666-224  Grill bent in room 122  Punch Lists  $475  31-Mar-96  277  Contractor:  Ae Bee Cee Contracting  Proj. Manager: John Cook Engineer:  XYZ Engineering, Inc.  From Vendor Phone  RFI # Title RFI Description  Responsible Dept: Painting  PMICS Requests for Information By Participant To Vendor Phone  (604) 666-2233  (604) 444-2222  Demolition methods Destruction Co.  City Warehouse  (604) 666-2240  (604) 555-8122  Status  5/7/95  1-RFI-2  Done  1-RFI-6  Done  1-RFI-7  Done  6  2-RFI-1  Done  6  1-RFI-3  Done  6  1-RFI-4  Done  5/12/95  5/23/95  5/29/95  6  5/25/95  5/31/95  6  Refer to the method statements attached.  Electrical Contractin City Roads Dept.  Figure F.9  5/13/95  Yes, all electrical handholes require drainage.  Globe Electricals, In City Warehouse  Do DB conduits require coating?  5/7/95  (604) 555-8122  Do all electrical handholes require drainage?  (604) 888-6655  5/16/95  Yes, they do. Follow the specifications for cathodic  Elecetrical handhole Globe Electricals, In City Warehouse (604) 888-6655  5/10/95  (604) 555-8122  Do all buried pipe receive cathodic protection?  Electrical systems  File Location  Days Taken  Electrical  (604) 444-1111  4  Response Date  1  No, appropriate sealing method to be employed.  Questions on demolition methods.  3  Page #:  (604) 555-8122  Can manholes be sealed with mastic?  Electrical service  1  Yes, all metal boxes get paint.  Super Readymix Co. City Warehouse  Responsible Dept:  Project #:  City Warehouse Building  (604) 555-8122  Do all metal boxes get paint?  7  Initiation Date  4/10/96  Civil Ae Bee Cee Contrac City Warehouse  Concrete  Project Name:  Print Date:  5/12/95  5/18/95  (604) 555-8122 Not necessarily.  Requests for Information Report  278  Contractor:  PMICS  Ae Bee Cee Contracting  Print Date:  Proj. Manager: John Cook Engineer:  P r o  XYZ Engineering, Inc.  Project Name:  Shop Drawing*: Ceilings  Subcontractor 1-SD-9.15  Contacts  Phone  e c t  # ;  4/10/96 1  City Warehouse Building  Shop Drawing Sumittals Due Trade  J  Page #: Scheduled Date Activity Submission  1  Remaining Days  Status  Description: plan and sections  Civil Contracting Co.  Don Holland  (604)444-1234  15-Apr-96  01-Apr-96  -9  Due  15-Apr-96  01-Apr-96  -9  Due  (604)444-1234  15-Apr-96  01-Apr-96  -9  Due  (604)444-1234  18-Apr-96  04-Apr-96  -6  Due  15-May-96  l0-Apr-96  0  Due  25-Apr-96  11-Apr-96  1  Due  (604)666-2244  25-May-96  20-Apr-96  10  Due  (604)666-2244  01-Jun-96  27-Apr-96  17  Due  15-May-96 01-May-96  21  Due  Brian Whisker Shop Drawing*: Drywall & Partition  1-SD-9.13  Description: plan and sections  Civil Contracting Co.  Don Holland  (604)444-1234  Brian Whisker Shop Drawing*: Tiling  1-SD-9.21  Description: plan and elevation details  Civil Contracting Co.  Don Holland Brian Whisker  Shop Drawing*: Drywall & Partition  1-SD-9.14  Description:  Civil Contracting Co.  Don Holland Brian Whisker  Shop Drawing*: Plumbing  1-SD-15.1  Description: plan and sections  Aeros Mechanical Cont Anu Bo  (604)666-2244  Safdar Alam Shop Drawing*: Drywall & Partition  1-SD-9.11  Description: layout details  Civil Contracting Co.  Don Holland  (604)444-1234  Brian Whisker Shop Drawing*: HVAC  1-SD-15.3  Description: plan and sections  Aeros Mechanical Cont Anu Bo Safdar Alam  Shop Drawing*: Fire Protection  1-SD-15.4  Description: details  Aeros Mechanical Cont Anu Bo Safdar Alam  Shop Drawing*: Special Flooring  1-SD-9.27  Description: layout details  Civil Contracting Co.  Don Holland  (604)444-1234  Brian Whisker  Figure F.10  Shop Drawing Submittals Due Lists  279  Contractor:  PMICS  Ae Bee Cee Contracting  Proj. Manager: John Cook Engineer:  XYZ Engineering, Inc.  Subcontractor  Shop Drwg #  Shop Drawing SumittalS Status By Trade or Package  Project Name:  Print Date:  4/10/96  Project #:  1  City Warehouse Building Page#:  Scheduled Date Remaining Start Submission Days  Keyword Description  1 Status  Ceilings  Trade Name:  Civil Contracting Co.  Contact: 1-SD-9.15  Trade Name:  Don Holland  or  plan and sections  Brian Whisker 15-Apr-96  (604)444-1234  01-Apr-96  Due  -9  Demolition  Ae Bee Cee Contracting 1-SD-2.1  Trade Name:  Contact:  Allan Maxwell  or  plan & location for detonator  Dick Wise 05-Feb-95  (604)666-2233 22-Jan-95  Approved  -444  Doors & Windows  Grade One Doors & Windows  Trade Name:  Contact:  Mike Noble  or  (604)666-3322  1-SD-8.1  plan and elevation details  15-Apr-96  25-Mar-96  -16  1-SD-8.2  plan and elevation details  20-Apr-96  30-Mar-96  -11  Revise and resubmit  New  Diywall & Partition  Civil Contracting Co.  Contact:  Don Holland  or  Brian Whisker  (604) 444-1234 Due  1-SD-9.11  layout details  25-Apr-96  11-Apr-96  1  1-SD-9.13  plan and sections  15-Apr-96  01-Apr-96  -9  Due  18-Apr-96  04-Apr-96  -6  Due  1-SD-9.14  Trade Name:  Mike Noble  Electrical  Globe Electricals, Inc.  Contact:  Mahmood Has or  1-SD-16.1  plan and sections  1-SD-16.2  conduit layout plan and details  Figure F.11  Allan Jo 15-Jun-96  15-Jun-96  (604) 888-6655 11-May-96  11-May-96  31  31  Shop Drawing Submittals Status Report  Due  Due  

Cite

Citation Scheme:

    

Usage Statistics

Country Views Downloads
United States 23 1
China 9 22
Russia 7 1
Canada 7 0
Spain 6 0
India 4 0
France 2 0
Turkey 1 0
Singapore 1 0
Germany 1 10
Ghana 1 0
Pakistan 1 0
City Views Downloads
Unknown 24 12
Ashburn 6 0
Guangzhou 4 0
Shenzhen 3 6
Atlanta 3 0
Senoia 2 0
Wilmington 2 0
Ottawa 2 0
Mountain View 2 1
Woodland 2 0
Kansas City 2 0
Moscow 1 1
Lakewood 1 0

{[{ mDataHeader[type] }]} {[{ month[type] }]} {[{ tData[type] }]}
Download Stats

Share

Embed

Customize your widget with the following options, then copy and paste the code below into the HTML of your page to embed this item in your website.
                        
                            <div id="ubcOpenCollectionsWidgetDisplay">
                            <script id="ubcOpenCollectionsWidget"
                            src="{[{embed.src}]}"
                            data-item="{[{embed.item}]}"
                            data-collection="{[{embed.collection}]}"
                            data-metadata="{[{embed.showMetadata}]}"
                            data-width="{[{embed.width}]}"
                            async >
                            </script>
                            </div>
                        
                    
IIIF logo Our image viewer uses the IIIF 2.0 standard. To load this item in other compatible viewers, use this url:
http://iiif.library.ubc.ca/presentation/dsp.831.1-0050343/manifest

Comment

Related Items