Issues with Problem 2
SELECT DISTINCT O.Order_ID, O.Order_Date,
C.Name, L.Quantity, I.Name
FROM Orders O,
Order_Lines L, Items I, Customers C
WHERE
O.Customer_ID = :B1
AND
L.Order_ID = O.Order_ID
AND I.Item_ID =
L.Item_ID
AND C.Customer_ID =
O.Customer_ID;
Observations regarding the non-unique join
to Items:
•If every Item
sharing the same Item_ID also shares the same
Name, then this query
will return one row per Order, thanks to
the DISTINCT. If different Orgs might name an Item differently, then the query is
almost certainly wrong, functionally.
•The inner join to I.Item_ID might succeed where an inner join to I.Item_ID and to I.Org_ID might
fail, causing a subtle functional difference between the two alternatives.
•The query implies a
design question: should there be an Items table keyed
on Item_ID, alone, and an Items_Orgs table keyed on
Item_ID,
Org_ID?
What are the odds that the original
developers thought all this through and got it right?!?
What are the odds that the corner cases were well tested?!?
