downloadfile

.PDF

School

University of Toronto *

*We aren’t endorsed by this school

Course

343

Subject

Computer Science

Date

Jan 9, 2024

Type

PDF

Pages

Uploaded by ProfBuffalo3824

Fall 2023 Midterm CSC 343 H1F Duration: 90 min. Question 1. [11 marks] Part (a) [1 mark] Using bag semantics, what is {8, 4, 4, 2, 10, 10, 10, 1} − {1, 1, 4, 5, 10, 10}? {8, 4, 2, 10} Part (b) [4 marks] Consider this schema: Kitchen(dish, spoon, towel) Office(desk, chair) Kitchen[dish] ⊆ Office[desk] Suppose we know that relation Kitchen has 18 tuples. 1. What is the fewest tuples that Office could have? 18 2. What is the most tuples that Office could have? (write “no limit" if there is no limit) no limit Suppose we know instead that relation Office has 25 tuples. 3. What is the fewest tuples that Kitchen could have? 0 4. What is the most tuples that Kitchen could have? (write “no limit" if there is no limit) 25 Part (c) [3 marks] Consider this schema with two relations and no other constraints: R1(alpha, bravo, charlie ) R2(charlie, echo , foxtrot) Suppose we know that R2 has 5 tuples (but nothing about what’s in them), and that the contents of R1 a alpha bravo charlie 1 2 3 4 2 5 4 6 5 1. What is the most tuples possible from this query? ( 𝜎 𝑏𝑟𝑎𝑣𝑜 = echo ( 1× 2)) 𝑅 𝑅 3 (not 2, as in the original solutio 2. What is the most tuples possible from this query? (Π ℎ 𝑐 𝑎𝑟𝑙𝑖𝑒 𝑅 2) − (Π ℎ 𝑐 𝑎𝑟𝑙𝑖𝑒 𝑅 1) 5 3. What is the most rows possible from the SQL query below? 3 SELECT distinct bravo, charlie FROM R1, R2 WHERE alpha = foxtrot; Part (d) [3 marks] Suppose we have tables Pony with 10 rows, and Cow with 7 rows, and there are no NULL values in t Assume each of the queries below runs without error. This is all you know. For each of the queries below, what is the fewest possible rows it could yield? 1. SELECT * FROM Pony FULL JOIN Cow ON hoof = ear; 10 (not 17, as in the original solution set) 2. SELECT * FROM Pony NATURAL JOIN Cow; 0 3. SELECT * FROM Pony NATURAL RIGHT JOIN Cow; 7 Solution:

Fall 2023 Midterm CSC 343 H1F Duration: 90 min. This space is for any rough work you might like to do. It will not be marked.

Fall 2023 Midterm CSC 343 H1F Duration: 90 min. Question 2. [6 marks] Recall this schema which you saw in a prep exercise: Relations Employee(eid, name, salary, dept) Department(did, name, division) Sales(eid, day, amount) The employee with this eid had sales valued at this amount on this day. Manages(manager, junior) Employee“manager” manages employee “junior”. Integrity constraints Employee[dept] ⊆ Department[did] Sales[eid] ⊆ Employee[eid] Manages[manager] ⊆ Employee[eid] Manages[junior] ⊆ Employee[eid] We want to find the eid of every employee who has never had a day with sales valued at over 150 bu day with sales valued at over 75. For each query below circle one answer to indicate whether the query is • invalid (cannot be evaluated): in this case, explain why it is invalid. • valid (can be evaluated) but incorrect: in this case, give a valid instance of relation Sales that dem this, as well as the expected query result and the actual query result. The expected result must be non-empty, unless that is impossible. • or correct : in this case, no explanation or instance is required. 1. Π 𝑒𝑖𝑑 ( 𝜎 𝑎𝑚𝑜𝑢𝑛𝑡 >75 ∧ 𝑎𝑚𝑜𝑢𝑛𝑡 6150 𝑆𝑎𝑙𝑒𝑠 ) Invalid Valid but incorrect Correct Explanation below: Instance below: No explanation or instance required The sigma in this expression keep tuples with an amount in the range (75, 150] . The project keeps the eids from these rows, so we end up with the eid of anyone who had a day of sales valued in the rang (75, 150] . This does not guarantee that they’ve never had a day over 150! Any instance that has a person in the range (75, 150] AND a day over 150 demonstrates the bug. The question also requires a non-empty expected result, so we must include someone who has never had a day over 150 but has had a (NB: This explanation was NOT required on the test. It’s just for your learning.) Input instance: eid day amount 123 A 78 123 B 153 456 C 145 Expected result: eid 456 Actual result: eid 123 456

Fall 2023 Midterm CSC 343 H1F Duration: 90 min. 2. Π 𝑒𝑖𝑑 [( 𝜎 𝑎𝑚𝑜𝑢𝑛𝑡 >75 𝑆𝑎𝑙𝑒𝑠 ) − ( 𝜎 𝑎𝑚𝑜𝑢𝑛𝑡 >150 𝑆𝑎𝑙𝑒𝑠 )] Invalid Valid but incorrect Correct Explanation below: Instance below: No explanation or instance required This expression is partly correct: We need to use set difference to get rid of people who have had 150.However, it applies the set difference to entire tuples from Sales. So a single tuple with a day over 150 will remove itself from the result of the first select, but isn’t guaranteed to eliminate that person’s e (Trace the example below if you’re not sure about this.) Any instance that has someone who should result, that is someone who never had a day over 150 but did have a day over 75, but also had 150 will demonstrate the problem. (NB: This explanation was NOT required on the test. It’s just for your learning.) Input instance: eid day amount 123 A 78 123 B 153 456 C 145 Expected result: eid 456 Actual result: eid 123 456 3. Π 𝑒𝑖𝑑 [( 𝜎 𝑎𝑚𝑜𝑢𝑛𝑡 >75 𝑆𝑎𝑙𝑒𝑠 ) ∩ ( 𝜎 𝑎𝑚𝑜𝑢𝑛𝑡 6150 𝑆𝑎𝑙𝑒𝑠 )] Invalid Valid but incorrect Correct Explanation below: Instance below: No explanation or instance required This expression intersects rows that have an amount over 75 with rows that have an amount a A tuple will be in the result iff the amount is in the range (75, 150] . The project keeps the eids from these rows, so we end up with the eid of anyone who had a day of sales valued in the range (75, 150] . This does not guarantee that they’ve never had a day over 150! Again, any instance that has a person with a range (75, 150] AND a day over 150 demonstrates the bug. The question also requires a non-empty expected result, so we must include someone who has never had a day over 150 but has had a day over 7 (NB: This explanation was NOT required on the test. It’s just for your learning.) Input instance: eid day amount 123 A 78 123 B 153 456 C 145 Expected result: eid 456 Actual result: eid 123 456

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version