CSC343 - Midterm3

Midterm Test — Solutions Summer 2019 Question 1. [7 marks] Part (a) [5 marks] Indicate whether each statement is True or False by circling the appropriate answer. TRUE FALSE All integrity constraints represent a foreign key constraint. TRUE FALSE Theta joins are natural joins that include a select operator on a condition. TRUE FALSE The assignment operator is not usually used to update the content of an existing relation. TRUE FALSE A natural join on two tables with no common attributes results in an empty relation. TRUE FALSE If a relational algebra query has a select operator followed by a project operator, you cannot always swap the positions of the operators to get the same resulting relation. Part (b) [2 marks] Briefly explain what is meant by ‘dangling’ tuples. Show an example with some small tables. Must explain dangling tuples and show example - see lecture slides. Page 1 of 8 Downloaded by Elyse Ando (elysekando@gmail.com) lOMoARcPSD|32012607

Midterm Test — Solutions Summer 2019 Recall this schema, which we have used many times in class. Relations Student(sID , surName, firstName, campus, email, cgpa) Course(dept, cNum , name, breadth) O ff ering(oID , dept, cNum, term, instructor) Took(sID, oID , grade) Integrity constraints O ff ering[dept, cNum] ⊆ Course[dept, cNum] Took[sID] ⊆ Student[sID] Took[oID] ⊆ O ff ering[oID] Question 2. [4 marks] Part (a) [2 marks] Consider this constraint: Proom ( cNum 1 , cNum 2 , term ) := Π O 1 .cNum,O 2 .cNum,O 1 .term σ O 1 .cNum<O 2 .cNum ∧ O 1 .dept = O 2 .dept =‘ CSC ∧ O 1 .instructor = O 2 .instructor ∧ O 1 .term = O 2 .term [( ρ O 1 O ff ering ) × ( ρ O 2 O ff ering )] σ P 1 .cNum 1= P 2 .cNum 1 ∧ P 1 .cNum 2= P 2 .cNum 2 ∧ P 1 .term = P 2 .term [( ρ P 1 Proom ) × ( ρ P 2 Proom )] = ∅ Define an instance of O ff ering that violates the constraint. Solution: oID dept cNum term instructor o1 CSC 343 termA Sina o2 CSC 443 termA Sina o3 CSC 343 termB Diane o4 CSC 443 termB Diane Part (b) [2 marks] Write the following constraint using relational algebra: If a student takes a course taught by Horton, they cannot take a course taught by Gries. Solution: Any solution that did something like the following: Find all students taking Horton Courses. Find all students taking Gries Courses. Intersection of the two above should be empty. Page 2 of 8 Downloaded by Elyse Ando (elysekando@gmail.com) lOMoARcPSD|32012607

Midterm Test — Solutions Summer 2019 Question 4. [8 marks] For this question, you will write SQL queries using a version of the Restaurants schema from Assignment 1. Relations Restaurant(name , owner, capacity, country) Patron(PID , name, birthday) Dish(DID , name, dietary) Reservation(RID , PID, rname, date) Order(RID, DID , number) Rating(PID, rname , rating, comment) Integrity constraints Reservation[PID] ⊆ Patron[PID] Reservation[rname] ⊆ Restaurant[name] Order[RID] ⊆ Reservation[RID] Order[DID] ⊆ Dish[DID] Rating[PID] ⊆ Patron[PID] Rating[rname] ⊆ Restaurant[name] Part (a) [3 marks] In our schema, users can leave comments on their restaurant rating, and the comment text can be null. Write a query in SQL to find, for each Patron who has made a rating for a restaurant (where the comment text is not null), their name and the number of restaurants they have made a comment on. Report the Patron’s name and the number of restaurants. Organize the output in non-increasing order by the number of restaurants. Solution: SELECT name, count (rname) FROM Patron, Rating WHERE Patron.pid = Rating.pid AND comment IS NOT NULL GROUP BY Patron.name ORDER BY count(rname) DESC; Part (b) [2 marks] Write a query in SQL that finds the names and owners of all restaurants that have a lower capacity than the restaurant named ‘Red Lobster’. Solution: SELECT name, owner FROM Restaurant WHERE capacity < (SELECT capacity FROM Restaurant WHERE name = 'Red Lobster'); Page 4 of 8 Downloaded by Elyse Ando (elysekando@gmail.com) lOMoARcPSD|32012607

Midterm Test — Solutions Summer 2019 The following query is supposed to print the number of pairs of dishes which have the same dietary restriction. It runs but does not always give the correct output. SELECT count(*) FROM ( SELECT D1.DID, D2.DID FROM Dish D1, Dish D2 WHERE D1.dietary = D2.dietary AND D1.DID <> D2.DID ) as DietaryPairs; Part (c) [1 mark] Suppose that Dish has these values. What will be the output of the query? DID | name | dietary ----+-----------------+---------- 1 | 'veggie burger' | 'veg' 2 | 'salad' | 'veg' 3 | 'lasagna' | 'normal' 4 | 'quinoa' | 'gf' 5 | 'pistachio' | 'gf' Solution: count ------- 4 Part (d) [1 mark] Generalizing to any dataset, explain what is wrong with the output of this query. Solution: It counts every pair twice. Part (e) [1 mark] Fix the query by making the smallest change that you can. Write your corrections directly on the query text above. Solution: One option is: add AND D1.DID < D2.DID (or change the <> to <) Another solution is to do count(*)/2 Page 5 of 8 Downloaded by Elyse Ando (elysekando@gmail.com) lOMoARcPSD|32012607

Midterm Test — Solutions Summer 2019 Here are the tables again, for easy reference: Follows: a | b ----------+---------------- sina | kanyewest sina | RonConwayFacts diane | LilaFontes diane | swcarpentry diane | mfeathers diane | sina michelle | sina michelle | diane michelle | Jeff (9 rows) Profile: id | name | location ----------+------------+---------- alan | catman | Ottawa sina | superman | diane | superwoman | Toronto michelle | rockstar | Montreal (4 rows) Tweets: id | userid | content -----+--------+---------------- 123 | alan | hellow twitter 125 | alan | bye twitter 126 | alan | hellow twitter 128 | alan | bye twitter 476 | sina | hellow twitter 553 | diane | hellow twitter (6 rows) SELECT P.id, count(T.content) AS number FROM Profile P JOIN Tweets t On T.userid = P.id AND P.location='Montreal'; -- Output: ERROR: column "p.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT P.id, count(T.content) AS number Select Tweets.content From Tweets Join Profile On Tweets.userid = Profile.ID And Profile.location IN (select location from profile where name='catman'); content ---------------- hellow twitter bye twitter hellow twitter bye twitter (4 rows) Page 7 of 8 Downloaded by Elyse Ando (elysekando@gmail.com) lOMoARcPSD|32012607

Midterm Test — Solutions Summer 2019 Page 8 of 8 End of Solutions Downloaded by Elyse Ando (elysekando@gmail.com) lOMoARcPSD|32012607