cis4301 Homework #4 Solution

CIS 4301 - Spring 2025
Submission Format
You will submit a soft copy of your solution using e-Learning (http://elearning.ufl.edu) by the end of the day (23:59 / 11:59 PM) on the assigned date (March 14th). Save your solution as a PDF file and name it hw4.pdf.
Description
Students must use the database they created in Homework 3, including all relations and inserted data. In this homework, students must create the new relations listed in the schema below and populate them with data.
Step 1: Start MariaDB (0 pts)
Ensure MariaDB is running on your system before executing the SQL queries.
Step 2: Create New Relations and Insert Data (0 pts)
Using the schema below, create and populate the new relations:
• GoesOn(ssn: integer, id: integer)

• Leg(Trip id: integer, startLocation: String, endLocation: String,

• Owns(ssn: integer, passport number: integer, country: String)


holderName: String)
Populate the relations with sample data:
-- GoesOn
(101, 201),
(101, 208),
(102, 202),
(102, 205),
(103, 203),
(103, 206),
(106, 206),
(106, 201),
(107, 207),
(108, 208);
-- Leg
(201, 'New York', 'London', '2025-07-10', '2025-07-12'),
(201, 'London', 'Paris', '2025-07-13', '2025-07-20'), (202, 'Tokyo', 'Seoul', '2025-08-01', '2025-08-05'),
(202, 'Seoul', 'Sydney', '2025-08-06', '2025-08-15'),
(203, 'London', 'Berlin', '2025-09-05', '2025-09-08'),
(203, 'Berlin', 'Rome', '2025-09-09', '2025-09-15'),
(204, 'Miami', 'Atlanta', '2025-06-15', '2025-06-17'),
(204, 'Atlanta', 'New York', '2025-06-18', '2025-06-20'),
(205, 'San Francisco', 'Frankfurt', '2025-10-01', '2025-10-06'),
(205, 'Frankfurt', 'Berlin', '2025-10-07', '2025-10-10'),
(206, 'Chicago', 'Denver', '2025-09-10', '2025-09-11'),
(206, 'Denver', 'Los Angeles', '2025-09-12', '2025-09-12'),
(207, 'Boston', 'Dubai', '2025-07-05', '2025-07-10'),
(208, 'New York', 'Istanbul', '2025-09-10', '2025-09-12'),
(208, 'Istanbul', 'Dubai', '2025-09-13', '2025-09-15');
-- Owns
(101, 5001, 'USA'),
(102, 5002, 'Canada'),
(103, 5003, 'UK'),
(104, 5004, 'Germany'),
(105, 5005, 'France'),
(106, 5006, 'Australia'),
(107, 5007, 'Japan'),
(108, 5008, 'India');
-- Passport
(5001, 'USA', '2030-01-01', 'David Harris'),
(5002, 'Canada', '2027-06-15', 'Sarah Connor'),
(5003, 'UK', '2026-12-30', 'Mike Johnson'),
(5004, 'Germany', '2028-05-10', 'Laura White'), (5005, 'France', '2029-11-20', 'James Miller'),
(5006, 'Australia', '2027-03-25', 'Emma Watson'),
(5007, 'Japan', '2031-09-10', 'Chris Evans'),
(5008, 'India', '2025-07-05', 'Sophia Brown');
Step 3: SQL Query Exercises (100 pts)
Students must solve the following SQL exercises:
3. [10 Points] Retrieve the names of travel agents who have more experience than any agent whose name starts with ’John’.
4. [10 Points] Find the SSNs of travelers who both own a passport and have gone on multiple trips. Use INTERSECT to solve this question.
5. [10 Points] Find the start and end locations of trips booked by each travel agent. You must use natural join in your query.
6. [10 Points] Find the names of travel agents who have fewer years of experience than any agent with more than 10 years of experience.
8. [10 Points] List all travel agents and the number of trips they have booked. Only show agents who have booked more than 1 trip. Use GROUP BY and HAVING to
solve this question.
9. [10 Points] List the travelers who have not gone on any trips.
10. [10 Points] Find all the trips booked by travel agents with fewer than 5 years of
experience.
What to Submit
For this assignment, submit a single PDF file named hw4.pdf containing:
• Step 3: SQL Query Exercises. For each question, submit the following:
– The SQL query in text format.
– A screenshot of the SQL query in MariaDB.
– A screenshot of the query output.