Design, implement and document:

a database 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 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?

Question

Design, implement and document:

a database 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 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?

Accepted Answer

The answer is given below.