Assignment
For the coding/experiment part of the homework, I want you to see the actually plan a DBMS will choose for various kinds of queries. You can conduct your experiments using the DBMS of your choice from among sqlite,
Mysql, Postgres, DB2, and Oracle. So that the grader has something to evaluate I want you to produce a ?le Experiment.pdf which at its top a write up of your results, followed by transcripts of each of the explain operation I am asking for as well as how you created the input tables. Each of the databases mentioned has a variant on the EXPLAIN command which tells you how the database would evaluate a query. For example,
EXPLAIN SELECT * FROM USERS;
or some variant will tell you how the database would perform the query select * from users; without actually performing the query. To do the experiments you will ?rst need to create a program DataGenerator.java. This program will be run from the command line with a line with the following format:
java DataGenerator start_value num wrap_number txtfile
The program should output into txt?le num many rows of two columns, space separated. The ?rst column starts with the value start_value and increments one with each row. The second column's value is chosen randomly from between 1 and wrap_number For example, ?lling in these values we might write:
java DataGenerator 10 20 5 data.txt
The program might output into data.txt the rows:
10 1
11 1
12 5
13 2
14 3
15 3
16 4
17 5
18 1
19 2
20 1
21 2
22 4
23 1
24 5
25 2
26 3
27 4
28 4
29 1
You should include both the ?le Experiment.pdf and DataGenerator.java in the Hw3.zip ?le you submit. Create ?ve tables R1(A,B), R2(C,B), R3(D,B), R4(E,B), R5(F,B). All of the columns be of integer type. You should include the execution of the create tables in Experiment.pdf. Next generate ?ve tables worth of data with the following lines:
java DataGenerator 0 500 50 R1data.txt
java DataGenerator 500 500 50 R2data.txt
java DataGenerator 1000 500 50 R3data.txt
java DataGenerator 1500 500 100 R4data.txt
java DataGenerator 2000 500 100 R5data.txt
Use the bulk loader facility of the database you chose to load these ?ve ?les into their corresponding table. Copy the text or show a screenshot of performing the load operation into Experiment.pdf. Now consider the join of all ?ve tables with the condition R1.B=R2.B and R2.B=R3.B and R3.B=R4.B and R4.B=R5.B and R5.B=51.
Express this query in SQL in as a join that would look like a left-tree, bushy tree, and right-tree. For each way, use explain to ?nd out how your DBMS would execute the query. Put this information into Experiment.pdf as well. Try executing each of your equivalent queries and check that the results match. Write up, again in Experiment.pdf, which you think is the best way to do the query. If you desire you can also experiment with indexes to see if it helps the execution speed.