09:59

# Exercise #23 page 2

There is one more attempt to “change” the situation in the better way:

Solution 1.15.2

Console
Execute
`SELECT DISTINCT maker FROM Product a, PC b, Laptop c WHERE b.speed >= 750 AND  c.speed >= 750 AND  (a.model = b.model OR  a.model = c.model );`

Using an equality of predicates,

`x AND (y OR z) = (x AND y) OR (x AND z),`
we'll perform syntax conversions of the considered query:

Console
Execute
`SELECT DISTINCT maker FROM Product a, PC b, Laptop c WHERE ((b.speed >= 750 AND  c.speed >= 750 ) AND  a.model = b.model ) OR  ((b.speed >= 750 AND  c.speed >= 750 ) AND  a.model = c.model );`

The rows that satisfy even one of predicates, connected with an OR operator, will appear in the result set. Let's consider, for example, a query with the first predicate:

Console
Execute
`SELECT DISTINCT maker FROM Product a, PC b, Laptop c WHERE ((b.speed >= 750 AND  c.speed >= 750 ) AND  a.model = b.model );`

Let's rewrite it in a more convenient way from the syntax point of view:

Console
Execute
`SELECT DISTINCT maker FROM Product a JOIN  PC b ON a.model = b.model,  Laptop c WHERE (b.speed >= 750 AND  c.speed >= 750 );`
and further

Console
Execute
`SELECT DISTINCT maker FROM (SELECT maker  FROM Product a JOIN  PC b ON a.model = b.model WHERE b.speed >= 750 ) x,  (SELECT *  FROM Laptop c  WHERE c.speed >= 750 ) y;`

Now we may analyze it. First subquery, which we marked as “x”, joins table PC with table Product on a foreign key, selecting the producers of PC with the speed >=750. Second subquery (“y”)  filters models of laptops with the speed >=750.

The way of joining “x” and “y” is called Cartesian product. That is the producer of required PCs in the result set will match with EVERY model of laptop, even if it was produced by ANOTHER maker.

As a result, we will get again the producers that can make only something one. Some difference in comparison with the first solution is in that if NOONE produces laptops with a required speed, then we'll get the empty result set. The first example 1.15.1 doesn't give this partially correct result.

The coincidence of results on the main database is completely accidental. It's turned out that those makers, who produce PC, required by task conditions, also produce required laptops. So, in spite of coincidence of results on “visible” database the query is wrong in any scheme-compatible state of DB.

 Pages 1 2 3 4