2021-10-30第二次数据库E-R与脚本

T31作业目录

第二次 数据库E-R与脚本

---

前言

T31作业

---

一、数据库E-R图

1.车票

二、建库脚本

drop table if exists train_carriage;

drop index uk_train_number on train_number;

drop table if exists train_number;

drop table if exists train_pass_stop_station;

drop table if exists train_seat_used;

drop table if exists train_ticket;

drop table if exists train_timetable;

/*==============================================================*/
/* Table: train_carriage                                        */
/*==============================================================*/
create table train_carriage
(
   id                   bigint(20) not null,
   number_id            bigint(20),
   carriage_no          int(11),
   seat_type            varchar(20),
   seat_cols            int(11),
   seat_count           int(11),
   seat_price           int(11),
   comment              varchar(50),
   create_time          timestamp,
   update_time          timestamp,
   primary key (id)
);

/*==============================================================*/
/* Table: train_number                                          */
/*==============================================================*/
create table train_number
(
   id                   bigint not null auto_increment comment 'Id',
   train_number         varchar(20) comment '车次号',
   train_type           char(2) comment '类别',
   carriage_count       int(11) comment '车厢数量',
   start_station        varchar(20) comment '始发站',
   end_station          varchar(20) comment '终点站',
   start_time           int(11) comment '开始时间',
   end_time             int(11) comment '终到时间',
   arrive_type          varchar(20),
   duration             int(11),
   run_type             varchar(20),
   comment              varchar(50),
   create_time          timestamp,
   update_time          timestamp,
   primary key (id)
);

alter table train_number comment '车次表';

/*==============================================================*/
/* Index: uk_train_number                                       */
/*==============================================================*/
create unique index uk_train_number on train_number
(
   train_number
);

/*==============================================================*/
/* Table: train_pass_stop_station                               */
/*==============================================================*/
create table train_pass_stop_station
(
   id                   bigint(20) not null,
   number_id            bigint(20),
   train_number         varchar(20),
   arrive_type          varchar(20),
   arrive_time          int(11),
   start_time           int(11),
   duration             int(11),
   station_num          varchar(20),
   station_name         varchar(20),
   comment              varchar(50),
   create_time          timestamp,
   update_time          timestamp,
   primary key (id)
);

/*==============================================================*/
/* Table: train_seat_used                                       */
/*==============================================================*/
create table train_seat_used
(
   id                   bigint(20),
   tra_id               bigint(20),
   number_id            bigint(20),
   train_number         varchar(20),
   train_date           date,
   start_station        varchar(20),
   end_station          varchar(20),
   carriage_no          int(11),
   seat_cols            int(11),
   seat_type            int(11),
   seat_no              int(11),
   seat_used            bigint(20),
   create_time          timestamp,
   update_time          timestamp
);

/*==============================================================*/
/* Table: train_ticket                                          */
/*==============================================================*/
create table train_ticket
(
   id                   bigint(20) not null comment 'Id',
   tra_id               bigint(20),
   tra_id2              bigint comment 'Id',
   train_date           date comment '出行日期',
   number_id            bigint(20),
   train_number         varchar(20) comment '车次号',
   start_station        varchar(20),
   end_station          varchar(20),
   from_station         varchar(20),
   to_station           varchar(20),
   start_time           int(11),
   arrival_time         int(11),
   duration             int(11),
   hard_seat_stock      int(11),
   hard_sleeper_stock   int(11),
   soft_sleeper_stock   int(11),
   first_seat_stock     int(11),
   second_seat_stock    int(11),
   special_seat_stock   int(11),
   create_time          timestamp,
   update_time          timestamp,
   primary key (id)
);

alter table train_ticket comment '车票表';

/*==============================================================*/
/* Table: train_timetable                                       */
/*==============================================================*/
create table train_timetable
(
   id                   bigint(20) not null,
   number_id            bigint(20),
   train_number         varchar(20),
   start_station        varchar(20),
   end_station          varchar(20),
   from_station         varchar(20),
   to_station           varchar(20),
   start_time           int(11),
   arrival_time         int(11),
   duration             int(11),
   commont              varchar(50),
   create_time          timestamp,
   update_time          timestamp,
   primary key (id)
);

总结

顶住。我们能赢！！！