设为首页收藏本站
开启辅助访问

航空公司数据库设计

 您所在的位置：网站首页 › 航班时刻表包括座位吗 › 航空公司数据库设计

航空公司数据库设计

2024-01-05 08:11| 来源: 网络整理| 查看: 265

背景

通过学习航空公司数据库设计，学习PostgreSQL的使用。

这个DEMO来自postgrespro：

https://postgrespro.com/docs/postgrespro/10/demodb-bookings.html

 

加载航空公司数据

可以任选一个数据集，使用pg_restore还原到你的PostgreSQL数据库中，还原时，会新建一个demo数据库，数据导入demo数据库中。

demo-small.zip (21 MB) — flight data for one month (DB size is about 300 MB)

demo-medium.zip (62 MB) — flight data for three months (DB size is about 700 MB)

demo-big.zip (232 MB) — flight data for one year (DB size is about 2.5 GB)

航空公司数据库schema

 

描述

主要的实体是bookings(demo库里的schema)，所有的对象都在这里。

一个booking可以包括几名乘客，每位乘客一张票。票有一个独特的号码，包括乘客的信息。因此，乘客不是一个单独的实体。乘客的姓名和身份证件号码都可以随时间变化，因此不可能唯一地识别某一个人的所有票; 为了简单起见，假设所有乘客都是唯一的。

机票包括一个或多个飞行段(ticket_flights)。如果在起飞点和目的地之间没有直达航班，或者是往返机票，可以将几个航班段包括在单张机票中。尽管在模式中没有约束，但是假设预订中的所有机票都有相同的航班段。

每个航班(flights)从一个机场飞往另一个机场(airports)。同一航班号的航班有相同的出发点和目的地，但不同的起飞日期。

在飞行登记时，乘客需要打印登机牌(boarding_pass)，包含座位号码信息。旅客可以办理登机手续，只有在这趟航班被列入ticket的情况下才能办理登机手续。飞行座椅的组合必须是唯一的，以避免为同一个座位签发两个登机牌。

飞机上的座位(seats)和不同的旅行等级之间的分配取决于飞机(aircrafts)的飞行模式。假设每个飞行器模型只有一个舱位结构。数据库中不检查登机牌上的座位号是否有相应的飞机座位(这样的验证可以使用表触发器，或者在应用程序中调度)。

Schema Objects

大量的使用了JSON类型，视图。

Name | Type | Small | Medium | Big | Description -----------------+---------------+--------+--------+--------+------------------------- aircrafts | view | | | | Aircraft aircrafts_data | table | 16 kB | 16 kB | 16 kB | Aircraft (translations) airports | view | | | | Airports airports_data | table | 56 kB | 56 kB | 56 kB | Airports (translations) boarding_passes | table | 31 MB | 102 MB | 427 MB | Boarding passes bookings | table | 13 MB | 30 MB | 105 MB | Bookings flights | table | 3 MB | 6 MB | 19 MB | Flights flights_v | view | | | | Flights routes | view | | | | Routes seats | table | 88 kB | 88 kB | 88 kB | Seats ticket_flights | table | 64 MB | 145 MB | 516 MB | Flight segments tickets | table | 47 MB | 107 MB | 381 MB | Tickets 对象解释

1、视图bookings.aircrafts

包括飞机编号，飞机机型，飞机最大飞行公里数。

Column | Type | Modifiers | Description ---------------+---------+--------------+----------------------------------- aircraft_code | char(3) | not null | Aircraft code, IATA model | text | not null | Aircraft model range | integer | not null | Maximal flying distance, km View definition: SELECT ml.aircraft_code, ml.model ->> lang() AS model, ml.range FROM aircrafts_data ml;

2、表bookings.aircrafts_data

机型使用JSON存储，包含更多内容。

Column | Type | Modifiers | Description ---------------+---------+--------------+----------------------------------- aircraft_code | char(3) | not null | Aircraft code, IATA model | jsonb | not null | Aircraft model range | integer | not null | Maximal flying distance, km Indexes: PRIMARY KEY, btree (aircraft_code) Check constraints: CHECK (range > 0) Referenced by: TABLE "flights" FOREIGN KEY (aircraft_code) REFERENCES aircrafts_data(aircraft_code) TABLE "seats" FOREIGN KEY (aircraft_code) REFERENCES aircrafts_data(aircraft_code) ON DELETE CASCADE

3、视图bookings.airports

机场信息，机场代码，机场名，城市，经纬度，时区。

Column | Type | Modifiers | Description --------------+---------+--------------+-------------------------------------------- airport_code | char(3) | not null | Airport code airport_name | text | not null | Airport name city | text | not null | City coordinates | point | not null | Airport coordinates (longitude and latitude) timezone | text | not null | Airport time zone View definition: SELECT ml.airport_code, ml.airport_name ->> lang() AS airport_name, ml.city ->> lang() AS city, ml.coordinates, ml.timezone FROM airports_data ml;

4、表bookings.airports_data

机场代码，机场名（JSON存储），城市（JSON存储），经纬度，时区。

Column | Type | Modifiers | Description --------------+---------+--------------+-------------------------------------------- airport_code | char(3) | not null | Airport code airport_name | jsonb | not null | Airport name city | jsonb | not null | City coordinates | point | not null | Airport coordinates (longitude and latitude) timezone | text | not null | Airport time zone Indexes: PRIMARY KEY, btree (airport_code) Referenced by: TABLE "flights" FOREIGN KEY (arrival_airport) REFERENCES airports_data(airport_code) TABLE "flights" FOREIGN KEY (departure_airport) REFERENCES airports_data(airport_code)

5、表bookings.boarding_passes

CHECK IN信息，机票ID，飞行ID，登机口，座位号。

Column | Type | Modifiers | Description -------------+------------+--------------+-------------------------- ticket_no | char(13) | not null | Ticket number flight_id | integer | not null | Flight ID boarding_no | integer | not null | Boarding pass number seat_no | varchar(4) | not null | Seat number Indexes: PRIMARY KEY, btree (ticket_no, flight_id) UNIQUE CONSTRAINT, btree (flight_id, boarding_no) UNIQUE CONSTRAINT, btree (flight_id, seat_no) Foreign-key constraints: FOREIGN KEY (ticket_no, flight_id) REFERENCES ticket_flights(ticket_no, flight_id)

6、表bookings.bookings

一条记录代表一趟行程，日期，该行程所有机票加起来的金额。

Column | Type | Modifiers | Description --------------+---------------+--------------+--------------------------- book_ref | char(6) | not null | Booking number book_date | timestamptz | not null | Booking date total_amount | numeric(10,2) | not null | Total booking cost Indexes: PRIMARY KEY, btree (book_ref) Referenced by: TABLE "tickets" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)

7、表bookings.flights

航班信息，编号，状态等。

Column | Type | Modifiers | Description ---------------------+-------------+--------------+----------------------------- flight_id | serial | not null | Flight ID flight_no | char(6) | not null | Flight number scheduled_departure | timestamptz | not null | Scheduled departure time scheduled_arrival | timestamptz | not null | Scheduled arrival time departure_airport | char(3) | not null | Airport of departure arrival_airport | char(3) | not null | Airport of arrival status | varchar(20) | not null | Flight status aircraft_code | char(3) | not null | Aircraft code, IATA actual_departure | timestamptz | | Actual departure time actual_arrival | timestamptz | | Actual arrival time Indexes: PRIMARY KEY, btree (flight_id) UNIQUE CONSTRAINT, btree (flight_no, scheduled_departure) Check constraints: CHECK (scheduled_arrival > scheduled_departure) CHECK ((actual_arrival IS NULL) OR ((actual_departure IS NOT NULL AND actual_arrival IS NOT NULL) AND (actual_arrival > actual_departure))) CHECK (status IN ('On Time', 'Delayed', 'Departed', 'Arrived', 'Scheduled', 'Cancelled')) Foreign-key constraints: FOREIGN KEY (aircraft_code) REFERENCES aircrafts(aircraft_code) FOREIGN KEY (arrival_airport) REFERENCES airports(airport_code) FOREIGN KEY (departure_airport) REFERENCES airports(airport_code) Referenced by: TABLE "ticket_flights" FOREIGN KEY (flight_id) REFERENCES flights(flight_id)

8、表bookings.seats

座位信息

Column | Type | Modifiers | Description -----------------+-------------+--------------+-------------------- aircraft_code | char(3) | not null | Aircraft code, IATA seat_no | varchar(4) | not null | Seat number fare_conditions | varchar(10) | not null | Travel class Indexes: PRIMARY KEY, btree (aircraft_code, seat_no) Check constraints: CHECK (fare_conditions IN ('Economy', 'Comfort', 'Business')) Foreign-key constraints: FOREIGN KEY (aircraft_code) REFERENCES aircrafts(aircraft_code) ON DELETE CASCADE

9、表bookings.ticket_flights

机票与航班关联的信息

Column | Type | Modifiers | Description -----------------+---------------+--------------+--------------------- ticket_no | char(13) | not null | Ticket number flight_id | integer | not null | Flight ID fare_conditions | varchar(10) | not null | Travel class amount | numeric(10,2) | not null | Travel cost Indexes: PRIMARY KEY, btree (ticket_no, flight_id) Check constraints: CHECK (amount >= 0) CHECK (fare_conditions IN ('Economy', 'Comfort', 'Business')) Foreign-key constraints: FOREIGN KEY (flight_id) REFERENCES flights(flight_id) FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no) Referenced by: TABLE "boarding_passes" FOREIGN KEY (ticket_no, flight_id) REFERENCES ticket_flights(ticket_no, flight_id)

10、表bookings.tickets

机票销售记录。

Column | Type | Modifiers | Description ----------------+-------------+--------------+----------------------------- ticket_no | char(13) | not null | Ticket number book_ref | char(6) | not null | Booking number passenger_id | varchar(20) | not null | Passenger ID passenger_name | text | not null | Passenger name contact_data | jsonb | | Passenger contact information Indexes: PRIMARY KEY, btree (ticket_no) Foreign-key constraints: FOREIGN KEY (book_ref) REFERENCES bookings(book_ref) Referenced by: TABLE "ticket_flights" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)

11、视图bookings.flights_v

航班视图

Column | Type | Description ---------------------------+-------------+-------------------------------------- flight_id | integer | Flight ID flight_no | char(6) | Flight number scheduled_departure | timestamptz | Scheduled departure time scheduled_departure_local | timestamp | Scheduled departure time, | | local time at the point of departure scheduled_arrival | timestamptz | Scheduled arrival time scheduled_arrival_local | timestamp | Scheduled arrival time, | | local time at the point of destination scheduled_duration | interval | Scheduled flight duration departure_airport | char(3) | Departure airport code departure_airport_name | text | Departure airport name departure_city | text | City of departure arrival_airport | char(3) | Arrival airport code arrival_airport_name | text | Arrival airport name arrival_city | text | City of arrival status | varchar(20) | Flight status aircraft_code | char(3) | Aircraft code, IATA actual_departure | timestamptz | Actual departure time actual_departure_local | timestamp | Actual departure time, | | local time at the point of departure actual_arrival | timestamptz | Actual arrival time actual_arrival_local | timestamp | Actual arrival time, | | local time at the point of destination actual_duration | interval | Actual flight duration

12、视图bookings.routes

航班路由信息

Column | Type | Description ------------------------+-----------+------------------------------------- flight_no | char(6) | Flight number departure_airport | char(3) | Departure airport code departure_airport_name | text | Departure airport name departure_city | text | City of departure arrival_airport | char(3) | Arrival airport code arrival_airport_name | text | Arrival airport name arrival_city | text | City of arrival aircraft_code | char(3) | Aircraft code, IATA duration | interval | Flight duration days_of_week | integer[] | Days of the week on which flights are performed

13、函数bookings.now

回退到数据集的快照时间函数。

14、含bookings.lang

显示语言。

使用

1、修改参数，适配测试样本。（注释语言、now函数回溯快照（倒推到数据集的时间节点））

ALTER DATABASE demo SET bookings.lang = en; alter role all set search_path=bookings, public;

2、查询（略），详见参考链接

3、购票

BEGIN; INSERT INTO bookings (book_ref, book_date, total_amount) VALUES ('_QWE12', bookings.now(), 0); INSERT INTO tickets (ticket_no, book_ref, passenger_id, passenger_name) VALUES ('_000000000001', '_QWE12', '1749 051790', 'ALEKSANDR RADISHCHEV'); INSERT INTO ticket_flights (ticket_no, flight_id, fare_conditions, amount) VALUES ('_000000000001', 8525, 'Business', 0), ('_000000000001', 4967, 'Business', 0); COMMIT;

...略

参考

https://postgrespro.com/docs/postgrespro/10/demodb-bookings.html



【本文地址】


今日新闻

推荐新闻

CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3