Consider the relational database defined below: create table customer ( customer
ID: 3755718 • Letter: C
Question
Consider the relational database defined below:
create table customer ( customer_ID varchar ( 1 0 ) ,
customer_name varchar ( 3 0 ) ,
ZIP_code char ( 5 ) , primary key ( customer_ID ) ) ;
create table product ( product_name varchar ( 1 0 ) ,
price numeric ( 1 0 , 2 ) , primary key ( product_name ) ) ;
create table purchase ( purchase_ID varchar ( 1 0 ) ,
customer_ID varchar ( 1 0 ) ,
product_name varchar ( 1 0 ) ,
quantity int , primary key ( purchase_ID ) ,
foreign key ( customer_ID ) references customer ,
foreign key ( product_name ) references product ) ;
Express in relational algebra each of the following queries:
(a) (10 points) For each ZIP code stored in the customer table, find the number of customers who have purchased product ’X’.
(b) (10 points) For each product whose price is less than $10,000, increase the price of that product by 10 percent. For all other products, increase their prices by 5 percent.
(c) (10 points) Find the names of the products that have not been purchased by anyone whose ZIP code is 12222.
Explanation / Answer
a) SELECT count(*) FROM customer c, purchase p WHERE c.customer_ID = p.customer_ID AND p.product_name = 'X' GROUP BY c.ZIP_code;
b) UPDATE product SET price = 1.1*price WHERE price < 10000;
UPDATE product SET price = 1.05*price WHERE price >= 10000;
c) SELECT p.product_name FROM product WHERE p.customer_ID NOT IN (SELECT customer_ID FROM customer WHERE ZIP_code = 12222);