The purpose of the following assignment is to practice writing queries using relational algebra and SQL. First, read Lab 4 notes before proceeding. Please use the relational algebra operators to write the queries, and then write the equivalent query in SQL. You will need to turn-in the lab, so either write your answers in text/word document, or write the solutions on paper and take a photo to upload to iLearn.
Assume that the following relations exist:
BOOKS(DocId, Title, Publisher, Year) STUDENTS(StId, StName, Major, Age) AUTHORS(AName, Address)
borrows(DocId, StId, Date) has-written(DocId, AName) describes(DocId, Keyword)
The following shows the entity relationship diagram. The key elds of the relations are underlined in the ER diagram.
1
Write the following queries in relational algebra and then the equivalent
SQL query:
List the year and title of each book
List all information about students whose major is CS
List all students with books they can borrow
List all books published by McGraw-Hill before 1990
List the name of those authors who are living in Davis
List the name of students who are older than 30 and who are not studying CS
Rename AName in the relation AUTHORS to Name
List the names of all students who have borrowed a book and who are CS majors
List the title of books written by the author \Jones"
As previous, but not books that have the keyword \database"
Find the name of the youngest student
Find the title of the oldest book
2
