11.3 Case Study 2: "Improve Cash Flow"

   

---

In this section we describe the second case study.

11.3.1 Company Scenario

A high school administrator wants to improve the school's cash flow by getting paid faster from the state's Department of Education for appropriate reimbursable expenses. "My teachers are complaining that they spend their own money buying supplies and then have to wait weeks to get reimbursed. The faster that I can get funds in the school's bank account, the faster I can reimburse the teachers, and they can focus their energies on what they love: teaching their students."

What Does It Take to Solve This Problem?

What is stopping the high school from submitting the claims quickly to the Department of Education and getting fast payment? Currently, the high school has to create paper documents to send to the Department of Education to request payment. The state's finance department won't pay claims for an individual school unless that school can show that it is maintaining a certain level of academic achievement. Along with the details about the expenses that it wants reimbursed, the school has to send data on current student grades and levels of attendance to show that the school is meeting the mandated levels. The finance department won't reimburse the school until someone at the Department of Education can go through that paperwork and verify that the school meets the educational criteria. If the high school could submit documents electronically with the necessary information directly to the Department of Education's data systems, the turn-around time would decrease dramatically.

11.3.2 How This Business Problem Is Addressed

The high school negotiates to do a pilot program with the Department of Education where the expense claims are submitted electronically. The benefits that they hope for are

• High school: They receive prompt payment from the state and can reimburse their teachers quickly.

• Department of Education: They reduce the amount of paperwork that they have to do. They also receive the school's performance data faster than they have before. They can then use this data for evaluating trends, for supporting evidence when requesting money from the federal government, or to report to the public to show how well the school is performing.

The high school stores their data in one type of system, and the Department of Education uses another system. Therefore, they decide to leverage XML as a common language to exchange the information and use XML-based Web Services.

The High School

The high school has a student and classroom record system based on a DB2 database that maintains information about each student, such as grades and attendance. When a teacher comes to the high school administrator with an out-of-pocket expense for reimbursement, the administrator uses a Web application to retrieve the teacher's classroom record and enter the information for the expense. A classroom reimbursement request document is created. Previously, that request document would have been submitted by fax or mail to the Department of Education. In the new process, the request document will be submitted to the Department of Education using a Web Service.

The Department of Education

The Department of Education has a reimbursement request system that maintains the criteria for reimbursing the various public schools in the state. When the Department of Education's Web Service receives the request document from the high school, it validates the request by accessing its reimbursement request system and returns a confirmation or rejection of approval back to the school. If the request is approved, the Department of Education also forwards the request to the state's finance department to initiate the process to transfer funds to the school.

The Department of Education's database does not have to be a DB2 database for this system to work. Because the information that is sent to the Department of Education is XML-based, they can access the data in the XML document as long as they are using a database that can extract the data from the document.

The High School Reimbursement Request Application

A teacher turns in a request for reimbursement to the school administrator. The reimbursement application is a Web application that first retrieves the classroom data, which is stored in DB2. The administrator opens the home page of the Web application and enters the classroom ID number for that teacher. The classroom ID number is then sent by a servlet that queries the database for the classroom record.

Table 11.5 shows a portion of the structure for the CLASSROOM_RECORD table that contains the classroom information.

The application displays the query results and prompts the administrator for the following additional information:

• Category for the teacher's out-of-pocket expense (for example, purchasing paper)

• Amount requested for reimbursement

This additional information is submitted to a servlet that invokes the Web Service to go to the Department of Education's application for submitting reimbursement claims.

The Web Service returns the Department of Education's response to the claim. The high school administrator knows at once that the Department of Education has received the request and whether it has been approved.

The Department of Education Reimbursement Request Web Service

The high school submits the XML-based request document to this Web Service. The data that the high school must send as input to the Web Service is shown in Listing 11.3.

Listing 11.3 Input Data to Web Service

School identification number (SCHOOLID)
Class subject (SUBJECT_ID)
Average student grade for this class (AVG_GRADE)
Average student attendance (%) in this class (AVG_ATTDNCE)
Category of expense (EXPENSE_TYPE)
Amount requested (EXPENSE_AMT)

The output response data for the claim service is: School identification number (SCHOOLID) Approval flag (APPROVED = yes or no).

The response data is returned as an XML document (response.xml), which can be transformed to HTML and displayed in the high school's Web application immediately. The school administrator sees immediately whether the reimbursement is approved and can notify the teacher. This can be done as shown in Listing 11.4.

Listing 11.4 Response Data

<?xml version="1.0" ?>
<EXPENSE_APPROVAL>
  <SCHOOLID>3091578832</SCHOOLID>
  <APPROVED>yes</APPROVED>
</EXPENSE_APPROVAL>

The Department of Education has a relational database, possibly DB2 or an other database system. This database stores information about the minimum criteria for class subjects, such as the average grades for each type of class and the attendance levels for those classes. It also stores the values reported by each state school.

The SCHOOL_INFO table (see Table 11.6) in that database stores the specifics about each school. The SUBJECT_DATA table (see Table 11.7) stores the information on standards that the state schools are mandated to meet in each subject. Table 11.6 contains the current average grade and attendance for that class in that specific school. Then Table 11.7 is used to look up the acceptable state standards for that class subject.

Table 11.6. SCHOOL_INFO Table

Column name	Description
SCHOOLID	State school identification number (primary key)
SUBJECTID	School subject (for example, math) ID code
AVGGRADE	Average grade for students in this class in this school
PCTATTDNCE	Average attendance percentage for students in this class in this school
APPROVED	A flag (yes or no) that shows whether this school is meeting the standards

Table 11.7. SUBJECT_DATA Table

Column name	Description
SUBJECT_ID	School subject ID code (primary key)
MIN_GRADE	Minimum acceptable average grade
MIN_ATTDNCE	Minimum acceptable average attendance

The flow of the process at the Department of Education's database once its Web Service has received the request from the school is

• Decompose the incoming XML document and update Table 11.6 with the values for the average student grade and attendance in that subject for that school. Set the APPROVED flag to no.

• Query the Table 11.7 for the values of acceptable average grades and attendance that correspond to the class subject ID that was submitted.

• Compare the data from Table 11.7 to the school's data in Table 11.6.

• If the comparison shows that the average grade and attendance level are higher than the minimum acceptable values, update Table 11.6 to set the APPROVED flag to yes.

• Query Table 11.6 for that SCHOOLID value and convert the result of the query to an XML document (Approval_Status.xml).

• Transform that XML document to the XML format of the desired response to send to the high school.

• Return the response document (response.xml) to the school.

In this case, the high school requires a particular XML format of the response they want (response.dtd). The benefit of using XML for data exchange is that to exchange data from the Department of Education's internal XML document (Approval_Status.xml) to another XML document, they need to know only the DTD of the required response and create an XSLT file to transform to it. This makes it easy to reuse their existing applications in the future. For example, suppose another school wants to use the Department of Education's Web Service and get a different response format back or the federal government might later define the format of response documents to be used by all schools in the country. The Department of Education would not have to change Approval_Status.xml. All they would have to do is have an XSLT to transform the Approval_Status.xml document to the desired response XML document.

The Approval_Status.xml document is shown in Listing 11.5.

Listing 11.5 Approval_Status XML Document

<?xml version="1.0" ?>
<SCHOOL_INFO>
  <SCHOOLID>3091578832</SCHOOLID>
  <SUBJECTID>1235</SUBJECTID>
  <AVGGRADE>2.9</AVGGRADE>
  <PCTATTNDCE>87</PCTATTNDCE>
  <APPROVED>yes</APPROVED>
</SCHOOL_INFO>

To transform this to the response.xml document to send back to the high school, the transform shown in Listing 11.6 is applied (response.xml).

Listing 11.6 Response XML Document

<?xml version="1.0" ?>
<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="xml" indent="no" />
<xsl:strip-space elements="*" />
<xsl:template match="/">
<xsl:apply-templates select="//SCHOOL_INFO [1]" />
</xsl:template>
<xsl:template match="SCHOOL_INFO">
<EXPENSE_APPROVAL>
<SCHOOLID>
<xsl:value-of select="SCHOOLID[1]/text()" />
</SCHOOLID>
<APPROVED>
<xsl:value-of select="APPROVED[1]/text()" />
</APPROVED>
</EXPENSE_APPROVAL>
</xsl:template>
<xsl:template match="*|@*|comment()|processing-instruction()|text()">
<xsl:copy>
<xsl:apply-templates select="*|@*|comment()|processing-instruction()|text()" />
</xsl:copy>
</xsl:template>
</xsl:transform>

11.3.3 Future Extensions

After solving the immediate problem and improving cash flow, the high school has plans to expand this system. Now that they have a model for composing an XML document from their database of classroom and student records, the high school can expand in these areas:

• Create a Web site where parents can look up their children's records. Parents can monitor how their child is progressing week by week instead of waiting until the next report card.

• Enable teachers to determine the likelihood of getting approval for reimbursements before they make a purchase for the classroom. Because the Web Service exists at the Department of Education, the teacher can use a Web application to find out before making an out-of-pocket purchase whether it would be denied.

---

Top Title TOC Preface P.1 What Is XML? P.2 XML Concepts P.3 XML-Related Technologies P.4 XML Data Management P.5 How This Book Is Organized P.6 Who Should Read This Book P.7 Resources Acknowledgments Chapter 8 Chapter 12 Chapter 13 Chapter 14 Chapter 19 Part I: What Is XML? Chapter 1. Information Modeling with XML 1.1 Introduction 1.2 XML as an Information Domain 1.3 How XML Expresses Information 1.4 Patterns in XML 1.5 Common XML Information-Modeling Pitfalls 1.6 A Very Simple Way to Design XML 1.7 Conclusion Part II: Native XML Databases Chapter 2. Tamino-Software AG's Native XML Server 2.1 Introduction 2.2 Tamino Architecture and APIs 2.3 XML Storage 2.4 Querying XML 2.5 Tools 2.6 Full Database Functionality 2.7 Conclusion Chapter 3. eXist Native XML Database 3.1 Introduction 3.2 Features 3.3 System Architecture Overview 3.4 Getting Started 3.5 Query Language Extensions 3.6 Application Development 3.7 Technical Background 3.8 Conclusion Chapter 4. Embedded XML Databases 4.1 Introduction 4.2 A Primer on Embedded Databases 4.3 Embedded XML Databases 4.4 Building Applications for Embedded XML Databases 4.5 Conclusion Part III: XML and Relational Databases Chapter 5. IBM XML-Enabled Data Management Product Architecture & Technology 5.1 Introduction 5.2 Product and Technology Offering Summaries 5.3 Current Architecture and Technology 5.4 Future Architecture and Technology 5.5 Conclusion Notices Chapter 6. Supporting XML in Oracle9i 6.1 Introduction 6.2 Storing XML as CLOB 6.3 XMLType 6.4 Using XSU for Fine-Grained Storage 6.5 Building XML Documents from Relational Data 6.6 Web Access to the Database 6.7 Special Oracle Features 6.8 Conclusion Chapter 7. XML Support in Microsoft SQL Server 2000 7.1 Introduction 7.2 XML and Relational Data 7.3 XML Access to SQL Server 7.4 Serializing SQL Query Results into XML 7.5 Providing Relational Views over XML 7.6 SQLXML Templates 7.7 Providing XML Views over Relational Data 7.8 Conclusion Chapter 8. A Generic Architecture for Storing XML Documents in a Relational Database 8.1 Introduction 8.2 System Architecture 8.3 The Data Model 8.4 Creating the Database 8.5 Connecting to the Repository 8.6 Uploading XML Documents 8.7 Querying the Repository 8.8 Further Enhancements 8.9 Conclusion Chapter 9. An Object-Relational Approach to Building a High-Performance XML Repository 9.1 Introduction 9.2 Overview of XML Use-Case Scenario 9.3 High-Level System Architecture 9.4 Detailed Design Descriptions 9.5 Conclusion Part IV: Applications of XML Chapter 10. Knowledge Management in Bioinformatics 10.1 Introduction 10.2 A Brief Molecular Biology Background 10.3 Life Sciences Are Turning to XML to Model Their Information 10.4 A Genetic Information Model 10.5 NeoCore XMS* 10.6 Integration of BLAST into NeoCore XMS Conclusion Chapter 11. Case Studies of XML Used with IBM DB2 Universal Database 11.1 Introduction 11.2 Case Study 1: "Our Most Valued Customers Come First" 11.3 Case Study 2: "Improve Cash Flow" 11.4 Conclusion Notices Chapter 12. The Design and Implementation of an Engineering Data Management System Using XML and J2EE 12.1 Introduction 12.2 Background and Requirements 12.3 Overview 12.4 Design Choices 12.5 Future Directions 12.6 Conclusion Chapter 13. Geographical Data Interchange Using XML-Enabled Technology within the GIDB System 13.1 Introduction 13.2 GIDB METOC Data Integration 13.3 GIDB Web Map Service Implementation 13.4 GIDB GML Import and Export 13.5 Conclusion Chapter 14. Space Wide Web by Adapters in Distributed Systems Configuration from Reusable Components 14.1 Introduction 14.2 Advanced Concept Description: The Research Problem 14.3 Integration of Components with Architecture 14.4 Example 14.5 Future Generation NASA Institute for Advanced Concepts, Space Wide Web Research, and Boundaries 14.6 Advanced Concept Development 14.7 Conclusion Chapter 15. XML as a Unifying Framework for Inductive Databases 15.1 Introduction 15.2 Past Work 15.3 The Proposed Data Model: XDM 15.4 Benefits of XDM 15.5 Toward Flexible and Open Systems 15.6 Related Work 15.7 Conclusion Chapter 16. Designing and Managing an XML Warehouse 16.1 Introduction 16.2 Architecture 16.3 Data Warehouse Specification 16.4 Managing the Metadata 16.5 Storage and Management of the Data Warehouse 16.6 DAWAX: A Graphic Tool for the Specification and Management of a Data Warehouse 16.7 Related Work 16.8 Conclusion Part V: Performance and Benchmarks Chapter 17. XML Management System Benchmarks 17.1 Introduction 17.2 Benchmark Specification 17.3 Benchmark Data Set 17.4 Existing Benchmarks for XML 17.5 Conclusion Chapter 18. The Michigan Benchmark: A Micro-Benchmark for XML Query Performance Diagnostics 18.1 Introduction 18.2 Related Work 18.3 Benchmark Data Set 18.4 Benchmark Queries 18.5 Using the Benchmark 18.6 Conclusion Chapter 19. A Comparison of Database Approaches for Storing XML Documents 19.1 Introduction 19.2 Data Models for XML Documents 19.3 Databases for Storing XML Documents 19.4 Benchmarking Specification 19.5 Test Results 19.6 Related Work 19.7 Summary Chapter 20. Performance Analysis between an XML-Enabled Database and a Native XML Database 20.1 Introduction 20.2 Related Work 20.3 Methodology 20.4 Database Design 20.5 Discussion 20.6 Experiment Result 20.7 Conclusion Chapter 21. Conclusion References Contributors Editors Chapter 1: Information Modeling with XML Chapter 2: Tamino-Software AG's Native XML Server Chapter 3: eXist Native XML Database Chapter 4: Embedded XML Databases Chapter 5: IBM XML-Enabled Data Management Product Architecture and Technology Chapter 6: Supporting XML in Oracle9i Chapter 7: XML Support in Microsoft SQL Server 2000 Chapter 8: A Generic Architecture for Storing XML Documents in a Relational Database Chapter 9: An Object-Relational Approach to Building a High-Performance XML Repository Chapter 10: Knowledge Management in Bioinformatics Chapter 11: Case Studies of XML Used with IBM DB2 Universal Database Chapter 12: The Design and Implementation of an Engineering Data Management System Using XML and J2EE Chapter 13: Geographical Data Interchange Using XML-Enabled Technology within the GIDB System Chapter 14: Space Wide Web by Adapters in Distributed Systems Configuration from Reusable Components Chapter 15: XML as a Unifying Framework for Inductive Databases Chapter 16: Designing and Managing an XML Warehouse Chapter 17: XML Management System Benchmarks Chapter 18: The Michigan Benchmark: A Micro-Benchmark for XML Query Performance Diagnostics Chapter 19: A Comparison of Database Approaches for Storing XML Documents Chapter 20: Performance Analysis between an XML-Enabled Database and a Native XML Database Remember the name: eTutorials.org Copyright eTutorials.org 2008-2023. All rights reserved.