DREP is a tracking program that holds the status of problem items within Tinker and creates exception reports based on these items. The current DREP system is a stand-alone application that is not compliant with Air Force requirements. Also, with the recent updates to Access 2007, the queries and programming logic no longer work properly. We plan to meet Tinker’s goal of enhancing DREP by migrating the data within the Access Database to SQL Server. This update will make this a more viable, secure network and an Air Force compliant system. This enhances the security and integrity of the data, lends itself to improved backups of the data, and adds superior transaction logs and backups. This also improves the scalability and reliability of the system while addressing deployment issues found within the stand alone Access application. Currently, users are having difficulty accessing the information from DREP. By creating a web interface, users will acquire easier access to the program. There are alternative solutions to this problem; however, Team 3 lacks the technical knowledge to produce a working prototype within the semester time-span.

Introduction

Project Statement

The IT Department at Tinker AFB has a specific business goal for this project that includes the migration of an Access[1] database to SQL Server. This includes creating a web interface and using VB.NET on the front end and SQL Server on the backend. This update will make this a more viable, secure network and an Air Force compliant system. This enhances the security and integrity of the data, lends itself to improved backups of the data, and adds superior transaction logs and backups. This also improves the scalability and reliability of the system while addressing deployment issues found within the stand alone Access application.

Overview

The following project definition report is divided into four main sections. The first section, Current Situation Analysis, provides the business reasons for conducting this project. The second section, Current Operations, describes how DREP currently works, what functionalities are to be kept, and what functionalities will be changed. The third section, Proposed System Specifications, describes what the new DREP system is to look like. This section will also address the problems and goals identified in the first two sections. The fourth section, Alternative Evaluation and Recommendation, recommends whether to modify, build or buy a product for a solution. The appendices will include a copy of our team’s Statement of Work and graphical diagrams.

Current Situation Analysis

Project Statement

Tinker’s IT department business goals for this project include migrating from an Access database to a SQL Server database. This includes creating a web interface and using VB.NET on the front end and SQL on the backend. We hope to use ASP.NET and ADO.NET to complete this project. This update will make this a more viable, as well as a more secure network, and an Air Force compliant system. This enhances the security of the data, the integrity of the data, lends itself to improved backups of the data, and adds superior transaction logs and backups. This also improves the scalability and reliability of the system while addressing deployment issues found with the stand alone Access application.

Strategic Alignment Analysis

Organization

Tinker is the headquarters of the Air Force Materiel Command's Oklahoma City Air Logistics Center, which is the worldwide manager for a wide range of aircraft, engines, missiles, software and avionics and accessories components.

Mission

The Oklahoma City Air Logistics Center (OC-ALC), based at Tinker Air Force Base, Okla., is the largest of three ALCs in the Air Force Materiel Command and provides depot maintenance, product support, services and supply chain management, as well as information support for 31 weapon systems, 10 commands, 93 Air Force bases and 46 foreign nations.

Goals

Tinker AFB goals consist of Combat Support through People, War Fighter Support, and Continuous Improvement. The strategic goals represent the roadmap the OC-ALC will follow to make sure it fulfills its vision, mission and strategic imperative. They are:

o Secure the Right Workload

o Lean/Transform the Center

o Lead Aircraft Availability Improvement Program Achievement

o Improve ALC Operations through the Defense Logistics Agency and the Air Force Global Logistics

Support Center

o Develop People...Better

Measures

The strategic plan also outlines a process to make certain its goals are being met and the plan, itself is updated as required to adjust to changing circumstances. Regular reviews are scheduled to take place to measure and score the center's progress and to analyze and make changes, as necessary.

The Air Force has mandated units to stray away from stand alone applications, such as Access. While transferring the data structure from Access, our team hopes to increase the usability of DREP and ensure data integrity.

System Impact

The impact of the system will be the reduced cost of running and maintaining legacy systems to a more advanced architecture that is already being utilized throughout the organization. The financial resources benefit would be an unpredictable cost savings while the need for this information continues to be utilized. The end users will be able to have more access to the data elements that they utilize throughout DREP when the migration to SQL Server is complete. The end users can utilize the program and the data through the web interface that will be designed for this project and this will give all users’ access to DREP that are authorized to utilize it for their jobs. The overall security will be greatly increased because Access 2007 is not within a server environment and its security features are limited. SQL Server will be more functional in two separate very important areas. The first is updating the database and the end-users will not be affected to such a degree as they are in Access 2007. An example would be user rights that would be found in active directory, etc. The second and perhaps the most important feature would be backing up the database periodically. Since SQL Server is already being utilized by Tinker AFB, backing up is a very frequent occurrence; while backing up secondary systems is not of the highest or most important priority for them.

Goals and Features for the Proposed System

Goals for the proposed system would be to successfully transfer Access to SQL Server 2005. Presentation layer will be completed in a .NET framework, serving as the front end of the system. The code will be designed in .NET and the business rules will be in .NET, used on the front end. The data schema from the access database will be copied to SQL Server and will be on the back end. A web interface will be used to connect .NET to SQL Server.

Goal	Objective	Contribution
1. Create an updated working prototype for Tinker AFB.	1.1. Successfully transfer Access to SQL Server 2005.	1.1.1. Eliminates a standalone application.
	1.2. Build a web interface used to connect .NET to SQL Server.	1.2.1. Enables employees to access DREP through the Local Area Network web interface.

Constraints for the Proposed System

The proposed system has a limited number of constraints. These constraints include:

· System will be written in VB.NET 2008

· System will use SQL Server 2005

Current Operations

Current System Analysis

The current DREP system runs through Access. When a user opens the program, a form will appear and it’s possible that if the user is the first member to open the database for the data, an update will run. The database will inform you that an update is about to run. It is also possible that a new version of DREP has been released. If that is the situation then a message will be displayed informing the user that a new version is available. If the quarter has rolled over, it is possible for the past repair data to be updated. This is a very long data update that could last up to 15 minutes.

On the main screen, the user can choose six selectable options. The first option is “Change Default Shop Code”. When this option is selected, the user will be prompted to enter in a new shop code that appears to change the table.

Another option the user can select from the main screen is “Enter Problem Items”. This form contains all the-user controlled data that is displayed on the charts. It is related to the information pulled by raw data by the first field NIIN (National Item Identification Number). In order for information to be displayed correctly on the chart, the NIIN must match exactly. This form allows users the option to find multiple records. There are several fields that are necessary for the database to ensure integrity.

The user is given two options for screen pixel size, 800 X 600 or 1024 X 768. In either option, a chart will appear and provide an overall rating and its constraints. The data you entered on the problems table will be available; such as quarterly demand rate (QDR), production, noun, production control number (PDN), and backorder (BO) information will be pulled from an external raw data. There are some colors that are toggled automatically. Overall Rating will be red if MICAPs or Due Outs, yellow will appear if you have zero balance of 09 on hand. Flowday will be red if standard are exceeded. Carcasses will be red if consolidated repairable inventory (CRI) is less than the net repair objective (NRO). Personnel will be red if failing for hours. The only data that doesn’t save from all the file maintenance is the colors. Any time you change the records the data on the old record you just worked on the new record will default to green.

The last option the user can choose is a “Query by Project Code”. An input box will appear allowing the user to input a project code that needs to be researched. The query retrieves all those items with quantity of project code backorders that met the shop default criteria.

Graphic Data Model of the Current Situation

The following data model, represented as an entity relationship diagram (ERD), represents the underlying conceptual data structure for the current operations at Tinker. We currently do not possess a Graphic Data Model due to the current limitations with the material provided by the client. We are diligently working to solve this dilemma and will have to be added at a later time.

Graphic Process Model of the Current Situation

The graphic process model of the current situation can be found in Appendix B, Figure 1. The representation is an Access database integrated with VB.NET. The presentation layer is represented by the forms in Access. The code is accessing the data from external sources using macros to extract the data. The data is represented to the user as a print out report for the user. The macros retrieve the data from an external database and use the macros to create the forms and reports through Access.

Current Infrastructure

The physical infrastructure at Tinker Air Force Base includes multiple sources from mainframes to extract data to place inside of the Access database table for generating exception reports.

Retention Analysis for Features of the Current System

Tinker has requested to retain the same look and feel of DREP. Maintaining the same appearance will eliminate user training on a new version of DREP. This will allow user to stay consistent while using the system. Users will be able to create the same reports without any noticeable change.

Proposed System Specifications

Problem Analysis

The Tinker IT staff has identified the following problems that will be alleviating with the proposed system:

1. The proposed system will help eliminate the constant breaking of queries and programming logic with each version of Access released.

2. The proposed system will comply with an Air Force mandate to remove standalone applications, such as Access.

3. A user interface will be created to improve user access to eliminate the problems presented with Access. Users cannot use the current system due to updates and various modifications.

4. The proposed system will allow for easier backups and maintenance.

Narrative

Tinker Air Force base uses a Depot Repair program called “DREP” to keep track of maintenance parts that are classified as having problems. Problems can include missing parts, backorder issues, and defective parts. Each problem is given a rating, represented by a color (red, yellow, green) defining the status of repair process. Our proposed system will create a web interface using VB.NET code on the front end to retrieve data from SQL Server in the back end to create exception reports.

Conceptual Data Model or Alternative

Modified Data Flow Diagram or Alternative

The modified process model for the proposed system can be found in Appendix B, Figure 2. The proposed system will maintain the same look and feel of DREP. Inside the bold box is the representation of the current system which will be migrated into SQL Server. The presentation layer and code layer that contain the forms and macros will be coded in .NET, and used on the front end of the programs. The data layer will be transferred from Access to SQL Server acting on the back end. All business rules and constraints will remain.

Difference Reduction Analysis for the Proposed System

Problems in the Current Operation	Reduction Action for Proposed System
1. Latest upgrade of Access broke the relationships and programming logic	1.1. Migrating links to the data to SQL Server
2. Current DREP system is in violation of Air Force mandate	2.1 Migrating current standalone application to a client-server architecture
3. Users have difficulty accessing the system to retrieve necessary reports	3.1 Create a web interface to allow users entry
4. Current DREP system is hard to backup and maintain	4.1 Transfer the data structure to SQL Server which is consistently backed up on a daily and weekly basis

Alternative Evaluation and Recommendations

Comparison of Alternatives

Alternatives for this project are limited due to constraints set by the client. Our alternatives are summarized below.

Alternative	Description	Evaluation
Use SharePoint Server	SharePoint Server would allow Tinker to have a web interface with the Access database.	This is a disadvantage because of the Air Force mandate, which leads to not being operationally feasible. This alternative is not feasible due to the lack of technical knowledge of SharePoint.
Use cubes to group data	Cubes would allow users to customize a report.	We lack technical knowledge in cubes and would not be feasible.
Use Oracle	Oracle is a different database management system tool that uses SQL statements. The use of web interfaces may not be required.	We lack technical knowledge in Oracle systems and would not be feasible for this project.

Recommended Alternative

Our recommended alternative would be in line with our proposed system. We feel the proposed system meets requirements and will produce an enhanced version of DREP.

Appendix A: Statement of Work Draft

Organization Description

The Oklahoma City Air Logistics Center (OC-ALC), based at Tinker Air Force Base, OK, is the largest of three ALCs in the Air Force Materiel Command and provides depot maintenance, product support, services and supply chain management, as well as information support for 31 weapon systems, 10 commands, 93 Air Force bases and 46 foreign nations. Tinker is the headquarters of the Air Force Materiel Command's Oklahoma City Air Logistics Center, which is the worldwide manager for a wide range of aircraft, engines, missiles, software and avionics and accessories components. Tinker AFB vision consists of Combat Support through People, War Fighter Support, and Continuous Improvement. [2]The Air Force bases these core competencies and distinctive capabilities on a shared commitment to three core values -- integrity first, service before self, and excellence in all we do.

The overall goal of this project is to achieve the modernization of an existing application, Depot Repair Enhancement Program (DREP), while helping to give us an understanding of the business of software development. The end would be a quality system delivered within the semester that would enhance Tinker’s business need for a new depot repair tracking system.

Problem Statement

Tinker’s IT department’s business goals for this project involve migrating the Access database to SQL Server. The Access database is used as a reporting tool to generate exception reports. This includes creating a web interface, using VB.NET on the front-end and SQL on the back-end. This update will make the program more viable, as well as more security focused across the network, and will create an Air Force compliant system. This improves the security of the data while adding improved transaction logs. As a result, the integrity of the data will become more efficient and effective in creating backups. This also improves the scalability and reliability of the system while addressing deployment issues found with the stand alone Access application.

Projected Work

· Solution Approach: We will be revising the current DREP program and building a new web interface.

· Tasks: We will be using .NET and SQL Server to create a secure web interface for Tinker.

· Deliverables and Dates: The tentative dates for project activities include:

o October 17, 2:00 pm New interface with documentation

o October 31, 2:00 pm Present Code

o November 14, 2:00 pm Demonstration of team’s progress

o November 21, 2:00 pm Proposed Final Presentation

Constraints

· System will be written in VB.NET 2008

· System will use SQL Server 2005

Resources Required

Our team will need a ready-only copy of the current Access database. This will help us better understand the relationships between the tables and how they are used. We will also need permission to interview users about the current system operations and to gather the requirements for the proposed system.

Project Milestone Dates

The tentative milestone dates include:

o October 17, 2:00 pm Progress Meeting

o October 31, 2:00 pm Progress Meeting

o November 14, 2:00 pm Progress Meeting

o November 21, 2:00 pm Proposed Final Presentation

o December 5 Back Up date for Final Presentation

Course Milestone Dates

o October 3 Proposal Report Due

o October 29 Exam 2

Signatures

For Team 3

_____________________________________________________________________

Dr. Al Schwarzkopf, Manager Date

For Tinker AFB

_____________________________________________________________________

Mike Kennedy, Chief, Enterprise Management Division Date

Appendix B: Any documents or diagrams

Figure 1. Current Situation Model

Figure 2. Proposed System Model

[1] Access or Access 2007 is referring to Microsoft Access that is a part of the Microsoft Office Suite.

[2] Tinker Air Force Base Website. Web Source: <http://www.tinker.af.mil/main/welcome.asp>. Found 9 September 2008.