00:13 CS353 Spring 2019 Homework 2 Due: 6 March, Wednesday till 17:00 Q.1 (36 points) Consider the university database schema below STUDENT(s id, s name, address, status) PROFESSOR(p_id. p_name, deptid) COURSE(dept id, c code, c name, descr) TRANSCRIPT(-id. c-code. semester, year, grade) TEACHING(p id, c code, section, semester, year) Relational Algebra: Write the following queries in a) Find the id's of professors who work for the CS or EE departments. (i) with set operations b) c) d) e) Find the student id's and names of students who took all courses that were taught by all (ii) without set operations Find the id's of professors who have a joint appointment in CS and EE departments (work for both departments). Find the names of professors who have a joint appointment in CS and EE departments (work for both departments). Find the id's and names of CS professors or those who teach a CS course (use course code for string matching). ) with set operations, (ii) without set operations. computer science (CS) professors. Find the id's of professors who teach the maximum number of courses (different sections) in Spring 2019 Find the pairs of professor id's who teach the same course in Spring 2019, outputting each pair only once. g) h) Find the id's of professors, together with their names, who teach only one section ofa course in Spring 2019. Find the id's of courses that are taught by at least two different professors in Spring 2019 (without aggregate operators). Q.2 (20 points) Given two relations RI and R2, where RI contains NI tuples, and R2 contains N2 tuples, N2> NI>0, give the minimum and maximum number of tuples for the resulting relations produced by each of the following Relational Algebra expressions. Assume RI has attribute s and R2 has attributes s and t. In each case, state any assumptions about the schemas for R1 and R2 to make the expression meaningful (I)RI UR2; (2) RI n R2: (3)RI R2:(4) R2-R1: (5) RI x R2 Q.3 (14 points) Prove that, if R and S are union compatible, r s s where r and s are instances of the relations R and S, respectively. Assume tat R and S have attributes with the same names rl Q.4 (14 points) a) Division is not an essential operator in Relational Algebra Write an expression that corresponds to division using the basic Relational Algebra operations of cartesian product, set difference, and projection. Show the result of the division operations A/B1, A/B2. and AB3 for the following relations: b) B B3 00:14O e) Find the student id's and names of students who took all courses that were taught by all computer science (CS) professors Find the id's of professors who teach the maximum number of courses (different sections,) in Spring 2019 g) Find the pairs of professor id's who teach the same course in Spring 2019, outputting cch pair only once. Find the id's of professors, together with their names, who teach only one section of a course in Spring 2019 Find the id's of courses that are taught by at least two different professors in Spring 2019 h) without aggregate operators). Q.2 (20 points) Given two relations RI and R2, where RI contains NI tuples, and R2 contains N2 tuples, N2> NI 0, give the minimum and maximum number of tuples for the resulting relations produced by each of the following Relational Algebra expressions. Assume R1 has attribute s and R2 has attributes s and t. In each case, state any assumptions about the schemas for R1 and R2 to make the expression meaningful. (I)RI UR2: (2) Rl nR2: (3) R R2: (4) R2-R1: (5) RI x R2; Q.3 (14 points) Prove that, if R and S are union compatible, r n s = r p. s where r and s are instances of the relations R and S, respectively. Assume that R and S have attributes with the same names rl Q4 (14 points) a) Division is not an essential operator in Relational Algebra Write an expression that corresponds to division using the basic Relational Algebra operations of cartesian product, set difference, and projection. b) Show the result of the division operations A/B1, A/B2, and A/B3 for the following relations: B1 B2 B3 s2 s2 s3 0.5 (16 points) State the English meaning of the following Relational Algebra Expressions (some of them may yield an empty result): a) .code" semester, year(TRANSCRIPT)/_code (TRANSCRIPT)) b) ce year (TRANSCRIPT) Ty(TRANSCRIPT) Da COURSE) c)_cok, uid (TRANSCRIPT)/ 'ul STUDENT) d)cule, saneil", year, ud (TRANSCRIPT)/ T,Jd (STUDENT) 00:13 CS353 Spring 2019 Homework 2 Due: 6 March, Wednesday till 17:00 Q.1 (36 points) Consider the university database schema below STUDENT(s id, s name, address, status) PROFESSOR(p_id. p_name, deptid) COURSE(dept id, c code, c name, descr) TRANSCRIPT(-id. c-code. semester, year, grade) TEACHING(p id, c code, section, semester, year) Relational Algebra: Write the following queries in a) Find the id's of professors who work for the CS or EE departments. (i) with set operations b) c) d) e) Find the student id's and names of students who took all courses that were taught by all (ii) without set operations Find the id's of professors who have a joint appointment in CS and EE departments (work for both departments). Find the names of professors who have a joint appointment in CS and EE departments (work for both departments). Find the id's and names of CS professors or those who teach a CS course (use course code for string matching). ) with set operations, (ii) without set operations. computer science (CS) professors. Find the id's of professors who teach the maximum number of courses (different sections) in Spring 2019 Find the pairs of professor id's who teach the same course in Spring 2019, outputting each pair only once. g) h) Find the id's of professors, together with their names, who teach only one section ofa course in Spring 2019. Find the id's of courses that are taught by at least two different professors in Spring 2019 (without aggregate operators). Q.2 (20 points) Given two relations RI and R2, where RI contains NI tuples, and R2 contains N2 tuples, N2> NI>0, give the minimum and maximum number of tuples for the resulting relations produced by each of the following Relational Algebra expressions. Assume RI has attribute s and R2 has attributes s and t. In each case, state any assumptions about the schemas for R1 and R2 to make the expression meaningful (I)RI UR2; (2) RI n R2: (3)RI R2:(4) R2-R1: (5) RI x R2 Q.3 (14 points) Prove that, if R and S are union compatible, r s s where r and s are instances of the relations R and S, respectively. Assume tat R and S have attributes with the same names rl Q.4 (14 points) a) Division is not an essential operator in Relational Algebra Write an expression that corresponds to division using the basic Relational Algebra operations of cartesian product, set difference, and projection. Show the result of the division operations A/B1, A/B2. and AB3 for the following relations: b) B B3 00:14O e) Find the student id's and names of students who took all courses that were taught by all computer science (CS) professors Find the id's of professors who teach the maximum number of courses (different sections,) in Spring 2019 g) Find the pairs of professor id's who teach the same course in Spring 2019, outputting cch pair only once. Find the id's of professors, together with their names, who teach only one section of a course in Spring 2019 Find the id's of courses that are taught by at least two different professors in Spring 2019 h) without aggregate operators). Q.2 (20 points) Given two relations RI and R2, where RI contains NI tuples, and R2 contains N2 tuples, N2> NI 0, give the minimum and maximum number of tuples for the resulting relations produced by each of the following Relational Algebra expressions. Assume R1 has attribute s and R2 has attributes s and t. In each case, state any assumptions about the schemas for R1 and R2 to make the expression meaningful. (I)RI UR2: (2) Rl nR2: (3) R R2: (4) R2-R1: (5) RI x R2; Q.3 (14 points) Prove that, if R and S are union compatible, r n s = r p. s where r and s are instances of the relations R and S, respectively. Assume that R and S have attributes with the same names rl Q4 (14 points) a) Division is not an essential operator in Relational Algebra Write an expression that corresponds to division using the basic Relational Algebra operations of cartesian product, set difference, and projection. b) Show the result of the division operations A/B1, A/B2, and A/B3 for the following relations: B1 B2 B3 s2 s2 s3 0.5 (16 points) State the English meaning of the following Relational Algebra Expressions (some of them may yield an empty result): a) .code" semester, year(TRANSCRIPT)/_code (TRANSCRIPT)) b) ce year (TRANSCRIPT) Ty(TRANSCRIPT) Da COURSE) c)_cok, uid (TRANSCRIPT)/ 'ul STUDENT) d)cule, saneil", year, ud (TRANSCRIPT)/ T,Jd (STUDENT)