Consider the following relational schema and SQL query: Employee(eid integer, di
ID: 3606053 • Letter: C
Question
Consider the following relational schema and SQL query: Employee(eid integer, did: integer, sal: real, hobby: char(20)) Department did integer, dname : char(20). floor : integer, phone: char(10)) Financeldid: integer, budget: real, sales : real, expenses: real) SELECT D.dname, F.budget FROM Employee E, Department D, Finance F WHERE E.did = D.did AND D.did F.did AND D.floor= 1 AND E.sal >= 59000 AND E.hobby= ‘golf Suppose we have the following indexes: . B+ indexes exist on the attributes Employee.did. Employee.sal, Deptfloor, Department.did, and Finance.did. . The DBMS has only the index nested loops and sort-merge as join algorithms (A) 110%! Trace the System R dynamic programming algorithm on this query (B) 110%) Give a relational algebra tree that is reasonably efficient (Hint: think of algebraic equivalences)Explanation / Answer
The original paper on the subject, "Access Path Selection in a Relational Database Management System" considers it more of a search tree grouped by heuristics involving index preferences that allows some paths not to be calculated. Since then, a variety of strategies have been undertaken to pursue these seach trees, where at one time PostgreSQL used both a greedy and GA-based planners on a configurable basis, but it my understanding the basic principles of that paper are still in play.
Floyd Warshall Algorithm
We initialize the solution matrix same as the input graph matrix as a first step. Then we update the solution matrix by considering all vertices as an intermediate vertex. The idea is to one by one pick all vertices and update all shortest paths which include the picked vertex as an intermediate vertex in the shortest path. When we pick vertex number k as an intermediate vertex, we already have considered vertices {0, 1, 2, .. k-1} as intermediate vertices. For every pair (i, j) of source and destination vertices respectively, there are two possible cases.
1) k is not an intermediate vertex in shortest path from i to j. We keep the value of dist[i][j] as it is.
2) k is an intermediate vertex in shortest path from i to j. We update the value of dist[i][j] as dist[i][k] + dist[k][j].
// C Program for Floyd Warshall Algorithm
#include<stdio.h>
// Number of vertices in the graph
#define V 4
/* Define Infinite as a large enough value. This value will be used
for vertices not connected to each other */
#define INF 99999
// A function to print the solution matrix
void printSolution(int dist[][V]);
// Solves the all-pairs shortest path problem using Floyd Warshall algorithm
void floydWarshall (int graph[][V])
{
/* dist[][] will be the output matrix that will finally have the shortest
distances between every pair of vertices */
int dist[V][V], i, j, k;
/* Initialize the solution matrix same as input graph matrix. Or
we can say the initial values of shortest distances are based
on shortest paths considering no intermediate vertex. */
for (i = 0; i < V; i++)
for (j = 0; j < V; j++)
dist[i][j] = graph[i][j];
/* Add all vertices one by one to the set of intermediate vertices.
---> Before start of a iteration, we have shortest distances between all
pairs of vertices such that the shortest distances consider only the
vertices in set {0, 1, 2, .. k-1} as intermediate vertices.
----> After the end of a iteration, vertex no. k is added to the set of
intermediate vertices and the set becomes {0, 1, 2, .. k} */
for (k = 0; k < V; k++)
{
// Pick all vertices as source one by one
for (i = 0; i < V; i++)
{
// Pick all vertices as destination for the
// above picked source
for (j = 0; j < V; j++)
{
// If vertex k is on the shortest path from
// i to j, then update the value of dist[i][j]
if (dist[i][k] + dist[k][j] < dist[i][j])
dist[i][j] = dist[i][k] + dist[k][j];
}
}
}
// Print the shortest distance matrix
printSolution(dist);
}
/* A utility function to print solution */
void printSolution(int dist[][V])
{
printf ("Following matrix shows the shortest distances"
" between every pair of vertices ");
for (int i = 0; i < V; i++)
{
for (int j = 0; j < V; j++)
{
if (dist[i][j] == INF)
printf("%7s", "INF");
else
printf ("%7d", dist[i][j]);
}
printf(" ");
}
}
// driver program to test above function
int main()
{
/* Let us create the following weighted graph
10
(0)------->(3)
| /|
5 | |
| | 1
|/ |
(1)------->(2)
3 */
int graph[V][V] = { {0, 5, INF, 10},
{INF, 0, 3, INF},
{INF, INF, 0, 1},
{INF, INF, INF, 0}
};
// Print the solution
floydWarshall(graph);
return 0;
}
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.