Question
Problem 1. (70 points) Consider the relational database defined below: create table customer ( customer_ID varchar ( 1 0 ) , customer_name varchar ( 3
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started