@prefix vivo: . @prefix edm: . @prefix ns0: . @prefix dcterms: . @prefix dc: . @prefix skos: . vivo:departmentOrSchool "Applied Science, Faculty of"@en, "Civil Engineering, Department of"@en ; edm:dataProvider "DSpace"@en ; ns0:degreeCampus "UBCV"@en ; dcterms:creator "Shahid, Syed Mohammad"@en ; dcterms:issued "2009-02-11T19:02:37Z"@en, "1996"@en ; vivo:relatedDegree "Master of Applied Science - MASc"@en ; ns0:degreeGrantor "University of British Columbia"@en ; dcterms:description """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 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."""@en ; edm:aggregatedCHO "https://circle.library.ubc.ca/rest/handle/2429/4440?expand=metadata"@en ; dcterms:extent "14562398 bytes"@en ; dc:format "application/pdf"@en ; skos:note "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 f^equired 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/-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 N I A M (Nijssen's Information Analysis Methodology) modeling methodology for a relational database model. 2.2.6 Computerized Change Management The inevitable changes on construction projects requires an administrative process, which can be streamlined with computer applications. [Krone 93] discusses the benefits of managing construction change orders with computers over manual methods. The automated method avoids management disorder by maintaining records, providing a clear and timely reminder of who is responsible for the next step in the change procedure, and providing standardized reports. 39 [Leymeister et al 93] describes a computer application for analyzing and managing change order work at several projects. They illustrate how a well planned database application on a personal computer (PC) proved extremely useful and versatile in processing the voluminous amount of data involved. They also discuss several types of databases which were developed for specific functions needed to analyze the data submitted for each of the contractor's claims. The types of databases used were: labor and equipment database, material database, subcontractor database, labor database, and linked labor and equipment database. 2.2.7 Claims Analysis One of the problems associated with conducting a claims analysis is the meticulous sorting of project documentation to ascertain pertinent delay information. [Mazerolle & Alkass 93] propose a database management system in a project control process to store information on each delay when it occurs. They further outline the advantage of such system, such as: keeping track of delay information; easy retrieval; and assistance in ascertaining the type of delay, indicating which party is liable, and what actions should be taken. 40 2.2.8 Photo Records [Hiroshi & Nobuoh 93] describes a filing system of construction pictures and its integration with a database. Here the proposed system is claimed to have the capability of supplying construction information through joint operation with existing databases which can perform multi-phase retrieval and index transaction. [Maher 78] suggests that the statement, \"A picture is worth a thousand words\", be given practical effect by using photograph to help solve the problems occasioned by time-delays in construction projects. Acceptance of a photographic record, properly formed and used to present evidence in time delay situations, would be one method of saving time and money. 2.3 Information and Construction Projects 2.3.1 Construction Personnel Role and Information Needs [Lock 93] states that the type of information required by an organization depends largely on its business and its people, and it is this organizational environment that determines what information must be provided, how it is to be organized, formatted, and accessed to be useful for aiding decision-making and for the general management process. 41 [Sanvido & Paulson 92] state that as people progress from lower levels in a site organization to higher levels, they use different information, skills, and knowledge to make new decisions. [Sanvido 88] developed a model representing the functions performed by each level in a hierarchical organization. He also defined the feedback and information required to support the control and planning functions. [Tenah 86] acknowledges that there is a lack of information in the construction industry regarding functions, responsibilities, and information needs of construction personnel, whereas a manager or supervisor cannot perform his/her functions efficiently without proper information on which to base decisions. He further discusses how the information required by the construction personnel is organized into reports, the contents of the reports, the purpose(s) they serve, and the frequency at which they are issued. In the same paper, he presents results of a field study conducted to determine the information needs of projects and their functional managers. In his concluding remarks he says that, since management functions and management information needs are inextricably linked, there is the need to redefine personnel functions, responsibilities and information needs after revising each organizational structure. In any project management system, the people who make up that system should be aware of their precise role. The project responsibility matrix is a means of achieving this. In the project responsibility matrix all functional specialists who have a role to play occupy a position in the matrix. [Tenah 86] and [Deatherage 64] describe some of the roles and 42 information needs of key construction personnel. As part of this thesis, Appendix-A presents matrices of construction personnel versus functions and construction personnel versus information needs. 2.3.2 Traditional Information Flow Framework [Goldhaber et al 77] illustrate a traditional framework of information flow in a construction industry (Figure 2.3). In such a system, it is assumed that all parties and levels of management have the same information and that all must come to the same conclusion. The same reports are generated and distributed to all levels of management. These assumptions do not reflect the actual conditions and information needs of the decision makers. Different decisions are made at different levels. Unless meaningful and timely information is generated, there is no justification of an information system. 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 Definit ion The concept of information is related to facts, data and knowledge. A fact is something that has happened in the real world and that can be verified [Barton 85]. Data are facts obtained through empirical research or observation. Knowledge is facts or data represented in some way (e.g., reports, lists, letters, etc.) and stored for future reference. Information represents data or knowledge evaluated for specific use. Consequently, facts or data are processed to provide meaningful information. [Murdick & Ross 75] defines information as \"the behavior-initiating stimuli between sender and receiver and in the form of signs that are coded representations of data\". [Tenah 83] states that the information is a behavior-affected data. Data differ from information in a sense that data are considered signs, usually recorded observations, that do not affect the behavior of men or machines. However, data may become information if behavior becomes affected. For example, the database for computer systems consists of masses of such signs that are not affecting behavior. Until the data are properly organized for a manager so that he/she reacts to them, they are not information. Thus management information is not just the forms and reports produced. It includes all the data and intelligence—cost, financial, schedule, trend forecast, and control of a particular project or job, as well as the organization as a whole. 45 [Aoki et al 93] define information not only as measurement data, but also as expertise and construction data collected from previous projects. 2.4.2 Management Information and the Construction Industry Several authors have emphasized the importance of accurate and timely information for construction project management, e.g. status of project resources [Rasdorf & Herbert 88], labor and equipment costs [Pierce 88], building product information [Coker 85], overseas projects [Fanous & Samara 94], and project control [Liu et al 94a]. [Fischer et al 94] stress that project managers must devote a significant portion of their attention to the management of information, i.e., ensuring that information is available in a consistent and accurate form and in a timely manner. While giving a background of information systems [Riley & Sabet 94] explain that a construction project involves a large number of individuals, firms, and organizations and develops a considerable amount of information; it is the way in which this information flows between the participants in the project that has a considerable impact on the success of the project. [Fanous & Samara 94] and [Pierce 88] describe that the successful management of engineering and construction projects requires coordination, and control of many diverse activities performed by specialists assigned to the project. Successful coordination and control of these activities require the project manager and his staff to have access to accurate and 46 current information regarding all facets of project activities. It is also essential that the specialists in each area of the project have the information they need from other disciplines in order to perform their own work effectively and maximize their contribution to the project. In their paper, [Couzen et al 9 3 ] explain that the day-to-day running and strategic planning of any organizations relies upon effective and efficient decision making by the organization's executive and senior management, and that the information forms the basis for such strategic decision making. [Vanegas 9 4 ] writes that during the construction phase of a building project, the different members of the project team, namely, owner, designer, and contractors (including subcontractors and suppliers), need to communicate with each other; exchange technical and management data and information; analyze and comment on these information to resolve issues and make decisions; and, when necessary, negotiate to reach agreements among all parties. Thus, the quality of the total project team decision-making and implementation process is a direct result of the availability and reliability of information. While introducing information technology, [Vanier et al 9 3 ] describe the importance of information in the construction industry. They state that the construction industry is information intensive, and it needs accurate, reliable, and timely information, ranging from legal requirements, building codes and standards, through scientific research, to 47 manufacturers' product information, and including, finally, site-specific data and past project information. They further state that the need of information is becoming more urgent and more critical as the projects are becoming more complex, and the time frame for decision-making is becoming shorter. They write that there are many reasons for these changes, including increasingly complex projects, the debilitating cost of long term project, and shifting demographic and client requirements. Further, they quote from (Mackinder et al 1982) that the task of managing the vast quantity and variety of information in a professional and timely manner represents a very considerable investment on the part of the construction industry around the world. Information has always been an important ingredient of construction management, which relies on relevant data for effective management in all its operations [Lock 93]. What is new, however, is the increased pressure on managers to process information more effectively and to integrate them with schedules, finance, market, contractor, and operational data. [Bhandari 78] writes that, increasingly, we depend on meaningful and relevant information for the growth and health of our endeavors, and for the smooth functioning of our institutions. Information is one of the few resources not in danger of exhaustion on the shrinking planet. It is unique because the supply is limitless. One of the primary reasons for information having proved to be such a dynamic resource is the fact that there exists a remarkable technological capacity for dealing with it rapidly and effectively. 48 [Burger & Halpin 77] explain that the super-projects pose new and complex logistical and management problems for project managers. The information at the project level is enormous and traditional methods of information handling are not adequate to meet the needs of new management in this environment. Managers need help in controlling information flow at the project level between the project personnel and the participating groups. Hence, new tools are required for the management of information on large construction projects. In recent years the construction industry has started to take advantage of new developments in information technology, particularly in the field of construction management. 2.4.3 Current Information Problems [Evt 92] states that the key features of the typical information-handling problems encountered in construction projects are the following: • Each project tends to be unique. A lot of information is specific to the project at hand. 4 9 • 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 R i and R 2 is defined over a subset of their attributes X i and X 2 with common domains. An ownership connection between an owner relation R i and an owned relation R 2 describes the dependency of the multiple owned tuples on a single owner tuple. The ownership connection implies that the owned tuples are specifically related to and dependent on a single owner tuple. As an example, a building structure may consist of structural elements, floor, foundation, roof, space, etc. The components exist if the building exists. This connection type specifies the following constraints: • Every tuple in R 2 must be connected to an owning tuple in R i . • Deletion of an owning tuple in R i requires deletion of all tuples connected to that tuple in R 2 . • Modification of X i in an owning tuple of R i requires either propagating the modification to attributes X 2 of all tuples in R 2 or deleting those tuples. A reference connection between a primary (referencing) relation R i and a foreign (referenced) relation R 2 describes the dependency of multiple primary tuples on the same 58 foreign tuple. As an example, an architectural space, which may be an office, elevator, opening, etc., locates on a floor. The floor can not be removed without first removing the spaces defined on that floor. This connection type specifies the following constraints: • Every tuple in R i must either be connected to a reference tuple in R 2 or have null values for its attributes X i . • Deletion of a tuple in R 2 requires either deletion of its referencing tuples in R i , assignment of null values to attributes X x of all the referencing tuples in R i , or assignment of new valid values to attributes X i of all referencing tuples corresponding to an existing tuple in R 2 . • Modification of X 2 in a referenced tuple R 2 requires either propagating the modification to attributes X i of all referencing tuples in R i , assigning null values to attributes X i of all referencing tuples in R i , or deleting those tuples. A subset connection between a general relation R i and a subset relation R 2 links general classes to their subclasses and describes the dependency of a single tuple in a subset on a single general tuple. For example, a structural element can either be a column, a beam, a wall, or a slab. Furthermore, a beam can be generalized to be either a main girder or a joist (secondary beam). Deleting a specific instance in the generic class of structural element 59 must delete the corresponding instance existing in the subclass. The subset connection specifies the following constraints: • Every tuple in R 2 must be connected to one tuple in R i . • Deletion of a tuple in R i requires deletion of the connected tuple in R 2 . • Modification of X i in a tuple of R i requires either propagating the modification to attributes X 2 of its connected tuple in R 2 or deleting the tuple in R i . 2.4.10 Users Interface [Townsend 92] defines different interface terms as follows: • Interface: The way that users communicate with a computer system. In a database, the interface consists of elements such as menus, forms, tables, reports, and queries. • Graphical User Interface (GUI): A design in which a computer interacts with a user by means of graphical menus and symbols rather than a command language. Microsoft Windows, the Apple Macintosh, and GeoWorks are examples of graphical user interface. 60 • Programming Interface: A user interface based on a programming language especially suited to database applications. The first PC database packages to appear were based on programming languages. • Spreadsheet Interface: This is one of the world's most popular user interfaces. • Form-Oriented Interface: 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 M U L T R O L , a multimedia project control and documentation system [Liu et al 94], was developed for the PC platform and runs under Microsoft Windows™ operating environment. This system uses a graphical user interface for all operations, creating a 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. 6 4 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. 6 5 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 7 6 during the construction phase of a project. It will be simple but will realistically demonstrate all database functions. At this stage, all the forms, reports, menus, and queries will be designed, with frequent user-views evaluation. Following the results of evaluations of the forms and reports, the database will be refined, as required. Performance Evaluation: involves monitoring the performance of the system. The system performance for typical queries will be estimated. System Hardware/ Software Selection Prototype System Development Performance Evaluation Figure 3.5 Phase TV - Implementation Phase 77 3.3 PMICS - Project Management Information Control System An important part of this research is a prototype system. This section outlines the phased development methodology. This methodology is the result of methodologies present in the literature. The prototype system development and implementation involves almost all the steps mentioned in section 3.2. However, the system analysis and design (Chapter 4) does not completely follow the same sequence as outlined in section 3.2. 3.3.1 Objectives of PMICS The main objective of this program of research is the development of a prototype information control system for a construction firm. The prototype focuses on the support of the important information requirements of one particular aspect of a construction organization, i.e., information control. The PMICS also serves, however, as a template for similar development on a wider scale, rather than focusing on the information during the construction phase only. 3.3.2 Planning for PMICS This research uses, as a case study, an information tracking database application for an imaginary construction organization, A B C Construction Company. Although the example 78 is hypothetical, it does contain many of the elements of a real construction environment. Section 4.1.1 describes the business environment and strategy. Section 4.1.2 includes the description of organization's existing information system, its information needs, and data distribution plan. Section 4.1.3 presents information flow-diagrams. 3.3.3 Requirements of 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 8 0 of cost accounts and other data. With the advancement in computer technology as well as in data base management systems (DBMS), it is possible to develop a computerized database for even small organizations and projects. 4.1.1 Description of Users This system is intended to be used by managers of the fictious A B C Construction Company, a small to medium-sized construction company. Although this company is a hypothetical, it does contain many of the elements of a real construction company. The company works as a general contractor on most of its ongoing projects and employs a number of trade subcontractors. An organizational chart for A B C Construction Company is shown in Figure 4.1. This company employs a number of managers to manage projects. Each manager is assigned specified functions and responsibilities. Every manager prepares or receives a number of periodic reports, including progress reports, daily reports, cost reports, project control reports, etc. Currently, the company has computers to do accounting, word processing, and spreadsheet procedures at their home office as well as at all project sites. Some of the information is maintained by individual managers or staff, either manually or using software less suited to database management. 81 General Manager Chief 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 A B C 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 I I 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 E E DS MATRIX M-3) 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 i n 69 69 i n 69 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 •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 C L Q . E CO Q cn c oo X3 C D co l i <* 8 D) C \\£ a, ig O m ° Q > CC o \"a e l w p- ^ o o Q x: cu co XI II CO g — y . c o 5 E 5 co CT CD a o T 3 ^ 8 *- _ V 0) C31 *-CO ' CO _ D) P O I S 2 8 Q . X i l LU T - N C O t m i O M O O l O T - N C O ^ i n i D S C O 106 This view provides bill of quantities information to the Construction Manager, Chief Estimator, Procurement Manager, Project Manager, Project Engineer, Planning and Scheduling Engineer, Cost Engineer, Estimator, Field Engineer, Superintendent, and Foremen. The following queries are typical examples for this view of the data: Construction Manager, Chief Estimator, Project Manager, Project Engineer, Estimator, and Accountant: • Find the quantity of an item. The output should have the project id, project name, WBS id, and item description, quantity and unit. • Find the item cost and cost summary. The output should have the project id, project name, WBS id, item description and cost, subtotal for a division of work or trade, and grand total. Procurement Manager and Cost Engineer: • Find the quantity, cost summary, and budget of an item. The output should have the project id, project name, WBS id, item (description, cost, budget, and markup), subtotal for a division of work or trade, and grand total. 107 Planning and Scheduling Engineer, Field Engineer, Superintendent, and Foreman: • Find the quantity of an item. The output should have the project id, project name, WBS id, and item description, quantity and unit. It is assumed that only one table is maintained for the Bill of Quantities of all the projects or sites executed by this organization. Also, a particular work item is identified by both ProjectlD and W B S I D . The Bill of Quantities (BoQ) is expressed in the following relations: BoQ (ProjectlD, Project Name, (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 t o < 3 g C Q o zz D _i D CD LU CO Z> o X LU or < i— o » O o 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— Q j 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 I l l 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 U J a or o U J O z < X o o o o # o o o CO d c i d d o CO o \" LO in o 5 LO in\" § 11 i t s IS 9 O L; o !• !R \" o a> c O o o o a: a: a. o. < £ o .g ° £ re co « *° I I S e O O .N \"c to IJsJ 13 B 3 tj 1 111 CD CD C '5 •C C O « i (N O u. u_ 5 < 8 8 8 8 8 8 8 8 o 5 IO m CN IS*\" (CJ W » W V» 8 8 8 § 8 8 8 ° o> Co in r- 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: C O T L (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)))))) 1 1 9 Table 4.4 K e y Terms for Change Orders Tracking L o g Key Terms Definitions OCBP Original contract bid price in $ A C O authorized change orders in $ RCP revised contract price in $ CO%C change order as % of the contract bid price OCCD original contract completion date C D A 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 C M R 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^ \\ ^ i n 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 H I 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. ra ra a. a. c C 3 3 « O M « E E E E P 9 P P t: ii t: i: ° J2 to _ l _l _l _l O U- _l - J —i - J w o £ w Q w o o CC f| t3 CO g S o f c o a w c c g < t g Q g g o g g i s8 03 g o 5 i * i * f £ O c K o ; £ f £ c £ a : L y ^ W W w w W W w i i J D_D_ — XQ_Q_ — — Q_Q_ Q . Q . O O Q . Q . O O Q . D . C 0 C O U J < c / ] C O l l J L U t 0 C / ) r§ n i l l < < LU g o >• .2 6 f t . _S\"5 2 O (I • ' c J I\" I I t i 1 u n o 5 o CO C C 3 O < < LU LU O Q o - a> . i - oi | • E ti . S E t , _ c l g g g g g g g g g 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 - i M O V i n c B N o o o i O ' - c N O ^ i n c D N i i o o i o 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 Q L 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 I g g g g g g g x g <<<<<<<<<< ffl rfl rn rp fp m m ffl ffl o>0)wC7>o>o>a>Cipcncn c c c c c c c c c c ( o n n m n n n n i o n CN N P ) r f -t cu > JQ m • T~ i - N n v i n i o N o o O ' - i N n t ^ i D s c o o i o 125 • Find the originator or receiver name. The output should have the project id, correspondence number, reference number, from people id, to people id. • Find the original date and receiving date. The output should have the project id, correspondence number and date, reference number, receiving date. • Locate a filed correspondence or submittal. The output should have the project id, correspondence number, reference number, file reference no. To simplify the presentation it is assumed that the organization has several ongoing projects, and only one Correspondence Tracking Log is maintained to record all incoming or outgoing correspondence. Also, it assumed that the Corr# (correspondence identifier) is a unique serial number of correspondence for a project (including both incoming or outgoing). A particular correspondence is identified by both ProjectID and Corr# . The user view for the Correspondence Tracking Log (CTL) is expressed as follows: C T L (ProjectID, Project Name, (Corr#, Referenced CorrDate, Date Sent, DateRcvd, Follow up, (From People, To People, (From Company, To Company, (From Participant Type, To Participant Type))), Keyword 1, Keyword2, Notes, File Ref. no.*)) 126 Table 4.5 Key Terms for Correspondence Tracking Log Key Terms Definitions Corr# 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 L o g 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 X Y Z X Y Z 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 C o I D FFC2 field force contractor-2 C o I D FFC3 field force contractor-3 CoJLD FFC4 field force contractor-4 C o I D 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 t i n LLI O S o O 2 •fi 1.1 O S <» O CJ Q/ ^- <0 $ IS § . E E s ra *: > LLI LLI ra cc Q r r i r O L O W N C O r O ) u ^ i n i o i n i n ^ ^ L n L j Q L r ) C n n C f l C n C n w C A w w O ) c c c c c= c c c c c c c c c c L U U J U J I I J L J U J U J L U U J U J 3 3 3 3 3 •c- CM < N g S5 un uj in ^ - T - CM CN o o o o U) IA Ul O c~ co •*»• co 3 3 3 3 3 3 3 3 3 r Z c ^ c i i A S ^ ' 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 o o o O O c o Dl CO -s i * 1 § O Q - U 5 Q . \" c 0 L L J c j o o) .9-o o o o o o o o o o O N O O O O O d J O J) c N c o ^ c o i n ^ i ' ^ r ^ - T - ' * -c o c o i o c o i n i n c N e N c o h . 0 0 - - 0 0 . - 0 0 0 0 i n i o i n i n i n i n i n t o t n i n c » o ) c n c n o ) 0 ) 0 ) 0 ) u ) 0 ) 3 3 3 3 3 c 6 c N i o u S c i « l A < 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: D W N T L (ProjectlD. Project Name, (EngineerlD, Engr Name, (Engr Contact), Engr Phone), (DWN#, NoticeDate, Description, DateRcvd, EstimatValue, Date Reinspected, Reinspected By, D-NoCR, TDfC*, Comments, File Ref. 136 no.*, (SpecsDD, Specs Section, (WBSID, Item Description, (TradelD, Trade Name, (SubID, Sub Name, Sub Contact, Sub Phone)))))) Table 4.7 K e y Terms for D W N Tracking L o g Key Terms Definitions DWN# project's D W N entry serial number NoticeDate D W N issue date DateRcvd date D W N received Estimat Value estimated value of the D W N item DateReinsp date the D W N 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 D W N can be identified by both ProjectlD and DWN# (Table C.8, Appendix C). A D W N will be referred to a single specs section. A specs section may appear on many DWN. As the Figure 4.20 is similar to the Figure 4.14., the relationships of entities specs section, WBS, trade, proj-trade, project, PTD, company and people are the same as explained in the last paragraph of section 4.2.3. 137 Figure 4.20 E/R Diagram for Defective Work Notifications Tracking Log 4.4.7 View 7; Materials Stored Tracking Log This view describes materials delivered to the site but not yet incorporated. Most construction projects have construction materials stored at site or at another predetermined locations. Information about the availability of materials is always helpful in order for construction to proceed smoothly. Some contracts even allow for partial or full 138 payment of materials stored. When costly items requiring long lead times to fabricate are delivered to the site, it is often customary to ask for partial payment for such items while they are stored at the site. The remaining value is then paid only after they have finally been installed in the work. A Materials Stored Tracking Log (Figure 4.21) is useful in tracking down stored materials, their location and value. This view provides the Project Manager, Project Engineer, Purchasing Agent, Estimator, and Superintendent information about materials delivered but not yet incorporated into the work. The following queries are typical examples for this view of the data: Project Manager, Project Engineer, Purchasing Agent, and Superintendent: • Find the status of materials on the site. To get materials if required. The output should have the project id, material description, supplier id, supplier name, supplier phone number, stored location, original quantity stored, quantity installed to-date, location installed, quantity currently stored. 139 o o o o LO LO CO CO T t of T f LO CM CM 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— o . p. in i - \" T~ cj) r--~ oo\" CN -r-* CN 0) o> CO CN CO (N CO T - T -f^t\" (/^ o o o o o o o o o o O O O O O L O L O O O l f i L O O C O O O O m O O - ? 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 c M c ^ c ^ c o c o c o ^ f c o T r T r ^ ^ T - C M C N C M C O C S C O C O 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 2 g .•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 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: M S T L (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 K e y Terms for Materials Stored Tracking L o g 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 . § . a i CO CO LU a c i i . z is a. o o o o o o o o o o o u o o o o o o o o c N o m o o ) 8\" o\" io\" r - \" g\" r-~ of T - \" CO CN CN 00 CD Cft i -v / v / vy V 7 v 7 v / o o a i n co r~-CN •tf T - CN Cft Vi o IO CN o\" CN Vi o o p i n p o o p C D ^ C N C N C N C O C N C N 8SS88SS8 O N N O O M S C O O IO 10 S Q CN T \" T - T - I - ^ i D c o i n c o c D i o n Vi Vi Vi Vi Vi o o o 8 8 8 o\" If) UI o r» CN - r- «-JO m jo 2 3 <* 3 « o ^ o 2 fe c § 8 § 8 =5 O co m co o j i c w g l * 5 -8 5 i - N l O ^ i n i O N C O O l O ' - t M n ^ l D I O S O 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 CO LU CO Z) o LU < O T _ w » o o CD CD CL CL CD . i J= (5 ° > c -2 C O > L U O > > N O ~ >- CQ O X < CD \"t> CD CO CD c > O) c < <= ° O H I O 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> t i 1 CO o ,2 < LU o LU cc o r - O Q S O Q S _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 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 - M O ^ K l l D S t O d l O 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 o f items, usually minor, prepared by the owner or engineer, that must be completed by the contractor before a project is finally accepted. In other words, it is a guide list during finishing stages o f a contract to indicate all work or corrective action remaining before acceptance of the work. The quantity o f 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 o f this critical close-out requirement, a definitive method of recording, tracking and reporting punch list items is a necessity o f 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 o f the work to be done, the person generating the item, an estimated dollar value o f the work, the date each punch list item was rechecked, and the person rechecking the item. This view provides punch list information to the Project Manager, Project Engineer, Field Engineer, and Superintendent. 153 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 U J 3 dl 5 O OJ 03 r c h £ c to c o > UJ U y N O £ > 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 ( U V I I ) ( U < U ( 1 ) < 1 ) < D w < D ( U Q J < D < D < D < l > < l ) C C C C C C _C C C C Q ) D ) 0 ) O ) D ) D ) G O ) D ) O ) c c c c c c c c c c U J I l J U J L U L U U J L U L U U J L L l < 7 > 0 0 > 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 ( 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ( 0 ( 0 ( 1 ) 1 0 ( 0 ( 0 ( 0 1 0 ( 0 ( 0 uSiAc6cDcir>-r i i>-c6oi u m d i v v n i i u t t v a i Q J C O C O C U C U C U C U C U C U C U c c c c c c c c c c . c o c o c o c n c o c o c o c o c o c o c c c c c c c c c c u J U J U J u J L J L l J L L J U J U J L L J i f i o i n L n L n L O i n i n i n i r ) C)cn0)O>c7)O)O)c>o)C) 0 . 0 . 0 . 0 . 0 . 0 . 0 - 0 . 0 . 0 . < < < < < < < < < : < : ySiAi/SuSuSinuSioioLo N N N N N f M C M C M C M N CO CO c C •5 u o j= S 8 J2 c D ) - - . 8 c <= ca c -£ •= o ~ o £ * o C o u a; 8 •§) -2 .8 -2 8 5 UJ ui c p 8 | 5 5 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 g o o D) Q. J -— CO CO co +-* o C CO c = D ra co TJ* ra U < CO O I U U J I O U I O 1 1 1 5 1 <<<<<<<<<< 8 8 8 8 8 . 8 . 8 . 8 . 8 8 0 0 0 0 0 0 0 0 0 0 (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 a)cnc7)C7) c c c c c c c c c c c c a c a c a r a c o c a c o c o c o c a c a c o c o i A c o c n c i c i i A c N ^ c o ^ ^ ^ T - T - C M C M C M C M C M N m i n i n i n i n u i i n i n u i i n m o i c n c n o i c n c f l c p c x c n c n c f ) c c c c c c c c c c c c o c o c o c o c a c o c a c o c a c o c t i —3 —3 —3 —3 3 5 3 3 3 3 ~^ 3 i^uScAciiicicNCNcori \"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 : £ l c ! H e I C to o i o O > Q 5 CJ> CN CO CN ± 6 6 ?v2 2 0 o o o «- T -O O O O O O O O O O C ) c t J C B c a t S f S c o c a f a t s t o c o E E E J J | E E J J | lo lo l f lmlototo' to ' to 'co ' t i ) U J U J U J L U L i J L U l l J L l J U J L U L l J c n c n c n m c n c o r a C n O i C n C n 1 1 1 i 1 1 1 1 • • 1 c c c c c c c c c c c C O C O C O C O C O t O t S t O f O C O f O cfSifScAciSicicNCMtAri T - N l O ^ i n t O M D 0 ) O t - C N C 0 T r i 0 C D I ^ 0 0 0 ) O x - C N 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° i l l 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 W B S T D together (Table C.20, Appendix C), as could a PID (Table C.14, Appendix C). A SDS is related to a single PID, and vise versa. A shop drawing log lists each SDS. As part of the Figure 4.32 is similar to the Figure 4.14., the relationships of entities specs section, WBS, trade, proj-trade, project, 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 . . m o 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 r i < i O C N C N C N C N i C O f— C D « U ) W W W t f l C » « O ) C 0 o o o o o 2 o o o o o < D Q ) C U C U C U C U < U C L ) O O C L ) _ rj — — s w s s Li CJ F O '1% i CO OJ ! \" C C O ra - 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^ \\ ^ i n Project TradeDetails c^orresponds 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 d^etails 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 n e e d 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 E R M and SDM can be explained as follows: • E R M emphasizes the relationships between entities. SDM formalizes the relationships between entities. • E R M 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). • E R M distinguishes between entities and relationships. S D M does not make any major distinctions between entities and relationships; a relationship is regarded as an entity. • E R M does not provide constraints and integrity details precisely. SDM provides constraints and the integrity rules. • E R M provides a map of the information system, and also acts like a process model. In SDM, relations are used to capture data about objects and their parts. • E R M is more flexible. It allows to create entities for M : M relationships, and uses normalized relations. 176 • E/R diagrams for an organization may become voluminous. S D M diagrams require less space. The following sections, 4.5.1 to 4.5.13, present the normalized relations in third normal form (3NF) or local data model and structural data model diagram for each user view. Section 4.5.14 presents the structural data model diagram for an integrated (user) view. The underlined attributes are prime keys. Each sub-section is divided into three parts. The first part presents a set o f entities or relations derived from the relations, as expressed in sections 4.4.1 to 4.4.13, for each user view. 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 ( S D M ) diagram. 4.5.1 View 1: Bi l l of Quantities The initial relations derived from the entity-relationship model, as shown in section 4.4.1, are as follows: Project (ProjectID. Project Name) 177 Bill of Quantities (ProjectlD. WBS ID. Item Description, Quantity, Unit, Unit Price) Project ItemDetails (ProjectlD. WBS ID. TradelD, Trade Name) The local data model or normalized relations for the above view are as follows: Project (ProjectlD. Project Name) WBS (WBS ID. Item Description, Unit, TradelD) Trade (TradelD. Trade Name) Project ItemDetails (ProjectlD. WBS ID. Quantity, Unit Price) The system control derived from the semantics required by the users is shown in Fig. 4.36. Project 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, B i d Due Date) Bids (ProjectID. TradelD. Bidder lD. Bidder Name, Bidder Phone, B i d Pkg. Sent, B i d Received, B i d Amount, Remarks) Bidder Type (ProjectID. TradelD. Bidder lD. Bidder Type) Bidder Contact (ProiectlD. TradelD. Bidder lD. Bidder Contact (=PeopleID)) The local data model or normalized relations for the above view are as follows: Project (ProjectID. Project Name) Trade (TradelD. Trade Name) Project TradeDetails (ProjectID. TradelD. Budget, StartDate, EndDate, B i d Due Date, SubID (Co_ID), Subcontract Amount) Bids (ProjectID. TradelD. Bidder lD (=Co_TD), B i d Pkg. Sent, B i d Received, B i d Amount, Remarks) Bidder (BidderlD. Bidder Name, TypeTD, PeoplelD, Phone), or Company (Co ID. C o N a m e , 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 B i d 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, O C B P , O C C D ) 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, W B S I D ) CMRtrade (ProiectlD. CMR#, SpecsID, WBS_ID, TradelD, Trade Name) CMRsub (ProjectID. CMR#. SpecsID, W B S I D , TradelD, SublD, Sub Name, Sub Contact, Sub Phone) The local data model or normalized relations for the above view are as follows: Project (ProjectID. Project Name, 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, W B S I D ) WBS (WBS ID. Item Description, TradelD) Trade (TradelD. Trade Name) Project TradeDetails (ProjectID. TradelD. SublD (=Co_ID)) Company (Co_ID. Co_Name, TypelD, Contact 1 (=PeopleID), Contact2 (=PeopleID), Phone) People (PeoplelD. Name, C o J D ) 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 i x : 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, Fol low 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 L o g 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#, V i s i t o r l , Visitor2, Vis i tT imel , VisitTime2, Companyl , Company2, Purposel , Purpose2, EquipUse, Equipldle, Activities, N e w Act, Ac t Compl, Material Needs) DailySub (ProjectID. Date. F F C 1 , F F C 2 , F F C 3 , F F C 4 , N M F F 1 , N M F F 2 , N M F F 3 , N M F F 4 , M F F 1 , M F F 2 , M F F 3 , M F F 4 , Job l , 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, F F C 1 , F F C 2 , F F C 3 , FFC4) , N M F F 1 , N M F F 2 , N M F F 3 , N M F F 4 , M F F 1 , M F F 2 , M F F 3 , M F F 4 , Job l , Job2, Job3, Job4, V i s i t o r l , Visitor2, Vis i tT imel , VisitTime2, Companyl , Company2, Purposel , Purpose2, EquipUse, Equipldle, Activities, N e w Act , Act Compl, Material Needs) People (PeoplelD. Name, C o I D , Phone) Company ( C o I D . C o N a m e , TypelD, Contact 1 (=PeopleID), Phone) Participant Type (TypelD. Participant Type) The system control derived from the semantics required by the users is shown in Fig . 4.40. People I 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 W o r k 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 B y , D - N o C R , Comments) DWNspecs (ProiectlD. DWN#. SpecsID, Specs Section) D W N i t e m (ProiectlD. DWN#. SpecsID, W B S I D ) DWNtrade (ProjectID. D WN#. SpecsID, WBS_1D, TradelD, Trade Name) DWNsub (ProjectID. DWNtf. SpecsID, W B S I D , TradelD, SublD, Sub Name, Sub Contact, Sub Phone) The local data model or normalized relations for the above view are as follows: Project (ProjectID. Project Name, EngineerlD (=Co_ID) DefectiveWork (ProjectID. DWNtf. SpecsID, Notice Date, Description, Date Rcvd, EstimatValue, Date Reinspected, Reinspected By , D - N o C R , Comments) Specs Section (SpecsID. Specs Section, W B S I D ) W B S ( W B S ID. Item Description, TradelD) 186 Trade (TradelD. Trade Name) Project TradeDetails (ProjectlD. TradelD. SubID (=Co_ID)) Company (CoJTD. C o N a m e , TypelD, Contact 1 (=PeopleID), Contact2 (=PeopleID), Phone) People (PeoplelD. Name, C o I D ) Participant Type (TypelD. Participant Type) The system control derived from the semantics required by the users is shown in Fig . 4.41. People r Project TradeDetails sr~ Company Project Trade V r Participant Type Defective Work W B S L J T 1 D W N L o g Specs Section Figure 4.41 Structural Model Diagram for DWN Tracking log 187 4.5.7 V i e w 7: Mater ia ls Stored Track ing L o g The initial relations derived from the entity-relationship model, as shown in section 4.4.7, are as follows: Project (ProjectID. Project Name) ProjectEngr (ProjectID. EngineerlD, Engr Name, Engr Phone) EngrContact (EngineerlD. Engr Contact (=PeopleID)) MaterialsStored (ProjectID. Mat#. Item Description, Loc-Installed, Original Value, Pay Est#, Pay Est Date, % Installed) MatStorage (ProjectID. Mat#, L o c I D , Location Stored) MatSpecs (ProjectID. Mat#, SpecsID, Specs Section) Matl tem (ProjectID. Mat#, SpecsID, W B S J D ) MatTrade (ProjectID. Mat#. SpecsID, W B S I D , TradelD, Trade Name) MatSub (ProjectID. Mat#, SpecsID, W B S I D , TradelD, SublD, Supplier Name, Sub Contact, Sub Phone) The local data model or normalized relations for the above view are as follows: Project (ProjectID. Project Name, EngineerlD (=Co_ID) MaterialsStored (ProjectID. Mat#, SpecsID, Loc_ ID , Item Description, L o c -Installed, Original Value, Pay Est#, Pay Est Date, % Installed) Storage Location ( L o c I D . Location name, Address) Specs Section (SpecsID. Specs Section, W B S J L D ) 188 WBS (WBS ID. Item Description, TradelD) Trade (TradelD. Trade Name) Project TradeDetails (ProjectlD. TradelD. SubID (=Co_K>)) Company (Co ID. CoName, TypelD, Contactl (=PeopleID), Contact2 (=PeopleID), Phone) People (PeoplelD. Name, Co_ID) Participant Type (TypelD. Participant Type) The system control derived from the semantics required by the users is shown in Fig. 4.42. 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#. W B S I D , Item Description, Quantity, Unit, Unit Price) ProgressTrade (PjcjectJD, Pav Est#. WBSJD, TradelD, Trade Name) ProgressSub (ProjectlD, Pav Est#. W B S I D , TradelD, SubID, Sub Name, Sub Contact, Sub Phone) The local data model or normalized relations for the above view are as follows: Project (ProjectlD. Project Name, EngineerlD (=Co_ID) Monthly Progress (ProjectlD. Pay Est#. WBS_ID, PayEst Date, This Qntty, 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 ( C o I D . CoJSTame, TypelD, Contact 1 (=PeopleID), Contact2 (=PeopleID), Phone) People fPeoplelD. Name, C o I D ) Participant Type (TypelD. Participant Type) The system control derived from the semantics required by the users is shown in Fig. 4.43. 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, C o I D ) 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, W B S I D ) 193 PLtrade (ProjectID. PL#. SpecsID, WBS_ID, TradelD, Trade Name) PLsub (ProjectID. PL#. SpecsID, WBS_ID, TradelD, SubTD, Sub Name, Sub Contact, Sub Phone) The local data model or normalized relations for the above view are as follows: Project (ProjectID. Project Name, EngineerlD (=Co_ID) Punch Lists (ProjectID, PL#. SpecsID, PunchList Items, Facility/Area, Date Identified, Identified By, Date Completed, Date Checked, Checked By) Specs Section (SpecsID. Specs Section, WBSJLD) WBS (WBS ID. Item Description, TradelD) Trade (TradelD. Trade Name) Project TradeDetails (ProiectlD. TradelD. SublD (=Co_ID)) Company (Co ID. CoJSfame, TypelD, Contactl (=PeopleID), Contact2 (=PeopleID), Phone) People (PeoplelD. Name, C o I D ) 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 B y (=PeopleID), Initiator ID (=Co_ID)) RFIcompanyType (ProjectID. RFI#. Initiated B y (=Co_ID), Initiator Type) RFIresponse (ProjectID. RFI#. Response by (=PeopleID)) The local data model or normalized relations for the above view are as follows: Project (ProjectID. Project Name, EngineerlD (=Co_ID) R F I (ProiectlD. RFI#. Initiated By(=PeopleID), Response by(=PeopleID), Initiation Date, Drawing#, R F I Description, D-RfS , D - S t A E , D - R B f A E , D-SBtS) Company ( C o I D . 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 -^Reques ts for Information (RFI) Company ^ Projects Participant Type R F I L o g 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#. W B S I D , TradelD, Trade Name) SDsub (ProjectlD. Shop Drwg#. W B S I D , TradelD, SubID, Sub Name, Sub Contact, Sub Phone) The local data model or normalized relations for the above view are as follows: Project (ProjectlD. Project Name, EngineerlD (=Co_ID) Shop Drawing (ProjectlD. Shop Drwg#. W B S I D , Description, Disposition, D-RfS, D-StAE-I, D-StAE-R, D-StAE-F, D-RBfAE, D-SBtS, C-RfS, C-StAE, C-RBfAE, C-SBtS, Comments) WBS (WBS ID. Item Description, TradelD) 197 Trade (TradelD. Trade Name) Project ItemDetails (ProjectlD. WBS ID. Shop Drawing (yes/no)) Project TradeDetails (ProjectlD. TradelD. SublD (=Co_ID)) Company (CoID. Co_Name, TypelD, Contact 1 (=PeopleID), Contact2 (=PeopleID), Phone) People (PeoplelD. Name, C o I D ) Participant Type (TypelD. Participant Type) The system control derived from the semantics required by the users is shown in Fig. 4.47. Project TradeDetails V ~ ^ Project Trade Shop Drawing Hi. Shop Drawing Log WBS Figure 4.47 Structural Model Diagram for Shop Drawing Submittals Tracking Log 198 4.5.13 View 13: Spare Parts Tracking Log The initial relations derived from the entity-relationship model, as shown in section 4.4.13, are as follows: Project (ProjectlD. Project Name) ProjectEngr (ProjectlD. EngineerlD, Engr Name, Engr Phone) EngrContact (EngineerlD. Engr Contact (=PeopleID)) Spare Parts (ProjectlD. SP#, SP Description, E q Description, Qntty Required, Qntty Dlvd , Unit) SPstorage (ProjectlD. SP#, Loc_ ID , Location Stored) SPspecs (ProiectlD. SP#, SpecsID, Specs Section) SPitem (ProiectlD. SP#, SpecsID, W B S J D ) SPtrade (ProiectlD. SP#, SpecsID, W B S _ I D , TradelD, Trade Name) SPsub (ProjectlD. SPJ , SpecsID, W B S _ I D , TradelD, SubID, Supplier Name, Sub Contact, Sub Phone) The local data model or normalized relations for the above view are as follows: Project (ProjectlD. Project Name, EngineerlD (=Co_ID) Spare Parts (ProjectlD. SP#, SpecsID, L o c I D , SP Description, E q Description, Qntty Required, Qntty Dlvd , Unit, Status) Specs Section (SpecsID. Specs Section, W B S I D ) W B S ( W B S 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, C o I D ) Participant Type (TypelD. Participant Type) Storage Location (Loc ID. Location name, Address) The system control derived from the semantics required by the users is shown in Fig. 4.48. People XT A_ Project TradeDetails —v Company I -), ManagerlD (=PeopleID), O C B P , O C C D , StartDate, EndDate, Address, Phone, Fax) 205 Punch Lists (ProjectlD. PL#. SpecsID, PunchList Items, Facility/Area, Date Identified, Identified By, Date Completed, Date Checked, Checked By) RFI (ProjectlD. RFI#, Initiated By (=PeopleID), Response by (=PeopleID), Initiation Date, Drawing#, RFI Description, D-RfS, D-StAE, D-RBfAE, D-SBtS, Comments) Shop Drawing (ProjectlD. Shop Drwg#. W B S I D , Description, Disposition, D-RfS, D-StAE-I, D-StAE-R, D-StAE-F, D-RBfAE, D-SBtS, C-RfS, C-StAE, C-RBfAE, C-SBtS, Comments) Spare Parts (ProjectlD. SP#. SpecsID, Loc_ID, SP Description, Eq Description, Qntty Required, Qntty Dlvd, Unit, Status) Specs Section (SpecsID. W B S I D , 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, W B S ID. Quantity, Unit Price, % Markup, Delivery/Action Date, Delivery Days, Approval Days, ShopDrawing (Yes/No), MatSample (Yes/No), Catalog (Yes/No), Misc . (Yes/No), M i s c Submittals, As-Buil t Drawing (Yes/No)) Project TradeDetails (ProjectlD. TradelD. Budget, StartDate, EndDate, B i d Due Date, SublD ( C o I D ) , Subcontract Amount) Proi-Companv(ProjecfID. C o I D ) Proj-Trade (ProjectlD. TradelD) 4.6.3 Entity Cross-Reference Matrix A s a preliminary to preparing the integrated structural data model in the preceding section, as shown in Figure 4.49, it is useful to record the entities and their relationships on an entity cross-reference matrix. This is a table in which each entity wi l l appear as a row-heading and each basic entity, as described in section 4.3, wi l l appear as a column-heading. A t the intersection of the row and the column, any of the letters O, R , or S (corresponding 207 to ownership, reference, or subset) wi l l be placed i f there is a structural relationship between the entity at the head of the column and the entity at the head o f the row. The cross-reference matrix is shown in Table 4.15. The column-headings show E l to E10, as an abbreviation for each entity. The abbreviated headings are as follows: E l is for company, E 2 for Day, E3 for participant type, E 4 for people, E5 for photo type, E 6 for project, E 7 for specs section, E8 for storage location, E 9 for trade, and E10 for W B S . Table 4.15 Entity Cross-Reference Matrix E l E 2 E3 E 4 E5 E 6 E 7 E8 E 9 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 R F I R O Shop Drawing 0 R Spare Parts 0 R R Specs Section R Storage Location Trade W B S 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 wi l l be approximately 100 punch list items per project. • there wil l be approximately 100 requests for information (RFI) per project. • there wil l be approximately 30 shop drawings per project. • there wi l l 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, R A M (random access memory) for file sizes and new software requirements (database, spreadsheets, and graphics capabilities), expandability of computer system, type of printer for faster printouts, and costs. Anticipating that other project management applications will be used on the same PC, it is suggested that the PC be an IBM-compatible with an 80386 or higher processor (486/66 is recommended); 2 M B (megabyte) of R A M (4 M B or more is recommended); 240 M B or higher hard drive; E G A or V G A monitor (VGA is recommended); compatible mouse; and a laser printer (2 M B memory or higher). The PC system will have MS-DOS® version 3.1 or later, and Microsoft Windows™ version 3.0 or later. 213 It is further recommended that a commercial database management software package, such as Microsoft Access, be used because this application does not require programming. Microsoft access is designed around database objects that enable to do most of the work without programming. However, it also allows programming to create custom functions. 4.9.2 Sources of Data Input The Microsoft Access macro will be used to automate the data editing and viewing operations, as shown in Figure 4.50, as well as to aid the process of database maintenance. As per the company's requirements, the data input to the system will be through the following documents: • Project and storage location data will be obtained from existing company project description reports. • Employee and company data will be obtained from existing company address book. • Specs Section data will be obtained from any literature outlining CSI (Construction Specifications Institute) Masterformat, such as [O'Brien 90]. • Bill of Quantity (BoQ) data will be obtained from the project's work item details (estimated or extracted from contract drawings) and WBS (work 214 breakdown structure). WBS will be based on either CSI Masterformat or 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 wi l l be on screen (forms) as well as on printout reports. Data output on screen wil l be form-oriented and the user interface wi l l employ some of the gadgets o f the window operating system like fields, menus, buttons, dropdown menus, scroll bars, etc. A s required by the company, the following forms and reports wi l l be created: • Bill-of-Quantities, • B i d Summary Sheets, • Change Orders Tracking L o g , • Correspondence Tracking L o g , • Daily Site Reports, • Defective Work Notifications Tracking L o g , • Materials Stored Tracking L o g , • Monthly Progress Reports, • Photographs Tracking L o g , • Punch Lists, • Requests for Information Tracking L o g , • Shop Drawing Submittals Tracking L o g , and • Spare Parts Tracking L o g . r SYSTEM DATA ORGANIZATION Data Input Collect Input Data , 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 2 2 6 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 C M Systems. J. of the Construction Division, ASCE, September, pp. 261-267. [Bjork et al 93] Bjork, B.C., Huovila, P., and Hutt, S. (1993). Integrated Construction Project Document Management (ICPDM). Advanced Technologies, Elsevier Science Publishers B.V. pp. 135-45. [Bowler 94] Bowler, Charles E . (1994). Database use in the Engineering Office. Proc. Computing in Civil Engineering, ASCE. pp. 1874-79. [Burger & Halpin 77] Burger, Amadeus M . , and Halpin, Daniel W. (1977). Database Method for Complex Project Control. J. of the Construction Division, September, pp. 453-463. [Carter 87] Carter, D.J. (1987). The Use of Structured Information System In Building Contract Administration Managing Construction Worldwide, Vol. One, Lansley, Peter R., et al (Eds.), E . & F.N. Spon, London, pp. 437-447. [Chamberlain 91] Chamberlain, Elliot A. (1991). Graphics/Database Integration for Engineers. Proc. Computing in Civil Engineering, 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, H G , and McKay, D G (19930. Towards an Information and Decision-Support System for the Building Industry. Management of Information Technology for Construction, K.S. Mathur et al (Eds.), World Scientific Publishing Co., Singapore, pp. 101-111. [Liu et al 94a] Liu, L .Y . , Stumpf, A.L. , and Kim, S.S. (1994). Applying Multimedia Technology to Project Control. Proc. Computing in Civil Engineering, 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). N I A M 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, M A . Ch 22. [Deatherage 64] Deatherage, G.E. (1964). Contractor Organization and Management. Construction Company Organization and Management, McGraw-Hill Book Company, New York. Chapter 2. [Fisk 93] Fisk, Edward R. (1993). 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 J G B E U E L M |9UUOSJ3cj 111 o\" )U3iuajnooJd o J01BUJ|IS3 j 9 | 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 J 8 6 6 U E I A J | E J 8 U 8 0 LU u\" UI l u a p j s a i d cc AjHiqisuodsay in •a' I ii cc TO ° i Q. 1 £ | i o c 8 O X u » .\". •S .1 s (/)>, -S > ro f»< p c O Q. ™ (0 TJ CO V) CD ' r a CD I I 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 U J 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 T J 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 l l 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 \" i t TJ ra > ra \"O co ra i i CO , CO '8. I § Or ra CD C o CO E ra g.Jjl 8J I 1 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 J8su i6u3 AJOJBS SD/J01BIUIIS3 jaauiBug ( S O Q 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 J S J U O JG8UIBU3 )ssy jsBi o z LL LU Z z o \"> 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 i d •§ i 1 i i 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 G E N E R A L DISCIPLINES 1.10 BUILDING 1.20 TRANSPORTATION 1.30 INDUSTRIAL 1.40 HEAVY/UTILITIES 1.50 POWER 2.00 SITE W O R K DISCIPLINES 2.10 SITE PREPARATION - TYPE I (DEMOLITION) 2.11 Mechanical Demolition 2.13 Explosive Demolition 2.15 Salvaging 2.20 SITE PREPARATION - TYPE II (EARTHWORK) 2.21 Site Grading 2.22 Rock Excavation 2.23 Trench Excavation 2.24 Hauling 2.26 Finish Grading 2.27 Soil Stabilization 2.29 Dewatering 2.30 SITE PREPARATION - TYPE III (PILES, CAISSONS & SHORING) 2.31 Pile Foundations 2.35 Pier Foundations 2.37 Drilled Caissons 2.38 Sheet Piling & Cribbing 2.39 Underpinning 2.40 SITE DEVELOPMENT - TYPE I (UTILITIES) 2.41 Permanent Site Drainage 2.43 Water Utilities 2.44 Sanitary Utilities 2.45 Gas Utilities 2.46 Oil Utilities 2.47 Electrical Power Utilities 2.48 Telecommunication Utilities 2.50 SITE DEVELOPMENT - TYPE II (PAVING, SPECIALTIES, LANDSCAPE) 2.51 Curb and Gutter Specialist 2.52 Mudjacking Specialist 2.53 Bituminous Paving 2.54 Concrete Paving 2.55 Fencing 2.56 Site Specialties 2.57 Irrigation 2.58 Landscaping 2.60 RAILROAD WORK 2.70 MARINE WORK 2.80 TUNNELING 3.00 C O N C R E T E DISCIPLINE 3.10 CAST-IN-PLACE 3.20 REINFORCING STEEL 3.30 PRECAST 3.40 SPECIALIZED DECKS & FINISHES 4.00 MASONRY DISCIPLINE 4.10 BRICK & BLOCK MASONRY 4.20 STONE MASONRY 5.00 M E T A L DISCIPLINES 5.10 STRUCTURAL (FAB & ERECT) 5.11 Fabrication only 5.15 Erection only 5.20 MISCELLANEOUS ((FAB & ERECT) 5.30 ORNAMENTAL ((FAB & ERECT) 5.40 DECKING & SIDING 5.50 PRE-ENGINEERED BUILDING 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 T H E R M A L & MOISTURE DISC. 7.10 WATERPROOFING & DAMP. 7.20 INSULATION 7.30 ROOFING 7.31 Shingle & Tile 7.35 Membrane 7.40 ARCHITEC. SHEET M E T A L 7.50 SKYLIGHTS 7.60 CAULKING & SEALANT 8.00 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 P A R T m O N 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 M E C H A N I C A L DISCIPLINES 15.10 PLUMBING 15.20 PIPING 15.30 HVAC 15.40 FIRE PROTECTION 16.00 E L E C T R I C A L DISCIPLINES 16.10 RESIDENTIAL 16.20 COMMERCIALTND. 16.30 POWER TRANSMISSION 16.40 COMMUNICATION SYSTEMS 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 2 4 6 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 C o I D FFC2 Long 4 No field force contractor-2 C o I D FFC3 Long 4 No field force contractor-3 C o I D 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 D W N entry serial number SpecsID Integer 2 No same as SpecsID in Specs Section NoticeDate Date/Time 8 No D W N issue date Description Text 50 No brief description of the D W N DateRcvd Date/Time 8 No date the D W N received EstimatValue Currency 8 No estimated value of the D W N item DateReinsp Date/Time 8 No date the D W N 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 W B S J D Double 8 No same as W B S I D 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 . l l 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 C o I D 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 W B S J D Double 8 No same as W B S I D 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 L o c J D Long 4 No same as L o c I D 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 W B S I D 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 L o c I D 1-M Yes Storage Location Spare Parts L o c J D 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 W B S J D 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 W B S J D 1-M Yes Change Orders Company People C o J D 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 W B S J D 1-M Yes Correspondence Company People C o J D 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 C o J D 1-M Yes Company Project CoJD/EngineerlD 1-M Yes Company Project Co JD/OwnerJD 1-M Yes Company Daily Events C o J D / F F C l 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 C o J D 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 W B S J D 1-M Yes Materials Stored Company People C o I D 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 2 6 0 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