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.