[Assignment Spec]  [Database Design]  [Examples]  [Testing]  [Submitting]  [Fixes+Updates]

This file will contain descriptions for any fixes to solve minor bugs that people might find in the supplied code/database. Each fix is tagged with a timestamp, which is when I fixed it in the supplied code. If you collected the code/database after that time, it will already have the relevant fix included.

#10: no matches in Q12   (Wed 28 Sep 10:00pm)

Q12 didn't specify how to handle the case where there are no matching beers. If there are no beers matching partial_name, then return an empty table (0 rows).

#9: LIMIT 1 ban   (Wed 28 Sep 1:00pm)

Don't use ORDER BY ... LIMIT 1 in your views. Pay attention to Note #1 in the spec.

#8: coding style   (Wed 28 Sep 1:00pm)

There was always the intention to have a style mark, but it didn't seem to fit with "Tasks", where all the marks were specified. I've added the mark for coding style into the spec explicitly as Task Q0.

#7: what's in a Location?   (Tue 27 Sep 10:30pm)

Locations will always have at least one of metro or town as not null. This is true in the current database, and will be true in the extra "unseen" database used in our testing, but if you wanted to ensure that it was true in all instances of this database (same schema, different data) you could add a constraint to the Locations table (left as an exercise for the reader).

#6: order of wording in barrel-aged beers   (Tue 27 Sep 10:30pm)

There are a number of beers where "aged" comes before "barrel". We only want to consider notes where "barrel" comes before "aged".

The words "aged" and "barrel" may appear in either order.

#5: tweaks on schema   (Mon 26 Sep 8:30am)

The original ER diagram on the Database Schema page did not accurately reflect some aspects of the SQL schema. The original diagram suggested that each beer was brewed by exactly one brewer; the existence of collaboration beers indicates that this is not the case. The original diagram had a totle participation between Beer and Ingredient. While this is true in reality, the database has many beers which have no ingredients recorded. The ER diagram has been updated to reflect this; there is no change to the SQL schema or the database.

#4: output format for Q11   (Mon 26 Sep 7:30am)

Q11 didn't state how the values of minABV and maxABV were to be formatted. They are most likely calculated as a float, but their final values should be type-cast to numeric(4,1).

#3: wording of Q6   (Sun 25 Sep 5:00pm)

The wording of Q6 didn't make clear that you only needed to list the strongest beer(s) (beer(s) with highest ABV). It was in my head, but unfortunately, not on the page.

#2: treatment of "collaboration beers"   (Sun 25 Sep 3:00pm)

An issue that affects just Q6 and Q12 is how you consider beers that involve multiple brewers. Such beers should be considered as a single beer, with a single id. In dealing with them, represent the brewer by a string that combines the names of all breweries, separated by a "+" sign.

#1: output from q4 had incorrect counts   (Sun 25 Sep 2:30pm)

Changed the output from the q4 view in Examples. I was double-counting beers that involved multiple breweries (so-called "collaboration beers"). If you have a count of 106 for plain IPA, you have probably done the same thing.