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.
Answer to question 1
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;
Answer to question 2
Step 1
The answer as given below:
Answer to question 3
Importance of the relational data model:
The relational data model allows the mapping of all relevant data from each entity and creating an adequate abstraction on the situation and discarding any irrelevant data that is not important to be stored or used in our system. Let’s take an example of a parking ticket generation system, it is irrelevant if the user may be “a man or a woman”, here, this information is not required to be stored. Similarly, if an insurance records system is required the relational data then it is important to store the gender information of that client and associates to cover maternity policy and other different requirements that are needed for the insurance purpose.
Importance of the entity-relationship model:
To express the relationships between the entities involved in a situation then the entity-relationship model is required where the entity model will help, how the entities in the given process are related to each other such as a student from the library borrow more than 1 title which is another entity and that 1 title can be borrowed by several people because the library has more than one book of each title.