1. (Reading Assignment) Read Sections 6.1-6.3 of the textbook .

2. (Nulls and 3-valued logic, 8pt) Remember Date's example from class (last slide of the Basic SQL presentation). Date argues that queries can "produce results that are correct according to the three-valued logic but not correct in the real world". Some people would argue that the problem is with the query that Date writes:

Help Date write a correct query, i.e. a query that lists all pairs (SNO,PNO) "where either the supplied and part cities are different or the part city isn't Paris (or both)", even if there are null values in the city fields. Test-run it on a couple of examples to make sure it really works correctly. E.g. in Date's sample database

(S1,P1) should get listed (since City is either London, in which case it's different from Paris, or different from London, so supplier and part city are different and it should get listed). But the query should also still work if S.City contains null values (or, of course, other cities than London). Hint: there are four possible cases to consider depending on whether S.City and/or P.City are null. Analyze the four cases and figure out which of the cases cause the query above to fail (e.g. if both S.city and P.city are known, the query works just fine). Then add conditions to deal with that case/those cases. Briefly explain your reasoning for each case.

3. (3-valued logic, 7pt)  Not all 2-valued truths are 3-valued truths. E.g. in 2-valued logic the condition "A or not(A)" can always be replaced with "TRUE", but that's not the case in 3-valued logic (if A is unknown). In 2-valued logic there is a law which states that "not(A or B)" is equivalent to "not(A) and not(B)" for all truth-values of A and B. Intuitively, both are ways of expressing that neither A nor B are true. We can prove the equivalence using a truth table in which we "plot" the truth values of "not(A or B)" compared to "not(A) and not(B)" for all combinations of A and B to verify that they are equal:

This is a useful law to have, and it can help us simplify some database queries ("not(A or B)" has one less negation than "not(A) and not(B)"), but is this law still true for 3-valued logic? Build a truth-table like the one above for all 9 possible combinations of truth-values of A and B, and calculate expressions "not(A)", "not(B)", "A or B", "not(A or B)" and "not(A) and not(B)" to check whether this law still holds for 3-valued logic.

State clearly whether it does or not.

4. (University SQL, 25pt) Write SQL for the following problems on the university database. Run each query. Submit screenshots of both query and output. Note: Do not use SQL features (e.g. GROUP BY) that we haven't seen in class.

1. List students (SID, Lastname, Firstname) and which groups (GroupName, Founded) they are members of. Students should be listed even if they are not members of a group. However, we don't want to list groups that have no members.

2. List students (SID, Lastname, Firstname) who enrolled in DC and GAM classes, but not in a CSC class.

3. We are planning to establish the following rule at our university: To become a member of the Computer Science Society, you must have taken at least one CSC class. List all students (SID, Lastname, Firstname) that violated this role (in hindsight); that is, when they became members of the computer science society, they had not yet taken any CSC class.

4. List groups (GID, Name) which only have undergraduate members.

5. List students (SID, Lastname, Firstname) who have a unique SSN (in the student table). That is, list all students for whom there isn't another student with the same SSN. Note: since we declared SSN unique, there can't be two students with the same SSN, so for testing purposes you have to drop/disable the uniqueness constraint; since it's not a named constraint, it will  be easiest to modify the DDL to remove the constraint when building the database; alternatively, you can name the constraint in the DDL, and then use the ALTER TABLE command to DISABLE or ENABLE the constraint by name. Hint: your call whether you want to list the three students without SSN, either way is fine, so I'd recommend going with whatever is easier.

5. (Extra Credit, SQL, hard, 10pt). List studentgroups which have the same members. Hint: no, really, this one is tough, only try if everything else is working.