Exercise 7.1

Do the specified lab 6 review exercises.

Exercise 7.1.1

Distinguish between these two queries:

SELECT t.name, t.mandate, pt.personId
FROM Team t LEFT OUTER JOIN PersonTeam pt
            ON pt.teamName = t.name AND pt.role = 'chair';

SELECT t.name, t.mandate, pt.personId "ChairID"
FROM Team t LEFT OUTER JOIN PersonTeam pt
            ON pt.teamName = t.name
WHERE pt.role = 'chair';

Exercise 7.1.2

This query gets all households with 2 or more members. How would we get all households, regardless of whether they have members?

SELECT h.id, h.phonenumber, COUNT(1) "Size"
FROM Household h JOIN Person p ON p.householdId = h.id
WHERE h.city = 'Grand Rapids'
GROUP BY h.id, h.phonenumber
HAVING COUNT(1) >= 2
ORDER BY COUNT(1) DESC
;

Exercise 7.2

Create appropriate views for the following types of users.

  1. The Admissions Department would like a list of CS courses along with their descriptions. Include (only) the ID, department, code and name of all courses. Call this view CSView.

  2. The Dean’s office would like a list of all course sections. Include the section ID, course department, code, name and the section letter, semester and professor. Call this view SectionView.

Exercise 7.2.1

Given these views, explain what the following queries/updates do.

SELECT * FROM CSView WHERE name like '%Data%';
SELECT * FROM SectionView WHERE department='CS';
INSERT INTO CSView VALUES (12, 'CS', '324', 'Divide');
INSERT INTO SectionView
    VALUES (13, 'CS', '324', 'A', 'Divide', 'FA', '2015', 'VL');
DELETE FROM CSView  WHERE id = 12;
DELETE FROM SectionView WHERE id = 8;
UPDATE SectionView SET name='DBMS'
WHERE id = 11 AND department='CS' AND code='342' AND letter='A';

Exercise 7.2.2

Can you build a materialized version of either of the previous two views? If not, why not? If so, when would it make sense to do so?

Exercise 7.3

Write relational algebra queries for the GRDB that retrieve:

  1. the full name of all female students.

  2. the name and email addresses for all students with an A in some course.

  3. the IDs of all the mentors and/or mentees.

  4. the cross product of the course and section relations.

Exercise 7.4

Write the queries specified in the previous exercise using the tuple relational calculus.

Which form, the algebra or the calculus, looks most like SQL?