CSCC43 Summer 2021

Calendar

CSCC43 Summer 2021

Name	Date	Tags	Weight
Midterm	@June 28, 2021 9:00 AM-11:00 AM	Individual & Proctored	30%
Assignment 3	@July 26, 2021	Group Work (3)	12%
Assignment 1	@June 8, 2021 11:59 PM	Group Work (2)	8%
Assignment 2	@July 12, 2021	Group Work (3)	10%

RelaX

Relax Guide by Andrew Leung

1. go to https://dbis-uibk.github.io/relax/calc/local/uibk/local/0
2. click 'Group Editor' tab to create a database
3. paste your database (make sure the formatting is correct, see employee_data base_creation.txt for an example)
4. click 'preview' button to preview your database
5. click 'use Group in editor' to now use your newly created database for your relational algebra queries (you will see on the left side that the database being used has changed)
6. click 'Relational Algebra' tab to test your queries
7. paste your query (make sure the formatting is correct, see employee_queries.txt for some examples, SPACING IS IMPORTANT)
8. click 'execute query' button to run your query

Once you run your query, you can see a tree visualization of how your query is processed against the database as well as the result of your query.

Week 2

Tutorial Slides

Example Employee DB For RelaX

group: employee_data

employee = {
emp_ID:number, person_name:string
	123,  	'Rahul'
	234	'Alex'
	456	'Emma'
	789	'Jemma'
	341	'Nida'
}
	company = {
company_name:string, rank:number
	'A'	4
	'B'	5
	'C'	6
	'D'	2
	'E'	3
}
roster = {emp_ID:number, company_name:string, salary:number, city:string
	234	'A'	12000000	'Miami'
	123	'B'	234444	'Kent'
	456	'C'	567777	'Miami'
	789	'D'	123333	'LA'
	341	'E'	345555	'San Francisco'
}

Tutorial 1 Worksheet Solutions w/ some other equivalent solutions and explanations

Notes

Keys

In general, keys is an umbrella term for defining various ways of uniquely/efficiently accessing tuples from a relation.

Q: What are Keys, Super keys, Primary-keys, Foreign keys ... etc?

https://www.guru99.com/dbms-keys.html

Q: What's the difference between a Super Key and Composite Key?

https://www.geeksforgeeks.org/difference-between-super-key-and-candidate-key/

Q: What's the difference between Primary Key and Foreign Key?

Primary Key vs Foreign Key

Key Type	Primary Key	Foreign Key
Purpose	Uniquely identifies a row in a table	An attribute in a table that is a primary key of another table (creating a relationship between the two tables)
Null Values	Never accepts null values	may accept multiple null values
Indexing (not covered yet)	Primary key is a clustered index and data in the DBMS table are physically organized in the sequence of the clustered index.	A foreign key cannot automatically create an index, clustered or non-clustered. However, you can manually create an index on the foreign key.
Amount	You can only have the single Primary/Composite key in a table.	You can have multiple foreign keys in a table.

Additional Notes

(Not taught in lecture)

Additional Practice

Some students requested some additional practice for relational algebra so I compiled a list of worksheets found online. I cannot promise these will be helpful or these are the kinds of questions our Prof. will evaluate you on. This is not mandatory. This is not graded. This is completely optional.

Extra Relational Algebra Practice From Other Schools

Relational Algebra Worksheet, By York University (w/ solutions)

Relational Algebra Worksheet Part 1, by University of Bozen-Bolzano
- Solutions

Extra Relational Algebra Practice From Past CSCC43 Offerings

(Question are obtained from UTSC Old Exam Repository)

Past Final Relational Algebra Questions - Worksheet
- No solutions, however you can come to office hours to discuss your solutions. I've turned on the commenting feature so students can crowdsource and share their solutions. Since this not assigned work, it's okay to collaborate on this.

Relational Algebra Worksheet, by Prof. Anna Bretscher
- Solutions

Week 3 (No Lecture Or Tutorials)

Week 4

Tutorial Slides

Tutorial 2 Worksheet Solutions w/ some other equivalent solutions and explanations

Recording

Basic Relational Algebra Operations Table

Example Student DB For RelaX

group: StudentDB

Student = {
	sID:number, surName:string, firstName:string, campus:string, email:string, cgpa:number
	99132, 'Avery', 'Marchmount', 'StG', 'avery@cs', 3.13
	98000, 'William', 'Fairgrieve', 'StG', 'will@cs', 4.00
	99999, 'Afsaneh', 'Ali', 'UTSC', 'aali@cs', 2.98
	00157, 'Leilani', 'Lakemeyer', 'UTM', 'lani@cs', 3.42
	11111, 'Homer', 'Simpson', 'StG', 'doh@gmail', 0.4
}

Course = {
	cNum:number, name:string, dept:string, breadth:boolean
	343, 'Intro to Databases', 'CSC', false
	207, 'Software Design', 'CSC', false
	148, 'Intro to Comp Sci', 'CSC', false
	263, 'Data Struct & Anal', 'CSC', false
	320, 'Intro to Visual Computing', 'CSC', false
	200, 'Intro Archaeology', 'ANT', true
	203, 'Human Biol & Evol', 'ANT', false
	150, 'Organisms in Environ', 'EEB', false
	216, 'Marine Mammal Bio', 'EEB', false
	263, 'Compar Vert Anatomy', 'EEB', false
	110, 'Narrative', 'ENG', true
	205, 'Rhetoric', 'ENG', true
	235, 'The Graphic Novel', 'ENG', true
	200, 'Environmental Change', 'ENV', false
	320, 'Natl & Intl Env Policy', 'ENV', false
	220, 'Mediaeval Society', 'HIS', true
	296, 'Black Freedom', 'HIS', true
	222, 'COBOL programming', 'CSC', false
}

Offering = {
	oID:number, cNum:number, dept:string, term:number, instructor:string
	1, 343, 'CSC', 20089, 'Horton'
	2, 343, 'CSC', 20089, 'Truta'
	3, 343, 'CSC', 20009, 'Heap'
	4, 320, 'CSC', 20089, 'Jepson'
	5, 207, 'CSC', 20089, 'Craig'
	6, 207, 'CSC', 20089, 'Gries'
	7, 148, 'CSC', 20089, 'Jepson'
	8, 148, 'CSC', 20089, 'Chechik'
	9, 263, 'CSC', 20089, 'Heap'
	11, 200, 'ANT', 20089, 'Zorich'
	12, 203, 'ANT', 20089, 'Davies'
	13, 263, 'EEB', 20089, 'Johancsik'
	14, 235, 'ENG', 20089, 'Percy'
	15, 205, 'ENG', 20089, 'Reisman'
	16, 110, 'ENG', 20089, 'Atwood'
	17, 320, 'ENV', 20089, 'Suzuki'
	18, 220, 'HIS', 20089, 'Young'
	19, 220, 'HIS', 20089, 'Dow'
}

Took = {
	sID:number, oID:number, grade:number
	99132, 1, 79
	99132, 16, 98
	99132, 31, 82
	99132, 11, 99
	99132, 14, 39
	99132, 15, 62
	99132, 34, 75
	98000, 11, 79
	
	98000, 11, 79
	98000, 1, 82
	98000, 5, 89
	98000, 6, 72
	98000, 7, 89
	98000, 8, 93
	98000, 13, 98
	98000, 16, 79
	98000, 17, 79
	98000, 22, 54
	98000, 27, 89
	98000, 31, 78
	98000, 38, 92
	98000, 39, 97
	98000, 9, 78
	
	99999, 11, 99
	99999, 1, 89
	99999, 5, 76
	99999, 6, 78
	99999, 7, 71
	99999, 8, 91
	99999, 13, 100
	99999, 16, 100
	99999, 17, 94
	99999, 22, 96
	99999, 27, 52
	99999, 31, 70
	
	00157, 1, 99
	00157, 2, 70
	00157, 14, 98
	00157, 31, 82
	00157, 21, 71
	00157, 11, 39
	00157, 34, 62
	00157, 35, 75
	00157, 3, 82
	00157, 5, 59
	00157, 6, 72
	00157, 7, 100
	00157, 28, 100
	00157, 13, 100
	00157, 26, 71
	00157, 17, 59
	
	11111, 17, 46
	11111, 14, 40
	11111, 15, 0
	11111, 16, 17
	11111, 34, 45
}

Week 5

Tutorial Notes

/*
Student (sID, surName, firstName, campus, email, cgpa)
Course (dept, cNum, name, breadth)
Offering (oID, dept, cNum, term, instructor)
Took (sID, oID, grade)

Offering [dept, cNum] ⊆ Course [dept, cNum]
Took [sID] ⊆ Student [sID]
Took [oID] ⊆ Offering [oID]
*/

-- Part 1
--------------------------------------------------

-- 1. Suppose a row occurs n times in table R and m times in table S.
-- Recall: SQL uses bag sematics. A bag is a multi-set, a set that potentially
--         contains multiple copies of the same tuple      
-- a) Using bag semantics, how many times will it occur in table 𝑅 ∪ 𝑆?
n + m
-- b) Using bag semantics, how many times will it occur in table 𝑅 ∩ 𝑆?
min(n, m)
-- c) Using bag semantics, how many times will it occur in table 𝑅 − 𝑆? 
max(n - m, 0)

-- 2. Use a set operation to find all terms when Jepson and Suzuki were both
-- teaching. Include every occurrence of a term from the result of both 
-- operands.

/*
Note: `INTERSECT` removes duplicates. `INTERSECT ALL` keeps duplicates.
*/

-- SOLUTION:
(SELECT Term FROM Offering WHERE instructor = 'Suzuki')
INTERSECT ALL
(SELECT Term FROM Offering WHERE instructor = 'Jepson');

-- OUTPUT:
term
-------
20089
20081
20081
(3 rows)

-- 3. Find the sID of students who have earned a grade of 85 or more in some
-- course, or who have passed a course taught by Atwood. Ensure that no
-- sID occurs twice in the result.

/* 
Note: UNION is a set operation so it removes duplicates.
*/

-- SOLUTION:
(SELECT sid FROM took WHERE grade >= 85)
UNION
(SELECT sid
FROM Took, Offering
WHERE Took.oid = Offering.oid AND instructor = 'Atwood' AND grade >= 50);

-- ALTERNATE SOLUTION:
SELECT DISTINCT sid
FROM Took, Offering
WHERE Took.oid = Offering.oid AND 
((instructor = 'Atwood' AND grade >= 50) OR grade >= 85);

-- OUTPUT:
	sid
-------
	 157
 98000
 99132
 99999
(4 rows)

-- 4. Find all terms when csc369 was not offered.
-- Recall: EXCEPT keyword acts like the minus operator in relational algebra

-- SOLUTION:
(SELECT term FROM Offering)
EXCEPT
(SELECT term FROM Offering WHERE dept = 'CSC' AND cNum = 369);

-- OUTPUT:
 term
-------
 20081
 20089
(2 rows)

Recording

CoursesDDL.txt

CoursesData.txt

Using PostgreSQL on Mathlab

PSQL Commands

Week 6

Tutorial Notes

/*
Student (sID, surName, firstName, campus, email, cgpa)
Course (dept, cNum, name, breadth)
Offering (oID, dept, cNum, term, instructor)
Took (sID, oID, grade)

Offering [dept, cNum] ⊆ Course [dept, cNum]
Took [sID] ⊆ Student [sID]
Took [oID] ⊆ Offering [oID]
*/

-- Part 1
--------------------------------------------------

/* CONTINUED FROM WEEK 5 */

-- 5. Make a table with two columns: oID and results. In the results column,
-- report either “high” (if that offering had an average grade of 80 or
-- higher), or “low” (if that offering had an average under 60). Offerings
-- with an average in between will not be included.
-- Hints: use a set operation. You can use the SELECT clause to put a literal 
-- value into a column. 
-- For example: SELECT ‘high’ as results ....

/*
RECALL:
In SQL, GROUP BY Clause is one of the tools to summarize or aggregate the data
series. After Grouping the data, you can filter the grouped record using HAVING
Clause. HAVING Clause returns the grouped records which match the given 
condition
*/

-- SOLUTION:
(SELECT oID, 'HIGH' AS results
FROM Took
GROUP BY oID
HAVING avg(grade) >=80)
UNION
(SELECT oID, 'LOW' as results
FROM Took
GROUP by oID
HAVING avg(grade) < 60);

-- OUTPUT:
 oid | results
-----+---------
  38 | high
  14 | low
   8 | high
   7 | high
   3 | high
  28 | high
  13 | high
  39 | high
  15 | low
   1 | high
(10 rows) 


-- Part 2
--------------------------------------------------

-- 1. Write a query to find the average grade, minimum grade, and maximum grade
-- for each offering.

-- Use aggregate funtions

-- SOLUTION:
SELECT oid, avg(grade), min(grade), max(grade)
FROM Took
GROUP BY oid;

-- OUTPUT:
 oid |          avg        | min | max
-----+---------------------+-----+-----
  31 | 78.0000000000000000 |  70 | 82
  34 | 60.6666666666666667 |  45 | 75
            . . . rows omitted
   8 | 92.0000000000000000 |  91 | 93
  11 | 79.0000000000000000 | 39  | 99
(23 rows)

-- 2. Which of these queries is legal?
-- a) (top-left)
SELECT surname, sid
FROM Student, Took
WHERE Student.sid = Took.sid
GROUP BY sid;

-- OUTPUT:
ERROR: column reference "sid" is ambiguous
LINE 1: SELECT surname, sid
                         ^
/* In line does sid refer to Student.sid or Took.sid? */

-- b) (bottom-left)
SELECT surname, Student.sid
FROM Student, Took
WHERE Student.sid = Took.sid
GROUP BY campus;

-- OUTPUT:
ERROR: column "student.surname" must appear in the GROUP BY 
clause or be used in an aggregate function

-- c) (top-right)
SELECT instructor, max(grade), count(Took.oid)
FROM Took, Offering
WHERE Took.oid = Offering.oid
GROUP BY instructor;

-- OUTPUT:
 instructor | max | count
------------+-----+-------
     Jepson | 89 | 5
       Heap | 82 | 1
           . . .
     Mendel | 75 | 3
(17 rows)

-- d) (right-bottom)
SELECT Course.dept, Course.cnum,count(oid), count(instructor)
FROM Course, Offering
WHERE Course.dept = Offering.dept and Course.cnum = Offering.cnum
GROUP BY Course.dept, Course.cnum
ORDER BY count(oid);

-- OUTPUT:
 dept | cnum | count | count
------+------+-------+-------
  ENV |  320 |     1 |     1
  CSC |  369 |     1 |     1
            . . .
  CSC |  343 |     5 |     5
(18 rows)

-- 3. Find the sid and minimum grade of each student with an average over 80.

-- SOLUTION:
SELECT SID, min(grade)
FROM Took
GROUP BY sID
HAVING AVG(grade) > 80;

-- OUTPUT:
   sid | min
-------+-----
 98000 | 54
 99999 | 52
(2 rows)

-- 4. Find the sid, surname, and average grade of each student, but keep the 
-- data only for those students who have taken at least 10 courses.

-- SOLUTION:
SELECT Student.sID, surname, avg(grade)
FROM Student, Took
WHERE Student.sID = Took.sID
GROUP BY Student.sID
HAVING count(grade) >= 10;

-- OUTPUT:
   sid |  surname   |         avg
-------+------------+---------------------
   157 | Lakemeyer  | 75.9333333333333333
 99999 | Ali        | 84.5833333333333333
 98000 | Fairgrieve | 83.2000000000000000
(3 rows)

-- 5. For each student who has passed at least 10 courses, report their sid
-- and average grade on the courses that they passed.

-- SOLUTION:
SELECT sid, AVG(grade)
FROM took
WHERE grade >= 50
GROUP BY sid
HAVING count(*) >= 10;

-- OUTPUT:
  sid  |         avg
-------+---------------------
 98000 | 83.2000000000000000
 99999 | 84.5833333333333333
   157 | 78.5714285714285714
(3 rows)

-- 6. For each student who has passed at least 10 courses, report their sid
-- and average grade on all of their courses.

-- SOLUTION:
CREATE VIEW Seniors AS
SELECT sid
FROM Took
WHERE grade >= 50
GROUP BY sid
HAVING count(*) >= 10;

SELECT Seniors.sid, AVG(grade)
FROM Seniors, Took
WHERE seniors.sid = Took.sid
GROUP BY Seniors.sid;

/*
We use a VIEW because we do want a filter applied (only passing grades count) 
when choosing which students to report on, but we don’t want that filter
applied when we compute their average grade. A single WHERE clause can't 
accomplish this.
*/

-- OUTPUT:
   sid |         avg
-------+---------------------
 98000 | 83.2000000000000000
 99999 | 84.5833333333333333
   157 | 75.9333333333333333
(3 rows)

-- 7. Which of these queries is legal?

-- a) (top-left)
SELECT dept
FROM Took, Offering
WHERE Took.oID = Offering.oID
GROUP BY dept
HAVING avg(grade) > 75;

-- OUTPUT:
 dept
------
 HIS
 CSC
 EEB
 ANT
(4 rows)

-- b) (top-right)
SELECT Took.oID, avg(grade)
FROM Took, Offering
WHERE Took.oID = Offering.oID
GROUP BY Took.oID
HAVING avg(grade) > 75;

-- OUTPUT:
 oid | avg
-----+---------------------
  31 | 78.0000000000000000
   3 | 82.0000000000000000
  28 | 91.0000000000000000
  13 | 95.6666666666666667
   9 | 78.0000000000000000
   7 | 83.0000000000000000
   1 | 87.2500000000000000
  38 | 92.0000000000000000
  39 | 97.0000000000000000
  11 | 79.0000000000000000
   8 | 92.0000000000000000
(11 rows)

-- c) (bottom-left)
SELECT Took.oID, dept, cNum,avg(grade)
FROM Took, Offering
WHERE Took.oID = Offering.oID
GROUP BY Took.oID
HAVING avg(grade) > 75;

-- OUTPUT:
ERROR: column "offering.dept" must appear in the GROUP BY
clause or be used in an aggregate function
LINE 1: SELECT Took.oID, dept, cNum, avg(grade)

/*
We cannot add `dept` & `cNum` into the SELECT clause, because according to SQL
standards, SELECT clause may contain columns mentions in the GROUP BY and
expression based on aggregate functions only.

This is because of ambiguity; the GROUP BY clause produces a single record
for each group of records. GROUP BY columns such as `Took.oID` contian the
same value for all for all records in the group and any aggregate funtion
produces the ssingle value for a group. Howeverver, `dept` & `cNum` may have
multiple values with a group - which value should SQL choose as a single value
to be related to the group? Here lies the problem of ambiguity.

Note that this problem of ambiguity is removed if we GROUP BY the primary 
key.
*/

-- d) (bottom-right)
SELECT oID, avg(grade)
FROM Took
GROUP BY sID
HAVING avg(grade) > 75;

-- OUTPUT:
ERROR: column "took.oid" must appearin the GROUP BY
clause or be used in anaggregate function
LINE 1: SELECT oID, avg(grade)


-- Week 6 
-- Part 1 (Same schema db as week 5)
--------------------------------------------------

-- 1. Which of these queries is legal?
Only a and c.
Problem with b: ERROR: syntax error at or near "distinct"

-- 2. Under what conditions could these two queries give different results?
-- If that is not possible, explain why

-- SOLUTION:
If there were two students on the same campus with the same surname, 
their surname and campus would be repeated in the result of the first query,
but not in the result of the second.

-- 3. For each student who has taken a course, report their sid and the number
-- of different departments they have taken a course in.

-- SOLUTION:
SELECT sid, count(distinct dept)
FROM Took JOIN Offering ON Took.oid = Offering.oid
GROUP BY sid;

Recording

Joins Animation

There are 3 types of "inner joins"
- Theta join
- Natural join
- EQUI join
Note: natural join is not recommend because it can cause a dangling tuple problem due to the implicit nature of the join. An explicit inner join is more favourable such as theta join

Clause Order

Side Notes

In SQL you cannot use aggregate functions in a WHERE clause or in a JOIN condition. However, a SELECT statement with aggregate functions in its select list often includes a WHERE clause that restricts the rows to which the aggregate is applied

In SQL, GROUP BY Clause is one of the tools to summarize or aggregate the data series. After Grouping the data, you can filter the grouped record using HAVING Clause. HAVING Clause returns the grouped records which match the given condition. You can also sort the grouped records using ORDER BY. ORDER BY used after GROUP BY on aggregated column.

Set Schema

-- in psql
set search_path to university

-- in pgadmin
SET search_path = university

Setting up PGAdmin

Week 7 (No Tutorials)

Good luck on your midterm! 🙂

Week 8

Tutorial Notes

/*
Student (sID, surName, firstName, campus, email, cgpa)
Course (dept, cNum, name, breadth)
Offering (oID, dept, cNum, term, instructor)
Took (sID, oID, grade)

Offering [dept, cNum] ⊆ Course [dept, cNum]
Took [sID] ⊆ Student [sID]
Took [oID] ⊆ Offering [oID]
*/

-- Part 2 Subqueries
--------------------------------------------------

/* CONTINUED FROM WEEK 6 */

-- 1. What does this query do? (Recall that the || operator concatenates
-- two strings 
SELECT sid, dept || cnum as course, grade
FROM Took,
	(SELECT * 
	 FROM Offering
   WHERE instructor = 'Horton') Hoffering
WHERE Took.oid = Hoffering.oid;

-- SOLUTION:
It finds the students who took an offering taught by Horton.
Note: subqueries are often used within a FROM clause. Hoffering is the
name of the resulting subquery relation

-- OUTPUT:
	 sid | course | grade
-------+--------+-------
 99132 | CSC343 | 79
 98000 | CSC343 | 82
 98000 | CSC263 | 78
 99999 | CSC343 | 89
   157 | CSC343 | 99
(5 rows)

-- 2. What does this query do?
SELECT sid, surname
FROM Student
WHERE cgpa >
	(SELECT cgpa
	 FROM Student
	 WHERE sid = 99999);

-- SOLUTION:
Question 2 demonstrates that subqueries can be used within the WHERE clause.
It finds information about students whose cGPA is higher than student 99999
Note: the the resulting relation for the subquery only has 1 tuple. 

-- OUTPUT:
	 sid | surname
-------+------------
 99132 | Marchmount
 98000 | Fairgrieve
   157 | Lakemeyer
(3 rows)

-- 3. What does this query do?
SELECT sid, dept || cnum AS course, grade
FROM Took JOIN Offering ON Took.oid = Offering.oid
WHERE
	grade >= 80 AND
	(cnum, dept) IN (
		SELECT cnum, dept
		FROM Took JOIN Offering ON Took.oid = Offering.oid
							JOIN Student ON Took.sid = Student.sid
		WHERE surname = 'Lakemeyer');

-- SOLUTION:
It finds information about students that got an 80 or higher in a course that
some Lakemeyer took. They did not have to take the course together.


-- 4.
-- (a) Suppose we have these relations: R(a, b) and S(b, c).
-- What does this query do?

SELECT a
FROM R
WHERE b in (SELECT b FROM S);

-- SOLUTION:
It files attribute 'a' values from R whose 'b' value occurs in S

-- (b) Can we express this query without using subqueries?

-- SOLUTION: yes, we might think to join R and S
SELECT a
FROM R, S
WHERE R.b = S.b

But the 2 queries are not always equivalent. If a tuple from R connects/joins
successfully with more than one tuple from S, this new query will yeild
duplicates that the original query from part (a) did not have.


-- 5. What does this query do?
SELECT instructor
FROM Offering Off1
WHERE NOT EXISTS (
	SELECT *
	FROM Offering
	WHERE
		oid <> Off1.oid AND
		instructor = Off1.instructor );

-- SOLUTION:
It finds instructors who have exactly one offering.

-- OUTPUT:
 instructor
------------
	Truta
	Heap
	Chechik
	Davies
	Johancsik
	Reisman
	Dow
	Arv
	Miller
	Mendel
	Richler
(11 rows)


-- 6. What does this query do?
SELECT DISTINCT oid
FROM Took
WHERE EXISTS (
	SELECT *
	FROM Took t, Offering o
	WHERE
		t.oid = o.oid AND
		t.oid <> Took.oid AND
		o.dept = 'CSC' AND
		took.sid = t.sid )
ORDER BY oid;

-- SOLUTION:
It finds course offerings that include a student who has taken something
else that is a CSC course.

-- Now let us write some queries!

-- 7. For each course, that is, each department and course number combination,
-- find the instructor who has taught the most offerings of it. If there are
-- ties, include them all.
-- Report the course (eg “csc343”), instructor and the number of offerings
-- of the course by that instructor.

-- (a) First, create a view called Counts to hold, for each course, and
-- each instructor who has taught it, their number of offerings.

-- SOLUTION:
CREATE VIEW Counts AS
SELECT (dept || cNum) AS course, instructor, count(*) AS count
FROM Offering
GROUP BY (dept || cNum), instructor;

-- (b) Now solve the problem. Do not use any joins. 
-- (This will force you to use a subquery.)

SELECT course, instructor
FROM Counts
WHERE (course, count) 
	IN (
		SELECT course, max(count)
		FROM Counts
		GROUP BY course
	);

DROP VIEW Counts;


-- 8. Use EXISTS to find the surname and email address of students who have
-- never taken a CSC course.

-- SOLUTION:
SELECT sid
FROM Student s
WHERE NOT EXISTS (
	SELECT *
	FROM Took t, Offering o
	WHERE t.oid = o.oid 
	AND o.dept = 'CSC'
	AND s.sid = t.sid
);


-- 9. Use EXISTS to find every instructor who has given a grade of 100.

--SOLUTION;
SELECT DISTINCT o.instructorFROM Offering o
WHERE EXISTS (
	SELECT *
	FROM Took t
	WHERE t.oid = o.oid
	AND grade = 100
);



-- 10. Let's say that a course has level “junior” if its cNum is between 100 
-- and 299 inclusive and has level “senior" if its cNum is between 300 and 499
-- inclusive. Report the average grade, across all departments and course 
-- offerings, for all junior courses and for all senior courses. 
-- Report your answer in a table that looks like this:
   level | levelavg
---------|-----------
  junior |
  senior |
-- Each average should be an average of the individual student grades, 
-- not an average of the course averages.
CREATE VIEW LevelAverages AS(
	SELECT 'junior' AS level, AVG(t.grade) AS grade
  FROM Took t, Offering o
	WHERE t.oid = o.oid
	AND o.cNum >= 100AND o.cNum < 300GROUP BY t.oid
)
UNION ALL(
	SELECT 'senior' AS level, AVG(t.grade) AS grade
	FROM Took t, Offering o
	WHERE t.oid = o.oidAND o.cNum >= 300AND o.cNum < 500GROUP BY t.oid
);
SELECT level, AVG(grade)

Recording

Week 9

Tutorial Notes

What was covered:

How to read ER Diagrams
- e.g. ternary relationships

How to identify entities and relationships when reading a requirements description

When to add a new entity set

When to add a descriptive attribute to a relationship set
- Do this usually when it's a Many-to-Many relationships. Examples:
https://www.geeksforgeeks.org/attributes-to-relationships-in-er-model/

When to create a ternary relationship vs connecting two binary relationships

Recording

Note about A2 Java Part

The JDBC can only connect to the UTSC Postgres server when ran on MATHLAB servers. Running on a UTSC lab computer or on your local computer will not work (only use UTSC lab and your local computers to compile your code).

See the following Piazza post for more detailed instructions: https://piazza.com/class/kodc8qliqpv3hg?cid=187

Week 10

Tutorial Notes

What was covered:

Strategies to design an ER diagram given business requirements/specifications

Strategies and patterns to convert an ER diagram to a relational model