Problem 1. (70 points) Consider the relational database defined below: create ta

Question

Problem 1. (70 points) 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 ) ;

Questions:

(e) (10 points) Find the names of the products that have been purchased by every customer whose ZIP code is 12222.

(f) (10 points) Find the most selling product(s). Their total purchase quantity must be no less than the total purchase quantity of any other product. 1

(g) (10 points) Write a check condition to ensure that, in the case of product ’X’, each person whose ZIP code is 12222 can purchase only up to 10 items at a time.

Explanation / Answer

Answer)

(e)

select product_name from

purchase where customer_ID in (

select customer_ID from customer where ZIP_code ='12222'

);

(f)

select product_name, sum(purchase.quantity) from

product inner join purchase on

product.product_name =

purchase.product_name group by product_name having sum(purchase.quantity) =

(select max(a.total) from (select sum(quantity) as total from pruchase group by product_name) as a);

(g)

alter table purchase

ADD CONSTRAINT CHK_Purchase CHECK (product_name='X' and quantity<=10 and customer_ID=(select customerID from customer where ZIP_code ='12222'));

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.