Help new to TSQL

Question

Help new to TSQL

achowe

SSCrazy

Points: 2075
More actions
May 19, 2004 at 12:10 pm

#108049

I have a question which needs answering and im not sure how to answer it. Basically, I have a basic map of depots within a transportation system, labelled depot1 thru depot 10.
The question:
"Explain a transact SQL programming technique (using code snippets) that could be used to program the listing of all routes that pass through depot 6"

Any idea would be great
Cheers guys

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Sounds like homework.

But the question I have is what is the table structure and what data would tell me the vehicle travels thru that depot. I need more info please.

achowe SSCrazy Points: 2075 More actions · Answer 2

At the moment their is no table structure, just a discourse....

......i presume some sort of flag would be in place (pretty much left up to me to decide i think) to detect when a vechile travels thru a depot.....

.......

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 3

An additional question is whether the transportation system is constrained to a hierarchical structure, a network structure, or a directed graph? If a directed graph, does the graph cycle? Each of these structures has a different optimal solution.

Sounds like either the traveling salesman problem, or a logistics network problem such as FedEx or UPS uses.

Wayne

achowe SSCrazy Points: 2075 More actions · Answer 4

Your right, its a logisitics problem.............constrained to a network structure as afas as i can tell....

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 5

create table TblRoutes (

GID int IDENTITY (1, 1) NOT NULL PRIMARY KEY,

Node1 NUMERIC NOT NULL,

Node2 NUMERIC NULL)

insert into TblRoutes (Node1, Node2) VALUES(...)

SELECT * FROM TblRoutes WHERE Node1 = 'Depot6' OR Node2 = 'Depot6'

There is a simple, brute force solution that had little practical value.

You have a few options at this point. You can either insert all source depots that eventually pass through each depot. You can set up a recursive procedure to traverse the table looking for nodes that connect to the depot6 node. Or you can go pick up one of the books on Data Warehousing by Ralph Kimball and implement a "bridge" table. The bridge table will allow you to run a single query equivalent to the recursive procedure.

Hope this helps

Wayne

achowe SSCrazy Points: 2075 More actions · Answer 6

Thanks for that wayne, very helpful indeed, appreciate it

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

achowe SSCrazy Points: 2075 More actions · Answer 7

Just to make certain, are there any alternatives that would be worth thinking about?

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

achowe SSCrazy Points: 2075 More actions · Answer 8

could you also explain the line

GID int IDENTITY

What does GID stand for / relate to?

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

achowe SSCrazy Points: 2075 More actions · Answer 9

Sorry for all the followups,

is your solution by any chance related to dijkstras algorithm? or is that somethin different?

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 10

Let me answer the questions as presented.

> are there any alternatives that would be worth thinking about?

Yes, but without a more specific problem domain, theory is about all we can offer you.

> could you also explain the line "GID int IDENTITY"? What does GID stand for / relate to?

The simple answer is that GID stands for "Generated ID". I use GID because "ID" is a reserved word in some databases.

The longer answer is that every object in a database has at least four identifiers of interest. A GID, an internal name, a business name, and a display label. Not all identifiers are always used. The GID is a numeric identifier that may be unique to the table, to the database, to a system, and so on. GIDs are for use by the database for referential integrity and should never be used directly in a query. For example, Select * from SomeTable where GID = 123 is forbidden. Due to the nature of databases, GIDs can and do vary across database instances. Ping me if you want more information on the other identifiers and when they are appropriate. I will generate an article on the topic if there is sufficient interest.

> is your solution by any chance related to dijkstras algorithm? or is that somethin different?

dijkstras algorithm may be useful for this problem, depending on the real-world problem constraints. The challenge with the algorithm is that the approach uses procedural logic rather than set-based logic. As such, it is better suited for a procedural language. When we add a database into the solution, we can precompute (and therefore store) most if not all of the possible information we need to solve the problem. This reduces the actual run-time element to a single query which is typically much more efficient than the dynamic algorithm. This is a very simplistic answer. A real solution would need to involve a fully populated testbed with more constraints than we have yet identified.