Description
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