CMPE321 Spring 2018

question-1.md

BOUN CMPE321 Spring 2018 MT2 Questions

1st Question

In SQL and a language that you've learned in this course write the expressions for the names of publishers who published every book in Book relation.

BOOK(isbn*, title, ...)
PUBLISH(isbn*, publ_name*, year*)
PUBLISHER(publ_name*, location, ...)

SELECT p.name
FROM Publisher p
-- For a Publisher p
WHERE NOT EXISTS(
	SELECT *
	FROM Book b
	-- Check for for a Book b
	WHERE NOT EXISTS(
		-- If the Publisher p has published the Book b, then skip that book
		SELECT *
		FROM Publish pb
		WHERE pb.isbn = b.isbn
			AND pb.publ_name = p.publ_name
	)
	-- if all books are skipped (no books are returned), then it means 
	-- all books have been published by the Publisher p
	-- we should return p (with NOT EXISTS)
)

In Tuple Relational Calculus

{t^1 | (∃ px)(PUBLISHER(px) ∧ t[1] = px[PUBL_NAME]
               ∧ (∀ bx)(BOOK(bx) => ((∃ pbx) (PUBLISH(pbx)
                                               ∧ pbx[ISBN] = bx[ISBN]
                                               ∧ pbx[PUBL_NAME] = px[PUBL_NAME]))))}

student-teacher.md

STUDENT(sid*, name, gpa, class)
TEACHER(tid*, name, status)
COURSE(cid*, name, department)
STUDENT_COURSE(sid*, cid*, year)
TEACHER_COURSE(tid*, cid*, year)
(Keys are marked with *)

Question: Write in SQL and Tuple RC. The names and GPA's of all students who took all the courses offered by teacher Ali in the year 1999

SELECT s.name, s.gpa
FROM STUDENT s
WHERE NOT EXISTS(
	SELECT *
	FROM COURSE c, TEACHER_COURSE tc, TEACHER t
	WHERE c.tid = tc.tid
	AND t.tid = tc.tid
	AND t.name = 'Ali'
	AND tc.year = 1999

	AND NOT EXISTS(
		SELECT *
		FROM STUDENT_COURSE sc,
		WHERE sc.sid = s.sid
		AND sc.cid = c.cid
	)
)