We have completed Chapter 6 on SQL, finishing with aggregation (GROUP BY), covered in Section 6.4and transactional processing (Section 6.6). Transactions point us to PL/SQL which we will see later in the course. We started talking about relational design (3.1), of which we'll see more after the midterm. If you want to look ahead, check out Chapter 3.
The in-class midterm is scheduled for 5/6 in the lab CDM 819.
1. (Reading Assignment) Read Sections 6.4.5-6.4.7, 6.6, and 3.1 of the textbook (Ullman, Widom, A First Course in Database Systems).
2. (University SQL, 15pt) Write SQL for the following problems on the university database. Run the query. Submit screenshots of both query and output.
[5pt] List students who enrolled in more 'IT' than 'GAM' courses. Hint: start with a query that counts how many 'IT' courses a particular student (e.g. SID = 11035) enrolled in.
[5pt] List students that have taken all CSC courses. Note: in our current database that would be CSC 489, CSC 440, CSC 355, and CSC 389, but in your query do not hardcode this list of courses (or how many CSC courses there are). Your query needs to keep working even if courses are added or removed from the course table. Hint: there are two ways of doing this: using sets, or using counting.
[5pt] List all courses, and how often they have been offered. Note: A course should be listed even if it has never been offered (with a count of 0 in that case).
3. (Extended University SQL, 12pt) The queries in this exercise require you to extend your SQL-DDL for the university database first (as described below). Make sure you read the whole problem first so you know how/what data you need to add.
[2pt] Add a grade field to the enrolled table and assign grades from 0.0 to 4.0 to students enrolled in classes. Include just the new DDL for the enrolled table, including the new/modified data in the table. Hint: you can update the enrolled table with some random data: dbms_random.value() returns a random value in the range (0,1), that could come in handy (with an update statement).
[5pt] Based on this extended university database write and run a query that lists students (SID, Lastname, Firstname) and their GPA, as long as the GPA is at least 2. Note: the GPA (grade point average) is the average grade a student earned in all classes they took.
[5pt] We're worried about grade inflation: list courses (Department, CourseNr, CourseName, Quarter, Year) in which the average student grade has been 3.5 or higher. For the purposes of this problem, a course is a course offered in a particular quarter/year.
4. (Reaching Closure, 12pt) You have a relation R(A,B,C,D,E,F) and a set of functional dependencies F = {AB → DE, EF → B, C → E, A → F, F → C }.
[3pt] Determine F+. Does F determine D?
[3pt] Can we infer CD → BE from F ? Justify your answer using closure.
[3pt] A friend of your discovered a new business rule: CF → B. Is it necessary to add that rule to F ?