CS 61A: Quiz 4

Due by 11:59pm on Thursday, 4/30

Instructions

Download quiz04.zip. Inside the archive, you will find a file called quiz04.sql, along with a copy of the OK autograder.

Complete the quiz and submit it before 11:59pm on Thursday, 4/30. You must work alone, but you may talk to the course staff (see Asking Questions below). You may use any course materials, including an interpreter, course videos, slides, and readings. Please do not discuss these specific questions with your classmates, and do not scour the web for answers or post your answers online.

Your submission will be graded automatically for correctness. Your implementations do not need to be efficient, as long as they are correct. We will apply additional correctness tests as well as the ones provided. Passing these tests does not guarantee a perfect score.

Asking Questions: If you believe you need clarification on a question, make a private post on Piazza. Please do not post publicly about the quiz contents. If the staff discovers a problem with the quiz or needs to clarify a question, we will email the class via Piazza. You can also come to office hours to ask questions about the quiz or any other course material, but no answers or hints will be provided in office hours.

Submission: When you are done, submit with python3 ok --submit. You may submit more than once before the deadline; only the final submission will be scored.

Using OK

The ok program helps you test your code and track your progress. The first time you run the autograder, you will be asked to log in with your @berkeley.edu account using your web browser. Please do so. Each time you run ok, it will back up your work and progress on our servers. You can run all the doctests with the following command:

python3 ok

To test a specific question, use the -q option with the name of the function:

python3 ok -q <function>

By default, only tests that fail will appear. If you want to see how you did on all tests, you can use the -v option:

python3 ok -v

If you do not want to send your progress to our server or you have any problems logging in, add the --local flag to block all communication:

python3 ok --local

When you are ready to submit, run ok with the --submit option:

python3 ok --submit

Readings: You might find the following references useful:

Table of Contents

SQL

To complete this quiz, you will need to use Sqlite version 3.8.3 or greater. See Lab 12 for setup and usage instructions.

To check your progress, you can run sqlite3 directly by running:

sqlite3 -init quiz04.sql

You should also check your work using ok:

python3 ok

If OK is not able to use sqlite3 to verify your work directly, it will at least show you the output of the test cases, like the following:

size > Suite 1 > Case 1

Unfortunately, OK is unable to use sqlite3 to test your code directly.
Here is a transcript of what your code does in the sqlite3 interpreter.

TEST:
    .read quiz04.sql
    select * from open_locations where n >= 5;
EXPECTED (order does not matter):
    5
    7
    9
    10
OUTPUT:
    5
    7
    9
    10

Please manually check if your solution's output is correct.

As long as the displayed OUTPUT matches the EXPECTED output, your solution will be considered correct (even though OK will report a test failure).

Data

A block of 10 store locations, numbered 1 through 10, already has some cafes. Cafes can serve espresso, bagels, coffee, muffins, and eggs (but nothing else).

-- Locations of each cafe
create table cafes as
  select "nefeli" as name, 2 as location union
  select "brewed"        , 8             union
  select "hummingbird"   , 6;

-- Menu items at each cafe
create table menus as
  select "nefeli" as cafe, "espresso" as item union
  select "nefeli"        , "bagels"           union
  select "brewed"        , "coffee"           union
  select "brewed"        , "bagels"           union
  select "brewed"        , "muffins"          union
  select "hummingbird"   , "muffins"          union
  select "hummingbird"   , "eggs";

-- All locations on the block
create table locations as
  with locations(n) as (
    select 1 union
    select n+1 from locations where n < 10
  )
  select * from locations;

Question 1

You would like to open a new cafe. Create a table open_locations that has a single column n where each row is a location that is not already occupied by an existing cafe.

-- Locations without a cafe
create table open_locations as
  select "REPLACE THIS LINE WITH YOUR SOLUTION";

-- select * from open_locations where n >= 5;
-- Expected output:
--   5
--   7
--   9
--   10

Hint: Join locations and cafes in the from clause, so that you can compare a location n with the location of an existing cafe.

Hint: A location is open if its minimum distance from an existing cafe is more than 0. The built-in abs and min functions can be used to express this condition in a having clause.

You can test your solution interactively using sqlite3 by comparing with the expected output above.

$ sqlite3 -init quiz04.sql
sqlite3> select * from open_locations where n >= 5;

You can also test your solution using ok:

python3 ok -q open

Question 2

To limit competition, the block mandates that no cafe can have a menu item that is sold by another cafe within 2 locations. So, if location 2 already sells bagels, then a cafe located at 1, 3, or 4 cannot sell bagels. You may assume that every item is sold by at least one cafe.

Create a table allowed that has two columns, a location n and a menu item. The rows should contain every item that can be sold at every location, based on these constraints. It should include occupied locations that could expand their current menus.

-- Items that could be placed on a menu at an open location
create table allowed as
  with item_locations(item, location) as (
    select item, location from cafes, menus where name = cafe
  )
  select "REPLACE THIS LINE WITH YOUR SOLUTION";

-- select * from allowed where n >= 5;
-- Expected output:
--   5|bagels
--   5|coffee
--   5|espresso
--   6|espresso
--   7|espresso
--   8|espresso
--   9|eggs
--   9|espresso
--   10|eggs
--   10|espresso

Hint: Join locations and item_locations, so that you can compare how far a location n is from the location at which an item is already sold.

Hint: Group by a combination of the location n and the item that might be sold at n; use a having clause to filter the groups.

Hint: You do not need to use open_locations from the previous question.

You can test your solution interactively using sqlite3 by comparing with the expected output above.

$ sqlite3 -init quiz04.sql
sqlite3> select * from allowed where n >= 5;

You can also test your solution using ok:

python3 ok -q allowed

Question 3: Challenge Problem (optional)

This question is optional and ungraded. It is strongly recommended for practice! You will need to answer the previous two questions successfully to answer this question.

You decide you will sell as many menu items as possible in your new location. Create a table full that has three columns, an open location n, a comma-separated list of items that you would sell, and the length of that list. The items should be in alphabetical order, and only the longest possible list of items for each location should appear as a row.

-- Open locations and their maximum-length menus
create table full as
  select "REPLACE THIS LINE WITH YOUR SOLUTION";

-- select n, items from full where n >= 5;
-- Expected output:
--   5|bagels, coffee, espresso
--   7|espresso
--   9|eggs, espresso
--   10|eggs, espresso

You can test your solution interactively using sqlite3 by comparing with the expected output above.

$ sqlite3 -init quiz04.sql
sqlite3> select n, items from full where n >= 5;

You can also test your solution using ok:

python3 ok -q full