Applied Database Systems - Tutorial 5

Today's tutorial is a data integration and data transformation exercise similar to the project part in Assignment 2. You should be capable to complete the following exercise based on the SQL statements that were used in the previous tutorials.

 Exercise

You are given the following relational database schema together with the respective load files:

CREATE TABLE munro_magic(
         height int NOT NULL,
         name varchar(80) NOT NULL,
         type char(2) NOT NULL,
         gridref char(10) NOT NULL
);

CREATE TABLE munro_magic_routes(
         id int NOT NULL,
         ascent char(6) NOT NULL,
         distance char(8) NOT NULL,
         time char(8) NOT NULL,
         mname1 varchar(80) NOT NULL,
         mname2 varchar(80),
         mname3 varchar(80),
         mname4 varchar(80),
         mname5 varchar(80),
         start_point varchar(200) NOT NULL,
         rating int,
         PRIMARY KEY(id)
);

CREATE TABLE visitscotland(
         name varchar(80) NOT NULL,
         height varchar(11) NOT NULL,
         region varchar(40) NOT NULL
);

Relation munro_magic is the same as in the exercise of Tutorial 2. Note, however, that the data has slightly changed and you have to overwrite any existing copy of the data that results from previous tutorials.

Relation munro_magic_routes is a listing of recommended routes by the Munro Magic Web-Site. Each route has a unique id, information about the routes ascent, distance, and projected time to complete it. Each route furthermore contains a listing of up to five mountains the rout passes along, a description of the starting point, and an optional rating (between 1 and 5).

Relation visitscotland contains a listing of Munros together with their height (in metres) and the name of the region they are located in.

Your first task is to create a copy of the given database in your PostgreSQL database (note that the order of columns in the load files is similar to the order of columns in the given CREATE TABLE statements; NULL values are represented by value null; the column delimiter is |).

The schema of your target database is as follows:

CREATE TABLE regions (
         rid int NOT NULL,
         rname varchar(120) NOT NULL,
         PRIMARY KEY (rid)
);

CREATE TABLE munros(
         mid int NOT NULL,
         mname varchar(80) NOT NULL,
         gridref char(10) NOT NULL,
         heightm int NOT NULL,
         rid int NOT NULL,
         FOREIGN KEY (rid) REFERENCES regions(rid),
         PRIMARY KEY(mid)
);

CREATE TABLE routes(
         rid int NOT NULL,
         ascent char(6) NOT NULL,
         distance char(8) NOT NULL,
         time char(8) NOT NULL,
         start_point varchar(200) NOT NULL,
         rating int,
         PRIMARY KEY (rid)
);

CREATE TABLE passes_by(
         rid int NOT NULL,
         mid int NOT NULL,
         FOREIGN KEY (rid) REFERENCES routes(rid),
         FOREIGN KEY (mid) REFERENCES munros(mid),
         PRIMARY KEY (rid, mid)
);

Relations regions, and munros are similar to those in previous exercises. Relation routes list recommended hiking routes, and passes_by establishes the N:M-relationship between routes and Munros that are located on them.

Your next task is to create the given relations in your PostgreSQL database and populate the database in the following way:

  • regions contains a listing of distinct region names found in visitscotland. Regions should be numbered starting from 1.
  • munros contains all Munros in visitscotland. Munros should be numbered starting from 1. In order to obtain the region id (rid) it is save to assume that the names of regions are unique. The gridref value can be obtained from munro_magic. We assume that the combination of name and height uniquely identifies a Munro. Note that names in munro_magic contain additional information in brackets. This information should be removed when joining visitscotland and munro_magic on their names.
  • routes contains a tuple for each route in munro_magic_routes. However, we only want to include routes that pass along Munros for which we have regional information (i.e., for which there is a tuple in munros). Note that the names of mountains in munro_magic_routes are equal to names in munro_magic, i.e., these names may contain information in brackets that has to be taken care of when identifying those routes that are to be deleted. It is save to assume that the names of mountains in munro_magic are unique.
  • For each of the remaining (non-null) Munro names in munro_magic_routes there should be a tuple in passes_by linking the respective route and Munro.

Note: This tutorial makes use of the following insert statement:

INSERT INTO table1(column1, column2, ...) SELECT column1, column2, ... FROM table2 ....

For more details, check the online documentation.