A Query Rosetta Stone: Natural Language, Microsoft Access QBE, SQL, and Relational Algebra

by Justin Watt
comments gratefully accepted: jwatt [at] email [dot] unc [dot] edu
Last Modified: 10/15/04

Query in Natural Language

One person can be in one or many photos and one photo can contain zero, one, or many people. List all the people who are in photos along with the filename and the description of the photo(s) they are in. People and photos may be listed multiple times, but a person will never be in the same photo twice (assuming the photos haven't been doctored).

Query in Microsoft Access Query Design View
also known as Query by Example (QBE)

Query in SQL generated by Microsoft Access

SELECT [tblPeople].[LastName], [tblPeople].[FirstName], [tblPhotos].[Filename], [tblPhotos].[Description] FROM (tblPeople INNER JOIN tblPeopleInPhoto ON [tblPeople].[PeopleID] = [tblPeopleInPhoto].[PeopleID]) INNER JOIN tblPhotos ON [tblPeopleInPhoto].[PhotoID] = [tblPhotos].[PhotoID]WHERE ((([tblPeople].[LastName]) Is Not Null) And (([tblPeople].[FirstName]) Is Not Null));

Query in SQL cleaned up for readability

SELECT	tblPeople.LastName,
	tblPeople.FirstName,
	tblPhotos.Filename,
	tblPhotos.Description
FROM	(tblPeople INNER JOIN tblPeopleInPhoto ON tblPeople.PeopleID = tblPeopleInPhoto.PeopleID)
	INNER JOIN tblPhotos ON tblPeopleInPhoto.PhotoID = tblPhotos.PhotoID
WHERE	(tblPeople.LastName Is Not Null) AND (tblPeople.FirstName Is Not Null);

Query in Relational Algebra (color-coded to match the SQL)

R1 ← tblPeople equijoin_{PeopleID=PeopleID} tblPeopleInPhoto	join tblPeople and tblPeopleInPhoto
R2 ← R1 equijoin_{PhotoID=PhotoID} tblPhotos	join the result of the first join, R1, with tblPhotos
R3 ← σ LastName != Null (R2)	select only those R2 records where LastName is not null
R4 ← σ FirstName != Null (R2)	select only those R2 records where FirstName is not null
R5 ← R3 ∩ R4	intersect (AND) R3 and R4
Result ← π LastName, FirstName, Filename, Description (R5)	project 4 fields (columns) in the final Result

Note:

If the characters above don't look like arrows, sigma's or pi's, try the PDF version.