Loading...
HomeMy WebLinkAboutContracts & Agreements_36-1997_CCv0001.pdf C " AGREEMENT TO FURNISH ENGINEERING SERVICES ' FOR PREPARATION OF THE AU:WATER RESOURCES MANAGEMENT PLAN T <° TASK ORDER 14 f"` Groundwater Database Customization and Reconciliation (Amendment No. 15) This Task Order (Amendment No.15) authorizes the ENGINEER to perform Engineering Services included in this Amendment and in accordance with the AGREEMENT for the preparation of the Water Resources Management Plan, 6 November 1990. This Amendment makes the following changes to the AGREEMENT; all other provisions of the AGREEMENT remain in effect. ARTICLE 1—ENGAGEMENT OF THE ENGINEER No changes. ARTICLE 2— SERVICES OF THE ENGINEER 2.1 The Scope of Work is amended to include Attachment A to this Amendment which defines the scope of work for engineering services for Task Order 14, Groundwater Database Customization and Reconciliation. ARTICLE 3— RESPONSIBILITIES OF THE OWNER No changes. ARTICLE 4— PERIOD OF SERVICE 4.1 The time period for performance of the ENGINEER`S services for Task Order 14 set forth in Article 2 shall be in accordance with the schedule defined in Attachment B to this Amendment. ARTICLE 5— PAYMENT TO THE ENGINEER 1 5.4 The total compensation to perform work under this Amendment for Task Order 14 shall be in accordance with Attachments C and D to fl-ds Amendment. ARTICLE 6— INSURANCE AND INDEMNIFICATION No changes. ARTICLE 7— GENERAL CONSIDERATIONS No changes. IN WITNESS WHEREOF, duly authorized representatives of the parties have signed in confirmation of this Amendment. CITY OF REDLANDS CH2M HILL, CALIFORNIA, INC. By: James L. Bloomquist, Swen Larson, Mayor Vice President,CH2M HILL, Inc. ATTEST: Lo e�Poy z yy l Clerk 2 Task Order 14 Redlands Water Resources Management Plan Groundwater Database Customization and Reconciliation Attachment A Scope of Work Purpose The purpose of this task order is to customize the Redlands groundwater databto the City of ase developed under Task Order 12, deliver the customized database and user interfaces (City), train City staff in use of the database, and reconcile data gaps and inconsistencies in the existing database developed under Task Order 12. The customized databrepoase will be used by City staff to enter and store groundwater-related data, generate specific rts, and extract selected data to support analyses of groundwater issues. The database will also act as an interface for the City's Geographic Information Systems (GIS) database. Scope Task 1. Review and Summarize Existing Database CH2M HILL staff will review the existing Microsoft Access' groundwater database developed under Task Order 12 and identify data gaps, inconsistencies, and problems in each of the tables. CH2M HILL will recommend methods for filling data gaps, standardizing and reconciling inconsistencies (e.g., a chemical synonym data table), and correcting problems (e.g.,eliminate zero water level depths that are artifacts of the Well Depth Mnformat agement zing the above ion. System). CH2M HILL will prepare a memorandum summan Task 2. Meet With City Staff CH2M HILL staff will meet with the City's management staff,operations personnel, and GIS analyst to address the following topics: a) Review memorandum and recommendations developed in Task 1, b)Develop a list of desired features for the modified and customized data tables based on the ase developed under Task Order 12, and Microsoft AccessT'M groundwater datab c) Develop a list of desired features for the customized user interfaces, reporting functions, and associated queries. Three meetings with City staff are planned: linkage of the 1)one meeting between the City's and CI-12M,fIELL's GIS analysts to discuss - Microsoft Access` groundwater database with the City's GIS and water system hydraulic model, 2) one 1/2-day meeting to address topics a) and b) from above, and 3) One 1/2-day meeting to address topic c) from above* The groundwater database developed under Task order 12 will be the starting point for these meetings. we assume that City staff will provide existing field d eetcan bee features. To aff, CH2M, L will provide example basis for desired database assist City start functions for the meeting in item 3, above (see forms (data entry user interfaces) or repo example in Attachment E). A database design memorandum will be delivered at the completion of Task 2 that will summarize topics b) and c), from above. C142M HILL and City staff will meet to review the draft memorandum. The finalized memorandum will also describe, amori other things, the database structure, screen layouts for forms and queries, and reporting tion formats. The scope of work for Tasks 3,4, and 5 is dependent upon the results of Task 2. Consequently,the scope of work and cost for Tasks 3, 4, and 5 will be reviewed and revised, if necessary,to match the level of features desired by City staff upon completion of the database design memorandum. Task 3. Customize Database CH2M HILL will customize the existing Microsoft AccessTm groundwater database based groundwater database will be converted from upon the outcome of Task 2. The existing Microsoft Access TM version 2.0 to version 7.0 to be compatible with the City'modified s version of Microsoft Access'". Examples of existing data table parameters that may be include: table names, field(column heading) names, or well and chemical names. Data tables may be modified by adding fields (e.g., operator name, flowmeter units, flowmeter readings), deleting unnecessary fields, and/or rearranging the order of the fields. User interfaces (forms) will be created for each of the data tables in the groundwater database. User interfaces will be created so that data entry will be relatively simple and so error messages will alert the data that data entry errors or problems are minimized(e.g., entry operator if data format/nomenclature is incompatible with the data tables and/or pull- levels will be established so that down menus will be available for key parameters,). Security only designated City staff are permitted to enter data into the database. All into the data tables, other users will be given 'read only' access to the database so that once data are entered there is minimal chance that they will be erroneously deleted or modified. queries may be developed for easily creating data charts (see exampleAttachment E). example of a reporting function that may be created would be for summarizing annual pumpage data from each well for reporting to the Watermaster. To enable a working database to be delivered as part of Task 4,a chemical synonym data table will created so that inconsistencies in the way that water quality parameters are reported are ce reconciled automatically within the database. CH2M HILL will also rectify data gaps in the well information table and assign a unique Etoll to each w llthnkage betablishdWe assume that City and relationships with other data tables and the GIS can staff will provide missing information identified in Task 1, such as well recordation numbers or State of California well numbers, for all of the wells. CH2M HILL will provide the City with a prototype database approximately midway through Task 3. This will enable City staf�°L cmmedae tocreating thefinalversion of the database. C14M Hwill meet with selected City staff and give a pe database. City staff will Provide comments to C142M HILL demonstration of the prototype database. These comments will then be within 3 weeks of receiving the prototype incorporated into the final version of the database. CH2M HILL will develop documentation for the database. This doecumentatias as ystems on will in user documentation, with example screen layouts, report outputs, tc., well s documentation. Any revisions to the City's field data sheets (e.g.,sheets that operators use to record water levels or meter readings), will be made at this time, if necessary. Task 4.Deliver Customized Database and Provide Training d Microsoft Access database to the CH2M HILL will deliver the customize use of the Microsoft Access TM City. CH2M HILL will provide a 2-day training seminar on us . the user interfaces/forms, groundwater database, including,but not limited to, data entry via gro the generating reports and charts, and creating queries to extract selected data from e tables, gener dheld at CH2M HELL,will include the use importing and exporting1Zata. The training will he d will include "hands on" examples with the City,s database. of personal computers, and Database documentation developed in Task 3 will also be provided to the City at that time. The format for the training seminar will be four 1/2-day (4-hour) sessions that will focus on ds and skill levels. For example,the first two 1/2-day the different database users' nee water quality lab, water utility sessions may include field operations, senior operations, personnel, and the Access"database administrator. department managers, GIS database pe d session These sessions will cover basic database functions, including data entry. The thir will include senior operations, water utility department managers, GIS database personnel, and the AccessTgdatabase administrator. This session will cover special database functions . s, and importinglexporting data. The such as generating reports, creating charts from quene and the Access lu last session w datacess base ill include the City GIS database analye GIS and Microsoft Acst TM th administrator. and will cover interfacing between rms, and reports, and database groundwater databases, creating custom queries, fo maintenance. use of the groundwater database,they should begin When City staff have been trained in related data into the database. City staff entering water level, water quality, pumpage, and (e.g., the Well Depth should continue to use the City's existing database programs System, MIS 1, and Lotus spreadsheets to store data)for up to three months in Managementthe Microsoft Access TM groundwater database. Once City staff are proficient parallel with with data entry and are confident that the Microsoft Access, groundwater database meets their needs and is performing properly, they should discontinue use of the City's existing database programs (e.g., the Well Depth Management System,MIST,and Lotus spreadsheets to store data). During the initial data entry period, CH2M HILL will provide user support for the Microsoft Access TM groundwater database for one month via telephone communication. Task 5. Reconcile Existing Microsoft Access' Database Tables CH2M HELL data entry staff will reconcile and rectify the groundwater database tables 12 based on, and after, the completion of Tasks 1, 2, and 3, developed from Task Order ormation provided by City staff above. We assume that data gaps can be filled using sing data/inf(for example missing units and dates associated with water quality data). If data are not available in some instances, CH2M HILL will meet with City staff to fills these gaps (e.g., City staff can provide their best guess as to the nature of the missing information, such as water quality units). filled, inconsistencies reconciled, and�ntwe'th cocost chAfter data gaps have been les have been modified tobeothemized of the data tables, and the tab tables will be delivered to the City so that database developed in Task 3, the reconciled data C112Mded in HILL will Task 4append the they can be merged with the master groundwater database. -C112M data tables from this task to those in the Redlands database provi . HILL staff will verify consistency of the two data sets during merging of the two data sets. Note that this task does not include back entry of any new data(e.g.,water quality, pumpage, or water level data not provided to CH2M HELL as part of Task Order 12). Task Order 14 Redlands Water Resources Management Plan Groundwater Database Customization and Reconciliation Attachment B Schedule Work under this task order initiates on March 1, 1997 and terminates August 31, 1997. Task Order 14 Redlands Water Resources Management Plan Groundwater Database Customization and Reconciliation Attachment C Fee The fee for this task order is based on the hourly rates shown in Attachment D and Table C-1. The fee will not exceed$73,880. The project is defined by the not-to-exceed fee lin-tit. The City is not obligated to pay Consultant beyond the not-to-exceed fee. Consultant is not obligated to expend costs beyond the not-to-exceed fee limit. Table C-1. City of Redlands Task Order 14: Groundwater Database Customization and Reconciliation Q p � H U U U as Ln v 1 9 pI H Project Tasks Hours I Review Database 2 121 34 2 16 66 $5,600 $560 $6,160 2 Meet with City Staff 8 8 38 64 12 130 $13,10 $1,310 $14,410 3 Customize Database 2 6 28 142 12 48 32 270 $22,600 $2,260 $24,860 4 Deliver Database and Train 2 8 26 52 16 44 148 $13,100 $2,810 $15,910 5 IReconcile Existing Tables 1 21 10 461 1 14 100 172 $11,400 $1,140 $12,540 Subtotal 1 161 LL 1141 3381 421 1061 1481 786 $65,800 1 $8,080 1 $73,880 * ExLenses include Lhone,reproduction, mileage, computer,etc. Task Order 14 Redlands Water Resources Management Pian Groundwater Database Customization and Reconciliation Attachment D Rate Schedule Classification ----Hour l rate Professional Grade 7 $168.00 Professional Grade 6 $144.00 Professional Grade 5 $129.00 Professional Grade 4 $114.00 Professional Grade 3 $100.00 Professional Grade 2 $90.00 Professional Grade 1 $83.00 Professional Grade 0 $68.00 Technician Grade 5 $105.00 Technician Grade 4 $90.00 Technician Grade 3 $79.00 Technician Grade 2 $67.00 Office $50.00 Rates are subject to adjustment on December 25, 1997 Task Order 14 Redlands Water Resources Management Plan Groundwater Database Customization and Reconciliation Attachment E f Database Examples I i FIE, E Data Analysis � waEet o+�aity Dsta C F„n�pspe bats 6� wa6ar i,.eved Data Frn�Datac 111 Tv Dates Pararri Narrix TricNaoel�►er�e Record] of 7 Example Form/User Interface for creating charts using data from the Microsoft Access' groundwater database.