Question 5

(a) The complete declaration is below:

    name PRIMARY KEY,
    status CHAR(10)

    res INT,
    country CHAR(50) REFERENCES Members(name),
    vote CHAR(10) CHECK(vote = 'yes' OR vote = 'no' OR vote =
    PRIMARY KEY(res, country)

Oddball notes: You actually did not have to declare vote NOT NULL, because the check fails if the vote is NULL. You did not lose credit for including NOT NULL. Also, many people used conditions such as vote LIKE 'yes'. While not incorrect, there is no reason not to use =. I suspect some people were worrying that a CHAR(10) value is stored like 'yes*******', where * is some mysterious character. It is stored that way, but when the value is extracted by the DMBS for comparison, the value is just 'yes'.

(b,c,d) Here is the complete query, with comments.

SELECT res FROM Votes vv
WHERE 9 <= ( /* count the number of votes for vv.res */
    WHERE vote = 'yes' AND res = vv.res
) AND NOT EXISTS ( /* a permanent member with vote no on vv.res */
    SELECT * FROM Members, Votes
    WHERE name = country AND vote = 'no' AND
          status = 'permanent' AND res = vv.res

(e,f) The continuation of the query, with comments. As many of you noted, this was essentially the "Surigao Strait" query from the Battleships assignment.

( /* names of permanent members that never vetoed, with 0 as
     the value of numVetos */
    SELECT name, 0 AS numVetos FROM Members
    WHERE status = 'permanent' AND name NOT IN ( /* countries that
            ever voted no */
        SELECT country FROM Votes
        WHERE vote = 'no'

Error Codes: