FACILITIES MANAGEMENT EQUIPMENT TRACKING SYSTEM

EXECUTIVE SUMMARY

Welcome to Facilities Management Equipment Tracking System (FMETS) Database/Project. This database is for maintaining the Capital Equipment information that is ordered for any building in and off campus, within the University's system.

First, I will give an introduction to the Department of Facilities Management. Formerly, the Department of Architecture, Engineering and Construction, the Department was reorganized in order to merge with Physical Plant, and Facilities Planning to form a unitary organization. This process is still active and is now in phase III of the reengineering process. This newly organized Department will be a model facilities organization dedicated to improving the built environment that supports the university's goal of national pre-eminence in higher education. This Department consists of a diverse, professional team that has a strong commitment to the customer, overall efficiency and to providing safe and quality facilities. Information Services, is one of the many units that forms this department and is responsible for at least 20 legacy systems including the Equipment Tracking System.

This project will reengineer the current FoxPro-based system. The database is organized as five files: PMAIN, PEQUIPT, PPORDERS, PITEMS, PCUSTOMERS. Each of these files stores records of similiar type - these files and records are more formally defined later in this project. FMETS is designed using a 3NF rigorous relational database provided through Microsoft Access 7.0. Access's DBMS form/report generators were also used to produce the report output for this system. FMETS is built using the following Design:


 

FACILITIES MANAGEMENT EQUIPMENT TRACKING SYSTEM

(FMETS)
 


 



 
 
 
 
 
 
 
 
 
 

 Debra Js. Aranmolate



 
 
 
 
 

 

 December, 12, 1998
 

for


 



 
 
 

 University of Maryland College Park - Department of Facilities Management
 
 


 


FMETS PROJECT DESCRIPTION

The following list specifies the purpose and functions that FMETS database application will provide:

PURPOSE

The Facilities Management Equipment Tracking System (FMETS) Database is for maintaining the Capital Equipment information that is ordered for any building in and off campus, within the University's system. This system is used to track all the equipment items purchased for projects.

All the data in this system contains a five-character field that links the item to a project in PMAIN.  There is one-to-many relationship between a project in PMAIN and the equipment items in the equipment system.

FMETS applications are diverse and this system is used by: Project Managers, Construction Managers, Design Managers and Assistant Directors.  General supervisors and end-users have limited access to restricted levels of this system. This is a widely-used system that is accessed throughout all phases of a project life cycle including the design, construction and implementation phases.

Projects are funded through various sources and before allocations of funds can be granted for any project,  the project has to be presented to the Board of Public Works (BPW) and reviewed within the department monthly during regular "Briefings". For example, a typical project - the  "New Arena Facility" project shows the necessary justification why such a versatile and  robust sytesm is necessary:

PROJECT SAMPLE
Location: University of Maryland, College Park, MD

Title: New Arena Facility

Description: This project covers the new 17,000 seat basketball arena of approximately 443,206 GSF, concourses, entry lobbies, public restrooms, concessions, first aid, security, suites, Terrapin Club, and Hall of Fame spaces. The facility also includes administrative office, athletic, merchandising and storage space to support the athletic programs currently housed in Cole Student Activities Building. The flexibility to host additional events, such as post-season tournaments, concerts and trade shows is desired.

Architect: TBD

Projected RFP Date: TBD

Est. Construction Cost: $67,300,000

Consequently, Facilities Management is tasked with providing quality and cost effective facilities planning, design and construction services to support the University's mission and community. FMETS is intended to be a database system that will help to manage all the equipment purchases that supports the various construction projects within the University's spectrum.

This system has been redesigned using Access 7.0 rather than the current Foxpro design. The first time you enter a project into the equipment system, you must select the "Add to Equipment List" option from the Equipment Lists Menu. Once an equipment list has been set up for a given project, you will be able to add additional information through the "Review/Edit Equipment List" Option. In order to add any equipment item, the project has to exist in PMAIN database. A project is identified by a Quick Code (QC) that is assigned by the Construction Department.

FMETS APPLICATION  SCOPE
(1) Manage and maintain information for the Department of Facilities Management.
(2) Help to successfully track, manage & maintain Capital Equipment Inventory for UMD projects.
(3) Act as a Query and Analytic Tool for the Programmers , End-Users and Management.
(4) Provide a link with other critical systems within the Department of Facilities Management.
(5) Provide consistent and timely information to the organization.
(6) Promote flexible corporate System Change Requests (SCRs)
(7) Can be used to provide monthly Briefing Demos and Reports
(8) Help synchronize and justify the the organization's position at regular Board of PublicWorks (BPW) meetings.

Apart from the above uses of FMETS DBMS, by using Access 7.0 in conjunction with Oracle,  SQL Server and BrioQuery, other applicable applications of this system include: redundancy control, security and authorization control mechanisms [username, password to access the database], table access, enforced integrity constraints [Exclusive Locks], Back-up and Recovery controls [mirrored data and logs], complex relationships representation among the data [1:M, M:M], reduced application development time and overall, a general robust and flexible means of controlling, managing and using Facilities Management's Information/Data resources.
 

Project Definition and Design Phases
This phase consisted of requirements specifications and the database scope definition. The Department of Facilities Management needs and objectives were examined and because this database was based on  reeengineering efforts, old database blue prints, design models and schemas were reviewed and incorporated within the newly designed relational model for this database.

The design of  FMETS captured data requirements within tables - (entities) that were bounded within tuples.  Attributes and relationships were depicted as seen by the following:

PMAIN:   Proj_Code, Ekey, Proj_Name, Proj_Mgr, Proj_Locate
PEQUIPT:  Eqpt_ID, Eqpt_Name, Eqpt-For, Purchaser
PORDERS: Order_ID, Cust_ID, Date, Qty, Price_Each
PITEMS:  Item_ID, Item_Desc, Color_ID, Size_ID, Matl_ID
PCUSTOMERS:  Cust_ID, Cust_Desc, Cust_Dept, City, State, Zip

FMETS 3NF design methodology helped to capture Facilities Management's business rules. This technique assisted greatly in providing justification of  the normal forms achieved throughout the implementing provided by such a rigorous design. Also incorporated in FMETS database definition, were specifics about the structure of the records of each file that specified the different types of data elements that are being stored in each record.

FMETS 3NF DESIGN METHODOLOGY
 
PMAIN Proj_Code EKey Proj_Name Proj_Mgr Proj_Loc
QC-NAF101 3922 New Arena Facility Tony Francis UMCP
QC-NAF102 5000 New Housing Enrique Salvador FSU
QC-NAF010 6632 MFRI Training Center Bill Olen UMES
QC-SH104 1921 Somerset Hall Renovation Linda Clard UMBC

 
PEQUIPT Eqpt_ID Eqpt_Name Eqpt_For Purchaser
3922 Metal Door New Arena Facility Theresa Patterson
5000 Vinyl Blind New Housing Vinod Ghoting
6632 Student Desk MFRI Training Center Esmond Sydney
1921 Student Chair Somerset Hall Renovation Irfan Kahn

 
PORDERS Order_ID Item_ID Cust_ID Date Qty Price_Each
3922 101 3011 01-02-98 100 $100
5000 203 4041 02-03-98 200 $50
6632 303 5052 01-28-98 50 $65
1921 402 6054 03-18-98 300 $25

 
PITEMS Item_ID Item_Desc Color_ID Size_ID Matl_ID
101 Door A 1 Metal
203 Desks B 2 Wood
303 BlackBoard C 3 Mahogany
402 Venetian Blind C 4 Aluminum

 
PCUSTOMERS Cust_ID Cust_Desc Cust_Dept City State Zip
3011 UMCP Construction College Park MD 20742
4041 FSU Engineering Frostberg MD 22112
5052 UMES Mathematics Eastern Shore MD 20222
6054 UMBC Physics Baltimore MD 21214

FMETS logical design schema was supported by Access user friendly data Gui forms and through
Access's pre-defined data forms and reporting facilities. Modification, Deletion and Updates were a few other necessary functions that Access ealily supported. Additionally, Access reports generators helped provide FMETS Reports for:
 

  • the Department
  • Units
  • Summary BPW Reports

    •  

     

    FEMETS PHYSICAL SCHEMA
     
     
    File Data Item Name  Data Type  Starting Pos. in Record Length in Characters
    PMAIN Proj_Code 
    Ekey
    Proj_Name
    Proj_Mgr
    Proj_Locate    		 Alphanumeric
    Numeric
    Character
    Character
    Character    		 1
    12
    16
    36
    56    		 11
    4
    20
    20
    8
    PEQUIPT Eqpt_ID
    Eqpt_Name
    Eqpt_For
    Purchaser    		 Numeric
    Character
    Character
    Character    		 1
    5
    25
    55    		 4
    20
    30
    20
    PORDERS Order_ID
    Item_ID
    Cust_ID
    Date
    Qty
    Price    		 Numeric
    Numeric
    Numeric
    Date
    Numeric
    Numeric    		 1
    5
    8
    12
    20
    24    		 4
    3
    4
    8
    4
    6
    PITEMS Item_ID
    Item_Desc
    Color_ID
    Size_ID
    Matl_ID    		 Numeric
    Character
    Boolean
    Numeric
    Character    		 1
    4
    34
    35
    15    		 3
    30
    1
    3
    15
    PCUSTOMERS Cust_ID
    Cust_Desc
    Cust_Dept
    City
    State
    Zip    		 Numeric
    Character
    Character
    Character
    Character
    Numeric    		 1
    5
    13
    28
    43
    45    		 4
    8
    15
    15
    2
    5

    Access I found to be a quite a versatile but user friendly DBMS tool. This helped to facilitate FMETS design and implementation. Using facilities such as Access's Design Views, Table Wizards and Relationships attributes, "Enforced Referential Integrity" between  tables for example, PMAIN and PEQUIPT were applied.  Relationships including 1:M Relationship, M:M and cardinality were imposed and furthermore,  Null values were disallowed through Access's integrity relation facility.

    Access's ease-of-use allowed functions such as  APPEND,  DELETE, INSERT and UPDATE to be implemented through available specific SQL commands and with a few customized queries. Access made forms implementation look like the alphabet! Consequently, FMETS data was easily captured and presented in designed forms such as, " FMETS Pmain Form" as shown by the screen captures below. Additional features that are critical to the Department of Facilities Management were:
    Queries:  PCustomer and Pmain
    Reports:  PMain Reports for FMETS and PCustomers
    These too were also easily facilitated within Access 7.0.

    ---------> The next few screen captures show the tables, queries, forms and reports  implemented for FMETS using Access and also moderate use of BrioQuery  <--------------
     

    SCREEN CAPTURES FOR: PORDERS & PITEMS TABLES
    DESIGNED IN ACCESS 7.0


     
     

    SCREEN CAPTURES FOR PEQUIPT & PCUSTOMERS TABLES


     
     

    SCREEN CAPTURE OF QUERIES IMPLEMENTEND ON FMETS
    PMAIN & PCUSTOMERS FILES


     
     


     
     

    SCREEN CAPTURE  OF ONE OF FMETS REPORT


     

    SCREEN CAPTURE OF AN EXAMPLE FORM PRODUCED IN THE FMETS SYSTEM

     
     


     

    ERWIN ER DIAGRAM
    REPRESENTING FMETS CONCEPTUAL SCHEMA
     


     


     

    USING FMETS EQUIPMENT SYSTEM DATABASE
    The system's design supports ease of use and as mentioned earlier in this project, can be used by first selecting the "Add to Equipment List" from within a lists menu after which adding more information through a "Review/Edit Equipment List" option is simple. In order to add any equipment item, the project has to exist in the PMAIN database where the unique key that links PMAIN to PEQUIPT database is EKEY.

    Once you have selected a project, you will get a screen on which you may enter information for the item(s). Pressing <F1> function key will allow you to enter a selection from pop up menus. Items can again be updated and reviewed, or reports accessed  by entering a project's QuickCode (QC-) number.

    Each item is listed by a sequence of record number. However, all items must be accessed and identified through a unique Item_Code and this Item_Code will be specific to that item and always associated with it.
     

    SCREEN CAPTURE OF FMETS MENU


     


     

    IMPLEMENTATION & DBMS PROBLEMS
    This system is still in its Pilot phase. Because the original system was designed in FoxPro DOS that is linked to many databases in the Department's MIS System, it probably supports more functions at the current time. However, with the aid of Access and Oracle, the design of FMETS new facilities  helped to overcome these severe limitations with the current system.  Since FMETS current environment, being DOS-based, was not too efficient this new Windows-based design allowed for greater scalability, portability and accessibility. Data synchronization and import were a few other challenges that were addresses and overcome within this project.

    Due to time restraints, if this project had been allowed a little more extensive time period, say six months, more thorough planning, documentation and structured implementionn may have been possible. Gantt charts, PERT charts and other critical and valuable tools could also have been incorporated. Also, if Oracle 8 had been available and supported by the University's Resource Center, FMETS Menu and interface could have been greatly enhanced. Furthermore, the system would have been able to provide more extensive reports. Despite these minor setbacks, the versatile features, documented in this project, with the aid of Access 7.0, BrioQuery, ERwin and Billy's Microsoft Office and Netscape, all these tools,  made it  possible to increase and add to the scope of FMETS design throughout any later phase of its implementation.

    This project has shown me how important it is to apply theoretical knowledge towards a "real world effort".  Therefore, my advice to future students is for them to keep in mind that "Database Processing and Design" is designed using varied concepts. These concepts should be fully understood, appreciated then applied on an informal level. This statement can be viewed as a KEY concept that is necessary in order to design and implement any successful system, be it life or a database. Also, I have yet to hear of versatility being blamed for killing an individual. Consequently, having a natural hunger for learning new concepts, be it application tools or developers tools, may prove to be added bonuses along the way. This advice should help future students in their quest in designing a simple yet manageable prototype of life and a database. These achievements however,  may only be possible when careful planning and design strategies are used as guides along life's  rugged path.