Midterm Notes

Know the following:

  1. Understand the relationship between a disk’s seek time, rotational latency, transfer rate, buffer size and file size when reading a file from disk.
  2. How to write simple to complex SQL statements as applied to this database. It contains five related tables.
    1. Find menu items costing between $7 and $10.
      SELECT MID,mName,price 
      FROM `MenuItem` 
      WHERE price>=7 AND price<=10
    2. Compute Ron Wong’s bill (from his order items) including tax.
      SELECT SUM(qty*price) AS tab, SUM(qty*price)*0.04712 AS tax,
           SUM(qty*price)+SUM(qty*price)*0.04712 AS total
      FROM `OrderItem` NATURAL JOIN MenuItem
      WHERE OID=2
    3. How much did Ron Wong’s order cost the restaurant in ingredients?
      Intermediate – list every ingredient including unit quantity (uQty), unit price (price) and number of units (qty):

      SELECT *, qty*uQty*unitCost AS qqCost
      FROM OrderItem NATURAL JOIN MenuItem
         NATURAL JOIN Uses NATURAL JOIN Ingredient
      WHERE OID=2

      Final – sum the unit quantity times the unit price times the number of units to one number:

      SELECT SUM(qty*uQty*unitCost) AS TOT_qqCost
      FROM OrderItem NATURAL JOIN MenuItem
         NATURAL JOIN Uses NATURAL JOIN Ingredient
      WHERE OID=2
    4. What is the total restaurant ingredient cost for each of the 4 orders?
      SELECT customerName, SUM(qty*uQty*unitCost) AS TOT_qqCost
      FROM OrderItem NATURAL JOIN MenuItem NATURAL JOIN Uses 
          NATURAL JOIN Ingredient NATURAL JOIN Orders
      GROUP BY OID
  3. How to rewrite two of the more simple SQL statements from (2) in an equivalent relational algebra form.
    1. Find menu items costing between $7 and $10.
      π MID,mName,price ( σ price≥7 AND price≤10 ( MI ) )
  4. How to rewrite two of the more simple SQL statements from (2) as a Datalog program.
    1. Find menu items costing between $7 and $10.
      S(MID,mName,price) ← MI(MID,mName,price) AND price≥7 AND price≤10