toursdb_readme.htm

# Derby Example and Demo Database toursdb

## 1. Introduction

This document describes some aspects of the Derby Example and demo database toursdb.

## 2. Purpose of the database toursdb

The toursdb is provided as an example and demo database to illustrate some functionality of the Derby RDBMS. Many of the examples in the Derby documentation use tables that exist in the toursdb database.

The toursdb database is created, and can be recreated using the files in <your derby install directory>/demo/programs if you have an installation that includes these.

The built database is placed in <your derby install directory>/demo/databases.

## 3. The toursdb schema

The toursdb schema is the default schema, 'APP'.

The toursdb tables are:
(Note: in the following table no description is given where the columnname is deemed to be explanation enough.)
AIRLINES
COUNTRIES
CITIES
FLIGHTS
FLIGHTAVAILABILITY
MAPS
FLIGHTS_HISTORY

Constraints and Indices are listed underneath the respective tables

TRIGGERS

TableName | ColumnName | DataType | other | Description
AIRLINES |  |  |  | Information about Airline companies
| AIRLINE | CHAR(2) | PK, not null | Airline company code
| AIRLINE_FULL | VARCHAR(24) |  | Full name of Airline company
| BASIC_RATE | DOUBLE_PRECISION |  | Basic rate for flying with this airline
| DISTANCE_DISCOUNT | DOUBLE PRECISION |  | Discount for larger distances
| BUSINESS_LEVEL_FACTOR | DOUBLE PRECISION |  |
| FIRSTCLASS_LEVEL_FACTOR | DOUBLE PRECISION |  | Percentage addition for first class cost
| ECONOMY_SEATS | INTEGER |  | No. of economy seats
| BUSINESS_SEATS | INTEGER |  | No. of business seats
| FIRSTCLASS_SEATS | INTEGER |  | No. of first class seats

CONSTRAINTS:

constraint AIRLINES_PK AIRLINE Primary Key

COUNTRIES |  |  |  |
| COUNTRY | VARCHAR(26) | not null | Country name in English
| COUNTRY_ISO_CODE | CHAR(2) | PK, not null | Country code
| REGION | VARCHAR(26) |  | Region

CONSTRAINTS:

constraint COUNTRIES_PK COUNTRY_ISO_CODE Primary Key
constraint COUNTRIES_UNQ_NM Unique Country
constraint COUNTRIES_UC Check country_ISO_code = upper(country_ISO_code)

CITIES |  |  |  |
| CITY_ID | INTEGER | PK, not null |
| CITY_NAME | VARCHAR(24) | not null |
| COUNTRY | VARCHAR(26) | not null |
| AIRPORT | VARCHAR(3) |  | airport code
| LANGUAGE | VARCHAR(16) |  |
| COUNTRY_ISO_CODE | CHAR(2) | FK |

CONSTRAINTS:

constraint CITIES_PK CITY_ID Primary Key
constraint COUNTRIES_FK Foreign Key COUNTRY_ISO_CODE references

COUNTRIES.COUNTRY_ISO_CODE

FLIGHTS |  |  |  |
| FLIGHT_ID | CHAR(6) | PK, not null |
| SEGMENT_NUMBER | INTEGER | PK, not null | number for segment of a flight
| ORIG_AIRPORT | CHAR(3) |  | airport where flight originates
| DEPART_TIME | TIME |  | scheduled departure time
| DEST_AIRPORT | CHAR(3) |  | destination airport
| ARRIVE_TIME | TIME |  | scheduled arrival time
| MEAL | CHAR(1) |  | indicator for type of meals available B - Breakfast L - Lunch D - Dinner S - Snack
| FLYING_TIME | DOUBLE PRECISION |  | scheduled duration of flight
| MILES | INTEGER |  | length of flight
| AIRCRAFT | VARCHAR(6) |  | type of aircraft

CONSTRAINTS:

constraint FLIGHTS_PK FLIGHT_ID,SEGMENT_NUMBER Primary Key
constraint MEAL_COUNSTRAINT Check meal IN ('B', 'L', 'D', 'S')

INDICES:

index DESTINDEX on FLIGHTS (DEST_AIRPORT)
index ORIGINDEX on FLIGHTS (ORIG_AIRPORT)

FLIGHTAVAILABILITY |  |  |  |
| FLIGHT_ID | CHAR(6) | PK, not null |
| SEGMENT_NUMBER | INTEGER | PK, not null | number for segment of a flight
| FLIGHT_DATE | DATE | PK, not null | date on which the flight takes place
| ECONOMY_SEATS_TAKEN | INTEGER | def. 0 |
| BUSINESS_SEATS_TAKEN | INTEGER | def. 0 |
| FIRSTCLASS_SEATS_TAKEN | INTEGER | def. 0 |

def. = default

CONSTRAINTS:

constraint FLIGHT_AVAILABILITY_PK FLIGHT_ID, SEGMENT_NUMBER, FLIGHT_DATE Primary Key
constraint FLIGHTS_FK2 Foreign Key FLIGHT_ID,SEGMENT_NUMBER references

FLIGHTS. FLIGHT_ID, SEGMENT_NUMBER

MAPS |  |  |  |
| MAP_ID | INTEGER | not null, gen. alw. as id. st. 1 incr. by 1 |
| MAP_NAME | VARCHAR(24) | not null |
| REGION | VARCHAR(26) |  |
| AREA DECIMAL(8,4) |  | not null |
| PHOTO_FORMAT | VARCHAR(26) | not null |
| PICTURE | BLOB(102400) |  |

gen alw. as id. st. 1 incr. by 1 = generated always as identity (start with 1, increment by 1)

CONSTRAINTS:

constraint Unique MAP_ID, MAP_NAME

FLIGHTS_HISTORY |  |  |  | archived flights
| FLIGHT_ID | CHAR(6) | not null |
| SEGMENT_NUMBER | INTEGER | not null | number for segment of a flight
| ORIG_AIRPORT | CHAR(3) |  | airport where flight originates
| DEPART_TIME | TIME |  | scheduled departure time
| DEST_AIRPORT | CHAR(3) |  | destination airport
| ARRIVE_TIME | TIME |  | scheduled arrival time
| MEAL | CHAR(1) |  | indicator for type of meals available B - Breakfast L - Lunch D - Dinner S - Snack
| FLYING_TIME | DOUBLE PRECISION |  | scheduled duration of flight
| MILES | INTEGER |  | length of flight
| AIRCRAFT | VARCHAR(6) |  | type of aircraft
| STATUS | VARCHAR(20) |  | code indicating which trigger created the record

TRIGGERS:

TRIG1

after update on FLIGHTS
referencing old as UPDATEDROW
for each row mode DB2SQL
insert into FLIGHTS_HISTORY values (
UPDATEDROW.FLIGHT_ID,
UPDATEDROW.SEGMENT_NUMBER,
UPDATEDROW.ORIG_AIRPORT,
UPDATEDROW.DEPART_TIME,
UPDATEDROW.DEST_AIRPORT,
UPDATEDROW.ARRIVE_TIME,
UPDATEDROW.MEAL,
UPDATEDROW.FLYING_TIME,
UPDATEDROW.MILES,
UPDATEDROW.AIRCRAFT,
'INSERTED FROM TRIG1');

TRIG2

after delete on FLIGHTS
for each statement mode DB2SQL
insert into FLIGHTS_HISTORY (STATUS) values ('INSERTED FROM TRIG2');