The partreceipt entity is called a weak or

  • No School
  • AA 1
  • lamanhdo
  • 5

This preview shows page 3 - 5 out of 5 pages.

3. Given the relations in Question 2, write the following queries in SQL, and identify relational algebra tree that reflects the order of operations a decent query optimizer would choose. Notice that you should optimize the queries if possible: a. Display a list of Parts (number, desc) ordered in January, 2015. b. Display a list of vendors who quote the lowest price to sell Parts whose number is 123.
4 FROMPART P, PART_RECEIPT PR WHEREP.ITEM_NUMBER = PR.ITEM_NUMBER AND(PR.DATE >= 1/1/2015 ANDPR.DATE <=31/01/2015) A worse SQL query: SELECTP.ITEM_NUMBER, P.DESC FROMPART P WHEREP.ITEM_NUMBER IN(SELECTPR.ITEM_NUMBER FROMPART_RECEIPT PR WHEREPR.DATE >= 1/1/2015 ANDPR.DATE <=31/01/2015) b. SET @M =(SELECTMIN(PRICE) FROMPRICE_QUOTE WHEREITEM_NUMBER = ‘123’) SELECTV.VENDOR_ID, V.ADDRESS FROMVENDOR V, PRICE_QUOTE PQ WHEREV. VENDOR_ID = PQ.VENDOR_ID ANDPQ.ITEM_NUMBER = ‘123’ ANDPQ.PRICE = @M; A worse SQL query: SELECTV.VENDOR_ID, V.ADDRESS FROMVENDOR V, PRICE_QUOTE PQ WHEREV. VENDOR_ID = PQ.VENDOR_ID ANDPQ.ITEM_NUMBER = ‘123’ ANDPQ.PRICE = (SELECTMIN(PRICE) FROMPRICE_QUOTE WHEREITEM_NUMBER = ‘123’)
5

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture