The Relational Algebra and SQL

lab notes

Exercise 1  Assume the following relations:

• BOOKS(DocId, Title, Publisher, Year)

• STUDENTS(StId, StName, Major, Age)

• AUTHORS(AName, Address)

• borrows(DocId, StId, Date)

• has-written(DocId, AName)

• describes(DocId, Keyword)

Here it is the entity relationship diagram. The key fields of the relations are underlined in the entity relationship diagram.

Write the following queries in relational algebra.

• 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.