· 5 years ago · May 05, 2020, 01:50 PM
1/*==============================================================*/
2/* DBMS name: Sybase SQL Anywhere 12 */
3/* Created on: 2020/5/1 17:42:09 */
4/*==============================================================*/
5
6
7drop table if exists orders;
8
9drop table if exists flight;
10
11drop table if exists authority;
12
13drop table if exists information;
14
15drop table if exists users;
16
17drop table if exists FlightInfo;
18
19/* FlightInfo->flightinfo(包括外键) type->_type function->_function orders.id:varchar(32)->integer */
20/*==============================================================*/
21/* Table: flightinfo */
22/*==============================================================*/
23create table flightinfo
24(
25 id varchar(8) not null,
26 time_start time null, /*注意格式*/
27 time_end time null,
28 place_start varchar(128) null,
29 place_end varchar(128) null,
30 first_fare numeric(10,2) null,
31 business_fare numeric(10,2) null,
32 economy_fare numeric(10,2) null,
33 constraint PK_FLIGHTINFO primary key clustered (id)
34);
35
36/*==============================================================*/
37/* Table: authority */
38/*==============================================================*/
39create table authority
40(
41 _function varchar(128) not null,
42 _type integer not null, /*type统一为：0管理员；1地勤人员；2乘客*/
43 constraint PK_AUTHORITY primary key clustered (_function, _type)
44);
45
46/*==============================================================*/
47/* Table: flight */
48/*==============================================================*/
49create table flight
50(
51 id varchar(16) not null,
52 flightinfo_id varchar(8) not null,
53 Date date not null, /*注意格式*/
54 status varchar(8) not null, /*统一分为："normal"，"delay","cancel"*/
55 cnt_first integer default 8,
56 cnt_business integer default 12,
57 cnt_economy integer default 160,
58 constraint PK_FLIGHT primary key clustered (id)
59);
60
61/*==============================================================*/
62/* Table: information */
63/*==============================================================*/
64create table information
65(
66 error integer not null, /*error为错误编号*/
67 information varchar(128) not null, /*打印用的信息*/
68 constraint PK_INFORMATION primary key clustered (error, information)
69);
70
71/*==============================================================*/
72/* Table: orders */
73/*==============================================================*/
74create table orders
75(
76 u_id varchar(18) not null,
77 flight_id varchar(16) not null,
78 class varchar(16) not null, /*"first class", "business class", "economy class"*/
79 fare numeric(10,2) not null,
80 id integer not null,
81 seat_number integer null,
82 luggage_weight numeric(5,2) null,
83 lugggage_inspection varchar(1024) null, /*如未通过，在这个属性里面写是什么东西导致的，如"power bank"，若通过，则依然是null*/
84 info_confirm bit not null,
85 constraint PK_ORDER primary key clustered (id)
86);
87
88/*==============================================================*/
89/* Table: users */
90/*==============================================================*/
91create table users
92(
93 id varchar(18) not null, /*身份证号*/
94 _type integer not null, /*同上*/
95 password varchar(64) not null,
96 money numeric(10,2) not null,
97 status bit not null,
98 constraint PK_USER primary key clustered (id)
99);
100
101alter table flight
102 add constraint FK_FLIGHT_REFERENCE_FLIGHTIN foreign key (flightinfo_id)
103 references flightinfo (id)
104 on update restrict
105 on delete restrict;
106
107alter table orders
108 add constraint FK_ORDER_REFERENCE_USER foreign key (u_id)
109 references users (id)
110 on update restrict
111 on delete restrict;
112
113alter table orders
114 add constraint FK_ORDER_REFERENCE_FLIGHT foreign key (flight_id)
115 references flight (id)
116 on update restrict
117 on delete restrict;