Assignment 4

Suggested Problems (not to be turned in)

• Rosen, Section 2.4, ex: 5, 9, 11, 12, 16, 21. 23, 29, 31
• Rosen, Section 9.2, ex: 1, 2, 4, 5, 9
• Rosen, Section 5.1, ex: 5, 6, 7, 8, 15, 16, 31, 32, 32

Required Problems, due September 16

Submission Instructions

• Submissions are to be submitted on paper, with one submission from each assigned pair of students, unless a student has explicit (written) permission from the instructor.

• Both partners should collaborate on every problem, and both names should be at the top of the first page. Since this is a collaborative effort, both partners should agree that the work represents their agreement in the correctness of the answers, and the same grade will be recorded for each assignment.

• Problems for each section (each bullet item in the assignment) should begin on a new page, and problems within a section should be in numeric order.

• If a problem requires the writing of a Python program, then the submission should include a printout of the code, a listing of appropriate test runs, and a statement regarding how the test runs provide sufficient evidence to conclude the program works properly.

• Each problem from Rosen's text should be identified with its section number and problem number. Each problem written out as a separate bullet point should be identified in a phrase or sentence, so it is clear which question is being answered.

• When a problem asks for a conclusion, the solution must include both a statement of the conclusion and an argument justifying that conclusion.

• Answers should be placed in Section and problem order, so the first page (more if needed) should give answers for the problems in the first assignment bullet. Answers for subsequent bullets should be in the order of the bullets on the Assignment sheet.

• Although the rules of this course allow the use of sources beyond the discussions of a student pair, any outside references (e.g., from the textbook, from Web sources, from discussions with others, etc.) must be fully cited.

Problems to be Turned In

• Rosen, Section 2.4, ex: 10abc, 17aceg, 22, 32ad
• Rosen, Section 9.2, ex: 8, 10
• Rosen, Section 5.1, ex: 4, 18, 20
• The following groceries table contains the first 5 records with items found in local grocery stores.

  +----+--------------------------------+-----------+-------+----------+
  | id | name                           | category  | price | unitName |
  +----+--------------------------------+-----------+-------+----------+
  |  1 | Pacific Rose Apples            | fruit     |  0.99 | pound    |
  |  2 | Navel Oranges                  | fruit     |  1.69 | pound    |
  |  3 | Honeycrips Apples              | fruit     |  2.99 | pound    |
  |  4 | Anjou Pears                    | fruit     |  1.49 | pound    |
  |  5 | Green Seedless Grapes          | fruit     |  1.79 | pound    |
    

  Also, the following groceryOrders table contains information for order 1 and order 2—orders that customers may sent into the store, indicating the quantity of items they want for each item.

  +-------------+-------------+--------+----------+
  | orderItemID | orderNumber | itemID | quantity |
  +-------------+-------------+--------+----------+
  |          22 |           1 |      1 |     5.10 |
  |          23 |           1 |      5 |     3.70 |
  |          24 |           1 |     11 |     2.50 |
  |          25 |           2 |      1 |     3.50 |
  |          26 |           2 |      5 |     4.50 | 
  |          27 |           2 |      9 |     4.20 |
  |          28 |           2 |     13 |     5.10 |

  • In the jargon of relations, what are the domains in this table.
  • How many items would be retuned by the SQL query (and briefly explain):

      SELECT * FROM groceries JOIN groceryOrders;
        

  • How many items would be retuned by the SQL query (and briefly explain):

      SELECT * FROM groceries JOIN groceryOrders on groceries.id = groceryOrder.orderNumber ;
        

  • Construct a table indicating all items that would be retuned by the SQL query:

      SELECT * FROM groceries JOIN groceryOrders on groceries.id = groceryOrder.orderNumber where groceries.id = 5;
        

    The table should show all relevant rows, including entries for all relevant fields in the table.

  • Identify a likely primary key for the groceries table (and briefly explain))
  • Identify two domains/fields that could comprise a likely secondary key for the groceryOrders table.

created July 22, 2021 revised September 13, 2021
For more information, please contact Henry M. Walker at walker@cs.grinnell.edu.