(a) The complete declaration is below:

CREATE TABLE Members (
    name PRIMARY KEY,
    status CHAR(10)
);

CREATE TABLE Votes (
    res INT,
    country CHAR(50) REFERENCES Members(name),
    vote CHAR(10) CHECK(vote = 'yes' OR vote = 'no' OR vote =
       'abstain'),
    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 */
    SELECT COUNT(*) FROM Votes
    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.

    UNION
( /* 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:

• 5A: In part (b): ignoring the requirement of "permanent" in looking for vetos (-4).

• 5B: In part (e): producing 0 as the count whenever a country has ever voted something other than 'no'. This is a major logic error, equivalent to confusing "not any" with "not all" (-6).

• 5C: In (b): not connecting the count to vv.res. (-4) but (-2) if you did it right once and wrong once.

• 5D: In (a): declaring res and country separate keys (-1).

• 5E: In (b): trying to use vv as if it were the name of a relation in another FROM clause (-4).

• 5F: In (a): Using a CHECK to try to simulate the foreign-key constraint (-1). Remember, the CHECK is not checked on deletions from the referenced relation.

• 5G: In (e): using '0' instead of the integer 0. You would get a type error if you tried to take the union (-1).