tabase system that satisfies the requirements listed in Section 3.3 and that contains the sample of data listed in Section 3.4. SQL for the queries listed in Section 3.2 Mountain Rescue Teams   Climbing mountains is a popular leisure activity in Great Britain that is enjoyed by a very large number of people every year. However this hobby can be dangerous, especially when participants have not prepared adequately. Every year some people have to be rescued while on mountains in Great Britain. These rescues are conducted by teams of volunteers affiliated to the Mountaineering Trust. The teams search on foot. They navigate using maps prepared by Ordnance Survey, the official map-making body of the British government. Ordnance Survey maps have grids that allow

Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
Question
100%

Design, implement and document:  

  1. a database system that satisfies the requirements listed in Section 3.3 and that contains the sample of data listed in Section 3.4.
  2. SQL for the queries listed in Section

3.2 Mountain Rescue Teams  

Climbing mountains is a popular leisure activity in Great Britain that is enjoyed by a very large number of people every year. However this hobby can be dangerous, especially when participants have not prepared adequately. Every year some people have to be rescued while on mountains in Great Britain. These rescues are conducted by teams of volunteers affiliated to the Mountaineering Trust. The teams search on foot. They navigate using maps prepared by Ordnance Survey, the official map-making body of the British government. Ordnance Survey maps have grids that allow anywhere in Great Britain to be located to the nearest 100 meters using a grid reference. If necessary, the teams request assistance from the Royal Air Force (RAF), which provides search and rescue helicopters from its bases across Great Britain. 

 

3.3 The Database 

 The location, name, and height of every mountain is stored. The location is taken to be the grid reference of the summit and serves as a unique identifier for each mountain. A grid reference is a string of eleven characters. The height of each mountain is the height (to the nearest meter) of the summit above sea level. When known, the approximate shape of the summit of the mountain is also stored. Each mountain rescue team is named after the town in which it is based and has a code that starts with the string “MRT”, followed by a unique two digit integer. Each member of a mountain rescue team has a identifier that starts with the string “MEM”, followed by a unique four digit integer. Most members have a nickname. Each member has one (and only one) role. As teams may have to attend an emergency at any time, on any day, all members are permanently on-call. Therefore, a contact telephone number must be stored for each member. 

 

As more than one mountain rescue team may be conducting a rescue at any one time, a member cannot belong to more than one team; this avoids the possibility that a member is required to be in two different places at the same time. Each mountain has one or more paths that lead to its summit. (For the sake of simplicity, the database ignores the fact that some of these paths may partially overlap with each other.) Each path may have a name, a length (to the nearest mile) and a grading that indicates how difficult it is. Each path starts in a town and leads to the summit of just one mountain. (For the sake of simplicity, the database ignores the fact that a path could continue from a summit to another summit.) Each team is responsible for conducting rescues on the mountains surrounding the town in which the team is based. A team may be responsible for more than one path on a particular mountain. This scenario would occur, for example, if there were two paths from the town where a team is based to the summit of the same mountain. In order to avoid more than one team searching the same path, each path is the sole responsibility of just one rescue team. A mountain may be served by more than one team if it has more than one path to the summit. This scenario could occur, for example, if two teams are based on either side of a mountain. Each mountain rescue team is allocated one RAF base from which it may request help. Every RAF base has a unique name and operates just one type of helicopter. Sometimes the region in which a base is located is stored. An RAF base can assist more than one mountain rescue team. 

Find :

Q1)SQL statements that create the relations ?

Q2) SQL statements to query the database?

Q3) SQL statements that populate the database?

Expert Solution
steps

Step by step

Solved in 5 steps

Blurred answer
Knowledge Booster
Single Table
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education