Our team offers a free demo database for PostgreSQL. The subject field of this database is airline flights across various airports.
$ unzip demo-big-en.zip
$ psql -U postgres
postgres=# \i demo-big-en-20170815.sql
- display scheman
demo=# \dn
List of schemas
Name | Owner
----------+-------------------
bookings | postgres
public | pg_database_owner
(2 rows)
¶Tables
- bookings
To fly with our airline, passengers book the required tickets in advance (book_date, which must be not earlier than one month before the flight). The booking is identified by its number (book_ref, a six-position combination of letters and digits). The total_amount field stores the total price of all tickets included into the booking, for all passengers.
-- Name: bookings; Type: TABLE; Schema: bookings; Owner: -
--
CREATE TABLE bookings (
book_ref character(6) NOT NULL,
book_date timestamp with time zone NOT NULL,
total_amount numeric(10,2) NOT NULL
);
demo=# \d+ bookings
Table "bookings.bookings"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+--------------------
book_ref | character(6) | | not null | | extended | | | Booking number
book_date | timestamp with time zone | | not null | | plain | | | Booking date
total_amount | numeric(10,2) | | not null | | main | | | Total booking cost
Indexes:
"bookings_pkey" PRIMARY KEY, btree (book_ref)
Referenced by:
TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)
Access method: heap
demo=# SELECT * FROM bookings limit 10;
book_ref | book_date | total_amount
----------+------------------------+--------------
000004 | 2016-08-13 20:40:00+08 | 55800.00
00000F | 2017-07-05 08:12:00+08 | 265700.00
000010 | 2017-01-09 00:45:00+08 | 50900.00
000012 | 2017-07-14 14:02:00+08 | 37900.00
000026 | 2016-08-30 16:08:00+08 | 95600.00
00002D | 2017-05-20 23:45:00+08 | 114700.00
000034 | 2016-08-08 10:46:00+08 | 49100.00
00003F | 2016-12-12 20:02:00+08 | 109800.00
000048 | 2016-09-17 06:57:00+08 | 92400.00
00004A | 2016-10-14 02:57:00+08 | 29000.00
(10 rows)
- Tickets
A ticket has a unique number (ticket_no), which consists of 13 digits. The ticket contains the passenger’s identity document number (passenger_id), as well as their first and last names (passenger_name) and contact information (contact_data). Note that neither the passenger ID, nor the name is permanent (for example, one can change the last name or passport), so it is impossible to uniquely identify all tickets of a particular passenger. For simplicity, let’s assume that all passengers are unique.
--
-- Name: tickets; Type: TABLE; Schema: bookings; Owner: -
--
CREATE TABLE tickets (
ticket_no character(13) NOT NULL,
book_ref character(6) NOT NULL,
passenger_id character varying(20) NOT NULL,
passenger_name text NOT NULL,
contact_data jsonb
);
This SQL script creates a table named "tickets" in the "bookings" schema of a PostgreSQL database. The table has the following columns:
- "ticket_no": a character field with a length of 13 characters, which cannot be NULL.
- "book_ref": a character field with a length of 6 characters, which cannot be NULL.
- "passenger_id": a varying character field with a maximum length of 20 characters, which cannot be NULL.
- "passenger_name": a text field, which cannot be NULL.
- "contact_data": a JSONB field, which can store JSON data.
The table is created with the default privileges of the user executing the script.
- Flight Segments
A flight segment connects a ticket with a flight and is identified by their numbers. Each flight segment has its price (amount) and travel class (fare_conditions).
- Flights
--
-- Name: flights; Type: TABLE; Schema: bookings; Owner: -
--
CREATE TABLE flights (
flight_id integer NOT NULL,
flight_no character(6) NOT NULL,
scheduled_departure timestamp with time zone NOT NULL,
scheduled_arrival timestamp with time zone NOT NULL,
departure_airport character(3) NOT NULL,
arrival_airport character(3) NOT NULL,
status character varying(20) NOT NULL,
aircraft_code character(3) NOT NULL,
actual_departure timestamp with time zone,
actual_arrival timestamp with time zone,
CONSTRAINT flights_check CHECK ((scheduled_arrival > scheduled_departure)),
CONSTRAINT flights_check1 CHECK (((actual_arrival IS NULL) OR ((actual_departure IS NOT NULL) AND (actual_arrival IS NOT NULL) AND (actual>
CONSTRAINT flights_status_check CHECK (((status)::text = ANY (ARRAY[('On Time'::character varying)::text, ('Delayed'::character varying)::>
);
This is a SQL script to create a table called flights in the bookings schema of a PostgreSQL database.
-
The table has 10 columns:
- flight_id: an integer value representing the flight identifier (primary key)
- flight_no: a character string of length 6 representing the flight number
- scheduled_departure: a timestamp with time zone representing the scheduled departure time
- scheduled_arrival: a timestamp with time zone representing the scheduled arrival time
- departure_airport: a character string of length 3 representing the departure airport code
- arrival_airport: a character string of length 3 representing the arrival airport code
- status: a character string of length 20 representing the flight status (on time, delayed, etc.)
- aircraft_code: a character string of length 3 representing the aircraft code
- actual_departure: a timestamp with time zone representing the actual departure time
- actual_arrival: a timestamp with time zone representing the actual arrival time
-
The flights table has several constraints:
- flights_check: checks that the scheduled arrival time is after the scheduled departure time
- flights_check1: checks that if actual_arrival is not NULL, then actual_departure is not NULL and both actual_departure and actual_arrival are not NULL
- flights_status_check: checks that the flight status is either "On Time" or "Delayed"
These constraints ensure the integrity of the data stored in the flights table.
¶ Airports
--
-- Name: airports_data; Type: TABLE; Schema: bookings; Owner: -
--
CREATE TABLE airports_data (
airport_code character(3) NOT NULL,
airport_name jsonb NOT NULL,
city jsonb NOT NULL,
coordinates point NOT NULL,
timezone text NOT NULL
);
This is the creation of the airports_data table in the bookings schema. The table has five columns:
- airport_code of type character(3) and it cannot be null.
- airport_name of type jsonb and it cannot be null.
- city of type jsonb and it cannot be null.
- coordinates of type point and it cannot be null.
- timezone of type text and it cannot be null.
The NOT NULL constraint for each column ensures that none of the columns can contain null values.
¶ Boarding Passes
--
-- Name: boarding_passes; Type: TABLE; Schema: bookings; Owner: -
--
CREATE TABLE boarding_passes (
ticket_no character(13) NOT NULL,
flight_id integer NOT NULL,
boarding_no integer NOT NULL,
seat_no character varying(4) NOT NULL
);
This is a SQL command that creates a table named "boarding_passes" in the "bookings" schema.
- The "boarding_passes" table has four columns:
- "ticket_no" column of type character(13) that stores the ticket number of the passenger.
- "flight_id" column of type integer that stores the ID of the flight.
- "boarding_no" column of type integer that stores the boarding number of the passenger.
- "seat_no" column of type character varying(4) that stores the seat number of the passenger.
This table is used to store the boarding passes of the passengers who have booked a flight.
¶Aircraft
--
-- Name: aircrafts_data; Type: TABLE; Schema: bookings; Owner: -
--
CREATE TABLE aircrafts_data (
aircraft_code character(3) NOT NULL,
model jsonb NOT NULL,
range integer NOT NULL,
CONSTRAINT aircrafts_range_check CHECK ((range > 0))
);
- This SQL script creates a table named aircrafts_data in the bookings schema with the following columns:
- aircraft_code of type character(3) and NOT NULL
- model of type jsonb and NOT NULL
- range of type integer and NOT NULL, with a CHECK constraint that ensures the value is greater than zero.
- This table seems to hold information about different types of aircraft, their models, and their ranges.
¶Seats
--
-- Name: seats; Type: TABLE; Schema: bookings; Owner: -
--
CREATE TABLE seats (
aircraft_code character(3) NOT NULL,
seat_no character varying(4) NOT NULL,
fare_conditions character varying(10) NOT NULL,
CONSTRAINT seats_fare_conditions_check CHECK (((fare_conditions)::text = ANY (ARRAY[('Economy'::character varying)::text, ('Comfort'::char>
);
- This SQL code creates a table named "seats" in the "bookings" schema of a PostgreSQL database. The table has the following columns:
- "aircraft_code" (character(3)): a non-null character string with a length of 3 that specifies the aircraft code of the plane in which the seat is located.
- "seat_no" (character varying(4)): a non-null character string with a variable length of up to 4 that specifies the seat number.
- "fare_conditions" (character varying(10)): a non-null character string with a variable length of up to 10 that specifies the fare conditions for the seat, which can either be "Economy" or "Comfort".
- Additionally, there is a constraint on the "fare_conditions" column to ensure that the values entered are either "Economy" or "Comfort".
¶ Flights View
--
-- Name: flights_v; Type: VIEW; Schema: bookings; Owner: -
--
CREATE VIEW flights_v AS
SELECT f.flight_id,
f.flight_no,
f.scheduled_departure,
timezone(dep.timezone, f.scheduled_departure) AS scheduled_departure_local,
f.scheduled_arrival,
timezone(arr.timezone, f.scheduled_arrival) AS scheduled_arrival_local,
(f.scheduled_arrival - f.scheduled_departure) AS scheduled_duration,
f.departure_airport,
dep.airport_name AS departure_airport_name,
dep.city AS departure_city,
f.arrival_airport,
arr.airport_name AS arrival_airport_name,
arr.city AS arrival_city,
f.status,
f.aircraft_code,
f.actual_departure,
timezone(dep.timezone, f.actual_departure) AS actual_departure_local,
f.actual_arrival,
timezone(arr.timezone, f.actual_arrival) AS actual_arrival_local,
(f.actual_arrival - f.actual_departure) AS actual_duration
FROM flights f,
airports dep,
airports arr
WHERE ((f.departure_airport = dep.airport_code) AND (f.arrival_airport = arr.airport_code));
The flights_v view is joining the flights table with the airports table twice to get the name and city of both the departure and arrival airports. It also includes various timezone conversions and calculates the scheduled and actual duration of each flight.
Here's a breakdown of the columns in the flights_v view:
flight_id: ID of the flight flight_no: Flight number scheduled_departure: Scheduled departure time in UTC scheduled_departure_local: Scheduled departure time in the timezone of the departure airport scheduled_arrival: Scheduled arrival time in UTC scheduled_arrival_local: Scheduled arrival time in the timezone of the arrival airport scheduled_duration: Scheduled duration of the flight (difference between scheduled arrival and departure times) departure_airport: Three-letter code of the departure airport departure_airport_name: Name of the departure airport departure_city: City where the departure airport is located arrival_airport: Three-letter code of the arrival airport arrival_airport_name: Name of the arrival airport arrival_city: City where the arrival airport is located status: Status of the flight (On Time or Delayed) aircraft_code: Three-letter code of the aircraft used for the flight actual_departure: Actual departure time in UTC actual_departure_local: Actual departure time in the timezone of the departure airport actual_arrival: Actual arrival time in UTC actual_arrival_local: Actual arrival time in the timezone of the arrival airport actual_duration: Actual duration of the flight (difference between actual arrival and departure times)
¶routes view
--
-- Name: routes; Type: VIEW; Schema: bookings; Owner: -
--
CREATE VIEW routes AS
WITH f3 AS (
SELECT f2.flight_no,
f2.departure_airport,
f2.arrival_airport,
f2.aircraft_code,
f2.duration,
array_agg(f2.days_of_week) AS days_of_week
FROM ( SELECT f1.flight_no,
f1.departure_airport,
f1.arrival_airport,
f1.aircraft_code,
f1.duration,
f1.days_of_week
FROM ( SELECT flights.flight_no,
flights.departure_airport,
flights.arrival_airport,
flights.aircraft_code,
(flights.scheduled_arrival - flights.scheduled_departure) AS duration,
(to_char(flights.scheduled_departure, 'ID'::text))::integer AS days_of_week
FROM flights) f1
GROUP BY f1.flight_no, f1.departure_airport, f1.arrival_airport, f1.aircraft_code, f1.duration, f1.days_of_week
ORDER BY f1.flight_no, f1.departure_airport, f1.arrival_airport, f1.aircraft_code, f1.duration, f1.days_of_week) f2
GROUP BY f2.flight_no, f2.departure_airport, f2.arrival_airport, f2.aircraft_code, f2.duration
)
SELECT f3.flight_no,
f3.departure_airport,
dep.airport_name AS departure_airport_name,
dep.city AS departure_city,
f3.arrival_airport,
arr.airport_name AS arrival_airport_name,
arr.city AS arrival_city,
f3.aircraft_code,
f3.duration,
f3.days_of_week
FROM f3,
airports dep,
airports arr
WHERE ((f3.departure_airport = dep.airport_code) AND (f3.arrival_airport = arr.airport_code));
The routes view seems to be a complex query that generates a table-like structure combining information from the flights table and the airports table. It calculates the duration and days of the week for each flight, and groups them by flight number, departure airport, arrival airport, aircraft code, duration, and days of the week. The resulting table contains information about each flight route, including the departure and arrival airports, their names and cities, the aircraft code, the duration, and the days of the week in which the flight operates. This view could be used to generate reports or queries that require information about flight routes, such as analyzing flight demand or scheduling maintenance on aircraft.
¶The “now” Function
--
-- Name: now(); Type: FUNCTION; Schema: bookings; Owner: -
--
CREATE FUNCTION now() RETURNS timestamp with time zone
LANGUAGE sql IMMUTABLE
AS $$SELECT '2017-08-15 18:00:00'::TIMESTAMP AT TIME ZONE 'Europe/Moscow';$$;
--
-- Name: FUNCTION now(); Type: COMMENT; Schema: bookings; Owner: -
--
COMMENT ON FUNCTION now() IS 'Point in time according to which the data are generated';
The above SQL code creates a function named now() in the bookings schema that returns a timestamp with time zone data type. However, the function is not actually returning the current timestamp but a fixed timestamp value of '2017-08-15 18:00:00' in the Europe/Moscow timezone.
The function is defined as IMMUTABLE which means that it always returns the same result given the same arguments and can be optimized by the database.
A comment is added to the function using the COMMENT ON statement, which provides a description of what the function does.