To load up the MRP database, we ran the “MRP Schema & Data.sql” script provided by selecting File > Open Script… in the Query Browser and executing it. From here we were able to cut and paste certain queries in the “MRPQueries.doc” Word file provided and analyze them using the Query Browser. Such analysis led to optimization changes in table data types as well as the addition of helpful indices.

Recommending Indices
As a group, we viewed each table in the database and looked for opportunities for indexing. As a general rule, we created indices for tables that included a primary key and then a secondary key (another ID which type included INTEGER). If the tables had a primary key but not an evident foreign key, then we made sure that the primary key had an index. An example of the conventions we used to name the indices is as follows:

As applied to the Amenity table

There were some tables that we didn’t have a primary key but had a foreign key (e.g. unavailabledate table). For those tables we added an index for the foreign key. We did not take the time to add indexes to every table. We only indexed the tables that were associated to the queries that we were testing.

Simple Query

Amenity Table
To start our optimization process, we executed a simple query from the Amenity Table. The purpose of the query is to grabs all amenities where the groundID is greater than one. The query execution took .043 seconds without any indexes. With adding campID as an index it was resulted in a faster execute time of .0409 seconds. Although the execution time difference isn’t very significant, it taught us that indexing keys can have an impact on execution time.

SELECT * FROM Amenity WHERE groundID>’1’

More complex Queries

Camp Reservation
The first complex query that we executed pulled reservation data by stake and ward out of the Reservation and ReservationByCampsite tables. By not changing anything and running the queries as is the execution time was 1.7 seconds. In order to decrease the execution time, we deleted the casts on the startDate, endDate, and wardID attributes. After deleting the cast and changing the start date and end date type from VARCHAR[50] to DATETIME, we included actual dates in the “firstdate” and “lastdate” parameters. These changes improved the execution time to 1.6193 seconds.

We also added an index called “campID_IX” which includes the campID and reservID columns and is a generic “INDEX” kind with the binary tree index type. Adding this index improved the execution time to 1.42 seconds. Therefore, indexing and changing the data type to be more appropriate saved us 0.3 seconds of execution time. Although that may not seem significant for a small database, an enterprise database that receives thousands of queries would benefit from such an optimization.

SELECT r.reservid, r.reservedBy, r.stake, r.ward, r.groupType,
r.startDate AS arrival,
r.endDate AS departure,
(SELECT stakeName FROM Stake s
WHERE s.stakeID=r.stake) AS stakeName,
(SELECT wardName FROM Ward w
WHERE w.wardID =r.ward) AS wardName
FROM Reservation r
INNER JOIN ReservationForCampsite rfc ON (r.reservid=rfc.reservid)
WHERE ((r.startDate >= ‘2006-07-10′ AND
r.startDate <= ‘2007-04-15′) OR
(r.endDate >= ‘2006-07-11′ AND
r.endDate <= ‘2007-04-16′))
ORDER BY departure

Agents Stake
Again like before we wanted to have a benchmark time before we tried to optimize the queries which resulted in a 4.8 second execution time. As our first try at optimizing it we indexed with both columns CampID and reservID. This help reduce the execution time down to 2.5, but we thought we could still do better then that. We figured that since indices worked so well let’s keep adding more. So we have been adding indices to foreign keys and primary keys of any table that this query is referencing. Execution times were recorded for each added index. Here are the results.
• .18 seconds when stakeID on the Stake table was indexed
• .16 seconds when areaID and StakeID were indexed

In short we were able to get a query that executed at a 4.8 second time down to a .16 second execute time. This is a dramatic increase in performance and would surely save the customer from waiting a lengthy amount of time for a query to execute.

SELECT startDate, endDate, numPeople, groupType,
ward, reservedBy, r.campID,
r.contactPhone, contactAddress,
r.contactEmail, specialRequests,
managerComments, confirmed, totCost, c.name as propertyName,
managerName, c.contactPhone AS manPhone,
c.contactEmail as manEmail,
g.name AS spName, street, city, state, zip,
s.stakeName AS ssname, a.stakeName AS asname
FROM Reservation r, Camp c, AgentStake g, Stake s, Stake a
WHERE r.campID=c.campID AND c.campID=g.CampID AND
s.stakeID=stake AND a.stakeID=g.stakeID AND reservID>’100′

Select Name, Accept Reservation
Before we added indices the query ran at a speed of 1.3 seconds, but shortly after adding indices to all of the tables involved, it executed at a speed of .1415 seconds. The indices we added were the following:

• campId_IX (consisting of campId & reservID) to ‘reservation’ table
• campID_IX to ‘unavailabledate’ table
• groundID_IX (consisting of groundID & siteID) to ‘campsite’ table

We changed the firstScheduled and lastScheduled columns from VARCHAR(50) to DATETIME datatypes. We did the same for the nextfirstScheduled and nextlastScheduled columns as these also should be represented as dates. These four changes incrementally increased the speed of this query. We also changed ground_ID in the campsite table from VARCHAR[10] to INTEGER.

SELECT name, acceptReservations,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID AND confirmed<>’N') AS Confirmed,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID AND confirmed=’N') AS Unconfirmed,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID) AS Total,
(SELECT COUNT(*) FROM UnavailableDate WHERE UnavailableDate.campID=c.campID) AS Unavailable,
(SELECT COUNT(*) FROM Campsite s, Campground g WHERE s.groundID=g.groundID AND g.campID=c.campID) AS Sites,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (’2006-04-12′)) As Day0,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (’2006-04-13′)) As Day1,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (’2006-04-14′)) As Day2,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (’2006-04-15′)) As Day3,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (’2006-04-16′)) As Day4,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc
WHERE r.reservID=rfc.reservID AND r.startDate=r.endDate AND
r.campID=c.campID AND r.startDate IN (’2006-04-17′)) AS Day5
FROM Camp c
ORDER BY name

Matt Thomas
Jeff Brinkerhoff
Mike White

Enterprise Application Integration
This is not possible, although this would be wonderful. The reason that I say this is because there are too many different systems out there. These systems are built on many different platforms in many different languages that it would almost be impossible to assimilate all of the data between these systems together. If this were to be successful then managers would be much more informed with correct information that they can base their decisions.

Currently at my job I work with an application that is very proprietary. This application allows us to create attributes inside of this tool. These attributes such as a text box are not stored in a relational way which makes it very difficult to retrieve any data from this application. You have to use an OLE DB provider to access anything. It is rather annoying and it makes my life difficult. I bring this up because this is one example of a systems being very hard to get data out of and I can not imagine integrating it with other systems. I think this would be very hard.

The other thing with application integration is the strain on the servers. We are already consuming tons of energy to run these machines, but how much more would we need to make integrate all of this data. Along with energy costs there would need to be an increase in bandwidth and number of servers. I just don’t see this being feasible.

Service Oriented Architecture and Web Services
Coming into the class I had never really heard much about we services or service oriented architecture. The two are pretty similar in nature in the fact that SOA is the way to design a reusable piece of software and a web service is a the ability to connect to someone else’s code with out actually seeing their code or knowing it. All they would have to know is what to expect in return.

It was funny because once we started to talk about it in class then I started noticing it everywhere. It is very similar to buying a car, in the fact that which ever one you just bought you suddenly see a lot more of them on the road because you have one too. One of the first times that I heard about it since learning it in class was at work. One of the project managers was talking about using web services with this new software they were looking to purchase. It was a perfect example of the real world application of all of this.

The Web Services assignment really got my feet wet. I had learned it at a very high level conceptually and now I needed to take a deeper look the nuts and bolts of the whole thing. At first I kept putting it off because I didn’t know what was being asked to do. Once I buckled down and realized that I had to get it done I really focused and got it done. It was cool to see that it actually worked correctly. I know I felt like I had really accomplished something that I could use throughout my life.

MDA
It seems as this solution seems like pie in the sky like application integration does. The reason that I say this is because it would seem that your models don’t always capture what you want the application to do. I missed the OlivaNova day in class so I am not exactly sure how the tool works, but it is worth using. I hate writing simple parts of code to help set everything up so that I can start programming logic, but this might work.

With trying to abstract away as much as possible from machine language this might just be another step. We have layers on top of layers that make it easier to write code for so maybe this is just one of those layers. We will see if it really works.

Database Optimization
I love databases because it makes finding things so easy. I have written code before that would interact with databases, but I have never needed to optimize it. The main reason for that is because I have never had the traffic on my site to necessitate it. I knew from Dr. Gary Hansen’s class that queries could be optimized so that run the fastest. I never really knew how to do this, but this class taught me this. I really don’t see myself ever optimizing a database so I really didn’t see a lot of value and doing the assignment.

None the less I found that indexes and making sure data types were appropriate to what they were would definitely help to improve performance executing a query. With a database that is executing queries all the time shaving off a .001 second would make a huge difference because if they were any longer that would just have a cascading affect on all other queries that would be run.

Event Logging/Monitoring
There are bugs in software everywhere. Sometimes these errors are never captured by some system or file which makes it impossible to fix the problems. With event loggers such as log4j we see that you can make it so that ever error that occurs in an application is caught and reported somehow. What is really need is that you can put the severity of the error right in to the error. That way after it has been logged you can create a condition to escalate it according to its severity.

With enterprise systems having millions of lines of code it is critical that you track and log these errors. I have not had any personal experience with event logging other then the standard Microsoft event viewer, which doesn’t provide a lot of information regarding the error. There is a lot of benefit to using an event logger.

Team Paper
The class so far had been very high level which is sometimes good. The team paper allowed me to learn and a deeper level. I had to do research on load-balancing and how it worked. It was a great chance to dive deep and understand the algorithms that some people enterprises use. With turning in the paper to you not everyone was able to benefit. By having us do an oral presentation we were able to discuss what we had learned and also get some feedback. I felt that the paper was a great learning experience for me.

It only cost $335 and allows you to do all of your diagramming and then allows you to implement it. If you are creating small modules that can then be put together in the end then this tool would work for you. I have seen this tool first hand since BYU has some licenses for it, although I haven’t had a lot of experience with it. It seemed a little cryptic, but I am sure I just did not have enough experience with it. From what Sparx says it does this product is great and would be beneficial to any organization.

To make sure that MDA was a viable option for building our systems I would first have to make sure that we had well documented our processes so that we could build UML models based on those processes. The other important thing that I would look for would be that we looked into how complex and how customizable we wanted the application after it was built. Making sure that this application would be built to our specifications with the functionality that we needed would be one of the most important factors in the decision to use MDA or not.

It seems when you are working with an industry that has processes that are well defined then MDA would be a great approach to take. I am going to work for Omniture, who does web analytics, once I graduate. Omniture is a company that has built it self of having amazing databases to store all of the different information that it gathers from people visiting their clients’ sites. I see MDA playing a huge role in building their applications. Their products are so complex that I do not think creating a UML model of what they want their system to be like will be practical.

MDA is the idea of creating Unified Modeling Language (UML) models, which are written in a way which makes them technology-independent. This is adding a layer of abstraction to the already many layers that exist. By creating more and more levels of abstraction it makes it easier for applications to be developed and deployed. MDA standard addresses the software development life cycle of designing, deploying, integrating, and managing systems.

These UML models will then be used to create code in any language such as Java, .Net, PHP, Ruby. So in a sense it allows you to create the models so that depending upon what platform you are using the code can be generated with that specific language.

One of the main promoters of MDA is the Object Management Group (OMG). Their main role since they were founded in 1989 has been creating and promoting standards. This consortium and originally consisted of eleven companies including; Hewlett-Packard, IBM, Sun Microsystems, Apple Computer, American Airlines and Data General.

Value Proposition

The main reason that an organizations would want to use MDA is simply for cost savings. Through MDA there will be a reduced number of hours spent on writing code which will say on the overall cost of an application. With having the code generated automatically this greatly reduces the time spent on debugging and fixing problems with the code.

Tools and Vendors

From all of the websites that I visited is seemed that they offered some if not all of the type of tools that are listed below.

• Creation Tool: A tool used to elicit initial models and/or edit derived models.
• Analysis Tool: A tool used to check models for completeness, inconsistencies, or error and warning conditions.
• Transformation Tool: A tool used to transform models into other models or into code and documentation.
• Composition Tool: A tool used to merge several different models together.
• Test Tool: A tool used to test the models
• Simulation Tool: A tool used to simulate the execution of a system represented by a given model.
• Metadata Management Tool: A tool used to handle model information and the relationships between different versions
• Reverse Engineering Tool: A tool used to turn legacy systems into working models

It seems that there are many software packages that will help you with achieving what is desired from using a MDA approach.

• OptimalJ
• integranova
• Sparxsystems
• TenFold
• Adaptive
• IBM

The other cool thing about log4j is that you can capture the severity of the error using these six logging levels:

• FATAL
• ERROR
• WARN
• INFO
• DEBUG
• TRACE

When you know the severity of the error you can allocate appropriate resources to address the issue. To help better understand how this would be used in your code I have copied and pasted from the lo4j manual.

import com.foo.Bar;

 // Import log4j classes.
 import org.apache.log4j.Logger;
 import org.apache.log4j.BasicConfigurator;

 public class MyApp {

   // Define a static logger variable so that it references the
   // Logger instance named “MyApp”.
   static Logger logger = Logger.getLogger(MyApp.class);

   public static void main(String[] args) {

     // Set up a simple configuration that logs on the console.
     BasicConfigurator.configure();

     logger.info(”Entering application.”);
     Bar bar = new Bar();
     bar.doIt();
     logger.info(”Exiting application.”);
   }
 }

package com.foo;
 import org.apache.log4j.Logger;

 public class Bar {
   static Logger logger = Logger.getLogger(Bar.class);

   public void doIt() {
     logger.debug(”Did it again!”);
   }
 }

Today Kevin Rollins, former CEO of Dell, came and spoke to the Collegiate Entrepreneurs Organization on campus at BYU. We were all stuffed into a small auditurium style room in the law building. He spoke about the entrepreneur spirit. He said that the door has not been closed on entrepreneurial opportunities, but that you have to be very thoughtful but also visionary approach. He said that the internet is continuing to grow and we can not comprehend how it will change our lives. He focused his lecture on technology and some areas that have not been totally capitalized on yet including:

• Community Based
  • The internet is growing, but changing into more of a community model
  • Using what is out there and using forums - risk associated take the good and the bad
  • Examples would be Facebook, MySpace, YouTube
• Globalization
  • 2/3 of the worlds population has not been able to use the internet like we in the US experience
  • 1 billion users on the internet currently and that will double in 1 year
  • They will start off in an advanced stage such as starting with wireless not wired access
  • They will start off with laptops, or something smaller and cheaper, instead of desktops
  • The internet will penetrate new markets
• Wireless
  • WIMAX is going to make wireless easy and faster

Kevin also said that we need to expand our horizons on how we use this new. The US market is a very mature market with some opportunities, but right now there are a lot of opportunities in countries outside the US. Other countries will be able to grow quickly because we have already taken the time to develop the technologies and test it. With this increase in data we have to be willing to constantly be learning. Kevin said that leadership capabilities need to exceed what we are doing right now. We need to get better, faster and assimilate more then we have ever had to before. He closed the lecture with four suggestions:

• Ever learning as a leader
• Continual balance
  • There is a time and a season for everything
  • Understand where you are and prioritize
• Give back
  • The responsibility that you have with that success is that you give back
• Maintain the standards you started with
  • He has never felt he has had to compromise his standards
  • You do not have to feel you need to compromise to be successful