RelaX - Online Relational Algebra Tool
RelaX is an online relational algebra tool that we will use for the assignments. It allows entering relational algebra expressions and executing them to get results. The data set can be loaded from a GitHub Gist including the Bookstore data set GIST: 367f41bb51110ef3c84bb5f906f2fb87 used for sample relational algebra queries in this lab, and the Shipment database GIST: 585ee1836abb142a461d137e12dd14a3 used in the lab questions.
Given the following relational schema, write queries in relational algebra to answer the English questions using the online relational algebra tool. The database definition is available as a Shipment database GIST: 585ee1836abb142a461d137e12dd14a3. The database is also available as an DDL file.
Customer(cid: integer, cname: string, address: string, city: string, state: string)
Product(pid: integer, pname: string, price: currency, inventory: integer)
ShippedProduct(sid: integer, pid: integer, amount: integer)
1. Return the product id and amount for each ShippedProduct where the amount was less than or equal to 5 or greater than 30. Expected output:
2. Return the shipment id, product id, shipped amount, product name, and inventory for products that have any past shipment amount more than the current inventory. Expected output:
4. Return the product name, current inventory, shipment amount, and customer state where the product had a shipment of an amount at least
30% of its current inventory and the customer state was either 'CA' or 'IA'. Expected output:
5. Return a list of products (id, name, price) where the product has not been in any shipments or the product price is greater than $5 and has been shipped to a customer in 'NJ'. Expected output:
Note: Order does not matter for the output.
