need help solving three queries

Learning Goal: I’m working on a mysql question and need an explanation and answer to help me learn.

Q#1 Query plan comparisons

This question does not require any code development Instead, it displays the query plan generated by MySQL for two very similar queries on 3 COMPANY database tables.

Consider the query Q: “For only the projects located at Stafford,

display each project number, the department number that controls that project, and the last name, address, and date of birth of the manager of the controlling department”

First, generate the MySQL query plan for Q. (Study the GREEN section “Notes on qo” at end of document)

Second, modify Q by omitting the project location condition.

Generate the MySQL query plan for this modified query.

Note – Query plan display column “key” identifies actual indexes used by the corresponding table name or table alias in that display row. You should see a difference in the key values appearing in the query plans.

Q#2 12 pts Computing transitive closureDevelop the following using MySQL:

Create an empty table (refer to as anstab) having row schema (ssn, superssn) whose columns have the same types as in the mitchell_test_1.EMPLOYEE ssn and superssn columns.

anstab must not have a PK because some rows inserted in anstab have a likelihood of duplication.

1. Given an initialized user-defined MySQL variable (aka bind variable) @f_ssn (its value is any existing EMPLOYEE ssn value)
2. Store the transitive closure chain (@f_ssn, supssn1)(supssn1, supssn2) … (supssnj, null)

as j rows in anstab

1. Store a row in anstab that looks like: – – – – -, – – – – – (it indicates visially that a transitive closure chain is complete)

In this chain, supssn1 represents the superssn value for the EMPLOYEE row with ssn = @f_ssn, supssn2 represents the superssn column for the EPLOYEE row with ssn = supssn1, and so on.

The MySQL arguments/parameters interface between P1’s returned INOUT parameter value and P2 is that in P2, a user-defined variable named @supssn should be initialized to, say ‘000000000’

(or to any other impossible EMPLOYEE table ssn value).

This argument/parameter interaction simplifies the PREPARE statements logic in P1’s code.

• Assuming the above notations, each P2 call to P1 has the form: CALL P1( x, @supssn );

The last P2 execution action inserts a “border” row into anstab consisting of (‘_ _ _’, ‘_ _ _’).

The border row separates consecutive complete transitive closure, where each transitive closure is produced by a call to P2.

= = = = = = = = = = = = = = = = = = = = =

{ You have the option in the P1 & P2 parameter specifications to add another IN mode parameter for the name of the table. This means that instead of hard-coding table name anstab (as done above to simplify the specifications), P1 and P2 become more adaptable procedures. IF you choose to do this, pass the table name enclosed in single quotes, as demoed in module P4

Use the MySQL built-in function ISNULL(x) that returns 1 if x’s value is null, and returns 0 if x’s value is not null. Thus, you can use ISNULL in P2’s code logic to determine when a transitive closure calculation is finished.

Display your source code for P1 and P2 (or all (alternative to P1 & P2) source code you develop)

 

 

Execute your anstab table creation

Call P2 (or your functionally equivalent stored procedure) with argument ‘987987987’

Call P2 (or your functionally equivalent stored procedure) with argument ‘333445555’

Call P2 (or your functionally equivalent stored procedure) with argument ‘888665555’

Display all rows of your final anstab table.

Final Note – DO NOT remove the rows from anstab between P2 calls. The content of anstab must follow the format of the above illustrated “Sample results” and will contain 3 complete transitive closure chains, one chain per P2 call.

Q#3 4 pts Joining projected tables

Earlier we covered horizontal partitioning in which a table T is divided into a number of “partitioned” tables, each partition containing a disjoint subset of entire rows of T.

Only sketched earlier is vertical partitioning in which a table is divided into a number of tables Tk where each Tk has a subset of T’s columns. However, as we will see in this question, the columns of the Tk cannot be disjoint column sets for most vertical partitioning applications.

In large-scale databases, there is often a need for

a) decomposing a table T into tables Tj, 1 <= j <= m and then, at some point in time

b) re-constructing T from the tables Tj.

(Such de/re-constructions occur in (truly) distributed databases where a table can be vertically partitioned across several independent database servers)

Consider the requirement (refer to as “R”) in which the join on all Tj must be exactly all rows of T.

There is a significant problem when implementing R –

Almost every way of creating the tables Tj is such that the join of the Tj WILL NOT equal T …

Given this join issue, fortunately, there is known technique that ensures that the natural join of the Tj satisfies R. That is, the join result returns exactly the rows of T, no more rows, and no fewer rows.::

Given FD: X  Y in relation T(U), T can safely be split into two relations with attributes X in common, so that (Π X,Y (T)) naturalJoin x=x (Π X,Z (T)) = T.

In this equation, Z = U–{X,Y} (i.e., Z is all attributes of T other than X and Y)

This means that when the Tj are natural-joined, no data rows are lost, nor are any spurious (means: extra, incorrect) rows introduced. This process is called “non-lossless-join” decomposition of T into smaller relations Tj.

This problem illustrates the R solution technique using the COMPANY database table DEPENDENT.

To simplify your solution development, instead of tables Tj, use views, as defined below.

PART a)

Create a view (refer to as hv1) consisting of the distinct columns essn,dependent_name,sex,bdate of DEPENDENT. Note that SQL DISTINCT implements the RA Π operation.

X is essn, dependent_name and sex, bdate is Y.

Clearly, X  Y (i.e. X functionally determines Y)

Create a view (refer to as hv2) consisting of distinct columns essn,dependent_name,relationship of DEPENDENT.

Execute and display the results of query “Q1”: the natural join of views hv1 and hv2 on x.

PART b)

Repeat PART a) changing only the columns for the views. To keep descriptions separate, the two views are named de1 and de2.

The columns of de1 are essn,sex,bdate,relationship, and

the columns of de2 are essn,dependent_name

Here X is essn, and Y is sex,bdate,relationship. Notice that X  Y does not hold.

Execute and display the results of query “Q2”: the natural join of views de1 and de2 on x

Suggestions/Hints – Take advantage of the OR REPLACE clause of CREATE VIEW;

it simplifies the scripting and cleanup/reset between successive code executions (eliminates errors such as ‘view xyz already exists …’)

By the way, the default privileges in effect when a user executes a view created by another user is:

so-called SQL invoker privileges. This means that the view is executed with the privileges of the user that is executing the view, not the user that created the view (if different)

Notes on qo

Results of the MySQL query plans are disappointing concerning their completeness/usefulness:

The EXPLAIN EXTENDED format indicates little more than 1) index use per table 2) no index use when

a table is scanned (indicated by a “WHERE” entry in the query plan table column named Extra).

We need only compare the MySQL plan for, say the First query of Q#1 to any advanced RDBMS

(Microsoft SQL server, IBM DB2, Postgres, Oracle) for the exact same query to see the difference in sophistication and usefulness.

Below is the Oracle default query plan for the exact same table schemas, indexes, etc. as Q#1, First query

SQL> select p.pnumber, p.dnum, lname, address, bdate

2 from p, d, e

3 where p.dnum=d.dnumber and

4 d.mgrssn=e.ssn and

5 p.plocation = ‘Stafford’;

———————————————————————————————–

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

———————————————————————————————–

| 0 | SELECT STATEMENT | | 2 | 196 | 7 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | | | | |

| 2 | NESTED LOOPS | | 2 | 196 | 7 (0)| 00:00:01 |

| 3 | NESTED LOOPS | | 2 | 66 | 5 (0)| 00:00:01 |

|* 4 | TABLE ACCESS FULL | PROJECT | 2 | 42 | 3 (0)| 00:00:01 |

| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 12 | 1 (0)| 00:00:01 |

|* 6 | INDEX UNIQUE SCAN | SYS_C0011634 | 1 | | 0 (0)| 00:00:01 |

|* 7 | INDEX UNIQUE SCAN | SYS_C0011630 | 1 | | 0 (0)| 00:00:01 |

| 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 65 | 1 (0)| 00:00:01 |

———————————————————————————————–

Predicate Information (identified by operation id):

—————————————————

4 – filter(“P”.”PLOCATION”=’Stafford’)

6 – access(“P”.”DNUM”=”D”.”DNUMBER”)

7 – access(“D”.”MGRSSN”=”E”.”SSN”)

Oracle qo operations are performed from inner-most rows (here, Id = 5 and 6) to outer-most.

There is much more info compared to a MySQL query plan.

The most notable are the Rows through Time (right-most plan columns) that indicate the relative costs of each qo operation. MySQL does no such thing, as MySQL acts mostly as a Rule-based rather than Cost-based optimizer.

Rule-based optimizers are obsolete.

Relative cost terms are the way that a user can identify the most expensive steps of a query plan, for which adjustments in the query, or changes in existing AMs might improve the query plan.

The Predicate Information trailing section is also helpful as it describes HOW and/or WHY a given index/AM is used in a query WHERE clause..