# Consider the following three relations

Question 1

Consider the following three relations:

TRAVEL_AGENT (name, age, salary)

CUSTOMER (name, departure_city, destination, journey_class)

TRANSACTION (number, cust_name, travel_agent_name, amount_paid)

Write SQL statements to answer the following questions.

a. Compute the number of different customers who have a transaction.

b. Display the name of the oldest travel agent.

c. List the total number of transactions for each travel agent. Consider only those transactions where the amount paid exceeds 1 000.

d. Display the names and ages of the travel agents who have arranged journeys for customer “John Smith”, in descending order of age (use a subquery).

e. Display the names and ages of travel agents who have arranged journeys for customer “John Smith”, in descending order of age (do not use a subquery).

f. Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa” (use a subquery).

g. Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa” (do not use a subquery).

h. Display the names and salaries of all travel agents who did not arrange journeys for customer “John Smith”, in ascending order of salary.

i. Display the names of travel agents who have five or more transactions.

j. Display the names of all travel agents who have arranged at least ten journeys to “Ottawa”.

Question 2

Consider the following three simple rules to represent fuzzy relationship between food quality and
service in a restaurant as input variables and tip amount as output variables.
1) If the food is bad OR the service is poor, then the tip will be low
2) If the service is acceptable, then the tip will be medium
3) If the food is great OR the service is amazing, then the tip will be high.
Given the membership functions illustrated in Figure 1 and 2 as follows.

Using the clipped center of gravity method, compute the tip amount based on the following
circumstances:
1) Food quality was 6.0, and
2) Service was 8.0

Question 3

Describe the importance of the relational data model or entity-relationship model. Relational Data Model: The relational data model is a model where the data is expressed in the form of a table that contains or listed as a set of relations. It is having attributes and tuples for each row as well as for columns.

a. SELECT COUNT DISTINCT(Cust_name) FROM TRANSACTION;

b. SELECT MAX(age) FROM TRAVEL_AGENT;

c. SELECT number,Travel_agent FROM TRANSACTION WHERE amount_paid>1000;

d. SELECT name,age FROM TRAVEL_AGENT WHERE name=(SELECT travel_agent_name from TRANSACTION WHERE cust_name=”john smith”) ORDER BY age DESC;

e. SELECTa.name,a.age FROM TRAVEL-AGENT .a , TRANSACTION .b WHERE a.name=b.travel_agent_name AND b.cust_name=”john smith”ORDER BY a.age DESC;

f. SELECT age FROM TRAVEL_AGENT WHERE name= (SELECT travel_agent_name FROM TRANSACTION WHERE cust_name=(SELECT name FROM CUSTOMER WHERE destination=”ottawa” AND name=”john smith”));

g. SELECT a. age FROM TRAVEL-AGENT .a , TRANSACTION .b,CUSTOMER.c WHERE a.name=b.travel_agent_name AND b. cust_name=c.name AND c.name=”john smith”AND c.destination=”ottawa”;

h. SELECT name,salary FROM TRAVEL_AGENT WHERE name=(SELECT travel_agent_name FROM TRANSACTIONS WHERE cust_name != “john smith”)

ORDER BY salary ASC;

i. SELECT transaction, travel_agent _name FROM TRANSACTION GROUP BY travel_agent_name HAVING COUNT (travel_agent_name)>=5

j. SELECT travel_agent _name FROM TRANSACTION WHERE cust_name=(SELECT COUNT ( name), destination FROM CUSTOMER WHERE destination=”ottawa”)

GROUP BY travel_agent _name HAVING COUNT (name)>10;

Step 1