Exercise 1

Given the following relational model database schema:

PRODUCT (Product_Code, Product_Name, Stock, Unit_Price)

SUPPLIER (Supplier_Code, Supplier_Name, Address)

SUPPLY (Supplier _Code, Product_Code) (Supplier_Code) FK SUPPLIER

(Product_Code) FK PRODUCT

Solve the following queries using Relational Algebra.

1. List the codes of the products supplied by supplier with code 20. [2 marks]

2. List product data for the products with name iPhone 8 plus. [2 marks]

3. List product data for the products with price greater than $750. [2 marks]

4. List the address of the suppliers that supply products with name Galaxy 9. [3 marks]

5. List the product code and unit price for each product supplied by John Smith. [5 marks]

6. List the product code and unit price for each product supplied by John Smith with less

than 20 units in stock. [5 marks]

7. List the product code for products supplied by suppliers named John Smith or Jane

Doe. [6 marks]

8. List pairs of suppliers that both supply at least one product in common. [7 marks]

9. List all the information for suppliers who supply: [8 marks]

a. the product with code 20, the product with code 30 or both.

b. both the product with code 20 and the product with code 30.

c. the product with code 20 or the product with code 30 but not both.

d. the product with code 20 but they do not supply the product with code 30.

10. List the code and name of the suppliers that supply all the products. [10 marks]

Homework 3 Spring 2019 Page 1 of 2

CSCI 3287 Database System Concepts

Homework 3 Relational Algebra Due Date: 03/05/2019