Problem 4 will refer to the following relational database that records information about foot races in the United States. We will refer to the database as the "foot races" database. For simplicity, we will assume all runners live in the United States. Primary key fields are underlined; foreign keys are indicated with keyword "references":
• relation runners with attributes (runner ID, name, street address, city, state, emer-gency contact)
• relation courses with attributes (course name, length, city, state)
• relation races with attributes (race name, date, course name), where (course name) references courses.
• relation results with attributes
(runner ID, race name, date, time) where (runner ID) references runners and (race name, date) references races.
Attribute time records the time it took the runner to complete the race.
The type of every attribute except length and time is CHAR(100); length and time are of type REAL.
Problem 4
Express the following queries with relational algebra expressions. You may use any relational algebra operations, including joins and division. Your solutions will be graded primarily on correctness. However, you may lose some points for a solution if it is inefficient - that is, if the solution uses more operations than necessary, especially if it is fairly obvious that some operations are unnecessary. DO NOT spend time trying to optimize your expressions, but do ask yourself if all the operations serve a purpose.
A. Find the name, length, and city of all courses in Hawaii that are at least 4 kilometers long. (Assume all course lengths are given in kilometers.)
B. Find the runner IDs of all runners who have run at least two races.
C. Find the IDs, cities, and states of all runners who have run a race in the city where they live. (Warning: there may be only one New York City, but there are a lot of Middletown's out there.)
D. Find the names of runners who have run all courses.