relational algebra soln

# relational algebra soln - WHERE l.cid=c.cid AND(l.year=2000...

This preview shows pages 1–2. Sign up to view the full content.

Study Problems on Relational Algebra Understand each of the following SQL query, and then translate it into relational algebra. Database schema: Courses (cid, name) Staff (sid, firstname, lastname) Lecturing (sid, cid, year) (1). SELECT c.name FROM courses c, staff s, lecturing l WHERE l.sid=s.sid AND l.cid=c.cid AND (l.year=1999 OR l.year=2000) AND s.lastname='Jung'; Answer: Find the names of courses that are taught by staff with last name “Jung” in year 1999 or 2000. π name ( σ (lecturing.sid=staff.sid) (lecturing.cid = course.cid) (year = 1999 year = 2000) (lastname = Jung) (lecturing staff course)) or π name ( σ (year = 1999 year = 2000) lecturing   σ lastname = Jung staff   course) The second form is much more efficient! (2). SELECT c.name FROM lecturing l, courses c WHERE l.cid=c.cid and l.year=2001 EXCEPT SELECT c.name FROM lecturing l, courses c

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: WHERE l.cid=c.cid AND (l.year=2000 OR l.year=1999); Answer: Find the names of courses that are taught in 2001 but not taught in 2000 or 1999. π name ( σ year = 2001 (lecturing ⋈ courses)) - π name ( σ (year = 1999 ∨ year = 2000) (lecturing ⋈ course)) (3). SELECT c.name FROM lecturing l1, lecturing l2, courses c WHERE l1.cid=l2.cid AND l1.cid=c.cid AND l1.sid=l2.sid AND l1.year=1999 AND l2.year=2000; Answer: Find the names of the courses that are taught by the same staff in both 1999 and 2000 π name ( σ year = 1999 ∧ year2 = 2000 (lecturing ⋈ courses ⋈ ρ year -> year2 lecturing) (4). SELECT s.lastname FROM staff s WHERE s.sid NOT IN (SELECT l.sid FROM lecturing l); Answer: Find the last names of the staff that do not teach π lastname (( π sid staff - π sid lecturing) ⋈ staff)...
View Full Document

### Page1 / 2

relational algebra soln - WHERE l.cid=c.cid AND(l.year=2000...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online