[Solved] CS6360.001 Assignment 2

Database Design

Use COMPANY database schema and specify following queries in relational algebra.

1. List the last names of other employees who have the same supervisor as Franklin Wong.

2. List the last names of other employees who work on some project along with Franklin Wong.

3. For each employee, list his/her ssn, last name and the number of dependents he/she has.

4. Find the last names of employees earning above average salary in their respective departments.

5. List the last names of employees who do not work on any project controlled by their respective departments.

Use LIBRARY database schema below and specify following queries in relational algebra.

6. Find all books (book titles) that are borrowed from Richardson library branch.

7. Find all books (book titles) that are overdue. (Books become overdue if the Due_date is a date in the past and Return_date is null)

8. Retrieve the names of all borrowers who have overdue books.

9. For each library branch (branch name), find total number of books that are overdue.

10. Retrieve the names of all borrowers who do not have any books checked out.

11. For each book that is checked out from the Richardson library branch and whose Due_date is today, retrieve the book title, the borrowers name, and the borrowers address.

12. For each library branch, retrieve the branch name and the total number of books loaned out from that branch.

13. Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out.

* Assume we add one additional attribute to BOOK_LOANS table with name Return_date. When a book is borrowed, Return_date takes initial value of NULL.

You can solve the questions on paper and send the pictures of your solutions. Make sure to include all pictures in one single file (word document or pdf file) and denote question numbers for your solutions.

All questions except question #6 are 8 points. Question #6 is 4 points.