CS425 Database Organization - Assignment 1: Relational Algebra
Homework Assignment 1
Question 1.1.1
List the titles of all the books held by the bookstores in region ‘BS-IL’. (Hint: Books in inventory are considered as “held by the stores”)
Question 1.1.2
List the query result as “book title, store name, store region, quantity” where the book price is greater than 20. (Hint: only consider the books held by the stores)
Question 1.1.3
List the books (result: bid, title) held by the stores that can receive books from ‘NS-IL’ and quantity is less than 5. (Hint: only consider the stores directly receiving books from ‘NS-IL’)
Question 1.1.4
List the books (result: bid, title) which are held by all the stores in the ‘US’. (Hint: only national stores with region ‘US’, not including other regional stores in the US)
Question 1.1.5
List the books (result: bid, title) where the books’ quantity in ‘BS-US’ store is more than that in ‘NS-US’ store. (Hint: only count the books held by both ‘BS-US’ and ‘NS-US’)
Question 1.1.6
List the books (result: bid, title) only held by the stores with rating greater than 1.
Question 1.1.7
List all the ‘US’ stores that hold “Database” book (result: title, region). (Hint: only national stores with region ‘US’, not including other regional stores in the US)
Question 1.1.8
List the books and their total values (result: title, values) in all the stores’ inventory. (Hint: values = ((quantity + ordered) x price))
Question 1.1.9
List all the stores and the counts of the stores they directly receive books from (result: sid, count) in the database. (Hint: if a store does not get books from any store, return constant 0)
Question 1.1.10
List all the stores where the average price of the books held by the store is lowerthan 30 (result: sid, avg_price).
Question 1.1.11
List all the stores and the title(s) of the book(s) with the latest publication year in each store without using max in the query (result: sid, title).
Question 1.1.12
List the books (result: bid, title) which are held by region ‘NS-US’ stores and the stores that they can directly receive books from, as well as the stores that they can ship to.
