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
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');