Homework 4 (due 4/25)
CSC 453

We have finished SQL for the time being (Sections 6.3, 6,4). We will later return to SQL in other contexts (transactions, database programming). We have started talking about relational design (Chapter 3).

The in-class mideterm is scheduled for 5/3 in CDM 819 (a computer lab), online registration is open.


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.4, 3.1, 3.2.1-3.2.3, and 3.3.1, and 3.3.2 of the textbook. If you want to read ahead, complete the remaining Sections of Chapter 3.

2. (University SQL, 15pt) Write SQL for the following problems on the university database. Run the query. Submit screenshots of both query and output.

  1. [5pt] List the total number of students enrolled in each quarter (a quarter is the combination of quarter and year). You only need to list quarters in which students actually enrolled.

  2. [5pt] Order the output from your query in a) by Quarter and Year. Work with academic year, so the order would be Fall 2017, Winter 2017, Spring 2017, Fall 2018, Winter 2018, Spring 2018, Fall 2019, etc. Hint: you can order by expressions, and you may need some function or similar construct to make sure the quarters are in the right (as opposed to the lexicographic) order.

  3.  [5pt] List all students, and how many groups they are president of.

3. (University SQL, 15pt) Write SQL for the following problems on the university database. Run the query. Submit screenshots of both query and output.

  1. [5pt] List students that are members of at least two groups.

  2. [5pt] List groups that have (strictly) more undergraduate than graduate student members. Hint: start with a query that counts the number of ugrd (or grd) members in a particular student group (e.g. gid = 101).

  3. [5pt] List (pairs of) students that have taken two or more classes together. Hint: taking a class together means being enrolled in the same class in the same quarter of the same year. Try grouping.

4. (Inference, 12pt) You have a relation R(A,B,C,D,E,F) and a set of functional dependencies F = {ACD→B, BC→AED, C→D, DE→AC}.

5. (Extra Credit, 5pt) In class we wrote a query for: "List the number of Chicago students in each large program, where a program is large if it contains at least 3 students." That query was not correct, confusing the order of filtering and aggregation. Fix it.
Marcus Schaefer
Last updated: April 20th, 2018.