This document describes some aspects of the Derby Example and demo 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.
The toursdb schema is the default schema, 'APP'.
The toursdb tables are:
(Note: in the following tables 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.
The AIRLINES table provides information about airline companies.
| ColumnName | DataType | Description |
|---|---|---|
| AIRLINE | CHAR(2) | Airline company code
PK, not null |
| 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 | Percentage addition for business class cost |
| 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
The COUNTRIES table provides information about the countries served by the airlines.
| ColumnName | DataType | Description |
|---|---|---|
| COUNTRY | VARCHAR(26) | Country name in English
Not null |
| COUNTRY_ISO_CODE | CHAR(2) | Country code
PK, not null |
| 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)
The CITIES table provides information about the cities served by the airlines.
| ColumnName | DataType | Description |
|---|---|---|
| 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
The FLIGHTS table provides information about airline flights.
| ColumnName | DataType | Description |
|---|---|---|
| FLIGHT_ID | CHAR(6) | PK, not null |
| SEGMENT_NUMBER | INTEGER | Number for segment of a flight
PK, not null |
| 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_CONSTRAINT Check meal IN ('B', 'L', 'D', 'S')
INDICES:
index DESTINDEX on FLIGHTS (DEST_AIRPORT) index ORIGINDEX on FLIGHTS (ORIG_AIRPORT)
The FLIGHTAVAILABILITY table provides information about the availability of flights.
| ColumnName | DataType | Description |
|---|---|---|
| FLIGHT_ID | CHAR(6) | PK, not null |
| SEGMENT_NUMBER | INTEGER | Number for segment of a flight
PK, not null |
| FLIGHT_DATE | DATE | Date on which the flight takes place
PK, not null |
| ECONOMY_SEATS_TAKEN | INTEGER | Default is 0 |
| BUSINESS_SEATS_TAKEN | INTEGER | Default is 0 |
| FIRSTCLASS_SEATS_TAKEN | INTEGER | Default is 0 |
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
The MAPS table provides information about flight maps.
| ColumnName | DataType | Description |
|---|---|---|
| MAP_ID | INTEGER | Not null, generated always as identity (start with 1, increment 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) |
CONSTRAINTS:
constraint Unique MAP_ID, MAP_NAME
The FLIGHTS_HISTORY table provides information about archived flights.
| ColumnName | DataType | Description |
|---|---|---|
| FLIGHT_ID | CHAR(6) | Not null |
| SEGMENT_NUMBER | INTEGER | Number for segment of a flight
Not null |
| 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 |
TRIG1
after update on FLIGHTS
referencing old as UPDATEDROW
for each row
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
insert into FLIGHTS_HISTORY (STATUS) values ('INSERTED FROM TRIG2');