Help me with my SQL project (please).

Question

Hey everyone,

For this grading period, my CS teacher left us a open-choice project involving SQL and Delphi/VB.

I ended up with the assignment of designing and building a program that allowed the users to, through a GUI in Delphi/VB, insert and read hurricane data pulled from a database (latest SQL Server, by the way). However, there are a few catches.

Three tables are required: Hurricanes, Hurricane_History, and Category

The Category table is not meant to be modified, and it contains the columns 'Min. Speed', 'Max. Speed', and 'Category'. The idea is that a hurricane with a rotational speed of X falls into category Y if X is within the minimum and maximum speed of category Y.

The Hurricane table is meant to be modified by the end-user, through the Delphi/VB gui. It contains the following columns: 'Name', 'Day', 'Time', 'Rotational_Speed', 'Movement_Speed', 'Latitude', 'Longitude', and 'Photo'.

Then there is the Hurricane_History table, which contains 'Name', 'Category', 'Starting_DateTime', 'Ending_DateTime', 'Starting Latitude', 'Starting Longitude', 'Ending Latitude', 'Ending Longitude'. This table is not meant to be directly modified, but rather automatically populated through SQL (I figure using SQL triggers and stored procedures).

What the program should end up doing is the following: The user opens the visual app, and enters in information for a certain hurricane. Since only the table Hurricanes is meant to be modified, the user would insert the Name, Day, Time, Current Rotational Speed, Current Movement speed, current latitude, current longitude, and, optionally, a picture.

If the user enters a hurricane that does not exist yet, then it would create a new hurricane with the corresponding data in the Hurricane_History table. If he enters data for a hurricane that already exists, then the data for that hurricane should be updated, and stored into the corresponding Hurricane_History row. Furthermore, the current category of the hurricane should be automatically populated with SQL using the data that was stored in the Category table.

So far, I have the three tables, the columns, the Delphi GUI, the connections (between Delphi and SQL Server), etc.

What I'm having a real hard time with is the SQL Triggers and Stored procedures needed to generate the data in the Hurricane_History table. Here's my algorithm, the first one for populating the category, and the second one for populating the data of the Hurricane_History table:

create trigger determine_category on Hurricanes for insert, update as
  *when a value is inserted into Hurricanes.Rotational_Speed, match it with the corresponding row in the      Categories table, and insert the corresponding category into the Category column of the hurricane's Hurricane_History row.* 

create trigger populate_data on Hurricanes for insert, update as
  *if Hurricane.name exists, perform an update instead of an insert for using Hurricanes.Day as Hurricanes_History.Ending_Day, Hurricanes.Latitude and Hurricane.Longitude as Hurricanes_History.Ending_Latitude and Hurricanes_History.Ending_Longitude, and the Category using the determine_category trigger.*

   *if Hurricane.name does not exist, create a record in Hurricanes_History using the data from the newly inserted Hurricane record, and populating the Category using the determine_category trigger*

What I need help with is translating my thoughts and ideas into SQL code, so I was wondering if anyone might want to help me throughout this.

Thanks a bunch!

EDIT:

I just whipped up a simple stored procedure for determining the category. What I don't know how to do is use the result/output of the stored procedure as an insertion value. Does anyone have insight on how to do it?

CREATE PROCEDURE determine_category
@speed int(5)
AS
SELECT Category FROM Categories
WHERE Max_Speed >= @speed AND Min_Speed >= @speed

Answer 1

First, since you're using SQL Server and you can use stored procedures, don't use a trigger. It's not necessary. If your teacher needs justification, here's an article from SQL Server MVP Tom LaRock which discusses issues with handling triggers.

Second, as far as how to write the stored procedures, think about how to handle all the functionality logically. You've said you need to do the following:

• Read existing hurricane information
• Update existing hurricane information
• Insert a new hurricane into the database

Your application should handle all of those as separate paths. And you need to think about the functionality before you write your first bit of T-SQL code. That means you have to have an interface which presents existing information. You're going to have to display the hurricanes existing in the database. Then once the user selects the one to get more information on, you'll have to pull back the hurricane history information. So I know in that situation I have two different data retrievals based on user input. That tells me I need to build the GUI interface to handle that progression logically and display the information in a way the user can use. And it also tells me I've got to build two different stored procedures. The second one will be passed some information identifying the hurricane to retrieve data on (which would be the primary key).

Now roll through the rest of the application's functionality. That should get you started.

Answer 2

Rather than use triggers to do this, I would be more inclined to perform logical DML SQL statements inside transactions. Triggers, whilst sometimes proving useful, are not really necessary in this scenario (unless they are required for your coursework).

As a first approach, think about what is required to complete the application -

• A UI layer to present data to the user, allow a user to search, insert, update (and possibly delete) hurricane data.

In this layer, we'll most likely want to

1.present users with a list of previous hurricanes, perhaps with some key details displayed and give users the ability to select a particular hurricane and see all the details.
2.give users the ability to insert new hurricane data. Think about how category will be displayed to a user to choose and how inputted data will be taken from this layer and ultimately end up in the data layer. Think also about how and if we should validate the user input. What needs to be validated? Well, ensuring against SQL injection, that values are in permitted ranges and lengths, etc. if this were a real application, then user input validation would be a necessity.

• A data layer used to store the data in a defined entity relationship.
• A data access layer used to perform all data access logic in regard to manipulating the application data.
• A Business logic layer that contains the classes required for the application. Will contain any of the rules associated with the entities and will be used to present data to the UI layer.

We could take an extremely simplified approach and have the UI layer call straight into the data layer through stored procedures (which would be acting as our data access layer and also our business logic layer as they will encapsulate the rules regarding whether a hurricane record already exists and needs updating or a new record needs creating, possibly some validation too).

Answer 3

Re: Inserting sproc output into a table. Use the following general syntax:

INSERT INTO table (field1, field2, field3)
EXEC yourSproc(param, param)

In the insert documentation, search for execute_statement for details.