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-02-11

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

Item Metadata

Download

Media
831-ubc_1996-0278.pdf [ 13.89MB ]
Metadata
JSON: 831-1.0050343.json
JSON-LD: 831-1.0050343-ld.json
RDF/XML (Pretty): 831-1.0050343-rdf.xml
RDF/JSON: 831-1.0050343-rdf.json
Turtle: 831-1.0050343-turtle.txt
N-Triples: 831-1.0050343-rdf-ntriples.txt
Original Record: 831-1.0050343-source.json
Full Text
831-1.0050343-fulltext.txt
Citation
831-1.0050343.ris

Full Text

USE OF INFORMATION FOR PROBLEM 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 this thesis in partial fulfilment of the requirements for an advanced degree at the University of British Columbia, I agree that the Library shall make it freely available for reference and study. I further agree that permission for extensive copying of this thesis for scholarly purposes may be granted by the head of my department or by his or her representatives. It is understood that copying or publication of this thesis for financial gain shall not be allowed without my written permission. Department of CIV"- * A/<S/*J The University of British Columbia Vancouver, Canada Date AS*U '2, DE-6 (2/88) 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 data from different 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 v List of Tables x List of Figures xii Acknowledgment xvDedication xvi1. INTRODUCTION 1 1.1 Introduction1.2 Goals1.3 Objectives 4 1.4 Research Methodology 5 1.4.1 Literature Review1.4.2 System Development 6 1.5 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 1.6 Overview of Databases and Database Management Systems 11 1.6.1 Database Concepts 11.6.2 Top-Down Planning versus Bottom-Up Design 13 1.6.3 Database Terminology 14 1.6.4 Data Modeling 6 1.6.5 DBMS versus Data Models 18 1.6.6 Relational Databases Terminology 9 1.6.7 Normalization 21 1.6.8 Repeating Groups 3 1.6.9 Normalization Examples 25 1.6.10 Data Dictionary 30 1.7 Focus of the Thesis1.8 Overview of the Thesis 31 LITERATURE REVIEW 32 2.1 Introduction 32.2 Documents, Records and Reports in the Construction Industry 32 2.2.1 Document Classifications 32.2.2 Project Documentation 3 2.2.3 Reporting Hierarchy 35 2.2.4 Routing of Submittals 7 2.2.5 Computerized Daily Site Reporting 38 2.2.6 Computerized Change Management2.2.7 Claims Analysis 39 2.2.8 Photo Records 40 2.3 Information and Construction Projects 42.3.1 Construction Personnel Role and Information Needs 40 2.3.2 Traditional Information Flow Framework 42 2.3.3 Information Flow Requirements 43 2.4 Information and Information Systems 4 2.4.1 Information Definition 42.4.2 Management Information and the Construction Industry 45 2.4.3 Current Information Problems 48 vi 2.4.4 Management Information Control Systems 49 2.4.5 Computer-Based Information Systems 51 2.4.6 Information or Problem Tracking2.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 9 2.5 Related Work 60 2.5.1 Expedition2.5.2 Resident Management System (RMS) 61 2.5.3 Centex-Rooney's Jobsite Program2.5.4 MULTROL 62 2.6 Matrix as an Analytical Tool 62.7 Summary 4 3. FRAMEWORK 65 3.1 Introduction3.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 5 3.3 PMICS - Project Management Information Control System 77 3.3.1 Objectives of PMICS 73.3.2 Planning for PMICS 7 3.3.3 Requirements of PMICS 78 3.3.4 System Design of PMICSvii 4. SYSTEM ANALYSIS AND DESIGN 79 4.1 General Description 74.1.1 Description of Users 80 4.1.2 Background for the Application 82 4.1.3 Information Model Building 3 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 PMICS4.1.5 Selected User Views 88 4.2 Database Design Overview 96 4.3 File Organization Schemes 7 4.3.1 Core Entity - the specifications 94.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 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 4.6 Entity and Relationship Attributes 201 4.6.1 Entities 202 4.6.2 Relationships 6 4.6.3 Entity Cross-Reference Matrix 204.7 Data Dictionary 208 4.8 Data Volume and Usage 204.8.1 Assumptions 9 4.8.2 Entity Cardinality 210 4.8.3 Expected Entity Size 1 4.9 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 5 ix 4.10 Physical Implementation 217 4.10.1 Microsoft Access Terminology 214.10.2 Prototype Development 220 4.10.2.1 Developing Tables 221 4.10.2.2 Setting Default Relationships between Tables 221 4.10.2.3 Designing Queries 224.10.2.4 Creating Forms ...222 4.10.2.5 Producing Reports 224.10.2.6 Creating Applications with Macros 223 5. CONCLUSIONS 224 5.1 Thesis Review5.2 Benefits and Applications of PMICS 226 5.3 Experiences and Observations 227 5.4 Contributions 228 5.5 Extensions and Future Research 229 Bibliography 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 ....155 Table 4.12 Key Terms for RFI Tracking Log 160 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 1 Table C. 1 Data Element List for Bids 244 Table C.2 Data Element List for Change Orders 24Table C.3 Data Element List for Company 245 Table C.4 Data Element List for Correspondence 24Table C.5 Data Element List for Daily Events 246 Table C.6 Data Element List for Daily Weather (Weather) 247 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 24Table C. 12 Data Element List for People 249 Table C. 13 Data Element List for Photograph 24Table 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 25Table C. 19 Data Element List for RFI 252 Table C.20 Data Element List for Shop Drawing 25Table C.21 Data Element List for Spare Parts 253 Table C.22 Data Element List for Specs Section 25Table 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 25Table C.26 Space Estimate for Tables 4 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 3 Figure 2.1 Contract Reporting Hierarchy 36 Figure 2.2 Routing of Contractor Submittals 7 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 5 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) 2 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 3 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 project-management 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 PROJECT 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 84] 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 73] 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. Business system planning Physical (distributed) data bases Top-Down •< Information model Data models V Bottom-Up Proposed data bases User views \J Figure 1.2 Top-Down Planning versus Bottom-Up Design (source: [McFadden & Hoffer 88], p68) 1.6.3 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. An 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 DBMS insists that only two relationships need be used on the data model: one-to-one and one-to-many. Many-to-many 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 DBMS 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 DBMS 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 two-dimensional 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 DBMS 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 6-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 tuples attributes Relation: Employee Figure 1.3 Relational Database Example 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) and fifth normal form (5NF). 23 User Views Unnormalized relations Normalized relations (INF) Second normal form relations (2NF) Third normal form relations (3NF) remove repeating groups remove partial dependencies remove transitive dependencies 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 2NF 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 above five relations 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 DBMS 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. Appendix-B 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. It first looks at documents, records and reports in the construction industry; then reviews issues of information and construction management, information and information systems, and related works; and finally explores 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 are fixed when 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 of 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 of the current status of some set of information: it usually accesses databases to quote the most recent revision of 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 of 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, fields directions, shop-drawing submittal log, bid tabulation forms, meeting minutes, estimates, cost records, quantity take-offs, 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 Data traceability Monthly contract cost report Contract formation schedule report Contract formation schedule CHZr-rj-rJ-i 1 Project schedule Change request and claim log Contract letters and claims Summary management reporting Integration of information Change order summary Change order log Contract letters and claims Progress payments Backcharge summary Detailed contract reports Backcharge log Tracking aids 4 Contract letters and claims Source documents 4 forecasts Figure 2.1 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 In Routing procedures for submittals being received by the A/E General contractor Resident project representative A/E project manager Design dept reviewers In -Out—>-Routing procedures for submittals being returned to the contractor 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 NIAM (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. Information Top management Middle management Project management Project staff Computer Network systems 1 System staff Figure 2.3 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 93] 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 94] 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 93] 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 Model 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). An 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 (one-to-many and many-to-many) relationships between entities (rectangles). Attributes are associated with entities and represented with an ellipse. Figure 2.4 Entity-Relationship Data Model Examples 2.4.9 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 Ri and R2 is defined over a subset of their attributes Xi and X2 with common domains. An ownership connection between an owner relation Ri and an owned relation R2 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 R2 must be connected to an owning tuple in Ri. • Deletion of an owning tuple in Ri requires deletion of all tuples connected to that tuple in R2. • Modification of Xi in an owning tuple of Ri requires either propagating the modification to attributes X2 of all tuples in R2 or deleting those tuples. A reference connection between a primary (referencing) relation Ri and a foreign (referenced) relation R2 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 Ri must either be connected to a reference tuple in R2 or have null values for its attributes Xi. • Deletion of a tuple in R2 requires either deletion of its referencing tuples in Ri, assignment of null values to attributes Xx of all the referencing tuples in Ri, or assignment of new valid values to attributes Xi of all referencing tuples corresponding to an existing tuple in R2. • Modification of X2 in a referenced tuple R2 requires either propagating the modification to attributes Xi of all referencing tuples in Ri, assigning null values to attributes Xi of all referencing tuples in Ri, or deleting those tuples. A subset connection between a general relation Ri and a subset relation R2 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 R2 must be connected to one tuple in Ri. • Deletion of a tuple in Ri requires deletion of the connected tuple in R2. • Modification of Xi in a tuple of Ri requires either propagating the modification to attributes X2 of its connected tuple in R2 or deleting the tuple in Ri. 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: An 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 DBMS 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 MULTROL, 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 user-friendly 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 Enterprise Data Model Phase 1 Planning Phase 2 >j Requirements Formulation Requirement specifications Phase 3 System Design Information structure Phase 4 Implementation Logical database structure (DBMS processible) and prototype system Database management system characteristics Hardware/ software operating system characteristics Figure 3.1 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. An 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 the fields to be used, and showing the field name, 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, ABC 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 PMICS 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 PMICS 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 ABC 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 ABC 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 Personnel Finance Engineer Manager Manager Chief Accountant Procurement Manager Construction Manager Chief Estimator Purchasing Agent Project Manager Project Engineer Planning/Sch. Engineer Cost Engineer Estimator Accountant Superintendent Subcontractor Field Office Engineer Field Engineer Foreman Foreman Foreman Foreman Figure 4.1 ABC Company Organization Chart 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 numerous processes and functions. Models are therefore needed to 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 project information 2 home-office information 3 information for head-office 4 checks/progress statement 5 supervision 6 inquiry/submittals 7 confirmation/inspection 8 payment request 9 payment 10 quotations/sub information 11 instruction 12 supplier quotations 13 requisitions to supplier 14 plant quotations 15 requisitions to equipment rental companies Figure 4.2 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 Materials Stored Log User View 7 Monthly Reports User View 8 RFILog User View 9 Photographs Log User View 10 Punch Lists User View 11 Shop Drawing Log User View 12 Spare Parts Log User View 13 Data-in Modem to Home-office —*• Data-out Setup done by Project Manager/ Project Engineer Entry done by Field Office Engineer/ Secretary 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, accounting/financing, personnel management, engineering, estimating, 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 PERSONNEL/ FUNCTIO NS MATRIX M-2) Leqends: Responsibility Construction Manager Chief Engineer Procurement Manager Project Manager Project Engineer V 03 r. o & -E I c c II o_ uj Cost Engineer Estimator / QS Accountant Purchasing Agent Field Office Engineer Field Engineer Superintendents Foremen A=advisory/assist C=control responsibility D=data provisions E=execution H=handle R=review & analyze Responsibility Construction Manager Chief Engineer Procurement Manager Project Manager Project Engineer Cost Engineer Estimator / QS Accountant Purchasing Agent Field Office Engineer Field Engineer Superintendents Foremen Function/Activity Construction Manager Chief Engineer Procurement Manager Project Manager Project Engineer Cost Engineer Estimator / QS Accountant Purchasing Agent Field Office Engineer Field Engineer Superintendents Foremen Engineering project's engineering & design operations A C,H field and office engineering liaison H change orders, work orders, claims C H E design, drafting & shop drawing A C,H E drawing, papers and submittals C H A Estimating project's estimating C,A H E D quantity survey/estimate from drawings C E D,E subcontract estimate C H E Planning project's planning and scheduling R A C,A H E D project's schedule for resources c H E delivery schedules C H E detail schedules c E Quantity Surveying work measurement & payment estimates C H E valuations preparation C H E cost /value preparation c E final accounts agreements C H E Construction Management construction operations management C,H construction methods development c H D subcontractor selection and negotiation C,A A H D procurement of resources C,H A building production supervision C H architect/client liaison C,H A Procurement quotations requests/receipts C,A A D D D H technical assistance to subs/suppliers C,A A D negotiation with subs/suppliers c A H procurement contracts/specs approval C,A A E project's purchasing/expediting/inspection A C H A transportation & routes arrangements c H materials order C,A A E materials chase c H Project Management project completion C,H A project's progress/potential problem reports R C,R H,R E D D D project's progress & status reports R C,R H E progress photographs C H E Site Management field work, field survey, layout, etc. C A E subcontractors' progress check A punching lists completion C,R R A E subcontractors organization & coordination A A H Figure 4.4 Personnel/Functions Matrix (M-2) 92 PERSONNEL/ NFONEE DS MATR X (M-3) Infoneed By Construction Manager Chief Engineer-Procurement Manager Project Manager Project Engineer Planning/Scheduli ng Engineer Cost Engineer Estimator / QS Accountant Purchasing Agent Field Office Engines Field Engineer Superintendents Foremen Information Needs Chief Engineer-Procurement Manager Project Manager Project Engineer Planning/Scheduli ng Engineer Cost Engineer Estimator / QS Accountant Purchasing Agent Field Office Engines Field Engineer Superintendents Foremen Estimating 1 Quantity Surveying work item lists X X X bill of quantities (item & quantity) X X X X X X X X X X X X item cost X X X X X X X X cost summary X X X X X X X X X budget for a trade X X X X start and end dates of trades X X X X X X X X sub-bid due dates X X X X subcontractors/suppliers directory X X X bid receipt dates X X X X bid amounts (individual bidder/trade) X X X X bid comparison or summary X X X X bidder ranking lists X X X selected subcontractors/suppliers lists X X X X X X X X X progress measurements X X pay estimate number & date X monthly work progress X X X quantity of materials stored at sites X X X value of materials stored at sites X monthly valuation and report X X X X X subcontract estimate X X Planning approval time for submittals X X X X delivery time of an item X X X X time impact by change orders X X X revised completion date of project X X X X X X X X Engineering shop drawing lists X X X X X shop drawing submission due dates X X shop-drawing subcontractor/supplier X X X X status of shop drawings X X X X X lists of approved shop drawings X X X X X X shop drawing approval delay time X X requests for information (RFI) lists X X X RFI initiator's name X X contents of RFI X X date of RFI X X status of RFI X X X response time of RFI X X RFI responding person X outstanding RFI X X X change order lists X X X X X X X X X change order details X X X X X X X X X subcontractor affected by change order X X X X X change order value X X X X X X change order contract time impact X X X status of change orders X X X X X X revised total contract price X X X X revised completion date X X X X X X X X Figure 4.5 Personnel/InfoNeeds Matrix (M-3) ... contd... 93 PERSONNEL/ NFON EE DS MATRIX M-3) <u UI <u OJ • CO c CD nginee w c CO •D c o <u _c CD co c IO c 'cn CD CD c O < LU 91 CD CU C CD TD c Infonee Construct Manager Chief Eng Procurem Manager Project M: Project Er Planning/ ng Engins Cost Engi Estimator Accountai Purchasir Field Offic Field Eng Superinte Foremen Information Needs Procurement suppliers directory X X requirement dates of materials X X X X sample/approval requirements X X X X X approval time for submittals X X X X X delivery time of materials X X X X materials affected by change orders X X X status of materials on job site X X X X X quantity of materials required X X X X quantity of materials stored X X X supplier of materials X X lists of spare parts required X X X details of spare parts X X X quantity of spare parts required & delivered X X X quantity of spare parts balance X X X location of spare parts stored X X supplier of spare parts X X Project Management daily work progress X X X X equipment in use and idle on job site X X X X X X daily labor force on job site X X X X X daily weather condition X visitors on job site X X X X materials requirements lists of site photograph X X negative/roll number of photograph X X date and location of photograph X X purpose of photograph X X Site Management lists of defective work notice (DWN) X X X X details of defective work notice X X X X subcontractor responsible for DWN X X X X type of defects/rejection of DWN X X X X value of DWN X X punch lists and details Hern lists X X X X subcontractor responsible for punch list X X X X status of punch lists X X X X Administrative owners contact & phone X X architects/engineers contact & phone X X X X X subcontractors contact & phone X X X X X X suppliers contact & phone X X X X X X X correspondence in & out list X X X X X X X X X X X contents of a correspondence X X X X X X X X X X X origins of correspondence X X X X X X X X X X X dates of correspondence X X X X X X X X X X X file-location of correspondence X X X X X X X X X X ... contd... Figure 4.5 Personnel/InfoNeeds Matrix (M-3) 94 DOCUMENT TYPE / INFOR MATION CONTENTS IV ATRIX (M -4) Document Type Bill of Quantities Bid Summary Sheets Change Orders TL Correspondence TL Daily Site Report Defective Work TL Materials Stored TL Monthly Prog. Report Photographs TL Punch Lists TL Requests for Info TL Shop Drawing TL Spare Parts TL Company Directory Information Contents Bid Summary Sheets Change Orders TL Correspondence TL Daily Site Report Defective Work TL Materials Stored TL Monthly Prog. Report Photographs TL Punch Lists TL Requests for Info TL Shop Drawing TL Spare Parts TL Company Directory Estimating 1 Quantity Surveying work item lists X bill of quantities (item & quantity) X item cost X cost summary X budget for a trade X start and end dates of trades X sub-bid due dates X subcontractors/suppliers directory X bid receipt dates X bid amounts (individual bidder/trade) X bid comparison or summary X bidder ranking lists X selected subcontractors/suppliers lists X progress measurements X pay estimate number & date X monthly progress estimates X quantity of materials stored at sites X X value of materials stored at sites X X monthly valuation and report X subcontract estimate X Planning approval time for submittals X delivery time of an item X time impact by change orders X revised completion date of project X Engineering shop drawing lists X shop drawing submission due dates X shop-drawing subcontractor/supplier X status of shop drawings X lists of approved shop drawings X shop drawing approval delay time X requests for information (RFI) lists X RFI initiator's name X contents & dates of RFI X status ans response time of RFI X RFI responding person X outstanding RFIs X change order lists and details X subcontractor affected by change order X change order value X change order contract time impact X status of change orders X revised total contract price X revised completion date X Figure 4.6 Document Type/Information Contents Matrix (M-4) ... contd... 95 DOCUMENT TYPE / INFOR VIATION CONTENTS M ATRIX (M -4) Document Type Bill of Quantities Bid Summary Sheets Change Orders TL Correspondence TL Daily Site Report Defective Work TL Materials Stored TL Monthly Prog. Report Photographs TL Punch Lists TL Requests for Info TL Shop Drawing TL Spare Parts TL Company Directory information Contents Bid Summary Sheets Change Orders TL Correspondence TL Daily Site Report Defective Work TL Materials Stored TL Monthly Prog. Report Photographs TL Punch Lists TL Requests for Info TL Shop Drawing TL Spare Parts TL Company Directory Procurement suppliers directory X requirement date of materials X sample/approval requirements X approval time for submittals X delivery time of materials X materials affected by change orders X status of materials on job site X X quantity of materials required X quantity of materials stored X X supplier of materials X lists of spare parts required X details of spare parts X quantity of spare parts required/delivered X quantity of spare parts balance X location of spare parts stored X supplier of spare parts X Project Management daily work progress X equipment in use and idle on job site X daily labor force on job site X daily weather condition X visitors on job site X materials requirements X lists of site photograph X negative/roll number of photograph X date of photograph X location of photograph X purpose of photograph X Site Management lists of defective work notice (DWN) X details of defective work notice X subcontractor responsible for DWN X type of defects/rejection of DWN X value of DWN X punch lists and details item lists X sub responsible for punch list X status of punch lists X Administrative owners contact & phone X arch./engineers contact & phone X subcontractors contact & phone X suppliers contact & phone X correspondence in & out list X contents of a correspondence X origins & dates of correspondence X file-location of correspondence X ... contd... Figure 4.6 Document Type/Info Contents Matrix (M-4) 96 4.2 Database Design Overview As 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 of 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 of the proposed system. Section 4.3 deals with the basic files and their organization schemes. Section 4.4 covers the description of user views, identification of information requirements, identification of entities and their attributes including primary keys, description of relationships between entities, and presentation of 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 will be performed by the DBMS 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 a framework for 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 ASPE (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 94]. 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, HVAC, 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 WBS WBS •1 j ' i ri Specs Specs Specs Specs Section Section Section Section Figure 4.7 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 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 * 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., HVAC, 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\ 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 CO LU t I-< o OQ ID m LU CO 0 x UJ a. 1 o o o LU LU —J -3 o o cn a. CL 0-Q-o « i" 9 ~ § o 8 g c t co <2 in CD o o o o o O o o o o O o o o o o o o O o o o o o O o o o o O o o o o o o o O ••-» o o o o o O o m o o O o o m o o o o o o o" in in in cf m" in of of •«r *r co" in CN in •<*•" in o" o r— CN 00 o CN •<*• in 69 x— T— CO GO w 00 CO m CO a> 69 T— 69 CN 69 69 69 69 69 69 69 69 T— 69 T— E 69 69 69 69 69 69 ^ ~ Ite 69 o o o o o o O m o O o m o o O o O ct> o o o m o m O CO o in m co m o O o O u o o o 69 CM ^— o 69 m CO CO 69 69 69 o o o o o" Iff in 69 69 in 69 69 •<r in in 0. o CN 69 oo CO m CO TRAC ^— 69 69 T— 69 x— _l TRAC Uni: &9 69 </> OTA z o o .t: co CO E E c E CO E ca CD CO E E E CO CO CO CO C 3 cr o cr cr at CU a> cr cr cr 3 _i _i U CO -«-» in _i in in in in _j _i _i _i T_ o o o o CO O o O O O T— T— x— >> o o o o T O o m o in •<r in o T— CN CN T— 3 o UJ E cu £ o >z n a. cn o o c o o c o o CO > CO o X _ : w co OQ co ' JO 3 08 3 CD O ^ O C 3 CJ 3 Q CD co m co o E CO CL Q. E CO Q cn c oo X3 CD co li <* 8 D) C \£ a, ig O m ° Q > CC o "a el w p- ^ o o Q x: cu co XI II CO g — y .c o 5 E 5 co CT CD a o T3 ^ 8 *- _ V 0) C31 *-CO ' CO _ D) P O I S 2 8 Q. X il LU T-NCOtmiOMOOlOT-NCO^iniDSCO 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 WBSID. The Bill of Quantities (BoQ) is expressed in the following relations: BoQ (ProjectlD, Project Name, (WBSJD, 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 description of a work item as in WBS Quantity quantity of a work item Unit unit of measurement of a work item Unit Price price per unit of measurement Total Item Cost = 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. 110 UJ UJ X to < 3 g CQ o zz D _i D CD LU CO Z> o X LU or < i— o » O o <D cu "o o' CL rx < °-> io X CO r— iii UJ Q CD < 9 or h- CD — o CO o .9. o O CO LU h-llj LU Q O < Q Oi => - CO CD cn 1 » _ ro °> o 5 iS ° >>N & .t: s_ CO O x < L: ° cu "G L: cu co u c ^. J c ° O UJ O Difference $ 9,500 6,100 7,200 Bid Amount $ 155,500 158,900 157,800 Bid Rcving Date 10-Jan-95 10-Jan-95 10-Jan-95 Bid Due Date 10-Jan-95 10-Jan-95 10-Jan-95 Phone Number 888 9999 987 6543 876 5432 Bidder Name ACME Awal Engineering Super Aircond. Co. Serial No. ^ (M (O ^ U) CD S Difference $ 10,000 6,400 6,000 o,uuu Bid Amount $ 125,000 128,600 129,000 127,000 Bid Rcving Date 14- Jan-95 15- Jan-95 14- Jan-95 15- Jan-95 Bid Due Date 15-Jan-95 15-Jan-95 15-Jan-95 15-Jan-95 Phone Number 888 9999 987 6543 876 5432 765 4321 Bidder Name Globe Electricals Polar Electric Co. Power & Cable Co. Light & Power Cont Serial No. T- M O ^ If) CD N = o CO o . in oo \— UJ LU Q O < Q or => r- CD O O O Iff r>-I— Qj LU Q CD < o or => h- CQ o o o o o o o o O N* 0) CM CO" h-* CD" CO" o o o o io in io in 0)0)0)0) c c c c CO CO CO CQ ~> —> —> ~> in in uS in (N CN tN CN CO CO CO CO —3 ~i —3 —t in in in in CM CM CM CN T- CN o v in CD N Difference 5,000 6,500 3,600 10,500 Bid Amount $ 70,000 68,500 71,400 64,500 Bid Rcving Date 5-Jan-95 5-Jan-95 5-Jan-95 5-Jan-95 Bid Due Date 5-Jan-95 5-Jan-95 5-Jan-95 C Inn QC Phone Number 888 9999 987 6543 876 5432 765 4321 Bidder Name Ground Contracting Excavation Contracor Smith Earthwork Co. Excavation & Hauling Serial No. i- CN n ^ in co s 0) cu JC CO cr ca 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 total planned cost of work items included in a trade package StartDate start date of a trade activities EndDate completion date of a trade activities Bid a proposal to do all or a part of the work for a stipulated sum Bid Due Date date established for the receipt of bids for a trade Lowest Bid the lowest bid that complies with the bidding requirements Bidder the company that submits a bid BidderlD company identification number of a bidder Bidder Contact contact or responsible person of a bidder Bid Pkg. Sent date bid package sent to a bidder Bid Received date bid received from a bidder Bid Amount bid amount quoted by a bidder Difference 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 PTD may appear on many BoQ. A BoQ is related to a single PTD 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 PTD 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 tn n a « a S s o n1 co co a ^ > IS Q U-V II" If 111 e> o —I o 2 o W DC UJ a or o UJ O z < X o o o o # o o o CO d ci d d o CO o" LO in o 5 LO in" § 11 its IS9 O L; o !• !R " o a> c O o o o a: a: a. o. < £ o .g ° £ re co « *° II S e O O .N "c <D « o S 5 ?5 o < o: o > to IJsJ 13 B 3 tj 1 111 CD CD C '5 •C C O <D o o u at 2 B CO ~ E 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 in co "V io m 5 5 Q-ILS5< £ o ci £ LL LL 5 < LL LL. 5 < c6 c6 CD J2 I 6 d> « i (N O u. u_ 5 < 8888 8888 o 5 IO m CN IS*" (CJ W » W V» 888§ 8 8 8° o> Co in r-<N r*-" co" vt v* Vi vt o o o o o o o o dddd 8 8 8 S CN" in co" w » « » 5 s JS 115 *- CN CN CO dddd z z z z 2 5 5 5 o o o o i in in in i oi co co ! 4 S 5. . u. 5 < • d> UI o _l en c o RI (A CO T3 fl> CO c a> 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: COTL (ProjectlD, Project Name, (EngineerlD, Engr Name, (Engr Contact), Engr Phone), OCBP, ACO*, RCP*, CO%C*, OCCD, CD A*, RCCD*, (CMR#, Initiation Date, Current Status, Description, EsitmatValue, EngrEstimate, Final Value, Time Impact, D-RfAE, D-StS , D-BfS, D-RtAE, D-FS, CMR Days*, Disposition, File Ref. no.*, (SpecsID, Specs Section, (WBSJD, Item Description, (TradelD, Trade Name, (SubID, Sub Name, Sub Contact, Sub Phone)))))) 119 Table 4.4 Key Terms for Change Orders Tracking Log Key Terms Definitions OCBP Original contract bid price in $ ACO authorized change orders in $ RCP revised contract price in $ CO%C change order as % of the contract bid price OCCD original contract completion date CDA contract days added RCCD revised contract completion date CMR Contract Modification Request CMR# Contract Modification Request number Initiation Date date CMR initiated/introduced Description brief description of CMR EstimatValue contractor's estimate of CMR EngrEstimate engineer's estimate of CMR Final Value final settlement value Time Impact contract time impact in days D-RfAE date CMR received from architect/engineer (A/E) D-StS date sent to subcontractor D-BfS date back from subcontractor D-RtAE date returned to A/E D-FS date for final settlement CMR Days 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 PTD 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 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 the A/E. 121 employs. People receives^ Proj-Company Change Orders refers toN logs Project has Company Trade Specs Section L Change Orders Log Proj-Trade \*—<^ joins is in participates^ \^in Project TradeDetails 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 o _J o z CD >. I— 01 0 II lit c g ° o u. o Q-< < c HI Q. » oi S .§ 1 3 » o o bi oi o • s a ^ 1118! •El O CO c (A "D »- . o> .E = _j> ; I * * 5 iiSJ •o S • ° ts ^ ^ o CD CL. CL .E -S o. Q. ra -S 3 3 c a (A (A CP CO E E E E 5 I-•c ^ 8.1 £ jfj £ £ ra ra cn E 5"^ 1 § £ Is 8.« -g M S 8 1 5 3 CO £ £ £ 2 o <5 0) oi .H .= .E o. o. ra ra a. a. c C 3 3 « O M « E E E E P 9 P P t: ii t: i: ° J2 t o _l _l _l _l O U- _l -J —i -J w o £ w Q woo CC f| t3 CO gSofcoawcc g<tgQggogg is8 03 g o 5 i*i*f£OcKo;£f£c£a: Ly^WWwwWWwiiJ D_D_ — XQ_Q_ — — Q_Q_ Q.Q.OOQ.Q.OOQ.D. C0COUJ<c/]COllJLUt0C/) r§ n ill < < LU g o >• .2 6 ft. _S"5 2 O (I • ' cJ I" I I ti 1 u n o 5 o CO C C 3 O < < LU LU O Q o - a> . i- oi | • E ti . S E t , _ clggggggggg Q i CD o fc "cO O) o —I c IS o C8 cu o c CU •o c o a. (0 2 k> o o t 5 cu > ra 10 •«* 2! 3 11 LU O LU Q O Q. CO LU 0. Q_ O O =3 CQ UJ CO 0 1 LU CC < o w 35 o o cu cu E" 2" et ct o CD tn o cu CO CD o 'ro c c o LU O >>N O >~ CO <-> X < CD c O CD o CD CO .E J= O) c c o LU O Date Received 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 Date of Corresp. 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 01 0 CC z 40.1 1.1 2.1 50.1 41.2 1.2 1.3 51.1 42.1 Corr. in No. i-iMOVincBNoooiO'-cNO^incDNiiooio 124 .fc.fc.fc.fc «Q £ £ g < 5 LU < K co 5 § et o °. 5 | 9= 5 o 1 « a g < cc o g S cc cc °= LU UJ O CC CC CC EC zz\ — Ul I— — — LU LU - - ^ X 0. 0. — — O 0. 0- o o CC 3 3 Z Z Q. Q. — Q_ 0. O 3 3 Z W CO LU a. a. a. a. 3 3 < co co m m w co o -i O) _c IS o 2 cu o c 0> •a c o a </> £ L_ o o 0 O z O 3 OQ UJ o LU a o QL CO 111 CC cc o o LU W z> 0 1 LU CC I >-O "-o o a> a> CL CL o _c ci) CD c .c cu cu <5 "o> § LU O ±i > CD o >< < cu t> CU CO c o •S * at o t r-O O O LU O w C E 2 o u_ u CU O cc z O E w 5 w- d . | - 1 « 9 E g _- .3 E P Igggggggxg <<<<<<<<<< ffl rfl rn rp fp m m ffl ffl o>0)wC7>o>o>a>Cipcncn cccccccccc (onnmnnnnion CN N P) r f-t cu > JQ m • T~ i-NnvinioNooO'-iNnt^iDscooio 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: CTL (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# project's correspondence entry serial number Reference# reference number of a correspondence (i.e. sender's reference number as appears on the correspondence) CorrDate original date of a correspondence Date Sent date of mailing DateRcvd date correspondence received Follow-up follow up required? Participant Type type of the project participant, e.g., engineers, supplier Keyword 1 first keyword describing the subject Keyword2 second keyword describing the subject 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 Figure 4.16 E/R Diagram for Correspondence Tracking Log 4.4.5 View 5: Daily 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 Report No. Date: 12 15-Jan-95 Project: CITY WAREHOUSE BUILDING Day: M T W T F S S Project* 1 Weather Sunny Cloudy Rain Snowy Owner: City Warehouse Temp. 0-5 5-15 15-20 20-30 Engineer: XYZ Engineering, Inc. Wind Still Moder. High Contractor: ABC Contracting Humidity Dry Moder. Humid AVERAGE FIELD FORCE Name of Contractor ABC Contracting Awal Engineering Ready Mix Supply Smith Steel Non-manual Manual 5 10 12 8 Remarks Supervision & Q.C. HVAC & Ducts Forms & Concrete Steel Erection Total Force 35 VISITORS Time 9:30 AM 11:00 AM Name Ferguson, R. Brannon, W. Representing XYZ XYZ Remarks Jobsite visitation Jobsite visitation EQUIPMENT AT THE SITE: CONSTRUCTION ACTIVITIES: MATERIAL NEEDS: Prepared By: Signature: Figure 4.17 View-5 Daily Site Report 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), (Daily Report#, Visitorl, Visitor2, VisitTimel, VisitTime2, Companyl, 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 date of a report Report# project's serial number of a report Prepared by ID PeoplelD of the person making report Day ID same as Day ID in Day table FFC1 field force contractor-1 CoID FFC2 field force contractor-2 CoID FFC3 field force contractor-3 CoJLD FFC4 field force contractor-4 CoID NMFF1 non-manual field force of FFC1 NMFF2 non-manual field force of FFC2 NMFF3 non-manual field force of FFC3 NMFF4 non-manual field force of FFC4 MFF1 manual field force of FFC 1 MFF2 manual field force of FFC2 MFF3 manual field force of FFC3 MFF4 manual field force of FFC4 lob 1 j ob/trade name of FFC 1 Job2 job/trade name of FFC2 Job3 job/trade name of FFC3 Job4 job/trade name of FFC4 Visitor 1 name of visitor-1 Visitor2 name of visitor-2 VisitTimel time of visit of visitor-1 VisitTime2 time of visit of visitor-2 Companyl name of the visitor-l's company Company2 name of the visitor-2's company Purpose 1 purpose of the visitor-1' s visit Purpose2 purpose of the visitor-2's visit EquipUse equipment in use at the project Equipldle equipment idle at the project Activities activities in progress at the site New Act new activities started at the site Act Compl activities completed at the site Material Needs 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 o zt o o i-< o H- i O 3 z 1 LU W o x LLI CC < O LU > H O LU LL. LU Q O T-0 5> D) S E .E 1 $ H (1) .E £ g UJ O o L: o tin LLI O So O 2 •fi 1.1 O S <» O CJ Q/ ^- <0 $ IS § .EEs ra *: > LLI LLI ra cc Q rrirOLOWNCOrO) u^inioinin^^LnLjQLr) CnnCflCnCnwCAwwO) c c c c c= cccccccccc LUUJUJIIJLJUJUJLUUJUJ 3 3 3 3 3 •c- CM <N g S5 un uj in ^- T- CM CN So o o o U) IA Ul O c~ co •*»• co 3 3 3 3 3 3 3 3 3 rZc^ciiAS^'n^^S T— CN CO o. E E o o c 5 c — 33 o o c * 3-2 K> ° " — -a 8 S ai si 11 •s o 0) = o o o T3 O 1 a| c to S o> E S .5 n i-"O _L X> .3- « °> °" S C» TJ £ C 3 •* O TJ <B TJ II •5 2> o o o O O c o Dl CO -si* 1 § OQ-U5Q."c0LLJcj o o) .9-oooooooooo ONOOOOOdJO J) cNco^coin^i'^r^-T-'*-cocoiocoinincNeNcoh. 00--00.-0000 inioininininintotnin c»o)cncno)0)0)0)u)0) 3 3 3 3 3 c6cNiouSci«lA<i^l£ x- CN CO 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: DWNTL (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, (WBSID, Item Description, (TradelD, Trade Name, (SubID, Sub Name, Sub Contact, Sub Phone)))))) Table 4.7 Key Terms for DWN Tracking Log Key Terms Definitions DWN# project's DWN entry serial number NoticeDate DWN issue date DateRcvd date DWN received Estimat Value estimated value of the DWN item DateReinsp date the DWN item reinspected D-NoCR date no-objection certificate received TDfC total days for correction UUIAUhUWJLUJUUUIAfkUit^^ 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 DWN. A DWN can be identified by both ProjectlD and DWN# (Table C.8, Appendix C). A DWN 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 o o o o LO LO CO CO Tt of Tf LO CM CM </> </» a Q UI STO STOI Q > tn UI i— s s o UI s to Lu z a: tt O 3 tt U o o o o z o I-Q LU 0. O I-co CO _l < 0-LU 3 m LU co ID 0 1 LU a: < ¥ * o o CD CD o" o" k_ i Q. Q_ J2> cn .£ c i— CD U a) co °> 5 c o LU O CD CO LJ L: ° .. co tj i_ co co g cn g JE c o O LU O z> z S) 2 ui s m i-< 5 </> o Q UI UI 1| z o o i-u < ^ 2 o < o i- 2 Ui ra LU Q >- i— <s < in Q Q_ LU UI O 88888888853 in o_ CN o_ o o> o. p. in i-" T~ cj) r--~ oo" CN -r-* CN 0) o> CO CN CO (N CO T- T-^ft" (/^ oooooooooo OOOOOLOLOOOlfi LOOCOOOOmOO-? co" co" cn co" co" TT" to" co" co" co" &t tf^ O i O i O i 8888888 o o o o o o o LO LO LO LO LO LO LO LO LO Cn CO f4 C)) L4 Q cMc^c^cococo^fcoTrTr ^^T-CMCNCMCOCSCOCO CB CO ~ p p c ra ra o _ CO C CO CO ^ o o 2 J? co <» ra u u 2 •- o_ O 2. ra ra 2 V fp w w w O O O * o a £ ra ••= o S ra $ w ra ; . » o s £. ra \_ z. 3 ra m n 5; -tt CO CD o 2g .•2 * 5 CO CO O o) ra 1 _ a. S 2 o 2 2 w </> g to 8 0 tS CO CD « O O " o O " O in » C C O C 111 CO <D CD -ti CO Z LL CO U. LL CO LL. O) o _i c !Z o 5 H •O £ o to w ra cu « CD > CM 2 3 O) U-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: MSTL (ProjectlD. Project Name, (EngineerlD, Engr Name, (Engr Contact), Engr Phone), (Mat#, Item Description, (Loc_ID, Location Stored), Loc-Installed, Original Value, Pay Est#, Pay Est Date, % Installed, Amount Consumed To-date*, Amount Currently Stored*, File Ref. no.*, (SpecsID, Specs Section, (WBSJD, Item Description, (TradelD, Trade Name, (SupplierlD, Supplier Name, Supplier Contact, Supplier Phone))) 141 Table 4.8 Key Terms for Materials Stored Tracking Log Key Terms Definitions Mat# project's materials stored entry serial number Item Description description of the materials stored as in the WBS Location Stored location where the materials stored Loc-Installed location where the materials installed Original Value total value of the materials when initially stored Pay Est# monthly payment estimate number Pay Est Date monthly payment estimate date % Installed 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). An MS is related to a single storage location. A storage location may appear on many MSL. An MS 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 employs: Company Proj-Company U People prepares" Project Materials Stored has refers tcr stores Trade Proj-Trade joins Specs Section L Storage Location logs Materials Stored Log is in Project TradeDetails ^corresponds to WBS relates ti 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 z D _j m in to Z> o I LU < >-o * H i-O o LU LU -3 —> o o cr a: a. a. ID CO CO CM co CO ,_ CO o .§. ai CO CO LU a c ii <U CO M co in CL LU UJ ui UJ a. >. z is a. oooooooo ooouooooo ooocNomoo) 8" o" io" r-" g" r-~ of T-" CO CN CN 00 CD Cft i-v/ v/ vy V7 v7 v/ o o a in co r~-CN •tf T- CN Cft Vi o IO CN o" CN Vi oopinpoop CD^CNCNCNCOCNCN 8SS88SS8 ONNOOMSCO O IO 10 S Q CN T" T- T- I <ft </> <ft <ft <ft < CO Vi Vi O O O If) CD CN T~ T- CN Vi Vi o CN Vi oinmooininp CNCN-r-CNi-CNf-CN o o o o oooooooooooooooooo OOQQQOOOOOOOOQOOOO o o o 5 5 o o ui o o o_ o_ o_ in o" uf in" in" O N • — T- (ft Vi Vi NcogNTfinc»>-^iDcoincocDion Vi Vi Vi  Vi o o o 888 o" If) UI o r» CN <ft Vi O O Q i in o in i (ft M T-O O O I O If) If) • in co co ' - eft eft (ft (ft o_ o_ o S co S (ft T- <ft c/) c/3 E E c E (/) : i 3 cr o cr • —i —i o in in —i §. E w w w w w w -J -i -i -i «- o o o o co ° 8 8 £ CN O O O •>- r- «-JO m jo 2 3 <* 3 « o ^ o 2 fe c § 8 § 8 =5 O co m co ojicwgl * 5 -8 5 i-NlO^iniONCOOlO'-tMn^lDIOSO 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: MPR (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# project's sequential payment estimate number PayEst Date date of the payment estimate Item Description item description as in WBS Quantity total quantity of the item Unit measurement unit of the item as in WBS Unit Price unit price of the item in $ Item Total Quantity x Unit Price This Qntty percentage of the work item done for the month This Earnings Quantity x Unit Price x This Qntty To-date Qntty percentage of the work item done to-date To-date Earnings 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 CIO, 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 _i o o 2 O t-o X Q_ > > s o fl> .9 -J -i O Q. o E 9 9 <S CD Z> CO LU CO Z) o LU < O T_ w » o o CD CD CL CL CD .i J= (5 °> c -2 C O > LU O >>N O ~ >- CQ O X < CD "t> CD CO CD c > O) c < <= ° OHIO TJ o g o CD "o CD_ to | LU Q « i- c O) o O 2 s * X o 0. Q. 2 a E " re C 6 E o -0. co O D)Z o o £ 6 o o o d £ z o 2 _ O o> ti 1 CO <M a. -o E co 6 .£ o co o o O Xf XI 3 C Z O (A E « -c fe §• CO 3 _o JO CO O hllLOlOU 0-t?j Q: LU < w m o < > o ,2 < LU o LU cc o r-OQSOQS _i < —' o LU — . O CO O S LU s 3 LU 0. LU < < < < o o o o O CO o o o o cn o 5 5 5 < < 0-tn tn in io in in m 0) o> o> o> cn o> o> c c c c c c c (0 CO 10 ffi CQ CO (0 "7 ~* "° "7 "? ~? ~? LO LTS ci oS oS a> Q CN <N CN CN CN CN CO | | I5 8 5 c c c c ^ •o g b o s g -g <0 u- cc LL O u. co LU 2 o _ 2 8 h LL CO LL LL O) o _l c o e a E a> o o a. cn • a> > CM O) Lu 5 5 2 5 5 S <<<<<< CN TT O) T- TT IO CO IO CD CO T LO CO T-MO^KllDStOdlO 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: PTL (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 Keyword2 Caption 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 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 of 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 of a contract to indicate all work or corrective action remaining before acceptance of the work. The quantity of punch list items may vary from project to project. From a contractor's standpoint, the punch list is the final doorway to project completion and acceptance. In order to ensure prompt completion of this critical close-out requirement, a definitive method of recording, tracking and reporting punch list items is a necessity of the project. A Punch List (Figure B-10, Appendix-B) is a useful means of recording, tracking and reporting punch list items. It contains the date each punch list item originated, an accurate description of the work to be done, the person generating the item, an estimated dollar value of 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 m LU co z> o X LU rr < 3 £ X o z 0_ o o o .<u c? 'c? k_ I— CL CL v 8 o o -g II II II CD O UJ 3 dl 5 O OJ 03 r c h £ c to c o > UJ U yNO £ > CO o x < JE c 0 O LU O OJ co o o TO CD •o cu co TJ "8 Q § TJ If 0) % 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) cccccccccc UJIlJUJLULUUJLULUUJLLl <7>00>0)0)0)0)0)0)0) I I I I t I I I I I >*>t>t>i>i>s>.><.>*:>. I I I I I I I I I I (0(000000000000000 (0(0(1)10(0(0(010(0(0 uSiAc6cDcir>-rii>-c6oi umdivvniiuttvai QJCOCOCUCUCUCUCUCUCU c c c c c c c c c c. cocococncocococococo cccccccccc uJUJUJuJLJLlJLLJUJUJLLJ ifioinLnLnLOinininir) C)cn0)O>c7)O)O)c>o)C) 0.0.0.0.0.0.0-0.0.0. <<<<<<<<<:<: ySiAi/SuSuSinuSioioLo NNNNNfMCMCMCMN CO CO c C •5 u o j= S 8 J2 cD)- -. 8 c <= ca c -£ •= o ~ o £ * o C o u a; 8 •§) -2 .8 -2 8 5 UJ ui c p 8| 55 o o — O a> CJ cu •o "ra O -5 O -2 O < < O < O CO CO < < 8 s ca °-CJJ CD • CO .£ •= 2 2. E 8 .Si CO _ -*2 & -= 8* o c ca g 9- 2 ^ o CD J= 1 § o o .8 S E § 8 o c n .cp cu C m JB J3 J= = CO c CU "a ro c -C g) § 2 g % 8 I ^ "2 1 <" 0^0 co S co a) 3 a; O W O TJ a> o 0) t ° E 3 goo D) Q. J-— CO CO co +-* o C CO c = D ra co TJ* ra U < CO OIUUJIOUIO11151 <<<<<<<<<< 88888.8.8.8.88 0000000000 (0 o c 3 0. 5 a> > (N. CN •<* 2 3 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, (WBSJD, Item Description, (TradelD, Trade Name, (SubID, Sub Name, Sub Contact, Sub Phone)))) Table 4.11 Key Terms for Punch Lists Key Terms Definitions PL# project's punch list entry serial number Punch List Item item description of the punch list Facility/Area facility or area of the punch list item Date Identified date noticed or identified by the A/E Date Completed date the item completed for re-inspection Date Checked date rechecked or inspected by the A/E Checked By 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 id,, RFI# and description, related specs section and drawing number, dates of transaction, response, response by, and comments. 158 I- I- Q Q. V A IS Q 9 O C — "8 S > 15 ra <U m a: • 2 S CD o _l CD Z LL o r-< or o or o u. CO co LU a LU tt c TJ '5 CQ CD to 3 O JZ cu o • *J 3*3 u o CU cu "o "o CL CL o _c 03 c CU C +3 CO *bZ u 3 <U CO O « h •c c c £ ra o co c O O X ^ L; S CU O CU CO .£ £= c o LU O cu •2 CQ CS CS S a oooocnocnoocoocoT-33 222333222 <<<oo<<<ooo Loincnioii2y2toioLnLnLO CqCoCnCnCaOlcnCflCnOlCn 1 1 1 1 1 1 1 1 1 1 • ccccccccccc BBBBBBBBBBIB C^SSSSPSL^CNTI-CO ^-I-^^^NCSNNCMCM cninuiinininioinisinin a)c^czocno)cAcj>a)cnc7)C7) ccccccccccc cacacaracocacocococaca cocoiAcocnciciiAcN^co ^^^T-T-CMCMCMCMCMN mininininuiininuiinm oicncnoicncflcpcxcncncf) ccccccccccc cococococacocacocacocti —3 —3 —3 —3 3 5 3 3 3 3 ~^3 i^uScAciiicicNCNcori <u tu "5. « E z 8 a c^-03 c ca "2 co 2 -a cn co «> "ffi a o ^8 cn O cn co to *: 0) t» £ c J2 3 cr "ffi ]? 8 co J IH r\ •— C TJ 1 8 ± CO co Q o o Q Q 't5 o JS 812 JS J2 $ O O 0 ^ 1 % 2 C Q. £ 3 3 2:£lc! H e I C to o i o O > Q 5 CJ> CN CO CN ±66 ?v2 2 0 o o o «- T-OOOOOOOOOOC) 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 ccccccccccc COCOCOCOCOtOtStOfOCOfO cfSifScAciSicicNCMtAri T-NlO^intOMD 0)Ot-CNC0Tri0CDI^000)Ox-CN cn o _i c !2 o c o CQ E c 4) a> > cn CN cn iZ 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 RFI Tracking Log Key Terms Definitions RFI# project's RFI entry serial number Initiated By PeoplelD of the initiator Initiator ID Company identification number of the initiator Initiator Type Participant Type of the initiator company Initiation Date date of RFI initiation Drawing* drawing number for reference RFI Description short description of the RFI D-RfS date received from subcontractor D-StAE date sent to architect/engineer (A/E) D-RBfAE date returned back from A/E D-SBtS date sent back to subcontractor Response By PeoplelD of the person who responded the RFI Total Days 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. An RFI log lists each RFI. An 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 proj-company, 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 shop-drawing 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 Jp CQ O -CO ro c < > Q- 0) (U CO I— o -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 < .. * r- r— O O LU LU O O CC cc Q_ Q_ S C f I |i 1° ill r~ co to a i •6 8 ; °-o (J 13 A .a Q 15 3 (0 CD CO CD c c c c (0 (0 to to ^ ^ 3 C C c c to ro ro to g OS O IT) O Q O < o 3 T i CO CN i ro co CN < O O O i Copies back to Sub CN -^r CN T CO £ a view Status Date Sent back to Sub/Supp 16-Jan-95 19-Jan-95 19-Jan-95 Log Submittal/Re Date Back from Engineer 15-Jan-95 18-Jan-95 18-Jan-95 Tracking Submittal/Re Date Sent to Engineer 08-Jan-95 10-Jan-95 10-Jan-95 bmittals 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, shop-drawing 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.*, (WBSJD, (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 WBSTD 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, PTD, 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 O O to I-< CL LU < CL 00 CD Z LU W 0 1 UJ cc I O T-o o cu OJ o"o a! CL ..mo O UJ O 3 • 1 S.2 CO Q- 5) 55 & 8) 8) 05 55 •tt -a =tt -tt co c c: c E E E . ST 5T ST cr a- d ci d to c cr cr E E E . . ±i ±i cr cr cr O o c c tO W tO C C 3 3 O O Q i ' T- CN ri < i O CN CN CN CN i CO f— CD«U)WWWtflC»«O)C0 o o o o o 2 o o o o o <DQ)CUCUCUCU<UCL)OOCL) _ rjj — — s w s s Li CJ F O '1% i CO OJ ! "C CO ra <j3 i N 8 8 S COCO^IQCNCO<J5T-CNIJOCD irir^cocococoQ^^^in OOOOOOO-«--"-->-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, equipment description, specs section, 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, Eq Description, Qntty Required, Qntty Dlvd, Unit, (LocfD, 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 Eq 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. employs. Company Proj-Company People is part prepares^ Project Spare Parts List refers tcr stores logs identifies* app fror Trade has "N—J Proj-Trade joins Specs Section k Spare Parts Log is in participates^ \^in Project TradeDetails ^corresponds to WBS relates ti Storage Location 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 involve! Company has .bids or Bids ibmiti semployj People Project-Trade . enters ^details Bid Summary Sheet identifies* Project TradeDetails Winning Bids/ Subcontractors joins Trade Shop Drawing Log Change Order Log Defective Work Log Punch List Log Materials Stored Log Spare Parts Log Monthly Progress Report Storage Location 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 entity-relationship (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 SDM 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 Ownership Reference Subset Symbol Insert/delete Constraints Emp. 1—X{ Skills I need employee/skills goes with employee employee needs department/ department stays Emp. > 1 Dept Emp. —3 Salesman salesman must be employee/salesman goes with employee The basic difference between an entity-relationship model (ERM) and SDM 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 ERM and SDM can be explained as follows: • ERM emphasizes the relationships between entities. SDM formalizes the relationships between entities. • ERM is expressed as a degree of relationship between entities (1:1, 1:M, or M:l). SDM is expressed as a connection between entities (ownership, reference, or subset). • ERM distinguishes between entities and relationships. SDM does not make any major distinctions between entities and relationships; a relationship is regarded as an entity. • ERM does not provide constraints and integrity details precisely. SDM provides constraints and the integrity rules. • ERM 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. • ERM 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. SDM 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. Each sub-section is divided into three parts. The first part presents a set of entities or relations derived from the relations, as expressed in sections 4.4.1 to 4.4.13, for each user view. Each 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 (SDM) diagram. 4.5.1 View 1: Bill 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 Trade Project ItemDetails ^7 WBS Bill of Quantities Figure 4.36 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, Bid Due Date) Bids (ProjectID. TradelD. BidderlD. Bidder Name, Bidder Phone, Bid Pkg. Sent, Bid Received, Bid Amount, Remarks) Bidder Type (ProjectID. TradelD. BidderlD. Bidder Type) Bidder Contact (ProiectlD. TradelD. BidderlD. 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, Bid Due Date, SubID (Co_ID), Subcontract Amount) Bids (ProjectID. TradelD. BidderlD (=Co_TD), Bid Pkg. Sent, Bid Received, Bid Amount, Remarks) Bidder (BidderlD. Bidder Name, TypeTD, PeoplelD, Phone), or Company (Co ID. CoName, TypelD, Contact (=PeopleID), Phone) People (PeoplelD. Name, Co_ID) 179 Participant Type (TypelD. Participant Type) The system control derived from the semantics required by the users is shown in Fig. 4.37. People r A. Company Participant Type Project TradeDetails > < 1 Project Trade > K N< Bids I r Bid Summary Sheet Figure 4.37 Structural Model Diagram for Bid Summary Sheet 4.5.3 View 3: Change Orders Tracking Log The initial relations derived from the entity-relationship model, as shown in section 4.4.3, are as follows: Project (ProjectlD. Project Name, OCBP, OCCD) ProjectEngr (ProjectlD. EngineerfD, Engr Name, Engr Phone) EngrContact (EngineerlD. Engr Contact (=PeopleID)) 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, WBSID) CMRtrade (ProiectlD. CMR#, SpecsID, WBS_ID, TradelD, Trade Name) CMRsub (ProjectID. CMR#. SpecsID, WBSID, 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, Engineer© (=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, WBSID) WBS (WBS ID. Item Description, TradelD) Trade (TradelD. Trade Name) Project TradeDetails (ProjectID. TradelD. SublD (=Co_ID)) Company (Co_ID. Co_Name, TypelD, Contact 1 (=PeopleID), Contact2 (=PeopleID), Phone) People (PeoplelD. Name, CoJD) Participant Type (TypelD. Participant Type) 181 The system control derived from the semantics required by the users is shown in Fig. 4.38. People I A-Company ix: Participant Type Project TradeDetails — Project Trade Change Order _UJ WBS Change Order Log Specs Section Figure 4.38 Structural Model Diagram for Change Orders Tracking Log 4.5.4 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#. From Company (=TD), To Company (=ID)) From/toType (ProjectlD. Corr#. From Type (=ID), To Type (=)D)) The local data model or normalized relations for the above view are as follows: Project (ProjectlD. Project Name) Correspondence (ProjectlD. Corr#. From People (=PeopleTD), To People (=PeopleID), Reference.^, CorrDate, Date Sent, Date Rcvd, Keyword One, Keyword Two, Follow up, Notes) People (PeoplelD. Name, Co_ID, 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. 4.39. Company ~rn— A People Participant Type Project Correspondence Correspondence Log Figure 4.39 Structural Model Diagram for Correspondence Tracking Log 183 4.5.5 View 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, TypelD, Participant Type) ReportDay (ProjectID. Date. Day ID, Day) Weather (ProjectID. Date. 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)) Daily Events (ProjectID. Date. Daily Report#, Visitorl, Visitor2, VisitTimel, VisitTime2, Companyl, Company2, Purposel, Purpose2, EquipUse, Equipldle, Activities, New Act, Act Compl, Material Needs) DailySub (ProjectID. Date. FFC1, FFC2, FFC3, FFC4, NMFF1, NMFF2, NMFF3, NMFF4, MFF1, MFF2, MFF3, MFF4, Jobl, Job2, Job3, Job4) DailyReporter (ProjectID. Date. Prepared by ID, Prepared by) The local data model or normalized relations for the above view are as follows: Project (ProjectID. Project Name, EngineerlD (=Co_ID), OwnerlD (=Co_ID)) 184 Weather (ProjectlD. 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) Daily Events (ProjectlD. 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) People (PeoplelD. Name, CoID, Phone) Company (CoID. CoName, 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 Daily Events _A_ Company r Daily Site Report Tn Participant Type Daily Weather N Day Figure 4.40 Structural Model Diagram for Daily Site Report 185 4.5.6 View 6: Defective Work Notifications Tracking 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 By, D-NoCR, Comments) DWNspecs (ProiectlD. DWN#. SpecsID, Specs Section) DWNitem (ProiectlD. DWN#. SpecsID, WBSID) DWNtrade (ProjectID. DWN#. SpecsID, WBS_1D, TradelD, Trade Name) DWNsub (ProjectID. DWNtf. SpecsID, WBSID, 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, Reinspected By, D-NoCR, Comments) Specs Section (SpecsID. Specs Section, WBSID) WBS (WBS ID. Item Description, TradelD) 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, CoID) 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 Project Trade V r Participant Type Defective Work WBS LJ T 1 DWN Log Specs Section Figure 4.41 Structural Model Diagram for DWN Tracking log 187 4.5.7 View 7: Materials Stored Tracking Log 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#, LocID, Location Stored) MatSpecs (ProjectID. Mat#, SpecsID, Specs Section) Matltem (ProjectID. Mat#, SpecsID, WBSJD) MatTrade (ProjectID. Mat#. SpecsID, WBSID, TradelD, Trade Name) MatSub (ProjectID. Mat#, SpecsID, WBSID, 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, Loc_ID, Item Description, Loc-Installed, Original Value, Pay Est#, Pay Est Date, % Installed) Storage Location (LocID. Location name, Address) Specs Section (SpecsID. Specs Section, WBSJLD) 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. People Company I Project TradeDetails —V -ed Project Participant Type Trade WBS Materials Stored —V Storage Location Specs Section UJ Materials Stored Log Figure 4.42 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#. WBSID, Item Description, Quantity, Unit, Unit Price) ProgressTrade (PjcjectJD, Pav Est#. WBSJD, TradelD, Trade Name) ProgressSub (ProjectlD, Pav Est#. WBSID, 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, To-date 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, CoID) Participant Type (TypelD. Participant Type) The system control derived from the semantics required by the users is shown in Fig. 4.43. People I Project TradeDetails A. Company —v— Trade ^ Project Participant Type Project ItemDetails b->r< A WBS UJ Monthly Progress Report Figure 4.43 Structural Model Diagram for Monthly Progress Report 4.5.9 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, Contact 1 (=PeopleID), Contact2 (=PeopleID), Phone) People (PeoplelD. Name, CoID) Participant Type (TypelD. Participant Type) Photo Type (Photo TypelD. Photo Type) 192 The system control derived from the semantics required by the users is shown in Fig. 4.44. People I Photograph Log Ac company Project Participant Type Photo Type «H" Photograph Figure 4.44 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, WBSID) 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, Contactl (=PeopleID), Contact2 (=PeopleID), Phone) People (PeoplelD. Name, CoID) Participant Type (TypelD. Participant Type) 194 The system control derived from the semantics required by the users is shown in Fig. 4.45. People IX Project TradeDetails —sr~ Company T Project Participant Type Trade Punch Lists Punch Lists Log I A_ WBS Specs Section Figure 4.45 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 By (=PeopleID), Initiator ID (=Co_ID)) RFIcompanyType (ProjectID. RFI#. Initiated By (=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#, RFI Description, D-RfS, D-StAE, D-RBfAE, D-SBtS) Company (CoID. Co_Name, TypelD, Contact 1 (=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.46. People n -^Requests for Information (RFI) Company ^ Projects Participant Type RFI Log Figure 4.46 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#. WBSID, TradelD, Trade Name) SDsub (ProjectlD. Shop Drwg#. WBSID, 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#. WBSID, 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, CoID) 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, Eq Description, Qntty Required, Qntty Dlvd, Unit) SPstorage (ProjectlD. SP#, Loc_ID, Location Stored) SPspecs (ProiectlD. SP#, SpecsID, Specs Section) SPitem (ProiectlD. SP#, SpecsID, WBSJD) SPtrade (ProiectlD. SP#, SpecsID, WBS_ID, TradelD, Trade Name) SPsub (ProjectlD. SPJ, SpecsID, WBS_ID, 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, LocID, SP Description, Eq Description, Qntty Required, Qntty Dlvd, Unit, Status) Specs Section (SpecsID. Specs Section, WBSID) WBS (WBS ID. Item Description, TradelD) 199 Trade (TradelD. Trade Name) Project TradeDetails (ProjectID. TradelD. SublD (=Co_ID)) Company (CoJLD. Co_Name, TypelD, Contact 1 (=PeopleID)] Contact2 (=PeopleID), Phone) People (PeoplelD. Name, CoID) 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. People XT A_ Project TradeDetails —v Company I -<d Project Participant Type Trade WBS Spare Parts ^_ V Storage Location _A_ Specs Section Spare Parts Log Figure 4.48 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. Specs Section Storage Location A_ Materials Stored .A. DefectiveWork Change Order A_ -A Spare Parts —5K— WBS Bill of Quantities Tn -A-Project ItemDetails .A PunchLists — ShopDrawing A_ Monthly Progress PROJECT v— Correspondence v— Photograph —V V RFI Daily Weather I Project TradeDetails v v People Photo Type \ Participant Type Figure 4.49 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 DBMS is not a factor in designing a conceptual model, but designing a logical model is dependent on the DBMS to be used. Considering a DBMS 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. An 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 DBMS 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 Reinspected, Reinspected By, D-NoCR, Comments) 204 Materials Stored (ProjectlD. Mat#. SpecsID, LocID, Item Description, Loc-Installed, Original Value, Pay Est#, Pay Est Date, % Installed) Monthly Progress (ProjectlD. Pav Est#. WBSID, PayEst Date, This Qntty, To-date Qntty) Participant Type (TypelD. Participant Type) People (PeoplelD. Last Name, First Name, Co_ID, Title, Address, City, Province, Postal Code, Phone, Fax) Photograph (ProjectlD. Photo#. Photo TypelD, Photgr ID (=PeopleID), Roll#, Negative#, Date Taken, Time of 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), OwnerlD (=Co_K>), ManagerlD (=PeopleID), OCBP, OCCD, 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#. WBSID, 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, Eq Description, Qntty Required, Qntty Dlvd, Unit, Status) Specs Section (SpecsID. WBSID, Specs Section, Section Title, Division, Division Title) Storage Location (LocID. Location Name, Address) Trade (TradelD. Trade Name, Description) WBS (WBS ID. Item Description, TradelD, Unit) 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, WBS ID. Quantity, Unit Price, % Markup, Delivery/Action Date, Delivery Days, Approval Days, ShopDrawing (Yes/No), MatSample (Yes/No), Catalog (Yes/No), Misc. (Yes/No), Misc Submittals, As-Built Drawing (Yes/No)) Project TradeDetails (ProjectlD. TradelD. Budget, StartDate, EndDate, Bid Due Date, SublD (CoID), Subcontract Amount) Proi-Companv(ProjecfID. CoID) Proj-Trade (ProjectlD. TradelD) 4.6.3 Entity Cross-Reference Matrix As 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 will appear as a row-heading and each basic entity, as described in section 4.3, will appear as a column-heading. At 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 if there is a structural relationship between the entity at the head of the column and the entity at the head of the row. The cross-reference matrix is shown in Table 4.15. The column-headings show El to E10, as an abbreviation for each entity. The abbreviated headings are as follows: El is for company, E2 for Day, E3 for participant type, E4 for people, E5 for photo type, E6 for project, E7 for specs section, E8 for storage location, E9 for trade, and E10 for WBS. Table 4.15 Entity Cross-Reference Matrix El E2 E3 E4 E5 E6 E7 E8 E9 E10 Bids R O R Change Order O R Company R R Correspondence R 0 Daily Events R R R O Daily Weather R 0 Day DefectiveWork O R Materials Stored 0 R R Monthly Progress 0 R Participant Type People R Photograph R R 0 Photo Type Project R R Punch Lists O R RFI R O Shop Drawing 0 R Spare Parts 0 R R Specs Section R Storage Location Trade WBS R Project ItemDetails 0 R Project TradeDetails R 0 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 DBMS 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: • the name of the field, • the data type, • the length or width of the field, • indexing requirements, and • brief description of the field. 4.8 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 • 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. 4.8.2 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 Work 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 Entity Size Table 4.17 evaluates the expected entity size in terms of bytes. Record size is the sum of field length of all the fields in a file, 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 Expected Entity Sizes No. File Name Frequency No. of Record Size Total Size Records (bytes) (bytes) 1 Bill of Quantities Monthly 72 150 10,800 2 Bid Summary Sheet 1/project 180 360 64,800 3 Change Orders Log 100/project 600 510 306,000 4 Correspondence Log 5/day/project 10,800 450 4,860,000 5 Daily Site Report 1/day/project 2,190 1,100 2,409,000 6 DWN Log 100/project 600 520 312,000 7 Materials Stored Log Monthly 72 500 36,000 8 Monthly Progress Monthly 72 300 21,600 9 Photograph Log 3 0/month/proj ect 2,160 400 864,000 10 Punch List 100/project 600 500 300,000 11 RFI Log 100/project 600 500 300,000 12 Shop Drawing Log 30/prqject 180 540 97,200 13 Spare Parts Log 50/project 300 670 201,000 Total 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, RAM (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 MB (megabyte) of RAM (4 MB or more is recommended); 240 MB or higher hard drive; EGA or VGA monitor (VGA is recommended); compatible mouse; and a laser printer (2 MB 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 ASPE (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 reports from job sites. Photograph data will be obtained from photo log of job 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 records from job 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 of the gadgets of the window operating system like fields, menus, buttons, dropdown menus, scroll bars, etc. As required by the company, the following forms and reports will be created: • 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, • Punch Lists, • Requests for Information Tracking Log, • Shop Drawing Submittals Tracking Log, and • Spare Parts Tracking Log. r SYSTEM DATA ORGANIZATION Data Input Collect Input Data , Edit Database Data Output Output to Screen Output to Printer Printouts r > View Table View Query View Form View Report Figure 4.50 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-by-example (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 QBE 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 (1-M), 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 (Appendix-D), were used to create QBE 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 SDM, 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 Benefits and Applications of PMICS 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. • PMICS is a user friendly and easy to use system. 5.3 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. • Prototyping is very efficient in pointing out deficiencies in the design. 5.4 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. • And last, but not the least, the PMICS itself. 5.5 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, ASCE. 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 CM 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, ASCE. 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., USA. [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 AEC, 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, ASCE. pp. 1381-88. [Hamilton 91] Hamilton, Dennis O. (1991). Records Management in Engineering Firms. J. of Management 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, ASCE. 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, ASCE. pp. 192-201. [Leslie & McKay 93] Leslie, HG, and McKay, DG (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, ASCE. pp. 608-613. [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, ASCE. 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, ASCE. 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). NIAM Conceptual Data-base Design In Construction Management J. of Computing In Civil Engineering, ASCE, 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, ASCE, June. pp. 385-401. [Sadri & Kangari 93] Sadri, S.L., and Kangari, R. (1993). Construction Information Management. Proc. Computing in Civil Engineering, ASCE. 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 Techniques for Design, Performance, and Management. John Wiley & Sons, New York. [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). Students' Guide to Databases. Heinemann Newnes, Oxford, UK. [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). Managing Construction. Delmar Publishers Inc., New York. pp. 385-409. [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, MA. 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). Resident Engineer's Field Manual. PTR Prentice-Hall, Inc. New Jersey. [Gilbreath 83] Gilbreath, Robert D. (1983). Contract Reporting. Managing Construction Contracts. John Wiley & Sons, New York. Ch-19. [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). A Concise Dictionary of Construction, Crisp Publications, Inc., Los Altos, California. [Levy 94] Levy, Sidney M. (1994). Project Management in Construction. McGraw-Hill, Inc. New York. [Liskin 93] Liskin, Miriam (1993). Help! Microsoft Access. Ziff-Davis Press, CA. [Lock 92] Lock, Dennis (1992). Information Management-1 & 2. Handbook of Engineering Management, Ch. 20 & 27. [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). Construction Inspection Handbook. VanNostrand Reinhold, New York. Third Edition. [Peters 84] Peters, Glen (1984). Construction Project Management Using Small Computers, The Architectural Press, London. [Pierce 88] Pierce, David R. (1988). Project Planning & Control for Construction, R.S. Means Company, Inc. Kingston, Ma. [Skitmore 89] Skitmore, R.M. (1989). Contract Bidding in Construction. Longman Scientific and Technical.. [Stewart & Stewart 86] Stewart, Rodney D., and Stewart, Ann L. (1986). Microestimating for Civil Engineers. McGraw-Hill Book Company, New York, ch 2 & 6. [Tidwell 92] Tidwell, Mike C. (1992). Microcomputer Application for Field Construction Projects. McGraw Hill, Inc. New York. [Townsend 92] Townsend, James J. (1992). Introduction to Databases. Que Corporation, Carmel, IN U.S.A. Chapters 1-4. 236 APPENDIX A Matrix This appendix contains matrix M-l (personnel versus functions) only. Other matrices (M-2, 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, planning, construction management, project 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 PIS!J •jpdns |Bjeuao )ue)uno33V SD / Jtnewiisg jssuiBug )soo JSSUjBU^ B U!inp9H3g/Buiuue|d jaamBug loafcud or JGBEUELM |9UUOSJ3cj 111 o" )U3iuajnooJd o J01BUJ|IS3 j9|l|0 13IMQ lssy jsBeueiAJ uoi)onj(suoo o z 3 jssmBug jaiiio 3 HI O or UJ )ue(unooov jamo jsBeuei/M souem j J866UEIAJ |EJ8U80 LU u" UI luapjsaid cc AjHiqisuodsay in •a' I ii cc TO ° i Q. 1 £ | i o c 8 O X u <I> » .". •S .1 s </>(/)>, -S > ro f»< p c O Q. ™ (0 TJ CO V) CD 'ra CD II i °3 CD < or LU z UJ CD o. 1 E i o JD 1 _Q ' co .! C ! O i a < CO -j CO 1 B. o o o < CD z o o o 08 I c E I B B in o o "5 1 > TJ CO E c O. ; "ro -, 'o c .: co -SHE LU CD < z < LU Q < I c ro •fc> e? a J cn i c I 'c 'i 1 J 08 ; Ol | C i 1 B 8 in m c o JO CO ra CD a 238 jopejiuooqns UGLU8J0-J sluapuaiuuadns bsu|6ug eoyjo Pl8!d •IjpdnS |EJ8U8Q ui )us6v Bujseqojnd lueiunooov jesujBug AjajES SD / JOIBLUHS3 2 Q. jgsu|6ug ISOQ jesUjBug "IT" uiinponos/Bumueid J88iij6ug patoJd 0£ O o or d je6euEiM isuuosjsd J8tieue|/\| z o joieunjsg iamo i o; x cc o o J88U;BU3 I8JU3 )ssy o z LL UJ O V) DC UJ Q. jetJBUEifti uoipnjtsuoo cc J88U|6U3 J8ILO )UB;unooov jsmo J86BUB1AI soueuy J86euey\| |Bjauao luapjsajd A}!i!q|suodsay in TJ II f * XI (0 "5> -c c* .-£ | I £ 5 oi II ll O UJ .S2 c _ a ° N H co >• -2 > ra ->» p c 8 ra TJ g) CO oa <0 CO c 1 E ' P IT c I CD "P| o x: •s El 1 (A CO o p CA CD J8 TJ CD 2 >5f CD i CO "3 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 jssuiBug ISOQ LU of uiinpsgos/Buiuueid i • J88ui6ug pafojd jsBeueyy lasfojd BC o" jaBEUB^J |8UUOSJ8d JOBBUEI^ tueiuejnoojd O w z o H O ZZ) UL ZJ UJ jeemBug-laiLip tssy jeBBUEinj uoi)onj|suoo Si I d jaaujBug jaigo )UB)unooov J8IM0 I* jsBeueyy SOUBUI-J o K UJ Q. jeBeuey^ I&I8U8Q |U8p!S8Jd Ajinqisuodsay II cc f * XI Cfl io J= £ § 8 $ II II O UJ CO 'to (A <8 to >, > ra "it TJ ra > ra "O co ra ii CO , CO '8. I § Or ra CD C o CO E ra g.Jjl 8J I1 111 CO > to o C I If c o '« ••§ a CD c TJ C ra , co o QJ CD 2* o 5 c o c o u a • o CD > CD I* C o 8 to CO c 8 j ZJ to CT a c '» ra 9^ 240 jopaiiuooqns S)U8pU3)UM8dnS jaaujBug p|sy beuiBug 93IUQ P|8!d ipdns |ejsu8Q o lusBv Biuseupjnd (UB)unooov J8sui6u3 AJOJBS SD/J01BIUIIS3 jaauiBug (SOQ J88UjbU3 B unnpsups/Buiuueid CC I o X or JGBEUBVM paloJd O o O CC d jaBeueyu, isuuosjsd jeBeueiq [uaLuajnooJd o o 0} o JO;BLUI)S3 JSJUO JG8UIBU3 )ssy jsBi o z LL LU Z z o </) a: LU o. beuBjM uoiprutsuoQ jeeinBug jamo lUBiunoooy iaiqo jeBeueyu eoueuy jaBBUB/J |eJ8U80 |U8p|S8Jd Ajlliqisuodsay II f * X) cc CO -c 8.x 8 c c o s 1 n 8 8 o UJ <2 io "5 21 CO m ofl ••ill CO TJ CO > "> U < 3 s c o 1. ra c 8Jl i-SI co I 1 c a c CD 32 CO 1 3 CO IT? 8. Hi 5 i CA '-8 3 I Si 3 id •§ i 1 ii si SI 1 o CD a co o3 •8 9' .•el 2 Si .sr 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. 1.00 GENERAL DISCIPLINES 1.10 BUILDING 1.20 TRANSPORTATION 1.30 INDUSTRIAL 1.40 HEAVY/UTILITIES 1.50 POWER 2.00 SITE WORK 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 3.00 CONCRETE DISCIPLINE 3.10 CAST-IN-PLACE 3.20 REINFORCING STEEL 3.30 PRECAST 3.40 SPECIALIZED DECKS & FINISHES 4.00 MASONRY DISCIPLINE 4.10 BRICK & BLOCK MASONRY 4.20 STONE MASONRY 5.00 METAL 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 6.00 WOOD & 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 7.00 THERMAL & MOISTURE DISC. 7.10 WATERPROOFING & DAMP. 7.20 INSULATION 7.30 ROOFING 7.31 Shingle & Tile 7.35 Membrane 7.40 ARCHITEC. SHEET METAL 7.50 SKYLIGHTS 7.60 CAULKING & SEALANT 8.00 DOOR & WINDOW DISCIPLINE 8.10 HOLLOW METAL 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 PARTmON SYSTEMS 11.00 EQUIPMENT DISCIPLINES 12.00 FURNISHING DISCIPLINES 13.00 SPECIAL CONSTRUCTION DISC. 14.00 CONVEYING SYSTEM DISC. 14.10 ELEVATORS & ESCALATORS 14.20 HOISTS & CRANES 14.30 MATERIAL CONVEYING SYSTEM 15.00 MECHANICAL DISCIPLINES 15.10 PLUMBING 15.20 PIPING 15.30 HVAC 15.40 FIRE PROTECTION 16.00 ELECTRICAL DISCIPLINES 16.10 RESIDENTIAL 16.20 COMMERCIALTND. 16.30 POWER TRANSMISSION 16.40 COMMUNICATION SYSTEMS 17.00 ENVIRONMENTAL DISCIPLINES Figure B.l 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: field name, data type, length, index and comments. Field name is same as the attribute name. Data type is the type of data the field will 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 the field. 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; Bold field name(s) = key-field(s). 244 Table C. 1 Data Element List for Bids Field Name Data Type Length Index Comments ProjectID Long 4 Yes (DOK) same as ProjectID in Project table TradeDD Integer 2 Yes (DOK) same as TradelD in Trades table BidderlD Long 4 Yes (DOK) same as CoJLD in Company table Bid Pkg Sent Date/Time 8 No date bid package sent to a bidder Bid Received Date/Time 8 No date bid received from a bidder Bid Amount Currency 8 No bid amount quoted by a bidder Remarks Text 50 No special comments or remarks Table C.2 Data Element List for Change Orders Field Name Data Type Length Index Comments ProjectID Long 4 Yes (DOK) same as ProjectID in Project table CMR# Integer 2 Yes (DOK) Contract Modification Request no. SpecsID Integer 2 No same as SpecsID in Specs Section table Initiation Date Date/Time 8 No date the CMR initiated/introduced Current Status Text 20 No current status of the CMR Description Text 50 No brief description of the CMR EstimatValue Currency 8 No contractor's estimate of the CMR EngrEstimate Currency 8 No engineer's estimate of the CMR Final Value Currency 8 No final settlement value Time Impact Byte 1 No contract time impact (CTI) in days D-RfAE Date/Time 8 No date CMR received from the A/E D-StS Date/Time 8 No date CMR sent to subcontractor D-BfS Date/Time 8 No date back from the subcontractor D-RtAE Date/Time 8 No date returned to the A/E D-FS Date/Time 8 No date of final settlement Disposition Text 50 No disposition or comments 245 Table C.3 Data Element List for Company Field Name Data Type Length Index Comments Co ID Counter 4 Yes (NOD) company identification number Co_Name Text 40 Yes (DOK) name of the company TypelD Integer 2 Yes (DOK) same as TypelD in Participant Type Contact 1 Long 4 No PeoplelD of primary contact person Contact2 Long 4 No PeoplelD of second contact person Address Text 30 No address of the company City Text 20 Yes (DOK) name of the city Province Text 20 No province abbreviated name Postal Code Text 7 No postal code of the area Phone Text 20 No company's phone number Fax Text 20 No company's fax number Size Text 6 No company size: big, medium or small Table C.4 Data Element List for Correspond ence Field Name Data Type Length Index Comments ProjectID Long 4 Yes (DOK) same as ProjectlD in Project table Corr# Integer 2 No project's correspondence entry serial number From People Long 4 No PeoplelD of the sender To People Long 4 No PeoplelD of the addressee Reference# Text 20 Yes (DOK) reference no. on a correspondence Corr Date Date/Time 8 Yes (DOK) original date of a correspondence Date Sent Date/Time 8 No date of mailing DateRcvd Date/Time 8 No date correspondence received Follow-up Yes/No 1/8 Yes (DOK) follow up required? Keyword 1 Text 20 Yes (DOK) first keyword describing the subject Keyword2 Text 20 Yes (DOK) 2nd keyword describing the subject Notes Text 75 No notes or comments 246 Table C.5 Data Element List for Daily Events Field Name Data Type Length Index Comments ProjectID Long 4 Yes (DOK) same as ProjectID in Project table Date Date/Time 8 Yes (DOK) date of a report Day ID Integer 2 No same as Day ID in Day table Daily Report# Integer 2 Yes (DOK) project's serial number of a report Prepared by ID Long 4 No PeoplelD of person making report FFC1 Long 4 No field force contractor-1 CoID FFC2 Long 4 No field force contractor-2 CoID FFC3 Long 4 No field force contractor-3 CoID FFC4 Long 4 No field force contractor-4 Co_ID NMFF1 Byte 1 No non-manual field force of FFC1 NMFF2 Byte 1 No non-manual field force of FFC2 NMFF3 Byte 1 No non-manual field force of FFC3 NMFF4 Byte 1 No non-manual field force of FFC4 MFF1 Byte 1 No manual field force of FFC1 MFF2 Byte 1 No manual field force of FFC2 MFF3 Byte 1 No manual field force of FFC3 MFF4 Byte 1 No manual field force of FFC4 Jobl Text 20 No job/trade name of FFC1 Job2 Text 20 No job/trade name of FFC2 Job3 Text 20 No job/trade name of FFC3 Job4 Text 20 No job/trade name of FFC4 Visitor 1 Text 30 No name of visitor-1 Visitor2 Text 30 No name of visitor-2 VisitTimel Date/Time 8 No time of visit of visitor-1 VisitTime2 Date/Time 8 No time of visit of visitor-2 Companyl Text 30 No name of the visitor-l's company Company2 Text 30 No name of the visitor-2's company Purpose 1 Text 30 No purpose of the visitor-l'S visit Purpose2 Text 30 No purpose of the visitor-2'S visit EquipUse Memo N/A equipment in use at the project Equipldle Memo N/A equipment idle at the project Activities Memo N/A activities in progress at the site New Act Memo N/A New activities started at the site Act Compl Memo N/A activities completed at the site Material Needs Memo N/A future material needs 247 Table C.6 Data Element List for Daily Weather (Weather) Field Name Data Type Length Index Comments ProjectTD Long 4 Yes (DOK) same as ProjectlD in Project table Date Date/Time 8 Yes (DOK) date of a report Day ID Integer 2 No same as Day ID in Day table Temperature Integer 2 No recorded average temperature Sunny Yes/No 1/8 No sunny weather? Cloudy Yes/No 1/8 No cloudy weather? Rainy Yes/No 1/8 No rainy weather? Snowy Yes/No 1/8 No snowy weather? Still Wind Yes/No 1/8 No wind is still? Moderate Wind Yes/No 1/8 No wind is moderate? High Wind Yes/No 1/8 No wind is high? Humid-dry Yes/No 1/8 No humidity: dry? Humid-mod Yes/No 1/8 No humidity: moderate? Humid-high Yes/No 1/8 No humidity: high? Table C.7 Data Element List for Days Field Name Data Type Length Index Comments Day TD Integer 2 Yes (DOK) day's identity number Day Name Text 9 No name of the day Table C.8 Data Element List for Defective Work Notice (DWN) Field Name Data Type Length Index Comments ProjectTD Long 4 Yes (DOK) same as ProjectlD in Project table DWN# Integer 2 Yes (DOK) project's DWN entry serial number SpecsID Integer 2 No same as SpecsID in Specs Section NoticeDate Date/Time 8 No DWN issue date Description Text 50 No brief description of the DWN DateRcvd Date/Time 8 No date the DWN received EstimatValue Currency 8 No estimated value of the DWN item DateReinsp Date/Time 8 No date the DWN item re-inspected Reinspected by Long 4 No name of the inspector D-NoCR Date/Time 8 No date no-objection received Comments Text 50 No special comments, if any 248 Table C.9 Data Element List for Materials Stored Field Name Data Type Length Index Comments ProjectTD Long 4 Yes (DOK) same as ProjectlD in Project table Mat# Integer 2 Yes (DOK) project's materials serial number SpecsID Long 4 No same as SpecsID in Specs Section table LocJLD Long 4 No same as Loc_ID in Storage Location table ItemDescription Text 50 No item description of the materials Loc-Installed Text 30 No location of materials installed Original Value Currency 8 No original value of materials stored % Installed Single 4 No estimated % quantity installed to-date PayEst# Byte 1 No payment estimate number Pay Est Date Date/Time 8 No monthly payment estimate date Table C.10 Data Element List for Monthly Progress Field Name Data Type Length Index Comments ProjectTD Long 4 Yes (DOK) same as ProjectlD in Project table Pay Est# Byte 1 Yes (DOK) project's pay estimate serial number WBSJD Double 8 No same as WBSID in WBS table PayEst Date Date/Time 8 Yes (DOK) date of payment estimate This Qntty Single 4 No % of the work item done this month Todate Qntty Single 4 No % of the work item done to-date Table C.ll Data Element List for Participant Type Field Name Data Type Length Index Comments TypeTD Participant Type Integer Text 2 15 Yes (NOD) No identity number of participant type name of the participant type 249 Table C.12 Data Element List for People Field Name Data Type Length Index Comments PeopleTD Counter 4 Yes (NOD) people identification number Last Name Text 20 Yes (DOK) last name of the people First Name Text 10 No last name of the people Co ID Long 4 Yes (DOK) same as CoID in Company table Title Text 20 No title of the person in the company Address Text 30 No address of the people City Text 20 No name of the city Province Text 5 No province abbreviated name Postal Code Text 7 No postal code of the area Phone Text 20 No phone number including area code Fax Text 20 No 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) same as ProjectlD in Project table Photo# Integer 2 Yes (DOK) project's photograph serial no. Photo TypelD Long 4 Yes (DOK) same as in Photo Type table Photgr ID Long 4 No PeoplelD of the photographer Roll# Integer 2 No serial number of the film roll Negative# Integer 2 No negative-number in the film roll Date Taken Date/Time 8 No date of photography Time of Day Date/Time 8 No day time when photo taken Camera Direction Text 25 No direction of camera when taking Photgr Position Text 25 No position of the photographer Facility/Location Text 20 Yes (DOK) name of location where photo taken Keyword 1 Text 20 Yes (DOK) first keyword describing the photo Keyword2 Text 20 Yes (DOK) second keyword describing the photo Caption Text 30 No 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 2 Yes (NOD) identity number of photo type Photo Type Text 15 No name of the photo type Table C.15 Data Element List for Project ItemDetails Field Name Data Type Length Index Comments ProjectID Long 4 Yes (DOK) same as ProjectID in Project table WBSJD Double 8 Yes (DOK) same as WBSID in WBS table Quantity Single 4 No quantity of the WBS item Unit Price Currency 8 No unit cost of the item % Markup Single 4 No % markup to be added Delivery Date Date/Time 8 Yes (DOK) scheduled delivery or action date Delivery days Integer 2 No number of days required for delivery Approval Days Integer 2 No number of days required for approval ShopDrawing Yes/No 1/8 Yes (DOK) shop drawing required? MatSample Yes/No 1/8 No materials sample required? Catalog Yes/No 1/8 No catalog or brochure required? Misc Yes/No 1/8 No other submittals required? Misc Submittals Text 20 No calculation method statement etc. As-built Drawing Yes/No 1/8 No as-built drawing required? Table C.16 Data Element List for Project TradeDetails Field Name Data Type Length Index Comments ProjectTD Long 4 Yes (DOK) same as ProjectlD in Project table TradelD Integer 2 Yes (DOK) same as TradelD in Trades table Budget Currency 8 No estimated budget for the trade StartDate Date/Time 8 Yes (DOK) start date of the trade activities EndDate Date/Time 8 No end date of the trade activities Bid Due Date Date/Time 8 Yes (DOK) bid due date from bidders SublD Long 4 No CoK) of the trade subcontractor Sub Amount Currency 8 No subcontract amount 251 Table C.17 Data Element List for Project Field Name Data Type Length Index Comments ProjectTD Counter 4 Yes (NOD) project identification number Project Name Text 30 Yes (DOK) official name of the project Project Type Text 10 Yes (DOK) type of project (building, bridge, etc.) EngineerlD Long 4 No Co_ID of the architects/engineers OwnerlD Long 4 No Co ID of the owner ManagerlD Long 4 No PeoplelD of contractor's project manager OCBP Currency 8 No original contract bid price OCCD Date/Time 8 Yes (DOK) original contract completion date Start Date Date/Time 8 Yes (DOK) start date of the project End Date Date/Time 8 No actual completion date Address Text 50 No address or location of the project Phone Text 20 No site office phone number Fax Text 20 No site office fax number Table C.18 Data Element List for Punch Lists Field Name Data Type Length Index Comments ProjectTD Long 4 Yes (DOK) same as ProjectlD in Project table PL# Integer 2 Yes (DOK) project's punch list serial number SpecsID Integer 2 No same as SpecsID in Specs Section table Punch List Item Text 50 No item description of the punch list Facility/Area Text 20 No location of the punch list item Date Identified Date/Time 8 No date identified by the engineer Identified by Long 4 No PeoplelD of the engineer Date Completed Date/Time 8 No date punch list item completed Date Checked Date/Time 8 No date rechecked by the engineer Checked by Long 4 No PeoplelD of the engineer 252 Table C.19 Data Element List for RFI Field Name Data Type Length Index Comments ProjectID Long 4 Yes (DOK) same as ProjectID in Project table RFT# Integer 2 Yes (DOK) project's RFI entry serial number Initiated By Long 4 No PeoplelD of the initiator Initiation Date Date/Time 8 No date of RFI initiation Drawing# Text 10 No drawing number, for reference RFI Description Text 50 No short description of the RFI D-StAE Date/Time 8 No date sent to the architect/engineer D-RBfAE Date/Time 8 No date returned back from the A/E D-SBtS Date/Time 8 No date sent back to subcontractor Response By Long 4 No PeoplelD of the person who responded Comments Text 50 No comments or remarks Table C.20 Data Element List for Shop Drawing Field Name Data Type Length Index Comments ProjectID Long 4 Yes (DOK) same as ProjectID in Project table ShopDrwg# Integer 2 Yes (DOK) shop drawing number WBSJD Double 8 No same as WBSID in WBS table Description Text 40 No keyword or title of the shop drawing Disposition Text 10 No disposition or comments D-RfS Date/Time 8 No date received from the subcontractor D-StAE-I Date/Time 8 No date sent to A/E (initial) D-StAE-R Date/Time 8 No date sent to A/E (resubmission), if any D-StAE-F Date/Time 8 No date sent to A/E (Final), if any D-RBfAE Date/Time 8 No date received back from the A/E D-SBtS Date/Time 8 No date sent back to the subcontractor C-RfS Byte 1 No copies received from the subcont. C-StAE Byte 1 No copies sent to the A/E C-RBfAE Byte 1 No copies received back from the A/E C-SBtS Byte 1 No copies sent back to subcontractor Comments Text 50 No comments or remarks 253 Table C.21 Data Element List for Spare Parts Field Name Data Type Length Index Comments ProjectTD Long 4 Yes (DOK) same as ProjectlD in Project table SP# Integer 2 No project's spare parts entry serial number Specs ID Integer 2 No same as Specs ID in Specs Section LocJD Long 4 No same as LocID in Storage Location table SP Description Text 40 No description of the spare parts Eq Description Text 40 No equipment the spare part is for Qntty Required Integer 2 No total quantity required to be delivered Qntty Dlvd Integer 2 No actual quantity delivered to-date Unit Text 4 No unit of the spare parts Status Text 50 No 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) identity number of a specification section WBSID Long 4 No same as WBS ID in WBS table Specs Section Text 5 Yes (NOD) CSI Masterformat section number Section Title Text 50 No CSI Masterformat section title Division Text 12 No CSI Masterformat specs division Division Title Text 40 No CSI Masterformat Division Title Table C.23 Data Element List for Storage Location Field Name Data Type Length Index Comments LocID Counter 4 Yes (NOD) identity number of storage location Location Name Text 30 No name of the storage location Address Text 50 No address of the storage location 254 Table C.24 Data Element List for Trades Field Name Data Type Length Index Comments TradeTD Trade Name Description Integer Text Memo 2 25 N/A YesfNOD) Yes (NOD) No identity number of construction trade name of the trade short description or scope of the trade Table C.25 Data Element List for WBS Field Name Data Type Length Index Comments WBS ID Double 8 Yes (NOD) identity number of Work Breakdown Structure element Item Description Text 45 No WBS item description TradelD Integer 2 No same as TradelD in Trade table Unit Text 5 No unit of the WBS item Table C.26 Space Estimate for Tables View View Name Frequency No. of Record Size Total Size No. Records (bytes) (bytes) 1 Bill of Quantities Monthly 24 150 3,600 2 Bid Summary Sheet 1/project 1 360 360 3 Change Orders Log 5/month 120 510 61,200 4 Correspondence Log 5/day 3600 450 1,620,000 5 Daily Site Report 1/day 730 1100 803,000 6 DWN Log 5/month 120 520 62,400 7 Materials Stored Log Monthly 24 500 12,000 8 Monthly Progress Monthly 24 300 7,200 9 Photograph Log 20/month 480 400 192,000 10 Punch List 100/project 100 500 50,000 11 RFI Log 100/project 100 500 50,000 12 Shop Drawing Log 30/project 30 540 16,200 13 Spare Parts Log 50/project 50 670 33,500 Total 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 Fields Type Enforce Company Bids CoJD/BidderlD 1-M Yes Company Daily Events Co ID/FFC1 1-M Yes Company Daily Events Co ID/FFC2 1-M Yes Company Daily Events Co_ID/FFC3 1-M Yes Company Daily Events Co_ID /FFC4 1-M Yes Company People Co_ID 1-M Yes Company Project CoID/Engineer 1-M Yes Company Proj-Company Co_ID 1-M Yes Company Project TradeDetails Co_ID/SubID 1-M Yes Day Daily Events Day ID 1-M Yes Day Daily Weather Day ID 1-M Yes Participant Type Company TypelD 1-M Yes People Company PeoplelD/Contactl 1-M Yes People Company PeopleID/Contact2 1-M Yes People Correspondence PeopleTD/From 1-M Yes 256 Primary Table Related Table Fields Type Enforce People Correspondence PeoplelD/To 1-M Yes People Daily Events PeoplelD/Prep by ID 1-M Yes People Photograph PeoplelD/Photgr ID 1-M Yes People Project PeoplelD/ManagerlD 1-M Yes People RFI PeoplelD/Initiated By 1-M Yes People RFI PeoplelD/ResponseBy 1-M Yes Photo Type Photograph Photo TypelD 1-M Yes Project Bids ProjectlD 1-M Yes Project Change Order ProjectlD 1-M Yes Project Correspondence ProjectlD 1-M Yes Project Daily Events ProjectlD 1-M Yes Project Daily Weather ProjectlD 1-M Yes Project Defective Work ProjectlD 1-M Yes Project Materials Stored ProjectlD 1-M Yes Project Monthly Progress ProjectlD 1-M Yes Project Photograph ProjectlD 1-M Yes Project Project ItemDetails ProjectlD 1-M Yes Project Proj-Company Project 1-M Yes Project Proj-Trade Project 1-M Yes Project Project TradeDetails Project 1-M Yes Project Punch List ProjectlD 1-M Yes Project RFI ProjectlD 1-M Yes Project Shop Drawing ProjectlD 1-M Yes Project Spare Parts ProjectlD 1-M Yes Specs Section Change Order SpecsID 1-M Yes Specs Section Defective Work SpecsID 1-M Yes Specs Section Materials Stored SpecsID 1-M Yes Specs Section Punch Lists SpecsID 1-M Yes Specs Section Spare Parts SpecsID 1-M Yes Storage Location Materials Stored LocID 1-M Yes Storage Location Spare Parts LocJD 1-M Yes Trade Bids TradelD 1-M Yes Trade Proj-Trade TradelD 1-M Yes Trade Project TradeDetails TradelD 1-M Yes Trade WBS TradelD 1-M Yes WBS Monthly Progress WBS ID 1-M Yes WBS Project ItemDetails WBS ID 1-M Yes WBS Shop Drawing WBSJD 1-M Yes WBS Specs Section WBS ID 1-M Yes 257 Table D.2 Queries and Join Tables Primary Table Related Table Fields Type Enforce BidSummary Company Bids Co_ID/BidderID 1-M Yes Company Project TradeDetails Co_ID/SubID 1-M Yes Project Bids ProjectID 1-M Yes Project Project TradeDetails ProjectID 1-M Yes Trade Bids TradelD 1-M Yes Trade Project TradeDetails TradelD 1-M Yes Bill of Quantities Project Project ItemDetails ProjectID 1-M Yes Trade WBS TradelD 1-M Yes WBS Project ItemDetails WBSJD 1-M Yes Change Orders Company People CoJD 1-M Yes Company Project CoJD/EngineerlD 1-M Yes Company Project TradeDetails CoJD/SuMD 1-M Yes People Company PeoplelD/Contactl 1-M Yes People Company PeopleID/Contact2 1-M Yes Project Change Order ProjectID 1-M Yes Project Project TradeDetails ProjectID 1-M Yes Specs Section Change Order SpecsID 1-M Yes Trade Project TradeDetails TradelD 1-M Yes WBS Specs Section WBSJD 1-M Yes Correspondence Company People CoJD 1-M Yes Participant Type Company TypelD 1-M Yes People Company PeoplelD/ Contact 1 1-M Yes People Company PeoplelD/ Contact2 1-M Yes People Correspondence PeopleTD/From People 1-M Yes People Correspondence PeoplelD/To People 1-M Yes Project Correspondence ProjectID 1-M Yes Daily Report Company People CoJD 1-M Yes Company Project CoJD/EngineerlD 1-M Yes Company Project Co JD/OwnerJD 1-M Yes Company Daily Events CoJD/FFCl 1-M Yes Company Daily Events Co ID/FFC2 1-M Yes Company Daily Events CoJD/FFC3 1-M Yes Company Daily Events Co ID/FFC4 1-M Yes 258 Primary Table Related Table Fields Type Enforce Day Weather Day ID 1-M Yes People Daily Events PeoplelD/Prep by ID 1-M Yes Project Daily Events ProjectlD 1-M Yes Project Weather ProjectlD 1-M Yes Defective Work Company People CoJD 1-M Yes Company Project TradeDetails CoJD/SublD 1-M Yes Company Project Co_ID/EngineerID 1-M Yes People Company PeoplelD/Contactl 1-M Yes People Company PeopleID/Contact2 1-M Yes Project Defective Work ProjectlD 1-M Yes Project Project TradeDetails ProjectlD 1-M Yes Specs Section Defective Work SpecsID 1-M Yes Trade Project TradeDetails TradelD 1-M Yes WBS Specs Section WBSJD 1-M Yes Materials Stored Company People CoID 1-M Yes Company Project CoID/EngineerlD 1-M Yes Company Project TradeDetails CoJD/SublD 1-M Yes People Company PeoplelD/Contactl 1-M Yes People Company PeopleID/Contact2 1-M Yes Project Materials Stored ProjectlD 1-M Yes Project Project TradeDetails ProjectlD 1-M Yes Specs Section Materials Stored SpecsID 1-M Yes Storage Location Materials Stored LocJLD 1-M Yes Trade Project TradeDetails TradelD 1-M Yes WBS Specs Section WBS ID 1-M Yes Monthly Progres Company People Co_ID 1-M Yes Company Project CoID/EngineerlD 1-M Yes Company Project TradeDetails Co_JD/SubID 1-M 1-M People Company PeoplelD/Contactl 1-M Yes People Company PeopleID/Contact2 1-M Yes Project Monthly Progress ProjectlD 1-M Yes Project Project ItemDetails ProjectlD 1-M Yes Project Project TradeDetails ProjectlD 1-M Yes Trade Project TradeDetails TradelD 1-M Yes WBS Monthly Progress WBS ID 1-M Yes WBS Project ItemDetails WBS ID 1-M Yes 259 Primary Table Related Table Fields Type Enforce Photographs Company People Co_ID 1-M Yes Company Project Co_ID/Engineer 1-M Yes People Company PeoplelD/Contactl 1-M Yes People Company PeopleID/Contact2 1-M Yes People Photograph PeoplelD/Photgr ID 1-M Yes Photo Type Photograph Photo TypelD 1-M Yes Project Photograph ProjectID 1-M Yes Punch Lists Company People CoJD 1-M Yes Company Project TradeDetails CoID/SublD 1-M Yes Company Project CoJD/EngineerlD 1-M Yes People Company PeoplelD/Contactl 1-M Yes People Company PeopleID/Contact2 1-M Yes Project Punch Lists ProjectID 1-M Yes Project Project TradeDetails ProjectID 1-M Yes Specs Section Punch Lists SpecsID 1-M Yes Trade Project TradeDetails TradelD 1-M Yes WBS Specs Section WBSID 1-M Yes RFIs Company People CoJD 1-M Yes Company Project CoJD/EngineerlD 1-M Yes Participant Type Company TypelD 1-M Yes People Company PeoplelD/Contactl 1-M Yes People Company PeoplelD /Contact2 1-M Yes People RFI PeoplelD/Initiated By 1-M Yes People RFI PeoplelD/Response by 1-M Yes Project RFI ProjectID 1-M Yes Shop Drawing Company People CoJD 1-M Yes Company Project TradeDetails CoJD/SubID 1-M Yes Company Project CoJD/EngineerlD 1-M Yes Participant Type Company TypelD 1-M Yes People Company PeoplelD/Contactl 1-M Yes People Company PeopleJX)/Contact2 1-M Yes Project Shop Drawing ProjectID 1-M Yes Project Project ItemDetails ProjectID 1-M Yes Project Project TradeDetails ProjectID 1-M Yes Specs Section WBS SpecsID 1-M Yes Trade Project TradeDetails TradelD 1-M Yes Trade WBS TradelD 1-M Yes 260 Primary Table Related Table Fields Type Enforce WBS Project ItemDetails WBSJD 1-M Yes WBS Shop Drawing WBSID 1-M Yes Spare Parts Company People CoID 1-M Yes Company Project CoID/EngineerlD 1-M Yes Company Project TradeDetails Co_ID/SublT> 1-M Yes Participant Type Company TypelD 1-M Yes People Company PeoplelD/Contactl 1-M Yes People Company PeopleID/Contact2 1-M Yes Project Project TradeDetails ProjectlD 1-M Yes Project Spare Parts ProjectlD 1-M Yes Specs Section Spare Parts SpecsID 1-M Yes Storage Location Spare Parts LocID 1-M Yes Trade Project TradeDetails TradelD 1-M Yes WBS Specs Section WBS ID 1-M Yes 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, fifty seven 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 ProjectlD: Daily Report*: Temperature: 1 1 Report Date: Humidity: |moderat |j2 Average Field Force Main Contractor ID: 1st Subcontractor: 2nd Subcontractor: 3rd Subcontractor: VISITORS Time Visitor 1 L_ Visitor 2 Non-manual Manual Job Title 10 [layout, excavation 11 underground drainage 26|]26 'ormwortc grade beam 13 '3 Name Representing electrical conduit Purpose 0:00 AMI K.N Smith Owner to see work progress 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 Entry Form: Daily Site Activity 264 Bill of Quantities Rotum to r-oims Swietibord Jtem# Description-2.10-:Derrof*icr 2.30 ^llns works ZgUUm. 3.3C;^ecasT Concr 4 ip^3rttjk&8,ock J< 5 40-pesking & Sic City ^w&mm& BuHAi I Unit-1 Unit Price: I S i S7.5jQ.aO Ml -ip PQ-Liafr'r-g If jjjjffl,jTO,QP : E.3 View Form: Bill of Quantities 265 Saium to forms SwitcJiborod Shop Drawing Submittals Tracking Log ProjectlD: Project Name: |aty Warehouse Building | Engineer: Contact!: Contocf2: XYZ Engineering, inc. j Allan Woo | I Lam Wonder Phone: (604)555-2211 • WBS ID; 2.10 ShoD Drawina*: l-SD-2.1 Scheduled Date: 22-Jan-95 Status: Approved Kevword DescriDtion: plan & location for detonator D-Rfj 25-Jai 2.30 l-SD-2.3 11 -Feb-95 Make correction not plan and section of piles 11-Fel 2.40 l-SD-2,4 14-Feb-95 Submit specified item rletnils 14-Fel 2,50 l-SD-2.5 06-Mar-95 Aooroved plan and section 06-Mc 3.30 l-SD-3.3 14-Feb-95 Ac-Droved lavout plan and sections 13-Fei 5.10 l-SD-5.1 14-Feb-95 Submit specified item plan and elevation details 14-Fei 5.40 l-SD-5.4 14-Feb-95 Approved plan and elevation details 14-Fel 7.30 l-SD-7.3 16-Feb-96 Approved plan and sections 14-Fel 7.50 l-SD-7.5 02-Feb-96 Approved plan and sections 02-Fet 8.10 l-SD-8.1 25-Mar-96 Revise and resubmit plan and elevation details 25-Mc 8.20 l-SD-8.2 30-Mar-96 New plan and elevation details 01-Ac 9.11 l-SD-9,11 1 l-Apr-96 Due lavout details 9.13 l-SD-9.13 Ol-Apr-96 Due plan and sections 9.14 l-SD-9.14 04-Apr-96 Due 9.15 l-SD-9.15 Ol-Apr-96 Due plan and sections 9,21 l-SD-9.21 Ol-Apr-96 Due plan and elevation details 9.27 l-SD-9.27 Ol-Mav-96 Due lavout details 14.10 l-SD-14.1 02-Mav-96 Due sections and details 15.10 l-SD-15.1 10-Apr-96 Due plan and sections 15.30 l-SD-15.3 20-Apr-96 Due plan and sections 15.40 l-SD-15.4 27-Apr-96 Due details 16,10 l-SD-16,1 ll-Mav-96 Due plan and sections 16.20 l-SD-16.2 ll-Mav-96 Due conduit lavout plan and detail E.4 View Form: Shop Drawing Submittals Log 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 Form: 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: Ae Bee Cee Contracting Proj. Manager: John Cook Engineer: XYZ Engineering, Inc. Bidder Name Contact Phone Trade: Concrete ABC Concrete PMICS Bid Summary Sheet by Trade or Package Bid Due Date Package Sent on Bid Received Project Name: Bid Amount Print Date: 4/10/96 Project #: 1 City Warehouse Building Page #: 1 Budget PaulNandi 4/13/95 3/25/95 4/13/95 $135,500 $157,500 (604) 666-423 Remarks: Price includes formwork, rebar, placing and curing. Difference $22,000 Concrete Pouring & Contr Comil Dey (604) 777-445 4/13/95 3/25/95 Remarks: 4/13/95 $134,000 $157,500 Price includes formwork, rebar, placing and curing. $23,500 Quick Concrete Supply Tahir Ahmad (604) 555-668 4/13/95 3/25/95 Remarks: 4/12/95 $135,000 $157,500 Price includes formwork, rebar, placing and curing. $22,500 Super Readymix Co. Abdul Kareem (604) 444-222 Trade: Doors & Windows City Doors & Windows Nigel Ho (604) 555-668 4/13/95 3/25/95 Remarks: 4/13/95 $133,500 $157,500 $24,000 Price includes formwork, rebar, placing and curing. 4/23/95 4/3/95 Remarks: 4/23/95 $33,100 $42,000 Price includes labor, materials and installation only. $8,900 Grade One Doors & Win Mike Noble (604) 666-332 4/23/95 4/3/95 Remarks: 4/23/95 $32,900 $42,000 Price includes labor, materials and installation. Scaffolding not included. $9,100 Insta Doors, Inc. Laloo Tipnis (604) 555-112 4/23/95 4/3/95 4/24/95 $34,000 $42,000 Remarks: Price includes labor, materials and installation only. $8,000 Figure F.1 Bid Summary Sheet 270 Contractor: Ae Bee Cee Contracting PMICS Print Date: 4/11/96 Proj. Manager: John Cook Project #: 1 Engineer: XYZ Engineering, Inc. Bill of Quantities Project Name: City Warehouse Building by Project Page #: 1 ltem#/WBS-ID Description Quantity Unit Unit Price 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 1,500 sqm $35 $52,500 9.13 Metal Stud Framing 500 sqm $30 $15,000 9.14 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 Page Total: $20,000 $1,487,900 Figure F.2 Bill of Quantities 271 Contractor: Ae Bee Cee Contracting PMICS Print Date: 4/10/96 Proj. Manager: John Cook Project*: 1 Engineer: XYZ Engineering, Inc. Requests and Changes Log Project Name: City Warehouse Building by Trade or Package Page #: 1 Initiated Responded Date CMR# To Vendor Value Vendor Date Vendor Date Approved Current Status Trade: Concrete 5 XYZ Engineer $1,675 Ae Bee Cee Co 02-Aug-95 XYZ Engineerin 03-Aug-95 15-Aug-95 CO No. 4 Comments: Incorporated into CO No. 4 7 XYZ Engineer $0 Ae Bee Cee Co 22-Aug-95 XYZ Engineerin 23-Aug-95 03-Sep-95 Not incorporated Comments: Not incorporated Trade: Electrical 4 Ae Bee Cee $550 Globe Electrical 25-Jul-95 XYZ Engineerin 26-Jul-95 04-Aug-95 CO No. 3 Comments: Incorporated into CO No. 3 10 Ae Bee Cee $2,000 Globe Electrical 06-Sep-95 XYZ Engineerin 07-Sep-95 l8-Sep-95 CO No. 7 Comments: Incorporated into CO No.7 Trade: HVAC 8 Ae Bee Cee $1,200 Aeros Mechanic 27-Aug-95 XYZ Engineerin 28-Aug-95 07-Sep-95 CO No. 5 Comments: Incorporated into CO No. 5 9 Ae Bee Cee $1,250 Aeros Mechanic 02-Sep-95 XYZ Engineerin 02-Sep-95 15-Sep-95 CO No. 6 Comments: Incorporated into CO No. 6 12 Ae Bee Cee $1,250 Aeros Mechanic 15-Sep-95 XYZ Engineerin 16-Sep-95 28-Sep-95 CO No. 9 Comments: Incorporated into CO No. 9 Trade: Masonary 6 XYZ Engineer $0 Ae Bee Cee Co 15-Aug-95 XYZ Engineerin 15-Aug-95 29-Aug-95 Time & material Comments: To be done time & material 11 XYZ Engineer $1,550 Ae Bee Cee Co 11-Sep-95 XYZ Engineerin 12-Sep-95 25-Sep-95 CO No. 8 Comments: Incorporated into CO No. 8 Figure F.3 Change Orders Status Report 272 Contractor: Ae Bee Cee Contracting PMICS Project*: 1 Proj. Manager: John Cook Project Name: City Warehouse Building Print Date: 4/10/96 Company Listing With Contacts PA9E# 1 Contacts Title Adress City Province Postal Code Phone Fax Company: ABC Concrete Participant Type: Subcontractor PaulNandi Technical Manager 303 Green Ave. Vancouver B.C. V6A3K3 (604)666-423 (604)666-4235 Ratan Mahi Asst. Manager Company: Ae Bee Cee Contracting Participant Type: Home-office Allan Maxwell Construction Mana 123 Green Avenue Vancouver B.C. V5R 4W8 (604)666-223 604)666-2234 Dick Wise Chief Engineer Company: Aeros Mechanical Contracting Participant Type: Subcontractor AnuBo Engineering Mana 707 Plum Street Vancouver B.C. V6N 4X8 (604)666-224 (604)666-2245 Safdar Alam Site Representativ Company: City Warehouse Participant Type: Owner Ajay Peter Warehouse Manag 123 Dead End Street Vancouver B.C. V6M 5W9 (604)888-556 604)888-5567 Andrew Wael Asst. Manager Company: Civil Contracting Co. Participant Type: Subcontractor Don Holland Sales Agent 879 Nariman Point Vancouver B.C. V6B 3B3 (604)444-123 (604)444-1233 Brian Whisker Sales Agent Company: Constructive Steel Participant Type: Subcontractor Lotte Green Business Manager 404 Alps Street Vancouver B.C. V5T6M7 (604)666-599 (604)666-5996 Saad Cho Asst. Manger Company: Globe Electricals, Inc. Participant Type: Subcontractor MahmoodHassa Manger 555 Avon Street Vancouver B.C. V6A4M3 (604)888-665 (604)888-6656 Allan Jo Sales Agent Company: Grade One Doors & Windows Participant Type: Subcontractor Mike Noble Business Manager 333 Fonton Avenue Vancouver B.C. V5R 6H3 (604)666-332 (604)666-3323 Mike Noble Business Manager Figure F.4 Company Listing 273 Daily Site Report PMICS Page #: 1 Project #: 1 Project Name: City Warehouse Building Daily Report#: Date: Day: 11 16-Jan-95 Monday Engineer: XYZ Engineering, Inc. Weather: sunny Owner: City Warehouse Temperature: 11 Contractor: Ae Bee Cee Contracting Wind: low Proj. Manager: John Cook Humidity: moderate AVERAGE FIELD FORCE Name of Contractor Non-manual Manual Remarks / Job Type Ae Bee Cee Contracting 2 10 layout, excavation Drain Fast Co. 4 5 underground drainage Super Readymix Co. 3 6 formwork grade beam Electrical Contracting Est. 2 5 electrical conduit Total Force: 11 26 = 37 VISITORS Time Visitors'Name Representing Remarks / Purpose 10:00 AM K.N.Smith Owner 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 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. Prepared By: Allan Maxwell Signature: Figure F.5 Daily Site Report 274 Contractor: Proj. Manager Engineer: Owner: Ae Bee Cee Contracting John Cook XYZ Engineering, Inc. City Warehouse PMICS Weekly Site Activity Print Date: 4/10/96 Project #: 1 Project Name: City Warehouse Building Page #: 1 Report # Temp c Sky Wind Humidity Contractors on site Construction Activities 16-Jan-95 11 11 Monday sunny low moderate Ae Bee Cee Contracting Drain Fast Co. Super Readymix Co. Electrical Contracting Est. 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. 17-Jan-95 12 13 Tuesday sunny low moderate Ae Bee Cee Contracting Drain Fast Co. Super Readymix Co. Electrical Contracting Est. 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. 18-Jan-95 13 4 Wednesday snowy high low Ae Bee Cee Contracting Drain Fast Co. Super Readymix Co. Electrical Contracting Est. 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. 19-Jan-95 14 3 Thursday cloudy high moderate Ae Bee Cee Contracting Drain Fast Co. Super Readymix Co. Civil Contracting 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. 20-Jan-95 15 4 Friday sunny moderat low Ae Bee Cee Contracting Drain Fast Co. Super Readymix Co. Civil Contracting 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. 21-Jan-95 16 7 Saturday rainy low moderate Ae Bee Cee Contracting Drain Fast Co. Super Readymix Co. Civil Contracting 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. 22-Jan-95 Sunday Ae Bee Cee Contracting No work done today. 17 6 cloudy high moderate Ae Bee Cee Contracting Ae Bee Cee Contracting Ae Bee Cee Contracting Figure F.6 Weekly Activity Report 275 Contractor: Ae Bee Cee Contracting Proj. Manager: John Cook Engineer: XYZ Engineering, Inc. PMICS Print Date: 4/11/96 Project #: 1 Monthly Progress Report project Name: City Warehouse Building 1 ltem# Description by Project Quantity Unit Unit Price Item Total Page #: This Amount Tod ate Amount Pay Est#: 1 PayEst Date: 15-Feb-95 2.10 Demolition 1 $7,500 $7,500 $24,750 $24,750 L.S. % Progress: 20 20 2.20 Earthwork 10,000 $20 $200,000 $66,000 $66,000 cum % Progress: 15 15 2.30 Piling works 1 $25,000 $25,000 $8,250 $8,250 L.S. % Progress: 15 15 2.40 Utilities 1 $10,000 $10,000 $2,200 $2,200 L.S. % Progress: 10 10 2.50 Paving/Landscaping 1 $7,500 $7,500 $12,375 $12,375 LS. % Progress: 10 10 3.10 Cast-in-Place Concrete 450 $350 $157,500 $51,976 $51,976 cum % Progress: 15 15 3.30 Precast Concrete 900 $150 $135,000 $29,700 $29,700 sqm % Progress: 10 10 4.10 Brick & Block Masonary 5,000 $40 $200,000 $44,000 $44,000 sqm % Progress: 10 10 5.10 Structural Metal 1,500 $45 $67,500 $14,850 $14,850 tons % Progress: 10 10 5.40 Decking & Siding 1,050 $30 $31,500 $10,396 $10,396 sqm % Progress: 15 15 7.10 Waterproofing & Dampproofing 1 $5,000 $5,000 $1,100 $1,100 LS. % Progress: 10 10 7.20 Insulation 1 $5,000 $5,000 $1,100 $1,100 LS. % Progress: 10 10 7.30 Roofing 1,000 $45 $45,000 $14,850 $14,850 sqm % Progress: 15 15 7.50 Skylights 20 $1,000 $20,000 $4,400 $4,400 ea % Progress: 10 10 8.10 Hollow Metal Doors & Windows 70 $300 $21,000 $6,930 $6,930 ea % Progress: 15 15 8.20 Wood & Plastic Doors & Windo 70 $300 $21,000 $4,620 $4,620 ea % Progress: 10 10 Figure F.7 Monthly Progress Report 276 Contractor: Ae Bee Cee Contracting Proj. Manager: John Cook Engineer: XYZ Engineering, Inc. Item# Status Trade Date PMICS Punch List by Contractor Location Print Date: 4/10/96 Project #: 1 Project Name: City Warehouse Building Page #: 1 Description Value Checked Company: Ae Bee Cee Contracting Contact: Allan Maxwell or Dick Wise Phone: (604)666-223 5 New General Constru 31-Mar-96 Admin Bldg Clean debris from around $450 building Company: Aeros Mechanical Contracting Contact: Anu Bo or SafdarAlam Phone: (604)666-224 Approved HVAC 11-Mar-96 Admin Bldg Balance HVAC system Company: Civil Contracting Co. Contact: Don Holland New Utilities or Brian Whisker Phone: (604)444-123 30-Mar-96 OP Bldg Clean out storm inlet box Company: Globe Electricals, Inc. Contact: Mahmood Hassa or Allan Jo 2 Approved Electrical New Electrical New Electrical 19-Mar-96 OP Bldg 30-Mar-96 Admin Bldg 01-Apr-96 OP Bldg Phone: (604) 888-665 Light in control room not operating correctly Tighten electrical outlet wall plates Submit load test reports Company: Aeros Mechanical Contracting Contact: Anu Bo or SafdarAlam Phone: (604)666-224 New HVAC 02-Apr-96 Level 1 Grill bent in room 122 $2,500 28-Mar-96 $400 $1,500 31-Mar-96 $250 $1,000 $475 Figure F.8 Punch Lists Contractor: Ae Bee Cee Contracting Proj. Manager: John Cook Engineer: XYZ Engineering, Inc. RFI # Title RFI Description From Vendor Phone Responsible Dept: Civil PMICS Requests for Information By Participant Project Name: To Vendor Phone Initiation Date Response Date Days Taken 277 Print Date: 4/10/96 Project #: 1 City Warehouse Building Page #: 1 File Location Status Painting Ae Bee Cee Contrac City Warehouse (604) 666-2233 (604) 555-8122 Do all metal boxes get paint? 5/7/95 5/12/95 Yes, all metal boxes get paint. 1-RFI-2 Done Concrete Super Readymix Co. City Warehouse (604) 444-2222 (604) 555-8122 Can manholes be sealed with mastic? 5/23/95 5/29/95 6 1-RFI-6 No, appropriate sealing method to be employed. Done 7 Demolition methods Destruction Co. City Warehouse (604) 666-2240 (604) 555-8122 Questions on demolition methods. 5/25/95 5/31/95 6 1-RFI-7 Refer to the method statements attached. Done Responsible Dept: Electrical Electrical service Electrical Contractin City Roads Dept. (604) 444-1111 (604) 555-8122 Do all buried pipe receive cathodic protection? 5/10/95 5/16/95 6 2-RFI-1 Yes, they do. Follow the specifications for cathodic Done 3 Elecetrical handhole Globe Electricals, In City Warehouse (604) 888-6655 (604) 555-8122 Do all electrical handholes require drainage? 5/7/95 5/13/95 6 1-RFI-3 Yes, all electrical handholes require drainage. Done 4 Electrical systems Globe Electricals, In City Warehouse (604) 888-6655 (604) 555-8122 Do DB conduits require coating? 5/12/95 5/18/95 6 1-RFI-4 Not necessarily. Done Figure F.9 Requests for Information Report 278 Contractor: Ae Bee Cee Contracting PMICS Print Date: 4/10/96 Proj. Manager: John Cook ProJect #; 1 Engineer: XYZ Engineering, Inc. Project Name: City Warehouse Building Shop Drawing Sumittals Due Page #: 1 Scheduled Date Remaining Trade Subcontractor Contacts Phone Activity Submission Days Status Shop Drawing*: 1-SD-9.15 Description: plan and sections Ceilings Civil Contracting Co. Don Holland (604)444-1234 15-Apr-96 01-Apr-96 -9 Due Brian Whisker Shop Drawing*: 1-SD-9.13 Description: plan and sections Drywall & Partition Civil Contracting Co. Don Holland (604)444-1234 15-Apr-96 01-Apr-96 -9 Due Brian Whisker Brian Whisker Brian Whisker Brian Whisker Shop Drawing*: 1-SD-9.21 Description: plan and elevation details Tiling Civil Contracting Co. Don Holland (604)444-1234 15-Apr-96 01-Apr-96 -9 Due Shop Drawing*: 1-SD-9.14 Description: Drywall & Partition Civil Contracting Co. Don Holland (604)444-1234 18-Apr-96 04-Apr-96 -6 Due Shop Drawing*: 1-SD-15.1 Description: plan and sections Plumbing Aeros Mechanical Cont Anu Bo (604)666-2244 15-May-96 l0-Apr-96 0 Due Safdar Alam Shop Drawing*: 1-SD-9.11 Description: layout details Drywall & Partition Civil Contracting Co. Don Holland (604)444-1234 25-Apr-96 11-Apr-96 1 Due Shop Drawing*: 1-SD-15.3 Description: plan and sections HVAC Aeros Mechanical Cont Anu Bo (604)666-2244 25-May-96 20-Apr-96 10 Due Safdar Alam Shop Drawing*: 1-SD-15.4 Description: details Fire Protection Aeros Mechanical Cont Anu Bo (604)666-2244 01-Jun-96 27-Apr-96 17 Due Safdar Alam Shop Drawing*: 1-SD-9.27 Description: layout details Special Flooring Civil Contracting Co. Don Holland (604)444-1234 15-May-96 01-May-96 21 Due Brian Whisker Figure F.10 Shop Drawing Submittals Due Lists 279 Contractor: Ae Bee Cee Contracting PMICS Print Date: 4/10/96 Proj. Manager: John Cook Project #: 1 Engineer: XYZ Engineering, Inc. Shop Drawing SumittalS Status Project Name: City Warehouse Building By Trade or Package Page#: 1 Subcontractor Shop Drwg # Keyword Description Scheduled Date Remaining Start Submission Days Trade Name: Ceilings Civil Contracting Co. Contact: Don Holland or Brian Whisker (604)444-1234 1-SD-9.15 plan and sections 15-Apr-96 01-Apr-96 -9 Trade Name: Demolition Ae Bee Cee Contracting Contact: Allan Maxwell or Dick Wise (604)666-2233 1-SD-2.1 plan & location for detonator 05-Feb-95 22-Jan-95 -444 Status Due Approved Trade Name: Doors & Windows Grade One Doors & Windows Contact: Mike Noble or Mike Noble (604)666-3322 1-SD-8.1 plan and elevation details 15-Apr-96 25-Mar-96 -16 Revise and resubmit 1-SD-8.2 plan and elevation details 20-Apr-96 30-Mar-96 -11 New Trade Name: Diywall & Partition Civil Contracting Co. Contact: Don Holland or Brian Whisker (604) 444-1234 1-SD-9.11 layout details 25-Apr-96 11-Apr-96 1 Due 1-SD-9.13 plan and sections 15-Apr-96 01-Apr-96 -9 Due 1-SD-9.14 18-Apr-96 04-Apr-96 -6 Due Trade Name: Electrical Globe Electricals, Inc. Contact: Mahmood Has or Allan Jo (604) 888-6655 1-SD-16.1 plan and sections 15-Jun-96 11-May-96 31 Due 1-SD-16.2 conduit layout plan and details 15-Jun-96 11-May-96 31 Due Figure F.11 Shop Drawing Submittals Status Report 

Cite

Citation Scheme:

        

Citations by CSL (citeproc-js)

Usage Statistics

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