Translating English into Relational Algebra


















Translate the queries into relational algebra.

	SNAP	(StudentID-Name-Address-Phone)
	CSG	(Course-StudentID-Grade)
	CDH	(Course-Day-Hour)
	CR	(Course-Room)
	CP	(Course-Prerequisite)

1. List the prerequisites for EE200.

2. When does CS101 meet?

3. When and where does EE200 meet?


















Classwork

You may work with a partner.
Translate the queries into relational algebra.

	SNAP	(StudentID-Name-Address-Phone)
	CSG	(Course-StudentID-Grade)
	CDH	(Course-Day-Hour)
	CR	(Course-Room)
	CP	(Course-Prerequisite)

1. What days and hours is the 'Turing Auditorium' used?

2. List the names of students in 'Ohm Hall' on 'Tuesdays'.

3. List the addresses of students that have a prereq for CS206.


















SQL Queries

Write the queries in SQL.

1. project[P] select[C='EE200'] CP

2. project[D,H] select[C='CS101'] CDH

3. project[D,H,R] select[C='EE200'] (CDH |X| CR)


select P from CP where C='EE200'

select D, H from CDH where C='CS101'

select D, H, R from CDH natural join CR where CDH.C='EE200';

DEMO: sqlite3 < snoopy.sql


















Datalog Queries

Write the queries in Datalog.

1. project[P] select[C='EE200'] CP

2. project[D,H] select[C='CS101'] CDH

3. project[D,H,R] select[C='EE200'] (CDH |X| CR)


CP('EE200',P)?

CDH('CS101',D,H)?

CDHR(C,D,H,R) :- CDH(C,D,H), CR(C,R).
CDHR('EE200',D,H,R)?

DEMO: ./datalog snoopy.dlog