Help new to TSQL
-
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! -
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.
-
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! -
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
-
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! -
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
-
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! -
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! -
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! -
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! -
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.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply