=========================preview======================
(COMP231)final-spring05-solution.pdf
Back to COMP231 Login to download
======================================================
COMP231 - Spring 2005
FINAL EXAM
Name: SOLUTION Student ID Lecture Section: ITSC account:
All questions should be answered within the space provided after each problem.
Problem 1 C Relational Model (28%)
Suppose a university database has the following four tables (primary keys are underlined and foreign keys are in italics):
STUDENT (SID, SNAME, YEAR, MAJOR) PROFESSOR (PID, PNAME, DEPT) CLASS (CID, ROOM, TIME, PID) ENROLLED (SID, CID)
Write the following queries in SQL, relational algebra, and/or relational calculus as specified.
1] Find the distinct names of the students who have enrolled in at least two different classes.
1a] Algebra (3%)
SNAME (STUDENT JOIN STUDENT.SID = E1.SID (E1(ENROLLED) JOIN E1.SID = E2.SID AND C1.CID <> C2.CID E2(ENROLLED)))
1b] Calculus (3%)
{<SN> | . SI, Y, M (<SI,SN,Y,M>STUDENT AND . CI1 (<SI,CI1>ENROLLED AND . CI2 (<SI, CI2>ENROLLED AND CI1<>CI2)))}
1c] SQL (3%)
SELECT DISTINCT SNAME FROM STUDENT S, ENROLLED E1, ENROLLED E2 WHERE S.SID = E1.SID AND S.SID = E2.SID AND E1.CID<>E2.CID
2a] Algebra (3%)
PNAME, SID ( PROFESSOR JOINPROFESSOR.PID=CLASS.PID CLASS JOINCLASS.CID=ENROLLED.CID ENROLLED) / (SID ( (YEAR = 3 AND MAJOR = CPEG ) STUDENT))
2b] Calculus (3%)
{<PN> | . PI,D (<PI,PN,D >PROFESSOR AND . SI,SN,Y,M
(<SI,SN,Y,M>STUDENT AND Y= 3 AND M=CPEG .
. CI,R,T (<CI, R, T, PI>CLASS AND <SI, CI> ENROLLED)))}
2c] SQL (3%)
SELECT DISTINCT PNAME FROM PROFESSOR P WHERE NOT EXISTS
((SELECT S.SID
FROM STUDENT S
WHERE S.YEAR =3 AND S.MAJOR= CPEG)
EXCEPT
(SELECT E.SID
FROM ENROLLED E, CLASS C
WHERE P.PID = C.PID AND C.CID = E. CID))
3] Find the CIDs of the classes that take place in LTA but each has an enrollment less than 100 students.
SQL (5%)
SELECT CID FROM CLASS C, ENROLLED E WHERE C.CID=E.CID AND C.ROOM=LTA GROUP BY C.CID HAVING COUNT(E.SID) < 100
4] Find the distinct names of the COMP students who have taken the largest number of classes among all COMP students.
SQL (5%)
SELECT DISTINCT SNAME
FROM (SELECT .S.SID AS SID, S.SNAME AS SNAME, COUNT(E.CID) AS COUNTC FROM STUDENT S, ENROLLED E WHERE S.CID=E.CID AND S.MAJOR = COMP
GROUP BY S.SID, S.SNAME) AS TEMP WHERE TEMP.COUNTC = (SELECT MAX(COUNTC) FROM TEMP)
Problem 2 C Functional Dependencies C Canonical Forms (12%)
Consider a relation R(A,B,C,D,E) with the following set of functional dependencies:
ABC, AB, ACDB, EC.
1] What is (are) the candidate key(s) for R? (3%).
AE
2] Write a canonical cover for the above set of functional dependencies (3%).
ABCD, EC
3] Provide a decomposition of R in 3NF (3%)
R1(ABCD), R2(EC), R3(AE)
4] Provide a decomposition in BCNF C discuss briefly if you decomposition is dependency preserving (3%)
R1(ABCD), R2(AE) not dependency preserving - EC is lost
Problem 3 C External Sorting (8%)
Consider a file R with 100 pages.
1] What is the minimum number o