Posted by arlene

How do we know if we have any rows in a table? We use the COUNT function to return a count of the number of rows in a table. If you enter Listing into Query Analyzer we can check the data insert for the Spy table.

This will return the current number of rows (well actually PersonlDs) in the Person table.

You should now go ahead and check that the rest of the tables have data in them. By the way, they all should have!

Okay, so now what? Well because we saved our DTS packages, we can take a quick look at what SQL Server 2008 does when it creates a DTS package. Navigate your way to Data Transformation Services, Local Packages, and then you should see our three new packages. If you double-click one of the packages, you will get a graphical representation of what the package looks like.

This package allows us to alter it or run the package again. To view some of the information SQL Server 2008 uses when importing the data, right-click the different icons, including the arrows, and choose Properties from the menu.

Have a good look around DTS. Because you can easily re-create the wizard that we used earlier, do not be afraid to try some things out in the DTS package designer.

Here is a challenge for you. See whether you can figure out a way to import the data from all three spreadsheets in a single package! It is possible, though it might require some in-depth investigation on your behalf and some clever VBScript.

Where Does Our Application Go Now?

Our application is now in a stable state, the database design hasn’t changed from our original concept, but there are many more things that our application could include.

Because our SQLSpyNet application is for recording spies‘ movements and bad guys’ activities, the next logical step would be to add monitoring of spies‘ equipment while on assignment. Currently our spies go out to fight bad guys, but we have no way of measuring how much equipment they use. Now in James Bond films, Q is always nagging 007 about being careful with equipment. Shouldn’t we be doing the same thing?

What change would this require? Because our model is relatively stable we can add new tables without impacting the rest of our database too much. The amount of change required would be to add probably two more tables to the structure. One would be a lookup table that contains a list of equipment. The second table would be a junction table, which breaks up the many-to-many relationship that would exist between our new lookup table and the Activity table.

So Rob, how did you come up with this? When a spy is involved in an activity is when they require resources (equipment). However, a spy can have more than one piece of equipment while on assignment. This creates a many-to-many relationship between Resource and Activity, so a junction (or associative) table is required.

If we-implement this change, what else can we get our application to do? The next logical step is to use our application as a management-reporting tool. This would allow senior management to create graphs on the performance of our spies over the bad guys.

We can also create reports, either Web-based or using reporting tools such as Crystal reports or Access, to monitor performance, salaries, and the number of staff on the books. These reports are simple to develop and only require you to create some stored procedures (or views) to retrieve the data, and then build a front end.

Other enhancements to the application could include

Adding a financial component to the application so we can record expenses. This would allow us to find out how much per year it costs us to fight crime.

Adding another person type to the application. This could be so human resources (HR) could manage the number of staff we have.

As you can see, there are several enhancements that we can perform on our application to make it perform more than just the basic functionality. However, in a real- world scenario the first thing we would need to do is roll out the application to our users so they can assess the application and give us feedback on performance, look and feel, and most importantly, functionality!

So there we have it: an application that has not only provided you with hours of entertainment in developing, but also has the possibility of providing many more hours of delight. So let your imaginations run wild!

Possibly related posts: (automatically generated)
Checking the Results of the Import

• Using the DTS Wizard to Reload Our SQLSpyNet
• Microsoft Directory Synchronization Services (MSDSS) continue...
• Creating a Web Service File
• Get your Web Site ready for Toasting/Hosting
• Creating Enhanced Indexes
• Public Key Revocation continue...
• Base software URLConnect，Web-based agent in Java
• Using SQL to Generate SQL
• Master and Slave NIS Servers
• Useful Tips for Small Business Domain Search Engine Optimization