开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

航空公司数据库设计

简介:
+关注继续查看

标签

PostgreSQL , 航空公司数据库设计


背景

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

这个DEMO来自postgrespro:

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

pic

加载航空公司数据

可以任选一个数据集,使用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

pic

描述

主要的实体是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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
数据库设计
数据库设计
8 0
数据库设计
数据库设计
29 0
数据库设计
<p><br></p> <p><br></p> <p></p> <p class="p0" style="margin-top:0pt; margin-bottom:0pt; padding-top:0px; padding-bottom:0px; font-family:Helvetica,Tahoma,Arial,sans-serif; font-size:14px; line-
2140 0
数据库设计
数据库设计 ER模型 Entity-Relationship,实体-关系模型。该模型的图形化表示就是ER图。 1.用矩形表示实体。 2.用椭圆表示实体属性。 3.用零星表示是体检关系。 例一:有一个简单的数据库系统,实体有班级、学生、课程。 一个班有若干个学生,一个学生只能属于一个班。 一个学生可以选修多门课,一门课有若干学生选修。 将ER图转换为关系数据模型 1.
1324 0
数据库设计
数据库的设计既要满足用户的需求,又与给定的应用环境密切相关,因此必须采用系统化、规范化的设计方法,按需求分析、概念设计、逻辑设计、物理设计四个阶段展开。 需求分析就是分析用户的要求,是数据库设计的基础。
1024 0
数据库优化设计方案
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/chinahuyong/article/details/4344546 本文首先讨论了基于第三范式的数据库表的基本设计,着重论述了建立主键和索引的策略和方案,然后从数据库表的扩展设计和库表对象的放置等角度概述了数据库管理系统的优化方案。
897 0
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
在业务量暴增中痛并快乐——数据交易平台的成长记事
立即下载
预测分析技术让你做客户“肚子里的蛔虫”
立即下载
在业务量暴增中痛并快乐--数据交易平台的成长记事
立即下载