Homework 3 (due 4/21)
CSC 355

We have continued (and will continue) talking about SQL. This week's material corresponds to what the book covers in Sections 6.1, 6.2, and parts of 6.3 (6.3.6-6.3.8). Next week we'll cover Sections 6.3 and 6.4. After that we'll go back to Chapter 3 in the book for relational design.
Handing it in: Submit to the d2l dropbox. Use some standard format for your homework (Word or pdf, for example), and make sure to include your name and assignment number. Include screenshots for code and testruns.

1. (Reading Assignment) Read Sections 6.1-6.3 of the textbook (Ullman, Widom, A First Course in Database Systems).

2. (SQL, 6pt) On the last homework we added mentor information to the student table. We'll need this set-up for the current problem (my solution is on d2l if you want to use that). Make sure you assign mentors to several (but not all) of the students.

a) [2pt] Write a query which lists all students (SID, Lastname, Firstname) who have not been assigned a mentor.

b) [4pt] Our university has a rule that student mentors must be in the same program as their mentees. Write a query which lists all mentees (SID, Lastname, Program) which have been assigned a mentor (SID, Program) violating this rule. Hint: how many students are involved in this query?

As always, submit screenshots of both queries and output.

3. (SQL, 14pt) You have relation Orders(OrderID, CustomerID, NumberOfItems, ItemsTotal), Customer(CID, Name, State), StateTax(State, SalesTaxRate).

a) [4pt] Create SQL-DDL to create these tables (with foreign keys: CustomerID -> CID) and fill them with a small number of records. E.g. in State you could have ('Illinois', 6.25) and ('Michigan', 6) (do not include all US states).

b) [6pt] Write a query that lists all orders (by OrderID) and the expected price the customer has to pay. Calculate the expected price according to the following rules

c) [4pt] Improve the query to deal with the following situation: Sometimes our order records are incomplete (i.e. contain null values) or missing (state not listed in StateTax table at all). If we don't have ItemsTotal, the query should not get an expected price for the order (i.e. it should be null). If NumberOfItems is missing, we assume it is 1. If there's a null entry for a state's sales tax in the table or if there is no entry for a state in StateTax, we assume the sales tax for that state is 0. (Hint: Recall the coalesce function). Hand in your solution of c) separately from the solution to b).

As always, submit screenshots of both queries and output.

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

This is a useful law to have, and it could help us simplify some database queries ("not(A and B)" has one less negation than "not(A) or 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)", "not(A) or not(B)", "A and B" and "not(A and B)" to check whether DeMorgan's law still holds for 3-valued logic. State explicitly whether DeMorgan's law holds for 3-valued logic or not.

5. (University SQL, 10pt) 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 courses (CourseName, CID) and all students (SID, Lastname, Firstname) who enrolled in those courses. Courses should get listed even if nobody enrolled in them. On the other hand, you do not want to list students who haven't enrolled in a course.

  2. List IDs of students who enrolled in Fall of 2013, but not in winter of 2013. Hint: yes, you just need to list IDs, no names, etc., and that's a bit of a hint. Think in terms of sets. Drawing a Venn diagram may help.


Marcus Schaefer
Last updated: April 15th, 2015.