5.5 Exercises (See Section A.1 for the solution to each exercise.)

1. Diagram the following query:

    SELECT ...  FROM Customers C, ZIP_Codes Z, ZIP_Demographics D, Regions R WHERE C.ZIP_Code=Z.ZIP_Code   AND Z.Demographic_ID=D.Demographic_ID   AND Z.Region_ID=R.Region_ID   AND C.Active_Flag='Y'   AND C.Profiled_Flag='N'   AND R.Name='SOUTHWEST'   AND D.Name IN ('YUPPIE', 'OLDMONEY'); 

   Make the usual assumptions about primary-key names , except that the primary key of ZIP_Codes is simply ZIP_Code , and note that the Name columns of both REGIONS and ZIP_Demographics are also uniquely indexed. You have 5,000,000 rows in Customers , 250,000 rows in Zip_Codes , 20 rows in ZIP_Demographics , and 5 rows in Regions . Assume all foreign keys are never null and always point to valid primary keys. The following query returns 2,000,000 rows:

    SELECT COUNT(*) FROM Customers C WHERE Active_Flag='Y' AND Profiled_Flag='N'; 

2. Diagram the following query:

    SELECT ... FROM Regions R, Zip_Codes Z, Customers C, Customer_Mailings CM,       Mailings M, Catalogs Cat, Brands B WHERE R.Region_ID(+)=Z.Region_ID   AND Z.ZIP_Code(+)=C.ZIP_Code   AND C.Customer_ID=CM.Customer_ID   AND CM.Mailing_ID=M.Mailing_ID   AND M.Catalog_ID=Cat.Catalog_ID   AND Cat.Brand_ID=B.Brand_ID   AND B.Name='OhSoGreen'   AND M.Mailing_Date >= SYSDATE-365 GROUP BY... ORDER BY ... 

   Start with the same assumptions and statistics as in Exercise 1. Customer_Mailings contains 30,000,000 rows. Mailings contains 40,000 rows. Catalogs contains 200 rows. Brands contains 12 rows and has an alternate unique key on Name . The following query returns 16,000 rows:

    SELECT COUNT(*) FROM Mailings M WHERE Mailing_Date >= SYSDATE-365; 

3. Diagram the following query:

    SELECT ... FROM Code_Translations SPCT, Code_Translations TRCT, Code_Translations CTCT,       Products P, Product_Lines PL, Inventory_Values IV, Brands B,      Product_Locations Loc, Warehouses W, Regions R,       Inventory_Taxing_Entities ITx, Inventory_Tax_Rates ITxR, Consignees C WHERE W.Region_ID=R.Region_ID   AND Loc.Warehouse_ID=W.Warehouse_ID   AND W.Inventory_Taxing_Entity_ID=ITx.Inventory_Taxing_Entity_ID   AND ITx.Inventory_Taxing_Entity_ID= ITxR.Inventory_Taxing_Entity_ID   AND ITxR.Effective_Start_Date <= SYSDATE   AND ITxR.Effective_End_Date > SYSDATE   AND ITxR.Rate>0   AND P.Product_ID=Loc.Product_ID   AND Loc.Quantity>0   AND P.Product_Line_ID=PL.Product_Line_ID(+)   AND P.Product_ID=IV.Product_ID   AND P.Taxable_Inventory_Flag='Y'   AND P.Consignee_ID=C.Consignee_ID(+)   AND P.Strategic_Product_Code=SPCT.Code   AND SPCT.Code_Type='STRATEGIC_PRODUCT'   AND P.Turnover_Rate_Code=TRCT.Code   AND TRCT.Code_Type='TURNOVER_RATE'   AND P.Consignment_Type_Code=CTCT.CODE   AND CTCT.Code_Type='CONSIGNMENT_TYPE'   AND IV.Effective_Start_Date <= SYSDATE   AND IV.Effective_End_Date > SYSDATE   AND IV.Unit_Value>0   AND P.Brand_ID=B.Brand_ID   AND B.Name='2Much$'   AND ITX.Tax_Day_Of_Year='DEC31' GROUP BY... ORDER BY ... 

   Start with the same assumptions and statistics as in Exercises 1 and 2, except that W.Inventory_Taxing_Entity_ID points to a valid taxing entity only when it is not null, which is just 5% of the time. The counts for table rows are as follows :

   Products=8,500

   Product_Lines=120

   Inventory_Values=34,000

   Brands=12

   Product_Locations=176,000

   Warehouses=80

   Regions=5

   Inventory_Taxing_Entities=4

   Inventory_Tax_Rates=7

   Consignees=14

   Code_Translations has a two-part primary key: Code_Type, Code .

   Inventory_Values and Inventory_Tax_Rates have a time-dependent primary key consisting of an ID and an effective date range, such that any given date falls in a single date range for any value of the key ID. Specifically, the join conditions to each of these tables are guaranteed to be unique by the Effective_Start_Date and Effective_End_Date conditions, which are part of the joins, not separate filters. (Unfortunately, there is no convenient way to enforce that uniqueness through an index; it is a condition created by the application.) The following queries return the rowcounts shown in the lines that follow each query:

     Q1:  SELECT COUNT(*) A1 FROM Inventory_Taxing_Entities ITx   WHERE ITx.Tax_Day_Of_Year='DEC31'  A1: 2  Q2:  SELECT COUNT(*) A2 FROM Inventory_Values IV   WHERE IV.Unit_Value>0   AND IV.Effective_Start_Date <= SYSDATE   AND IV.Effective_End_Date > SYSDATE  A2: 7,400  Q3:  SELECT COUNT(*) A3 FROM Products P   WHERE P.Taxable_Inventory_Flag='Y'  A3: 8,300  Q4:  SELECT COUNT(*) A4 FROM Product_Locations Loc   WHERE Loc.Quantity>0  A4: 123,000  Q5:  SELECT COUNT(*) A5 FROM Inventory_Tax_Rates ITxR   WHERE ITxR.RATE>0   AND ITxR.Effective_Start_Date <= SYSDATE   AND ITxR.Effective_End_Date > SYSDATE  A5: 4  Q6:  SELECT COUNT(*) A6 FROM Inventory_Values IV   WHERE IV.Effective_Start_Date <= SYSDATE   AND IV.Effective_End_Date > SYSDATE  A6: 8,500  Q7:  SELECT COUNT(*) A7 FROM INVENTORY_TAX_RATES ITxR   WHERE ITxR.Effective_Start_Date <= SYSDATE   AND ITxR.Effective_End_Date > SYSDATE  A7: 4  Q8:  SELECT COUNT(*) A8 FROM Code_Translations SPCT   WHERE Code_Type = 'STRATEGIC_PRODUCT'  A8: 3  Q9:  SELECT COUNT(*) A9 FROM Code_Translations TRCT   WHERE Code_Type = 'TURNOVER_RATE'  A9: 2  Q10: SELECT COUNT(*) A10 FROM CTCT   WHERE Code_Type = 'CONSIGNMENT_TYPE'  A10: 3 

4. Fully simplify the query diagram for Exercise 1. Try starting from the queries and the query statistics, rather than from the full query diagrams. Then, compare your result with what you get when you start from the full query diagrams that you already did.

5. Fully simplify the query diagram for Exercise 2, following the guidelines in Exercise 4.

6. Fully simplify the query diagram for Exercise 3, following the guidelines in Exercise 4.