1. Suppose R has k tuples and S has l tuples. a. max: k+l, min: max(k,l) b. max: min(k,l), min: 0 c. max: k, min: 0 d. max: k*l, min: k*l e. max: k*l, min: 0 f. max: k/l, min: 0 g. max: k*l, min: 0 2. a. 1. take all tables listed in thing2, compute cross product of them 2. select rows of the cross product that satisfy the condition in thing3 3. group the selected rows by attribute thing4 4. select the groups that satisfy the condition in thing5 5. project out for the selected groups attributes in thing1 6. sort the result by attributes in thing6 Below we will use _ for subscript, and use {} for scoping. Table names start with uppercase letters; attribute names with lowercase ones. b. pi_{crsCode,semester} (Teaching join_{profId=id} sigma_{deptId='EE' OR deptId='MGT'}(Professor)) SELECT T.crsCode, T.semester FROM Teaching T, Professor P WHERE T.profId=P.id AND (P.deptId='EE' OR P.deptId='MGT') c. student-dept pairs where student took a course from a prof in dept: pi_{studId,deptId} (Transcript join Teaching join_{profId=id} Professor) CREATE VIEW StudDept(studId,deptId) AS SELECT R.studId, P.deptId FROM Transcript R, Teaching T, Professor P WHERE R.crsCode=T.crsCode AND R.semester=T.semester AND T.profId=P.id desired answer, similar as one in textbook: pi_name (Student join_{id=studId} sigma_{deptId<>deptId2} (StudDept join StudDept[studId,deptId2])) SELECT S.name FROM Student S, StudDept SD1, StudDept SD2 WHERE S.id=SD1.studId AND S.id=SD2.studId AND SD1.deptId<>SD2.deptId d. pi_{crsCode,studId} (sigma_{deptId='MGT'}(Course) join Transcript) / pi_{studId}(Student) SELECT C.crsCode FROM Course C WHERE C.deptId='MGT' AND NOT EXISTS (SELECT S.studId FROM Student S) EXCEPT (SELECT T.studId FROM Transcript T WHERE C.crsCode=T.crsCode) e. SELECT T.crsCode, MIN(S.age) FROM Student S, Transcript T WHERE S.id=T.studId AND NOT EXISTS (SELECT T.grade FROM Transcript T WHERE S.id=T.studId AND T.grade<>'A') GROUP BY T.crsCode for the second part, add at the end HAVING S.crsCode IN ('CSE305','MAT123') 3. a. values of attributes in X uniquely determines values of attributes in Y. b. a key is functional dependency X->Y such that (1) Y contains all attributes of the schema, and (2) there is no functional dependency X'->Y for any subset X' of X. c. (1) BC->B, by reflexivity (2) A->B, by transitivity using A->BC (given) plus BC->B (1) (3) A->AB, by augmentation using A->AB (2) there can be other ways. d. 1. {A,B,D, E,H,J,G} 2. No, because C is not in the set above. 3. Yes, because the attribute closure of AE is {A,E,D,H,J,G} and G is in it. e. 1. No. Since D is not uniquely determined by any other attributes, it must be part of all keys. 2. No. None of the left hand sides are superkeys. 3. Can choose any of the functional dependency as basis for decomposition. For example, R1=(ABDEFGH, {BE->GH, G->FA, F->B}), R2=(DC, {D->C}). In this case, R1 is not in BCNF, but R2 is. R1 is not 3NF, R2 is. 4. {ABDEFGH} intersect {CD} is {D}, which is a key of R2. 5. Yes, because each of the FDs of R is a FD of one of the subrelations.