Need help solving three queries.

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

Q#1 4 pts 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 closure

MySQL does not support the SQL WITH clause, one use is: calculating recursive transitive closure.

Recall that given a COMPANY database EMPLOYEE row PK value, such as ‘333445555’, the transitive closure “chain” of pairs (ssn, superssn) is (‘333445555’,’ 888665555’) (‘888665555’,null).

Each PK value has an associated transitive closure chain, the last element of which is always (‘888665555’,null) because Borg is the only employee having no supervisor.

Develop 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.

There are many ways to populate table anstab as above. We need to simulate the standard SQL WITH using: stored procedure/MySQL processing. The following outlines a suggested solution.

Create a stored procedure (refer to here as P1).

P1 has IN mode parameter ssn and INOUT parameter supssn. Each P1 execution stores a row of a transitive closure chain. P1 uses the MySQL CONCAT function to build and then execute two PREPARE statements:

The first PREPARE statement stores into supssn the superssn column value of the EMPLOYEE row having PK value ssn < = this ssn refers to the IN parameter


The second PREPARE statement stores into anstab the row

(value of IN parameter ssn, value of INOUT parameter supssn).

Because supssn is an INOUT parameter, supssn passes back to the caller of P1 the first element of the next transitive closure pair.

Create another stored procedure (refer to as P2).

Each P2 execution calls P1 several times such that all rows of a complete transitive closure are stored in anstab.

P2 has IN mode parameter (refer to as x) whose value is the left component of the first member pair of a transitive closure to be computed. x initiates/starts the storage into anstab of one complete transitive closure.

P2 executes a loop whose loop body continually calls P1 until the previous P1 call returns supssn value null – – returning null means that a complete transitive closure chain has been stored in anstab, and then, the loop terminates (thus finishing a transitive closure).

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 }

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

Sample results

At MySQL command level, the two successive calls call P2(‘999887777’); and call P2( ‘333445555’); with an initially existing and empty anstab table produce:


| ssn | supssn |


| 333445555 | 888665555 | < == Recall that MySQL displays string type column values without quotes

| 888665555 | NULL | < == Last pair of a transitive closure

| _ _ _ | _ _ _ | < == Transitive closure chain separator

| 999887777 | 987654321 |

| 987654321 | 888665555 |

| 888665555 | NULL | < == Last pair of a transitive closure

| _ _ _ | _ _ _ | < == Transitive closure chain separator


7 rows in set (0.00 sec)

Hint#1 – Although the SQL standard specifies (using an indicator variable) a way for 3GL host languages to recognize a null value returned from SQL, each 3GL has its own way of detecting a null value returned from SQL. MySQL does not implement such SQL standard indicator variables.

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.

Hint#2 – MySQL error messages are terse and many are inaccurate/unhelpful for stored procedure development and debugging. It is a good idea, particularly during P1 development, to trace/display passed argument values, bind variable values, and so on.


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.


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.


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..

Calculate your paper price
Pages (550 words)
Approximate price: -

Why Choose Us

Quality Papers

We value our clients. For this reason, we ensure that each paper is written carefully as per the instructions provided by the client. Our editing team also checks all the papers to ensure that they have been completed as per the expectations.

Professional Academic Writers

Over the years, our Acme Homework has managed to secure the most qualified, reliable and experienced team of writers. The company has also ensured continued training and development of the team members to ensure that it keep up with the rising Academic Trends.

Affordable Prices

Our prices are fairly priced in such a way that ensures affordability. Additionally, you can get a free price quotation by clicking on the "Place Order" button.

On-Time delivery

We pay strict attention on deadlines. For this reason, we ensure that all papers are submitted earlier, even before the deadline indicated by the customer. For this reason, the client can go through the work and review everything.

100% Originality

At Essay Helper, all papers are plagiarism-free as they are written from scratch. We have taken strict measures to ensure that there is no similarity on all papers and that citations are included as per the standards set.

Customer Support 24/7

Our support team is readily available to provide any guidance/help on our platform at any time of the day/night. Feel free to contact us via the Chat window or support email:

Try it now!

Calculate the price of your order

We'll send you the first draft for approval by at
Total price:

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

Essay Helper has stood as the world’s leading custom essay writing services providers. Once you enter all the details in the order form under the place order button, the rest is up to us.


Essay Writing Services

At Essay Helper, we prioritize on all aspects that bring about a good grade such as impeccable grammar, proper structure, zero-plagiarism and conformance to guidelines. Our experienced team of writers will help you completed your essays and other assignments.


Admission and Business Papers

Be assured that you’ll definitely get accepted to the Master’s level program at any university once you enter all the details in the order form. We won’t leave you here; we will also help you secure a good position in your aspired workplace by creating an outstanding resume or portfolio once you place an order.


Editing and Proofreading

Our skilled editing and writing team will help you restructure you paper, paraphrase, correct grammar and replace plagiarized sections on your paper just on time. The service is geared toward eliminating any mistakes and rather enhancing better quality.


Technical papers

We have writers in almost all fields including the most technical fields. You don’t have to worry about the complexity of your paper. Simply enter as much details as possible in the place order section.