Thursday-solution_-1139956641

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 had a day with sales valued at over 20, but has neve with sales valued at over 50. 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. Π 𝑒𝑖𝑑 ( 𝜎 𝑎𝑚𝑜𝑢𝑛𝑡 650 ( 𝜎 𝑎𝑚𝑜𝑢𝑛𝑡 >20 𝑆𝑎𝑙𝑒𝑠 )) Invalid Valid but incorrect Correct Explanation below: Instance below: No explanation or instance required The inner sigma keeps only tuples with an amount over 20. It keeps entire tuples with all 3 attributes. Of these, the next sigma keeps the tuples that have an amount at most 50. The net effect of both sigmas is to keep tuples with an amount in the range (20, 50] . 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 (20, 50] . This does not guarantee that they’ve never had a day over 50! Any instance that has a person with a day in the range (20, 50] AND a day over 50 demonstrates the bug. The question also requires a non-empty expected result, so we must inc someone who has had a day over 20 but never had a day over 50. (NB: This explanation was NOT required on the test. It’s just for your learning.) Input instance: eid day amount 123 A 23 123 B 51 456 C 48 Expected result: eid 456 Actual result: eid 123 456

Fall 2023 Midterm CSC 343 H1F Duration: 90 min. Part (c) [8 marks] The university is interested in knowing who started teaching during covid and has broad teaching ex Write a query in relational algebra to find all instructors who meet these conditions: (a) their earliest (firs of any course was in term 20201, and (b) they have taught in at least two depts or they have taught a the dept is "JSC". You must name the attributes on the LHS of each assignment statement you use. Solution: – This instructor offered a course in this term. Offered ( , ) ∶= Π 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 𝑡𝑒𝑟 𝑚 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 , 𝑡𝑒𝑟 𝑚 Offering – This is not the first term when this instructor offered something. 𝑁 𝑜𝑡𝐹 𝑖𝑟 𝑠𝑡 ( , ) ∶= Π 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 𝑡𝑒𝑟 𝑚 𝑂 1. , 1. 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 𝑂 𝑡𝑒𝑟 𝑚 ( 𝜎 𝑂 1. = 2. 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 𝑂 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 ∧ 𝑂 1. > 2. 𝑡𝑒𝑟𝑚 𝑂 𝑡𝑒𝑟𝑚 ( 𝜌 𝑂 1 Offering × 𝜌 𝑂 1 Offering )) – This is the first term when this instructor offered something. 𝐹 𝑖𝑟 𝑠𝑡 ( , ) ∶= 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 𝑡𝑒𝑟 𝑚 Offered − 𝑁 𝑜𝑡𝐹 𝑖𝑟 𝑠𝑡 – The instructor had their earliest offering of any course in term 20201. 𝐽 𝑢𝑛𝑖𝑜𝑟 ( ) ∶= Π 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 𝜎 𝑡𝑒𝑟𝑚 =20201 𝐹 𝑖𝑟 𝑠𝑡 – This instructor taught in at least two departments. ℎ 𝑇𝑎𝑢𝑔 𝑡𝐼 𝑛𝑇𝑤𝑜 ( ) ∶= 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 𝜎 𝑂 1. = 2. ∧ 1. ≠ 2. 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 𝑂 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 𝑂 𝑑𝑒𝑝𝑡 𝑂 𝑑𝑒𝑝𝑡 ( 𝜌 𝑂 1 Offering × 𝜌 𝑂 1 Offering ) – This instructor has taught a JSC course. ℎ 𝑇𝑎𝑢𝑔 𝑡𝐽 𝑆𝐶 ( ) ∶= Π 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 𝜎 𝑑𝑒𝑝𝑡 = ′ 𝐽 𝑆𝐶 ′ Offering – This instructor meets all the criteria for inclusion in the answer. 𝐴𝑛𝑠𝑤𝑒𝑟 ( ) ∶= ∩ ( ℎ ∪ ℎ ) 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 𝐽 𝑢𝑛𝑖𝑜𝑟 𝑇𝑎𝑢𝑔 𝑡𝐼 𝑛𝑇𝑤𝑜 𝑇𝑎𝑢𝑔 𝑡𝐽 𝑆𝐶 There is a simpler way to find the junior instructors, that doesn’t require finding everyone’s firs term: – This instructor taught in 20201, and never before that. 𝐽 𝑢𝑛𝑖𝑜𝑟 ( ) ∶= (Π 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 𝜎 𝑡𝑒𝑟𝑚 =20201 Offering ) − (Π 𝑖𝑛𝑠𝑡𝑟 𝑢𝑐𝑡𝑜𝑟 𝜎 𝑡𝑒𝑟𝑚 <20201 Offering )

Fall 2023 Midterm CSC 343 H1F Duration: 90 min. Question 5. [6 marks] Below are four similar SQL queries on the same schema about sales from Question 3: -- Query 1: select manager as successful from Manages, Sales s1, Sales s2 where junior = s1.eid and manager = s2.eid; -- Query 2: select manager as successful from Manages, Sales where manager = eid and junior in (select eid from sales); -- Query 3: select manager as successful from Manages, Sales where junior = eid and manager in (select distinct eid from sales); -- Query 4: select manager as successful from Manages where junior in (select distinct eid from sales) and manager in (select distinct eid from sales); Consider a manager (let’s call her Sonia) who meets the following conditions: • She manages ≥ 1 𝑘 juniors. • 𝑗 ≥ 1 of her juniors have had sales (that is, appear in the Sales table). • In total, her juniors appear 𝑚 times in the Sales table. • Sonia herself appears ≥ 1 𝑛 times in the Sales table. For each question, circle one answer and provide the required additional information below. 1. Will Query 1 run successfully? Yes No If yes, Sonia will appear in the result this many times: 𝑚 × 𝑛 If no, this is why there is an error: 2. Will Query 2 run successfully? Yes No If yes, Sonia will appear in the result this many times: 𝑗 × 𝑛 If no, this is why there is an error: 3. Will Query 3 run successfully? Yes No If yes, Sonia will appear in the result this many times: 𝑚 If no, this is why there is an error: 4. Will Query 4 run successfully? Yes No If yes, Sonia will appear in the result this many times: 𝑗 If no, this is why there is an error: